MySQL之Explain命令详解

MySQL之Explain命令详解

目录技术文章 2016年4月24日

explain命令是查看查询优化器如果决定执行查询的主要方法。

Explain的输出字段

id列

SQL执行顺序的标识，SQL从大到小执行。

select_type列

MySQL将SELECT查询分为简单和复杂类型，复杂类型可以分为三大类：简单子查询，FROM子句中子查询和UNION查询。select_type列显示了对应查询行是简单还是复杂的SELECT(如果是复杂的，属于哪一种)

1.SIMPLE类型，简单的SELECT，不包括UNION和子查询

2.PRIMARY类型，如果查询有任何的复杂的字部分，最外层SELECT类型

3.UNION类型，UNION查询中第二个开始后面所有的SELECT

4.SUBQUERY类型，子查询中第一个SELECT

5.DEPENDENT UNION类型，子查询中的UNION中的第二个开始后面所有的SELECT，依赖于外部查询的结果集

6.DEPENDENT SUBQUERY类型, 子查询中第一个SELECT，依赖于外部的查询结果

7.UNION RESULT类型，UNION的结果

8.DERIVED类型，FROM子句的子查询

table列

显示这次查询是基于哪个表，有时候显示的是derivedx, 代表第x步执行的结果

type列

访问的类型，即MySQL决定如何查找表中的行，类型从最好到最差依次为：system、const、eq_ref、ref、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。

• system/const类型

  当表最多只有一个匹配行，它将在查询时被读取并转换成一个常量时，使用const类型。const表很快，因为只读取一次。const常用于常数值比较主键或唯一索引索引的所有部分。system类型是const类型一种特例，在表仅有一行时使用。

• eq_ref类型

  对于来自前面表的行组合，从该表中读取一行，查询基于主键或唯一索引进行查找最多返回一行记录。MySQL对这种访问类型的优化做的非常好，因为它无需估计匹配行的范围或找到匹配行后再继续查找。

  如下图，基于users.id联接users和useraddrs表，查询计划是扫描useraddrs表，然后根据useraddrs.user_id对users表使用eq_ref类型对主键进行查找。

• ref/ref_or_null类型

  ref是一种索引访问，它返回所有匹配单个值得行，但查询不是基于主键或唯一索引进行的，所以它可能会返回多个符合条件的行。如果查询不能基于索引选择单行的话，则使用ref。ref_or_null类型是在ref初次查找的基础里进行第二次查询以找出NULL条目。

• index_merge类型

  使用索引合并优化方法，此情况下key列包含了使用的索引清单

• unique_subquery/index_subquery类型

  unqiue_subquery类型用于替换 value IN (SELECT primary_key FROM single_table WHERE some_expr)， index_subquery用于替换value IN (SELECT column FROM single_table WHERE some_expr)，两种唯一区别就是子查询中的查的分别是主键和非唯一索引

• range类型

  使用索引来进行范围查询，常使用的操作符是>、>=、<、<=、Between，当MySQL使用索引去查找一些列值如IN和OR列表，也会显示为范围扫描，但在性能上是有很大区别的。

• index类型

  扫描索引树，当只查询作为索引字段的列时，使用此类型。

• ALL类型

  全表扫描，MySQL必须扫描整张表，从头到尾，去找到所需要的行。

possible_keys列

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

key列

显示了MySQL决定采用哪个索引来优化对该表的访问。如果没有选择索引，则显示为NULL。如果显示的索引没有在possible_keys列中出现，可能是因为它选择了一个覆盖索引(SELECT数据列从索引中就可获取，无需访问表数据)。

key_len列

显示了MySQL在索引中使用的字节数。

ref列

显示了之前表在key列记录的索引中查找值所用的列或常量(const)。

rows列

MySQL为了估计查找到所需的行数而要读取的行数，这个数字是内嵌循环关联计划里的循环数目，是MySQL为了找到符合查询的那些行而必须读取的行的平均数。

extra列

显示的是一些额外信息，常用的如下所示

• Using index：MySQL使用覆盖索引以避免访问表
• Using where：MySQL不能仅仅通过索引就能获取所有需要满足查询条件的数据
• Using temporary：MySQL需要创建临时表来处理查询，这情况下说明需要优化
• Using filesort：MySQL无法使用索引完成排序操作，而需要选择相应的排序算法在内存或磁盘上完成排序