MySQL（十四）分析查询语句Explain七千字总结-6ren

MySQL（十四）分析查询语句Explain七千字总结

转载作者：我是一只小鸟更新时间：2023-04-14 22:32:03

35

4

分析查询语句：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 utf8mb4
BEGIN 
	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的输出列

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应着一个唯一的id
select_type	SELECT关键字对应查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能使用到的索引
key	实际使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询的时候，与索引列进行等值匹配的对象信息
rows	预估需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

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: 1003
Message: /* 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)

看着挺别扭，即下面:

                        
                          select s1.key1, s2.key1
from s1 join s2
where s1.key1 = s2.key1 and s2.common_field is not null;

最后此篇关于MySQL（十四）分析查询语句Explain七千字总结的文章就讲到这里了,如果你想了解更多关于MySQL（十四）分析查询语句Explain七千字总结的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

35

4

0

文章推荐：我变秃了，也变强了——再探博客调优

文章推荐： $\Beta$分布推导与可视化

文章推荐： Spring自定义参数解析器设计

文章推荐：从零开始配置深度学习环境：CUDA+Anaconda+Pytorch+TensorFlow

mysql - 只有 Mysql OR mysql+sqlite OR mysql+自己的解决方案
目前我正在构建相当大的网络系统，我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres，因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
mysql - Linux/mysql 将 mysql 表输出写入文件并保持 mysql 格式。
我在 mysql 中使用如下命令。当它显示表格数据时，它被格式化为一个非常干净的表格，间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时，如下
mysql - 无法从终端加载 mysql 但可以使用系统首选项启动 mysql
我知道这个问题之前已经被问过好几次了，我已经解决了很多问题，但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
mysql - 在 mysql 数据查询上获取不需要的输出 mysql
以下是我的 SQL 数据结构，我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
mysql - 将本地 mysql 服务器复制到基于云的 mysql
我们有一个本地 mysql 服务器(不在公共(public)域上)，并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗？2.我们的本地服务器只能在本地网络上访问
mysql - MySQL 触发器上 MySQL 变量的算术运算
我有一个表(test_table)，其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的，还有一个字段(字段 D)，我想从现有表(store_table)插入其值，但在插入前者(A、B 和
mysql - 无法在创建 MySQL 服务器的同一计划中使用 mysql
我想创建一个 AWS RDS 实例，然后使用 terraform 管理数据库用户。因此，首先，我创建了一个 RDS 实例，然后使用创建的 RDS 实例初始化 mysql 提供程序，以进一步将其用于用户
mysql - MySql 用户数量对 MySql 性能影响大吗？
当用户在我的网站上注册时，他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。创建它应该
mysql - mysql 表中的下和子类别(coldfusion，mysql)
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
mysql - 如何使用 MYSQL 运算符选择列所有值都必须可用 - MYSQL
我想获取 recipes_id 列的值，以获取包含 ingredient_id 的 2,17 和 26 条目的值。假设 ingredient_id 2 丢失则不获取记录。我已经尝试过 IN 运算符
mysql - MySQL 服务器和 MySQL 客户端有什么区别
在 Ubuntu 中，我通常安装两者，但 MySQL 的客户端和服务器之间有什么区别。作为奖励，当一个新语句提到它需要 MySQL 5.x 时，它是指客户端、服务器还是两者兼而有之。例如这个链接ht
mysql - mysql - 如何在没有触发器和手动插入的情况下在插入时生成/自动增加 guid mysql？
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。这还不够独特，所以我想我会有一个指导。我来自微软 sql 背景，在 ssms 中你可以选择类型为“uniqeidentifier”并自
mysql - Oracle MySQL 与 MySQL 相同吗？
我的系统上有 MySQL，我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
mysql - 本地 mysql 服务器和生产 mysql 服务器之间的显着性能差异
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开，留下的文档很少，而且联系不上了。我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
mysql - 如何自动将数据从一个 MySQL 数据库传输到另一个 MySQL 数据库？
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说，我希望每天早上 4:00 进行数据传输。可以吗？是否有任何 MySQL 内置功能可以让我们做到这一
mysql - 从 mysql 目录外的 mysql 表查询？
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表，还是必须将它们移动到 mysql 根目录内的数据库文件夹中？我在 Google 上搜索时没有找到任何东西。最
mysql - 使用另一个 mysql 表的值更新 Mysql 表
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段，都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份，其中成员中的代码与 ClassNumb
mysql - 是否可以将本地 MySQL 数据库复制到远程 MySQL 数据库？
情况:我有 2 台服务器，其中一台当前托管一个实时 WordPress 站点，我希望能够将该站点转移到另一台服务器，以防第一台服务器出现故障。传输源文件很容易；传输数据库是我需要弄清楚如何做的。两台服
mysql - 使用 mysql 查询复制 mysql 数据库
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数？类似于将 db A 复制到新的 db B。最佳答案首先创建复制数据库: CREATE DATABASE du
mysql - 当 mySQL 已安装并由另一个应用程序配置时，为新应用程序配置 mySQL
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试，并且运行良好。当我在计算机上安装我的软件时，我必须执行以下步骤: 安装 mySQL 服务器将用户名指定为“root

首页

博学

6Ren·AI

商城