SQL｜JOIN算法

原创于 2025-07-04 14:20:55 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #算法 #数据库

SQL 专栏收录该内容

4 篇文章

订阅专栏

文章目录

前言
小结

前言

关系型数据库真正执行 JOIN 时，优化器通常只能在若干“物理联接算法(physical join algorithm)”里做取舍。主流实现几乎都支持下列三大类，并在此基础上再细分若干变体：

嵌套循环联接（Nested Loop Join，NLJ）
哈希联接（Hash Join，HJ）
排序-归并联接（Sort-Merge Join，SMJ）

下面以最常见的三大物理 JOIN 算法——嵌套循环（Nested Loop）、哈希（Hash Join）、排序-归并（Sort-Merge Join）为核心，给出典型的业务场景、SQL 语句，以及执行器的实际操作过程。为便于理解，示例使用两张简单的表：

Customers(cust_id PK, name …)≈ 1 万行
Orders (order_id PK, cust_id FK, amount …)≈ 100 万行

并假设我们可以在 PostgreSQL / MySQL / Oracle 中用 EXPLAIN 查看执行计划。

1. 嵌套循环联接（Nested Loop Join，NLJ）

场景：驱动表很小，内表在连接列上有索引。

SQL
SELECT o.order_id, o.amount
FROM Customers c
JOIN Orders o ON o.cust_id = c.cust_id
WHERE c.cust_id = 123; – 条件把 Customers 缩小到 1 行

执行步骤
① 扫描 Customers，受 WHERE 过滤，只返回 1 行 → 作为 outer table。
② 对这 1 行取到 cust_id=123，用其去 Orders 的索引(customer_id_idx) 进行一次快速查找 → inner table。
③ 找到匹配行立刻输出。

成本估算 ≈ 1（外表行数） × 1 次索引访问 ~= O(|Outer|)。
为什么合适：外表很小且内表有索引，单次查找成本低。

变体举例
• 如果 WHERE 没有过滤，Customers 仍有 1 万行，但 Orders 上有索引，优化器可能仍选 NLJ；成本变为 1 万 × 1 次索引访问。
• 若 Orders 上没有索引，则会发生 1 万次全表扫描 → 成本跳为 1 万 × 100 万，不可接受，优化器通常就不会再选 NLJ。

2. 哈希联接（Hash Join，HJ）

场景：两个表都很大，连接为等值 = ，内存充足且较小表没有合适索引。

SQL
SELECT o.order_id, c.name, o.amount
FROM Orders o – 100 万行，较大
JOIN Customers c ON c.cust_id = o.cust_id; – 1 万行，较小

执行步骤
① 选择较小的 Customers 做 Build 端：顺序扫描 1 万行，把 cust_id 做哈希并存入内存哈希表。
② Probe 阶段顺序扫描 Orders 的 100 万行，对每一行按 cust_id 查刚才那张哈希表，命中则输出。

成本估算 ≈ 1 次顺序读 Customers + 1 次顺序读 Orders ≈ |Build| + |Probe|。
• 若内存不足以容纳 1 万记录，数据库还可把两个输入先 hash 分区到临时文件（Grace Hash Join），然后对子分区各做一次内存哈希。

为什么合适：
• 大量行且等值连接；
• 相比建索引再走 NLJ 更快；
• 顺序 I/O 可并行、可“流式”输出。

3. 排序-归并联接（Sort-Merge Join，SMJ）

场景 A：连接列需要范围比较（≥ ≤ BETWEEN），哈希联接无法处理。
场景 B：查询本身还要 ORDER BY 连接列，排序一次即可复用。

SQL
SELECT o.order_id, c.name
FROM Orders o – 无索引
JOIN Customers c ON o.cust_id = c.cust_id
ORDER BY c.cust_id, o.order_id; – 结果需排序输出

执行步骤
① 如果两边已有相同的 B-tree 索引且可走“索引顺序扫描”，数据库可以跳过显式排序直接 Merge。
② 若没有索引，则：
• 先把 Customers 按 cust_id 排序（外部排序，写临时文件）。
• 再把 Orders 按 cust_id 排序。
• 接着像归并排序那样同时流扫两股有序数据流，ID 相等就输出；因为数据天然已排序，可直接满足 ORDER BY。

如何在数据库里确定算法？

PostgreSQL:
EXPLAIN ANALYZE ;
会看到 “Nested Loop / Hash Join / Merge Join” 节点。

MySQL 8:
EXPLAIN FORMAT=JSON ;
NLJ：Nested Loop → “nested_loop” 数组；
HJ：block_nested_loop (8.0 的 Batched NLJ) 或 hash_join (8.0.18 之后引入)；
SMJ：sort_merge_join（8.0.22 之后）。

Oracle:
EXPLAIN PLAN 或 AUTOTRACE：
NESTED LOOPS / HASH JOIN / MERGE JOIN 关键字。
可用 Hint 强制：/*+ USE_NL(o c) /、/+ USE_HASH(o c) /、/+ USE_MERGE(o c) */。