在大型資料分析系統中,隨著資料規模與查詢複雜度提升,單純依賴 SELECT 聚合查詢(如 SUM、COUNT、AVG)將無法滿足即時回應的需求。ClickHouse 針對高效聚合查詢場景,提供了 AggregatingMergeTree 儲存引擎,透過預計算 (Pre-Aggregation) 與聚合函數壓縮 (AggregateFunction 型別),大幅降低查詢延遲。
AggregatingMergeTree 是 ClickHouse MergeTree 家族中的「聚合專用儲存引擎」,其特點是:
這種設計特別適合高頻寫入 + 聚合查詢的場景,如:網站流量統計、實時 KPI 指標儀表板、IoT 裝置資料彙總。
特性 | SummingMergeTree | AggregatingMergeTree |
---|---|---|
支援聚合函數 | 只支援 SUM | 支援所有 AggregateFunction (如 SUM, AVG, COUNT, MIN, MAX) |
資料儲存型別 | 普通數值欄位 (UInt, Float) | AggregateFunction 型別 (儲存聚合狀態) |
Merge 時是否會聚合 | 會針對 Primary Key 相同的數值欄位做加總 | 會將相同 Primary Key 的聚合狀態進行合併 |
查詢時是否需特殊處理 | 不需特殊處理,直接 SELECT 查詢即可 | 查詢時需使用聚合函數將狀態還原(如 sumMerge(col), avgMerge(col)) |
適用場景 | 計數器類型(PV、點擊數、金額彙總) | 複雜聚合運算(平均值、去重計數、分位數、標準差等統計需求) |
-- Source Table
CREATE TABLE visits (
StartDate DateTime64 NOT NULL,
CounterID UInt64,
Sign Nullable(Int32),
UserID Nullable(Int32)
) ENGINE = MergeTree ORDER BY (StartDate, CounterID);
-- AggregatingMergeTree Table
CREATE TABLE agg_visits (
StartDate DateTime64 NOT NULL,
CounterID UInt64,
Visits AggregateFunction(sum, Int32),
Users AggregateFunction(uniqExact, Int32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (StartDate, CounterID);
-- Materialized View Table
CREATE MATERIALIZED VIEW visits_mv TO agg_visits
AS SELECT
StartDate,
CounterID,
sumState(Sign) AS Visits,
uniqState(UserID) AS Users
FROM visits
GROUP BY StartDate, CounterID;
INSERT INTO visits (StartDate, CounterID, Sign, UserID)
VALUES (1667446031000, 1, 3, 4), (1667446031000, 1, 6, 3);
資料會同時寫入 visits
和 agg_visits
Table
SELECT
StartDate,
sumMerge(Visits) AS Visits,
uniqMerge(Users) AS Users
FROM test.visits_mv
GROUP BY StartDate
ORDER BY StartDate;
┌───────────────StartDate─┬─Visits─┬─Users─┐
│ 2022-11-03 03:27:11.000 │ 9 │ 2 │
└─────────────────────────┴────────┴───────┘
資料寫入 (AggregateFunction 狀態寫入)
sumState()
, avgState()
, uniqExactState()
等函數將資料寫入為「聚合狀態」。背景 Merge 操作 (State Merging)
查詢時還原聚合結果 (State Finalization)
sumMerge()
, avgMerge()
, uniqExactMerge()
等函數將聚合狀態還原為最終數值。應用場景 | 說明 |
---|---|
即時流量與使用者指標統計 (PV / UV 報表) | 大量寫入使用者行為事件,彙總統計每天每頁面瀏覽量與不重複訪客數 (uniqExact) |
IoT 裝置資料彙總與即時狀態統計 | 實時收集 IoT 感測器資料,進行彙總平均值、最大最小值、分位數計算 |
行為事件流去重與複雜指標計算 | 如多指標 KPI 彙總、Session Duration 平均值等需進階統計場景 |
資料層級預聚合 (Pre-Aggregation) | 針對查詢頻繁的聚合結果進行預先計算,減少查詢時的 CPU 負載與 I/O 操作 |
為了將聚合計算成本從 INSERT 時移至 Merge 階段,可以這麼做:
SET optimize_on_insert = 0;
CREATE MATERIALIZED VIEW visits_mv TO agg_visits
AS SELECT
StartDate,
CounterID,
initializeAggregation('sum', Sign) AS Visits,
initializeAggregation('uniqExact', UserID) AS Users
FROM visits;
透過以下兩個關鍵步驟:
initializeAggregation()
:將原始值轉為聚合狀態寫入,無需 Group By。optimize_on_insert = 0
:關閉插入時的自動聚合優化,將聚合推遲到後續 Merge 操作進行。可以帶來:
initializeAggregation
必須搭配 optimize_on_insert = 0
?當使用 initializeAggregation()
時,ClickHouse 會為每筆資料產生一個「尚未合併的 Aggregate State」,這讓每筆資料都能保留獨立的聚合狀態,直到 MergeTree 在背景進行合併 (Merge) 時,才會把相同 Primary Key 的資料進行實際聚合。
然而,這種「不經 Group By 預聚合」的行為,預設情況下 (optimize_on_insert = 1
) 是無法成立的,因為 ClickHouse 會自動優化將相同 Key 的資料提前聚合後才寫入 (insert-time pre-aggregation)。
只有當你將 optimize_on_insert
設為 0 時,ClickHouse 才會跳過這個 insert-time pre-aggregation 優化邏輯,將資料原封不動地寫入 AggregatingMergeTree,並將聚合計算延遲到 Merge 階段進行。
條件 | 行為 |
---|---|
initializeAggregation() + optimize_on_insert = 0 |
每筆來源資料都寫入為單獨的聚合狀態,查詢時需依賴背景 Merge 來獲得最終聚合結果 |
initializeAggregation() + optimize_on_insert = 1 |
ClickHouse 會自動進行 Group By 聚合後才寫入 |
傳統 sumState() + Group By 寫法 |
INSERT 時資料已經被預先聚合 |
Best Practice | 說明 |
---|---|
聚合狀態資料需搭配 State/ Merge 函數處理 | INSERT 時寫入 State,SELECT 時用 Merge 函數還原 |
Primary Key 設計決定 Merge 粒度 | ORDER BY 應設計為能唯一標識聚合維度 (如 date, page),避免去重無效或合併過大範圍 |
結合 Partition Key 進行資料區塊管理 | 減少 Merge 時的資源消耗,提高查詢裁剪效率 |
Materialized View 進行即時聚合計算 | 提升原始資料寫入到 AggregatingMergeTree 的實時性與性能 |
定期 OPTIMIZE FINAL 合併資料 | 確保合併後查詢結果的一致性與效能,避免過多小 Data Part 影響性能 |
適用場景 | 建議引擎選擇 |
---|---|
單純計數器類型 (如 PV/點擊數) | SummingMergeTree |
需計算不重複數量 (如 UV、去重計數) | AggregatingMergeTree + uniqExact |
需計算平均值、分位數等進階聚合指標 | AggregatingMergeTree |
資料補寫與修正較頻繁的場景 | AggregatingMergeTree |
個人覺得 AggregatingMergeTree 是處理「高頻寫入 + 複雜聚合統計」場景的強大工具。但內部真的很複雜 XD,知識點極多,當你在選用 MergeTree 引擎時,應當具備較深的相關知識,才能發揮很好的優化系統。
而透過 AggregateFunction 型別預儲存聚合狀態,搭配 MergeTree 的合併機制與 Materialized View 實時計算能力,能將查詢效能提升到毫秒級,極適合指標報表、流量監控、IoT 資料彙總等應用場景。
但也需留意 Primary Key 與 Partition 設計,才能讓聚合合併與查詢裁剪效能發揮到極致。