原文地址: 【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 语句提供有关 MySQL 如何执行目标语句的信息:
如何连接
以及以 何种顺序
连接的信息. 有关使用 EXPLAIN 获取执行计划信息的信息, 请参阅第 8.8.2 节 EXPLAIN 输出格式.FOR CONNECTION connection_id
而不是可解释的语句一起使用时, 它显示在命名连接中执行的语句的执行计划. 请参阅第 8.8.4 节 获取命名连接的执行计划信息.SHOW WARNINGS
可是使 EXPLAIN 生成并显示的附加执行计划信息. 请参阅第 8.8.3 节 扩展 EXPLAIN 输出格式.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 为 SELECT 语句中使用的每个表返回一行信息. 它按照 MySQL 在处理语句时读取它们的顺序排列的. 这意味着 MySQL 从第一个表中读取一行, 然后在第二个表中找到匹配的行, 然后在第三个表中, 以此类推. 处理完所有表后, MySQL 会输出选定的列并通过表列表回溯, 直到找到匹配行较多的表. 从此表中读取下一行, 并继续处理下一个表.
本节介绍由 EXPLAIN 生成的输出列. 后面的部分提供了有关 type
和 Extra 列的附加信息.
EXPLAIN 的每个输出行提供有关一个表的信息. 每一行都包含 表 8.1 解释输出列
中总结的值, 并在表后进行了更详细的描述. 列名显示在表格的第一列; 第二列提供了 FORMAT=JSON
使用时输出中显示的等效属性名称.
列 | JSON 名称 | 含义 |
---|---|---|
id | select_id | SELECT 标识符 |
select_type |
| SELECT 类型 |
table | table_name | 输出行的表 |
partitions | partitions | 匹配的分区 |
type | access_type | 联接 (join) 类型 |
possible_keys | possible_keys | 可供选择的索引 |
key | key | 实际选择的索引 |
key_len | key_length | 所选 key 的长度 |
ref | ref | 与索引比较的列 |
rows | rows | 估计要检查的行数 |
filtered | filtered | 按表条件过滤的行百分比 (过滤得越多说明效果越好) |
Extra |
| 附加信息 |
JSON 格式的输出中的 NULL 不会在 JSON 格式的 EXPLAIN 信息中显示.
table
列显示的是 <unionM,N>
, 表示该行是 M 和 N 在 id 上联合 (union) 的行.select_type 价值 | JSON 名称 | 意义 |
---|---|---|
SIMPLE | 没有任何 | 简单 SELECT(不使用 UNION 或子查询) |
PRIMARY | 没有任何 | 最外层 SELECT |
UNION | 没有任何 | UNION 中的第二个或靠后的 SELECT 的语句 |
DEPENDENT UNION | dependent(true) | UNION 中的第二个或后面 SELECT 的语句, 取决于外部查询 |
UNION RESULT | union_result | UNION 后的结果. |
SUBQUERY | 没有任何 | 子查询中的第一个 SELECT |
DEPENDENT SUBQUERY | dependent(true) | 子查询中第一个 SELECT, 依赖于外部查询 |
DERIVED | 没有任何 | 派生表 |
DEPENDENT DERIVED | dependent(true) | 派生表依赖于另一个表 |
MATERIALIZED | materialized_from_subquery | 物化子查询 |
UNCACHEABLE SUBQUERY | cacheable( false) | 一个子查询, 其结果无法缓存, 必须为外部查询的每一行重新计算 |
UNCACHEABLE UNION | cacheable( false) | UNION 中第二个或靠后的 SELECT, 属于不可缓存子查询 (请参阅 参考资料 UNCACHEABLE SUBQUERY) |
DEPENDENT
一般指关联子查询的使用. 详情见 Section 13.2.11.7 Correlated Subqueries
DEPENDENT SUBQUERY
和计算和 UNCACHEABLE SUBQUERY
的计算不太一样.DEPENDENT SUBQUERY
中的子查询对外部上下文中的不同集合的值只计算一遍. 而 UNCACHEABLE SUBQUERY
, 会对外部上下文中的每一行都重新计算.
<union M,N>
: 该行是指具有 M 和 N 并.
- <derived N>
: 该行是指值为 N 的派生表结果. 例如, 派生表可能来自 FROM 子句中的子查询.
- <subquery N>
: 该行指的是 N 的物化子查询的结果. 请参阅第 8.2.2.2 节 使用物化优化子查询.SHOW INDEX FROM tbl_name
FORCE INDEX
, USE INDEX
或 IGNORE INDEX
.
对于 MyISAM 表, 运行 ANALYZE TABLE
有助于优化器选择更好的索引. 对于 MyISAM 表, myisamchk --analyze
也是如此.EXPLAIN 输出中的 type
列描述了表是如何连接的. 在 JSON 格式的输出中, 则放到了 access_type
属性. 以下列表描述了连接类型, 按从最佳到最差的顺序排列:
SELECT \* FROM tbl\_name WHERE primary\_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
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
. 如果使用的键只匹配几行, 这是一个很好的连接类型.
ref 可用于使用 =
或 <=>
运算符比较的索引列. 在以下示例中, MySQL 可以使用 ref 连接来处理 ref_table:`sql
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:```sql
SELECT \* FROM ref\_table
WHERE key\_column=expr OR key\_column IS NULL;
```请参阅[第 8.2.1.15 节 IS NULL 优化](https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html).
- index\_merge
此连接类型表明使用了索引合并优化. 在这种情况下, key 输出行中的列包含所用索引的列表, 并 key\_len 包含所用索引的最长键部分的列表. 有关更多信息, 请参阅[第 8.2.1.3 节 索引合并优化](https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html).- index\\_subquery
此连接类型类似于 unique\\_subquery. 它替换 IN 子查询, 但它适用于以下形式的子查询中的非唯一索引:```sql
value IN (SELECT key\\_column FROM single\\_table WHERE some\\_expr)
- unique\_subquery
此类型可将通过下面的 IN 子查询替换 eq\_ref:`sql
value IN (SELECT primary\_key FROM single\_table WHERE some\_expr)
- range
仅检索给定范围内的行, 使用索引选择行. 输出行中的 key 列指示使用了哪个索引. key\_len 包含使用的最长的关键部分. 该 ref 列适用 NULL 于这种类型.
range 可以在使用运算符中的任何一个与常量进行比较:`sql
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);
哇偶, 好厉害, 感觉这个得掌握一下哦
输出列 Extra 包含 EXPLAIN 有关 MySQL 如何解析查询的附加信息. 下面的列表解释了可以出现在此列中的值. 每个项目还为 JSON 格式的输出指示哪个属性显示该 Extra 值. 对于其中一些, 有一个特定的属性. 其他显示为 message 属性的文本.
如果您想尽可能快地进行查询, 请注意查看 Extra 值是否包含 Using filesortUsing
还是 Using temporary
.
Extra 提供了更多的信息, 比如是否用了临时表, 是否走了文件排序
SELECT ... FROM tbl_name
的查询, 表是空的.semijoin FirstMatch
连接快捷策略.SELECT MIN(...) FROM ... WHERE condition
EXPLAIN INSERT INTO t SELECT 10 FROM DUAL
会显示这个.sql
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 的所有行.
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 1EXPLAIN 输出中的 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)
使用了子查询的物化.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 值相关联.<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 节 配置服务器.
没有刻意增加 Explain 的使用 Demo, 后续的开发中会找机会验证的.
原文地址: 【MySQL 文档翻译】理解查询计划
欢迎访问我的博客: http://blog.duhbb.com/
本文系外文翻译,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文系外文翻译,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。