【MySQL 文档翻译】理解查询计划

原文地址: 【MySQL 文档翻译】理解查询计划

欢迎访问我的博客: http://blog.duhbb.com/

官方文档

MySQL 官方文档地址: 8.8 Understanding the Query Execution Plan

引言

MySQL 优化器会根据 SQL 语句中的表, 列, 索引和 WHERE 子句中的条件的详细信息, 使用许多技术来有效地执行 SQL 查询. 可以在不读取所有行的情况下对一个巨大的表执行查询; 可以在不比较每个行组合的情况下执行涉及多个表的连接. 优化器选择执行最有效查询的一组操作称为 查询执行计划 (query execution plan), 也称为 EXPLAIN plan. 你的目标是认识到 EXPLAIN 计划表明查询已优化好, 如果发现一些低效的操作, 可以通过学习 SQL 语法和索引技术来改进查询计划.

使用 EXPLAIN 优化查询

EXPLAIN 语句提供有关 MySQL 如何执行目标语句的信息:

• EXPLAIN 可以与 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 语句一起使用.
• 当 EXPLAIN 与可解释语句 (explainable statement) 一起使用时, MySQL 会显示来自优化器的有关语句执行计划的信息. 也就是说, MySQL 解释了它将如何处理该语句, 包括有关表 如何连接 以及以 何种顺序 连接的信息. 有关使用 EXPLAIN 获取执行计划信息的信息, 请参阅第 8.8.2 节 EXPLAIN 输出格式.
• 当 EXPLAIN 与 FOR CONNECTION connection_id 而不是可解释的语句一起使用时, 它显示在命名连接中执行的语句的执行计划. 请参阅第 8.8.4 节 获取命名连接的执行计划信息.
• 对于 SELECT 语句, 使用 SHOW WARNINGS 可是使 EXPLAIN 生成并显示的附加执行计划信息. 请参阅第 8.8.3 节 扩展 EXPLAIN 输出格式.
• EXPLAIN 对于检查涉及分区表的查询很有用. 请参阅第 24.3.5 节 获取有关分区的信息.
• FORMAT 选项可用于选择输出格式. TRADITIONAL 以表格格式显示输出. 如果没有 FORMAT 选项, 这是默认设置. 当 FORMAT 的选项值为 JSON 可以显示 JSON 格式的信息.

在 EXPLAIN 的帮助下, 可以查看应该在哪里为表添加索引, 以便通过使用索引查找行来更快地执行语句. 您还可以使用 EXPLAIN 检查优化器是否以最佳顺序连接表. 要提示优化器使用与语句中表命名顺序相对应的连接顺序, 请以 SELECT STRAIGHT_JOIN 语句开头, 而不是 SELECT. (请参阅 第 13.2.10 节 SELECT 语句.) 但是, STRAIGHT_JOIN 可能会阻止使用索引, 因为它禁用了 半连接转换. 看第 8.2.2.1 节 使用半连接转换优化 IN 和 EXISTS 子查询谓词.

优化器跟踪 有时可能会提供比 EXPLAIN 更详细的信息. 但是, 优化器跟踪格式和内容可能会因版本而异. 有关详细信息, 请参阅 MySQL 内部: 跟踪优化器.

如果您认为应该用到的索引在查询计划中确没有被使用, 请运行 ANALYZE TABLE 以更新表统计信息, 例如键的基数, 这可能会影响优化器所做的选择. 请参阅第 13.7.3.1 节 ANALYZE TABLE 语句.

Explain 的输出格式

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

Explain 输出的列

本节介绍由 EXPLAIN 生成的输出列. 后面的部分提供了有关 type 和 Extra 列的附加信息.

EXPLAIN 的每个输出行提供有关一个表的信息. 每一行都包含 表 8.1 解释输出列 中总结的值, 并在表后进行了更详细的描述. 列名显示在表格的第一列; 第二列提供了 FORMAT=JSON 使用时输出中显示的等效属性名称.

JSON 格式的输出中的 NULL 不会在 JSON 格式的 EXPLAIN 信息中显示.

• id

SELECT 标识符. 这是查询中 SELECT 的序号. 如果该值是 NULL 则表示该行引用其他行的联合结果 (union result). 在这种情况下, table 列显示的是 <unionM,N> , 表示该行是 M 和 N 在 id 上联合 (union) 的行.

• select_type

SELECT 的类型, 可以是下表中显示的任何一种.

DEPENDENT 一般指关联子查询的使用. 详情见 Section 13.2.11.7 Correlated Subqueries

