MySQL :: MySQL 8.4 参考手册 :: 10.8.3 扩展 EXPLAIN 输出格式

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.8 理解查询执行计划 / 10.8.3 扩展 EXPLAIN 输出格式

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 97%

10.8.3 扩展 EXPLAIN 输出格式10.8.3 Extended EXPLAIN Output Format

EXPLAIN 语句会生成额外（“扩展”）信息，这些信息不属于 EXPLAIN 的输出内容，但可以通过在 EXPLAIN 之后执行 SHOW WARNINGS 语句来查看。扩展信息适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。The EXPLAIN statement produces extra (“extended”) information that is not part of EXPLAIN output but can be viewed by issuing a SHOW WARNINGS statement following EXPLAIN. Extended information is available for SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

SHOW WARNINGS 输出中的 Message 值显示了优化器如何限定 SELECT 语句中的表名和列名，SELECT 在应用重写和优化规则后的样子，以及有关优化过程的其他可能备注。The Message value in SHOW WARNINGS output displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process.

在 EXPLAIN 之后使用 SHOW WARNINGS 语句显示的扩展信息仅针对 SELECT 语句生成。对于其他可解释的语句（DELETE、INSERT、REPLACE 和 UPDATE），SHOW WARNINGS 会显示空结果。The extended information displayable with a SHOW WARNINGS statement following EXPLAIN is produced only for SELECT statements. SHOW WARNINGS displays an empty result for other explainable statements (DELETE, INSERT, REPLACE, and UPDATE).

这是一个扩展的 EXPLAIN 输出示例：Here is an example of extended EXPLAIN output:

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 WARNINGS 显示的语句可能包含用于提供查询重写或优化器操作信息的特殊标记，所以该语句不一定是有效的 SQL，并且不打算执行。输出还可能包含带有 Message 值的行，这些值提供了额外的、关于优化器所采取操作的非 SQL 解释性说明。Because the statement displayed by SHOW WARNINGS may contain special markers to provide information about query rewriting or optimizer actions, the statement is not necessarily valid SQL and is not intended to be executed. The output may also include rows with Message values that provide additional non-SQL explanatory notes about actions taken by the optimizer.

下面的列表描述了可能出现在 SHOW WARNINGS 显示的扩展输出中的特殊标记：The following list describes special markers that can appear in the extended output displayed by SHOW WARNINGS:

<auto_key>
临时表的自动生成键。An automatically generated key for a temporary table.
<cache>(expr)<cache>(expr)
该表达式（例如一个标量子查询）只执行一次，结果值会被保存在内存中以供后续使用。对于由多个值组成的结果，可能会创建一个临时表，并显示为<temporary table>。The expression (such as a scalar subquery) is executed once and the resulting value is saved in memory for later use. For results consisting of multiple values, a temporary table may be created and <temporary table> is shown instead.
<exists>(query fragment)<exists>(query fragment)
子查询谓词被转换为EXISTS谓词，并且子查询被改造以便可以与EXISTS谓词一起使用。The subquery predicate is converted to an EXISTS predicate and the subquery is transformed so that it can be used together with the EXISTS predicate.
<in_optimizer>(query fragment)<in_optimizer>(query fragment)
这是一个对用户没有意义的内部优化器对象。This is an internal optimizer object with no user significance.
<index_lookup>(query fragment)<index_lookup>(query fragment)
查询片段使用索引查找来处理，以找到符合条件的行。The query fragment is processed using an index lookup to find qualifying rows.
<if>(condition, expr1, expr2)<if>(condition, expr1, expr2)
如果条件为真，则计算 expr1，否则计算 expr2。If the condition is true, evaluate to expr1, otherwise expr2.
<is_not_null_test>(expr)<is_not_null_test>(expr)
测试表达式是否不等于 NULL。A test to verify that the expression does not evaluate to NULL.
<materialize>(query fragment)<materialize>(query fragment)
使用了子查询物化。Subquery materialization is used.
`materialized-subquery`.col_name`materialized-subquery`.col_name
引用内部临时表中用于保存子查询评估结果的列 col_name。A reference to the column col_name in an internal temporary table materialized to hold the result from evaluating a subquery.
<primary_index_lookup>(query fragment)<primary_index_lookup>(query fragment)
查询片段使用主键查找来查找符合条件的行。The query fragment is processed using a primary key lookup to find qualifying rows.
<ref_null_helper>(expr)<ref_null_helper>(expr)
这是一个对用户没有意义的内部优化器对象。This is an internal optimizer object with no user significance.
/* select#N */ select_stmt/* select#N */ select_stmt
SELECT 与非扩展 EXPLAIN 输出中 id 值为 N 的行相关联。The SELECT is associated with the row in non-extended EXPLAIN output that has an id value of N.
outer_tables semi join (inner_tables)outer_tables semi join (inner_tables)
一个半连接操作。inner_tables 显示未被抽取出的表。参见使用半连接转换优化 IN 和 EXISTS 子查询谓词。A semijoin operation. inner_tables shows the tables that were not pulled out. See Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations.
<temporary table><temporary table>
这表示一个内部临时表，用于缓存中间结果。This represents an internal temporary table created to cache an intermediate result.

当某些表是const或system类型时，涉及这些表列的表达式会被优化器提前计算，并不包含在显示的语句中。然而，使用FORMAT=JSON时，某些const表访问会显示为使用常量值的ref访问。When some tables are of const or system type, expressions involving columns from these tables are evaluated early by the optimizer and are not part of the displayed statement. However, with FORMAT=JSON, some const table accesses are displayed as a ref access that uses a const value.

首页上一级下一节