四、关于JOIN的优化

JOIN 连接

内连接

以下三种写法都是内连接：

SELECT * FROM t1 JOIN t2 ON t1.a = t2.b;
SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;
SELECT * FROM t1 CROSS JOIN t2 ON t1.a = t2.b;

左连接、右连接

左连接

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b;

右连接

SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.b;

---

连接的原理

不管是内连接还是左右连接，都需要一个驱动表和一个被驱动表，对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左连接的驱动表就是左边的那个表，右连接的驱动表就是右边的那个表。

连接的大致原理：

1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的访问形式来执行对驱动表的单表查询。
2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

连接过程的伪代码：

// 此处表示遍历满足对t1单表查询结果集中的每一条记录
for (each row in t1) {
    // 此处表示对于某条t1表的记录，遍历满足对t2单表查询结果集中的每一记录
    for (each row in t2) {
        // 判断是否满足join的条件
    }
}

嵌套循环连接（Nested-Loop Join）

上面的过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于，对驱动表执行单表查询后的结果集中记录条数的连接执行方式称之为
嵌套循环连接(Nested-Loop Join) ，这是最简单，也是最笨拙的一种连接查询算法。

比如对于下面这个 SQL：

SELECT * FROM t1 JOIN t2 ON t1.a = t2.b WHERE t1.b IN (1, 2);

会先执行：

SELECT * FROM t1 WHERE t1.b IN (1, 2);

+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 1 | 1 | 1 | 1 | a |
| 2 | 2 | 2 | 2 | b |
| 5 | 2 | 3 | 5 | e |
+---+---+---+---+---+
3 rows in set (0.00 sec)

得到三条记录后，再依次执行：

SELECT * FROM t2 WHERE t2.a = 1;
SELECT * FROM t2 WHERE t2.a = 2;
SELECT * FROM t2 WHERE t2.a = 5;

所以上面的步骤实际上都是针对单表的查询，所以都可以使用索引来帮助查询。

基于块的嵌套循环连接（Block Nested-Loop Join）

嵌套循环连接的缺陷

扫描一个表的过程其实是先把这个表 从磁盘上加载到内存中，然后 在内存中比较匹配的条件是否满足。

实际中表的数据可能很多，内存中无法存放表中所有的记录。所以在扫描表前边的记录时，后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，就会将前边的记录从内存中释放。

但采用 嵌套循环连接算法 的两表连接过程中，被驱动表 会被访问多次，如果 被驱动表 中的数据特别多且不能使用索引进行访问，那就相当于从磁盘上多次读这个表，I/O 代价非常大。

解决办法：尽量减少访问被驱动表的次数。

join buffer

MySQL 中有一个叫做 join buffer 的概念，join buffer 就是：执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个 join buffer 中，然后开始扫描被驱动表，
每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配。因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的 I/O 代价。

这种加入了 join buffer 的 嵌套循环连接算法 称之为 基于块的嵌套连接(Block Nested-Loop Join)算法。

join_buffer_size 参数

join buffer 的大小是可以通过启动参数或者系统变量 join_buffer_size进行配置，默认大小为 **262144 字节(也就是 256KB)**，最小可以设置为 128 字节。
当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大 join_buffer_size 的值来对连接查询进行优化。

---

JOIN 优化

1. 小表连接大表

可显著减少嵌套循环连接的次数。

2. 为被驱动表加索引

牺牲部分空间，换取时间。

3. 消除外连接

• 内连接 的 驱动表 和 被驱动表 的 位置可以互换。
• 左连接 和 右连接 的 驱动表 和 被驱动表 是 固定的。

这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。

外连接和内连接的本质区别

外连接的驱动表的记录，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录 仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。

内连接的驱动表的记录，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录 会被舍弃。

消除外连接案例

表数据：

t1 大表：有多条数据
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
+---+---+

t2 小表：部分数据
+---+---+
| a | b |
+---+---+
| 1 | 1 |
+---+---+

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN (1, 2);

+------+------+------+------+
| t1.a | t1.b | t2.a | t2.b |
+------+------+------+------+
|  1   |  1   |  1   |  1   |
|  2   |  2   | NULL | NULL |
+------+------+------+------+

此 SQL 的驱动表和被驱动表示固定的，无法转换。

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN (1, 2) AND t2.b IS NOT NULL;

+------+------+------+------+
| t1.a | t1.b | t2.a | t2.b |
+------+------+------+------+
|  1   |  1   |  1   |  1   |
+------+------+------+------+

此 SQL 加上 IS NOT NULL 之后被优化成了内连接，就可以利用查询优化器选择最优的连接顺序了。