【PostgreSQL从零到精通】第20篇：PostgreSQL中的各种JOIN操作—

上一篇【第19篇】索引原理与优化——从B-Tree到索引策略
下一篇【第21篇】事务处理完全指南——ACID与事务控制（明日更新，敬请期待）

标签：PostgreSQL、JOIN、Nested Loop、Hash Join、Merge Join、连接优化、多表连接
摘要：JOIN 是 SQL 中最核心的操作之一。PostgreSQL 提供了三种物理连接算法：嵌套循环、哈希连接和合并连接。理解它们的工作原理和适用场景，是编写高效 SQL 查询的关键。本文通过大量执行计划对比和真实场景，帮你掌握 JOIN 优化的核心技能。

一、开篇引言

假设你需要关联 5 张表查询一个报表，跑了 30 秒才返回结果。你打开 EXPLAIN 执行计划一看，发现数据库选择了一个 Nested Loop 连接，外层返回了 100 万行，内层每次都要全表扫描一个 500 万行的大表——这就是经典的"笛卡尔积灾难"。

JOIN 操作是 SQL 性能优化的重点区域。PostgreSQL 的查询优化器通常能自动选择最优的 JOIN 策略，但"通常"不代表"总是"。理解三种连接算法的原理和适用场景，能帮助你在优化器选择不当时进行干预。

本文将深入剖析 PostgreSQL 的三种物理 JOIN 实现，以及如何通过执行计划分析 JOIN 性能。

二、三种 JOIN 算法原理

2.1 Nested Loop Join（嵌套循环连接）

原理：外层表（outer）的每一行，都在内层表（inner）中查找匹配行。

外层表（100行） 内层表（1000行） ┌──┐ ┌────────┐ 1 │ │ ──扫描──→│ 查找 │ → 匹配 → 输出 └──┘ │ 查找 │ ┌──┐ │ 查找 │ 2 │ │ ──扫描──→│ 查找 │ → 匹配 → 输出 └──┘ │ 查找 │ ... │ ... │ ┌──┐ └────────┘ 100 │ │ └──┘ 总共扫描：100 × 1000 = 100,000 次

关键特征：

时间复杂度：O(N × M)
外层表行数少时效率高
内层表有索引时效率极高
不需要额外的内存
不需要预先排序

最佳场景：小表驱动大表，且大表有索引

EXPLAINANALYZESELECTo.*,c.nameFROMorders oJOINcustomers cONo.customer_id=c.idWHEREo.id<100;-- orders 只返回少量行

2.2 Hash Join（哈希连接）

原理：先扫描较小的表，在内存中构建哈希表，然后扫描较大的表，用连接键在哈希表中探测匹配行。

步骤1：构建哈希表 ┌──────────────────────┐ │ 小表（customers） │ │ ┌─────┬────┐ │ │ │ id │name│ → 哈希表│ │ │ 1 │张三│ │ │ │ 2 │李四│ │ │ │ 3 │王五│ │ │ └─────┴────┘ │ └──────────────────────┘ 步骤2：探测匹配 ┌──────────────────────┐ │ 大表（orders） │ │ ┌──────┬──────┐ │ │ │c_id │amount│ │ │ │ 1 │ 299 │ → 在哈希表中查找 id=1 → 匹配张三 │ │ 2 │ 150 │ → 在哈希表中查找 id=2 → 匹配李四 │ │ 5 │ 400 │ → 在哈希表中查找 id=5 → 无匹配 │ └──────┴──────┘ │ └──────────────────────┘

关键特征：

时间复杂度：O(N + M)（等值连接）
需要内存存放哈希表
如果内存不够，会分批处理（Batches）
只支持等值连接（=）
通常是最快的 JOIN 方式

最佳场景：两表都较大，等值连接

EXPLAINANALYZESELECTo.*,c.nameFROMorders oJOINcustomers cONo.customer_id=c.id;-- 通常优化器会选择 Hash Join

