MySQL 5.7 EXPLAIN 文档翻译

quest94

已于 2023-11-29 15:53:06 修改

阅读量31

点赞数

文章标签： mysql 数据库

于 2023-11-29 15:47:44 首次发布

原文链接：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

版权

原文地址：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
拜读 MySQL 英文文档并保留翻译结果，非单纯机翻，会在尽可能保留原文语义的目标下保证语句通顺。
能力有限，如有错误的地方欢迎指正。

8.8.2 EXPLAIN 输出格式

EXPLAIN 语句提供了有关 MySQL 如何执行语句的信息。EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE和UPDATE语句。
EXPLAIN 为 SELECT 语句中使用到的每个表返回一行信息。它按照 MySQL 在处理语句时读取的顺序列出这些表。MySQL 使用嵌套循环联接方法解析所有联接。这意味着 MySQL 从第一个表中读取一行，然后在第二个表、第三个表中找到匹配的行，依此类推。当处理完所有表时，MySQL 输出选定的列，并在表列表中回溯，直到找到更多匹配行的表。从该表中读取下一行，并继续处理下一个表。
EXPLAIN 输出包括分区信息。此外，对于 SELECT 语句，EXPLAIN 生成扩展信息，生成的扩展信息可以在 EXPLAIN 后面执行 SHOW WARNINGS 查看 (参见 Section 8.8.3, “Extended EXPLAIN Output Format”)。

便条
在较旧的 MySQL 版本中，分区和扩展信息是使用 EXPLAIN PARTITIONS 和 EXPLAIN EXTENDED 生成的。这些语法仍然可以识别为向后兼容性，但分区和扩展输出现在默认启用，因此 PARTITIONS 和 EXTENDED 关键字是多余的，不推荐使用。它们的使用会引起警告；期望它们在将来的 MySQL 版本中从 EXPLAIN 语法中删除。

便条
MySQL Workbench 有 Visual Explain 功能，可提供 Explain 输出的可视化表示。参见 Tutorial: Using Explain to Improve Query Performance.

EXPLAIN 输出列

本节介绍 EXPLAIN 生成的输出列。后续内容提供了有关 type 列和 Extra 列的附加信息。
EXPLAIN 的每一个输出行都提供有关一个表的信息。每一行包含的值的概述在 表 8.1 EXPLAIN 输出列含义 中，并随后进行了更详细的描述。列名显示在表的第一列中；当使用 FORMAT=JSON 时，第二列提供输出中显示的等效属性名称。

表 8.1 EXPLAIN 输出列含义

列名	JSON 命名	含义
id	select_id	SELECT 标识符
select_type	None	SELECT 类型
table	table_name	本输出行对应的表
partitions	partitions	匹配的分区
type	access_type	联接类型
possible_keys	possible_keys	可供选择的索引
key	key	实际选择的索引
key_len	key_length	所选 key 的长度
ref	ref	与索引进行匹配的列
rows	rows	预计要扫描的行数
filtered	filtered	按表条件筛选出的结果占 rows 的百分比
Extra	None	附加信息

便条
值为 NULL 的 JSON 属性不会显示在 JSON 格式的 EXPLAIN 输出中。

id（JSON 命名：select_id）
SELECT 标识符。这是 SELECT 在查询语句中的序列号。如果本行是其他行的并集结果，则该值为NULL。在这种情况下，table 列显示一个类似于 <union M,N> 的值，表示本行是 id 值为 M 和 N 的行的并集。
select_type (JSON 命名: none)
SELECT 的类型，可以是下表中显示的任何类型。JSON 格式的 EXPLAIN 将 SELECT 类型公开为 query_block 的属性，除非它是 SIMPLE 或 PRIMARY。JSON 命名（如果适用）也显示在表中。

select_type 值	JSON 命名	含义
SIMPLE	None	简单SELECT（不使用UNION或子查询）
PRIMARY	None	最外层的 SELECT
UNION	None	UNION 中的第二个或更晚的 SELECT 语句
DEPENDENT UNION	dependent (true)	UNION 中的第二个或更晚的 SELECT 语句，依赖于外层查询
UNION RESULT	union_result	UNION 的结果
SUBQUERY	None	子查询中的第一个 SELECT
DEPENDENT SUBQUERY	dependent (true)	子查询中的第一个SELECT，依赖于外层查询
DERIVED	None	派生表
MATERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable (false)	无法缓存其结果的子查询，并且必须针对外层查询的每一行重新评估该子查询
UNCACHEABLE UNION	cacheable (false)	UNION 中属于不可缓存子查询的第二个或更晚的 SELECT （参见 UNCACHEABLE SUBQUERY）

