Mysql深度讲解 – explain关键字（一）

前言

Mysql中给我们提供了explain关键字去分析一条sql执行的大概过程。比如工作中间常常会遇到执行效率很低的”慢sql”。有些sql语句一直执行的就很慢，有些sql只是偶发性的慢。所以有时候可能sql执行得慢并不是sql的问题，而是服务器资源被占满(比如表被锁，网络问题等等)，无法获取执行sql的资源导致的慢，这时候其实还好。那么如果一个sql一直运行的效率比较低，那么就必须要用到explain关键字去分析sql，本篇就是要详细的说下explain关键字。在本篇最后附录中有笔者创建的演示表，方便大家对照阅读。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

Explain 关键字解析

对于优化SQL一般第一步就会去查看explain输出的内容，因为通过explain的输出我们可以分析出SQL执行的步骤，从而优化SQL。下表就是explain关键字输出的字段以及说明。在解释这些关键字的同时，可能会有一些名词，比如物化表，in和exist的转换等等，这部分内容的详细解释在【Mysql深度讲解 – 子查询优化】这篇博客里基本都有涉及。

字段	说明
id	在一个查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

table字段

这个字段记录了每一个参与查询结果集的表名，比如：explain select * from t1 join t2; 这里显示的t1和t2就是参与这条查询语句的表。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	Using join buffer (Block Nested Loop)

id字段

一般写的查询语句都以SELECT关键字开头，比较简单的查询语句里只有一个SELECT，但是有两种情况会在一条语句中出现多个SELECT。第一、WHERE中包含子查询；第二、使用UNION语句的时候。一般来说查询语句中每出现一个SELECT，Mysql就会去分配一个唯一的id。所以这个id字段是按照SELECT关键字区分是否属于一个查询语句的。

---

例如，普通的一条查询语句explain select * from t1：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	NULL

---

例如，带join的一条查询语句explain select * from t1 join t2，可以看到即便是两个表也被认为是一个select语句，其id都是一样的。值得注意的是在join的执行中，虽然join在一起表都会对应一条记录，而且id是相同的，但是靠前的表是驱动表，后边的表则是被驱动表。这里的结果是被查询优化器优化过执行的t2驱动t1：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	Using join buffer (Block Nested Loop)

---

例如，含有In的子查询explain select * from t1 where a in (select a from t2) or c = 'c'，这次查询的id就是id不同了，因为这里的两个查询是分开的。由于查询优化器可能会把in优化为join语句因此这个语句的后面加了一个or关键字。因此这里两个select就代表两次查询，所以id分配了两个：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	Using where
2	SUBQUERY	t2	NULL	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index

---

例如，含有Union或者 Union All的语句，Union和Union All的区别在于Union会对查询出来的结果去重，而Union All则是原始数据，我们可以通过explain关键字查看其执行的不同之处。通过对比可以发现Union语句比Union All多了一行，这多出来的一行就是去重复的逻辑：把t1表和t2表的数据都查出来，然后放到一个临时表中，这个临时表名的字就叫做<union1,2>，字段Extra也标注了Using temporary使用临时表，Union就是基于这个临时表去重然后再返回结果出来，由于<union1,2>是一个临时表，因此它没有id。而Union All则不需要去重，因此不需要额外的步骤直接返回即可。

执行Union的查询explain select * from t1 union select * from t2：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	NULL
2	UNION	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
NULL	UNION RESULT	< union1,2 >	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary

执行执行UnionAll的查询explain select * from t1 union all select * from t2：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	NULL
2	UNION	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL

---

select_type字段

这个字段用来表示这次查询是一个什么样的查询类型，仅仅是为了显示select进行的查询类型是什么样的，用于给用户比较直观的展示而已，没什么可以深入讲解的，它有如下几个类型。

---

SIMPLE 简单的查询：表示查询类型就是针对单一表的简单查询，比如：
explain select * from t1 join t2 或者explain select * from t1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	Using join buffer (Block Nested Loop)

---

PRIMARY 主查询：区分于子查询SUBQUERY，一般来说就是指最外面的Select查询。

---

SUBQUERY子查询(不相关)：区分于主查询来说，一般指条件语句中的Select查询，单独出现的时候特指不相关子查询，比如：
explain select * from t1 where a in (select a from t2) or c = 'c';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	Using where
2	SUBQUERY	t2	NULL	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index

---

UNION 查询：和主查询平级，表示这个查询是一个UNION修饰得查询。

---

UNION RESULT 查询：表示这个查询是UNION查询中构建的去重临时表。
explain select * from t1 union select * from t2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	NULL
2	UNION	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
NULL	UNION RESULT	< union1,2 >	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary

---

DEPENDENT SUBQUERY 相关子查询：区分于PRIMARY和SUBQUERY，一般指子查询中有数据依赖于著查询的条件查询。相关子查询如果不经过优化，会对子查询有比较多的执行次数，如果发现查询条件里有DEPENDENT SUBQUERY，可以尝试分析是否有办法优化掉。
explain select * from t1 where a in (select a from t2 where t1.a = t2.a) or c ='c';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	Using where
2	DEPENDENT SUBQUERY	t2	NULL	eq_ref	PRIMARY	PRIMARY	4	world.t1.a	1	100.00	Using where; Using index

---

DERVIED 派生表：表示该查询含有用户指定的表名的表/临时表，比如：
explain select * from (select a| count(*) from t2 group by a ) as deliver_table;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	< derived2 >	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
2	DERIVED	t2	NULL	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index

---

