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

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

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

上来先看processlist吧，我也不例外，甭管啥问题或者不知道咋查的时候，上来先一通操作猛如虎，而且不停的刷processlist，旁边的人就感觉你一直在操作，觉得你很专业。
看下这次的这个processlist，里面的SQL就是一个联合子查询的delete，看起来没啥特殊，按照平常的思路接下来应该看执行计划了。
简单查了下这个状态的意思，大概是说这是sql语句处于查询优化过程，持续时间较长。

我们看下执行计划，按照我以往浅薄的思路，key字段都用了主键了，肯定没问题啊。但是仔细看id=2的dependent subquery，感觉挺奇怪，仔细度娘了一下，发现这玩意威力无穷啊。

子查询的类型是DEPENDENT SUBQUERY，表示这个查询是子查询的第一个查询，外部的查询会反复去进行这个操作。即在这条语句中，外部查询结果集(数据量为1771579)的每一条结果都将执行一次子查询，进行1771597次匹配，若数据量较大的情况，即使加了索引也会使效率低下。
我又从慢日志里查了下以前的执行情况，以前这个语句也执行了，但是没这么慢，而且慢日志里记录的exam rows是逐渐增长的，看样子应该是随着表的数据量增长，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 |

+----+-------------+------------+------------+------+-----------------------------------------+-----------------+---------+------------------------------+------+----------+-------------+

这个问题到这算是解决了，我往下做了些实验，发现同样的子查询在做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 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、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 MySQL 数据库优化案例 DELETE 表慢详细优化过程及思路