全网最全-MySQL-EXPLAIN-完全解读

EXPLAIN 作为 MySQL 的性能剖析神器，读懂其后果是很有必要的，然而我在各种搜索引擎上居然找不到特地残缺的解读。都是只有重点，没有细节（例如 type 的取值不全、Extra 不足残缺的介绍等）。

所以，我肝了将近一个星期，整顿了一下。这应该是全网最全面、最粗疏的 EXPLAIN 解读文章了，上面是全文。

文章比拟长，倡议珍藏。

TIPS

本文基于 MySQL 8.0 编写，实践反对 MySQL 5.0 及更高版本。

EXPLAIN 应用

explain 可用来剖析 SQL 的执行打算。格局如下：

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

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

{EXPLAIN | DESCRIBE | DESC} ANALYZE select_statement    

explain_type: {FORMAT = format_name}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

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

示例：

EXPLAIN format = TRADITIONAL json SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

后果输入展现：

后果解读

id

该语句的惟一标识。如果 explain 的后果包含多个 id 值，则数字越大越先执行；而对于雷同 id 的行，则示意从上往下顺次执行。

select_type

查问类型，有如下几种取值：

table

示意以后这一行正在拜访哪张表，如果 SQL 定义了别名，则展现表的别名

partitions

以后查问匹配记录的分区。对于未分区的表，返回 null

type

连贯类型，有如下几种取值，性能从好到坏排序 如下：

1 system：该表只有一行（相当于零碎表），system 是 const 类型的特例

2 const：针对主键或惟一索引的等值查问扫描, 最多只返回一行数据. const 查问速度十分快, 因为它仅仅读取一次即可

3 eq_ref：当应用了索引的全副组成部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 才会应用该类型，性能仅次于 system 及 const。

-- 多表关联查问，单行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

-- 多表关联查问，联结索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

4 ref：当满足索引的最左前缀规定，或者索引不是主键也不是惟一索引时才会产生。如果应用的索引只会匹配到大量的行，性能也是不错的。

-- 依据索引（非主键，非惟一索引），匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;

-- 多表关联查问，单个索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

-- 多表关联查问，联结索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

TIPS

最左前缀准则，指的是索引依照最左优先的形式匹配索引。比方创立了一个组合索引(column1, column2, column3)，那么，如果查问条件是：

• WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都能够应用该索引；
• WHERE column1 = 2、WHERE column1 = 1 AND column3 = 3 就无奈匹配该索引。

5 fulltext：全文索引

6 ref_or_null：该类型相似于 ref，然而 MySQL 会额定搜寻哪些行蕴含了 NULL。这种类型常见于解析子查问

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

7 index_merge：此类型示意应用了索引合并优化，示意一个查问外面用到了多个索引

8 unique_subquery：该类型和 eq_ref 相似，然而应用了 IN 查问，且子查问是主键或者惟一索引。例如：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

9 index_subquery：和 unique_subquery 相似，只是子查问应用的是非惟一索引

value IN (SELECT key_column FROM single_table WHERE some_expr)

10 range：范畴扫描，示意检索了指定范畴的行，次要用于有限度的索引扫描。比拟常见的范畴扫描是带有 BETWEEN 子句或 WHERE 子句里有 >、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

11 index：全索引扫描，和 ALL 相似，只不过 index 是全盘扫描了索引的数据。当查问仅应用索引中的一部分列时，可应用此类型。有两种场景会触发：

• 如果索引是查问的笼罩索引，并且索引查问的数据就能够满足查问中所需的所有数据，则只扫描索引树。此时，explain 的 Extra 列的后果是 Using index。index 通常比 ALL 快，因为索引的大小通常小于表数据。
• 按索引的程序来查找数据行，执行了全表扫描。此时，explain 的 Extra 列的后果不会呈现 Uses index。
• ALL：全表扫描，性能最差。

possible_keys

展现以后查问能够应用哪些索引，这一列的数据是在优化过程的晚期创立的，因而有些索引可能对于后续优化过程是没用的。

key

示意 MySQL 理论抉择的索引

key_len

索引应用的字节数。因为存储格局，当字段容许为 NULL 时，key_len 比不容许为空时大 1 字节。

key_len 计算公式：https://www.cnblogs.com/gomys…

ref

示意将哪个字段或常量和 key 列所应用的字段进行比拟。

如果 ref 是一个函数，则应用的值是函数的后果。要想查看是哪个函数，可在 EXPLAIN 语句之后紧跟一个 SHOW WARNING 语句。

rows

MySQL 估算会扫描的行数，数值越小越好。

filtered

示意合乎查问条件的数据百分比，最大 100。用 rows × filtered 可取得和下一张表连贯的行数。例如 rows = 1000，filtered = 50%，则和下一张表连贯的行数是 500。

