背景
最近看到了有同事反映执行单条update语句巨慢的情况，初步看了一下sql，平淡无奇，但看到了执行时间长达要2分钟，我就来了兴趣想研究一下。
问题重现
- 先看一下那条平淡无奇的sql
update table1 set IS_EDIT ='0' where ID in
(select ID from table2 where main_id='23054605329037312')
and attr_code='200001718';
- 再看看执行的结果
- 开始我以为是数据库网络有问题或者说是库表数据量太多，加上没有走索引而进行全表扫描的原因，但看了一下也就十几万条记录，全表扫描的时间也没有2分钟这么夸张。于是乎我想到了用explain工具来分析一下这条sql了。实践发现，根源还真不是没加索引或者数据量过大。
SQL分析
- 在sql前面加上explain extended(也可以直接用explain)
explain EXTENDED update table1 set IS_EDIT ='0' where ID in
(select ID from table2 where main_id='23054605329037312')
and attr_code='200001718';
- 查看执行计划结果
- 问题定位：select_type为DEPENDENT SUBQUERY，即依赖子查询，什么意思呢？
- DEPENDENT SUBQUERY：子查询依赖于外面的查询。也就是说，这条sql会先执行外部的查询，即select * from table1 得到一个结果集，然后该结果集的每一条记录都作为子查询的条件各执行一次。
- 以本例sql来分析，看执行计划id=1，可以知道外部查询会得到一个行数为161138的结果集（结果集1），那么id=2的这个select语句，也就是子查询select ID from table2 where main_id='23054605329037312'会执行多少次呢？
- 没错，就是要执行161138次。比如，结果集1的第一行的ID = 1001,那么子查询就会按select ID from table2 where main_id='23054605329037312' and ID = 1001 去执行一次;结果集1的第二行的ID = 1002,则会执行select ID from table2 where main_id='23054605329037312' and ID = 1002；以此类推，执行161138次。
- 这就解释了执行这条平淡无奇的sql要用2分钟了吧。
解决方案
从上面的分析中，我们知道了在update语句的where中用子查询是个大坑，但从sql的逻辑来上说实话没啥毛病，不得不说这是mysql优化器的一个bug，那么看看mysql官方是建议我们怎么用的： If you have a slow 'correlated' subquery with IN, you can optimize it with a join to get around the bug described by Ryan and Stephen. After the optimization the execution time is no longer O(M×N).
- 简单说，就是叫我们用关联表的方式来替代IN中的子查询，我们用left join改写一下上面的sql：
update table1 a,table2 b
set a.IS_EDIT ='0'
where b.main_id='23054605329037312'
and attr_code='200001718'
and a.ID = b.ID;
-
看看执行结果：
-
查看执行计划：
-
select_type变成SIMPLE了，也就是简单查询了，解决。
把update换成select会怎样？
- 执行计划：
explain EXTENDED select * FROM table1 where ID in
(select ID from table2 where main_id='23054605329037312')
and attr_code='200001718';
- 执行计划结果
- select_type是MATERIALIZED，即会把子查询的结果保存为临时表，换言之就是子查询只会执行一次，按我们想象中的步骤执行了。
- 也就是说，mysql存储引擎的优化器对select的子查询进行了优化，而update并没有。
总结
- 不要在update的where条件中使用子查询，如果需要批量执行更新操作，用关联表的方式替代。
- 不要过分依赖mysql的优化器，在编写sql的时候把索引、sql执行过程等因素充分考虑进去，编写高效的sql。
- 遇到慢sql的时候用explain工具进行分析，通过调整sql的实现方式或者条件顺序来调整mysql存储引擎的执行计划。
