分析查询语句：EXPLAIN

1 概述

定位了查询慢的 SQL 之后，就可以使用 EXPLAIN 或者 DESCRIBE 工具做针对性的分析查询。两者使用方法相同，并且分析结果也是相同的。

​ MySQL 中有专门负责 SQL 语句优化的优化器模块，主要功能是计算分析系统中收集到的统计信息，为客户端请求的 Query 提供它最优的执行计划（它认为的最优数据检索方案毕竟是自动分析成的，所以不一定是 DBA 或者开发人员认为的最优方案）

​ 这个执行计划展示了接下来进行具体查询的方式，比如多表连接的顺序、对每个表采用什么方式进行具体的查询等等，MySQL 提供的 EXPLAIN 语句可以用来查询某个查询语句的具体执行计划，根据 EXPLAIN 语句的输出项，可以有针对性地提升查询 SQL 的性能。

能查到什么？

• 表的读取顺序

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

• 哪些索引可以被使用

• 哪些索引实际被使用

• 表之间的引用关系

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

版本区别

• MySQL5.6.3 之前只能使用 EXPLAIN SELECT，之后可以使用 EXPLAIN SELECT， UPDATE， DELETE

• 5.7 之前的版本，想要查看 partitions（分区）和 filtered 需要使用 EXPLAIN partitions、filtered，而 5.7 之后直接默认显示

数据准备

创建表

CREATE TABLE s1 (	id INT AUTO_INCREMENT,	key1 VARCHAR(100),	key2 INT,	key3 VARCHAR(100),	key_part1 VARCHAR(100),	key_part2 VARCHAR(100),	key_part3 VARCHAR(100),	common_field VARCHAR(100),	PRIMARY KEY (id),	INDEX idx_key1 (key1),	UNIQUE INDEX idx_key2(key2),	INDEX idx_key3(key3),	INDEX idx_key_part(key_part1, key_part2, key_part3))ENGINE=INNODB CHARSET=utf8

CREATE TABLE s2 (	id INT AUTO_INCREMENT,	key1 VARCHAR(100),	key2 INT,	key3 VARCHAR(100),	key_part1 VARCHAR(100),	key_part2 VARCHAR(100),	key_part3 VARCHAR(100),	common_field VARCHAR(100),	PRIMARY KEY (id),	INDEX idx_key1 (key1),	UNIQUE INDEX idx_key2(key2),	INDEX idx_key3(key3),	INDEX idx_key_part(key_part1, key_part2, key_part3))ENGINE=INNODB CHARSET=utf8

创建存储函数

-- 函数返回随机字符串DELIMITER //
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4BEGIN 	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';	DECLARE return_str VARCHAR(255) DEFAULT '';	DECLARE i INT DEFAULT 0;	WHILE i < n DO        SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));       SET i = i + 1;    END WHILE;    RETURN return_str;END //DELIMITER ;

首先要确保相信函数的变量 log_bin_trust_function_creators 为 1

SELECT @@log_bin_trust_function_creators variable;
SET GLOBAL log_bin_trust_function_creators = 1;

存储过程

​ 向 s1、s2 表添加数据的存储过程

DELIMITER //CREATE PROCEDURE insert_s1 (IN min_num INT (10), IN max_num INT(10))BEGIN	DECLARE i INT DEFAULT 0;	SET autocommit = 0;	REPEAT	SET i = i + 1;	INSERT INTO s1 VALUES(		(min_num + i),		rand_string(6),		(min_num + 30* i + 5),		rand_string(6),		rand_string(10),		rand_string(5),		rand_string(10),		rand_string(10)	);	UNTIL i = max_num	END REPEAT;	COMMIT;END //DELIMITER;


DELIMITER //CREATE PROCEDURE insert_s2 (IN min_num INT (10), IN max_num INT(10))BEGIN	DECLARE i INT DEFAULT 0;	SET autocommit = 0;	REPEAT	SET i = i + 1;	INSERT INTO s1 VALUES(		(min_num + i),		rand_string(6),		(min_num + 30* i + 5),		rand_string(6),		rand_string(10),		rand_string(5),		rand_string(10),		rand_string(10)	);	UNTIL i = max_num	END REPEAT;	COMMIT;END //DELIMITER;

