导读

在 OLAP 的业务场景中，不仅要把数据存起来，还需要把数据处理好。在 ClickHouse 中，为了提高数据处理性能，使用 Materialized View 是有效的方法之一。本文主要探讨 Materialized View（下文称 MV）的工作原理与最佳实践，并介绍了使用过程中容易踩坑的一些问题和解决方案。

Materialized View 介绍

物化视图可以看做是一种特殊的触发器，当数据被插入时，它将数据上执行 SELECT 查询的结果存储为到一个目标表中。

其设计初衷可以概括为：通过在数据插入时的预处理来加速查询。

CREATE MATERIALIZED VIEW mv TO dest
AS SELECT a, b, count() AS cnt
FROM source
GROUP BY a, b

其中 source 为 MV 的源表（source table），dest 为 MV 的目标表（target table）。

在使用 MV 时，需要谨记以下三个原则。

① 只有源表插入（Insert）才会触发 MV 的更新。MV 不感知对源表的 truncate、 alter delete、alter update、 drop partition、 drop table、 rename 等所有其他操作。需要注意两种特殊场景： ReplicatedMergeTree 在副本（ replica）之间的数据同步(replication) 不会触发 MV 更新，分布式表（Distributed）向其本地表（Local）转发会触发 MV 更新。

② MV 不会对源表做查询，只会查询本次插入的数据块（ insert block）。有个例外是在创建 MV 时如果指定了 populate 会对源表执行一次查询并将结果初始化到 MV 中。

③ MV 支持任意引擎源表，包括 Null 引擎或者 Kafka 引擎这种没有保存数据能力的引擎。

典型使用场景

3.1 用于数据预聚合(pre-aggregate)

这里主要使用 SummingMergeTree 或者 AggregatingMergeTree 作为 MV 的目标表引擎。 MV 中保存源表的数据的聚合结果，在对源表执行 Insert 时，触发对 MV 聚合数据的更新。 MV 中的数据量相比源表可以大幅下降，查询 MV 可以直接获取聚合结果，而不需要每次查询对源表的大量数据扫描。

比如下面的例子使用了SummingMergeTree表引擎为每个date/project汇总hits值。

-- 创建 SummingMergeTree 引擎的目标表
CREATE TABLE wikistat_top_projects
(
    `date` Date,
    `project` LowCardinality(String),
    `hits` UInt32
) ENGINE = SummingMergeTree
ORDER BY (date, project);
-- 创建物化视图
CREATE MATERIALIZED VIEW wikistat_top_projects_mv TO wikistat_top_projects AS
SELECT
    date(time) AS date,
    project,
    sum(hits) AS hits
FROM wikistat
GROUP BY date, project;

3.2 数据冗余以支持不同维度的数据查询

Clickhouse MergeTree 引擎的查询效率受表的主键（primary key）和排序键（order by ）的设计影响较大。我们可以通过 MV 来创建一张同步表，两张表具有不同的主键和排序键，可以满足不同的查询需求。

3.3 数据提取转换

比如有一个空表(Null table)，有多个不同数据源的数据插入到这个空表中。空表有多个MV，它们执行不同的数据转换并将结果写入不同的目的地。

3.4 配合 Kafka 或者 RabbitMQ 引擎

Kafka 引擎和 RabbitMQ 引擎自身没有数据持久化能力，与 MV 配合可以进行数据的持久化。

常见问题

MV 的逻辑比较简单，仅用一句话就可以概括：在数据插入源表时，将数据同步到目标表。

ClickHouse 的 CK 几乎没有对使用做任何限制。用户可以根据需要灵活的使用，并可以与其他的能力进行搭配，比如 MV + Distributed Table, MV+ReplicatedMergeTree 等。在实际应用中我们时常遭遇如下情形：尽管已成功构建了 MV，且确信已将数据成功插入源表。然而，当我们在MV 内执行查询时，结果却未能与预期相符，出现数据缺失或者数据重复。

4.1 数据聚合应该使用 SummingMergeTree 或者 AggregatingMergeTree

