MySQL explain命令详解

使用explain + sql语句可以得到该条sql语句的执行计划，具体信息如下

下面将依次介绍每个字段的含义

id

id表示在多表查询时，表的执行顺序，它是一组数字序列号，表示查询中执行select子句或操作表的顺序,其取值分为以下三种情况

1.id相同，表示表的执行顺序由上至下  

上例中，表示该sql语句执行时对三张表的查询顺序是先查询t1，再查询t3，最后查询t3

2.id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

上例中，表示该sql语句执行时对三张表的查询顺序是先查询t3，再查询t2，最后查询t1

3.id有相同值也有不同值,id值越大越先被执行，id值相同自上而下顺序执行

上例中，t3表最先被查询，第二个被查询的并不是一张真实的表，而是一张衍生表derived2，它是表t3的查询结果，也就是将上一步中t3的查询结果作为一张表来进行查询，该衍生表的命名方式是derived+2(2 表示由 id =2 的查询衍生出来的表)。最后被查询的表是t2。

select_type

select_type表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询，其值主要有以下几种情况

1.SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION，实例如下

2.PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary，实例如下

3.DERIVED：表示衍生表查询，意味着该查询的结果会作为一张临时表以供下一步的查询，实例如下

4.SUBQUERY：子查询，一般表示出现在SELECT或WHERE列表中的子查询，实例如下

5.DEPENDENT SUBQUERY：依赖子查询，同样用于表示出现在SELECT或WHERE列表中的子查询。与SUBQUERY不同的是，SUBQUERY的查询结果为单值，而DEPENDENT SUBQUERY的查询结果为多值，实例如下

6.UNCACHEABLE SUBQUREY：表示无法被缓存的子查询，实例如下

图中的 @@ 表示查的环境参数 ，无法缓存

7.UNION：表示出现在UNION关键字后的第二个获地N个select查询，实例如下

8.UNION RESULT：从UNION表获取结果的SELECT，实例如下

table

顾名思义，table表示查询的是哪张表

type

type表示查询的访问类型，它是一个重要指标，表示查询的性能高低，其值根据性能高低排列如下

system>const>eq_ref>ref>range>index>ALL

1.system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

2.const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快
如将主键置于where列表中，MySQL就能将该查询转换为一个常量，实例如下

上述查询中id为t1的主键，由于id唯一，所以id=1只匹配一条记录，因此该查询访问类型为const,查询结果作为一张衍生表供第二步查询，由于结果集中只有1条记录，因此第二步的查询访问类型为system.

3.req_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

该查询中首先对t1进行全表扫描，然后遍历查询出来的id值，依次用每个id值作为t2.id = key中的key值，去关联查询出t2表中符合条件的记录，由于t2中的id是主键索引，其值唯一，因此该查询类型是eq_ref

4.ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，对于每个索引值，可能会找到多个符合条件的行

t2表中的content字段上建立的是非唯一索引，因此该查询中对t2表的访问方式是ref

5.range：索引范围扫描，一般是在where语句中出现了between、<、>、in等的索引范围查询

6.index：Full Index Scan，全索引扫描，需要遍历索引树。

7.all：Full Table Scan，全表扫描，需要遍历全表以找到匹配的行

一般来说，要保证查询至少达到range级别，最好能达到ref。

possible_keys

possible_keys显示可能会用到的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

查询中实际使用到的索引，如果为NULL，则没有使用到索引

key_len

查询中使用到的索引的字节长度，该字段可用于判断是否充分的利用上了索引，该值越小说明索引的利用越充分，索引长度的计算方式如下

ref

ref表示使用了那些值进行索引查找，一般为一个常量值或表的某个字段

上例中，查询条件中有ename = AvDEjl，ref为const，表示使用常量值'AvDEjl'对idx_ename索引进行了查找，另一个查询条件为emp.deptno = dept.deptno，ref为mystest.emp.deptno，表示使用mytest库中的emp的deptno字段值对idx_deptno索引进行了查询。

rows

rows列显示MySQL认为它执行查询时需要读取的行数，该值越小，查询效率越高

Extra

包含不适合在其他列中显示但十分重要的额外信息，其常见的取值有几下几种情况

1.Using filesort：说明mysql会对数据使用一个外部的排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”

上述查询中要根据ename进行排序，由于该字段上无索引，因此使用到了文件排序

在ename字段加上索引后，则不再出现Using filesort，且查询时间大幅下降，这是因为索引本身是排好序的，若需要对加索引的字段进行排序，只需要从索引树上依次读取值即可，不再需要额外的排序操作，因此对排序字段加索引可以提高查询速度。

2.Using temporary：使用了临时表保存中间结果,MySQL在对查询结果进行排序，且数据量较大时便会使用临时表。常见于排序 order by 和分组查询 group by。

上述查询中要根据ename排序，但该字段上并无索引，因此出现了Using filesort和Using temporary，优化该查询，可以建立（deptno，ename）联合索引=，建立索引后的查询计划如下

可以看到，加上索引后Using filesort和Using temporary消失，性能大幅提高。

3.USING index：使用了覆盖索引，只需要查找索引树便可以得到查询结果，查询效率较高

4.Using where：表示查询中使用了where条件过滤

5.using join buffer：使用了连接缓存，当两张表做关联查询时，被驱动表上无索引可用，便会出现using join buffer

上述关联查询中，dept表为驱动表，emp表为被驱动表，算法的执行流程是先将dept表的所有记录读入内存，该内存区域被称为join_buffer，然后将emp表中的记录依次取出，和join_buffer中的数据做对比，若满足条件dept,deptno = emp.deptno，则将该条记录作为结果集中的一部分返回。可以看到该算法流程对两张表都做了全表扫描，表中的数据量大时，查询效率会很低。可以在emp表的deptno字段上添加索引来优化该查询。添加索引后该查询的执行流程会变成：先遍历表dept，然后根据从表dept中取出的每行数据中的deptno值去表emp中查找满足emp.deptno = 'xxxx' 条件的记录，由于emp表的deptno字段上有索引，因此只需要走索引树搜索便可以快速找到结果。因此对于出现using join buffer的查询，常用的优化手段是给被驱动表的关联字段加上索引。

6.impossible where：where子句的值总是false，不能用来获取任何元组，出现这种情况是sql的逻辑不正确，需要修改

7.select tables optimized away：表示在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化了COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在innodb中：

在Myisam中：

myisam 中会维护 总行数 (还有其他参数)这个参数，所以在执行查询时不会进行全表扫描。而是直接读取这个数。但会对增删产生一定的影响。根据业务情况决定谁好谁坏。innodb 中没有这个机制。

下图为一个explain的使用实例

参考资料:尚硅谷MySQL高级教程.周阳