Explain详解与索引最佳实战

Explain用途

分析mysql中sql语句的执行计划

Explain变种

5.7之前用explain加上extended在分析结果中会多出一列filtered

5.7之前用explain加上partitions在分析的结果中会多出一列partitions

partitions：当前表有没有分区（一般互联网公司不使用分区来优化、一般采用分库分表）

filtered：(filtered/100)*rows可以估算出将要和explain中前一个表进行连接的行数

5.7版本以及后续版本直接用explain默认就会在分析结果中显示filtered和partitions

show warings

显示explain后面的sql在执行前被优化后的结果

 

Explain结果说明

• select_type

simple：简单查询、sql语句中不包含子查询以及union

primary：复杂查询中最外层的select

subquery：包含在select中的子查询（不在from子句中）

derived：包含在from子句中的子查询、mysql会将结果存放在一个临时表中、页成为派生表（derived的英文含义）

table

表示查询的是哪张表

id

值越大代表对应的查询越先执行、如果值相等则排在前面的先执行

type

system > const > eq_ref >ref >range>index>all

查询性能从左到右依次递减、一般来说、得保证查询达到range级别、最好达到ref

• NULL

  mysql能够在优化阶段分析查询语句、在执行阶段用不着再访问表和索引。例如再索引列中选取最小值、可以单独查找索引来完成、不用再执行时访问表

  explain select min(id) from user;

  type为null时查询性能很高、但实际开发中很少用到这种查询。

• const

  一般用主键或唯一索引来查询

   select * from user where id = 1;

• system

  该种类型时const的一种特例、从结果集为1的临时表中进行查询、也可以理解成整张表中只有一条数据

  select * from (select * from user where id = 1) tmp;

• eq_ref

  多表查询的时候用primary key或者unique key进行关联

  select * from user  left join user_partition_tbl on user.id = user_partition_tbl.user_id;

   

• ref

  不使用唯一索引、而是使用普通索引或者唯一索引的部分前缀从而可能查询到多条结果集

  #已经为name创建了普通索引
  select * from user where name = 'scot'

  select film_id from film left join film_actor on film.id = film_actor.film_id;
  #idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

   

• range

  范围扫描、通常出现再in()、between，>,<,>=,<=等操作中、使用一个索引来检索出给定范围的行

  select * from user where id > 1;

• index

  当查询的结果集中聚集索引和二级索引中都有的情况下mysql优先查询二级索引、因为聚簇索引中包含所有数据、查找时扫描范围较大、二级索引存放的是主键、所以会优先选择二级索引。

  该类型代表全索引扫描。

  select * from user;#从聚簇索引的第一个叶子节点往后扫描
  select * from user where id > 3; #因为有条件、则会从聚簇索引的根节点进行扫描

   

• all

  该类型代表全表扫描/全聚簇索引扫描（扫的是聚簇索引）

   

possible_key

explain分析sql执行过程中可能会用到的索引

key

sql执行过程中真正用到的索引

key_len

如果使用的是联合索引、则可以根据key_len的值来判断当前sql使用的是联合索引中的那个字段索引

select * from role_permission where user_id = 2

key_len计算规则

字符串：

char(n)：n字节长度

varchar(n)：如果是utf-8、则长度3n+2字节、加的2字节用来存储字符串的长度

数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节

时间类型

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为null、需要1字节记录是否为null

索引的最大长度是768字节、当字符串过长时、mysql会做一个类似左前缀索引的处理、将前半部分的字符串提取出来做索引

ref列

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

rows

当前sql执行过程中预估扫描的行数

*Extra

• using index：覆盖索引、简单来说覆盖索引并不能算是一种索引而是一种查询方式、查询的字段在索引树中都包含、不用再次回表。

select film_id from film_actor where film_id = 1;

• using where：使用where语句来处理结果、并且查询的列未被索引覆盖（没有对name创建索引）。

select * from actor where name = 'a'

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

select * from film_actor where film_id > 3;

• using temporary：使用临时表

#在actor表中name没有用到覆盖索引、所以去重时会在临时表中进行
select distinct name from actor;
#在film表中name用到了覆盖索引、效率要比第一种情况高
select distinct name from film;

• using filesort：文件排序、如果查询结果集很大的时候mysql会使用磁盘进行排序、结果集小一般使用内存排序

#在actor表中没有对name列创建二级索引、会使用文件排序
select * from actor order by name;
#在film表中对name列创建了二级索引、会使用覆盖索引
select * from film order by name;