SQL优化--关联子查询的前世今生

一、发现SQL问题

还是先从发现问题说起，笔者在处理数据时，写了一个嵌套的sql，类似如下（这里是简化了，原sql是多层嵌套的in查询，原理一样）：

update  room_relation
	set cus_phone = '18222311909'
where room_id in 
(
select id from room where room_name = '101'
)
1
2
3
4
5
6

当时sql执行了30+分钟依然没有执行完毕，当时就蒙了，怎么会这么慢，因为按照括号内的条件筛选的话其实只有几条数据，应该是毫秒级就会执行完毕的。然后就用explain查看了执行计划，发现主表竟然是基本全表扫描，当时表里大概有700w数据左右。问题就是这样。

二、关联子查询慢的根本原因

上面这个update语句之所以出问题（超级慢），关键在于使用了in和关联子查询，为什么用了这个sql就这么慢呢，其实和mysql的执行优化器有关，mysql的执行优化器并没有按我们想的那样，先执行括号内的内容，再执行外层查询，实际删恰恰相反，先执行了外层sql再执行的子查询，下面给出《高性能MySql》这本书的官方解释。

1.查阅资料

下面这一段是《高性能MySql》中6.5.1这一节给上面的现象作出的解释：

上面电子书的版本比较老了是基于mysql5.5来讲的，其实mysql5.5以后对关联子查询做了优化，这点在最后一节我们一起对比下各个版本中关联子查询是如何被优化的。

2.总结

慢的原因总结就是我们使用了in（子查询） 的模式进行了数据筛选，这样会导致执行优化器将查询优化的更加复杂，从而导致了性能的急剧下降，优化的话我们只需要将子查询更改为inner join即可。比如笔者最初的sql更改成如下即可：

update  room_relation rel
inner join room ro on rel.room_id = ro.id and ro.room_name = '101'
set cus_phone = '18222311909'
1
2
3

三、MySql关联子查询优化的前世今生

上面我们已经知道了只要使用了in加上子查询那么这个sql就会非常慢，而这个问题到mysql5.6才被解决，这句话虽然是书上说的但是不全对，因为mysql5.6只对select中的子查询进行了优化，update中的子查询还是一样的糟糕。注意若是使用explain查看执行计划时查询类型出现了 dependent subquery，说明sql真正执行时有关联子查询，我们下面的验证主要是根据这个特征来判断的。下面从两个角度update、select来一起看下各个版本的mysql是的执行计划。下面所有环境的数据库、表等所有信息均一致。
所使用的update如下：

explain 
update  room_relation
set cus_phone = '18222311909'
 where room_id in (
select id from room where room_name = '101'
)
1
2
3
4
5
6

所使用的select如下：

explain 
select * from   room_relation
 where room_id in (
select id from room where room_name = '101'
)
1
2
3
4
5

1.Mysql5.5中的关联子查询

从下面两个图的执行结果我们可以看到mysql5.5select还是会有关联子查询的，但是执行计划不支持update这个有点坑，不过5.6时已经支持update的执行计划了。

1. select

2. update

2.Mysql5.6中的关联子查询

msyql5.6开始支持update的执行计划，同时根据下图我们可以看到被优化器优化后的select已经不会有关联子查询出现了，但是update还是会有关联子查询。

1. select

2. update

3.Mysql5.7中的关联子查询

从下面的结果我们可以看出，在这一块从5.6到5.7没有任何变化，我们继续往下看8.0版本

1. select

2. update

4.Mysql8.0中的关联子查询

从下面的运行截图我们可以看出8.0时无论是select还是update，执行优化器都会把关联子查询优化掉。所以来说到mysql8.0优化器已经变得更加符合我们需要了。

1. select

2. update

四、无索引下关联子查询与内连效率相等

1 无索引效率相等

使用内连查询时，若是连接条件上没有索引时，则内连的效率与关联子查询其实是一样的，都是十分的低，如下所示：

• 这是关联子查询
  
• 这是内连查询
  
  此时可以看到上面无论使用关联子查询和内连他们效率是相等的，这也是为什么我们的关联条件一定要加索引的原因，没有索引效率其实都很低。下面我们再看下有索引下的一个情况如何。

2 有索引关联子查询效率还是低

在有索引时关联子查询的效率其实还是低，这也是我们为什么不选用关联子查询的原因了，下面看下有索引的关联子查询的扫描行数：

• 有索引的关联子查询
  
• 有索引的内连查询
  
  从截图上可以看到，即使有索引对于主表依然是全表扫描，这是非常可怕的行为。所以禁止使用关联子查询。

五、总结

关联子查询会给我们的select或者update降低效率，但是这句话必须结合mysql版本来说，抛开版本聊这句话是没有意义的，从我们上面的实践来看，在mysql8.0之前更新语句里只要出现关联子查询（就是in+子查询）那么sql性能就会急剧下降（无论更新还是查询数据量较小时看不出来影响，必须数据量达到大几十万才能明显看到影响），在mysql5.6之前只要select语句中出现关联子查询也会使得查询性能骤降，鉴于目前主流的msyql版本还是5.6/5.7/8.0，所以说我们完全可以不考虑select的问题，只需要关注若是msyql不是8.0，就不要写关联子查询的语句了。