MySQL explain命令详解

在工作中，饿哦们用于捕捉性能问题最常见的就是打开慢查询，定位执行效率差的SQL，那么当我们定位到一个SQL以后还不算完事，我们还需要知道SQL的执行计划，比如是全表扫描，还是索引扫描，我们创建的索引是否被MySQL优化器使用到等，这些都需要通过explain去完成。explain命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。需要注意的是，生成的QEP（执行计划：Query Execution Plan）并不确定，他可能会根据很多因素发生改变。MySQL不会将一个QEP和某个给定查询绑定，QEP将由SQL语句每次执行时的实际情况确定，即便使用存储过程也是如此。尽管在存储过程中SQL语句都是预先编译过的，但QEP仍然会在每次调用存储过程的时候才被确定。

Explain命令详细说明：

  1. 如何使用Explain命令呢？
  Explain SQL语句
  运行show warning可以查看警告信息。

  2. 通过Explain命令我们可以知道什么呢？
  mysql> explain select * from emp;
  +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
  |  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4731860 |   100.00 | NULL  |
  +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
  1 row in set, 1 warning (0.15 sec)

  3. explain各字段解析：

  1）id：包含一组数字，表示查询中执行select子句或操作表的顺序
  id相同从上往下顺序执行；
  id不同id值越大越先被执行
  id有相同有不同时，相同的可以认为是一组，从上到下顺序执行，id不同的id值越大，越先执行。

  2）select_type：查询中每个select子句的类型

  a）simple：简单SELECT,不使用UNION或子查询等；
  b）primary：查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY；
  c）subquery：在select或where中包含子查询，则该子查询被标记为subquery；
  d）derived(衍生)：在from中包含的子查询被标记为derived（（5.7及以上版本中改为MATERIALIZED））。
  用来标识包含在from子句中的子查询的select，MySQL会递归执行并将结果放到一个临时表中。
  服务器内部称为“派生表”，因为该临时表是从子查询中派生出来的。
  e）union：若第二个select出现在union之后，则该select被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived。
  f）union result：从union表获取结果的select被被标记为union result。
  subquery和union还可以被标记为dependent和uncacheable。
  dependent意味着select依赖于外层查询中发现的数据。
  uncacheable意味着select中的某些特性阻止结果被缓存域一个item_cache中。
  
  3）table：表示查询的是哪张表，当table值为derivedx(<5.7)或subqueryx(>=5.7)0时(x是一数字，对应前面的id编号，表示从第几步执行的结果衍生出来的表)。
  
  4）partitions：使用的哪个分区，需要结合表分区才可以看到

  5）type:
  Explain命令中的type列，显示MySQL查询所使用的关联类型（Join Types）或者访问类型，它表明MySQL决定如何查找表中符合条件的行。
  常见的访问类型性能由差到优依次为：
  ALL < index < range < index_subquery < unique_sunquery < index_merge < ref_or_null < fulltext < ref < eq_ref < const < system。
  a）all：全表扫描，通常意味着MySQL必须从头到尾扫描整张表，去查询匹配的行，性能极差。
  但是如果在查询里使用了limit n ，虽然type仍然是all，但是MySQL只需要扫描到符合条件的前n行数据，就会停止继续扫描。

  b）index：根all一样，也会进行全表扫描，只是MySQL会按索引次序进行全表扫描，而不是直接扫描行数据。
  它的主要优点就是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。
  若是按随机次序访问，开销将会非常大。
  
  c）range：范围扫描，一个有范围限制的索引扫描，它开始于索引里的某一点，返回匹配这个范围值的行。
  range比全索引扫描更有效，因为它不用遍历全部索引。
  范围扫描分为以下两种情况：
  ① 范围条件查找：在where子句中带有between、>、<、>=、<=的查询。
  ② 多个等值条件查询：使用in()和or，以及适用like进行前缀匹配的模糊查询。

  d）index_subquery：index_subquery替换了一下形式的子查询中的eq_ref访问类型，其中key_column是非唯一索引。
  value in (select key_column from single_table where some_expr)
  index_subquery只是一个索引查找函数，它可以完全替换子查询，提高查询效率。
  大多数情况下，使用select子查询时，MySQL查询优化器会自动将子查询优化为联表查询，因此type不会显示为index_subquery。

  e）unique_subquery：跟index_subquery类似，它替换了以下形式的子查询中的ed_ref访问类型，其中primary_key可以是主键索引或唯一索引。
  value in (select primary_key from single_table where son_expr)
  unique_subquery只是一个索引查找函数，它可以完全替换子查询，提高查询效率。

  f）index_merge：表示出现了索引合并优化，通常是将多个索引字段的范围扫描合并为一个。
  包括但表中多个索引的交集，并集以及交集之间的并集，但不包括跨多张表和全文索引。
  这种优化并非必然发生的，当查询优化器判断优化后查询效率更优时才会进行优化。

  g）ref_or_null：ref_or_null与ref类似，但是MySQL必须对包含null值的行进行额外搜索。
  mysql> create index idx_name on emp(name);
  mysql> mysql> explain select * from emp where name='' or name is null;
  +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
  | id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
  +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
  |  1 | SIMPLE      | emp   | NULL       | ref_or_null | idx_name      | idx_name | 83      | const |    2 |   100.00 | Using index condition |
  +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
  1 row in set, 1 warning (0.00 sec)

  h）fulltext：命中全文索引时type为fulltext。
  
  i）ref：索引访问(有时也叫索引查找)，它返回所有匹配某个单个值的行。
  然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体。
  此类索引访问只有当使用非唯一索引或者唯一性索引的非唯一性前缀时才会发生。
  把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或是一个常量，或是来自多表查询前一个表里的结果值。
  mysql> explain select * from emp where name='aJHSUnced';
  +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  |  1 | SIMPLE      | emp   | NULL       | ref  | idx_name      | idx_name | 83      | const |    1 |   100.00 | NULL  |
  +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  1 row in set, 1 warning (0.00 sec)

  j）eq_ref：当进行等值联表查询时，联结字段命中主键索引或唯一的非空索引时，将使用eq_ref。

  mysql> explain select * from emp left join dept on emp.deptid=dept.id;
  +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+---------+----------+-------+
  | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows    | filtered | Extra |
  +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+---------+----------+-------+
  |  1 | SIMPLE      | emp   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               | 4986630 |   100.00 | NULL  |
  |  1 | SIMPLE      | dept  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb1.emp.deptid |       1 |   100.00 | NULL  |
  +----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+---------+----------+-------+
  2 rows in set, 1 warning (0.00 sec)

  k）const：MySQL知道查询最多只能匹配到一条符合条件的记录。因为只有一行，所以优化器可以将这一行中的列中的值视为常量。
  const表查询非常快，因为它们只读取一次数据行。
  通常使用主键或唯一索引进行等值条件查询时会用const。
  
  mysql> explain sysql> select * from emp where id=3;
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  |  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
  +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  1 row in set, 1 warning (0.03 sec)

  l）system：官方文档原文是：The table has only one row (=system table).This is a special case of the const join type.
  即该表只有一行(=系统表)。这是const关联类型的特例。
  
  6）possible_keys：SQL语句可能会用到的索引。
  
  7）key：SQL语句中实际使用到的索引

  8）key_len：SQL语句中使用到的索引长度，可根据此值推算出有多少个字段使用到了索引。复合索引key_len值越大，表示使用到的索引越多，查询的速度越快。

  9）ref：真正使用索引的字段，如果可能的话，是一个常量。哪些列或常量被用于查找索引列上的值。

  10）rows：显示MySQL认为它执行查询时必须检查的行数，该值越少越好。

  11）filtered：表示存储引擎返回的数据在SERVER层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体的值。

  12）Extra：额外信息
  a）using filesort：文件排序，表示排序字段没有用到索引。
  b）using temporary：分组查询group by 没有使用索引。
  c）using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行。
  如果同时出现using where，表明索引被用来执行索引键值的查找，如果没有出现using where，表明索引只是用来读取数据而非利用索引执行查询。
  d）using where：表明使用了where过滤。
  e）using JOIN buffer：关联字段没有使用到索引。
  f）impossible where：不可能的查询条件。
  g）select tables optimized away：在没有group by子句的情况下，基于索引优化器min/max操作或者对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。