手把手教你彻底理解MySQL的explain关键字

这是我参与更文挑战的第3天，活动详情查看： 活动链接

数据库是程序员必备的一项基本技能，基本每次面试必问。对于刚出校门的程序员，你只要学会如何使用就行了，但越往后工作越发现，仅仅会写sql语句是万万不行的。写出的sql，如果性能不好，达不到要求，可能会阻塞整个系统，那对于整个系统来讲是致命的。

所以如何判断你的sql写的好不好呢？毕竟只有先知道sql写的好不好，才能再去考虑如何优化的问题。

MySQL官方就给我们提供了很多sql分析的工具，这里我们主要说一下EXPLAIN。

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

1.1 概念

使用EXPLAIN关键字可以模拟优化器执行sql语句，从而知道MySQL是如何处理你的语句，分析你的查询语句或者表结构的性能瓶颈。

用法：EXPLAIN+ sql语句

EXPLAIN执行后返回的信息如下：

各个字段的大致含义如下：

• id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
• select_type: SELECT 查询的类型.
• table: 查询的是哪个表
• partitions: 匹配的分区
• type: join 类型
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引.
• key_len: 查询优化器使用了索引的字节数.
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息

1.2 准备工作

新建一个数据库test，执行下面的sql语句

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));
复制代码

下面一一解释各列的含义。

1.3 id

select查询的序列号，包含一组数字，表示查询中执行select子句的顺序或操作表的顺序。大致分为下面几种情况

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

上面的查询语句，三个id都为1，具有相同的优先级，执行顺序由上而下，具体执行顺序由优化器决定，这里执行顺序为t1，t2，t3。

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

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

在我测试的时候，无意中发现，下面的语句，一个使用的是IN关键字，一个使用的=运算符，但使用EXPLAIN执行后，结果天壤之别。

这说明使用IN嵌套子查询，它是按顺序来执行的，也就是说每执行一次最外层子查询，里面的子查询都会被重复执行，这好像和我的理解差很多啊（我一直以为是先执行最里面的子查询，再执行外面的）。

具体可以看看这篇文章，我觉得讲的大概算是明白了。segmentfault.com/a/119000000…。这里就不再继续赘述了。

千万别用IN，使用JOIN或者EXISTS代替它

（3）id存在相同的和不同的

在上面语句的基础上，增加一个IN的子查询，执行结果如下

执行顺序为t3、t1、t2、t4。值越大的越先执行，相同值的从上往下执行。

1.4 select_type

select_type表示查询的类型，主要是为了区分普通查询、子查询、联合查询等复杂查询。分为以下几种类型：

（1）SIMPLE

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

（2）PRIMARY

查询中若包含任何复杂的子查询，那么最外层的查询被标记为PRIMARY。

（3）DERIVED

在from子句中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中。

（4）SUBQUERY

在select或where子句中包含了子查询，该子查询被标记为SUBQUERY。

（5）UNION

若第二个select查询语句出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

（6）UNION RESULT

从UNION表获取结果的SELECT。

上面的前三种在上一小节已经出现过了，看看后面这三种

可以看到id列出现了一个NULL，这是上面没讲到的。一般来说，特殊情况下，如果某行语句引用了其他多行结果集的并集，则该值可以为 NULL。

1.5 table

这个没啥好讲的，表示这个查询是基于哪种表的。并不一定是真实存在的表，比如上面出现的DERIVED和<union1,2>，一般来说会出现下面的取值：

（1）<union a,b>：输出结果中编号为 a 的行与编号为 b 的行的结果集的并集。

（2）< derived a>：输出结果中编号为 a 的行的结果集，derived 表示这是一个派生结果集，如 FROM 子句中的查询。

（3）< subquery a>：输出结果中编号为 a 的行的结果集，subquery 表示这是一个物化子查询。

1.6 partitions

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

根据官方文档，在创建表的时候，指定不同分区存放的id值范围不同。

插入测试数据，让id值分布在四个分区内。

执行查询输出结果。

1.7 type

type是查询的访问类型，是较为重要的一个指标，性能从最好到最坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

一般来说，得保证查询至少到达range级别，最好能达到ref。

（1）system

当表仅存在一行记录时（系统表），数据量很少，速度很快，这是一种很特殊的情况，不常见。

（2）const

当你的查询条件是一个主键或者唯一索引（UNION INDEX）并且值是常量的时候，查询速度非常快，因为只需要读一次表。

