id（JSON 名称：select_id） id (JSON name: select_id)

SELECT 标识符。它是查询中 SELECT 的顺序编号。如果该行指向其他行的联合结果，则该值可以为 NULL。在这种情况下，table 列显示类似 <unionM,N> 的值，以表明该行指向 id 值为 M 和 N 的行的联合结果。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_type（JSON 名称：无） select_type (JSON name: none)

SELECT 的类型，可以是下表中显示的任意一种。以 JSON 格式的 EXPLAIN 会将 SELECT 类型作为 query_block 的属性暴露出来，除非它是 SIMPLE 或 PRIMARY。表中也显示了 JSON 名称（如适用）。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.

DEPENDENT 通常表示使用了相关子查询。参见 第 15.2.15.7 节，“相关子查询”。DEPENDENT typically signifies the use of a correlated subquery. See Section 15.2.15.7, “Correlated Subqueries”.

DEPENDENT SUBQUERY 的评估方式不同于 UNCACHEABLE SUBQUERY 的评估方式。对于 DEPENDENT SUBQUERY，子查询仅在其外部上下文变量的不同值集合下重新评估一次。对于 UNCACHEABLE SUBQUERY，子查询会针对外部上下文的每一行重新评估。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.

当您在 EXPLAIN 中指定 FORMAT=JSON 时，输出中没有与 select_type 直接对应的单个属性；query_block 属性对应于给定的 SELECT。与刚刚显示的大多数 SELECT 子查询类型相对应的属性是可用的（例如 materialized_from_subquery 对应 MATERIALIZED），并在适当的时候显示。对于 SIMPLE 或 PRIMARY 没有 JSON 对应项。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.

非SELECT语句的select_type值显示受影响表的语句类型。例如，对于DELETE语句，select_type的值为DELETE。The select_type value for non-SELECT statements displays the statement type for affected tables. For example, select_type is DELETE for DELETE statements.

table（JSON 名称：table_name） 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:

partitions（JSON 名称：partitions） partitions (JSON name: partitions)

查询会匹配记录的分区。对于非分区表，该值为 NULL。参见 第 26.3.5 节，“获取分区信息”。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”.

type（JSON 名称：access_type） type (JSON name: access_type)

连接类型。有关不同类型的描述，请参见 EXPLAIN 连接类型。The join type. For descriptions of the different types, see EXPLAIN Join Types.

possible_keys（JSON 名称：possible_keys） possible_keys (JSON name: possible_keys)

possible_keys 列表示 MySQL 可以选择用来查找该表中行的索引。请注意，这一列与输出中显示的表顺序完全无关，输出顺序来自 EXPLAIN。这意味着 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.

如果该列为 NULL（或在 JSON 格式输出中未定义），则没有相关索引。在这种情况下，你可以通过检查 WHERE 子句是否涉及适合索引的某些列来提高查询性能。如果是这样，请创建适当的索引，然后再次使用 EXPLAIN 检查查询。参见 第 15.1.9 节，“ALTER TABLE 语句”。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”.

要查看表有哪些索引，请使用 SHOW INDEX FROM tbl_name。To see what indexes a table has, use SHOW INDEX FROM tbl_name.

key（JSON 名称：key） key (JSON name: key)

key 列表示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用 possible_keys 中的某个索引来查找行，则该索引会列为 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 可能会命名一个不在 possible_keys 值中的索引。如果 possible_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.

对于 InnoDB，即使查询也选择了主键，二级索引仍可能涵盖所选列，因为 InnoDB 在每个二级索引中存储了主键值。如果 key 为 NULL，则 MySQL 未找到可用于更高效执行查询的索引。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.

要强制 MySQL 使用或忽略 possible_keys 列中列出的索引，请在查询中使用 FORCE INDEX、USE INDEX 或 IGNORE INDEX。请参阅 第 10.9.4 节，“索引提示”。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”.

