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 最小的值，因为聚集索引是排好序的，只需要拿到第一个数据即可直接返回，根本不需要再查询表使用任何索引。所以数据库对语句进行了优化。

学习日记，结束。