执行存储过程添加数据

CALL insert_s1(10001, 10000);CALL insert_s2(10001, 10000);

Explain 的输出列

1 id

​ id，在一个大的查询语句中每个 SELECT 关键字都对应着一个唯一的 id，所以有几个 select 关键字就会有几个 id：

EXPLAIN SELECT * FROM s1

EXPLAIN SELECT * FROM s1 INNER JOIN s2

上面的两个 SQL 都只有一个 select 所以只有一个 id

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

子查询有两个 select，所以对应两个 id1 和 2

查询优化器可能会对涉及子查询的查询语句进行重写：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a')

优化器在看到子查询后判断能够变为多表连接以降低复杂度（O(n^2) -> O(n)）:

​ SELECT * FROM s1, s2 ON s1.key1 = s2.key2 WHERE s2.common_field = 'a'

重写后的 sql 变成了一个 select，所以查询结果仍然是一个 id

​ 但是如果 s2 查的是 key1，就会变成下面这样：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE common_field = 'a')

UNION 去重

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

union 由于去重操作会使用到中间表，所以会有一个 table<union, 1, 2>

但是我这里的临时表也有 id = 3，看康师傅视频是没有的，是版本的问题吗？也就是还对中间表进行了 select

​ 如果使用的是 UNION ALL 不进行去重，则是：

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

小结：

• id 如果相同，则会被认为是同一组查询，会按照从上往下的顺序执行

• 如果不同，则 id 越大的优先级越高，越先执行

• id 的号码表示一趟独立的查询，一个 sql 的查询趟数越少越好

2 select_type

​ 一个大的查询里面可以包含多个 select 关键字，每个 select 关键字代表一个小的查询语句，而每个小的查询中都包含着若干的表进行连接操作，而每一张表都对应着 EXPLAIN 查询计划的一条记录，对于在同一个 select 关键字的表来说，他们的 id 是相同的。

​ select_type：SELECT 关键字对应查询的类型，即我们只要知道了某个小查询的 select_type 属性，就能知道这个小查询在大查询中扮演的角色、起到的作用

常见的 select_type：

• SIMPLE：不包含 UNION 或者子查询的查询都算是 SIMPLE 类型

• UNION、PRIMARY、UNION RESULT：对于包含 UNION 和 UNION ALL 的语句，它是由几个小的查询组成的，除了最左边的查询的 select_type 是 PRIMARY，其余的均为 UNION，而针对临时表的 select 则是 UNION RESULT

• 
  

• SUBQUERY：如果包含子查询的查询语句不能够转化为 semi-join 的方式（即优化器将子查询优化为表连接），并且子查询不是相关子查询(即用到了外表的子查询)，则该子查询的第一个 select 关键字代表的那个查询的 select_type 就是 SUBQUERY

• explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a'

• 
  

• 首先这个子查询不是相关子查询，那么这个 sql 能不能优化成表连接的 sql 呢？

• select * from s1 INNER JOIN s2 on s1.key1 = s2.key1

• 答案是不能，这两个 sql 是不同的：比如 s1 表中有一个 key1 值，s2 表有两个重复的 key1 值，则第一条语句由于是 in，所以只会匹配一次，而第二条 sql 是等于号，所以这种情况下会匹配两次，从而二个 sql 得到的结果是完全不同的，因此这个 sql 会用到两个 select，也就出现两个 id 了，一个 select 为 Primary，子查询的 select 为 subquery。

• DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转化为 semi-join 的方式，但是子查询涉及到了外表，也就是为相关子查询，那么该子查询的第一个 select 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY

• EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 from s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'

• 
  

• select_type 为 DEPENDENT SUBQUERY 的查询可能会被执行多次

