MySQL 处理表连接（Join）时的四种核心算法

为了让你一目了然，我们可以把它们分为两组：

1. 有索引时的强者：INLJ -> 进化为 -> BKA
2. 无索引时的救星：BNLJ -> 进化为 -> Hash Join

下面是详细的对比和演进逻辑：

---

第一组：当被驱动表（大表）有索引时

INLJ (Index Nested-Loop Join)：MySQL 最基础、最常用的 Join 方式；

机制：“点对点单兵作战”

1. 从驱动表（t1）拿1行。
2. 利用被驱动表（t2）的索引，去树上搜索匹配的行。
3. 如果是非主键索引，可能还需要回表查主键。
4. 重复上述步骤。

缺点：如果 t1 很大，意味着要进行很多次“随机 IO”访问 t2。如果是机械硬盘（HDD），磁头会跳来跳去，效率受限。

BKA (Batched Key Access)：对 INLJ 的物理层面优化（需要开启 mrr=on）。

机制：“集结部队，批量进攻”

1. 从驱动表（t1）拿一批数据（Batch）。
2. 把这批数据的 Key 提取出来，交给MRR (Multi-Range Read)接口。
3. MRR 会在内存里把这些 Key 按照被驱动表（t2）的RowID（物理位置）进行排序。
4. 按照排序后的顺序，顺序地去读取 t2 的数据。

---

第二组：当被驱动表（大表）没有索引时

BNLJ (Block Nested-Loop Join)：在 MySQL 8.0.18 之前，如果 Join 字段没索引，只能用它。

机制：“死记硬背”。

1. 把驱动表（t1）的数据加载到内存的 join_buffer 里。
2. 扫描被驱动表（t2），每读一行 t2，就拿着去跟内存里 t1 的所有数据做对比（双重循环）。

缺点：虽然省了磁盘 IO，但CPU 消耗极大。内存里要进行M * N次判断。

Hash Join：MySQL 8.0.18+ 引入，全面取代 BNLJ。这是处理大表无索引 Join 的工业界标准算法（Oracle, PG, SQL Server 早就有了）。

机制：“查字典”。

1. Build 阶段：把驱动表（t1）读入内存，构建一个 Hash Map（字典）。
2. Probe 阶段：扫描被驱动表（t2），算出 Join 字段的哈希值，直接去 Hash Map 里查（O(1)时间复杂度）。

优势：

• BNLJ 的复杂度是O(N * M)。
• Hash Join 的复杂度是O(N + M)。

适用场景：大数据量分析、报表类查询、或者无论如何都加不上索引的情况。

总结：

算法	依赖条件	核心逻辑	IO 特征	推荐指数
INLJ	有索引	查 B+ 树	随机 IO	⭐⭐⭐⭐⭐ (OLTP 首选)
BKA	有索引 + MRR	排序后查表	顺序 IO	⭐⭐⭐⭐ (IO 密集型优化)
BNLJ	无索引 (旧)	内存循环对比	扫描 t2 一次	⭐ (尽量避免)
Hash Join	无索引 (新)	哈希表查找	扫描 t2 一次	⭐⭐⭐ (OLAP/报表首选)