长达 1.7 万字的 explain 关键字指南！

当你的数据里只有几千几万，那么 SQL 优化并不会发挥太大价值，但当你的数据里去到了几百上千万，SQL 优化的价值就体现出来了！因此稍微有些经验的同学都知道，怎么让 MySQL 查询语句又快又好是一件很重要的事情。要让 SQL 又快又好的前提是，我们知道它「病」在哪里，而 explain 关键字就是 MySQL 提供给我们的一把武器！

在我们所执行的 SQL 前面加上 explain 关键字，MySQL 就不会真正去执行这条语句，而是模拟优化器执行 SQL 查询语句，最后会输出一系列的指标告诉我们这条语句的性能如何，如下图所示。

```markdown
mysql> explain select * from student where id = 1 \G
******************************************************
           id: 1
  select_type: SIMPLE
        table: subject
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
******************************************************
```

总的来说，explain 关键字可以告诉我们下面这么多信息：

1.  表的读取顺序如何
2.  数据读取操作有哪些操作类型
3.  哪些索引可以使用
4.  哪些索引被实际使用
5.  表之间是如何引用
6.  每张表有多少行被优化器查询  
    ......

今天，我们就来介绍 explain 关键字的各个指标的含义。系好安全带，准备发车了！

为了方便讲解，这里新建了几张表，并初始化了一些数据（建表语句见附录）。这些表的关系如下：

-   一共有老师、学生、课程三个实体，分别为：teacher、student、course。
-   三个实体间的关系分别为：老师教学生的关系（teacher\_student）、学生的课程分数（student\_course）。

## ID 字段

ID 字段的值及其排列顺序，表明 MySQL 执行时从各表取数据的顺序。一般情况下遵循下面两个原则：

-   ID 相同的组，其执行优先级按照其顺序由上到下。
-   ID 越大的组，其执行优先级越高。

对于下面这个例子：

```sql
EXPLAIN SELECT
	teacher.* 
FROM
	teacher,
	teacher_student 
WHERE
	teacher_student.student_name = 's001' 
	AND teacher.NAME = teacher_student.teacher_name
```

该例子的输出为：

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_9853000.9191208847334837.jpeg)

上面的输出一共有 2 条记录，其 ID 都为 1，这表示其归为一组。对于 ID 相同的组，MySQL 按照顺序从上到下执行，即：先拿 teacher\_student 表的数据，再拿 teacher 表的数据。

再来看下面这个例子：

```sql
EXPLAIN SELECT
	* 
FROM
	teacher 
WHERE
	NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = 'S002' )
```

该例子的输出为：

![](http://doc.xiqi.site/media/202304/2023-04-10_084521_1279040.9758633283356314.jpeg)

上面的输出一共有 3 条记录，其中第 1、2 条的 ID 相同，第 3 条 ID 不同。那么其执行顺序就是 ID 值越大，其越早执行。ID 相同的，按顺序执行。上面的例子，最早拿 teacher\_student 表的数据，之后是一个子查询组成的表，最后拿 teacher 表的数据。结合 SQL 分析，这也符合我们的常识。因为我们必须先把子查询的值算出来，因此需要先把 teacher\_student 表里的数据拿出来，之后才可以拿去 teacher 表里查询。

## select\_type 字段

select\_type 字段表示该 SQL 是什么查询类型，一共有以下 6 种：

-   SIMPLE：简单查询，不包含子查询或 union 查询
-   PRIMARY：主键查询
-   SUBQUERY：在 select 或 where 中包含子查询
-   DERIVED：from 中包含子查询
-   UNION：
-   UNION RESULT

### SIMPLE

简单查询，不包含子查询或 union 查询。

```sql
-- 查询T001老师都教了哪些学生
EXPLAIN SELECT
	student.* 
FROM
	teacher,
	teacher_student,
	student 
WHERE
	teacher.NAME = 'T001' 
	AND teacher.NAME = teacher_student.teacher_name 
	AND teacher_student.student_name = student.NAME
```

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_8080410.8996830242585746.jpeg)

