记一次数据库程序优化

一个批加工程序，每日日终，由A、B、C三个表，获取8万条待处理记录，然后逐条处理并插入到D表中，每条记录会对应向D表插入两条记录。

D表结构(D1, D2, D3, D4, D5, D6, D7, …)，D1, D2, D3, D4, D5, D6, D7是主键

 

一开始，程序一次性开一个8万条记录的CURSOR，逐条处理，插表。测试时发现执行效率很差，模拟前几次批量加工就需要花费很长时间，而且是一次比一次慢，完全不可接受。

 

问题原因在哪呢？其实整个程序逻辑很简单，自我猜测效率差是因为数据量太大，插表慢导致执行速度慢。然后开始各种搜索“批量插表”、“batch insert”、“bulk insert”这些，搜索到的结果可以分为以下几类：

(1)    关闭logging，加hint

(2)    每处理XXX条记录就commit一次

(3)    一次插入多条数据，避免频繁insert

各种折腾无果：(1)没有试，(2)试了无明显效果，(3)无法使用，因为批处理过程中后处理的记录可能要用到前处理的记录，不及时insert会导致后处理记录结果不正确。

 

找老司机咨询，老司机太忙，没时间帮忙看程序，直接建议修改为按省行加工，“多进程好使”。修改起来也并不复杂，加了一个脚本，按每个省行启一个进程，经过测试，效率确实大幅提升，模拟跑了几次大概花费时间如下（以分钟计）：

3， 5， 8， 12， 18， 25

 

呵呵，稳步增长？再多跑几次真的回到了一开始的小时级，为什么越跑越慢呢？难道表里数据越多插表速度就越慢？

 

再次修改程序，打印几个关键步骤的前后时间差，结果发现插表前后几乎没有时间差，唯一有明显时间差的部分是批加工过程中有一个汇总步骤，以D3、D4、D5、D6、D7为条件汇总。问题原因就在这里，虽然D3、D4、D5、D6、D7都是主键的一部分，但因为没有用到D1，自动创建的主键索引在这里不生效，所以每次汇总都是全表查询，表里数据越多，汇总速度越慢，所以模拟跑批次数越多，花费的时间就越长！

 

以D3、D4、D5、D6、D7建立索引，在已有数据基础上再次测试，速度超快，1分钟左右搞定，多模拟几次测试也没有明显耗时增加。

 

如果一开始就打时间戳，及时发现效率差的原因所在，可能就不会走这么多弯路了…

转载于:https://www.cnblogs.com/skyliuxu/p/8976373.html