给t1表的content列增加一个唯一索引

（3）eq_ref

除了system和const，性能最好的就是eq_ref了。唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

（4）ref

非唯一性索引扫描，返回匹配某个单独值的所有行。区别于eq_ref，ref表示使用除PRIMARY KEY 和UNIQUE index 之外的索引，即非唯一索引，查询的结果可能有多个。可以使用 = 运算符或者<=> 运算符。

在t2表的content列加上普通索引

进行查询

（5）fulltext

查询时使用 fulltext 索引。

（6）ref_or_null

对于某个字段既需要关联条件，也需要null 值的情况下。查询优化器会选择用ref_or_null 连接查询。

（7）index_merge

在查询过程中需要多个索引组合使用，通常出现在有or 关键字的sql 中。

（8）unique_subquery

该联接类型类似于index_subquery。子查询中的唯一索引。在某些in子查询里，用于替换eq_ref，比如下面的查询语句

value IN (SELECT primary_key FROM single_table WHERE some_expr)
复制代码

（9）index_subquery

[图片上传中...(19.png-e55cec-1621069077487-0)]
利用索引来关联子查询，不再全表扫描。用于非唯一索引，子查询可以返回重复值。类似于unique_subquery，但用于非唯一索引

value IN (SELECT key_column FROM single_table WHERE some_expr)
复制代码

（10）range

只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where 语句中出现
了between、<、>、in 等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而
结束语另一点，不用扫描全部索引。

举个例子，t3表中id字段为主键，有PRIMARY索引，content字段没有建立索引，查询时使用id作为条件，结果如下

使用content作为条件，结果如下

所以，只有对设置了索引的字段，做范围检索 type 才是 range。

（11）index

sql语句使用了索引，但没有通过索引进行过滤，一般是使用了覆盖索引或者利用索引进行了排序分组。

index和ALL都是读全表，区别在于index是遍历索引树读取，ALL是从硬盘读取。index通常比ALL更快，因为索引文件通常比数据文件小。

举个例子，查询t3表主键id，结果如下

（12）ALL

全表扫描，性能最差。

1.8 possible_keys

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

1.9 key

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

举个例子，trb1表中有一个组合索引(age, name)，那么当你的查询列和索引的个数和顺序一致时，查询结果如下：

1.10 key_len

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

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

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

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

举个例子，有表trb1，存在以下字段，以及一个组合索引idx_age_name

下面查询语句的执行结果

key_len的值为153、158、null。如何计算：

①先看索引上字段的类型+长度。比如int=4 ; varchar(50) = 50 ; char(50) = 50。

②如果是varchar 或者char 这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘3,GBK 要乘2。

③varchar 这种动态字符串要加2 个字节。

④允许为空的字段要加1 个字节。

第一条：key_len = name的字节长度 = 50 * 3 + 2 + 1 = 153

第二条：key_len = age 的字节长度 + name 的字节长度= 4 +1 + ( 50*3 + 2 + 1)= 5 + 153 = 158。（使用的索引更充分，查询结果更精确，但消耗更大）

第三条：索引失效了。

1.11 ref

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

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

举个例子，t3表的content字段有普通索引，下面的查询语句结果如下

1.12 rows

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

1.13 filtered

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

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

1.14 Extra

其他额外的信息。

（1）Using filesort

说明mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。

举个例子，trb1表建立一个组合索引

下面的查询出现filesort ：

按照组合索引的顺序，是name、age、purchased，而上面的查询语句，没有使用中间的age，所以在order by的时候索引失效了。通常这种情况是需要进行优化的。

修改一下上面的sql语句，让索引不失效。

（2）Using temporary

使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。

这条sql语句用了临时表，又用了文件排序，在数据量非常大的时候效率是很低的，需要进行优化。

所以在使用group by 和 order by的时候，列的数量和顺序尽量和索引的一样。

（3）Using index

Using index 表示相应的select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，可以提高效率。

如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

还是使用上面的trb1表举例子

只出现了Using index，说明索引用来读取数据而不是执行查找。

出现了Using where，说明索引被用来执行查找。

（4）Using where

表示查询时有索引被用来进行where过滤。

（5）Using join buffer

查询时使用了连接缓存。

（6）impossible where

查询语句的where条件总是为false，举个例子

一般情况下不会出现这种。

关于Extra字段，有很多取值，这里就不一一列举了，具体可以看官方文档。

参考资料：dev.mysql.com/doc/refman/…