MySQL优化之执行计划

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

 表的读取顺序

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

 哪些索引可以使用

 哪些索引被实际使用

 表之间的引用

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

MySQL表创建：

create table t1(
id int(11) not null auto_increment comment '订单主键',
order_no varchar(11) character set utf8 collate utf8_general_ci not null comment '订单编号',
create_time datetime(0) not null default current_timestamp(0) on update current_timestamp(0) comment '创建订单时间',
expire_time datetime(0) not null comment '创建过期时间',
order_status smallint(6) not null default 0 comment '订单状态 0 未支付 1 已支付 -1 已过期',
primary key (id) using btree,
unique index idx_time_status(create_time, order_status, expire_time) using btree,
index idx_order_no(order_no) using btree,
index idx_expire_time(expire_time) using btree
) engine = InnoDB AUTO_INCREMENT = 1 character set utf8 collate = utf8_general_ci row_format = Dynamic;

create table t2(
id int(11) not null auto_increment comment '订单主键',
order_no varchar(11) character set utf8 collate utf8_general_ci not null comment '订单编号',
create_time datetime(0) not null default current_timestamp(0) on update current_timestamp(0) comment '创建订单时间',
expire_time datetime(0) not null comment '创建过期时间',
order_status smallint(6) not null default 0 comment '订单状态 0 未支付 1 已支付 -1 已过期',
primary key (id) using btree,
unique index idx_time_status(create_time, order_status, expire_time) using btree,
index idx_order_no(order_no) using btree,
index idx_expire_time(expire_time) using btree
) engine = InnoDB AUTO_INCREMENT = 1 character set utf8 collate = utf8_general_ci row_format = Dynamic;

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

例如：

EXPLAIN SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 where
t1.create_time = t2.create_time) OR order_no = 'DD000000008'

执行计划详解：

 先大致解释一下上图中各个字段的含义：

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

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

table: 表名

partitions：匹配的分区信息

type：针对单表的访问方法

possible_keys：可能用到的索引

key：实际上使用的索引

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

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

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

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

Extra：—些额外的信息

接下来详细的解释说明一下上述各个字段的含义和作用：

1、先看table：

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

例如：

explain select * from t1 join t2

explain select * from t1 

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

2、id:

查询语句一般都以 SELECT 关键字开头，比较简单的查询 语句里只有一个 SELECT 关键字， 稍微复杂一点的连接查询中也只有一个 SELECT 关键字，比如:

SELECT * FROM t1 INNER J0IN t2 ON t1.id = t2.id WHERE t1.order_status = 0 ;

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

1、查询中包含子查询的情况 比如下边这个查询语句中就包含 2 个 SELECT 关键字:

SELECT * FROM t1 WHERE id IN ( SELECT id FROM t2);

2、查询中包含 UNION 语句的情况 比如下边这个查询语句中也包含 2 个 SELECT 关键字:

SELECT * FROM t1 UNION SELECT * FROM t2 ;

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

补充：UNION 子句会把多个查询 的结果集合并起来并对结果集中的记录进行去重， MySQL 使用的是 内部的临时表。正如上边的查询计划中所示，UNION 子句是为了把 id 为 1 的查 询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为的临时表（就是执行计划第三条记录的 table 列的名称)，id 为 NULL 表明这个 临时表是为了合并两个查询的结果集而创建的。 跟 UNION 对比起来，UNION ALL 就不需要为最终的结果集进行去重，它只 是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需 要使用临时表。

3、select_type:

MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为 select_type 的属性。

常用的select_type 取值如下：

SIMPLE：简单的 select 查询,不使用 union 及子查询

PRIMARY：最外层的 select 查询

UNION：UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果 集

UNION RESULT：UNION 结果集

SUBQUERY：子查询中的第一个 select 查询,不依赖于外 部查询的结果集

DEPENDENT UNION：UNION 中的第二个或随后的 select 查询,依赖于外部查 询的结果集 DEPENDENT SUBQUERY：子查询中的第一个 select 查询,依赖于外部查询的 结果集 DERIVED： 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些 子查询, 把结果放在临时表里。

MATERIALIZED：物化子查询

下面模拟一些产生的场景：

1、SIMPLE:

简单的 select 查询，查询中不包含子查询或者 UNION

EXPLAIN SELECT * FROM t1 WHERE order_no = 'DD000000008';

连接查询也算是 SIMPLE 类型

EXPLAIN SELECT * FROM t1 INNER JOIN t2

 2、PRIMARY

