Mysql 中 Explain 命令详解

explain 的作用

1. 描述 MySQL 如何执行查询操作、执行顺序，使用到的索引，以及 MySQL 成功返回结果集需要执行的行数。
2. 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们的查询，让查询优化器能够更好的工作

查询优化器的作用：

1. 优化 select 语句，分析哪些是常量表达式（例如 id=1），以及分析哪些表达式可以直接转换成常量的
2. 对 where 条件进行简化和转换，如去掉无用条件，调整条件结构等
3. 读取涉及的表的统计信息，并计算分析（例如返回的行数，索引信息等），最终得出执行计划

执行计划（QEP）包含的信息

explain select * from employees where emp_no in(10001, 10008) \G;
*************************** 1. row ***************************
                  id:  1
   select_type:   SIMPLE
             table:  employees
      partitions:  NULL
             type:   range
possible_keys:  PRIMARY
               key:  PRIMARY
         key_len:  4
                ref:  NULL
             rows:   2
         filtered:  100.00
            Extra:  Using where

id：标识符，表示执行顺序
select_type：查询类型
table：输出行所引用的表
partitions：使用的哪个分区，需要结合表分区才可以看到
type：表示按某种类型来查询，例如按照索引类型查找，按照范围查找。从最好到最差的连接类型为const、eq_reg、ref、range、index和all
possible_keys：可能用到的索引，保存的是索引名称，如果是多个索引的话，用逗号隔开
key：实际用到的索引，保存的是索引名称，如果是多个索引的话，用逗号隔开
key_len：表示本次查询中，所选择的索引长度有多少字节
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：显示 mysql 认为执行查询时必须要返回的行数
filtered：通过过滤条件之后对比总数的百分比
extra：额外的信息，例如：using file sort ，using where， using join buffer，using index 等

执行计划中各个参数的详解

1.id

表示 select 标识符，同时表明执行顺序，也就是说 id 是一个查询的序列号，查询序号即为 sql 语句执行的顺序。(一个 SQL 中可能是存在多个子 SQL)
（1）当 id 值相同时，按从上到下的顺序执行
（2）当 id 全部不同时，按 id 从大到小执行
（3）当 id 部分不同时，先执行 id 大的，id 相同的，按从上到下的顺序执行

2.select_type

（1）simple：表示简单的 select，没有 union 和子查询
（2）primary：最外面的查询 或者 主查询，在有子查询的语句中，最外面的 select 查询就是 primary
（3）subquery：子查询
（4）union：union 语句的第二个或者说是后面那一个 select
（5）union result：union 之后的结果
（6）dependent unoin：unoin 中的第二个或随后的 select 查询，依赖于外部查询的结果集
（7）dependent subquery：子查询中的第一个 select 查询，依赖于外部 查询的结果集
（8）derived：衍生表（5.7 版本中不存在这一个）

3.table

通常是表名，或者表的别名，或者一个为查询产生临时表的标示符（如派生表、子查询、集合）

4.partitions

使用的哪些分区（对于非分区表值为 null），在 5.5 版本中需要加上 explain partitions select .....

5.type

1. const：表中最多有一个匹配行，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快.

2. eq_ref：唯一性索引扫描，对于每个来自于前面的表的记录，从该表中读取唯一一行

3. ref：非唯一性索引扫描，对于每个来自于前面的表的记录，所有匹配的行从这张表取出

4. ref_or_null：类似于 ref，但是可以搜索包含 null 值的行，例如：select * from student where address='xxx' or address is null，需要在 address 建立索引。

5. index_merge：查询语句用到了一张表的多个索引时，mysql 会将多个索引合并到一起。

6. range：按指定范围（如 in、<、>、between and 等，但是前提是此字段要建立索引）来检索，很常见。如：select * from student where id < 5，id 上要有索引。

7. index：全”表“扫描，但是是在索引树中扫描，通常比 ALL 快，因为索引文件通常比数据文件小，index 扫描是通过二叉树的方式扫描，而 all 是扫描物理表。（也就是说虽然 all 和 index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）。例如：select name from student，但 name 字段上需要建立索引，也就是查询的字段属于索引中的字段。

8. all：全表扫描，扫描完整的物理表，此时就需要优化了。

从上到下的效率逐次的越来越低

6.possible_keys

指出 MySQL 能在该表中可能使用的索引，显示的是索引的名称，多个索引用逗号隔开，如果没有，则为 null。

7.key

MySQL 决定实际用到的索引，显示的是索引的名称，多个索引用逗号隔开，如果没有，则为 null

8.key_len

当用到组合索引的时候判断索引是否完全用上。
key_len 只计算 where 条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到 key_len 中。

计算 key_len 的公式：
varchr(10)变长字段且允许 NULL = 10* ( character set：utf8=3,gbk=2,latin1=1) + 1(NULL) + 2(变长字段)
varchr(10)变长字段且不允许 NULL = 10*( character set：utf8=3,gbk=2,latin1=1) + 2(变长字段)
char(10)固定字段且允许 NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许 NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

9.ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

10.rows

显示 mysql 认为执行查询时必须要返回的行数，可结合 type 和 key 分析，没有用上索引的情况下，会全表扫描。rows 的值越小越好，说明检索的数据少

11.filtered

给出了一个百分比的值，这个百分比值和 rows 列的值一起使用，可以估计出那些将要和执行计划中的前一个表（前一个表就是指 id 列的值比当前表的 id 小的表）进行连接的行的数目。
这一列在 5.5 版本中，需要加上 explain extended select ....。

12.extra

此字段显示一些额外的信息，但是此字段的部分值具有优化的参考意义。

1. using where：表示查询使用了 where 语句来处理结果
2. using index：表示使用了覆盖索引。这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要直接访问表数据。
3. using join buffer：这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能
4. using filesort：这是 order by 语句的结果。这可能是一个 CPU 密集型的过程。using filesort 表示出现了文件内排序，表示很不好的现象，必须要优化，特别是大表，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。_
5. using temporary：mysql 需要创建一张临时表来保存中间结果。 也就是说，需要先把数据放到临时表中，然后从临时表中获取需要的数据。出现这种临时表，也是必须需要优化的地方，特别是数据量大的情况。两个常见的原因是在来自不同表的列上使用了 distinct，或者使用了不同的 order by 和 group by 列