ClickHouse MergeTree 与物化视图预聚合实践

---

title: ClickHouse MergeTree 与物化视图预聚合实践

keywords: MergeTree, AggregatingMergeTree, materialized view, countState, finalizeAggregation

description: 使用 MergeTree 存储明细并通过物化视图写入 AggregatingMergeTree 预聚合，提高查询性能并降低资源消耗。

tags:

• AggregatingMergeTree
• ClickHouse
• MergeTree
• countState
• finalizeAggregation
• materialized view
• 数据库
• 物化视图

categories:

• 文章资讯
• 编程技术

---

创建明细与聚合表：

CREATE TABLE events (
  ts DateTime,
  uid UInt64,
  category LowCardinality(String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (ts, uid);

CREATE TABLE events_agg (
  month Date,
  category LowCardinality(String),
  cnt AggregateFunction(count)
) ENGINE = AggregatingMergeTree()
PARTITION BY month
ORDER BY (month, category);

创建物化视图做预聚合：

CREATE MATERIALIZED VIEW mv_events_agg 
TO events_agg AS
SELECT toStartOfMonth(ts) AS month, category, countState() AS cnt
FROM events
GROUP BY month, category;

查询聚合结果：

SELECT month, category, finalizeAggregation(cnt) AS total
FROM events_agg
ORDER BY month DESC, category
LIMIT 10;