SQL优化实战：临时表+分批提交+按日结存

 

结存数据太慢怎么办？

 

 

（1）第1次优化

 

 

一开始，客户经理说客户，不想在多个查询中看数据，想在一个表中看所有的数据，也就是说需要把原有的多个查询的sql合并为一个，但是实际上合并后，每个月的原始数据量大概是100w条左右，然后进行计算，最后返回大概3w条左右，速度比较慢。

然后，客户反馈，在查询数据时非常慢，基本上查询1个月的数据，需要5分钟，于是对sql中涉及到的表，都更新了统计信息，速度提高不少。

 

 

（2）第2次优化

 

 

之后，客户说需要显示结存的数据，于是修改sql，增加了表关联，但是速度又慢了。

再之后，尝试用了修改sql，但是效果都不理想。

最后想出的办法是，新建结存表，新建存储过程，然后把数据预先结存到表中。

通过一个存储过程，来结存2015年全年的数据，每次结存一个月的数据。
在结存1、2、3月时，每个月在2分钟左右，但从4月份开始，结存一个月数据10分钟都不止。

一开始，由于结存时间过长，也无法判断有多少数据已经结存进去，所以直接kill，但是rollback时间达到了20分钟。

后来，通过 select * from tb with(nolock)查询，大致能掌握已结存进去多少数据。

 

 

 

（3）第3次优化

接下来进行了几种尝试：
1、rebuild index，通过重建索引，来减少索引的碎片。
2、删除日期字段biz_date上的索引，减少需要维护的索引数量，加快insert的速度。
3、删除聚集索引，同2。

 

4、先插入临时表，再把临时表的数据插入表中。

 

 

但是以上4种尝试，基本上无效，通过：
select * from sys.sysprocesses where spid = xx

 

发现查询数据的时候比较快，但是在insert时很慢，基本上都是PAGEIOLATCHEX等待，这个等待基本上说明是 由于磁盘的写入等待，导致速度很慢。

 

 

那么在磁盘写入速度很慢的情况下，如何提高速度呢？

 

（4）第4次优化

 

尝试了下面的2种方法：

1、先把1个月的数据存入临时表，创建索引，然后每1w条数据提交1次。
2、把存储过程修改为按日结存。
最后，这2种方法，都能在3分钟以内，结存一个月的数据，且第1个方法最快，只需要2分钟左右，但是第1个方法需要写的代码比较多。

总结一下：
随着表的数据越来越多，插入速度越来越慢，通过分批插入数据，然后提交，来提高速度。
这里的关键是分批，每一批的大小，太大的话，虽然提交次数少了，但是对磁盘压力太大，必然会有长时间等待。

 

如果太小，那么提交次数太多，甚至每次插入数据的时间比每次提交所消耗的时间还小，而数据很多，这样一算，就会发现，消耗在提交上的时间，可能都超过了插入数据的时间。