做分区实验的初衷很简单：验证一把"分区能加速查询"这句话到底值多少。造了张 1000 万行的传感器表，先按 sensor_type 和 location 分区，几个带分区键的查询确实快了，最多 7 倍，符合预期。然后顺手加了一组按天分区的对照——同一个查询，从 82 毫秒变成 1.6 秒。

第一反应是 SQL 写错了，翻来覆去看了三遍，没写错。问题在分区本身。

分区这件事，各种教程都说"能加速查询"。这话对一半。分区是拿文件数量换 IO 裁剪，换到手的那部分是真的，代价也是真的，只是没人把代价算给你看。这篇用 1000 万行数据，把几个关键问题测成数字：分区到底快多少、多大数据量才值得分区、什么时候分区反而更慢、文件数量超过多少开始崩。

测试环境

• 机器：Intel i7-8750H（6 核 12 线程），16GB 内存，macOS 13.7.6
• DuckDB：1.5.5，PRAGMA threads=4
• 数据：合成 IoT 传感器数据，3 种 sensor_type × 4 个 location × 8760 小时时间戳
• 计时方式：每条查询先跑一次预热，再跑 7～9 次取中位数

造数据的 SQL：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE t AS
SELECT i AS sensor_id,
  CASE WHEN i % 3 = 0 THEN 'temperature'
       WHEN i % 3 = 1 THEN 'humidity'
       ELSE 'pressure' END AS sensor_type,
  CASE WHEN i % 4 = 0 THEN 'room_a'
       WHEN i % 4 = 1 THEN 'room_b'
       WHEN i % 4 = 2 THEN 'room_c'
       ELSE 'room_d' END AS location,
  TIMESTAMP '2024-01-01 00:00:00' + INTERVAL (i % 8760) HOUR AS ts,
  ROUND(20 + (i % 15) * 0.5, 2) AS value
FROM range(10000000) t(i);

三种落盘方式做对照：单文件 Parquet、按 sensor_type 一级分区、按 sensor_type + location 两级分区。

1
2
3
4
5
6
7

COPY t TO 'flat.parquet' (FORMAT PARQUET);

COPY t TO 'by_sensor'
(FORMAT PARQUET, PARTITION_BY (sensor_type), OVERWRITE_OR_IGNORE);

COPY t TO 'by_sensor_loc'
(FORMAT PARQUET, PARTITION_BY (sensor_type, location), OVERWRITE_OR_IGNORE);

生成的目录结构是标准的 Hive 布局，文件夹名就是列名：

1
2
3
4
5

by_sensor_loc/
├── sensor_type=humidity/location=room_a/data_0.parquet
├── sensor_type=humidity/location=room_b/data_0.parquet
├── ...
└── sensor_type=temperature/location=room_d/data_0.parquet

3 个一级分区、12 个二级分区，1000 万行总共 48MB 左右，单个分区文件约 4MB。

分区键过滤：7 倍加速是真的

先测最有利的场景——查询条件里带分区键。读分区数据要开 hive_partitioning：

1
2
3

SELECT COUNT(*), AVG(value)
FROM read_parquet('by_sensor_loc/**/*.parquet', hive_partitioning = true)
WHERE sensor_type = 'temperature' AND location = 'room_a';

1000 万行的结果：

最后一行值得单独说一下：全量聚合几乎没有提升，因为分区键上没有过滤条件，12 个分区文件全得读。分区只在"能砍掉数据"的时候有价值，砍不掉就只剩开销。

砍掉多少，收益就有多少。第二行只读 12 个文件里的 1 个，理论加速比是 12 倍，实测 7 倍，剩下 5 倍被打开文件、解析元数据的固定成本吃掉了。

数据量越大，分区收益越高

同一套查询在 100 万行和 1000 万行上各跑一遍：

100 万行的时候，双键分区只快 2.54 倍——数据总共才 5MB，全部读进内存也就几十毫秒，分区省下的那点 IO 意义不大。数据量涨 10 倍，加速比跟着涨。这说明一件事：数据量在千万行以下、文件本来就不大的表，分区带来的收益大概赶不上它增加的复杂度。

反过来：过滤条件不含分区键，分区更慢

