MySQL的explain简书_mysql的explain命令

一 简介

使用explain命令可以模拟优化器执行sql语句，从而知道mysql是如何处理我们写的sql语句的，还可以分析你的查询语句或者是结构的性能瓶颈

二 注意事项

在select语句之前增加explain关键字以后，mysql会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条sql(如果from中包含子查询，则仍会执行该子查询，将结果放入临时表中)

三 数据准备

创建如下3个表，演员，电影，演员电影中间表

电影表对电影名称name字段创建普通索引

演员表对电影id和演员id联合辅助索引，非联合主键索引

DROP TABLE IF EXISTS `actor`;

CREATE TABLE `actor` (

`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 `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;

CREATE TABLE `film` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_name` (`name`) // 电影名称索引

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;

CREATE TABLE `film_actor` (

`id` int(11) NOT NULL,

`film_id` int(11) NOT NULL,

`actor_id` int(11) NOT NULL,

`remark` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_film_actor_id` (`film_id`,`actor_id`) //电影id和演员id联合辅助索引，非联合主键索引！！！

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

四 explain结果每一列解析

4.1 id列

id列的编号是select的序列号，有几个select就会有几个id，并且id的顺序是按照select出现的顺序来顺序增长的，mysql将select查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)

复杂查询分为三类

简单子查询

派生表(from语句中的子查询)

union查询

id列越大，执行优先级越高，id相同则从上往下执行，id为null最后执行

(1)简单子查询

explain select( select 1 from actor limit 1) from file

image.png

(2)from字句中的子查询

explain select id from (select id from film) as der

查询执行时有临时表别名为der，外部select查询引用了该临时表

image.png

(3)union查询

见4.2节union案例，union实际用的不太多

union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL

4.2 select_type列

select_type 表示对应行是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种

explain select * from film where id = 1

image.png

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

image.png

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

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

PRIMARY：复杂查询中最外层的 select，使用了id为3的查询结果衍生出来的表

explain select 1 union all select 1

image.png

union：在 union 中的第二个和随后的 select

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

4.3 table列

改行表示explain的一行sql正在访问哪个表

当from子句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，因此先执行id=N的查询

当有union时，union result的table列的值为,1和2表示参与union的select的id

4.4 type列(重要)

关联类型，表示mysql将如何找到查找表中的行，查询数据行的大概范围

结果从最好到最坏依次是

system>const>eq_ref>ref>range>index>ALL

一般需要保证查询达到range级别，最好达到ref

(1)system

表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，可以忽略不计

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

image.png

(2)const

表的查询结果只有一行，一般出现在主键索引和唯一索引上，因为是唯一，所以读取1次，速度比较快

select * from film where id = 1

(3)eq_ref(const之外最好的性能)

表连接的时候，关联的b表的主键索引或者唯一索引，b表根据主键索引或者唯一索引最多返回一条记录，这样关联的效率高

比如film表的id=2，film只能关联出一条记录，file_actor能关联出多条记录，即如果我

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

image.png

(4)ref

表连接的时候，关联的b表采用普通辅助索引(非唯一索引)或者唯一索引的部分前缀，一般都是用在多表连接上的，关联的字段不是主键索引或者唯一索引 即a left join b on a.filmname=b.name

(4.1)简单 select 查询，name是普通索引(非唯一索引)

explain select * from film where name='film1'

image.png

使用唯一索引(

索引名：idx_film_actor_id |

构成： file_id和actor_id

)的部分前缀film_id

image.png

explain select * from film_actor where film_id=1

image.png

explain select * from film_actor where actor_id=1

image.png

(4.2)关联表查询

idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分

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

image.png

(5)range

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

explain select * from actor where id > 1

image.png

之所以范围查找速度慢，原因一：查询出的记录多，原因二：比如上面的id>1，除了第一条记录id=1，其他记录都大于1，等于进行了全表扫描

(6)index

会扫描全表索引，通常比ALL速度快，原因是index是在索引中读取所有记录(可以设置把索引都加载到内存中)，而ALL是在硬盘上读取

explain select * from film;

image.png

film表2个字段，id字段为主键索引，name字段为普通辅助索引，要想是index，则必须表的所有字段都有索引

(7)ALL

不会走索引，顺序从第一行到最后一行查找所需要的行，全表扫描，这种情况需要添加索引优化

