执行计划（上）

添码座原创大约 14 分钟

什么是执行计划

开发一个系统的时候，一般都是设计好表结构，然后通过代码进行CRUD操作，再根据实际场景创建主联合索引或辅助联合索引，但如果查询当中遇到了问题，该怎么知道问题出在了哪里呢？

这就需要了解MySQL对查询语句的执行计划分析和对SQL的优化措施了，这也属于开发工程师需要掌握的一个必备技能。

其实设计良好的索引本身就属于一种SQL优化的技巧，但SQL优化的技巧并不仅仅只包含索引优化，还有很多其他东西。

不管是简单还是复杂的SQL语句，针对数据表、聚簇索引和二级索引，它是如何检索查询的？如何筛选过滤的？如何排序分组的？又是如何使用函数与进行多表关联的？这就涉及到一个很重要的概念：执行计划。

也就是说，每当工程师提交一个SQL时，MySQL都会针对这个SQL语句的语义去生成一个执行计划，这个计划代表了这个SQL需要查哪些表，用哪些索引，怎么做排序和分组等等内容，如果能够理解它，SQL优化就已经完成一半了。

看懂计划后，根据实际开发情况想各种办法改进SQL语句，进而优化执行计划，最终提升数据库的CRUD性能，这就是一个完整的SQL调优过程。

执行计划的内容

const和ref

学习掌握了索引查询过程后，可以知道下面的SQL查询。

> SELECT * FROM TABLE WHERE name = "xyz";

是可以直接通过二级索引 + 回表操作查到需要的数据的，这种根据索引快速查找数据的过程，在执行计划的type字段中表现为const类型，等于是将查询优化成了一个常量，速度非常快。

执行下面的SQL。

> EXPLAIN SELECT * FROM t_test WHERE id = 1;

然后观察执行结果。

主键查询一般都会是这样的结果，如果利用二级索引查询，则type会变成ref，它的意思是索引要和某个值相比较，可能会找到多个符合条件的行。

如果是多个列的普通二级索引，那么只有当满足最左侧列匹配 + 等值匹配时，才能得到ref，只有二级索引是唯一索引时才会是const。

例外情况：如果查询使用IS NULL这种方式，那么即使name是主键或者唯一索引列，仍然只能得到ref。

range和index

如果使用了聚簇索引，或者唯一二级索引时，那么执行计划的type就是const，如果是普通的等值匹配二级索引，那么type会是ref。

除了const和ref之外，当查询语句利用索引（聚簇索引或二级索引）进行范围筛选，如使用>或<时，type的类型会变成range，表示使用索引返回一个范围中的行。

执行下面的SQL。

> EXPLAIN SELECT * FROM t_test WHERE id > 1 AND id < 3;
> EXPLAIN SELECT * FROM t_test_2 WHERE name > "网络";

然后观察执行结果：

还有另外一种比较特殊的type类型，就是index——它的含义并不是像字面上的通过索引（index）查找数据的意思。

准备实验环境。

-- 删除表
> DROP TABLE IF EXISTS t_test_3;

-- 复制表
> CREATE TABLE t_test_3 AS SELECT * FROM t_test;

-- 复制过来的表没有主键，需要手动指定主键
> ALTER TABLE t_test_3 MODIFY id INT(11) NOT NULL PRIMARY KEY;

-- 增加字段
> ALTER TABLE t_test_3 ADD COLUMN sort TINYINT(1) NOT NULL DEFAULT '0';

-- 创建联合索引
> CREATE INDEX t_test_3_name ON t_test_3 (name, sort);

-- 准备数据
> UPDATE t_test_3 SET sort = 1 WHERE id = 1;

再执行下面的SQL。

> EXPLAIN SELECT name, sort FROM t_test_3 WHERE sort = 1;

然后观察执行结果：

按照之前讲过的最左侧列匹配规则，WHERE语句显然是无法满足要求的，但是这个查询有一个巧合——SELECT子句中的字段刚好就是联合索引里面的全部字段。

针对这种特别巧合的查询，MySQL会做特别处理。

不经过根索引而是直接找到包含(name, sort)所在的索引页，再遍历它全部的数据页叶子结点，然后再遍历到sort=1的那行数据，把其中的全部字段值提取出来。
由于普通二级索引并不包含全部的字段值，所以还需要回表到聚簇索引中找到对应主键的完整数据行，但既然SELECT中除了索引字段没有其他需要回表的数据了，那自然也就不需要回表了。

这种只需要遍历二级索引就能找到数据且不用回表到聚簇索引的查询方式，就是index。

执行计划内容的小结

通过EXPLAIN命令得到的SQL执行信息就是查询分析器的执行计划，只有对索引的结构和使用原理有了比较清晰的了解，才能更好地理解SQL执行计划，也就很容易根据这些执行计划有针对性地优化SQL查询语句了。