对于 MyISAM 表，运行 ANALYZE TABLE 可以帮助优化器选择更好的索引。对于 MyISAM 表，myisamchk --analyze 也具有相同的作用。参见 第 15.7.3.1 节，“ANALYZE TABLE 语句” 和 第 9.6 节，“MyISAM 表维护和崩溃恢复”。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”.

key_len（JSON 名称：key_length） key_len (JSON name: key_length)

key_len 列表示 MySQL 决定使用的键长度。key_len 的值使您能够确定 MySQL 实际使用了多部分键中的多少部分。如果 key 列显示 NULL，则 key_len 列也会显示 NULL。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.

由于键的存储格式，可为 NULL 的列的键长度比 NOT 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.

ref（JSON 名称：ref） ref (JSON name: ref)

ref 列显示与 key 列中命名的索引进行比较以从表中选择行的列或常量。The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

如果值是 func，则使用的值是某个函数的结果。要查看是哪一个函数，请在 EXPLAIN 之后使用 SHOW WARNINGS 查看扩展的 EXPLAIN 输出。这个函数实际上可能是一个算术运算符等操作符。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.

rows（JSON 名称：rows） rows (JSON name: rows)

rows 列表示 MySQL 认为必须检查的行数，以执行查询。The rows column indicates the number of rows MySQL believes it must examine to execute the query.

对于 InnoDB 表，这个数字是一个估计值，可能并不总是准确的。For InnoDB tables, this number is an estimate, and may not always be exact.

filtered（JSON 名称：filtered） filtered (JSON name: filtered)

filtered 列表示按表条件过滤的表行的估计百分比。最大值为 100，表示未对行进行任何过滤。从 100 开始下降的值表示过滤量增加。rows 显示估算检查的行数，而 rows × filtered 显示将与下一个表连接的行数。例如，如果 rows 为 1000 且 filtered 为 50.00 (50%)，则要与下一个表连接的行数为 1000 × 50% = 500。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.

Extra（JSON 名称：none） Extra (JSON name: none)

此列包含有关 MySQL 如何解析查询的附加信息。有关不同值的描述，请参见 EXPLAIN 附加信息。This column contains additional information about how MySQL resolves the query. For descriptions of the different values, see EXPLAIN Extra Information.

没有与 Extra 列对应的单一 JSON 属性；但是，该列中可能出现的值会以 JSON 属性的形式显示，或作为 message 属性的文本。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.

system

该表只有一行（= 系统表）。这是 const 连接类型的特殊情况。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 用于当你将 PRIMARY KEY 或 UNIQUE 索引的所有部分与常量值进行比较时。在以下查询中，tbl_name 可以作为 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:

SELECT * FROM tbl_name WHERE primary_key=1;

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

eq_ref

对于前面表的每种行组合，将从此表读取一行。除了 system 和 const 类型之外，这是可能的最佳连接类型。当连接使用索引的所有部分并且该索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，将使用此类型。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.

eq_ref 可以用于通过 = 操作符进行比较的索引列。比较值可以是常量，也可以是使用先前读取的表中的列的表达式。在以下示例中，MySQL 可以使用 eq_ref 连接来处理 ref_table：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:

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

对于来自前面表的每一行组合，将从此表中读取所有具有匹配索引值的行。如果连接仅使用键的最左前缀，或者该键不是PRIMARY KEY或UNIQUE索引，则使用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.

ref 可以用于通过 = 或 <=> 操作符进行比较的索引列。在以下示例中，MySQL 可以使用 ref 联接来处理 ref_table：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:

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

该联接是使用 FULLTEXT 索引执行的。The join is performed using a FULLTEXT index.

ref_or_null

这种连接类型类似于 ref，但额外地，MySQL 会对包含 NULL 值的行进行一次额外搜索。这种连接类型的优化最常用于解析子查询。在以下示例中，MySQL 可以使用 ref_or_null 连接来处理 ref_table：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:

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

参见 第 10.2.1.15 节，“IS NULL 优化”。See Section 10.2.1.15, “IS NULL Optimization”.

index_merge

