拨开慢SQL迷雾｜连载03：一文掌控多表JOIN策略，从选型到Hint强制绑定

原创神经兮兮哇 2026-09-18

257

导语

多表关联是 SQL 性能瓶颈的重灾区。磐维（PanWeiDB/openGauss）优化器会自动选择连接方式，但在数据倾斜、统计信息不准、或大表关联未走索引时，可能做出"错误"选择。本文详解 NestLoop、HashJoin、MergeJoin 三种连接方式的底层原理与适用场景，并通过 TPCH Q3 优化实战（23 秒 → 11 秒）展示如何通过 Hint 强制绑定执行计划，让优化器"听话"。

一、三种连接方式原理与适用场景

数据库在执行多表关联时，本质上只有三种物理连接算法。理解它们的原理，是判断执行计划优劣的前提。

连接方式	核心原理	适用场景	不适用场景
Nested Loop	遍历外表每一行，对内表进行匹配扫描	外表小、内表有索引、返回行数少	两表都很大且无索引
Hash Join	对小表（驱动表）建哈希表，大表逐行探测	两表都较大、等值关联、内存充足	非等值关联、内存不足
Merge Join	先对两表按关联键排序，再归并扫描	两表已排序、或需要有序输出	未排序且数据量极大（排序成本高）

1.1 Nested Loop（嵌套循环连接）

原理：选取一个表作为外表（Outer）/ 驱动表（Driving），另一个作为内表（Inner）/被驱动表（Driven）。对外表的每一行，都去内表中查找匹配的行。如果内表上有索引，每次查找可以极快完成。

性能关键：驱动表返回的行数决定了被驱动表被查询的次数。

适用场景：

外表经过过滤后返回很少的行数
内表在关联列上有高效索引
关联结果为小结果集

文档案例：supplier 与 partsupp 的半连接

EXPLAIN ANALYZE
SELECT * FROM supplier
WHERE s_comment LIKE '%Customer%Complaints%'
  AND s_suppkey IN (SELECT ps_suppkey FROM partsupp);

执行计划：

 Nested Loop Semi Join  (cost=0.00..2027.26 rows=5 width=144) (actual time=0.351..18.784 rows=26 loops=1)
+   ->  Seq Scan on supplier  (cost=0.00..2020.00 rows=5 width=144) (actual time=0.337..18.481 rows=26 loops=1)
         Filter: ((s_comment)::text ~~ '%Customer%Complaints%'::text)
         Rows Removed by Filter: 49974
-   ->  Index Only Scan using partsupp_n2 on partsupp  (cost=0.00..5.85 rows=80 width=4) (actual time=0.213..0.213 rows=26 loops=26)
         Index Cond: (ps_suppkey = supplier.s_suppkey)

分析：

驱动表 supplier 过滤后仅 26 行
被驱动表 partsupp 走索引，每次循环 0.2 ms
总耗时仅 18.7 ms

1.2 Hash Join（哈希连接）

原理：选择较小的表（驱动表）在内存中构建哈希表（Build 阶段），然后扫描较大的表（被驱动表），用关联列的值到哈希表中探测匹配（probe阶段）。

性能关键：驱动表（Build表）必须足够小，能放入内存（Work Mem），否则会落盘（Batches）。

适用场景：

两表都较大，无法通过索引高效过滤
等值关联（=），非等值关联无法使用
内存足够容纳哈希表

案例：t_tables 与 t_objects 的关联

EXPLAIN ANALYZE
SELECT /*+ hashjoin(t o) */ *
FROM t_tables t
INNER JOIN t_objects o ON o.owner = t.owner AND o.object_name = t.table_name;

执行计划：

Hash Join  (cost=202.47..3856.25 rows=864 width=544)
           (actual time=2.144..37.811 rows=3062 loops=1)
   Hash Cond: (((o.owner)::text = (t.owner)::text) AND ...)
-   ->  Seq Scan on t_objects o
   ->  Hash
+         ->  Seq Scan on t_tables t

分析：

t_tables 仅 2859 行，作为驱动表构建哈希表
t_objects 86657 行，作为被驱动表
内存中完成匹配，总耗时 37.8 ms

1.3 Merge Join（归并连接）

原理：先对两表按关联列排序，然后像"拉链"一样同时扫描两张表，匹配相同键值的行。

适用场景：