TIPS

在 MySQL 5.7 之前，想要显示此字段需应用 explain extended 命令；

MySQL.5.7 及更高版本，explain 默认就会展现 filtered

Extra

展现无关本次查问的附加信息，取值如下：

1 Child of ‘table’ pushed join@1

此值只会在 NDB Cluster 下呈现。

2 const row not found

例如查问语句 SELECT … FROM tbl_name，而表是空的

3 Deleting all rows

对于 DELETE 语句，某些引擎（例如 MyISAM）反对以一种简略而疾速的形式删除所有的数据，如果应用了这种优化，则显示此值

4 Distinct

查找 distinct 值，当找到第一个匹配的行后，将进行为以后行组合搜寻更多行

5 FirstMatch(tbl_name)

以后应用了半连贯 FirstMatch 策略，详见 https://mariadb.com/kb/en/fir…

6 Full scan on NULL key

子查问中的一种优化形式，在无奈通过索引拜访 null 值的时候应用

7 Impossible HAVING

HAVING 子句始终为 false，不会命中任何行

8 Impossible WHERE

WHERE 子句始终为 false，不会命中任何行

9 Impossible WHERE noticed after reading const tables

MySQL 曾经读取了所有 const（或 system）表，并发现 WHERE 子句始终为 false

10 LooseScan(m..n)

以后应用了半连贯 LooseScan 策略，详见 https://mariadb.com/kb/en/loo…

11 No matching min/max row

没有任何能满足例如 SELECT MIN(…) FROM … WHERE condition 中的 condition 的行

12 no matching row in const table

对于关联查问，存在一个空表，或者没有行可能满足惟一索引条件

13 No matching rows after partition pruning

对于 DELETE 或 UPDATE 语句，优化器在 partition pruning（分区修剪）之后，找不到要 delete 或 update 的内容

14 No tables used

当此查问没有 FROM 子句或领有 FROM DUAL 子句时呈现。例如：explain select 1

15 Not exists

MySQL 能对 LEFT JOIN 优化，在找到合乎 LEFT JOIN 的行后，不会为上一行组合中查看此表中的更多行。例如：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
  WHERE t2.id IS NULL;

假如 t2.id 定义成了NOT NULL，此时，MySQL 会扫描 t1，并应用 t1.id 的值查找 t2 中的行。如果 MySQL 在 t2 中找到一个匹配的行，它会晓得 t2.id 永远不会为 NULL，并且不会扫描 t2 中具备雷同 id 值的其余行。也就是说，对于 t1 中的每一行，MySQL 只须要在 t2 中只执行一次查找，而不思考在 t2 中理论匹配的行数。

在 MySQL 8.0.17 及更高版本中，如果呈现此提醒，还可示意形如 NOT IN (subquery) 或 NOT EXISTS (subquery) 的 WHERE 条件曾经在外部转换为反连贯。这将删除子查问并将其表放入最顶层的查问打算中，从而改良查问的开销。通过合并半连贯和反联接，优化器能够更加自在地对执行打算中的表从新排序，在某些状况下，可让查问提速。你能够通过在 EXPLAIN 语句后紧跟一个 SHOW WARNING 语句，并剖析后果中的 Message 列，从而查看何时对该查问执行了反联接转换。

Note

两表关联只返回主表的数据，并且只返回主表与子表没关联上的数据，这种连贯就叫反连贯

16 Plan isn’t ready yet

应用了 EXPLAIN FOR CONNECTION，当优化器尚未实现为在指定连贯中为执行的语句创立执行打算时，就会呈现此值。

17 Range checked for each record (index map: N)

MySQL 没有找到适合的索引去应用，然而去查看是否能够应用 range 或 index_merge 来检索行时，会呈现此提醒。index map N 索引的编号从 1 开始，依照与表的 SHOW INDEX 所示雷同的程序。索引映射值 N 是批示哪些索引是候选的位掩码值。例如 0x19（二进制 11001）的值意味着将思考索引 1、4 和 5。

示例：上面例子中，name 是 varchar 类型，然而条件给出整数型，波及到隐式转换。
图中 t2 也没有用到索引，是因为查问之前我将 t2 中 name 字段排序规定改为 utf8_bin 导致的链接字段排序规定不匹配。

explain select a.* from t1 a left  join t2 b
on t1.name = t2.name
where t2.name = 2;

后果：

18 Recursive

呈现了递归查问。详见“WITH (Common Table Expressions)”

19 Rematerialize

用得很少，应用相似如下 SQL 时，会展现 Rematerialize

SELECT
  ...
FROM
  t,
  LATERAL (derived table that refers to t) AS dt
