用维度表加速字符串聚合-未完纪

分析型工作负载中，经常会遇到反复出现的字符串：产品名称、国家名称、车站名称、用户代理字符串，以及各种分类标签。以 DuckDB 的公开列车服务数据集为例，其中每一行都对应荷兰铁路列车的一次停站记录。数据来自 Rijden de Treinen（列车在运行吗？）应用发布的开放数据集。直接使用数据集 URL 就能查询：

SELECT departure_time, station_name, type
FROM 'https://blobs.duckdb.org/train_services.parquet'
LIMIT 5;

departure_time	station_name	type
2023-05-15 00:00:00	Rotterdam Centraal	Intercity
2023-05-15 00:13:00	Delft	Intercity
2023-05-15 00:29:00	Den Haag HS	Intercity
2023-05-15 00:45:00	Leiden Centraal	Intercity
2023-05-15 01:03:00	Schiphol Airport	Intercity

这张表有 380,959 行，却只有 537 个不同的车站名称。因此，同一个名称会在数千行中反复存储：仅 Amsterdam Centraal 这个占 18 字节的名称，就出现了 7,591 次。可以在 Web Shell 或 DuckDB CLI 中，将数据加载成表，跟着下面的示例操作：

CREATE TABLE train_services AS
    FROM 'https://blobs.duckdb.org/train_services.parquet';

按车站名称执行 GROUP BY 时，DuckDB 必须为每一行处理完整的字符串。例如，下面的查询统计每个车站有多少次列车停靠：

SELECT station_name, count(*) AS calls
FROM train_services
GROUP BY station_name;

假设只看几个在两个车站停靠的记录，分组时需要做的工作如下：

行	station_name	按 `station_name` 分组时的工作
1	Amsterdam Centraal	对 18 字节求哈希；创建新分组，将 18 字节复制进哈希表
2	Rotterdam Centraal	对 18 字节求哈希；创建新分组，将 18 字节复制进哈希表
3	Amsterdam Centraal	对 18 字节求哈希；分组已存在，比较 18 字节
4	Amsterdam Centraal	对 18 字节求哈希；分组已存在，比较 18 字节
5	Rotterdam Centraal	对 18 字节求哈希；分组已存在，比较 18 字节

即使这里只涉及两个不同的车站，整张表也只有 537 个车站，这些字符串处理工作仍会对每一行重复执行。

下面介绍如何给每个不同的字符串分配一个编号，将上述工作转移到小整数上，并在查询的最后才查回字符串。

背景

这种方法来自数据仓库中的星型模式，这里将它用于改善查询性能。我们在研究一份高基数分组占用大量内存的用户报告时讨论到了这个思路。

后来发现，那份报告并不涉及字符串。不过 Richard Wesley 指出，在他自己的工作中，先建立维度表、最后再通过连接取回字符串，能显著改善大量涉及字符串的聚合。

我们随后将这一模式加入了性能指南的模式设计章节。

为什么按字符串分组成本较高

DuckDB 使用哈希聚合执行 GROUP BY，为每个分组保留一条哈希表记录。这一机制在 2022 年的文章《DuckDB 中的并行分组聚合》中有所介绍。聚合先根据每个分组键的哈希值找到哈希表中的槽位，再将这个键与槽位里存储的键比较。对整数来说，这只需要少量 CPU 指令；对字符串来说，成本会随字符串长度增长。

在 DuckDB 中，一个字符串值使用16 字节的结构表示。不超过 12 字节的字符串直接内联存储；更长的字符串则存储一个 4 字节前缀，以及指向实际字符的指针。这种设计让短字符串的处理成本较低，但现实中的许多标签都超过了 12 字节。

对于这些较长的字符串，求哈希需要读取每一行中每个字符串的全部字节。仅靠前缀无法确认字符串是否相等，因此 DuckDB 还需要沿着指针读取并比较完整字符串。出现新分组时，字符串会被复制到哈希表自己的内存中，因而哈希表会比采用固定宽度键时更大。

字符串复制也会影响内存使用。更宽的哈希表更难装进 CPU 缓存；当聚合所需的数据量超过内存容量时，它会更早触及内存限制，并需要将更多数据溢写到磁盘。DuckDB 确实会对磁盘上的字符串列应用字典编码，2022 年的《DuckDB 中的轻量级压缩》介绍了这一点。但这是存储优化：列被读入聚合后，每个分组键又是完整的字符串。

