MySQL 5.7-8.8.3 Extended EXPLAIN Output Format

For SELECT statements, 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.

对于SELECT语句，EXPLAIN语句产生额外的(“扩展”)信息，这些信息不是EXPLAIN输出的一部分，但可以通过在EXPLAIN之后发出SHOW WARNINGS语句来查看。

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.

SHOW WARNINGS输出中的Message值显示了优化器如何在SELECT语句中限定表和列名，在应用重写和优化规则之后，SELECT是什么样子，以及关于优化过程的其他注意事项。

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后面的SHOW WARNINGS语句可显示的扩展信息仅为SELECT语句生成。SHOW WARNINGS显示其他可解释语句(DELETE、INSERT、REPLACE和UPDATE)的空结果。

Note

In older MySQL releases, extended information was produced using EXPLAIN EXTENDED. That syntax is still recognized for backward compatibility but extended output is now enabled by default, so the EXTENDED keyword is superfluous and deprecated. Its use results in a warning; expect it to be removed from EXPLAIN syntax in a future MySQL release.

在旧的MySQL版本中，扩展信息是使用EXPLAIN extended生成的。该语法仍然被认为是向后兼容的，但扩展输出现在在默认情况下是启用的，因此extended关键字是多余的，已弃用。它的使用导致警告;希望在未来的MySQL版本中从EXPLAIN语法中删除它。

Here is an example of extended EXPLAIN output:

 

 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显示的语句可能包含特殊标记，以提供有关查询重写或优化器操作的信息，因此该语句不一定是有效的SQL，也不打算执行。输出还可能包括具有Message值的行，这些值提供关于优化器所采取操作的额外非sql解释性说明。

The following list describes special markers that can appear in the extended output displayed by SHOW WARNINGS:

下面的列表描述了可以出现在SHOW WARNINGS扩展输出中的特殊标记:

<auto_key>

An automatically generated key for a temporary table.

为临时表自动生成的键。

<cache>(expr)

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 you might see <temporary table> instead.

表达式(例如标量子查询)执行一次，结果值保存在内存中供以后使用。对于由多个值组成的结果，可能会创建一个临时表，您可能会看到<临时表>。

<exists>(query fragment)

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.

将子查询谓词转换为EXISTS谓词，并对子查询进行转换，以便它可以与EXISTS谓词一起使用。

<in_optimizer>(query fragment)

This is an internal optimizer object with no user significance.

这是一个没有用户意义的内部优化器对象。

<index_lookup>(query fragment)

The query fragment is processed using an index lookup to find qualifying rows.

查询片段使用索引查找来查找符合条件的行。

<if>(condition, expr1, expr2)

If the condition is true, evaluate to expr1, otherwise expr2.

如果条件为真，则计算expr1，否则计算expr2。

<is_not_null_test>(expr)

A test to verify that the expression does not evaluate to NULL.

验证表达式不为NULL的测试。

<materialize>(query fragment)

Subquery materialization is used.

使用子查询物化。

`materialized-subquery`.col_name

A reference to the column col_name in an internal temporary table materialized to hold the result from evaluating a subquery.

内部临时表中对列col_name的引用，实体化用于保存子查询的求值结果。

<primary_index_lookup>(query fragment)

The query fragment is processed using a primary key lookup to find qualifying rows.

查询片段使用主键查找来查找符合条件的行。

<ref_null_helper>(expr)

This is an internal optimizer object with no user significance.

这是一个没有用户意义的内部优化器对象。

/* select#N */ select_stmt

The SELECT is associated with the row in non-extended EXPLAIN output that has an id value of N.

SELECT与id值为N的非扩展EXPLAIN输出中的行相关联。

outer_tables semi join (inner_tables)

A semijoin operation. inner_tables shows the tables that were not pulled out. See Section 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”.semijoin操作。Inner_tables显示没有被拉出的表。

<temporary table>

This represents an internal temporary table created to cache an intermediate result.

这表示为缓存中间结果而创建的内部临时表。

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.

当一些表是const或系统类型时，包含这些表列的表达式会由优化器提前计算，而不是显示语句的一部分。然而，使用FORMAT=JSON，一些const表访问显示为使用const值的ref访问。