MySQL学习之explain

 

from之后的查询得到的表叫做衍生表，是临时表数据，生成临时表之后的数据是无法使用索引的，如果数据量大查询效率就会比较低，这就是查询要尽量少使用子查询这些临时表。

 

 

explain详解

id：

表示查询序号，也可以表示优先级；当值都不一样的时候，值越大表示优先级越高，越先执行；当值都一样的时候，按照从上到下的顺序去执行。

 

select_type：

表示查询的类型，是简单类型还是复杂类型。

1. simple：简单查询。查询不包含子查询和union。
2. primary：复杂查询中最外层的 select。
3. subquery：包含在 select 中的子查询（不在 from 子句中）。
4. derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）。

 

table：

表示查询的表名，有些真实表有表名，临时表也会起一个表名，复杂查询可能没有表名。表示本行访问的表。当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

 

partitions：

表示分区信息，很少使用到。

 

type：

索引使用类型，很重要。表示查找范围。最常使用的是有system，const，eq_ref，ref，range，index，ALL。这些查询效率从左到右依次降低。一般来说我们要将查询优化到range级别，最好是到ref级别。

1. null表示查询优化阶段就可以确认要查询的数据，不用再去查表了。类似扫描个别索引就能获取到值。
2. system，是查询结果有且仅有一条数据，是const的特例。const是使用主键索引，或者唯一索引，查询结果最多只有一条数据，扫描次数很少，效率高。
3. eq_ref，是equals，也就是说如果有连表查询，被连的表的查询条件是主键或者唯一键，能确认被连表只能查询一条数据。简单查询不会出现这样的结果。
4. ref，表示没有使用唯一索引，或者使用了唯一索引的部分，查询到的数据不是唯一的，可能多个也可能一个。
5. range，明确的是范围查询，常在int，between和and，>，<，>=等，是使用到了索引的查询，查询范围数据，数据量较多。
6. index，表示扫描索引就能获取到全部数据，一般是查询二级索引，并且要查询的列都在二级索引上，可以避免回表操作。
7. all，表示全表扫描，不会再使用索引了，效率最低。

注意：查询语句优先查询二级索引，因为二级索引更小，存储的数据量更多，如果不满足，才会去主键索引上去查询。

 

possible_keys：

表示可能会用到的索引值，可能有多个，也可能为空。

 

key：

表示真正使用到的索引，该值有可能没有在possible_keys中显式，也可能在其中显式；总的来说两个没有必然联系，这个是要看MySQL的查询优化器的优化。

注意：这里的key是针对的where后的查询条件，不包括group by和order by之类的分组排序等，这个要明确。

 

key_len：

表示使用到的索引的长度值。通过这个值可以算出具体使用了索引中的哪些列。

key_len计算规则如下：

字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节

　　char(n)：如果存汉字长度就是 3n 字节

　　varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

数值类型

　　tinyint：1字节

　　smallint：2字节

　　int：4字节

　　bigint：8字节　　

时间类型　

　　date：3字节

　　timestamp：4字节

　　datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL。

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。所以在创建索引的时候要注意索引个数。

 

ref：

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

 

rows：

表示本次查询预估的条目数，越少越好。但是这个值并不准确。

 

filtered 列：

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指explain 中的id值比当前表id值小的表）

 

Extra列：

这一列展示的是额外信息，并不是很准确的，只能是一个参考。常见的重要值如下

Using index：使用覆盖索引。也就是说查询的列在二级索引上即可获取到，不用再回表操作。

Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖，一般来说要回表，效率降低了。

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。类似在查询条件上加索引，或者令查询条件使用到索引。例如针对一个没有加索引的列去重，就要先查询这一列的全部数据，然后放到临时表中，再去重，所以要进行优化。

 

注意：能够使用索引的前提条件是索引文件中有这些数据，才能使用到索引，否则是无法使用索引的。

 

索引最佳优化实践

使用等值查询，可以使用到索引；

联合索引，查询条件符合最左前缀原则，保证能命中索引；

不要对索引进行运算（类型转换，函数，数学计算），因为这些查询条件索引结构中没有，会退化成全表扫描；

如果联合索引中有范围查询，那么后面的索引就失效了；

尽量使用覆盖索引，尽量少使用select *，避免回表操作；

is null，is not null一般条件下无法使用索引；

使用不等于，not in，not exist会导致索引失效，大于小于不大于不小于这些会根据优化判断是否走索引。有时候判断查询结果过多或者过少都会不走索引，而是去全表扫描；

like模糊查询使用右侧模糊，左侧模糊索引无效；

避免查询条件进行类型转换，如果查询列是字符串，那么查询条件要加上引号；

少用in和or，当数据量大的情况下，可能不走索引，而去全表扫描；