10.8 Understanding the Query Execution Plan

10.8 Understanding the Query Execution Plan

• 10.8.1 Optimizing Queries with EXPLAIN
• 10.8.2 EXPLAIN Output Format
• 10.8.3 Extended EXPLAIN Output Format
• 10.8.4 Obtaining Execution Plan Information for a Named Connection
• 10.8.5 Estimating Query Performance

Depending on the details of your tables, columns, indexes, and the conditions in your WHERE clause, the MySQL optimizer considers many techniques to efficiently perform the lookups involved in an SQL query. A query on a huge table can be performed without reading all the rows; a join involving several tables can be performed without comparing every combination of rows. The set of operations that the optimizer chooses to perform the most efficient query is called the “query execution plan”, also known as the EXPLAIN plan. Your goals are to recognize the aspects of the EXPLAIN plan that indicate a query is optimized well, and to learn the SQL syntax and indexing techniques to improve the plan if you see some inefficient operations.

根据表、列、索引的详细信息以及WHERE子句中的条件，MySQL优化器考虑了许多技术来有效地执行SQL查询中涉及的查找。可以在不读取所有行的情况下执行对大型表的查询；可以在不比较行的每个组合的情况下执行涉及多个表的连接。优化器选择执行最有效查询的一组操作称为“查询执行计划”，也称为EXPLAIN计划。您的目标是识别EXPLAIN计划中表明查询优化良好的方面，并学习SQL语法和索引技术，以便在看到一些低效操作时改进计划。

10.8.1 Optimizing Queries with EXPLAIN

使用EXPLAIN优化查询

The EXPLAIN statement provides information about how MySQL executes statements:

• EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
• When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. For information about using EXPLAIN to obtain execution plan information, see Section 10.8.2, “EXPLAIN Output Format”.
• When EXPLAIN is used with FOR CONNECTION connection_id rather than an explainable statement, it displays the execution plan for the statement executing in the named connection. See Section 10.8.4, “Obtaining Execution Plan Information for a Named Connection”.
• For SELECT statements, EXPLAIN produces additional execution plan information that can be displayed using SHOW WARNINGS. See Section 10.8.3, “Extended EXPLAIN Output Format”.
• EXPLAIN is useful for examining queries involving partitioned tables. See Section 26.3.5, “Obtaining Information About Partitions”.
• The FORMAT option can be used to select the output format. TRADITIONAL presents the output in tabular format. This is the default if no FORMAT option is present. JSON format displays the information in JSON format.

EXPLAIN语句提供有关MySQL如何执行语句的信息：

• EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。
• 当EXPLAIN与可解释语句一起使用时，MySQL会显示优化器提供的关于语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括有关表如何连接以及连接顺序的信息。有关使用EXPLAIN获取执行计划信息的信息，请参阅第10.8.2节“EXPLAIN输出格式”。
• 当EXPLAIN与FOR CONNECTION CONNECTION_id而不是可解释语句一起使用时，它会显示在指定连接中执行的语句的执行计划。见第10.8.4节“获取命名连接的执行计划信息”。
• 对于SELECT语句，EXPLAIN会生成其他执行计划信息，这些信息可以使用SHOW WARNINGS显示。请参阅第10.8.3节“扩展解释输出格式”。
• EXPLAIN对于检查涉及分区表的查询非常有用。请参阅第26.3.5节“获取分区信息”。
• FORMAT选项可用于选择输出格式。TRADITIONAL以表格形式呈现输出。如果没有FORMAT选项，则这是默认值。JSON格式以JSON格式显示信息。

With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order. To give a hint to the optimizer to use a join order corresponding to the order in which the tables are named in a SELECT statement, begin the statement with SELECT STRAIGHT_JOIN rather than just SELECT. (See Section 15.2.13, “SELECT Statement”.) However, STRAIGHT_JOIN may prevent indexes from being used because it disables semijoin transformations. See Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”.

在EXPLAIN的帮助下，您可以看到应该在哪里向表添加索引，以便通过使用索引查找行来更快地执行语句。您还可以使用EXPLAIN检查优化器是否以最佳顺序连接表。为了提示优化器使用与SELECT语句中表的命名顺序相对应的连接顺序，请以SELECT STRIGHT_join而不仅仅是SELECT开始该语句。（请参阅第15.2.13节“SELECT语句”。）但是，STRIGHT_JOIN可能会阻止使用索引，因为它禁用了半连接转换。请参阅第10.2.2.1节，“使用半连接转换优化IN和EXISTS子查询谓词”。

The optimizer trace may sometimes provide information complementary to that of EXPLAIN. However, the optimizer trace format and content are subject to change between versions. For details, see MySQL Internals: Tracing the Optimizer.

优化器跟踪有时可以提供与EXPLAIN互补的信息。但是，优化器跟踪格式和内容可能会在版本之间发生变化。有关详细信息，请参阅MySQL内部：跟踪优化器。

If you have a problem with indexes not being used when you believe that they should be, run ANALYZE TABLE to update table statistics, such as cardinality of keys, that can affect the choices the optimizer makes. See Section 15.7.3.1, “ANALYZE TABLE Statement”.

如果您在认为应该使用索引时遇到索引未被使用的问题，请运行ANALYZE TABLE来更新表统计信息，例如键的基数，这可能会影响优化器的选择。见第15.7.3.1节“分析表声明”。

Note

EXPLAIN can also be used to obtain information about the columns in a table. EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name and SHOW COLUMNS FROM tbl_name. For more information, see Section 15.8.1, “DESCRIBE Statement”, and Section 15.7.7.5, “SHOW COLUMNS Statement”.

EXPLAIN还可以用于获取表中列的信息。解释tbl_name与描述tbl_name和显示tbl_name中的列是同义词。有关更多信息，请参阅第15.8.1节“描述声明”和第15.7.7.5节“显示列声明”。

10.8.2 EXPLAIN Output Format

The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

EXPLAIN语句提供有关MySQL如何执行语句的信息。EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。

EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. This means that MySQL reads a row from the first table, then finds a matching row in the second table, and then in the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

EXPLAIN为SELECT语句中使用的每个表返回一行信息。它按照MySQL在处理语句时读取表的顺序在输出中列出表。这意味着MySQL从第一个表中读取一行，然后在第二个表中找到一个匹配的行，然后再在第三个表中查找，以此类推。当处理完所有表后，MySQL会输出所选的列，并在表列表中回溯，直到找到一个有更多匹配行的表。从该表中读取下一行，并继续处理下一个表。

Note

MySQL Workbench has a Visual Explain capability that provides a visual representation of EXPLAIN output. See Tutorial: Using Explain to Improve Query Performance.

MySQL Workbench具有可视化解释功能，可提供Explain输出的可视化表示。请参阅教程：使用Explain提高查询性能。

• EXPLAIN Output Columns
• EXPLAIN Join Types
• EXPLAIN Extra Information
• EXPLAIN Output Interpretation

EXPLAIN Output Columns

This section describes the output columns produced by EXPLAIN. Later sections provide additional information about the type and Extra columns.

本节介绍EXPLAIN生成的输出列。后面的部分提供了有关类型和额外列的更多信息。