这是踩坑的起点。查一段时间范围，条件里没有 sensor_type 和 location：

1
2
3

SELECT COUNT(*), AVG(value)
FROM read_parquet('by_sensor_loc/**/*.parquet', hive_partitioning = true)
WHERE ts >= TIMESTAMP '2024-02-01' AND ts < TIMESTAMP '2024-02-08';

分区表慢了一半到一倍。原因是读取端要去列 12 个文件、打开 12 个文件、各读一段，而数据该读的还是全读。多出来的全是固定成本。

很多文档把这条写成"分区只对分区键有效"，说得太轻了。实际情况是它会主动变慢，不是没效果而已。

文件数量：从 12 个到 5825 个，收益直接翻负

分区粒度的选择比"分不分区"更关键。按天分区看起来很自然，一年就是 365 个目录，但如果表里有多个维度，或者按小时、按 ID 分，文件数会失控。

DuckDB 写分区时，每个目录每个线程会写一个文件（官方文档原话：“Currently, one file is written per thread to each directory”）。我用 1000 个 bucket 分区写 1000 万行，最后统计出 18549 个文件——远超分区数，因为某些分区被拆成了二十多个小文件。

几组文件数量的对照测试，都在 1000 万行数据上：

第三行是最能说明问题的一条：EXPLAIN 里明明白白写着 Scanning Files: 1/5825，只读了一天、两万多行数据，结果还是比直接扫 55MB 的单文件慢 5.7 倍。

1
2
3
4
5
6
7
8

┌───────────────────────────┐
│        READ_PARQUET       │
│     Projections: value    │
│       File Filters:       │
│  (d = '2024-02-01'::DATE) │
│      Scanning Files:      │
│           1/5825          │
└───────────────────────────┘

裁到 1/5825 都没救。原因是裁剪发生在规划阶段之后——得先把 5825 个文件的路径列出来、读每个文件的 footer、比对统计信息，这些开销和读数据无关，只和文件数量成正比。这批文件一共 5825 个，其中 5668 个不到 20KB，元数据加起来的量级已经和数据本身相当了。

第四行更惨：过滤条件套了个 CAST(ts AS DATE)，分区列 d 上没了可直接比较的谓词，裁剪直接失效，5825 个文件全部打开，1.6 秒。同一个查询，只是过滤写法不同，差了 3.4 倍。

所以文件数量的红线，比大多数人想的低得多。这套测试里，12 个文件是甜的，5000 个以上基本等于自残。经验值是：分区目录控制在几十到几百个，单个分区文件 100MB 往上。低于这个体量，分区省下的 IO 还不够赔元数据的钱。

换个思路：排序 + 行组统计，可能比分区更划算

既然分区的问题在文件数量，那有没有办法只享受裁剪、不承担文件开销？有——把单文件按查询列排好序。

Parquet 每个 row group（默认 12 万行左右）都存了列的 min/max 统计。数据乱序的时候，每个 row group 的 ts 都横跨全年，统计信息筛不掉任何东西。按 ts 排序之后，row group 的 ts 区间互不重叠，范围查询可以直接跳过整个 row group。

1
2

-- 按查询列排序后落盘
COPY (SELECT * FROM t ORDER BY ts) TO 'flat_sorted.parquet' (FORMAT PARQUET);

1000 万行，查某一个月：

排序后的单文件，比月度分区还快一点，而且只有一个文件、体积小 19%（排序后相邻行重复度高，压缩率更好）。分区能做的事，排序 + 行组统计基本都能做，还不用管目录结构和 hive_partitioning 参数。

顺序也有讲究：一次排序只能照顾一种查询模式。按 ts 排序，按 sensor_type 过滤就没好处了。真实场景里最常出现的过滤维度是时间，所以按时间排序通常是最稳的选择。两个维度都要，那才是分区该上场的时候。

已经拆成几千个文件，怎么收拾

分区一旦写坏，救回来的办法就是重写一遍——把一堆小文件读出来，排序，落成一个（或少量几个）大文件：

1
2
3
4
5

COPY (
  SELECT sensor_id, sensor_type, location, ts, value
  FROM read_parquet('by_day/**/*.parquet', hive_partitioning = true)
  ORDER BY ts
) TO 'compact.parquet' (FORMAT PARQUET);

