ClickHouse 物化视图刷新与数据一致性（Aggregating 与 TTL 验证）

概述物化视图在写入明细表时同步聚合到汇总表，结合AggregatingMergeTree与TTL策略在后台进行合并与清理。通过一致性检查与查询对比验证聚合正确性。关键实践与参数预聚合: 使用 `countState/sumState` 在视图端聚合合并与Finalize: 查询时使用 `finalizeAggregation`TTL策略: 明细表或汇总表按时间清理与重组索引与分区: 根据查询维度设计排序与分区键示例/配置/实现CREATE TABLE logs ( ts DateTime, app String, status UInt16, size UInt64 ) ENGINE = MergeTree() PARTITION BY toYYYYMM(ts) ORDER BY (app, ts) TTL ts + INTERVAL 90 DAY; CREATE TABLE logs_agg ( day Date, app String, count AggregateFunction(count), sum_size AggregateFunction(sum, UInt64) ) ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(day) ORDER BY (app, day); CREATE MATERIALIZED VIEW mv_logs_agg TO logs_agg AS SELECT toDate(ts) AS day, app, countState() AS count, sumState(size) AS sum_size FROM logs GROUP BY day, app; SELECT day, app, finalizeAggregation(count) AS cnt, finalizeAggregation(sum_size) AS total FROM logs_agg WHERE day >= today() - 7 ORDER BY day, app; 验证一致性: 对比汇总结果与在明细表直接聚合的结果一致性能: 汇总表查询耗时显著低于明细聚合TTL生效: 观察过期分区自动清理, 存储占用下降合并稳定: 后台合并任务正常, 无拥塞注意事项物化视图基于写入触发, 导入旧数据需重新回放或批量聚合TTL与合并任务在高负载下需监控资源占用排序与分区键影响巨大, 需谨慎设计对非加总类指标使用合适的聚合函数