环境

MySQL版本8.3.0

数据准备

新建一个explain_test的数据库，包含三张表：演员表、影片表、影片与演员关联表。

表结构如下所示：

 

什么是执行计划

根据MySQL的执行计划信息，可以用来分析当前查询的执行过程，是否用到序列，是否可以在某些地方进行优化等操作。

在navicate中，使用EXPLAIN关键字，就可以看到该SQL的执行计划信息。

下面的这个SQL本身没有实际意义，只是为了说明执行计划的信息。

EXPLAIN
SELECT (select 1 from actor where id = 1) a
from (select * from  film where id = 1) t1;

执行计划解析

id

id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。id=1是驱动表。

select_type

查询类型，说明查询的种类，常见种类有以下几种：

• simple：简单查询，查询中不包含子查询和union。

•  primary：复杂查询中最外侧的select。

 

• derived：包含在from子句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表。

• union：在union中的第二个随后的select。

• union result：从union临时表检索结果的select。

因为这个SQL中使用的时union关键字，是需要去重的，所以就会出现union result。如果把union改为union all，那么就不会产生union result。

所以在可以用union all时，尽量用union all，可以减少临时表的产生，加快执行速度。

• subquery：包含在select中的子查询。

table

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

如果查询的是具体表，那就显示表名称。如果是个结果集，那就显示对应编号，例如“union3,4”就代表id为3和4的结果再进行联合查询。

partitions

说明查询作用在哪个分区上。

type

这一列表示关联类型或访问类型，即MySQL决定如何查询表中数据。

执行效率排序：

• system
• const：(常用)

mysql能对查询的某部分进行优化并将其转化成一个常量。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。（一般就是根据主键查询）

 

• eq_ref：(常用)

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

• ref：(常用)

相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。（非主键或非唯一性索引的数据检索）

• fulltext

全文检索

ref_or_null：(常用)

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

• index_merge
• unique_subquery
• index_subquery
• range：(常用)

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

• index：(常用)

和ALL一样，不同就是mysql只需扫描索引树，无需回表，这通常比ALL快一些。

• all：(常用)

即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

possible_keys

这一列显示查询可能使用哪些索引来查找。

上图显示的可能索引是主键索引。

key

这一列显示mysql实际采用哪个索引来优化对该表的访问。

key_len

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

例如: key=PRIMARY，主键类型为int ，长度为4，所以key_len=4。

ref

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

说明索引与列的引用关系。

rows

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

filtered

这一列是一个百分比的值,代表 (rows * filtered) / 100 ,这个结果将于前表产生交互。

预估 3* 100% = 3条数据与film产生交互。

Extra

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

• distinct

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

• Using index（常用）

这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录(回表)。是性能高的表现。using index也叫索引覆盖。

• Using where

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

• using temporary

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

因为actor表的name没有索引，所以做去重操作时，就会创建临时表。

这种情况在name字段上，创建一个索引，然后就可以提升效率。如下所示：

通过创建索引，再次查询时，就使用索引。

• using filesort

采用文件扫描对结果进行计算排序，效率很差。

如果把查询的字段改为name（name字段上有索引），这时候就是Using index。

这是因为当select 字段与排序字段相同时，Extra 为 Using Index。

对于排序，只有select 字段 与order by 字段都被索引覆盖是才允许使用Using Index。

创建索引：

create index idx_name_ut on actor(name,update_time);

再次查询：

这里有个点需要注意下，如果order by字段的顺序与索引顺序不一致，也会导致查询速率变慢，如下所示，把name，update_time两个字段的顺序调换一下：

产生文件检索的原因是，只对update_time字段使用了索引，name字段并没有用到索引，所以产生了Using filesort。

所以order by字段的顺序与索引需要保持一致。