此连接类型表示使用了索引合并（Index Merge）优化。在这种情况下，输出行中的 key 列包含使用的索引列表，key_len 包含所使用索引的最长键部分列表。更多信息，请参见 第 10.2.1.3 节，“索引合并优化”。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”.

unique_subquery

此类型在某些形式如下的 IN 子查询中替代 eq_ref：This type replaces eq_ref for some IN subqueries of the following form:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery 只是一个索引查找函数，用来完全替代子查询，以提高效率。unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

index_subquery

这种连接类型类似于 unique_subquery。它替代了 IN 子查询，但它适用于以下形式子查询中的非唯一索引：This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

只有位于给定范围内的行会被检索，使用索引来选择这些行。输出行中的key列表示使用的是哪个索引。key_len包含使用的最长键部分。对于这种类型，ref列为NULL。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.

range 可以在键列与常量使用任何 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 运算符时使用：range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:

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 index

index连接类型与ALL相同，只是扫描的是索引树。出现这种情况有两种方式：The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

当查询只使用属于单个索引的列时，MySQL 可以使用这种连接类型。MySQL can use this join type when the query uses only columns that are part of a single index.

ALL ALL

对于来自前面表的每一行组合，都会进行全表扫描。如果该表是未标记const的第一个表，这通常不是好事，在其他所有情况下一般是非常糟糕的。通常，你可以通过添加索引来避免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.

Backward index scan（JSON: backward_index_scan）Backward index scan (JSON: backward_index_scan)

优化器能够在 InnoDB 表上使用降序索引。与 Using index 一起显示。更多信息，请参见 第 10.3.13 节，“降序索引”。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”.

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

当该表作为 table 的子表在可以下推到 NDB 内核的连接中引用时才适用。仅在启用下推连接的 NDB 集群中适用。有关更多信息和示例，请参阅 ndb_join_pushdown 服务器系统变量的说明。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.

const row not found (JSON 属性: const_row_not_found)const row not found (JSON property: const_row_not_found)

对于如 SELECT ... FROM tbl_name 的查询，该表为空。For a query such as SELECT ... FROM tbl_name, the table was empty.

Deleting all rows（JSON 属性：message）Deleting all rows (JSON property: message)

对于 DELETE，一些存储引擎（例如 MyISAM）支持一种处理方法，可以简单快速地删除所有表行。如果引擎使用此优化，则会显示此 Extra 值。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.

Distinct（JSON 属性：distinct）Distinct (JSON property: distinct)

MySQL 正在查找不同的值，因此在找到第一个匹配的行之后，它会停止搜索当前行组合的更多行。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.

FirstMatch(tbl_name)（JSON 属性：first_match）FirstMatch(tbl_name) (JSON property: first_match)

半连接 FirstMatch 连接捷径策略用于 tbl_name。The semijoin FirstMatch join shortcutting strategy is used for tbl_name.

Full scan on NULL key（JSON 属性：message）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 属性：message）Impossible HAVING (JSON property: message)

HAVING 子句总是为假，无法选择任何行。The HAVING clause is always false and cannot select any rows.

Impossible WHERE（JSON 属性：message）Impossible WHERE (JSON property: message)

WHERE 子句总是为假，无法选择任何行。The WHERE clause is always false and cannot select any rows.

Impossible WHERE noticed after reading const tables（JSON 属性：message）Impossible WHERE noticed after reading const tables (JSON property: message)

MySQL 已经读取了所有 const（和 system）表，并注意到 WHERE 子句总是为假。MySQL has read all const (and system) tables and notice that the WHERE clause is always false.

LooseScan(m..n)（JSON 属性：message）LooseScan(m..n) (JSON property: message)

使用半连接 LooseScan 策略。m 和 n 是关键零件编号。The semijoin LooseScan strategy is used. m and n are key part numbers.

No matching min/max row（JSON 属性：message）No matching min/max row (JSON property: message)

没有行满足查询条件，例如 SELECT MIN(...) FROM ... WHERE condition 的查询条件。No row satisfies the condition for a query such as SELECT MIN(...) FROM ... WHERE condition.

