MySQL-Explain执行计划

欢迎大家来到IT世界,在知识的湖畔探索吧!

什么是执行计划

有了慢查询语句后，就要对语句进行分析。一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，我们需要搞懂EPLATNEXPLAIN的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。

通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析查询语句或是表结构的性能瓶颈，总的来说通过EXPLAIN我们可以：

l 表的读取顺序

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

l 哪些索引可以使用

l 哪些索引被实际使用

l 表之间的引用

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

执行计划的语法

执行计划的语法其实非常简单：在SQL查询的前面加上EXPLAIN关键字就行。比如：

EXPLAIN select * from table1

欢迎大家来到IT世界,在知识的湖畔探索吧!

重点的就是EXPLAIN后面你要分析的SQL语句

除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPOATE语句前边都可以加上EXPLAIN，用来查看这些语句的执行计划，不过我们这里对SELECT语句更感兴趣，所以后边只会以SELECT语句为例来描述EsxPLAIN语句的用法。

执行计划详解

为了让大家先有一个感性的认识，我们把EXPLAIN语句输出的各个列的作用先大致罗列一下:

欢迎大家来到IT世界,在知识的湖畔探索吧! explain select * from order_exp;

id ：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id

select_type ：SELECT 关键字对应的那个查询的类型

table ：表名

partitions ：匹配的分区信息

type ：针对单表的访问方法

possible_keys ：可能用到的索引

key ：实际上使用的索引

key_len ：实际使用到的索引长度

ref ：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows ：预估的需要读取的记录条数

filtered ：某个表经过搜索条件过滤后剩余记录条数的百分比

Extra ：—些额外的信息

查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列。

对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字，所以在包含子查询的查询语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值，比如这样:

EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a';

但是这里大家需要特别注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了，比如说:

欢迎大家来到IT世界,在知识的湖畔探索吧!EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE order_no = 'a');

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

包含UNION子句

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

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

这个语句的执行计划的第三条记录为什么这样？UNION子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，怎么去重呢? MySQL使用的是内部的临时表。正如上边的查询计划中所示，UNION 子句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union1，2>的临时表（就是执行计划第三条记录的table列的名称)，id为NULL表明这个临时表是为了合并两个查询的结果集而创建的。

跟UNION 对比起来，UNION ALL就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含UNION ALL子句的查询的执行计划中，就没有那个id为NULL的记录，如下所示:

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

table

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

可以看见，只涉及对s1表的单表查询，所以EXPLAIN输出中只有一条记录，其中的table列的值是s1，而连接查询的执行计划中有两条记录，这两条记录的table列分别是s1和s2.

partitions

和分区表有关，一般情况下我们的查询语句的执行计划的partitions列的值都是NULL。

type

我们前边说过执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型，其中的type列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：

出现比较多的是system>const>eq_ref>ref>range>index>ALL

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

system

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

explain select * from test_myisam;

当然，如果改成使用InnoDB存储引擎，试试看执行计划的type列的值是什么。

const

就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。因为只匹配一行数据，所以很快。

例如将主键置于where列表中

EXPLAIN SELECT * FROM s1 WHERE id = 716;

B+树叶子节点中的记录是按照索引列排序的，对于的聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的。B+树矮胖，所以这样根据主键值定位一条记录的速度很快。类似的，我们根据唯一二级索引列来定位一条记录的速度也很快的，比如下边这个查询：

SELECT * FROM order_exp WHERE insert_time=’’ and order_status=’’ and expire_time=’’ ;

这个查询的执行分两步，第一步先从u_idx_day_status对应的B+树索引中根据索引列与常数的等值比较条件定位到一条二级索引记录，然后再根据该记录的id值到聚簇索引中获取到完整的用户记录。

MySQL把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。

不过这种const访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，组成索引的每一个列都是与常数进行等值比较时，这个const访问方法才有效。

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊，因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说is null不可以使用const访问方法来执行。

eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的〈如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的访问方法就是eq_ref。

(驱动表与被驱动表： A表和B表join连接查询，如果通过A表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到B表中查询数据，然后合并结果。那么我们称A表为驱动表，B表为被驱动表)

比方说:

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

从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问方法是eq_ref，表明在访问s1表的时候可以通过主键的等值匹配来进行访问。

ref

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

本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他属于查找和扫描的混合体

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

对于这个查询，我们当然可以选择全表扫描来逐一对比搜索条件是否满足要求，我们也可以先使用二级索引找到对应记录的id值，然后再回表到聚簇索引中查找完整的用户记录。

由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref。

对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种ref访问方法比const要差些，但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了）。

range

如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法，一般就是在你的where语句中出现了between、<、>、in等的查询。

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

EXPLAIN SELECT * FROM s1 WHERE order_no IN ('a', 'b', 'c'); EXPLAIN SELECT * FROM s1 WHERE order_no > 'a' AND order_no < 'b';

这种利用索引进行范围匹配的访问方法称之为：range。

此处所说的使用索引进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引。

index

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

EXPLAIN SELECT insert_time FROM s1 WHERE expire_time = '2021-03-22 18:36:47';

all

最熟悉的全表扫描，将遍历全表以找到匹配的行

EXPLAIN SELECT * FROM s1;

possible_keys与key

在EXPLAIN 语句输出的执行计划中,possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。比方说下边这个查询:。

EXPLAIN SELECT order_note FROM s1 WHERE insert_time = '2021-03-22 18:36:47';

上述执行计划的possible keys列的值表示该查询可能使用到u_idx_day_status,idx_insert_time两个索引，然后key列的值是u_idx_day_status，表示经过查询优化器计算使用不同索引的成本后，最后决定使用u_idx_day_status来执行查询比较划算。

key_len

key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，计算方式是这样的：

对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8，那么该列实际占用的最大存储空间就是100 x 3 = 300个字节。

如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。

对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

⽐如下边这个查询：

EXPLAIN SELECT * FROM s1 WHERE id = 718;

由于id列的类型是bigint，并且不可以存储NULL值，所以在使用该列的索引时key_len大小就是8。

对于可变长度的索引列来说，比如下边这个查询:

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

由于order_no列的类型是VARCHAR(50)，所以该列实际最多占用的存储空间就是50*3字节，又因为该列是可变长度列，所以key_len需要加2，所以最后ken_len的值就是152。

MySQL在执行计划中输出key_len列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列(复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用)，而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用1个字节还是2个字节。

rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。比如下边两个个查询:

EXPLAIN SELECT * FROM s1 WHERE order_no > 'z'; EXPLAIN SELECT * FROM s1 WHERE order_no > 'a';

我们看到执行计划的rows列的值是分别是1和10573，这意味着查询优化器在经过分析使用idx_order_no进行查询的成本之后，觉得满足order_no> ‘ a ‘这个条件的记录只有1条，觉得满足order_no> ‘ a ‘这个条件的记录有10573条。

filtered

查询优化器预测有多少条记录满⾜其余的搜索条件，什么意思呢？看具体的语句：

EXPLAIN SELECT * FROM s1 WHERE id > 5890 AND order_note = 'a';

从执行计划的key列中可以看出来，该查询使用 PRIMARY索引来执行查询，从rows列可以看出满足id > 5890的记录有5286条。执行计划的filtered列就代表查询优化器预测在这5286条记录中，有多少条记录满足其余的搜索条件，也就是order_note = ‘a’这个条件的百分比。此处filtered列的值是10.0，说明查询优化器预测在5286条记录中有10.00%的记录满足order_note = ‘a’这个条件。

对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，比方说下边这个查询:

EXPLAIN SELECT * FROM s1 WHERE id > 5890 AND order_note = 'a';

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

Extra

顾名思义，Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句

喜欢本文就点赞转发收藏吧