可以看出其 3 个查询都是简单（SIMPLE）查询。因为 ID 相同，所以其查询顺序是按顺序来的。首先从 teacher 表中取出数据，之后从 student 表取出数据，最后 teacher\_student 表取数据。

### PRIMARY

一般情况下，如果查询中包含了任何复杂的子查询，那么最外层查询会被标记为主查询。

```sql
-- PRIMARY 查询哪些老师教授了选修数学课的学生
EXPLAIN SELECT
	* 
FROM
	teacher 
WHERE
	NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = ( SELECT student_name FROM student_course WHERE course_name = 'shuxue' ) )
```

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_6875400.9996662653189626.jpeg)

在上面的查询中，首先是执行 ID 为 3 的查询，即去 student\_course 表取出选修了数学课的学生名字，之后再去进行最外层的查询。可以看到最外层查询的 select\_type 为 PRIMARY。

### SUBQUERY

在 select 或 where 中包含子查询，那么 select\_type 会被标记为 SUBQUERY。以上面的查询为例：

```sql
-- PRIMARY 查询哪些老师教授了选修数学课的学生
EXPLAIN SELECT
	* 
FROM
	teacher 
WHERE
	NAME IN ( SELECT teacher_name FROM teacher_student WHERE student_name = ( SELECT student_name FROM student_course WHERE course_name = 'shuxue' ) )
```

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_5840880.9867101492179083.jpeg)

在该查询中，where 中包含了子查询，因此在 explain 中有一个 ID 为 3 的查询被标记为 SUBQUERY。

### DERIVED

在 FROM 中包含子查询，那么 select\_type 会被标记为 SUBQUERY。

### UNION

类似包含 union 关键字的会被标记成 UNION 类型，这种查询方式比较少，这里不做深入讲解。

### UNION RESULT

类似包含 union 关键字的会被标记成 UNION RESULT 类型，这种查询方式比较少，这里不做深入讲解。

## type 字段

type 字段表示访问情况，通常用来衡量 SQL 的查询效率。其值的查询效率从最好到最差分别为：

-   NULL
-   system
-   const
-   eq\_ref
-   ref
-   fulltext
-   ref\_or\_null
-   index\_merge
-   unique\_subquery
-   index\_subquery
-   range
-   index
-   ALL

### NULL

NULL 表示 MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

```python
explain select max(id) from teacher
```

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_6751380.5402419700357227.jpeg)

### system

表只有一行记录（等于系统表），这是const类型的特列。

出现的情况较少，这里不深入介绍。

### const

const 表示该表最多有一个匹配记录。

通常情况下是 SQL 中出现了主键索引或唯一索引。

```csharp
explain select * from teacher where name = 'T002'
```

![](https://imgkr.cn-bj.ufileos.com/5c240fe2-e987-4176-94f4-98acbdd35827.png)

上面例子中，teacher.name 字段为唯一索引字段，所以通过该字段只能唯一找到一条记录，因此其 type 类型为 const。

### eq\_ref

eq\_ref 表示主键索引或唯一索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。

与 const 类型非常相似，唯一的区别是 eq\_ef 通常出现在联表的情况下，而 const 通常出现在单表情况下。

```sql
EXPLAIN SELECT
	* 
FROM
	teacher,
	teacher_student 
WHERE
	teacher.NAME = teacher_student.teacher_name
```

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_7903670.35201547218941576.jpeg)

从上面的执行结果可以看出，其首先全表扫描了 teacher\_student 表，之后使用 teacher.name 唯一索引去将联合 teacher 表的每一条记录。

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_7749960.000585262510619522.jpeg)

要注意的是，eq\_ref 这种情况重点在于：**读取本表中和关联表表中的每行组合成的一行。** 如果并没有关联表中每行这个概念，那么就不会出现 eq\_ref 这种类型。例如我在上面的 SQL 中加上 age 为 24 这个条件，即 SQL 为：