no matching row in const table（JSON 属性：message）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 属性：message）No matching rows after partition pruning (JSON property: message)

对于 DELETE 或 UPDATE，优化器在分区修剪后没有找到可删除或更新的内容。这在意义上类似于 SELECT 语句的 Impossible WHERE 条件。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.

No tables used（JSON 属性：message）No tables used (JSON property: message)

查询没有 FROM 子句，或有 FROM DUAL 子句。The query has no FROM clause, or has a FROM DUAL clause.

对于 INSERT 或 REPLACE 语句，当没有 SELECT 部分时，EXPLAIN 会显示此值。例如，对于 EXPLAIN INSERT INTO t VALUES(10) 它会出现，因为这相当于 EXPLAIN INSERT INTO t SELECT 10 FROM DUAL。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.

Not exists（JSON 属性：message）Not exists (JSON property: message)

MySQL 能够对查询进行LEFT JOIN优化，并且在找到符合LEFT JOIN条件的一行后，不会为之前的行组合检查该表中的更多行。以下是可以通过这种方式优化的查询类型示例：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:

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 中实际有多少行匹配。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.

这也可能表示形式为 WHERE 或 NOT IN (subquery) 的 NOT EXISTS (subquery) 条件已在内部被转换为反连接。这会移除子查询，并将其表引入到最顶层查询的计划中，从而改进成本规划。通过合并半连接和反连接，优化器可以更自由地重新排序执行计划中的表，在某些情况下会产生更快的执行计划。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.

你可以通过在执行 EXPLAIN 后检查 Message 的 SHOW WARNINGS 列，或者查看 EXPLAIN FORMAT=TREE 的输出，来确定给定查询是否进行了反连接转换。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.

Plan isn't ready yet（JSON 属性：无）Plan isn't ready yet (JSON property: none)

当优化器尚未完成为在命名连接中执行的语句创建执行计划时，此值会在 EXPLAIN FOR CONNECTION 中出现。如果执行计划输出由多行组成，根据优化器确定完整执行计划的进度，它们中的任何一行或所有行都可能具有此 Extra 值。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.

Range checked for each record (index map: N)（JSON 属性：message）Range checked for each record (index map: N) (JSON property: message)

MySQL 没有找到可用的良好索引，但发现某些索引在前面表的列值已知后可能会被使用。对于前面表中的每一行组合，MySQL 会检查是否可以使用 range 或 index_merge 访问方法来检索行。这不是非常快，但比完全没有索引进行连接要快。适用条件如 第 10.2.1.2 节，“范围优化” 和 第 10.2.1.3 节，“索引合并优化” 所述，例外情况是前面表的所有列值都已知并被视为常量。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.

索引的编号从 1 开始，顺序与SHOW INDEX 为表显示的顺序相同。索引映射值N是一个位掩码值，用于指示哪些索引是候选索引。例如，值0x19（二进制 11001）表示索引 1、4 和 5 被考虑。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.

Recursive（JSON 属性：recursive）Recursive (JSON property: recursive)

这表示该行适用于递归公共表表达式的递归SELECT部分。见第 15.2.20 节，“WITH（通用表表达式）”。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)”.

Rematerialize（JSON 属性：rematerialize）Rematerialize (JSON property: rematerialize)

Rematerialize (X,...) 显示在表 T 的 EXPLAIN 行中，其中 X 是任何横向派生表，当读取表 T 的新行时，该派生表的重新生成将被触发。例如：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:

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

每次顶层查询处理新行 t 时，派生表的内容都会重新生成以保持最新状态。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.

Scanned N databases（JSON 属性：message）Scanned N databases (JSON property: message)

这表示服务器在处理对 INFORMATION_SCHEMA 表的查询时执行的目录扫描次数，如 第 10.2.3 节，“优化 INFORMATION_SCHEMA 查询” 中所述。N 的值可以是 0、1 或 all。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.

Select tables optimized away（JSON 属性：message）Select tables optimized away (JSON property: message)