DEPENDENT SUBQUERY 和计算和 UNCACHEABLE SUBQUERY 的计算不太一样.DEPENDENT SUBQUERY 中的子查询对外部上下文中的不同集合的值只计算一遍. 而 UNCACHEABLE SUBQUERY, 会对外部上下文中的每一行都重新计算.

• table

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

- `<union M,N>`: 该行是指具有 M 和 N 并.
- `<derived N>`: 该行是指值为 N 的派生表结果. 例如, 派生表可能来自 FROM 子句中的子查询.
- `<subquery N>`: 该行指的是 N 的物化子查询的结果. 请参阅[第 8.2.2.2 节 使用物化优化子查询](https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html).

• partitions

查询将匹配记录的分区. 如果该值为 NULL 则表明是非分区表.

• type

联接类型. 有关不同类型的描述, 请参阅 EXPLAIN 连接类型. 联接类型还挺重要的.

• possible_keys (重要)

该 possible_keys 列指示 MySQL 可以选择从中查找此表中的行的索引. 请注意, 此列完全独立于输出中显示的表格顺序 EXPLAIN. 这意味着某些键在 possible_keys 实际中可能无法与生成的表顺序一起使用.
如果此列是 NULL(或在 JSON 格式的输出中未定义), 则没有相关索引. 在这种情况下, 您可以通过检查 WHERE 子句来检查它是否引用了适合索引的某些列或列, 从而提高查询的性能. 如果是这样, 请创建一个适当的索引并 EXPLAIN 再次检查查询.
要查看表有哪些索引, 请使用: SHOW INDEX FROM tbl_name

• 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.
对于 MyISAM 表, 运行 ANALYZE TABLE 有助于优化器选择更好的索引. 对于 MyISAM 表, myisamchk --analyze 也是如此.

• key_len

key_len 列指示 MySQL 决定使用的 key 的长度. key_len 的值使您能够确定 MySQL 实际使用的 multi-part key 的多少部分. 如果 key 是 NULL, key_len 也是 NULL.
由于 key 存储格式的原因, 可以为空的 key 的长度要不不能为空的 key 的长度多 1.
(这个应该是联合索引可以用到前几个的情况)

• ref

ref 列显示将哪些列或常量与 key 列中指定的索引进行比较以从表中选择行.
如果值为 func, 则使用的值是某个函数的结果. 要查看哪个功能, 请在 EXPLAIN 后使用 SHOW WARNINGS 以 EXPLAIN 扩展输出. 该函数实际上可能是一个运算符, 例如算术运算符.

• rows

rows 列指示 MySQL 认为它必须检查以执行查询的行数.
对于 InnoDB 表格, 这个数字是一个估计值, 可能并不总是准确的.

• filtered

filtered 列指示按表条件过滤的表行的估计百分比. 最大值为 100, 这意味着没有过滤行. 从 100 开始减小的值表示过滤量增加. rows 显示检查的估计行数, $rows × filtered$ 显示与下表连接的行数. 例如, 如果 rows 是 1000 并且 filtered 是 50.00 (50%), 则要与下表连接的行数是 $1000 × 50% = 500$.

• Extra

此列包含有关 MySQL 如何解析查询的附加信息. 有关不同值的描述, 请参阅 EXPLAIN 额外信息.

解释连接类型

EXPLAIN 输出中的 type 列描述了表是如何连接的. 在 JSON 格式的输出中, 则放到了 access_type 属性. 以下列表描述了连接类型, 按从最佳到最差的顺序排列:

• system

该表只有一行 (= 系统表). 这是 const 连接类型的一个特例.

• const

该表最多有一个匹配行, 在查询开始时读取. 因为只有一行, 所以这一行中列的值可以被优化器的其余部分视为常量. const 表非常快, 因为它们只被读取一次.
当您将 PRIMARY KEY 或 UNIQUE 索引的所有部分与常量值进行比较时就是使用 const. 在以下查询中, 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 索引)

对于先前表中的每个行组合, 从该表中读取具有匹配索引值的所有行. 如果连接仅使用键的最左前缀或键不是 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 加了一个可以为 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 输出行中的列包含所用索引的列表, 并 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 于这种类型.
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 通过添加索引来避免基于先前表中的常量值或列值从表中检索行.

哇偶, 好厉害, 感觉这个得掌握一下哦

解释额外信息

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

如果您想尽可能快地进行查询, 请注意查看 Extra 值是否包含 Using filesortUsing 还是 Using temporary.

Extra 提供了更多的信息, 比如是否用了临时表, 是否走了文件排序

• Backward index scan

