sql优化-记一次生产n小时的update语句优化到1秒内的全过程

Love The Way You Lie 2022-04-04 04:22 562阅读 0赞

近期生产业务中，有一批导入数据，数据量大约16万多，由于需要做树查询，涉及一个层级关系维护。前期，实施维护的时候由于sql问题，导致除一级数据外的下级数据（总共六级）大量维护出错，导致现在需要将这16万数据层级关系全部重新维护。  
表中，有一个编码字段（以下用task\_code表示），同时维护其父项的task\_code(一下称parent\_task\_code)。  
现任务是需要将这16w多的数据在表中找到每个数对应的父项目的编码，然后将父项目的id维护到当前数据的parent\_id中去。

原sql是这样的  
![在这里插入图片描述][20181214185811172.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70]  
由于导入数据id是手工维护的，主键id都是以LIGZ为开头的生成的流水号，所以sql意思是，首先根据id定位出所有的导入数据，然后由于一级任务不需要维护父任务，所以过滤掉所有的一级任务，然后由于需要有一个PROJECT\_CODE的过滤条件，所有过滤掉所有没有维护PROJECT\_CODE（其实基本上都是有PROJECT\_CODE的）的数据，可以看到需要更新的数据大概是13w多。

先不看sql，我们来看下执行计划  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 1]  
这个也是比较简单的，两个全表扫描，数据量一大，效率可想而知。

第一步：  
基于上述sql，考虑到数据都是手工维护，但是由于在id上使用了like查询，导致了基于id的查找并不能使用索引，发现id是手工维护的流水号，于是做出如下优化，将原来的like改造成BETWEEN AND  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 2]  
可以看到，现在最外层的嵌套表已经改成索引扫描，而非全表扫描了，虽然这会增加一定的CPU cost，但是可以减少全表扫描。但是sql执行依旧很慢，因为还有嵌套表存在。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 3]  
可以看到，更新13万多条数据，当前sql的执行时间为3257s，大概54分钟多（想想最初的sql，两个全表扫描，怕是得执行一天吧），显然在生产生是不能接受的，因为你update的过程中会对数据进行一个行级锁定，会阻塞其他用户的正常dml操作，极容易造成锁表。

考虑到表需要根据project\_code，以及父子任务编码做连接查询，所以考虑索引优化，由于任务编码原本是查询条件之一，已经有btree索引，考虑到project\_code重复值很多，基本是13w对2000左右，而且在后续生产过程中不需要做查询条件，于是建临时bitMap索引。更新完后drop掉，减少后期不必要的维护成本。完了后执行效率如下。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 4]  
可以看到此时更新13w多的数据只需要18s多了，由此可见索引的强大，以及适当使用索引可以给我们带来无法预估的效率。

但是sql还能不能再优化呢？

注意到这里，还是有嵌套表的存在，有嵌套就有循环，就会增加耗时，能不能改成使用hash join来做关联。  
由于是同一张表更新，我们考虑使用merge into来做更新；  
merge into 语法如下：

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
    WHEN MATCHED THEN
    	[UPDATE sql]
    WHEN NOT MATCHED THEN
    	[INSERT sql]

作用:判断Ｂ表和Ａ表是否满足ON中条件，如果满足则用B表去更新A表，如果不满足，则将B表数据插入A表但是有很多可选项，如下:  
1.正常模式  
2.只update或者只insert  
3.带条件的update或带条件的insert  
4.全插入insert实现  
5.带delete的update(觉得可以用3来实现)

我们这里由于考虑到我们一个子任务和父任务是多对一的关系，此时用MERGE into来做update完全可以实现hash join  
改良后，sql及执行计划如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 5]  
可以看到，已然实现了hash join ，而且执行cpu cost也是非常小的。那么具体的执行效率如何，见下图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 6]  
可以看到配合索引及merge into 此时的更新时间已经小于1s，可以说是很完美了。

完事儿后drop掉我们的bitmap索引即可。

以上便是此次sql优化的全过程。

[20181214185811172.png]: /images/20220404/e16cd10e0e154c83a44a5c82da346f42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70]: /images/20220404/d42b807f0be040678e61705ed293862a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 1]: /images/20220404/4b0ee2d03fd84bf995c0030f57f59578.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 2]: /images/20220404/a78a68511d7f4308b2aa3df5974ab1f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 3]: /images/20220404/7dcd326afdfe45b5a551f65db4b4be02.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 4]: /images/20220404/321373b0d5644e38a66fefa180efe0c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 5]: /images/20220404/d607fdf69c3f48a3ae944b1c4daddac3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 6]: /images/20220404/315c2456c4884c75b144f4dfe4527d8c.png