MySQL: 使用explain 优化查询性能

Explain 介绍

为了优化MySQL的SQL语句的执行性能，MySQL提供了explain关键字用于查看SQL的执行计划。
格式如下：

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

DESCRIBE和EXPLAIN语句是同义词。实际上，DESCRIBE关键字更常用于获取有关表结构的信息，而EXPLAIN用于获取查询执行计划（即，解释MySQL将如何执行查询）。

从上面的EXPLAIN的用法可以看出：

• EXPLAIN 可以与 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 一起使用，用于查询相应SQL的执行计划。
• 当EXPLAIN与可解释语句(explainable statement)一起使用时，MySQL显示来自优化器的关于语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括有关如何联接表以及以何种顺序联接表的信息。
• 当EXPLAIN与FOR CONNECTION connect_id 而不是可解释语句一起使用时，它将显示在命名连接中执行的语句的执行计划。
• 对于SELECT语句，EXPLAIN可以使用SHOW WARNINGS 语句显示的其他额外的执行计划信息。
• EXPLAIN对于检查涉及分区表的查询很有用。
• FORMAT选项可用于选择输出格式。TRADITIONAL以表格格式显示输出,默认为TRADITIONAL，JSON格式以JSON格式显示信息。

在EXPLAIN的帮助下，可以看到应该在哪里向表添加索引，以便通过使用索引查找使语句执行得更快，还可以使用EXPLAIN检查优化器是否以最佳顺序连接表。

当EXPLAIN与SELECT语句一起使用时，EXPLAIN的结果以表格的格式显示输出，每个行表示一张表。MYSQL使用循环内嵌的方法解析所有的表的连接，也就意味着MYSQL会先读取第一张表的第一行，然后在第二张表中查找匹配的行，然后是第三张表等。当所有的表格都处理完成之后，MySQL输出所选列并回溯所有表，直到找到一个表，其中有更多匹配行。从该表中读取下一行，并继续处理下一个表。

Explain 的输出

EXPLAIN中的每个输出行提供关于一个表的信息。
EXPLAIN的输出如下(第二列为FORMAT=JSON时的输出)：

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

下面对上面的每一列逐一说明：
id ： 这是查询中SELECT的序列号。如果该行指的是其他行的UNION结果，则该值可以为NULL。在这种情况下，table 列显示一个类似<unionM，N>的值，以指示该行引用id值为M和N的行的并集。

explain select * from small_note.small_note_detail where id = 5 union select * from small_note.small_note_detail where id = 6;

结果如下：

select_type: select_type的取值如下：

select_type Value	Meaning
SIMPLE	简单查询，没有使用UNION和子查询
PRIMARY	最外层的SELECT语句
UNION	UNION中第二个或者更后的SELECT语句
DEPENDENT UNION	UNION中的第二个或以后的SELECT语句，依赖于外部查询
UNION RESULT	UNION的结果，因为它不需要参与查询，所以id字段为NULL
SUBQUERY	除了from字句中包含的子查询外，其他地方出现的子查询都可能是SUBQUERY类型
DEPENDENT SUBQUERY	子查询中的第一个SELECT语句, 依赖于外部查询, 对于上下文中变量的每一组不同值，子查询只重新计算一次
DERIVED	FROM语句中出现的子查询，也叫做派生表, 当FROM语句中包含多个SELECT语句时，第一个SELECT语句的select_type也可能为DERIVED
MATERIALIZED	物化的字查询
UNCACHEABLE SUBQUERY	子查询的结果不能缓存下来，对于外部查询的每一行都需要重新计算
UNCACHEABLE UNION	UNION中的第二个或以后的SELECT语句属于UNCACHEABLE SUBQUERY

DEPENDENT SUBQUERY与UNCACHEABLE SUBQUERY不同。对于DEPENDENT SUBQUERY，对于外部上下文中变量的每一组不同值，子查询只重新计算一次。对于UNCACHEABLE SUBQUERY，将为外部上下文的每一行重新计算子查询。

