增量物化视图

背景

增量物化视图 (Materialized Views) 允许您将计算成本从查询时转移到插入时，从而加快 SELECT 查询速度。

与 Postgres 等事务数据库不同，ClickHouse 的物化视图只是一个触发器，它会在数据块插入到表时运行一个查询。该查询的结果会插入到第二个“目标”表中。如果插入更多行，结果将再次发送到目标表，其中中间结果将被更新和合并。这种合并后的结果等同于在所有原始数据上运行查询。

物化视图的主要动机是插入到目标表中的结果代表对行进行的聚合、过滤或转换的结果。这些结果通常是原始数据的较小表示形式（例如，聚合情况下的部分草图）。此外，从目标表读取结果的查询简单，确保查询时间比在原始数据上执行相同的计算更快，从而将计算（以及查询延迟）从查询时间转移到插入时间。

ClickHouse 中的物化视图会随着数据流入其基于的表而实时更新，功能更像是持续更新的索引。这与使用物化视图的其他数据库形成对比，在这些数据库中，物化视图通常是需要刷新的查询的静态快照（类似于 ClickHouse 可刷新物化视图）。

示例

为了举例说明，我们将使用文档中 “Schema Design”（模式设计） 中记录的 Stack Overflow 数据集。

假设我们想要获取帖子每天的赞成和反对票数。

CREATE TABLE votes
(
    `Id` UInt32,
    `PostId` Int32,
    `VoteTypeId` UInt8,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `BountyAmount` UInt8
)
ENGINE = MergeTree
ORDER BY (VoteTypeId, CreationDate, PostId)

