Explain 用法

explain模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

语法：Explain + SQL 语句;

如：Explain select * from user; 会生成如下 SQL 分析结果，

 

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

下面详细对每个字段进行详解

id

id是一组数字，代表多个表之间的查询顺序，或者包含子句查询语句中的顺序，id 总共分为三种情况，依次详解

1.id 相同，执行顺序由上至下

2.id 不同，如果是子查询，id 号会递增，id 值越大优先级越高，越先被执行

3.id 相同和不同的情况同时存在

 id相同的为一组,组内从上到到下执行,在所有组中,id越大,越优先执行

select_type

select_type 包含以下几种值

simple
primary
subquery
derived
union
union result

simple

简单的 select 查询，查询中不包含子查询或者 union 查询

primary

如果 SQL 语句中包含任何子查询，那么子查询的最外层会被标记为 primary

 

subquery

在 select 或者 where 里包含了子查询，那么子查询就会被标记为 subQquery，见上例

derived

在 from 中包含的子查询，会被标记为衍生查询，会把查询结果放到一个临时表中

union / union result

如果有两个 select 查询语句，他们之间用 union 连起来查询，那么第二个 select 会被标记为 union，union 的结果被标记为 union result。它的 id 是为 null 的

 

table

表示这一行的数据是哪张表的数据

type

type 是代表 MySQL 使用了哪种索引类型，不同的索引类型的查询效率也是不一样的，type 大致有以下种类

system
const
eq_ref
ref
range
index
all

性能按type排序

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system

表中只有一行记录，system 是 const 的特例，几乎不会出现这种情况，可以忽略不计

const

将主键索引或者唯一索引放到 where 条件中查询，MySQL 可以将查询条件转变成一个常量，只匹配一行数据，索引一次就找到数据了

eq_ref

在多表查询中，如 T1 和 T2，T1 中的一行记录，在 T2 中也只能找到唯一的一行，说白了就是 T1 和 T2 关联查询的条件都是主键索引或者唯一索引，这样才能保证 T1 每一行记录只对应 T2 的一行记录

举个不太恰当的例子，EXPLAIN SELECT * from t1 , t2 where t1.id = t2.id

 

ref

不是主键索引，也不是唯一索引，就是普通的索引，可能会返回多个符合条件的行。

range

体现在对某个索引进行区间范围检索，一般出现在 where 条件中的 between、and、<、>、in 等范围查找中。

index

将所有的索引树都遍历一遍，查找到符合条件的行。索引文件比数据文件还是要小很多，所以比不用索引全表扫描还是要快很多。

all

没用到索引，单纯的将表数据全部都遍历一遍，查找到符合条件的数据

possible_keys

此次查询中涉及字段上若存在索引，则会被列出来，表示可能会用到的索引，但并不是实际上一定会用到的索引

key

此次查询中实际上用到的索引

key_len

表示索引中使用的字节数，通过该属性可以知道在查询中使用的索引长度，注意：这个长度是最大可能长度，并非实际使用长度，在不损失精确性的情况下，长度越短查询效率越高

ref

显示关联的字段。如果使用常数等值查询，则显示 const，如果是连接查询，则会显示关联的字段。

tb_emp 表为非唯一性索引扫描，实际使用的索引列为 idx_name，由于 tb_emp.name=’rose’为一个常量，所以 ref=const。
tb_dept 为唯一索引扫描，从 sql 语句可以看出，实际使用了 PRIMARY 主键索引，ref=db01.tb_emp.deptid 表示关联了 db01 数据库中 tb_emp 表的 deptid 字段。

rows

根据表信息统计以及索引的使用情况，大致估算说要找到所需记录需要读取的行数，rows 越小越好

extra

不适合在其他列显示出来，但在优化时十分重要的信息

using  fileSort（重点优化）

俗称 ” 文件排序 ” ，在数据量大的时候几乎是“九死一生”，在 order by 或者在 group by 排序的过程中，order by 的字段不是索引字段，

或者 select 查询字段存在不是索引字段，或者 select 查询字段都是索引字段，但是 order by 字段和 select 索引字段的顺序不一致，都会导致 fileSort

using temporary（重点优化）

使用了临时表保存中间结果，常见于 order by 和 group by 中。

USING index（重点）

表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！

如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表面索引用来读取数据而非执行查找动作。

Using wher

表明使用了 where 过滤

using join buffer

使用了连接缓存

impossible where

where 子句的值总是 false，不能用来获取任何元组

select tables optimized away

在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者 对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算， 查询执行计划生成的阶段即完成优化。

distinct

优化 distinct，在找到第一匹配的元组后即停止找同样值的工作

 

 

总结

性能按type排序

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

性能按Extra排序

Using index：用了覆盖索引
Using index condition：用了条件索引（索引下推）
Using where：从索引查出来数据后继续用where条件过滤
Using join buffer (Block Nested Loop)：join的时候利用了join buffer（优化策略：去除外连接、增大join buffer大小）
Using filesort：用了文件排序，排序的时候没有用到索引
Using temporary：用了临时表（优化策略：增加条件以减少结果集、增加索引，思路就是要么减少待排序的数量，要么就提前排好序）
Start temporary, End temporary：子查询的时候，可以优化成半连接，但是使用的是通过临时表来去重
FirstMatch(tbl_name)：子查询的时候，可以优化成半连接，但是使用的是直接进行数据比较来去重

常见的优化手段

SQL语句中IN包含的值不应过多，不能超过200个，200个以内查询优化器计算成本时比较精准，超过200个是估算的成本，另外建议能用between就不要用in，这样就可以使用range索引了。
SELECT语句务必指明字段名称：SELECT * 增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。
当只需要一条数据的时候，使用limit 1
排序时注意是否能用到索引
使用or时如果没有用到索引，可以改为union all 或者union
如果in不能用到索引，可以改成exists看是否能用到索引
使用合理的分页方式以提高分页的效率
不建议使用%前缀模糊查询
避免在where子句中对字段进行表达式操作
避免隐式类型转换
对于联合索引来说，要遵守最左前缀法则
必要时可以使用force index来强制查询走某个索引
对于联合索引来说，如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效。
尽量使用inner join，避免left join，让查询优化器来自动选择小表作为驱动表
必要时刻可以使用straight_join来指定驱动表，前提条件是本身是inner join