优化之EXPAIN执行计划

准备测试数据
CREATE TABLE actor (
id int(22) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
update_time datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert into actor(id,name,update_time) values (1,‘a’,‘2019-10-21 14:03:45’),(2,‘b’,‘2019-10-22 14:03:49’),(3,‘c’,‘2019-10-23 14:03:52’);

CREATE TABLE film (
id int(22) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert into film(id,name) values (1,‘film1’),(2,‘film2’),(3,‘film0’);

CREATE TABLE film_actor (
id int(22) NOT NULL AUTO_INCREMENT,
film_id int(22) DEFAULT NULL,
actor_id int(22) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert into film_actor(id,film_id,actor_id) values (1,1,1),(2,1,2),(3,2,1);

一. explain 之 id
EXPLAIN SELECT (SELECT 1 FROM actor WHERE id = 1)
FROM (SELECT * FROM film WHERE id = 1) der;

三种情况
1.id相同，表示加载表的顺序是从上到下
2.id不同，值越大优先级越高，越先执行
3.id有相同也有不同，id相同的可以认为是一组，从上往下执行，在所有组中，id值越大，优先级越高，越先执行。

二. explain 之 select type

EXPLAIN SELECT * FROM film;

EXPLAIN
SELECT (SELECT 1 FROM actor WHERE id = 1)
FROM (SELECT * FROM film WHERE id = 1 UNION SELECT * FROM film WHERE id = 1) der;

simple ：简单select查询，查询中不包含子查询或union
primary ：复杂查询中最外层的select
derived : 包含在from子句中的子查询。派生出一张表临时存放子查询的结果集
union : 在union中的第二个随后的select并集去重查询
union result ：从union临时表检索结果的select，并集去重查询后派生出一张临时表所存放的结果
subquery: ：包含在select中的查询，子查询

三. explain 之 table
从哪个表中读取数据

四. explain 之 partitions
分区表
如果没有设置分区的话，则为null

五. explain 之 type

EXPLAIN SELECT * FROM (SELECT * FROM film WHERE id = 1) tmp;

EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id = film.id;

EXPLAIN SELECT * FROM actor WHERE id > 1;

EXPLAIN SELECT COUNT(0) FROM film;

EXPLAIN SELECT * FROM actor;

type 含义：关联类型或访问类型，MYSQL决定如何查找表中的行
NULL MySQL不访问任何表，索引，直接返回结果

system 表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现

const 表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。
如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const于将"主键" 或 “唯一” 索引的所有部分与常量值进行比较

eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描

ref 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）

range 只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。

index index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。

all 将遍历全表以找到匹配的行

结果值从最好到最坏依次是：

最好 system > const > eq_ref > ref > range > index > ALL 最坏

六. explain 之 possible_keys（可能）
显示查询可能使用哪些索引来查找

七. explain 之 key（实际）
显示mysql实际采用哪个索引来优化对该表的访问

八. explain 之 key_len
显示mysql在索引李使用的字节数，通过这个值可以算出具体使用了索引中的那个列

九. explain 之 ref
这一列显示了在key列记录的索引中，表查找值所用到的列或常量

十. explain 之 rows
mysql估计要读取并检测的行数，这个不是结果集的行数

十一. explain 之 filtered
是一个百分比的值， rows * filtered / 100 这个结果将与前表产生交互

十二. explain 之 extra

EXPLAIN SELECT DISTINCT NAME FROM film LEFT JOIN film_actor ON film.id = film_actor.film_id;

EXPLAIN SELECT id FROM film ORDER BY id;

EXPLAIN SELECT * FROM film WHERE id > 1;

EXPLAIN SELECT * FROM actor ORDER BY NAME;

展示的额外信息
distinct 一旦mysql找到了与行相关联匹配的行，就不再搜索了
using index 发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的杭机路， 是性能高的表现。
using where mysql服务器将在存储引擎检索行后再进行过滤，就是先读取整行数据，再按照where条件进行检查，符合就留下，不符合就丢弃。
using temporary 使用临时表来中间操作。效率低下，解决： 为查询的列创建索引
using filesort 采用文件扫描对结果进行计算排序，效率低下 解决： order by 的列要出现在select字段中。