DEPENDENT 通常意味着使用相关的子查询。参见 Section 13.2.10.7, “Correlated Subqueries”。
DEPENDENT SUBQUERY 评估不同于 UNCACHEABLE SUBQUERY 评估。对于 DEPENDENT SUBQUERY，来自其外层上下文的变量的每一组不同值，只对子查询重新评估一次。对于 UNCACHEABLE SUBQUERY，将为外层上下文的每一行重新评估子查询。
子查询的可缓存性不同于在查询缓存中查询结果的缓存（详细描述参见 Section 8.10.3.1, “How the Query Cache Operates”）。子查询缓存发生在查询执行期间，而查询缓存仅用于在查询执行完成后存储结果。
当指定 FORMAT=JSON 执行 EXPLAIN，输出结果中没有直接等效于 select_type 的单个属性；query_ block 属性对应于给定的 SELECT。刚刚展示的大多数 SELECT 子查询类型等效的属性是可用的（例如存在 materialized_from_subquery 对于 MATERIALIZED），并在适当的时候显示。 SIMPLE 或 PRIMARY 没有等效的 JSON 属性。
对于非 SELECT 语句的 select_type 值对于受影响的表显示语句类型。例如，对于 DELETE 语句，select_type 为 DELETE。

table (JSON 命名：table_name)
输出行所引用的表的名称。它也可以是以下值之一：
- <union M,N>：该行指的是 id 值为 M 和 N 的行的并集
- <derived N>：该行指的是 id 值为 N 的行的派生表结果。例如，派生表可能来自 FROM 子句中的子查询。
- <subquery N>：该行指的是 id 值为 N 的行的物化子查询的结果。参见 Section 8.2.2.2, “Optimizing Subqueries with Materialization”.
partitions (JSON 命名: partitions)
将从中匹配记录的分区。对于未分区的表，该值为NULL。参见 Section 22.3.5, “Obtaining Information About Partitions”
type (JSON 命名: access_type)
联接类型。对于不同类型的描述，参见 EXPLAIN 联接类型 。
possible_keys (JSON 命名: possible_keys)
possible_keys 列表示 MySQL 可以从中选择查找该表中的行的索引。请注意，此列完全独立于 EXPLAIN 输出中显示的表的顺序。这意味着 possible_keys 中的一些键在实际中可能无法使用生成的表顺序。
如果此列为 NULL（或在 JSON 格式的输出中为 undefined），则不存在相关索引。在这种情况下，您可以通过检查 WHERE 子句来确认它是否引用了一列或多列适合的索引，从而提高查询的性能。如果是，请创建一个适当的索引，并再次使用 EXPLAIN 检查查询。参见 Section 13.1.8, “ALTER TABLE Statement”。
要查看表的索引，请使用 SHOW INDEX FROM tbl_name。
key (JSON 命名: key)
key 列表示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用 possible_keys 中的其中一个索引来查找行，那么该索引将作为键值列出。
key 列命中的索引可能是不在 possible_keys 列值中的索引。如果 possible_keys 中的所有索引都不适合用于查找行，但查询语句中所有 selected 列是其他索引的列，则可能会发生这种情况。也就是说，被命中的索引覆盖了 selected 列，因此尽管它不用于决定要检索哪些行，但扫描索引比扫描数据行效率更高。
对于 InnoDB，二级索引可能覆盖了 selected 列，即使查询选择了主键索引，因为 InnoDB 将主键值与每个二级索引一起存储。如果 key 列的值为 NULL，MySQL 就找不到可用于更有效地执行查询的索引。
要强制 MySQL 使用或忽略 possible_keys 列中列出的索引，请在查询语句中使用 FORCE INDEX、USE INDEX 或 IGNORE INDEX。参见 Section 8.9.4, “Index Hints”。
对于 MyISAM 表，执行 ANALYZE TABLE 可以帮助优化器选择更好的索引。对于 MyISAM 表，myisamchk --analyze 也执行同样的操作。参见 Section 13.7.2.1, “ANALYZE TABLE Statement”，以及 Section 7.6, “MyISAM Table Maintenance and Crash Recovery”。
key_len (JSON 命名: key_length)
key_len 列表示 MySQL 决定使用的键的长度。key_len 的值使您能够确定 MySQL 实际使用了多部分键的多少部分。如果 key 列显示 NULL，那么 key_len 列也显示 NULL。
由于键存储格式的原因，可以为 NULL 的列的键长度比 NOT NULL 列的键长一个。
ref (JSON 命名: ref)
ref 列表示将哪些列或常量与 key 列中命中的索引进行比较，从表中选择行时。
如果值是 func，则使用的值是某个函数的结果。要查看是哪个函数，可以在 EXPLAIN 后面使用 SHOW WARNINGS 来查看扩展的 EXPLAIN 输出。函数实际上可能是一个运算符，例如算术运算符。
rows (JSON 命名: rows)
rows 列表示 MySQL 认为执行查询必须检索的行数。
对于 InnoDB 表，这个数字是一个估计值，可能并不总是准确的。
filtered (JSON 命名: filtered)
filtered 列表示按表条件筛选的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。值从 100 开始递减意味着过滤量递增。rows 显示估计的检索行数，rows × filtered 显示与后续表联接的行数。例如，如果 rows 为 1000，filtered 为50.00（50%），则与后续表联接的行数是 1000 × 50% = 500。
Extra (JSON 命名: none)
此列包含有关MySQL如何解析查询的附加信息。有关不同值的描述，参见 EXPLAIN 额外信息。
没有单个 JSON 属性对应于 Extra 列；但是，此列中可能出现的值会暴露在 JSON 属性或作为 message 属性的文本。