考虑如下需求，使用 mv 对源表数据进行聚合，客户错误地使用非聚合引擎（MergeTree）作为 MV 的目标表。

❎ 错误范例：使用普通 MergeTree 引擎做聚合

CREATE TABLE source (a Int64, b Int64)
ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);
-- 目标表错误的使用 MergeTree 引擎
CREATE TABLE dest (a Int64, b Int64, cnt Int64)
ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);
-- 创建基于 dest 表创建物化视图
CREATE MATERIALIZED VIEW mv to dest
AS SELECT a, b, count() as cnt
FROM source GROUP BY a, b;
-- 执行源表插入
insert into source(a,b) values(1, 1), (1,1), (1,2);
insert into source(a,b) values(1, 1), (1,1), (1,2);
-- MV 实际查询结果，不符合预期
SELECT a,b,count() FROM dest GROUP BY a, b;
┌─a─┬─b─┬─count()─┐
│ 1 │ 2 │       2 │
│ 1 │ 1 │       2 │
└───┴───┴─────────┘
-- 预期的正确结果
SELECT a, b, count() as cnt FROM source GROUP BY a, b
┌─a─┬─b─┬─sum─┐
│ 1 │ 2 │   2 │
│ 1 │ 1 │   4 │
└───┴───┴─────┘

✅ 正确范例 1：使用SummingMergeTree

MV 中涉及到聚合操作，应该使用 SummingMergeTree 或者 AggregatingMergeTree 。

首先如果聚合操作为 sum 操作，可以使用SummingMergeTree。

CREATE TABLE source (a Int64, b Int64)
ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);
-- 创建 SummingMergeTree 引擎目标表
-- 需要注意这里通过 ORDER BY(a,b ) 指定 a,b 作为SummingMergeTree的聚合键
CREATE TABLE dest_2 (a Int64, b Int64, sum UInt64)
ENGINE = SummingMergeTree ORDER BY (a, b);
-- 创建 MV 
CREATE MATERIALIZED VIEW mv_2 to dest_2
AS SELECT a, b, sum(a) as sum
FROM source GROUP BY a, b;
insert into source(a,b) values(1, 1), (1,1), (1,2);
insert into source(a,b) values(1, 1), (1,1), (1,2);
-- 为什么数据并没有根据a,b聚合?
-- 这里是因为 SummingMergeTree 是在后台merge时异步进行聚合的
select * from mv_2;
┌─a─┬─b─┬─sum─┐
│ 1 │ 1 │   2 │
│ 1 │ 2 │   1 │
│ 1 │ 1 │   2 │
│ 1 │ 2 │   1 │
└───┴───┴─────┘
-- 因此查询 SummingMergeTree 时，需要添加 group by
select a,b,sum(a) as sum from mv_2 group by a;
┌─a─┬─b─┬───sum──┐
│ 1 │ 1 │      2 │
│ 1 │ 2 │      4 │
└───┴───┴────────┘
-- 或者手动执行 optimize table，手动触发 SummingMergeTree 的聚合
optimize table dest_2 final;
-- optimize 之后不需要 group by 
select * from mv_2;
┌─a─┬─b─┬─sum─┐
│ 1 │ 1 │   4 │
│ 1 │ 2 │   2 │
└───┴───┴─────┘

✅ 正确范例 2：使用 AggregatingMergeTree 引擎

如果包含有其他聚合操作，需选择 AggregatingMergeTree，下面给出AggregatingMergeTree 的实现。

CREATE TABLE source (a Int64, b Int64)
ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);
-- 创建 AggregatingMergeTree 引擎目标表
CREATE TABLE dest_3(
  a Int64, b Int64, 
  cnt AggregateFunction(count, Int64))
ENGINE = AggregatingMergeTree order by (a, b);
-- 创建 MV 
CREATE MATERIALIZED VIEW mv_3 TO dest_3 AS
              SELECT a, b, countState(a) AS cnt
              FROM source GROUP BY a, b
