MySQL: 使用explain 优化查询性能

编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《MySQL: 使用explain 优化查询性能》，文章讲解的知识点主要包括MySQL、explain，如果你对数据库方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

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 列显示一个类似的值，以指示该行引用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: 表的名称。除了可以是表的名称，这也可以是以下值之一。

：该行表示id值为M和N的行的并集。

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

：该行是指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，发现第一个匹配后，停止为当前的行组合搜索更多的行；

今天关于《MySQL: 使用explain 优化查询性能》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！