1000 万行、5825 个小文件，整个重写耗时 6.33 秒，产出 55.84 MB 的单个文件。重写之后查同一个月份：

重写前后行数校验都是 10,000,000。一次 6 秒的离线操作，换来 230 倍的查询提速，这笔账怎么算都值。

如果分区确实要留，就重写成"少量分区 + 每分区内排序"：分区数控制在几十个，每个分区文件重写成一个大的有序文件。这样既保住了目录语义，又不至于被碎片拖死。

什么时候分区，什么时候不分区

测完这些，我给自己的规则是这样的：

分区层级也别贪多。两级是上限，三级以上文件数量是乘法增长的：3 类 × 4 房间 × 365 天 = 4380 个目录，光打开就要命。

踩坑记录

hive_partitioning 不是必须写，但别写成 false

调研资料里普遍说"必须加 hive_partitioning=true，否则列不存在"，在 DuckDB 1.5.5 上已经不成立了。这个参数默认是 auto-detected，实测不写参数也能读到分区列：

1
2

SELECT COUNT(*) FROM read_parquet('by_sensor_loc/**/*.parquet');           -- 正常，10000000
SELECT COUNT(*) FROM read_parquet('by_sensor_loc/**/*.parquet', hive_partitioning = false);  -- 也能跑

区别在列清单。加 false 之后分区列直接消失，WHERE sensor_type = ... 会报列不存在。所以写不写 true 都行，但千万别写 false。

分区列上套函数，裁剪就没了

1
2
3
4
5

-- 462 ms，裁剪生效
WHERE d >= DATE '2024-02-01' AND d < DATE '2024-03-01'

-- 1610 ms，裁剪失效
WHERE CAST(ts AS DATE) >= DATE '2024-02-01' AND CAST(ts AS DATE) < DATE '2024-03-01'

谓词必须直接落在分区列上。需要按年-月分区，就在导出前用 SELECT 造出 ym 列再分区，别指望读的时候现算：

1
2

COPY (SELECT *, strftime(ts, '%Y-%m') AS ym FROM t)
TO 'by_month' (FORMAT PARQUET, PARTITION_BY (ym), OVERWRITE_OR_IGNORE);

PARTITION_BY 不接受表达式

PARTITION_BY (strftime(ts, '%Y-%m')) 会报错，必须是列名。官方文档写得很清楚，顺手记一下省得撞墙。

分区键是 NULL 会生成 __HIVE_DEFAULT_PARTITION__

导出后检查目录名就知道有没有漏网的数据：

1
2

k=__HIVE_DEFAULT_PARTITION__
k=a

脏数据混进默认分区之后，后续所有按分区键的过滤都会漏掉这一部分。导出前先过滤：

1
2

COPY (SELECT * FROM t WHERE sensor_type IS NOT NULL)
TO 'clean' (FORMAT PARQUET, PARTITION_BY (sensor_type));

分区表是只读的，改数据得整块重写

read_parquet() 返回的不是表，UPDATE read_parquet('...') 直接语法错误。分区数据集本质是一堆文件的集合，没有事务、没有行级更新。要改一个分区，只能把那个分区重算一遍重写。如果业务有频繁更新需求，分区 Parquet 不合适，该回到 DuckDB 自己的表文件或者带 upsert 的存储。

参考资源

• DuckDB Partitioned Writes - 分区写入语法，含"每目录每线程一个文件"的说明
• DuckDB Hive Partitioning - hive_partitioning 参数与 PARTITION_BY 限制
• DuckDB Parquet Overview - read_parquet 全部选项，含 hive_partitioning 默认 auto-detected
• MotherDuck: Partitioned Writes in DuckDB - 分区写入的实践建议

现在再有人问"要不要分区"，我的答案是先看两件事：数据量过千万了吗，查询条件里有没有低基数维度。两个都是"是"，分区，两级以内。有一个不是，先试试按查询列排序的单文件——很多时候它更快，还更好维护。

至于日分区那种一天一个目录的做法，数据量不大就别碰。5825 个文件里挑 1 个来读，最后还是比直接扫整个文件慢，这事儿挺反直觉的，但测试数据就摆在那儿。