目录

前言：

最近在查看阿里上面的一些慢SQL查询，抽取一个例子，DELETE + IN 没有使用索引的。

原表名涉及到公司，所以就擦掉 下面就简称 a、b 数据表

案发现场

作案武器 DELETE + IN, 感觉就是一个很普通的语句， But 这样子想你就GG了~

DELETE FROM a
	WHERE a_id IN (SELECT id FROM b WHERE b_id = 123);

EXPLAIN 分析

1. 从下面可以直接发现全表扫描了，而且有 134937 行数据

mysql> EXPLAIN  DELETE  FROM a WHERE a_id IN (SELECT id FROM b WHERE b_id = 123);
+----+--------------------+---------------------------------+------------+-----------------+-----------------------------------+---------+---------+------+--------+----------+-------------+
| id | select_type        | table                           | partitions | type            | possible_keys                     | key     | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+---------------------------------+------------+-----------------+-----------------------------------+---------+---------+------+--------+----------+-------------+
|  1 | DELETE             | a | NULL       | ALL             | NULL                              | NULL    | NULL    | NULL | 134937 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b               | NULL       | unique_subquery | PRIMARY,index_b_id | PRIMARY | 8       | func |      1 |     5.00 | Using where |
+----+--------------------+---------------------------------+------------+-----------------+-----------------------------------+---------+---------+------+--------+----------+-------------+
2 rows in set (0.03 sec)

2. 查看a、b表索引

mysql> show index from a;
+---------------------------------+------------+-------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                           | Non_unique | Key_name                                        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------------------+------------+-------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| a |          0 | PRIMARY                                         |            1 | id          | A         |      130791 | NULL     | NULL   |      | BTREE      |         |               |
| a |          1 | index_a_id |            1 | a_id   | A         |       52390 | NULL     | NULL   |      | BTREE      |         |               |
+---------------------------------+------------+-------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.14 sec)

mysql> show index from b;
+-------------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| b |          0 | PRIMARY                         |            1 | id          | A         |      269170 | NULL     | NULL   |      | BTREE      |         |               |
| b |          1 | index_b_id       |            1 | b_id     | A         |       30432 | NULL     | NULL   |      | BTREE      |         |               |
| b |          1 | index_b_exam_id |            1 | exam_id     | A         |         979 | NULL     | NULL   |      | BTREE      |         |               |
+-------------------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.08 sec)

3. SELECT * 复查一下

是有使用索引的，为什么没有走索引？ 难度 DELETE + IN 是不行的？

mysql> EXPLAIN SELECT * FROM a WHERE a_id IN (SELECT id FROM b WHERE b_id = 123);
+----+-------------+---------------------------------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+--------------------------------------+------+----------+-----------------------+
| id | select_type | table                           | partitions | type | possible_keys                                   | key                                             | key_len | ref                                  | rows | filtered | Extra                 |
+----+-------------+---------------------------------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+--------------------------------------+------+----------+-----------------------+
|  1 | SIMPLE      | b               | NULL       | ref  | PRIMARY,index_b_id               | index_b_id                       | 8       | const                                |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | a | NULL       | ref  | index_a_id | index_a_id | 8       | eddie_prod.b.id |    2 |   100.00 | Using index condition |
+----+-------------+---------------------------------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+--------------------------------------+------+----------+-----------------------+
2 rows in set (0.10 sec)

4. 优化方案

1. 覆盖索引+JOIN

mysql> EXPLAIN DELETE a FROM a 
		INNER JOIN b ON a_id = b.id WHERE b.id = 123;
+----+-------------+---------------------------------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+--------------------------------------+------+----------+-------------+
| id | select_type | table                           | partitions | type | possible_keys                                   | key                                             | key_len | ref                                  | rows | filtered | Extra       |
+----+-------------+---------------------------------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+--------------------------------------+------+----------+-------------+
|  1 | SIMPLE      | b               | NULL       | ref  | PRIMARY,index_b_id               | index_b_id                       | 8       | const                                |    1 |   100.00 | Using index |
|  1 | DELETE      | a | NULL       | ref  | index_a_id | index_a_id | 8       | eddie_prod.b.id |    2 |   100.00 | Using where |
+----+-------------+---------------------------------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+--------------------------------------+------+----------+-------------+
2 rows in set (0.04 sec)

2. 别名关联

mysql> EXPLAIN DELETE a FROM t1 a,  t2 b WHERE a.a_id = b.id AND b. b_id = 123;
+----+-------------+-------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                                   | key                                             | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | ref  | PRIMARY,index_b_id               | index_b_id                       | 8       | const                |    1 |   100.00 | Using index |
|  1 | DELETE      | a     | NULL       | ref  | index_a_id | index_a_id | 8       | eddie_prod.b.id |    2 |   100.00 | Using where |
+----+-------------+-------+------------+------+-------------------------------------------------+-------------------------------------------------+---------+----------------------+------+----------+-------------+
2 rows in set (0.03 sec)

部分表名与数据经过修改，本人赖仔细复查了，反正大同小二吧。
想知道更多MySQL优化可以留言~