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_* 参数只用于观察算法。生产环境应让规划器基于统计信息、索引和成本参数选择方案。
若规划器经常选错,优先检查估算行数、统计信息、连接条件和索引,而不是直接禁用某种连接算法。
按自己的节奏,确认真正理解。