祥哥的技术博客

主题

📅 2025年2月28日 🏷️ MySQL ⏱️ 12 分钟阅读 mysqljoinperformance

JOIN 优化：NLJ vs BNL vs Hash Join（MySQL 8.0+） ​

提出问题 ​

MySQL 执行 JOIN 查询的性能差异可以相差几个数量级——同样的 SQL，走对算法毫秒级返回，走错算法让 CPU 跑满、磁盘 IO 打爆。很多 DBA 和开发只知道"小表驱动大表"，但并不知道 MySQL 底层到底用了哪种 JOIN 算法、各自的前提条件是什么、为什么 MySQL 8.0 引入了 Hash Join 之后有些场景反而变慢了。

一个真实案例：2024 年某电商大促期间，一笔订单查询接口在 MySQL 5.7 上跑出了 12 秒的响应时间。EXPLAIN 显示被驱动表（order_detail，2000 万行）没有索引，走了 BNL，每次扫描 2000 万行，驱动表（orders，8 万行）的 join_buffer_size 仅 256KB，一次只能缓存约 3000 行，导致被驱动表被全表扫描了 27 次。迁移到 MySQL 8.0.21 后，Hash Join 自动启用，查询降到 1.2 秒，提升 10 倍。但随后 DBA 为 order_detail 补上了索引，NLJ 接手，查询进一步降到 80ms——这才是最终解法。

面试中，P7 级别要求能说出三种 JOIN 算法的名称和适用场景，P8 级别则要求能根据 EXPLAIN 输出判断当前走了什么算法，并给出具体的调优方向。生产上，一次 JOIN 线上事故往往是因为被驱动表没加索引，导致 BNL 暴力扫描全表，直接拖垮数据库。

分析问题 ​

三种 JOIN 算法的核心原理 ​

**Nested Loop Join（NLJ）**是最经典的 JOIN 算法，也是 MySQL 5.7 及以下版本的默认首选。流程如下：

驱动表（小表）  被驱动表（大表，有索引）
  行1 ──────────→ 索引查找（log₂N）
  行2 ──────────→ 索引查找（log₂N）
  行3 ──────────→ 索引查找（log₂N）
  ...
  行R ──────────→ 索引查找（log₂N）

假设驱动表 1000 行，被驱动表 100 万行且有索引，复杂度是 1000 × log₂(1000000) ≈ 1000 × 20 = 20000 次索引查询。如果被驱动表没有索引，NLJ 会退化为 1000 × 1000000 = 10 亿 次全表扫描，直接不可用——这就是为什么 DBA 反复强调"JOIN 的关联字段必须加索引"。

**Block Nested Loop Join（BNL）**是 MySQL 5.7 及之前版本应对"没索引"场景的补救方案。流程：

join_buffer_size = 256KB（默认）
┌──────────────────────────────┐
│ 驱动表行1 | 行2 | ... | 行3000 │  ← 缓存一批
└──────────────────────────────┘
         ↓ 批量传给被驱动表
被驱动表全表扫描一次，匹配这批数据
         ↓
┌──────────────────────────────┐
│ 驱动表行3001 | ... | 行6000   │  ← 下一批
└──────────────────────────────┘
         ↓ 再扫一次被驱动表
...

虽然复杂度仍然是 O(R × S)，但通过块缓存大幅减少了被驱动表的扫描次数。例如 256KB 缓存能装约 3000 行（假设行宽 80 字节），驱动表 10000 行只需 4 次扫描被驱动表，而不是 10000 次。但被驱动表扫描次数 = ceil(R / (join_buffer_size / row_size))，驱动表行数越多，扫描次数越多，拖垮 IO。

Hash Join是 MySQL 8.0.18 引入的重磅改进。它在无索引的 JOIN 条件下，用 Hash Table 替代 BNL。流程：

阶段1：构建（Build Phase）
驱动表（选择较小者）逐行读取
  ┌─────────────────────┐
  │ Hash Table (内存)    │
  │ key(user_id) │ value │
  │ 42          │ row1  │
  │ 99          │ row2  │
  │ ...         │ ...   │
  └─────────────────────┘

阶段2：探测（Probe Phase）
被驱动表逐行扫描
  每行算 hash(user_id)
  去 Hash Table 中 O(1) 查找
  找到 → 输出匹配行
  没找到 → 跳过

复杂度从 BNL 的 O(R × S) 降到 O(R + S)，在无索引的大表 JOIN 场景下性能提升可达 10-100 倍。

sql
-- 示例：两张没有索引的表做 JOIN
-- MySQL 8.0 会走 Hash Join，5.7 走 BNL
SELECT /*+ NO_HASH_JOIN(o) */  -- 用 hint 可以强制不走 Hash Join
  u.name, o.order_id, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01';

Hash Join 的触发条件与内存管理 ​

