explain详解-蒲公英云

explain是干嘛的？

使用explain可以模拟优化器执行sql语句，分析查询语句的结构，是否使用索引等等

使用方法：
在查询语句select关键字前面加上explain关键字，如下图的格式，然后就会返回分析的结果

在这里插入图片描述
下面来详解使用explain后返回的数据字段的含义

首先这是表结构：

DROP TABLE IF EXISTS `actor`; 3 CREATE TABLE `actor` ( 4 `id` int(11) NOT NULL, 5 `name` varchar(45) DEFAULT NULL, 6 `update_time` datetime DEFAULT NULL, 7 PRIMARY KEY (`id`) 8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 910 INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22 15:27:18'), (2,'b','20 17‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18'); 1112 DROP TABLE IF EXISTS `film`; 13 CREATE TABLE `film` ( 14 `id` int(11) NOT NULL AUTO_INCREMENT, 15 `name` varchar(10) DEFAULT NULL, 16 PRIMARY KEY (`id`), 17 KEY `idx_name` (`name`) 18 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1920 INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2'); 2122 DROP TABLE IF EXISTS `film_actor`; 23 CREATE TABLE `film_actor` ( 24 `id` int(11) NOT NULL, 25 `film_id` int(11) NOT NULL, 26 `actor_id` int(11) NOT NULL, 27 `remark` varchar(255) DEFAULT NULL, 28 PRIMARY KEY (`id`), 29 KEY `idx_film_actor_id` (`film_id`,`actor_id`) 30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 3132 INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

id字段列：

id有几个就代表这个sql语句被解析成多少个select. 其中id越大，代表执行的优先级别越高，id相等则从上到下依次执行，id为NULL的最后执行

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

在这里插入图片描述
2.select_type字段列：代表查询的语句的复杂类型
1）.simple:简单查询
2）.primary:复合查询的最外层查询
3）.subquery:是select 后面的子查询

explain select (select 1 from actor where id = 1);

在这里插入图片描述

4）.derived是紧跟在from后面的子查询，MySQL会将结果存放在一个临时表中，也称为派生表
在这里插入图片描述
为什么这里查出来的是一个简单的语句呢？而不是想象中的derived类型呢，原因很简单，在mysql5.7以后，对派生表进行优化了，优化器引入derived_merge。当然我们也可以通过设置参数将派生表优化给关闭。

set optimizer_switch='derived_merge=off'

在这里插入图片描述
5）union:紧跟union后面的select
3.table字段列：表示访问的是哪一个表
4.type列：表示MYSQL决定如何查找表中的行。
他有七个级别，依次从最优到最差分别为：system>const>eq_red>red>range>index>ALL
一般来说，最好达到ref级别，实在达不到，range级别也不差。
1）NULL:当type为null时，表示查询语句在执行过程中被优化，不用访问表，直接从索引中得到值

EXPLAIN SELECT min(id) from film;

在这里插入图片描述
2）const:表示对查询的部分进行优化，并将其转化为一个常量。用于primary key 或者unique key的所有列与常量比较时，所以表中只有一条记录，查询速度快。

EXPLAIN SELECT *from film where id =1;

在这里插入图片描述
3）eq_ref:primary key 或者unique key 索引的所有部分被连接使用，最多只返回一条符合条件的记录。

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

在这里插入图片描述
4）ref:相比rq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行

explain select * from film where name = 'film1';

在这里插入图片描述
5）range:范围扫描通常出现在in(),between,>,<,>=等操作中。

explain select * from actor where id > 1;

在这里插入图片描述
6)index:扫描全索引，一般是扫描某个二级索引，她不会从索引根节点查找，而是直接对二级索引的叶子节点遍历和扫描。速度较慢。这种一般为使用了覆盖索引，二级索引一般比较小。（因为select * 这里面包含二级索引字段，所以就会走二级索引）

explain select * from film;

7）all:即全表扫描，扫描聚集索引下的所有叶子节点，因为select * 这里面的字段都没有建立除了聚集索引外的索引，所以就只会走聚集索引，如果不知道聚集索引的可以去看一下这篇博文（https://editor.csdn.net/md/?articleId=107474948），因为聚集索引叶子节点存了data数据多，所以效率就会低

explain select * from actor;

在这里插入图片描述
5.possible_keys列
表示该查询语句可能使用哪个索引，如果为NULL,则表明没有相关索引以供查询，这时候可以在where语句后面字段加个索引，看是否提高了性能
6.key列
表示实际用了哪个索引，如果possible_keys不为空，key为null，说明数据少，不需要走索引，直接全表扫描。
7.key_len列
这表示用到的索引字段的字节数

计算规则如下：
（当然，索引的最大长度为768，如果超过了这个长度，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。）

字符串 :

char(n)：n字节长度
varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

数值类型:

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

时间类型:

date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL

8. ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
9. rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
10. Extra列
这一列展示的是额外信息。常见的重要值如下
1）Using index：使用覆盖索引覆盖索引定义：如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键
在这里插入图片描述
2)Using where：where后面查询的字段没有建立索引，使用 where 语句来处理结果

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是