0. 引言
在实际开发中，我们往往需要针对某些较为复杂的SQL进行优化，首先我们就需要了解SQL的具体执行情况和过程，但是如何知道呢？知道之后又如何优化我们的sql效率呢？
今天我们就来聊聊，如何通过explain来优化SQL
1. 什么是explain? 如何使用？
explain，即执行计划，是mysql提供的用来模拟优化器执行sql语句的指令，用它我们可以知道sql的执行效果，需要注意的是explain是模拟执行，并不是真正的执行，所以其效果分析并不能完全还原真实的执行效果。
explain指令的用法就是explain+要执行的sql，比如
explain select * from user_test.order;
结果显示
在explain官方文档[1]中我们可以知道，explain语句输出的内容中包含下列信息：
2. 输出信息详解
下面我们详细解释下这些输出信息的含义
2.1 id 语句的唯一标识
sql的序列号，表示sql及子句或者操作表的执行顺序，数字越大的越先执行，数字相同的从上往下依次执行，举例说明，我们执行如下的语句
按照我们的正常预期，子查询select id from user_test.user
肯定是先执行，然后才执行外面的针对order
表的查询，那么我们查看执行计划的结果
两个语句的id都是1，但是按照从上往下执行的规则，先执行针对user表的查询，再执行针对order表的查询，符合我们的预期
2.2 select_type 查询类型
查询类型，用来区分是普通查询还是联合查询或者子查询，从官方文档中我们知道，查询类型分为以下几种：
下面我们用具体的sql来举例说明这些查询类型
（1）SIMPLE 简单查询，不包含union或者子查询
（2）PRIMARY 最外层的查询
可以看到因为有比较复杂的子查询语句，所以最外层针对表o(order 表的别名)的查询类型为PRIMARY。这里还有一个针对表<derived2>
的查询被标注为了PRIMARY，这个查询是针对派生表的查询
（3）UNION 第二个及之后的select出现在union之后，被标注为union 我们在上述例子中可以看到，针对user表的第二个查询select id,name from user_test.user where password='1'
被标注为了union，这是因为这个select出现在union之后，并且是第三个出现的select了
（4） UNION RESULT 从union临时表中检索结果的select
从上述的例子中可以看到针对union的临时表<union2,3>
的查询，其实也就是查询出来形成我们的表t
，这个针对union临时表的查询被标注为了UNION RESULT
（5）DERIVED 派生表，表示包含在from子句的子查询中的select 上述例子中的select id,name from user_test.user where user_name='1'
子查询被标注为了DERIVED，这是因为该表是在from之后的子查询中的select
（6）DEPENDENT UNION 在union中的第二个或者之后的查询，并且外部查询依赖于union的结果
从执行结果可以看到，子句SELECT creator FROM user_test.ORDER WHERE id=2
因为是属于union子句，该union产生的结果与外部查询有直接关系，外部查询的结果依赖与这里union的结果
如果大家这里理解的不清楚的，我们来举个反例，让大家弄明白其中的区别，
上述的sql我们做了一点调整，其外层也是一个查询语句select * from
，但是区别在于其并没有where
来限定它的查询范围，也就是说无论你union内部如何变化，它查询的数据依旧是整个union result
，不受影响，但如果有id in
的条件，其结果实际上是有明显的限定条件的，受到union结果的制约，其中的差别十分微妙
（7）subquery 在select或者where列表中包含的子查询
可以看到where后的子查询select avg(amount) from user_test.order
被标注为了subquery
（8）dependent subquery 在select或者where列表中包含的子查询，并且外部查询依赖于select的结果 理解了上述的dependent union，再来理解dependent subquery会更好理解一些，实际上在我们的案例6中出现的第一个子句SELECT creator FROM user_test.ORDER WHERE address LIKE '贵阳%'
就已经被标注为dependent subquery，这是因为该子查询的结果直接影响了外部查询的结果
（9）UNCACHEABLE SUBQUERY 无法缓存的子查询
@@max_connections
是mysql的参数，当语句中使用了mysql参数时，就不会将该结果进行缓存，所以我们可以看到查询子句被标注为了UNCACHEABLE SUBQUERY，当然不是只有使用了mysql参数的语句会被标注为UNCACHEABLE，具体要根据sql语句来分析
（10）UNCACHEABLE UNION 无法缓存的union
结论同上，@@max_connections
是mysql的参数，当语句中使用了mysql参数时，就不会将该结果进行缓存，所以我们可以看到查询子句被标注为了UNCACHEABLE UNION
2.3 table 表名
正在对拿个表进行访问，如果声明了表的别名，就会显示别名。同时也可能是临时表、派生表或者union合并表，如我们上述中的例子所示的<union2,3>
,<derived2>
派生表：<derivedN>
形式，N表示产生派生表的查询的queryId 合并表：<unionN1,N2>
形式，N2,N2表示参与union的查询的queryId
2.4 partitions 匹配的分区
mysql中提供了分区功能，可以将表数据按照一定的规则进行分区，比如按照创建时间进行分区，这样就可以将创建时间久远的数据分到冷数据区，这类数据访问量少，分配的资源就少，创建时间近的分到热数据区，这类数据访问频繁，分配的资源就多，以此实现冷热数据分离，提高查询效率
所以如果表开启了分区功能的，就会显示该sql涉及到的分区，如果没有开启分区，就会显示为空
2.5 type 连接类型
连接类型/访问类型，表示该sql是以何种方式访问的数据，比较常见的是全表扫描，就是简单粗暴的将全表便利一遍找到我们想要的数据，这种方式效率非常低下
所以我们引入了索引的概念，基于索引，我们将连接类型分为以下几种
1、system：表中只有一行记录，一般只出现在一些系统表中，业务表很少出现2、const：该表最多有一条匹配的行 在查询开始时被读取。因为只有一行，这一行中的列的值可以被优化器的其他部分视为常数。常数表非常快，因为它们只被读取一次
3、eq_ref：使用唯一索引进行数据查找 当使用的是主键索引或者唯一索引来进行连接使用时就会使用eq_ref，如下所示
4、const：该表最多有一条匹配的行 在查询开始时被读取。因为只有一行，这一行中的列的值可以被优化器的其他部分视为常数。常数表非常快，因为它们只被读取一次
5、fulltext：使用全文索引进行的数据查找6、ref_or_null：某个字段即需要关联条件，也需要null值的情况
7、index_merge：需要多个索引组合使用进行的数据查找
8、unique_subquery：利用唯一索引来关联子查询
order表中no是唯一索引
9、index_subquery：利用索引来关联子查询 order表中no是索引
10、range：利用索引查询时限制了范围 比如：order表中no和amout字段都是索引，适用的操作符：=
,like 'xxx%'
,>
,<
,>=
,<=
,between and
,is null
,or in
11、index：全索引扫描 当我们需要的数据在索引中就能查找到，或者需要用到索引进行排序时其连接类型就是index，比如：当order表中no字段为索引时，我们只查询该字段，其type就是index
12、ALL：全表扫描
以上访问类型的效率从高到低依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
我们期望效率越高越好，也代表着sql的执行效率更高，一般要求至少达到range
级别，最好能达到ref
级别
2.6 possible_keys 可能会选择的索引
显示可能会在这张表中使用的索引，查询中涉及到的列如果是索引列则可能会被列出显示出来，但不一定在查询中实际使用
如下图所示，no_index索引在查询中被使用了，所以被列出来了
2.7 key 实际选择的索引
查询中实际使用的索引
如2.6中的例子所示，因为order表和products表中no_index对应的字段no都被查询使用了，所以key都为no_index。我们将例子做一些改动：从order中查询出的列不止no,还加上了address
因为在no索引树上不能一次性拿出address数据了，还是要回表进行查询，所以使得实际使用的key就没有no_index了
2.8 key_len 索引的长度
索引中使用的字节数
2.9 ref 索引的哪一列被引用了
显示的是索引的哪一列被使用了，如下述例子
对order表的查询，其查询条件no in
使用的是products表的no
字段
2.10 rows 估计要扫描的数据行数
这时个预估值，非常重要的参数，我们可以通过该参考了解到sql执行需要查找多个行数据，只要能查找我们想要的结果，该值越少越好
2.11 filtered 符合查询条件的数据百分比
符合查询条件的数据百分比
2.12 extra 拓展信息
一些额外的信息，该信息包括有
3. 执行计划并不是一定是最佳
有时候mysql选择的执行计划并不一定是最佳的，所以执行计划的结果仅仅是个优化的参考值而不是一定值。执行计划的选择受以下因素的影响：
1、统计信息不准确，因为innodb采用MVCC机制（多版本并发控制），导致查询的数据并不一定是实际数据，于是执行计划预估的成本不等于实际成本 2、mysql的优化基于成本模型的优化，所以并不一定是最快的优化 3、mysql不会考虑其他并发执行的查询，但是实际执行是会有并发的 4、mysql不考虑不受控制的操作成本，如存储过程或者用户自定义函数的成本
总结
相信通过上述的讲解，大家对执行计划已经有了深入的了解，要体会执行计划的使用场景，并且将其应用到你平时的开发调优中。
文中代码可通过点击阅读原文获取
Elastic开源社区
长按加关注，学习不迷路
你们点点“分享”，给我充点儿电吧~
References
[1]
explain官方文档: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