优化器能够对 InnoDB 表使用降序索引. 与 Using index 一起显示. 有关更多信息, 请参阅第 8.3.13 节 降序索引.

• Child of 'table' pushed join@1(不重要)

该表被引用为 table 可以下推到 NDB 内核的连接中的子项. 仅在启用下推连接时适用于 NDB Cluster.ndb_join_pushdown.

• const row not found

对于诸如 SELECT ... FROM tbl_name 的查询, 表是空的.

• Deleting all rows(删除的时候是否走捷径?)

对于 DELETE, 一些存储引擎 (例如 MyISAM) 支持一种处理程序方法, 该方法以简单快速的方式删除所有表行. 如果引擎使用此优化, Extra 则会显示此值.

• Distinct(是否 distinct?)

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

• FirstMatch(tbl_name)

对 tble_name 这个表使用了 semijoin FirstMatch 连接快捷策略.

• Full scan on NULL key

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

• Impossible HAVING

HAVING 子句始终为 false, 不能选择任何行.

• Impossible WHERE

WHERE 子句始终为 false, 不能选择任何行.

• Impossible WHERE noticed after reading const tables

MySQL 已读取所有 const(and system) 表并注意到该 WHERE 子句始终为 false.

• LooseScan(m..n)

使用半连接 LooseScan 策略. m 并且 n 是关键部件号.

• No matching min/max row

没有行满足查询的条件, 例如: SELECT MIN(...) FROM ... WHERE condition

• no matching row in const table

对于带有连接的查询, 有一个空表或没有满足唯一索引条件的行的表.

• No matching rows after partition pruning

对于 DELETE or UPDATE, 优化器在分区修剪后没有发现要删除或更新的内容. 它与 SELECT 语句 Impossible WHERE 的含义相似.

• No tables used

查询没有 FROM 子句, 或有 FROM DUAL 子句.
对于 INSERT 或 REPLACE 语句, 当没有 SELECT 部分时 EXPLAIN 显示此值. 执行 EXPLAIN INSERT INTO t SELECT 10 FROM DUAL 会显示这个.

• Not exists

MySQL 能够对 LEFT JOIN 查询进行优化, 并且在找到与条件匹配的行后, 不会检查该表中的前一行组合的更多行. 以下是可以通过这种方式优化的查询类型的示例:

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

假设 t2.id 定义为 NOT NULL. 在这种情况下, MySQL 扫描 t1 并查找行 t2 中与 t1.id 值相等的行. 如果 MySQL 在 t2 中找到匹配的行 , 它就知道 t2.id 永远不可能为 NULL, 并且不会扫描具有相同值的其余行. 换句话说, 对于 t1 中的每一行, MySQL 只需要在 t2 中进行一次查找, 而不管在 中实际匹配了多少行.
在 MySQL 8.0.17 及更高版本中, 这也可以指示 WHERE 中的 NOT IN (subquery) 或 NOT EXIST (subquery) 已在内部转换为反连接 (antijoin). 这将移除子查询并将其表带入最顶层查询的计划中, 从而提供改进的成本计划. 通过合并半连接 (semijoins) 和反连接 (antijoins), 优化器可以更自由地重新排序执行计划中的表, 在某些情况下会产生更快的计划.(牛逼了)
您可以通过 EXPLAIN 后执行 SHOW WARNINGS 后的 Message 列或在 EXPLAIN FORMAT=TREE 的输出中查看对给定查询是否执行反连接转换.

> 笔记
反连接是半连接 table_a 和 table_b 在 condition 上的补充. 反连接返回 table_a 中所有没有在 condition 上匹配 table_b 的所有行.

• Plan isn't ready yet

EXPLAIN FOR CONNECTION 当优化器尚未完成为在命名连接中执行的语句创建执行计划时, 会出现此值. 如果执行计划输出包含多行, 则任何或所有行都可能具有此 Extra 值, 具体取决于优化器在确定完整执行计划时的进度.

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

MySQL 没有找到可以使用的好的索引, 但发现某些索引可能会在之前表中的列值已知后使用. 对于前面表格中的每个行组合, MySQL 检查是否可以使用 range 或者 index_merge 访问方法来检索行. 这不是很快, 但比执行完全没有索引的连接要快. 应用的准则在 第 8.2.1.2 节 范围优化 和第 8.2.1.3 节 索引合并优化 中所述, 但上表的所有列值都是已知的并被视为常量.
索引从 1 开始编号, 顺序与表中所示的相同 SHOW INDEX. 索引映射值 N 是指示哪些索引是候选索引的位掩码值. 例如, 值 0x19(二进制 11001) 表示考虑索引 1,4 和 5.

