mysql执行计划type_浅谈mysql执行计划之type

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 (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

grade int(11) NOT NULL,

PRIMARY KEY (id)

);

CREATE TABLE student (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

name char(20) NOT NULL,

age tinyint(4) NOT NULL,

score tinyint(4) NOT NULL,

class_id int(11) NOT NULL,

PRIMARY KEY (id),

KEY IDX_CLASS_ID (class_id),

KEY IDX_AGE (age)

);

DML:

INSERT INTO class(grade) VALUES(1),(1),(1),(2),(2);

INSERT INTO student(name, age, score, class_id)

VALUES ('Sawyer', 29, 88, 1), ('Del Piero', 22, 91, 1),

('Mitsunari', 44, 99, 2), ('Natalie', 41, 95, 2);

运行之后的结果：

class

student

至此，准备工作完成，接下来我们将一个一个地分析常见的type类型，从最效率的到最不效率的。

NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。由于innodb采用B+树最为索引的物理结构，而B+树的叶子节点是顺序排列的，所以当查询索引的最大或最小值时，不需要遍历叶子节点，只需要拿到叶子节点头或者尾即可。看下面的例子：

select max(age) from student;

NULL

system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。我们这里演示一下const的情况：

select * from student where id = 1;

const

但是要注意的是，并不是所有的where＝都是const，只有＝的右边是常量的时候才会走const。比如：

select * from class where id = grade;

not const

由于＝的右边并不是常量，且grade上没有建索引，所以该查询走了效率最差的ALL全表扫描。通常，我们会将查询条件里的所有字段都建立索引，上面的sql优化后会变成使用index索引查询：

eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况。这个只会出现在关联查询中，并且是根据唯一非空键(主键或唯一非空索引)查询的情况。如下：

select * from class c join student s

on c.id = s.class_id

where s.name = 'Sawyer';

eq_ref

发现了么，这里的执行结果出现了两条数据，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;

ref

通过这个执行计划可以分析出，由于查询条件是c.id=1,是一个常数查询，mysql首先使用const对c表进行查询，之后s表使用索引IDX_CLASS_ID对结果进行关联，由于索引IDX_CLASS_ID是非唯一非空索引，所以这里的查询方式为ref。

range：在一个索引上进行范围查找。既只使用一个索引，查询条件满足多个时。比如：

select * from student where age between 20 and 30;

range

通常情况下，where in 也是range查询，但是下面的例子却显示ALL：

select * from student where age in (29, 44);

ALL instead of range

这是mysql的优化器(optimizer)的优化结果。mysql通过分析发现此查询走全表扫描的代价比走索引的代价要小，所以选择了走全表扫描而非索引。大体的原因是通过索引读取一条数据至少要经过两次节点检索(聚簇索引的高度为3，数据存在叶子节点上)，而全表扫描是一次读取一个page中的多条记录，当查询结果超过总数据一定比例的时候(在这个例子中有一半的数据满足了查询条件)，走索引的查询开销反而比全表扫描要大，这时mysql则会放弃索引而选择进行全表扫描。优化器不仅仅能在索引和全表扫描中选择，甚至在不影响查询结果的前提下改变查询顺序等黑科技，这里就不再展开。对于我们的例子，有两种方式让mysql使用索引查询。一种是使用语法强制mysql使用索引：

select * from student force index(IDX_AGE) where age in (29, 44);

force index

当然，实际开发中在你没有十足的把握时，最好不要使用这样的语法，毕竟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条)的占比比较小。

image.png

index：遍历索引树查询，通常发生在查询结果只包含索引字段时。比较好理解，既只select索引字段，并且没有where条件，如：

select id from student;

index

ALL：全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。比如：

select * from student where score = 100;

ALL

有时就算是通过索引的字段查询，也会出现全表扫描的情况，最常见的情况就是对字段进行了函数处理，如：

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成为程序的瓶颈。