怎样玩转千万级别的数据？ - 文章 - 伯乐在线
& 怎样玩转千万级别的数据？
大数据处理是一个头疼的问题，特别当达不到专业DBA的技术水准时，对一些数据库方面的问题感到无赖。所以还是有必要了解一些数据库方面的技巧，当然，每个人都有自己的数据库方面的技巧，只是八仙过海，所用的武功不同而已。我把我最常用的几种方式总结来与大家分享，大家还有更多的数据库设计和优化的技巧，尽量的追加到评论中，有时一篇完整的博客评论比主题更为精彩。
方法1：采用表分区技术。
第一次听说表分区，是以前的一个oracle培训。oracle既然有表分区，就想到mssql是否有表的分区，当时我回家就google了一把，资料还是有的，在这我儿只是再作一次推广，让更多的人了解和运用这些技术。
表分区，就是将一个数据量比较大的表，用某种方法把数据从物理上分成若干个小表来存储，从逻辑来看还是一个大表。首先来个结构图：
上图虽然不能很清晰的表达表分区的执行过程，但是可以看出表分区要用到那些对象，比如数据文件，文件组，分区方案，分区函数等。
我们以一个用户表(TestUser)为例，假设这个表准备用来存储中国部分公民的数据，每条数据记录着每个人所属的省份(Area)，以及每个人的姓名(UserName)，如下图所示。当数据量达到1千万的时候，查询就比较慢了，这时候的数据优化就迫在眉睫。
在优化之前，根据数据的结构，读写操作等，肯定会提出若干个解决方案。在这儿就以分区表的方案来优化数据库的查询，这儿以区域来分别存储数据，比如广东的公民存放在AreaFile01.MDF文件中，湖南的公民存放在AreaFile02.MDF的文件中，四川的公民存放在AreaFile03.MDF的文件中，以此类推其它省份，为了实现这个功能我们就得做分区方案。在做分区方案时，首先要搞清楚分区方案要涉及到的四个对象：文件组，文件，分区函数，分区方案。
a：文件组，用来组织数据文件(.MDF)的一个虚拟名称，一个文件组可以添加多个数据文件(.MDF)。打开SQL管理器，找到具体的数据库，然后右键【属性】，进入到【文件组】选项卡，添加Area01,Area02,Area03,Area04四个文件组。如图：
b：然后选择中【文件】选项卡，添加AreaFile01，AreaFile02，AreaFile03，AreaFile04，AreaFile05，AreaFile06六个数据文件（.MDF）,然后指定每个文件属于那个文件组(一个文件组可以存储多个数据文件)，以及这个文件的物理路径。在这儿大家已经看明白了，这些数据文件，就是物理上来分割一个数据表的数据的。也就是说一个表的数据有可能存储在AreaFile01中，也有可能存储在AreaFile02中，只要用某种方法来指定他们的存储规则就行了。
c：分区函数，就是指定数据的存储规则。就是告诉SQL，把新增的数据如何分区。创建一个分区函数，可以用下边的SQL语句来实现。
CREATE PARTITION FUNCTION partitionFunArea (nvarchar(50))
AS RANGE Left
FOR VALUES ('广东','湖南','四川')
CREATE PARTITION FUNCTION partitionFunArea (nvarchar(50))AS RANGE Left&&FOR VALUES ('广东','湖南','四川')
d：辛苦的创建了文件，又为其指定文件组，还建一个分区函数，目的只有一个，就是为了创建一个分区方案。分区方案可以用以下代码来创建。
CREATE PARTITION SCHEME partitionSchemeArea
AS PARTITION partitionFunArea
CREATE PARTITION SCHEME partitionSchemeAreaAS PARTITION partitionFunAreaTO (&&&&Area01,&&&&Area02,&&&&Area03,&&&&Area04)
经过紧张的四步操作，一个分区方案就呈现在我们的眼前了。接下来的事，就是我们要怎样来消费这个分区方案。
首先我们创建一人普通的表，然后给这个表指定一个分区方案。如下代码。
CREATE TABLE TestUser(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Area] nvarchar(50),
[UserName] nvarchar(50)
) ON partitionSchemeArea([Area])
CREATE TABLE TestUser(&&&&[Id] [int] IDENTITY(1,1) NOT NULL,&&&&[Area] nvarchar(50),&&&&[UserName] nvarchar(50)) ON partitionSchemeArea([Area])
为了能看到效果，再插入一些数据。
INSERT TestUser ([Area],[UserName]) Values('四川','肖一');
INSERT TestUser ([Area],[UserName]) Values('四川','肖二');
INSERT TestUser ([Area],[UserName]) Values('四川','肖三');
INSERT TestUser ([Area],[UserName]) Values('四川','肖四');
INSERT TestUser ([Area],[UserName]) Values('广东','张一');
INSERT TestUser ([Area],[UserName]) Values('广东','张二');
INSERT TestUser ([Area],[UserName]) Values('广东','张三');
INSERT TestUser ([Area],[UserName]) Values('湖南','杨一');
INSERT TestUser ([Area],[UserName]) Values('湖南','杨二');
1234567891011
INSERT TestUser ([Area],[UserName]) Values('四川','肖一');INSERT TestUser ([Area],[UserName]) Values('四川','肖二');INSERT TestUser ([Area],[UserName]) Values('四川','肖三');INSERT TestUser ([Area],[UserName]) Values('四川','肖四');&INSERT TestUser ([Area],[UserName]) Values('广东','张一');INSERT TestUser ([Area],[UserName]) Values('广东','张二');INSERT TestUser ([Area],[UserName]) Values('广东','张三');&INSERT TestUser ([Area],[UserName]) Values('湖南','杨一');INSERT TestUser ([Area],[UserName]) Values('湖南','杨二');
查询所有的数据，可以用select * from TestU 按分区查询：就用如下方法：
select $PARTITION.partitionFunArea([Area]) as 分区编号,count(id) as 记录数
from TestUser group by $PARTITION.partitionFunArea([Area])
select * from TestUser where $PARTITION.partitionFunArea([Area])=1
select * from TestUser where $PARTITION.partitionFunArea([Area])=2
select * from TestUser where $PARTITION.partitionFunArea([Area])=3
select * from TestUser where $PARTITION.partitionFunArea([Area])=4
select $PARTITION.partitionFunArea([Area]) as 分区编号,count(id) as 记录数 from TestUser group by $PARTITION.partitionFunArea([Area])select * from TestUser where $PARTITION.partitionFunArea([Area])=1select * from TestUser where $PARTITION.partitionFunArea([Area])=2select * from TestUser where $PARTITION.partitionFunArea([Area])=3select * from TestUser where $PARTITION.partitionFunArea([Area])=4
你们看我一个简单的表的分区是不是就已经完成了。呵呵，当然在实际应用中，仅仅掌握这点是不够的，比如在原分区方案上添加一个分区，删除一个分区。
方法2：用xml类型代替主从表设计，从而达到提高查询性能。
优化和提高数据库的性能，是从一个良好的数据库设计开始的。以一个会议预订系统为例，一个预订会议系统包括了会议时间，会议地点，主持人，参与人，知会人，记录者等相关信息。在的TDD，DDD模型主导的时代，在这儿为了更好的想表达我要阐述的问题，还是以表驱动模型来进行开发。
用户需求：
a：一个会议可能有多个主持人，虽然这种情况比较少，但是也有可能有。
b：一个会议有多个参与人，这个不难理解。
c：一个会议有可能要让某人知晓，这人可以参与或不参与会议，一般为高层。
d：一个会议有可能有零个或者多个记录者。
e：一个会议需要远程视频，投影仪，电脑，麦克风等会议设备中的某些设备。
f：会议预订成功，或者会议时间，会议地点等重要信息修改后，邮件通知与会人员。
常规数据库设计：
a：建一个Meeting的主表，用于存放会议名称，会议地点，会议时间等的相关信息。
b：再建一个MeetingUser的表存储主持人，参与人，知会人，记录者。
c：同样，会议所需要的设备用MeetingDevice表来存储相关的信息。如图：
这样的表结构，是比较常规的设计方法，但是在实际应用中，你会发现一些待改进的问题。比如：
a：在提取一个会议的相关信息时，会连接多个表进行查询。这种查询在很大的程序上影响了数据库性能。
b：在做修改操作时也够呛的，先修改主表的相关信息，再把主表关联的子表信息全部删除重新插入一次，这样的操作是否够吐血了。当然有人精益求精，会比较修改前和修改后的数据，再用增加，删除，修改的手段达到子表数据的更新。这样的操作在有些ORM操作中已经实现了，但当自己code代码来实现的时候，特别是在多次code的时候，感觉总是那么烦心。
吐槽了这么多，是否有更好的解决方案呢？当然，在SQL里，我们可以XML数据类型来消除主从表的设计。如图：
上面的表结构设计，是不是有一个小清新的感觉呢？很明显，可以把第一种表的设计缺陷给消除了。一个会议的相关信息都存储在了一个表的一条记录中，这样的数据看起来是不是更直观呢？
a：获取一个预订会议的详细信息，我不需要进行多个表的连接查询，我要做的是只需用C#的Linq.Xml来解析查询出来的XML字符串即可。
b：修改操作时，我只需要重新组合XML数据，一个Update就更新了与会议相关的信息，操作是不是简单多了。
表面上看这种设计已经完美了，但是用户的需求是无止境的，有一天，你收到了一个需求，查询某个用户参与过的所有会议（就是只要主持人，参与人，或者记录者中包括了这个用户，就把这些记录都给查询出来），Oh！My God
这种表结构设计应该怎么解决这个问题呢？其实可以用XQuery解决这个问题，还没接触过XQuery的那得赶快充一下电了。XQuery中最常用的有exist(),value()这些函数，这儿就不详细的介绍了，网上搜索一下有很多相关资料，如果有必要，我会把以前项目中用的XQuery技巧与大家分享。
最后，如果博客对你有帮助，我将感觉到荣幸之至。
可能感兴趣的话题
关于伯乐在线博客
在这个信息爆炸的时代，人们已然被大量、快速并且简短的信息所包围。然而，我们相信：过多“快餐”式的阅读只会令人“虚胖”，缺乏实质的内涵。伯乐在线内容团队正试图以我们微薄的力量，把优秀的原创文章和译文分享给读者，为“快餐”添加一些“营养”元素。
新浪微博：
推荐微信号
（加好友请注明来意）
– 好的话题、有启发的回复、值得信赖的圈子
– 分享和发现有价值的内容与观点
– 为IT单身男女服务的征婚传播平台
– 优秀的工具资源导航
– 翻译传播优秀的外文文章
– 国内外的精选文章
– UI,网页，交互和用户体验
– 专注iOS技术分享
– 专注Android技术分享
– JavaScript, HTML5, CSS
– 专注Java技术分享
– 专注Python技术分享
& 2017 伯乐在线mysql千万级数据update怎么优化 - 开源中国社区
当前访客身份：游客 [
当前位置：
问题是，对于有上千万的数据库进行批量修改摸个字段，但是mysql会变得很满，能不能通过优化sql的方法来提高速度
共有14个答案
<span class="a_vote_num" id="a_vote_num_
你怎么批量法,,,概要的字段添加索引没有。不过单表千万级的mysql会存在瓶颈了...
<span class="a_vote_num" id="a_vote_num_
<span class="a_vote_num" id="a_vote_num_
换 Postgresql&
--- 共有 2 条评论 ---
: 好贱的回答
(2个月前)&nbsp&
能不能来点实际的啊，我只是程序狗
(2个月前)&nbsp&
<span class="a_vote_num" id="a_vote_num_
--- 共有 2 条评论 ---
: 不要一条条提交。批量提交就好了。
多条一起提交.这样提交会比一条条提交提升几十倍。
(2个月前)&nbsp&
(2个月前)&nbsp&
<span class="a_vote_num" id="a_vote_num_
应该分批来搞。
--- 共有 1 条评论 ---
考虑过这个办法
(2个月前)&nbsp&
<span class="a_vote_num" id="a_vote_num_
<span class="a_vote_num" id="a_vote_num_
有索引吗？可以考虑先删索引，然后更新，然后再回复索引。而且最好申请窗口期。
<span class="a_vote_num" id="a_vote_num_
alisql可以解决吗？
<span class="a_vote_num" id="a_vote_num_
删除索引,查询出需要更新的记录ID,通过工具生成单条更新语句,执行语句文件.
<span class="a_vote_num" id="a_vote_num_
其实觉得lz走入死胡同了，干嘛要一口气更新那么多行？是不是懒得用连接查询导致的？
更多开发者职位上
有什么技术问题吗？
zsl2016...的其它问题
类似的话题求每天千万数据量的插入或更新一张大表高效存储过程 - ITeye问答
1.已经写了一个视图，统计每天十几张基础表的增量数据。视图统计中有每个基础表的ID,排序视图中操作时间，按时间再根据ID更新或插入到总表中相应字段。
2.每天的十几张表的增量数据大概1K万左右。
3.每个基础表都有分区，而且根据视图查询每天字段做了函数索引，所以查询视图时间很快，排序等时间可以接受。
4.总表已经建立复合分区，有全局唯一索引。
5.刚开始写了一个存储过程，执行还可以，当总表数量达到6K万后执行时间很慢。
6.我写的存储过程是先把视图放入游标，遍历视图，判断总表中是否有重复记录，根据当前基础表ID，插入或更新总表的相应字段。
7.使用了nologging,和分批提交数据，性能没有根本改善。
8.考虑过使用把增量数据放入临时表中，然后使用marge into 批量更新，但是要根据基础表ID判断要更新字段，而且必须按时间排序操作。
请教各位大神，有什么好的思路。
问题补充：max_annie 写道把每天的数据都更新到同一张大表吗？分区是按时间么？比如按天分区还是按月分区？
我思路是这样的：
1.把大表分成若干的表，比如按天来存储（命名如：tablename_yyyymmdd），每天都有独立的表来存储数据，在天表中执行插入或者更新操作
2.如果数据只能放到一张总表，那么把它按天来分区
&&&&&&&&&&&&&& 1）把总表中当天的数据存到一个中间表中
&&&&&&&&&&&&&& 2）在获取增量数据以后，在中间表中执行插入或者更新操作
&&&&&&&&&&&&&& 3）在把中间表的数据与这张总表实现交换分区
如果按天来分区，插入新纪录还可以，但是如果是更新以前的数据呢？
把每天的数据都更新到同一张大表吗？分区是按时间么？比如按天分区还是按月分区？
我思路是这样的：
1.把大表分成若干的表，比如按天来存储（命名如：tablename_yyyymmdd），每天都有独立的表来存储数据，在天表中执行插入或者更新操作
2.如果数据只能放到一张总表，那么把它按天来分区
&&&&&&&&&&&&&& 1）把总表中当天的数据存到一个中间表中
&&&&&&&&&&&&&& 2）在获取增量数据以后，在中间表中执行插入或者更新操作
&&&&&&&&&&&&&& 3）在把中间表的数据与这张总表实现交换分区
已解决问题
未解决问题