Each output row from EXPLAIN provides information about one table. Each row contains the values summarized in Table 10.1, “EXPLAIN Output Columns”, and described in more detail following the table. Column names are shown in the table's first column; the second column provides the equivalent property name shown in the output when FORMAT=JSON is used.

EXPLAIN的每一行输出都提供了关于一个表的信息。每一行都包含表10.1“EXPLAIN输出列”中总结的值，并在表后进行了更详细的描述。列名显示在表的第一列中；当使用FORMAT=JSON时，第二列提供输出中显示的等效属性名。

Table 10.1 EXPLAIN Output Columns

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

Note

JSON properties which are NULL are not displayed in JSON-formatted EXPLAIN output.

JSON格式的EXPLAIN输出中不显示NULL的JSON属性。

• id (JSON name: select_id)

  The SELECT identifier. This is the sequential number of the SELECT within the query. The value can be NULL if the row refers to the union result of other rows. In this case, the table column shows a value like <unionM,N> to indicate that the row refers to the union of the rows with id values of M and N.

  SELECT标识符。这是查询中SELECT的序列号。如果该行引用其他行的并集结果，则该值可以为NULL。在这种情况下，表列显示了一个类似于<unionM，N>的值，表示该行是指id值为M和N的行的并集。

• select_type (JSON name: none)

  The type of SELECT, which can be any of those shown in the following table. A JSON-formatted EXPLAIN exposes the SELECT type as a property of a query_block, unless it is SIMPLE or PRIMARY. The JSON names (where applicable) are also shown in the table.

  SELECT的类型，可以是下表所示的任何类型。JSON格式的EXPLAIN将SELECT类型作为query_block的属性公开，除非它是SIMPLE或PRIMARY。JSON名称（如适用）也显示在表中。

  select_type Value	JSON Name	Meaning
  SIMPLE	None	Simple SELECT (not using UNION or subqueries)
  PRIMARY	None	Outermost SELECT
  UNION	None	Second or later SELECT statement in a UNION
  DEPENDENT UNION	dependent (true)	Second or later SELECT statement in a UNION, dependent on outer query
  UNION RESULT	union_result	Result of a UNION.
  SUBQUERY	None	First SELECT in subquery
  DEPENDENT SUBQUERY	dependent (true)	First SELECT in subquery, dependent on outer query
  DERIVED	None	Derived table
  DEPENDENT DERIVED	dependent (true)	Derived table dependent on another table
  MATERIALIZED	materialized_from_subquery	Materialized subquery
  UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
  UNCACHEABLE UNION	cacheable (false)	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

  DEPENDENT typically signifies the use of a correlated subquery. See Section 15.2.15.7, “Correlated Subqueries”.

  DEPENDENT通常表示使用相关子查询。见第15.2.15.7节“相关子查询”。

  DEPENDENT SUBQUERY evaluation differs from UNCACHEABLE SUBQUERY evaluation. For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context.

  从属子查询评估不同于无法实现的子查询评估。对于DEPENDENT SUBQUERY，对于来自其外部上下文的变量的每组不同值，子查询只会重新计算一次。对于UNCACHEABLE SUBQUERY，会为外部上下文的每一行重新评估子查询。

  When you specify FORMAT=JSON with EXPLAIN, the output has no single property directly equivalent to select_type; the query_block property corresponds to a given SELECT. Properties equivalent to most of the SELECT subquery types just shown are available (an example being materialized_from_subquery for MATERIALIZED), and are displayed when appropriate. There are no JSON equivalents for SIMPLE or PRIMARY.

  当您使用EXPLAIN指定FORMAT=JSON时，输出没有直接等效于select_type的单个属性；query_block属性对应于给定的SELECT。与刚才显示的大多数SELECT子查询类型等效的属性是可用的（一个例子是materialized_from_subquery for materialized），并在适当的时候显示。SIMPLE或PRIMARY没有JSON等价物。

  The select_type value for non-SELECT statements displays the statement type for affected tables. For example, select_type is DELETE for DELETE statements.

  非select语句的select_type值显示受影响表的语句类型。例如，对于DELETE语句，select_type是DELETE。

• table (JSON name: table_name)

  The name of the table to which the row of output refers. This can also be one of the following values:

  • <unionM,N>: The row refers to the union of the rows with id values of M and N.

  • <derivedN>: The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.

  • <subqueryN>: The row refers to the result of a materialized subquery for the row with an id value of N. See Section 10.2.2.2, “Optimizing Subqueries with Materialization”.

  输出行所引用的表的名称。这也可以是以下值之一：

  • <unionM，N>：该行是指id值为M和N的行的并集。
  • <derivedN>：该行引用id值为N的行的派生表结果。例如，派生表可能来自from子句中的子查询。
  • <subqueryN>：该行是指id值为N的行的物化子查询的结果。请参阅第10.2.2.2节“使用物化优化子查询”。

• partitions (JSON name: partitions)

  The partitions from which records would be matched by the query. The value is NULL for nonpartitioned tables. See Section 26.3.5, “Obtaining Information About Partitions”.

  查询将从中匹配记录的分区。对于非分区表，该值为NULL。请参阅第26.3.5节“获取分区信息”。

• type (JSON name: access_type)

  The join type. For descriptions of the different types, see EXPLAIN Join Types.

  连接类型。有关不同类型的描述，请参阅EXPLAIN连接类型。

• possible_keys (JSON name: possible_keys)

  The possible_keys column indicates the indexes from which MySQL can choose to find the rows in this table. Note that this column is totally independent of the order of the tables as displayed in the output from EXPLAIN. That means that some of the keys in possible_keys might not be usable in practice with the generated table order.

  possible_keys列表示MySQL可以从中选择查找此表中行的索引。请注意，此列完全独立于EXPLAIN输出中显示的表的顺序。这意味着possible_key中的一些键在实际中可能无法用于生成的表顺序。

  If this column is NULL (or undefined in JSON-formatted output), there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again. See Section 15.1.9, “ALTER TABLE Statement”.

  如果此列为NULL（或在JSON格式的输出中未定义），则没有相关索引。在这种情况下，您可以通过检查WHERE子句来检查它是否引用了适合索引的一个或多个列，从而提高查询的性能。如果是这样，请创建一个适当的索引，并再次使用EXPLAIN检查查询。请参阅第15.1.9节“ALTER TABLE语句”。

  To see what indexes a table has, use SHOW INDEX FROM tbl_name.

  要查看表具有哪些索引，请使用SHOW INDEX FROM tbl_name。

