delete in (select …) 语句执行慢，delete语句不走索引。
在清理MySQL中的一些过期数据时，发现delete操作执行非常慢。
问题场景
需要清理的数据查询统计结果如下：
1
2
3
4
5
6
7
|
mysql> select count(1) from t_strategy_detail where id in (select strategy from robots where stop_time<1699782290 AND stop_kind > 0 AND stop_kind < 100);
+----------+
| count(1) |
+----------+
| 503902 |
+----------+
1 row in set (1.75 sec)
|
开始以为是清理的数据太多导致，所以把清理范围缩小：
1
2
3
4
5
6
7
|
mysql> select count(1) from t_strategy_detail where id in (select strategy from robots where stop_time<1629782290 AND stop_kind > 0 AND stop_kind < 100);
+----------+
| count(1) |
+----------+
| 1779 |
+----------+
1 row in set (0.01 sec)
|
缩小清理范围后，发现删除操作执行依然很慢，执行语句如下:
1
2
|
mysql> delete from t_strategy_detail where id in (select strategy from robots where stop_time<1629782290 AND stop_kind > 0 AND stop_kind < 100);
|
这个时候意识到和数据大小无关，应该是执行语句存在问题，立即分析执行SQL。
问题分析
执行计划分析结果如下：
1
2
3
4
5
6
7
8
|
mysql> explain delete from t_strategy_detail where id in (select strategy from robots where stop_time<1629782290 AND stop_kind > 0 AND stop_kind < 100);
+----+--------------------+------------------+------------+-------+---------------------------------+-----------------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------------+------------+-------+---------------------------------+-----------------+---------+------+--------+----------+------------------------------------+
| 1 | DELETE | t_strategy_detail | NULL | ALL | NULL | NULL | NULL | NULL | 454862 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | robots | NULL | range | index_stop_time,index_stop_kind | index_stop_time | 5 | NULL | 2180 | 5.00 | Using index condition; Using where |
+----+--------------------+------------------+------------+-------+---------------------------------+-----------------+---------+------+--------+----------+------------------------------------+
2 rows in set (0.00 sec)
|
可以看到，子查询是有走索引的，可是delete语句是全表扫描，这是为什么呢，delete条件是主键匹配，为什么不走主键索引？
把语句修改为查询操作分析，查询语句有走索引
1
2
3
4
5
6
7
8
9
10
|
mysql> explain select * from t_strategy_detail where id in (select strategy from robots where stop_time<1629782290 AND stop_kind > 0 AND stop_kind < 100);
+----+--------------+------------------+------------+--------+---------------------------------+-----------------+---------+----------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------------+------------+--------+---------------------------------+-----------------+---------+----------------------+------+----------+------------------------------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where |
| 1 | SIMPLE | t_strategy_detail | NULL | eq_ref | PRIMARY | PRIMARY | 4 | <subquery2>.strategy | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | robots | NULL | range | index_stop_time,index_stop_kind | index_stop_time | 5 | NULL | 2181 | 50.00 | Using index condition; Using where |
+----+--------------+------------------+------------+--------+---------------------------------+-----------------+---------+----------------------+------+----------+------------------------------------+
3 rows in set, 1 warning (0.00 sec)
|
修改为select后，可以看到主查询是走主键索引的。
在网上找到一些相同的问题分析，问题原因大概是，MySQL对UPDATE，DELETE子查询的语句不会优化，推荐改为JOIN的方式执行。
优化方案
还有一种简便的方案，就是给表加别名，加别名也会触发MySQL优化策略。
优化方案执行计划分析结果如下：
1
2
3
4
5
6
7
8
9
|
mysql> explain delete s from t_strategy_detail as s where s.id in (select strategy from robots where stop_time<1629782290 AND stop_kind > 0 AND stop_kind < 100);
+----+--------------+-------------+------------+--------+---------------------------------+-----------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------------------+-----------------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using where |
| 1 | DELETE | s | NULL | eq_ref | PRIMARY | PRIMARY | 4 | <subquery2>.strategy | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | robots | NULL | range | index_stop_time,index_stop_kind | index_stop_time | 5 | NULL | 2181 | 50.00 | Using where |
+----+--------------+-------------+------------+--------+---------------------------------+-----------------+---------+----------------------+------+----------+-------------+
3 rows in set (0.00 sec)
|
可以看到delete操作是有走索引的。
修改为别名后执行很快，执行结果如下：
1
2
|
mysql> delete s from t_strategy_detail as s where s.id in (select strategy from robots where stop_time<1629782290 AND stop_kind > 0 AND stop_kind < 100);
Query OK, 1779 rows affected (0.04 sec)
|
扩大清理范围相对之前要快很多，执行结果如下：
1
2
|
mysql> delete s from t_strategy_detail as s where s.id in (select strategy from robots where stop_time<1699782290 AND stop_kind > 0 AND stop_kind < 100);
Query OK, 502123 rows affected (9.53 sec)
|
最后清理子查询数据：
1
2
|
mysql> delete from robots where stop_time<1699782290 AND stop_kind > 0 AND stop_kind < 100;
Query OK, 503902 rows affected (33.82 sec)
|
参考