insert into source(a,b) values(1, 1), (1,1), (1,2);
insert into source(a,b) values(1, 1), (1,1), (1,2);
-- 使用 Merge 进行查询
select a,b,countMerge(cnt) from mv_3 group by a,b;
┌─a─┬─b─┬─countMerge(cnt)─┐
│ 1 │ 2 │               2 │
│ 1 │ 1 │               4 │
└───┴───┴─────────────────┘

4.2 聚合引擎需要注意排序键(ORDER BY) 与 SQL 中聚合键(GROUP BY) 保持一致

使用SummingMergeTree 时，需要注意其排序键(ORDER BY)和 GROUP BY 聚合字段保持一致。

CREATE TABLE source (a Int64, b Int64, d Date) ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b,d);

❎ 错误范例

CREATE TABLE dest_2 (a Int64, b Int64, d Date, sum UInt64)

ENGINE = SummingMergeTree PARTITION BY toYYYYMM(d) ORDER BY (a, b);

CREATE MATERIALIZED VIEW mv_wrong

ENGINE = SummingMergeTree ORDER BY (a, b);

to dest_2

AS SELECT a, b, d, sum(a) as sum

FROM source GROUP BY a, b, d;

✅ 正确范例

CREATE TABLE dest_3 (a Int64, b Int64, sum UInt64)

ENGINE = SummingMergeTree PARTITION BY toYYYYMM(d) ORDER BY (a, b, d);

CREATE MATERIALIZED VIEW mv_right

ENGINE = SummingMergeTree ORDER BY (a, b);

to dest_2

AS SELECT a, b, sum(a) as sum

FROM source GROUP BY a, b, d;

4.3 始终谨记 MV 同步只会查询 Insert Block，不会查询原表

如下面的 MV 定义中，每次插入只会对插入的数据执行 SELECT 查询，而不是查询source 整张表。

CREATE TABLE source (a UInt64, b UInt64) ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b);

-- 组内排序，按 a 进行分组，取每个组内出现次数最多的 b

❎ 错误范例

CREATE TABLE dest(

a Int64, b Int64,

max_cnt AggregateFunction(max, UInt64))

ENGINE = AggregatingMergeTree order by (a);

CREATE MATERIALIZED VIEW mv to dest

AS SELECT a, maxState(cnt_by_a_b) as max_cnt

FROM (

SELECT a, b,

count() AS cnt_by_a_b

FROM source

GROUP BY a, b

) GROUP BY a

❎ 正确范例

-- 同时插入两条数据

insert into source(a, b) values(1, 1),(1,1);

select a, maxMerge(max_cnt) as max_cnt from dest group by a;

┌─a─┬─max_cnt─┐

│ 1 │ 2 │

└───┴─────┘

-- 依次插入两条数据

insert into source(a, b) values(1, 1);

┌─a─┬─max_cnt─┐

│ 1 │ 1 │

└───┴─────┘

4.4 MV 的字段名需要与查询结果字段完全一致

CREATE TABLE source (a Int64, b Int64, d Date) ENGINE = MergeTree PARTITION BY (a) ORDER BY (a,b,d);

❎ 错误范例

CREATE TABLE dest_2 (a Int64, b Int64, d Date, sumA UInt64)

ENGINE = SummingMergeTree PARTITION BY toYYYYMM(d) ORDER BY (a, b);

CREATE MATERIALIZED VIEW mv_wrong

ENGINE = SummingMergeTree ORDER BY (a, b);

to dest_2

AS SELECT a, b, d, sum(a)

FROM source GROUP BY a, b, d;

✅ 正确范例

CREATE TABLE dest_2 (a Int64, b Int64, d Date, sumA UInt64)

ENGINE = SummingMergeTree PARTITION BY toYYYYMM(d) ORDER BY (a, b);

CREATE MATERIALIZED VIEW mv_wrong

ENGINE = SummingMergeTree ORDER BY (a, b);

to dest_2

AS SELECT a, b, d, sum(a) as sumA

FROM source GROUP BY a, b, d;

4.5 MV 和 ReplicationMergeTree 结合

使用过程中需要注意：

