explain的常用字段解析
Type
null > system > const(主键索引扫描) > eq_ref(表中只有一条记录与之匹配,常见于主键索引和唯一索引) > ref(非唯一索引扫描) > ref_or_null > index > all
const 通过索引一次就找到了,const用于primary key或unique索引的比较,因为只匹配一行数据,所以很快,如主键至于where中,mysql会将查询置换成一个常量
eq_ref 常见于主键索引和唯一索引 对于每个索引键,表中只有一条记录与之匹配 应该多在链表的时候出现
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;
ref 非唯一索引扫描 返回匹配某个单独值的所有行
ref_or_null 类似于ref,一般存在于非唯一索引的or查询,如select * from invest where num=2 or num is null;
index_merge 索引合并时会出现 useing intersect using sort_uion , 索引合并之后还需要进行一些计算工作生成最后的结果,例如下面这条sql,key1、key2、key3有创建独立的索引,mysql会单独扫描每个索引,共扫描三次,最后把三次的结果合并,但这个是可以通过组合索引来优化的,即创建(key1,key2,key3)组合索引,这样mysql就会优先使用组合索引,因此只要扫描一次索引就可以了
SELECT * FROM t1 WHERE key1=1 AND key2=2 AND key3=3;
range 出现了范围查找
index full index scan index和All的区别,index虽然也是全表扫,但它扫的是索引树,而All扫的是硬盘,总的来说索引文件的大小要比数据文件小
All full table scan 全表扫描
Extra
using where
使用单列索引后再对查找到的数据进行剩下的where的条件的过滤
高性能MYSQL第三版对它的解释:
using where 代表MYSQL服务器层将在存储引擎层返回行以后再应用WHERE过滤条件;
Using filesort
当不能使用索引树排序的时候,mysql会通过相应的算法将取得的数据在内存中进行排序
解决办法有二:一、获取数据后在程序中进行排序 二、在where条件列和order by列的字段中建立联合索引
Using temporary
以下这几种情况会产生内存临时表
使用了临时表保存中间结果 , mysql对结果排序时使用了临时表,常见于order by排序和分组查询group by
1、当连表时,如left join时,order by后面是第二张表的字段就会出现using temporary。例
explain select * from film left join film_actor p on film.film_id=p.film_id order by p.actor_id;
2、如果语句中出现了distinct关键字也会(distinct非常耗时)。distinct:一旦mysql找到了与行相联合匹配的行,就不再搜索了
explain select distinct * from film left join film_actor p on film.film_id=p.film_id order by film.title;
3、UNION查询;
4、用到TEMPTABLE算法或者是UNION查询中的视图;
5、ORDER BY和GROUP BY的子句不一样时;
6、表连接中,ORDER BY的列不是驱动表中的;
7、DISTINCT查询并且加上ORDER BY时;
8、SQL中用到SQL_SMALL_RESULT选项时;
9、FROM中的子查询;
10、子查询或者semi-join时创建的表;
在以下几种情况下,会创建磁盘临时表:
1、数据表中包含BLOB/TEXT列;
2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列(或者超过 512字节的 二进制类型列,在5.6.15之前只管是否超过512字节);
3、在SELECT、UNION、UNION ALL查询中,存在最大长度超过512的列(对于字符串类型是512个字符,对于二进制类型则是512字节);