mysql执行计划作为分析一条sql的执行效率的工具十分有效，通过explain关键字便可查看select语句的具体执行计划，分析其是否按我们设计的执行，是否使用了索引，是否全表扫描等等。不过有很多开发同学对explain返回的执行计划不是非常了解，这里我通过一些简单的例子，为大家做个入门，希望能够抛砖引玉，让大家在日常开发中看懂explain的执行计划，并且优化sql。
首先要明确的一点是，explain只能解释select语句，所以不要试图执行explain update之类的语句。
在对一条select进行explain之后，我们会看到返回结果中有如下几个字段：
-
id表示执行的顺序，id越大越先执行，id一样的从上往下执行。 -
select_type表示查询类型，通常有:- simple：表示不需要union操作或者不包含子查询的简单查询。
- primary：表示最外层查询。
- union：union操作中第二个及之后的查询。
- dependent union：union操作中第二个及之后的查询，并且该查询依赖于外部查询。
- subquery：子查询中的第一个查询。
- dependent subquery：子查询中的第一个查询，并且该查询依赖于外部查询。
- derived：派生表查询，既from字句中的子查询。
- materialized：物化查询。
- uncacheable subquery：无法被缓存的子查询，对外部查询的每一行都需要重新进行查询。
- uncacheable union：union操作中第二个及之后的查询，并且该查询属于uncacheable subquery。
-
table表名或者表的别名。 -
partitions分区信息，非分区表为null。 -
type访问类型，表示找到所查询数据的方法，也是本文重点介绍的属性。该属性的常见值如下，性能从好到差：- NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。
- system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。
- eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况。
- ref：多表查询时，根据非唯一非空索引进行查询的情况。
- range：在一个索引上进行范围查找。
- index：遍历索引树查询，通常发生在查询结果只包含索引字段时。
- ALL：全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。
-
possible_keys表示mysql此次查询中可能使用的索引。 -
key表示mysql实际在此次查询中使用的索引。 -
key_len表示mysql使用的索引的长度。该值越小越好。 -
ref表示连接查询的连接条件。 -
rows表示mysql估计此次查询所需读取的行数。该值越小越好。 -
extra表示mysql解决查询的其他信息，有几十种不同的值，该信息也是我们优化sql可以专注的一个值。关于这个extra信息我可能会再下一篇中介绍，这里先略过。
到目前为止，我们基本已经了解了explain语句的输出的含义，结下来就进入实战，借助例子来介绍一下每种type的不同。首先我们准备一下本次演示中需要用到的数据。
- DDL:
CREATE TABLE
class(
idint(10) unsigned NOT NULL AUTO_INCREMENT,
gradeint(11) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE
student(
idint(10) unsigned NOT NULL AUTO_INCREMENT,
namechar(20) NOT NULL,
agetinyint(4) NOT NULL,
scoretinyint(4) NOT NULL,
class_idint(11) NOT NULL,
PRIMARY KEY (id),
KEYIDX_CLASS_ID(class_id),
KEYIDX_AGE(age)
);
- DML:
INSERT INTO
class(grade) VALUES(1),(1),(1),(2),(2);
INSERT INTOstudent(name,age,score,class_id)
VALUES ('Sawyer', 29, 88, 1), ('Del Piero', 22, 91, 1),
('Mitsunari', 44, 99, 2), ('Natalie', 41, 95, 2);
运行之后的结果：
至此，准备工作完成，接下来我们将一个一个地分析常见的type类型，从最效率的到最不效率的。
NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。由于innodb采用B+树最为索引的物理结构，而B+树的叶子节点是顺序排列的，所以当查询索引的最大或最小值时，不需要遍历叶子节点，只需要拿到叶子节点头或者尾即可。看下面的例子：
select max(age) from student;
system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。我们这里演示一下const的情况：
select * from student where id = 1;
但是要注意的是，并不是所有的where＝都是const，只有＝的右边是常量的时候才会走const。比如：
select * from class where id = grade;
由于＝的右边并不是常量，且grade上没有建索引，所以该查询走了效率最差的ALL全表扫描。通常，我们会将查询条件里的所有字段都建立索引，上面的sql优化后会变成使用index索引查询：
eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况。这个只会出现在关联查询中，并且是根据唯一非空键（主键或唯一非空索引）查询的情况。如下：
select * from class c join student s
on c.id = s.class_id
where s.name = 'Sawyer';
发现了么，这里的执行结果出现了两条数据，id同为1，回忆一下我们一开始说的，id代表了执行的顺序，当id相同时，顺序自上而下。所以分析这个执行结果可知，mysql首先在s表也就是student表中查询name字段为Sawyer的值，由于name字段上并没有索引，所以使用了全表扫描，该表一共有4条记录，所以扫描了4行，rows为4。然后c表也就是class表使用主键和之前的结果通过s.class_id关联，由于是关联查询，并且是通过唯一键进行查询，所以使用了eq_ref的类型。这里也可以通过建立name字段的索引来优化全表扫描的问题，这里就不再演示。
ref：多表查询时，根据非唯一非空索引进行查询的情况。这个跟eq_ref的唯一区别就是关联查询是根据非唯一非空索引进行的。比如：
select * from class c join student s
on c.id = s.class_id
where c.id = 1;
通过这个执行计划可以分析出，由于查询条件是c.id=1,是一个常数查询，mysql首先使用const对c表进行查询，之后s表使用索引IDX_CLASS_ID对结果进行关联，由于索引IDX_CLASS_ID是非唯一非空索引，所以这里的查询方式为ref。
range：在一个索引上进行范围查找。既只使用一个索引，查询条件满足多个时。比如：
select * from student where age between 20 and 30;
通常情况下，where in 也是range查询，但是下面的例子却显示ALL：
select * from student where age in (29, 44);
这是mysql的优化器（optimizer）的优化结果。mysql通过分析发现此查询走全表扫描的代价比走索引的代价要小，所以选择了走全表扫描而非索引。大体的原因是通过索引读取一条数据至少要经过两次节点检索（聚簇索引的高度为3，数据存在叶子节点上），而全表扫描是一次读取一个page中的多条记录，当查询结果超过总数据一定比例的时候（在这个例子中有一半的数据满足了查询条件），走索引的查询开销反而比全表扫描要大，这时mysql则会放弃索引而选择进行全表扫描。优化器不仅仅能在索引和全表扫描中选择，甚至在不影响查询结果的前提下改变查询顺序等黑科技，这里就不再展开。对于我们的例子，有两种方式让mysql使用索引查询。一种是使用语法强制mysql使用索引：
select * from student force index(IDX_AGE) where age in (29, 44);
当然，实际开发中在你没有十足的把握时，最好不要使用这样的语法，毕竟mysql优化器还是比较准的。
另一种方式是增加数据，使得查询条件命中的数据占总数据的占比比较小。这也是索引最能发挥作用的情况（既只有少部分数据满足查询条件）。
INSERT INTO student(name, age, score, class_id)
VALUES ('Tom', 61, 84, 3), ('Obama', 56, 100, 4),
('Nedved', 43, 86, 5), ('Buffon', 49, 86, 5);
这个时候我们再执行之前的sql就发现这次走的是索引的range了，原因是这次条件命中的数据（2条）占所有数据（8条）的占比比较小。
index：遍历索引树查询，通常发生在查询结果只包含索引字段时。比较好理解，既只select索引字段，并且没有where条件，如：
select id from student;
ALL：全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。比如：
select * from student where score = 100;
有时就算是通过索引的字段查询，也会出现全表扫描的情况，最常见的情况就是对字段进行了函数处理，如：
explain select * from student where coalesce(age, 18) > 20;
这里虽然age字段上建有索引，但是由于我们对age进行了函数处理，所以就没法再使用索引了，在写sql时一定要避免这种情况，如果实在有需要，可以使用mysql 5.7的计算字段（generated column），并建立索引来实现，这个我在《oracle迁移mysql总结》最后有提到。
关于explain的常见type类型就先讲这么多，在介绍的过程中其实也提到了一些sql优化的方案及优化器方面的东西，这些下次有机会再写一篇来具体介绍sql优化。 type其实还有别的类型如fulltext, ref_or_null, index_merge, unique_subquery和index_subquery，这些不太常见这里也先略过，下次有机会再补上。包括explain的extra信息也值得单独用一篇来讲，本文就暂且开个头，希望能对大家的sql技巧和sql优化有些帮助，活用explain，不要让sql成为程序的瓶颈。