• Recursive

这表明该行适用于 SELECT 递归公用表表达式的递归部分. 请参见第 13.2.15 节 WITH(公用表表达式).

• Rematerialize

Rematerialize (X,...) 显示在 EXPLAIN T 中, 其中是在读取 X 新行时触发重新实现的任何横向派生表. 例如:

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

每次 t 顶部查询处理新行时, 派生表的内容都会重新实现以使其保持最新状态.

• Scanned N databases

这表示在处理表查询时服务器执行了多少目录扫描 INFORMATION_SCHEMA, 如第 8.2.3 节 优化 INFORMATION_SCHEMA 查询 中所述. N 的值可以是 0, 1 或 all.

• Select tables optimized away

优化器确定 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 子句的 COUNT(*) 或始终为真且没有 GROUP BY 子句的查询, Extra 可能会出现此值 .(这是一个隐式分组查询的实例, 其中存储引擎影响是否可以读取确定的行数.)

• Skip_open_table, Open_frm_only, Open_full_table

这些值表示适用于 INFORMATION_SCHEMA 表查询的文件打开优化.

- Skip_open_table: 表格文件不需要打开. 该信息已从数据字典中获得.
- Open_frm_only: 表信息只需要读取数据字典.
- Open_full_table: 未优化的信息查找. 表信息必须从数据字典中读取并通过读取表文件.

• Start temporary, End temporary

这表明临时表用于 semijoin Duplicate Weedout 策略.

• unique row not found

对于诸如 SELECT ... FROM tbl_name 的查询, 没有行满足 UNIQUE 索引或 PRIMARY KEY.

• Using filesort(重要)

MySQL 必须做一个额外的过程来找出如何按排序顺序检索行. 排序是通过根据连接类型遍历所有行并存储排序键和指向与 WHERE 子句匹配的所有行的行的指针来完成的. 然后对键进行排序, 并按排序顺序检索行. 请参见第 8.2.1.16 节 排序优化.

• Using index

仅使用索引树中的信息从表中检索列信息, 而无需执行额外的查找来读取实际行. 当查询仅使用属于单个索引的列时, 可以使用此策略.
对于 InnoDB 具有用户定义的聚集索引的表, 即使 Extra 这一列中不存在 Using index 也可以使用该索引 Extra. 如果 type 是 index 而 key PRIMARY 就是这种情况.

• Using index condition

通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表. 这样, 除非有必要, 否则索引信息用于延迟 ("下推") 读取全表行. 请参阅第 8.2.1.6 节 索引条件下推优化.

• 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

表示使用了 跳过扫描访问 (Skip Scan) 方法. 请参阅跳过扫描范围访问方法.

• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join)

