ClickHouse系列（七）：Materialized View 与多分辨率 Rollup 设计

聚合与趋势——分钟到月级趋势如何高效查询引言监控系统每秒写入百万条指标但用户看的是过去 7 天的 QPS 趋势。如果每次查询都扫描原始数据集群迟早扛不住。ClickHouse 的 Materialized ViewMV AggregatingMergeTree 提供了一套优雅的多级 Rollup 方案写入时自动聚合查询时直接读预计算结果。本篇将深入 MV 的执行机制、性能代价以及如何设计 Raw → 10min → 1h → 1d 的多分辨率聚合链路。1. MV 是同步计算不是后台任务很多人第一次接触 MV 时会以为它类似 ETL 定时任务。事实恰好相反INSERT 原始数据 → 触发 MV 的 SELECT → 结果写入目标表这三步在同一次 INSERT 的执行路径中同步完成。这意味着MV 的 SELECT 越复杂INSERT 延迟越高MV 数量越多每次写入的扇出越大如果目标表写入失败原始表的 INSERT 也会失败验证方式——查看system.query_log你会发现每次 INSERT 后紧跟着 MV 触发的子查询SELECTquery,type,query_kindFROMsystem.query_logWHEREqueryLIKE%mv_target_table%ORDERBYevent_timeDESCLIMIT5;核心认知MV 不是免费午餐它用写入时的 CPU 换取查询时的 IO。2. MV 的性能成本在哪里成本维度说明CPUMV 的 SELECT 在 INSERT 线程中执行GROUP BY、函数计算都消耗 CPU内存聚合状态AggregateFunction需要在内存中维护 hash table写放大1 条 INSERT 触发 N 个 MV磁盘写入量翻 N 倍延迟INSERT 的 latency 原始写入 所有 MV 执行时间之和Merge 压力目标表产生大量小 part后台 merge 线程压力增大实际经验值3 个简单 MVSUM/COUNT对写入延迟的影响约 15-30%超过 5 个 MV 时建议评估是否拆分写入链路MV 的 SELECT 中避免 JOIN否则写入性能会断崖式下降3. Raw / 10min / 1h 表的职责划分多分辨率 Rollup 的核心思想是不同时间粒度的查询命中不同的表。表粒度TTL典型查询场景metrics_raw原始秒级3 天实时告警、最近 1 小时明细metrics_10min10 分钟30 天过去 24 小时趋势图metrics_1h1 小时180 天周报、月度趋势metrics_1d1 天3 年年度同比、容量规划原始表定义CREATETABLEmetrics_raw(tsDateTime,host String,metric String,valueFloat64)ENGINEMergeTree()PARTITIONBYtoYYYYMMDD(ts)ORDERBY(metric,host,ts)TTL tsINTERVAL3DAY;关键设计原则原始表保留最短时间用 TTL 自动清理聚合表保留更长时间数据量小得多查询层根据时间范围自动路由到对应的表4. AggregatingMergeTree MV 的正确组合AggregatingMergeTree 的核心能力是在后台 merge 时继续聚合。配合 MV实现写入时预聚合 merge 时再聚合的两阶段计算。第一步创建目标表CREATETABLEmetrics_10min(ts_bucketDateTime,host String,metric String,value_sum AggregateFunction(sum,Float64),value_max AggregateFunction(max,Float64),value_count AggregateFunction(count,Float64))ENGINEAggregatingMergeTree()PARTITIONBYtoYYYYMMDD(ts_bucket)ORDERBY(metric,host,ts_bucket)TTL ts_bucketINTERVAL30DAY;第二步创建 MVCREATEMATERIALIZEDVIEWmv_metrics_10minTOmetrics_10minASSELECTtoStartOfTenMinutes(ts)ASts_bucket,host,metric,sumState(value)ASvalue_sum,maxState(value)ASvalue_max,countState(value)ASvalue_countFROMmetrics_rawGROUPBYts_bucket,host,metric;第三步查询时用 Merge 函数SELECTts_bucket,host,sumMerge(value_sum)AStotal,maxMerge(value_max)ASpeak,countMerge(value_count)AScntFROMmetrics_10minWHEREmetriccpu_usageANDts_bucketnow()-INTERVAL24HOURGROUPBYts_bucket,hostORDERBYts_bucket;注意sumState/sumMerge的配对关系阶段函数作用MV 写入sumState(value)将值编码为中间聚合状态查询sumMerge(value_sum)将多个中间状态合并为最终结果如果直接用sum(value_sum)查询 AggregatingMergeTree会得到错误结果或报错。5. 多级 Rollup 的写入与查询策略链式 MV从 10min 再聚合到 1hCREATETABLEmetrics_1h(ts_bucketDateTime,host String,metric String,value_sum AggregateFunction(sum,Float64),value_max AggregateFunction(max,Float64),value_count AggregateFunction(count,Float64))ENGINEAggregatingMergeTree()PARTITIONBYtoYYYYMM(ts_bucket)ORDERBY(metric,host,ts_bucket)TTL ts_bucketINTERVAL180DAY;CREATEMATERIALIZEDVIEWmv_metrics_1hTOmetrics_1hASSELECTtoStartOfHour(ts_bucket)ASts_bucket,host,metric,sumMergeState(value_sum)ASvalue_sum,maxMergeState(value_max)ASvalue_max,countMergeState(value_count)ASvalue_countFROMmetrics_10minGROUPBYts_bucket,host,metric;注意这里用的是sumMergeState而不是sumState——因为输入已经是聚合状态需要先 merge 再重新编码为 state。查询路由策略应用层根据查询的时间范围选择表时间范围 1 小时 → metrics_raw 时间范围 24 小时 → metrics_10min 时间范围 30 天 → metrics_1h 时间范围 30 天 → metrics_1d也可以用 ClickHouse 的VIEW封装路由逻辑但实践中应用层路由更灵活、更可控。6. 为什么不能一张表打天下“直接在原始表上 GROUP BY 不行吗” 我们用数据说话假设场景1000 台主机 × 50 个指标 × 每秒 1 条 5000 万条/天查询扫描原始表扫描 10min 聚合表加速比最近 1 小时趋势1.8 亿行30 万行600x最近 7 天趋势35 亿行504 万行700x最近 30 天趋势150 亿行2160 万行700x除了扫描量还有以下问题原始表 TTL 3 天后数据就没了30 天趋势根本查不到大范围 GROUP BY 消耗大量内存可能触发 OOM并发查询时集群负载飙升影响写入稳定性多级 Rollup 的本质是用空间换时间用写入时的计算换查询时的速度。在时序、监控、日志分析等场景中这是标准做法。总结要点说明MV 是同步的INSERT 路径中执行影响写入延迟用 AggregatingMergeTree支持两阶段聚合merge 时继续合并State/Merge 函数配对写入用xxxState查询用xxxMerge链式 MV 用 MergeState从聚合表再聚合时用xxxMergeState查询路由根据时间范围命中不同粒度的表控制 MV 数量超过 5 个需要评估写入链路压力下一篇我们讨论 ClickHouse 中最容易踩坑的操作——UPDATE 和 DELETE。