非SELECT语句的select_type为语句的type, 比如对于DELETE语句而言，其select type 就是DELETE。

table: 表的名称。除了可以是表的名称，这也可以是以下值之一。

<unionM，N>：该行表示id值为M和N的行的并集。

<derived N>：该行引用id值为N的行的派生表结果。例如，派生表可能来自from子句中的子查询。

<subquery N>：该行是指id值为N的行的物化子查询的结果。

partitions : 查询匹配的分区，对于非分区表，该值为NULL。

type：关联类型，决定通过什么方式找到每一行数据。以下按照速度由快到慢。
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。

• system，表中只有一行记录，相当于系统表；
• const，该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以优化器的其余部分可以将此行中列的值视为常量。常量表非常快，因为它们只读取一次。将主键或唯一索引的所有部分与常量值进行比较时，type将是const。
• eq_ref，读取本表中和关联表表中的每行组合成的一行，即只返回一条数据。除了system和const类型之外，这是最好的联接类型。当联接使用索引的所有部分，并且索引是主键或唯一的非空索引时，type为eq_ref。
• ref，将从此表中读取具有匹配索引值的所有行。如果联接仅使用键的最左侧前缀，或者键不是主键或唯一索引（换句话说，如果联接无法基于键值选择单行），则使用ref。如果使用的键只匹配几行，则这是一种良好的联接类型。ref可用于使用=或<=>运算符进行比较的索引列。
• fulltext, 使用FULLTEXT索引
• ref_or_null, 和ref类似，但是还要进行一次查询找到NULL的数据。
• index_merge, 对于单表查询（无法跨表合并）用到了多个索引的情况，每个索引都可能返回一个结果，Mysql会对结果进行取并集、交集，这就是索引合并了。
• unique_subquery, 对于in的子查询中使用了唯一索引，有的时候使用unique_subquery而不是eq_ref
• index_subquery, 和unique_subquery类似，只是针对的是非唯一索引。
• range，只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
• index，只遍历索引树；
• all，全表扫描；

possible_keys: 表示MySQL可以从中选择查找此表中的行的索引。请注意，此列完全独立于EXPLAIN输出中显示的表格顺序。这意味着possible_keys中的一些键在实际生成的表顺序中可能不可用。

key: 表示MySQL实际决定使用的键（索引）。如果MySQL决定使用possible_keys中的某个索引来查找行，则该索引将作为键值列出。key也可能是possible_keys中不存在的索引，如果所有possible_keys都不适合查找行，但查询选择的所有列都是其他索引的列，则可能发生这种情况。也就是说，命名索引覆盖选定的列，因此尽管它不用于确定要检索的行，但索引扫描比数据行扫描更有效。

key_len: key_len列表示MySQL决定使用的key的长度(字节)，char为4个字节，允许为NULL需要额外一个字节，不定长还需要额外2个字节存储长度。如果key列表示NULL，则key_len列也表示NULL。

ref: 显示该表的索引字段关联了哪张表的哪个字段；

rows: 表示MySQL认为执行查询必须检查的行数。

filtered：返回结果的行数占读取行数的百分比，值越大越好；

extra：包含不适合在其他列中显示但十分重要的额外信息。常见的值如下：

• using filesort，MySQL会对数据使用一个外部索引排序，而不是按照表内索引顺序进行读取，若出现改值，则应优化SQL语句；
• using temporary，使用临时表缓存中间结果，比如，MySQL在对查询结果排序时使用临时表，常见于order by和group by，若出现该值，则应优化SQL；
• using index，仅使用索引树中的信息从表中检索列信息，而无需进行额外的查找以读取实际行。 当查询仅使用属于单个索引的列时，可以使用此策略。表示select操作使用了覆盖索引，避免了访问表的数据行；
• Using index condition, 使用索引下推，索引下推简单来说就是加上了条件筛选，需要回表，但是减少了回表的操作。
• using where，where子句用于限制哪一行；
• using join buffer，使用连接缓存；
• distinct，发现第一个匹配后，停止为当前的行组合搜索更多的行；