MySQL 数据库优化案例 – DELETE 表慢详细优化过程及思路

问题现象

一个比较重要的系统出现批量慢的情况,而且听说之前并不慢,今天开始慢了。当时心里一咯噔,一个是这系统之前出现过数据库切换影响业务了,还被投诉了。

另一个就是这个系统之前没出现过性能问题,大多是集群类的问题,感觉没有啥经验可循。当时就有点方,赶紧让值班的同事把信息发过来。

问题分析

一、看processlist

上来先看processlist吧,我也不例外,甭管啥问题或者不知道咋查的时候,上来先一通操作猛如虎,而且不停的刷processlist,旁边的人就感觉你一直在操作,觉得你很专业。
看下这次的这个processlist,里面的SQL就是一个联合子查询的delete,看起来没啥特殊,按照平常的思路接下来应该看执行计划了。
简单查了下这个状态的意思,大概是说这是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在优化上有不一样,这块可能需要源码的大佬帮忙解释下了。


数据运维技术 » MySQL 数据库优化案例 – DELETE 表慢详细优化过程及思路