✅分析SQL执行计划，我们需要关注哪些重要信息

下面是一次 explain 返回的一条 SQL 语句的执行计划的内容：

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | NULL          | idx_abc | 198     | NULL |    5 |    20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+

一个执行计划中，共有 12 个字段，每个字段都十分重要。简单介绍这 12 个字段：

1. id：执行计划中每个操作的独特标识符。对于一条查询语句，每个操作都有其唯一的 id。然而，在多表连接时，一次解释中的多个记录可能具有相同的 id。
2. select_type：操作的种类。常见种类包括 SIMPLE、PRIMARY、SUBQUERY、UNION 等。不同种类的操作会影响查询的执行效率。
3. table：当前操作所涉及的表。
4. partitions：当前操作所涉及的分区。
5. type：表示查询时所使用的索引类型，包括 ALL、index、range、ref、eq_ref、const 等。
6. possible_keys：表示可能被查询优化器选择使用的索引。
7. key：表示查询优化器选择使用的索引。
8. key_len：表示索引的长度。索引的长度越短，查询时的效率越高。
9. ref：用来表示哪些列或常量被用来与 key 列中命名的索引进行比较。
10. rows：表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。
11. filtered：表示此操作过滤掉的行数占扫描行数的百分比。该值越大，表示查询结果越准确。
12. Extra：表示其他额外的信息，包括 Using index、Using filesort、Using temporary 等。

假如我们有如下一张表（MySQL Innodb 5.7）：

