MySQL JOIN 优化：从 NLJ 到 BKA + 6 种索引失效场景

一句话结论（30s）

JOIN 性能的核心是被驱动表 JOIN 列是否有索引，因为 Simple NLJ 会让内表被全表扫描驱动表行数次（1000×10000=1000 万次），而 Index NLJ 把每次匹配降到 O(log n) 的 B+ 树查找。关键设计是四种算法的演进：NLJ → Index NLJ → BNL（Join Buffer）→ BKA（MRR 批量回表），把随机 IO 逐步转成顺序 IO。权衡在于：小表驱动大表 + JOIN 列加索引是前提，被驱动表无索引时只能退化为 BNL 的缓冲区批量比较。

核心原理（2min）

Simple NLJ 对驱动表每行都全扫被驱动表，代价是行数乘积；Index NLJ 要求被驱动表 JOIN 列有索引，每行用 O(log n) 定位，快约 700 倍，原则是”小表驱动大表”。被驱动表无索引时用 BNL，把驱动表一批行读进 256KB Join Buffer，再扫描一次内表与 Buffer 中所有行比较，减少内表全扫次数。BKA 进一步把 Index NLJ 的随机回表批量化：批量查索引拿到 rowid 列表后按 rowid 排序、用 MRR 顺序回表，在 HDD 上效果明显。此外 6 种索引失效场景（函数运算、隐式类型转换、前导通配符、OR 非全索引、联合索引非最左前缀、不等于）会让优化器放弃索引转向全表扫描。

底层深入（5-10min）

JOIN 四种算法

1. Simple NLJ（Nested-Loop Join）

SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;
-- t1 1000 行，t2 10000 行

执行过程：
for each row in t1 (1000 rows):
    for each row in t2 (10000 rows):
        if t1.id == t2.t1_id → 返回

t2 全表扫描 1000 次 → 1000 × 10000 = 1000 万次扫描。最原始、最慢。

💭 想一想：Simple NLJ 的代价为什么是”两表行数相乘”？——因为外层每取一行，内层就要从头到尾扫一遍内表；外层 1000 行 × 内层全扫 10000 行 = 1000 万次比较。JOIN 慢的根源就是”内表被反复全扫”，一切优化都在想办法减少这个内层全扫的次数。

2. Index NLJ

前提：被驱动表（t2）的 JOIN 列有索引。

for each row in t1:
    index lookup t2.t1_id = t1.id → 直接定位到匹配行

t2 不扫描全表 → 每次 O(log n) 的 B+ 树查找。1000 行驱动表 × O(log 10000) ≈ 1000 × 14 ≈ 14000 次操作。比 Simple NLJ 快 700 倍。

关键优化原则：JOIN 列加索引，小表驱动大表。

💭 想一想：为什么要”小表驱动大表”？——因为内层循环的轮数由外层（驱动表）决定：小表做驱动表，外层就少跑几轮；再配合内表索引，每轮只做 O(log n) 定位。反过来大表驱动小表，外层轮数暴涨，即使内表有索引也扛不住。所以”谁当外层”和”内表有没有索引”是同一枚硬币的两面。

3. BNL（Block Nested-Loop）

无索引时（Simple NLJ 太慢），用 Join Buffer：

1. 从 t1 读一批行到 Join Buffer（默认 256KB）
2. 扫描 t2 一次，与 Buffer 中所有行比较
3. 清空 Buffer，重复直到 t1 读取完毕

t2 只扫描 t1 行数 / Buffer 容量 次，大幅减少内表全扫次数。

4. BKA（Batch Key Access）

Index NLJ 的回表是随机 IO → BKA 批量化回表：

1. 从 t1 读一批行 → Join Buffer
2. 批量去 t2 索引查 → 获得需要回表的 rowid 列表
3. 对 rowid 排序 → 顺序回表（MRR 多范围读）

将随机回表变为顺序回表（按 rowid 排序后回表 = 近似顺序 IO），在 HDD 上效果明显。

💭 想一想：BKA 比 Index NLJ 到底多做了什么？——Index NLJ 每定位到一行就立刻回表，回表顺序随二级索引走、是散乱的随机 IO；BKA 先攒一批 rowid、按主键排序后再批量回表，把随机 IO 变成顺序 IO。它的收益在 HDD 上最明显，因为机械盘最怕随机寻道。

6 种索引失效场景

章末提问

追问 1：JOIN 性能的关键在哪？为什么被驱动表的 JOIN 列加索引最重要？

回答思路：结论先行——关键在于减少”内表被反复全扫”的次数，被驱动表 JOIN 列有索引是根本前提。因为 Simple NLJ 会让内表被全扫驱动表行数次（代价是行数乘积）；被驱动表 JOIN 列建了索引后，每次匹配退化为 O(log n) 的 B+ 树定位，全扫变定位，量级上快了几百倍。

追问 2：为什么强调”小表驱动大表”？反过来会怎样？

回答思路：结论先行——因为内层循环轮数由驱动表行数决定，反过来会让外层轮数暴涨。因为驱动表每行都要去内表查一次，小表做驱动表意味着外层只跑小表行数那么多轮；若大表驱动小表，外层轮数变成大表行数，即使内表有索引，整体 IO 也会被放大，性能明显更差。

追问 3：BNL 和 BKA 分别解决什么 IO 问题？区别在哪？

回答思路：结论先行——BNL 解决”内表无索引时全扫次数太多”，BKA 解决”有索引但回表是随机 IO”。因为 BNL 用 Join Buffer 攒一批驱动表行、只扫一次内表与整批比较，减少内表全扫次数；BKA 则先把要回表的 rowid 按主键排序、用 MRR 批量顺序回表，把随机 IO 转成顺序 IO。一个是省”扫描轮数”，一个是省”回表寻道”。