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无法使用索引完成排序操作,而需要选择相应的排序算法在内存或磁盘上完成排序