MySQL数据库使用子查询方式更新数据优化及思考

【环境介绍】

  云数据库MySQL 5.7

【背景描述】

业务需要：需要对16370077的表数据进行更新部分数据操作

UPDATE P_MOXXXX_REXXXX SET FISAVAILABLE = 1 WHERE FREG_ID IN (SELECT FREG_ID FROM P_MOXXXX_REXXXX_UPDATE_TEMP);

 

【按照正常流程更新数据操作】

使用archery的SQL审核工具进行提单操作，操作超过10分多钟，最后使用kill会话暂停操作，

SQL为子查询更新数据操作，业务反馈查询数据很快，从更新操作看执行很久时间：

 

临时使用具体的条件值进行更新操作执行很快：

 

 

【分析更新慢问题】

从查看SQL看为子查询更新数据操作，查看官方对子查询的解释：

 官方解释数据库子查询很明确，更新，删除数据不能使用到半连接或者优化子查询方式优化，建议使用连接方式执行SQL。

semijoin实现策略

业务反馈查询数据很快，查看对应的执行计划信息，从执行计划中就可以马上看出问题，扫描数据跟驱动表很大的差别：

查询的执行计

 从执行计划看，临时表作为驱动表，使用索引主键，从extra列看，使用了semijoin Duplicate Weedout 策略优化子查询；

 

 

 

update的执行计划：

 从执行计划看，不能优化成semijoin子查询，并且是一个相关子查询，会被优化器转为 exists相关子查询进行查询（select_type:DEPENDENT SUBQUERY），会根据外查询结果执行很多次；

 

【解决方法】

update/delete 无法使用 semijoin、materialization 优化策略，会以 exists 方式执行，外查询必须要进行全表扫描。
优化的方法，改成 join 即可方式更新数据操作；

 

 

查看执行计划及更新操作：

 从执行时间看执行了15.34秒

 

 【参考资料】

https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html

https://opensource.actionsky.com/20200628-mysql/

https://opensource.actionsky.com/20210202-explain/