MySQL-explain笔记

2020 年 1 月 23 日
筆記

使用 EXPLAIN 查看执行计划， 5.6后可以加参数 EXPLAIN FORMAT=JSON xxx输出json格式的信息。

FORMAT=JSON格式的内容可参考8.8.2 EXPLAIN Output Format中的相关部分。

+----+-------------+-------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------+  | id | select_type | table | partitions | type  | possible_keys              | key               | key_len | ref                  | rows  | filtered | Extra                             |  +----+-------------+-------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------+  |  1 | SIMPLE      | bc    | NULL       | index | NULL                       | PRIMARY           | 62      | NULL                 | 1444 |      100 | Using index                        |  |  1 | SIMPLE      | gt    | NULL       | ref   | idx_tmp_1,idx_tmp_2        | idx_tmp_2         | 83      | func                 |  233 |      100 | Using index condition; Using where |  |  1 | SIMPLE      | oc    | NULL       | ref   | idx_tmp_class_1            | idx_tmp_class_1   | 9       | dwdb.gt.condition_id |    1 |      100 | NULL                               |  +----+-------------+-------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------+  3 rows in set

1. id

SELECT操作的序号(select_id)，用于标识查询中执行SELECT子句或操作表的顺序。永远是id值越大，优先级越高，越先被执行。

id值存在的情况

子查询id顺序递增：内层的select语句一般会按顺序增长编号，对应于其在原始语句中的位置。
id值为NULL：该行引用其他行的并集结果。
每行都显示1：在语句中没子查询或关联查询，只有唯一的select。
NULL是最后执行,如合并结果集等。

执行顺序

id相同，可以认为是一组，由上至下顺序执行。
在所有组中，id值越大，优先级越高，越先执行。

2. select_type

查询的类型，用于区分是简单查询还是复杂查询，以及其中的具体类型。

类型	含义
SIMPLE	简单查询，不使用 UNION 或子查询。
PRIMARY	最外层查询，当包含 UNION 或者子查询等任何复杂的子部分，最外层的查询被标为 PRIMARY。
UNION	位于 UNION 中第二个或其以后的SELECT语句，第一个就被标记为 PRIMARY；如果是union位于from中则标记为 DERIVED。
DEPENDENT UNION	位于UNION 中的第二个或其以后SELECT语句，同时该语句依赖外部的查询。
UNION RESULT	UNION的结果。
SUBQUERY	子查询中的第一个SELECT语句。
DEPENDENT SUBQUERY	子查询中的第一个SELECT语句，同时该语句依赖外部的查询。
DERIVED	派生表。位于form中的子查询，MySQL会将其结果存放在一个临时表中，即这里的派生表。
MATERIALIZED	物化子查询，优化 FROM/IN 子句中的子查询。
UNCACHEABLE SUBQUERY	其结果无法缓存的子查询，必须针对外部查询的每一行重新进行评估。
UNCACHEABLE UNION	位于 UNION 中的第二个或其以后SELECT语句，与上面的 UNCACHEABLE SUBQUERY 类似，属于结果无法缓存的子查询。

2.1 DEPENDENT SUBQUERY 与 UNCACHEABLE SUBQUERY

两者都是子查询。

对于 DEPENDENT SUBQUERY ，子查询对于外部上下文里每一个集合中不同的变星值仅仅重新计算一次。
对于 UNCACHEABLE SUBQUERY ，子查询对于外部上下文里的每一行都会重新计算一次。

2.2 子查询缓存

子查询的可缓存性不同于查询结果在查询缓存中的缓存（8.10.3.1 How the Query Cache Operates）。

子查询缓存在查询执行期间发生，而查询缓存仅在查询执行完成后才用于存储结果。

2.3 非查询语句(non-SELECT statements)

非查询语句(non-SELECT statements)的 select_type 值的显示受影响表的语句类型(statement type)。如，DELETE 语句的 select_type 值为 DELETE。

2.4 MATERIALIZED

物化子查询，也常被翻译为“具体化”。通过生成子查询结果作为临时表（通常在内存中）来加快查询的执行速度。

2.4.1 优化方式

