PostgreSQL / 系统课时

理解连接算法

理解连接算法

多表查询不仅要决定连接顺序,还要选择具体连接算法。本课通过可运行示例分别观察嵌套循环、哈希连接和归并连接,并说明每种算法适合的数据形态。

学习目标

  • 识别三种常见连接节点
  • 理解它们对数据量、索引和排序的要求
  • 避免在生产环境盲目强制连接算法

准备两张关联表

下面创建客户表和订单表,并建立订单外键列索引。随后通过会话参数仅为教学目的分别展示三种算法。

sql
CREATE TEMP TABLE join_customers (
    id integer PRIMARY KEY,
    name text NOT NULL
);

CREATE TEMP TABLE join_orders (
    id integer PRIMARY KEY,
    customer_id integer NOT NULL,
    amount numeric(10, 2) NOT NULL
);

INSERT INTO join_customers (id, name)
SELECT n, 'customer-' || n
FROM generate_series(1, 1000) AS n;

INSERT INTO join_orders (id, customer_id, amount)
SELECT n, (n % 1000) + 1, (n % 500 + 1)::numeric(10, 2)
FROM generate_series(1, 10000) AS n;

CREATE INDEX idx_join_orders_customer_id
ON join_orders(customer_id);

ANALYZE join_customers;
ANALYZE join_orders;

SET enable_hashjoin = off;
SET enable_mergejoin = off;
EXPLAIN (COSTS OFF)
SELECT c.id, o.amount
FROM join_customers AS c
JOIN join_orders AS o ON o.customer_id = c.id
WHERE c.id = 10;

SET enable_hashjoin = on;
SET enable_nestloop = off;
EXPLAIN (COSTS OFF)
SELECT count(*)
FROM join_customers AS c
JOIN join_orders AS o ON o.customer_id = c.id;

SET enable_nestloop = on;
SET enable_hashjoin = off;
SET enable_mergejoin = on;
EXPLAIN (COSTS OFF)
SELECT c.id, o.id
FROM join_customers AS c
JOIN join_orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

RESET enable_hashjoin;
RESET enable_mergejoin;
RESET enable_nestloop;

预期结果

text
第一次计划包含 Nested Loop
第二次计划包含 Hash Join
第三次计划包含 Merge Join

节点下方的扫描方式和排序步骤可能因 PostgreSQL 版本不同而变化,但三次计划的主连接节点应分别对应上述算法。

嵌套循环连接

Nested Loop 对外层每一行执行一次内层查找。外层结果很小、内层连接键有索引时,它通常非常高效。

风险在于外层实际行数远超估算。此时内层节点会被重复执行大量次数,应结合 EXPLAIN ANALYZE 的 loops 判断是否出现放大效应。

哈希连接

Hash Join 通常先把较小输入构建成哈希表,再扫描另一侧并按连接键查找。它适合等值连接和较大的无序数据集。

哈希表过大时可能分批并使用临时磁盘,性能会下降。计划中的批次数、内存使用和临时读写是重要检查项。

归并连接

Merge Join 要求两侧按连接键有序,然后像合并两个有序序列一样推进。输入本身有合适索引顺序,或后续还需要排序时,它可能更有优势。

若两侧都需要额外排序,排序成本可能抵消归并连接的收益。因此不能只看连接节点,还要看它下面是否出现昂贵的 Sort。

不要长期强制算法

示例中的 enable_* 参数只用于观察算法。生产环境应让规划器基于统计信息、索引和成本参数选择方案。

若规划器经常选错,优先检查估算行数、统计信息、连接条件和索引,而不是直接禁用某种连接算法。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

理解连接算法

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通