• key (JSON name: key)

  The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

  key列表示MySQL实际决定使用的键（索引）。如果MySQL决定使用一个可能的_keys索引来查找行，则该索引将作为键值列出。

  It is possible that key may name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

  key可能会命名一个不在possible_keys值中的索引。如果所有可能的_keys索引都不适合查找行，但查询选择的所有列都是其他索引的列，则可能会发生这种情况。也就是说，命名索引覆盖了选定的列，因此尽管它不用于确定要检索哪些行，但索引扫描比数据行扫描更有效。

  For InnoDB, a secondary index might cover the selected columns even if the query also selects the primary key because InnoDB stores the primary key value with each secondary index. If key is NULL, MySQL found no index to use for executing the query more efficiently.

  对于InnoDB，即使查询也选择了主键，辅助索引也可能覆盖所选列，因为InnoDB将主键值与每个辅助索引一起存储。如果键为NULL，MySQL找不到用于更有效地执行查询的索引。

  To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query. See Section 10.9.4, “Index Hints”.

  要强制MySQL使用或忽略possible_keys列中列出的索引，请在查询中使用force index、use index或ignore index。见第10.9.4节“索引提示”。

  For MyISAM tables, running ANALYZE TABLE helps the optimizer choose better indexes. For MyISAM tables, myisamchk --analyze does the same. See Section 15.7.3.1, “ANALYZE TABLE Statement”, and Section 9.6, “MyISAM Table Maintenance and Crash Recovery”.

  对于MyISAM表，运行ANALYZE TABLE有助于优化器选择更好的索引。对于MyISAM表，myisamck-analyze也会执行相同的操作。见第15.7.3.1节“分析表声明”和第9.6节“MyISAM表维护和故障恢复”。

• key_len (JSON name: key_length)

  The key_len column indicates the length of the key that MySQL decided to use. The value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses. If the key column says NULL, the key_len column also says NULL.

  key_len列表示MySQL决定使用的键的长度。key_len的值使您能够确定MySQL实际使用的多部分密钥的多少部分。如果键列显示NULL，则key_len列也显示NULL。

  Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column.

  由于密钥存储格式的原因，对于可以为NULL的列，密钥长度比NOT NULL列长一个。

• ref (JSON name: ref)

  The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

  ref列显示了将哪些列或常量与键列中指定的索引进行比较，以从表中选择行。

  If the value is func, the value used is the result of some function. To see which function, use SHOW WARNINGS following EXPLAIN to see the extended EXPLAIN output. The function might actually be an operator such as an arithmetic operator.

  如果值是func，则使用的值是某个函数的结果。要查看哪个函数，请使用EXPLAIN后的SHOW WARNINGS查看扩展的EXPLAIN输出。该函数实际上可能是一个运算符，如算术运算符。

• rows (JSON name: rows)

  The rows column indicates the number of rows MySQL believes it must examine to execute the query.

  行列表示MySQL认为执行查询必须检查的行数。

  For InnoDB tables, this number is an estimate, and may not always be exact.

  对于InnoDB表，这个数字是一个估计值，可能并不总是准确的。

• filtered (JSON name: filtered)

  The filtered column indicates an estimated percentage of table rows that are filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred. Values decreasing from 100 indicate increasing amounts of filtering. rows shows the estimated number of rows examined and rows × filtered shows the number of rows that are joined with the following table. For example, if rows is 1000 and filtered is 50.00 (50%), the number of rows to be joined with the following table is 1000 × 50% = 500.

  筛选列表示按表条件筛选的表行的估计百分比。最大值为100，这意味着没有对行进行过滤。值从100开始递减表示过滤量增加。行显示了检查的估计行数，行×过滤显示了与下表连接的行数。例如，如果行数为1000，过滤后的行数为50.00（50%），则要与下表连接的行数是1000×50%=500。

• Extra (JSON name: none)

  This column contains additional information about how MySQL resolves the query. For descriptions of the different values, see EXPLAIN Extra Information.

  此列包含有关MySQL如何解析查询的其他信息。有关不同值的描述，请参阅EXPLAIN附加信息。

  There is no single JSON property corresponding to the Extra column; however, values that can occur in this column are exposed as JSON properties, or as the text of the message property.

  Extra列没有对应的单个JSON属性；但是，此列中可能出现的值将作为JSON属性或消息属性的文本公开。

EXPLAIN Join Types

The type column of EXPLAIN output describes how tables are joined. In JSON-formatted output, these are found as values of the access_type property. The following list describes the join types, ordered from the best type to the worst:

EXPLAIN输出的type列描述了表是如何连接的。在JSON格式的输出中，这些是access_type属性的值。以下列表描述了连接类型，按从最佳类型到最差类型的顺序排列：

• system

  The table has only one row (= system table). This is a special case of the const join type.

  该表只有一行（=系统表）。这是const连接类型的一个特例。

• const

  The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

  该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以该行列中的值可以被优化器的其余部分视为常量。const表非常快，因为它们只被读取一次。

  const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table:

  const用于将PRIMARY KEY或UNIQUE索引的所有部分与常数值进行比较。在以下查询中，tbl_name可以用作const表：

  SELECT * FROM tbl_name WHERE primary_key=1;
  
  SELECT * FROM tbl_name
    WHERE primary_key_part1=1 AND primary_key_part2=2;
  

• eq_ref

  One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

  对于前几张表中的每一行组合，都会从该表中读取一行。除了system和const类型之外，这是最好的连接类型。当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时，使用它。

  eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table. In the following examples, MySQL can use an eq_ref join to process ref_table:

  eq_ref可用于使用=运算符进行比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中的列的表达式。在以下示例中，MySQL可以使用eq_ref连接来处理ref_table：

  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;
  

• ref

  All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

  对于前面表中的每一行组合，将从该表中读取具有匹配索引值的所有行。如果连接仅使用键的最左侧前缀，或者键不是PRIMARY key或UNIQUE索引（换句话说，如果连接无法根据键值选择单行），则使用ref。如果使用的键只匹配几行，则这是一种很好的连接类型。

  ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use a ref join to process ref_table:

  ref可用于使用=或<=>运算符进行比较的索引列。在以下示例中，MySQL可以使用ref连接来处理ref_table：

  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;
  

• fulltext

  The join is performed using a FULLTEXT index.

  连接是使用FULLTEXT索引执行的。

• ref_or_null

  This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries. In the following examples, MySQL can use a ref_or_null join to process ref_table:

  这种连接类型类似于ref，但MySQL会额外搜索包含NULL值的行。这种连接类型优化最常用于解决子查询。在以下示例中，MySQL可以使用ref_or_null连接来处理ref_table：

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

  See Section 10.2.1.15, “IS NULL Optimization”.

• index_merge

  This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used. For more information, see Section 10.2.1.3, “Index Merge Optimization”.

  此连接类型表示使用了索引合并优化。在这种情况下，输出行中的键列包含所使用的索引列表，key_len包含所使用索引的最长键部分列表。有关更多信息，请参阅第10.2.1.3节“索引合并优化”。

• unique_subquery

  This type replaces eq_ref for some IN subqueries of the following form:

  此类型替换以下形式的某些IN子查询的eq_ref：

  value IN (SELECT primary_key FROM single_table WHERE some_expr)
  

  unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

  unique_subquery只是一个索引查找函数，它完全替换了子查询以提高效率。

• index_subquery

  This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

  此连接类型类似于unique_subquery。它取代了IN子查询，但它适用于以下形式的子查询中的非唯一索引：

  value IN (SELECT key_column FROM single_table WHERE some_expr)
  

• range

  Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

  使用索引选择行，只检索给定范围内的行。输出行中的键列指示使用了哪个索引。key_len包含所使用的最长键部分。此类型的ref列为NULL。

  range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:

  当使用=、<>、>、>=、<、<=、is NULL、<=>、BETWEEN、LIKE或IN（）运算符中的任何一个将键列与常量进行比较时，可以使用range：

  SELECT * FROM tbl_name
    WHERE key_column = 10;
  
  SELECT * FROM tbl_name
    WHERE key_column BETWEEN 10 and 20;
  
  SELECT * FROM tbl_name
    WHERE key_column IN (10,20,30);
  
  SELECT * FROM tbl_name
    WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
  