• DEPENDENT UNION：在包含 UNION 和 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的小查询之外，其余查询的 select_type 均为 DEPENDENT UNION

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

• 
  

• 第二个子查询 UNION 加上了 DEPENDENT 好理解，因为用到了外表

• 但是，为什么第一个子查询没有用到外表，也是 DEPENDENT SUBQUERY 呢？

• 这是由于优化器对于 in 的改动：

• ​ where exists (s1.key1 = s2.key1 ...)，这样就变为了相关子查询，至于为啥这么做完全不知道了。。

• DERIVED：派生表对应子查询的 select_type 为 DERIVED

• EXPLAIN SELECT * FROM (SELECT key1, count(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1

• 
  

• <drived2>即为 id 为 2 的派生表

• MATERIALIZED（物化）：当查询优化器在执行包含子查询语句的时候，选择将子查询之后与外层查询进行连接时，该子查询对应的 select_type 就是 MATERIALIZED

• EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2)

• 
  

• SELECT key1 FROM s2 的结果是一个个的记录然后与外表进行连接，则这些记录就可以被称作是物化表，查询方式为 MATERIALIZED

• 而外层 select 直接将子查询成的物化表看做普通的表，查询方式为 SIMPLE

• 这个和上面的非相关子查询有点像，后面添加了一个 or key3 = 'a'，非相关子查询就变成物化表了？？？

• EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) or key3 = 'a'

• 
  

3 table

​ table，即表名

• 查询出来的每一行记录都对应着一个单表

• EXPLAIN SELECT * FROM s1

• 
  

• EXPLAIN SELECT * FROM s1, s2

• 
  

• 可以看到两个记录的 id 是一样的，因为属于同一个大的查询语句（只有一个 select）

• 并且 s2 排在 s1 的前面，所以 s2 是驱动表，s1 是被驱动表（并不能根据 sql 语句判断，因为 sql 的顺序有可能被优化器优化修改）

4 partitions

• 代表分区表中的命中情况，非分区表，该值为 NULL，一般情况下我们查询语句执行计划的 partitions 列的值也都是 NULL

​

5 type

​ 执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法，又称访问类型，即这里的 type。比如，type 是 ref，表名 mysql 将使用 ref 方法对改行记录的表进行查询。

​ 完整的访问方法如下：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all，越靠前代表效率越高

​ SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 const 级别。

• system：当表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system

• CREATE TABLE t(i INT) ENGINE=MYISAM; INSERT INTO t VALUES(1); EXPLAIN SELECT * FROM t

• 
  

• 存储引擎的统计数据是精确的，意思是例如 MyISAM 存储存储引擎有记录的记录的个数

• system 是性能最高的情况

• 而如果再添加一条记录，会变为 all，而 InnoDB 即使一条数据也是 all

• 
  

• 于此同时，INNODB 访问 count()的数据也是 all 的

• CREATE TABLE tt(i INT) ENGINE=INNODB; INSERT INTO tt VALUES(1); EXPLAIN SELECT count(*) FROM tt

• 
  

• const：当根据主键或者唯一的二级索引与常数进行等值匹配的时候，对单表的访问就是 const，表示常数级别

• EXPLAIN SELECT * FROM s1 WHERE id = 10005; EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

• 
  

• 如果是 key3，则为 all

• EXPLAIN SELECT * FROM s1 WHERE key3 = 1006;

• 
  

• 这里其实牵扯到隐式转换导致索引失效的问题：由于 key3 是 varchar 类型的，但是这里是数字进而进行了函数转换，进而索引失效只能 all 查询了

• eq_ref：在连接查询的时候，如果被驱动表是通过主键或者唯一的二级索引等值匹配的方式进行访问的（如果主键或者唯一的二级索引是联合索引，则要求索引的每一列进行联合匹配），则对于该被驱动表的访问方式就是 eq_ref

• EXPLAIN SELECT * from s1 INNER JOIN s2 WHERE s1.key2 = s2.key2