整数键可以避免这些成本，因为它们的宽度固定，求哈希和比较都很便宜。键值范围较小时，DuckDB 甚至可以完全省去哈希计算：如果统计信息表明，键值落在足够小的取值范围内，优化器就会选择完美哈希聚合。这一行为由 perfect_ht_threshold 设置控制，直接把键值当作数组索引使用。

建立采用有序窄键的维度表

下面的示例基于前面加载的 train_services 表。每一行是一条停站记录，包含 service_id、date、服务 type、train_number、station_code、station_name、departure_time 和 arrival_time。需要编码的重复字符串是 station_name。

第一步：测量基数

先查出这一列有多少个不同的值，因为这个数量决定了键可以有多窄。

SELECT count(DISTINCT station_name) AS num_stations
FROM train_services;

查询返回 537。这个数量决定了键需要使用多宽的整数类型。应当选择能够覆盖所有不同值的最窄整数类型，因为键越窄，事实表每行占用的字节数就越少，分组哈希表中的记录也越小。

键由 row_number() 产生。它从 1 开始，只向上计数，因此无符号类型更合适，不必将一部分取值范围用于负数。UTINYINT 占 1 字节，最多容纳 255 个不同值；USMALLINT 占 2 字节，最多容纳 65,535 个；UINTEGER 占 4 字节，最多容纳约 43 亿个。

537 个车站名称无法放进 UTINYINT，所以 USMALLINT 是满足要求的最窄类型，下一步就会转换成这一类型。如果不同值的数量还会增长，应选择更大的类型，避免耗尽可用的键。

第二步：建立维度表

接下来，为每个不同的字符串分配一个整数键。这里的重要细节，是窗口函数中的 ORDER BY station_name：键按字符串顺序分配。

CREATE OR REPLACE TABLE stations AS
    SELECT
        station_name,
        (row_number() OVER (ORDER BY station_name))::USMALLINT AS station_id
    FROM (SELECT DISTINCT station_name FROM train_services WHERE station_name IS NOT NULL);

有序键有两个优点。首先，ORDER BY station_id 得到的顺序与 ORDER BY station_name 相同，因此可以改为对成本更低的整数排序。其次，键的分配是确定的：使用相同数据重建维度表，会得到相同的键。

第三步：在事实表中存储键

最后，把事实表中的字符串列替换成对应的键。这需要重写一次表，成本只支付一次。维度表没有包含 NULL，所以这里采用 LEFT JOIN，保留没有车站的行，并给它们一个 NULL 键。

CREATE OR REPLACE TABLE train_services_encoded AS
    SELECT ts.* EXCLUDE (station_name), s.station_id
    FROM train_services ts
    LEFT JOIN stations s USING (station_name);

stations 维度表只存储一次每个名称，并按字母顺序编号：

station_id	station_name
1	's-Hertogenbosch
2	's-Hertogenbosch Oost
3	't Harde
4	Aachen Hbf

事实表现在存储的是 2 字节的 USMALLINT 键，按它分组的成本就较低：

行	station_id	按 `station_id` 分组时的工作
1	28	对 2 字节求哈希；创建新分组，存储 2 字节
2	403	对 2 字节求哈希；创建新分组，存储 2 字节
3	28	对 2 字节求哈希；分组已存在，比较 2 字节
4	28	对 2 字节求哈希；分组已存在，比较 2 字节
5	403	对 2 字节求哈希；分组已存在，比较 2 字节

键的顺序与字符串顺序一致：Amsterdam Centraal 排在 Rotterdam Centraal 前面，因此它的键也较小，分别为 28 和 403。对于这样小而密集的键值范围，DuckDB 可以使用完美哈希聚合，直接按键值索引，而无需计算哈希。

也可以省略这一步，在每次查询中临时连接维度表。这仍然能让聚合的哈希表保持较窄，但每个查询都需要在连接时对字符串求一次哈希。在事实表中存储键，则能将字符串处理移出查询。

查询编码后的表

有了键以后，查询先在整数上聚合，等结果集缩小后才查回字符串。这里的 LEFT JOIN 会保留没有车站的那一组记录。

WITH rollup AS (
    SELECT
        station_id,
        date,
        count(*) AS calls
    FROM train_services_encoded
    GROUP BY ALL
)
SELECT s.station_name, rollup.* EXCLUDE (station_id)
FROM rollup
LEFT JOIN stations s USING (station_id)
ORDER BY station_id, date;