...

20 Scanned N databases

示意在解决 INFORMATION_SCHEMA 表的查问时，扫描了几个目录，N 的取值能够是 0，1 或者 all。详见“Optimizing INFORMATION_SCHEMA Queries”

21 Select tables optimized away

优化器确定：①最多返回 1 行；②要产生该行的数据，要读取一组确定的行，时会呈现此提醒。个别在用某些聚合函数拜访存在索引的某个字段时，优化器会通过索引间接一次定位到所须要的数据行实现整个查问时展现，例如上面这条 SQL。

explain
select min(id)
from t1;

22 Skip_open_table，Open_frm_only，Open_full_table

这些值示意实用于 INFORMATION_SCHEMA 表查问的文件关上优化；

23 Skip_open_table：无需关上表文件，信息曾经通过扫描数据字典取得

24 Open_frm_only：仅须要读取数据字典以获取表信息

25 Open_full_table：未优化的信息查找。表信息必须从数据字典以及表文件中读取

26 Start temporary, End temporary

示意长期表应用 Duplicate Weedout 策略，详见 https://mariadb.com/kb/en/dup…

27 unique row not found

对于形如 SELECT … FROM tbl_name 的查问，但没有行可能满足惟一索引或主键查问的条件

28 Using filesort

当 Query 中蕴含 ORDER BY 操作，而且无奈利用索引实现排序操作的时候，MySQL Query Optimizer 不得不抉择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain 不会显示的通知客户端用哪种排序。官网解释：“MySQL 须要额定的一次传递，以找出如何按排序程序检索行。通过依据联接类型浏览所有行并为所有匹配 WHERE 子句的行保留排序关键字和行的指针来实现排序。而后关键字被排序，并按排序程序检索行”

29 Using index

仅应用索引树中的信息从表中检索列信息，而不用进行其余查找以读取理论行。当查问仅应用属于单个索引的列时，能够应用此策略。例如：

explain SELECT id FROM t

30 Using index condition

示意先按条件过滤索引，过滤完索引后找到所有合乎索引条件的数据行，随后用 WHERE 子句中的其余条件去过滤这些数据行。通过这种形式，除非有必要，否则索引信息将能够提早“下推”读取整个行的数据。详见“Index Condition Pushdown Optimization”。例如：

TIPS

• MySQL 分成了 Server 层和引擎层，下推指的是将申请交给引擎层解决。

• 了解这个性能，可创立所以 INDEX (zipcode, lastname, firstname)，并别离用如下指令，

  SET optimizer_switch = 'index_condition_pushdown=off'; 
  SET optimizer_switch = 'index_condition_pushdown=on';

  开或者敞开索引条件下推，并比照：

  explain SELECT * FROM people
    WHERE zipcode='95054'
    AND lastname LIKE '%etrunia%'
    AND address LIKE '%Main Street%';

  的执行后果。

• index condition pushdown 从 MySQL 5.6 开始反对，是 MySQL 针对特定场景的优化机制，感兴趣的能够看下 https://blog.51cto.com/lee90/…

31 Using index for group-by

数据拜访和 Using index 一样，所需数据只须要读取索引，当 Query 中应用 GROUP BY 或 DISTINCT 子句时，如果分组字段也在索引中，Extra 中的信息就会是 Using index for group-by。详见“GROUP BY Optimization”

-- name 字段有索引
explain SELECT name FROM t1 group by name

32 Using index for skip scan

示意应用了 Skip Scan。详见 Skip Scan Range Access Method

33 Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

应用 Block Nested Loop 或 Batched Key Access 算法进步 join 的性能。详见 https://www.cnblogs.com/chenp…

34 Using MRR

应用了 Multi-Range Read 优化策略。详见“Multi-Range Read Optimization”

35 Using sort_union(…), Using union(…), Using intersect(…)

这些批示索引扫描如何合并为 index_merge 连贯类型。详见“Index Merge Optimization”。

36 Using temporary

为了解决该查问，MySQL 须要创立一个长期表来保留后果。如果查问蕴含不同列的 GROUP BY 和 ORDER BY 子句，通常会产生这种状况。

-- name 无索引
explain SELECT name FROM t1 group by name

37 Using where

如果咱们不是读取表的所有数据，或者不是仅仅通过索引就能够获取所有须要的数据，则会呈现 using where 信息

explain SELECT * FROM t1 where id > 5

38 Using where with pushed condition

仅用于 NDB

39 Zero limit

该查问有一个 limit 0 子句，不能抉择任何行

explain SELECT name FROM resource_template limit 0

扩大的 EXPLAIN

EXPLAIN 可产生额定的扩大信息，可通过在 EXPLAIN 语句后紧跟一条 SHOW WARNING 语句查看扩大信息。

