0. 引言

在实际开发中，我们往往需要针对某些较为复杂的SQL进行优化，首先我们就需要了解SQL的具体执行情况和过程，但是如何知道呢？知道之后又如何优化我们的sql效率呢？

今天我们就来聊聊，如何通过explain来优化SQL

1. 什么是explain? 如何使用？

explain，即执行计划，是mysql提供的用来模拟优化器执行sql语句的指令，用它我们可以知道sql的执行效果，需要注意的是explain是模拟执行，并不是真正的执行，所以其效果分析并不能完全还原真实的执行效果。

explain指令的用法就是explain+要执行的sql，比如

explain select * from user_test.order;

结果显示

在explain官方文档中我们可以知道，explain语句输出的内容中包含下列信息：

列名	含义
id	语句的唯一标识
select_type	查询类型
table	表名
partitions	匹配的分区
type	连接类型（left join,right join等）
possible_keys	可能会选择的索引
key	实际选择的索引
key_len	索引的长度
ref	索引的哪一列被引用了
rows	估计要扫描的数据行数
filtered	符合查询条件的数据百分比
extra	扩展项，附加信息，没有定值，根据执行的sql显示不同的信息

2. 输出信息详解

下面我们详细解释下这些输出信息的含义

2.1 id 语句的唯一标识

sql的序列号，表示sql及子句或者操作表的执行顺序，数字越大的越先执行，数字相同的从上往下依次执行，举例说明，我们执行如下的语句

EXPLAIN SELECT
	* 
FROM
	user_test.`order` 
WHERE
	id IN (SELECT id FROM user_test.`user`)

按照我们的正常预期，子查询select id from user_test.user肯定是先执行，然后才执行外面的针对order表的查询，那么我们查看执行计划的结果

两个语句的id都是1，但是按照从上往下执行的规则，先执行针对user表的查询，再执行针对order表的查询，符合我们的预期

2.2 select_type 查询类型

查询类型，用来区分是普通查询还是联合查询或者子查询，从官方文档中我们知道，查询类型分为以下几种：

查询类型	含义
SIMPLE	简单查询，不包含union或者子查询
PRIMARY	查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
UNION	第二个及之后的select出现在union之后，被标注为union
DEPENDENT UNION	在union中的第二个或者之后的查询，并且外部查询依赖于union的结果
UNION RESULT	从union临时表中检索结果的select
SUBQUERY	在select或者where列表中包含的子查询
DEPENDENT SUBQUERY	在select或者where列表中包含的子查询，并且外部查询依赖于select的结果
DERIVED	派生表，表示包含在from子句的子查询中的select
UNCACHEABLE SUBQUERY	无法缓存的子查询
UNCACHEABLE UNION	无法缓存的union

下面我们用具体的sql来举例说明这些查询类型 （1）SIMPLE 简单查询，不包含union或者子查询

explain select * from user_test.order

（2）PRIMARY 最外层的查询

EXPLAIN SELECT
	o.id,
	o.NO,
	t.NAME 
FROM
	( SELECT id, NAME FROM user_test.USER WHERE user_name = '1' 
	  UNION 
	  SELECT id, NAME FROM user_test.USER WHERE PASSWORD = '1' ) t
	LEFT JOIN user_test.ORDER o ON o.creator = t.id

可以看到因为有比较复杂的子查询语句，所以最外层针对表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的结果

EXPLAIN SELECT
	* 
FROM
	user_test.user 
WHERE
	id IN (
	SELECT creator FROM user_test.ORDER WHERE address LIKE '贵阳%' 
	  UNION
	SELECT creator FROM user_test.ORDER WHERE id=2) 

从执行结果可以看到，子句SELECT creator FROM user_test.ORDER WHERE id=2因为是属于union子句，该union产生的结果与外部查询有直接关系，外部查询的结果依赖与这里union的结果 如果大家这里理解的不清楚的，我们来举个反例，让大家弄明白其中的区别，

EXPLAIN SELECT
	*
FROM
	( SELECT creator AS id FROM user_test.ORDER WHERE address LIKE '贵阳%' 
	 UNION 
	 SELECT creator AS id FROM user_test.ORDER WHERE id = 2 ) t

上述的sql我们做了一点调整，其外层也是一个查询语句select * from ，但是区别在于其并没有where来限定它的查询范围，也就是说无论你union内部如何变化，它查询的数据依旧是整个union result，不受影响，但如果有id in 的条件，其结果实际上是有明显的限定条件的，受到union结果的制约，其中的差别十分微妙

（7）subquery 在select或者where列表中包含的子查询

EXPLAIN SELECT
	* 
FROM
	user_test.ORDER 
WHERE
	amount > (SELECT avg( amount ) FROM user_test.ORDER)

可以看到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 无法缓存的子查询

EXPLAIN SELECT
	* 
FROM
	user_test.ORDER 
