第 10 章物化视图与 Projection

学习目标：理解 ClickHouse 物化视图（Materialized View，MV）的「INSERT 触发器」本质，能用 AggregatingMergeTree + MV + xxxState/xxxMerge 函数搭一条实时聚合流水线；理解 24.x+ 的 Projection 与 MV 的取舍；遇到「实时大屏 / 实时漏斗 / 实时 PV-UV / 实时分位数」类需求，30 秒内能在白板上画出方案。

10.0 一句话总览

「ClickHouse 的物化视图不是『预先算好结果的视图』，它是一条焊在源表上的『INSERT 触发器』 —— 你每往源表灌一行，它就跟着算一行写到目标表。」

┌──────────────┐                      ┌────────────────┐
│   events_raw │   (源表，明细)         │   events_agg   │  (目标表，聚合)
│              │                      │                │
│  ts uid url  │   ── INSERT 来了 ──▶  │  hour url uvS  │
│  ── INSERT ─▶│   (MV 触发器自动跑)   │  pvSum quantile│
└──────────────┘                      └────────────────┘
                       ▲
                       │
              ┌────────┴────────┐
              │  MaterializedView│  ←─ 一段 SELECT，对「这次 INSERT 的那批行」执行
              │  events_to_agg   │     把 SELECT 结果再 INSERT 到目标表
              └─────────────────┘

⚠️ 重要反直觉：MV 的 SELECT 只对「本次 INSERT 的那个 Block」执行，不是定时全表 refresh，也不是看「整个源表」。这一句话理解了，本章就拿下了 80%。

10.1 普通视图 vs 物化视图：先把"视图"两个字搞清楚

10.1.1 普通 `VIEW` —— 只是查询别名

sql

CREATE VIEW v_today_pv AS
SELECT toDate(ts) AS d, count() AS pv
FROM learn_ck.events_raw
WHERE toDate(ts) = today()
GROUP BY d;

SELECT * FROM v_today_pv;   -- 每次都现算，等同于把 SQL 复制粘贴

不存数据，只存 SQL 文本。
每次 SELECT v_today_pv，引擎都把那段 SQL 重新执行一遍 —— 几亿行扫一遍，照样要等。
用途：把复杂 SQL 起个名字，给业务方一个干净的「逻辑表」。

10.1.2 物化视图 `MATERIALIZED VIEW` —— 真实存数据

sql

CREATE MATERIALIZED VIEW mv_hour_pv
TO learn_ck.events_agg
AS
SELECT
    toStartOfHour(ts) AS hour,
    url,
    count()           AS pv,
    uniqState(uid)    AS uv_state
FROM learn_ck.events_raw
GROUP BY hour, url;

有一张真实的目标表（TO events_agg）。
每次往源表 events_raw 灌数据，MV 拿到这一批行，按 SELECT 算一遍，结果立刻 INSERT 到目标表。
查询时直接读 events_agg —— 几毫秒返回，不用再扫亿级原始数据。

📌 生活类比 · 快递站的"自动复印机" 想象一个小区快递站：每来一个包裹（INSERT 一行原始事件），快递员就自动复印一张缩略单（执行 MV 的 SELECT）放到隔壁货架（目标表）。
老板每天对账时不用翻所有包裹，扫一眼缩略单堆就行（查询目标表，秒出）。
复印是实时的，不是夜里跑批（不是 PG 的 REFRESH MATERIALIZED VIEW）。
但是你老包裹的缩略单是没有的 —— 复印机是装上去之后才开始工作的（POPULATE 的坑，下面会讲）。

10.2 物化视图的本质：INSERT 触发器（重点反复强调）

源表 events_raw                            目标表 events_agg
─────────────────                          ────────────────────
                                           