MATERIALIZED 物化表查询：表示该查询含有被物化处理过的表，比如下面这个查询语句，就会把in条件内的查询结果做成一个物化表。下面<subquery2>就是t2表经过物化以后的表，Mysql的查询优化器自动把t2表物化，然后和t1进行了一个join操作，比如：
explain select * from t1 where a in (select c from t2 where e = 1);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	< subquery2 >	NULL	ALL	NULL	NULL	NULL	NULL	NULL	100.00	Using where
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	< subquery2 >.c	1	100.00	NULL
2	MATERIALIZED	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where

type字段

这个字段表示访问方法，比如一个查询是全表扫描ALL，还是索引index，或者部分查询range等等查询的类型，就是通过这个字段显示的。

---

system：在MyISAM这种精确存储引擎中使用，且当表里仅有一行数据时使用，不多说。

---

const：表示常量查询。针对查询使用了主键索引或者唯一索引，并且该查询有等值常量条件的时候会使用的类型，比如：
explain select * from t1 where a = 1 或者 select * from t1 where a in(1);

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

---

eq_ref：一般出现在join查询中，表示被驱动表，比如。
explain select * from t1 join t2 on t1.a = t2.a;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	PRIMARY	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	world.t2.a	1	100.00	NULL

---

ref：一般出现在辅助索引查询中。当一个查询使用的不是主键索引，而是使用的辅助索引会使用该类型，比如：
explain select * from t1 where b = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	idx_t1_bcd	idx_t1_bcd	5	const	2	100.00	NULL

---

ref_or_null：查询语句除了ref的条件以外还是用了is null条件，比如：
explain select * from t1 where b = 1 or b is null;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref_or_null	idx_t1_bcd	idx_t1_bcd	5	const	3	100.00	Using index condition

---

index_merge：索引合并。被Mysql判断为索引可以合并的情况，比如下面的sql语句。这个语句的条件a=1可以用到主键索引，b=1可以用到辅助索引。因此a=1拿到结果集1，b=1拿到结果集2。由于使用的是or连接，因此将结果集1和结果集2合并即可，可以看作两个索引同时再用，所以被称为index_merge。
explain select * from t1 where a = 1 or b = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index_merge	PRIMARY,idx_t1_bcd	idx_t1_bcd,PRIMARY	5,4	NULL	3	100.00	Using sort_union(idx_t1_bcd,PRIMARY); Using where

---

unique_subquery：唯一子查询。这个条件出现的情况是：如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行的类型就是unique_subquery，比如：
explain select * from t1 where c in (select a from t2 where t1.e = t2.e) or a = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	PRIMARY	NULL	NULL	NULL	9	100.00	Using where
2	DEPENDENT SUBQUERY	t2	NULL	unique_subquery	PRIMARY	PRIMARY	4	func	1	33.33	Using where

---

index_subquery：索引子查询。这个条件和上面的类似，只不过访问子查询中的表时使用的时普通的索引，比如：
explain select * from t1 where c in (select b from t1) or a = 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	PRIMARY	NULL	NULL	NULL	9	100.00	Using where
2	DEPENDENT SUBQUERY	t2	NULL	unique_subquery	PRIMARY	PRIMARY	4	func	1	33.33	Using where

---

range：范围查询。一般出现在<、>、in条件比较少的情况，表示使用的是一个范围。比如：
explain select * from t1 where a > 1 或者 select * from t1 where a in( 1|2);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY	PRIMARY	4	NULL	8	100.00	Using where

---

index：索引查询。表示使用索引的查询，对于建了索引的列来说，最快的就是使用索引扫描，因此即便是没有where条件，也不符合最左原则，依然会使用索引。只不过如果是不适用索引的条件进行扫描的时候，会扫描整个索引。该过程类似于全表扫描，但是由于索引比全表扫描数据少，所以依然比全表扫描成本少，因此也会使用索引。注：由于不会进行where比较，因此不会走索引非叶子节点，比如：
explain select c from t1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	idx_t1_bcd	15	NULL	9	100.00	Using index

---

All：全表扫描。对于某些不是主键，且没有索引的列进行的查找，比如。
explain select e from t1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	9	100.00	NULL

---

type字段的性能排序

对于这么多的查询类型，就有了性能问题了。type的查询性能就是本篇对type字段的介绍顺序，从上往下依次减少。

字段名	解释
const	常量自然是最快的，第一。
eq_ref	代表使用的是主键或者唯一索引，第二。
ref	代表使用的普通索引，第三。
ref_or_null	代表使用的普通索引且使用了is null条件，第四。
index_merge	代表使用索引合并，已经至少使用两个索引了，第五。
unique_subquery	子查询使用唯一索引，第六。
index_subquery	子查询使用辅助索引，第七。
range	范围查询慢于等值查询，第八。
index	使用索引进行全表查询，扫描整个索引树，第九。
ALL	全表扫描，不使用索引，最慢的，第十。

所以性能排序从大到小就是：
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

总结

本篇中笔者对explain中的关键字table、id、select_type、type等四个字段做了详细的解释，并将其出现条件做了归类整理，下篇【Mysql深度讲解 – explain关键字（二）】将会继续对剩余字段进行解析。

附：本例中构建的表：

create table t1(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;
create index idx_t1_bcd on t1(b,c,d);

insert into t1 values(12,1,2,4,'a');
insert into t1 values(6,4,5,4,'b');
insert into t1 values(9,1,1,1,'c');
insert into t1 values(1,6,7,4,'d');
insert into t1 values(15,2,2,5,'e');
insert into t1 values(7,9,3,6,'f');
insert into t1 values(4,2,1,7,'g');
insert into t1 values(3,3,3,3,'h');
insert into t1 values(10,5,5,5,'ss');

create table t2(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;

insert into t2 values(1,6,7,4,'d');
insert into t2 values(4,2,1,7,'g');
insert into t2 values(3,3,3,3,'h');