来源：MySQL数据库联盟

作者简介

马听，多年 DBA 实战经验，对MySQL、 Redis、MongoDB、Go等有一定了解，书籍《MySQL DBA精英实战课》作者，慕课网DBA体系课（https://class.imooc.com/sale/dba）讲师。

尽管现在很多SQL可以借助AI来优化，比如小编前面写的一篇文章：如何通过ChatGPT优化MySQL的SQL语句。

但是有些场景，可能ChatGPT并不能完全正确的优化，或者ChatGPT优化的SQL，需要我们再去校验，这个时候就需要用到执行计划的基本功了。

这一节我们就来详细介绍一下MySQL的执行计划工具explain。

Explain 可以获取 MySQL 优化器考虑使用的 SQL 语句执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL 。

使用方法：在查询语句前面加上 explain 运行就可以了。

这也是分析 SQL 时最常用的，也是最推荐的一种分析慢查询的方式。

这篇文章详细讲了执行计划各个字段的含义，大部分是不用记的，只要在使用时，再找到这篇文章对照就行。

1 准备

准备测试数据

为了便于理解，先创建两张测试表，建表及数据写入语句如下：

CREATE DATABASE martin; use martin;                      drop table if exists t1;       
CREATE TABLE `t1` (  `id` int NOT NULL auto_increment,  `a` int DEFAULT NULL,  `b` int DEFAULT NULL,  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',  PRIMARY KEY (`id`),  KEY `idx_a` (`a`),  KEY `idx_b` (`b`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;    
drop procedure if exists insert_t1; delimiter ;;create procedure insert_t1()        begin  declare i int;                     set i=1;                          while(i<=1000)do                    insert into t1(a,b) values(i, i);    set i=i+1;                        end while;end;;delimiter ;               call insert_t1();        
drop table if exists t2;   create table t2 like t1;   insert into t2 select * from t1;

尝试explain的用法

下面尝试使用 explain 分析一条 SQL，例子如下：

explain select * from t1 where b=100;

2 Explain的结果各字段解释

如下：

加粗的列为需要重点关注的项。

列名	解释
id	查询编号
select_type	查询类型：显示本行是简单还是复杂查询
table	涉及到的表
partitions	匹配的分区：查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表，该值为 NULL。
type	本次查询的表连接类型
possible_keys	可能选择的索引
key	实际选择的索引
key_len	被选择的索引长度：一般用于判断联合索引有多少列被选择了
ref	与索引比较的列
rows	预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确
filtered	按条件筛选的行的百分比
Extra	附加信息

其中 explain 各列都有各种不同的值，这里介绍几个比较重要列常包含的值:

下面将列出它们常见的一些值，可稍微过一遍，不需要完全记下来，在后续分析SQL时，可以返回查询这篇文章的内容并对比各种值的区别。

3 select_type各种值的解释

select_type 的值	解释
SIMPLE	简单查询(不使用关联查询或子查询)
PRIMARY	如果包含关联查询或者子查询，则最外层的查询部分标记为primary
UNION	联合查询中第二个及后面的查询
DEPENDENT UNION	满足依赖外部的关联查询中第二个及以后的查询
UNION RESULT	联合查询的结果
SUBQUERY	子查询中的第一个查询
DEPENDENT SUBQUERY	子查询中的第一个查询，并且依赖外部查询
DERIVED	用到派生表的查询
MATERIALIZED	被物化的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外层查询的每一行
UNCACHEABLE UNION	关联查询第二个或后面的语句属于不可缓存的子查询

4 type各种值的解释

type的值	解释
system	查询对象表只有一行数据,且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况
const	基于主键或唯一索引查询，最多返回一条结果
eq_ref	表连接时基于主键或非 NULL 的唯一索引完成扫描
ref	基于普通索引的等值查询，或者表间等值连接
fulltext	全文检索
ref_or_null	表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	利用索引进行范围查询
index	全索引扫描
ALL	全表扫描

上表的这些情况，查询性能从上到下依次是最好到最差。

5 key_len各种字段类型对应的长度

explain 中的 key_len 列用于表示这次查询中，所选择的索引长度有多少字节，常用于判断联合索引有多少列被选择了。下表总结了常用字段类型的 key_len：

列类型	KEY_LEN	备注
int	key_len = 4+1	int 为 4 bytes，允许为 NULL，加 1 byte
int not null	key_len = 4	不允许为 NULL
bigint	key_len=8+1	bigint 为 8 bytes，允许为 NULL 加 1 byte
bigint not null	key_len=8	bigint 为 8 bytes
char(30) utf8	key_len=30*3+1	char(n)为：n * 3 ，允许为 NULL 加 1 byte
char(30) not null utf8	key_len=30*3	不允许为 NULL
varchar(30) not null utf8	key_len=30*3+2	utf8 每个字符为 3 bytes，变长数据类型,加 2 bytes
varchar(30) utf8	key_len=30*3+2+1	utf8 每个字符为 3 bytes，允许为 NULL,加 1 byte,变长数据类型，加 2 bytes
datetime	key_len=8+1 (MySQL 5.6.4之前的版本)；key_len=5+1(MySQL 5.6.4及之后的版本)	允许为 NULL，加 1 byte

6 Extra常见值的解释

Extra 常见的值	解释	例子
Using filesort	将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序	explain select * from t1 order by create_time;
Using temporary	需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时	explain select * from t1 group by create_time;
Using index	使用覆盖索引	explain select a from t1 where a=111;
Using where	使用 where 语句来处理结果	explain select * from t1 where create_time='2019-06-18 14:38:24';
Impossible WHERE	对 where 子句判断的结果总是 false 而不能选择任何数据	explain select * from t1 where 1<0;
Using join buffer (hash join)	关联查询中，被驱动表的关联字段没索引	explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time);
Using index condition	先条件过滤索引，再查数据	explain select * from t1 where a >900 and a like '%9';
Select tables optimized away	使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是	explain select max(a) from t1;
......