```sql
EXPLAIN SELECT
	* 
FROM
	teacher,
	teacher_student 
WHERE
	teacher.NAME = teacher_student.teacher_name and teacher.age = 24
```

执行计划变为：

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_7994000.22114864078564644.jpeg)

会看到 type 类型都变为 ref 了，eq\_ref 消失了。

### ref

ref 表示使用了非唯一索引扫描，会返回匹配某个单独值的所有行。

与 const 非常类似，只不过 ref 会匹配到多个记录，而 const 则只会匹配到单个记录。

```csharp
explain select * from teacher where age = 24
```

age 为普通索引，表中有 2 条记录。

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_7448430.1517517290783127.jpeg)

表中数据为：

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_8338160.26138227665329505.jpeg)

### ref\_or\_null

类似ref，但是可以搜索值为NULL的行。

```csharp
explain select * from teacher where age = 24 or age is null
```

当我们增加 `age is null` 查询条件后，其 type 字段就变成了 `ref_or_null`。

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_8222890.5794113338792062.jpeg)

### index\_merge

表示使用了索引合并的优化方法。

索引合并指的是：**对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。**

```sql
EXPLAIN SELECT * from teacher where id = 1 or age = 24
```

执行计划为：

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_8989310.12054791380709606.jpeg)

可以看到使用了 index\_merge 的查询类型。在 teacher 表中 id 和 age 都是索引，其将两个字段的索引结果进行合并了。

### range

range 表示检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引。

一般就是在你的 where 语句中出现 between、<>、in 等的范围查询。

```sql
EXPLAIN SELECT * FROM TEACHER where age between 10 and 20
```

执行计划为：

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_9360370.0320949900588009.jpeg)

上面语句中，我们使用 between 进行范围查询，因此 type 类型为 range。

### index

index 表示只遍历索引树，且只从索引树中获取数据。

```sql
EXPLAIN SELECT id, age FROM TEACHER 
```

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_9538310.15400977976820673.jpeg)

上面 SQL 中的 id、age 都是索引字段，可以直接从索引树中读取。因此其 type 字段为 index，表示此次查询数据可以直接从索引树获取到。但是如果查询的字段不在索引树中，那么就是全表扫描了。例如：

```sql
EXPLAIN SELECT id, enter_time FROM TEACHER 
```

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_9699590.04105285050308949.jpeg)

查询 SQL 的 enter\_time 字段不是索引，所以上面的查询就变成了全表查询（ALL）。

### ALL

ALL 表示该查询将遍历全表以找到匹配行，这是最糟糕的一种查询方式。

## table 字段

表示数据来自哪张表

## possible\_keys 字段

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用。

## key 字段

实际使用到的索引，如果为NULL，则没有使用索引。

查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

```csharp
select * from teacher where name = 'T001'
```

