概述

本文主要说明MySQL EXPLAIN用途、输出字段说明、Join Type和Extra及实验。
MySQL中explain语句是让你知道MySQL的执行计划，也是为你提供优化器是如何优化你的SQL的。
在实际业务中，我们主要用EXPLAIN来分析SELECT语句为什么会慢，为什么这个SELECT查询没有使用索引，或者SELECT语句使用了查询为什么依然很慢等等。

功能

explain用于想用户提供MySQL如何执行SQL语句。

可以用于分析的语句有SELECT、DELETE、INSERT、UPDATE和REPLACE五种。
优化器提供SQL执行计划信息。
使用连接ID分析该连接的SQL
SELECT语句可以使用SHOW WARNINGS可以看到额外的执行计划信息
EXPLAIN可以分析分区表

EXPLAIN输出

EXPLAIN输出列
join type说明
Extra信息说明

在我们实际业务开发中，我们主要关注的是join type和Extra信息，也是我们主要优化的两个点。

实验数据

实验基于的MySQL版本：

Server version:        8.0.25 MySQL Community Server - GPL

实验使用的建表及数据：

CREATE TABLE `sys_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
  `name_pinyin` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名拼音',
  `id_card` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '身份证号',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_idx_id_card` (`id_card`) USING BTREE COMMENT '唯一索引-身份证号',
  KEY `idx_phone_name` (`phone`,`name`) USING BTREE COMMENT '普通索引-手机号'
) ENGINE=InnoDB AUTO_INCREMENT=3495 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户';

