2026-10-02 — DuckDB 長字串 GROUP BY 改用維度表與窄整數鍵

DuckDB 官方：長字串 GROUP BY 改用維度表與窄整數鍵

DuckDB 官方部落格 · 2026-10-02

與其直接對長字串做 GROUP BY，不如先把字串搬進小型維度表，聚合時只比對窄整數鍵，最後再 join 回字串。這是 DuckDB 團隊在官方部落格上整理的純 SQL 改寫手法，不需要升級版本或開任何設定。

範例使用荷蘭鐵路公開資料集 train_services，共 380,959 列，卻只有 537 個不同的車站名稱。

原本的問題

DuckDB 的 GROUP BY 走 hash 聚合，每一列都要對分組欄位算 hash、再與 hash table 內既有的鍵比對。字串越長，這兩步成本越高。

文中指出，超過 12 bytes 的字串需要透過指標間接讀取，複製進 hash table 也會增加記憶體占用，並降低 CPU cache 的使用效率。「Amsterdam Centraal」是 18 bytes，在資料集裡重複出現數千次，每次都要付一次這樣的成本。

核心做法

步驟分三段：先量基數，再建維度表，最後把事實表的字串欄位換成整數鍵。鍵依字串排序編號，所以結果可重現。

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);

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);

查詢時先在整數鍵上聚合，再 join 字串回來，所以字串只會被讀取「分組數」次，而不是「列數」次：

	舊寫法	新寫法
分組鍵	`station_name`（字串）	`station_id`（`USMALLINT`）
字串出現時機	每一列都參與 hash 與比對	只在聚合後 join 一次

鍵的型別依基數挑選：UTINYINT 1 byte、可放 255 個值；USMALLINT 2 bytes、可放 65,535 個值，537 個車站就用這個；UINTEGER 4 bytes，約 43 億個值。文中另一個欄位 type 只有 15 個不同值，用 UTINYINT 即可。

ENUM 還是維度表

DuckDB 的 ENUM 型別能達到類似效果，語法也更短。文中建議在新值會持續出現、需要附加屬性、或資料要匯出時改用維度表。

新值進來時，用 ANTI JOIN 只把尚未存在的字串補進維度表，鍵從 max(station_id) 往後接，再用同一張維度表編碼新批次的事實列，既有鍵不會變動。

影響範圍

最直接受益的是對低基數長字串欄位反覆做彙總的人，例如依車站、國家、事件類型、主機名稱出報表的 DuckDB 使用者。要先檢查的是兩個數字：欄位的 count(DISTINCT ...)，以及字串是否超過 12 bytes。

文中明列不適用的情況：

字串很短（12 bytes 以內，直接內嵌儲存）。
基數接近全部唯一。
資料只查詢一次，編碼的成本攤不回來。
字串只用於過濾或顯示，不參與聚合。

另外要注意，窄鍵只縮小 hash table 每個項目的大小，不會減少分組數量。文中引用 issue #14584，該案例是 92 億列產生 3.2 億個分組，換成整數鍵仍然吃大量記憶體，文中提到的折衷是設 threads = 1，以速度換記憶體。

文章沒有公布加速倍率，只提供 .timer on 與 EXPLAIN ANALYZE 的比較方式，以及用 SET memory_limit 比較記憶體的建議。要不要採用，得用自己的資料量一次。

原始來源：DuckDB 官方部落格、duckdb/duckdb#14584、DuckDB Performance Guide

End of article