![](http://doc.xiqi.site/media/202304/2023-04-10_084520_9687720.14465818801356922.jpeg)

上面这个查询中，key 字段显示使用了 `udx_name` 这个索引，也就是 name 这个字段作为索引。

## key\_len 字段

这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 举例来说，film\_actor的联合索引 idx\_film\_actor\_id 由 film\_id 和 actor\_id 两个int列组成，并且每个int是4字节。通过结果中的key\_len=4可推断出查询使用了第一个列：film\_id列来执行索引查找。

key\_len计算规则如下：

### 字符串

-   char(n)：n字节长度
-   varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2

### 数值类型

-   tinyint：1字节
-   smallint：2字节
-   int：4字节
-   bigint：8字节

### 时间类型

-   date：3字节
-   timestamp：4字节
-   datetime：8字节

### 其他

如果字段允许为 NULL，需要1字节记录是否为 NULL

## ref 字段

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）。

## rows 列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

## Extra 列

这一列展示的是额外信息。

### distinct

一旦mysql找到了与行相联合匹配的行，就不再搜索了。

### Using index

这表示查找某个表的时候，所需要的信息直接从索引就可以拿到，而不需要再访问行记录。

上面例子中，我只是选择了 id 列，这个列本身是索引，其信息直接在索引树中就可以拿到，因此不需要再访问行记录。

### Using where

mysql 服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

### Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

### Using filesort

MySQL 中无法利用索引完成的排序操作称为「文件排序」。

在MySQL中的ORDER BY有两种排序实现方式：

1.  利用有序索引获取有序数据
2.  文件排序

在explain中分析查询的时候，利用有序索引获取有序数据显示 Using index ，文件排序显示 Using filesort。至于什么时候使用索引排序，什么时候使用文件排序，这个问题太过于复杂，这里不做深入介绍。

## 附录

### explain速查表

![](https://www.cnblogs.com/chanshuyi/p/media/15933052077677/15933062190446.jpg)

### 建表语句

建表语句如下：

```sql
/*
 Navicat Premium Data Transfer

Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80019
 Source Host           : localhost:3306
 Source Schema         : test

Target Server Type    : MySQL
 Target Server Version : 80019
 File Encoding         : 65001

Date: 22/06/2020 08:59:15
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of course
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES (2, 'shuxue');
INSERT INTO `course` VALUES (3, 'yingyu');
INSERT INTO `course` VALUES (1, 'yuwen');
COMMIT;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_name` (`name`),
  UNIQUE KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, 'S001', 24);
INSERT INTO `student` VALUES (2, 'S002', 23);
INSERT INTO `student` VALUES (3, 'S003', 22);
COMMIT;

-- ----------------------------
-- Table structure for student_course
-- ----------------------------
DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_name` varchar(20) DEFAULT NULL,
  `course_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_student_name` (`student_name`),
  KEY `idx_course_name` (`course_name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of student_course
-- ----------------------------
BEGIN;
INSERT INTO `student_course` VALUES (1, 'S001', 'yuwen');
INSERT INTO `student_course` VALUES (2, 'S001', 'shuxue');
INSERT INTO `student_course` VALUES (3, 'S001', 'yingyu');
INSERT INTO `student_course` VALUES (4, 'S002', 'yuwen');
INSERT INTO `student_course` VALUES (5, 'S002', 'shuxue');
INSERT INTO `student_course` VALUES (6, 'S003', 'yuwen');
COMMIT;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `enter_time` datetime DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_name` (`name`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of teacher
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES (1, 'T001', '2020-06-16 21:51:54', 12);
INSERT INTO `teacher` VALUES (2, 'T002', '2020-06-15 21:52:02', 12);
INSERT INTO `teacher` VALUES (3, 'T003', '2020-06-14 21:52:08', 24);
INSERT INTO `teacher` VALUES (4, 'T004', '2020-06-14 21:52:08', 24);
COMMIT;

-- ----------------------------
-- Table structure for teacher_student
-- ----------------------------
DROP TABLE IF EXISTS `teacher_student`;
CREATE TABLE `teacher_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(20) DEFAULT NULL,
  `student_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_teacher_name` (`teacher_name`),
  KEY `idx_student_name` (`student_name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of teacher_student
-- ----------------------------
BEGIN;
INSERT INTO `teacher_student` VALUES (1, 'T001', 'S001');
INSERT INTO `teacher_student` VALUES (2, 'T001', 'S002');
INSERT INTO `teacher_student` VALUES (3, 'T001', 'S003');
INSERT INTO `teacher_student` VALUES (4, 'T002', 'S001');
INSERT INTO `teacher_student` VALUES (5, 'T002', 'S002');
INSERT INTO `teacher_student` VALUES (6, 'T003', 'S001');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
```

## 参考资料

-   [一张图彻底搞懂MySQL的 explain - 个人文章 - SegmentFault 思否](https://segmentfault.com/a/1190000021458117?utm_source=tag-newest#item-5-3)
-   [MySQL explain详解 - butterfly100 - 博客园](https://www.cnblogs.com/butterfly100/archive/2018/01/15/8287569.html)