问题现象
一天周末，我还在家happy呢，突然值班群里传来消息，说一个比较重要的系统出现批量慢的情况，而且听说之前并不慢，今天开始慢了。当时心里一咯噔，一个是这系统之前出现过数据库切换影响业务了，还被投诉了。另一个就是这个系统之前没出现过性能问题，大多是集群类的问题，感觉没有啥经验可循。当时就有点方，赶紧让值班的同事把信息发过来。
问题分析
一、看processlist
大多数mysql dba的习惯应该都是上来先看processlist吧，我也不例外，甭管啥问题或者不知道咋查的时候，上来先一通操作猛如虎，而且不停的刷processlist，旁边的人就感觉你一直在操作，觉得你很专业。
看下这次的这个processlist，里面的sql就是一个联合子查询的delete，看起来没啥特殊，按照平常的思路接下来应该看执行计划了。我突然注意到state的值是preparing，这个还真是没怎么见过。
简单查了下这个状态的意思，大概是说这是sql语句处于查询优化过程，持续时间较长。
二、看执行计划
我们看下执行计划，按照我以往浅薄的思路，key字段都用了主键了，肯定没问题啊。但是仔细看id=2的dependent subquery，感觉挺奇怪，仔细度娘了一下，发现这玩意威力无穷啊。
子查询的类型是dependent subquery，表示这个查询是子查询的第一个查询，外部的查询会反复去进行这个操作。即在这条语句中，外部查询结果集(数据量为1771579)的每一条结果都将执行一次子查询，进行1771597次匹配，若数据量较大的情况，即使加了索引也会使效率低下。
我又从慢日志里查了下以前的执行情况，以前这个语句也执行了，但是没这么慢，而且慢日志里记录的exam rows是逐渐增长的，看样子应该是随着表的数据量增长，sql性能是逐渐下降的，可能是之前值班同事并没有注意，这次快跑不完了才注意到。
三、改写sql
对于这种子查询，可以改写成联合delete，优化后通过执行计划看到查询变为普通查询，扫描数据量大幅度降低，且都应用了索引，效率有很大提升。
explain delete test1 from test1 ,test2 where test1.asso_code=test2.prd_code and test2.date_type='2' and test1.real_prd_code='hwyxcyqzqusd1yb' and (test2.trans_date>20991231 or test2.trans_date <20211020);
---- ------------- ------------ ------------ ------ ----------------------------------------- ----------------- --------- ------------------------------ ------ ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---- ------------- ------------ ------------ ------ ----------------------------------------- ----------------- --------- ------------------------------ ------ ---------- -------------
| 1 | simple | test1 | null | ref | primary,idx_2,idx_1 | idx_1 | 98 | const | 2 | 100.00 | using index |
| 1 | delete | test2 | null | ref | primary,idx_transday | primary | 67 | const,tbproduct.prd_code | 388 | 36.12 | using where |
---- ------------- ------------ ------------ ------ ----------------------------------------- ----------------- --------- ------------------------------ ------ ---------- -------------
5.7 vs 8.0
这个问题到这算是解决了，我往下做了些实验，发现同样的子查询在做select和delete的时候mysql的优化方式是不一样的，而且在5.7和8.0上的表现也是不一样的。
我们可以看到在5.7上，同样的子查询语句，delete走的是dependent subquery，而select被改写成了join。
mysql5.7:
mysql> explain delete from test1 where id in (select id from test2 where date>'2010-10-10');
---- -------------------- ------- ------------ ----------------- --------------- --------- --------- ------ -------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---- -------------------- ------- ------------ ----------------- --------------- --------- --------- ------ -------- ---------- -------------
| 1 | delete | test1 | null | all | null | null | null | null | 523328 | 100.00 | using where |
| 2 | dependent subquery | test2 | null | unique_subquery | primary | primary | 4 | func | 1 | 33.33 | using where |
---- -------------------- ------- ------------ ----------------- --------------- --------- --------- ------ -------- ---------- -------------
2 rows in set (0.01 sec)
mysql> explain select * from test1 where id in (select id from test2 where date>'2010-10-10');
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
| 1 | simple | test2 | null | all | primary | null | null | null | 73932 | 33.33 | using where |
| 1 | simple | test1 | null | eq_ref | primary | primary | 4 | test.test2.id | 1 | 100.00 | null |
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
2 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| level | code | message |
------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| note | 1003 | /* select#1 */ select `test`.`test1`.`id` as `id`,`test`.`test1`.`name` as `name`,`test`.`test1`.`address` as `address` from `test`.`test2` join `test`.`test1` where ((`test`.`test1`.`id` = `test`.`test2`.`id`) and (`test`.`test2`.`date` > '2010-10-10')) |
------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql> select count(*) from test1;
----------
| count(*) |
----------
| 524288 |
----------
1 row in set (0.07 sec)
mysql> select count(*) from test2;
----------
| count(*) |
----------
| 73728 |
----------
1 row in set (0.01 sec)
我们看到在8.0上不论delete还是select都是走了join的方式，这种方式效率就要高的多，看起来8.0相比于5.7在执行计划选择上还是高效了不少。
mysql 8.0：
mysql> show tables;
----------------
| tables_in_test |
----------------
| test1 |
| test2 |
----------------
2 rows in set (0.00 sec)
mysql> select count(*) from test1;
----------
| count(*) |
----------
| 524288 |
----------
1 row in set (0.03 sec)
mysql> select count(*) from test2;
----------
| count(*) |
----------
| 73728 |
----------
1 row in set (0.00 sec)
mysql> explain delete from test1 where id in (select id from test2 where date>'2010-10-10');
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
| 1 | simple | test2 | null | all | primary | null | null | null | 73932 | 33.33 | using where |
| 1 | delete | test1 | null | eq_ref | primary | primary | 4 | test.test2.id | 1 | 100.00 | null |
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
2 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
------- ------ ---------------------------------------------------------------------------------------------------------------------------
| level | code | message |
------- ------ ---------------------------------------------------------------------------------------------------------------------------
| note | 1003 | delete from `test`.`test1` where ((`test`.`test1`.`id` = `test`.`test2`.`id`) and (`test`.`test2`.`date` > '2010-10-10')) |
------- ------ ---------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql> explain select * from test1 where id in (select id from test2 where date>'2010-10-10');
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
| 1 | simple | test2 | null | all | primary | null | null | null | 73932 | 33.33 | using where |
| 1 | simple | test1 | null | eq_ref | primary | primary | 4 | test.test2.id | 1 | 100.00 | null |
---- ------------- ------- ------------ -------- --------------- --------- --------- --------------- ------- ---------- -------------
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| level | code | message |
------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| note | 1003 | /* select#1 */ select `test`.`test1`.`id` as `id`,`test`.`test1`.`name` as `name`,`test`.`test1`.`address` as `address` from `test`.`test2` join `test`.`test1` where ((`test`.`test1`.`id` = `test`.`test2`.`id`) and (`test`.`test2`.`date` > '2010-10-10')) |
------- ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
总结
1、在mysql中，尤其是8.0以下，这个dependent subquery还是危害挺大，如果有性能问题，而且sql的执行计划中有这个，那么大概率就是他的原因。
2、至于为什么delete和select在优化上有不一样，这块可能需要源码的大佬帮忙解释下了。
最后修改时间：2022-10-25 15:05:35
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。