性能文章>长达 1.7 万字的 explain 关键字指南!>

长达 1.7 万字的 explain 关键字指南!原创

327326

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

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

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 越大的组,其执行优先级越高。

对于下面这个例子:

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

该例子的输出为:

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

再来看下面这个例子:

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

该例子的输出为:

上面的输出一共有 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 查询。

-- 查询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

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

PRIMARY

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

-- 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' ) )

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

SUBQUERY

在 select 或 where 中包含子查询,那么 select_type 会被标记为 SUBQUERY。以上面的查询为例:

-- 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' ) )

在该查询中,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 能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。

explain select max(id) from teacher

system

表只有一行记录(等于系统表),这是 const 类型的特列。

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

const

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

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

explain select * from teacher where name = 'T002'

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

eq_ref

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

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

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

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

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

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

执行计划变为:

会看到 type 类型都变为 ref 了,eq_ref 消失了。

ref

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

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

explain select * from teacher where age = 24

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

表中数据为:

ref_or_null

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

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

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

index_merge

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

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

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

执行计划为:

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

range

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

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

EXPLAIN SELECT * FROM TEACHER where age between 10 and 20

执行计划为:

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

index

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

EXPLAIN SELECT id, age FROM TEACHER 

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

EXPLAIN SELECT id, enter_time FROM TEACHER 

查询 SQL 的 enter_time 字段不是索引,所以上面的查询就变成了全表查询(ALL)。

ALL

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

table 字段

表示数据来自哪张表

possible_keys 字段

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

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

key 字段

使用到的索引,如果为 NULL,则没有使用索引。

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

select * from teacher where name = 'T001'

上面这个查询中,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 列来执行索引查找。

mysql> explain select * from film_actor where film_id = 2;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | film_actor | ref  | idx_film_actor_id | idx_film_actor_id | 4       | const |    1 | Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+

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 找到了与行相联合匹配的行,就不再搜索了。

mysql> explain select distinct name from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
| id | select_type | table      | type  | possible_keys     | key               | key_len | ref          | rows | Extra                        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
|  1 | SIMPLE      | film       | index | idx_name          | idx_name          | 33      | NULL         |    3 | Using index; Using temporary |
|  1 | SIMPLE      | film_actor | ref   | idx_film_actor_id | idx_film_actor_id | 4       | test.film.id |    1 | Using index; Distinct        |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+

Using index

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

mysql> explain select id from film order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | PRIMARY | 4       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 

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

Using where

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

mysql> explain select * from film where id > 1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | film  | index | PRIMARY       | idx_name | 33      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

Using temporary

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

1. actor.name没有索引,此时创建了张临时表来distinct
mysql> explain select distinct name from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+

2. film.name建立了idx_name索引,此时查询时extra是using index,没有用临时表
mysql> explain select distinct name from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | idx_name      | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

Using filesort

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

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

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

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

1. actor.name未创建索引,会浏览actor整个表,保存排序关键字name和对应的id,然后排序name并检索行记录
mysql> explain select * from actor order by name;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | actor | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

2. film.name建立了idx_name索引,此时查询时extra是using index
mysql> explain select * from film order by name;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_name | 33      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

建表语句

建表语句如下:

/*
 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 思否

  • MySQL explain 详解 - butterfly100 - 博客园

 

点赞收藏
分类:标签:
树哥聊编程
请先登录,查看2条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

实现定时任务的六种策略

实现定时任务的六种策略

6
2