ClickHouse物化视图与Projection机制

一、物化视图（Materialized View）：

原理：

• 物化视图是一张隐式存储的目标表，数据写入源表时自动触发SELECT转换后写入
• 类似「Insert...Select」的触发器

创建：

CREATE MATERIALIZED VIEW mv_agg
ENGINE = SummingMergeTree()
ORDER BY (event_date, city)
AS SELECT
    toDate(event_time) AS event_date,
    city,
    count() AS pv,
    uniqExact(user_id) AS uv
FROM source_table
GROUP BY event_date, city;

特点：

• 存储新表，占用额外空间
• 增量聚合（仅新写入数据触发）
• 数据一致性：源表和物化视图原子写入
• 对已有历史数据不生效（需单独历史回填）

适用： 需要多种聚合粒度的预计算（如日/时/分不同维度）

二、Projection（投影）：

原理：

• 在同一张表内创建聚合数据的副本（存储级物化）
• ClickHouse 21.11+，更轻量

创建：

ALTER TABLE source_table ADD PROJECTION prj_agg (
    SELECT toDate(event_time), city, count(), uniqExact(user_id)
    GROUP BY toDate(event_time), city
)
ALTER TABLE source_table MATERIALIZE PROJECTION prj_agg;

特点：

• 存储在同一张表的Part内（不新建表）
• 查询优化器自动选择Projection
• 历史数据回填只需MATERIALIZE命令
• 空间占用略小于物化视图

三、对比表： | 维度 | 物化视图 | Projection | |------|---------|-----------| | 存储 | 独立目标表 | 同一表内 | | 查询使用 | 显式查视图名 | 自动选择（透明）| | 版本 | 所有版本 | 21.11+ | | 回填历史 | 手动Insert | MATERIALIZE命令 | | 管理复杂度 | 高（多表） | 低（单表） |

四、选型建议：

• 新项目→优先使用Projection（更简洁）
• 需要不同存储引擎/TTL策略→物化视图
• 已有物化视图体系→无需迁移