• index

  The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

  • If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.
  • A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

  MySQL can use this join type when the query uses only columns that are part of a single index.

  索引联接类型与ALL相同，只是扫描了索引树。这有两种方式：

  • 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则只扫描索引树。在这种情况下，Extra列显示正在使用索引。仅索引扫描通常比ALL更快，因为索引的大小通常小于表数据。
  • 使用从索引中读取的数据按索引顺序查找数据行来执行全表扫描。使用索引不会出现在“额外”列中。

  当查询仅使用单个索引中的列时，MySQL可以使用此联接类型。

• ALL

  A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

  对前面表中的每一行组合进行全表扫描。如果表是第一个未标记为const的表，这通常是不好的，在所有其他情况下通常都是非常糟糕的。通常，您可以通过添加索引来避免ALL，这些索引允许根据早期表中的常量值或列值从表中检索行。

EXPLAIN Extra Information

The Extra column of EXPLAIN output contains additional information about how MySQL resolves the query. The following list explains the values that can appear in this column. Each item also indicates for JSON-formatted output which property displays the Extra value. For some of these, there is a specific property. The others display as the text of the message property.

EXPLAIN输出的Extra列包含有关MySQL如何解析查询的其他信息。以下列表解释了此列中可能出现的值。每个项目还指示JSON格式输出的哪个属性显示Extra值。对于其中一些，有一个特定的属性。其他则显示为消息属性的文本。

If you want to make your queries as fast as possible, look out for Extra column values of Using filesort and Using temporary, or, in JSON-formatted EXPLAIN output, for using_filesort and using_temporary_table properties equal to true.

如果你想让查询尽可能快，请注意Using filesort和Using temporary的额外列值，或者在JSON格式的EXPLAIN输出中，注意Using _filesort和Using _temporary_table属性等于true。

• Backward index scan (JSON: backward_index_scan)

  The optimizer is able to use a descending index on an InnoDB table. Shown together with Using index. For more information, see Section 10.3.13, “Descending Indexes”.

  优化器能够对InnoDB表使用降序索引。与使用索引一起显示。有关更多信息，请参阅第10.3.13节“降序索引”。

• Child of 'table' pushed join@1 (JSON: message text)

  This table is referenced as the child of table in a join that can be pushed down to the NDB kernel. Applies only in NDB Cluster, when pushed-down joins are enabled. See the description of the ndb_join_pushdown server system variable for more information and examples.

  此表在可以下推到NDB内核的联接中被引用为表的子表。仅在启用下推连接时适用于NDB群集。有关更多信息和示例，请参阅ndb_join_pushdown服务器系统变量的描述。

• const row not found (JSON property: const_row_not_found)

  For a query such as SELECT ... FROM tbl_name, the table was empty.

  对于SELECT ... FROM tbl_name等查询，表为空。

• Deleting all rows (JSON property: message)

  For DELETE, some storage engines (such as MyISAM) support a handler method that removes all table rows in a simple and fast way. This Extra value is displayed if the engine uses this optimization.

  对于DELETE，一些存储引擎（如MyISAM）支持以简单快速的方式删除所有表行的处理程序方法。如果发动机使用此优化，则显示此额外值。

• Distinct (JSON property: distinct)

  MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.

  MySQL正在寻找不同的值，因此在找到第一个匹配的行后，它停止为当前行组合搜索更多行。

• FirstMatch(tbl_name) (JSON property: first_match)

  The semijoin FirstMatch join shortcutting strategy is used for tbl_name.

  tbl_name使用半连接FirstMatch连接快捷策略。

• Full scan on NULL key (JSON property: message)

  This occurs for subquery optimization as a fallback strategy when the optimizer cannot use an index-lookup access method.

  当优化器无法使用索引查找访问方法时，作为回退策略的子查询优化会发生这种情况。

• Impossible HAVING (JSON property: message)

  The HAVING clause is always false and cannot select any rows.

  HAVING子句始终为false，不能选择任何行。

• Impossible WHERE (JSON property: message)

  The WHERE clause is always false and cannot select any rows.

  WHERE子句始终为false，不能选择任何行。

• Impossible WHERE noticed after reading const tables (JSON property: message)

  MySQL has read all const (and system) tables and notice that the WHERE clause is always false.

  MySQL已经读取了所有const（和系统）表，并注意到WHERE子句始终为false。

• LooseScan(m..n) (JSON property: message)

  The semijoin LooseScan strategy is used. m and n are key part numbers.

  使用半连接LooseScan策略。m和n是关键零件号。

• No matching min/max row (JSON property: message)

  No row satisfies the condition for a query such as SELECT MIN(...) FROM ... WHERE condition.

  没有一行满足SELECT MIN（…）FROM…等查询的条件。。。何处条件。

• no matching row in const table (JSON property: message)

  For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.

  对于具有联接的查询，存在一个空表或一个没有满足唯一索引条件的行的表。

• No matching rows after partition pruning (JSON property: message)

  For DELETE or UPDATE, the optimizer found nothing to delete or update after partition pruning. It is similar in meaning to Impossible WHERE for SELECT statements.

  对于DELETE或UPDATE，优化器在分区修剪后没有发现任何要删除或更新的内容。它的含义类似于SELECT语句的Impossible WHERE。

• No tables used (JSON property: message)

  The query has no FROM clause, or has a FROM DUAL clause.

  For INSERT or REPLACE statements, EXPLAIN displays this value when there is no SELECT part. For example, it appears for EXPLAIN INSERT INTO t VALUES(10) because that is equivalent to EXPLAIN INSERT INTO t SELECT 10 FROM DUAL.

  查询没有FROM子句，或者有FROM DUAL子句。

  对于INSERT或REPLACE语句，当没有SELECT部分时，EXPLAIN会显示此值。例如，它出现在EXPLAIN INSERT INTO t VALUES（10）中，因为这相当于EXPLAIN INSERT INTO t SELECT 10 FROM DUAL。