WHERE
	amount > (SELECT avg( amount ) FROM user_test.ORDER where amount > @@max_connections)

@@max_connections是mysql的参数，当语句中使用了mysql参数时，就不会将该结果进行缓存，所以我们可以看到查询子句被标注为了UNCACHEABLE SUBQUERY，当然不是只有使用了mysql参数的语句会被标注为UNCACHEABLE，具体要根据sql语句来分析

（10）UNCACHEABLE UNION 无法缓存的union

EXPLAIN SELECT
	* 
	from (
	SELECT creator FROM user_test.ORDER WHERE address LIKE '贵阳%' 
	  UNION
	SELECT creator FROM user_test.ORDER WHERE amount>@@max_connections) t

结论同上，@@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是以何种方式访问的数据，比较常见的是全表扫描，就是简单粗暴的将全表便利一遍找到我们想要的数据，这种方式效率非常低下

所以我们引入了索引的概念，基于索引，我们将连接类型分为以下几种

• system：表中只有一行记录，一般只出现在一些系统表中，业务表很少出现

• const：该表最多有一条匹配的行 在查询开始时被读取。因为只有一行，这一行中的列的值可以被优化器的其他部分视为常数。常数表非常快，因为它们只被读取一次

explain select * from user_test.order where id=2

• eq_ref：使用唯一索引进行数据查找 当使用的是主键索引或者唯一索引来进行连接使用时就会使用eq_ref，如下所示

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

• ref：使用了非唯一性索引进行数据的查找 当使用的是非唯一索引来进行连接使用时就会使用eq_ref，如下所示

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

• fulltext：使用全文索引进行的数据查找

• ref_or_null：某个字段即需要关联条件，也需要null值的情况

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

• index_merge：需要多个索引组合使用进行的数据查找

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

• unique_subquery：利用唯一索引来关联子查询 order表中no是唯一索引

explain select no from user_test.order where no in 
(select no from user_test.products)

• index_subquery：利用索引来关联子查询 order表中no是索引

explain select no from user_test.order where no in
 (select no from user_test.products)

• range：利用索引查询时限制了范围 比如：order表中no和amout字段都是索引，适用的操作符：=,like 'xxx%',>,<,>=,<=,between and,is null,or in

explain select no from user_test.order where no ='ss'
explain select no from user_test.order where no like 'ss%'
explain select no from user_test.order where amount > 10;
explain select no from user_test.order where amount < 10;
explain select no from user_test.order where amount BETWEEN 10 and 200;

• index：全索引扫描 当我们需要的数据在索引中就能查找到，或者需要用到索引进行排序时其连接类型就是index，比如：当order表中no字段为索引时，我们只查询该字段，其type就是index

explain select no from user_test.order 

• 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 可能会选择的索引

显示可能会在这张表中使用的索引，查询中涉及到的列如果是索引列则可能会被列出显示出来，但不一定在查询中实际使用

explain select no from user_test.order where no in
 (select no from user_test.products)

如下图所示，no_index索引在查询中被使用了，所以被列出来了

2.7 key 实际选择的索引

查询中实际使用的索引

如2.6中的例子所示，因为order表和products表中no_index对应的字段no都被查询使用了，所以key都为no_index。我们将例子做一些改动：从order中查询出的列不止no,还加上了address

EXPLAIN SELECT no,address 
FROM
	user_test.ORDER 
WHERE
	NO IN (
	SELECT NO 
FROM
	user_test.products)

因为在no索引树上不能一次性拿出address数据了，还是要回表进行查询，所以使得实际使用的key就没有no_index了

2.8 key_len 索引的长度

索引中使用的字节数

2.9 ref 索引的哪一列被引用了

显示的是索引的哪一列被使用了，如下述例子

EXPLAIN SELECT NO 
FROM
	user_test.ORDER 
WHERE
	NO IN (
	SELECT NO 
	FROM
	user_test.products)

对order表的查询，其查询条件no in使用的是products表的no字段

2.10 rows 估计要扫描的数据行数

这时个预估值，非常重要的参数，我们可以通过该参考了解到sql执行需要查找多个行数据，只要能查找我们想要的结果，该值越少越好

2.11 filtered 符合查询条件的数据百分比

符合查询条件的数据百分比

2.12 extra 拓展信息

一些额外的信息，该信息包括有

扩展信息	含义
using filesort	说明mysql不能利用索引排序
using temporary	sql建立并使用了临时表
using index	当前的索引满足覆盖索引，即查询的数据就是索引列，可以直接从索引树中获取到想要的列
using where	使用了where语句
using join buffer	使用了连接缓存
impossible where	结果总是不满足where 条件

总结

相信通过上述的讲解，大家对执行计划已经有了深入的了解，要体会执行计划的使用场景，并且将其应用到你平时的开发调优中。

关注专栏，了解更多新鲜内容