亿级数据表的优化问题

业务是这样的，定期要把最新的数据写到数据库里。表里有一列UpdateStatus，用来设置Archived和Current，每次在写入新数据时，把已有的数据改成Archived，然后新的数据改成Current。
所以每次插入数据的步骤是，
1. 先根据条件把表里对应的数据Update成Archived，
2. 然后Insert新数据
3. 再Update新插入的新数据为Current

用的是批量插入（SqlBulkCopy)，数据量少的时候性能还可以。由于需要频繁大批量Insert，所以表没有主键外键，索引加不加好像并没有太大改进。

现在的问题是有的表里有1亿多条数据，其它表里也都是百万级别的，而且一次更需批量插入1千多次，每次Update几乎是都是遍历全表，最少也要4，5分钟，全部做完就要4多千分钟，这个是不能接受的。


有没有高人指点一下？

72 个解决方案

#1

在线等

#2

表有做分区吗？

#3

有的表里有1亿多条数据
而且一次更需批量插入1千多次
每次Update几乎是都是遍历全表

按以下优先级依次做优化：
1.尝试将你的大数据表做分区或历史数据转移，并修改程序对应的查询和更新部分。
2.针对UPDATE语句设计最合适的索引/分区索引。
3.努力把更新的次数减少，比如先把待插入和更新数据合并到一张临时表中，再一次性将临时表插入或更新主表。

#4

update过程应该可以加上where条件吧？当然没有索引的情况下有没有where都是一样的

#5

引用 2 楼 DBA_Huangzj 的回复:

表有做分区吗？

分区要每张分到百万级效率才有明显提升，那要分几百张表了。

#6

思路有问题

所以每次插入数据的步骤是，
1. 先根据条件把表里对应的数据Update成Archived，
（应该是把非Archived的数据Update成Archived，字段加索引会快很多很多）

2. 然后Insert新数据
3. 再Update新插入的新数据为Current
（直接INSERT,对应字段直接Current，根本不必两次操作，如果外部导入问题，可以考虑在外部数据写入的地方修改）

#7

引用 5 楼 yangyhq 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

表有做分区吗？

分区要每张分到百万级效率才有明显提升，那要分几百张表了。

分区不能单纯按数据量来分，而且2005的分区已经不再是分实体表，管理起来还是当一个表一样，但是IO那些可以分摊很多。对于没必要扫描的分区，可以不扫描，这样通常能减少很大的范围。

#8

操作多了一步吧

先更新已经有的为Archived

然后插入新的（直接设置为Current）

#9

表有无时间字段？最近数据是否全部是当天的？
如果上面全部是，可考虑两张表，一张放Archived一张Current，字段就可以省了，也就没有这么多UPDATE操作，至多是搬迁操作。
搬迁操作实际就是将Current表数据写入Archived，然后清空Current表，在Current表写入新数据。

如果时间日期比较规整的话，可以考虑两个表都按日期分区，每天作业将Current表前一天分区切换到Archived，这样连搬迁操作的费用都可以省掉了

#10

引用 3 楼 guguda2008 的回复:

有的表里有1亿多条数据
而且一次更需批量插入1千多次
每次Update几乎是都是遍历全表

按以下优先级依次做优化：
1.尝试将你的大数据表做分区或历史数据转移，并修改程序对应的查询和更新部分。
2.针对UPDATE语句设计最合适的索引/分区索引。
3.努力把更新的次数减少，比如先把待插入和更新数据合并到一张临时表中，再一次性将临时表插入或更新主表。

谢谢
1. 现在基本每条数据两个版本，一个Archived一个Current，没有其他历史版本，一次全更新就有2千多万条新数据，也就是表最少也有将近4千万条数据，效率还是很低下。
2. 我把Update中Where的列建了非聚集索引，没有发现显著改变
3. 能不能详细说明？

用的是SQL Server 2008，它会把查询的表保存到数据缓存，直到把内存占完，之后效率就更慢了。

#11

引用 6 楼 Haiwer 的回复:

思路有问题

所以每次插入数据的步骤是，
1. 先根据条件把表里对应的数据Update成Archived，
（应该是把非Archived的数据Update成Archived，字段加索引会快很多很多）

2. 然后Insert新数据
3. 再Update新插入的新数据为Current
（直接INSERT,对应字段直接Current，根本不必两次操作，如果外部导入问题，可以考虑在外部数据写入的地方修改）

有时候只是部分更新，所以不能把所有的非Archived改成Current,要根据待插入的数据中的条件去更新已有的数据。
直接插的时候就是Current这条可行，的确会减少很多时间。

#12

