SQL 优化（四）：合理使用 join

在工作的时候经常听到的一句话就是，“这条 SQL 因为 join 了很多表，导致查询速度比较慢”，可以从侧面反映出，join语句对性能的影响是比较大的，而且大部分人不知道如何进行优化。这篇文章我们来讲讲join的执行过程，以及该如何对join语句进行优化

有A、B两张表，表的初始化语句如下

CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a`(`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE B like A;

接着我们向表A插入 100 条数据，向表B插入 1000 条数据

Index Nested-Loop Join

我们看下这条语句

select * from A straight_join B on A.a = B.a

explain 查看该语句的执行计划

可以看到，该语句对 A 表做了全表扫描，并使用了 B 表上的索引，完整的执行流程如下：

1. 从 A 表中读取一行数据 R
2. 从 R 中取出 a 字段，根据 a 字段到 B 表中查询 （使用B表的索引a ）
3. 从 B 表中取出 B 表的数据行
4. 重复 1～3，直到 A 表遍历结束

可以看到，整个查询流程扫描了 200 行，其中对 A 表做全表扫描了 100 行，另外 100 行是对 B 表的嵌套查询中产生的

对于这种遍历驱动表A，然后根据A中每一行的值，去被驱动表B查找，并使用驱动表B的索引的搜索过程，由于在形式上与我们写代码时的嵌套查询类似，因此该过程称之为 Index Nested-Loop Join (简称NLJ)

Simple Nested-Loop join

如果我们将查询语句写成这样

select * from A straight_join B on A.a = B.b

由于 B 表的 b 字段上没有索引，因此 sql 的执行流程如下

1. 从 A 表中读取一行数据 R
2. 从 R 中取出 a 字段，根据 a 字段到 B 表中查询 （对 B 表进行全表遍历）
3. 从 B 表中取出 B 表的数据行
4. 重复 1～3，直到 A 表遍历结束

与NLJ不同的是，由于进行A.a=B.b的时候，无法使用索引，所以整个查询总共扫描了 1000 * 100 = 10w 行，这种查询方式也被称为 Simple Nested-Loop Join

Block Nested-Loop Join

由于Simple Nested-Loop Join对两张表都做了全表扫描，比较笨重，在性能上表现很差。为了解决这个问题，MySQL 使用了另外一种叫做Block Nested-Loop Join的算法（简称 BNL）

查看select * from A straight_join B on A.a = B.b;的执行计划，可以看到，MySQL 并没有使用Simple Neststed-Loop Join，而是使用Block Nested Loop Join

BNL的执行流程如下：

1. 把 A 表的数据读出来放到join_buffer中，由于我们是select *，所以相当于把整个 A 表放入到内存中
2. 遍历 B 表，取出 B 表的每行数据，将其与内存中的 A 表进行比对，满足 join 条件的，作为结果集的一部分返回

这时你可能会说，如果 A 表的数据太多，内存放不下怎么办呢？这也很简单，只需要对 A 表进行分段缓存就行啦，这时候的执行流程如下：

1. 扫描 A 表，顺序将 A 表的数据加入到join_buffer中，直到join_buffer满了，执行第二步
2. 扫描 B 表，取出 B 表的每行数据，将其与join_buffer中的数据进行比对，满足 join 条件的，作为结果集的一部分返回
3. 清空join_buffer，继续读取 A 表的数据，重复 1、2 步，直到遍历结束

对 A 表进行分段（分块）缓存，这也是Block Nested-Loop Join名字的由来。可以看到，Block Nested-Loop Join与Simple Nested-Loop Join的唯一区别，就是 NBL 对驱动表进行了缓存，将嵌套匹配的操作都放到内存中进行，大大提升了关联查询的性能

10w 次内存操作跟 10w 次磁盘操作，性能相差还是很大的

如何优化 join

了解 join 的三种查询算法后，可以知道，NLJ 的查询性能是最快的。所以第一个优化方案是：尽量使用索引字段进行关联，让 MySQL 能使用 NLJ

还有一点是，不管哪种查询算法，都对驱动表做了全表扫描，所以我们在关联的时候，应该用“小表”作为驱动表，“大表”作为被驱动表，这样就能减少全表扫描的行数了，提高性能

注意，“小表”、“大表” 指的不一定是表的数据量大小，而是在查询的时候，扫描行数多的称之为“大表”，反之称之为“小表”

如 t1 表有 10w 数据，t2 表有 1000 数据，那么可以用 t2 表做驱动表，sql 可以这样写

select * from t2 straight_join t1 on t2.a = t1.a;

如果 SQL 带 where 条件，where 后的 t1 表只有 100 条数据，这时候，t1 就是“小表”，t2 则是“大表”，那么就应该用 t1 做驱动表

select * from t1 
straight_join t2 on t1.a = t2.a
where t1.id <= 100;

关注公众号：huangxy，一起学习，一起进步