mysql explain执行计划详解-蒲公英云

前言

在日常开发中，经常会碰到mysql性能调优的问题，比如说某个功能一开始使用的时候，响应挺快，但是随着时间的推移，应用的访问量，数据量上去之后，发现越来越慢，甚至更糟糕，通常来说，排除了网络相关的因素之后，大多数情况下都是由sql问题引起的

因此，如何对查询的sql语句进行优化就成了关键所在，但是对不少开发同学来说，sql调优的范围太大，经常会显得无从下手，基于经验是一方面，另一方面需要对一条sql的底层执行原理有着较为深入的理解，这样才不会显得毫无头绪，其中，掌握explain关键字的使用对于mysql的调优有着重要的意义，下面将详细介绍下explain的使用

实验准备1：准备3张表，电影表，演员表和电影演员关联表，分别为,film表，actor表和film_actor表

CREATE TABLE `film` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dataflow`.`film` (`id`, `name`) VALUES ('1', 'film1');
INSERT INTO `dataflow`.`film` (`id`, `name`) VALUES ('2', 'film2');
INSERT INTO `dataflow`.`film` (`id`, `name`) VALUES ('3', 'film3');
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dataflow`.`actor` (`id`, `name`, `update_time`) VALUES ('1', 'a', '2021-01-16 18:46:51');
INSERT INTO `dataflow`.`actor` (`id`, `name`, `update_time`) VALUES ('2', 'b', '2021-01-27 18:47:03');
INSERT INTO `dataflow`.`actor` (`id`, `name`, `update_time`) VALUES ('3', 'c', '2021-01-05 18:47:16');
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) DEFAULT NULL,
  `actor_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dataflow`.`film_actor` (`id`, `film_id`, `actor_id`) VALUES ('1', '1', '1');
INSERT INTO `dataflow`.`film_actor` (`id`, `film_id`, `actor_id`) VALUES ('2', '1', '2');
INSERT INTO `dataflow`.`film_actor` (`id`, `film_id`, `actor_id`) VALUES ('3', '2', '1');

要看一条sql语句的执行计划，只需要在sql语句之前加上explain关键字即可

explain select (select 1 from actor where id=1)
from (select * from film where id=1) dd

在这里插入图片描述
执行上面的加上explain关键字执行的sql之后可以看到一个执行计划的列表，我们要做的就是对这个列表中的各项参数进行分析即可，下面对各个参数进行简单的说明

1、id列的编号就是 select 的序列号

即有几个select就有几个id，并且id出现的顺序是按照select出现的顺序增长的
如果出现了关联查询，则id=1的表为驱动表

2、select_type

查询类型，说明查询的种类，select_type的类型在实际使用中出现可能有多种，举几种常用的简单介绍

simple：简单查询，查询不包含子查询或者union,大多数简单的业务查询都会出现simple这个类型

EXPLAIN select * from film  where id=2

在这里插入图片描述

但是如果查询语句比较复杂，比如出现了子查询，嵌套子查询，关联查询之后，这个select_type可能会出现多种情况，且看下面的sql语句

explain select (select 1 from actor where id=1)
from (select * from film where id=1 union select * from film where id=1) dd

在这里插入图片描述

可以看到，通过这个比较复杂的sql语句，一条sql语句的select_type出现了很多种情况，这个对应实际开发业务场景，可能是比较常见的，下面针对这个情况做一下详细的说明

在这条sql中，主要布恩有，有子查询，还有一个子查询嵌套关联查询

PRIMARY:复杂查询中最外侧的select
DERIVED:包含在from子句中的子查询，mysql会将结果放在一个临时表中，也叫做派生表
UNION：对应union关键字后面的这个select查询，union的意思表示将前后两个select查询的结果进行去重后汇总的结果
UNION RESULT：union前后两个结果集进行去重后的真正结果集，但是一般来说，尽量不要使用union，因为会产生临时表，这个可以在extra关键字那一列看到，因为临时表无法使用索引进行优化
SUBQUERY：在select子句中出现的子查询

总结来说，遇到类似复杂的sql语句，通过explain之后的结果，只需要按照id出现的顺序号的大小依次进行查找分析即可，没有id的，则代表是中间的执行结果

3、table

这一行的执行正在访问的表是哪一个，比如这个驱动表就是film
在这里插入图片描述
如果没有表名称，则代表将相关的表通过执行引擎进行了计算，比如这里的UNION RESULT 对应的table是：表示将第三和第四步的两个操作的结果进行了union

4、partions

分区表，假如你的表在创建的时候采用了分区表的话，在进行查询的时候这里将会产生响应的结果

5、type

表示关联类型或访问类型，即MySql决定如何查找表中的行，常见的可能出现的值按照执行效率从高到低排列如下：

system
const 【常用】
eq_ref 【常用】
ref 【常用】
fulltext
ref_or_null 【常用】
index_merge
unique_subquery
index_subquery
range 【常用】
index 【常用】
ALL 【常用】