ReplicatedMergeTree 在副本（ replica）之间的数据同步(replication) 不会触发 MV 更新。
MV 在源表向目标表的数据同步，会传导到ReplicatedMergeTree 进行副本间同步。

如果需要在 replica 之间保障 MV 的数据一致，需要将 MV 的目标表定义为 ReplicatedMergeTree。

如下图数据同步的过程如下：① 在 Node1 中执行插入，数据写入本副本的 Table，② Insert 操作同步进行本副本内 MV 的同步，③ ReplicatedMergeTree 会将数据同步到 Node2 的 Table 中。④ Node1 的 MV 目标表为ReplicatedMergeTree 引擎，会触发同步数据到 Node2 的 MV 目标表。

数据重复问题

MV 和 ReplicatedMergeTree 有一个场景会导致数据重复。如果对 ReplicatedMergeTree 执行 Populate 操作时，会触发数据重复。

关于该问题细节可以参考 https://github.com/ClickHouse/ClickHouse/issues/9081

4.6 MV 和 JOIN

需注意一点：只有驱动表的 INSERT 操作才会触发 MV 更新，被驱动表不会。

如果希望驱动表和被驱动表插入时都会触发 MV 的更新，需要创建两个 MV。实例如下

CREATE MATERIALIZED VIEW mv1 TO dest
SELECT ...
FROM source left join some_dimension on (...)
CREATE MATERIALIZED VIEW mv2 TO dest
SELECT ...
FROM some_dimension right join source on (...)

4.7 MV 和 Distributed Table

可以分成四种情况分别讨论

方案	MV Source	MV Target	INSERT INTO	Data in Target	数据同步过程，Distributed(D), Local(L)
①	Local	Local	Distributed	YES	1. 写入 D 表， D 表异步插入 Local 表 2. 触发 Local 表的 MV 更新
①	Local	Local	Local	YES	1. 数据写入 L 表，触发 MV 更新
②	Distributed	Distributed	Distributed	YES	1. 数据写入 D 表，D 表 MV 更新 2. 数据写入 L 表中
②	Distributed	Distributed	Local	NO	1. 数据写入 L 表，不会触发 D 表 MV 更新
③	Local	Distributed	Distributed	YES	1. 数据写入 D 表，D 异步插入 L 表 2. 触发 L 表的 MV 更新
③	Local	Distributed	Local	YES	1. 数据写入 L 表，触发 MV 更新
④	Distributed	Local	Distributed	YES	1. 数据写入 D 表，D 异步插入 Local 表 2. 触发 D 表的 MV 更新
④	Distributed	Local	Local	NO	1. 数据写入 L 表，不会触发 D 表MV 更新

方案 ① 种较为常见，用于单 shard 场景。该方案最简单、效率最高、且不会出现数据不同步问题。

方案 ② 和 ③ 用于多 shard 场景，数据基于 sharding key 规则分布于不同的 shard 中。

其中方案 ② 如果数据实际执行写入到 Local 表中，则会导致 MV 中的数据不同步。

了解更多

云数据库 ClickHouse 版是全托管云原生 Serverless 实时数仓服务。具有开箱即用，高吞吐写入，秒级实时分析、自动弹性优势。广泛应用于流量分析、广告营销分析、行为分析、人群划分、客户画像、敏捷BI、数据集市、网络监控、分布式服务和链路监控等业务场景。详细内容见ClickHouse产品详情页

阿里云ClickHouse 企业版已于4.26日正式商业化，并推出了指定规格资源包首购优惠的折扣活动哦！首次购买规格在3000-9500CCU*H的计算包，以及首次购买1个月450G及以下规格的存储包，均可享 0.4折的超优惠价格！首次计算和存储资源组合购买不超过 99.58 元，欢迎登陆阿里云ClickHouse官网进行选购。

点击链接立刻体验：

计算包：https://common-buy.aliyun.com/?commodityCode=clickhouse_serverless_dp_cn

存储包：https://common-buy.aliyun.com/?commodityCode=clickhouse_storage_dp_cn