关于mysql: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 时的输入)：

上面对下面的每一列逐个阐明：
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 的取值如下：

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，发现第一个匹配后，进行为以后的行组合搜寻更多的行；