const、ref、range和index本质上都是基于B+树的二分查找和链表指针来实现数据查询的，但执行性能上还是有差别的。

const > ref > range > index。
const直接利用主键实现等值匹配。
ref利用二级索引或唯一索引实现等值匹配。
range利用二级索引的二分查找实现范围匹配。
index虽然没有回表操作，但需要遍历某个索引页的全部叶子结点，效率上会比二分查找差些。

更多的执行计划

选择不同的索引

其实type字段对应的值并不只const、ref、range和index，但这几种是99%的查询都可能出现的，当然还包括一个all，意思是全表扫描，性能最差，是需要尽一切努力避免的情况，如果在一个千万级大表中出现全表扫描的all，那就自求多福吧。

之前的SQL语句都比较简单，如果复杂一点的SQL，又该怎么查看执行计划呢？

假设有这样的索引结构(s1, s2)、(s3, s4)，也有这样的SQL语句。

> SELECT * FROM TABLE WHERE s1 = "x" OR s3 >= y;

MySQL该用哪个二级索引去完成执行计划呢？——查询优化器会优先选择在索引里扫描行数比较少的那个二级索引。

如果s1 = "x"经过等值匹配只需要扫描比较少的数据，就会选择(s1, s2)来完成执行计划（可能是ref）。
反之，如果发现s3 >= y经过范围匹配发现需要扫描的数据更少，就会选择(s3, s4)来完成执行计划（可能是range）。

有的查询语句的多个字段中仅有一个字段是有索引的。

例如，只有f1有索引。

> SELECT * FROM TABLE WHERE f1 = "x" AND f2 = "y" AND f3 > 0;

这种情况非常普遍，这种情况下查询优化器会仅仅对f1执行等值匹配（ref），并快速回表到聚簇索引中，把满足f1字段的数据行全部查出来，然后再针对这些数据行筛选出满足f2、f3字段条件的数据。

因此这时候必须尽可能让f1="x"这个条件在索引页中查找出来的数据量较少，才能保证后续的高性能。

选择多个索引

一般情况下，一条SQL语句只会用到一个二级索引，但有些特殊情况下，可能会用到多个二级索引。

> SELECT * FROM TABLE WHERE f1 = "x" AND f2 = "y";
> SELECT * FROM TABLE WHERE f1 = "x" OR f2 = "y";

f1和f2分别属于两个不同的二级索引，这种情况下，要让两个索引同时起作用，需要满足一些前提条件。

取两个B+树交集可以极大减少仅仅选择一个索引后需要筛选的数据量。
取两个B+树并集可以避免回表查询操作，提升查询性能。

这时候查询优化器就会执行这种方案。

准备实验环境。

-- 删除表
> DROP TABLE IF EXISTS t_test_4;

-- 复制表
> CREATE TABLE t_test_4 AS SELECT * FROM t_test;

-- 指定主键
> ALTER TABLE t_test_4 MODIFY id INT(11) NOT NULL PRIMARY KEY;

-- 增加字段
> ALTER TABLE t_test_4 ADD COLUMN sort TINYINT(1) NOT NULL DEFAULT '0';

-- 创建索引
> CREATE INDEX t_test_4_name ON t_test_4 (name);

-- 创建索引
> CREATE INDEX t_test_4_sort ON t_test_4 (sort);

-- 准备数据
> UPDATE t_test_4 SET sort = 1 WHERE id = 1;

再执行下面的SQL。

> EXPLAIN SELECT name, sort FROM t_test_4 WHERE id = 1 OR sort = 1;
> EXPLAIN SELECT name, sort FROM t_test_4 WHERE name = "封面" OR sort = 1;

然后观察执行结果。

由于使用了两个二级索引，type的字段值变成了index_merge，这就是对同一张表的多个索引查询进行合并的结果。

查询的执行顺序

针对单表查询的SQL语句之前已经做了执行计划的说明和实验，也知道了const、ref、range、index和index_merge这些不同的数据查询方式之间的区别，那么在设计、执行多表关联查询时，又该怎么保障执行计划呢？

多表关联在开发中是避免不了的，而且查询的频次可能比单表更高，这才是真正考验索引设计是否合理的关键。

假如有一张最简单的两张表的关联查询。

> SELECT * FROM t1, t2 WHERE t1.f1 = t2.f1 AND t1.f2 = "x" AND t2.f2 = "y";

SQL基础查询会把两张表做一个笛卡尔积，然后在笛卡尔积中筛选出满足WHERE条件里的数据。

除了t1.f1 = t2.f1这个真正的关联条件之外，t1.f2 = "x" AND t2.f2 = "y"都是在各自所在数据表中做的筛选，那么刚才那个SQL的查询过程也就很清楚了。

