剖析查询句子：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
explainselect*froms1wherekey1in(selectkey1froms2)orkey3=’a’
首要这个子查询不是相关子查询，那么这个sql能不能优化成表衔接的sql呢？
select*froms1INNERJOINs2ons1.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
EXPLAINSELECT*FROMs1WHEREkey1IN(SELECTkey1froms2WHEREs1.key2=s2.key2)ORkey3=’a’
select_type 为 DEPENDENT SUBQUERY 的查询或许会被履行多次
DEPENDENT UNION：在包括UNION和UNION ALL的大查询中，假如各个小查询都依赖于外层查询的话，那除了最左边的小查询之外，其他查询的select_type均为DEPENDENT UNION
EXPLAINSELECT*FROMs1WHEREkey1IN(SELECTkey1FROMs2WHEREkey1=’a’UNIONSELECTkey1FROMs1WHEREkey1=’b’)
第二个子查询UNION加上了DEPENDENT 好了解，因为用到了表面
可是，为什么榜首个子查询没有用到表面，也是DEPENDENT SUBQUERY呢？
这是因为优化器关于in的改动：
whereexists(s1.key1 = s2.key1 …)，这样就变为了相关子查询，至于为啥这么做完全不知道了。。
DERIVED：派生表对应子查询的select_type为DERIVED
EXPLAINSELECT*FROM(SELECTkey1, count(*)AScFROMs1GROUPBYkey1)ASderived_s1WHEREc >1
即为id为2的派生表
MATERIALIZED（物化）：当查询优化器在履行包括子查询句子的时分，挑选将子查询之后与外层查询进行衔接时，该子查询对应的select_type便是MATERIALIZED
EXPLAINSELECT*FROMs1WHEREkey1IN(SELECTkey1FROMs2)
SELECT key1 FROM s2的成果是一个个的记载然后与表面进行衔接，则这些记载就可以被称作是物化表，查询办法为MATERIALIZED
而外层select直接将子查询成的物化表看做一般的表，查询办法为SIMPLE
这个和上面的非相关子查询有点像，后边增加了一个or key3 = ‘a’，非相关子查询就变成物化表了？？？
EXPLAINSELECT*FROMs1WHEREkey1IN(SELECTkey1FROMs2)orkey3=’a’

3 table

table，即表名