来自早期连接的表被部分读入连接缓冲区, 然后从缓冲区中使用它们的行来执行与当前表的连接. (Block Nested Loop) 指示使用块嵌套循环算法,(Batched Key Access) 指示使用批量 key 访问算法, (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 中不使用 Block Nested-Loop 算法. 有关这些优化的更多信息, 请参阅第 8.2.1.4 节,"哈希连接优化"和 块嵌套循环连接算法.
有关批量密钥访问算法的信息, 请参阅批量密钥访问连接.

• Using MRR

使用多范围读取优化策略读取表. 请参见第 8.2.1.11 节 多范围读取优化.

• Using sort_union(...), Using union(...), Using intersect(...)

这些指示显示了如何为 index_merge 连接类型索引扫描的特定算法. 请参阅第 8.2.1.3 节 索引合并优化.

• Using temporary

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

• Using where

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

• Using where with pushed condition

此项仅适用于 NDB 表.

• Zero limit

该查询有一个 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;

对于此示例, 作出以下假设:

• 被比较的列已声明如下.

• 这些表具有以下索引.

• 这些 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 分析 key 分布很容易:

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 连接优化器的有根据的猜测. rows 通过将产品与查询返回的实际行数进行比较, 检查这些数字是否更接近事实. 如果数字完全不同, 您可能会通过 STRAIGHT_JOIN 在 SELECT 语句中使用并尝试在 FROM 子句中以不同的顺序列出表来获得更好的性能.(但是, STRAIGHT_JOIN 可能会阻止使用索引, 因为它禁用了 半连接转换. 请参阅第 8.2.2.1 节 使用半连接转换优化 IN 和 EXISTS 子查询谓词.

在某些情况下, 当 EXPLAIN SELECT 与子查询一起使用时, 可以执行修改数据的语句; 有关更多信息, 请参阅第 13.2.11.8 节 派生表.

扩展 EXPLAIN 输出格式

该 EXPLAIN 语句产生额外的信息, 这些信息不是 EXPLAIN 输出的一部分, 但可以通过在 EXPLAIN 后接着 SHOW WARNINGS 语句来查看. 从 MySQL 8.0.12 开始, 扩展信息可用于 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 语句. 在 8.0.12 之前, 扩展信息仅适用于 SELECT 语句.

SHOW WARNINGS 输出的 Message 显示优化器在 SELECT 语句如何限定表名和列名, SELECT 应用重写和优化规则后的样子, 以及可能有关优化过程的其他注释.

EXPLAIN 后的 SHOW WARNINGS 仅针对 SELECT 语句生成扩展信息. 其他可解释语句 (DELETE, INSERT, REPLACE 和 UPDATE) 则显示的空结果.

这是扩展 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)

因为显示的语句 SHOW WARNINGS 可能包含特殊标记以提供有关查询重写或优化器操作的信息, 所以该语句不一定是有效的 SQL, 并且不打算执行. 输出还可能包含带有 Message 值的行, 这些值提供有关优化器所采取的操作的附加非 SQL 解释性说明.

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

• <auto_key>

为临时表自动生成的键.

• <cache>(expr)

表达式 (例如标量子查询) 执行一次, 结果值保存在内存中供以后使用. 对于由多个值组成的结果, 可以创建一个临时表并 <temporary table> 改为显示.

• <exists>(query fragment)

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

• <in_optimizer>(query fragment)

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

• <index_lookup>(query fragment)

使用索引查找来处理查询片段以查找符合条件的行.

• <if>(condition, expr1, expr2)

如果条件为真, 则计算为 expr1, 否则 为 expr2.

• <is_not_null_test>(expr)

验证表达式不为 NULL.

• <materialize>(query fragment)

使用了子查询的物化.

• 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.

• <primary_index_lookup>(query fragment)

使用主键查找来处理查询片段以查找符合条件的行.

• <ref_null_helper>(expr)

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

• /* select#N */ select_stmt

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

• outer_tables semi join (inner_tables)

半连接操作. inner_tables 显示未拉出的表. 请参阅第 8.2.2.1 节,"使用半连接转换优化 IN 和 EXISTS 子查询谓词".

• <temporary table>

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

当某些表属于 const 或 system 类型时, 涉及这些表中的列的表达式由优化器提前求值, 而不是显示语句的一部分. 但是, 使用 FORMAT=JSON 时, 某些 const 表访问会显示为使用 const 值的 ref 访问.

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

暂时用不上, 这部分地址: 获取命名连接的执行计划信息

估计查询性能

在大多数情况下, 您可以通过计算磁盘寻道 (disk seeks) 次数来估计查询性能. 对于小型表, 通常可以在一次磁盘查找中找到一行 (因为索引可能已缓存). 对于更大的表, 您可以估计, 使用 B-tree 索引, 您需要这么多次查找才能找到一行:

$$ \frac{log(row\\\_count)}{log(index\\\_block\\\_length / 3 * 2 / (index\\\_length + data\\\_pointer\\\_length))} + 1 $$

在 MySQL 中, 索引块通常为 1024 字节, 数据指针通常为 4 字节. 对于一个 500000 行的表, 键值长度为 3 个字节 (大小为 MEDIUMINT), 公式表示 $log(500000)/log(1024/3\*2/(3+4)) + 1= 4$ seeks.

该索引需要大约 $500000 \* 7 \* 3/2 = 5.2MB$ 的存储空间 (假设典型的索引缓冲区填充率为 2/3), 因此您可能在内存中有很多索引, 因此只需要一两次调用读取数据以查找行.

但是, 对于写入, 您需要四个查找请求来查找放置新索引值的位置, 通常需要两次查找来更新索引并写入行.

前面的讨论并不意味着您的应用程序性能会以 $log(N)$ 的速度缓慢下降. 只要一切都被操作系统或 MySQL 服务器缓存, 随着表变大, 事情只会稍微变慢. 在数据变得太大而无法缓存后, 事情开始变得慢得多, 直到您的应用程序仅受磁盘搜索 (增加 log N) 的约束. 为避免这种情况, 请随着数据的增长而增加 key 的缓存大小. 对于 MyISAM 表, 键缓存大小由 key_buffer_size 系统变量控制. 请参见第 5.1.1 节 配置服务器.

笔者的验证 Demo

没有刻意增加 Explain 的使用 Demo, 后续的开发中会找机会验证的.

原文地址: 【MySQL 文档翻译】理解查询计划

欢迎访问我的博客: http://blog.duhbb.com/