• 
  

• key2 是带有唯一约束的二级索引，因此被驱动表 s2 的访问方式为 eq_ref

• 其中 ref 表示查询的值已经被指定：即通过 all 方式查询的 s1 表指定的

• ref：当通过普通的二级索引与常量进行等值匹配来查询某个表，对该表的访问方式可能是 ref

• EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq';

• 
  

• 这里 key3 就是没有唯一约束的普通索引，可以看到用到了索引 key3，因此 type 为 ref

• ref_or_null：当通过普通的二级索引与常量进行等值匹配来查询某个表，当该值也可能是 null 值时，那么对该 表的访问方式可能就是 ref_not_null

• EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq' OR key3 IS NULL;

• 
  

• index_merge：单表访问在某些情况下可以使用 Intersection、Union、Sort-Union 这三种索引合并的方式来执行查询

• EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key2 = 123131

• 
  

• key1 和 key2 均为索引列，一个 Select 关键字只能使用一个索引，所以这里使用了合并索引为一个虚拟索引的办法，相当于扫描两个索引树取出主键并取并集再回表的操作

• 但是，如果是 AND 的情况，只会使用一个索引（这里是唯一的二级索引。故而是 const）

• EXPLAIN SELECT * FROM s1 WHERE key1 = 'rCLXEg' AND key2 = 10036

• 
  

• unique_subquery：是针对一些包含 IN 子查询的查询语句中，如果查询优化器决定将 In 子查询语句变为 EXISTS 子查询，并且子查询可以使用到主键的等值匹配的话，那么子查询的 type 就是 unique_subquery

• EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a'

• 
  

• range：如果使用索引获取某些范围区间的记录，就可能使用到 range 方法

• EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c')

• 
  

• 非索引列则为 all

• index：当可以使用索引覆盖，并且需要扫描全部的索引记录，该表的访问方法就是 index

• EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'

• 
  

• 可以看到在 key 里面还是用到了联合索引的，尽管根据最左前缀原则，只有检索条件为 key_part1 才能用到索引，这里是因为检索条件和 select 返回列都是和联合索引相关的列，所以使用了联合索引扫描了全部的索引记录，因为这样就不需要再回表找其他的列了（查的列都在索引上）

• 不需要回表就能查找到所需要的数据，称作索引覆盖

• 这时候再添加一个其他的列：

• EXPLAIN SELECT key1, key_part2 FROM s1 WHERE key_part3 = 'a'

• 结果为 ALL，因为联合索引列上没有 key1 的信息，需要回表去查 key1

• all：全表扫描

6 possible_key 和 key

​ 在 EXPLAIN 语句输出的执行计划中，possible_key 表示在单表查询中可能会用到的索引，一般查询涉及到的字段上存在索引，则该索引就将被列出，但不一定被查询使用。

​ key 则表示经过查询优化器计算使用不同索引的查询成本之后，最终确定使用的索引。

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

key1 和 key3 均为普通的二级索引，但是 key3 是等值匹配因此耗费的成本较低，所以最终选择使用索引 key3

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

而如果这里改成 OR，则会演变成之前讲的 index_merge 合并索引，即将两个索引树的主键提取取并集，然后统一到聚簇索引中执行一次回表操作

EXPLAIN SELECT key1, key3 FROM s1 WHERE key1 > 'z' OR key3 = 'a'

再扩展一下，即使查询列可以使用覆盖索引（即查询列的值都可以在索引树中找到），仍然需要进行一次回表操作，因此两个查询的执行计划是相同的：

7 index_len （联合索引分析）

​ 实际使用到的索引的长度（即字节数），用来查看是否充分利用了索引，index_len 的值越大越好

这里的越大越好是跟自己进行的比较，因为主要是针对的联合索引，因为利用联合索引的长度越大，查询需要读入的数据页就越少，效率也就越高

EXPLAIN SELECT * FROM s1 WHERE id = 10005