GROUP BY ALL 按所有未参与聚合的已选列分组，这里就是 station_id 和 date，因此不必再重复列出它们。最后的连接针对聚合结果执行：每个分组只有一行，而不是每个事件一行。如果聚合将十亿行缩减为几十万行，连接就只需查回几十万个字符串。由于键已经排序，ORDER BY station_id 同时也会按车站名称的字母顺序排序。

对于取前 N 项的查询，应在连接之前使用 LIMIT，这样下面的查询只需要查回十个字符串：

WITH top_stations AS (
    SELECT station_id, count(*) AS calls
    FROM train_services_encoded
    GROUP BY station_id
    ORDER BY calls DESC
    LIMIT 10
)
SELECT s.station_name, top_stations.calls
FROM top_stations
LEFT JOIN stations s USING (station_id)
ORDER BY calls DESC;

station_name	calls
Utrecht Centraal	7663
Amsterdam Centraal	7591
Zwolle	5013
Schiphol Airport	4961
Amsterdam Sloterdijk	4854
…	…

如果要按字符串筛选，先在维度表中查出它的键，再按整数键筛选事实表。

SELECT count(*)
FROM train_services_encoded
WHERE station_id IN (SELECT station_id FROM stations WHERE station_name LIKE '%Centraal%');

衡量效果

能获得多大收益，取决于数据本身，主要是字符串长度和不同值的数量。在这个约 380,000 行的样本中，差异较小；随着行数和字符串长度增加，差异会扩大。要了解自己的数据是否适合，可以用两种方式执行相同的聚合并比较：

.timer on

-- Group on the string
SELECT station_name, count(*) AS calls
FROM train_services
GROUP BY station_name;

-- Group on the key, then join the strings back
WITH rollup AS (
    SELECT station_id, count(*) AS calls
    FROM train_services_encoded
    GROUP BY station_id
)
SELECT s.station_name, rollup.calls
FROM rollup
LEFT JOIN stations s USING (station_id);

要看时间花在哪里，可以给每个查询加上 EXPLAIN ANALYZE，比较聚合算子的耗时。要比较内存使用，可以通过 SET 将 memory_limit 调低，然后观察哪个查询更早开始将数据溢写到磁盘。

其他用法

前面的示例使用一个字符串列，并假定其取值集合固定。下面介绍手工维度表的一种内置替代方案、多个字符串列的编码，以及新数据到来后如何保持键的更新。

与 ENUM 比较

DuckDB 的 ENUM 类型将字典编码内置到了类型系统中：值存成小整数，整数的宽度由 DuckDB 自动选择。2021 年的文章《Lord of the Enums》对此做了基准测试：在 ENUM 列上执行 GROUP BY，比直接按原始字符串分组更快。可以使用查询创建一个枚举类型：

CREATE TYPE station_enum AS ENUM (
    SELECT DISTINCT station_name FROM train_services WHERE station_name IS NOT NULL ORDER BY station_name
);

如果取值集合事先已知，而且很少变化，ENUM 能提供大部分收益，也不需要额外连接。以下情况则更适合维度表：

不断有新值出现。 ENUM 的取值在创建类型时就固定了，插入未知值会失败；维度表则可以继续增长。
需要附加属性。 维度表可以携带额外的列，例如车站所在城市或所属线路。可以直接按这些属性分组或筛选，无需解析字符串。
数据会离开 DuckDB。 整数键和查找表可以导出为 Parquet 或 CSV，并在其他工具中使用。

多个字符串列

这种模式可以分别应用于每一列：为每个重复程度较高的字符串列建立一张维度表。在这个数据集中，station_name 和服务 type 都符合条件。每个键分别采用适合该列基数的整数类型，而查询只连接取回自己需要的维度。type 列只有 15 个不同值，因此它的键可以放进 UTINYINT；station_name 仍然需要 USMALLINT：

CREATE OR REPLACE TABLE service_types AS
    SELECT
        type,
        (row_number() OVER (ORDER BY type))::UTINYINT AS type_id
    FROM (SELECT DISTINCT type FROM train_services WHERE type IS NOT NULL);

CREATE OR REPLACE TABLE train_services_encoded AS
    SELECT ts.* EXCLUDE (station_name, type), s.station_id, t.type_id
    FROM train_services ts
    LEFT JOIN stations s USING (station_name)
    LEFT JOIN service_types t USING (type);

