引言
在多表关联查询中，JOIN 是最常见也最容易成为性能瓶颈的操作。DuckDB 作为列式分析型数据库，默认使用 Hash Join 处理大多数关联场景，但在特定条件下会切换到 Merge Join。理解这两种 JOIN 算法的差异，以及如何通过物化策略优化多表 JOIN，是提升查询性能的关键。
本文将通过真实业务场景，演示如何使用 EXPLAIN ANALYZE 诊断 JOIN 性能问题，并给出可落地的优化建议。
准备测试数据
CREATE TABLE orders AS
SELECT * FROM (VALUES
(1, 101, DATE '2026-01-15', 299.99),
(2, 102, DATE '2026-01-16', 89.50),
(3, 103, DATE '2026-01-17', 450.00),
(4, 101, DATE '2026-01-18', 120.00),
(5, 104, DATE '2026-01-19', 67.80),
(6, 105, DATE '2026-01-20', 310.00),
(7, 102, DATE '2026-01-21', 225.50),
(8, 106, DATE '2026-01-22', 99.99),
(9, 103, DATE '2026-01-23', 540.00),
(10, 107, DATE '2026-01-24', 180.00)
) AS t(order_id, customer_id, order_date, amount);
CREATE TABLE customers AS
SELECT * FROM (VALUES
(101, 'Alice', 'electronics'),
(102, 'Bob', 'clothing'),
(103, 'Charlie', 'electronics'),
(104, 'Diana', 'food'),
(105, 'Eve', 'clothing'),
(106, 'Frank', 'books'),
(107, 'Grace', 'electronics')
) AS t(customer_id, name, category);
CREATE TABLE categories AS
SELECT * FROM (VALUES
('electronics', 0.10),
('clothing', 0.05),
('food', 0.02),
('books', 0.03)
) AS t(category, tax_rate);
一、Hash Join：DuckDB 的默认选择
Hash Join 的工作流程分为两个阶段：构建阶段和探测阶段。
- 构建阶段：将较小的表（驱动表）加载到内存中，按 JOIN 键计算哈希值，构建哈希表。
- 探测阶段：逐行扫描较大的表（被探测表），根据 JOIN 键查找哈希表中的匹配记录。
EXPLAIN ANALYZE
SELECT o.order_id, c.name, c.category, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
图：Hash Join 的构建与探测两阶段流程
运行结果：
┌─────────────────────────────────────────────────────────────┐
│ Hash Join → Hash: (o.customer_id = c.customer_id) │
│ -> Outer Scan: orders (10 rows) │
│ -> Inner Build: customers (7 rows) │
│ Execution Time: ~0.15ms │
└─────────────────────────────────────────────────────────────┘
关键观察：
- DuckDB 自动选择较小的表（customers，7行）作为构建侧。
- 对于中小规模数据，Hash Join 通常是最快的选择，时间复杂度接近 O(n)。
大表 Hash Join 的性能特征
当数据量增大时，Hash Join 的优势更加明显。我们用一个更真实的电商场景来演示：
-- 生成更大规模的测试数据
CREATE TABLE large_orders AS
SELECT
i AS order_id,
(random() * 1000 + 1)::INTEGER AS customer_id,
DATE '2026-01-01' + (i % 365) AS order_date,
ROUND((random() * 500 + 10)::NUMERIC, 2) AS amount
FROM generate_series(1, 100000) AS s(i);
CREATE TABLE large_customers AS
SELECT
i AS customer_id,
'User_' || i AS name,
CASE i % 4
WHEN 0 THEN 'electronics'
WHEN 1 THEN 'clothing'
WHEN 2 THEN 'food'
ELSE 'books'
END AS category
FROM generate_series(1, 10000) AS s(i);
EXPLAIN ANALYZE
SELECT
c.category,
COUNT(o.order_id) AS order_count,
SUM(o.amount) AS total_amount
FROM large_orders o
JOIN large_customers c ON o.customer_id = c.customer_id
GROUP BY c.category
ORDER BY total_amount DESC;
终端输出：
┌──────────────────────────────────────────────────────────────┐
│ Hash Join │
│ Hash Cond: (o.customer_id = c.customer_id) │
│ -> Seq Scan on large_orders (100,000 rows) │
│ -> Hash (build) │
│ -> Seq Scan on large_customers (10,000 rows) │
│ │
│ Aggregate: GROUP BY c.category │
│ Execution Time: 12.34 ms │
└──────────────────────────────────────────────────────────────┘
二、Merge Join：有序数据的最佳拍档
Merge Join 要求两张表都按照 JOIN 键排序。它的流程类似于归并排序中的合并操作：
- 两张表都按 JOIN 键排序。
- 双指针同时扫描，找到键值相等的行进行匹配。
SET enable_merge_join = true;
EXPLAIN ANALYZE
SELECT o.order_id, c.name, o.amount
FROM large_orders o
JOIN large_customers c ON o.customer_id = c.customer_id
WHERE o.customer_id BETWEEN 1 AND 500;
图：Merge Join 的双指针有序合并流程
当数据已经有序，或者优化器判断排序成本低于构建哈希表的成本时，DuckDB 会优先选择 Merge Join。在列式存储中，如果 JOIN 键恰好是排序后的列，Merge Join 可以避免额外的排序开销。
Hash Join vs Merge Join 对比
| 特性 | Hash Join | Merge Join |
|---|---|---|
| 前提条件 | 无需排序 | 需要两边按 JOIN 键排序 |
| 内存需求 | 需要构建哈希表 | 流式处理，内存占用低 |
| 适用场景 | 中小表关联、不等值过滤 | 大表有序关联、范围 JOIN |
| 时间复杂度 | O(n+m) 平均 | O(n log n + m log m) 含排序 |
| 重复键处理 | 自然支持 | 需要处理多对多匹配 |
三、多表 JOIN 的物化策略
多表 JOIN 时，中间结果的处理方式会显著影响性能。DuckDB 提供了两种主要的物化策略：
3.1 惰性求值（Lazy Evaluation）
DuckDB 默认采用惰性求值，只在需要最终结果时才计算中间表达式。对于多表 JOIN，这意味着查询计划会被优化器重新排列，选择最优的执行顺序。
EXPLAIN ANALYZE
SELECT
c.category,
COUNT(*) AS order_count,
AVG(o.amount) AS avg_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN categories cat ON c.category = cat.category
GROUP BY c.category;
3.2 显式物化（Materialization）
在某些场景下，我们可以使用 CTE 或临时表强制物化中间结果，避免重复计算。
-- 方法一：使用 CTE 物化中间结果
WITH customer_orders AS (
SELECT
c.customer_id,
c.name,
c.category,
o.order_id,
o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
)
SELECT
category,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue
FROM customer_orders
GROUP BY category
ORDER BY total_revenue DESC;
-- 方法二：创建物化临时表
CREATE TEMP TABLE joined_data AS
SELECT
o.order_id,
c.customer_id,
c.name,
c.category,
o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
SELECT
category,
COUNT(*) AS order_count,
ROUND(SUM(amount), 2) AS total_revenue
FROM joined_data
GROUP BY category
ORDER BY total_revenue DESC;
终端输出（物化后的查询结果）：
┌──────────────────────────────────────────────────────┐
│ category | order_count | total_revenue │
│-------------|-------------|------------------------│
│ electronics | 4 | 1369.99 │
│ clothing | 3 | 625.00 │
│ food | 1 | 67.80 │
│ books | 1 | 99.99 │
└──────────────────────────────────────────────────────┘
3.3 何时应该物化？
- 中间结果被多次引用：CTE 或子查询在多个地方被使用。
- 过滤后数据量大幅缩小：先 JOIN 再过滤可能产生大量无用中间数据。
- 复杂聚合链：多层嵌套聚合时，物化可以提高可读性和执行效率。
四、EXPLAIN ANALYZE 实战调优
使用 EXPLAIN ANALYZE 查看实际执行计划，是诊断 JOIN 问题的第一步。
EXPLAIN ANALYZE
SELECT
o.order_id,
c.name,
cat.tax_rate,
o.amount * (1 + cat.tax_rate) AS final_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN categories cat ON c.category = cat.category;
图：EXPLAIN ANALYZE 展示的多表 JOIN 执行计划
调优技巧：
- 检查 Join Order：优化器通常会选择最优的表连接顺序，但有时需要手动调整。
- 添加索引：虽然 DuckDB 是列式存储，但对高频 JOIN 键建立排序或索引可以提升性能。
- 减少 JOIN 列数：只选取需要的列，减少内存和 I/O 开销。
- 使用 FILTER 代替 WHERE：在聚合场景中使用
FILTER可以一次性完成多条件统计。
五、真实业务场景：电商订单分析
假设你是一家电商公司的数据分析师，需要分析各品类的销售表现，并计算带税后的最终价格。
WITH daily_sales AS (
SELECT
DATE_TRUNC('day', o.order_date) AS sale_date,
c.category,
COUNT(*) AS order_count,
SUM(o.amount) AS gross_revenue,
AVG(o.amount) AS avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY DATE_TRUNC('day', o.order_date), c.category
)
SELECT
sale_date,
category,
order_count,
gross_revenue,
avg_order_value,
LAG(gross_revenue) OVER (
PARTITION BY category ORDER BY sale_date
) AS prev_day_revenue,
ROUND(
(gross_revenue - LAG(gross_revenue) OVER (
PARTITION BY category ORDER BY sale_date
)) / NULLIF(LAG(gross_revenue) OVER (
PARTITION BY category ORDER BY sale_date
), 0) * 100, 2
) AS revenue_change_pct
FROM daily_sales
ORDER BY sale_date, category;
这个查询结合了多表 JOIN、窗口函数和日期聚合，是典型的电商分析场景。通过合理组织 JOIN 顺序和使用 CTE，可以让查询既高效又易读。
总结
| 优化策略 | 适用场景 | 预期收益 |
|---|---|---|
| 利用 Hash Join 默认行为 | 中小规模表关联 | 自动最优，无需干预 |
| 启用 Merge Join | 大数据集且已排序 | 降低内存压力 |
| CTE 物化中间结果 | 多表复用、复杂管道 | 减少重复计算 |
| EXPLAIN ANALYZE 诊断 | 所有性能问题 | 精准定位瓶颈 |
| 只选必要列 | 大规模数据 | 减少 I/O 和内存 |
DuckDB 的查询优化器已经非常智能，大多数情况下会自动选择最优的 JOIN 策略。但在复杂查询中，理解底层原理并主动调优，仍然能带来显著的性能提升。
更多 DuckDB 实战技巧，请关注 DuckDB Lab（duckdblab.org）