EXPLAIN 联接类型

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

system
该表只有一行（=系统表）。这是 const 联接类型的一种特殊情况。
const
该表最多有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将此行中列的值视为常量。const类型的表非常快，因为它们只读取一次。
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
对于前一个表中的每一个行联合体，都会从该表中读取一行。除了 system 和 const 类型之外，这是最好的联接类型。当联接使用了索引的所有部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，就会使用它。
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
对于前一个表中的每一个行联合体，都会从此表中读取索引值匹配的所有行。如果联接仅使用键的最左边前缀，或者键不是 PRIMARY KEY 或 UNIQUE 索引（换句话说，如果联接不能根据键值选中一个单行），则使用 ref。如果所使用的键只匹配少量行，则这是一个好的联接类型。
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
联接是使用 FULLTEXT 索引执行的。
ref_or_null
这种联接类型类似于 ref，但 MySQL 对包含 NULL 值的行进行了额外的搜索。这种联接类型优化最常用于解析子查询。在以下示例中，MySQL 可以使用 ref_or_null 联接来处理 ref_table：

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

参见 Section 8.2.1.13, “IS NULL Optimization”

index_merge
此联接类型表示使用了索引合并优化。在这种情况下，输出行中的 key 列包含所使用索引的列表，key_len 包含所使用的索引的最长键部分的列表。有关详细信息，参见 Section 8.2.1.3, “Index Merge Optimization”。
unique_subquery
此类型将替换以下形式的某些 IN 子查询的 eq_ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

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

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

value IN (SELECT key_column FROM single_table WHERE some_expr)

range
只检索给定范围内的行，使用索引选择行。输出行中的 key 列指示使用的索引。key_len 包含已使用的最长键部分。此类型的 ref 列为 NULL。
当使用 =、<>、>、>=、<、<=、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
index 联接类型与 ALL 相同，只是扫描的是索引树。发生在两种情况：
- 如果查询的索引是覆盖索引，并且可以用于满足表中所需的所有数据，则只扫描索引树。在这种情况下，Extra 列显示 Using index。仅索引扫描通常比 ALL 快，因为索引的大小通常小于表数据。
- 使用从索引中读取数据来执行全表扫描，以按索引顺序查找数据行。Uses index 不显示在 Extra 列中。
  当查询仅使用单个索引一部分的列时，MySQL 可以使用此联接类型。
ALL
对前一个表中的每一个行联合体都进行全表扫描。这通常是不好的，但是如果该表是第一个未被标记为 const 的表时除外，在所有其他情况下通常非常糟糕。通常，您可以通过添加索引来避免 ALL，基于以前表中的常数值或列值启用表中的行检索。

EXPLAIN 额外信息

EXPLAIN 输出的 Extra 列包含有关 MySQL 如何解析查询的附加信息。以下列表说明了可以在此列中显示的值。每一项还指示 JSON 格式输出的哪个属性显示 Extra 值。对于其中的一些，有一个特定的属性。其它的显示为 message 属性的文本。
如果您想使查询尽可能快，小心 Using filesort 和 Using temporary 的 Extra 列值，或者在 JSON 格式的 EXPLAIN 输出中，等于 true 的 using_filesort 和 using_temporary_table 属性。

