mysql使用EXPLAIN查看执行计划

我们经常使用 MySQL 的执行计划来查看 SQL 语句的执行效率:如下

目录

id列

select_type列

table列

type列

possible_keys列

key列

key_len列

ref列

rows列

filtered列

Extra列

---

id列

这一列总是包含一个编号,标识select所属的行,如果在语句中没有子查询或联合查询,那么只会有唯一的select,于是每一行在这个列中都将显示一个1.

mysql将select查询分为简单和复杂类型,复杂类型可以分为三大类:简单子查询、所谓的派生表(在from子句中的子查询),以及union查询

• id 相同，执行顺序由上至下
• id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

 

select_type列

这一列显示了对应行是简单还是复杂select(如果是后者,那么是三种复杂类型中的哪一种).

• SIMPLE

SIMPLE值意味着查询不包括子查询和UNION.如果查询有任何复杂的部分,则最外层部分标记为PRIMARY,其他部分标记如下:

• SUBQUERY

包含在SELECT列表中的子查询中的SELECT(换句话说,不在FROM子句中)标记为SUBQUERY;

• DERIVED

DERIVED值用来表示包含在FROM子句的子查询中的SELECT,MySQL会递归执行并将结果放到一个临时表中,服务器内部称其为"派生表",因为该临时表是从子查询中派生而来的.

• UNION

在UNION中的第二个和随后的SELECT被标记为UNION.第一个SELECT被标记就好像它以部分外查询来执行.这就是之前的例子中在UNION中的第一个SELECT显示为PRIMARY的原因.如果UNION被FROM子句中的子查询包含,那么它的第一个SELECT会被标记为DERIVED

• UNION RESULT

用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT.

除了这些值,SUBQUERY和UNION还可以被标记为DEPENDENT和UNCHCHEABLE.DEPENDENT意味着SELECT依赖于外层查询中发现的数据;UNCHCHEABLE意味着SELECT中的某些特性阻止结果被缓存于一个Item_cache中.(Item_cache未被文档记载;它与查询缓存不是一回事,尽管它可以被一些相同类型的构建否定,例如RAND()函数.).

table列

这一列显示了对应行正在访问那个表.在通常情况下,它相当明了:它就是那个表或者那个表的别名

type列

显示了"关联类型",准确说法是访问类型--换句话是MySq决定如何查找表中的行,下面是最重要的访问方法,依次从最差到最优:

• ALL

全表扫描,通常意味着MySQL必须扫描整张表,从头到尾去找到需要的行(或者使用limit,以及在Extra列中显示"usion distinct/not exists")

• index

这个和全表扫描一样,只是MySQL扫描是按索引次序进行而不是行,它的主要优点是避免了排序;最大的缺点是要承担按索引次序读取整个表的开销.通常意味着若是按随机次序访问行,开销将会非常大.

如果在Extra列中看到'Using index' ,说明MySQL正在使用覆盖索引,它只扫描索引的数据,而不是按索引次序的每一行.它比按索引次序全表扫描的开销要小很多.

• range

范围扫描就是一个有限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行.这比全索引扫描好一些,因为它不用遍历全部索引.显而易见的扫描范围是带有BETWEEN或在WHERE子句里带有>的查询.

当MySQL使用索引去查找一系列值的时候,例如IN()和OR列表,也会显示为范围扫描.然而,这两者其实是相当不同的访问类型,在性能上有重要的差异.

• ref

这是一种索引访问(有时也叫索引查找),它返回所有匹配某个单个值的行.然而,它可能会找到多个符合条件的行,它是查找和扫描的混合体.此类索引访问只有当使用非唯一索引或者唯一性索引的非唯一性前缀时才会发生.把它叫做ref是因为索引要和某个参考值相比较.这个参考值或者是一个常数,或者是来自多表查询前一个表里的结果值.

ref_or_null是ref之上的一个变体,它意味着mysql必须在初次查找的结果里面进行第二次查找以找出NULL条目.

• eq_ref

使用这种索引查找,MySQL知道最多只返回一条符合条件的记录.这种访问方法可以在MySQL使用主键或者唯一性索引查找时看到,它会将它们与某个参考值作比较.

• const,system

当mysql能对查询的某部分进行优化并将其转换成一个常量时,它就会使用这些访问类型

• NULL

这种访问方式意味着mysql能在优化阶段分解查询语句,在执行阶段甚至用不着在访问表或者索引.例如,从一个索引列里选取最小值可以通过单独查找索引来完成.不需要在执行时访问表.

possible_keys列

这一列显示了可以使用那些索引,这是基于查询访问的列的使用的比较操作符来判断的.这个列表是在优化过程早期创建的,因此有些罗列出来的索引可能对于后续优化过程是没用的.

key列

这一列显示了mysql决定采用那个索引来优化对该表的访问.如果该索引没有出现在possible_keys列中,那么MySQL选择它是处于另外的原因----例如,它可能选择了一个覆盖索引,哪怕没有WHERE子句.

换句话说,possible_keys揭示了那一个索引能有助于高效的查找,而key显示的是优化采用那一个索引可以最小化查询成本.

key_len列

该列显示了MySQL在索引里使用的字节数.

ref列

这一列显示了之前的表在key列记录的索引中查找的值所用的列或常量.

rows列

这一列是MySQL估计为了找到所需的行而要读取的行数.这个数字是内嵌循环关联计划里的循环数目.也就是说它不是mysql认为它最终要从表里读取出来的行数,而是MySQL为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数(这个估算可能很不精确).

filtered列

这一列是在MySQL5.1里新加的,在使用EXPLAIN EXTENEND时出现.它显示的是针对表里符合某个条件(WHERE子句或联接条件)的记录数的百分比所做的一个悲观估算

Extra列

这一列包含的是不适合在其他列显示的额外信息,常见的最重要的值如下:

• "Using index" 

此值表示MySQL将使用覆盖索引,以避免访问表.

• "Using where"

这意味着MySQL服务器将在存储引擎检索行后再进行过滤.许多WHERE条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带WHERE子句的查询都会显示"Using where".有时,"Using where"的出现就是一个暗示:查询可收益于不同的索引.

• "Using temporary"

这意味着MySQL在堆查询结果排序时会使用一个临时表.

• "Using filesort"

这意味着MySQL会对结果使用一个外部索引排序,而不是按索引次序从表里读取行.

• "Range checked for each record(index map:N)"

这个值意味着没有好用的索引,新的索引将在连接的每一行上重新估算.