ClickHouse物化视图与Projection的工程化预聚合策略的性能对比与选型一、报表查询的永恒困境实时计算与预计算的博弈在 OLAP 系统中一个经典的对立是实时查询的灵活性 vs 预计算带来的响应速度。以某广告系统的实时报表为例需要按广告主 广告计划 小时维度聚合展示量、点击量和消耗金额。原始数据表每天新增 20 亿行直接查询的典型 SQL 需要 45 秒才能返回结果SELECT advertiser_id, campaign_id, toStartOfHour(event_time) AS h, count() AS impressions, sum(clicks) AS total_clicks FROM ad_events WHERE event_date today() GROUP BY advertiser_id, campaign_id, h ORDER BY total_clicks DESC LIMIT 100;在报表看板中45 秒的等待完全不可接受。物化视图Materialized View和投影Projection是 ClickHouse 提供的两种预聚合方案但它们的实现机制、适用场景和维护成本截然不同。选错方案可能导致存储膨胀 3-5 倍或者查询性能提升远低于预期。本文将通过真实的性能压测数据系统性地对比两种方案的工程优劣并提供明确的选型决策框架。二、两类预聚合机制的底层原理差异flowchart TB subgraph MV[物化视图 (Materialized View)] A1[原始表写入] -- B1[INSERT 触发器] B1 -- C1[增量聚合计算] C1 -- D1[(物化视图表br/独立存储)] D1 -- E1[查询时直接读取] end subgraph PROJ[投影 (Projection)] A2[原始表写入] -- B2[后台合并] B2 -- C2[按 Projection 排序br/重构 Part] C2 -- D2[与原始数据br/同Part存储] end subgraph QUERY[查询路由] Q[SELECT ... GROUP BY] -- R{选择数据源} R --|匹配物化视图| E1 R --|匹配 Projection| D2 R --|无匹配| O[原始表扫描] end物化视图的本质一个独立的、被自动更新的新表。它类似于 MySQL 的 Trigger Summary Table由INSERT触发器自动维护。物化视图的数据存储是独立的可以与原始表使用不同的排序键和分区策略。投影Projection的本质原始表的数据按不同排序键重新组织的一个影子副本。它隐藏在原始表的 Part 内部对外透明。查询时如果匹配 Projection 的排序键ClickHouse 自动选择 Projection 而非原始数据。核心差异对比维度物化视图Projection数据存储独立的表独立文件嵌入在原始 Part 内更新方式INSERT 触发器同步后台 Merge 异步查询方式显式查询 MV 表自动路由透明聚合能力支持任意聚合函数仅数据排序不聚合灵活性高可关联多张表低仅原始表数据存储膨胀高独立存储 聚合结果中排好序的原始数据三、实际场景的代码实践与性能数据3.1 物化视图实现-- 1. 原始事件表 CREATE TABLE ad_events ( event_date Date, event_time DateTime, advertiser_id UInt32, campaign_id UInt32, creative_id UInt32, event_type LowCardinality(String), bid_price Decimal(10, 4), charge_price Decimal(10, 4) ) ENGINE MergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, advertiser_id, campaign_id, event_time); -- 2. 物化视图目标表独立的汇总表 CREATE TABLE ad_hourly_agg ( event_date Date, hour DateTime, advertiser_id UInt32, campaign_id UInt32, impressions UInt64, clicks UInt64, cost AggregateFunction(sum, Decimal(20, 4)), revenue AggregateFunction(sum, Decimal(20, 4)) ) ENGINE AggregatingMergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, advertiser_id, campaign_id, hour); -- 3. 物化视图定义触发器 CREATE MATERIALIZED VIEW ad_hourly_agg_mv TO ad_hourly_agg AS SELECT event_date, toStartOfHour(event_time) AS hour, advertiser_id, campaign_id, countIf(event_type impression) AS impressions, countIf(event_type click) AS clicks, sumState(charge_price) AS cost, sumState(charge_price) AS revenue FROM ad_events GROUP BY event_date, hour, advertiser_id, campaign_id; -- 4. 查询示例 SELECT advertiser_id, campaign_id, sum(impressions) AS total_impressions, sum(clicks) AS total_clicks, sumMerge(cost) AS total_cost FROM ad_hourly_agg WHERE event_date today() GROUP BY advertiser_id, campaign_id ORDER BY total_impressions DESC LIMIT 100;物化视图的查询耗时0.05 秒预聚合后直接从汇总表读取3.2 Projection 实现-- 1. 在原始表上创建 Projection ALTER TABLE ad_events ADD PROJECTION proj_advertiser_campaign_hour ( SELECT advertiser_id, campaign_id, toStartOfHour(event_time) AS hour, event_type ORDER BY (advertiser_id, campaign_id, hour, event_type) ); -- 2. 对历史数据物化 Projection ALTER TABLE ad_events MATERIALIZE PROJECTION proj_advertiser_campaign_hour; -- 3. 查询时自动使用 Projection透明路由 SELECT advertiser_id, campaign_id, toStartOfHour(event_time) AS h, countIf(event_type impression) AS impressions, countIf(event_type click) AS clicks FROM ad_events WHERE event_date today() AND advertiser_id 12345 GROUP BY advertiser_id, campaign_id, h; -- 手动指定 Projection SELECT ... FROM ad_events WHERE event_date today() GROUP BY advertiser_id, campaign_id, h SETTINGS optimize_use_projections 1;Projection 查询耗时0.5-2 秒仍需实时扫描聚合只是数据已按目标维度排序3.3 性能对比数据基于 20 亿行/天、6 个分片节点的压测结果维度原始表物化视图Projection日均聚合查询耗时45s0.05s1.2s存储膨胀1x (基准)0.3x (汇总后更小)1.8x (排序后列压缩效果降低)写入吞吐100%88% (触发器开销)95% (后台 Merge)查询灵活性最高任意维度最低固定维度高可查询所有列历史数据变更支持需重建 MV支持关键发现当查询维度恰好匹配物化视图的聚合维度时性能差距可达 24 倍。但物化视图牺牲了查询灵活性——一旦聚合维度固定如按小时就无法查询按分钟维度的数据。四、选型决策框架决策树需要查原始明细 ├── 是 → 只能用 Projection └── 否 → 聚合维度固定 ├── 是 → 数据量超过 1 亿行 │ ├── 是 → 物化视图 │ └── 否 → Projection 即可 └── 否 → 查询延迟要求 ├── 100ms → 物化视图 └── 1s → Projection混合策略按时间冷热分层一个更工程化的方案是将物化视图和 Projection 结合使用近 7 天数据使用物化视图查询频率最高响应要求最高7-30 天数据使用 Projection查询频率中等允许秒级延迟30 天以上数据使用物化视图月汇总 原始表按需查询五、总结物化视图和 Projection 不是非此即彼的选择而是互补的工具物化视图 查询速度优先适合维度固定、响应要求极致的报表场景代价是查询灵活性和部分写入性能Projection 查询灵活性优先适合查询模式不固定、需要保留原始明细的场景代价是存储空间和弱于 MV 但强于原始表的查询性能混合策略 最佳实践按数据冷热分层热数据 MV 极致性能温数据 Projection 平衡冷数据原始表兜底在实际的广告系统中混合策略将 95% 的报表查询控制在 100ms 以内总存储增长控制在原始数据的 2.2 倍。纯 MV 方案会丢失 30% 的查询灵活性纯 Projection 方案会让 40% 的查询超过 2 秒——混合方案恰好平衡了这两个极端。