乐趣区

关于mysql:MySQL-使用explain-优化查询性能

jiezi

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