MySQL 执行计划详解 | 京东物流技术团队
1. EXPLAIN 详解
本文基于 MySQL 8.0 编写，理论支持 MySQL 5.0 及更高版本。
2. EXPLAIN 使用
2.1 explain 分析 SQL 的执行计划
2.2 示例
2.3 结果输出展示
3. 结果解读
id：该语句的唯一标识。如果 explain 的结果包括多个 id 值，则数字越大越先执行；而对于相同 id 的行，则表示从上往下依次执行。
select_type：查询类型，有如下几种取值：
table：表示当前这一行正在访问哪张表，如果 SQL 定义了别名，则展示表的别名
partitions：当前查询匹配记录的分区。对于未分区的表，返回 null
type：连接类型，有如下几种取值，性能从好到坏排序如下：
◦system：该表只有一行（相当于系统表），system 是 const 类型的特例
◦const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
◦eq_ref：当使用了索引的全部组成部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 才会使用该类型，性能仅次于 system 及 const。
◦ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
TIPS 最左前缀原则
指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是： WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用该索引； WHERE column1 = 2、WHERE column1 = 1 AND column3 = 3 就无法匹配该索引。
◦fulltext：全文索引 ref_or_null：该类型类似于 ref，但是 MySQL 会额外搜索哪些行包含了 NULL。这种类型常见于解析子查询
◦index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引
◦unique_subquery：该类型和 eq_ref 类似，但是使用了 IN 查询，且子查询是主键或者唯一索引。例如：value IN (SELECT primary_key FROM single_table WHERE some_expr)
◦index_subquery：和 unique_subquery 类似，只是子查询使用的是非唯一索引。value IN (SELECT key_column FROM single_table WHERE some_expr)
◦range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有 BETWEEN 子句或 WHERE 子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
◦index：全索引扫描，和 ALL 类似，只不过 index 是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
▪如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain 的 Extra 列的结果是 Using index。index 通常比 ALL 快，因为索引的大小通常小于表数据。
▪按索引的顺序来查找数据行，执行了全表扫描。此时，explain 的 Extra 列的结果不会出现 Uses index。
◦ALL：全表扫描，性能最差。
possible_keys：展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。
key：表示 MySQL 实际选择的索引 key_len：索引使用的字节数。由于存储格式，当字段允许为 NULL 时，key_len 比不允许为空时大 1 字节。
◦key_len 计算公式：https://www.cnblogs.com/gomysql/p/4004244.html
ref：表示将哪个字段或常量和 key 列所使用的字段进行比较。如果 ref 是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在 EXPLAIN 语句之后紧跟一个 SHOW WARNING 语句。
rows：MySQL 估算会扫描的行数，数值越小越好。
filtered：表示符合查询条件的数据百分比，最大 100。用 rows × filtered 可获得和下一张表连接的行数。例如 rows = 1000，filtered = 50%，则和下一张表连接的行数是 500。
TIPS
在 MySQL 5.7 之前，想要显示此字段需使用 explain extended 命令； MySQL.5.7 及更高版本，explain 默认就会展示 filtered
Extra：展示有关本次查询的附加信息，取值如下：
◦Child of ‘table’ pushed join@1：此值只会在 NDB Cluster 下出现。
◦const row not found：例如查询语句 SELECT … FROM tbl_name，而表是空的
◦Deleting all rows：对于 DELETE 语句，某些引擎（例如 MyISAM）支持以一种简单而快速的方式删除所有的数据，如果使用了这种优化，则显示此值
◦Distinct：查找 distinct 值，当找到第一个匹配的行后，将停止为当前行组合搜索更多行
◦FirstMatch(tbl_name)：当前使用了半连接 FirstMatch 策略，详见 https://mariadb.com/kb/en/firstmatch-strategy/ ，翻译 https://www.cnblogs.com/abclife/p/10895624.html
◦Full scan on NULL key：子查询中的一种优化方式，在无法通过索引访问 null 值的时候使用
◦Impossible HAVING：HAVING 子句始终为 false，不会命中任何行
◦Impossible WHERE：WHERE 子句始终为 false，不会命中任何行
◦Impossible WHERE noticed after reading const tables：MySQL 已经读取了所有 const（或 system）表，并发现 WHERE 子句始终为 false
◦LooseScan(m…n)：当前使用了半连接 LooseScan 策略，详见 https://mariadb.com/kb/en/loosescan-strategy/ ，翻译 http://www.javacoder.cn/?p=39
◦No matching min/max row：没有任何能满足例如 SELECT MIN(…) FROM … WHERE condition 中的 condition 的行
◦no matching row in const table：对于关联查询，存在一个空表，或者没有行能够满足唯一索引条件
◦No matching rows after partition pruning：对于 DELETE 或 UPDATE 语句，优化器在 partition pruning（分区修剪）之后，找不到要 delete 或 update 的内容
◦No tables used：当此查询没有 FROM 子句或拥有 FROM DUAL 子句时出现。例如：explain select 1
◦Not exists：MySQL 能对 LEFT JOIN 优化，在找到符合 LEFT JOIN 的行后，不会为上一行组合中检查此表中的更多行。例如：
◦假设 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 WARNING 语句，并分析结果中的 Message 列，从而查看何时对该查询执行了反联接转换。
◦Note：两表关联只返回主表的数据，并且只返回主表与子表没关联上的数据，这种连接就叫反连接 Plan isn’t ready yet：使用了 EXPLAIN FOR CONNECTION，当优化器尚未完成为在指定连接中为执行的语句创建执行计划时， 就会出现此值。MySQL 没有找到合适的索引去使用，但是去检查是否可以使用 range 或 index_merge 来检索行时，会出现此提示。index map N 索引的编号从 1 开始，按照与表的 SHOW INDEX 所示相同的顺序。 索引映射值 N 是指示哪些索引是候选的位掩码值。 例如 0x19（二进制 11001）的值意味着将考虑索引 1、4 和 5。
Plan isn’t ready yet：使用了 EXPLAIN FOR CONNECTION，当优化器尚未完成为在指定连接中为执行的语句创建执行计划时， 就会出现此值。
◦MySQL 没有找到合适的索引去使用，但是去检查是否可以使用 range 或 index_merge 来检索行时，会出现此提示。index map N 索引的编号从 1 开始，按照与表的 SHOW INDEX 所示相同的顺序。 索引映射值 N 是指示哪些索引是候选的位掩码值。 例如 0x19（二进制 11001）的值意味着将考虑索引 1、4 和 5。
◦示例：下面例子中，name 是 varchar 类型，但是条件给出整数型，涉及到隐式转换。图中 t2 也没有用到索引，是因为查询之前我将 t2 中 name 字段排序规则改为 utf8_bin 导致的链接字段排序规则不匹配。
结果：
Recursive：出现了递归查询。详见“WITH (Common Table Expressions)”
Rematerialize：用得很少，使用类似如下 SQL 时，会展示 Rematerialize
Scanned N databases：表示在处理 INFORMATION_SCHEMA 表的查询时，扫描了几个目录，N 的取值可以是 0，1 或者 all。详见 “Optimizing INFORMATION_SCHEMA Queries”
Select tables optimized away：优化器确定：①最多返回 1 行；②要产生该行的数据，要读取一组确定的行，时会出现此提示。一般在用某些聚合函数访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询时展示，例如下面这条 SQL。
Skip_open_table， Open_frm_only， Open_full_table：这些值表示适用于 INFORMATION_SCHEMA 表查询的文件打开优化；
◦Skip_open_table：无需打开表文件，信息已经通过扫描数据字典获得
◦Open_frm_only：仅需要读取数据字典以获取表信息
◦Open_full_table：未优化的信息查找。表信息必须从数据字典以及表文件中读取 Start temporary, End temporary：表示临时表使用 Duplicate Weedout 策略，详见 https://mariadb.com/kb/en/duplicateweedout-strategy/ ，翻译 https://www.cnblogs.com/abclife/p/10895531.html
unique row not found：对于形如 SELECT … FROM tbl_name 的查询，但没有行能够满足唯一索引或主键查询的条件
Using filesort：当 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain 不会显示的告诉客户端用哪种排序。官方解释：“MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”Using index：仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。例如：explain SELECT id FROM t
Using index condition：表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。通过这种方式，除非有必要，否则索引信息将可以延迟“下推”读取整个行的数据。详见 “Index Condition Pushdown Optimization” 。例如：
MySQL 分成了 Server 层和引擎层，下推指的是将请求交给引擎层处理。理解这个功能，可创建所以 INDEX (zipcode, lastname, firstname)，并分别用如下指令，
开或者关闭索引条件下推，并对比：
的执行结果。index condition pushdown 从 MySQL 5.6 开始支持，是 MySQL 针对特定场景的优化机制，感兴趣的可以看下 https://blog.51cto.com/lee90/2060449
Using index for group-by：数据访问和 Using index 一样，所需数据只须要读取索引，当 Query 中使用 GROUP BY 或 DISTINCT 子句时，如果分组字段也在索引中，Extra 中的信息就会是 Using index for group-by。详见 “GROUP BY Optimization”
Using index for skip scan：表示使用了 Skip Scan。详见 Skip Scan Range Access Method
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)：使用 Block Nested Loop 或 Batched Key Access 算法提高 join 的性能。详见 https://www.cnblogs.com/chenpingzhao/p/6720531.html
Using MRR：使用了 Multi-Range Read 优化策略。详见 “Multi-Range Read Optimization”Using sort_union(…), Using union(…), Using intersect(…)：这些指示索引扫描如何合并为 index_merge 连接类型。详见 “Index Merge Optimization”
Using temporary：为了解决该查询，MySQL 需要创建一个临时表来保存结果。如果查询包含不同列的 GROUP BY 和 ORDER BY 子句，通常会发生这种情况。
Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现 using where 信息
Using where with pushed condition：仅用于 NDB
Zero limit：该查询有一个 limit 0 子句，不能选择任何行 explain SELECT name FROM resource_template limit 0
4. 扩展的 EXPLAIN
EXPLAIN 可产生额外的扩展信息，可通过在 EXPLAIN 语句后紧跟一条 SHOW WARNING 语句查看扩展信息。
TIPS
在 MySQL 8.0.12 及更高版本，扩展信息可用于 SELECT、DELETE、INSERT、REPLACE、UPDATE 语句；在 MySQL 8.0.12 之前，扩展信息仅适用于 SELECT 语句； 在 MySQL 5.6 及更低版本，需使用 EXPLAIN EXTENDED xxx 语句；而从 MySQL 5.7 开始，无需添加 EXTENDED 关键词。
使用示例：
由于 SHOW WARNING 的结果并不一定是一个有效 SQL，也不一定能够执行（因为里面包含了很多特殊标记）。特殊标记取值如下：
◦<auto_key>：自动生成的临时表 key
◦<cache>(expr)：表达式（例如标量子查询）执行了一次，并且将值保存在了内存中以备以后使用。对于包括多个值的结果，可能会创建临时表，你将会看到 <temporary table> 的字样
◦<exists>(query fragment)：子查询被转换为 EXISTS
◦<in_optimizer>(query fragment)：这是一个内部优化器对象，对用户没有任何意义
◦<index_lookup>(query fragment)：使用索引查找来处理查询片段，从而找到合格的行
◦<if>(condition, expr1, expr2)：如果条件是 true，则取 expr1，否则取 expr2
◦<is_not_null_test>(expr)：验证表达式不为 NULL 的测试
◦<materialize>(query fragment)：使用子查询实现
◦materialized-subquery.col_name：在内部物化临时表中对 col_name 的引用，以保存子查询的结果
◦<primary_index_lookup>(query fragment)：使用主键来处理查询片段，从而找到合格的行
◦<ref_null_helper>(expr)：这是一个内部优化器对象，对用户没有任何意义
◦/* select#N */ select_stmt：SELECT 与非扩展的 EXPLAIN 输出中 id=N 的那行关联
◦outer_tables semi join (inner_tables)：半连接操作。inner_tables 展示未拉出的表。详见 “Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations”
◦<temporary table>：表示创建了内部临时表而缓存中间结果
当某些表是 const 或 system 类型时，这些表中的列所涉及的表达式将由优化器尽早评估，并且不属于所显示语句的一部分。但是，当使用 FORMAT=JSON 时，某些 const 表的访问将显示为 ref。
5. 估计查询性能
多数情况下，你可以通过计算磁盘的搜索次数来估算查询性能。对于比较小的表，通常可以在一次磁盘搜索中找到行（因为索引可能已经被缓存了），而对于更大的表，你可以使用 B-tree 索引进行估算：你需要进行多少次查找才能找到行：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
在 MySQL 中，index_block_length 通常是 1024 字节，数据指针一般是 4 字节。比方说，有一个 500,000 的表，key 是 3 字节，那么根据计算公式 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次搜索。
该索引将需要 500,000 * 7 * 3/2 = 5.2MB 的存储空间（假设典型的索引缓存的填充率是 2/3），因此你可以在内存中存放更多索引，可能只要一到两个调用就可以找到想要的行了。
但是，对于写操作，你需要四个搜索请求来查找在何处放置新的索引值，然后通常需要 2 次搜索来更新索引并写入行。
前面的讨论并不意味着你的应用性能会因为 log N 而缓慢下降。只要内容被 OS 或 MySQL 服务器缓存，随着表的变大，只会稍微变慢。在数据量变得太大而无法缓存后，将会变慢很多，直到你的应用程序受到磁盘搜索约束（按照 log N 增长）。为了避免这种情况，可以根据数据的增长而增加 key 的。对于 MyISAM 表，key 的缓存大小由名为 key_buffer_size 的系统变量控制，详见 Section 5.1.1, “Configuring the Server”
6. 参考文档
作者：京东物流 柳宏
来源：京东云开发者社区 自猿其说 Tech 转载请注明来源
版权声明: 本文为 InfoQ 作者【京东科技开发者】的原创文章。
原文链接:【http://xie.infoq.cn/article/e890604140ab51714000a03e2】。文章转载请联系作者。
拥抱技术，与开发者携手创造未来！ 2018-11-20 加入
我们将持续为人工智能、大数据、云计算、物联网等相关领域的开发者，提供技术干货、行业技术内容、技术落地实践等文章内容。京东云开发者社区官方网站【https://developer.jdcloud.com/】，欢迎大家来玩
评论