您现在的位置是:首页 >其他 >学习之-Mysql Sql 优化之 Explain网站首页其他

学习之-Mysql Sql 优化之 Explain

飞行的鞋子 2023-07-02 00:00:03
简介学习之-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 最小的值,因为聚集索引是排好序的,只需要拿到第一个数据即可直接返回,根本不需要再查询表使用任何索引。所以数据库对语句进行了优化。

学习日记,结束。 

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。