DuckDB复杂查询优化：从400ms到10ms的实战技巧

上个月接到一个需求：分析10万条订单数据，找出每个客户的高价值订单并计算客户平均订单金额。第一版SQL写完，跑了400多毫秒。当时就觉得不对劲，10万条数据而已，怎么这么慢？

用EXPLAIN ANALYZE一看，好家伙，相关子查询在疯狂执行。改成CTE之后，10毫秒搞定。38倍的性能差距，就这么来了。

这篇文章记录了8个真实的DuckDB复杂查询优化案例，每个都有❌慢查询和✅快查询的对比，以及真实的性能数据。如果你也在处理DuckDB的复杂查询，这些技巧应该能帮到你。

测试环境

先说清楚测试环境，方便复现：

硬件：AMD EPYC 7K62 × 4核，7.5GB内存，118GB SSD
系统：Ubuntu 24.04.4 LTS
DuckDB：1.5.5
并行线程：4

测试数据集：

large_ecommerce_orders.csv：10万行订单数据，10.3MB
products_catalog.json：5000个产品，1.4MB
iot_sensor_data.csv：100万行IoT传感器数据，70.1MB

优化1：相关子查询 → CTE重写

这是最常见的性能杀手。相关子查询对每一行都要执行一次子查询，10万行数据就要执行10万次。

❌ 慢查询：相关子查询（413ms）

1
2
3
4
5
6
7
8
SELECT 
    order_id, customer_id, total_amount,
    (SELECT AVG(total_amount) 
     FROM orders o2 
     WHERE o2.customer_id = o.customer_id) as customer_avg
FROM orders o
WHERE total_amount > 100
LIMIT 1000;

✅ 快查询：CTE方式（10ms）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
WITH customer_avg AS (
    SELECT customer_id, AVG(total_amount) as avg_amount
    FROM orders 
    GROUP BY customer_id
)
SELECT 
    o.order_id, o.customer_id, o.total_amount,
    ca.avg_amount as customer_avg
FROM orders o
JOIN customer_avg ca ON o.customer_id = ca.customer_id
WHERE o.total_amount > 100
LIMIT 1000;

性能提升：38.5倍（413ms → 10ms）

CTE先计算每个客户的平均订单金额，然后JOIN回去。DuckDB优化器会自动将CTE内联，所以CTE和JOIN的性能差不多。

优化2：窗口函数替代子查询排名

计算每个客户内部的订单排名，用子查询需要对每行执行一次全表扫描，窗口函数只需一次扫描。

❌ 慢查询：子查询排名（218ms）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT 
    order_id, customer_id, total_amount,
    (SELECT COUNT(*) + 1 
     FROM orders o2 
     WHERE o2.customer_id = o.customer_id 
       AND o2.total_amount > o.total_amount) as rank_in_customer
FROM orders o
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    GROUP BY customer_id 
    HAVING COUNT(*) > 10
)
LIMIT 1000;

✅ 快查询：窗口函数排名（25ms）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
WITH top_customers AS (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 10
)
SELECT 
    order_id, customer_id, total_amount,
    RANK() OVER (
        PARTITION BY customer_id 
        ORDER BY total_amount DESC
    ) as rank_in_customer
FROM orders
WHERE customer_id IN (SELECT customer_id FROM top_customers)
LIMIT 1000;

性能提升：8.7倍（218ms → 25ms）

窗口函数的PARTITION BY决定了分区粒度，用错字段会导致排名计算错误。写之前先理解清楚PARTITION BY的含义。

优化3：聚合优化（多次扫描 → 单次扫描）

计算每个客户的订单数、总金额、平均金额，用子查询方式需要扫描表3次，GROUP BY只需扫描1次。

❌ 慢查询：多次扫描（35ms）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT 
    customer_id,
    (SELECT COUNT(*) FROM orders o2 
     WHERE o2.customer_id = o.customer_id) as order_count,
    (SELECT SUM(total_amount) FROM orders o3 
     WHERE o3.customer_id = o.customer_id) as total_amount,
    (SELECT AVG(total_amount) FROM orders o4 
     WHERE o4.customer_id = o.customer_id) as avg_amount
FROM (SELECT DISTINCT customer_id FROM orders) o
LIMIT 100;

✅ 快查询：单次扫描（7ms）

