Explain 关键字

优质博文：IT-BLOG-CN

explain关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句：explain + SQL语句。表头信息如下：

一、ID 参数

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。三种情况：
【1】id 相同： 执行顺序由上而下；

explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';
1

【2】id 不同： 如果是子查询，id 序号会递增，id 越大优先级越高，越先被执行；

explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));
1

【3】id 相同不同同时存在： id 如果相同，可以认为是一组，由上往下执行；在所有组里 id 越大，优先级越高，越先执行；

explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id;
1

二、select_type 类型

主要用于区别普通查询、联合查询、子查询等的复杂程度。
【1】SIMPLE： 简单的 select 查询，查询中不包含子查询或者 UNION；
【2】PRIMARY： 查询中若包含任何复杂的自查询，最外层查询为 PRIMARY；

【3】SUBQUERY： 在 SELECT 或 WHERE 中包含子查询；
【4】DERIVED： 在 FROM 列表中包含的子查询被标记为 DERIVED（衍生）MySQL 会递归执行这些子查询，把结果放进临时表；

【5】UNION： 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION，若 UNION 包含在 FROM 子句的子查询，则外层SELECT 将被标记为 DERIVED；
【6】UNION RESULT： 从 UNION表中获取结果的 SELECT；

三、table

显示这行数据是关于那张表

四、type

从最好到最差：system>const>eq_ref>ref>range>index>ALL，一般达到 rang 级别，最好达到 ref 级别。
【1】system： 表只有一行记录（系统表），平时不会出现；
【2】const： 表示通过索引一次就能找到，const用于比较 primary和 unique索引。因为只匹配一行数据，所以很快；

【3】eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描。eg:CEO部门；

【4】ref： 非唯一性索引扫描，返回匹配某个单独值的所有行；

【5】rang： 只检索给定范围的行，使用一个索引来选择行。一般where语句中出现between、<、>、in等的查询。这种范围扫描索引比全表扫描要好，因为只需开始索引的某一点，而结束另一点，不用扫描全部索引；

【6】index： Full Index Scan，index与 ALL区别为 index类型只遍历索引树，索引文件通常比数据文件小。index从索引中读取，而All是从硬盘读取；

【7】ALL： 从磁盘中读取

五、possible_keys 与 key

possible_keys： 显示可能应用到这张表中的索引，一个或多个，查询字段上若存在索引则列出来，但不一定被查询实际使用。
key： 实际使用的索引，如果该值为NULL，则没有使用索引；如果查询中使用了覆盖索引，则该索引仅出现在 key列表中。

【覆盖索引】： 就是 select后面的字段都具备索引，提高了查询效率，前提顺序、个数都要一致；
【理解方式一】： 就是 select的数据列从索引中就能够获取到，不必读取没有必要多余的数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建索引覆盖。
【理解方式二】： 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个数据行，毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
【使用覆盖索引注意】： 如果使用覆盖索引，一定注意 select列表中只取需要的列，不可使用select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

六、key_len

表示索引中使用的字节数，可通过该列查找出使用索引的长度。在不损坏精准性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际长度，即 key_len是根据表定义实际计算出来的，不是通过表内检出来的。

七、ref

显示索引的那一列被使用，如果可能的话，是一个常数。那些列或常量被用于查找索引上的值。

八、rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录的行数。常用于优化时查看，使用该值与实际返回的行数进行比较，如果相差很大，则需要调优。

九、Extra

包含不适合在其他列中显示，但十分重要的信息。
【1】Using fileSort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内索引进行读取。MySQL无法利用索引完成的排序操作称为“文件排序”。导致该问题的原因一般是Where条件和order by子句作用在了不同的列上，一般可以通过合适的索引来减少或者避免。（出现表示不好）

上面提到的常见情况，SQL语句通常写成这样select * from a where type = 5 order by id，这类语句一般会产生Using filesort这个选项，即使你在type和id上分别添加了索引。我们想一下它的工作过程，先根据type的索引从所有数据信息中挑选出满足type = 5条件的，然后根据id列的索引信息对挑选的数据进行排序，所以产生了Using filesort选项，想想怎样可以把后面排序的这个步骤省略掉？联合索引可以解决这个问题。

可以在type, id两列上建立一个联合索引，索引类型一般是BTREE，根据Mysql索引的最左原则，可以知道一共建立了type_index和type_id_index两条索引，由于有了type_id_index这个联合索引，后面的排序步骤就可以省略了，在按照type = 5条件挑选数据时，挂在type = 5节点下的数据，其实按照id列的值也是有顺序的，我们只需要在挑选数据的同时，按照id从小到大的顺序挑选即可，最后得到的数据就是有序的，直接返回就行了，从这一点可以看出，“排序”操作并不是不存在了，只是隐含在了前面必要的步骤中，不需要单独操作了而已，下面举个简单例子，看看具体的效果。

【2】Using temporary： 使用临时表保存中间结果，MySQL在查询结果排序时使用临时表。重用于排序 order by和分组查询 group by。
【3】Using index： 表示相应的 select操作中使用了覆盖索引（convering index），避免访问了表的数据行，效率不错！

【4】using where,using index： 查询的列被索引覆盖，并且 where筛选条件是索引列之一但是不是索引的前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据；

查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查询到符合条件的数据

【5】NULL（既没有Using index，也没有Using where Using index，也没有using where）： 查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)

【6】Using where： 查询条件中使用了索引查找。查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra 中为 Using where。order_id 也是索引。

查询的列未被索引覆盖，where筛选条件非索引列，Extra中为Using where。意味着通过索引或者表扫描的方式进行 where条件的过滤，反过来说，也就是没有可用的索引查找，当然这里也要考虑索引扫描+回表与表扫描的代价。这里的 type都是 all，说明MySQL认为全表扫描是一种比较低的代价。

【7】Using index condition： 查询的列不全在索引中，where条件中是一个前导列的范围

查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）

【8】Using join buffer： 使用了连接缓存。
【9】impossible where： where子句总是false，不能用来获取任何元素。
【10】select tables optimized away： 在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX操作。
【11】distinct： 优化 distinct操作。在找到第一匹配的时候就停止找同样的动作。