查询出来的每一行记载都对应着一个单表
EXPLAINSELECT*FROMs1
EXPLAINSELECT*FROMs1, 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
CREATETABLEt(iINT) ENGINE=MYISAM;INSERTINTOtVALUES(1); EXPLAINSELECT*FROMt
存储引擎的计算数据是准确的，意思是例如MyISAM存储存储引擎有记载的记载的个数
system是功用最高的状况
而假如再增加一条记载，会变为all，而InnoDB即使一条数据也是all
于此同时，INNODB拜访count()的数据也是all的
CREATETABLEtt(iINT) ENGINE=INNODB;INSERTINTOttVALUES(1); EXPLAINSELECTcount(*)FROMtt
const：当依据主键或许仅有的二级索引与常数进行等值匹配的时分，对单表的拜访便是const，表明常数等级
EXPLAINSELECT*FROMs1WHEREid=10005; EXPLAINSELECT*FROMs1WHEREkey2=10066;
假如是key3，则为all
EXPLAINSELECT*FROMs1WHEREkey3=1006;
这儿其实牵扯到隐式转换导致索引失效的问题：因为key3是varchar类型的，可是这儿是数字进而进行了函数转换，进而索引失效只能all查询了
eq_ref：在衔接查询的时分，假如被驱动表是经过主键或许仅有的二级索引等值匹配的办法进行拜访的（假如主键或许仅有的二级索引是联合索引，则要求索引的每一列进行联合匹配），则关于该被驱动表的拜访办法便是eq_ref
EXPLAINSELECT*froms1INNERJOINs2WHEREs1.key2=s2.key2
key2是带有仅有束缚的二级索引，因而被驱动表s2的拜访办法为eq_ref
其中ref表明查询的值现已被指定：即经过all办法查询的s1表指定的
ref：当经过一般的二级索引与常量进行等值匹配来查询某个表，对该表的拜访办法或许是ref
EXPLAINSELECT*FROMs1WHEREkey3=’CUTLVwqweqweq’;
这儿key3便是没有仅有束缚的一般索引，可以看到用到了索引key3，因而type为ref
ref_or_null：当经过一般的二级索引与常量进行等值匹配来查询某个表，当该值也或许是null值时，那么对该表的拜访办法或许便是ref_not_null
EXPLAIN SELECT * FROM s1WHEREkey3=’CUTLVwqweqweq’OR key3 IS NULL;
index_merge：单表拜访在某些状况下可以运用Intersection、Union、Sort-Union这三种索引兼并的办法来履行查询
EXPLAINSELECT*FROMs1WHEREkey1=’a’ORkey2=123131
key1和key2均为索引列，一个Select关键字只能运用一个索引，所以这儿运用了兼并索引为一个虚拟索引的办法，适当于扫描两个索引树取出主键并取并集再回表的操作
可是，假如是AND的状况，只会运用一个索引（这儿是仅有的二级索引。故而是const）
EXPLAINSELECT*FROMs1WHEREkey1=’rCLXEg’ANDkey2=10036
unique_subquery：是针对一些包括IN子查询的查询句子中，假如查询优化器决议将In子查询句子变为EXISTS子查询，而且子查询可以运用到主键的等值匹配的话，那么子查询的type便是unique_subquery
EXPLAINSELECT*FROMs1WHEREkey2IN(SELECTidFROMs2WHEREs1.key1=s2.key1)ORkey3=’a’
range：假如运用索引获取某些范围区间的记载，就或许运用到range办法
EXPLAINSELECT*FROMs1WHEREkey1IN(‘a’,’b’,’c’)
非索引列则为all
index：当可以运用索引掩盖，而且需求扫描悉数的索引记载，该表的拜访办法便是index
EXPLAINSELECTkey_part2FROMs1WHEREkey_part3=’a’
可以看到在key里边仍是用到了联合索引的，虽然依据最左前缀原则，只要检索条件为key_part1才能用到索引，这儿是因为检索条件和select回来列都是和联合索引相关的列，所以运用了联合索引扫描了悉数的索引记载，因为这样就不需求再回表找其他的列了（查的列都在索引上）
不需求回表就能查找到所需求的数据，称作索引掩盖
这时分再增加一个其他的列：
EXPLAINSELECTkey1, key_part2FROMs1WHEREkey_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字句，即没有用到表的状况
EXPLAINselect1
impossible where：where句子永远为false的状况
EXPLAINselect*FROMs1WHERE1!=1
这样也没有用到表，横竖条件都不对
where：运用全表扫描来履行针对某个表的查询，字句中有针对该表的查找条件，则在Extra中展示
EXPLAINselect*FROMs1WHEREcommon_field=’a’
common_field是一个不带索引的一般字段，因而type为all，Extra展示了句子是经过where履行的
no matching min/max row当查询列表处有min或许max聚合函数，可是没有契合where条件的记载时，将会提示该额定信息
EXPLAINSELECTMIN(key1)FROMs1WHEREkey1=’adqwdqweqwe’
而当where条件契合（或许底子没有where条件的时分），则显现Select tables optimized away，表明挑选优化后的表
EXPLAINSELECTMIN(key1)FROMs1
using index：当呈现索引掩盖，即查询和检索条件的列都在运用的索引里边，也便是不需求回表操作的状况
EXPLAINSELECTkey1FROMs1WHEREkey1=’a’
当呈现主键的状况也是掩盖索引
using index condition：即索引条件下推，考虑下面的sql查询：
EXPLAINSELECT*FROMs1WHEREkey1>’z’ANDkey1like’%a%’
这条sql履行的正常次序应该是：首要运用idx_key1的索引树，查询key1 > z的一切主键值，这儿找到了385条记载的主键，然后对这些主键进行回表操作，在聚簇索引中找到包括其他列的数据，然后判别剩下的过滤条件进行回来。
而索引条件下推针对特殊状况进行了优化：便是假如剩下的过滤条件针对的是索引列，则不需求在回表后进行判别，这样就可以削减回表的操作，可是rows仍为385
using join buffer：即根据块的嵌套循环算法：当被驱动表不可以有用利用索引加速拜访速度，mysql就会为其在内存中分配一块join buffer的内存块来加速拜访的速度
EXPLAINSELECT*FROMs1INNERJOINs2ONs1.common_field=s2.common_field
common_field是一个没有索引的列
not exists：在表衔接的时分，当where条件中被驱动表的某个列等于null，而这个列又有非空束缚的时分，Extra就会展示not exists
EXPLAINSELECT*FROMs1LEFTJOINs2ons1.key1=s2.key1WHEREs2.idISNULL
注意必定是被驱动表的列，假如是主驱动表呈现这种状况，会直接显现为impossible where，就不会再看被驱动表了
using union(index_merge)：or运用两个索引的状况，即前面type讲到的index_merge，这时分会将两个索引树查出的id取并集然后再回表在进行where条件过滤
EXPLAINSELECT*FROMs1WHEREkey1=’a’ORkey3=’a’
zero limit：limit为0的状况
file sort 文件排序：
- 有一些状况排序是可以用到索引的：
- EXPLAINSELECT*FROMs1ORDERBYkey1 LIMIT10;
- 这个查询利用idx_key1索引直接取出key1列的10条记载（依照索引列排序的），然后再拿着记载的主键值进行回表得到悉数列的值。可是更多状况下的排序操作无法利用到索引，只能在内存中（记载较少的状况）或许磁盘中进行排序，mysql把这种在内存或许磁盘中排序的办法统称为文件排序 file sort
- 可是这儿有个地方很不了解，为什么去掉limit或许limit较大的时分，就会变成文件排序？
- EXPLAINSELECT*FROMs1ORDERBYkey1 LIMIT97;
- 个人猜想：有一个地方需求注意，便是随着limit的增大rows也在增大，尤其是在limit为95左右的时分突然增大了许多，这是不是因为：limit较小的时分，经过索引次序得到的主键值也比较会集，这时分回表操作也是次序查询的等级，可是limit过大甚至没有的时分，主键值就会特别涣散（由所以依照key1索引列排序的，所以key1会集而主键值涣散），因而这时分回表读取操作适当所以随机查找的等级了，那这样查询优化器判别本钱后，还不如直接在内存或许磁盘中进行文件排序。
- 关于没有索引的查询，天然只能文件排序了：
- EXPLAINSELECT*FROMs1ORDERBYcommon_field LIMIT10;
using temporary：mysql在进行一些如去重、排序的功用的时分，假如不可以有用地利用索引，就或许需求经过树立内部的暂时表来完结。
EXPLAINSELECTDISTINCTcommon_fieldFROMs1;
履行方案中呈现暂时表不是一个很好的征兆，因为树立和保护暂时表都需求很大的本钱，应该尽量经过运用索引来替换暂时表