• Not exists (JSON property: message)

  MySQL was able to do a LEFT JOIN optimization on the query and does not examine more rows in this table for the previous row combination after it finds one row that matches the LEFT JOIN criteria. Here is an example of the type of query that can be optimized this way:

  MySQL能够对查询进行LEFT JOIN优化，并且在找到一行符合LEFT JOIN条件后，不会检查此表中前一行组合的更多行。以下是可以通过这种方式优化的查询类型的示例：

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

  Assume that t2.id is defined as NOT NULL. In this case, MySQL scans t1 and looks up the rows in t2 using the values of t1.id. If MySQL finds a matching row in t2, it knows that t2.id can never be NULL, and does not scan through the rest of the rows in t2 that have the same id value. In other words, for each row in t1, MySQL needs to do only a single lookup in t2, regardless of how many rows actually match in t2.

  假设t2.id被定义为NOT NULL。在这种情况下，MySQL扫描t1，并使用t1.id的值查找t2中的行。如果MySQL在t2中找到匹配的行，它就知道t2.id永远不会为NULL，并且不会扫描t2中具有相同id值的其余行。换句话说，对于t1中的每一行，MySQL只需要在t2中进行一次查找，而不管t2中实际匹配的行数是多少。

  In MySQL 8.0.17 and later, this can also indicate that a WHERE condition of the form NOT IN (subquery) or NOT EXISTS (subquery) has been transformed internally into an antijoin. This removes the subquery and brings its tables into the plan for the topmost query, providing improved cost planning. By merging semijoins and antijoins, the optimizer can reorder tables in the execution plan more freely, in some cases resulting in a faster plan.

  在MySQL 8.0.17及更高版本中，这也可以表示形式为NOT In（子查询）或NOT EXISTS（子询问）的WHERE条件已在内部转换为反连接。这将删除子查询，并将其表带入最顶层查询的计划中，从而提供改进的成本计划。通过合并半连接和反连接，优化器可以更自由地重新排序执行计划中的表，在某些情况下可以实现更快的计划。

  You can see when an antijoin transformation is performed for a given query by checking the Message column from SHOW WARNINGS following execution of EXPLAIN, or in the output of EXPLAIN FORMAT=TREE.

  通过在执行EXPLAIN后检查SHOW WARNINGS中的Message列，或在EXPLAIN FORMAT=TREE的输出中检查Message列，可以查看何时对给定查询执行反连接转换。

  Note

  An antijoin is the complement of a semijoin table_a JOIN table_b ON condition. The antijoin returns all rows from table_a for which there is no row in table_b which matches condition.

  反连接是半连接表a JOIN表b ON条件的补码。antijoin返回table_a中没有符合条件的行的所有行。

• Plan isn't ready yet (JSON property: none)

  This value occurs with EXPLAIN FOR CONNECTION when the optimizer has not finished creating the execution plan for the statement executing in the named connection. If execution plan output comprises multiple lines, any or all of them could have this Extra value, depending on the progress of the optimizer in determining the full execution plan.

  当优化器尚未完成为在指定连接中执行的语句创建执行计划时，此值将与EXPLAIN FOR CONNECTION一起出现。如果执行计划输出包含多行，则根据优化器确定完整执行计划的进度，其中任何一行或所有行都可能具有此Extra值。

• Range checked for each record (index map: N) (JSON property: message)

  MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a range or index_merge access method to retrieve rows. This is not very fast, but is faster than performing a join with no index at all. The applicability criteria are as described in Section 10.2.1.2, “Range Optimization”, and Section 10.2.1.3, “Index Merge Optimization”, with the exception that all column values for the preceding table are known and considered to be constants.

  MySQL发现没有好的索引可供使用，但发现在已知前面表中的列值后，可能会使用一些索引。对于前面表中的每一行组合，MySQL都会检查是否可以使用range或index_merge访问方法来检索行。这不是很快，但比在没有索引的情况下执行连接要快。适用性标准如第10.2.1.2节“范围优化”和第10.2.1.3节“索引合并优化”所述，但上表的所有列值都是已知的，并且被视为常数。

  Indexes are numbered beginning with 1, in the same order as shown by SHOW INDEX for the table. The index map value N is a bitmask value that indicates which indexes are candidates. For example, a value of 0x19 (binary 11001) means that indexes 1, 4, and 5 are considered.

  索引从1开始编号，顺序与表中的SHOW INDEX所示的顺序相同。索引映射值N是指示哪些索引是候选的位掩码值。例如，值0x19（二进制11001）表示考虑索引1、4和5。

• Recursive (JSON property: recursive)

  This indicates that the row applies to the recursive SELECT part of a recursive common table expression. See Section 15.2.20, “WITH (Common Table Expressions)”.

  这表示该行适用于递归公共表表达式的递归SELECT部分。见第15.2.20节“WITH（通用表表达式）”。

• Rematerialize (JSON property: rematerialize)

  Rematerialize (X,...) is displayed in the EXPLAIN row for table T, where X is any lateral derived table whose rematerialization is triggered when a new row of T is read. For example:

  再物质化（X，…）显示在表T的EXPLAIN行中，其中X是任何横向派生表，当读取T的新行时，会触发其再序列化。例如：

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

  The content of the derived table is rematerialized to bring it up to date each time a new row of t is processed by the top query.

  每次顶部查询处理t的新行时，都会重新序列化派生表的内容，使其保持最新。

• Scanned N databases (JSON property: message)

  This indicates how many directory scans the server performs when processing a query for INFORMATION_SCHEMA tables, as described in Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”. The value of N can be 0, 1, or all.

  这表示服务器在处理对INFORMATION_SCHEMA表的查询时执行的目录扫描次数，如第10.2.3节“优化INFORMATY_SCHEMA查询”所述。N的值可以是0、1或全部。

• Select tables optimized away (JSON property: message)

  The optimizer determined 1) that at most one row should be returned, and 2) that to produce this row, a deterministic set of rows must be read. When the rows to be read can be read during the optimization phase (for example, by reading index rows), there is no need to read any tables during query execution.

  优化器确定1）最多应返回一行，2）要生成此行，必须读取一组确定性的行。当在优化阶段可以读取要读取的行时（例如，通过读取索引行），在查询执行期间不需要读取任何表。

  The first condition is fulfilled when the query is implicitly grouped (contains an aggregate function but no GROUP BY clause). The second condition is fulfilled when one row lookup is performed per index used. The number of indexes read determines the number of rows to read.

  当查询隐式分组时（包含聚合函数但没有GROUP BY子句），满足第一个条件。当对每个使用的索引执行一行查找时，满足第二个条件。读取的索引数量决定了要读取的行数。

  Consider the following implicitly grouped query:

  考虑以下隐式分组查询：

  SELECT MIN(c1), MIN(c2) FROM t1;
  

  Suppose that MIN(c1) can be retrieved by reading one index row and MIN(c2) can be retrieved by reading one row from a different index. That is, for each column c1 and c2, there exists an index where the column is the first column of the index. In this case, one row is returned, produced by reading two deterministic rows.

  假设MIN（c1）可以通过读取一个索引行来检索，MIN（c2）可以通过从不同的索引读取一行来检索。也就是说，对于每一列c1和c2，都存在一个索引，其中该列是索引的第一列。在这种情况下，返回一行，这是通过读取两个确定性行产生的。

  This Extra value does not occur if the rows to read are not deterministic. Consider this query:

  如果要读取的行不是确定性的，则不会出现此Extra值。考虑以下查询：

  SELECT MIN(c2) FROM t1 WHERE c1 <= 10;
  

  Suppose that (c1, c2) is a covering index. Using this index, all rows with c1 <= 10 must be scanned to find the minimum c2 value. By contrast, consider this query:

  假设（c1，c2）是一个覆盖指数。使用此索引，必须扫描c1<=10的所有行以找到最小c2值。相比之下，考虑一下这个查询：

  SELECT MIN(c2) FROM t1 WHERE c1 = 10;
  

  In this case, the first index row with c1 = 10 contains the minimum c2 value. Only one row must be read to produce the returned row.

  在这种情况下，c1＝10的第一索引行包含最小c2值。只需读取一行即可生成返回的行。

  For storage engines that maintain an exact row count per table (such as MyISAM, but not InnoDB), this Extra value can occur for COUNT(*) queries for which the WHERE clause is missing or always true and there is no GROUP BY clause. (This is an instance of an implicitly grouped query where the storage engine influences whether a deterministic number of rows can be read.)

  对于每个表保持精确行数的存储引擎（如MyISAM，但不包括InnoDB），对于缺少WHERE子句或始终为真且没有GROUP BY子句的count（*）查询，可能会出现此Extra值。（这是隐式分组查询的一个实例，其中存储引擎影响是否可以读取确定数量的行。）

