Projections 与预聚合加速

1. Projections 是什么：表内的物化索引

Projection（投影）是 ClickHouse 在表内部维护的一份额外数据副本，按不同的排序键、粒度或聚合方式组织。它不像普通二级索引那样只存指针，而是真正把数据重新排布一份，因此查询可以直接从投影读取，跳过原表扫描。对聚合查询而言，投影可以预先算好中间状态，把「扫十亿行做 GROUP BY」变成「扫几千行合并状态」。

Projection 解决的核心痛点是：同一张明细表要同时服务多种查询形态。明细写入只按一个主键排序，而分析侧可能频繁按另一维度聚合。传统做法要么建多张物化视图（需要自己维护目标表、回填、写入链路），要么忍受全表扫描。Projection 把这份维护工作交给了表引擎自身——它随 part 一起写入、一起合并、一起删除，天然与源数据保持一致。

需要澄清一个常见误解：Projection 不是索引，它没有「回表」概念。一旦优化器选中某个投影，整条查询就完全在投影那份数据上执行，原表列不会被读取。这也是它能带来数量级提升的原因，同时也意味着投影必须包含查询所需的全部列。

1.1 与物化视图的差异

很多人第一反应是「这不就是物化视图吗」。两者确实都做预计算，但归属与触发方式完全不同：

一句话取舍：只涉及单表、想要「零改 SQL」的加速，用 Projection；需要跨表加工或复杂变换，用物化视图。若你还在犹豫物化视图的增量语义与回填成本，可以先读 /clickhouse-materialized-views/ 建立对比基线。

1.2 存储结构

一个 Projection 在磁盘上表现为每个 part 目录下的 proj_<name> 子目录，内部是一份完整的列式存储（自己的主键索引、自己的压缩编码）。目录结构大致如下：

/var/lib/clickhouse/data/default/events/
└── 202610_1_1_0/
    ├── event_time.bin / .mrk2      # 原表列
    ├── user_id.bin / .mrk2
    ├── primary.idx                  # 原表主键索引
    └── proj_agg_by_user/            # 投影的独立存储
        ├── user_id.bin / .mrk2
        ├── event_type.bin / .mrk2
        ├── cnt.bin / .mrk2
        └── primary.idx              # 投影自己的主键索引

这意味着：

• 存储放大：投影会额外占用磁盘，通常是原表的一个比例（取决于列裁剪与聚合程度）；
• 写入放大：每个新 part 都要额外写一份投影数据，INSERT 吞吐会下降；
• 合并放大：part 合并时投影也要跟着合并，CPU 与 IO 开销上升；
• 压缩独立：投影列可以使用与原表不同的 CODEC，聚合列的压缩率往往更高。

这些代价是必须提前评估的，后文第 5 节给出量化方法。

2. 定义与语法

2.1 基本语法

ALTER TABLE events
    ADD PROJECTION proj_by_user
    (
        SELECT user_id, event_type, count() AS cnt, sum(value) AS total
        GROUP BY user_id, event_type
    );

ADD PROJECTION 只是注册定义，此刻还不产生数据。需要显式物化：

ALTER TABLE events MATERIALIZE PROJECTION proj_by_user;

MATERIALIZE 会异步重写所有现存 part，把投影数据补齐。对于大表这是一次全量 IO，建议放在低峰期，并用 system.mutations 观察进度。

2.2 物化与后台合并

新写入的 part 会立即带上投影（写入路径同步生成），所以 MATERIALIZE 只负责历史数据。之后每次 part 合并，投影数据也会重新合并，聚合投影的中间状态会在这个过程中进一步归并。因此：

• 刚 MATERIALIZE 完，投影里的分组数可能很多（每个 part 各自聚合）；
• 随着后台合并推进，分组数逐渐收敛到真实基数；
• 用 OPTIMIZE TABLE ... FINAL 可以强制合并到位，但代价高，不建议对大表频繁执行。

判断投影是否已经收敛，可以观察 system.projection_parts 中同一投影在不同 part 的行数之和——它会随着合并逐步逼近真实去重后的分组数。

2.3 变体：normal 与 aggregate

ClickHouse 支持两类投影：

normal 投影本质是「另一种排序的副本」，优化器在发现查询所需的列与排序前缀被投影覆盖时会改读投影。aggregate 投影则要求查询能被「状态合并」改写，例如 count()、sum()、uniq() 这类有对应 -State/-Merge 的聚合。

一个 normal 投影的典型写法，把按时间排序的表额外提供一份按用户排序的视图：