MySQL 8.0.20+ 已彻底移除 BNL，所有无索引的 JOIN 都走 Hash Join。但 Hash Join 不是免费的——它需要内存来构建 Hash Table。

sql
-- 查看 Hash Join 是否启用
SHOW VARIABLES LIKE 'optimizer_switch';
-- 输出中找 hash_join=on（默认启用）

-- 控制 Hash Table 内存
SHOW VARIABLES LIKE 'join_buffer_size';
-- 默认 262144 字节（256KB），建议根据驱动表大小调整

内存控制是关键。如果驱动表数据量超过 join_buffer_size，MySQL 会使用磁盘上的 Hash Join（On-disk Hash Join）：将驱动表分块写入磁盘临时文件，多次加载进行 Hash 匹配。具体流程：

驱动表 500MB，join_buffer_size = 64MB
  → 分成 8 块（chunk），每块写入临时文件
  → 逐块加载到内存构建 Hash Table
  → 每加载一块，扫描一次被驱动表进行探测
  → 被驱动表被扫描 8 次

性能急剧下降 10 倍以上。可以通过 EXPLAIN 确认是否走了 Hash Join：

sql
EXPLAIN FORMAT=TREE
SELECT u.name, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 输出示例：
-- -> Inner hash join (u.id = o.user_id)  (cost=1.2 rows=1000)
--     -> Table scan on u  (cost=0.35 rows=100)
--     -> Hash
--         -> Table scan on o  (cost=10.5 rows=10000)

FORMAT=TREE 是 MySQL 8.0 新增的 EXPLAIN 格式，能清晰展示是 Hash Join 还是 NLJ。看到 Inner hash join 即确认走了 Hash Join；看到 Nested loop inner join 是 NLJ。

注意：EXPLAIN 的传统格式（行格式）在 type 列显示 ALL 或 index 并不能区分是 Hash Join 还是 BNL。5.7 的在线文档里很多 BNL 调优文章在 8.0 已经不适用了——因为 8.0.20+ 根本没有 BNL 了。

NLJ 什么时候比 Hash Join 更快？ ​

这是一个反直觉但非常重要的问题。如果被驱动表的 JOIN 字段有索引，NLJ 的复杂度是 O(R × logS)，而 Hash Join 是 O(R + S) 但需要全表扫描被驱动表。当 R 很小时（比如驱动表只有 10 行），NLJ 只需要 10 次索引查找，而 Hash Join 需要构建 Hash Table（扫描驱动表 10 行）并扫描被驱动表全表（假设 100 万行）。此时 NLJ 远快于 Hash Join。

实测数据对比（模拟 100 万行 orders 表，user_id 有索引）：

场景驱动表行数算法耗时
单用户查询1 行NLJ（走索引）0.3ms
单用户查询1 行Hash Join（强制）120ms（全表扫描 orders）
批量查询100 行NLJ（走索引）2ms
批量查询100 行Hash Join（强制）125ms
无索引查询100 行NLJ（无索引）5000ms+
无索引查询100 行Hash Join130ms
10 万行驱动表 JOIN 100 万行被驱动表100000 行NLJ（有索引）约 200ms
10 万行驱动表 JOIN 100 万行被驱动表100000 行Hash Join（无索引）约 800ms（内存够时）

关键结论：被驱动表有索引时，NLJ 在几乎所有场景下都优于 Hash Join。Hash Join 的真正价值在于"被驱动表无法加索引的特殊场景"。

sql
-- 被驱动表 orders 有索引 idx_user_id 时，NLJ 更优
EXPLAIN
SELECT u.name, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 42;  -- 驱动表只有 1 行

-- Extra 列显示 Using index 说明覆盖索引，无 Using join buffer 说明不是 Hash Join

调优结论：加索引仍然是最优的 JOIN 优化手段。Hash Join 是"兜底方案"，不是"替代 NLJ 的终极方案"。

小表驱动大表的 EXPLAIN 验证 ​

sql
-- 看 EXPLAIN 中 id 相同的行，第一行是驱动表
EXPLAIN
SELECT *
FROM big_table b
JOIN small_table s ON b.key = s.key;
-- 第一行出现 small_table 说明优化器选择了小表驱动大表
-- 如果第一行是 big_table，说明优化器选错了，可能需要 STRAIGHT_JOIN 强制指定

-- 强制指定驱动表（慎用，只在确认优化器选错时用）
SELECT STRAIGHT_JOIN
  s.*, b.*
FROM small_table s
JOIN big_table b ON s.key = b.key;

特殊场景：无法加索引的 JOIN ​

什么场景下不能给 JOIN 字段加索引？

  1. 中间表/派生表：子查询产生的临时表，MySQL 无法给临时表加索引。
  2. 多表关联的中间结果：三层 JOIN 时，中间步骤的 JOIN 结果作为输入，关联字段没有索引。
  3. ETL 数据比对：两张数据源表做全量差异比对，JOIN 字段是散列值（如 MD5），加索引的空间成本太高。