MySQL第一次需要子查询结果时，会将结果具体化为临时表。
任何随后的需要结果的时间里，MySQL都会再次引用临时表。
优化器可以使用哈希索引为表编制索引，以使查找快速、低成本。
索引包含唯一值，以消除重复项并使表更小。

物化子查询尽可能使用内存中存储临时表，若表太大，会退回到磁盘上存储(可参考8.4.4 Internal Temporary Table Use in MySQL)。

2.4.2 与不使用物化的比较

物化子查询使用临时表避免了优化器的某些重写，并使得子查询尽可能只执行一次，而不是对外部查询的每一行执行一次。

如果未使用物化，则优化器有时会将不相关的子查询重写为相关的子查询。例如，以下IN子查询是不相关的（where_condition 仅涉及from t2和not中的列t1）：

SELECT * FROM t1  WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

优化器可能将此重写为 EXISTS相关子查询：

SELECT * FROM t1  WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

2.4.3 开启方式

使用物化子查询前需开启 optimizer_switch 系统变量的 materialization：

set optimizer_switch='materialization=on';

开启之后，物化子查询适用于出现在任何地方的子查询谓词(in the select list，WHERE， ON，GROUP BY， HAVING，或ORDER BY)。

详情可见8.2.2.2 Optimizing Subqueries with Materialization

3. table

输出行所引用的表的名称，显示这一行的数据是关于哪张表的。不一定是真实的表名/别名，也可以是以下值之一：

<unionM,N>：ID值是M和N的两行union产生的结果集(The row refers to the union of the rows with id values of M and N.)，。
<derivedN>：ID值n的行的派生表结果，派生表可能是从FROM子句中的一个子查询生成的(The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.)。
<subqueryN>：ID值n的行的物化子查询结果。

4. partitions

给定表所使用的分区，未分区的表该值为NULL。

5. type

连接类型，即MySQL如何查找表中的行。-待补

system>const>eq_ref>ref>range>index>all

类型	说明
all	全表扫描，性能最差。
index	和全表扫描一样，只是扫描的索引,主要优点就是避免了排序。
range	范围扫，仅检索给定范围内的行。
ref	对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。
eq_ref	对于先前表中的每行组合，从此表中读取一行。除了 system和 const类型，这是最好的联接类型。当使用主键索引(PRIMARY KEY)或不为空的唯一索引(UNIQUE NOT NULL index)
const	该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为只读取一次。
system	const的一种特殊情况，表仅有一行满足条件

5.1 index

index时存在两种情况：

如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra列显示为 Using index。仅索引扫描(index-only)通常比全表扫描(ALL)更快，因为索引的大小通常小于表数据。
使用对索引的读取执行全表扫描，以按索引顺序查找数据行。 Uses index不会出现在Extra列中。

5.2 range

当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range

5.3 ref

如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。

如果使用的键仅仅匹配少量行，该联接类型是不错的。

ref可以用于使用=或<=>操作符的带索引的列。

5.4 Null

意味说mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）。

网上好多的资料有这么一条，但暂时未在官方文档8.8.2 EXPLAIN Output Format一节中找到相关信息。

6. possible_keys

显示查询可以使用的索引,这是基于查询访问的列和使用的比较操作符来判断的。这个列表在优化过程的早期创建,因此有些列出来的索引对后续优化是没有用的。

7. key

MySQL实际决定使用的键（索引）

如果为NULL可能是没有索引或建了没用，即索引失效。
如果MySQL决定使用possible_keys中的索引之一来查找行，值为该索引。
key也可能会是 possible_keys中之外的索引，如某个覆盖索引。当possible_keys中没有适合的索引，但是该查询的所有列都是某个索引的列，即索引覆盖了选定的列，此时尽管不使用索引来确定要检索的行，但索引扫描依旧比数据行扫描更高效。
要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

8. key_len

MySQL决定使用的key的长度,即MySQL在索引里使用的字节数，它包含了在索引字段中可能的最大长度,而不是表中使用的实际字节数。

key_len 值能够使你判定 MySQL 实际使用了 multiple-part key 中的多少个 part，即在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。