两表已经按关联键有序（如索引扫描结果）
查询本身需要有序输出，排序成本可被复用
大数据量的等值关联

案例：partsupp 与 part 的 Merge Join

EXPLAIN ANALYZE
SELECT /*+ mergejoin(partsupp part) */ p_brand, p_type, p_size, ps_suppkey
FROM partsupp, part
WHERE p_partkey = ps_partkey
  AND p_brand <> 'Brand#45'
  AND p_type NOT LIKE 'MEDIUM POLISHED%'
  AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9);

执行计划：

Merge Join  (cost=10000063188.44..100001947919.45 rows=1696769 width=40)
            (actual time=440.042..1176.768 rows=592984 loops=1)
   Merge Cond: (part.p_partkey = partsupp.ps_partkey)
+   ->  Sort
         Sort Key: part.p_partkey
         Sort Method: quicksort Memory: 20043kB
-   ->  Index Only Scan using partsupp_pkey on partsupp

分析：

partsupp 通过主键索引已有序，无需额外排序
part 需要排序，消耗 20043 KB 内存
总耗时 1207 ms，相比 Hash Join（1506 ms）在此场景下略快

二、通过 Hint 强制指定连接方式

当优化器因统计信息不准选择了错误的连接方式时，可以通过 Hint 强制干预。磐维支持以下语法：

/*+ [no] nestloop|hashjoin|mergejoin(table_list) */

2.1 案例：强制 NestLoop 优化 TPCH Q3

业务 SQL：

select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue,
       o_orderdate, o_shippriority
from customer, orders, lineitem
where c_mktsegment = 'BUILDING' and c_custkey = o_custkey
  and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15'
  and l_shipdate > date '1995-03-15'
group by l_orderkey, o_orderdate, o_shippriority
order by revenue desc, o_orderdate 
limit 10;

优化前执行计划（Hash Join）

Hash Join (rows=150615)
  ->  Seq Scan on lineitem (rows=16168962)
        Filter: (l_shipdate > '1995-03-15 00:00:00'::oradate)
        Rows Removed by Filter: 13830833
  ->  Hash Join (customer 与 orders，返回 732138 行)

问题分析：

lineitem 表（6000 万行）以 Seq Scan 方式参与 Hash Join，扫描了 1600 万行数据
虽然最终只产生 15 万行有效关联结果，但 Hash Join 必须先扫描、过滤、再构建/探测哈希表
大表 lineitem 没有任何索引被利用，是性能瓶颈所在

优化方案

思路：lineitem 表通过 l_orderkey 和 l_shipdate 过滤后，如果能利用索引直接定位到少量数据，再以 NestLoop 方式逐行关联，可以避免全表扫描 1600 万行的开销。

创建复合索引：

CREATE INDEX LINEITEM_N3 ON LINEITEM(L_ORDERKEY, L_SHIPDATE);

使用 Hint 强制 lineitem 走索引和 NestLoop：

select /*+ indexscan(lineitem lineitem_n3) */
       l_orderkey, sum(...) ...

优化后执行计划（Nested Loop）

Nested Loop
   ->  Hash Join  (customer 与 orders，返回 732138 行)
   ->  Index Scan using lineitem_n3 on lineitem   (rows=150615)
         Index Cond: ((l_orderkey = orders.o_orderkey) AND (l_shipdate > ...))

关键变化：

lineitem 从 Seq Scan 变为 Index Scan using lineitem_n3
连接方式从 Hash Join 变为 Nested Loop
lineitem 不再全表扫描，而是通过索引直接定位：Index Cond: ((l_orderkey = orders.o_orderkey) AND (l_shipdate > ...))

效果：

耗时：11572 ms
性能提升约 1 倍

2.2 案例：强制 HashJoin 避免 NestLoop 灾难

当两表都很大且无有效索引时，NestLoop 会导致灾难性性能。此时应强制 HashJoin。

EXPLAIN ANALYZE
SELECT /*+ hashjoin(t o) */ *
FROM t_tables t
INNER JOIN t_objects o ON o.owner = t.owner AND o.object_name = t.table_name;

执行计划：

