您现在的位置是:首页 >其他 >学习之-Mysql Sql 优化之 Explain网站首页其他
学习之-Mysql Sql 优化之 Explain
在开发中,往往遇到一些慢查询语句, 我们需要对慢查询进行优化。Explain工具就是用来分析某个慢查询执行情况的工具。通过在select 语句前加上explain 关键字,然后执行就会得到某个sql 执行计划信息,通过分析执行计划,我们就可以确定是否需要对其进行优化。
但是要想优化sql ,首先要能读懂执行计划,下面就explain执行计划相关字段针对个人理解进行解释:
表结构:三个表,actor 演员表,只有主键,没有其他索引;film 电影表,除主键外,有普通索引name;film_actor 两表的关联表,film_id,actor_id 形成联合索引。
Explain 中相关的列:
执行 explain select * from film where id = 1; 我们得到如下执行计划,计划中包含id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,extra.
id : 执行计划序号列,我觉得也可以看成执行计划执行顺序列,号越大,越先执行。id 若相同,从上至下执行,若为null ,则最后执行。
select_type:
主要值有SIMPLE,PRIMARY,SUBQUERY,DERIVED。
- SIMPLE:表示当前查询为简单查询,没有union,子查询等。
- PRIMARY:在复杂查询中,当前执行计划对应的查询会返回最终查询结果。也就是复杂查询中,最外层的select 。
- SUBQUERY:指在select 中的而不在from 中的查询。
- DERIVED:在from 子句中的子查询语句。该语句结果,数据库会使用临时表进行存储。
示例:explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
其中,select 1 from actor where id = 1 为subquery;select * from film where id = 1 为derived.
table:
执行计划中,对应sql 需要查询的表。
partitions:
表进行分区时,该字段才会有值,可以不用太关注。
type:
表示当前执行计划,查询类型,用以表示查询对应的数据范围。由最优到最差的值排列为: system > const > eq_ref > ref > range > index > ALL
其中system,const 表示,当前查询使用primary key , unique key 进行匹配查询时,最多只有一条数据返回,数据库可以转化为类似于查询常量的方式进行查询。system 是const 的一种特例,表里只有一条数据匹配时,为system。
示例:explain extended select * from (select * from film where id = 1) tmp;
select * from film where id = 1 使用主键,匹配常量1,获得唯一记录,存储于临时表中,该语句type 所以为const ,而临时表中,目前仅有一条数据可以匹配,则type 为system .
eq_ref , 则是当使用primary key 或者unique key 进行关联查询时,最多只会返回一条符合条件的记录,此时type 值则为eq_ref. 相较于const 效率稍差。
示例:explain select * from film_actor left join film on film_actor.film_id = film.id;
以上语句使用主键进行关联查询时,对film 来说 一个id 只会有一条数据。
ref:当使用普通索引或者唯一索引前缀,进行查询时。
示例:explain select * from film where name = 'film1';
以上为使用普通索引 name 进行查询
explain select film_id from film left join film_actor on film.id = film_actor.film_id;
以上查询,使用了film_actor的联合索引的前置字段film_id 进行了关联查询。
range: 范围扫描,通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
示例:explain select * from actor where id > 1;
index: 表示查询时,使用全索引扫瞄即可获得所有结果时的值。一般指二级索引,同时,此时select 中的字段,也全部包含在了索引文件中,索引这种情况下也叫使用了覆盖索引。
示例:explain select * from film;
比如,上面的语句,使用了name 普通索引,因为表film 中只有id 和name 两个字段,二级索引idx_name索引文件中刚好全部包含了所有列。这种情况下数据库可以不用回表查询,同时因为二级索引文件大小一般比聚集索引文件要大,所以数据库在执行上述sql 时会优化直接使用二级索引。
all:该值为效率最差的,会进行全表扫描。会从左到右扫描聚集索引的所有叶子结点数据。该值,表数据较大的话,则需要进行优化了。
示例:explain select * from actor;
比如演员表中,仅有聚集索引。所以上述查询无法优化至其他二级索引进行查询。
possible_keys:
表示执行计划对应的sql 可能使用的索引。若为null 则可能数据表中数据很少,数据库可能觉得不值当进行索引查询,所以选择直接进行全表扫描
key:
表示执行计划对应的sql 使用的索引。如果没有使用索引,则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index。
key_len:
表示执行sql时用到索引的长度。
示例:explain select * from film_actor where film_id = 2;
比如上述sql,使用了film_actor 中的联合索引,长度为4. 为什么是4呢,因为film_id为int 类型,数据库中长度为4,占4个字节。所以表示使用了联合索引的前置字段,且长度为4.
ref列:
表查找值所用到的列或常量。
rows列:
估计要读取并检测的行数,不是结果集里的行数。
Extra列:
展示的是额外信息,常用值:
Using index:使用覆盖索引
示例:explain select film_id from film_actor where film_id = 1;
Using where:使用 where 语句来处理结果,并且查询的列未被索引覆盖
Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围
示例:explain select * from film_actor where film_id > 1;
Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。
示例:explain select distinct name from actor;
Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
示例:explain select * from actor order by name;
Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段
示例:explain select min(id) from film;
上述语句,使用min 函数获取film 中id 最小的值,因为聚集索引是排好序的,只需要拿到第一个数据即可直接返回,根本不需要再查询表使用任何索引。所以数据库对语句进行了优化。
学习日记,结束。