为什么是 4：因为 id 列是 int 型所以真实数据占 4 个字节，同时行格式中主键非空因此不需要 NULL 值列表，定长不需要变长字段长度列表，故而是 4

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

key2 是 int 类型，占 4 个字节，并且具有唯一性约束但是可能为空，因此行格式中 null 值列表占 1 个字节，总共 5 个字节

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

首先 key1 是 varchar(100)，并且表是 utf8mb3 格式的，因此真实数据存储占(100 * 3) = 300 个字节，本身定长所以行格式的变长字段长度列表占 2 个字节，NULL 值列表占 1 个字节，共计 303 个字节

同理下面的查询一个为 303，另一个是 606，这时候才体现出 key_len 的作用：第二个 sql 比第一个 sql 利用联合索引更加充分

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

8 ref

​ ref 表示当我们使用索引列等值查询的时候，与索引列进行等值匹配的对象的信息。

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

key1 是普通的二级索引，所以 type 是 ref（唯一的二级索引是 const），而等值的匹配类型是一个常量，因此 ref 列的值是 const

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

由于是表连接，所以只有一个 select id，然后由于是主键进行的连接，所以对于第二个表的访问方式 type 是 eq_ref（普通索引则为 ref），同时等值比较的是 s1 的列，因此 ref 为 atguigu1.s2.id

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

key1 是普通的二级索引，因此 type 为 ref，等值比较的类型是一个函数返回值，因此 ref 列的值为 func

9 rows

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

值越小表示在同一个数据页中的可能性越大，IO 的次数也就越少

10 filtered （结合 rows 分析）

​ filtered：表示某个表经过条件过滤之后，剩余记录条数的百分比，值越大越好

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

如上表示经过条件过滤后，100%都是符合要求的

值越大越好的原因：假设条件过滤后是 40 条记录，如果 filtered 是 100%，则原来有 40 条，如果 filtered 是 10%，则原来有 400 条，相比之下 40 条需要读取的数据页要少一些

​ 而如果执行的是索引的单表扫描，那么计算的时候除了估计出满足对应索引的搜索条件，还应计算同时满足其他条件的记录是多少条

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

如上面的 sql，rows303 表示预估满足索引列 key1 需要读取的记录数，而 filtered 表示加上 common_field 字段后预估读取占全部的百分比

​ 对于单表查询这个 filtered 列其实没有太大作用，但是它在多表连接中驱动表对应的执行计划记录的 filtered 值，决定了被驱动表的执行次数。

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

首先多表连接查询所以为同一个 select id，其次连接条件是普通的二级索引，所以驱动表的访问类型 type 为 all，被驱动表的访问类型 type 为 ref，最后 s1 表预估读取的记录数 rows 为 10152，再经过条件过滤 10152 * 10%和 s2 做等值匹配，因此 1015 就是 s2 表的执行次数

11 Extra

​ Extra 用来说明一些不适合在其他列中展示但是十二分重要的额外信息。通过这些额外信息可以更准确地知道 mysql 导致是怎么执行给定的查询语句的。

• no tables used：没有 from 字句，即没有用到表的情况

• EXPLAIN select 1

• 
  

• impossible where：where 语句永远为 false 的情况

• EXPLAIN select * FROM s1 WHERE 1 != 1

• 
  

• 这样也没有用到表，反正条件都不对

• where：使用全表扫描来执行针对某个表的查询，字句中有针对该表的搜索条件，则在 Extra 中展现

• EXPLAIN select * FROM s1 WHERE common_field = 'a'

• 
  

• common_field 是一个不带索引的普通字段，因此 type 为 all，Extra 展现了语句是通过 where 执行的

• no matching min/max row 当查询列表处有 min 或者 max 聚合函数，但是没有符合 where 条件的记录时，将会提示该额外信息

•  EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'adqwdqweqwe'

• 
  

• 而当 where 条件符合（或者根本没有 where 条件的时候），则显示 Select tables optimized away，表示选择优化后的表

• EXPLAIN SELECT MIN(key1) FROM s1

