用改写的方式避开MySQL子查询优化的坑

今天早上遇到个性能问题,同事说有个sql执行很慢,SQL是这样的:

update busi_flightdynamic t
    SET t.actualarrivaltime = '09:00',
    t.actualintervaltime = 2
    where exists (
    select 'x' from busi_manifest t1
    where t.id = t1.dynamicflightid
     and (t1.id = 19027 or t1.parentid = 19027)
     and t1.manifeststatus = 90
   )

复制

这个sql表面看出来啥问题,感觉写的还不错啊,子查询用了exists.

问了下生产环境版本是5.5. 拿到测试环境(5.7.21)看了下执行计划,

mysql> explain update busi_flightdynamic t
    ->     SET t.actualarrivaltime = '09:00',
    ->   t.actualintervaltime = 2
    ->    where exists (
    ->     select 'x' from busi_manifest t1
    ->     where t.id = t1.dynamicflightid
    ->      and (t1.id = 19027 or t1.parentid = 19027)
    ->      and t1.manifeststatus = 90
    ->    );
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | UPDATE             | t     | NULL       | index | NULL          | PRIMARY | 8       | NULL | 25676 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t1    | NULL       | ALL   | PRIMARY       | NULL    | NULL    | NULL | 27086 |     0.10 | Using where |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

复制

擦,发现"DEPENDENT SUBQUERY",吓死爸爸了.
这里说说啥是"DEPENDENT SUBQUERY"

转述官方说法:子查询中的第一个SELECT,取决于外面的查询结果.换成人话就是说:子查询的查询方式依赖于外面的查询结果. 在这儿就是说先去外面查询利用
select * from busi_flightdynamic
 的结果集再去和里面select * from busi_manifest t1 where (t1.id = 19027 or t1.parentid = 19027 and t1.manifeststatus = 90
 的结果集进行关联,和我们想象的先执行里面再执行外面有点不一样.
顺便看了下warning

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message                                                               |
+-------+------+-----------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'test.t.id' of SELECT #2 was resolved in SELECT #1 |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

复制

和官网的解释差不多.

第一次改写:

将exists用in来代替:

mysql> explain update busi_flightdynamic t
    ->     SET t.actualarrivaltime = '09:00',
    ->       t.actualintervaltime = 2
    ->    where t.id in (
    ->     select dynamicflightid from busi_manifest t1
    ->      where (t1.id = 19027 or t1.parentid = 19027)
    ->      and t1.manifeststatus = 90
    ->    );
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | UPDATE             | t     | NULL       | index | NULL          | PRIMARY | 8       | NULL | 25676 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t1    | NULL       | ALL   | PRIMARY       | NULL    | NULL    | NULL | 27086 |     0.10 | Using where |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
2 rows in set (0.00 sec)

复制

发现速度快了,没有waring了,但执行计划还是有"DEPENDENT SUBQUERY"

第二次改写:

老老实实的用inner join来代替子查询

mysql> explain update busi_flightdynamic t,busi_manifest t1
    -> set t.actualdeparturetime = '18:15'
    -> where t.id = t1.dynamicflightid
    -> and (t1.id = 19027 or t1.parentid = 19027)
    -> and t1.manifeststatus = 90;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                     | rows  | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+-------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                    | 27086 |     1.00 | Using where |
|  1 | UPDATE      | t     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | test.t1.dynamicflightid |     1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+-------+----------+-------------+
2 rows in set (0.00 sec)

复制

执行计划正常了,也没有warning了.

总结一下:

MySQL对子查询的改写优化还不是很完善,平时写代码的时候还是尽量的少用子查询改写还是尽量用关联查询来代替.

MySQL中遇到执行计划有"DEPENDENT SUBQUERY"或者"subquery"的赶紧想办法改写吧,不然今后就是隐患.

BTW,最上面那个exists的sql在Oracle上是完美~~~