Mysql执行计划——解读Explain

mysql的执行计划再熟悉不过了，sql优化经常使用，但是里面很多字段都是知道个大概，没有很系统的学习过。今天就抽空整理一下这块的知识点。

如何查看执行计划

---

在要分析的sql之前加上 explain 关键字。
举个例子

explain 
select * from table

字段说明

---

id

普通的id字段，一般不用

select_type

查询类型

值	说明
SIMPLE	单表查询，不包含任何子部分
PRIMARY	如果包含复杂的子部分，最外层部分标记为PRIMARY
DERIVED	在from后面的子查询，mysql会为子查询创建一个临时表，也叫派生表
SUBQUERY	包含在select列表中的子查询
UNION	在union语句中，第二个开始的表都会被标记为UNION
UNION RESULT	用来从UNION的匿名临时表检索结果的select被标记为UNION RESULT

一般比较常用的是SIMPLE，PRIMARY，DERIVED。记住这三个，其他用的时候查资料就行。

table

当前sql访问的表名
当sql包含子查询时，table字段会标记为<derivedN> 这里的N是子查询对应id
举个例子

select count(1) from (select * from table) t

对应的执行计划应该是

id	select_type	table
1	PRIMARY	<derived2>
2	DERIVED	table

可以在从一列中从上往下观察 关联优化器 为查询选择的关联顺序。
举个例子

   explain 
   select film.file_id
   from film
   inner join film_actor using(film_id)
   inner join actor using(actor_id)

对应的执行计划是

id	select_type	table
1	SIMPLE	actor
2	SIMPLE	film_actor
3	SIMPLE	film
mysql的执行计划总是左侧深度树优先，我们可以根据这个原则从执行计划中解读出关联顺序。关联顺序与执行计划的关系如下图所示。

partitions

查询使用到表分区的分区名

type

访问类型，这个字段很重要。下面从差到优枚举一下。

值	说明
ALL	全表扫描，mysql需要扫描整张表才能取到想要的数据
index	按索引的顺序读取整个表的数据。 相比ALL，index的优点是避免排序。 出现index通常意味着若是按随机次序访问行，开销会非常大。 如果在EXtra列中看到"Using index"，说明Mysql正在使用覆盖索引， 它只扫描索引的数据，不是按索引次序扫描的每一行（不回表）。
rang	范围扫描是一个有限制的索引扫描。 它开始于索引的某一点，返回匹配的行。 rang比index好一些，因为它不需要全索引扫描
ref	索引访问，返回所有匹配某个单个值的行。 它可能返回多行，所以它是查找和扫描的混合体。 出现在使用非唯一索引，或者唯一索引的非唯一前缀时。
eq_ref	索引查找 和ref一样返回所有匹配某个单个值的行。区别是它只可能返回一行。 eq_ref可以在使用主键或者唯一键执行sql的时候看到。
const	当Mysql能将某部分查询语句转化成一个常量时，它就会使用const访问类型。 简单来说就是当查询最多只有一个匹配行的时候，出现在主键和唯一键查询。 const和eq_ref的区别： const一般出现在当前查询最多只有一个匹配行的情况下 eq_ref一般是多表关联，关联字段是唯一性索引，但是当前sql可能返回多行
system	system是const类型的特例，当查询的表只有一行的情况下，使用system。
NULL	意味着Mysql能在优化阶段分解查询语句，在执行阶段甚至不用访问表或者索引 例如从索引列里选取最小值可以通过单独查询索引来完成。

possible_keys

这一列表示本次查询可以使用哪些索引。这是基于查询访问的列和使用的比较操作符来判断的。

key

这一列显示了Mysql决定采用哪个索引。优化器会选择查询成本最低的索引。

key_len

该列显示了Mysql在索引里使用的字节数。可以使用这个值来计算出具体使用了索引中的哪几个字段。这个字节数对应的是字段类型的字节数，和存储值无关。
注意点：

1. 对于字符串类型，需要把字符列的字符集也考虑进去，比如utf8下一个字符最大占用3个字节，所以使用3字节来计算。
2. 当索引字段为定长数据类型，比如char，int，datetime，需要有是否为空的标记，这个标记需要占用1个字节，当字段定义为非空的时候，是否为空的标记将不占用字节。
3. 对于变长数据类型，比如：varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节。

ref

这一列显示了当前的表在key列记录的索引中查询值所用的列或者常量。
举个例子：

  explain
  select * from 
  a inner join b
  on a.bid = b.id

table	type	key	ref
a	ALL
b	eq_ref	PRIMARY	a.bid
这个例子里，b表走了主键索引，而走索引查询所使用的列就是a表的bid字段

rows

这是Mysql估算的，为了找到所需的行而要读取的行数，大体就是扫描行数的意思。这只是个估算，不一定准确，可以作为参考。

filtered

这一列显示的是针对扫描行里符合条件的记录数的百分比。rows * filtered/100 可以估算出将要和执行计划里前一个表关联的行数。
这个百分比应该是越大越好，越大代表扫描行数和最终结果集越接近。

extra

对于当前查询的额外信息。常见的见下表。

值	说明
Using index	表示Mysql将使用覆盖索引，以避免访问表。 不要把覆盖索引和index访问类型搞混了。 覆盖索引指的是，索引中包含了需要返回的所有列（select的字段）。
Using where	表示Mysql将在存储引擎检索行之后再进行过滤。 许多where条件里涉及索引中的列，当它读取索引时，就能被存储引擎校验。 因此不是所有带where子句的查询都会现实“Using where”。 有时“Using where”就是一个暗示：查询可受益于不同的索引（换个索引吧）
Using index Condition	Index Condition Pushdown（ICP）是Mysql 5.6开始支持的一种优化方式。 使用ICP时，Mysql会在取出索引的同时把where的部分过滤操作放在存储引擎层进行（当存在只通过索引就可以进行where过滤时发生）。 在某些查询下可以大大减少SQL层对存储引擎层的查询。
Using temporary	表示Mysql在对查询结果排序时会使用一个临时表
Using filesort	表示Mysql会对结果使用一个外部索引排序， 而不是按索引次序从表里读取行。
Range checked for each record (index map: N)	表示没有好用的索引，新的索引将在联接的每一行上重新估算。 N是显示在possible_keys列中索引的位图，并且是冗余的。
前5种在工作中会比较常见，掌握好可以解决80%的问题。

---

参考资料

《高性能Mysql》
《MySQL技术内幕-InnoDB存储引擎》