ClickHouse 查询从 8 秒到 200ms：我用分区键 + 物化视图改写了数据模型

iceveil

297人浏览 · 2026-06-06 09:06:36

iceveil · 2026-06-06 09:06:36 发布

ClickHouse 查询从 8 秒到 200ms：我用分区键 + 物化视图改写了数据模型

上周被一张 8 亿行的表搞崩溃了。

不是 OOM，不是磁盘满了，就一个聚合查询跑了 8 秒，PM 在群里疯狂艾特我。那张表存的是用户行为日志，每天灌进去 3000 万条，半年下来 8 亿。业务方要查"最近 7 天每个渠道的新增用户数"，8 秒。加索引？ClickHouse 压根不玩 B+Tree 那一套。我折腾了两天，最后靠分区键 + 物化视图把查询压到了 200ms。

今天把踩的坑和改写的全过程记下来，代码可以直接复制跑。

问题现场：一个聚合查询把集群 CPU 打满

先说表结构。当时为了"通用"，设计了一张大宽表：

CREATE TABLE user_events (
    event_id UInt64,
    user_id UInt64,
    event_type String,
    channel String,
    country String,
    event_time DateTime,
    properties String
) ENGINE = MergeTree()
ORDER BY event_id;

看起来人畜无害对吧？ORDER BY event_id 是为了保证唯一性，但业务查询几乎全是按 event_time 和 channel 过滤的。业务方的 SQL 长这样：

SELECT 
    channel,
    countDistinct(user_id) AS uv
FROM user_events
WHERE event_time >= now() - INTERVAL 7 DAY
  AND event_type = 'register'
GROUP BY channel;

8 秒。EXPLAIN 一看，全表扫描。ClickHouse 的 MergeTree 引擎是按主键排序存储的，但 event_id 是随机递增的，跟时间完全无关。所以虽然 SQL 写了 WHERE event_time >= ...，引擎却没法跳过数据块，只能把 8 亿行翻一遍。

我当时还傻乎乎地加了 event_type 的跳数索引：

ALTER TABLE user_events 
ADD INDEX idx_event_type event_type TYPE bloom_filter GRANULARITY 3;

屁用没有。跳数索引适合高基值的等值查询，但这里瓶颈是时间范围过滤。索引只是让你少读一点数据块，分区键才是直接跳过整个分区。

第一刀：分区键把时间过滤变成"直接跳过"

ClickHouse 的分区键决定了数据在磁盘上的目录结构。如果按 toYYYYMMDD(event_time) 分区，查询 event_time >= '2026-05-30' 时，引擎可以直接跳过 20260529 及之前的目录，根本不用打开文件。

我重建了表：

