MySQL——EXPLAIN用法详解

EXPLAIN是MySQL官方提供的sql分析的工具之一，可以用于模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。EXPLAIN主要用于分析查询语句或表结构的性能瓶颈。

以下是基于MySQL5.7.19版本进行分析的，不同版本之间略有差异。

1、EXPLAIN使用

explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。

2、EXPLAIN包含的信息

2.1 id

id是用来顺序标识整个查询中SELELCT语句的，在嵌套查询中id越大的语句越先执行。该值可能为NULL，如果这一行用来说明的是其他行的联合结果。大致分为下面几种情况：

（1）id相同，执行顺序由上至下

EXPLAIN select * from user,school,class where user.school_id = school.school_id and user.class_id = class.class_id

三个id都为1，表示具有相同的优先级，执行顺序由上而下，具体执行顺序由优化器决定，这里执行顺序为school，user，class。

（2）id不同，数字越大优先级越高

EXPLAIN select * from user where school_id = (select school_id from school where school_id = (select school_id from class where class_id = 2))

如果sql中存在子查询，那么id的序号会递增，id越大越先被执行。如上述查询，执行顺序是class、school、user，也就是说，最里面的子查询最先执行，由里往外执行。

如果上述查询中的“=”换成“in”，则运行结果会发生变化，原因是每执行一次最外层子查询，里面的子查询都会被重复执行，这也就是sql优化中提到的尽量不要使用in关键字

 使用in关键字示例：

EXPLAIN select * from user where school_id in (select school_id from school where school_id in (select school_id from class where class_id = 2))

2.2 select_type 

 select_type表示查询的类型

EXPLAIN select * from user where school_id = (select school_id from school where school_id = (select school_id from class where class_id = 2))

2.3 table

table显示查询操作基于哪张表的。

EXPLAIN select user.* from user where school_id = (select school_id from school where school_id = 2)
union 
select user.* from user where class_id = (select class_id from class where class_id = 2)

table列并不一定是真实存在的表，比如上面出现，一般来说会出现下面的取值：

• ：输出结果中编号为 a 的行与编号为 b 的行的结果集的并集。
• < derived a>：输出结果中编号为 a 的行的结果集，derived 表示这是一个派生结果集，如 FROM 子句中的查询。
• < subquery a>：输出结果中编号为 a 的行的结果集，subquery 表示这是一个物化子查询。

2.4 partitions

partitions列是查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

2.5 type

type显示的是访问类型，是较为重要的一个指标，性能从最好到最坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说，得保证查询至少达到range级别，最好能达到ref。

2.6 possible_keys

查询时可能使用的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。注意是可能，实际查询时不一定会用到。

2.7 key

查询时实际使用的索引，没有使用索引则为NULL。查询时若使用了覆盖索引，则该索引只出现在key字段中。

2.8 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

key_len显示的值是索引字段可能的最大长度，并非实际使用长度，即key_len是根据表定义计算得到，不是通过表内检索。

key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。

注意：key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到key_len中。

2.9 ref

显示索引的哪一列被使用了，常见的取值有：const， func，null，字段名。

• 当使用常量等值查询，显示const，
• 当关联查询时，会显示相应关联表的关联字段
• 如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
• 其他情况null

2.10 rows

rows 列表示 MySQL 认为它执行查询时可能需要读取的行数，一般情况下这个值越小越好！

2.11 filtered

filtered 是一个百分比的值，表示符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

在MySQL.5.7版本以前想要显示filtered需要使用explain extended命令。MySQL.5.7后，默认explain直接显示partitions和filtered的信息。

2.12 Extra

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