MYSQL性能调优Explain概述、详解id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra列

目录

①. Explain概述

②. 构建SQL坏境语句

③. id字段

④. select_type、table字段

⑤. type字段​编辑mysql> explain select min(id) from film

⑥. possible_keys、key列字段

⑦. key_len字段

⑧. ref、rows字段

⑨. Extra字段

①. Explain概述

• ①. 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理你的SQL语句。分析你的查询语句或是表结构的性能瓶颈

• ②. 能干什么？

• ③. 官方参考文档‘

• ④. 怎么玩？Expalin+SQL语句,执行计划包含的信息

• ⑤. 我们可以使用explain extended查询出filtered字段,使用explain partitions查询出partitions字段

  ②. 构建SQL坏境语句

• ①. 构建好SQL坏境语句,下面将对Explain中的各个字段进行详解

# 演员表的name是一个索引字段
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);
 

③. id字段

• ①. id列的编号是select的序列号,有几个select就有几个id,下面将分析三种情况

• ②. id相同,执行顺序由上到下
  ③. id不同,如果子查询,id的序号递增,id值越大优先级越高,越先被执行④. id相同又不同,注意:id为NULL最后执行
  

  ④. select_type、table字段

• ①. select_type:表示对应行是简单还是复杂的查询。

• ②. simple:简单查询。查询不包含子查询和union

mysql> explain select * from film where id = 2;

# 关闭mysql5.7新特性对衍生表的合并优化
mysql> set session optimizer_switch='derived_merge=off';
mysql> explain select (select 1 from actor where id = 1) from 
(select * from film where id = 1) der;
#还原默认配置
mysql> set session optimizer_switch='derived_merge=on';  

⑤. type字段mysql> explain select min(id) from film

mysql> explain extended select * from (select * from film where id = 1) tmp;
show warnings;
 

 # 简单select查询,name是普通索引(非唯一索引)
mysql> explain select * from film where name = 'film1';
# 关联表查询,idx_film_actor_id是film_id和actor_id的联合索引,这里使用到了film_actor的左边前缀film_id部分
mysql> explain select film_id from film left join film_actor on film.id = film_actor.fi lm_id;

mysql> explain select * from film;
 

⑥. possible_keys、key列字段

⑦. key_len字段

⑧. ref、rows字段

⑨. Extra字段