事实表现在同时携带两个键，查询只需连接它读取的那些维度。统计每个车站的停靠次数需要 stations，而按服务类型细分则需要 service_types。

如果两列总是一起出现，例如 station_code 和 station_name，通常可以使用一张按两者组合建键的维度表，结构会更简单。事实表也只需要存一个键而不是两个，因此还能进一步缩窄。

保持维度表更新

新数据到来时，把尚未出现过的字符串加入维度表，新键从当前最大键之后继续分配：

INSERT INTO stations
    SELECT
        n.station_name,
        ((SELECT max(station_id) FROM stations)
            + row_number() OVER (ORDER BY n.station_name))::USMALLINT AS station_id
    FROM (SELECT DISTINCT station_name FROM new_train_services WHERE station_name IS NOT NULL) n
    ANTI JOIN stations USING (station_name);

ANTI JOIN 只保留尚未存在于 stations 中的名称，因此已有键保持不变，每个真正新增的名称会得到一个接在当前最大键之后的新键。

接着，采用第三步中的方式编码新行，并追加进去：

INSERT INTO train_services_encoded
    SELECT n.* EXCLUDE (station_name), s.station_id
    FROM new_train_services n
    LEFT JOIN stations s USING (station_name);

追加的键不再遵循字母顺序。如果查询依赖 ORDER BY station_id 与 ORDER BY station_name 的顺序一致，就应定期重建维度表并重新给事实表分配键，或者在最后一次连接之后按字符串排序。

窄键不会减少分组数量

字典编码能够缩小每个分组，却不会减少分组数量。对基数特别高的聚合来说，这一点很重要。

引出这篇文章的报告 duckdb/duckdb#14584 就说明了这一点：它把 92 亿行聚合成 3.2 亿个不同的分组，使用的内存远超预期。分组键已经是 UBIGINT，因此没有字符串可以再编码。

原因在于 DuckDB 的并行聚合方式。每个线程先在自己的线程本地哈希表中聚合分配给它的行，最后再合并部分结果。如果每个线程都会看到大量相同分组的重复记录，这种方式很有效，而现实数据通常就是这种情况。

但在这份报告中，使用 8 个线程时，每个线程大约处理 11 亿行，仅为不同值数量的约 3 倍，而且这些值的分布没有有用的规律。几乎每个线程的哈希表里都出现了几乎所有分组，因此内存使用量接近“线程数 × 分组数量”。

窄键可以让这些记录中的每一条变小，但不能防止重复。分组数量接近每个线程处理的行数时，减少 threads 会更有帮助，因为每个线程都保存着每个分组的一份副本。

SET threads = 1;

这是用速度换内存，因此只应在聚合否则会耗尽内存、或需要向磁盘溢写大量数据时使用。如果数据按分组键聚集，也会有所帮助，因为每个线程看到的分组集合会更小，彼此之间的差异也更大。

这种模式不适合哪些情况

这种模式会让表结构和查询更复杂，因此并非总是值得采用。

以下情况不适合使用：

字符串很短。 不超过 12 字节的值已经内联存储，与整数键之间的成本差距小得多。
列中的值几乎都不同。 如果大部分值都不同，例如标识符或自由文本，维度表的行数就会接近事实表，能节省的空间很少。
只查询一次。 建立维度表并重写事实表，需要完整扫描一次数据；只有反复查询时，这笔成本才值得支付。
不按该列聚合。 如果始终只是按字符串筛选或显示字符串，收益就比较有限。

在这些情况下，保留原来的字符串列即可。如果不确定，可以按“衡量效果”中的方法比较两个版本。

结论

按重复字符串分组的成本较高。把这些字符串移到一张使用有序窄整数键的小型维度表中，DuckDB 就可以在固定宽度的整数上聚合，并让哈希表保持紧凑。字符串在最后一次连接时取回，而那时的结果集已经较小。

反复按较长、不同值较少的字符串聚合时，这种方法最有帮助。性能指南中也有这一技巧的精简版本。

原文：Faster String Aggregations with Dimension Tables。作者：DuckDB Team。发布日期：2026 年 10 月 2 日。官方仓库原文：duckdb/duckdb-web。仓库采用 MIT 许可证，以下保留完整许可声明。

Copyright 2018-2025 Stichting DuckDB Foundation

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

文章版权归作者所有，未经允许请勿转载。

THE END