对于包含 UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询 组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY，例如：

EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2

 3、UNION

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

4、UNION RESULT

MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查 询的 select_type 就是 UNION RESULT，可以对比第二个例子。

5、SUBQUERY

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

EXPLAIN SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) OR order_no = 'DD000000008'

 补充：

1、semi-join：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询 的表做 join 操作。关键词是“上拉”。对于子查询，其子查询部分相对于父表的 每个符合条件的元组，都要把子查询执行一轮。效率低下。用半连接操作优化子 查询，是把子查询上拉到父查询中，这样子查询的表和父查询中的表是并列关系， 父表的每个符合条件的元组，只需要在子表中找符合条件的元组即可。简单来说， 就是通过将子查询上拉对父查询中的数据进行筛选，以使获取到最少量的足以对 父查询记录进行筛选的信息就足够了。

2、子查询物化：子查询的结果通常缓存在内存或临时表中。

3、关联/相关子查询：子查询的执行依赖于外部查询。多数情况下是子查询的 WHERE 子句中引用了外部查询的表。

6、DERIVED

对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED。

例如：

EXPLAIN SELECT * FROM (SELECT id, count(*) as c FROM t1 GROUP BY id) AS derived_t1 where c >1;

从执行计划中可以看出， id 为 2 的记录就代表子查询的执行方式，它的 select_type 是 DERIVED ，说明该子查询是以物化的方式执行的。id 为 1 的记录 代表外层查询。

4、partitions:

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

5、type:

type 表明了这个访问方法/访问类型是个什么，结果值从最好到最坏依次是：

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

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

1、system

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

创建一个t3表，引擎是MyISAM，

 

 接下来将引擎换成Innodb：

2、const

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

例如：

EXPLAIN SELECT * FROM t1 where id=4

 说明：B+树叶子节点中的记录是按照索引列排序的，对于的聚簇索引来说，它对应 的 B+树叶子节点中的记录就是按照 id 列排序的。B+树矮胖，所以这样根据主键 值定位一条记录的速度很快。

另外根据唯一二级索引列来定位一条记录 的速度也很快的，比如：

explain SELECT * FROM t1 WHERE create_time ='2022-03-02 16:34:07' and order_status=1 and expire_time='2022-03-02 16:35:07'

 这个查询的执行分两步，第一步先从 idx_time_status对应的 B+树索引中根 据索引列与常数的等值比较条件定位到一条二级索引记录，然后再根据该记录的 id 值到聚簇索引中获取到完整的用户记录。 MySQL 把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定 义为：const，意思是常数级别的，代价是可以忽略不计的。不过这种 const 访问方法只能在主键列或者唯一二级索引列和一个常数进行 等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，组成索引 的每一个列都是与常数进行等值比较时，这个 const 访问方法才有效。

3、eq_ref

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

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

例如：

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

4、ref

当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该 表的访问方法就可能是 ref。 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能 会找到多个符合条件的行，所以他属于查找和扫描的混合体

例如：

explain SELECT * FROM t1 WHERE order_no = 'DD000000008'

 说明：ref和eq_ref区别，一个是主键或者唯一二级索引的情况，一个是普通索引的情况

5、unique_subquery

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

例如：

EXPLAIN SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 where
t1.create_time = t2.create_time) OR order_no = 'DD000000008'

6、range

如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法， 一般就是在你的 where 语句中出现了 between、、in 等的查询。 这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点， 而结束语另一点，不用扫描全部索引。

EXPLAIN SELECT * FROM t1 WHERE order_no IN ('DD000000008', 'DD000000009', 'DD000000007')

7、index

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

EXPLAIN SELECT create_time FROM t1 WHERE order_status =1

8、all

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

EXPLAIN SELECT * FROM t1

6、possible_keys 与 key:

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

例如：

EXPLAIN SELECT create_time FROM t1 WHERE expire_time = '2021-03-22 18:36:47'

 说明：possible keys 列中的值并不是越多越好，可能使用 的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话， 尽量删除那些用不到的索引。

7、key_len:

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大 长度，计算方式是： 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就 是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型 是 VARCHAR(100)，使用的字符集是 utf8，那么该列实际占用的最大存储空间就 是 100 x 3 = 300 个字节。 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多 1 个 字节。 对于变长字段来说，都会有 2 个字节的空间

例如：

EXPLAIN SELECT * FROM t1 WHERE id = 4

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

对于可变长度的索引列来说，比如:

EXPLAIN SELECT * FROM t1 WHERE order_no = 'DD000000008';

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

说明：执行计划的生成是在 MySQL server 层中的功能，并不是针对具体某个存储 引擎的功能，MySQL 在执行计划中输出 key_len 列主要是为了让我们区分某个使 用联合索引的查询具体用了几个索引列(复合索引有最左前缀的特性，如果复合 索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合 索引是否部分使用，还是全部使用)，而不是为了准确的说明针对某个具体存储 引擎存储变长字段的实际长度占用的空间到底是占用 1 个字节还是 2 个字节。 Key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长 度。在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len 是根据表定义计算而得，不是通过表内检索出的。 注意：char 和 varchar 跟字符编码也有密切的联系，比如 latin1 占用 1 个字节， gbk 占用 2 个字节，utf8 占用 3 个字节。

看下面这个sql:

EXPLAIN SELECT * FROM t1 WHERE create_time ='2022-03-02 16:35:07' 
and order_status =1 and expire_time = '2022-03-02 16:36:07'

 根据key_len长度，我们可以知道，联合索引三个字段全用上了

EXPLAIN SELECT * FROM t1 WHERE create_time ='2022-03-02 16:35:07' 
and order_status =1 

 根据key_len长度，我们可以知道，联合索引用了两个字段。

8、ref:

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、 eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery 其中之一时,ref 列 展示的就是与索引列作等值匹配的是谁，比如只是一个常数或者是某个列。

比如：

EXPLAIN  SELECT * FROM t1 WHERE order_no = 'DD000000008'

 可以看到 ref 列的值是 const，表明在使用 idx_order_no 索引执行查询时，与 order_no 列作等值匹配的对象是一个常数。

再例如：

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

可以看到对被驱动表 t2 的访问方法是 eq_ref，而对应的 ref 列的值是 dongsq.t2.id，这说明在对被驱动表进行访问时会用到 PRIMARY 索引，也就是 聚簇索引与一个列进行等值匹配的条件，与 t1 表的 id 作等值匹配的对象就是 dongsq.t2.id 列(注意这里把数据库名也写出来了)。

有的时候与索引列进行等值匹配的对象是一个函数

例如：

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t2.order_no= UPPER(t1.order_no); 

可以看到在查询计划的 ref 列⾥输出的是 func

9、rows:

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

EXPLAIN SELECT * FROM t1 WHERE order_no > 'DD000010000';

EXPLAIN SELECT * FROM t1 where order_no > 'DD000681760';

 需要强调的是这个rows统计是一个大概值：

EXPLAIN SELECT * FROM t1 WHERE order_no > 'DD000000001';

 当order_no大于DD000000001，统计rows数和大于DD000010000 一样。

10、filtered:

查询优化器预测有多少条记录满⾜其余的搜索条件，例如：

EXPLAIN SELECT * FROM t1 WHERE id > 589000 AND order_status = 1;

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

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

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.order_no = t2.order_no
WHERE t1.create_time > '2023-06-17 01:30:54' and t1.order_status =1;

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

11、Extra

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

No tables used ：当查询语句的没有 FROM 子句时将会提示该额外信息。

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

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

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

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

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.order_no = t2.order_no
WHERE t1.create_time > '2023-06-17 01:30:54' and t1.order_status =1;

Using where : 当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中 有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。

EXPLAIN SELECT * FROM t1 where order_status =1

 Using filesort : 有一些情况下对结果集中的记录进行排序是可以使用到索引的，比如下边这 个查询：

EXPLAIN SELECT * FROM t1 order by order_status limit 10

说明：很多情况下排序操作无法使用到索引，只能在 内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL 把这种在内存中或者磁盘上进行排序的方式统称为文件排序。如果某个查询需要 使用文件排序的方式执行查询，就会在执行计划的 Extra 列中显示Using filesort

需要注意的是，如果查询中需要使用 filesort 的方式进行排序的记录非常多， 那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为 使用索引进行排序

Using temporary ： 在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如 去重、排序之类的，比如我们在执行许多包含 DISTINCT、GROUP BY、UNION 等 子句的查询过程中，如果不能有效利用索引来完成查询，MySQL 很有可能寻求通 过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计 划的 Extra 列将会显示 Using temporary 提示： 

EXPLAIN select order_status , COUNT(*) AS amount FROM t2 GROUP BY
order_status ;

EXPLAIN select order_no , COUNT(*) AS amount FROM t2 GROUP BY
order_no ;

 如果根据索引分组，效率会高很多。