• 
  

• using index：当出现索引覆盖，即查询和检索条件的列都在使用的索引里面，也即是不需要回表操作的情况

• EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'

• 
  

• 当出现主键的情况也是覆盖索引

• using index condition：即索引条件下推，考虑下面的 sql 查询：

• EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 like '%a%'

• 
  

• ​ 这条 sql 执行的正常顺序应该是：首先使用 idx_key1 的索引树，查询 key1 > z 的所有主键值，这里找到了 385 条记录的主键，然后对这些主键进行回表操作，在聚簇索引中找到包含其他列的数据，然后判断剩下的过滤条件进行返回。

• ​ 而索引条件下推针对特殊情况进行了优化：就是如果剩余的过滤条件针对的是索引列，则不需要在回表后进行判断，这样就能够减少回表的操作，但是 rows 仍为 385

• using join buffer：即基于块的嵌套循环算法：当被驱动表不能够有效利用索引加快访问速度，mysql 就会为其在内存中分配一块 join buffer 的内存块来加快访问的速度

• EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field

• 
  

• common_field 是一个没有索引的列

• not exists：在表连接的时候，当 where 条件中被驱动表的某个列等于 null，而这个列又有非空约束的时候，Extra 就会展现 not exists

• EXPLAIN SELECT * FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.id IS NULL

• 
  

• 注意一定是被驱动表的列，如果是主驱动表出现这种情况，会直接显示为 impossible where，就不会再看被驱动表了

• using union(index_merge)：or 使用两个索引的情况，即前面 type 讲到的 index_merge，这时候会将两个索引树查出的 id 取并集然后再回表在进行 where 条件过滤

• EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'

• 
  

• zero limit：limit 为 0 的情况

• file sort 文件排序：有一些情况排序是能够用到索引的：EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

  
  

  这个查询利用 idx_key1 索引直接取出 key1 列的 10 条记录（按照索引列排序的），然后再拿着记录的主键值进行回表得到全部列的值。但是更多情况下的排序操作无法利用到索引，只能在内存中（记录较少的情况）或者磁盘中进行排序，mysql 把这种在内存或者磁盘中排序的方式统称为文件排序 file sort

  但是这里有个地方很不理解，为什么去掉 limit 或者 limit 较大的时候，就会变成文件排序？

  EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 97;

  
  

  个人猜测：有一个地方需要注意，就是随着 limit 的增大 rows 也在增大，尤其是在 limit 为 95 左右的时候突然增大了很多，这是不是因为：limit 较小的时候，通过索引顺序得到的主键值也比较集中，这时候回表操作也是顺序查询的级别，但是 limit 过大甚至没有的时候，主键值就会特别分散（因为是按照 key1 索引列排序的，所以 key1 集中而主键值分散），因此这时候回表读取操作相当于是随机查找的级别了，那这样查询优化器判断成本后，还不如直接在内存或者磁盘中进行文件排序。

  对于没有索引的查询，自然只能文件排序了：

  EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

  
  

• using temporary：mysql 在进行一些如去重、排序的功能的时候，如果不能够有效地利用索引，就可能需要通过建立内部的临时表来完成。

• EXPLAIN SELECT DISTINCT common_field FROM s1;

• 
  

• 执行计划中出现临时表不是一个很好的征兆，因为建立和维护临时表都需要很大的成本，应该尽量通过使用索引来替换临时表

小结

• Explain 不考虑 Cache（不考虑记录的加载方式，只是考量 sql 语句）

• Explain 不能显示 mysql 在执行查询时做的优化工作

• Explain 不会显示关于触发器、存储过程或用户自定义函数对于查询的影响

• 部分信息是估算的，并非精确的值

Explain 的进一步使用

Explain 的四种输出格式

​ Explain 的四种输出格式：传统格式、Json 格式、Tree 格式、可视化格式

1 传统格式

​ 即上面一直在使用的 EXPLAIN 语句，概要说明查询计划

2 JSON 格式

