MySQL JOIN 连接：到底能不能写 JOIN？

---

前言

很多人下意识地认为 JOIN 会降低 SQL 的性能效率，所以就将一条多表 SQL 拆成单表的一条条查询，但这样反而会影响 SQL 执行的效率。究其原因，在于不了解 JOIN 的实现过程

---

一、JOIN连接算法

Simple Nested-Loop Join：

驱动表和被驱动表都走全表扫描

Index Nested-Loop Join(NLJ)：

驱动表走全表扫描，被驱动表通过索引扫描

Block Nested-Loop Join(BNL)：

被驱动字段不存在索引，因此被驱动表也只能走全表扫描，此时通过join buffer，将驱动表的数据一块块读到join buffer, 然后从被驱动表一条条读取数据做join操作(与join buffer中的数据对比)，重复这两步操作，直到join操作完成；由此可见合理设置join buffer大小至关重要，join buffer越大分块放入次数越少, I/O次数也就越少

Batched Key Access(BKA)：

是NLJ算法的优化版本。借助于MRR优化操作，将多条主键ID暂存join buffer，排序后从聚簇索引读取数据，因为磁盘顺序读取的效率也很高

二、OLTP 业务能不能写 JOIN？

先说结论：可以使用，但需要考虑场景

1、如果两张join表数据量很小

直接使用即可，不会有性能问题

2、如果被驱动表能走索引

这种情况，innodb 引擎会选择使用NLJ算法，如果被驱动表走索引并且索引区分度较高，也可以放心使用。这种情况语句执行流程如下：

驱动表t1, 被驱动表t2:

• 从表 t1 中读入一行数据 R；
• 从数据行 R 中，取出 a 字段到表 t2 里去查找；
• 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
• 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

可以看出，只要被驱动表的索引区分度足够高，是可以快速检索数据，因此可以放心使用

3、被驱动表没有索引

假设：驱动表t1，被驱动表t2

BNL算法执行流程：

• 把表 t1 的数据读入线程内存 join_buffer 中，比如 select * … 语句，是把整个表 t1 放入了内存；
• 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

以上流程会进行t1, t2全表扫描，看起来和Simple Nested-Loop Join类似；不同的是，BNL算法的t1表是在内存操作，因此速度和性能上都要快很多。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。执行流程变成了：

• 扫描表 t1，顺序读取数据行放入 join_buffer 中，当 join_buffer 满了，继续第 2 步；
• 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
• 清空 join_buffer；
• 继续扫描表 t1，持续第1、2步骤，直到查询所有数据

这个流程体现出了这个算法名字中“Block”的由来，表示“分块去 join”。

因此，当Extra 字段里面出现“Block Nested Loop”字样时，是可以使用join操作的。

总结

能不能使用 join 语句？

• 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
• 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

所以在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

• 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
• 如果是 Block Nested-Loop Join 算法：在 join_buffer_size 足够大的时候，是一样的；在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以，这个问题的结论就是，总是应该使用小表做驱动表。

驱动表？

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

相关文章：

MySQL explain 洞悉SQL执行全过程