• Skip_open_table, Open_frm_only, Open_full_table (JSON property: message)

  These values indicate file-opening optimizations that apply to queries for INFORMATION_SCHEMA tables.

  • Skip_open_table: Table files do not need to be opened. The information is already available from the data dictionary.

  • Open_frm_only: Only the data dictionary need be read for table information.

  • Open_full_table: Unoptimized information lookup. Table information must be read from the data dictionary and by reading table files.

  这些值表示应用于对INFORMATION_SCHEMA表的查询的文件打开优化。

  • Skip_open_table：不需要打开表文件。数据字典中已经提供了该信息。
  • Open_frm-only：只需要读取数据字典以获取表信息。
  • Open_full_table：未优化的信息查找。必须通过读取表文件从数据字典中读取表信息。

• Start temporary, End temporary (JSON property: message)

  This indicates temporary table use for the semijoin Duplicate Weedout strategy.

  这表示半连接重复剔除策略的临时表使用。

• unique row not found (JSON property: message)

  For a query such as SELECT ... FROM tbl_name, no rows satisfy the condition for a UNIQUE index or PRIMARY KEY on the table.

  对于SELECT等查询。。。从tbl_name开始，没有行满足表上唯一索引或主键的条件。

• Using filesort (JSON property: using_filesort)

  MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order. See Section 10.2.1.16, “ORDER BY Optimization”.

  MySQL必须进行额外的传递，以了解如何按排序顺序检索行。排序是通过根据连接类型遍历所有行，并存储与WHERE子句匹配的所有行的排序键和指向该行的指针来完成的。然后对键进行排序，并按排序顺序检索行。见第10.2.1.16节“按优化排序”。

• Using index (JSON property: using_index)

  The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

  仅使用索引树中的信息从表中检索列信息，而不必进行额外的查找来读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。

  For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.

  对于具有用户定义聚集索引的InnoDB表，即使Extra列中没有Using index，也可以使用该索引。如果类型是索引，键是PRIMARY，则会出现这种情况。

  Information about any covering indexes used is shown for EXPLAIN FORMAT=TRADITIONAL and EXPLAIN FORMAT=JSON. Beginning with MySQL 8.0.27, it is also shown for EXPLAIN FORMAT=TREE.

  有关所使用的任何覆盖索引的信息显示为EXPLAIN FORMAT=TRADITIONAL和EXPLAIN FORMAT=JSON。从MySQL 8.0.27开始，它也显示为EXPLAIN FORMAT=TREE。

• Using index condition (JSON property: using_index_condition)

  Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary. See Section 10.2.1.6, “Index Condition Pushdown Optimization”.

  通过访问索引元组并首先测试它们来确定是否读取整个表行，从而读取表。这样，索引信息用于延迟（“向下推”）读取整个表行，除非有必要。见第10.2.1.6节“指标条件下推优化”。

• Using index for group-by (JSON property: using_index_for_group_by)

  Similar to the Using index table access method, Using index for group-by indicates that MySQL found an index that can be used to retrieve all columns of a GROUP BY or DISTINCT query without any extra disk access to the actual table. Additionally, the index is used in the most efficient way so that for each group, only a few index entries are read. For details, see Section 10.2.1.17, “GROUP BY Optimization”.

  与Using index表访问方法类似，Using index for group by表示MySQL找到了一个索引，该索引可用于检索group by或DISTINCT查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，索引以最有效的方式使用，因此对于每个组，只读取几个索引条目。有关详细信息，请参阅第10.2.1.17节“按优化分组”。

• Using index for skip scan (JSON property: using_index_for_skip_scan)

  Indicates that the Skip Scan access method is used. See Skip Scan Range Access Method.

  表示使用了跳过扫描访问方法。请参阅跳过扫描范围访问方法。

• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join) (JSON property: using_join_buffer)

  Tables from earlier joins are read in portions into the join buffer, and then their rows are used from the buffer to perform the join with the current table. (Block Nested Loop) indicates use of the Block Nested-Loop algorithm, (Batched Key Access) indicates use of the Batched Key Access algorithm, and (hash join) indicates use of a hash join. That is, the keys from the table on the preceding line of the EXPLAIN output are buffered, and the matching rows are fetched in batches from the table represented by the line in which Using join buffer appears.

  来自早期联接的表被分部分读入联接缓冲区，然后它们的行从缓冲区中用于执行与当前表的联接。（块嵌套循环）表示使用块嵌套循环算法，（批量密钥访问）表示使用批量密钥访问算法，（哈希连接）表示使用哈希连接。也就是说，EXPLAIN输出前一行表中的键被缓冲，匹配的行从出现Using join buffer的行表示的表中批量提取。

  In JSON-formatted output, the value of using_join_buffer is always one of Block Nested Loop, Batched Key Access, or hash join.

  在JSON格式的输出中，using _join_buffer的值始终是块嵌套循环、批处理密钥访问或哈希连接之一。

  Hash joins are available beginning with MySQL 8.0.18; the Block Nested-Loop algorithm is not used in MySQL 8.0.20 or later MySQL releases. For more information about these optimizations, see Section 10.2.1.4, “Hash Join Optimization”, and Block Nested-Loop Join Algorithm.

  MySQL 8.0.18开始提供哈希连接；MySQL 8.0.20或更高版本的MySQL中不使用块嵌套循环算法。有关这些优化的更多信息，请参阅第10.2.1.4节“哈希连接优化”和块嵌套循环连接算法。

  See Batched Key Access Joins, for information about the Batched Key Access algorithm.

  有关批量密钥访问算法的信息，请参见批量密钥访问联接。

• Using MRR (JSON property: message)

  Tables are read using the Multi-Range Read optimization strategy. See Section 10.2.1.11, “Multi-Range Read Optimization”.

  使用多范围读取优化策略读取表。见第10.2.1.11节“多范围读取优化”。

• Using sort_union(...), Using union(...), Using intersect(...) (JSON property: message)

  These indicate the particular algorithm showing how index scans are merged for the index_merge join type. See Section 10.2.1.3, “Index Merge Optimization”.

  这些指示了显示如何合并index_merge连接类型的索引扫描的特定算法。见第10.2.1.3节“索引合并优化”。

• Using temporary (JSON property: using_temporary_table)

  To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

  为了解决查询，MySQL需要创建一个临时表来保存结果。如果查询包含以不同方式列出列的GROUP BY和ORDER BY子句，则通常会发生这种情况。

• Using where (JSON property: attached_condition)

  A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

  WHERE子句用于限制哪些行与下一个表匹配或发送到客户端。除非您特别打算从表中获取或检查所有行，否则如果Extra值不是Using where，并且表连接类型是all或index，则查询中可能会出现问题。

  Using where has no direct counterpart in JSON-formatted output; the attached_condition property contains any WHERE condition used.

  在JSON格式的输出中使用where没有直接对应项；attachd_condition属性包含使用的任何WHERE条件。