Child of ‘table’ pushed join@1 (JSON: message text)
该表被引用为联接 table 的子表，该联接可以向下推送到NDB内核。仅在启用下推联接时应用于NDB群集。有关更多信息和示例，参见 ndb_join_pushdown 服务器系统变量的描述。
const row not found (JSON property: const_row_not_found)
对于 SELECT … FROM tbl_name 这样的查询，表为空。
Deleting all rows (JSON property: message)
对于 DELETE，一些存储引擎（如 MyISAM）支持一种执行方法，该方法可以简单快速地删除表的所有行。如果引擎使用此优化，将显示此 Extra 值。
Distinct (JSON property: distinct)
MySQL 正在寻找不同的值，因此在找到第一个匹配的行后，它将停止为当前行联合体搜索更多的行。
FirstMatch(tbl_name) (JSON property: first_match)
半联接 FirstMatch 联接方式快捷策略用于 tbl_name。
Full scan on NULL key (JSON property: message)
当优化器不能使用索引查找访问方法时，子查询优化作为后备策略会出现这种情况。
Impossible HAVING (JSON property: message)
HAVING 子句始终为 false，并且不能选择任何行。
Impossible WHERE (JSON property: message)
WHERE 子句始终为 false，并且不能选择任何行。
Impossible WHERE noticed after reading const tables (JSON property: message)
MySQL 已经读取了所有 const 类型（和 system 类型）的表，并注意到 WHERE 子句总是 false。
LooseScan(m…n) (JSON property: message)
使用半联接 LooseScan 策略。m 和 n 是键成员编号。
No matching min/max row (JSON property: message)
没有一行满足 SELECT MIN(…) FROM … WHERE condition 这样的查询条件
no matching row in const table (JSON property: message)
对于具有联接的查询，存在一个空表或没有满足唯一索引条件的行的表。
No matching rows after partition pruning (JSON property: message)
对于 DELETE 或 UPDATE，优化器在分区修剪后没有发现要删除或更新的内容。它在意义上类似于 SELECT 语句的 Impossible WHERE。
No tables used (JSON property: message)
查询没有 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 能够对查询进行 LEFT JOIN 优化，并且在找到一行符合 LEFT JON 条件后，不会为上一个行联合体检索该表中的更多行。以下是一个可以通过这种方式优化的查询类型的示例：

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 中实际匹配的行数。

Plan isn’t ready yet (JSON property: none)
当优化器在命名连接中尚未完成为执行的语句创建执行计划时，EXPLAIN FOR CONNECTION 会出现此值。如果执行计划输出包含多行，则根据优化器确定完整执行计划的进度，其中任何一行或所有行都可能具有此 Extra 值。
Range checked for each record (index map: N) (JSON property: message)
MySQL 没有发现好的索引可以使用，但发现一些索引在前面表的列值是已知的之后可能被使用。对于前面表中的每一个行联合体，MySQL 都会检查是否可以使用 range 或 index_merge 的访问方法来检索行。这不是很快，但比执行完全没有索引的联接更快。适用性标准参见 Section 8.2.1.2, “Range Optimization” 和 Section 8.2.1.3, “Index Merge Optimization” 所述，但前面表的所有列值都是已知的并被视为常数的除外。
索引以 1 开头进行编号，其顺序与表的 SHOW INDEX 所示的顺序相同。index map 值 N 是指示哪些索引是候选索引的位掩码值。例如，值 0x19（二进制 11001）表示考虑索引 1、4 和 5。
Scanned N databases (JSON property: message)
这指示在处理 INFORMATION_ SCHEMA 表的查询时服务器执行了多少目录扫描，如 Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries” 所述。N 的值可以是 0、1 或 all。
Select tables optimized away (JSON property: message)
优化器确定1）应该最多返回一行，2）要生成该行，必须读取一组具有确定性的行。如果在优化阶段可以读取要读取的行（例如，通过读取索引行），则在查询执行期间无需读取任何表。
当查询被隐式分组（包含聚合函数但没有 GROUP BY 子句）时，第一个条件就满足了。当对所使用的每个索引执行一行查找时，满足第二个条件。读取的索引数决定了要读取的行数。
斟酌以下隐式分组查询：

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

假设 MIN(c1) 可以通过读取一个索引行来检索，MIN(c2) 可以通过从不同的索引读取一行来检索。也就是说，对于 c1 和 c2 列，存在一个索引，其中该列是索引的第一列。在这种情况下，通过读取两个确定的行，返回一行。
如果要读取的行不具有确定性，则不会出现此 Extra 值。请斟酌以下查询：

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

