ClickHouse Projection 实战物化索引替代物化视图的场景一、物化视图的尴尬ClickHouse的物化视图Materialized View是个好东西——提前算好聚合结果查的时候直接从物化视图里拿比扫原始表快十倍百倍。但它有个很现实的痛点物化视图是独立的表数据和原始表不一定同步。来看一个典型案例-- 原始表用户行为日志 CREATE TABLE user_events ( event_date Date, event_time DateTime, user_id UInt64, event_type String, page_url String, duration UInt32, device_type String, city String ) ENGINE MergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_type, user_id); -- 物化视图按天事件类型的聚合 CREATE MATERIALIZED VIEW mv_daily_event_stats ENGINE SummingMergeTree() ORDER BY (event_date, event_type) POPULATE -- 同步历史数据 AS SELECT event_date, event_type, count() AS pv, uniq(user_id) AS uv, sum(duration) AS total_duration FROM user_events GROUP BY event_date, event_type;当你在原始表user_events做INSERT时物化视图mv_daily_event_stats会自动更新——这是理想情况。但现实是数据更新麻烦如果原始表有数据修正UPDATE/DELETE物化视图里的数据就脏了管道中断风险如果写入过程中物化视图所在的节点挂了数据就会丢失维护成本高每次改表结构相关的物化视图都要跟着改这时候ClickHouse自20.10版本引入的Projection就成了一个很香的替代方案。为什么 Projection 能解决物化视图的数据不同步痛点根源在于存储架构的不同。物化视图的数据是独立存放在另一张表中的写入原始表时通过触发器机制异步写入物化视图——如果触发失败节点宕机、写入超时数据就丢了。而 Projection 的数据和原始数据存在同一个 part 目录下写入时是原子操作要么都写入要么都失败不存在原始表写入了但投影没更新的中间状态。这一点对数据严苛场景金融、电商交易来说极为关键——你不需要额外开发数据对账脚本来检查原始表和物化视图是否一致因为 Projection 天然保证一致性。二、Projection vs 物化视图本质区别核心区别一句话总结物化视图是独立的表→ 数据可能不一致需要手动维护Projection是原始表的一部分→ 和原始数据在同一part中天然一致三、Projection 实战3.1 创建和使用-- 先建原始表 CREATE TABLE user_events_proj ( event_date Date, event_time DateTime, user_id UInt64, event_type String, page_url String, duration UInt32, device_type String, city String, -- 关键定义 Projection PROJECTION proj_daily_agg ( -- Projection内部的ORDER BY决定了这个投影的排序方式 -- 查询条件如果匹配这个排序ClickHouse会自动使用投影 SELECT event_date, event_type, count() AS pv, uniq(user_id) AS uv, sum(duration) AS total_duration GROUP BY event_date, event_type ) ) ENGINE MergeTree() PARTITION BY toYYYYMM(event_date) -- 原始表的排序键 ORDER BY (event_date, event_type, user_id);关键语法解读PROJECTION proj_daily_agg—— 给投影命个名括号内的SELECT ... GROUP BY定义了投影的数据结构投影不定义ENGINE因为它和原始表共享存储引擎投影继承原始表的分区每个part会同时包含原始数据和投影数据3.2 Projection的数据同步机制-- 写入数据和普通表完全一样 INSERT INTO user_events_proj VALUES (2026-07-17, 2026-07-17 10:00:00, 1001, page_view, /home, 30, mobile, Beijing), (2026-07-17, 2026-07-17 10:01:00, 1002, click, /product/123, 15, desktop, Shanghai), (2026-07-17, 2026-07-17 10:02:00, 1001, page_view, /home, 25, mobile, Beijing); -- 查看表结构projection信息在创建语句中可见 SHOW CREATE TABLE user_events_proj; -- 关键Projection是针对存量数据生效的 -- 新写入的数据自带Projection数据 -- 但已有数据需要手动物化 ALTER TABLE user_events_proj MATERIALIZE PROJECTION proj_daily_agg; -- 查询时会自动使用Projection无需手动指定 -- 正常写SQLClickHouse自动判断是否可以用投影 SELECT event_date, event_type, count() AS pv, uniq(user_id) AS uv FROM user_events_proj WHERE event_date 2026-07-17 GROUP BY event_date, event_type; -- ClickHouse会在后台分析这个查询能用proj_daily_agg投影吗 -- 能的话就用投影的预聚合结果速度直接起飞 3.3 验证Projection是否被使用-- 方法1EXPLAIN查看执行计划 EXPLAIN SELECT event_date, event_type, count() AS pv FROM user_events_proj WHERE event_date 2026-07-17 GROUP BY event_date, event_type; -- 如果输出中包含 ReadFromMergeTree (proj_daily_agg)说明投影生效了 -- 方法2关闭投影对比性能 -- 先看正常情况使用投影 SELECT event_date, event_type, count() AS pv FROM user_events_proj WHERE event_date 2026-07-17 GROUP BY event_date, event_type; -- 耗时: 0.005s (使用投影直接读预聚合结果) -- 强制不使用投影 SELECT event_date, event_type, count() AS pv FROM user_events_proj WHERE event_date 2026-07-17 GROUP BY event_date, event_type SETTINGS optimize_use_projections 0; -- 耗时: 0.12s (扫全表逐行聚合慢24倍) **为什么查询会自动路由到 Projection 而不是需要手动指定** ClickHouse 的查询优化器在解析 SQL 后会检查这个查询的 SELECT 列 GROUP BY WHERE 条件是否可以被某个 Projection 的预计算结果覆盖如果匹配就用投影数据代替原始表数据省掉聚合这一步。这个自动匹配的优势在于你不需要改任何业务代码BI 工具、Python 脚本里的 SQL 都不用改建完 Projection 后查询自动变快。如果想验证投影是否生效两条 SQL 就能看出来正常查一次记下耗时SETTINGS optimize_use_projections 0 关闭投影再查一次耗时差异就是投影带来的实际收益。 -- 方法3查询投影的存储大小 SELECT name, formatReadableSize(data_compressed_bytes) AS compressed, formatReadableSize(data_uncompressed_bytes) AS uncompressed, rows FROM system.projection_parts WHERE table user_events_proj AND active;四、Projection 的最佳实践场景4.1 场景对比决策4.2 典型场景代码场景1聚合查询优化最常见的用法-- 原始表按(日期, 事件类型, 用户)排序 -- 但业务经常要查按日期事件类型聚合 -- 这种场景Projection简直是量身定制 CREATE TABLE events_with_proj ( date Date, event_type String, user_id UInt64, metrics Float64 ) ENGINE MergeTree() ORDER BY (date, event_type, user_id) -- 原始ORDER BY精确查询快但聚合慢 PROJECTION proj_agg ( SELECT date, event_type, sum(metrics) AS total, count() AS cnt GROUP BY date, event_type ); -- Projection ORDER BY聚合查询快因为已经提前聚合好了 -- 当查询的SELECT/GROUP BY匹配Projection时ClickHouse自动切换场景2排序优化ORDER BY投影-- 原始表按ID排序 -- 但前端列表页要按创建时间倒序显示 CREATE TABLE articles_with_proj ( id UInt64, title String, author_id UInt64, created_at DateTime, view_count UInt32, content String ) ENGINE MergeTree() ORDER BY id -- 主键按ID查适合详情页 PROJECTION proj_by_time ( SELECT * ORDER BY created_at DESC ); -- Projection ORDER BY created_at适合按时间排序的列表查询 -- 当SELECT带有ORDER BY created_at时自动启用场景3JOIN预计算宽表预生成-- 反范式化预计算把JOIN的结果存在Projection里 CREATE TABLE orders_proj ( order_id UInt64, user_id UInt64, product_sku String, amount Decimal(10,2), order_date Date ) ENGINE MergeTree() ORDER BY order_date PROJECTION proj_order_with_product ( SELECT o.order_date, o.product_sku, p.category_id, p.brand, sum(o.amount) AS total_amount, count() AS order_count FROM orders_proj AS o -- 注意Projection中的JOIN表必须已经存在 -- 并且JOIN条件必须是等值JOIN GLOBAL LEFT JOIN products AS p ON o.product_sku p.sku GROUP BY o.order_date, o.product_sku, p.category_id, p.brand );4.3 Projection的限制-- ❌ 限制1Projection不支持子查询 -- 不能在PROJECTION内部写嵌套子查询 -- ❌ 限制2不能跨part -- Projection数据只在part内部聚合 -- 查询时如果涉及多个part还需要查询层再做一次合并 -- ❌ 限制3写入开销 -- 每个INSERT都要同时维护Projection数据 -- 写入性能会下降10%-30%取决于投影复杂度 -- ❌ 限制4存储翻倍 -- Projection的数据存储在原始表的part中但占独立空间 -- 可以通过system.projection_parts查看具体大小 SELECT database, table, name AS projection_name, sum(rows) AS total_rows, formatReadableSize(sum(bytes_on_disk)) AS disk_usage FROM system.projection_parts WHERE active GROUP BY database, table, name;为什么 Projection 有这些限制但仍然值得用限制 1不能子查询和限制 3写入开销 10-30%是 Projection 为了数据一致性付出的代价——它在 part 内部完成计算无法跨 part 做 JOIN 或子查询。但换个角度想这些限制反而让 Projection 的使用边界非常清晰固定聚合查询→用 Projection需要复杂多表预计算→用物化视图。限制 4存储翻倍是很多人犹豫的原因但实际上聚合类的 Projection 存储开销通常远小于翻倍——因为聚合操作已经把百万行压缩成了按日期事件类型的几百行。真正存储翻倍的场景是排序 ProjectionSELECT * ORDER BY created_at这种投影相当于把数据按不同方式存了两份。所以在建 Projection 前先跑一下system.projection_parts确认存储开销避免惊喜。踩坑提醒坑1建了 Projection 后没有 MATERIALIZE 历史数据— 建投影只对新写入数据生效存量的 part 需要用ALTER TABLE ... MATERIALIZE PROJECTION ...来生成投影数据。如果忘了这一步存量数据的查询还是慢的只有新数据才快。坑2Projection 的 SELECT 和原始查询不完全匹配导致未使用— ClickHouse 的投影匹配规则比较严格。如果你建了GROUP BY date, event_type的投影但查询写的是GROUP BY event_type, date顺序不同部分版本可能匹配不上。先 EXPLAIN 确认必要时代码里保持 GROUP BY 顺序和投影一致。坑3写入频繁场景下 Projection 导致 part 合并变慢— 每次 INSERT 都会在 part 里生成投影数据高频写入每秒数百条会让 part 数量快速增长merge 负载升高。解决方式适当调大 INSERT 的批量大小减少写入频率。补充一点Projection 在 ClickHouse 22.3 之前有个 bug——如果投影的 SELECT 里包含了原始表没有的列比如用 generateUUIDv4() 生成的列查询时会报错而不是自动忽略投影。升级到 22.8 后这个问题修复了但如果你们还在用老版本建投影前务必检查一下 ClickHouse 的版本。另外提醒一下Projection 不支持 DELETE 语句的增量更新——如果你用了 ClickHouse 的 DELETE通过 ALTER TABLE ... DELETE WHERE投影数据不会自动同步删除需要手动 MATERIALIZE。这个限制在 2025 年的版本里还存在生产环境用 DELETE 要特别注意。五、总结Projection是ClickHouse 20.10版本后给预聚合场景的一个更优雅的解决方案数据天然一致——Projection和原始数据在同一个part中写入即同步不存在物化视图的数据滞后问题。写入自动更新——INSERT数据时Projection同步生成无需额外的刷新任务或POPULATE操作。查询自动路由——不需要在SQL中显式指定用哪个ProjectionClickHouse会根据查询的SELECT/GROUP BY/ORDER BY自动选择最优投影。适用场景明确——聚合查询、排序重排、JOIN预计算这三种场景是Projection的最佳用武之地。如果需求超出这个范围还是老老实实用物化视图。一句话记忆物化视图是另起炉灶Projection是自带干粮。当你需要准确、一致、零维护的预聚合时Projection比物化视图更合适。