9. MySQL EXPLAIN解析

专栏地址：

MySQL系列文章专栏

---

1. EXPLAIN简介

EXPLAIN语句用来获取MySQL的执行计划，可被用于SELECT、DELETE、INSERT、REPLACE以及UPDATE语句。利用EXPLAIN语句可以获取到表的读取顺序、索引的使用情况等信息，需要注意的是，EXPLAIN输出的仅是近似结果。

EXPLAIN的使用方法：在SQL前添加EXPLAIN即可，例如：

# No.1
explain select distinct title from blog;

输出：

id	select_type	table	partition	type	possible_key	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	blog		ALL					10143	100	Using temporary

输出的行以MySQL实际的执行顺序出现，与SQL的语序不一定相一致。

本文基于MySQL5.7，所使用的表有blog、article、comment、author。含义如下：每个作者有多个博客，博客下面包含了许多文章，每篇文章包含若干评论。上述四张表分别有1w、10w、100w、1k条数据。 详细SQL见文末附录。

2. EXPLAIN输出格式

2.1 EXPLAIN 包含的列

列名	含义
id	SELECT 序号
select_type	SELECT 类型
table	该行输出所涉及的表，表的意义比较广泛：可以是一个子查询、一个UNION结果等等
partition	匹配的分区
type	访问类型，表示MySQL如何查找table中的记录
possible_key	揭示哪些索引有助于高效的查询
key	MySQL最终决定采用哪些索引来最小化查询成本，并不一定是possible_key中的索引
key_len	所使用索引部分的字节长度，与编码有关
ref	在Key列执行查询所使用的常量、列
rows	预估的扫描行数，利用表的统计信息和索引的选择得出，并不精确也并不是指结果集的行数
filtered	rows列中符合WHERE、JOIN等过滤条件的记录所占百分比的悲观估计，其值与rows相乘表示要与下表连接的行数
Extra	额外信息

2.2 id列

id列用以标记SELECT，在嵌套查询中SELECT会顺序编号，id对应其在SQL中的位置，id越大的查询越先被执行。
id可能为null，比如当该行是UNION结果时。由于UNION结果总是放在匿名临时表中，并不在原始SQL中出现，所以ID为NULL。
如果SQL语句中没有复杂查询，那么所有SELECT的id都为1。

2.3 select_type

MySQL中SELECT类型分为：简单查询、复杂查询，复杂查询细分为：简单子查询、派生表和UNION查询。

• 子查询：嵌套在其它查询中的查询
• 简单子查询：在SELECT或者WHERE子句中的子查询
• 派生表：FROM子句中的子查询，由于该临时表由子查询派生而来，故称派生表

select_type的取值如下：

SIMPlE

简单查询，不包含子查询和UNION的查询语句。例如：No.1 SQL，以及下面的IN查询。

# No.2 in型子查询，查询作者所收到的所有评论
EXPLAIN SELECT comment FROM comment WHERE article_id IN (SELECT id FROM article WHERE author_id = 2);

id	select_type	table	partition	type	possible_key	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	comment		ALL					997233	100
1	SIMPLE	article		eq_ref	PRIMARY	PRIMARY	4	explain_demo.comment.article_id	1	10	Using where

PRIMARY

复杂查询的最外层被标记位PRIMARY。

SUBQUERY

简单子查询，位于在SELECT或者WHERE子句中的子查询，会被标记为SUBQUERY。

# No.3 where型子查询，根据作者名查询其发表的所有文章名。
EXPLAIN SELECT title FROM article WHERE author_id = (SELECT id FROM author WHERE name = 'name_no_1');

id	select_type	table	partition	type	possible_key	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	article		ALL					100069	10	Using where
2	SUBQUERY	author		ALL					1001	10	Using where

DERIVED

MySQL在执行FROM型的子查询时，会在生成一个临时表，并由外层查询引用，所以必须指定别名。该表是由子查询派生而来，故称派生表。

# No.4 统计每个作者的文章数
SELECT name, article_sum FROM (SELECT author_id, sum(1) article_sum FROM article GROUP BY author_id) ar INNER JOIN author au ON ar.author_id = au.id;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	au	NULL	ALL	PRIMARY	NULL	NULL	NULL	1001	100	NULL
1	PRIMARY	<derived2>	NULL	ref	<auto_key0>	<auto_key0>	4	explain_demo.au.id	99	100	NULL
2	DERIVED	article	NULL	ALL	NULL	NULL	NULL	NULL	99720	100	Using temporary; Using filesort

在EXPLAIN结果中，结果在前面的是JOIN查询的驱动表。

DEPENDENT SUBQUERY

相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。

# No.5 exists型子查询，查询未发表过文章的作者
EXPLAIN SELECT au.id FROM author au WHERE NOT exists(SELECT 1 FROM article ar WHERE au.id = ar.author_id );

id	select_type	table	partition	type	possible_key	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	au		index		PRIMARY	4		1001	100	Using where; Using index
2	DEPENDENT SUBQUERY	ar		ALL					100069	10	Using where