假设 (c1, c2) 是一个覆盖索引。使用此索引，必须扫描 c1<=10 的所有行，以找到最小 c2 值。相比之下，请斟酌以下查询：

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

在这种情况下，c1＝10 的第一个索引行包含最小 c2 值。生成返回的行只需读取一行。
对于每个表保持精确行计数的存储引擎（如 MyISAM，但不是 InnoDB），对于缺少 WHERE 子句或始终为 true 且没有 GROUP BY 子句的 COUNT(*) 查询，可能会出现此 Extra 值。（这是一个隐式分组查询的实例，其中存储引擎影响是否可以读取确定数量的行。）

Skip_open_table, Open_frm_only, Open_full_table (JSON property: message)
这些值指示了应用于 INFORMATION_ SCHEMA 表的查询的文件打开优化，如 Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries” 所述。
- Skip_open_table：不需要打开表文件。通过扫描数据库目录，该信息已在查询中可用。
- Open_frm_only：只需要打开表的 .frm 文件。
- Open_full_table：未优化的信息查找。.frm、.MYD 和 .MYI 文件都必须打开。
Start temporary, End temporary (JSON property: message)
这表示临时表使用半联接 Duplicate Weedout 策略。
unique row not found (JSON property: message)
对于 SELECT … FROM tbl_name 这样的查询，没有行满足表 UNIQUE 索引或 PRIMARY KEY 的条件。
Using filesort (JSON property: using_filesort)
MySQL 必须做一个额外的过程来查明如何按排序顺序检索行。排序是通过根据联接类型遍历所有行，并存储排序键和指向 WHERE 子句匹配的所有行的指针来完成的。然后对键进行排序，并按排序顺序检索行。参见 Section 8.2.1.14, “ORDER BY Optimization”
Using index (JSON property: using_index)
只使用索引树中的信息从表中检索列信息，没有进行额外的查找去读取实际行。当查询仅使用单个索引的一部分列时，可以使用此策略。
对于具有用户定义的聚合的索引的 InnoDB 表，即使 Extra 列中没有 Using index，也可以使用该索引。比如这种情况，当 type 列为 index 并且 key 列为 PRIMARY 时。
Using index condition (JSON property: using_index_condition)
通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。通过这种方式，索引信息被用来推迟（“下推”）读取完整的表行，除非有必要。参见 Section 8.2.1.5, “Index Condition Pushdown Optimization”
Using index for group-by (JSON property: using_index_for_group_by)
与 Using index 表访问方法类似，Using index for group-by 指示 MySQL 找到了一个索引，该索引可用于检索 group by 或 DISTINCT 查询的所有列，没有对实际表进行任何额外的磁盘访问。此外，索引是以最有效的方式使用的，因此对于每个组，只读取少量索引条目。有关详细信息，参见 Section 8.2.1.15, “GROUP BY Optimization”。
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) (JSON property: using_join_buffer)
来自前期联接的表被分部分读取到联接缓冲区中，然后使用它们在缓冲区中的行来执行与当前表的联接。（Block Nested Loop）表示使用块嵌套循环算法，（Batched Key Access）表示使用批量键访问算法。也就是说，EXPLAIN 输出的前面行上的表中的键是被缓冲的，并且匹配的行被从显示 Using join buffer 的行所代表的表中批量提取。
在 JSON 格式的输出中，using_join_buffer 的值始终是 Block Nested Loop 或 Batched Key Access 之一。
有关这些算法的更多信息，参见 Block Nested-Loop Join Algorithm，和 Batched Key Access Joins。
Using MRR (JSON property: message)
使用多范围读取优化策略读取表格。参见 Section 8.2.1.10, “Multi-Range Read Optimization”.
Using sort_union(…), Using union(…), Using intersect(…) (JSON property: message)
这些指示了如何为 index_merge 联接类型合并索引扫描的算法详情。参见 Section 8.2.1.3, “Index Merge Optimization”。
Using temporary (JSON property: using_temporary_table)
为了解析查询，MySQL需要创建一个临时表来保存结果。这通常发生在查询包含不同列列表的 GROUP BY 和 ORDER BY 子句。
Using where (JSON property: attached_condition)
WHERE 子句用于限制哪些行与下一个表匹配或发送到客户端。除非您特别打算从表中提取或检索所有行，否则在 Extra 值不是 Using where 并且表联接类型是 ALL 或 index 时，你的查询中可能有错误。
Using where 在 JSON 格式的输出中没有直接对应项；attached_condition 属性包含所使用的任意 WHERE 条件。
Using where with pushed condition (JSON property: message)
本条款仅适用于 NDB 表。这意味着 NDB 集群正在使用条件下推优化来提高无索引列和常量之间直接比较的效率。在这种情况下，条件会“下推”到集群的数据节点，并在所有数据节点上同时进行评估。这消除了在网络上发送不匹配行的需要，并且在可以使用但不使用条件下推的情况下，跟没有使用条件下推的比可以将这种查询的速度提高5到10倍。有关更多信息，参见 Section 8.2.1.4, “Engine Condition Pushdown Optimization”。
Zero limit (JSON property: message)
查询里有 LIMIT 0 子句，无法选择任何行。