1
2
3
4
5
6
7
8
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_amount,
    AVG(total_amount) as avg_amount
FROM orders
GROUP BY customer_id
LIMIT 100;

性能提升：4.6倍（35ms → 7ms）

能用一个GROUP BY解决的，不要用多个子查询。

优化4：谓词下推

先过滤再JOIN，减少JOIN的数据量。DuckDB优化器会自动进行谓词下推，但显式写法更清晰，对于大数据集（GB级）效果更明显。

❌ 慢查询：先JOIN后过滤（8ms）

1
2
3
4
5
6
7
8
SELECT *
FROM (
    SELECT o.order_id, o.customer_id, o.total_amount, 
           p.name as product_name, p.category
    FROM orders o
    JOIN products p ON o.product = p.name
)
WHERE total_amount > 500 AND category = 'Electronics';

✅ 快查询：先过滤后JOIN（5ms）

1
2
3
4
5
6
SELECT 
    o.order_id, o.customer_id, o.total_amount,
    p.name as product_name, p.category
FROM (SELECT * FROM orders WHERE total_amount > 500) o
JOIN (SELECT * FROM products WHERE category = 'Electronics') p 
  ON o.product = p.name;

性能提升：1.6倍（8ms → 5ms）

对于10万行数据，提升不算特别大。但对于GB级数据集，谓词下推可以提升10-100倍性能。

优化5：EXISTS vs IN vs JOIN

查询总订单金额大于5000的客户的所有订单，三种写法的性能差异。

IN子查询（110ms）

1
2
3
4
5
6
7
SELECT * FROM orders
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    GROUP BY customer_id 
    HAVING SUM(total_amount) > 5000
);

EXISTS（114ms）

1
2
3
4
5
6
7
8
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 
    FROM orders o2 
    WHERE o2.customer_id = o.customer_id
    GROUP BY o2.customer_id
    HAVING SUM(o2.total_amount) > 5000
);

JOIN（107ms，最快）

1
2
3
4
5
6
7
8
SELECT o.* 
FROM orders o
JOIN (
    SELECT customer_id 
    FROM orders 
    GROUP BY customer_id 
    HAVING SUM(total_amount) > 5000
) h ON o.customer_id = h.customer_id;

性能差异：107-114ms，几乎没差别

DuckDB优化器会自动优化这三种写法。在DuckDB中，JOIN略快于IN和EXISTS，但差异可以忽略。选择哪种写法主要看可读性。

优化6：DISTINCT vs GROUP BY

查询所有不重复的客户+地区组合。

DISTINCT（17ms）

1
2
SELECT DISTINCT customer_id, region
FROM orders;

GROUP BY（15ms）

1
2
3
SELECT customer_id, region
FROM orders
GROUP BY customer_id, region;

性能提升：1.1倍（17ms → 15ms）

差异很小。如果只需要去重，用DISTINCT更直观；如果需要在去重基础上做聚合，用GROUP BY。

优化7：UNION vs UNION ALL

合并北方和南方地区的订单。

UNION（去重，13ms）

1
2
3
SELECT * FROM orders WHERE region = 'North'
UNION
SELECT * FROM orders WHERE region = 'South';

UNION ALL（不去重，10ms）

1
2
3
SELECT * FROM orders WHERE region = 'North'
UNION ALL
SELECT * FROM orders WHERE region = 'South';

性能提升：1.3倍（13ms → 10ms）

UNION需要额外的去重操作。如果确定没有重复数据，优先用UNION ALL。

优化8：100万行数据的复杂时序分析

处理100万行IoT传感器数据，按小时聚合后计算每日统计。

多层CTE复杂时序分析（96ms）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
WITH hourly_stats AS (
    SELECT 
        sensor_id,
        DATE_TRUNC('hour', timestamp) as hour,
        AVG(value) as avg_value,
        MAX(value) as max_value,
        MIN(value) as min_value,
        COUNT(*) as sample_count
    FROM iot
    GROUP BY sensor_id, DATE_TRUNC('hour', timestamp)
),
sensor_summary AS (
    SELECT 
        sensor_id,
        AVG(avg_value) as daily_avg,
        MAX(max_value) as daily_max,
        MIN(min_value) as daily_min,
        SUM(sample_count) as total_samples
    FROM hourly_stats
    GROUP BY sensor_id
)
SELECT *
FROM sensor_summary
WHERE total_samples > 100
ORDER BY daily_avg DESC
LIMIT 20;