CREATE TABLE `t2` (
  `id` INT(11),
  `a` varchar(64) NOT NULL,
  `b` varchar(64) NOT NULL,
  `c` varchar(64) NOT NULL,
  `d` varchar(64) NOT NULL,
  `f` varchar(64) DEFAULT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY `f` (`f`),
  KEY `idx_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

首先，我们来分析几个重要字段的不同取值及其区别：

对于type字段，不同取值对查询性能有显著影响：

• system：表示系统表，数据量较小，通常不需要进行磁盘 IO。
• const：使用常数索引，MySQL 在查询时只会使用常数值进行匹配。比如：

explain select * from t2 where f='Paidaxing';

• 此时使用了唯一性索引进行唯一查询。

• eq_ref：唯一索引扫描，只会扫描索引树中的一个匹配行。比如：

explain select * from t1 join t2 on t1.id = t2.id where t1.f = 'P';

• 当连接操作中使用了唯一索引或主键索引，并且连接条件是基于这些索引的等值条件时，MySQL 通常会选择 eq_ref 连接类型，以提高查询性能。

• ref：非唯一索引扫描，只会扫描索引树中的一部分来查找匹配的行。比如：

explain select * from t2 where a = 'Paidaxing';

• 此时使用了非唯一索引进行查询。

• range：范围扫描，只会扫描索引树中的一个范围来查找匹配的行。比如：

explain select * from t2 where a > 'a' and a < 'c';

• 此时使用了索引进行性范围查询。

• index：全索引扫描，会遍历索引树来查找匹配的行。比如：

explain select c from t2 where b = 'P';

• 这里的 index 表示做了索引树扫描，效率并不高，不符合最左前缀匹配的查询。

• ALL：全表扫描，将遍历全表来找到匹配的行。比如：

explain select * from t2 where d = "ni";

• 此时使用了非索引字段进行查询。

需要注意的是，以上类型由快到慢排列为：system > const > eq_ref > ref > range > index > ALL。

接下来我们来探讨两个常被忽略但十分重要的字段：

possible_keys 和 key 字段：

• possible_keys（可能的索引）：这一字段表示查询语句中可能可以利用的索引，但并不一定实际使用这些索引。possible_keys 列出了所有可能用于查询的索引，包括联合索引的组合。
• key（使用的索引）：相对应地，key 字段表示实际被查询所使用的索引。如果在查询中使用了索引，则该字段将显示使用的索引名称。它是实际用于查询的索引。

接着说一个很重要！的字段，但是经常被忽略的字段 extra，这个字段描述了 MySQL 在执行查询时所做的一些附加操作。下面是 Extra 可能的取值及其含义：

extra 字段：

• Using where（使用 where）：这表示 MySQL 在检索行后会再次进行条件过滤，使用 WHERE 子句进行进一步的筛选。这可能出现在列未被索引覆盖，或者 where 筛选条件涉及非索引的前导列或非索引列。

explain select * from t2 where d = "ni";   # 非索引字段查询
explain select d from t2 where b = "ni";    # 未索引覆盖，用联合索引的非前导列查询

• Using index（使用索引）：MySQL 使用了覆盖索引来优化查询，只需扫描索引而无需回到数据表中检索行。

explain select b,c from t2 where a = "ni";  # 索引覆盖

• Using index condition（使用索引条件）：表示查询在索引上执行了部分条件过滤，通常与索引下推有关。

explain select d from t2 where a = "ni" and b like "s%";   # 使用到索引下推。

• Using where; Using index（使用 where；使用索引）：查询的列被索引覆盖，且 where 筛选条件是索引列之一，但不是索引的前导列，或者 where 筛选条件是索引列前导列的一个范围。

 explain select a from t2 where b = "ni";   # 索引覆盖，但是不符合最左前缀
 explain select b from t2 where a in ('a','d','sd');   # 索引覆盖，但是前导列是个范围

• Using join buffer（使用连接缓存）：MySQL 使用了连接缓存。

explain select * from t1 join t2 on t1.id = t2.id where a = 's';

• Using temporary（使用临时表）：MySQL 创建了临时表来存储查询结果，通常在排序或分组时发生。

 explain select count(*),b  from t2 group by b;

• Using filesort（使用文件排序）：MySQL 将使用文件排序而不是索引排序，通常发生在无法使用索引进行排序时。

 explain select count(*),b  from t2 group by b;

• 该类型可参考文章：

聊聊 order by 是怎么实现的？

• Using index for group-by（使用索引进行分组）：MySQL 在分组操作中使用了索引。通常发生在分组操作涉及到索引中的所有列时。
• Using filesort for group-by（使用文件排序进行分组）：MySQL 在分组操作中使用了文件排序。这通常发生在无法使用索引进行分组操作时。
• Range checked for each record（为每条记录检查范围）：表示 MySQL 在使用索引范围查找时，需要对每一条记录进行检查。
• Using index for order by（使用索引进行排序）：MySQL 在排序操作中使用了索引。通常发生在排序涉及到索引中的所有列时。
• Using filesort for order by（使用文件排序进行排序）：MySQL 在排序操作中使用了文件排序。这通常发生在无法使用索引进行排序时。
• Using index for group-by; Using index for order by（在分组和排序中使用索引）：表示 MySQL 在分组和排序操作中都使用了索引。

课外补充

如何判断一条 SQL 走没有索引

首先看 key 字段有没有值，有值表示用到了索引树，但是具体是怎么用的，还得看 type 和 extra。

简单说以下几个情况：

情况一：

explain select b from t2 where a in ('a','d','sd');

+----+-------+---------------+----------+--------------------------+
| id | type  | possible_keys | key      | Extra                    |
+----+-------+---------------+----------+--------------------------+
|  1 | index | NULL          | idx_abc | Using where; Using index |
+----+-------+---------------+----------+--------------------------+

type = index，key = idx_abc，extra = 使用 where；使用 index。这表明查询利用了 idx_abc 的联合索引，但未严格遵守最左前缀匹配，或者虽然遵守了最左前缀，但在 a 字段上进行了范围查询。因此，实际上仍需扫描索引树，效率并不理想。

情况二：

explain select * from t2 where a = 'Paidaxing';

+----+-------+---------------+----------+--------------------------+
| id | type  | possible_keys | key      | Extra                    |
+----+-------+---------------+----------+--------------------------+
|  1 | ref   | idx_abc      | idx_abc | NULL                     |
+----+-------+---------------+----------+--------------------------+

表示用到了索引进行查询，并且用到的是 idx_abc 这个非唯一索引。

情况三：

explain select * from t2 where f = 'f';

+----+-------+---------------+----------+--------------------------+
| id | type  | possible_keys | key      | Extra                    |
+----+-------+---------------+----------+--------------------------+
|  1 | const | f             | f        | NULL                     |
+----+-------+---------------+----------+--------------------------+

表示用到了索引进行查询，并且用到的是 f 这个唯一索引。

情况四：

explain select b,c from t2 where a = 'Paidaxing';

+----+-------+---------------+----------+--------------------------+
| id | type  | possible_keys | key      | Extra                    |
+----+-------+---------------+----------+--------------------------+
|  1 | ref   | idx_abc      | idx_abc |  Using index             |
+----+-------+---------------+----------+--------------------------+

表示用到了索引进行查询，并且用到了 idx_abc 这个索引，而且查询用到了覆盖索引，不需要回表。

情况五：

explain select b,c from t2 where d = 'Paidaxing';

+----+-------+---------------+----------+--------------------------+
| id | type  | possible_keys | key      | Extra                    |
+----+-------+---------------+----------+--------------------------+
|  1 | ALL   | NULL          | NULL     |  Using where             |
+----+-------+---------------+----------+--------------------------+

表示没有用到索引。