Explain执行计划详解

2023-09-29 21:01:58

作者：IT王小二

博客：https://itwxe.com

一条查询 SQL 语句为什么会执行很慢？执行的时候走了什么索引？多表查询的时候 SQL 大体执行流程是怎么样的？Explain 执行计划详解给各位客官一一道来。

一、示例MySQL安装方式和版本

首先需要 MySQL 安装的客官看这两篇，小二演示使用的是 Docker 的安装方式：

小二安装完的 MySQL 数据库版本为 5.7.36。

二、Explain工具简介

通常我们在生产环境会碰到查询速度比较慢的 SQL 语句，那么怎么知道 SQL 语句为什么执行慢呢？这个时候就需要用到 Explain 执行计划来分析我们的语句了。

通过使用 Explain 关键字可以模拟优化器执行 SQL 查询语句，执行会返回执行计划的信息，而不是执行这条SQL，当然如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 。

总的来说，通过 Explain 从而知道 MySQL 是如何处理你的 SQL 语句的，分析查询语句或是表结构的性能瓶颈。那么通过 Explain 执行计划可以知道：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可能被使用
  • 哪些索引实际被使用
  • 表之间的引用
  • 每张表估计有多少行会被执行

三、本文用到的示例表

CREATE TABLE `author` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `author` (`id`, `name`, `update_time`) VALUES (1,'itwxe','2022-01-12 19:27:18'), (2,'admin','2022-01-12 19:27:18'), (3,'superAdmin','2022-01-12 19:27:18');

CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `blog` (`id`, `title`) VALUES (1,'blog1'),(2,'blog2'),(3,'blog3');

