先说一个很多人问过我的问题实时看板的明细数据已经是亿级了加维度下钻一次要跑好几秒到底该不该上预聚合我的答案很直接只要你的报表是相对固定的维度组合、查询又足够频繁就必须上。否则每一条看板 SQL 都是在扫明细表全分区CPU 和内存迟早被拖垮。这篇文章想分享的是我把一套 ClickHouse 实时数据立方体从零搭起来再到平稳跑生产的完整过程。所谓数据立方体说白了就是把明细数据按业务维度预先计算成聚合状态查询时直接查小得多的状态表尽量不碰原始明细。在 ClickHouse 里实现这套东西的黄金组合是明细表 物化视图 AggregatingMergeTree 状态表。我会把表结构怎么建、物化视图怎么写、查询怎么加速、上线后踩了哪些坑一条条讲清楚。正在做实时数仓、实时大屏、流量分析或者经营报表的朋友这篇可以直接照着改自己的场景。1. 为什么必须建一层立方体暴力 GROUP BY 的代价到底有多高1.1 一次看板查询背后的完整账目假设你有一个经营看板事件明细表每天新增几亿行需要按 渠道×地区×操作系统 三个维度统计访问量、收入、UV。最省事的写法当然是直接查原表SELECT channel, region, os, count() AS visits, sum(revenue) AS revenue, uniqExact(user_id) AS uv FROM events WHERE event_date today() GROUP BY channel, region, os ORDER BY revenue DESC;这段 SQL 在数据量小的时候没有任何问题但到了亿级就会暴露三个很实际的问题每次刷新都等于全分区扫描。就算 ORDER BY 里有日期WHERE 只过滤一天时你仍然要把当天上亿行读出来、反序列化、再聚合。读取成本是固定的跟你要不要这些数字没关系。GROUP BY 哈希表会吃掉大量内存。亿级行数做去重统计内存轻松升到几个 GB 到十几个 GB。看板往往是十几个查询同时刷新并发一叠内存直接破顶。聚合结果几乎不复用。早上看、中午看、晚上看查的都是同样的渠道、地区、系统汇总值但每次都在重复算。算完就扔等于每天做好几次无用功。打个比方这就好比超市每天晚上关门后把货架全部重新数一遍而不是在每次入库时顺手更新库存台账。预聚合就是把这个顺手更新台账变成常态让所有查询直接查台账而不是翻整个仓库。1.2 数据立方体在 ClickHouse 里的真实形态很多人对数据立方体有畏难情绪觉得是个复杂的 OLAP 理论概念。落到工程上其实就两部分预聚合状态表把明细数据按固定维度组合预先算好查询直接查这张小表这是 Cube 的主体。现场上卷/下钻当业务临时要看更高层级的合计时用GROUP BY WITH ROLLUP或WITH CUBE在查询时生成小计不用建新表。我见过不少团队本来就有实时数仓却迟迟不做预聚合核心理由是ClickHouse 这么快还需要搞这些吗。快是相对的。单条 GROUP BY 确实能跑出毫秒级但那是建立在全内存、无并发竞争的理想条件下。真实看板是几十个查询同时打过来而且筛选条件各不相同没有预聚合层兜底再快的引擎也会被打到高延迟。建预聚合层本质上是用存储换时间多花一倍左右的存储换来查询时间下降一到两个数量级。这买卖绝大多数业务都划算剩下的问题只是怎么把这层建得干净、不出错也就是下面要展开的内容。2. 表结构建模明细表、状态表与维度顺序的设计细节2.1 明细表别过度清洗留好物化字段明细表是所有聚合链路的源头它的核心职责是完整、稳定、好扫。我强烈建议源表保持原子粒度不要在里面堆一堆业务清洗逻辑但要把常用时间字段物化出来免得每个 SQL 都写toDate(event_time)CREATE TABLE events ( event_time DateTime, event_date Date MATERIALIZED toDate(event_time), event_type String, channel String, region LowCardinality(String), os LowCardinality(String), user_id String, revenue Float64, ... ) ENGINE MergeTree() PARTITION BY toYYYYMMDD(event_date) ORDER BY (event_date, event_type, channel, region, os, user_id);几个设计点都是实战里验证过的分区按天不要按小时。我也曾为了删数据方便按小时分区结果 parts 数量暴涨每次查询要打开的文件句柄成倍增加反而更慢。按天分区足够支撑常规生命周期管理。排序键把等值查询最频繁的字段放前面。这里的event_date是最常见的过滤条件所以放第一event_type、channel、region也是等值过滤的高频字段user_id是高基数且很少做等值过滤的字段放到最后。低基数字段用LowCardinality(String)。渠道、地区、操作系统这类字段取值往往只有几十个用 LowCardinality 能显著减少存储和 IO。MATERIALIZED列不是冗余。它让event_date在插入时自动生成省了每行手写做分区和过滤都用得上。还有一点很多人忽略ORDER BY里没有 revenue也不需要放。排序键的每一列都参与索引构建塞太多低效列只会让索引臃肿写放大更明显。2.2 SummingMergeTree 还是 AggregatingMergeTree按指标类型选预聚合表的引擎选择多数人第一个想到的是 SummingMergeTree因为它简单。但实际做实时看板我更推荐用 AggregatingMergeTree 作为核心。先看对比对比维度SummingMergeTreeAggregatingMergeTree适用指标sum、count 等纯加法sum、count、uniq、avg、quantile、argMin 等保存内容只保存数值保存聚合函数的中间状态查询方式直接 sum()/count() 即可必须用 sumMerge()/uniqMerge() 等收口典型场景点击量、GMVUV、去重访问数、分位数如果你的指标只有加法这一种SummingMergeTree 确实更省事写入就是普通数字查询也友好。但实时看板几乎绕不开 UV——去重统计。要做去重SummingMergeTree 就无能为力了。AggregatingMergeTree 能存聚合函数状态sum 和 uniq 一锅端一张表同时容纳访问量、GMV、UV 三类指标。所以我最终的方案是核心 Cube 表用 AggregatingMergeTree纯计数场景才考虑 SummingMergeTree。2.3 聚合状态表ORDER BY 就是你的维度组合核心状态表长这样CREATE TABLE cube_daily ( biz_date Date, channel String, region LowCardinality(String), os LowCardinality(String), visits AggregateFunction(sum, UInt64), revenue AggregateFunction(sum, Float64), uv AggregateFunction(uniq, String) ) ENGINE AggregatingMergeTree() PARTITION BY toYYYYMMDD(biz_date) ORDER BY (channel, region, os, biz_date);这里有一个新手最容易忽略的设计点AggregatingMergeTree 的 ORDER BY 不只是索引它直接决定后台 Merge 时把哪些行合并成一行。换句话说ORDER BY 前缀列就是你的多维分析维度键。所以你在建表之前就应该想清楚业务到底要按哪些维度组合看数据。如果既要天级又要小时级或者既要全渠道汇总又要单渠道明细不要试图用一张表硬撑——建两张不同粒度的 Cube 表更稳。我当时的做法是建了cube_daily和cube_hourly两张分别服务日报表和大屏实时曲线。指标列用AggregateFunction(...)声明后还有一个副作用插入的值必须是聚合状态不能直接手填数字。也就是说写入方必须调用sumState()、uniqState()这类函数把普通值转成状态。这个约束决定了写入链路必须借助物化视图或专门的 INSERT SELECT不能随便 append。这也是下一节的核心。3. 实时链路物化视图把查询时聚合改成写入时聚合3.1 物化视图的标准写法与触发机制想让数据实时进 Cube核心就是把明细表的写入动作自动转成状态表的聚合写入。ClickHouse 里最干净的实现是物化视图CREATE MATERIALIZED VIEW mv_events_to_cube_daily TO cube_daily AS SELECT toDate(event_time) AS biz_date, channel, region, os, sumState(toUInt64(1)) AS visits, sumState(revenue) AS revenue, uniqState(user_id) AS uv FROM events GROUP BY biz_date, channel, region, os;这里三个关键点必须理解到位物化视图本身不存数据只是一个触发器。每次events表有新数据 INSERT 进来ClickHouse 就把这批数据按视图定义的 GROUP BY 聚合好写入cube_daily。视图不维护自己的副本所以没有额外存储负担。目标表必须是TO指定的那张状态表。这样查询端只面向 Cube 表业务侧不需要知道视图存在上层逻辑很干净。State 函数必须和建表时的AggregateFunction类型严格对应。sumState对应sumuniqState对应uniq混用会在查询时报错或得到错误结果。这块没得商量。还需要特别提醒物化视图只对创建之后的新增数据生效历史数据不会自动进来。很多团队第一次搭就栽在这——建完视图发现之前的数据查不到还以为是写入链路坏了。历史数据必须手动回灌见 3.3。3.2 写入吞吐优化别让小 INSERT 把链路拖垮物化视图是跟着源表的 INSERT 逐 block 执行的。如果上游 Kafka 消费者每 1 万行就提交一次或者业务端高频小批次 INSERT那么每个小批次都会在目标 Cube 表生成一个小 part。后台 Merge 线程根本来不及合并parts 数量迅速膨胀CPU 被合并任务吃满实时链路就慢慢掉队了。我实际处理过的优化组合是这样上游攒批。每次写入建议至少 5 万到 10 万行或者按 1~2 秒攒一批。宁可批大一点也不要高频小批次。Kafka 引擎表可以调 block 大小。用 Kafka 表做源表时适当调大kafka_max_block_size让物化视图每次处理的行数更可观。偶尔手动合并兜底。如果已经产生了大量小 parts直接执行OPTIMIZE TABLE cube_daily FINAL;强制合并一次让水位降下来再观察。多张物化视图并行。同一份明细同时进天级、小时级两张 Cube 表时parallel_view_processing默认是关闭的视图串行执行。可以开启让它并行但代价是 CPU 峰值会放大要在压测后谨慎打开。我当时为了省事把 Kafka 消费者配成来一条处理一条结果大屏延迟从秒级恶化到十几分钟。后来改成攒批 控制 Kafka block size延迟稳定在秒以内parts 数量也一直很健康。3.3 历史数据回灌物化视图建好之后的存量处理每次新增一张 Cube 表或者调整维度组合都绕不开历史数据怎么补的问题。物化视图只处理增量所以存量必须靠手动 INSERT SELECT 回灌INSERT INTO cube_daily SELECT toDate(event_time) AS biz_date, channel, region, os, sumState(toUInt64(1)) AS visits, sumState(revenue) AS revenue, uniqState(user_id) AS uv FROM events WHERE event_date today() - 7 GROUP BY biz_date, channel, region, os;回灌有三个要点回灌语句必须和物化视图的 SELECT 保持一致。维度、State 函数、字段顺序都要一致否则状态表里的数据语义会乱。回灌要选业务低峰期。INSERT SELECT 本身会消耗大量 IO 和内存白天高峰期跑容易拖垮在线查询。回灌窗口和实时写入可能重叠。如果回灌期间源表还在写入同一分区Cube 表会出现同一维度组合的多份部分聚合。不用太慌AggregatingMergeTree 后台合并会兜底但峰值内存会更高。要彻底避免可以把回灌窗口控制在写流量最小的时间段或者干脆停写入再跑。4. 查询加速下钻、上卷与高并发看板接口的最终形态4.1 所有查询必须用 Merge 函数收口AggregatingMergeTree 最容易踩的坑是后台合并是异步的同一维度组合在查询瞬间可能仍然分散在多个 part 里。直接 SELECT 那些二进制状态列得到的是一堆序列化状态而不是数字。所以所有查询必须用对应的 Merge 函数收口SELECT channel, sumMerge(visits) AS visits, sumMerge(revenue) AS revenue, uniqMerge(uv) AS uv FROM cube_daily WHERE biz_date today() AND region region_a GROUP BY channel ORDER BY revenue DESC LIMIT 20;sumMerge()会在读取时把查询涉及的所有 part 里同一 channel 的多行状态合并成一个最终状态再输出成普通数值。这一步就是把这个分布式零件的组装动作显式做掉结果才可信。如果 Cube 表本身很小也可以加FINAL修饰符强制合并比如SELECT ... FROM cube_daily FINAL WHERE ...。FINAL 语义上更省心但它在读取时把所有排序键对应的行合并一遍CPU 开销高。日常看板查询我建议还是用 GROUP BY Merge 的方式灵活度和性能更均衡。4.2 上卷下钻WITH ROLLUP / CUBE 的正确使用姿势预聚合表解决了固定维度的查询但业务总得临时看更高层级的合计。比如运营想看渠道×地区的明细又想顺手看全渠道的合计。这种需求不需要新表直接在查询里加 ROLLUPSELECT channel, region, sumMerge(visits) AS visits FROM cube_daily WHERE biz_date today() GROUP BY channel, region WITH ROLLUP ORDER BY channel, region;WITH ROLLUP会在返回结果中追加小计行合计行的维度字段值会是 NULL前端渲染时记得处理空维度。如果还需要同时看到 region 维度的独立小计可以用WITH CUBE它会生成 N 个维度的所有组合。但我要提醒一句GROUP BY CUBE 是临时分析工具不是实时链路的常态。因为它会把 N 个维度扩展成 2^N 种组合行数爆炸查询成本很高。实时高并发路径上你应该预先建好需要的粒度表把 CUBE 留给运营离线分析别塞进看板接口。4.3 高并发接口的缓存兜底预聚合表已经把单条查询压到几十毫秒但看板页面往往一次要渲染几十个指标、多个筛选条件叠加起来就是几十条 SQL。高并发下我做三层兜底应用层加 Redis 缓存。对最近一天、最近一小时这类高频热点查询TTL 设为 30 秒到 1 分钟效果非常明显。查询超时和内存保护。在接口会话里设置max_execution_time 5和max_memory_usage避免某个异常查询拖垮整个服务。强制分区裁剪。所有查询都带日期条件只访问需要的分区。没有日期条件的聚合查询在我这边直接拒绝。ClickHouse 新版也提供了 query cache可以缓存相同 SELECT 文本的查询结果。但看板接口的 SQL 往往带着不同的筛选参数文本不完全相同命中率有限。我更推荐在应用层控制缓存逻辑直白、可控性更强。5. 上线后最容易翻车的三件事一次一次复盘5.1 UV 对不上uniq 是近似统计别当精确值用上线第一天运营就来反馈Cube 里的 UV 和之前从明细表直接跑uniqExact的结果差了 1% 左右。这不是 Bug是函数特性。uniq底层是 HyperLogLog 类算法误差通常在 0.5%~2%换来的是几十倍的性能提升uniqExact精确但内存和耗时都显著上升。我建议的排查链路是先确认差值是否在 1%~2% 内、且方向稳定。是的话基本就是近似误差属于正常现象。如果差值突然变大或者出现明显不符合业务常识的波动再怀疑重复写入或回灌窗口重叠。用同一份明细直接对比SELECT uniqExact(user_id) FROM events WHERE ...和SELECT uniqMerge(uv) FROM cube_daily WHERE ...快速缩小问题范围。如果业务明确要求绝对精确比如对账场景可以把建表字段改成AggregateFunction(uniqExact, String)写入也换成uniqExactState。代价是查询变慢、内存变大一般看板业务用不上。5.2 实时性掉队小 parts 与物化视图串行的连锁反应有一次大屏数据莫名停在 10 分钟前我去查system.processes看到一堆向 Cube 表写入的 INSERT 在排队再看目标表磁盘上堆了几百个小 parts。根因是上游 Kafka 消费者的批大小配得太小物化视图逐个 block 触发每个 block 产生独立 part后台 Merge 根本来不及合并。修复链路我按这个顺序做了调大 Kafka 消费的 block 大小和批次数保证每次触发物化视图的行数够大。适当调高background_pool_size和background_merges_mutations_concurrency_ratio让后台合并线程更快消化 parts。手动OPTIMIZE TABLE cube_daily FINAL;一次性合掉存量小 parts。观察一段时间确认平均 part 数量回到几十以内的健康水位。这个坑的教训是实时链路的瓶颈往往不在 ClickHouse 单查询性能而在写入侧的小批量放大效应。别只顾着实时也要让写入足够批量。5.3 分区膨胀与查询退化从 EXPLAIN 看到的问题另一个高频问题出现在 Cube 表的分区设计上。有人以为分区越细删数据越方便于是按小时建分区。结果 parts 数量破千查询需要打开的文件句柄暴增反而更慢。排查时一定要会用 EXPLAIN 验证索引是否真的裁剪了EXPLAIN indexes 1 SELECT sumMerge(visits) FROM cube_daily WHERE biz_date today();重点看输出里的SelectedParts和PrimaryKeyRanges。如果该裁掉的分区没裁掉或者 PrimaryKey 范围覆盖过大优先检查两件事分区表达式是否过细排序键是不是把最常用的过滤字段放在了后面我遇到过最典型的退化是Cube 表 ORDER BY 把biz_date放在最后但查询几乎全部按biz_date过滤。结果每个查询都扫了排序键的无效前缀等于做全量聚合预聚合的意义完全丧失。而且排序键这类问题建表后不能直接 ALTER只能新建表再迁移数据。所以一开始就把维度顺序想清楚比后续任何优化都重要。最后再分享一个实际操作中的体会。这套全流程方案上线后最直观的变化是看板接口从秒级降到几十毫秒查询并发翻了五六倍依然稳定。说实话ClickHouse 本身很强但再强的引擎也扛不住无休止的全表聚合。数据立方体的本质就是提前想清楚哪些维度组合会被反复查询然后用存储换时间把计算从请求时搬到写入时。如果你也在做实时报表我建议先别急着堆机器加配置把立方体这层架稳收益比任何单点调优都明显。