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时的输出)：

ColumnJSON NameMeaningidselect_idThe SELECT identifierselect_typeNoneThe SELECT typetabletable_nameThe table for the output rowpartitionspartitionsThe matching partitionstypeaccess_typeThe join typepossible_keyspossible_keysThe possible indexes to choosekeykeyThe index actually chosenkey_lenkey_lengthThe length of the chosen keyrefrefThe columns compared to the indexrowsrowsEstimate of rows to be examinedfilteredfilteredPercentage of rows filtered by table conditionExtraNoneAdditional 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 ValueMeaningSIMPLE简单查询，没有使用UNION和子查询PRIMARY最外层的SELECT语句UNIONUNION中第二个或者更后的SELECT语句DEPENDENT UNIONUNION中的第二个或以后的SELECT语句，依赖于外部查询UNION RESULTUNION的结果，因为它不需要参与查询，所以id字段为NULLSUBQUERY除了from字句中包含的子查询外，其他地方出现的子查询都可能是SUBQUERY类型DEPENDENT SUBQUERY子查询中的第一个SELECT语句, 依赖于外部查询, 对于上下文中变量的每一组不同值，子查询只重新计算一次DERIVEDFROM语句中出现的子查询，也叫做派生表, 当FROM语句中包含多个SELECT语句时，第一个SELECT语句的select_type也可能为DERIVEDMATERIALIZED物化的字查询UNCACHEABLE SUBQUERY子查询的结果不能缓存下来，对于外部查询的每一行都需要重新计算UNCACHEABLE UNIONUNION中的第二个或以后的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，发现第一个匹配后，停止为当前的行组合搜索更多的行；