EXPLAIN分析查询语句详解

定位了慢查询的SQL后，我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划。MySQL为我们提供了 EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN 语句的各个输出项，可以有针对性的提升我们查询语句的性能。
一、EXPLAIN语句的主要作用如下：
1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询
二、MySQL版本情况
MySQL 5.6.3以前只能 EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT, UPDATE, DELETE。在5.7以前的版本中，想要显示partitions需要使用explain partitions 命令;想要显示filtered需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。
三、基本语法
1、EXPLAIN 或 DESCRIBE语句的语法形式如下:
EXPLAIN SELECT select_options或者DESCRIBE SELECT select_options；
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个EXPLAIN。例如:
EXPLAIN SELECT * form student where name = ‘Jack’
输出的上述信息就是所谓的 执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以 SELECT 开头的查询语句，其余的 DELETE、INSERT、 REPLACE 以及UPDATE 语句等都可以加上 EXPLAIN，用来查看这些语句的执行计划，只是平时我们对 SELECT 语句更感兴趣。
**注意：**执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。
2、EXPLAIN 语句输出的各个列的作用如下：
id：在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
select_type：关键字对应的那个查询的类型
table：表名
partitions：匹配的分区信息
type：针对单表的访问方法
possible_keys：可能用到的索引
key：实际上使用的索引
key_len：实际使用到的索引长度
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows：预估的需要读取的记录条数
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
Extra：一些额外的信息
四、EXPLAIN中各列的作用
1、table：不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所以MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法 ，该条记录的 table 列代表着该表的表名（有时不是真实的表名字，可能是简称。
2、id的含义如下：
id 如果相同，可以认为是一组，从上往下顺序执行
在所有组中， id 值越大，优先级越高，越先执行
关注点： id 号每个号码，表示一趟独立的查询 , 一个 sql 的查询趟数越少越好
3、select type
一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的査询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的，
MySQL为每一个SELECT关键字代表的小査询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type属性，就知道了这个 小查询在整个大査询中扮演了一个什么角色，我们看一下select_type都能取哪些值，请看官方文档:
SIMPLE：简单SELECT(不使用UNION或子查询)
PRIMARY：最外层的SELECT
UNION：UNION 中第二个或之后的SELECT语句
DEPENDENT UNION :UNION 中第二个或之后的SELECT语句取决于外面的查询
UNlON RESULT：UNION的结果
SUBQUERY：子查询的第一个SELECT
DEPENDENT SUBQUERY：子查询的第一个SELECT语句取决于外面的查询
DERIVED：衍生表(FROM子句中的子查询)
MATERIALIZED：物化子查询
UNCACHEABLE SUBQUERY：结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION：UNION 中第二个或之后的SELECT，属于无法缓存的子查询

具体分析如下：
*# 查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型
EXPLAIN SELECT * FROM s1;
#连接查询也算是SIMPLE类型
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
#对于包含UNION或者UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY，其中除了最左边的那个小查询
#以外，其余的小查询的select_type值就是UNION
#MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
#子查询：
#如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询。
#该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = ‘a’;
#如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，
#则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
4、partition：数据的分区信息
代表分区表中的命中情况，非分区表，该项为NULL。一般情况下我们的查询语句的执行计划的partitions列的值都是 NULL。
https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
如果想详细了解，可以创建分区表测试。
5、type表示执行计划的一条记录就代表着MySQL对某个表的 执行査询时的访问方法，又称“访问类型”，其中的 type 列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL 即将使用 ref 访问方法来执行对 s1 表的查询。
完整的访问方法如下（从最好到最差的连接类型以此为：）system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL。
SQL性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts 级别。
(1) system：当表的存储引擎为MyISAM、MEMROY并且表中仅有一条记录，那么type的取值就是system；如果是Innodb引擎表，type列在这个情况通常是all或者index
(2) const：最多只有一行记录匹配。当主键或者唯一二级索引与常量进行等值匹配查询时，此时type的取值就是const
(3) eq_ref：执行连接查询时，被驱动的表使用的是主键或不为null的唯一二级索引进行等值匹配查询时，被驱动表的type的取值就是eq_ref
(4) ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
(5) fulltext：使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，MySQL不管代价，优先选择使用全文索引。
(5) ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null
(6) range：使用索引来进行范围查询，常见于使用=，<>，<，>，>=，<=，IS NULL，<=>，BETWEEN，IN()或者like等运算符查询
(7) index：扫描所有的索引记录
(8) ALL：表示全表扫描，性能最差
6、possible_keys：对某查询语句进行查询时可能使用到那些索引
7、key：对于某查询语句实际上使用到的索引
8、key_len：表示索引中使用的字节数，值越大越好,主要针对于联合索引，有一定的参考意义。
9、ref：显示了在key列记录的索引中，表查找值所用到的列或常量
10、rows：扫描出的行数，这个是个估算的值，并不是真正的结果集
11、filtered：filtered表示返回结果的行数占需读取行数的百分比，filtered列的值依赖于统计信息。
12、Extra：一些额外信息
以下附上宋红康老师的视频链接：
EXPLAIN分析查询语句详解上
EXPLAIN分析查询语句详解中
EXPLAIN分析查询语句详解下