ALTER TABLE events
    ADD PROJECTION proj_by_user_detail
    (
        SELECT event_time, user_id, event_type, value
        ORDER BY (user_id, event_time)
    );

这样 WHERE user_id = X ORDER BY event_time LIMIT 100 这类「查某人最近行为」的查询，就能靠投影的主键索引直接定位，避免全表扫描。

2.4 列裁剪与 CODEC

投影定义里应只保留查询真正用到的列，多余的列只会增加存储和合并成本。同时聚合列可以单独指定压缩编码：

ALTER TABLE events
    ADD PROJECTION agg_by_user
    (
        SELECT
            user_id,
            event_type,
            toStartOfHour(event_time) AS hour,
            count() AS cnt,
            sum(value) AS total CODEC(ZSTD(3))
        GROUP BY user_id, event_type, hour
    );

对基数不高的 user_id、event_type，Delta 或 DoubleDelta 编码收益有限，用 ZSTD 即可；对单调递增的时间列，Delta 编码通常能再压 2~3 倍。压缩编码的选型逻辑与 /clickhouse-columnar-compression/ 一致。

3. 优化器如何选择 Projection

3.1 用 EXPLAIN 验证命中

Projection 是否被用上，绝不能靠猜。打开 EXPLAIN 观察计划里是否出现 ReadFromMergeTree ... Projection: proj_by_user：

EXPLAIN
SELECT user_id, count()
FROM events
WHERE user_id = 42
GROUP BY user_id;

若计划仍是 ReadFromMergeTree (full)，说明优化器认为投影不划算或无法匹配。此时逐步排查：

• 查询的聚合函数是否都有对应的状态函数；
• WHERE/GROUP BY 的列是否都在投影定义里；
• 投影的排序键前缀是否与查询的过滤/排序方向一致；
• 是否命中了 optimize_use_projections 开关。

想要更细的执行信息，可以看 EXPLAIN PLAN actions = 1 或 EXPLAIN PIPELINE，后者会显示每个算子的输入输出行数估算，能直观看出投影是否真的把扫描量降下来了。

3.2 强制使用与禁用

-- 全局或会话级开关（默认 1）
SET optimize_use_projections = 1;

-- 强制某条查询使用指定投影
SELECT ... FROM events
SETTINGS force_optimize_projection = 1;

force_optimize_projection = 1 表示如果投影不可用就报错，适合压测与回归验证，能防止「以为在用投影其实没用到」的假象。反过来 force_optimize_projection_name = 'proj_by_user' 可指定具体投影，用于对比不同投影的收益。

3.3 与跳过索引的协同

Projection 与数据跳过索引（min-max、set、bloom_filter）解决的是不同层次的问题：跳过索引在原表粒度上减少读取，Projection 则换一份更适合的数据布局。二者可以叠加，但优先级上优化器先考虑投影。如果查询过滤条件恰好命中 /clickhouse-query-pruning-indexes/ 里的跳过索引，可能不建投影就够用——先用索引，再考虑投影。

一个常见的误判是：查询带 WHERE event_type = 'click'，而 event_type 上有 bloom_filter 跳过索引，此时即使建了 aggregate 投影，优化器也可能因为原表扫描量已经很小而不选投影。验证手段就是 force_optimize_projection = 1 对比两次执行的实际耗时。

4. 预聚合实战：aggregate 投影

4.1 典型场景

假设有一张原始事件表，写入按 (event_time, event_type) 排序，但报表频繁按 user_id 维度统计：

CREATE TABLE events
(
    event_time DateTime,
    event_type LowCardinality(String),
    user_id UInt64,
    value Float64
)
ENGINE = MergeTree
PARTITION BY toDate(event_time)
ORDER BY (event_time, event_type);

ALTER TABLE events
    ADD PROJECTION agg_by_user
    (
        SELECT
            user_id,
            event_type,
            toStartOfHour(event_time) AS hour,
            count() AS cnt,
            sum(value) AS total
        GROUP BY user_id, event_type, hour
    );

ALTER TABLE events MATERIALIZE PROJECTION agg_by_user;

之后这条查询会自动改走投影：

SELECT event_type, sum(cnt), sum(total)
FROM events
WHERE user_id = 10086 AND event_time >= now() - INTERVAL 7 DAY
GROUP BY event_type;

注意 WHERE event_time >= ... 这个条件依然有效：投影里保留了 hour 列，优化器会把它翻译成对 hour 的范围过滤，从而裁剪掉大部分投影分区。

4.2 与 AggregatingMergeTree 方案对比