# No.6 统计每个作者发表的文章总数
EXPLAIN SELECT au.id, (SELECT count(*) FROM article ar WHERE ar.author_id = au.id ) FROM author au;

id	select_type	table	partition	type	possible_key	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	au		index		PRIMARY	4		1001	100	Using index
2	DEPENDENT SUBQUERY	ar		ALL					100069	10	Using where

相关子查询的执行过程：

1. 从外层查询中取出一个元组，将元组相关列的值传给内层查询。
2. 执行内层查询，得到子查询操作的值。
3. 外查询根据子查询返回的结果或结果集得到满足条件的行。
4. 然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。

UNION & UNION RESULT

在UNION的第二个及以后的SELECT被标记为UNION，UNION结果放在匿名临时表中，UNION的结果被标记为UNION RESULT。

# No.7 查询id=1作者的博客、文章的标题（去重）
EXPLAIN SELECT title FROM article WHERE author_id = 1 UNION SELECT title FROM blog WHERE author_id =1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	article	NULL	ALL	NULL	NULL	NULL	NULL	99720	10	Using where
2	UNION	blog	NULL	ALL	NULL	NULL	NULL	NULL	10143	10	Using where
NULL	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary

2.4 table

表示当前行正在访问的表，显示表名或者别名。当访问的是临时表时：

• <derivedN>表示访问的是ID为N的派生表,N执行EXPLAIN的下一行；
• <unionM,N>表示当前UNION RESULT正在访问ID为M和N的并集；

2.5 type

type列显示了MySQL的访问类型，即如何查找表中的行。性能从从最优到最差分别为：

null

const、system

const发生在直接按照主键或者唯一键的完整部分进行读取，且表中最多只有一个匹配行，优化器在后续步骤中，可以将该行中的值视为常量。

# No.8 
EXPLAIN SELECT * FROM article WHERE id = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	article	NULL	const	PRIMARY	PRIMARY	4	const	1	100	NULL

eq_ref

索引查找，在使用主键或者唯一键的完整部分进行连接查询时，对于前一个表中的每一行记录，当前表只会存在一条符合条件的记录。eq_ref是除外const之外最好的访问类型，MySQL在查找到匹配行后无需继续查找。eq_ref不会出现在简单查询中。

# No.9
EXPLAIN SELECT title, content, name FROM article LEFT JOIN author ON article.author_id = author.id WHERE title = 'x';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	article	NULL	ALL	NULL	NULL	NULL	NULL	92631	10	Using where
1	SIMPLE	author	NULL	eq_ref	PRIMARY	PRIMARY	4	explain_demo.article.author_id	1	100	NULL

ref
相较于eq_ref，当前表满足匹配条件的记录有多条，因此，ref是索引查找和扫描混合体。在使用非唯一索引或者唯一索引的非唯一前缀时发生，ref也可以出现在使用=或<=>对索引进行比较时。
之所以叫ref，是因为要将索引和某个参考值进行比较，参考值来源于常数或者多表查询中前一个表的结果值。

# No.10 简单查询，使用非唯一索引
EXPLAIN SELECT * FROM article WHERE author_id = '';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	article	NULL	ref	author_id	author_id	4	const	98	100	NULL

# No.11 关联查询
EXPLAIN SELECT * FROM article LEFT JOIN author ON article.author_id = author.id WHERE name = '';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	author	NULL	ALL	PRIMARY	NULL	NULL	NULL	1001	10	Using where
1	SIMPLE	article	NULL	ref	author_id	author_id	4	explain_demo.author.id	90	100	NULL

index_merge
索引合并优化，对同一个表的多个索引同时进行条件扫描，并将结果进行合并。常见于WHERE涉及多个索引字段，并且其之间的关系是AND或者OR的关系。index_merge有三种类型：

• intersect：对多个扫描结果取交集，其WHERE条件是AND关系；
• union：对多个扫描结果取并集，其WHERE条件是OR关系；
• sort_union：对多个扫描结果，先排序，再取并集，其WHERE条件是OR关系，但union并不适用的场景下。

当出现intersect时按时索引有进一步优化的空间，可以通过复合索引进行优化。
对于WHERE中含有多个索引的情况下，MySQL一般只会选择使用其中的一个索引。出现index_merge说明索引尚有优化的空间。

# No.12 OR两侧分别为主键和非唯一索引
EXPLAIN SELECT * FROM article WHERE id = 1 OR author_id = 3;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	article	NULL	index_merge	PRIMARY,author_id	PRIMARY,author_id	4,4	NULL	112	100	Using union(PRIMARY,author_id); Using where

range
有范围的索引扫描，其开始于索引的某一个点，并扫描所有符合条件的行。出现于WHERE中 >、betwenn等场景。
相较于完整的索引扫描，range的优势在于不需要遍历全部索引。

# No.13 
EXPLAIN SELECT * FROM article WHERE id < 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	article	NULL	range	PRIMARY	PRIMARY	4	NULL	9	100	Using where

index
索引树扫描，与全表扫描类似，不同的是index是按照索引的次序对表进行扫描而不是按行。index一般有情况：

