[大数据]MySQL - Explain详解

1. 前言

Server version: 5.7.36 MySQL Community Server (GPL)
mysql官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

explain（执行计划），使用 explain 关键字可以模拟优化器执行 sql 查询语句，从而知道 MySQL 是如何处理 sql 语句。explain 主要用于分析查询语句或表结构的性能瓶颈。

explain select * from user where phone = '15233658888';

explain 输出内容大致如下：

2. explain的作用

通过 explain + sql 语句可以知道如下内容：

1. 表的读取顺序（对应id）

2. 数据读取操作的操作类型（对应select_type）

3. 哪些索引可以使用（对应possible_keys）

4. 哪些索引被实际使用（对应key）

5. 表直接的引用（对应ref）

6. 每张表有多少行被优化器查询（对应rows）

3. explain的内容

3.1 id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，该字段通常与table字段搭配来分析。

1. id 相同时，执行顺序由上至下

2. 如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

3. id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

3.2 select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：

1. SIMPLE

简单的 select 查询，查询中不包含子查询或 union 查询。

2. PRIMARY

查询中若包含任何复杂的子部分，最外层查询为 PRIMARY，也就是最后加载的就是 PRIMARY。

3. UNION

此查询是 UNION 中的第二个或后面的SELECT语句
若第二个 select 出现在 union 后，则被标记为 UNION，若 union 包含在 from 子句的子查询中，外层 select 将被标记为 DERIVED。

4. DEPENDENT UNION

UNION 中的第二个或后面的SELECT语句，取决于外面的查询。

5. UNION RESULT

UNION的结果。

6. SUBQUERY

子查询中的第一个SELECT，结果不依赖于外部查询
在 select 或 where 列表中包含了子查询，就为被标记为 SUBQUERY。

7. DERIVED

在 from 列表中包含的子查询会被标记为 DERIVED，MySQL 会递归执行这些子查询，将结果放在临时表中。

3.3 table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如tab_a，tab_a，也可能是第几步执行的结果的简称

3.4 partitions

官方定义为The matching partitions（匹配的分区），对于非分区表值为null。

3.5 type

表示查询所使用的访问类型，type 的值主要有八种，该值表示查询的 sql 语句好坏，从最好到最差依次为：
NULL > system > const > eq_ref > ref > range > index > ALL

1. NULL

MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

2. system

system 是 const 的特例，表里只有一条元组匹配时为 system。

3. const

MySQL 能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，表最多有一个匹配行，读取1次，速度比较快。

4. eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；
primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

5. ref

使用非唯一索引或非唯一索引前缀进行的查找；
相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

6. range

按指定范围（如in()、between and、>、<、>=等，但是前提是此字段要建立索引）来检索，很常见。
如：select * from student where id < 5，id上要有索引。

7. index

全"表"扫描，但是是在索引树中扫描，通常比 ALL 快，因为索引文件通常比数据文件小，index 扫描是通过二叉树的方式扫描，而 ALL 是扫描物理表。也就是说虽然 ALL 和 index 都是读全表，但 index 是从索引中读取的，而 ALL 是从硬盘中读的。
例如：select name from student，但 name 字段上需要建立索引，也就是查询的字段属于索引中的字段。

8. ALL

全表扫描，扫描完整的物理表，此时就需要优化了。

3.6 possible_keys

指出 MySQL 能在该表中可能使用的索引，显示的是索引的名称，多个索引用逗号隔开。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

3.7 key

MySQL决定实际用到的索引，显示的是索引的名称，多个索引用逗号隔开。

如果没有使用索引，则该列是 NULL。如果想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。

3.8 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。

不损失精确性的情况下，长度越短越好。

key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

举例来说，film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是4字节。通过结果中的 key_len = 4 可推断出查询使用了第一个列：film_id 列来执行索引查找。

计算key_len的公式：

varchr(10)变长字段且允许NULL = 10 * ( character set：utf8=3, gbk=2, latin1=1) + 1(NULL) + 2(变长字段)

varchr(10)变长字段且不允许NULL = 10 *( character set：utf8=3, gbk=2, latin1=1) + 2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3, gbk=2, latin1=1)+1(NULL)

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3, gbk=2, latin1=1)

3.9 ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值，常见的有：const（常量），字段名（例：film.id）

3.10 rows

显示 MySQL 认为执行查询时必须要返回的行数，可结合 type 和 key 分析，没有用上索引的情况下，会全表扫描。rows 的值越小越好，说明检索的数据少，注意这个不是结果集里的行数。

3.11 filtered

给出了一个百分比的值，这个百分比值和 rows 列的值一起使用，可以估计出那些将要和执行计划中的前一个表（前一个表就是指 id 列的值比当前表的 id 小的表）进行连接的行的数目。

3.12 Extra

此字段显示一些额外的信息，但是此字段的部分值具有优化的参考意义：

1. using where

查询的列未被索引覆盖，where 筛选条件非索引的前导列。

2. using index：

表示使用了覆盖索引。这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要直接访问表数据。

查询的列被索引覆盖，并且 where 筛选条件是索引的前导列， 是性能高的表现。一般是使用了覆盖索引（索引包含了所有查询的字段）。对于innodb来说，如果是辅助索引性能 会有不少提高。

3. Using index condition：

查询的列不完全被索引覆盖，where条件中是一个查询的范围。

4. NULL

查询的列未被索引覆盖，并且 where 筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过"回表"来实现，不是纯粹地用到了索引，也不是完全没用到索引。

5. using join buffer

这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能

6. using filesort

这是 order by 语句的结果。这可能是一个CPU密集型的过程。using filesort 表示出现了文件内排序，MySQL 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时 MySQL 会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。表示很不好的现象，必须要优化，特别是大表，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。

7. using temporary：

MySQL 需要创建一张临时表来保存中间结果。 也就是说，需要先把数据放到临时表中，然后从临时表中获取需要的数据。出现这种临时表，也是必须需要优化的地方，特别是数据量大的情况。两个常见的原因是在来自不同表的列上使用了 distinct，或者使用了不同的 order by 和 group by 列。

首先是想到用索引来优化。

4. 总结

1. EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；
2. EXPLAIN 不考虑各种 Cache；
3. EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作；
4. 部分统计信息是估算的，并非精确值；
5. EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划。