数据库查询优化-EXPLAIN的使用

如何使用EXPLAIN

这一篇是考虑到上一篇在优化SQL语句的时候，发现很多东西都需要explain去验证，而补充的内容。

之前在SQL语句优化中说过，使用EXPLAIN进行SQL语句效率的排查，而平时的工作中，explain命令提供给我们去分析这些SQL语句的执行计划。
查看该SQL语句有没有使用上了索引，有没有做全表扫描以及运行查询语句时可能会使用哪种优化器。
下面是一个EXPLAIN的输出结果。

+----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key     | key_len | ref                         | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                        | NULL |   100.00 | NULL        |
|  1 | SIMPLE       | t1          | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | <subquery2>.device_apply_id |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                        |   72 |    10.00 | Using where |
+----+--------------+-------------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+-------------+

返回列

列名	返回内容描述
id	查询序列号
select_type	SELECT类型
table	显示这一行的数据是关于哪张表的
partitions	匹配的分区
type	显示连接使用了何种类型
possible_keys	显示可能应用在这张表中的索引
key	实际使用的索引
key_len	使用的索引的长度
ref	显示索引的哪一列被使用了
rows	MYSQL认为必须检查的用来返回请求数据的行数
filtered	显示了通过条件过滤出的行数的百分比估计值
Extra	关于MYSQL如何解析查询的额外信息

我们需要关注的

我们主要关注的其实是type查找数据的方式，它是一个可以区分效率高低的参数，通过参数我们可以知道查询语句效率是否合理。

select_type

select_type主要返回的是

返回值	值的描述
SIMPLE	简单SELECT
PRIMARY	查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY
UNION	UNION中的第二个或后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果，union语句中第二个select开始后面所有select
SUBQUERY	子查询中的第一个SELECT，结果不依赖于外部查询
DEPENDENT SUBQUERY	子查询中的第一个SELECT，依赖于外部查询
DERIVED	FROM子句的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行

type

访问类型,Mysql找到需要的数据行的方式。下面按照性能由差到好排列

返回值	值的描述
All	全表扫描，没有用到任何的index，效率最差的
index	在扫描全表的顺序是按照索引顺序扫描的，优点是避免了排序，缺点就是要按照索引次序读取整张表
range	范围扫描索引，范围扫描比全表扫描要好
index_subquery	在某些IN类型的查询中使用索引会出现此类型，但是此索引非唯一性索引
unique_subquery	类似index_subquery，但使用的是唯一性索引
index_merge	查询中使用了多个索引，索引被合并优化了
ref_or_null	使用了非唯一性索引进行数据的查找，但是中间发现了null类型，进行了二次查找
ref	使用了非唯一性索引进行数据的查找
eq_ref	类似ref，区别就使用的索引是唯一索引，比如主键
const	通常情况下，将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量
system	只有一行数据的查找
NULL	不用访问索引或表数据，直接获得结果

extra

主要是包含了执行计划中十分重要的额外信息，对于性能调优有很重要的作用

返回值	值的描述
using filesort	说明mysql无法利用索引进行排序，那他只能用排序算法重新进行排序了（需要优化）
using temporary	建立了临时表来保存中间结果，查询完成之后又要把临时表删除（需要优化）
using index	使用了索引。如果同时出现using where表明索引被用来执行索引键值的查找
using where	使用了where进行条件过滤
using sort_union	如何为index_merge联接类型合并索引扫描
using union	如何为index_merge联接类型合并索引扫描
using intersect	如何为index_merge联接类型合并索引扫描
using index for group-by	表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列
using join buffer	表示使用了连接缓存
impossible where	where语句导致没有符合条件的行
Distinct	一旦MySQL找到了与行相联合匹配的行，就不再搜索了
Not exists	MySQL能够对查询进行Left join优化，发现一个匹配的行后，不再为前面的行在该表内检查更多的行
range checked for each record	MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知,可能部分索引可以使用