亿级数据表的优化问题

[yangyhq]

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

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

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


有没有高人指点一下？

[heshan521521]

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

[heshan521521]

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

[guguda2008]

引用 51 楼 yangyhq 的回复:

估计我原来的索引建的不合理，所以感觉没什么变化。 现在Update的速度非常快，几乎是秒更新，但是第一步Delete效率非常慢，可能是要维护索引，所以耗时。 这又什么建议吗？

所以我是建议你做两个UPDATE，而不是INSERT和DELETE，因为UPDATE不会变更索引结构

[阿铁桐]

笨办法，曾做过一个项目，因为数据量太大，插入数据时候，因为有索引，所以有点慢，于是在插入数据之前，删除索引，插入完成之后，再增加索引。update什么的不要动索引，这个估计有点用。

[yangyhq]

引用 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条数据吧。每次将这个表的数据插入到那个亿级的表后，就删除对应的数据。因为数据量少，所以感觉就算频繁做检索或者删除处理压力应该不会太大。[/quote] 批量插一千次，大概一共2千万条数据。

[yangyhq]

估计我原来的索引建的不合理，所以感觉没什么变化。 现在Update的速度非常快，几乎是秒更新，但是第一步Delete效率非常慢，可能是要维护索引，所以耗时。 这又什么建议吗？

[最美的词]

看完了楼上的回复

[mbugaifc]

[proer9988]

引用 20 楼 yangyhq 的回复:

[quote=引用 7 楼 DBA_Huangzj 的回复:] [quote=引用 5 楼 yangyhq 的回复:] [quote=引用 2 楼 DBA_Huangzj 的回复:] 表有做分区吗？

分区要每张分到百万级效率才有明显提升，那要分几百张表了。[/quote]分区不能单纯按数据量来分，而且2005的分区已经不再是分实体表，管理起来还是当一个表一样，但是IO那些可以分摊很多。对于没必要扫描的分区，可以不扫描，这样通常能减少很大的范围。[/quote] 谢谢，我准备试试看根据字符串范围分区[/quote] 不错的。

[csyp]

引用 52 楼 guguda2008 的回复:

[quote=引用 51 楼 yangyhq 的回复:] 估计我原来的索引建的不合理，所以感觉没什么变化。 现在Update的速度非常快，几乎是秒更新，但是第一步Delete效率非常慢，可能是要维护索引，所以耗时。 这又什么建议吗？

所以我是建议你做两个UPDATE，而不是INSERT和DELETE，因为UPDATE不会变更索引结构[/quote] SQL语句可以斟酌一下。

[cactus12345678]

数据库设计和更新策略有问题 历史就是历史，历史是不用更新的，当前就是当前，在当前数据插入的时候就和历史区别开了。 增加一个CreateTime(DateTime)或BatchId(Int) ,用另外一张只有一个字段的表CreateTime(DateTime)或BatchId(Int)记录当前

[吕津]

好的在干嘛。

[YHL27]

好东西，学习下。。。

[dongjiangquan]

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

[RabinSong]

同学，一句话，频繁的读写文件是很耗时的操作，你应该考虑你的业务设计是不是合理了，方向尽量降低读写操作次数 + 缓冲技术 + 后台任务处理，让界面感觉不到在写文件操作。

[lhw7791086]

[maitianhust]

merge操作行不行。

[niss]

你这个算简单的了,一次更新加一次插入,效率不会太低,更新的时候只把current的数据拿出来做比较和更改,插入的时候全部是current,建好索引,不会太慢,当然,索引多了会增加索引维护的时间 另外,也可以用下面较为复杂的方式: 建两个表,一个归档A,一个当前C,操作的时候 1把当前表中要归档的数据插入到归档表(索引加快查询),其实就是 insert into A select ....,'Archied' from C,I where 条件(I是接口表或者说是新进入的数据) 2然后把不需要归档的数据和新增的数据插入新建立的表1 也是insert + select操作 3删除当前表 4把新建立的表1重命名为当前表 5归档表和当前表重建分区视图供外部查询 以上所有步骤都不存在update和delete,效率会高很多.

[撸大湿]

学习一下~~

[最爱午夜]

1、从程序上优化，每次写新数据前更新旧数据，问题：写数据是有规律的还是无规律的，如果是有规律的，那么可不可以在写数据前更新旧数据，然后再进行新数据的插入，这中间搞个时间差。 2、没有索引只是让插入速度达到最快，但是查询速度就慢的可怜，经常会出现表扫描，对性能影响特别大，比如你插入前更新数据，如果没有索引就会特别慢。 3、采用业务表和历史表的方法，首先，对业务表和历史表分区，采取同样的方案，然后每次数据量达到一定规模，就把业务表中的分区切换到历史表，这样总保持业务表一定的数据，这个操作是针对数据库元数据操作，切换分区也只是改变页面的指针而已，不会引起大量的io操作。 4、改变数据库的存储结构，采用多文件多磁盘，提高io速度。 5、如果可以，把数据全部放入内存。