数据库表连接三大核心算法

数据库表连接三大核心算法

数据库执行JOIN底层只有3种标准连接算法：
嵌套循环连接 NLJ、排序合并连接 MergeJoin、哈希连接 HashJoin
MySQL、Oracle、PostgreSQL 全部通用。

一、整体对比

算法	适用场景	支持连接	核心开销
嵌套循环 NLJ	小表驱动大表、有索引	等值/非等值	索引查询开销
排序合并 MJ	两表数据量大、连接字段有序	等值/范围	排序开销
哈希连接 HJ	两大表无索引、大数据量	仅等值连接	内存哈希构建

---

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

原理

双层循环遍历

1. 选小表作为驱动表（外层循环）
2. 遍历驱动表每一行，取出关联键
3. 拿着关联键去被驱动表逐行匹配
4. 匹配成功拼接结果

优化版：索引嵌套循环 INLJ
被驱动表连接字段建立索引，内层遍历直接走索引查找，性能暴涨。

执行逻辑

for(驱动表每一行A){
    查询被驱动表中 匹配A关联键的数据
}

示例

表1：用户表 user（小表 100条）
表2：订单表 orders（大表 100万条，user_id 建索引）

select * from user u join orders o on u.id=o.user_id;

流程：

1. 遍历100条用户
2. 每条用户id走订单表索引快速匹配订单
3. 总共仅100次索引查询，效率极高

优点

1. 实现最简单
2. 有索引时性能最优
3. 占用内存极小
4. 支持 > < != 非等值连接

缺点

1. 无索引、大表驱动直接性能雪崩
2. 数据量越大越慢

---

2. 排序合并连接 Merge Join（MJ）

原理

先排序 + 双指针顺序匹配

1. 将两张表按照连接字段升序排序
2. 两个指针分别指向两表起始行
3. 相等则匹配合并；哪边值小哪边指针后移
4. 直到任意表遍历结束

执行逻辑

1. 表A按关联字段排序
2. 表B按关联字段排序
3. i、j双指针同向扫描匹配

示例

两张大表无索引，关联字段天然有序

select * from t1 join t2 on t1.no = t2.no;

流程：

1. 数据库自动对 no 字段排序
2. 从头开始依次比对，一次扫描完成连接

最佳场景

连接字段本身就是有序（主键、自增、有序索引），省去排序，速度极快。

优点

1. 大数据量稳定
2. 可处理范围连接 >= <=
3. 内存消耗低
4. 结果天然有序

缺点

1. 无序数据必须排序，排序成本极高
2. 小表场景2. 小表场景不如嵌套循环快

---

3. 哈希连接 Hash Join（HJ）

原理（大数据量JOIN最强算法）

分两阶段：构建阶段 + 探测阶段

1. 构建阶段（Build）
   选取较小表，以连接字段为key，在内存构建哈希散列表
2. 探测阶段（Probe）
   遍历大表，同样对连接字段求哈希，去哈希表匹配
3. 匹配成功直接拼接返回数据

执行逻辑

// 构建
小表数据 → 哈希函数 → 内存哈希表

// 探测
大表逐行 → 求哈希 → 匹配哈希表 → 输出结果

示例

两张千万级大表、无任何索引

select * from big_table_a a
join big_table_b b on a.id = b.id;

流程：

1. 取较小表构建内存哈希表
2. 全表扫描大表做哈希匹配
3. 全量数据快速关联

适用硬性规则

只支持等值连接 =
不支持 > < != between 等非等值连接

优点

1. 海量数据JOIN最快
2. 无需索引，无排序开销
3. 数据量越大优势越明显

缺点

1. 极度消耗内存
2. 内存不足会落盘，性能暴跌
3. 不支持非等值、范围连接
4. 结果无序

---

四、数据库优化器选择规则（必记）

1. 小表JOIN大表 + 有索引 → 优先 嵌套循环 NLJ
2. 两表数据量大、字段有序 → 优先 合并连接 MergeJoin
3. 两大表无索引、等值关联 → 强制 哈希连接 HashJoin
4. MySQL 8.0 正式支持 HashJoin，5.7及以下只有 NLJ+MergeJoin

---

五、三种算法时间复杂度极简

1. 嵌套循环：O(N*M) 有索引退化为 O(N logM)
2. 排序合并：O(NlogN + MlogM + N+M)
3. 哈希连接：平均 O(N+M) 最优大数据复杂度

---

六、实战区分（看执行计划）

• Using join buffer：嵌套循环无索引
• Using filesort + Using join buffer：排序合并连接
• Hash join：明确使用哈希连接