INSERT (block #1: 1万行)  ──┐              
                            │              
                            ▼              
                   ┌─────────────────┐     
                   │ MV 触发：把这1万行  │   
                   │  当作FROM源跑SELECT │── INSERT (聚合后 100 行) ──▶
                   └─────────────────┘     
                                           
INSERT (block #2: 5千行)  ──┐              
                            │              
                            ▼              
                   ┌─────────────────┐     
                   │ MV 再次触发，只针对 │   
                   │  block #2 的5千行  │── INSERT (聚合后 50 行) ──▶
                   └─────────────────┘

三条铁律：

MV 的 SELECT 的 FROM 不是真的查源表整张表，而是「把本次 INSERT 的 Block 临时塞到 FROM 位置」。所以你写 SELECT count() FROM events_raw 在 MV 里得到的是「本次 INSERT 的行数」，而不是源表总行数。
MV 不会回看历史数据。建 MV 之前已经存在的 200 亿行原始数据，MV 一行都不知道。要补历史，要么 POPULATE，要么手动 INSERT INTO events_agg SELECT ... FROM events_raw。
MV 不是异步。INSERT 源表的事务里，MV 的 SELECT + 写目标表是同步完成的。MV 写失败 → 源表 INSERT 也会失败（受 materialized_views_ignore_errors 影响，默认严格）。

📌 与 MySQL 触发器对比：MySQL 的 BEFORE/AFTER INSERT TRIGGER 是逐行触发；ClickHouse 的 MV 是逐 Block 触发，一次拿到几千上万行做向量化聚合，性能差距几个数量级。

📌 与 PG 物化视图对比：PG 的 CREATE MATERIALIZED VIEW 默认是「快照」语义，要看到新数据必须手动 REFRESH MATERIALIZED VIEW（或用 pg_cron 定时跑）。CK 的 MV 是「流式」语义，写入即可见。两者根本不是一种东西。

10.3 目标表：`TO target_table` vs 隐式建表

sql

-- 写法 ①：显式 TO，推荐！
CREATE MATERIALIZED VIEW mv_hour_pv
TO learn_ck.events_agg                 -- 目标表必须先建好
AS SELECT ... FROM events_raw GROUP BY ...;

-- 写法 ②：隐式建表（不推荐）
CREATE MATERIALIZED VIEW mv_hour_pv
ENGINE = AggregatingMergeTree           -- 自动建一张内部表 .inner_id.<uuid>
ORDER BY (hour, url)
AS SELECT ... FROM events_raw GROUP BY ...;

为什么强烈推荐 TO 写法：

维度	`TO target_table`	隐式 `.inner_id.xxxx`
表结构是否清晰	✅ 你自己建，看得见	❌ 内部表名带 UUID，难维护
多个 MV 写同一目标表	✅ 完全可以	❌ 每个 MV 一张内部表
重建 MV 不丢数据	✅ DROP MV 不影响目标表	❌ DROP MV 时数据一起没
备份恢复	✅ 备份目标表即可	❌ 还要操心内部表

一句话：生产永远用 TO，隐式表只在 demo/测试里用。

10.4 `POPULATE` 的坑（写在最显眼处）

sql

-- 看起来很美：建 MV 的时候顺手把历史数据也算进去
CREATE MATERIALIZED VIEW mv_hour_pv
TO learn_ck.events_agg
POPULATE                              -- ⚠️ 危险关键字
AS SELECT ... FROM events_raw GROUP BY ...;

坑在哪里：

时间线：
  T0  执行 CREATE MV ... POPULATE ...
       │
       │  此时 ClickHouse 开始跑：
       │     INSERT INTO events_agg SELECT ... FROM events_raw  (扫历史 10 亿行)
       │  这一步可能跑 10 分钟。
       │
  T1  外部业务在 T0~T1 之间又往 events_raw 灌了 200 万行新数据。
       │
       │  ❌ 这 200 万行 既没被 POPULATE 的快照拿到（因为快照早就开始扫了）
       │  ❌ 也没被 MV 触发器拿到（因为 MV 还没"建好"）
       │
  T2  POPULATE 完成，MV 正式开始当触发器用。
       │
       └─▶ 结果：T0~T1 这段时间的数据在 events_agg 里永久缺失。

生产正确姿势（避免数据丢失）：

sql

-- 1. 先停止源表写入，或者切到一个 staging 源表
-- 2. 建 MV（不带 POPULATE）
CREATE MATERIALIZED VIEW mv_hour_pv TO events_agg
AS SELECT ... FROM events_raw GROUP BY ...;

-- 3. 手动补历史
INSERT INTO events_agg
SELECT ... FROM events_raw
WHERE ts < now() - INTERVAL 1 SECOND       -- 留一秒安全区
GROUP BY ...;

-- 4. 恢复源表写入

或者更现代的做法 —— 用 CREATE MATERIALIZED VIEW ... TO ... EMPTY 加事后追数；亿级数据建议拆分区分批补。

10.5 链式物化视图：MV1 → MV2 → MV3

MV 也可以串成一条流水线：MV1 写到 agg1，agg1 又是另一个 MV2 的源 …… 像车间的传送带一样层层加工。

   events_raw
       │  (MV1: 1分钟聚合)
       ▼
   events_minute              ←  细粒度，留 7 天
       │  (MV2: 1小时 rollup)
       ▼
   events_hour                ←  小时粒度，留 90 天
       │  (MV3: 1天 rollup)
       ▼
   events_day                 ←  天粒度，留 3 年

SQL 骨架：

sql

CREATE MATERIALIZED VIEW mv_hour FROM events_minute
TO events_hour
AS SELECT
    toStartOfHour(minute) AS hour,
    url,
    sumState(pv_state)    AS pv_state    -- 注意：这里 sumState 套在已经是 state 的列上
FROM events_minute
GROUP BY hour, url;

⚠️ 关键约束：链式 MV 的中间表通常用 AggregatingMergeTree，列必须是 AggregateFunction(uniq, UInt64) 这种「State」类型，下游再用对应的 xxxMergeState / xxxMerge 拼接还原。

10.6 杀手锏：`AggregatingMergeTree` + MV + `xxxState/xxxMerge`

这是 ClickHouse 最经典的一招，也是面试必考。我们用一个完整案例打通它。

10.6.1 场景：网站埋点实时大屏

需求：

实时统计每小时 / 每个 URL 的：PV、UV、平均停留时长、停留时长 P95。
原始事件量 1 万 QPS（亿级 / 天），大屏每秒刷新。

10.6.2 表结构

源表（明细）：

sql

CREATE TABLE learn_ck.events_raw
(
    ts        DateTime,
    uid       UInt64,
    url       LowCardinality(String),
    duration  UInt32                  -- 停留时长（毫秒）
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (ts, uid);

目标表（聚合中间状态）：

sql

CREATE TABLE learn_ck.events_agg
(
    hour     DateTime,
    url      LowCardinality(String),
    pv       UInt64,                                       -- 普通 SUM 列
    uv_state AggregateFunction(uniq, UInt64),              -- UV 的中间状态
    dur_avg_state AggregateFunction(avg, UInt32),          -- 平均时长状态
    dur_p95_state AggregateFunction(quantile(0.95), UInt32)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, url);

AggregateFunction(uniq, UInt64) 的意思是：这个列存的是 uniq 函数的"中间运算状态"（HyperLogLog 草图），不是最终值。SummingMergeTree 给数值求和，AggregatingMergeTree 给「任意聚合函数」合并它们的中间状态。

10.6.3 物化视图

sql

CREATE MATERIALIZED VIEW learn_ck.mv_events_to_agg
TO learn_ck.events_agg
AS SELECT
    toStartOfHour(ts)               AS hour,
    url,
    count()                          AS pv,
    uniqState(uid)                   AS uv_state,           -- ★ State 函数
    avgState(duration)               AS dur_avg_state,
    quantileState(0.95)(duration)    AS dur_p95_state
FROM learn_ck.events_raw
GROUP BY hour, url;

约定：聚合函数加后缀 State 表示"我要把这一批数据聚合成中间状态"，留给下游/查询时再 Merge 还原。

10.6.4 查询：用 `xxxMerge` 还原最终值

sql

SELECT
    hour,
    url,
    sum(pv)                          AS pv,
    uniqMerge(uv_state)              AS uv,                 -- ★ Merge 还原
    avgMerge(dur_avg_state)          AS dur_avg_ms,
    quantileMerge(0.95)(dur_p95_state) AS dur_p95_ms
FROM learn_ck.events_agg
WHERE hour >= now() - INTERVAL 1 HOUR
GROUP BY hour, url
ORDER BY pv DESC
LIMIT 10;

为什么要 State / Merge 而不是直接存最终值？

错误想法：MV 直接 SELECT count(distinct uid) → 写入 uv 列
           ↓
问题：同一个 (hour, url) 在不同时间被 INSERT 了 N 次（块切片），
      每次只看见这次 block 里的 uid，根本算不出「全局唯一」。

正确做法：每次 INSERT 都把本批 uid 转成「HLL 草图」追加进去，
         AggregatingMergeTree 后台合并时把多个 HLL 草图合并成一个，
         查询时 uniqMerge() 一次性算出真实 UV。

📌 一句话记忆：xxxState 是把数据"装罐头"，xxxMerge 是开罐头吃掉。 两者必须成对出现。

10.7 Projection ——「表内嵌的二级排序 / 二级聚合」

Projection（投影）是 19.6 引入、24.x 后稳定的新机制，和 MV 是兄弟，但思路完全不同。

10.7.1 直观理解

MV 是隔壁车间复印一份；Projection 是同一张表里再印一份不同顺序的目录。

                events_raw 主表（按 ts 排序）
                ┌───────────────────────────┐
                │  data parts（按主排序）    │
                ├───────────────────────────┤
                │  projection p_by_url      │  ← 同表内嵌的"分身"，按 url 排序
                ├───────────────────────────┤
                │  projection p_hour_agg    │  ← 同表内嵌的预聚合
                └───────────────────────────┘

每个 Projection 是主表 Part 内部的子目录，引擎自己维护：写入主表 → 引擎自动给每个 Projection 同步写一份。

10.7.2 语法

sql

-- 二级排序 Projection：让按 url 的查询也能跳数
ALTER TABLE learn_ck.events_raw
ADD PROJECTION p_by_url
(
    SELECT * ORDER BY url, ts
);

-- 预聚合 Projection：直接存 GROUP BY 结果
ALTER TABLE learn_ck.events_raw
ADD PROJECTION p_hour_agg
(
    SELECT
        toStartOfHour(ts) AS hour,
        url,
        count()           AS pv,
        uniq(uid)         AS uv
    GROUP BY hour, url
);

-- 让历史数据也具体化（没这一步，旧 Part 里没有 projection）
ALTER TABLE learn_ck.events_raw
MATERIALIZE PROJECTION p_hour_agg;

10.7.3 优化器自动选择

写完 Projection 不用改任何业务 SQL：

sql

SELECT toStartOfHour(ts) AS h, url, count() FROM events_raw
GROUP BY h, url ORDER BY count() DESC LIMIT 10;

执行时优化器看到 p_hour_agg 已经把 (hour, url) → count() 算好了，就直接读 Projection 而不是扫主表。可以用 EXPLAIN PROJECTIONS = 1 SELECT ... 看到选了哪个 Projection。

10.7.4 MV vs Projection 选型决策表

维度	Materialized View	Projection
物理位置	独立的目标表	主表 Part 内的子目录
维护者	你自己写 SQL 触发	引擎自动同步
查询是否要改 SQL	✅ 必须查目标表 / 用 xxxMerge	❌ 业务 SQL 不变，优化器自动选
支持 JOIN 源	❌ 只看本批 INSERT，JOIN 拿不到全局	❌ 只对单表生效
支持跨表聚合	✅ 多个 MV 写一张目标表	❌ 只能投影自己
历史数据	需要 POPULATE / 手动补	`MATERIALIZE PROJECTION` 一句话补完
DROP / 修改成本	DROP MV 要重灌目标表	`ALTER TABLE ... DROP PROJECTION` 即可
存储放大	一个 MV = 一张表	一个 Projection = 主 Part 的额外子目录
典型场景	实时大屏、跨表 ETL、链式聚合	多查询模式的同一张事实表，二级排序加速

经验法则：

同一张表想用多种排序键 / 多种聚合粒度查 → Projection。
跨表 / 链式 / 实时聚合大屏 → MV。
不确定 → 先 Projection，简单且零改动；不行再上 MV。

10.8 真实案例：广告点击实时大屏

需求：

每秒刷新「过去 5 分钟 PV / UV / 各广告位点击 Top 10 / 点击均价」。
QPS 5 万，30 天留存。

方案：

   ad_click_raw  (MergeTree, PARTITION BY 日, ORDER BY ts)
        │
        │  MV: mv_click_to_minute
        ▼
   ad_click_minute (AggregatingMergeTree, ORDER BY (minute, ad_id))
        │  pv UInt64, uv_state AggFunc(uniq, UInt64),
        │  cost_sum_state AggFunc(sum, UInt64),
        │  cost_avg_state AggFunc(avg, UInt64)
        │
        │  MV: mv_minute_to_hour
        ▼
   ad_click_hour    (同结构，按小时 rollup，留 90 天)

大屏 SQL：

sql

SELECT
    ad_id,
    sum(pv)                          AS pv,
    uniqMerge(uv_state)              AS uv,
    sumMerge(cost_sum_state) / 100.0 AS cost_yuan,
    avgMerge(cost_avg_state) / 100.0 AS cpc_yuan
FROM ad_click_minute
WHERE minute >= now() - INTERVAL 5 MINUTE
GROUP BY ad_id
ORDER BY pv DESC
LIMIT 10;

实测在 8C16G 单节点上：扫过去 5 分钟数据约 5 万行（已聚合到分钟），耗时 8~15 ms。如果直接查 1500 万行原始 ad_click_raw，要 800 ms 起。

10.9 📌 与其他数据库的对比小框

概念	ClickHouse	MySQL	PostgreSQL
普通视图	`CREATE VIEW`（仅查询别名，不存数据）	同左	同左
物化视图	`CREATE MATERIALIZED VIEW`（INSERT 触发器，流式）	❌ 不支持	`CREATE MATERIALIZED VIEW`（快照，需 `REFRESH MATERIALIZED VIEW`）
触发器	没有传统行级 trigger，MV 是更高效的"批级触发器"	`BEFORE/AFTER INSERT TRIGGER`（行级，慢）	同 MySQL
预聚合表	Projection（同表内嵌） / SummingMergeTree	手写汇总表 + ETL	物化视图（需手动刷新）
索引视图	Projection 部分等价	❌	部分扩展支持

经典面试金句：

"PG 的物化视图是冰箱里事先做好的菜，要新鲜得自己再煮一遍（REFRESH）；CK 的物化视图是自动的小饭堂，每来一份原料立刻炒一道菜端出来。"

10.10 本章小结

┌──────────────────────────────────────────────────────────────┐
│                     第 10 章核心要点                            │
├──────────────────────────────────────────────────────────────┤
│                                                                │
│  ① CK 物化视图 = 焊在源表上的「批级 INSERT 触发器」              │
│     不是定时刷新，不是看全表，只看本次 INSERT 的 Block。         │
│                                                                │
│  ② 必须配 `TO target_table`，目标表用 AggregatingMergeTree。   │
│                                                                │
│  ③ POPULATE 有数据丢失风险 → 生产用「先建 MV，再手动补历史」。  │
│                                                                │
│  ④ 实时聚合三件套：                                              │
│     AggregatingMergeTree + MV + xxxState/xxxMerge              │
│     "装罐头 + 开罐头" 才能算出全局唯一/分位数等。                │
│                                                                │
│  ⑤ 链式 MV：events → 分钟 → 小时 → 天，做多档时间粒度。          │
│                                                                │
│  ⑥ Projection 是「同表内嵌的二级目录」，业务 SQL 零改动。        │
│     单表多查询模式用 Projection；跨表 / 实时大屏用 MV。          │
│                                                                │
│  ⑦ MV 写失败默认会让 INSERT 失败 → 用                            │
│     materialized_views_ignore_errors 调整。                    │
│                                                                │
└──────────────────────────────────────────────────────────────┘

10.11 面试高频题

Q1：ClickHouse 的物化视图和 PostgreSQL 的物化视图有什么本质区别？

考察点：是否真懂"物化视图"在不同数据库里的不同语义。

标准答案：

触发模型不同：PG 的物化视图是快照语义，建表后必须手动 REFRESH MATERIALIZED VIEW 才能看到新数据；CK 的物化视图是流式语义，本质是焊在源表上的"批级 INSERT 触发器"，源表写入即触发 MV 把这一批数据算完写到目标表。
执行时机不同：PG 是 SELECT 时读快照；CK 是 INSERT 时实时算。
数据是否一致：PG 物化视图与源表之间存在滞后（取决于 refresh 周期）；CK 物化视图与源表之间是准实时（写入完成即可见）。
底层存储：PG 的物化视图是单独的物理表；CK 的物化视图本质是"一段写到目标表的 SQL"，通常配 TO target_table 指定一张 AggregatingMergeTree 目标表。

加分项：能说出两边的实现成本：PG 每次 REFRESH 是全表重算，所以适合小表 / 离线；CK 是按 Block 增量算，适合亿级流量实时聚合。

易错点：千万别说"CK 的 MV 也要 REFRESH" —— CK 根本没有 REFRESH 命令（24.x 实验性 REFRESHABLE MATERIALIZED VIEW 是给那些"必须看跨 INSERT 全局"的特殊场景的，不是默认行为）。

Q2：解释 `AggregateFunction(uniq, UInt64)` 这个数据类型，以及 `uniqState` / `uniqMerge` 的作用？

考察点：理解 ClickHouse 聚合状态的本质。

标准答案：

AggregateFunction(uniq, UInt64) 的含义是「这一列存放的是 uniq 聚合函数对一组 UInt64 输入的中间运算状态」（HyperLogLog 草图），不是最终的去重计数值。
uniqState(x)：把列 x 当前 Block 的所有值算成一个 HLL 草图，写到 AggregateFunction 列里。
uniqMerge(state)：把多个 HLL 草图合并还原成最终的去重计数。
AggregatingMergeTree 引擎在后台 Merge Part 时，会自动把同主键的多行 AggregateFunction 列做 Merge —— 这就是"自动汇总聚合状态"。
类似的还有 sumState / avgState / quantileState / topKState / argMaxState 等，全部成对出现 xxxState ↔ xxxMerge。

加分项：能解释为什么不直接存最终值 —— 因为 MV 是"按 Block 增量"触发的，每次只看见自己这批数据，无法做"全局"去重 / 分位数；而 HLL / TDigest 这类草图是"可合并的代数结构"。

易错点：把 uniqState 写错成 uniq。uniq() 直接出最终值（UInt64），写入 AggregateFunction 列时类型不匹配。

Q3：`POPULATE` 关键字有什么坑？生产为什么不推荐用？

考察点：踩坑经验、对 MV 触发时机的精确理解。

标准答案：

POPULATE 让 MV 在创建时先把源表里已有数据做一次全量计算写到目标表。但有两个致命问题：

POPULATE 期间的新数据会丢失：POPULATE 启动的瞬间会拿一个源表快照去扫历史，扫的过程中正常业务往源表写的新数据既不会被这个快照看到，也不会被 MV 触发器看到（MV 还没生效）。等 POPULATE 完成、MV 触发器生效，中间这段时间的数据就永久缺失。
不可中断：POPULATE 跑 10 亿数据可能要几小时，期间 OOM 或重启就一切重来。

生产正确姿势：

sql

-- 1. 不带 POPULATE 建 MV，让触发器先生效
CREATE MATERIALIZED VIEW mv_xxx TO target ...;

-- 2. 手动补历史（追到一个安全的时间点）
INSERT INTO target SELECT ... FROM source WHERE ts < now() - INTERVAL 5 SECOND ...;

加分项：能提到 EMPTY 关键字 + 离线分批补数；亿级表分区分批补避免 OOM；用 system.parts 监控目标表 Part 数量增长。

易错点：把 POPULATE 当作"刷新"用 —— 它只在建 MV 那一刻执行一次，之后就消失了。

Q4：物化视图和 Projection 怎么选？

考察点：对两个机制差异的体感。

标准答案：

选 MV 当	选 Projection 当
多张源表汇聚到一张宽表	同一张表的多种查询模式
实时大屏需要跨 Block 全局聚合	想给主表加二级排序 / 二级聚合
链式分层（分钟→小时→天）	单层预聚合即可
业务方愿意改 SQL 查目标表	业务方 SQL 不能动
需要灵活的目标表引擎（Replicated / Distributed）	接受跟主表共享生命周期

核心一句话：

MV 是独立的表，靠你写 SQL 触发，业务 SQL 必须改（要查目标表 + 用 xxxMerge）；
Projection 是主表的内嵌分身，引擎自动维护，业务 SQL 不用改，优化器自动选。

加分项：能补一句"Projection 有 MATERIALIZE PROJECTION 一键回填历史，MV 没有"。

易错点：以为 Projection 能跨表 —— 它只是对主表自己的另一种排序 / 预聚合。

Q5：MV 失败会不会导致源表 INSERT 失败？怎么调？

考察点：MV 错误处理与故障隔离。

标准答案：

默认情况下 MV 是同步写入：源表 INSERT → MV 跑 SELECT → 写目标表。任何一个 MV 失败（写入冲突、聚合内存不足、Schema 不匹配）都会让源表 INSERT 整体失败。
通过 SETTINGS materialized_views_ignore_errors = 1 让 MV 错误降级为日志告警，源表 INSERT 继续成功。
通过 parallel_view_processing = 1 让一个源表上的多个 MV 并行跑而不是串行。
通过 deduplicate_blocks_in_dependent_materialized_views 控制依赖 MV 的去重行为（24.x 起默认开启更安全）。

加分项：能区分 MV 失败 vs 目标表写失败的不同根因；能说出 system.errors / system.query_log 里怎么定位 MV 错误。

易错点：以为打开 materialized_views_ignore_errors 就高枕无忧 —— 实际上 MV 写失败=目标表数据缺失，事后必须手动补数。

Q6：链式物化视图怎么搭？中间表为什么必须用 `AggregatingMergeTree` + `xxxState`？

考察点：理解 State 类型在层级聚合中的不可替代性。

标准答案：

链式的目的是分层降粒度：分钟 → 小时 → 天，每层数据量缩 60 倍。

sql

-- L1: 原始表 → 分钟聚合
CREATE MATERIALIZED VIEW mv_to_minute TO events_minute AS
SELECT toStartOfMinute(ts) AS minute, url,
       countState() AS pv_state, uniqState(uid) AS uv_state
FROM events_raw GROUP BY minute, url;

-- L2: 分钟 → 小时聚合（中间状态再聚合）
CREATE MATERIALIZED VIEW mv_to_hour TO events_hour AS
SELECT toStartOfHour(minute) AS hour, url,
       countMergeState(pv_state) AS pv_state,        -- ★ 注意 MergeState
       uniqMergeState(uv_state)  AS uv_state
FROM events_minute GROUP BY hour, url;

关键点：

中间表（events_minute）必须是 AggregatingMergeTree，且列类型是 AggregateFunction(...)。
下游 MV 用 xxxMergeState(state) 来「把多个状态合并成一个新状态」（不是 Merge，那是出最终值）。
最终查询时用 xxxMerge(state) 出最终值。

加分项：能说出"MV2 也是 MV1 写入触发的"，所以如果 MV1 写入分钟表是 INSERT，MV2 自动会看到这次 INSERT 的 Block。可以画一条数据流的箭头图。

易错点：在 L2 里直接写 count() uniq() —— 输入已经是状态了，无法再算原始计数。

Q7：MV 的 SELECT 里能用 JOIN 吗？有什么坑？

考察点：理解 MV 触发模型对 JOIN 的影响。

标准答案：

可以用 JOIN，但只对本次 INSERT 的 Block 与右表生效：

**左表（源表）**只看见本次 INSERT 的几千行，不是全表。
右表（如维度表 / 字典）每次 MV 触发时都会被读 —— 如果是大表，性能爆炸。

推荐姿势：

右表用 Dictionary 字典（驻留内存），通过 dictGet() 函数查询，性能最好。
右表用 Join 引擎（也是常驻内存的 hash table）。
右表用 小表 + ANY LEFT JOIN，避免笛卡尔放大。
绝对不要 JOIN 另一张大事实表 —— 每次 INSERT 都会扫一遍那张大表。

加分项：能解释"MV 看不到源表全局"的根本原因 —— MV 是把本批 INSERT 替换成 FROM 的输入，所以 count() 是这次的，不是历史总和。

易错点：在 MV 里写 SELECT a.x, b.y FROM events_raw a JOIN big_dim b 期待 b 被全部读 —— 实际上 b 每次都被全表扫，CPU 爆炸。

Q8：Projection 在什么场景下不会被命中？有哪些限制？

考察点：对 Projection 优化器边界的理解。

标准答案：

Projection 不会被命中的常见情况：

查询里有 Projection 没存的列（投影只能"覆盖"自己定义的列）。
WHERE 用了 Projection 排序键之外的高选择性条件，优化器认为读主表反而更优。
Projection 还没 Materialize（新增 Projection 默认只对未来数据生效，旧 Part 没投影）。
使用了 FINAL —— Projection 不参与 ReplacingMergeTree 的 FINAL 去重，会被绕过。
使用了 SAMPLE —— Projection 不支持采样。
用了 Projection 不支持的函数 / 子查询 / JOIN。

调试方法：

sql

EXPLAIN PROJECTIONS = 1
SELECT ... FROM events_raw WHERE ...;

会显示候选 Projection 列表和最终选择。

加分项：能提"Projection 不能跨表，不能聚合带子查询"；能用 system.projection_parts 看每个 Part 的投影实际大小。

易错点：以为 ALTER ADD PROJECTION 立即对所有数据生效 —— 必须 MATERIALIZE PROJECTION 才会回填到旧 Part。

📌 下一章预告：第 11 章我们讲 TTL、分区与数据生命周期 —— 怎么让冷数据自动从 SSD 搬到 HDD 再搬到 S3，怎么让 90 天前的数据自动消失，以及 OPTIMIZE FINAL 的代价警告。

🎬 可视化演示

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码

mv_query.py

python

#!/usr/bin/env python3
"""
第 10 章 · 物化视图 + Projection 查询对比

演示 4 件事:
  1. 直接扫源表 mv_events_raw 算 PV/UV    → 慢 (扫全量明细)
  2. 查 MV 目标表 mv_events_agg + xxxMerge → 快 (扫聚合)
  3. 查 Projection p_hour_agg               → 业务 SQL 不变, 优化器自动选
  4. EXPLAIN PROJECTIONS 看优化器选了谁

前置:
    1. 跑 init.sql 建表
    2. 跑 seed.py --once --total 1000000 (或 --duration 30) 灌点数据

依赖:
    pip install clickhouse-connect
"""
from __future__ import annotations

import argparse
import time
from typing import Tuple

import clickhouse_connect


def time_query(client, sql: str, label: str) -> Tuple[float, list]:
    t0 = time.time()
    rs = client.query(sql)
    cost = (time.time() - t0) * 1000
    print(f"\n=== {label} ===")
    print(f"SQL : {sql.strip()}")
    print(f"耗时: {cost:.1f} ms   返回行数: {len(rs.result_rows)}")
    for row in rs.result_rows[:5]:
        print(f"    {row}")
    if len(rs.result_rows) > 5:
        print(f"    ... ({len(rs.result_rows) - 5} more)")
    return cost, rs.result_rows


def get_table_size(client, table: str) -> str:
    sql = f"""
        SELECT formatReadableSize(sum(bytes_on_disk)) AS sz, sum(rows) AS rows
        FROM system.parts
        WHERE database = 'learn_ck' AND table = '{table}' AND active
    """
    r = client.query(sql).result_rows
    if not r:
        return "(none)"
    sz, rows = r[0]
    return f"{sz} / {rows} rows"


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--host", default="127.0.0.1")
    parser.add_argument("--port", type=int, default=8123)
    parser.add_argument("--user", default="default")
    parser.add_argument("--password", default="")
    args = parser.parse_args()

    client = clickhouse_connect.get_client(
        host=args.host, port=args.port,
        username=args.user, password=args.password,
    )

    print("======== 数据规模检查 ========")
    for t in ("mv_events_raw", "mv_events_agg", "mv_events_minute"):
        print(f"  {t:24s} {get_table_size(client, t)}")

    # ---------------- 1. 扫源表 ----------------
    sql_raw = """
        SELECT toStartOfHour(ts) AS hour,
               url,
               count()           AS pv,
               uniq(uid)         AS uv,
               avg(duration)     AS dur_avg,
               quantile(0.95)(duration) AS dur_p95
        FROM learn_ck.mv_events_raw
        WHERE ts >= now() - INTERVAL 1 HOUR
        GROUP BY hour, url
        ORDER BY pv DESC
        LIMIT 10
    """
    cost_raw, _ = time_query(
        client,
        # 强制不命中 projection, 直接扫主数据
        sql_raw + " SETTINGS optimize_use_projections = 0",
        "1. 直接扫明细表 (关闭 Projection)",
    )

    # ---------------- 2. 查 MV 目标表 ----------------
    sql_mv = """
        SELECT hour,
               url,
               sum(pv)                         AS pv,
               uniqMerge(uv_state)             AS uv,
               avgMerge(dur_avg_state)         AS dur_avg,
               quantileMerge(0.95)(dur_p95_state) AS dur_p95
        FROM learn_ck.mv_events_agg
        WHERE hour >= now() - INTERVAL 1 HOUR
        GROUP BY hour, url
        ORDER BY pv DESC
        LIMIT 10
    """
    cost_mv, _ = time_query(client, sql_mv, "2. 查物化视图目标表 (xxxMerge 还原)")

    # ---------------- 3. Projection 自动加速 ----------------
    cost_proj, _ = time_query(
        client,
        sql_raw + " SETTINGS optimize_use_projections = 1",
        "3. 同样的明细 SQL, 让 Projection 自动接管",
    )

    # ---------------- 4. EXPLAIN PROJECTIONS ----------------
    plan = client.query(
        "EXPLAIN PROJECTIONS = 1 " + sql_raw
    ).result_rows
    print("\n=== 4. EXPLAIN PROJECTIONS = 1 看优化器决策 ===")
    for row in plan:
        print(f"    {row[0]}")

    # ---------------- 5. 总结对比 ----------------
    print("\n======== 性能汇总 ========")
    print(f"  扫源表           : {cost_raw:8.1f} ms")
    print(f"  查 MV 聚合表     : {cost_mv:8.1f} ms   ({cost_raw/cost_mv:.1f}x 加速)")
    print(f"  Projection 自动  : {cost_proj:8.1f} ms   ({cost_raw/cost_proj:.1f}x 加速)")
    print("\n结论：")
    print("  - MV: 业务 SQL 要改 (查目标表 + xxxMerge)，但能跨表/链式")
    print("  - Projection: 业务 SQL 零改动, 引擎自动选, 但只能在单表内")


if __name__ == "__main__":
    main()

mv_query.py ↗

第 10 章 物化视图与 Projection ​

10.0 一句话总览 ​

10.1 普通视图 vs 物化视图：先把"视图"两个字搞清楚 ​

10.1.1 普通 VIEW —— 只是查询别名 ​

10.1.2 物化视图 MATERIALIZED VIEW —— 真实存数据 ​

10.2 物化视图的本质：INSERT 触发器（重点反复强调） ​

10.3 目标表：TO target_table vs 隐式建表 ​

10.4 POPULATE 的坑（写在最显眼处） ​

10.5 链式物化视图：MV1 → MV2 → MV3 ​

10.6 杀手锏：AggregatingMergeTree + MV + xxxState/xxxMerge ​

10.6.1 场景：网站埋点实时大屏 ​

10.6.2 表结构 ​

10.6.3 物化视图 ​

10.6.4 查询：用 xxxMerge 还原最终值 ​

10.7 Projection ——「表内嵌的二级排序 / 二级聚合」 ​

10.7.1 直观理解 ​

10.7.2 语法 ​

10.7.3 优化器自动选择 ​

10.7.4 MV vs Projection 选型决策表 ​

10.8 真实案例：广告点击实时大屏 ​

10.9 📌 与其他数据库的对比小框 ​

10.10 本章小结 ​

10.11 面试高频题 ​

Q1：ClickHouse 的物化视图和 PostgreSQL 的物化视图有什么本质区别？ ​

Q2：解释 AggregateFunction(uniq, UInt64) 这个数据类型，以及 uniqState / uniqMerge 的作用？ ​

Q3：POPULATE 关键字有什么坑？生产为什么不推荐用？ ​

Q4：物化视图和 Projection 怎么选？ ​

Q5：MV 失败会不会导致源表 INSERT 失败？怎么调？ ​

Q6：链式物化视图怎么搭？中间表为什么必须用 AggregatingMergeTree + xxxState？ ​

Q7：MV 的 SELECT 里能用 JOIN 吗？有什么坑？ ​

Q8：Projection 在什么场景下不会被命中？有哪些限制？ ​

🎬 可视化演示 ​

💻 示例代码 ​

第 10 章物化视图与 Projection

10.0 一句话总览

10.1 普通视图 vs 物化视图：先把"视图"两个字搞清楚

10.1.1 普通 `VIEW` —— 只是查询别名

10.1.2 物化视图 `MATERIALIZED VIEW` —— 真实存数据

10.2 物化视图的本质：INSERT 触发器（重点反复强调）

10.3 目标表：`TO target_table` vs 隐式建表

10.4 `POPULATE` 的坑（写在最显眼处）

10.5 链式物化视图：MV1 → MV2 → MV3

10.6 杀手锏：`AggregatingMergeTree` + MV + `xxxState/xxxMerge`

10.6.1 场景：网站埋点实时大屏

10.6.2 表结构

10.6.3 物化视图

10.6.4 查询：用 `xxxMerge` 还原最终值

10.7 Projection ——「表内嵌的二级排序 / 二级聚合」

10.7.1 直观理解

10.7.2 语法

10.7.3 优化器自动选择

10.7.4 MV vs Projection 选型决策表

10.8 真实案例：广告点击实时大屏

10.9 📌 与其他数据库的对比小框

10.10 本章小结

10.11 面试高频题

Q1：ClickHouse 的物化视图和 PostgreSQL 的物化视图有什么本质区别？

Q2：解释 `AggregateFunction(uniq, UInt64)` 这个数据类型，以及 `uniqState` / `uniqMerge` 的作用？

Q3：`POPULATE` 关键字有什么坑？生产为什么不推荐用？

Q4：物化视图和 Projection 怎么选？

Q5：MV 失败会不会导致源表 INSERT 失败？怎么调？

Q6：链式物化视图怎么搭？中间表为什么必须用 `AggregatingMergeTree` + `xxxState`？

Q7：MV 的 SELECT 里能用 JOIN 吗？有什么坑？

Q8：Projection 在什么场景下不会被命中？有哪些限制？

🎬 可视化演示

💻 示例代码