CREATE TABLE `sys_user_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` int NOT NULL COMMENT '年龄',
  `phone` varchar(20) NOT NULL COMMENT '手机号',
  `email` varchar(255) NOT NULL COMMENT '邮箱',
  `birthday` date NOT NULL COMMENT '生日',
  `sys_user_id` int NOT NULL COMMENT '用户ID',
  `self_introduction` varchar(1000) NOT NULL COMMENT '自我介绍',
  `intrest` varchar(255) DEFAULT NULL COMMENT '兴趣',
  `intrest2` varchar(255) DEFAULT NULL COMMENT '兴趣2',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_idx_user_id` (`sys_user_id`) USING BTREE COMMENT '唯一索引-用户ID',
  KEY `idx_phone_email` (`phone`,`email`) USING BTREE COMMENT '普通索引-手机号-邮箱',
  KEY `idx_intrest` (`intrest`) USING BTREE COMMENT '普通索引-兴趣',
  FULLTEXT KEY `idx_fulltext_self_introduction` (`self_introduction`) COMMENT '全文索引-自我介绍'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息';

INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (1, '张三', 'zhangsan', '123456789', '13000000000');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (2, '李四', 'lisi', '200000000', '13000000001');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (3, '王五', 'wangwu', '200000001', '13000000002');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (4, '王五', 'wangwu', '200000003', '13000000002');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (5, '王五', 'wangwu', '200000004', '13000000003');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (6, '王五', 'wangwu', '200000005', '13000000004');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (7, '王五', 'wangwu', '200000006', '13000000005');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (8, '王五', 'wangwu', '200000007', '13000000006');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (9, '王五', 'wangwu', '200000008', '13000000007');
INSERT INTO `employees`.`sys_user`(`id`, `name`, `name_pinyin`, `id_card`, `phone`) VALUES (10, '王五', 'wangwu', '200000009', '13000000008');

INSERT INTO `employees`.`sys_user_info`(`id`, `age`, `phone`, `email`, `birthday`, `sys_user_id`, `self_introduction`, `intrest`, `intrest2`) VALUES (1, 1, '13000000000', 'zhangsan@dev.com', '2022-02-18', 1, 'The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.\r\n\r\nThe CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.\r\n\r\nThe length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.', '123', '456');
INSERT INTO `employees`.`sys_user_info`(`id`, `age`, `phone`, `email`, `birthday`, `sys_user_id`, `self_introduction`, `intrest`, `intrest2`) VALUES (2, 2, '13000000001', 'lisi@dev.com', '2022-02-18', 2, 'Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section 8.4.7, “Limits on Table Column Count and Row Size”.\r\n\r\nIn contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.', NULL, NULL);

输出字段

输出字段概览

列名	意思
id	SELECT标识符
select_type	查询类型
table	输出行的表
partitions	分区信息
type	join type
possible_keys	可以选择使用的索引
key	实际使用的索引
ref	与索引比较的列
行	评估可能要检测的行
filtered	根据表的查询条件需要检测的行与表格总行的百分比
Extra	额外信息，比如：using index, using where, using filesort

字段解释

select type

select type	意思	示例
SIMPLE	就是一个简单的查询，没有使用UNION或者子查询	explain select * from sys_user;
PRIMARY	主查询	explain select * from sys_user where id= (select sys_user_id from sys_user_info limit 1);
UNION	UNION语句	explain select from sys_user where id= (select sys_user_id from sys_user_info limit 1) union select from sys_user where id in (select sys_user_id from sys_user_info);
DEPENDENT UNION	UNION操作中，查询有内层的SELECT与外层的SELECT 有依赖关系	explain select * from sys_user su where su.id in (select u1.id as uid from sys_user u1 where phone=(select phone from sys_user u2 where u1.id=u2.id) union select t.id as uid from sys_user t where t.id= (select sys_user_id from sys_user_info limit 1) );
UNION RESULT	合并结果	参见UNION
SUBQUERY	子查询	explain select * from sys_user where id= (select sys_user_id from sys_user_info limit 1)
DEPENDENT SUBQUERY	内层子查询与外层子查询有依赖关系	explain select * from sys_user u1 where phone=(select phone from sys_user u2 where u1.id=u2.id);
DERIVED	子查询在FROM子查询	explain select from sys_user su1 join (select from sys_user group by phone) su2 on su1.id=su2.id;
DEPENDENT DERIVED		不好模拟
MATERIALIZED		不好模拟
UNCACHEABLE SUBQUERY	对于外层的主表，子查询不能被缓存，只能每次重新计算	不好模拟
UNCACHEABLE UNION	UNION内层不能被缓存，只能每次重新计算	不好模拟

Join Types

MySQL EXPLAIN 的Join Type字段有11种类型，11种类型分别为：

system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
index_subquery
range
index
ALL

以上11种类型从好到坏排序为：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>index_subquery>range>index>ALL。
这种排序从好到坏排序并不一定代表你的查询速度从好到坏，因为还要看表的数据量。比如：
在one_row_table表中只有一条数据，你执行SQL语句:

select * from one_row_table

这条语句会执行全表扫表，但是他的速度等于system级别，但是explain的输出如下：

MySQL [employees]> explain select * from one_row_table;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | one_row_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

但是在表数据量大时，这个就会非常慢了。所以要根据表的实际情况来实际分析。下面对各个级别分别说明和实验。

system级别

这是最好的一个级别，也是const的一个特列。这个级别会出现输出中的条件是表中只有一行数据。

const

const级别是只匹配到一行数据。
const级别出现的条件是：

主键
唯一索引

实验

使用主键查询一条数据

MySQL [employees]> explain select id from one_row_table where id=1;
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | one_row_table | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用唯一索引查询一条数据

MySQL [employees]> explain select id from one_row_table where id_card='200000000';
+----+-------------+---------------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys   | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | one_row_table | NULL       | const | uni_idx_id_card | uni_idx_id_card | 1023    | const |    1 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

eq_ref

连表查询使用了主键或唯一索引，必须使用=号运算符。比较的值可以是字段或者常量。

实验

连表查询

注意：这里只有sys_user表的join type是eq_ref

MySQL [employees]> EXPLAIN select * from sys_user, sys_user_info where sys_user.id=sys_user_info.sys_user_id;
+----+-------------+---------------+------------+--------+-----------------+---------+---------+-------------------------------------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys   | key     | key_len | ref                                 | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+-----------------+---------+---------+-------------------------------------+------+----------+-------+
|  1 | SIMPLE      | sys_user_info | NULL       | ALL    | uni_idx_user_id | NULL    | NULL    | NULL                                |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | sys_user      | NULL       | eq_ref | PRIMARY         | PRIMARY | 4       | employees.sys_user_info.sys_user_id |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+-----------------+---------+---------+-------------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

ref

查询使用了普通索引/二级索引，而不是主键索引或唯一索引。使用所以必须满足最左匹配原则。
ref级别规定需要使用=或<=>操作符。

实验

索引列

MySQL [employees]> EXPLAIN select * from sys_user_info where phone='13000000000';
+----+-------------+---------------+------------+------+---------------------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table         | partitions | type | possible_keys             | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sys_user_info | NULL       | ref  | idx_phone_email,idx_phone | idx_phone_email | 82      | const |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+------+---------------------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

连表ref

MySQL [employees]> EXPLAIN select * from sys_user, sys_user_info where sys_user.phone=sys_user_info.phone;
+----+-------------+---------------+------------+------+-----------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table         | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+---------------+------------+------+-----------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | sys_user_info | NULL       | ALL  | idx_phone_email | NULL | NULL    | NULL |    2 |   100.00 | NULL                                       |
|  1 | SIMPLE      | sys_user      | NULL       | ALL  | idx_phone       | NULL | NULL    | NULL |    3 |    50.00 | Using where; Using join buffer (hash join) |
+----+-------------+---------------+------------+------+-----------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

fulltext

查询使用了全文索引。

实验

MySQL [employees]> EXPLAIN select * from sys_user_info where match (`self_introduction`) against ('a');
+----+-------------+---------------+------------+----------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table         | partitions | type     | possible_keys                  | key                            | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+---------------+------------+----------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | sys_user_info | NULL       | fulltext | idx_fulltext_self_introduction | idx_fulltext_self_introduction | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+---------------+------------+----------+--------------------------------+--------------------------------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.00 sec)

eq_or_null

index_merge

unique_subquery

range

范围查询一般是这些操作符：=、 <>、 >、 >=、 <、 <=、 IS NULL、<=>、BETWEEN、LIKE或IN()

实验

MySQL [employees]> explain select * from sys_user where id in  (155,156,157,158,159,162,163,164,166,167,168,169,172,173);
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sys_user | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   14 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index

index是扫描这个索引，但是要分两种情况：

如果查询能够覆盖索引，那么Extra列会显示Using index提示。覆盖索引就只需要扫描索引就可以完成查询需求。
如果查询不能覆盖索引，那么Extra列不会显示Using index提示。不能覆盖索引就需要使用索引的主键回表查询记录。

实验

覆盖索引

Extra列说明使用了索引。

MySQL [employees]> explain select phone from sys_user;
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sys_user | NULL       | index | NULL          | idx_phone_name | 124     | NULL | 2981 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

非覆盖索引

Extra列未说明使用索引。

MySQL [employees]> explain select phone from sys_user_info order by id;
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sys_user_info | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | NULL  |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ALL

ALL级别是全表扫描。

实验

MySQL [employees]> explain select * from sys_user;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sys_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2482 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Extra

Extra字段主要关注Using filesort或Using temporary输出，如果看到有这两个输出，那么这个查询应该被优化。

Backward index scan: InnoDB表中优化器使用降序索引，这个会和Using index一起输出。
Child of 'table' pushed join@1
const row not found：根据查询条件没有找到行。
Deleting all rows：删除所有的行。
Distinct:
FirstMatch
Full scan on NULL key
Impossible HAVING： HAVING的条件不可能出现。
Impossible WHERE： WHERE子句的条件可能出现。比如：在一个不能为空的字段搜索空： explain select * from sys_user where id is NULL;
No tables used：没有FROM 子句或FORM DUAL
Not exists
Plan isn't ready yet
Using filesort：
Using index
Using index condition
Using temporary
Using where

MySQL Explain

概述

功能