INSERT INTO votes SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/*.parquet')

0 rows in set. Elapsed: 29.359 sec. Processed 238.98 million rows, 2.13 GB (8.14 million rows/s., 72.45 MB/s.)

这在 ClickHouse 中是一个相当简单的查询，这得益于 toStartOfDay 函数

SELECT toStartOfDay(CreationDate) AS day,
       countIf(VoteTypeId = 2) AS UpVotes,
       countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY day
ORDER BY day ASC
LIMIT 10

┌─────────────────day─┬─UpVotes─┬─DownVotes─┐
│ 2008-07-31 00:00:00 │       6 │         0 │
│ 2008-08-01 00:00:00 │     182 │        50 │
│ 2008-08-02 00:00:00 │     436 │       107 │
│ 2008-08-03 00:00:00 │     564 │       100 │
│ 2008-08-04 00:00:00 │    1306 │       259 │
│ 2008-08-05 00:00:00 │    1368 │       269 │
│ 2008-08-06 00:00:00 │    1701 │       211 │
│ 2008-08-07 00:00:00 │    1544 │       211 │
│ 2008-08-08 00:00:00 │    1241 │       212 │
│ 2008-08-09 00:00:00 │     576 │        46 │
└─────────────────────┴─────────┴───────────┘

10 rows in set. Elapsed: 0.133 sec. Processed 238.98 million rows, 2.15 GB (1.79 billion rows/s., 16.14 GB/s.)
Peak memory usage: 363.22 MiB.

这个查询已经很快了，但我们能做得更好吗？

如果想在插入时使用物化视图来计算，我们需要一个表来接收结果。该表应该只保留每天 1 行。如果收到现有日期的更新，其他列应该合并到现有日期的行中。为了发生这种增量状态的合并，必须为其他列存储部分状态。

这需要在 ClickHouse 中使用一种特殊的引擎类型：SummingMergeTree。它会将具有相同排序键的所有行替换为包含数值列求和值的单行。以下表将合并具有相同日期的所有行，对任何数值列求和

CREATE TABLE up_down_votes_per_day
(
  `Day` Date,
  `UpVotes` UInt32,
  `DownVotes` UInt32
)
ENGINE = SummingMergeTree
ORDER BY Day

为了演示我们的物化视图，假设我们的 votes 表是空的，尚未接收任何数据。我们的物化视图在将数据插入到 votes 时执行上述 SELECT 操作，并将结果发送到 up_down_votes_per_day

CREATE MATERIALIZED VIEW up_down_votes_per_day_mv TO up_down_votes_per_day AS
SELECT toStartOfDay(CreationDate)::Date AS Day,
       countIf(VoteTypeId = 2) AS UpVotes,
       countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY Day

这里的 TO 子句是关键，表示结果将发送到哪里，即 up_down_votes_per_day。

我们可以从之前的插入中重新填充我们的 votes 表

INSERT INTO votes SELECT toUInt32(Id) AS Id, toInt32(PostId) AS PostId, VoteTypeId, CreationDate, UserId, BountyAmount
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/*.parquet')

0 rows in set. Elapsed: 111.964 sec. Processed 477.97 million rows, 3.89 GB (4.27 million rows/s., 34.71 MB/s.)
Peak memory usage: 283.49 MiB.

完成之后，我们可以确认 up_down_votes_per_day 的大小 - 我们应该每天有 1 行

SELECT count()
FROM up_down_votes_per_day
FINAL

┌─count()─┐
│    5723 │
└─────────┘

我们有效地将行数从 2.38 亿（在 votes 中）减少到 5000，方法是存储查询的结果。然而，关键在于，如果将新的 votes 插入到 votes 表中，新的值将被发送到 up_down_votes_per_day 的相应日期，并在后台异步自动合并 - 始终只保留每天一行。因此，up_down_votes_per_day 将始终又小又最新。

由于行的合并是异步的，因此当用户查询时，可能每天有多条 votes。为了确保在查询时合并所有未合并的行，我们有两种选择

• 在表名上使用 FINAL 修饰符。我们在上面的计数查询中执行了此操作。
• 按最终表中使用的排序键（即 CreationDate）对指标进行聚合并求和。通常，这更有效且更灵活（该表可用于其他用途），但前者对于某些查询可能更简单。我们下面展示两者

SELECT
        Day,
        UpVotes,
        DownVotes
FROM up_down_votes_per_day
FINAL
ORDER BY Day ASC
LIMIT 10

10 rows in set. Elapsed: 0.004 sec. Processed 8.97 thousand rows, 89.68 KB (2.09 million rows/s., 20.89 MB/s.)
Peak memory usage: 289.75 KiB.

SELECT Day, sum(UpVotes) AS UpVotes, sum(DownVotes) AS DownVotes
FROM up_down_votes_per_day
GROUP BY Day
ORDER BY Day ASC
LIMIT 10
┌────────Day─┬─UpVotes─┬─DownVotes─┐
│ 2008-07-31 │       6 │         0 │
│ 2008-08-01 │     182 │        50 │
│ 2008-08-02 │     436 │       107 │
│ 2008-08-03 │     564 │       100 │
│ 2008-08-04 │    1306 │       259 │
│ 2008-08-05 │    1368 │       269 │
│ 2008-08-06 │    1701 │       211 │
│ 2008-08-07 │    1544 │       211 │
│ 2008-08-08 │    1241 │       212 │
│ 2008-08-09 │     576 │        46 │
└────────────┴─────────┴───────────┘

10 rows in set. Elapsed: 0.010 sec. Processed 8.97 thousand rows, 89.68 KB (907.32 thousand rows/s., 9.07 MB/s.)
Peak memory usage: 567.61 KiB.

这使我们的查询从 0.133s 提高到 0.004s - 提高了 25 倍以上！

参考

重要提示：ORDER BY = GROUP BY

在大多数情况下，物化视图转换的 GROUP BY 子句中使用的列应与使用 SummingMergeTree 或 AggregatingMergeTree 表引擎的目标表的 ORDER BY 子句中使用的列一致。这些引擎依赖于 ORDER BY 列在后台合并操作期间合并具有相同值的行。GROUP BY 和 ORDER BY 列之间的错位可能导致查询性能低下、次优合并甚至数据差异。

一个更复杂的例子

上面的例子使用物化视图来计算和维护每天的两个求和值。求和代表了维护部分状态的最简单的聚合形式 - 当它们到达时，我们可以将新值添加到现有值中。但是，ClickHouse 物化视图可用于任何类型的聚合。

假设我们希望为每天的帖子计算一些统计信息：Score 的 99.9th 百分位数和 CommentCount 的平均值。计算此值的查询可能如下所示

SELECT
        toStartOfDay(CreationDate) AS Day,
        quantile(0.999)(Score) AS Score_99th,
        avg(CommentCount) AS AvgCommentCount
FROM posts
GROUP BY Day
ORDER BY Day DESC
LIMIT 10

┌─────────────────Day─┬────────Score_99th─┬────AvgCommentCount─┐
│ 2024-03-31 00:00:00 │  5.23700000000008 │ 1.3429811866859624 │
│ 2024-03-30 00:00:00 │                 5 │ 1.3097158891616976 │
│ 2024-03-29 00:00:00 │  5.78899999999976 │ 1.2827635327635327 │
│ 2024-03-28 00:00:00 │                 7 │  1.277746158224246 │
│ 2024-03-27 00:00:00 │ 5.738999999999578 │ 1.2113264918282023 │
│ 2024-03-26 00:00:00 │                 6 │ 1.3097536945812809 │
│ 2024-03-25 00:00:00 │                 6 │ 1.2836721018539201 │
│ 2024-03-24 00:00:00 │ 5.278999999999996 │ 1.2931667891256429 │
│ 2024-03-23 00:00:00 │ 6.253000000000156 │  1.334061135371179 │
│ 2024-03-22 00:00:00 │ 9.310999999999694 │ 1.2388059701492538 │
└─────────────────────┴───────────────────┴────────────────────┘

10 rows in set. Elapsed: 0.113 sec. Processed 59.82 million rows, 777.65 MB (528.48 million rows/s., 6.87 GB/s.)
Peak memory usage: 658.84 MiB.

与之前一样，我们可以创建一个物化视图，该视图会在将新帖子插入到我们的 posts 表时执行上述查询。

为了举例说明，并避免从 S3 加载帖子数据，我们将创建一个与 posts 具有相同模式的重复表 posts_null。但是，此表将不存储任何数据，而仅由物化视图在插入行时使用。为了防止存储数据，我们可以使用 Null 表引擎类型。

CREATE TABLE posts_null AS posts ENGINE = Null

Null 表引擎是一个强大的优化 - 可以将其视为 /dev/null。我们的物化视图将在我们的 posts_null 表接收行时计算和存储我们的摘要统计信息 - 它只是一个触发器。但是，原始数据将不会被存储。虽然在我们的例子中，我们可能仍然希望存储原始帖子，但这种方法可用于在避免原始数据存储开销的同时计算聚合。

因此，物化视图变为

CREATE MATERIALIZED VIEW post_stats_mv TO post_stats_per_day AS
       SELECT toStartOfDay(CreationDate) AS Day,
       quantileState(0.999)(Score) AS Score_quantiles,
       avgState(CommentCount) AS AvgCommentCount
FROM posts_null
GROUP BY Day

请注意，我们在聚合函数的末尾附加了后缀 State。这确保返回函数的聚合状态而不是最终结果。这将包含其他信息，以允许此部分状态与其它状态合并。例如，对于平均值，这将包括列的计数和总和。

需要部分聚合状态才能计算正确的结果。例如，对于计算平均值，简单地对子范围的平均值求平均值会产生不正确的结果。

现在我们创建此视图的目标表 post_stats_per_day，该表存储这些部分聚合状态

CREATE TABLE post_stats_per_day
(
  `Day` Date,
  `Score_quantiles` AggregateFunction(quantile(0.999), Int32),
  `AvgCommentCount` AggregateFunction(avg, UInt8)
)
ENGINE = AggregatingMergeTree
ORDER BY Day

虽然之前 SummingMergeTree 足以存储计数，但我们需要一种更高级的引擎类型来存储其他函数：AggregatingMergeTree。为了确保 ClickHouse 知道将存储聚合状态，我们将 Score_quantiles 和 AvgCommentCount 定义为类型 AggregateFunction，指定部分状态的函数源及其源列的类型。与 SummingMergeTree 类似，具有相同 ORDER BY 键值的行将被合并（在上面的示例中为 Day）。

要通过我们的物化视图填充我们的 post_stats_per_day，我们可以简单地将 posts 中的所有行插入到 posts_null 中

INSERT INTO posts_null SELECT * FROM posts

0 rows in set. Elapsed: 13.329 sec. Processed 119.64 million rows, 76.99 GB (8.98 million rows/s., 5.78 GB/s.)

在生产环境中，您可能会将物化视图附加到 posts 表。我们在这里使用 posts_null 来演示 null 表。

我们的最终查询需要使用我们的函数中的 Merge 后缀（因为列存储部分聚合状态）

SELECT
        Day,
        quantileMerge(0.999)(Score_quantiles),
        avgMerge(AvgCommentCount)
FROM post_stats_per_day
GROUP BY Day
ORDER BY Day DESC
LIMIT 10

请注意，我们在这里使用 GROUP BY 而不是使用 FINAL。

其他应用

以上主要关注使用物化视图来增量更新数据的部分聚合，从而将计算从查询时间转移到插入时间。除了这种常见的用例之外，物化视图还有许多其他应用。

过滤和转换

在某些情况下，我们可能希望仅插入一部分行和列。在这种情况下，我们的 posts_null 表可以接收插入，SELECT 查询在将行插入到 posts 表之前过滤行。例如，假设我们希望转换 posts 表中的 Tags 列。它包含一个由管道分隔的标签名称列表。通过将其转换为数组，我们可以更轻松地按单个标签值进行聚合。

我们可以在运行 INSERT INTO SELECT 时执行此转换。物化视图允许我们将此逻辑封装在 ClickHouse DDL 中，并使我们的 INSERT 简单，并将转换应用于任何新行。

用于此转换的物化视图如下所示

CREATE MATERIALIZED VIEW posts_mv TO posts AS
        SELECT * EXCEPT Tags, arrayFilter(t -> (t != ''), splitByChar('|', Tags)) as Tags FROM posts_null

查找表

在选择 ClickHouse 排序键时，您应该考虑其访问模式。应使用经常用于筛选和聚合子句的列。这对于用户具有无法封装在单个列集中的更多样化的访问模式的场景可能具有限制性。例如，考虑以下 comments 表

CREATE TABLE comments
(
    `Id` UInt32,
    `PostId` UInt32,
    `Score` UInt16,
    `Text` String,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `UserDisplayName` LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY PostId

0 rows in set. Elapsed: 46.357 sec. Processed 90.38 million rows, 11.14 GB (1.95 million rows/s., 240.22 MB/s.)

这里的排序键优化了按 PostId 筛选的查询。

假设用户希望按特定的 UserId 进行筛选并计算他们的平均 Score

SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

┌──────────avg(Score)─┐
│ 0.18181818181818182 │
└─────────────────────┘

1 row in set. Elapsed: 0.778 sec. Processed 90.38 million rows, 361.59 MB (116.16 million rows/s., 464.74 MB/s.)
Peak memory usage: 217.08 MiB.

虽然速度很快（对于ClickHouse来说，数据量很小），但我们可以从处理的行数——9038万行中看出，这需要全表扫描。对于更大的数据集，我们可以使用物化视图来查找我们的排序键值PostId，用于过滤列UserId。然后可以使用这些值来执行高效的查找。

在这个例子中，我们的物化视图可以非常简单，只选择comments中的PostId和UserId，在插入时进行选择。这些结果随后被发送到表comments_posts_users，该表按UserId排序。我们下面创建一个Comments表的空版本，并使用它来填充我们的视图和comments_posts_users表

CREATE TABLE comments_posts_users (
  PostId UInt32,
  UserId Int32
) ENGINE = MergeTree ORDER BY UserId

CREATE TABLE comments_null AS comments
ENGINE = Null

CREATE MATERIALIZED VIEW comments_posts_users_mv TO comments_posts_users AS
SELECT PostId, UserId FROM comments_null

INSERT INTO comments_null SELECT * FROM comments

0 rows in set. Elapsed: 5.163 sec. Processed 90.38 million rows, 17.25 GB (17.51 million rows/s., 3.34 GB/s.)

现在我们可以使用这个视图在子查询中来加速我们之前的查询

SELECT avg(Score)
FROM comments
WHERE PostId IN (
        SELECT PostId
        FROM comments_posts_users
        WHERE UserId = 8592047
) AND UserId = 8592047

┌──────────avg(Score)─┐
│ 0.18181818181818182 │
└─────────────────────┘

1 row in set. Elapsed: 0.012 sec. Processed 88.61 thousand rows, 771.37 KB (7.09 million rows/s., 61.73 MB/s.)

物化视图的链式/级联

物化视图可以链式连接（或级联），从而建立复杂的工作流程。有关更多信息，请参阅指南 “级联物化视图”。

物化视图和JOIN

可刷新物化视图

以下仅适用于增量物化视图。可刷新物化视图会定期对整个目标数据集执行其查询，并完全支持JOIN。如果可以容忍结果的新鲜度降低，请考虑使用它们进行复杂的JOIN。

ClickHouse中的增量物化视图完全支持JOIN操作，但有一个关键的限制：物化视图仅在源表（查询中最左边的表）插入时触发。 JOIN中的右侧表即使数据发生变化也不会触发更新。这种行为在构建增量物化视图时尤其重要，因为数据是在插入时聚合或转换的。

当使用JOIN定义增量物化视图时，SELECT查询中最左边的表充当源。当向此表插入新行时，ClickHouse仅使用这些新插入的行执行物化视图查询。JOIN中的右侧表在此执行期间会完全读取，但它们自身的更改不会触发视图。

这种行为使物化视图中的JOIN类似于对静态维度数据进行的快照JOIN。

这对于使用引用表或维度表丰富数据非常有效。但是，对右侧表（例如，用户元数据）的任何更新都不会追溯性地更新物化视图。要查看更新后的数据，必须在源表中进行新的插入。

示例

让我们通过一个具体的例子来演示，使用Stack Overflow数据集。我们将使用一个物化视图来计算每日每个用户的徽章数，包括来自users表的用户的显示名称。

作为提醒，我们的表模式如下

CREATE TABLE badges
(
    `Id` UInt32,
    `UserId` Int32,
    `Name` LowCardinality(String),
    `Date` DateTime64(3, 'UTC'),
    `Class` Enum8('Gold' = 1, 'Silver' = 2, 'Bronze' = 3),
    `TagBased` Bool
)
ENGINE = MergeTree
ORDER BY UserId

CREATE TABLE users
(
    `Id` Int32,
    `Reputation` UInt32,
    `CreationDate` DateTime64(3, 'UTC'),
    `DisplayName` LowCardinality(String),
    `LastAccessDate` DateTime64(3, 'UTC'),
    `Location` LowCardinality(String),
    `Views` UInt32,
    `UpVotes` UInt32,
    `DownVotes` UInt32
)
ENGINE = MergeTree
ORDER BY Id;

我们假设我们的users表已经预先填充

INSERT INTO users
SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/users.parquet');

物化视图及其关联的目标表定义如下

CREATE TABLE daily_badges_by_user
(
    Day Date,
    UserId Int32,
    DisplayName LowCardinality(String),
    Gold UInt32,
    Silver UInt32,
    Bronze UInt32
)
ENGINE = SummingMergeTree
ORDER BY (DisplayName, UserId, Day);

CREATE MATERIALIZED VIEW daily_badges_by_user_mv TO daily_badges_by_user AS
SELECT
    toDate(Date) AS Day,
    b.UserId,
    u.DisplayName,
    countIf(Class = 'Gold') AS Gold,
    countIf(Class = 'Silver') AS Silver,
    countIf(Class = 'Bronze') AS Bronze
FROM badges AS b
LEFT JOIN users AS u ON b.UserId = u.Id
GROUP BY Day, b.UserId, u.DisplayName;

分组和排序对齐

物化视图中的GROUP BY子句必须包含DisplayName、UserId和Day，以匹配SummingMergeTree目标表中的ORDER BY。这确保了行被正确聚合和合并。省略任何一个都可能导致不正确的结果或低效的合并。

如果现在填充徽章，视图将被触发 - 填充我们的daily_badges_by_user表。

INSERT INTO badges SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/badges.parquet')

0 rows in set. Elapsed: 433.762 sec. Processed 1.16 billion rows, 28.50 GB (2.67 million rows/s., 65.70 MB/s.)

假设我们希望查看特定用户获得的徽章，我们可以编写以下查询

SELECT *
FROM daily_badges_by_user
FINAL
WHERE DisplayName = 'gingerwizard'

┌────────Day─┬──UserId─┬─DisplayName──┬─Gold─┬─Silver─┬─Bronze─┐
│ 2023-02-27 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-02-28 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2013-10-30 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2024-03-04 │ 2936484 │ gingerwizard │    0 │      1 │      0 │
│ 2024-03-05 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-04-17 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2013-11-18 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-10-31 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
└────────────┴─────────┴──────────────┴──────┴────────┴────────┘

8 rows in set. Elapsed: 0.018 sec. Processed 32.77 thousand rows, 642.14 KB (1.86 million rows/s., 36.44 MB/s.)

现在，如果该用户收到一个新的徽章并插入一行，我们的视图将被更新

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

1 row in set. Elapsed: 7.517 sec.

SELECT *
FROM daily_badges_by_user
FINAL
WHERE DisplayName = 'gingerwizard'
┌────────Day─┬──UserId─┬─DisplayName──┬─Gold─┬─Silver─┬─Bronze─┐
│ 2013-10-30 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2013-11-18 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-02-27 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-02-28 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-04-17 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2023-10-31 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2024-03-04 │ 2936484 │ gingerwizard │    0 │      1 │      0 │
│ 2024-03-05 │ 2936484 │ gingerwizard │    0 │      0 │      1 │
│ 2025-04-13 │ 2936484 │ gingerwizard │    1 │      0 │      0 │
└────────────┴─────────┴──────────────┴──────┴────────┴────────┘

9 rows in set. Elapsed: 0.017 sec. Processed 32.77 thousand rows, 642.27 KB (1.96 million rows/s., 38.50 MB/s.)

注意

请注意这里的插入延迟。插入的用户行与整个users表连接，显著影响插入性能。我们下面在“在物化视图中使用源表进行过滤和JOIN”中提出了解决这些问题的方法。

相反，如果我们为新用户插入一个徽章，然后插入该用户的行，我们的物化视图将无法捕获用户的指标。

INSERT INTO badges VALUES (53505059, 23923286, 'Good Answer', now(), 'Bronze', 0);
INSERT INTO users VALUES (23923286, 1, now(),  'brand_new_user', now(), 'UK', 1, 1, 0);

SELECT *
FROM daily_badges_by_user
FINAL
WHERE DisplayName = 'brand_new_user';

0 rows in set. Elapsed: 0.017 sec. Processed 32.77 thousand rows, 644.32 KB (1.98 million rows/s., 38.94 MB/s.)

在这种情况下，视图仅在徽章插入之前存在用户行时执行。如果我们为该用户插入另一个徽章，则会插入一行，如预期

INSERT INTO badges VALUES (53505060, 23923286, 'Teacher', now(), 'Bronze', 0);

SELECT *
FROM daily_badges_by_user
FINAL
WHERE DisplayName = 'brand_new_user'

┌────────Day─┬───UserId─┬─DisplayName────┬─Gold─┬─Silver─┬─Bronze─┐
│ 2025-04-13 │ 23923286 │ brand_new_user │    0 │      0 │      1 │
└────────────┴──────────┴────────────────┴──────┴────────┴────────┘

1 row in set. Elapsed: 0.018 sec. Processed 32.77 thousand rows, 644.48 KB (1.87 million rows/s., 36.72 MB/s.)

但是，请注意，此结果是不正确的。

物化视图中JOIN的最佳实践

• 使用最左边的表作为触发器。 只有SELECT语句左侧的表才会触发物化视图。对右侧表的更改不会触发更新。

• 预先插入JOIN的数据。 确保JOIN表中存在数据，然后再将行插入到源表。JOIN在插入时进行评估，因此缺少数据将导致不匹配的行或空值。

• 限制从JOIN中提取的列。 仅选择JOIN表所需的列，以最大程度地减少内存使用并减少插入时间延迟（见下文）。

• 评估插入时间性能。 JOIN会增加插入成本，尤其是对于大型右侧表。使用代表性的生产数据基准测试插入速率。

• 对于简单的查找，优先使用字典。 使用字典进行键值查找（例如，用户ID到名称），以避免昂贵的JOIN操作。

• 使GROUP BY和ORDER BY对齐，以提高合并效率。 当使用SummingMergeTree或AggregatingMergeTree时，确保GROUP BY与目标表中的ORDER BY子句匹配，以允许高效的行合并。

• 使用显式列别名。 当表具有重叠的列名时，使用别名以防止歧义并确保目标表中的正确结果。

• 考虑插入量和频率。 JOIN在适度的插入工作负载中表现良好。对于高吞吐量摄取，请考虑使用暂存表、预JOIN或其他方法，例如字典和可刷新物化视图。

在过滤和JOIN中使用源表

在使用ClickHouse中的物化视图时，了解在执行物化视图查询期间如何处理源表非常重要。具体来说，物化视图查询中的源表将被插入的数据块替换。如果不正确理解这种行为，可能会导致一些意想不到的结果。

示例场景

考虑以下设置

CREATE TABLE t0 (`c0` Int) ENGINE = Memory;
CREATE TABLE mvw1_inner (`c0` Int) ENGINE = Memory;
CREATE TABLE mvw2_inner (`c0` Int) ENGINE = Memory;

CREATE VIEW vt0 AS SELECT * FROM t0;

CREATE MATERIALIZED VIEW mvw1 TO mvw1_inner
AS SELECT count(*) AS c0
    FROM t0
    LEFT JOIN ( SELECT * FROM t0 ) AS x ON t0.c0 = x.c0;

CREATE MATERIALIZED VIEW mvw2 TO mvw2_inner
AS SELECT count(*) AS c0
    FROM t0
    LEFT JOIN vt0 ON t0.c0 = vt0.c0;

INSERT INTO t0 VALUES (1),(2),(3);

INSERT INTO t0 VALUES (1),(2),(3),(4),(5);

SELECT * FROM mvw1;
┌─c0─┐
│  3 │
│  5 │
└────┘

SELECT * FROM mvw2;
┌─c0─┐
│  3 │
│  8 │
└────┘

说明

在上面的例子中，我们有两个物化视图mvw1和mvw2，它们执行类似的操作，但引用源表t0的方式略有不同。

在mvw1中，表t0在JOIN右侧的(SELECT * FROM t0)子查询中直接引用。当向t0插入数据时，物化视图的查询将使用插入的数据块替换t0执行。这意味着JOIN操作仅在新插入的行上执行，而不是整个表。

在第二个使用JOIN vt0的情况下，视图会读取t0中的所有数据。这确保了JOIN操作考虑了t0中的所有行，而不仅仅是新插入的块。

关键的区别在于ClickHouse处理物化视图查询中的源表的方式。当物化视图由插入触发时，源表（在本例中为t0）将被插入的数据块替换。这种行为可以用来优化查询，但也需要仔细考虑以避免意外的结果。

用例和注意事项

在实践中，您可以使用这种行为来优化仅需要处理源表数据子集的物化视图。例如，您可以使用子查询在JOIN与其他表之前过滤源表。这可以帮助减少物化视图处理的数据量并提高性能。

CREATE TABLE t0 (id UInt32, value String) ENGINE = MergeTree() ORDER BY id;
CREATE TABLE t1 (id UInt32, description String) ENGINE = MergeTree() ORDER BY id;
INSERT INTO t1 VALUES (1, 'A'), (2, 'B'), (3, 'C');

CREATE TABLE mvw1_target_table (id UInt32, value String, description String) ENGINE = MergeTree() ORDER BY id;

CREATE MATERIALIZED VIEW mvw1 TO mvw1_target_table AS
SELECT t0.id, t0.value, t1.description
FROM t0
JOIN (SELECT * FROM t1 WHERE t1.id IN (SELECT id FROM t0)) AS t1
ON t0.id = t1.id;

在本例中，从IN (SELECT id FROM t0)子查询构建的集合仅包含新插入的行，这可以帮助将其与t1进行过滤。

Stack Overflow示例

考虑我们的之前的物化视图示例来计算每日每个用户的徽章数，包括来自users表的用户的显示名称。

CREATE MATERIALIZED VIEW daily_badges_by_user_mv TO daily_badges_by_user
AS SELECT
    toDate(Date) AS Day,
    b.UserId,
    u.DisplayName,
    countIf(Class = 'Gold') AS Gold,
    countIf(Class = 'Silver') AS Silver,
    countIf(Class = 'Bronze') AS Bronze
FROM badges AS b
LEFT JOIN users AS u ON b.UserId = u.Id
GROUP BY Day, b.UserId, u.DisplayName;

此视图显著影响了badges表上的插入延迟，例如

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

1 row in set. Elapsed: 7.517 sec.

使用上述方法，我们可以优化此视图。我们将使用插入的徽章行中的用户ID来过滤users表

CREATE MATERIALIZED VIEW daily_badges_by_user_mv TO daily_badges_by_user
AS SELECT
    toDate(Date) AS Day,
    b.UserId,
    u.DisplayName,
    countIf(Class = 'Gold') AS Gold,
    countIf(Class = 'Silver') AS Silver,
    countIf(Class = 'Bronze') AS Bronze
FROM badges AS b
LEFT JOIN
(
    SELECT
        Id,
        DisplayName
    FROM users
    WHERE Id IN (
        SELECT UserId
        FROM badges
    )
) AS u ON b.UserId = u.Id
GROUP BY
    Day,
    b.UserId,
    u.DisplayName

这不仅可以加快初始徽章插入速度

INSERT INTO badges SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/badges.parquet')

0 rows in set. Elapsed: 132.118 sec. Processed 323.43 million rows, 4.69 GB (2.45 million rows/s., 35.49 MB/s.)
Peak memory usage: 1.99 GiB.

而且意味着未来的徽章插入也是高效的

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

1 row in set. Elapsed: 0.583 sec.

在上述操作中，仅从用户表检索一行，用户ID为2936484。此查找也通过表排序键Id进行了优化。

物化视图和UNION

UNION ALL查询通常用于将来自多个源表的数据组合成单个结果集。

虽然UNION ALL不直接支持增量物化视图，但您可以通过为每个SELECT分支创建单独的物化视图，并将它们的结果写入共享的目标表来实现相同的结果。

对于我们的示例，我们将使用Stack Overflow数据集。考虑下面的badges和comments表，它们分别表示用户获得的徽章和用户在帖子上的评论

CREATE TABLE stackoverflow.comments
(
    `Id` UInt32,
    `PostId` UInt32,
    `Score` UInt16,
    `Text` String,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `UserDisplayName` LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY CreationDate

CREATE TABLE stackoverflow.badges
(
    `Id` UInt32,
    `UserId` Int32,
    `Name` LowCardinality(String),
    `Date` DateTime64(3, 'UTC'),
    `Class` Enum8('Gold' = 1, 'Silver' = 2, 'Bronze' = 3),
    `TagBased` Bool
)
ENGINE = MergeTree
ORDER BY UserId

可以使用以下INSERT INTO命令填充这些表

INSERT INTO stackoverflow.badges SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/badges.parquet')
INSERT INTO stackoverflow.comments SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/comments/*.parquet')

假设我们想要创建一个统一的视图，显示每个用户的最新活动，通过组合这两个表

SELECT
 UserId,
 argMax(description, event_time) AS last_description,
 argMax(activity_type, event_time) AS activity_type,
    max(event_time) AS last_activity
FROM
(
    SELECT
 UserId,
 CreationDate AS event_time,
        Text AS description,
        'comment' AS activity_type
    FROM stackoverflow.comments
    UNION ALL
    SELECT
 UserId,
        Date AS event_time,
        Name AS description,
        'badge' AS activity_type
    FROM stackoverflow.badges
)
GROUP BY UserId
ORDER BY last_activity DESC
LIMIT 10

让我们假设我们有一个目标表来接收此查询的结果。请注意使用AggregatingMergeTree表引擎和AggregateFunction来确保结果被正确合并

CREATE TABLE user_activity
(
    `UserId` String,
    `last_description` AggregateFunction(argMax, String, DateTime64(3, 'UTC')),
    `activity_type` AggregateFunction(argMax, String, DateTime64(3, 'UTC')),
    `last_activity` SimpleAggregateFunction(max, DateTime64(3, 'UTC'))
)
ENGINE = AggregatingMergeTree
ORDER BY UserId

希望此表在向badges或comments插入新行时进行更新，对此问题的天真方法是尝试使用先前的UNION查询创建物化视图

CREATE MATERIALIZED VIEW user_activity_mv TO user_activity AS
SELECT
 UserId,
 argMaxState(description, event_time) AS last_description,
 argMaxState(activity_type, event_time) AS activity_type,
    max(event_time) AS last_activity
FROM
(
    SELECT
 UserId,
 CreationDate AS event_time,
        Text AS description,
        'comment' AS activity_type
    FROM stackoverflow.comments
    UNION ALL
    SELECT
 UserId,
        Date AS event_time,
        Name AS description,
        'badge' AS activity_type
    FROM stackoverflow.badges
)
GROUP BY UserId
ORDER BY last_activity DESC

虽然这在语法上是有效的，但会产生意想不到的结果 - 视图仅会触发对comments表的插入。例如

INSERT INTO comments VALUES (99999999, 23121, 1, 'The answer is 42', now(), 2936484, 'gingerwizard');

SELECT
 UserId,
 argMaxMerge(last_description) AS description,
 argMaxMerge(activity_type) AS activity_type,
    max(last_activity) AS last_activity
FROM user_activity
WHERE UserId = '2936484'
GROUP BY UserId

┌─UserId──┬─description──────┬─activity_type─┬───────────last_activity─┐
│ 2936484 │ The answer is 42 │ comment       │ 2025-04-15 09:56:19.000 │
└─────────┴──────────────────┴───────────────┴─────────────────────────┘

1 row in set. Elapsed: 0.005 sec.

插入到badges表不会触发视图，导致user_activity无法接收更新

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

SELECT
 UserId,
 argMaxMerge(last_description) AS description,
 argMaxMerge(activity_type) AS activity_type,
    max(last_activity) AS last_activity
FROM user_activity
WHERE UserId = '2936484'
GROUP BY UserId;

┌─UserId──┬─description──────┬─activity_type─┬───────────last_activity─┐
│ 2936484 │ The answer is 42 │ comment       │ 2025-04-15 09:56:19.000 │
└─────────┴──────────────────┴───────────────┴─────────────────────────┘

1 row in set. Elapsed: 0.005 sec.

为了解决这个问题，我们只需为每个SELECT语句创建一个物化视图

DROP TABLE user_activity_mv;
TRUNCATE TABLE user_activity;

CREATE MATERIALIZED VIEW comment_activity_mv TO user_activity AS
SELECT
 UserId,
 argMaxState(Text, CreationDate) AS last_description,
 argMaxState('comment', CreationDate) AS activity_type,
    max(CreationDate) AS last_activity
FROM stackoverflow.comments
GROUP BY UserId;

CREATE MATERIALIZED VIEW badges_activity_mv TO user_activity AS
SELECT
 UserId,
 argMaxState(Name, Date) AS last_description,
 argMaxState('badge', Date) AS activity_type,
    max(Date) AS last_activity
FROM stackoverflow.badges
GROUP BY UserId;

现在向任一表插入数据都会产生正确的结果。例如，如果我们向 comments 表插入数据

INSERT INTO comments VALUES (99999999, 23121, 1, 'The answer is 42', now(), 2936484, 'gingerwizard');

SELECT
 UserId,
 argMaxMerge(last_description) AS description,
 argMaxMerge(activity_type) AS activity_type,
    max(last_activity) AS last_activity
FROM user_activity
WHERE UserId = '2936484'
GROUP BY UserId;

┌─UserId──┬─description──────┬─activity_type─┬───────────last_activity─┐
│ 2936484 │ The answer is 42 │ comment       │ 2025-04-15 10:18:47.000 │
└─────────┴──────────────────┴───────────────┴─────────────────────────┘

1 row in set. Elapsed: 0.006 sec.

同样，向 badges 表的插入也会反映在 user_activity 表中

INSERT INTO badges VALUES (53505058, 2936484, 'gingerwizard', now(), 'Gold', 0);

SELECT
 UserId,
 argMaxMerge(last_description) AS description,
 argMaxMerge(activity_type) AS activity_type,
    max(last_activity) AS last_activity
FROM user_activity
WHERE UserId = '2936484'
GROUP BY UserId

┌─UserId──┬─description──┬─activity_type─┬───────────last_activity─┐
│ 2936484 │ gingerwizard │ badge         │ 2025-04-15 10:20:18.000 │
└─────────┴──────────────┴───────────────┴─────────────────────────┘

1 row in set. Elapsed: 0.006 sec.

并行与顺序处理

如前例所示，一个表可以作为多个物化视图的来源。这些视图的执行顺序取决于设置 parallel_view_processing。

默认情况下，此设置等于 0 (false)，这意味着物化视图将按照 uuid 顺序顺序执行。

例如，考虑以下 source 表和 3 个物化视图，每个视图将行发送到 target 表

CREATE TABLE source
(
    `message` String
)
ENGINE = MergeTree
ORDER BY tuple();

CREATE TABLE target
(
    `message` String,
    `from` String,
    `now` DateTime64(9),
    `sleep` UInt8
)
ENGINE = MergeTree
ORDER BY tuple();

CREATE MATERIALIZED VIEW mv_2 TO target
AS SELECT
    message,
    'mv2' AS from,
    now64(9) as now,
    sleep(1) as sleep
FROM source;

CREATE MATERIALIZED VIEW mv_3 TO target
AS SELECT
    message,
    'mv3' AS from,
    now64(9) as now,
    sleep(1) as sleep
FROM source;

CREATE MATERIALIZED VIEW mv_1 TO target
AS SELECT
    message,
    'mv1' AS from,
    now64(9) as now,
    sleep(1) as sleep
FROM source;

请注意，每个视图在将行插入到 target 表之前暂停 1 秒，同时包含其名称和插入时间。

向 source 表插入一行需要 ~3 秒，每个视图按顺序执行

INSERT INTO source VALUES ('test')

1 row in set. Elapsed: 3.786 sec.

我们可以使用 SELECT 确认每行数据的到达情况

SELECT
    message,
    from,
    now
FROM target
ORDER BY now ASC

┌─message─┬─from─┬───────────────────────────now─┐
│ test    │ mv3  │ 2025-04-15 14:52:01.306162309 │
│ test    │ mv1  │ 2025-04-15 14:52:02.307693521 │
│ test    │ mv2  │ 2025-04-15 14:52:03.309250283 │
└─────────┴──────┴───────────────────────────────┘

3 rows in set. Elapsed: 0.015 sec.

这与视图的 uuid 相符

SELECT
    name,
 uuid
FROM system.tables
WHERE name IN ('mv_1', 'mv_2', 'mv_3')
ORDER BY uuid ASC

┌─name─┬─uuid─────────────────────────────────┐
│ mv_3 │ ba5e36d0-fa9e-4fe8-8f8c-bc4f72324111 │
│ mv_1 │ b961c3ac-5a0e-4117-ab71-baa585824d43 │
│ mv_2 │ e611cc31-70e5-499b-adcc-53fb12b109f5 │
└──────┴──────────────────────────────────────┘

3 rows in set. Elapsed: 0.004 sec.

相反，考虑如果我们启用 parallel_view_processing=1 会发生什么。启用后，视图将并行执行，无法保证行到达目标表的顺序

TRUNCATE target;
SET parallel_view_processing = 1;

INSERT INTO source VALUES ('test');

1 row in set. Elapsed: 1.588 sec.

SELECT
    message,
    from,
    now
FROM target
ORDER BY now ASC

┌─message─┬─from─┬───────────────────────────now─┐
│ test    │ mv3  │ 2025-04-15 19:47:32.242937372 │
│ test    │ mv1  │ 2025-04-15 19:47:32.243058183 │
│ test    │ mv2  │ 2025-04-15 19:47:32.337921800 │
└─────────┴──────┴───────────────────────────────┘

3 rows in set. Elapsed: 0.004 sec.

虽然我们观察到的每行数据的到达顺序相同，但这并不能保证 - 正如每行插入时间相似所说明的那样。 另外请注意改进的插入性能。

何时使用并行处理

如上所示，启用 parallel_view_processing=1 可以显著提高插入吞吐量，尤其是在将多个物化视图附加到单个表时。 但是，了解权衡非常重要

• 增加插入压力：所有物化视图同时执行，增加 CPU 和内存使用量。 如果每个视图执行繁重的计算或 JOIN，则可能导致系统过载。
• 需要严格的执行顺序：在极少数需要视图执行顺序重要（例如，链式依赖关系）的工作流程中，并行执行可能导致不一致的状态或竞争条件。 虽然可以设计来规避这种情况，但这种设置很脆弱，并且可能在未来的版本中中断。

历史默认值和稳定性

顺序执行长期以来一直是默认设置，部分原因在于错误处理的复杂性。 历史上，一个物化视图中的故障可能会阻止其他视图执行。 新版本通过按块隔离故障来改进了这一点，但顺序执行仍然提供了更清晰的故障语义。

通常，当满足以下条件时，请启用 parallel_view_processing=1

• 您拥有多个独立的物化视图
• 您旨在最大化插入性能
• 您了解系统处理并发视图执行的能力

在以下情况下禁用它

• 物化视图相互依赖
• 您需要可预测的、有序的执行
• 您正在调试或审计插入行为，并希望进行确定性重放

物化视图和公共表表达式 (CTE)

非递归公共表表达式 (CTE) 在物化视图中受支持。

注意

公共表表达式 不会被物化

ClickHouse 不会物化 CTE；而是将 CTE 定义直接替换到查询中，这可能导致对同一表达式进行多次评估（如果 CTE 使用多次）。

考虑以下示例，该示例计算每种帖子类型的每日活动量。

CREATE TABLE daily_post_activity
(
    Day Date,
 PostType String,
 PostsCreated SimpleAggregateFunction(sum, UInt64),
 AvgScore AggregateFunction(avg, Int32),
 TotalViews SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (Day, PostType);

CREATE MATERIALIZED VIEW daily_post_activity_mv TO daily_post_activity AS
WITH filtered_posts AS (
    SELECT
 toDate(CreationDate) AS Day,
 PostTypeId,
 Score,
 ViewCount
    FROM posts
    WHERE Score > 0 AND PostTypeId IN (1, 2)  -- Question or Answer
)
SELECT
    Day,
    CASE PostTypeId
        WHEN 1 THEN 'Question'
        WHEN 2 THEN 'Answer'
    END AS PostType,
    count() AS PostsCreated,
    avgState(Score) AS AvgScore,
    sum(ViewCount) AS TotalViews
FROM filtered_posts
GROUP BY Day, PostTypeId;

虽然 CTE 在这里严格来说是不必要的，但出于示例目的，视图将按预期工作

INSERT INTO posts
SELECT *
FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/by_month/*.parquet')

SELECT
    Day,
    PostType,
    avgMerge(AvgScore) AS AvgScore,
    sum(PostsCreated) AS PostsCreated,
    sum(TotalViews) AS TotalViews
FROM daily_post_activity
GROUP BY
    Day,
    PostType
ORDER BY Day DESC
LIMIT 10

┌────────Day─┬─PostType─┬───────────AvgScore─┬─PostsCreated─┬─TotalViews─┐
│ 2024-03-31 │ Question │ 1.3317757009345794 │          214 │       9728 │
│ 2024-03-31 │ Answer   │ 1.4747191011235956 │          356 │          0 │
│ 2024-03-30 │ Answer   │ 1.4587912087912087 │          364 │          0 │
│ 2024-03-30 │ Question │ 1.2748815165876777 │          211 │       9606 │
│ 2024-03-29 │ Question │ 1.2641509433962264 │          318 │      14552 │
│ 2024-03-29 │ Answer   │ 1.4706927175843694 │          563 │          0 │
│ 2024-03-28 │ Answer   │  1.601637107776262 │          733 │          0 │
│ 2024-03-28 │ Question │ 1.3530864197530865 │          405 │      24564 │
│ 2024-03-27 │ Question │ 1.3225806451612903 │          434 │      21346 │
│ 2024-03-27 │ Answer   │ 1.4907539118065434 │          703 │          0 │
└────────────┴──────────┴────────────────────┴──────────────┴────────────┘

10 rows in set. Elapsed: 0.013 sec. Processed 11.45 thousand rows, 663.87 KB (866.53 thousand rows/s., 50.26 MB/s.)
Peak memory usage: 989.53 KiB.

在 ClickHouse 中，CTE 是内联的，这意味着它们在优化期间被有效地复制粘贴到查询中，并且 不 被物化。 这意味着

• 如果您的 CTE 引用与源表（即物化视图附加到的表）不同的表，并且在 JOIN 或 IN 子句中使用，它将表现得像子查询或连接，而不是触发器。
• 物化视图仍然仅在向主源表插入时触发，但 CTE 将在每次插入时重新执行，这可能会导致不必要的开销，尤其是在引用的表很大时。

例如，

WITH recent_users AS (
  SELECT Id FROM stackoverflow.users WHERE CreationDate > now() - INTERVAL 7 DAY
)
SELECT * FROM stackoverflow.posts WHERE OwnerUserId IN (SELECT Id FROM recent_users)

在这种情况下，users CTE 在每次插入 posts 时都会被重新评估，并且当插入新用户时，物化视图不会更新 - 只有在插入 posts 时才会更新。

通常，对于在与物化视图附加的相同源表上运行的逻辑，或者确保引用的表很小且不太可能导致性能瓶颈，请使用 CTE。 或者，请考虑 与物化视图 JOIN 相同的优化。