引用 9 楼 Haiwer 的回复:

表有无时间字段？最近数据是否全部是当天的？
如果上面全部是，可考虑两张表，一张放Archived一张Current，字段就可以省了，也就没有这么多UPDATE操作，至多是搬迁操作。
搬迁操作实际就是将Current表数据写入Archived，然后清空Current表，在Current表写入新数据。

如果时间日期比较规整的话，可以考虑两个表都按日期分区，每天作业将Current表前一天分区切换到Archived，这样连搬迁操作的费用都可以省掉了

这个方案正在考虑，需要每次完全更新才行，直接改表名就行了，不需要搬迁数据。但是现在的业务是要能部分更新，毕竟全更新有很多数据可能根本不用。

#13

既然都是历史数据，何必更新那么多，历史数据也都是有时间的，你更新的时候，只需要把最新的Current改成Archived，然后插入一条Current就行了。

#14

忘记还有第一个步骤是，Delete掉相应的Archived数据，之后再Update Current到Archived

#15

引用 14 楼 yangyhq 的回复:

忘记还有第一个步骤是，Delete掉相应的Archived数据，之后再Update Current到Archived

你这个很有问题，如果只存两条，你应该做两次UPDATE，而不是一次DELETE，一次UPDATE和一次INSERT

#16

引用 15 楼 guguda2008 的回复:

Quote: 引用 14 楼 yangyhq 的回复:

忘记还有第一个步骤是，Delete掉相应的Archived数据，之后再Update Current到Archived

你这个很有问题，如果只存两条，你应该做两次UPDATE，而不是一次DELETE，一次UPDATE和一次INSERT

先把Archived的Delete掉
再插入新的数据标识为Pending
然后Update Current为Archived
最后UPdate Pending为Current

#17

我个人建议使用增量式更新数据采集库。数据量小，更新速度快
思路：
1：设置源数据表的行版本，查询元数据源表的插入数据，更新数据、已删除数据
2：将删除数据的记录同步到目标数据进行删除操作；对于需要插入的和更新的目标数据删除，然后将源数据表的插入和更新数据插入目标数据表中。

微软自带的数据变更跟踪：http://www.cnblogs.com/worfdream/articles/3046377.html

#18

我把你的业务逻辑简单描述成这样：
1.表中根据ID分组，每个ID存两条数据，TYPE字段一条CURRENT一条ARCHIVED。
2.频繁根据ID将新数据导入这张表，导入的数据作为C，前一条C变成A，前一条A废弃。

如果流程大致是这样，那只需要一个索引 ID,TYPE：
鉴于你现在的更新就已经是贼慢的，我就暂定你们对更新的实时性要求不高，我觉得这时的流程应该是这样的：
1.一条更新请求进来，将新数据的所有字段传入
2.根据新数据的ID，找到表中的C和A，将C的所有字段更新到A里
3.把新数据的所有字段更新到表中的A里，通过ID和TYPE查找

#19

引用 17 楼 daiyueqiang 的回复:

我个人建议使用增量式更新数据采集库。数据量小，更新速度快
思路：
1：设置源数据表的行版本，查询元数据源表的插入数据，更新数据、已删除数据
2：将删除数据的记录同步到目标数据进行删除操作；对于需要插入的和更新的目标数据删除，然后将源数据表的插入和更新数据插入目标数据表中。

微软自带的数据变更跟踪：http://www.cnblogs.com/worfdream/articles/3046377.html

Sorry, 没太看懂，没有源数据表，是从外部的文本文件读进来的数据。

#20

引用 7 楼 DBA_Huangzj 的回复:

Quote: 引用 5 楼 yangyhq 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

表有做分区吗？

分区要每张分到百万级效率才有明显提升，那要分几百张表了。

分区不能单纯按数据量来分，而且2005的分区已经不再是分实体表，管理起来还是当一个表一样，但是IO那些可以分摊很多。对于没必要扫描的分区，可以不扫描，这样通常能减少很大的范围。

谢谢，我准备试试看根据字符串范围分区

#21

引用 18 楼 guguda2008 的回复:

我把你的业务逻辑简单描述成这样：
1.表中根据ID分组，每个ID存两条数据，TYPE字段一条CURRENT一条ARCHIVED。
2.频繁根据ID将新数据导入这张表，导入的数据作为C，前一条C变成A，前一条A废弃。

如果流程大致是这样，那只需要一个索引 ID,TYPE：
鉴于你现在的更新就已经是贼慢的，我就暂定你们对更新的实时性要求不高，我觉得这时的流程应该是这样的：
1.一条更新请求进来，将新数据的所有字段传入
2.根据新数据的ID，找到表中的C和A，将C的所有字段更新到A里
3.把新数据的所有字段更新到表中的A里，通过ID和TYPE查找