QUERY PLAN
--------------------------------------------------------------------------------
+ Hash Join  (cost=202.47..3856.25 rows=864 width=544)
           (actual time=2.144..37.811 rows=3062 loops=1)
   Hash Cond: (((o.owner)::text = (t.owner)::text) AND ((o.object_name)::text = (t.table_name)::text))
   ->  Seq Scan on t_objects o  (cost=0.00..2778.57 rows=86657 width=194)
                                (actual time=0.007..11.676 rows=86657 loops=1)
   ->  Hash  (cost=159.59..159.59 rows=2859 width=350)
             (actual time=2.001..2.001 rows=2859 loops=1)
         Buckets: 32768  Batches: 1  Memory Usage: 1071kB
         ->  Seq Scan on t_tables t  (cost=0.00..159.59 rows=2859 width=350)
                                    (actual time=0.004..0.541 rows=2859 loops=1)
Total runtime: 38.289 ms

效果：

t_tables 2859 行构建哈希表，内存占用仅 1071 KB
t_objects 86657 行作为探测表，全表扫描一次
哈希匹配在内存中完成，总耗时 37.8 ms

若错误地强制 NestLoop（无索引时）：

SELECT /*+ nestloop(t o) */ * FROM t_tables t INNER JOIN t_objects o ...

2859 行 × 86657 次扫描 = 2.4 亿次比较，性能将急剧恶化。

2.3 案例：强制 MergeJoin 利用有序性

当两表在关联列上均有索引且查询需要有序输出时，MergeJoin 可以避免额外排序。

原 SQL

EXPLAIN ANALYZE
SELECT /*+ mergejoin(partsupp part) */ p_brand, p_type, p_size, ps_suppkey
FROM partsupp, part
WHERE p_partkey = ps_partkey
  AND p_brand <> 'Brand#45'
  AND p_type NOT LIKE 'MEDIUM POLISHED%'
  AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9);

优化前执行计划（Hash Join）

QUERY PLAN
--------------------------------------------------------------------------------
+ Hash Join  (cost=52536.12..188411.18 rows=1696769 width=40)
           (actual time=433.771..1474.535 rows=592984 loops=1)
   Hash Cond: (partsupp.ps_partkey = part.p_partkey)
   ->  Index Only Scan using partsupp_pkey on partsupp  (cost=0.00..103925.94 rows=3995046 width=8) (actual time=0.011..419.593 rows=4000000 loops=1)
         Heap Fetches: 0
   ->  Hash  (cost=50719.00..50719.00 rows=145370 width=40) (actual time=432.617..432.617 rows=148246 loops=1)
          Buckets: 262144  Batches: 1  Memory Usage: 12772kB
         ->  Seq Scan on part  (cost=0.00..50719.00 rows=145370 width=40) (actual time=0.021..381.128 rows=148246 loops=1)
               Filter: ((p_brand <> 'Brand#45'::bpchar) AND ((p_type)::text !~~ 'MEDIUM POLISHED%'::text) AND (p_size = ANY ('{49,14,23,45,19,3,36,9}'::integer[])))
               Rows Removed by Filter: 851754
 Total runtime: 1506.013 ms

优化方案

partsupp 通过 partsupp_pkey 主键索引已有序，无需额外排序
考虑使用 Merge join

EXPLAIN ANALYZE
SELECT /*+ mergejoin(partsupp part) */ p_brand, p_type, p_size, ps_suppkey
FROM partsupp, part
WHERE p_partkey = ps_partkey
  AND p_brand <> 'Brand#45'
  AND p_type NOT LIKE 'MEDIUM POLISHED%'
  AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9);

优化后执行计划（Merge Join）

QUERY PLAN
--------------------------------------------------------------------------------
+ Merge Join  (cost=10000063188.44..100001947919.45 rows=1696769 width=40)
            (actual time=440.042..1176.768 rows=592984 loops=1)
   Merge Cond: (part.p_partkey = partsupp.ps_partkey)
   ->  Sort  (cost=63184.02..63547.44 rows=145370 width=40)
             (actual time=439.933..448.494 rows=148246 loops=1)
         Sort Key: part.p_partkey
         Sort Method: quicksort Memory: 20043kB
         ->  Seq Scan on part  (cost=0.00..50719.00 rows=145370 width=40)
                              (actual time=0.026..386.669 rows=148246 loops=1)
               Filter: ((p_brand <> 'Brand#45'::bpchar) AND ((p_type)::text !~~ 'MEDIUM POLISHED%'::text) AND (p_size = ANY ('{49,14,23,45,19,3,36,9}'::integer[])))
               Rows Removed by Filter: 851754
   ->  Index Only Scan using partsupp_pkey on partsupp  (cost=0.00..103925.94 rows=3995046 width=8)
                                                      (actual time=0.094..413.465 rows=3999985 loops=1)
         Heap Fetches: 0