CREATE TABLE user_events_v2 (
    event_id UInt64,
    user_id UInt64,
    event_type LowCardinality(String),
    channel LowCardinality(String),
    country LowCardinality(String),
    event_time DateTime,
    event_date Date DEFAULT toDate(event_time),
    properties String
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(event_date)
ORDER BY (event_type, channel, event_time, user_id);

几个关键改动：

PARTITION BY toYYYYMMDD(event_date)：按天分区。7 天的查询只需要扫 7 个分区，而不是 180 个。
ORDER BY (event_type, channel, event_time, user_id)：把业务查询的过滤条件放在主键前面。event_type 和 channel 是高频过滤字段，放前面能让数据块快速裁剪。
LowCardinality(String)：渠道和事件类型基值不高，用 LowCardinality 能省 30% 磁盘空间，查询时还能走字典优化。
event_date 冗余字段：避免 toDate() 重复计算，分区键直接用它。

数据迁移用了 INSERT ... SELECT：

INSERT INTO user_events_v2
SELECT 
    event_id, user_id, event_type, channel, country, 
    event_time, toDate(event_time), properties
FROM user_events;

迁移完同一个查询跑了 1.2 秒。从 8 秒到 1.2 秒，已经能交差了，但我不满足。

第二刀：物化视图把聚合结果"预计算"

1.2 秒还是慢。问题出在 countDistinct(user_id) 上。8 亿行里算去重，即使是 ClickHouse 的 uniqExact 优化也扛不住。而且业务方查"最近 7 天"是固定需求，没必要每次重算。

ClickHouse 的物化视图（Materialized View）可以自动把增量数据聚合到另一张表里。我建了一张聚合表：

CREATE TABLE user_events_agg (
    event_date Date,
    event_type LowCardinality(String),
    channel LowCardinality(String),
    uv AggregateFunction(uniq, UInt64),
    total_events UInt64
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMMDD(event_date)
ORDER BY (event_date, event_type, channel);

AggregateFunction(uniq, UInt64) 是 ClickHouse 的预聚合类型，存的是 HyperLogLog 状态，不是最终结果。查询时再用 uniqMerge(uv) 合并，精度足够，速度飞快。

然后创建物化视图，让它自动从原始表摄取数据：

CREATE MATERIALIZED VIEW user_events_mv
TO user_events_agg
AS SELECT
    toDate(event_time) AS event_date,
    event_type,
    channel,
    uniqState(user_id) AS uv,
    count() AS total_events
FROM user_events_v2
GROUP BY event_date, event_type, channel;

关键点：TO user_events_agg 指定了目标表，物化视图不会自己创建隐式表，而是把增量数据直接写进 user_events_agg。这样查询和存储都可控。

但物化视图只处理增量数据，历史数据不会自动回填。我手动刷了一遍：

INSERT INTO user_events_agg
SELECT
    toDate(event_time) AS event_date,
    event_type,
    channel,
    uniqState(user_id) AS uv,
    count() AS total_events
FROM user_events_v2
GROUP BY event_date, event_type, channel;

刷完历史数据后，业务查询改成查聚合表：

SELECT 
    channel,
    uniqMerge(uv) AS uv
FROM user_events_agg
WHERE event_date >= now() - INTERVAL 7 DAY
  AND event_type = 'register'
GROUP BY channel;

200ms。稳定 200ms，无论查 7 天还是 30 天。

踩坑记录：三个差点让我翻车的细节

坑 1：SummingMergeTree 在后台合并不是实时的

SummingMergeTree 会在后台合并分区时做聚合，但查询时如果数据还没合并，会返回未聚合的多行。解决办法：查询时永远加 FINAL 关键字，或者干脆用 AggregatingMergeTree + uniqMerge。

我最后换成了 AggregatingMergeTree：

CREATE TABLE user_events_agg (
    event_date Date,
    event_type LowCardinality(String),
    channel LowCardinality(String),
    uv AggregateFunction(uniq, UInt64),
    total_events SimpleAggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMMDD(event_date)
ORDER BY (event_date, event_type, channel);

AggregatingMergeTree 在查询时会自动合并聚合状态，不需要 FINAL，更可靠。

坑 2：物化视图 TO 语法和隐式存储表的区别

早期版本物化视图不加 TO 会自动创建一张 .inner 表，表结构不可控。加 TO 指向显式表后，可以自己定义分区键、排序键和引擎类型。生产环境一定要用 TO。

坑 3：分区粒度过细导致文件爆炸

我一开始试过 PARTITION BY toYYYYMM(event_date)（按月分区），但单分区 2.5 亿行，合并压力大。后来改成按天分区，每个分区 3000 万行，合并和查询都舒服。ClickHouse 官方建议每个分区 100 万 - 1000 万行，但我测试下来 3000 万行也没问题，看集群规模。

效果对比：一张表说清楚

方案	查询时间	扫描数据量	适用场景
原始表（ORDER BY event_id）	8 秒	8 亿行	千万别这么干
优化主键 + 分区键	1.2 秒	2100 万行（7 天）	偶尔查询、灵活性高
物化视图预聚合	200ms	聚合后 200 行	固定报表、高频查询