如果key是NULL，则长度为NULL

由于key存储格式的原因，key的值可以为NULL的列比不能为NULL列长度多一字节。

8.1 key_len的长度计算公式

key_len = L * C （+N）（+S）

L：索引列所定义字段类型字符长度
C：不同编码下一个字符所占的字节数（如utf8=3，gbk=2）
N：字段为空标记，占1字节（非空字段此标记不占用字节）
S：索引列字段是否定长（int、char、datetime为定长，varchar为不定长），不定长字段类型需记录长度信息，占2字节

9. ref

显示了之前的表在key列记录的索引中查找值所用的列或常量，即显示索引key使用了之前表的哪一列或常量。。

如果值为func，则使用的值是某些函数的结果。要查看是哪个功能，请在 EXPLAIN 命令之后接着使用SHOW WARNINGS 命令查看扩展EXPLAIN 输出。该函数实际上可能是算术运算符之类的运算符。

10. rows

该rows列指示MySQL认为执行查询必须检查的行数。对于InnoDB表，此数字是估计值，可能并不总是准确的。原则上值越小越好。

11. filtered

将被表条件过滤的表行的估计百分比,最大值为100，这表示未过滤行。值从100减小表示过滤量增加。

该值和rows列的值一起使用，rows × filtered显示将与下表(比当前id列小的表)连接的行数。例如，rows 为 1000 且 filtered 为50.00（50％），则与下表连接的行数为1000×50％= 500。

12. Extra

Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

类型	说明
Using filesort	MYSQL需要进行额外的步骤来发现如何对返回的行排序。排序是通过根据联接类型遍历所有行并存储与WHERE子句匹配的所有行的排序key和指向该行的指针来完成的,然后对key进行排序，并按排序顺序检索行。看到这个的时候，查询需要优化。
Using temporary	用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists	MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
Using index	仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。对于InnoDB具有用户定义的聚集索引的表，如果type为index,key为PRIMARY，即使Extra中没有Using index，该index 依旧可以被使用
Using index condition	通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。这样，除非有必要，否则索引信息将用于延迟（“下推”）整个表行的读取。
Using where	使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。除非想返回表中的全部行，否则如果查询中的Extra值不是 Using where且表联接类型为ALL或Index ，则查询中可能会有问题。
Using join buffer	使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接
Impossible where	该WHERE子句始终为false，不能选择任何行。
Select tables optimized away	在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
Distinct	在找到第一匹配的元组后即停止找同样值的动作
Range checked for each Record(index map: N)	没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
FirstMatch(tbl_name)	5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个。
LooseScan(m..n)	使用半连接的LooseScan策略。 m和n是key的行号。 5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个。

Using filesort

仅仅表示没有使用索引的排序，事实上filesort这个名字很糟糕，并不意味着在硬盘上排序，filesort与文件无关。因此消除Using filesort的方法就是让查询sql的排序走索引。

filesort使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其中filesort可以使用的内存空间大小为参数sort_buffer_size的值，默认为2M。当排序记录太多sort_buffer_size不够用时，mysql会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序。

Using index condition

这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。

请参见Section 8.2.1.5, “Index Condition Pushdown Optimization

Using join buffer

Using join buffer (Block Nested Loop)

使用块嵌套循环算法。

大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

BNL 算法对系统的影响主要包括三个方面： – 可能会多次扫描被驱动表，占用磁盘 IO 资源； – 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源； – 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

Using join buffer (Batched Key Access)

使用批量密钥访问算法

BNL转BKA

想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

JOIN 优化

BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
BNL 算法效率低，建议你都尽量转成 BKA 算法。
优化的方向就是给被驱动表的关联字段加上索引；
基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。
8.8.2 EXPLAIN Output Format
MySQL高级第二章——索引优化分析
MySQL EXPLAIN详解
mysql explain详解
MySQL 执行计划中Extra(Using where,Using index,Using index condition,Using index,Using where)的浅析
MySQL优化之EXPLAIN详解
MySQL key_len 大小的计算
性能优化系列七：SQL优化
Using temporary与Using filesort