1. Using index：使用了覆盖索引，此时只需扫描索引树，其开销远小于全表扫描，因为索引的数据一般小于表数据；
2. 回表：扫描索引树，并回表以获取所有数据。此时，随机访问会产生较大的开销。优点是，省略了排序成本。

# No.14
EXPLAIN SELECT id, author_id FROM article;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	article	NULL	index	NULL	author_id	4	NULL	92631	100	Using index

all
全表扫描，必须从头到尾的扫描整张表。但也有例外，譬如使用了LIMIT。

# No.15
EXPLAIN SELECT * FROM author;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	author	NULL	ALL	NULL	NULL	NULL	NULL	1001	100	NULL

2.6 Extra

显示一些额外信息，常见的有：

Using index
使用了覆盖索引，辅助索引已经可以满足SELECT所查询的列，无需回表。例如SQL No.14。

Using where
MySQL执行器将会对存储引擎返回的行进行进一步的过滤。

Using temporary
MySQL使用临时表来保存查询的中间结果。典型的场景有：

1. 对不同的列使用了ORDER BY和GROUP BY
2. 对非索引列使用了distinct
3. 派生表，FROM型子查询

# No.15 content无索引
EXPLAIN SELECT DISTINCT content FROM article;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	article	NULL	ALL	NULL	NULL	NULL	NULL	92631	100	Using temporary

Using filesort
对结果使用外部排序，而不是按索引次序。外排可以在内存或者硬盘上完成。

Using MRR
MRR（Multi-Range Read Optimization，多范围读取优化）。从辅助索引进行回表会产生大量的随机IO，MySQL对需要回表的ID进行排序，然后使用排序后的ID对表（聚集索引）进行顺序访问。

Using index condition
索引下推，在利用辅助索引进行查询时，可以将部分查询条件从Server层下推到存储引擎层，减少回表次数。
譬如，有abc联合索引，WHERE a = xxx AND c = yyy，对于不符合最左前缀的c部分，可以利用ICP在存储引擎层进行过滤。

3. 问答

3.1 EXPLAIN不总是说出真相？

EXPLAIN只是个近似结果，例如：

1. 不会显示执行计划的所有信息
2. 对一些事物不加详细的区分，譬如：对内存排序和硬盘排序都使用filesort

3.2 EXPLAIN时MySQL不会执行查询？

一般来说，EXPLAIN返回执行计划中每一步的信息，而不会真正去执行它。但也有例外，比如复杂查询中的派生表（FROM型子查询），MySQL需要首先去执行子查询。

附录

建表语句

# 准备测试数据
DROP DATABASE IF EXISTS explain_demo;
CREATE DATABASE explain_demo;
USE explain_demo;
CREATE TABLE blog
(
    id        INT PRIMARY KEY AUTO_INCREMENT,
    title     VARCHAR(255) NOT NULL,
    author_id INT          NOT NULL
);
CREATE TABLE article
(
    id        INT PRIMARY KEY AUTO_INCREMENT,
    blog_id   INT          NOT NULL,
    author_id INT          NOT NULL,
    title     VARCHAR(255) NOT NULL,
    content   TEXT         NOT NULL
);
CREATE TABLE IF NOT EXISTS comment
(
    id         INT PRIMARY KEY AUTO_INCREMENT,
    article_id INT          NOT NULL,
    comment    VARCHAR(255) NOT NULL
);
CREATE TABLE author
(
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);
ALTER TABLE article ADD INDEX author_id (author_id);
DELIMITER //
CREATE PROCEDURE prepare_data()
BEGIN
    DECLARE blog_num INT DEFAULT 10000;
    DECLARE article_num INT DEFAULT 100000;
    DECLARE comment_num INT DEFAULT 1000000;
    DECLARE author_num INT DEFAULT 1001;
    DECLARE i INT;
    SET i = 0;
    WHILE i < blog_num DO
    SET i = i + 1;
    INSERT INTO blog (id, title, author_id) VALUES (i, concat('blog_no_', i), 1 + floor(rand() * (author_num)));
    END WHILE;
    SET i = 0;
    WHILE i < article_num DO
    SET i = i + 1;
    INSERT INTO article (id, blog_id, author_id, title, content)
    VALUES (i, floor(rand() * (blog_num)),
            floor(rand() * (author_num)), concat('article_no_', i), concat('content_no_', i));
    END WHILE;
    SET i = 0;
    WHILE i < comment_num DO
    SET i = i + 1;
    INSERT INTO comment (id, article_id, comment) VALUES (i, floor(rand() * (author_num)), concat('comment_no_', i));
    END WHILE;
    SET i = 0;
    WHILE i < author_num DO
    SET i = i + 1;
    INSERT INTO author (id, name, password) VALUES (i, concat('name_no_', i), concat('password_no_', i));
    END WHILE;
END //
DELIMITER ;
CALL prepare_data();

参考文献

MySQL 5.7 Reference Manual - EXPLAIN Output Format
mysql explain详解
MySQL 优化之 index merge(索引合并)
《高性能MySQL》