Mysql Explain详解

@

目录

• Explain 常用列
  • id
  • select_type
  • table
  • type
  • extra

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈
本文基于Mysql5.7版本

Explain 常用列

id

select的序列号，有几个查询就有几个id，id越大优先级越高，id相同则从上往下执行，id为null最后执行

select_type

表示对应行是简单还是复杂查询，常见的值：
simple: 简单查询， 不包含子查询和union
primary : 复杂查询中的最外层select
subquery: 子查询（不在from中的）
derived: 在from中的子查询，mysql会将结果放到一个临时表中，也称派生表.
union: 在union中的第二个和随后的query

primary，subquery，derived区别:

set session optimizer_switch='derived_merge=off';
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) t

第一行的table为derived3的格式， 代表这个查询依赖id为3的查询。

union详解

explain select 1 union select id from actor

第三行的table为<union1,2>， 代表这个查询依赖id为1和3的查询。

table

表示这一行所引用的表

type

性能排序： system > const > eq_ref > ref > range > index > all
保证查询能到range， 最好ref

system ：system是const的特例，查的表里只有一条数据的情况
const：使用主键或者唯一索引查询

system和const

explain select * from (select * from film where id = 1) t

eq_ref：使用主键或者唯一索引进行关联的表

explain select * from film_actor a inner join film f on a.film_id = f.id

ref：使用的唯一索引的部分前缀或者普通索引或者普通索引的部分前缀

使用的普通索引的部分前缀直接查询

explain select * from film_actor where film_id  = 1

使用的普通索引的部分前缀关联

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

range：使用一个索引来进行范围查询，通常出现在in(), between, >, <等操作中
index：扫描二级索引，对其叶子结点遍历扫描，速度还是比较慢的；但通常二级索引比聚簇索引小，效率比all高

explain select * from film

all：全表扫描，扫描聚簇索引的所有叶子结点。效率低，需优化。

explain select * from actor 

extra

using index：使用到了覆盖索引（整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值）

// 从二级索引KEY `idx_film_actor_id` (`film_id`,`actor_id`) 中就能直接取到film_id 和 actor_id， 不用再去回表
explain select film_id, actor_id from film_actor where film_id = 1

using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

示例表

// An highlighted block
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;

-- film建表语句：
CREATE TABLE `film` (
  `id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 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;