EXPLAIN 输出演示

通过取得 EXPLAIN 输出的 rows 列中的值的乘积，可以很好地指示联接的好坏。这应该会告诉您大致有多少行在 MySQL 执行查询时必须被检索。如果您使用 max_join_size 系统变量限制查询，这个行乘积也用于确定要执行哪些多表 SELECT 语句以及中止哪些语句。参见 Section 5.1.1, “Configuring the Server”.
以下示例显示了如何根据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;

对于本例，请做出以下假设：

被比较的列声明如下。

表	列	数据类型
tt	ActualPC	CHAR(10)
tt	AssignedPC	CHAR(10)
tt	ClientID	CHAR(10)
et	EMPLOYID	CHAR(15)
do	CUSTNMBR	CHAR(15)

这些表具有以下索引。

表	索引
tt	ActualPC
tt	AssignedPC
tt	ClientID
et	EMPLOYID(primary key)
do	CUSTNMBR(primary key)

tt.ActualPC 值分布不均匀。

最初，在执行任何优化之前，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)

因为每个表的 type 都是 ALL，所以这个输出指示 MySQL 正在生成所有表的笛卡尔乘积；也就是说，要联合每一行。这需要相当长的时间，因为在每个表中都必须检索行数的乘积。对于手头的案例，为 74 × 2135 × 74 × 3872 = 45,268,558,720 行。如果表更大，你只能想象它需要花费多长时间。
这里的一个问题是，如果列被声明为相同的类型和大小，MySQL可以更有效地使用列上的索引。在这个语境下，如果 VARCHAR 和 CHAR 被声明为相同的大小，则它们被认为是相同的。tt.ActualPC 被声明为 CHAR(10)，而 et.EMPLOYID 是 CHAR(15)，因此存在长度不匹配。
要修复列长度之间的差异，请使用 ALTER TABLE 将 ActualPC 从 10 个字符延长到 15 个字符：

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

现在 tt.ActualPC 和 et.EMPLOYID 都是 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

这并不完美，但要好得多：rows 值的乘积减少了 74 倍。这个版本的执行在几秒钟内。
第二次更改可以为比较条件 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 消除列长度不匹配：

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

修改后，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

在这一点上，查询被几乎尽可能的优化。剩下的问题是，默认情况下，MySQL 假设 tt.ActualPC 列中的值是均匀分布的，而 tt 表的情况并非如此。幸运的是，很容易告诉 MySQL 分析键分布：

mysql> ANALYZE TABLE tt;

使用附加的索引信息，联接是完美的，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

EXPLAIN 输出中的 rows 列是 MySQL 联接优化器的一个有根据的猜测。通过将行乘积与查询返回的实际行数进行比较，检查这些数字是否接近真实值。如果数字差异很大，那么在 SELECT 语句中使用 STRAIGHT_JOIN，并尝试在 FROM 子句中以不同的顺序列出表，可能会获得更好的性能。（但是，STRIGHT_JOIN 可能会阻止使用索引，因为它会禁用半联接转换。参见 Section 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”。）
当 EXPLAIN SELECT 与子查询一起使用时，在某些情况下可能会执行修改数据的语句；有关更多信息，参见 Section 13.2.10.8, “Derived Tables”。

quest94

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL 5.7 EXPLAIN 文档翻译

原文地址：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types拜读 MySQL 英文文档并保留翻译结果，非单纯机翻，会在尽可能保留原文含义的目标下保证语句通顺。能力有限，如有错误的地方欢迎指正。EXPLAIN 语句提供了有关 MySQL 如何执行语句的信息。EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE和UPDATE语句。
复制链接

扫一扫