7 对比有无索引的执行计划

条件字段是主键的执行计划

explain select * from t1 where id=100;

type是const，表示基于主键或唯一索引的查询

key 是PRIMARY，表示走了主键索引

row 表示扫描的行数，只扫描了一行。

条件字段有索引的执行计划

explain select * from t1 where b=100;

重点关注

type，这里是ref，表示：基于普通索引的等值查询，或者表间等值连接

key这个字段，这里可以看出来，是走了索引的。

然后再看rows，发现只扫描了1行

条件字段没索引的执行计划

删除b字段上的索引

alter table t1 drop index idx_b;

再来看刚才这条语句的执行计划

explain select * from t1 where b=100;

type，是ALL，表示：全表扫描。

key，是NULL，表示没走索引。

rows，这里其实是扫描了很多行，这里是估值，所以不一定准确。

8 获取分区信息

创建测试表并写入数据

CREATE TABLE sales (    sale_id INT,    sale_date DATE,    amount DECIMAL(10, 2)) PARTITION BY RANGE (YEAR(sale_date)) (    PARTITION p2022 VALUES LESS THAN (2023),    PARTITION p2023 VALUES LESS THAN (2024),    PARTITION p2024 VALUES LESS THAN MAXVALUE);
INSERT INTO sales (sale_id, sale_date, amount)VALUES(1, '2022-01-01', 100.50),(2, '2022-02-15', 200.75),(3, '2022-03-20', 150.00),(4, '2023-04-10', 300.20),(5, '2023-05-05', 250.80),(6, '2023-06-12', 180.30),(7, '2023-07-08', 220.40),(8, '2024-08-23', 270.60),(9, '2024-09-17', 320.90),(10, '2024-10-05', 280.75);

查询分区表里面的数据

select * from sales where sale_date='2024-09-17';

查看执行计划

explain select * from sales where sale_date='2024-09-17';

explain select * from sales where sale_date>'2023-01-01';

可以看到，在这种情况下，执行计划就可以显示分区信息。

9 获取正在执行语句的执行计划

在一个窗口构造一条慢查询

select *,sleep(100) from t1 limit 1;

在另外一次窗口查看当前连接

show processlist;

再来查询给定连接的执行计划

EXPLAIN FOR CONNECTION 12;

就可以看到，这个连接正在执行SQL的执行计划。

这个通常用来分析正在执行的问题SQL。

10 MySQL 8.0执行计划的新用法

树状执行计划

从MySQL 8.0.16开始，可以输出树状执行计划，并且能返回预估成本和预估的返回行数

explain format=tree select * from t1 where a=100;

cost表示预估成本信息；

rows表示预估扫描行数。

explain analyze

从MySQL 8.0.18开始，引入了EXPLAIN ANALYZE

使用这个，会执行SQL，并返回有关执行成本，返回行数，执行时间，循环次数等信息

explain analyze select * from t1 where a=100;

cost 表示预估的成本信息

rows 前面的表示预估值，后面的表示实际返回的行数

actual time 第一个值是获取第一行的实际时间，第二个值获取所有行的时间，如果循环了多次就是平均时间，单位毫秒

loops 循环次数

一文搞懂MySQL执行计划