CREATE TABLE `author_blog` (
  `id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  `blog_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_author_blog_id` (`author_id`,`blog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `author_blog` (`id`, `author_id`, `blog_id`) VALUES (1,1,1),(2,2,1),(3,1,2);

四、Explain的变种

Explain 的语法非常简单，只需要在查询语句前面加一个 EXPLAIN 关键字即可。

explain select * from author where id = 1;

Explain执行计划详解

如果你发现你的版本少了 partitions 或者 filtered 列字段信息的话，那是因为在 MySQL5.7 的某个小版本之前的版本是没有这两个字段的。rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数(前一个表指 explain 中的 id 值比当前表 id 值小的表)。

那么低版本想要看到这两个字段也是有办法的，这就是小二说的 Explain 的两个变种。

  • explain extended：会在低版本 MySQL explain 的基础上额外提供一些查询优化的信息 filtered 列。
  • explain partitions：会在低版本 MySQL explain 的基础上额外提供一个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

在 explain 紧随其后执行的 show warnings 命令可以得到优化之后的查询语句，从而看到优化器优化了什么东西，当然优化后的 SQL 并不一定符合 SQL的语法，但是 MySQL 可以识别并可以执行。

Explain执行计划详解

五、Explain中的列

下面小二就来说说 Explain 中每个列的信息，当然每个列的值只会介绍常见的几个，如果没提到各位小伙伴可以查看 Explain 官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1. id

id 列表示的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按 select 出现的顺序增长的，id 列越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。

explain select * from author where id = (select author_id from author_blog where id = 2);

Explain执行计划详解

可以看到子查询 id 为 2，所以先执行子查询再执行外层查询。

2. select_type

select_type 列表示对应行查询的类型。

1）SIMPLE：简单查询，不包含子查询和 union。

explain select * from author where id = 1;

Explain执行计划详解

2）PRIMARY：复杂查询中最外层的 select。

3）SUBQUERY：包含在 select 中的子查询(不在 from 子句中)。

4）DERIVED：包含在 from 子句中的子查询。MySQL 会将结果存放在一个临时表中，也称为派生表(derived的英文含义)。

用这个例子来了解 PRIMARY、SUBQUERY 和 DERIVED 类型。

# 关闭 MySQL5.7 新特性对衍生表的合并优化
set session optimizer_switch = 'derived_merge=off';

explain select (select 1 from blog where id = 1) from (select 1 from author where id = 1) tmp;

Explain执行计划详解

别问小二为啥写这种语句，一切的一切都是为了演示效果啊~~~

开启 MySQL5.7 新特性默认对衍生表的合并优化，就可以看到不一样的效果了。

set session optimizer_switch = 'derived_merge=on';

Explain执行计划详解

5）UNION：在 union 中的第二个和随后的 select。

6）UNION RESULT：从 union 临时表检索结果的 select。

用这个例子来了解 UNION、UNION RESULT 类型。

explain select id from author where id = 1 union select id from blog where id = 2;

Explain执行计划详解

3. table

table 列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table 列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

Explain执行计划详解

当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1和2表示参与 union 的 select 行 id。

Explain执行计划详解

4. type

type 列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL ，一般来说，得保证查询达到 range 级别，最好达到 ref 级别。

除了上面几个拥有优先级的值之外，NULL 表示 MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

explain select min(id) from author;

Explain执行计划详解

1）const、system：MySQL 能对查询的某部分进行优化并将其转化成一个常量(可以看 show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条数据匹配时为system

explain select * from (select * from author where id = 1) tmp;

show warnings;

Explain执行计划详解

2）eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

explain select * from author_blog left join blog on author_blog.blog_id = blog.id;

Explain执行计划详解

3）ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者联合索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

  • . 简单 select 查询，title 是普通索引（非唯一索引）
explain select * from blog where title = 'blog1';

Explain执行计划详解

  • 关联表查询，idx_author_blog_id 是 author_id 和 blog_id 的联合索引，这里使用到了 author_blog 的左边前缀 author_id 部分。
explain select author_id from author left join author_blog on author.id = author_blog.author_id;

Explain执行计划详解

4）range：范围扫描通常出现在 in，between，>，<，>=，<= 等操作中，使用一个索引来检索给定范围的行。

explain select * from author where id > 1;

Explain执行计划详解

5）index：扫描全索引就能拿到结果，一般是扫描某个二级索引或者联合索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引或者联合索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用 覆盖索引(索引覆盖)，二级索引一般比较小，所以这种通常比 ALL 快一些。

覆盖索引(索引覆盖)定义：首先得说明的是覆盖索引不是一种索引类型，而是二级索引或者联合索引就包含所需要查询的所有字段，不需要再 回表 进行查询数据行获取其它字段值，这种情况一般可以说是用到了覆盖索引。

当然为啥不需要再回表，相信看过小二 深入理解MySQL索引底层数据结构 这篇文章的都晓得的哈，不知道的小伙伴可以看看哦。

explain select * from blog;

Explain执行计划详解

6）ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

explain select * from author;

Explain执行计划详解

5. possible_keys

possible_keys 列表示查询 可能使用 哪些索引来查找，但是最终查询可能不使用索引。

explain 时可能出现 possible_keys 列有可以使用的索引，但是 key 列显示 NULL 的情况，这种情况是因为 MySQL 经过查询成本计算，MySQL 认为索引对此查询速度不如全表扫描，最终选择了全表查询。

如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

6. key

key 列表示 MySQL 实际使用 哪个索引来对该表进行查询。

如果没有使用索引，则该列是 NULL。如果想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 force index (强制走某个索引)、ignore index (强制不走某个索引)。

例如：blog 表强制走 idx_title 索引和强制不走 idx_title 索引。

Explain执行计划详解

7. key_len

key_ len 列表示 MySQL 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

explain select * from author_blog where author_id = 1;

explain select * from author_blog where author_id = 1 and blog_id = 2;

Explain执行计划详解

在 author_blog 表中 idx_author_blog_id 是 author_id 和 blog_id 的联合索引，author_id 和 blog_id 都为 int 类型，int 类型占用 4 个字节。通过 key_len=4 可以推断出第一条 SQL 使用了 author_id 列，而第二条 SQL 中 key_len=8 可以对推断出使用了 author_id 列和 blog_id 列。

key_lenth 计算机规则：

  • 字符串，char(n) 和 varchar(n)，5.0.3以后版本中，n 均代表字符数，而不是字节数，如果是 utf-8，一个数字 或字母占1个字节，一个汉字占3个字节。

    • char(n)：如果存汉字长度就是 3 * n 字节。
    • varchar(n)：如果存汉字则长度是 3 * n + 2 字节。因为 varchar 是变长字符串，加的2字节用来存储字符串长度。

  • 数值类型

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

  • 时间类型

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

  • 如果字段允许为 NULL，需要1字节记录是否为 NULL。例如当允许 char(n) 为 NULL 时，存汉字长度为 3 * n + 1。

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

8. ref

ref 列表示在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const(常量)，字段名(例如film.id)。

9. rows

rows 列表示 MySQL 估计要读取并检测的行数，需要注意的是，是估计值，并非最后结果集里的行数。

10. Extra

Extra 列表示的是一些额外信息，常见重要值如下：

1）Using index：使用覆盖索引。

explain select blog_id from author_blog where author_id = 2;

Explain执行计划详解

2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。

explain select * from author where name = 'itwxe';

Explain执行计划详解

3）Using index condition：查询的列不完全被索引覆盖，需要回表查询。

explain select * from author_blog where author_id > 1;

Explain执行计划详解

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

  • author.name 没有索引，此时创建了一张临时表来 distinct。
explain select distinct name from author;

Explain执行计划详解

  • blog.title 建立了 idx_title 索引，此时查询是 extra 是 Using index 而不是 Using temporary。
explain select distinct title from blog;

Explain执行计划详解

5）Using filesort：将用外部排序而不是索引排序，需要注意的是不要被这个 Using filesort 名字欺骗了，并非出现这个就会使用磁盘排序，而是数据较小时从内存排序，否则需要在磁盘排序。这种情况下一般也是要考虑使用索引来优化的。

  • author.name 没有索引，会查找整个 author 表，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。
explain select * from author order by name;

Explain执行计划详解

  • blog.title 建立了 idx_title 索引，此时查询时 extra 是 using index。
explain select * from blog order by title;

Explain执行计划详解

6）Select tables optimized away：使用某些聚合函数(比如 max、min)来访问存在索引的某个字段时出现。

explain select min(id) from author;

Explain执行计划详解

说到这里 Explain 执行计划的每个列和常见的值都介绍完了，小伙伴们下期见~~~

都读到这里了，来个 点赞、评论、关注、收藏 吧！

上一篇：AutoCAD教程笔记


下一篇：手把手教你用Python提取PDF中的表格