MySQL进阶-分析查询语句EXPLAIN

概述

能做什么？  

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

官网介绍  

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

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

  版本情况

• MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE， DELETE

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

基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下：  

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

mysql> EXPLAIN SELECT 1;

EXPLAIN 语句输出的各个列的作用如下：

EXPLAIN各列作用

table

不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所 以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该 表的表名（有时不是真实的表名字，可能是简称）。

mysql > EXPLAIN SELECT * FROM s1;

这个查询语句只涉及对s1表的单表查询，所以 EXPLAIN 输出中只有一条记录，其中的table列的值为s1，表明这条记录是用来说明对s1表的单表访问方法的。下边我们看一个连接查询的执行计划，可以看出这个连接查询的执行计划中有两条记录，这两条记录的table列分别是s1和s2，这两条记录用来分别说明对s1表和s2表的访问方法是什么。

mysql > EXPLAIN SELECT * FROM s1 INNER JOIN s2;

id

我们写的查询语句一般都以 SELECT 关键字开头，比较简单的查询语句里只有一个 SELECT 关键字，比如下边这个查询语句：

SELECT * FROM s1 WHERE key1 = 'a';

稍微复杂一点的连接查询中也只有一个 SELECT 关键字，比如：

SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common_field = 'a';

但是下边两种情况下在一条查询语句中会出现多个SELECT关键字

• 当查询语句有子查询时
• 用union 来连接时

mysql > EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

对于连接查询来说，一个SELECT关键字后边的FROM字句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的，比如：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

# 查询优化器可能对涉及子查询的查询语句进行重写，转变为多表查询的操作。  
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

 可以看到，虽然我们的查询语句是一个子查询，但是执行计划中s1和s2表对应的记录的id值全部是1，这就表明查询优化器将子查询转换为了连接查询。

对于包含UNION子句的查询语句来说，每个SELECT关键字对应一个id值也是没错的，不过还是有点儿特别的东西，比方说下边的查询：

使用union是把多个查询的结果集合起来并对结果集中的记录进行去重，使用临时表的方式对结果集进行去重，所以在查询执行计划时就多了一条id为空的记录，表示这是为了合并两个查询的结果集而创建的。

# Union去重
mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; 

 小结:

• id如果相同，可以认为是一组，从上往下顺序执行

• 在所有组中，id值越大，优先级越高，越先执行

• 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

select_type

• SIMPLE

查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型，比方说下边这个单表查询select_type的值就是SIMPLE:  

mysql> EXPLAIN SELECT * FROM s1;

•  PRIMARY

对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type的值就是PRIMARY。从结果中可以看到，最左边的小查询SELECT * FROM s1对应的是执行计划中的第一条记录，它的select_type的值就是PRIMARY。

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

•  UNION

对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询意外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果。

• UNION RESULT

 MySQL 选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT, 例子上边有。

• SUBQUERY

如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

•  DEPENDENT SUBQUERY

当子查询是相关子查询的时候，即子查询内部与外部的表是有关联的

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

• DEPENDENT UNION

同关联子查询

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

• DERIVED

从执行计划中可以看出，id为2的记录就代表子查询的执行方式，它的select_type是DERIVED, 说明该子查询是以物化的方式执行的。id为1的记录代表外层查询，大家注意看它的table列显示的是derived2，表示该查询时针对将派生表物化之后的表进行查询的。

mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;

• MATERIALIZED

当查询优化器在执行包含子查询的语句时，选择将子查询物化之后的外层查询进行连接查询时，该子查询对应的select_type属性就是DERIVED，比如下边这个查询：  

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);

type

执行计划的一条记录就代表着MySQL对某个表的 执行查询时的访问方法 , 又称“访问类型”，其中的 type 列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

• system  

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。比方说我们新建一个MyISAM表，并为其插入一条记录：

mysql> CREATE TABLE t(i int) Engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO t VALUES(1);
Query OK, 1 row affected (0.01 sec)

然后我们看一下查询这个表的执行计划，可以看到type列的值就是system了，

mysql> EXPLAIN SELECT * FROM t;

•  const

当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const, 比如：

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;

• eq_ref  

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）。则对该被驱动表的访问方法就是eq_ref。从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问 方法是 eq_ref ，表明在访问s1表的时候可以 通过主键的等值匹配 来进行访问。

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

