mysql -- 优化之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 字段中。