MySQL中的EXPLAIN

使用EXPLAIN加上SELECT语句可以获取优化器的查询执行计划

MySQL会在查询上设置一个标记,当执行查询时,这个标记会返回关于在执行计划中每一步的信息,而不是执行它。它会返回一行或多行信息,一个表示一张表,显示出执行计划中的每一部分和执行的次序

复杂的查询如from子句中包含查询,select列中也包含查询,则执行顺序为,先执行外层查询,再执行from子句中的子查询,最后执行select列表中的子查询

下面是查询结果的一个示例(为了方便查看做成了表格):

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE scores const PRIMARY PRIMARY 8 const 1  

1 row in set

实际上EXPLAIN后的SELECT语句的from子句包含子查询时,MySQL会执行子查询,再将结果放在一个临时表中,然后完成外层查询优化。

 

其实EXPLAIN只是一个近似的结果,有时甚至会相差甚远,下面是EXPALIN的一些限制

  1、EXPLAIN不会告诉你触发器、存储过程或UDF会如何影响查询的

  2、它并不支持存储过程。尽管可以手动抽取查询并单独地对齐进行EXPLAIN操作

  3、它并不会告诉你MySQL在查询执行中所做的特定优化

  4、它并不会显示关于查询的执行计划的所有信息

  5、它并不区分具有相同名字的事物,例如对内存排序和临时文件排序都使用filesort,并且对于磁盘上和内存中的临时表都显示Using temporary

  6、可能会误导。例如,对一个有着很小LIMIT的查询显示全索引扫描,5.1及之后的版本中EXPLAIN关于检查的行数会显示更精确的信息

 

EXPLAIN中的列

id列

  每一列的编号,如果有子查询和JOIN时,会显示多行,id按其在原始SQL语句中的顺序编号

select_type列

  显示对应行的查询是简单还是复杂,复杂类型分为三大类:简单子查询、所谓的派生表(在from子句中的子查询)、UNION查询,下面是可能的值

  SIMPLE  意味着查询不包含子查询和UNION

  PRIMARY  查询中有复杂部分,最外层的查询标记为PRIMARY

  SUBQUERY  包含不在FROM子句中子查询(在SELECT列表中的子查询)

  DERIVED  表示FROM子句有SELECT子查询,MySQL会先执行子查询并将结果放在临时表中

  UNION  UNION关键字后及以后的SELECT标记为UNION,UNION前的查询标记为PRIMARY

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

 

table列

  这一列显示了对应行正在访问哪个表,从这一列从上往下可以观察到MySQL的关联优化器为查询选择的关联顺序。

  包含多个JOIN的SELECT,从下往上先执行JOIN中的表

  <derivedN>  FROM子句有子查询时,N表示子查询的id,N执行EXPLAIN输出中的后面一行(先前引用)

  <unionid1,id2>  UNION RESULT的table列,id表示参与UNION的行的列表

 

type列

  显示访问类型,即MySQL决定如何查找表中的行,下面是最重要的访问方法,依次从最差到最优

  ALL

    全表扫描,意味着MySQL必须扫描整张表,从头到尾去找到需要的行。但是在查询中使用了LIMIT或者在Extra列显示Using distinct/not exists时

    type列也会显示ALL

    index

    也是全表扫描,只是MySQL扫描表时按照索引次序扫描而不是逐行扫描。它最大的优点是避免了排序,最大的缺点是要按索引次序读取整个表

    (使用索引扫描来排序,每扫描一条记录就回表查询一次对应的行)

  range

    范围扫描,即一个有限制的索引扫描,始于索引中的某一点,返回匹配区域内的行。比全表扫描好些,它意味着不必遍历全部索引中的节点。

    这个类型通常出现在 =,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN() ,NOT IN()

  ref

    这是一种索引访问(索引查找),它返回所有匹配某个单个值的行,可能有多个符合儿条件的行。因此它是查找和扫描的混合体,此类索引访问只有当使用

    非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。称作ref因为索引要跟某个参考值比较,ref可以用于使用=或<=>操作符的带索引的列。

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

  eq_ref

    使用这种索引,MySQL知道最多只返回一条符合条件的记录。这种访问方法可以在MySQL使用主键或者唯一性索引查找时看到。

  const,system

    当MySQL能对查询的某部分进行优化并将其转换成一个常量时,就会返回此访问类型。如查找某一行的主键值where后的条件就是主键,MySQL就能把这个

    查询转换为一个常量,然后就可以高效地将表从联接执行中移除

  NULL

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

    来完成,不需要在执行访问表(覆盖索引)

 

possible_keys列

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

  对于后续优化过程是没用的(有些索引不会使用)

 

key列

  这一列显示了MySQL决定采用哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys列中,那么MySQL选用它是出于另外的原因——例如,它可能

  选择了一个覆盖索引,哪怕没有where子句。

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

 

key_len列

  该列显示了MySQL在索引里使用的字节数(索引列表中定义的字节而不是实际数据占用的)。如果MySQL正在使用的只是索引里的某些列,那么就可以用这个值

  来算出具体是哪些列。MySQL5.5及之前版本只能使用索引的最左前缀(最左列的前缀)。

 

ref列

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

 

rows列

  这列显示了MySQL估计的为了找到所需的行而要读取的行数。这个数字是内嵌循环关联计划里的循环数组。所有的rows列的值相乘,可以估算出整个查询会

  检查的行数。

 

filtered列

  5.1新增的,在使用EXPLAIN EXTENDED时出现。显示的是针对表里符合某个条件(where子句或联接条件)的记录数的百分比所做的一个悲观估算。将rows列

  和这个百分比相乘,就能看到MySQL估算它将和查询计划里前一个表关联的行数。

 

Extra列

  这一列包含是不适合显示在其他列上的额外信息。

  Using index

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

  Using where

    意味着MySQL服务器将在存储引擎返回行之后再应用WHERE条件进行过滤(通常意味着优化器需要通过索引回表查询数据)。

    许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示Using where,有事Using where的出现就是一个暗示:查询可受益于不同的索引。

  Using temporary

    这意味着MySQL了临时表,可能出现在对查询结果排序、SQL有子查询(先执行子查询并将结果放在临时表中)、SQL有UNION操作

  Using filesort

    这意味着MySQL会对结果使用给一个外部索引排序,而不是按索引次序从表中读取行。具体是在内存还

    是在磁盘上进行排序无从得知。

  Range checked for each record(index map:N)

    意味着没有好用的索引,新的索引将在关联的每一行上重新估算,N是显示在possible_keys列中索引

    的位图,并且是冗余的

  Using union(A,B)

    查询同时使用这两个单列索引进行扫描,并将结果合并。一般是where A or B,A和B都是单列索引

posted @ 2019-04-22 15:20  杨岂  阅读(196)  评论(0编辑  收藏  举报