mysql explain详解

mysql

explain即解释sql语句执行计划，使用explain关键字可以模拟优化器执行查询语句，从而知道MySQL是如何处理sql语句的。explain主要用于分析查询语句或表结构的性能瓶颈。

可以阅读官方原文：
Mysql8 Explain output format官方释义

一、explain包含的信息

mysql> explain select * from par_table\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: par_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100337
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

如上所示，explain包含id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra这些信息。

1.1 explain查询结果含义

1.1.1 id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，如果id相同，认为是同一组，执行顺序从上到下。如果id不同，id越大优先级越高，越先被执行

1.1.2 select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个:

• SIMPLE: 简单的select查询，查询中不包含子查询或union查询
• PRIMARY：查询中若包含任何复杂的子部分，则最外层的查询为PRIMARY，也就是最后加载的就是PRIMARY
• SUBQUERY：在select或where列表中包含了子查询，就为被标记为SUBQUERY
• DERIVED：在from列表中包含的子查询会被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，将结果放在临时表中
• UNION：若第二个select出现在union后，则被标记为UNION，若union包含在from子句的子查询中，外层select将被标记为DERIVED
• UNION RESULT：从union表获取结果的select

1.1.3 table

显示sql操作是在哪张表上进行的。

1.1.4 partitions

显示sql操作是在表上的哪个分区上进行的，值为NULL表示表未被分区

1.1.5 type

表示查询所使用的访问类型，type的值主要有八种，该值表示查询语句的性能好坏，从最好到最差依次为：system > const > eq_ref > ref > range > index > ALL。

• system：表只有一行记录（等于系统表），是const的特例类型，平时不会出现， 可忽略。
• const：用于所有where条件都是primary key或者unique key与常量的=匹配，通过一次索引就找到了结果，并且只有一个匹配行。

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

• eq_ref：用于所有where条件都是主键和非空的唯一键的时候，wehere条件的=匹配值可以是其他列，也可以是常量。对于每个索引键，表中只有一条记录与之匹配。

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。
• range: 只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。
• index: 全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。
• ALL: 全表扫描。

1.1.6 possible_keys

显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

1.1.7 key

实际中使用的索引，如为NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的select字段重叠。

1.1.8 key_len

表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

1.1.9 ref

显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

1.1.10 rows

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。rows估算时会忽略limit，见：https://www.percona.com/blog/2006/11/12/when-explain-can-be-misleading/

mysql> explain select * from par_table_pk limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: par_table_pk
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100130
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

如上，表中有10万行数据，explain估计会扫描10万多行数据，但实际上只需要1行数据就返回了。

1.1.11 filtered

百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。

1.1.12 Extra

显示十分重要的额外信息。其取值常见有以下几个：

• .Using filesort：Using filesort表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。通常是非常耗费时间的。
• Using temporary：使用了临时表保存中间结果，常见于排序order by和分组查询group by。
• Using index：表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。如果同时出现了Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

上述列举值并不完整，只是一些常用值，最好是翻阅官方文档。