一个真实案例：某数据中台做 T+1 对账，两张大表（各 500 万行）根据订单号 MD5 做 JOIN 对比差异。订单号没有索引，MySQL 5.7 走 BNL 跑了 47 分钟，超时断开。迁移到 MySQL 8.0.20 后 Hash Join 自动接手，耗时降到 3 分 20 秒。DBA 将 join_buffer_size 从 256KB 调整到 32MB（驱动表约 500MB，分 16 块），耗时进一步降到 45 秒。最终方案是改为 Spark 批处理，5 分钟完成全量对比——MySQL 不适合这种场景。

sql
-- 临时表 JOIN 场景，Hash Join 是最佳选择
EXPLAIN FORMAT=TREE
WITH tmp AS (
  SELECT order_id, SUM(amount) as total
  FROM order_detail
  GROUP BY order_id
)
SELECT o.order_no, tmp.total
FROM orders o
JOIN tmp ON o.id = tmp.order_id;
-- 这里 tmp 是派生表，没有索引，一定走 Hash Join

总结 ​

算法复杂度适用场景前提条件MySQL 版本
NLJO(R × logS)驱动表小，被驱动表有索引被驱动表 JOIN 字段有索引所有版本
BNLO(R × S)无索引时的兜底（已淘汰）无索引，MySQL 5.7 及以下5.0-8.0.19
Hash JoinO(R + S)无索引的大表 JOIN内存足够装 Hash Table8.0.18+

生产避坑要点：

  • 加索引仍然是最优策略，Hash Join 只是兜底，不是万能的。不要因为 MySQL 8.0 有 Hash Join 就放松索引规范。
  • join_buffer_size 不是越大越好：过大可能导致内存不足触发 OOM，甚至影响 Buffer Pool。建议驱动表估算后设为合理值（如 1-4MB），不要超过 innodb_buffer_pool_size 的 10%。
  • 用 EXPLAIN FORMAT=TREE 确认 JOIN 算法，别只看 type 和 Extra。传统格式的 Using join buffer (Block Nested Loop) 在 8.0.20+ 中不会出现，因为 BNL 已被移除。
  • 大表 JOIN 优先考虑拆成业务层多次查询：先查小表结果，再用 IN 查大表，比任何 JOIN 算法都稳定。但注意 IN 列表不要超过 1000 个值，否则可能触发 SQL 解析性能问题。
  • Hash Join 的 Build Phase 选择驱动表由优化器决定，通常选较小的表。如果开发确定某张表更小，可以用 /*+ JOIN_FIXED_ORDER() */ 或 STRAIGHT_JOIN 强制指定驱动表顺序，但只在 EXPLAIN 确认优化器选错时使用。

面试话术示例："MySQL 8.0 的 Hash Join 让无索引 JOIN 场景的性能提升了 10 倍以上，但它不是让你不加索引的借口。有索引的等值 JOIN 走 NLJ 永远是最优选择。生产上我的调优顺序是：先根据 EXPLAIN 确认算法，如果是 Hash Join 或 BNL，优先考虑给被驱动表加索引；只有无法加索引（如中间表、临时表）时才考虑调整 join_buffer_size 或改写 SQL 为 IN 子查询。如果数据量超过千万级别，我会建议业务层拆分查询，或者迁移到 ClickHouse/TiDB 等 OLAP 友好的存储。另外，MySQL 8.0.20+ 已经彻底移除了 BNL，如果面试官还在问 BNL 的调优，说明他可能还在用 5.7。"

面试追问准备：

  • Q：Hash Join 在非等值 JOIN（如 >、<、BETWEEN）下能走吗？A：不能。Hash Join 只支持等值 JOIN（=）。非等值 JOIN 仍然走 NLJ。如果被驱动表没有索引且是范围 JOIN，性能会非常差——这就是为什么 MySQL 8.0 的 Hash Join 不是万能药。
  • Q：join_buffer_size 是 session 级还是 global 级？A：两者都是。SET SESSION join_buffer_size = 1048576 只影响当前连接；SET GLOBAL join_buffer_size = 1048576 影响后续所有连接。生产上建议 session 级按需调整，不要全局改大，否则 200 个并发连接 × 4MB = 800MB 内存直接没了。
  • Q：Hash Join 在 LEFT JOIN 和 INNER JOIN 下的行为一样吗？A：基本相同，但 LEFT JOIN 的驱动表固定为左表，优化器不能自由选择构建 Hash Table 的驱动表；INNER JOIN 优化器可以选择较小的表作为驱动表。

参考：MySQL 8.0 Hash Join 官方文档（https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html）；《高性能 MySQL》第 5 章 JOIN 优化；MySQL 8.0.20 变更日志（BNL 移除）

手撕 → 框架 → 生产化，一步步把 AI Agent 工程化搞透。

Copyright © 2026 祥哥