Explain执行计划各参数含义

EXPLAIN 执行计划各字段含义

列	解释
id	查询中执行select子句或操作表的顺序
select_type	SELECT关键字对应的查询类型
table	表名、表别名或临时表的标识
type	查询使用了哪种类型
possible_keys	可能用到的索引
key	实际使用的索引
key_len	索引中使用的字节数
ref	使用索引列等值查询时，与索引列等值匹配的对象信息
rows	查询优化器估计要读取并检测的行数
Extra	不适合在其他列中显式但十分重要的额外信息

id

列编号是 SELECT 的序列号，并且 id 的顺序是按 SELECT 出现的顺序增长的。
id列越大执行优先级越高
id 相同则从上往下执行，id 为 NULL 最后执行。

select_type

类型	释义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM中包含的子查询被标记为DERIVED(衍生)
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED
UNION RESULT	从UNION表获取结果的SELECT

SELECT出现在UNION之后，则被标记为UNION

EXPLAIN SELECT * FROM student WHERE id =1 UNION SELECT * FROM student

table

当前执行的表

type

从最好到最差依次是：

system > const > eq_ref > ref > range > index > all

• system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
• const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

where id = 1 //常量

• eq_ref 在连接查询时，如果被驱动表是通过主键或者**唯一（表中只有一条记录与之匹配）**二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是 eq_ref。
• ref 非唯一性索引扫描，返回匹配某个单独值的所有行。

Where name = '王晶' //普通索引匹配符合条件所有行

• range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

possible_keys

EXPLAIN 执行计划结果可能出现 possible_keys 列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，MySQL 会认为索引对此查询帮助不大，选择了全表查询。

key

key 列表示SQL实际采用了哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。

查询中若使用了覆盖索引，该索引只出现在key列表中。

key_len

key_len表示索引记录的最大长度。

ref

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。常见的有：const（常量），字段名（例：student.id）。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

Extra

Using filesort

得到所需结果集，需要对所有记录进行文件排序。
典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

Using temporary

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。
group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

Using index

SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录.

Using index condition

确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录

Using where

表明使用了where过滤

参考样例

参考转载

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

insert into user values(1,'shenjian','no');
insert into user values(2,'zhangsan','no');
insert into user values(3,'lisi','yes');
insert into user values(4,'lisi','no');

数据说明：

用户表：id主键索引，name普通索引（非唯一），sex无索引；

四行记录：其中name普通索引存在重复记录lisi；

一、【Using where】

实验语句：

explain select * from user where sex='no';

结果说明：

Extra为Using where说明，SQL使用了where条件过滤数据。

需要注意的是：

（1）返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；

（2）使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断；

本例虽然Extra字段说明使用了where条件过滤，但type属性是ALL，表示需要扫描全部数据，仍有优化空间。

常见的优化方法为，在where过滤属性上添加索引。

画外音：本例中，sex字段区分度不高，添加索引对性能提升有限。

二、【Using index】

实验语句：

explain select id,name from user where name='shenjian';

结果说明：

Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

这类SQL语句往往性能较好。

问题来了，什么样的列数据，会包含在索引树上呢？

三、【Using index condition】

实验语句：

explain select id,name,sex from user where name='shenjian';

画外音：该SQL语句与上一个SQL语句不同的地方在于，被查询的列，多了一个sex字段。

结果说明：

Extra为Using index condition说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

画外音：聚集索引，普通索引的底层实现差异，详见《1分钟了解MyISAM与InnoDB的索引差异》。

这类SQL语句性能也较高，但不如Using index。

问题来了，如何优化为Using index呢？

四、【Using filesort】

实验语句：

explain select * from user order by sex;

结果说明：

Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。

这类SQL语句性能极差，需要进行优化。

典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

五、【Using temporary】

实验语句：

explain select * from user group by name order by sex;

结果说明：

Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。

这类SQL语句性能较低，往往也需要进行优化。

典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

六、【Using join buffer (Block Nested Loop)】

实验语句：

explain select * from user where id in (select id from user where sex='no');

结果说明：

Extra为Using join buffer (Block Nested Loop)说明，需要进行嵌套循环计算。

画外音：内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。

这类SQL语句性能往往也较低，需要进行优化。

典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。