explain工具分析

explain关键字 可以分析查询语句或结构的性能瓶颈

当我们想要查询一条sql的执行计划时 在该条sql语句前面加上explain关键字 即可

例如 表的结构如下：

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`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

使用select * from actor 是查询actor表中的信息

若加上explain的话 就会查询出这个sql的执行计划

使用explain查询的结果包括

1、id

id列的编号是select序列号 有几个select就有几个id  并且id的顺序是按select出现的顺序增长 

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

2、select_type

表示对应行时简单还是复杂查询

1）simple 简单查询 查询不包含子查询和union

        explain select * from film where id = 2;

2)  primary 复杂查询中最外层的select

3)  subquery  包含在select中的子查询

4)  derived 包含在from子句中的子查询 mysql会将结果放在一个临时表中 

        #关闭mysql5.7新特性对衍生表的合并优化

         set session optimizer_switch='derived_merge=off';   

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

        set session optimizer_switch='derived_merge=on'; #还原默认配置

5)  union 在union中的第二个和随后的select

        #无结果

        explain select 1 union all select 1;

3、table

表示explain正在访问哪个表

4、partitions

如果查询是基于分区表的话，partitions 字段会显示查询将访问的分区

5、type

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询达到range级别，最好达到ref

6、possible_keys

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

7、key

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

8、key_len

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

9、ref

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

10、rows

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

11、filtered

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）

12、Extra

这一列展示的是额外信息

              using index 使用覆盖索引

​              using where 使用where语句来处理结果 并且查询的列未被索引覆盖

​              using index condition 查询得列不完全被索引覆盖where条件中是一个前导列的范围

​              using temporary 需要创建一张临时表来处理查询

​              using filesort  将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在                                        磁盘完成排序

​              select tables optimized away 使用某些聚合函数来访问存在索引的某个字段