EXPLAIN 简介

The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

如官方文档所述，EXPLAIN 指令提供了 MySQL 如何执行一条 SQL 语句的信息。通过使用 EXPLAIN，我们可以查看 SQL 语句的执行方式，进而排查 SQL 语句的瓶颈，然后进行优化。本文使用的 MySQL 版本是 5.7。

首先我们定义需要使用的示例表结构：

-- 队伍
CREATE TABLE `team` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL DEFAULT '',
  `level` INT UNSIGNED NOT NULL DEFAULT 1,
  `code1` INT UNSIGNED NOT NULL DEFAULT 0,
  `code2` INT UNSIGNED NOT NULL DEFAULT 0,
  `code3` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `idx_code` (`code1`,`code2`,`code3`)
);

-- 成员
CREATE TABLE `member` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `team_id` INT UNSIGNED NOT NULL,
  `no` INT UNSIGNED NOT NULL,
  `name` VARCHAR(64) NOT NULL DEFAULT '',
  `join_time` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_teamId` (`team_id`),
  UNIQUE INDEX `uk_no` (`no`)
);

-- 成员详情
CREATE TABLE `member_detail` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `no` INT UNSIGNED NOT NULL,
  `mission` VARCHAR(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_no` (`no`)
);

-- 插入数据
INSERT INTO team VALUES (NULL, '霸天虎', 1, 0, 0, 1),(NULL, '汽车人', 100, 0, 0, 2);
INSERT INTO member VALUES (NULL, 1, 1001, '威震天', 1618641132),(NULL, 1, 1002, '红蜘蛛', 1618641133),(NULL, 2, 1003, '擎天柱', 1618641134),(NULL, 2, 1004, '大黄蜂', 1618641135),(NULL, 2, 1005, '梅根福克斯', 1618641136);
INSERT INTO member_detail VALUES (NULL, 1001, '消灭宇宙'),(NULL, 1002, '篡位'),(NULL, 1003, '保护地球'),(NULL, 1004, '找山姆'),(NULL, 1005, '负责好看');

使用 EXPLAIN 的方式很简单，即 EPLAIN + SQL 语句, 例如：

EXPLAIN SELECT * FROM member WHERE team_id = 1;

执行结果如下：

该结果即展示了 MySQL 执行该条 SQL 的执行计划，下面将逐一解释每个字段的含义。

字段详解

id

SELECT 查询的序列标识。当使用 JOIN 查询时，涉及多个 select：

• 当 id 相同时，执行顺序 由上向下。

EXPLAIN SELECT t.name, m.name FROM team t, member m WHERE m.team_id = t.id;

• 当 id 不同时，表示包含子查询，id 值会递增。id 值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM team t WHERE t.id = (SELECT MAX(team_id) FROM member m);

select_type

查询的类型，常见的值有：

• SIMPLE：简单查询，不包含 UNION 或者子查询。
• PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
• SUBQUERY：子查询中的第一个 SELECT。
• UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
• DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
• UNION RESULT：UNION 查询的结果。

table

当前查询执行的数据表。

partitions

查询所匹配记录所在的分区，对于未分区的表，值为 NULL。

type

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

具体含义如下：

• system

表中只有一行数据，是 const 的一种特例。

• const

表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。

EXPLAIN SELECT * FROM team t WHERE id = 1;

• eq_ref

当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。

EXPLAIN SELECT * FROM member m, member_detail d WHERE m.no=d.no;

• ref

使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。

EXPLAIN SELECT * FROM member m WHERE m.team_id=1;

• fulltext

查询使用到了全文索引。

• ref_or_null

类似于 ref，MySQL 在执行时还查询了值为 NULL 的行。

• index_merge

当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引；ken_len 列列出了所使用的索引的长度。

EXPLAIN SELECT * FROM member WHERE id=1 or team_id=1;

• unique_subquery

替代了 eq_ref。在一些使用 IN 子查询中，使用唯一索引 ，例如。

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery

与 unique_subquery 类似，在 IN 子查询中，使用普通索引，例如：

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range

对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了，key_len 列表示使用的索引的长度。

EXPLAIN SELECT * FROM member WHERE no>1001;

• index

查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。

EXPLAIN SELECT id FROM member;

• ALL

查询遍历全表，很可能要读磁盘，速度最慢。

EXPLAIN SELECT * FROM member;

possible_keys

列出查询中可能被使用到的一个或多个索引，不一定被查询实际使用。

key

查询中实际使用到的索引。如果为 NULL，则表示未建立索引或索引失效。

key_len

表示查询索引时使用的字节数，在满足需求的前提下越短越好。

ref

表示在查询索引时，哪些列或者常量被用来与索引的值进行比较。

rows

展示 MySQL 估算出的查询时需要遍历的行数，越少越好。

filtered

表示估算的经过查询条件删选出的列数的百分比。例如 rows 是 1000，filtered 是 50（50%），则实际筛选出的列数为 1000 * 50% = 500。

Extra

这一列包含了 MySQL 在查询时的一些额外信息。

特别注意，当 Extra 列包含 Using filesort 或 Using temporary，MySQL 的性能会存在问题，需要尽可能避免。

Extra 列常见的信息如下：

• Using filesort

在排序时使用了外部的索引排序，没有用到表内索引进行排序。

EXPLAIN SELECT * FROM team WHERE code1=1 ORDER BY code3;

• Using temporary

MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。

EXPLAIN SELECT count(*) FROM team GROUP BY code2;

对于这种情况，可以优化 GROUP BY：

EXPLAIN SELECT count(*) FROM team GROUP BY code1,code2;

• Using index

表明查询使用了覆盖索引，不用回表，查询效率非常高。

EXPLAIN SELECT code2 FROM team WHERE code1=1;

• Using where

表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

需要注意的是如果不是特意进行全表扫描，例如 select * from member，那么如果 Extra 中没有 Using where，且 type 列的值是 ALL 或者 index，则表示查询中有错误，需要排查。

EXPLAIN SELECT * FROM team WHERE level=1;

• Impossible WHERE

表示 WHERE 子句的结果总是 false 且无法查到任意行。

EXPLAIN SELECT * FROM member WHERE team_id=1 and team_id=2;

• Using join buffer (Block Nested Loop)

连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询，具体见 MySQL JOIN 的内容。

EXPLAIN SELECT * FROM team t INNER JOIN member m ON t.level=m.join_time;

• Using join buffer (Batched Key Access)

连表查询的方式，与 Using join buffer (Block Nested Loop) 类似。

MySQL 在链表查询时使用了 BKA 算法。