​ 传统的 EXPLAIN 语句的输出缺少了一个衡量执行计划好坏的重要属性--成本。JSON 格式是四种格式里面信息最详尽的格式，包含了执行的成本信息。 接下来对比一下传统和 JSON 格式的 EXPLAIN：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'

{  "query_block": {    "select_id": 1, // 原来的id    "cost_info": {      "query_cost": "1394.77" // 查询成本    },    "nested_loop": [      {        "table": {          "table_name": "s1", // table          "access_type": "ALL", // type          "possible_keys": [            "idx_key1"          ],          "rows_examined_per_scan": 10152, // rows          "rows_produced_per_join": 1015, // rows * filtered          "filtered": "10.00",          "cost_info": {            "read_cost": "937.93",            "eval_cost": "101.52",            "prefix_cost": "1039.45", // read + eval            "data_read_per_join": "1M" // 读取的数据量          },          "used_columns": [ // 查询字段            "id",            "key1",            "key2",            "key3",            "key_part1",            "key_part2",            "key_part3",            "common_field"          ],          "attached_condition": "((`atguigudb1`.`s1`.`common_field` = 'a') and (`atguigudb1`.`s1`.`key1` is not null))" // 查询条件        }      },      {        "table": {          "table_name": "s2",          "access_type": "eq_ref",          "possible_keys": [            "idx_key2"          ],          "key": "idx_key2",          "used_key_parts": [            "key2"          ],          "key_length": "5",          "ref": [            "atguigudb1.s1.key1"          ],          "rows_examined_per_scan": 1,          "rows_produced_per_join": 1015,          "filtered": "100.00",          "index_condition": "(cast(`atguigudb1`.`s1`.`key1` as double) = cast(`atguigudb1`.`s2`.`key2` as double))",          "cost_info": {            "read_cost": "253.80",            "eval_cost": "101.52",            "prefix_cost": "1394.77",            "data_read_per_join": "1M"          },          "used_columns": [            "id",            "key1",            "key2",            "key3",            "key_part1",            "key_part2",            "key_part3",            "common_field"          ]        }      }    ]  }}

• read_cost：由两部分组成：IO 成本 rows * (1 - filtered)条记录的 CPU 成本

• eval_cost： rows * filtered

3 Tree 格式

​ Tree 格式是 8.0.16 版本之后引入的新格式，主要根据各个部分之间的关系和各个部分的执行顺序来描述如何查询。

EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'

-> Nested loop inner join  (cost=1394.77 rows=1015)    -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null))  (cost=1039.45 rows=1015)        -> Table scan on s1  (cost=1039.45 rows=10152)    -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double))  (cost=0.25 rows=1)

4 可视化输出

​ 需要安装 MySQL workbench

Show Warnings 的使用

​ 当我们使用 Explain 语句查看了某个查询语句的执行计划之后，紧接着还可以使用 Show warnings 来查看与这个查询计划有关的一些扩展信息，比如：

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

​ 正常来说，我们使用 s2 left join s1，那么 s2 应该是驱动表，s1 是被驱动表，但是可以看到执行计划中实际上是反着的，这是由于优化器在判断两个表作为驱动表的执行成本的时候对 sql 进行的优化（where 语句是针对的 s2），使用 show warnings 可以看到这种优化：

mysql> show warnings \G*************************** 1. row ***************************  Level: Note   Code: 1003Message: /* select#1 */ select `atguigudb1`.`s1`.`key1` AS `key1`,`atguigudb1`.`s2`.`key1` AS `key1` from `atguigudb1`.`s1` join `atguigudb1`.`s2` where ((`atguigudb1`.`s1`.`key1` = `atguigudb1`.`s2`.`key1`) and (`atguigudb1`.`s2`.`common_field` is not null))1 row in set (0.00 sec)SQL 复制 全屏

​ 看着挺别扭，即下面：

select s1.key1, s2.key1from s1 join s2where s1.key1 = s2.key1 and s2.common_field is not null;