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

原创

@派大星

发布于 2024-06-12 11:10:55

670

发布于 2024-06-12 11:10:55

下面是一次 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 个字段：

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

表示没有用到索引。

好了，本章节到此告一段落。希望对你有所帮助，祝学习顺利。

我正在参与2024腾讯技术创作特训营最新征文，快来和我瓜分大奖！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S7

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S7

登录后参与评论

0 条评论

热度