explain字段详解

XPLAIN适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句。

当EXPLAIN与可解释的语句一起使用时，MySQL 会显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 解释了它将如何处理该语句，包括有关表如何连接以及连接顺序的信息。

因为MySQL底层有SQL优化，因此在一些执行计划查看特定的字段值需要关闭部分优化，具体的优化参数可以参考MySQL 官方文档

set session optimizer_switch='derived_merge=off';  -- 关闭派生表合并优化

查看SQL的执行计划，需要在SQL前加上explain关键字，具体如下：

explain select * from user_role 

执行计划字段描述 

id

ID编号是select的序列号，SQL中有多少select语句就存在多少ID,相同编号执行顺序由上而下，不同编号，值越大优先级越高,如果ID为空，则最后执行

explain select * from `user_role` where user_id IN (select id from `user` where name ='a');

explain select * from  (select remark from `user_role`) a;

explain select * from (select id from `user` union  select user_id from `user_role`) temp;

select_type

• SIMPLE

简单查询，不包含子查询或者union

explain select * from `user` where id=1;

• PRIMARY

复杂查询，是最外层的select；在查询中包含任何复杂的子部分，最外层select都会标记为 PRIMARY

• SUBQUERY

在select后面或者where后面，是子查询中第一个select语句

• DERIVED

派生(衍生)查询，在from 子句中的select语句，这个select语句会将结果集放到临时表中

explain select (select 1 from `user` where id = 1) from (select * from role where id = 1) temp;

• UNION

在 union 中的第二个或之后的 select

explain select id from `user` union all select user_id from `user_role`;

• DEPENDENT UNION

union 中的第二个或之后的select 语句，取决于外面的查询

• DEPENDENT SUBQUERY

子查询中的第一个 select，取决于外面的查询

• UNION RESULT

union的结果集

explain select * from `user` where id in(select id from `user` union select user_id from `user_role`) ;

table

当前行访问的表，其中有具体的表名，别名或者临时表

• <derivedN>

表示从派生表中查询，后面N表示id列编号，如下图：<derived3> 表示select_type是derived且id值是3

• <unionN,M>

在union行中，选择id是N和M的行

• <subqueryN>

同理，引用具体子查询行编号为N的结果

partitions

当前表是否使用分区，如果有用到分区，该字段就会显示具体使用的是什么分区

type

表示关联类型或访问类型，该值决定了mysql查找数据的大概范围

依次从最优到最差分别为:system > const > eq_ref > ref > fulltext > ref_or_null> index_merge > unique_subquery > index_subquery > range > index > ALL 其中红色部分是常用部分，这个字段在优化SQL时，经常使用

• system

system是const的特例，表里只有一行记录(一般是系统表)时标记为system

• const

查询条件是唯一索引或者主键,所以表最多有一个匹配行，读取1次，速度比较快，因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量

• er_ref

主键或者唯一索引的索引所有部分被关联的时候，值是eq_ref，一般是常见于关联查询

• ref

使用普通索引或者唯一索引的部分前缀（如：最左前缀匹配），可能会查询出多条记录

• fulltext

全文索引

• ref_or_null

这种连接类型类似于 ref，但 MySQL额外搜索NULL的数据,这种连接类型优化最常用于解析子查询

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

• index_merge

此连接类型表示使用索引合并优化。在这种情况下，key列输出行中的列包含使用的索引列表,且key_len列包含使用的索引的最长键部分列表

• unique_subquery

该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery

和unique_subquery类似，它取代了in子查询，但它适用于以下形式的子查询中的非唯一索引

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range

范围扫描通常出现在<>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN() 等操作中。使用一个索引来检索给定范围的行。 如果查询范围结果集太大，那也会出现全表扫描的情况

• index

扫描全索引，一般是扫描某个普通索引，只扫描索引树会有如下两种情况：

1. 如果查询使用了覆盖索引，则只扫描索引树，在这样的情况下，Extra列将显示Using index

2. 使用索引顺序来查找数据行来进行全表扫描。Extra列的结果则不会出现Uses index

当查询仅使用属于单个索引的列时，MySQL 可以使用此连接类型。

• all

全表扫描，性能最差，扫描的是聚簇索引，遇到这样的一般通过索引来优化

possible_keys

当前查询能使用哪些索引，有时可能出现 possible_keys 有值，而 key列显示 NULL 的情况，mysql认为索引对此查询帮助不大，选择了全表查询(索引的选择涉及到了成本计算)； 如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 WHERE 子句看是否它引用某些列或适合索引的列来提高查询性能，若没，则可以创建一个合适的索引进行优化

key

当前SQL实际用到的索引,如果没有使用索引，则该列为NULL;若要强制使用索引或者忽略possible_keys列中的索引，可以在查询中使用FORCE INDEX、USE INDEX 或者 IGNORE INDEX

key_len

代表了在索引里面使用的字节数，通过这个值可以算出具体使用了索引中哪些列

• key列为NULL时，那key_len也NULL

• 字段允许为 NULL，需要1字节记录是否为 NULL

• 字符集字节长度utf8=3, gbk=2

• key_len的计算规则

计算时，需要看当前字段是否可以为NULL,如果可以为空需要 + 1， 不可以则不用 + 1

1. char(n): n * 当前字符集字节长度 + 1(如果该字段可以为空则需要，不为空则不用加)

2. varchar(n)：n * 当前字符集字节长度 + 1(如果该字段可以为空则需要，不为空则不用加) + 2(2字节用来存储字符串变长)

3. tinyint:1字节

4. smallint:2字节

5. int:4字节

6. bigint:8字节

7. date:3字节

8. timestamp:4字节

9. datetime:8字节

explain select * from user_role where user_id=1

idx_user_role_id是一个联合索引（userID和roleID）,key_len值为4，表示只用了联合索引中的userID这个前缀索引，如果整个联合索引都有用到，则key_len值为8

ref

表示将哪个字段或常量和key列所使用的字段进行比较，值有可能是常量或者某个字段。

如果ref是一个函数，则使用的值是函数的结果。要查看是哪个函数，则可以在EXPLAIN语句之后紧跟一个SHOW WARNING语句。

rows

mysql估计要读取并检测的行数，对于InnoDB，此数字是一个估计值，可能并不准确

filtered

该值是一个百分比， rows * filtered 可以估算与下表关联的行数，如果 rows是 1000 并且 filtered是 50.00（50%），则与下表连接的行数为 1000 × 50% = 500 执行计划中filtered的计算方式和影响

extra

展示有关本次查询的附加信息，只列了部分类型

• Using index

使用了覆盖索引；查询的列在当前查询的索引树中全部包含，而不需要进行其他查找

• Using where

不查询表的所有数据或者不是仅通过索引就可以获取所有需要的数据，这样的情况就会是Using where

• Using index condition

先按条件过滤索引后找到所有符合索引条件的数据，然后用WHERE子句中的其他条件去过滤这些数据行

• Using temporary

MYSQL需要创建一个临时表来存储结果，用于后续的处理，一般group by,union会出现这样的情况，这种情况一般是要进行优化的，可以使用索引来优化

• Using filesort

在查询语句中包含 order by操作且无法使用索引完成排序操作时，当查询结果数据较少时会进行内存排序，反之会进行磁盘排序，可以使用索引来优化

参考资料

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

MySQL: 执行计划中filtered的计算方式和影响 - 简书