如何查看MySQL执行计划（Explain）

我们经常会使用Explain去查看执行计划，这个众所周知。但我在面试时问面试者，你用Explain主要是看什么？对方的回答大多是“查看是否有使用到索引”，很显然我对这个回答不太满意。
今天我们就来说一说Explain的详细用法。

1 查看执行计划命令

explain + SQL

example：

explain  SELECT * FROM billing_item_dis WHERE item_name='粪便常规'

2 执行计划中各个字段的含义

2.1 id

表示执行顺序，id的数字越大越先执行，如果数字一样，那么从上往下依次执行，如果为null表示这是一个结果集，不需要用它来进行查询。

2.2 select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询

取值	含义
simple	简单的select查询，查询中不包含子查询或者union
primary	查询中包含任何复杂的子部分，最外层查询则被标记为primary
union	若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
dependent union	与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
union result	包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
subquery	在select 或 where列表中包含了子查询
dependent subquery	与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
derived	在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在临时表里

2.3 table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

2.4 type

MySQL的官网解释为：连接类型(the join type)。它描述了找到所需数据使用的扫描方式。
访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

2.4.1 扫描方式汇总

取值	含义
system	系统表，少量数据，往往不需要进行磁盘IO
const	常量连接
eq_ref	主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
ref	非主键非唯一索引等值扫描
ref_or_null	与ref方法类似，只是增加了null值的比较。实际用的不多
range	范围扫描
index	索引树扫描
index_merge	表示查询使用了两个以上的索引，最后取交集或者并集
fulltext	全文索引检索
unique_subquery	用于where中的in形式子查询，子查询返回不重复值唯一值
index_subquery	用于in形式子查询,子查询可能返回重复值，可以使用索引将子查询去重
ALL	全表扫描(full table scan)

2.4.2 扫描方式详解

2.4.2.1 system

扫码类型为system，说明数据已经加载到内存里，不需要进行磁盘IO。
这类扫描是速度最快的。但是我没有遇到过，遇到了我再来补充！

2.4.2.2 const

explain select id from billing_item_dis where id =1;

const扫描的条件为：

1. 命中主键(primary key)或者唯一(unique)索引；
2. 被连接的部分是一个常量(const)值；

2.4.2.3 eq_ref

eq_ref扫描的条件为：对于前表的每一行(row)，后表只有一行被扫描。
出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref

2.4.2.4 ref

explain select * from billing_item_dis t1,billing_item_reslut t2 where t1.id = t2.binli_id;

对于前表的每一行(row)，后表可能有多于一行的数据被扫描。
不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现

2.4.2.5 range

explain select * from billing_item_dis where id > 4;

索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

2.4.2.6 index

explain select id from billing_item_dis;

索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

2.4.2.7 all

explain select * from billing_item_dis;

这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

2.5 possible_keys

查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

2.6 key

实际使用的索引，如果为NULL，则没有使用索引。
查询中如果使用了覆盖索引，则该索引仅出现在key列表中

2.7 key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

2.8 ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

2.9 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

2.10 Extra

取值	含义	举例
Using where	Extra为Using where说明， SQL使用了where条件过滤数据。	explain select * from billing_item_dis where id > 4;
Using index	Extra为Using index说明， SQL所需要返回的所有列数据均在一棵索引树上， 而无需访问实际的行记录。	explain select id from billing_item_dis;
Using index condition	Extra为Using index condition说明， 确实命中了索引，但不是所有的 列数据都在索引树上，还需要访问实际的行记录。	explain select * from billing_item_dis t1, billing_item_result t2 where t1.user_id = t2.id;
Using filesort	Extra为Using filesort说明，得到所需结果集， 需要对所有记录进行文件排序。典型的，在一个没有建立索引的列上进行了order by，就会触发，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。	explain select id from billing_item_dis order by item_name;
Using temporary	Extra为Using temporary说明， 需要建立临时表(temporary table)来暂存中间结果。 这类SQL语句性能较低，往往也需要进行优化。 典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。	explain select item_name, COUNT(*) from billing_item_dis GROUP BY item_name order by item_name;