小结

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)
SQL 仿制 全屏

看着挺别扭，即下面：

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

【七千字】教你如何用MySQL分析查询语句Explain

剖析查询句子：EXPLAIN

1概述

能查到什么？

版别区别

数据准备

创立表

创立存储函数

存储进程

履行存储进程增加数据

Explain的输出列

1 id

2 select_type

3 table

4 partitions

5 type

6 possible_key 和 key

7 index_len （联合索引剖析）

8 ref

9 rows

10 filtered （结合rows剖析）

11 Extra

小结

Explain的进一步运用

Explain的四种输出格局

1 传统格局

2 JSON格局

3 Tree格局

4 可视化输出

Show Warnings的运用

作者信息

【七千字】教你如何用MySQL分析查询语句Explain

剖析查询句子：EXPLAIN

1概述

能查到什么？

版别区别

数据准备

创立表

创立存储函数

存储进程

履行存储进程增加数据

Explain的输出列

1 id

2 select_type

3 table

4 partitions

5 type

6 possible_key 和 key

7 index_len （联合索引剖析）

8 ref

9 rows

10 filtered （结合rows剖析）

11 Extra

小结

Explain的进一步运用

Explain的四种输出格局

1 传统格局

2 JSON格局

3 Tree格局

4 可视化输出

Show Warnings的运用

相关文章

Podman vs Docker：有什么区别？

✍🏻 技术视角深入ChatGPT ｜ 技术专题20期

Kubernetes手记（6）- POD 配置清单

前端食堂技术周刊第 52 期：Babel 7.19.0、Fresh 1.1、React Native 0.70、新的 Web 性能指标 INP

作者信息

✍🏻 技术视角深入ChatGPT ｜技术专题20期