•  ref

当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref，比方说下边这个查询：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

• fulltext  

全文索引

• ref_or_null

当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null  

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

• index_merge  

一般情况下对于某个表的查询只能使用到一个索引，但单表访问方法时在某些场景下可以使用Interseation、union、Sort-Union这三种索引合并的方式来执行查询。我们看一下执行计划中是怎么体现MySQL使用索引合并的方式来对某个表执行查询的。从执行计划的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式来执行 对 s1 表的查询。   

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

•  unique_subquery

类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery，比如下边的这个查询语句：

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';

• index_subquery  

index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引

range  

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

或者：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

index  

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

 ALL

 最熟悉的全表扫描，就不多说了，直接看例子：

mysql> EXPLAIN SELECT * FROM s1;

小结:

结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

其中比较重要的几个提取出来（见上图中的粗体）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）  

possible_keys和key

 在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个列执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。执行计划的possible_keys列的值是idx_key1, idx_key3，表示该查询可能使用到idx_key1, idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定采用idx_key3。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

key_len

实际使用到的索引长度 (即：字节数)，帮你检查是否充分的利用了索引，值越大越好，主要针对于联合索引，有一定的参考意义。

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;

int 占四个字节 

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

key2上有一个唯一性约束，是否为NULL占用一个字节，那么就是5个字节  

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

key1 VARCHAR(100) 一个字符占3个字节，100*3，是否为NULL占用一个字节，varchar的长度信息占两个字节。  

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

ref 

ref列展示得就是与索引列作等值匹配的数据结构是什么，到底是常数还是

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数，当然有时候更复杂一点:

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

 

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

rows  

预估的需要读取的记录条数，值越小越好。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

filtered

 某个表经过搜索条件过滤后剩余记录条数的百分比，如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

对于单表查询来说，这个filtered的值没有什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数 (即: rows * filtered)

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

从执行计划中可以看出来，查询优化器打算把s1作为驱动表，s2当做被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9688，filtered列为10.00，这意味着驱动表s1的扇出值就是9688 x 10.00% = 968.8，这说明还要对被驱动表执行大约968次查询。   

Extra

顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑选比较重要的额外信息介绍给大家。

• No tables used

当查询语句没有FROM子句时将会提示该额外信息，比如：

mysql> EXPLAIN SELECT 1;

• Impossible WHERE

当查询语句的WHERE子句永远为FALSE时将会提示该额外信息

mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

• Using where  

表示该查询语句使用了非索引字段的查询条件，即使使用了索引，有非索引的查询条件也是如此。

 

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a'; 

• No matching min/max row  

当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时。

mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

• Using index

当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列将会提示该额外信息。比方说下边这个查询中只需要用到idx_key1而不需要回表操作。

mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';

• Using index condition  

有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：

SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

模糊查询会使索引失效，之前的处理是，先处理key1这个范围查询，然后立马根据范围查询所得到的二级索引去进行回文匹配。现在的处理逻辑是，处理完范围查询后，就对得到的二级索引判断是否满足模糊查询的条件，如果不符合的，就不进行回表操作，这样省略了大量回表操作，减少随机IO。 这个改进就是索引条件下推，当mysql使用了索引条件下推，就会显示Using index condition  

• Using join buffer (Block Nested Loop)

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

• Not exists

当我们使用左(外)连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示这个信息。

mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

• Using intersect(...) 、 Using union(...) 和 Using sort_union(...)

•  如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；
• 如果出现Using union(...)提示，说明准备使用Union索引合并的方式执行查询;
• 如果出现Using sort_union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

• Zero limit  

当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读取任何记录，将会提示该额外信息

mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;

• Using filesort

有一些情况下对结果集中的记录进行排序是可以使用到索引的。如果在内存或者磁盘中进行排序那就统称为文件排序。需要注意的是，如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为索引进行排序。

mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

•  Using temporary

mysql> EXPLAIN SELECT DISTINCT common_field FROM s1;

mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;

执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大的成本的，所以我们最好能使用索引来替代掉使用临时表，比方说下边这个包含GROUP BY子句的查询就不需要使用临时表：  

mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;