优化器确定了 1）最多应该返回一行，和 2）为了产生这一行，必须读取一组确定的行。当要读取的行可以在优化阶段读取时（例如，通过读取索引行），在查询执行期间就不需要读取任何表。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.

当查询是隐式分组（包含聚合函数但没有 GROUP BY 子句）时，第一个条件就满足。第二个条件是在每个使用的索引上进行一次行查找时满足。读取的索引数量决定了需要读取的行数。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.

考虑以下隐式分组查询：Consider the following implicitly grouped query:

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

假设可以通过读取一个索引行来获取 MIN(c1)，并且可以通过读取来自不同索引的一行来获取 MIN(c2)。也就是说，对于每个列 c1 和 c2，存在一个索引，其中该列是索引的第一列。在这种情况下，会返回一行，这是通过读取两行确定的行生成的。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.

如果要读取的行不是确定的，则不会出现这个 Extra 值。考虑以下查询：This Extra value does not occur if the rows to read are not deterministic. Consider this query:

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

假设 (c1, c2) 是一个覆盖索引。使用此索引，必须扫描所有 c1 <= 10 的行以找到最小的 c2 值。相比之下，考虑以下查询：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:

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

在这种情况下，第一个索引行中 c1 = 10 的行包含最小的 c2 值。只需读取一行即可生成返回的行。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.

对于维护每个表确切行数的存储引擎（例如 MyISAM，但不包括 InnoDB），当 COUNT(*) 查询缺少 WHERE 条件或条件总是为真，且没有 GROUP BY 子句时，可能会出现这个 Extra 值。（这是隐式分组查询的一个实例，其中存储引擎会影响是否可以读取到确定数量的行。）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.)

Skip_open_table、Open_frm_only、Open_full_table（JSON 属性：message）Skip_open_table, Open_frm_only, Open_full_table (JSON property: message)

这些值表示适用于 INFORMATION_SCHEMA 表查询的文件打开优化。These values indicate file-opening optimizations that apply to queries for INFORMATION_SCHEMA tables.

Start temporary，End temporary（JSON 属性：message）Start temporary, End temporary (JSON property: message)

这表示半连接重复去除策略中使用了临时表。This indicates temporary table use for the semijoin Duplicate Weedout strategy.

unique row not found（JSON 属性：message）unique row not found (JSON property: message)

对于如 SELECT ... FROM tbl_name 的查询，没有行满足表上 UNIQUE 索引或 PRIMARY KEY 的条件。For a query such as SELECT ... FROM tbl_name, no rows satisfy the condition for a UNIQUE index or PRIMARY KEY on the table.

Using filesort（JSON 属性：using_filesort）Using filesort (JSON property: using_filesort)

MySQL 必须进行额外的操作以确定如何按排序顺序检索行。排序是通过根据连接类型遍历所有行并为所有符合 WHERE 子句的行存储排序键和行指针来完成的。然后对键进行排序，并按排序顺序检索行。请参阅 第 10.2.1.16 节，“ORDER BY 优化”。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”.

Using index (JSON 属性: using_index)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.

对于具有用户定义聚簇索引的 InnoDB 表，即使 Extra 列中没有 Using index，也可以使用该索引。如果 type 是 index 且 key 是 PRIMARY，则属于这种情况。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.

使用的任何覆盖索引的信息会在 EXPLAIN FORMAT=TRADITIONAL 和 EXPLAIN FORMAT=JSON 中显示。在 EXPLAIN FORMAT=TREE 中也会显示。Information about any covering indexes used is shown for EXPLAIN FORMAT=TRADITIONAL and EXPLAIN FORMAT=JSON. It is also shown for EXPLAIN FORMAT=TREE.

Using index condition（JSON 属性：using_index_condition）Using index condition (JSON property: using_index_condition)

表通过访问索引元组并首先测试它们来读取，从而确定是否读取完整的表行。通过这种方式，索引信息用于推迟（“下推”）读取完整表行，除非有必要。请参见 第 10.2.1.6 节，“索引条件下推优化”。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”.

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

