什么是执行计划
有了慢查询语句后，就要对语句进行分析。一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，我们需要搞懂EPLATNEXPLAIN的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。
通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析查询语句或是表结构的性能瓶颈，总的来说通过EXPLAIN我们可以：
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
执行计划的语法
执行计划的语法其实非常简单： 在 SQL 查询的前面加上 EXPLAIN 关键字就行。 比如：
EXPLAIN select * from table1
重点的就是 EXPLAIN 后面你要分析的 SQL 语句除了以 SELECT 开头的查询语句， 其余的 DELETE、 INSERT、REPLACE 以及 UPOATE 语句前边都可以加上 EXPLAIN， 用来查看这些语句的执行计划， 不过我们这里对 SELECT 语句更感兴趣， 所以后边只会以 SELECT 语句为例来描述EXPLAIN 语句的用法。
执行计划详解
为了让大家先有一个感性的认识， 我们把 EXPLAIN 语句输出的各个列的作用先大致罗列一下:
id： 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id
select_type： SELECT 关键字对应的那个查询的类型
table： 表名
partitions： 匹配的分区信息
type： 针对单表的访问方法
possible_keys： 可能用到的索引
key： 实际上使用的索引
key_len： 实际使用到的索引长度
ref： 当使用索引列等值查询时， 与索引列进行等值匹配的对象信息
rows： 预估的需要读取的记录条数
filtered： 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra： —些额外的信息
看到这里， 是不是一脸懵逼， 这是正常的， 这里把它们都列出来只是为了描述一个轮廓， 随着我们课程的进行， 我们会仔细讲解每个列的含义， 显示值的含义。
为了方便学习， 我们使用范例表 order_exp：
这个表在库中有个三个派生表 s1， s2， order_exp_cut， 表结构基本一致， 有少许差别：
- table
不论我们的查询语句有多复杂， 里边包含了多少个表， 到最后也是需要对每个表进行单表访问的， MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法， 该条记录的 table 列代表着该表的表名。
可以看见， 只涉及对 s1 表的单表查询， 所以 EXPLAIN 输出中只有一条记录，其中的 table 列的值是 s1， 而连接查询的执行计划中有两条记录， 这两条记录的table 列分别是 s1 和 s2.
- id
我们知道我们写的查询语句一般都以 SELECT 关键字开头， 比较简单的查询语句里只有一个 SELECT 关键字，
稍微复杂一点的连接查询中也只有一个 SELECT 关键字， 比如:
SELECT * FROM s1 INNER J0IN s2 ON s1.id = s2.id WHERE s1.order_status = 0;
但是下边两种情况下在一条查询语句中会出现多个 SELECT 关键字:
1、 查询中包含子查询的情况
比如下边这个查询语句中就包含 2 个 SELECT 关键字:
SELECT* FROM s1 WHERE id IN ( SELECT * FROM s2);
2、 查询中包含 UNION 语句的情况
比如下边这个查询语句中也包含 2 个 SELECT 关键字:
SELECT * FROM s1 UNION SELECT * FROM s2 ;
查询语句中每出现一个 SELECT 关键字， MySQL 就会为它分配一个唯一的 id值。 这个 id 值就是 EXPLAIN 语句的第一个列。
单 SELECT 关键字
比如下边这个查询中只有一个 SELECT 关键字， 所以 EXPLAIN 的结果中也就只有一条 id 列为 1 的记录∶
EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';
连接查询
对于连接查询来说， 一个 SELECT 关键字后边的 FROM 子句中可以跟随多个表， 所以在连接查询的执行计划中， 每个表都会对应一条记录， 但是这些记录的 id 值都是相同的， 比如:
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
可以看到， 上述连接查询中参与连接的 s1 和 s2 表分别对应一条记录， 但是这两条记录对应的 id 值都是 1。 这里需要大家记住的是， 在连接查询的执行计划中， 每个表都会对应一条记录， 这些记录的 id 列的值是相同的。
包含子查询
对于包含子查询的查询语句来说， 就可能涉及多个 SELECT 关键字， 所以在包含子查询的查询语句的执行计划中， 每个 SELECT 关键字都会对应一个唯一的 id 值， 比如这样:
EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a';
但是这里大家需要特别注意， 查询优化器可能对涉及子查询的查询语句进行重写， 从而转换为连接查询。 所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写， 直接查看执行计划就好了， 比如说:
EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE order_no = 'a');
可以看到， 虽然我们的查询语句是一个子查询， 但是执行计划中 s1 和 s2 表对应的记录的 id 值全部是 1， 这就表明了查询优化器将子查询转换为了连接查询。
包含 UNION 子句
对于包含 UNION 子句的查询语句来说， 每个 SELECT 关键字对应一个 id 值也是没错的， 不过还是有点儿特别的东西， 比方说下边这个查询:
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
这个语句的执行计划的第三条记录为什么这样？ UNION 子句会把多个查询的结果集合并起来并对结果集中的记录进行去重， 怎么去重呢? MySQL 使用的是内部的临时表。 正如上边的查询计划中所示， UNION 子句是为了把 id 为 1 的查
询和id为2的查询的结果集合并起来并去重， 所以在内部创建了一个名为<union1，2>的临时表（就是执行计划第三条记录的 table 列的名称)， id 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的。
跟 UNION 对比起来， UNION ALL 就不需要为最终的结果集进行去重， 它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户， 所以也就不需要使用临时表。 所以在包含 UNION ALL 子句的查询的执行计划中， 就没有那个 id
为 NULL 的记录， 如下所示:
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
- select_type 列
通过上边的内容我们知道， 一条大的查询语句里边可以包含若干个 SELECT关键字， 每个 SELECT 关键字代表着一个小的查询语句， 而每个 SELECT 关键字的From 子句中都可以包含若干张表（这些表用来做连接查询)， 每一张表都对应着执行计划输出中的一条记录， 对于在同一个 SELECT 关键字中的表来说， 它们的id 值是相同的。
MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为select_type 的属性， 意思是我们只要知道了某个小查询的 select_type 属性， 就知道了这个小查询在整个大查询中扮演了一个什么角色， select_type 取值如下：
SIMPLE： 简单的 select 查询,不使用 union 及子查询
PRIMARY： 最外层的 select 查询
UNION： UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
UNION RESULT： UNION 结果集
SUBQUERY： 子查询中的第一个 select 查询,不依赖于外部查询的结果集
DEPENDENT UNION： UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
DEPENDENT SUBQUERY： 子查询中的第一个 select 查询,依赖于外部查询的结果集
DERIVED： 用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里。
MATERIALIZED： 物化子查询
UNCACHEABLE SUBQUERY： 结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估， 出现极少。
UNCACHEABLE UNION： UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询， 出现极少。
SIMPLE
EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';
连接查询也算是 SIMPLE 类型
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
PRIMARY
对于包含 UNION、 UNION ALL 或者子查询的大查询来说， 它是由几个小查询组成的， 其中最左边的那个查询的 select_type 值就是 PRIMARY， 比方说：
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
从结果中可以看到， 最左边的小查询 SELECT * FROMN s1 对应的是执行计划中的第一条记录， 它的 select_type 值就是 PRIMARY。
UNION
对于包含 UNION 或者 UNION ALL 的大查询来说， 它是由几个小查询组成的，其中除了最左边的那个小查询以外， 其余的查询的 select_type 值就是 UNION，可以对比上一个例子的效果。
UNION RESULT
MySQL 选择使用临时表来完成 UNION 查询的去重工作， 针对该临时表的查询的 select_type 就是 UNION RESULT， 例子上边有。
SUBQUERY
如果包含子查询的查询语句不能够转为对应的 semi-join 的形式， 并且该子查询是不相关子查询， 并且查询优化器决定采用将该子查询物化的方案来执行该子查询时， 该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是
SUBQUERY， 比如下边这个查询:
EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = 'a';
可以看到， 外层查询的 select_type 就是 PRTIMARY， 子查询的 select_type 就是 SUBOUERY。 需要大家注意的是， 由于 select_type 为 SUBQUERY 的子查询由于会被物化， 所以只需要执行一遍。
上面的说明里出现了几个新名字， 这里稍微解释下， 以后会有详解讲解。
semi-join： 半连接优化技术， 本质上是把子查询上拉到父查询中， 与父查询的表做 join 操作。 关键词是“上拉” 。 对于子查询， 其子查询部分相对于父表的每个符合条件的元组， 都要把子查询执行一轮。 效率低下。 用半连接操作优化子
查询， 是把子查询上拉到父查询中， 这样子查询的表和父查询中的表是并列关系，父表的每个符合条件的元组， 只需要在子表中找符合条件的元组即可。 简单来说，就是通过将子查询上拉对父查询中的数据进行筛选， 以使获取到最少量的足以对父查询记录进行筛选的信息就足够了。
子查询物化： 子查询的结果通常缓存在内存或临时表中。
关联/相关子查询： 子查询的执行依赖于外部查询。 多数情况下是子查询的WHERE 子句中引用了外部查询的表。 自然“非关联/相关子查询” 的执行则不依赖与外部的查询。
DEPENDENT UNION、 DEPENDENT SUBQUERY
在包含 UNION 或者 UNION ALL 的大查询中， 如果各个小查询都依赖于外层查询的话， 那除了最左边的那个小查询之外， 其余的小查询的 select_type 的值就是 DEPENDENT UNION。 比方说下边这个查询：
EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE id = 716 UNION SELECT id FROM s1 WHERE id = 718);
这个查询比较复杂， 大查询里包含了一个子查询， 子查询里又是由 UNION连起来的两个小查询。 从执行计划中可以看出来， SELECT id FROM s2 WHERE id =716 这个小查询由于是子查询中第一个查询， 所以它的 select_type 是 DEPENDENT SUBQUERY， 而 SELECT id FROM s1 WHERE id = 718 这个查询的 select_type 就是DEPENDENT UNION。
是不是很奇怪这条语句并没有依赖外部的查询？ MySQL 优化器对 IN 操作符的优化会将 IN 中的非关联子查询优化成一个关联子查询。 我们可以在执行上面那个执行计划后， 马上执行 show warnings\G， 可以看到 MySQL 对 SQL 语句的大
致改写情况：
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`s1`.`id` AS `id`,`test`.`s1`.`order_no` AS `order_no`,`test`.`s1`.`order_note` AS `order_note`,`test`.`s1`.`insert_time` AS `insert_time`,`test`.`s1`.`expire_duration` AS `expire_duration`,`test`.`s1`.`expire_time` AS `expire_time`,`test`.`s1`.`order_status` AS `order_status` from `test`.`s1` where <in_optimizer>(`test`.`s1`.`id`,<exists>(/* select#2 */ select 1 from `test`.`s2` where ((<cache>(`test`.`s1`.`id`) = 716) and multiple equal(716, NULL)) union /* select#3 */ select 1 from `test`.`s1` where ((<cache>(`test`.`s1`.`id`) = 718) and multiple equal(718, NULL))))
1 row in set (0.00 sec)
DERIVED
对于采用物化的方式执行的包含派生表的查询， 该派生表对应的子查询的select_type 就是 DERIVED。
EXPLAIN SELECT * FROM (SELECT id, count(*) as c FROM s1 GROUP BY id) AS derived_s1 where c >1;
从执行计划中可以看出， id 为 2 的记录就代表子查询的执行方式， 它的 select_type 是 DERIVED ， 说明该子查询是以物化的方式执行的。 id 为 1 的记录代表外层查询， 大家注意看它的 table 列显示的是， 表示该查询是针对将派生表物化之后的表进行查询的。
MATERIALIZED
当查询优化器在执行包含子查询的语句时， 选择将子查询物化之后与外层查询进行连接查询时， 该子查询对应的 select_type 属性就是 MATERIALIZED， 比如下边这个查询︰
EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2);
执行计划的第三条记录的 id 值为 2， 从它的 select_type 值为 NATERIALIED 可以看出， 查询优化器是要把子查询先转换成物化表。
然后看执行计划的前两条记录的 iad 值都为 1， 说明这两条记录对应的表进行连接查询， 需要注意的是第二条记录的 table 列的值是， 说明该表其实就是 id 为 2 对应的子查询执行之后产生的物化表， 然后将 s1 和该物化表进
行连接查询。
UNCACHEABLE SUBQUERY、 UNCACHEABLE UNION出现极少， 不做深入讲解
- partitions
和分区表有关， 一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL。
- type
我们前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法/访问类型， 其中的 type 列就表明了这个访问方法/访问类型是个什么东西， 是较为重要的一个指标， 结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
出现比较多的是 system>const>eq_ref>ref>range>index>ALL一般来说， 得保证查询至少达到 range 级别， 最好能达到 ref。
– 未完待续