Total runtime: 1207.956 ms

效果：

part 需要按 p_partkey 排序，消耗 20043 KB 内存
排序后两表"拉链"式归并匹配，总耗时 1207 ms
相比同条件的 Hash Join（1506 ms），MergeJoin 在此场景下略快，因为 partsupp 已天然有序

三、通过 Hint 调整驱动表

连接方式确定后，驱动表的选择同样关键。通常原则是：让小表驱动大表（NestLoop 中让结果集小的做外表；HashJoin 中让小的表做 Build 侧）。

在写SQL或使用leadingHINT时，请遵循以下原则：

过滤优先（哪张表先过滤得少，谁当驱动表）：
- 不要看表的大小，要看WHERE条件过滤后的行数。
- 案例：customer表有75万行，但c_mktsegment='BUILDING'过滤后只有15万行；lineitem有6000万行，但l_shipdate > date '1995-03-15'过滤后有1600万行。此时customer是更好的驱动表。
leadingHINT的使用：
- leading((a b c)) 表示从左到右执行。左边的就是驱动表，右边的就是被驱动表。
- 如果你想强制Nest Loop，务必把小表/过滤后行数少的表写在最左边（驱动表），把有索引的大表写在右边（被驱动表）。
总结记忆口诀：
- 写法决定外表和内表（语法）。
- 执行决定驱动和被驱动（物理）。
- 优化核心：小表或过滤后小的表做驱动表（Build表），大表做被驱动表（Probe/Scan表），并配合索引。

3.1 NestLoop 的驱动表调整

语法：leading((o t)) 表示先访问 o，再用 o 驱动 t。

原 SQL 执行计划（驱动表为 `t_tables`）

EXPLAIN ANALYZE
SELECT /*+ nestloop(t o) */ *
FROM t_tables t
INNER JOIN t_objects o ON o.owner = t.owner AND o.object_name = t.table_name;

QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop  (cost=0.00..10576.44 rows=864 width=544)
             (actual time=0.026..41.958 rows=3062 loops=1)
+   ->  Seq Scan on t_tables t  (cost=0.00..159.59 rows=2859 width=350)
                               (actual time=0.004..0.389 rows=2859 loops=1)
-   ->  Index Scan using t_objects_n10 on t_objects o  (cost=0.00..3.63 rows=1 width=194)
                                                      (actual time=38.598..39.058 rows=3062 loops=2859)
         Index Cond: ((object_name)::text = (t.table_name)::text)
         Filter: ((t.owner)::text = (owner)::text)
         Rows Removed by Filter: 164
Total runtime: 42.243 ms

分析：

外表 t_tables 2859 行做顺序扫描
内表 t_objects 通过索引 t_objects_n10 匹配，循环 2859 次
每次循环实际返回约 1 行（rows=3062 / loops=2859 ≈ 1）
总耗时 42.2 ms，性能良好

调整驱动表后（`t_objects` 驱动 `t_tables`）

EXPLAIN ANALYZE
SELECT /*+ nestloop(t o) leading((o t)) */ *
FROM t_tables t
INNER JOIN t_objects o ON o.owner = t.owner AND o.object_name = t.table_name;

QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop  (cost=0.00..28075.82 rows=864 width=544)
             (actual time=0.094..173.853 rows=3062 loops=1)
+   ->  Seq Scan on t_objects o  (cost=0.00..2778.57 rows=86657 width=194)
                                (actual time=0.006..15.715 rows=86657 loops=1)
-   ->  Index Scan using t_tables_n1 on t_tables t  (cost=0.00..0.28 rows=1 width=350)
                                                    (actual time=124.879..125.230 rows=3062 loops=86657)
         Index Cond: (((owner)::text = (o.owner)::text) AND ((table_name)::text = (o.object_name)::text))
Total runtime: 174.116 ms

分析：

外表变为 t_objects（86657 行），内表 t_tables 走索引
循环次数从 2859 次暴涨到 86657 次
虽然每次索引扫描很快，但总耗时升至 174 ms
结论：NestLoop 中外表应尽量小，否则循环次数过多会导致性能恶化。

3.2 HashJoin 的驱动表调整

HashJoin 中，Build 侧（被哈希的表）应尽量小，以节省内存。