类似于 Using index 表访问方法，Using index for group-by 表示 MySQL 找到了一个索引，可以用来检索 GROUP BY 或 DISTINCT 查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，该索引以最有效的方式使用，因此对于每个组，只读取少量索引条目。详情请参见 第 10.2.1.17 节，“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 for skip scan（JSON 属性：using_index_for_skip_scan）Using index for skip scan (JSON property: using_index_for_skip_scan)

表示使用 Skip Scan 访问方法。请参见 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 属性：using_join_buffer）Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join) (JSON property: using_join_buffer)

来自早期连接的表会分批读入连接缓冲区，然后其行会从缓冲区中使用以与当前表执行连接。(Block Nested Loop) 表示使用块嵌套循环算法，(Batched Key Access) 表示使用批量键访问算法，(hash join) 表示使用哈希连接。也就是说，来自 EXPLAIN 输出中前一行表的键会被缓存，并且匹配的行会从出现 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.

在 JSON 格式的输出中，using_join_buffer 的值总是 Block Nested Loop、Batched Key Access 或 hash join 之一。In JSON-formatted output, the value of using_join_buffer is always one of Block Nested Loop, Batched Key Access, or hash join.

有关哈希连接的更多信息，请参见 第 10.2.1.4 节，“哈希连接优化”。For more information about hash joins, see Section 10.2.1.4, “Hash Join Optimization”.

有关批量键访问算法的信息，请参见 批量键访问连接。See Batched Key Access Joins, for information about the Batched Key Access algorithm.

Using MRR（JSON 属性：message）Using MRR (JSON property: message)

表使用多范围读取优化策略进行读取。请参见 第 10.2.1.11 节，“多范围读取优化”。Tables are read using the Multi-Range Read optimization strategy. See Section 10.2.1.11, “Multi-Range Read Optimization”.

Using sort_union(...)、Using union(...)、Using intersect(...)（JSON 属性：message）Using sort_union(...), Using union(...), Using intersect(...) (JSON property: message)

这些表示用于 index_merge 联接类型的索引扫描合并的特定算法。参见 第 10.2.1.3 节，“索引合并优化”。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”.

Using temporary（JSON 属性：using_temporary_table）Using temporary (JSON property: using_temporary_table)

为了解析查询，MySQL 需要创建一个临时表来存储结果。如果查询包含 GROUP BY 和 ORDER BY 并且列的顺序不同，通常会发生这种情况。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.

Using where（JSON 属性：attached_condition）Using where (JSON property: attached_condition)

WHERE 子句用于限制哪些行与下一个表匹配或发送到客户端。除非您特别打算从表中获取或检查所有行，否则如果 Extra 的值不是 Using where 并且表连接类型是 ALL 或 index，可能说明您的查询有问题。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.

Using where 在 JSON 格式输出中没有直接对应；attached_condition 属性包含使用的任何 WHERE 条件。Using where has no direct counterpart in JSON-formatted output; the attached_condition property contains any WHERE condition used.

Using where with pushed condition (JSON 属性: message)Using where with pushed condition (JSON property: message)

此项仅适用于 NDB 表 仅。这意味着 NDB Cluster 正在使用条件下推优化来提高对非索引列与常量之间直接比较的效率。在这种情况下，条件会被 “下推” 到集群的数据节点，并在所有数据节点上同时评估。这消除了通过网络发送不匹配行的需要，并且在条件下推可能使用但未使用的情况下，可以将此类查询的速度提高 5 到 10 倍。更多信息，请参见 第 10.2.1.5 节，“引擎条件下推优化”。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”.

Zero limit（JSON 属性：message）Zero limit (JSON property: message)

查询包含 LIMIT 0 子句，因此无法选择任何行。The query had a LIMIT 0 clause and cannot select any rows.

正在比较的列已声明如下。The columns being compared have been declared as follows.

这些表具有以下索引。The tables have the following indexes.

tt.ActualPC 的值分布不均。The tt.ActualPC values are not evenly distributed.