TIPS

• 在 MySQL 8.0.12 及更高版本，扩大信息可用于 SELECT、DELETE、INSERT、REPLACE、UPDATE 语句；在 MySQL 8.0.12 之前，扩大信息仅实用于 SELECT 语句；
• 在 MySQL 5.6 及更低版本，需应用 EXPLAIN EXTENDED xxx 语句；而从 MySQL 5.7 开始，无需增加 EXTENDED 关键词。

应用示例：

mysql> EXPLAIN
       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
         type: index
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
         <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in
         ( <materialize> (/* select#2 */ select `test`.`t2`.`a`
         from `test`.`t2` where 1 having 1 ),
         <primary_index_lookup>(`test`.`t1`.`a` in
         <temporary table> on <auto_key>
         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
         IN (SELECT t2.a FROM t2)` from `test`.`t1`
1 row in set (0.00 sec)

因为 SHOW WARNING 的后果并不一定是一个无效 SQL，也不肯定可能执行（因为外面蕴含了很多非凡标记）。非凡标记取值如下：

1 <auto_key>

主动生成的长期表 key

2 <cache>(expr)

表达式（例如标量子查问）执行了一次，并且将值保留在了内存中以备当前应用。对于包含多个值的后果，可能会创立长期表，你将会看到 <temporary table> 的字样

3 <exists>(query fragment)

子查问被转换为 EXISTS

4 <in_optimizer>(query fragment)

这是一个外部优化器对象，对用户没有任何意义

5 <index_lookup>(query fragment)

应用索引查找来解决查问片段，从而找到合格的行

6 <if>(condition, expr1, expr2)

如果条件是 true，则取 expr1，否则取 expr2

7 <is_not_null_test>(expr)

验证表达式不为 NULL 的测试

8 <materialize>(query fragment)

应用子查问实现

9 materialized-subquery.col_name

在外部物化长期表中对 col_name 的援用，以保留子查问的后果

10 <primary_index_lookup>(query fragment)

应用主键来解决查问片段，从而找到合格的行

11 <ref_null_helper>(expr)

这是一个外部优化器对象，对用户没有任何意义

12 /* select#N */ select_stmt

SELECT 与非扩大的 EXPLAIN 输入中 id= N 的那行关联

13 outer_tables semi join (inner_tables)

半连贯操作。inner_tables 展现未拉出的表。详见“Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”

14 <temporary table>

示意创立了外部长期表而缓存两头后果

当某些表是 const 或 system 类型时，这些表中的列所波及的表达式将由优化器尽早评估，并且不属于所显示语句的一部分。然而，当应用 FORMAT=JSON 时，某些 const 表的拜访将显示为 ref。

预计查问性能

少数状况下，你能够通过计算磁盘的搜寻次数来估算查问性能。对于比拟小的表，通常能够在一次磁盘搜寻中找到行（因为索引可能曾经被缓存了），而对于更大的表，你能够应用 B -tree 索引进行估算：你须要进行多少次查找能力找到行：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1

在 MySQL 中，index_block_length 通常是 1024 字节，数据指针个别是 4 字节。比方说，有一个 500,000 的表，key 是 3 字节，那么依据计算公式 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次搜寻。

该索引将须要 500,000 7 3/2 = 5.2MB 的存储空间（假如典型的索引缓存的填充率是 2 /3），因而你能够在内存中寄存更多索引，可能只有一到两个调用就能够找到想要的行了。

然而，对于写操作，你须要四个搜寻申请来查找在何处搁置新的索引值，而后通常须要 2 次搜寻来更新索引并写入行。

后面的探讨并不意味着你的利用性能会因为 log N 而迟缓降落。只有内容被 OS 或 MySQL 服务器缓存，随着表的变大，只会略微变慢。在数据量变得太大而无奈缓存后，将会变慢很多，直到你的应用程序受到磁盘搜寻束缚（依照 log N 增长）。为了防止这种状况，能够依据数据的增长而减少 key 的。对于 MyISAM 表，key 的缓存大小由名为 key_buffer_size 的零碎变量管制，详见 Section 5.1.1,“Configuring the Server”

参考文档

• EXPLAIN Output Format
• EXPLAIN Statement
• Extended EXPLAIN Output Format
• Estimating Query Performance
• MySQL 中 explain 执行打算中额定信息字段 (Extra) 详解
• explain 参数详解
• What does eq_ref and ref types mean in MySQL explain
• 面试官：不会看 Explain 执行打算，简历敢写 SQL 优化？

本文首发

http://www.itmuch.com/mysql/e… 转载请注明出处。

本文由博客一文多发平台 OpenWrite 公布！