• Using where with pushed condition (JSON property: message)

  This item applies to NDB tables only. It means that NDB Cluster is using the Condition Pushdown optimization to improve the efficiency of a direct comparison between a nonindexed column and a constant. In such cases, the condition is “pushed down” to the cluster's data nodes and is evaluated on all data nodes simultaneously. This eliminates the need to send nonmatching rows over the network, and can speed up such queries by a factor of 5 to 10 times over cases where Condition Pushdown could be but is not used. For more information, see Section 10.2.1.5, “Engine Condition Pushdown Optimization”.

  此项仅适用于NDB表。这意味着NDB Cluster正在使用条件下推优化来提高无索引列和常数之间直接比较的效率。在这种情况下，条件被“下推”到集群的数据节点，并在所有数据节点上同时进行评估。这消除了在网络上发送不匹配行的需要，并且在可以但不使用条件推送的情况下，可以将此类查询的速度提高5到10倍。有关更多信息，请参阅第10.2.1.5节“发动机状态下推压优化”。

• Zero limit (JSON property: message)

  The query had a LIMIT 0 clause and cannot select any rows.

  查询有LIMIT 0子句，无法选择任何行。

EXPLAIN Output Interpretation

You can get a good indication of how good a join is by taking the product of the values in the rows column of the EXPLAIN output. This should tell you roughly how many rows MySQL must examine to execute the query. If you restrict queries with the max_join_size system variable, this row product also is used to determine which multiple-table SELECT statements to execute and which to abort. See Section 7.1.1, “Configuring the Server”.

通过获取EXPLAIN输出的行列中的值的乘积，可以很好地指示连接有多好。这应该大致告诉您MySQL必须检查多少行才能执行查询。如果使用max-join_size系统变量限制查询，则此行乘积还用于确定执行哪些多表SELECT语句以及中止哪些语句。请参阅第7.1.1节“配置服务器”。

The following example shows how a multiple-table join can be optimized progressively based on the information provided by EXPLAIN.

以下示例显示了如何根据EXPLAIN提供的信息逐步优化多表连接。

Suppose that you have the SELECT statement shown here and that you plan to examine it using EXPLAIN:

假设您有此处显示的SELECT语句，并且您计划使用EXPLAIN对其进行检查：

EXPLAIN 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;

For this example, make the following assumptions(假设):

• The columns being compared have been declared as follows.

  Table	Column	Data Type
  tt	ActualPC	CHAR(10)
  tt	AssignedPC	CHAR(10)
  tt	ClientID	CHAR(10)
  et	EMPLOYID	CHAR(15)
  do	CUSTNMBR	CHAR(15)

• The tables have the following indexes.

  Table	Index
  tt	ActualPC
  tt	AssignedPC
  tt	ClientID
  et	EMPLOYID (primary key)
  do	CUSTNMBR (primary key)

• The tt.ActualPC values are not evenly distributed.

Initially, before any optimizations have been performed, the EXPLAIN statement produces the following information:

最初，在执行任何优化之前，EXPLAIN语句会生成以下信息：

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

Because type is ALL for each table, this output indicates that MySQL is generating a Cartesian product of all the tables; that is, every combination of rows. This takes quite a long time, because the product of the number of rows in each table must be examined. For the case at hand, this product is 74 × 2135 × 74 × 3872 = 45,268,558,720 rows. If the tables were bigger, you can only imagine how long it would take.

因为每个表的类型都是ALL，所以此输出表示MySQL正在生成所有表的笛卡尔积；即每一行的组合。这需要相当长的时间，因为必须检查每个表中行数的乘积。对于手头的案例，该产品为74×2135×74×3872=45268558720行。如果桌子更大，你只能想象需要多长时间。

One problem here is that MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. tt.ActualPC is declared as CHAR(10) and et.EMPLOYID is CHAR(15), so there is a length mismatch.

这里的一个问题是，如果列被声明为相同的类型和大小，MySQL可以更有效地在列上使用索引。在这种情况下，如果VARCHAR和CHAR被声明为相同的大小，则它们被认为是相同的。tt.Actical PC被声明为CHAR（10），et.EMPLOYID为CHAR，因此存在长度不匹配。

To fix this disparity between column lengths, use ALTER TABLE to lengthen ActualPC from 10 characters to 15 characters:

要修复列长度之间的差异，请使用ALTER TABLE将ActualPC从10个字符延长到15个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Now tt.ActualPC and et.EMPLOYID are both VARCHAR(15). Executing the EXPLAIN statement again produces this result:

现在tt.AactualPC和et.MPLOYID都是VARCHAR（15）。再次执行EXPLAIN语句会产生以下结果：

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

This is not perfect, but is much better: The product of the rows values is less by a factor of 74. This version executes in a couple of seconds.

这并不完美，但要好得多：行值的乘积减少了74倍。此版本将在几秒钟内执行。

A second alteration can be made to eliminate the column length mismatches for the tt.AssignedPC = et_1.EMPLOYID and tt.ClientID = do.CUSTNMBR comparisons:

可以进行第二次更改，以消除tt.AssignedPC=et_1.EMPLOYID和tt.ClientID=do.CUSTNMBR比较的列长度不匹配：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

After that modification, EXPLAIN produces the output shown here:

修改后，EXPLAIN生成如下输出：

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

At this point, the query is optimized almost as well as possible. The remaining problem is that, by default, MySQL assumes that values in the tt.ActualPC column are evenly distributed, and that is not the case for the tt table. Fortunately, it is easy to tell MySQL to analyze the key distribution:

此时，查询几乎尽可能地优化了。剩下的问题是，默认情况下，MySQL假设tt.ActualPC列中的值是均匀分布的，而tt表则不是这样。幸运的是，告诉MySQL分析密钥分布很容易：

mysql> ANALYZE TABLE tt;

With the additional index information, the join is perfect and EXPLAIN produces this result:

有了额外的索引信息，连接就完美了，EXPLAIN会产生以下结果：

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

The rows column in the output from EXPLAIN is an educated guess from the MySQL join optimizer. Check whether the numbers are even close to the truth by comparing the rows product with the actual number of rows that the query returns. If the numbers are quite different, you might get better performance by using STRAIGHT_JOIN in your SELECT statement and trying to list the tables in a different order in the FROM clause. (However, STRAIGHT_JOIN may prevent indexes from being used because it disables semijoin transformations. See Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”.)

EXPLAIN输出中的行列是MySQL连接优化器的一个有根据的猜测。通过将行乘积与查询返回的实际行数进行比较，检查这些数字是否接近真实值。如果数字相差很大，在SELECT语句中使用STRIGHT_JOIN并尝试在FROM子句中以不同的顺序列出表，可能会获得更好的性能。（但是，STRIGHT_JOIN可能会阻止使用索引，因为它禁用了半连接转换。请参阅第10.2.2.1节“使用半连接转换优化IN和EXISTS子查询谓词”。）

It is possible in some cases to execute statements that modify data when EXPLAIN SELECT is used with a subquery; for more information, see Section 15.2.15.8, “Derived Tables”.

在某些情况下，当EXPLAIN SELECT与子查询一起使用时，可以执行修改数据的语句；有关更多信息，请参阅第15.2.15.8节“衍生表”。

