MySQL的explain工具介绍

介绍

使用 explain 可以模拟优化器执行 SQL 语句，会返回 SQL 语句的执行计划信息，但是不会真正去执行这条 SQL 语句。

注：如果 from 中包含子查询，则会真正执行子查询，将结果放入临时表中（衍生表）。

explain extended

会在 explain 的基础上额外返回 filtered，根据 rows*(filtered/100) 可以估算出将要和前一个表进行连接的行数。前一个表是指相对于当前表靠后执行的表，即 id 值更小的表

5.7 版本后不需要加 extended。

show warings

在 explain 语句后面紧跟着 show warings 语句，可以得到优化后的查询语句，从而看出优化器优化了什么。

mysql> EXPLAIN EXTENDED SELECT * from `user` where `NAME` LIKE 'zhangsan' \G 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS \G
*************************** 1. row ***************************
  Level: Warning
   Code: 1681
Message: 'EXTENDED' is deprecated and will be removed in a future release.
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`user`.`id` AS `id`,`test`.`user`.`NAME` AS `NAME` from `test`.`user` where (`test`.`user`.`NAME` like 'zhangsan')
2 rows in set (0.00 sec)

explain partitions

相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

5.7 版本后不需要加 partitions。

id 字段

mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

执行计划信息如下：

id 值越大，表示越优先执行。上图中可以看到 from 子句中的衍生表查询的优先级最高，所以最先执行；接着执行 select 子句中的子查询；最后执行最外面的主查询。

select_type

simple：简单查询 primary：复杂查询中的最外层的查询 subquery：包含在 select 子句中的子查询 derived：包含在 from 子句中的子查询，derived 表示衍生的，因为 from 子句中的子查询结果会放入临时表中，其实就是一个衍生表

type

system：表示查询的表只有一条记录，所以查询效率很高。这种类型不需要优化

const：表示查询条件是某个具体的索引值，例如主键索引或者唯一索引，所以表最多有一条匹配的数据记录，只要读取1次，效率很高。这种类型不需要优化。

range：表示查询条件是某个区间，最好要优化到这个类型

eq_ref：在多表关联查询时，主键所在表的查询类型就是 eq_ref，因为主键是唯一的；当然如果使用唯一键关联查询也属于这种类型。这种类型的查询基本不要优化了

ref：表示查询条件使用普通索引，而不是唯一索引，结果可能是多条记录

index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速査找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种査询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比 all 快一些。但是也要优化

all：表示全表扫描，效率很低，需要优化

key

查询语句用到的索引名称

possible_keys

执行 SQL 语句可能会用到的索引，但是最后执行时不一定用到

key_len

这一列显示了mysq 在索引里使用的字节数，通过这个值可以算出具体使用了复合索引中的哪些列。举例来说，film_actor 表的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是 4 字节。通过结果中的 key_len=4 可推断出查询使用了第一个列：film_id 列来执行索引查找。

key_len 计算规则

1.字符串 char(n)：n字节长度 varchar((n)：如果是u-8，则长度 3n+2 字节，加的2字节用来存储字符串长度数值

2.数值类型 tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节

3.时间类型 date：3字节 timestamp：4字节 datetime：8字节

注意： 1.如果字段允许为 NULL，需要 1 字节记录是否为 NULL 2.索引最大长度是 768 字节，当字符串过长时，MySQL 会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

ref

查询语句中，如果查询条件使用了常量，则会显示 const，如下：

mysql> select * from film_actor where film_id = 2 and actor_id = 5;

执行计划信息： 注：如果多表关联查询，这个 ref 会显示关联中的唯一索引字段。

rows

查询语句可能扫描的行数

extra

using index：表示使用覆盖索引 using where：表示使用了 where 子句，且查询的字段没有被二级索引全覆盖，通常查询语句没有用到任何索引 using index condition：通常查询条件使用到了联合索引的前面几个字段，而且查询的字段没有完全被二级索引覆盖 using temporary：表示使用了临时表处理查询 using filesort：表示使用索引字段以外的方式进行排序，效率较低 select tables optimized away：使用聚合函数访问索引字段