const ：常量引用，主键筛选时出现

mysql对查询的某部分进行优化，并将其转化为一个常量，常用于primary key 或者unique key的所有列与常数比较时，所以表最多出现一个匹配行，读取一次，这样速度比较快

explain select from (select from film where id = 1) temp

在这里插入图片描述

eq_ref ：关联查询

primary key 或者unique key的所有部分被连接使用，最多只会返回一条符合条件的记录，这可能是在const之外最好的连接类型了，简单的select类型不会出现这种情况

explain select * from film_actor left join film on film_actor.film_id = film.id;

ref : 非主键或非唯一性索引的数据检索

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

explain select * from film where name = ‘film1’;

在这里插入图片描述

ref_or_null : 包含了null的数据检索

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

explain select * from film where name = ‘film1’ or name is null;

在这里插入图片描述

range ：范围内的数据检索

范围扫描，通常出现在in()，between，>，<等操作中，使用一个索引来检索给定范围内的行数据,需要说明的是，当查询时候的范围特别大的时候，比如 id>1的数据量相当大的时候，type可能出现的是ALL而不是range了

explain select * from actor where id > 1;

在这里插入图片描述

index ：通过索引进行的全表扫描
ALL ：全表扫描

index和all类似，不同的是，index通常只需要扫描索引树，而ALL则出现在全表扫描，意味着mysql需要对表的数据进行全部范围的扫描，如果是这样的情况，就需要增加索引来进行优化了

6、possible_keys

显示查询可能会使用那些索引进行查找，mysql执行引擎会分析当前的sql语句，如果查询时可能使用到了索引或者可能会用到的索引就会展示在这里

explain select * from actor where id > 1;

在这里插入图片描述
比如这里的sql语句，查询id > 1的数据时，可以走主键索引，这里的possible_keys就为PRIMARY，如果存在，就会在下一列的key中列举出来，即key代表最终执行这条sql语句时使用的索引，当然这里也可能什么都没有

7、key
显示mysql实际采用哪个索引来优化对表的访问

8、key_len

显示mysql在索引里使用的字节数，通过这个值可以具体算出来使用了索引中的哪些列

9、ref

显示了在key列记录的索引中，表查找值所用到的列或者常量，常见的有const，func，NULL,字段名(如:film.id)

explain select * from film_actor left join film on film_actor.film_id = film.id

在这里插入图片描述
10、rows

表示mysql估计要检索并读取的行数，注意这个并不是最终检索出来的结果集里面的行数

11、filtered

是一个百分比的值，代表(rows * filtered ) / 100 ，这个结果将会与前表产生交互，通常在关联查询的时候并且关联查询的条件中存在索引的情况下，rows * filtered表示一个预估的计算行数（了解即可）

12、extra

展示在执行计划之外的附属信息，下面举几种常见出现的信息

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

explain select DISTINCT name from film left join film_actor on film.id = film_actor.film_id;

在这里插入图片描述
Using index: 一般发生在对表的请求列，都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录，是性能高效的表现,Using index通常也叫做覆盖索引,即检索数据时只需要索引就可以完成对数据的检索

在这里插入图片描述

Using where : mysql执行引擎在检索完毕后再对数据进行过滤，即先读取整行数据，再按照where条件进行检查，符合条件的留下，不符合的就丢弃

explain select * from film where id > 1;

在这里插入图片描述
Using temporary : mysql在进行数据检索并返回结果集使用了临时表，通常出现了这种情况需要进行优化操作

explain select DISTINCT name from actor;

在这里插入图片描述
比如在上述的例子中，查询name字段时由于该字段并没有设索引因此在最终返回结果集之前，将查询到的结果放到一个临时表中，通过临时表进行去重得到最终的结果，因此使用了Using temporary

对name字段创建索引：

CREATE INDEX idx_name ON actor (name);

再次使用explain，这时候就变成了 Using index，明显性能提升了
在这里插入图片描述
Using filesort : 采用文件扫描对结果进行计算排序（我们更期望使用索引排序），效率很差（也叫文件内排序），通过在使用 order by的时候出现

explain select * from actor order by name;

在这里插入图片描述
但是将上面的sql简单修改为下面的这样，再次执行explain，

需要说明的是：对于排序，只有select字段与order by字段都被索引覆盖的时候才会使用到Using index

本篇到这里差不多就要结束了，以上通过实例sql分别说明了explain关键字解析出来的各个参数的含义，以及常见的使用场景和注意事项，事实上，在实际开发过程中，sql语句可能要复杂很多，但是万变不离其宗，只有掌握了基本的看懂explain之后分析参数的方法，才能为下一步的sql优化做准备，更深入的有兴趣的同学可以在此基础上继续深入探究，本篇到此结束，最后感谢观看！