先在笛卡尔积中筛选出能够满足t1.f1和t2.f1相等的条件。
然后在筛选出来的数据中寻找能够满足t1.f2="x"并且t2.f2="y"的数据。

但如果把WHERE的顺序调换：WHERE t1.f2 = "x" AND t2.f2 = "y" AND t1.f1 = t2.f1，结果会有不同吗？可以通过实验来验证这两种顺序的差别。

> DROP TABLE IF EXISTS `t_org`;
> CREATE TABLE `t_org` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `name` varchar(64) NOT NULL COMMENT '部门名称',
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

> INSERT INTO `t_org` VALUES (1, '销售部');
> INSERT INTO `t_org` VALUES (2, '研发部');


> DROP TABLE IF EXISTS `t_emp`;
> CREATE TABLE `t_emp` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `branchid` int(11) NOT NULL COMMENT '部门编码',
    `name` varchar(64) NOT NULL COMMENT '姓名',
    `result` varchar(128) NOT NULL COMMENT '绩效',
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

> INSERT INTO `t_emp` VALUES (1, 1, '张三', '优秀');
> INSERT INTO `t_emp` VALUES (2, 1, '李四', '良好');
> INSERT INTO `t_emp` VALUES (3, 2, '王五', '极好');
> INSERT INTO `t_emp` VALUES (4, 2, '赵六', '较差');

再执行下面的SQL。

> EXPLAIN SELECT o.name, e.name, e.result FROM t_emp AS e, t_org AS o WHERE e.branchid = o.id AND e.name = "张三" AND o.name = "销售部";
> EXPLAIN SELECT o.name, e.name, e.result FROM t_emp AS e, t_org AS o WHERE e.name = "张三" AND o.name = "销售部" AND e.branchid = o.id;

然后观察执行结果。

type中出现了ALL，表示有全表扫描，说明表中无索引或者索引完全没有起作用。
单表查询如果利用了主键索引那么type就是const，而多表查询则是eq_ref。
两条语句的执行情况一模一样，说明 调换WHERE字段顺序对执行计划没有影响。

连接算法

什么是连接算法

数据库对表进行连接操作的本质，是把相关表的记录都取出来之后，进行集合运算（交集/并集/差集），并返回给用户所需要的结果，这个过程如果不做任何限制条件去过滤数据的话，多张表联接起来产生的笛卡尔积可能是非常巨大的，甚至拖垮数据库。

以三表关联查询为例，如果每张表都有10000条数据，那么仅仅三张表的笛卡尔积就有10000_0000_0000（一万亿）条数据记录，还不算各种缓存和计算的开销！

所以，通过某些过滤条件排除掉一些不满足要求的数据是很有必要的。

> SELECT * FROM t1, t2 WHERE t1.f1 > x AND t1.f1 = t2.f2 AND t2.f2 < y

以上面的SQL语句为例，其中的过滤条件可以分成两种：涉及单表的条件t1.f1 > x和t2.f2 < y，以及涉及多表的条件t1.f1 = t2.f2。

在多表关联查询中，一般会把左外连接中左边的表、右外连接中右边的表称为关联表或者驱动表，其他的表则称为被关联表或者被驱动表（内联接会选取查询成本最低的表为驱动表）。

有时A LEFT JOIN B的成本和B LEFT JOIN A的成本是完全不同的——也就是说，把关联表和被关联表的顺序调换一下，查询的性能可能就会有很大不同，之所以会这样，除了索引的因素之外，跟使用的连接算法有很大的关系。

所以，连接算法通俗地说就是查询时MySQL执行的过滤数据的策略，目前MySQL的8.0版本支持Nested-Loop Join（又分为Simple Nested-Loop Join和Block Nested-Loop Join）、Hash Join和Batched Key Access Join。

Nested-Loop Join嵌套循环连接

嵌套循环连接又分为简单嵌套循环连接（Simple Nested-Loop Join）和基于块的嵌套循环连接（Block Nested-Loop Join）。

简单嵌套循环连接（Simple Nested-Loop Join）：驱动表中的每一条记录与被驱动表中的每一条记录进行比较判断，驱动表只会被访问一遍，但被驱动表却会被访问到好多遍，逐条地和驱动表做比较。
基于块的嵌套循环连接（Block Nested-Loop Join）：有时候内存可能并不能完全放下表中所有的记录，尤其是被驱动表还要循环访问好几次，而且还要想办法减少访问表的次数，否则也会给I/O造成很大压力，但是如果一次性加载多条驱动表或被驱动表中的记录，既减少内存压力也可以大大减少I/O压力。

BNLJ是对SNLJ的改进，相比SNLJ，BNLJ仅多了一个所谓的Join Buffer开销，这个开销最终会反映到执行计划的extra字段中，值是Using join buffer。