EXPLAIN
语句提供了有关 MySQL 如何执行语句的信息。EXPLAIN
适用于 SELECT
、DELETE
、INSERT
、REPLACE
和 UPDATE
语句。
EXPLAIN
为 SELECT
语句中使用的每个表返回一行信息。它按照 MySQL 在处理语句时读取的顺序列出输出中的表。这意味着 MySQL 从第一个表中读取一行,然后在第二个表中找到一个匹配的行,然后再在第三个表中查找,依此类推。当所有表都被处理时,MySQL 输出所选的列,并在表列表中回溯,直到找到一个有更多匹配行的表。(译者注:读取完一个匹配的行组合后,按表列表顺序折返,直至找到一张存在更多匹配行的表)从该表中读取下一行,并继续处理下一个表。
注释
MySQL 工作台具有 可视化解释(Visual Explain) 功能,可提供EXPLAIN
输出的可视化显示。请参阅 教程:使用解释来提高查询性能 。
EXPLAIN 输出列
本节介绍 EXPLAIN
生成的输出列。后面的部分提供了有关类型和 Extra
列的其他信息。
EXPLAIN
的每一个输出行都提供了有关一个表的信息。每一行都包含 表 8.1 “EXPLAIN 输出列” 中总结的值,并在下表中进行了更详细的描述。列名显示在表的第一列中;当使用 FORMAT=JSON
时,第二列提供输出中显示的等效属性名称。
列 | 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 | 所选键的长度 |
ref | ref | 索引比较的列 |
rows | rows | 要检查的行的评估 |
filtered | filtered | 被表条件所过滤的行的百分比 |
Extra | None | 额外信息 |
说明
值为NULL
的 JSON 属性不会显示在JSON
格式的EXPLAIN
输出中。
-
id(JSON 名称:select_id)
SELECT
标识符。这是查询中SELECT
的序列号。如果该行引用其他行的并集结果,则该值可以为NULL
。在这种情况下,table
列显示一个类似于<unionM,N>
的值,表示该行引用id
值为 M 和 N 的行的并集。 -
select_type(JSON 名称:无)
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 派生表 DEPENDENT DERIVED dependent (true) 依赖于另一张表的派生表 MATERIALIZED materialized_from_subquery 物化子查询 UNCACHEABLE SUBQUERY cacheable (false) 无法缓存结果并且必须重新评估外层查询中每一行的子查询 UNCACHEABLE UNION cacheable (false) 在属于一个无法缓存的子查询的 UNION
中的第二个或下一个SELECT
(参考上面的UNCACHEABLE SUBQUERY
)DEPENDENT
通常表示使用相关子查询,参阅 13.2.15.7 相关子查询 。当您使用
EXPLAIN
指定FORMAT=JSON
时,输出没有直接等效于select_type
的单个属性;query_ block
属性对应于给定的SELECT
。 与刚才显示的大多数SELECT
子查询类型等效的属性是可用的(例如MATERIALIZED
对应于materialized_from_subquery
), 并在适当的时候显示。SIMPLE
或PRIMARY
没有等效的 JSON。非
select
语句的select_type
值显示受影响表的语句类型。例如,对于DELETE
语句,select_type
为DELETE
。 -
table(JSON 名称:table_name)
输出行所引用(参照)的表的名称。这也可以是以下值之一:<unionM,N>
:行是指id
值为 M 和 N 的行的并集。<derivedN>
:该行引用id
值为 N 的行的派生表结果。例如,派生表可能来自FROM
子句中的子查询。<subqueryN>
:该行是指id
值为 N 的行的物化子查询的结果。请参阅 8.2.2.2 使用物化优化子查询 。
-
partitions(JSON 名称:partitions)
与查询匹配的记录所属的分区。对于未分区的表,该值为NULL
。参见 24.3.5 获取分区信息 。 -
type (JSON 名称:access_type)
联接类型。有关不同类型的描述,请参阅EXPLAIN联接类型。 -
possible_keys(JSON 名称:possible_ keys)
possible_keys
列表示 MySQL 可以选择从中查找该表中的行的索引。请注意,此列完全独立于EXPLAIN
输出中显示的表的顺序。这意味着possible_keys
中的一些键实际可能不会按生成的表顺序使用。
如果此列为NULL
(或在JSON
格式的输出中未定义),则不存在相关索引。在这种情况下,您可以通过检查WHERE
子句来检查它是否引用了适合进行索引的一个或多个列,从而提高查询的性能。如果是,请创建一个适当的索引,并再次使用EXPLAIN
检查查询。参见 13.1.9 ALTER TABLE 语句 。要查看表的索引,请使用
SHOW INDEX FROM tbl_name
。 -
key(JSON 名称:key)
key
列表示 MySQL 实际决定使用的键(索引)。如果 MySQL 决定使用一个possible_keys
索引来查找行,那么该索引将作为键值列出。key
可能会指定一个不存在于possible_keys
值中的索引。如果所有possible_keys
索引都不适合查找行,但查询选择的所有列都是其他索引的列,则可能会发生这种情况。也就是说,指定的索引覆盖(涵盖)了选定的列,因此尽管它不用于确定要检索哪些行,但索引扫描比数据行扫描更有效。对于 InnoDB ,即使查询也选择主键,二级索引也可能覆盖所选列,因为 InnoDB 将主键值与每个二级索引(项)一起存储。若
key
为NULL
,MySQL 就找不到可用于更有效地执行查询的索引。要强制 MySQL 使用或忽略
possible_keys
列中列出的索引,请在查询中使用FORCE INDEX
,USE INDEX
,或IGNORE INDEX
。 参见 8.9.4 索引提示(hints) 。对于 MyISAM 表,运行
ANALYZE TABLE
可以帮助优化器选择更好的索引。对于 MyISAM 表,myisamchk --analyze
也执行同样的操作。参见 13.1.9 ALTER TABLE 语句 和 7.6 MyISAM 表维护和故障恢复 。 -
key_len(JSON 名称:key_length)
key_len
列表示 MySQL 决定使用的密钥的长度。key_len
的值使您能够确定 MySQL 实际使用多部分密钥的多少部分。如果key
列表示NULL
,那么key_len
列也表示NULL
。由于键存储格式的原因,可以为
NULL
的列的键长度比NOT NULL
列的(键长度)大一。 -
ref(JSON 名称:ref)
ref
列显示将哪些列或常量与键列中指定的索引进行比较,以便从表中选择行。如果该值是
func
,则使用的值是某个函数的结果。要查看哪个函数,请在EXPLAIN
后面使用SHOW WARNINGS
来查看扩展的EXPLAIN
输出。函数实际上可能是一个运算符,例如算术运算符。 -
rows(JSON名称:rows)
rows
列表示 MySQL 认为执行查询必须检查的行数。对于 InnoDB 表,这个数字只是一个估计,可能并不总是准确的。
-
filtered (JSON 名称: filtered)
filtered
列表示按表条件筛选的表行的估计百分比。(译者注:此处应反过来记,表示筛选后留下的行占表行总数的估计百分比。)最大值为 100, 这意味着没有对行进行筛选。从 100 开始递减的值表示过滤量的增加。rows
显示检查的估计行数,rows×filtered
显示与后一张表连接的行数。例如,如果行数为 1000, 过滤后的行数为 50.00(50%),则与后一张表连接的行数是 1000×50%=500 。 -
Extra (JSON name: none)
本列包含有关 MySQL 如何解析查询的其他信息。有关不同值的描述,请参阅下文中的 EXPLAIN 额外信息。不存在对应于
Extra 列
的 JSON 属性;但是,此列中可能出现的值将作为 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;
参阅 8.2.1.15 IS NULL 优化 。
-
index_merge
此联接类型表示使用了索引合并优化。在这种情况下,输出行中的键列包含所使用索引的列表,而key_len
包含所使用的索引的最长键部分的列表。有关更多信息,请参阅 8.2.1.3 索引合并优化 。 -
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
属性。
-
Backward index scan (JSON: backward_index_scan)
优化器能够对 InnoDB 表使用降序索引。与使用索引一起显示。有关更多信息,请参阅 第 8.3.13 节 “降序索引” 。 -
Child of ‘table’ pushed join@1 (JSON: message text)
该表被引用为联接中表的子表,该联接可以向下推送到 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)
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)支持以简单快捷的方式删除所有表行的处理器方法。如果引擎使用此优化,将显示此Extra
值。 -
Distinct (JSON property: distinct)
MySQL 正在寻找不同的值,因此在找到第一个匹配的行后,它将停止为当前行组合搜索更多的行。 -
FirstMatch(tbl_name) (JSON property: first_match)
对 tbl_name 使用 半联接 FirstMatch 联接简化策略。 -
Full scan on NULL key (JSON property: message)
当优化器无法使用索引查找访问方法时,它会作为对子查询优化的后备策略出现。 -
Impossible HAVING (JSON property: message)
HAVING
永远为假,并且无法选择任何行。 -
Impossible WHERE (JSON property: message)
WHERE
永远为假,并且无法选择任何行。 -
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(和 system )表,并察觉WHERE
子句总是假。 -
LooseScan(m…n) (JSON property: message)
使用 半连接宽松扫描( LooseScan )策略 。 m 和 n 表示键部分数量。
-
No matching min/max row (JSON property: message)
没有一行满足诸如SELECT MIN(...) FROM ... WHERE
查询的条件。 -
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 中实际匹配的行数。
在 MySQL 8.0.17 及更高版本中,这也可能表明形式为NOT IN (subquery)
或NOT EXISTS (subquery)
的WHERE
条件已在内部转换为反联接。这将移除子查询,并将其表带入最顶层查询的计划中,从而提供改进的成本计划。通过合并半联接和反联接,优化器可以更自由地对执行计划中的表进行重新排序,在某些情况下会产生更快的计划。
您可以通过在执行EXPLAIN
后检查SHOW WARNINGS
中的Message
列,或在EXPLAIN FORMAT=TREE
的输出中查看何时对给定查询执行反联接转换。
说明
反联接是半联接table_a JOIN table_b ON condition
的补集。反联接返回 table_a 中 满足在 table_b 中找不到符合 condition 的行的所有行。
-
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 访问方法来检索行。这不是很快,但比执行完全没有索引的联接更快。适用性标准如 第 8.2.1.2 节 “范围优化” 和 第 8.2.1.3 节 “索引合并优化” 所述,除了前一张表的所有列值都是已知的并被视为常量。索引以 1 开头进行编号,其顺序与表的
SHOW INDEX
所示的顺序相同。索引映射值 N 是一个候选索引的位掩码值。例如,值 0x19(二进制 11001 )表示考虑索引 1 、4 和 5 。 -
Recursive (JSON property: recursive)
这表示该行应用于 递归公共表表达式(Recursive CTE) 的递归SELECT
部分。参见 第 13.2.20 节 “WITH(公共表表达式)” 。 -
Rematerialize (JSON property: rematerialize)
Rematerialize (X,...)
显示在表 t 的EXPLAIN
行中,其中 X 是当读取 t 的新行时触发其重新物化的任何横向派生表。例如:SELECT ... FROM t, LATERAL (derived table that refers to t) AS dt
每当顶部查询处理 t 的新行时,派生表的内容都会被重新物化以保持最新。
-
Scanned N databases (JSON property: message)
这表示服务器在处理INFORMATION_SCHEMA
表查询时执行的目录扫描次数,如 第8.2.3节 “优化 INFORMATION_SCHEMA 查询” 所述。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
表查询的文件打开优化。-
Skip_open_table:不需要打开表文件。数据字典中已提供该信息。
-
Open_frm_only:表信息只需要读取数据字典。
-
Open_full_table:未优化的信息查找。必须从数据字典中,和通过读取表文件读取表信息。
-
-
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
子句匹配的所有行的指针来完成的。然后键就有序了,并且所有行以排序顺序被取回。参见 第 8.2.1.16 节 “按优化排序” 。 -
Using index (JSON property: using_index)
只使用索引树中的信息从表中检索列信息,而不必进行额外的查找来读取实际行。当查询仅使用作为单个索引一部分的列时,可以使用此策略。对于具有用户定义的聚集索引的 InnoDB 表,即使
Extra
列中没有 Using index ,也会使用该索引。如果类型为 index ,键为PRIMARY
,则会出现这种情况。对于
EXPLAIN FORMAT=TRADITIONAL
和EXPLAIN FORMAT=JSON
,会显示有关所使用的任何覆盖索引的信息。从 MySQL 8.0.27 开始,它也会为EXPLAIN FORMAT=TREE
显示。 -
Using index condition (JSON property: using_index_condition)
通过访问索引元组并首先测试它们来读取表,以确定是否读取完整的表行。通过这种方式,除非必要,否则索引信息用于延迟(“下推”)读取整个表行。见 第 8.2.1.6 节 “索引条件下推优化” 。 -
Using index for group-by (JSON property: using_index_for_group_by)
与使用 Using index 表访问方法类似,Using index for group-by 表示 MySQL 找到了一个索引,该索引可以用于检索GROUP BY
或DISTINCT
查询的所有列,而不需要对实际表进行任何额外的磁盘访问。此外,索引以最有效的方式使用,因此对于每个组,只读取几个索引项。有关详细信息,请参阅 第 8.2.1.17 节 “GROUP BY 优化”。 -
Using index for skip scan (JSON property: using_index_for_skip_scan)
表示使用 跳跃扫描(Skip Scan) 访问方法。请参见 跳跃扫描范围访问方法。 -
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join) (JSON property: using_join_buffer)
来自先前的联接的表被部分读取进 联接缓冲区(Join Buffer) ,然后使用它们的行从缓冲区中执行与当前表的联接。(Block Nested Loop) 表示使用块嵌套循环算法,** (Batched Key Access)** 表示使用批量键访问算法,** (hash join)** 表示使用哈希连接。即,缓冲来自EXPLAIN
输出中前一行上的表中的键,并从出现 Using join buffer 的行所表示的表中批量获取匹配的行。在
JSON
格式的输出中,using_join_buffer
的值始终是 Block Nested Loop 、Batched Key Access 或 hash join 之一。从 MySQL 8.0.18 开始支持使用哈希连接;MySQL 8.0.20 或更高版本的 MySQL 中不再使用块嵌套循环算法。有关这些优化的更多信息,请参阅 第 8.2.1.4 节 “哈希连接优化” 和 块嵌套循环连接算法 。
有关批量键访问算法的信息,请参见 批量键访问联接。
-
Using MRR (JSON property: message)
使用 多范围读(Multi-Range Read) 优化策略读取表格。参见 第 8.2.1.11 节 “多范围读优化” 。 -
Using sort_union(…), Using union(…), Using intersect(…) (JSON property: message)
这些表示显示针对 index_merge 联接类型如何合并索引扫描的特定算法。参见 第 8.2.1.3 节 “索引合并优化”。 -
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 Cluster 正在使用 条件下推(Condition Pushdown) 优化来提高非索引列和常量之间直接比较的效率。在这种情况下,条件被“下推”到集群的数据节点,并在所有数据节点上同时进行评估。这消除了通过网络发送不匹配行的需要,并且在本可以使用条件下推但未使用的情况下,可以将这种查询速度提高 5 到 10 倍。有关更多信息,请参阅 第 8.2.1.5 节“引擎条件下推优化” 。 -
Zero limit (JSON property: message)
该查询有一个LIMIT 0
子句,并且无法查出任何行。
EXPLAIN 输出解释
通过获取 EXPLAIN
输出的 rows
列中的值的乘积,可以很好地理解一个联接有多好。它会大致告诉您 MySQL 必须检查多少行才能执行查询。如果使用 max_join_size
系统变量限制查询,则此行乘积还用于确定要执行哪些多表 SELECT
语句和哪些要中止。请参阅 第 5.1.1 节 “配置服务器” 。
以下示例显示了如何根据 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;
对于本例,做出以下假设:
-
被比较的列声明如下。
Table Column Data Type tt ActualPC CHAR(10) tt AssignedPC CHAR(10) tt ClientID CHAR(10) et EMPLOYID CHAR(15) do CUSTNMBR CHAR(15) -
这些表有以下索引。
Table Index 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)
因为每个表的类型都是 ALL ,所以这个输出表明 MySQL 正在生成所有表的笛卡尔乘积;即行的每一个组合。这需要相当长的时间,因为必须检查每个表中行数的乘积。对于手头的案例,该产品为 74×2135×74×3872=45268558720
行。如果表更大,你只能想象需要多长时间。
这里的一个问题是,如果列被声明为相同的类型和大小,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
这并不完美,但要好得多:行值的乘积缩小了 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
语句中使用 STRIGHT_JOIN
并尝试在 FROM
子句中以不同的顺序列出表,可能会获得更好的性能。(但是,STRIGHT_JOIN
可能会阻止使用索引,因为它禁用了半联接转换。请参阅 第 8.2.2.1 节 “使用半联接转换优化 IN 和 EXISTS 子查询谓词” 。)
在某些情况下,当 EXPLAIN SELECT
与子查询一起使用时,可以执行修改数据的语句;有关更多信息,请参见 第13.2.15.8 节 “派生表” 。