性能数据：

全量时序聚合（按传感器+小时）：271ms，39万行
带过滤的时序聚合（只聚合temperature指标）：82ms，16万行
多层CTE复杂分析：96ms，20行

添加过滤条件可以提升3.3倍性能（271ms → 82ms）。对于100万行数据，DuckDB可以在1秒内完成复杂聚合。

性能提升排行榜

优化技巧	提升倍数	适用场景
子查询→JOIN/CTE重写	38.4x	相关子查询
窗口函数替代子查询排名	8.7x	排名、累计计算
聚合优化（多次→单次扫描）	4.6x	多字段聚合
谓词下推	1.6x	大表JOIN
UNION→UNION ALL	1.3x	无重复数据合并
DISTINCT→GROUP BY	1.1x	去重+聚合

踩坑记录

坑1：相关子查询的陷阱

写了一个相关子查询，以为逻辑清晰，结果跑了400多毫秒。原因是相关子查询对每一行都要执行一次子查询，10万行数据就要执行10万次。改用CTE或JOIN，先计算聚合结果，再JOIN回去。

坑2：窗口函数的PARTITION BY

窗口函数很快，但PARTITION BY用错了字段，结果不对。PARTITION BY决定了分区的粒度，用错字段会导致排名计算错误。仔细理解PARTITION BY的含义，先小数据集测试。

坑3：CTE会被重复计算

一个CTE被引用了3次，以为只会计算1次，结果计算了3次。DuckDB默认会将CTE内联，如果被引用多次，会重复计算。如果CTE计算成本高，使用 WITH x AS MATERIALIZED (...) 强制物化。

坑4：谓词下推不总是有效

显式写了先过滤再JOIN，但性能提升不明显。DuckDB优化器会自动进行谓词下推，显式写法帮助不大。对于小数据集，优化器已经很聪明；对于大数据集（GB级），显式写法更可靠。

如何看懂执行计划

用EXPLAIN ANALYZE定位瓶颈：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
EXPLAIN ANALYZE 
SELECT 
    o.order_id, o.customer_id, o.total_amount,
    ca.avg_amount as customer_avg
FROM orders o
JOIN (
    SELECT customer_id, AVG(total_amount) as avg_amount
    FROM orders
    GROUP BY customer_id
) ca ON o.customer_id = ca.customer_id
WHERE o.total_amount > 100;

关键算子：

SEQ_SCAN：全表扫描（大数据集时关注）
FILTER：过滤操作（看是否下推到扫描阶段）
HASH_JOIN：哈希JOIN（比NESTED_LOOP_JOIN快）
AGGREGATE：聚合操作（看是否使用了GROUP BY优化）
WINDOW：窗口函数（看PARTITION BY是否合理）

优化目标：

避免 NESTED_LOOP_JOIN（改用HASH_JOIN）
确保 FILTER 在 SEQ_SCAN 之前（谓词下推）
避免不必要的 SEQ_SCAN（考虑添加索引）

优化检查清单

拿到一个慢查询，按这个顺序检查：

有没有相关子查询？ 改成CTE或JOIN
有没有用子查询做排名？ 改成窗口函数
有没有多次扫描同一张表？ 用GROUP BY合并成单次扫描
JOIN之前有没有先过滤？ 谓词下推
UNION有没有重复数据？ 没有就用UNION ALL
用EXPLAIN ANALYZE看执行计划 找瓶颈

参考资源

DuckDB Performance Guide - 官方性能调优指南
DuckDB EXPLAIN文档 - EXPLAIN语法和执行计划解读
DuckDB Tuning Workloads - 工作负载调优
DuckDB Optimizers: The Low-Key MVP - DuckDB官方博客，详解优化器工作原理
DuckDB Performance Tuning: 5 Tips - 5个性能优化技巧

现在写复杂查询，第一反应就是避免相关子查询。400ms到10ms的差距，就是这么来的。

处理DuckDB复杂查询，先从EXPLAIN ANALYZE开始，找到瓶颈，再针对性优化。这8个技巧覆盖了大部分场景，应该够用了。