JOIN操作的执行方式详解

JOIN操作的执行方式详解

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

基本原理

Nested Loop Join是最直观和基础的JOIN执行方式，采用双重循环的方式进行连接操作。

执行过程

1. 选择一个表作为外表（驱动表）
2. 选择另一个表作为内表（被驱动表）
3. 对外表的每一行，在内表中查找匹配的行
4. 将匹配的行组合成结果集

算法伪代码

for each row R1 in outer_table:
    for each row R2 in inner_table:
        if R1.join_key = R2.join_key:
            output(R1, R2)

示例演示

-- 查询示例
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 假设users表有3条记录，orders表有4条记录
-- users表（外表）: u1(id=1), u2(id=2), u3(id=3)
-- orders表（内表）: o1(user_id=1), o2(user_id=2), o3(user_id=1), o4(user_id=3)

-- 执行过程：
-- 1. 取u1(id=1)，在orders中查找user_id=1的记录 → 找到o1, o3
-- 2. 取u2(id=2)，在orders中查找user_id=2的记录 → 找到o2
-- 3. 取u3(id=3)，在orders中查找user_id=3的记录 → 找到o4
-- 结果：(u1,o1), (u1,o3), (u2,o2), (u3,o4)

性能特点

• 时间复杂度：O(M × N)，其中M是外表行数，N是内表行数
• 适用场景：
  • 外表数据量较小
  • 内表有合适的索引
  • 返回结果集较小

优化策略

1. 选择小表作为外表
2. 在内表连接列上建立索引
3. 使用索引Nested Loop Join

-- 优化示例
-- 在orders表的user_id列上建立索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 这样内层循环的查找时间从O(N)降为O(log N)

2. Hash Join（哈希连接）

基本原理

Hash Join使用哈希表来优化JOIN操作，通过哈希函数将连接键值映射到哈希桶中，实现快速匹配。

执行过程

1. 构建阶段（Build Phase）：

   • 选择较小的表作为构建表
   • 读取构建表的所有行
   • 根据连接键计算哈希值
   • 将行存储在哈希表中

2. 探测阶段（Probe Phase）：

   • 读取探测表的每一行
   • 根据连接键计算哈希值
   • 在哈希表中查找匹配的行
   • 输出匹配的结果

算法伪代码

-- 构建阶段
hash_table = empty_hash_table
for each row R in build_table:
    hash_key = hash(R.join_key)
    hash_table[hash_key].add(R)

-- 探测阶段
for each row R in probe_table:
    hash_key = hash(R.join_key)
    for each row R2 in hash_table[hash_key]:
        if R.join_key = R2.join_key:
            output(R, R2)

示例演示

-- 使用相同的数据示例
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 构建阶段：选择较小的users表作为构建表
-- 创建哈希表：
-- hash(1) -> [u1]
-- hash(2) -> [u2]
-- hash(3) -> [u3]

-- 探测阶段：扫描orders表
-- o1(user_id=1): hash(1) -> 找到u1 -> 输出(u1, o1)
-- o2(user_id=2): hash(2) -> 找到u2 -> 输出(u2, o2)
-- o3(user_id=1): hash(1) -> 找到u1 -> 输出(u1, o3)
-- o4(user_id=3): hash(3) -> 找到u3 -> 输出(u3, o4)

性能特点

• 时间复杂度：O(M + N)，其中M和N分别是两个表的行数
• 空间复杂度：O(min(M, N))，需要存储较小表的哈希表
• 适用场景：
  • 两个表都比较大
  • 没有合适的索引
  • 等值连接（Hash Join通常只适用于等值连接）

优化变种

Grace Hash Join

当构建表太大无法完全放入内存时：

1. 将构建表和探测表都分割成多个部分
2. 对每个部分分别执行Hash Join
3. 合并所有部分的结果

Hybrid Hash Join

结合内存和磁盘操作：

1. 部分哈希表保留在内存中
2. 大部分数据溢出到磁盘
3. 减少磁盘I/O操作

3. Merge Join（排序合并连接）

基本原理

Merge Join要求两个表都按照连接键进行排序，然后通过合并排序后的结果来执行连接操作。

执行过程

1. 排序阶段：
   • 如果表未排序，则对两个表按照连接键进行排序
2. 合并阶段：
   • 使用两个指针分别扫描两个已排序的表
   • 比较当前行的连接键值
   • 根据比较结果移动指针并输出匹配的行

算法伪代码

-- 假设两个表都已经按照连接键排序
pointer1 = 0  -- 指向第一个表的指针
pointer2 = 0  -- 指向第二个表的指针

