MySQL Explain执行结果详解

目录

1 id

2 select_type

3 table

4 partitions

5 type

5.1 null级别查询

5.2 system&const级别查询

5.3 eq_ref级别查询

5.4 ref级别查询

5.5 range级别查询

5.5 index级别查询

5.6 all级别查询

6 possible_keys & key

7 key_len

8 ref

9 rows

10 filtered

11 Extra

---

参考文档

1 id

• select的序号列
• 通常有几个select就有几个id（除非产生衍生表合并优化）
• id的顺序是按select出现的顺序增长的
• id列数字越大执行优先级越高
• id相同则从上往下执行
• id为NULL最后执行

MySQL中的子查询

• select子查询
  • select与from之前的子查询语句
  • 在主from之后执行
• from子查询
  • from和where之间的子查询
  • 在where子查询之后执行
  • from子查询执行后通常会产生临时表，也称派生表
  • 当开启衍生表的合并优化时，MySQL会启动取消或者合并from子查询
  • MySQL7默认开启衍生表合并优化
  • 关闭衍生表合并优化的方式

set session optimizer_switch='derived_merge=off';

• where子查询
  • where之后的子查询
  • 优先执行where子查询

• 测试SQL

explain select 
	(select name  from actor where id= a.actor_id ) a_name,
	a.* 
from (select * from film_actor where id >= 1) a
where  a.film_id =   (select id  from film where id>=1 limit 1 )

• 执行结果为

由上观察可知，sql执行时的顺序：

where子查询>from子查询>主from查询>select子查询

2 select_type

• simple：简单查询，查询中不包含子查询和union

• primary：复杂查询中最外层的select
• subquery：select子查询或者where子查询
• dependent subquery：select子查询或者where子查询（与主form表进行关联查询的）
• derivde：from子查询产生的衍生表
• 测试SQL

explain select 
	(select name  from actor where id= a.actor_id ) a_name,
	a.* ,
	(select name  from film limit 1 ) f_name
from (select * from film_actor where id >= 1) a
where  a.film_id =   (select id  from film where id>=1 limit 1 ) 
       and a.actor_id =   (select id  from actor where id=a.actor_id )

• union：union后的查询
• union result：所有查询执行完后合并结果

3 table

• 表名将要访问的表名
• 当from中有子查询时，table列中存在<derivenN>，表示当前查询依赖id=N的查询，需要先执行id=N的查询

• 当使用union查询时，select_type为union result列的table值为<union1..N>,表示依赖id=1..N的查询，先执行这些查询

4 partitions

基于分区表查询时，partitions字段会显示查询将访问的分区

5 type

• 关联类型或者访问类型
• 决定着MySQL如何查找表中的行，查找数据行的大概范围
• 从最优到最差依次为
  • null > system > const > eq_ref > ref > range > index > ALL
• 一般来说，得保证查询达到range级别，只好达到ref
• 以下测试SQL语句达到的type级别不一定准确，因为MySQL执行时选择的type级别，与数据量有一定关系，通常情况下走辅助索引时因为数据量不多而走全表扫描

5.1 null级别查询

• 执行时不需要访问表
• 查询伪表时，不访问真实表，直接返回结果

• 不可能得条件，在执行优化阶段，发现不可能有结果机，直接返回null

• max，min聚合函数作用的字段存在索引时，直接从索引中取极值，不用再访问表

5.2 system&const级别查询

• 使用主键索引或者唯一索引常量等值查询
• 最多只有一行记录返回
• system级别是一种特殊const，极少出现

• 衍生表查询优化产生system级别查询

• 若子查询生成的派生表仅包含一行，外层查询访问访问该表时为system级别

• 主键常量等值查询产生const级别查询

• 唯一索引常量等值查询也是const级别查询

5.3 eq_ref级别查询

• 使用主键或者唯一索引关联表查询，并且主键或者唯一索引所在的表为被驱动表

5.4 ref级别查询

• 普通索引等值查询达到ref级别

• 联合索引使用到一部分索引时达到ref级别

注意此时select film_id改为select * 时，由于表数据量不多会走全表扫描

5.5 range级别查询

• in，between，>，<，>=，<=，like等范围查询
• 查询的字段需要存在索引
• 大于范围查询达到range查询

• 对于film表，只存在id，name字段，并且name存在索引时，mysql可能回选择name index索引

因为MySQL在选择索引时，如果辅助索引可以满足查询，并且不用回表时，优先采用辅助索引查询

5.5 index级别查询

• 走辅助索引查询即可拿到查询结果

5.6 all级别查询

• 需要走走主键索引才能拿到查询结果

6 possible_keys & key

• possible_key为可能使用哪些索引来查找
• key为选择使用的索引
• 当possible_key不为空时，key由于MySQL成本计算可能为空
• 可以使用force index强制使用哪个索引
• 可以使用ignore index忽略某个索引

7 key_len

• 当使用索引为联合索引时关注字段
• 显示MySQL在索引里面使用得字节数
• 根据该值可推断出联合索引中有几个字段生效

key_len计算规则

• 不同字段类型占用长度不一样

字段类别	字段类型	占用字节数
字符串	char(n)	3n
	varchar(n)	3n+2
数值类型	tinyint	1
	smallint	2
	int	4
	bigint	8
时间类型	date	3
	timestamp	4
	datetime	8

• 如果字段允许为null时，则需要额外1字节记录是否为null

8 ref

• 是key索引查询时，所用到的列或者常量

9 rows

• MySQL预估要读取的行数
• 并不是实际要读取的行数

10 filtered

• 查询条件过滤后的数据占总数据的百分比

11 Extra

展示额外信息，常见值如下：

• Using index
  • 使用辅助索引查询
  • 使用key表示的索引字段进行查询
  • 并且不需要进行回表查询，查询效率较快

• Using Where
  • 使用where过滤查询
  • 为使用索引字段

• Using Index Condition
  • 使用索引查询
  • 并且索引字段为范围查询
  • 如果使用索引为辅助索引时，先通过辅助索引锁定主键范围，然后回表查询主键索引

• Using temporary
  • 需要创建临时表来处理查询
  • 一般都是需要优化的

• Select tables optimized away
  • 在索引字段上使用max,min等极值聚合函数查询