已建立更新的索引，但是由于表太大，还是要好几分钟

#22

大数据的处理是更专业点的技术，观摩先进经验。

#23

可以考虑创建筛选索引。 
如: create nonclustered index ix_tableName_Archived on tableName (columns) where UpdateStatus='Archived'

Update的时候使用筛选索引ix_tableName_Archived 。

#24

可以考虑创建筛选索引。 
如: create nonclustered index ix_tableName_Current on tableName (columns) where UpdateStatus='Current'

Update的时候使用筛选索引ix_tableName_Current 。

#25

建立索引不一定对这种update操作有效，或许还会因为维护索引增加开销
不如这样，lz尝试把新数据放到一张单独的零时表中，然后用select进行查询得到需要更新的记录，然后一次性在原表中update掉数据，完成后在insert新记录到原表，同时delete零时表。
好处是批量处理可能要比单条记录逐条更新要快得多
UpdateStatus字段因为只有2种值：Archived和Current，建立索引未必能有效，不过感觉lz的意思是大部分都是Archived少部分是Current，那么加上条件UpdateStatus='Current'因为记录不是很多应该会很快，然后对这些记录做更新。

#26

该回复于2013-05-09 09:52:50被管理员删除

#27

该回复于2013-05-09 09:52:50被管理员删除

#28

该回复于2013-05-09 09:52:50被管理员删除

#29

分archived表和current表，每次将current表中的状态字段改为archived，转移到archived表，然后再把新数据放到current表

#30

1000多次，你就不能打开一次，一次插入。

#31

声明 我是新手：
1.建立存储过程: 存储过程将每次插入的数据改为current状态
2.建立临时表用于存放 新插入的数据和需要修改为Active的数据
3.将active的数据插入到有1亿条数据的表中

#32

你看看可否利用三个表实现
A 表存 archived 数据
C 表存 current 数据
P 表存 Pending 数据

1.新数据来放在p表，
2.truncate A表,C表数据Insert进A表，Insert 时同时设置状态为archived
3.truncate C表,P表数据Insert进C表，Insert 时同时设置状态为 current
4.truncate P表

然后继续循环

#33

1.建立分区表。利用分区消除减少你每次操作受影响的数据，同时也为你的快速归档创造条件。
   按什么分区就要看业务逻辑了。
 “ 1. 先根据条件把表里对应的数据”，除了你提到的这些，应该还有其它的列能把数据归类吧。
2. 贴出你的update语句和执行计划。再看要加什么样的索引。
3. 这样大批量的有损操作，还要注意日志的性能。

#34

#35

真心话....为什么要每次全表更新呢? 神马业务? 直接存入不行吗? 用日期区分,按日期分区. 多好.
我这里数据量和你差不多8W*43*5 条记录...每天都是这么多哟. 也很快的啊. 听版主话,去分区.

#36

一：3. 再Update新插入的新数据为Current，把这一步在插入的时候就直接定义成Current，而不是你现在的先插入其它内容，然后再来UPDATE
二：

#37

一：2. 然后Insert新数据
    3. 再Update新插入的新数据为Current
     把你的这两个步骤并成一步，直接在插入的时候指字值为Current，而不是你当前的先插入别的内容，再进       行修改
二：对你这张1亿多的表进行分区，根据你实际业务来分，如果是一天插入一次的，可以按时间字段进行分区，然后开始你第一步的工作，"先根据条件把表里对应的数据Update成Archived",不同的是先把这一步份数据切出到一张新的表里，毕竟相对1亿多的表，这部份数据是少的，然后对新表进行更新Archived，更新完了之后再把数据切入到1亿多的表里。。分区切换只是几妙钟的事情。。

楼主可以试试。。不正确之处，请多指点。

#38

引用 35 楼 jadilee 的回复:

真心话....为什么要每次全表更新呢? 神马业务? 直接存入不行吗? 用日期区分,按日期分区. 多好.
我这里数据量和你差不多8W*43*5 条记录...每天都是这么多哟. 也很快的啊. 听版主话,去分区.

LZ正在尝试分区+索引，目前2千万条数据规模工作良好。
不是每次都全表更新，可以也部分更新，但是数据量也很大。

#39

引用 37 楼 heshan521521 的回复:

一：2. 然后Insert新数据
    3. 再Update新插入的新数据为Current
     把你的这两个步骤并成一步，直接在插入的时候指字值为Current，而不是你当前的先插入别的内容，再进       行修改