while pointer1 < table1.size and pointer2 < table2.size:
    key1 = table1[pointer1].join_key
    key2 = table2[pointer2].join_key
    
    if key1 == key2:
        output(table1[pointer1], table2[pointer2])
        pointer2++  -- 或者根据具体情况移动指针
    elif key1 < key2:
        pointer1++
    else:
        pointer2++

示例演示

-- 假设两个表已经按连接键排序
-- users表（按id排序）: u1(id=1), u2(id=2), u3(id=3)
-- orders表（按user_id排序）: o1(user_id=1), o3(user_id=1), o2(user_id=2), o4(user_id=3)

-- 执行过程：
-- 1. 比较u1.id(1)和o1.user_id(1) → 相等，输出(u1,o1)，移动orders指针
-- 2. 比较u1.id(1)和o3.user_id(1) → 相等，输出(u1,o3)，移动orders指针
-- 3. 比较u1.id(1)和o2.user_id(2) → 1<2，移动users指针
-- 4. 比较u2.id(2)和o2.user_id(2) → 相等，输出(u2,o2)，移动两个指针
-- 5. 比较u3.id(3)和o4.user_id(3) → 相等，输出(u3,o4)

处理重复值的完整算法

while pointer1 < table1.size and pointer2 < table2.size:
    key1 = table1[pointer1].join_key
    key2 = table2[pointer2].join_key
    
    if key1 == key2:
        -- 处理重复值
        list1 = get_all_rows_with_key(table1, pointer1, key1)
        list2 = get_all_rows_with_key(table2, pointer2, key2)
        
        -- 输出笛卡尔积
        for each row1 in list1:
            for each row2 in list2:
                output(row1, row2)
                
        -- 移动指针跳过所有重复值
        pointer1 += list1.size
        pointer2 += list2.size
    elif key1 < key2:
        pointer1++
    else:
        pointer2++

性能特点

• 时间复杂度：O(M log M + N log N)，主要是排序的开销
• 空间复杂度：O(M + N)，需要临时存储排序结果
• 适用场景：
  • 两个表都很大
  • 结果集也很大
  • 表已经按照连接键排序（或有索引）

4. 三种JOIN方式的比较

性能对比表

JOIN方式	时间复杂度	空间复杂度	适用场景	优点	缺点
Nested Loop	O(M×N)	O(1)	小表JOIN或有索引	简单直观，适合小数据集	大数据集性能差
Hash Join	O(M+N)	O(min(M,N))	大表等值JOIN	性能好，线性时间复杂度	只适用于等值连接，需要内存
Merge Join	O(M log M + N log N)	O(M+N)	已排序的大表	适合大数据集，I/O友好	需要预排序

选择策略

数据库优化器的选择依据

1. 表大小：小表倾向于Nested Loop，大表倾向于Hash Join或Merge Join
2. 索引情况：有合适索引时优先Nested Loop
3. 连接类型：等值连接可使用Hash Join，非等值连接通常用Nested Loop
4. 排序情况：已排序的表适合Merge Join
5. 内存资源：Hash Join需要足够的内存

实际示例

-- 不同场景下的优化器选择

-- 场景1：小表JOIN（users 100行，orders 1000行）
-- 优化器可能选择：Nested Loop Join

-- 场景2：大表等值JOIN（两个表都100万行，无索引）
-- 优化器可能选择：Hash Join

-- 场景3：大表JOIN且已排序（两个表都按连接键排序）
-- 优化器可能选择：Merge Join

-- 场景4：范围连接（orders.amount BETWEEN users.min_amount AND users.max_amount）
-- 优化器可能选择：Nested Loop Join

5. 数据库中的实现

MySQL中的JOIN实现

• MySQL 8.0之前主要使用Nested Loop Join
• MySQL 8.0开始支持Hash Join（针对等值连接）
• BKA (Block Nested-Loop) Join优化

PostgreSQL中的JOIN实现

• 支持所有三种JOIN方式
• 优化器根据成本模型选择最优方式
• 支持多种Hash Join变种

Oracle中的JOIN实现

• 全面支持三种JOIN方式
• 复杂的成本计算模型
• 支持多种优化变种

SQL Server中的JOIN实现

• 支持所有三种JOIN方式
• Adaptive Join（SQL Server 2017+）：运行时动态选择JOIN方式

通过理解这三种JOIN执行方式，可以更好地优化数据库查询，选择合适的数据结构和算法来提升查询性能。