10.8.3 Extended EXPLAIN Output Format

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. As of MySQL 8.0.12, extended information is available for SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. Prior to 8.0.12, extended information is available only for SELECT statements.

EXPLAIN语句产生额外的（“扩展”）信息，这些信息不是EXPLAIN输出的一部分，但可以通过在EXPLAIN后发出SHOW WARNINGS语句来查看。从MySQL 8.0.12开始，SELECT、DELETE、INSERT、REPLACE和UPDATE语句可以使用扩展信息。在8.0.12之前，扩展信息仅适用于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.

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）的空结果。

Here is an example of extended EXPLAIN output:

以下是扩展EXPLAIN输出的示例：

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)

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，也不打算执行。输出还可以包括具有消息值的行，这些行提供了关于优化器所采取操作的额外非SQL说明。

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)

  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)(查询片段)

  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与非扩展EXPLAIN输出中id值为N的行相关联。

• outer_tables semi join (inner_tables)

  A semijoin operation. inner_tables shows the tables that were not pulled out. See Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”.

  半联合行动。inner_tables显示了未被拉出的表。请参阅第10.2.2.1节，“使用半连接转换优化IN和EXISTS子查询谓词”。

• <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访问。

10.8.4 Obtaining Execution Plan Information for a Named Connection

获取命名连接的执行计划信息

To obtain the execution plan for an explainable statement executing in a named connection, use this statement:

要获取在命名连接中执行的可解释语句的执行计划，请使用以下语句：

EXPLAIN [options] FOR CONNECTION connection_id;

EXPLAIN FOR CONNECTION returns the EXPLAIN information that is currently being used to execute a query in a given connection. Because of changes to data (and supporting statistics) it may produce a different result from running EXPLAIN on the equivalent query text. This difference in behavior can be useful in diagnosing more transient performance problems. For example, if you are running a statement in one session that is taking a long time to complete, using EXPLAIN FOR CONNECTION in another session may yield useful information about the cause of the delay.

EXPLAIN FOR CONNECTION返回当前用于在给定连接中执行查询的EXPLAIN信息。由于数据（和支持统计数据）的更改，它可能会产生与在等效查询文本上运行EXPLAIN不同的结果。这种行为差异可用于诊断更多瞬态性能问题。例如，如果您在一个会话中运行的语句需要很长时间才能完成，则在另一个会话上使用EXPLAIN For CONNECTION可能会产生有关延迟原因的有用信息。

connection_id is the connection identifier, as obtained from the INFORMATION_SCHEMA PROCESSLIST table or the SHOW PROCESSLIST statement. If you have the PROCESS privilege, you can specify the identifier for any connection. Otherwise, you can specify the identifier only for your own connections. In all cases, you must have sufficient privileges to explain the query on the specified connection.

connection_id是连接标识符，从INFORMATION_SCHEMA PROCESSLIST表或SHOW PROCESSLIST语句中获得。如果您拥有PROCESS权限，则可以为任何连接指定标识符。否则，您只能为自己的连接指定标识符。在所有情况下，您都必须具有足够的权限来解释指定连接上的查询。

If the named connection is not executing a statement, the result is empty. Otherwise, EXPLAIN FOR CONNECTION applies only if the statement being executed in the named connection is explainable. This includes SELECT, DELETE, INSERT, REPLACE, and UPDATE. (However, EXPLAIN FOR CONNECTION does not work for prepared statements, even prepared statements of those types.)

如果指定的连接没有执行语句，则结果为空。否则，只有当在指定连接中执行的语句是可解释的时，EXPLAIN FOR CONNECTION才适用。这包括SELECT、DELETE、INSERT、REPLACE和UPDATE。（但是，EXPLAIN FOR CONNECTION不适用于准备好的语句，即使是这些类型的准备好的报表。）

If the named connection is executing an explainable statement, the output is what you would obtain by using EXPLAIN on the statement itself.

如果指定的连接正在执行一个可解释的语句，那么输出就是在语句本身上使用EXPLAIN所得到的结果。

If the named connection is executing a statement that is not explainable, an error occurs. For example, you cannot name the connection identifier for your current session because EXPLAIN is not explainable:

如果指定的连接正在执行无法解释的语句，则会发生错误。例如，您无法为当前会话命名连接标识符，因为EXPLAIN无法解释：

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|             373 |
+-----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FOR CONNECTION 373;
ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported
only for SELECT/UPDATE/INSERT/DELETE/REPLACE

The Com_explain_other status variable indicates the number of EXPLAIN FOR CONNECTION statements executed.

Com_explain_other状态变量表示执行的explain FOR CONNECTION语句的数量。

10.8.5 Estimating Query Performance

In most cases, you can estimate query performance by counting disk seeks. For small tables, you can usually find a row in one disk seek (because the index is probably cached). For bigger tables, you can estimate that, using B-tree indexes, you need this many seeks to find a row: log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1.

在大多数情况下，您可以通过计算磁盘寻道次数来估计查询性能。对于小表，您通常可以在一次磁盘查找中找到一行（因为索引可能已被缓存）。对于更大的表，你可以估计，使用B树索引，你需要这么多的搜索来找到一行：log（row_count）/log（index_block_length/3*2/（index_length+data_pointer_length））+1。

In MySQL, an index block is usually 1,024 bytes and the data pointer is usually four bytes. For a 500,000-row table with a key value length of three bytes (the size of MEDIUMINT), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks.

在MySQL中，索引块通常为1024字节，数据指针通常为4字节。对于键值长度为三个字节（MEDIUMINT的大小）的500000行表，公式表示log（500000）/log（1024/3*2/（3+4））+1=4次寻道。

This index would require storage of about 500,000 * 7 * 3/2 = 5.2MB (assuming a typical index buffer fill ratio of 2/3), so you probably have much of the index in memory and so need only one or two calls to read data to find the row.

这个索引需要大约50000073/2=5.2MB的存储空间（假设典型的索引缓冲区填充率为2/3），因此您可能在内存中有很多索引，因此只需要一到两次调用来读取数据来查找行。

For writes, however, you need four seek requests to find where to place a new index value and normally two seeks to update the index and write the row.

然而，对于写入操作，您需要四个寻道请求来查找放置新索引值的位置，通常需要两个寻道来更新索引并写入行。

The preceding discussion does not mean that your application performance slowly degenerates by log N. As long as everything is cached by the OS or the MySQL server, things become only marginally slower as the table gets bigger. After the data gets too big to be cached, things start to go much slower until your applications are bound only by disk seeks (which increase by log N). To avoid this, increase the key cache size as the data grows. For MyISAM tables, the key cache size is controlled by the key_buffer_size system variable. See Section 7.1.1, “Configuring the Server”.

前面的讨论并不意味着你的应用程序性能会随着日志N的增加而缓慢下降。只要操作系统或MySQL服务器缓存了所有内容，随着表变大，事情只会稍微变慢。在数据变得太大而无法缓存后，事情开始变得慢得多，直到你的应用程序只被磁盘寻道绑定（磁盘寻道增加了log N）。为了避免这种情况，随着数据的增长，增加密钥缓存的大小。对于MyISAM表，键缓存大小由key_buffer_size系统变量控制。请参阅第7.1.1节“配置服务器”。