注意内存问题：

Hash Join (cost=... rows=...) Hash Cond: (o.customer_id = c.id) -> Hash (cost=... rows=200) Buckets: 1024 Batches: 1 Memory Usage: 16kB -- 在内存中 -> Seq Scan on orders (cost=...) -- 如果 Batches > 1，说明内存不够，数据被分批处理（性能下降） -- 增加 work_mem 可以改善 SET work_mem = '256MB';

2.3 Merge Join（合并连接）

原理：先对两张表按连接键排序，然后像"拉链"一样同步遍历两张表。

排序后： orders（按 customer_id） customers（按 id） ┌──────┬──────┐ ┌────┬────┐ │c_id │amount│ │ id │name│ │ 1 │ 299 │ ──匹配──→│ 1 │张三│ │ 1 │ 150 │ ──匹配──→│ 1 │张三│ │ 2 │ 400 │ ──匹配──→│ 2 │李四│ │ 3 │ 200 │ │ 2 │李四│ ← 跳过（orders 没有更多 id=2 的行） │ 3 │ 350 │ ──匹配──→│ 3 │王五│ │ 5 │ 500 │ │ 3 │王五│ ← 跳过 │ ... │ ... │ │ 5 │赵六│ │ │ │ ──匹配──→│ 5 │赵六│ └──────┴──────┘ └────┴────┘

关键特征：

时间复杂度：O(N log N + M log M)（主要是排序开销）
如果数据已经排序，则只需 O(N + M)
支持等值连接和范围连接（>, <, BETWEEN）
不需要额外的哈希内存

最佳场景：数据已经排序，或需要范围连接

EXPLAINANALYZESELECTo.*,c.nameFROMorders oJOINcustomers cONo.customer_id=c.idORDERBYo.customer_id;-- 如果连接键上已有索引，可能选择 Merge Join

三、三种 JOIN 对比总结

对比维度	Nested Loop	Hash Join	Merge Join
时间复杂度	O(N×M)	O(N+M)	O(NlogN+MlogM)
连接条件	任意	仅等值	等值 + 范围
额外内存	不需要	需要（哈希表）	需要（排序）
小表驱动大表	✅ 优秀	✅ 优秀	一般
大表 × 大表	❌ 差	✅ 优秀	✅ 良好
内层表有索引	✅ 非常快	不需要	不需要
数据已排序	无优势	无优势	✅ 最快
非等值连接	✅ 唯一选择	❌ 不支持	✅ 支持

四、JOIN 顺序优化

4.1 优化器的 JOIN 顺序选择

PostgreSQL 的查询优化器会自动选择 JOIN 顺序（基于代价估算）。通常的策略是：

先 JOIN 预估结果最少的表对
使用动态规划算法搜索最优顺序（表少于 12 张时）
使用 GEQO 基因算法（表多于 12 张时）

4.2 多表 JOIN 的执行计划分析

-- 三表连接EXPLAINANALYZESELECTo.id,c.name,p.nameASproduct_name,oi.quantityFROMorders oJOINcustomers cONo.customer_id=c.idJOINorder_items oiONo.id=oi.order_idJOINproducts pONoi.product_id=p.idWHEREo.order_date>='2024-01-01'ORDERBYo.amountDESCLIMIT50;-- 观察执行计划：-- 1. 先看最内层的连接（最先执行）-- 2. 确认每一步的扫描方式（Seq Scan vs Index Scan）-- 3. 确认连接方式（Hash Join 通常最优）-- 4. 关注实际的行数和耗时

4.3 强制使用特定的 JOIN 方式

当优化器选择不当时，可以通过配置参数强制使用特定的 JOIN 方式：

-- 禁用 Hash Join（强制使用 Nested Loop 或 Merge Join）SETenable_hashjoin=OFF;-- 禁用 Merge JoinSETenable_mergejoin=OFF;-- 禁用 Nested LoopSETenable_nestloop=OFF;-- 禁用顺序扫描（强制使用索引）SETenable_seqscan=OFF;-- 测试后记得恢复RESETALL;-- 或在单条查询中设置EXPLAINANALYZE/*+ NestLoop(o c) */SELECT*FROMorders oJOINcustomers cONo.customer_id=c.id;