只要聚合维度都来自同一张表，投影几乎总是更省心的选择。当聚合需要 JOIN 维表时，才回到物化视图路线。

4.3 收益估算方法

上线投影前应先估算收益，避免「建了却没用」。步骤：

1. 用 EXPLAIN 确认原查询扫描的行数与字节数（EXPLAIN PLAN 会给出估算）；
2. 建投影并 MATERIALIZE（或先在副本上验证）；
3. 再次 EXPLAIN，对比扫描量级；
4. 用 SELECT ... SETTINGS max_threads=1 做单线程前后对比，排除并发干扰。

如果扫描量没有数量级下降，说明投影的分组基数没有有效压缩，收益有限。

4.4 基数陷阱

aggregate 投影的分组数不能失控。如果 GROUP BY 的维度组合基数极高（比如 user_id × request_id），投影本身会膨胀到与原表相当甚至更大，收益归零。经验阈值：投影行数应显著小于原表行数（一个数量级以上），否则不如直接建 normal 投影或跳过索引。

一个诊断方法是先跑一次「如果建投影会得到多少行」的估算查询：

SELECT count() FROM (
    SELECT user_id, event_type, toStartOfHour(event_time)
    FROM events
    GROUP BY user_id, event_type, toStartOfHour(event_time)
);

若结果与 SELECT count() FROM events 处于同一量级，就不该建这个 aggregate 投影。

5. 运维与调优

5.1 查看投影状态

SELECT
    table,
    name,
    type,
    formatReadableSize(data_compressed_bytes) AS compressed,
    rows
FROM system.projections
WHERE table = 'events';

-- 每个 part 里投影的实际大小
SELECT
    partition,
    name,
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS rows
FROM system.projection_parts
WHERE table = 'events'
GROUP BY partition, name
ORDER BY partition DESC;

system.projection_parts 是排查「投影到底占了多少空间、是否被合并收敛」的第一手数据。若某分区的投影行数迟迟不降，说明后台合并没跟上。

5.2 关键参数

5.3 删除与重建

ALTER TABLE events DROP PROJECTION proj_by_user;

DROP PROJECTION 会立即释放投影占用的磁盘（不等待合并）。重建时记得重新 MATERIALIZE。注意：修改投影定义必须先 DROP 再 ADD，ClickHouse 不支持原地 ALTER PROJECTION。对于生产大表，重建投影等同于一次全量重写，务必规划在维护窗口内。

5.4 与冷热分层共存

如果表配置了 TTL 把老分区搬到 S3 或其它卷，投影会随 part 一起迁移，无需额外配置。但要注意：投影自身的存储放大也会计入冷数据成本，长期看是一笔持续开销。若投影主要用于近 7 天热查询，可以考虑对老分区用 ALTER TABLE ... DROP PROJECTION 配合分区级操作，但 ClickHouse 目前不支持按分区选择性保留投影，只能整表删除。这一限制需要在设计阶段就纳入成本考量。

6. 常见陷阱

• 忘记 MATERIALIZE：只 ADD 不物化，历史数据查不到投影，优化器可能直接忽略它。
• 查询改写不匹配：投影定义里用了 toStartOfHour(event_time)，查询里却写 toStartOfDay，两者无法匹配，投影不会被选中。
• 写入吞吐骤降：每条 INSERT 都要额外写投影，高并发小批量写入场景下放大明显，应配合批量写入策略。若写入已是瓶颈，先看 /clickhouse-insert-throughput-tuning/ 的合批手段。
• 存储估算失误：上线前务必用 system.projection_parts 或小规模样本压测，估算投影的存储与合并开销。
• 过多投影：每多一个投影就多一份合并负担。生产表通常控制在 2~3 个投影以内，且每个都对应明确的慢查询。
• 依赖投影掩盖建模问题：如果同一张表需要五六个投影才能覆盖查询，往往说明主键或分区设计本身有问题，应回到 Schema 建模层面重新权衡。

小结

Projection 是 ClickHouse 里「零改 SQL」的加速利器：normal 投影换布局、aggregate 投影换预计算，代价是存储与写入/合并放大。落地路径建议是——先用 EXPLAIN 定位慢查询瓶颈，优先尝试数据跳过索引，仍不够时再为高频且稳定的聚合形态建 aggregate 投影，并通过 system.projection_parts 持续监控其空间与收敛情况。把它当作「有代价的索引」来管理，而不是「免费的性能开关」；每增加一个投影，都要能回答「它替掉了哪条慢查询、省了多少扫描量、付出了多少存储」这三个问题。