原 SQL 执行计划（`t_tables` 做 Build 侧）

EXPLAIN ANALYZE
SELECT /*+ hashjoin(t o) */ *
FROM t_tables t
INNER JOIN t_objects o ON o.owner = t.owner AND o.object_name = t.table_name;

Hash Join
   Hash Cond: ((t.owner, t.table_name) = (o.owner, o.object_name))
-   ->  Seq Scan on t_objects o  (rows=86657)
   ->  Hash  (Memory Usage: 1071kB)
+         ->  Seq Scan on t_tables t  (2859 行构建哈希表)
Total runtime: 38.289 ms

分析：

t_tables 2859 行构建哈希表，内存占用仅 1071 KB
t_objects 86657 行作为探测表顺序扫描
总耗时 37.8 ms

强制 `t_objects` 做 Build 侧（大表构建哈希表）

EXPLAIN ANALYZE
SELECT /*+ hashjoin(t o) leading((t o)) */ *
FROM t_tables t
INNER JOIN t_objects o ON o.owner = t.owner AND o.object_name = t.table_name;

Hash Join
   Hash Cond: ((t.owner, t.table_name) = (o.owner, o.object_name))
-   ->  Seq Scan on t_tables t
   ->  Hash  (Memory Usage: 13143 KB)
+         ->  Seq Scan on t_objects o  (86657 行构建哈希表)
Total runtime: 40.964 ms

分析：

t_objects 86657 行被迫构建哈希表
哈希表内存占用从 1071 KB 暴涨至 13143 KB
总耗时从 37.8 ms 增至 40.9 ms
结论：HashJoin 中应让优化器自动选择小表做 Build 侧，强行指定大表会导致内存占用和构建成本显著增加。

四、连接方式选择的决策树

面对多表关联，如何选择最优连接方式？可参考以下决策逻辑：

是否为等值关联？
├── 否 → 只能用 NestLoop（或 MergeJoin 有限支持）
└── 是 → 外表过滤后行数很少？
    ├── 是 → NestLoop + 内表索引（最优）
    └── 否 → 两表都很大？
        ├── 是 → HashJoin（内存充足）或 MergeJoin（已有序）
        └── 否 → 优化器自动选择

五、小结

Hint 语法	作用
`/+ nestloop(t o) /`	强制 NestLoop 连接
`/+ hashjoin(t o) /`	强制 HashJoin 连接
`/+ mergejoin(t o) /`	强制 MergeJoin 连接
`/+ leading((o t)) /`	指定驱动顺序（先 o 后 t）

NestLoop：外表小、内表有索引时的王者，TPCH Q3 靠它从 23 秒降到 11 秒。
HashJoin：大数据量等值关联的通用选择，注意让小表做 Build 侧。
MergeJoin：利用已有序数据，避免哈希开销，适合索引覆盖的有序关联。

下一篇将深入子查询优化，剖析 NOT IN 的性能陷阱与标量子查询的改写技巧。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

拨开慢SQL迷雾｜连载03：一文掌控多表JOIN策略，从选型到Hint强制绑定

导语

一、三种连接方式原理与适用场景

1.1 Nested Loop（嵌套循环连接）

1.2 Hash Join（哈希连接）

1.3 Merge Join（归并连接）

二、通过 Hint 强制指定连接方式

2.1 案例：强制 NestLoop 优化 TPCH Q3

优化前执行计划（Hash Join）

优化方案

优化后执行计划（Nested Loop）

2.2 案例：强制 HashJoin 避免 NestLoop 灾难

2.3 案例：强制 MergeJoin 利用有序性

原 SQL

优化前执行计划（Hash Join）

优化方案

优化后执行计划（Merge Join）

三、通过 Hint 调整驱动表

3.1 NestLoop 的驱动表调整

原 SQL 执行计划（驱动表为 t_tables）

调整驱动表后（t_objects 驱动 t_tables）

3.2 HashJoin 的驱动表调整

原 SQL 执行计划（t_tables 做 Build 侧）

强制 t_objects 做 Build 侧（大表构建哈希表）

四、连接方式选择的决策树

五、小结

评论

原 SQL 执行计划（驱动表为 `t_tables`）

调整驱动表后（`t_objects` 驱动 `t_tables`）

原 SQL 执行计划（`t_tables` 做 Build 侧）

强制 `t_objects` 做 Build 侧（大表构建哈希表）