注意：这些参数应该只在调试时使用，不要在生产环境的配置文件中修改。

五、LEFT/RIGHT/FULL JOIN 的性能考量

-- INNER JOIN：只返回匹配的行-- 性能最好，优化器选择最多-- LEFT JOIN：返回左表所有行，右表不匹配的填 NULLSELECT*FROMorders oLEFTJOINcustomers cONo.customer_id=c.id;-- 优化器可能仍然选择 Hash Join-- FULL OUTER JOIN：返回两表所有行SELECT*FROMorders oFULLOUTERJOINcustomers cONo.customer_id=c.id;-- 通常使用 Hash Join，需要构建两张表的哈希表-- 或者使用 Merge Join（如果数据已排序）

性能提示：

如果 LEFT JOIN 的右表总是能匹配，改用 INNER JOIN 性能更好
如果 LEFT JOIN 产生的 NULL 行后续被 WHERE 过滤掉了，优化器会自动转为 INNER JOIN
FULL JOIN 代价最高，尽量避免使用

六、子查询 vs JOIN

6.1 IN 子查询 vs JOIN

-- IN 子查询（通常会被优化器转为 Semi Join）SELECT*FROMordersWHEREcustomer_idIN(SELECTidFROMcustomersWHEREcity='北京');-- JOIN 方式（手动改写）SELECTDISTINCTo.*FROMorders oJOINcustomers cONo.customer_id=c.idWHEREc.city='北京';-- EXISTS 方式（通常性能最好）SELECT*FROMorders oWHEREEXISTS(SELECT1FROMcustomers cWHEREc.id=o.customer_idANDc.city='北京');

PostgreSQL 的优化器通常能将这些写法优化成相同的执行计划。但如果性能有差异：

EXISTS通常比IN快（遇到匹配就停止）
JOIN的结果可能有重复（需要 DISTINCT）

6.2 NOT IN vs NOT EXISTS vs LEFT JOIN / IS NULL

-- NOT IN（如果子查询有 NULL 值，结果可能不对！）SELECT*FROMordersWHEREcustomer_idNOTIN(SELECTidFROMcustomersWHEREstatus='inactive');-- NOT EXISTS（推荐，没有 NULL 值陷阱）SELECT*FROMorders oWHERENOTEXISTS(SELECT1FROMcustomers cWHEREc.id=o.customer_idANDc.status='inactive');-- LEFT JOIN / IS NULLSELECTo.*FROMorders oLEFTJOINcustomers cONo.customer_id=c.idANDc.status='inactive'WHEREc.idISNULL;

七、实战：优化多表连接查询

场景：电商订单报表查询优化

-- 原始查询（可能很慢）EXPLAINANALYZESELECTo.order_date,c.nameAScustomer_name,c.city,p.nameASproduct_name,oi.quantity,oi.price,(oi.quantity*oi.price)ASline_totalFROMorders oJOINcustomers cONo.customer_id=c.idJOINorder_items oiONo.id=oi.order_idJOINproducts pONoi.product_id=p.idWHEREo.order_dateBETWEEN'2024-01-01'AND'2024-12-31'ANDc.cityIN('北京','上海','广州')ORDERBYo.order_dateDESC,line_totalDESCLIMIT100;-- 优化步骤：-- 1. 检查执行计划，找出最耗时的节点-- 2. 确认 WHERE 条件列上有索引-- 3. 确认连接列上有索引-- 4. 考虑创建覆盖索引-- 可能需要的索引：CREATEINDEXidx_orders_dateONorders(order_date);CREATEINDEXidx_order_items_orderONorder_items(order_id);CREATEINDEXidx_customers_cityONcustomers(city);