ClickHouse 投影（Projections）详解

投影（Projections） 是 ClickHouse 在 v21.6+ 版本中引入的一项革命性功能，它是一种强大的预聚合与查询优化技术，能够自动为同一张表构建多个“物化视图式”的存储结构，根据查询模式自动选择最优路径，极大提升复杂查询的性能。

本篇将深入详解 ClickHouse 投影（Projections） 的原理、语法、使用场景和最佳实践。

---

🚀 一、什么是投影（Projection）？

✅ 定义

投影 是 ClickHouse 表中的一种可选存储结构，它为表中的数据按不同方式（如不同排序、聚合、列子集）预先组织和存储，查询时 ClickHouse 会自动选择最匹配的投影来加速查询。

💡 简单理解：
投影 = 自动维护的“智能物化视图”嵌入在主表中

---

🔁 与物化视图的对比

特性	物化视图	投影（Projection）
是否独立表	是（需手动管理）	否（与主表一体）
维护复杂度	高（需建 MV、目标表）	低（定义后自动维护）
查询透明性	需显式查 MV 表	透明，查询主表自动命中
更新一致性	可能延迟	与主表完全一致（原子写入）
支持聚合	是	是（支持 GROUP BY）
支持不同排序	需建多个 MV	一个表可定义多个投影

✅ 投影是“更高级、更透明、更易用”的预聚合方案

---

🏗️ 二、投影的工作原理

当数据写入主表时：

1. 主表数据按 ORDER BY 排序存储
2. 所有定义的投影也同时被更新
3. 每个投影按自己的 SELECT 逻辑组织数据

查询时：

• ClickHouse 解析 SQL
• 判断是否有匹配的投影
• 如果有，直接从投影读取（跳过主表）
• 如果没有，回退到主表查询

✅ 效果：无需改查询 SQL，自动加速

---

📦 三、投影语法详解

CREATE TABLE user_log (
    user_id UInt32,
    event_date Date,
    city String,
    action String,
    duration UInt32,

    -- 定义投影
    PROJECTION city_agg_proj (
        SELECT
            city,
            toStartOfDay(event_date) AS hour,
            count(*) AS cnt,
            sum(duration) AS total_duration
        GROUP BY city, hour
        ORDER BY city, hour
    ),

    PROJECTION user_sort_proj (
        SELECT
            user_id,
            event_date,
            action
        ORDER BY (user_id, event_date)
    )

) ENGINE = MergeTree()
ORDER BY (event_date, user_id);

✅ 注意：

• 投影定义在表结构内部
• 每个 PROJECTION 是一个独立的存储结构
• 写入主表时，所有投影自动更新

---

🌟 四、核心使用场景

1. 自动预聚合（替代多个物化视图）

场景：频繁按城市、小时统计

-- 查询（无需改 SQL）
SELECT 
    city, 
    toStartOfDay(event_date) AS hour,
    COUNT(*), 
    SUM(duration)
FROM user_log 
GROUP BY city, hour;

如果定义了 city_agg_proj 投影，ClickHouse 会自动使用投影，避免扫描主表。

✅ 性能提升：从 10 秒 → 0.1 秒

---

2. 不同排序优化（加速不同查询模式）

场景：既有按时间查，也有按用户查

-- 投影1：按时间排序（默认）
ORDER BY (event_date, user_id)

-- 投影2：按用户排序
PROJECTION user_order_proj (
    SELECT * ORDER BY (user_id, event_date)
)

查询 WHERE user_id = 1001 ORDER BY event_date 时，自动使用 user_order_proj，避免全表扫描。

---

3. 列子集加速（减少 I/O）

PROJECTION simple_view (
    SELECT user_id, city, action
    ORDER BY city
)

查询 SELECT user_id, city FROM user_log WHERE city = 'Beijing' 时，只读这三列，跳过 duration 等大字段。

---

🔍 五、如何确认投影被使用？

1. 使用 EXPLAIN

EXPLAIN
SELECT city, COUNT(*) FROM user_log GROUP BY city;

输出中如果出现：

ReadFromMergeTree (use_projection: city_agg_proj)

说明投影被命中。

---

2. 查看系统表

SELECT 
    name,
    part_type,
    rows,
    bytes_on_disk
FROM system.parts
WHERE table = 'user_log' AND database = 'default';

你会看到多个 Parts，其中一些属于投影（可通过命名识别）。

---

⚠️ 六、注意事项与限制

项目	说明
版本要求	≥ v21.6，建议 ≥ v22.8（稳定）
写入性能	写入主表时所有投影同步更新 → 写入略慢
存储空间	每个投影都占用额外磁盘空间
不支持所有引擎	仅支持 MergeTree 及其变种
ALTER 添加投影	支持，但会触发全表重建（慎用）
事务性	投影与主表原子写入，一致性有保障

---

✅ 七、最佳实践建议

场景	推荐做法
高频聚合查询	定义 GROUP BY 投影
多种查询模式	定义多个排序投影
大宽表	定义列子集投影减少 I/O
写入频繁	评估投影数量，避免过多
新表设计	优先考虑投影而非多个物化视图
旧表添加	使用 ALTER TABLE ... ADD PROJECTION（注意重建成本）

---

🛠️ 八、实战：为现有表添加投影

-- 为 user_log 添加聚合投影
ALTER TABLE user_log 
ADD PROJECTION daily_city_stats (
    SELECT
        city,
        event_date,
        count(*) AS pv,
        uniqState(user_id) AS uv,
        sum(duration) AS total_duration
    GROUP BY city, event_date
    ORDER BY city, event_date
);

-- 触发重建（可选，立即生效）
ALTER TABLE user_log MATERIALIZE PROJECTION daily_city_stats;

⚠️ MATERIALIZE 会扫描全表，建议在低峰期执行。

---

🎯 九、总结：投影的核心价值

能力	说明
🔄 自动预聚合	替代多个物化视图，减少维护成本
🎯 查询透明加速	不改 SQL，自动命中最优路径
📦 与主表一体化	原子写入，一致性高
🚀 极致性能提升	聚合查询从秒级到毫秒级
🧩 灵活组织数据	支持不同排序、分组、列子集

🎯 投影是 ClickHouse 的“智能索引 2.0”：
它不仅是索引，更是为查询模式量身定制的存储结构。