mysql explain详解

本文转载自 https://www.cnblogs.com/LoveBB/p/15181075.html

 

Explain关键字是Mysql中sql优化的常用「关键字」，通常都会使用Explain来「查看sql的执行计划，而不用执行sql」，从而快速的找出sql的问题所在。

 

id字段

id表示执行select查询语句的序号，它是sql执行的顺序的标识，sql按照id从大到小执行，id相同的为一组，从上到下执行。

 

select_type字段

select_type表示查询的类型，也就是对应的是简单查询还是复杂查询，若是复杂查询又包含：「简单的子查询、from子句的子查询、union查询」。

simple

simple表示简单查询，不含有任何的复杂查询。

 

PRIMARY

复杂查询中「最外层的select语句的查询类型就是PRIMARY」。

  

SUBQUERY

在「select或者where中包含的子查询」会被表示为SUBQUERY类型

DERIVED

「DERIVED表示的是派生表或者衍生表的意思，在from包含的子查询中会被表示为DERIVED类型」，Mysql会递归执行这些子查询，并且把结果放在临时表中。

UNION

出现在「UNION查询语句中，第二个select的查询语句就会被表示为UNION」。

UNION RESULT

「UNION查询语句的结果被标记为UNION RESULT」。 

 

table字段

「table字段表示的是查询的是哪个表」，一个是已经存在的表，比如上面的user、role都是我们自己创建的表，也可以表示衍生表。比如：UNION RESULT的table字段表示为<union2,3>，也就是查询的是第二行和第三行的结果记录。

 

partitions

partitions表示所匹配的分区。

 

type字段

「type字段表示的sql关联的类型或者说是访问的类型」。从这个字段中我们可以确定这条sql查找数据库表的时候，查找记录的大概范围是怎么样的，直接就能体现sql的效率问题。

type字段的类型也是有比较多，主要常见掌握的有以下几个：system、const 、eq_ref 、ref 、range 、index 、ALL。它的性能体现是从高到低。system > const > eq_ref > ref > ref_or_null >index_merge > unique_subquery > index_subquery > range > index > ALL

system

system是const的特例，「表示表中只有一行记录」，这个几乎不会出现，也作为了解。

const

const表示通过索引一次就查找到了数据，一般const出现在「唯一索引或者主键索引中使用等值查询」，因为表中只有一条数据匹配，所以查找的速度很快。
如：explain select * from user where id =2;

  

eq_ref

「eq_ref表示使用唯一索引或者主键索引扫描作为表链接匹配条件，对于每一个索引键，表中只有一条记录与之匹配」。
例如：explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id;

  

ref

ref性能比eq_ref差，「也表示表的链接匹配条件，也就是使用哪些表字段作为查询索引列上的值」。
ref与eq_ref的区别就是：
eq_ref使用的是唯一索引或者主键索引。
ref扫描后的结果可能会找到多条符合条件的行数据，本质上是一种索引访问，返回匹配的行。
例如：explain select * from user where name = '张三';

  

ref_or_null

该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。

index_merge

该联接类型表示使用了索引合并优化方法。

unique_subquery

该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range

「range使用索引来检索给定范围的行数据，一般是在where后面使用between、<>、in等查询语句就会出现range」
例如：explain select * from user where id > 2;

  

index

「index表示会遍历索引树」，index会比ALL速度快一些，但是出现index说明需要检查自己的索引是否使用正确。
例如：explain select id from user;

  

ALL

「ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取」，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询
例如：explain select * from user ;

  

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

 

possible_keys字段

possible_keys表示这一列查询语句可能使用到的索引，仅仅只是可能，列出来的索引并不一定真正的使用到。

当没有使用索引为NULL时，说明需要增加索引来优化查询了，若是表的数据比较少的话，数据库觉得全表扫描更快，也可能为NULL。

 

key字段

key字段与possible_keys的区别就是，表示的真正使用到的索引，即possible_keys中包含key的值。

若是想Mysql使用或者忽视possible_keys中的索引，可以使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

 

 key_len字段

表示sql查询语句中索引使用到的字节数，这个字节数并不是实际的长度，而是通过计算查询中使用到的索引中的长度得出来的，显示的是索引字段最大的可能长度。

一般来说在不损失精度的前提下，key_len是越小越好，比如上面的测试表的id为int类型，int类型由4个字节组成
例如：explain select * from user where id =2 ;

 

 key_len对于不同的类型有自己的计算规则，具体的计算规则如下所示：

数据类型	所占字节数
字符串	char(n)：n字节长度 varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型	tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节
时间类型	date：3字节 timestamp：4字节 datetime：8字节

若是索引为字符串类型的时候，实际存储的字符串非常长，已经超出了字符串类型的存储最大长度（768字节），mysql，就会使用类似左前缀索引来处理。

 

ref字段

ref表示列与索引的比较，表连接的匹配条件，表示哪些列或者常量被用于查询索引列上的值。

 

rows字段

rows表示估算的要扫描的行数，一般Mysql会根据统计表信息和索引的选用情况，估算出 查找记录所要扫描的行数，注意这个并不是实际结果集的行数。

 

filtered字段

filtered表示的是查询表行所占表的百分比。

 

Extra字段

该字段显示的是sql查询的额外信息，主要有以下几种情况：
Using index、Using where、Using temporary、Using temporary、Using join buffer、Impossible where、Select tables optimized away

Using index

表示查询的列被索引覆盖，这个是查询性能比较高的体现，即所要查询的信息搜在索引里面可以得到，不用回表，索引被正确的使用
例如：explain select id from user where id =2;

  

假如同时出现了using where，表示索引用于执行索引键值的查找；若是没有出现using where，则表示索引用于读取数据，而非执行查询的动作。

Using where

该属性与Using index相反，查询的列并没有被索引覆盖，where条件后面使用的是非索引的前导列，它仅仅是使用了where条件而已。
例如：explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id; 

 Using temporary

「Using temporary表示使用了临时表存储中间的结果，一般在对结果排序的时候会使用临时表」。
例如：排序order by 和分组查询group by。
例：explain select * from (select name from user union select name from role) a where a.name = '张三' ;  

Using filesort

Using filesort表示文件排序，说明Mysql对数据使用了外部的索引进行排序，并没有使用表中的索引进行排序。
例如：explain select * from user order by name;

  

Using join buffer

Using join buffer表示使用连接缓存。
例如：explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id ; 

 它强调在获取连接条件时，并没有使用索引，而是使用连接缓冲区来存储中间结果，若是出现该值，一般说明需要添加索引来进行优化了。

Impossible where

Impossible where会出现在where后的条件一直为false的情况下，这种可以忽视，比较少出现
例如：explain select * from user where name = 'hah' and name = 'sfsd' ;  

Select tables optimized away

表示select语句没有遍历表或者索引就返回数据了。
例如：explain select min(id) from user ;