explain select * from actor;

image.png

之所以这次是ALL不是index，主要是因为actor表3个字段，id，name，update_time只有id有主键索引，其他两个字段上都没索引，所以无法采用全表索引查询

尝试只查询id，name

EXPLAIN select id,name from actor

image.png

为name字段加上索引再执行

image.png

4.5 possible_keys列

意思是这一次查询可能使用到的索引

可能出现possible_keys有值，而key为null的情况，这种情况一般发生在表中记录不多，mysql认为使用索引对查询帮助不大，因此选择了全表扫描

4.6 key列

意思是这次查询实际使用哪个索引来提高查询性能，如果没有使用索引，该列为null没如果想让mysql强制使用possible_keys列中的索引，可以使用force index

4.7 key_len列

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

比如，file_actor表联合索引idx_file_actor_id由 film_id 和 actor_id 两个int列组成，并且每个int是4个字节

explain select * from film_actor where film_id = 2;

image.png

通过上图中结果中的key_len=4可推断出查询使用了联合索引的第一个列file_id列

如果加上另一个字段

explain select * from film_actor where film_id = 2;

image.png

可以看出这次用了联合索引的全部字段

附注：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

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

4.8 ref列

此列显示了在key列记录的索引中，表查询值所用到的列或者是常量，常见的有：const(常量)，字段名(例：film.id)

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

image.png

用到了b表的主键索引，b表主键索引相比较的是a表的film_id,study是数据库名称

4.9 rows列

mysql估计要读取并检测的行数，该行数不是结果集中的行数

4.10 extra列

覆盖索引：索引包含了所有要查询的字段

using index

使用了覆盖索引，查询的列被索引覆盖(查询的列都位于联合索引里面，由联合索引数据结构，省去了根据索引key来找到索引值的步骤，直接从key获取)，并且where筛选条件是索引的前导列(最左前缀的字段)，是性能高的表现，对于innodb，如果该联合索引是辅助索引的话，会提高性能

explain select film_id from film_actor where film_id=1

image.png

using where;using index

使用了覆盖索引，查询的列被索引覆盖，并且where筛选条件是索引列之一，但是不是索引的前导列，意味着不能直接通过索引找到结果，但是可以通过索引找到select后的字段

explain select film_id from film_actor where actor_id=1

image.png

using where

未使用覆盖索引，查询的列未被索引覆盖/未被完全覆盖，且where筛选条件非索引的前导列

explain select remark from film_actor where actor_id=1

image.png

explain select * from actor where name='a'

image.png

上面的这个* 既包含了有索引的列id，还包含了没有索引的name和updatetime，因此查询的列未完全被索引覆盖，且where使用的条件name也没索引，优化的话：直接name创建索引，不用select * 改用select name

null

查询的列未完全被索引覆盖(部分字段未被索引覆盖)，但是where筛选条件是联合索引的前导列，意味着使用了索引，但是部分字段未被索引覆盖，不是纯粹的用到了索引(select后面字段没用)，也不是纯粹的没用索引(where后面的用了)

explain select * from film_actor where film_id = 1; //或者* 换成remark

image.png

using index condition

类似于using where，未使用覆盖索引，查询的列未被索引覆盖/未被完全覆盖，并且where条件是联合索引前导列的范围

explain select * from film_actor where film_id>1 // 有问题，最后是using where

using temporary

mysql需要创建一张临时表来处理查询，会拖慢速度，一般出现在distinct语句，group by，子查询。出现这种情况一般要进行优化，使用覆盖索引来进行优化

actor表的name没有索引，此时系统创建了张临时表来distinct

explain select distinct name from actor;

image.png

film表的name有索引，因此直接用索引查找，并未使用到临时表

explain select distinct name from film

image.png

using filesort

using filesort：在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息，这种情况需要索引优化

因为索引已经是排好序的，无论是主键索引还是辅助索引还是联合索引，都是从索引数据结果B+树上看都是从左往右递增的，因此使用了索引就不会出现using filesort。

actor的name字段没有索引，会浏览actor整个表，保存排序关键字name和对应的id对应关系，然后排序name并检索行记录

image.png

film的name字段有索引，索引本来就是有顺序的，直接按照索引顺序拿值即可

image.png