二：对你这张1亿多的表进行分区，根据你实际业务来分，如果是一天插入一次的，可以按时间字段进行分区，然后开始你第一步的工作，"先根据条件把表里对应的数据Update成Archived",不同的是先把这一步份数据切出到一张新的表里，毕竟相对1亿多的表，这部份数据是少的，然后对新表进行更新Archived，更新完了之后再把数据切入到1亿多的表里。。分区切换只是几妙钟的事情。。

楼主可以试试。。不正确之处，请多指点。

谢谢，关键是时间字段并不用于查询条件，按时间分区没有意义。还有关于合并Pending的那一步骤，LZ也曾经考虑过，但是感觉会影响数据的准确性。比如：
先把Current的改成Archived, 再去插入Current的，如果插入过程失败，则Current的数据就缺失了。
只有确保先插入完成，再去更新之前的数据，才比较稳妥。

#40

楼主有没有尝试用触发器实现啊

#41

长知识了。。。。。。

#42

引用 8 楼 Beirut 的回复:

操作多了一步吧

先更新已经有的为Archived

然后插入新的（直接设置为Current）

灌水都灌倒这了

#43

#44

引用 21 楼 yangyhq 的回复:

Quote: 引用 18 楼 guguda2008 的回复:

我把你的业务逻辑简单描述成这样：
1.表中根据ID分组，每个ID存两条数据，TYPE字段一条CURRENT一条ARCHIVED。
2.频繁根据ID将新数据导入这张表，导入的数据作为C，前一条C变成A，前一条A废弃。

如果流程大致是这样，那只需要一个索引 ID,TYPE：
鉴于你现在的更新就已经是贼慢的，我就暂定你们对更新的实时性要求不高，我觉得这时的流程应该是这样的：
1.一条更新请求进来，将新数据的所有字段传入
2.根据新数据的ID，找到表中的C和A，将C的所有字段更新到A里
3.把新数据的所有字段更新到表中的A里，通过ID和TYPE查找

已建立更新的索引，但是由于表太大，还是要好几分钟

有问题，就算是亿级数据，索引查找也不会这么慢，分析一下是不是其它问题，先看看计划。

#45

建分区看着好像简单，经过深入研究后又有好多疑问。
分区是为了提升查询效率，但是前提是不同的分区在不同的磁盘上，才可减少I/O耗时。
貌似并不是分区越多越好，这样的话1亿条数据做3，4个分区性能又能提升多少？

而且默认都是放在PRIMARY这个文件组下。是否有必要为每个分区创建新的文件组？

#46

引用 32 楼 gbren 的回复:

你看看可否利用三个表实现
A 表存 archived 数据
C 表存 current 数据
P 表存 Pending 数据

1.新数据来放在p表，
2.truncate A表,C表数据Insert进A表，Insert 时同时设置状态为archived
3.truncate C表,P表数据Insert进C表，Insert 时同时设置状态为 current
4.truncate P表

然后继续循环

楼主可以考虑一下这位伙计的方案吧。有些时候技术不能满足需求可能是因为业务分析处理不够合理吧。
这样做的好处就是对你那个亿级的表只做Insert处理，而另外一张表C是作为一张缓存表，照你讲的需求至多也就1000条数据吧。每次将这个表的数据插入到那个亿级的表后，就删除对应的数据。因为数据量少，所以感觉就算频繁做检索或者删除处理压力应该不会太大。

#47

仔细看下数据插入、修改、删除的特点，建立合适的索引，1亿条记录按几十G算，要仔细设计，权衡综合成本

#48

该回复于2013-05-10 10:53:19被管理员删除

#49

该回复于2013-05-10 10:53:50被管理员删除

#50

引用 46 楼 aqbeyond 的回复:

Quote: 引用 32 楼 gbren 的回复:

你看看可否利用三个表实现
A 表存 archived 数据
C 表存 current 数据
P 表存 Pending 数据

1.新数据来放在p表，
2.truncate A表,C表数据Insert进A表，Insert 时同时设置状态为archived
3.truncate C表,P表数据Insert进C表，Insert 时同时设置状态为 current
4.truncate P表

然后继续循环

楼主可以考虑一下这位伙计的方案吧。有些时候技术不能满足需求可能是因为业务分析处理不够合理吧。
这样做的好处就是对你那个亿级的表只做Insert处理，而另外一张表C是作为一张缓存表，照你讲的需求至多也就1000条数据吧。每次将这个表的数据插入到那个亿级的表后，就删除对应的数据。因为数据量少，所以感觉就算频繁做检索或者删除处理压力应该不会太大。

批量插一千次，大概一共2千万条数据。