可刷新materialized view

如何使用 materialized view 加速查询

可刷新materialized view在概念上类似于传统 OLTP 数据库中的 materialized view：它会存储指定查询的结果，以便快速检索，并减少重复执行高资源消耗查询的需要。与 ClickHouse 的增量materialized view不同，这类视图需要定期针对完整数据集执行查询，并将结果存储在目标表中供后续查询。从理论上讲，这个结果集应小于原始数据集，从而让后续查询执行得更快。

下图说明了可刷新materialized view的工作方式：

你也可以观看以下视频：

​

何时应使用可刷新materialized view？

ClickHouse 增量materialized view 非常强大，而且通常比可刷新materialized view 采用的方法更具扩展性，尤其是在需要对单个表执行聚合的场景中。由于它只会在数据插入时对每个数据块进行聚合，并在最终表中合并这些增量状态，因此查询始终只需处理部分数据。这种方法可扩展到潜在的 PB 级数据，通常也是首选方案。

不过，在某些用例中，这种增量处理并非必需，或者并不适用。有些问题要么不兼容增量方式，要么并不需要实时更新，此时更适合定期重建。例如，你可能希望定期基于完整数据集对某个视图进行一次完整重算，因为它使用了复杂的 join，而这种情况并不适合增量方式。

可刷新materialized view 可以运行批处理任务，执行诸如反规范化之类的操作。你还可以在可刷新materialized view 之间创建依赖关系，使一个视图依赖另一个视图的结果，并且仅在后者完成后才执行。这可以替代定时工作流或简单的 DAG，例如 dbt 作业。要了解如何在可刷新materialized view 之间设置依赖关系，请参阅 CREATE VIEW 的 Dependencies 部分。

​

如何刷新可刷新materialized view？

可刷新materialized view 会按创建时定义的时间间隔自动刷新。 例如，下面这个 materialized view 会每分钟刷新一次：

CREATE MATERIALIZED VIEW table_name_mv
REFRESH EVERY 1 MINUTE TO table_name AS
...

如果你想强制刷新 materialized view，可使用 SYSTEM REFRESH VIEW 子句：

SYSTEM REFRESH VIEW table_name_mv;

您还可以取消、停止或启动视图。 更多详情，请参阅管理可刷新materialized view文档。

​

可刷新materialized view 最近一次刷新是在什么时候？

要查看可刷新materialized view 最近一次刷新的时间，可以查询下方所示的 system.view_refreshes 系统表：

SELECT database, view, status,
       last_success_time, last_refresh_time, next_refresh_time,
       read_rows, written_rows
FROM system.view_refreshes;

┌─database─┬─view─────────────┬─status────┬───last_success_time─┬───last_refresh_time─┬───next_refresh_time─┬─read_rows─┬─written_rows─┐
│ database │ table_name_mv    │ Scheduled │ 2024-11-11 12:10:00 │ 2024-11-11 12:10:00 │ 2024-11-11 12:11:00 │   5491132 │       817718 │
└──────────┴──────────────────┴───────────┴─────────────────────┴─────────────────────┴─────────────────────┴───────────┴──────────────┘

​

如何更改刷新频率？

要更改可刷新materialized view的刷新频率，请使用 ALTER TABLE...MODIFY REFRESH 语法。

ALTER TABLE table_name_mv
MODIFY REFRESH EVERY 30 SECONDS;

完成后，你可以使用可刷新materialized view 上次是什么时候刷新的？查询来检查速率是否已更新：

┌─database─┬─view─────────────┬─status────┬───last_success_time─┬───last_refresh_time─┬───next_refresh_time─┬─read_rows─┬─written_rows─┐
│ database │ table_name_mv    │ Scheduled │ 2024-11-11 12:22:30 │ 2024-11-11 12:22:30 │ 2024-11-11 12:23:00 │   5491132 │       817718 │
└──────────┴──────────────────┴───────────┴─────────────────────┴─────────────────────┴─────────────────────┴───────────┴──────────────┘

​

使用 APPEND 添加新行

APPEND 功能可让你将新行追加到表末尾，而不是替换整个视图。

此功能的一个用途是获取某一时刻的值快照。例如，假设我们有一个 events 表，其中的数据来自 Kafka、Redpanda 或其他流式数据平台的消息流。

SELECT *
FROM events
LIMIT 10

Query id: 7662bc39-aaf9-42bd-b6c7-bc94f2881036

┌──────────────────ts─┬─uuid─┬─count─┐
│ 2008-08-06 17:07:19 │ 0eb  │   547 │
│ 2008-08-06 17:07:19 │ 60b  │   148 │
│ 2008-08-06 17:07:19 │ 106  │   750 │
│ 2008-08-06 17:07:19 │ 398  │   875 │
│ 2008-08-06 17:07:19 │ ca0  │   318 │
│ 2008-08-06 17:07:19 │ 6ba  │   105 │
│ 2008-08-06 17:07:19 │ df9  │   422 │
│ 2008-08-06 17:07:19 │ a71  │   991 │
│ 2008-08-06 17:07:19 │ 3a2  │   495 │
│ 2008-08-06 17:07:19 │ 598  │   238 │
└─────────────────────┴──────┴───────┘

该数据集在 uuid 列中有 4096 个值。我们可以编写如下查询，找出总计数最高的那些值：

SELECT
    uuid,
    sum(count) AS count
FROM events
GROUP BY ALL
ORDER BY count DESC
LIMIT 10

┌─uuid─┬───count─┐
│ c6f  │ 5676468 │
│ 951  │ 5669731 │
│ 6a6  │ 5664552 │
│ b06  │ 5662036 │
│ 0ca  │ 5658580 │
│ 2cd  │ 5657182 │
│ 32a  │ 5656475 │
│ ffe  │ 5653952 │
│ f33  │ 5653783 │
│ c5b  │ 5649936 │
└──────┴─────────┘

假设我们想要每 10 秒统计一次每个 uuid 的计数，并将结果存储到一个名为 events_snapshot 的新表中。events_snapshot 的 schema 如下：

CREATE TABLE events_snapshot (
    ts DateTime32,
    uuid String,
    count UInt64
)
ENGINE = MergeTree
ORDER BY uuid;

然后，我们可以创建一个可刷新materialized view 来填充该表：

CREATE MATERIALIZED VIEW events_snapshot_mv
REFRESH EVERY 10 SECOND APPEND TO events_snapshot
AS SELECT
    now() AS ts,
    uuid,
    sum(count) AS count
FROM events
GROUP BY ALL;

然后，我们可以查询 events_snapshot，以获取特定 uuid 的计数随时间变化的情况：

SELECT *
FROM events_snapshot
WHERE uuid = 'fff'
ORDER BY ts ASC
FORMAT PrettyCompactMonoBlock

┌──────────────────ts─┬─uuid─┬───count─┐
│ 2024-10-01 16:12:56 │ fff  │ 5424711 │
│ 2024-10-01 16:13:00 │ fff  │ 5424711 │
│ 2024-10-01 16:13:10 │ fff  │ 5424711 │
│ 2024-10-01 16:13:20 │ fff  │ 5424711 │
│ 2024-10-01 16:13:30 │ fff  │ 5674669 │
│ 2024-10-01 16:13:40 │ fff  │ 5947912 │
│ 2024-10-01 16:13:50 │ fff  │ 6203361 │
│ 2024-10-01 16:14:00 │ fff  │ 6501695 │
└─────────────────────┴──────┴─────────┘

​

示例

下面通过一些示例数据集，来看看如何使用可刷新materialized view。

​

Stack Overflow

数据反规范化指南 介绍了使用 Stack Overflow 数据集进行数据反规范化的多种方法。我们将数据写入以下表：votes、users、badges、posts 和 postlinks。

在该指南中，我们展示了如何使用以下查询将 postlinks 数据集反规范化到 posts 表中：

SELECT
    posts.*,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
    SELECT
         PostId,
         groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts_types_codecs_ordered.Id = postlinks.PostId;

然后，我们展示了如何将这些数据一次性插入 posts_with_links 表，但在生产系统中，我们通常希望定期执行此操作。

posts 和 postlinks 表都可能发生更新。因此，与其尝试用增量materialized view 来实现这个 join，不如直接将该查询设置为按固定时间间隔运行，例如每小时运行一次，并将结果存储到 post_with_links 表中。

这正是可刷新materialized view 的用武之地，我们可以使用以下查询创建一个：

CREATE MATERIALIZED VIEW posts_with_links_mv
REFRESH EVERY 1 HOUR TO posts_with_links AS
SELECT
    posts.*,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
    SELECT
         PostId,
         groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts_types_codecs_ordered.Id = postlinks.PostId;

该视图会立即执行一次，之后将按配置每小时执行一次，以确保源表中的更新得到反映。需要注意的是，当查询再次运行时，结果集会以原子且透明的方式更新。

此处的语法与增量materialized view 完全相同，只是我们添加了一个 REFRESH 子句：

​

IMDb

在 dbt 和 ClickHouse 集成指南 中，我们用以下表填充了一个 IMDb 数据集：actors、directors、genres、movie_directors、movies 和 roles。

随后，我们可以编写以下查询，汇总每位演员的信息，并按出演电影次数从高到低排序。

SELECT
  id, any(actor_name) AS name, uniqExact(movie_id) AS movies,
  round(avg(rank), 2) AS avg_rank, uniqExact(genre) AS genres,
  uniqExact(director_name) AS directors, max(created_at) AS updated_at
FROM (
  SELECT
    imdb.actors.id AS id,
    concat(imdb.actors.first_name, ' ', imdb.actors.last_name) AS actor_name,
    imdb.movies.id AS movie_id, imdb.movies.rank AS rank, genre,
    concat(imdb.directors.first_name, ' ', imdb.directors.last_name) AS director_name,
    created_at
  FROM imdb.actors
  INNER JOIN imdb.roles ON imdb.roles.actor_id = imdb.actors.id
  LEFT JOIN imdb.movies ON imdb.movies.id = imdb.roles.movie_id
  LEFT JOIN imdb.genres ON imdb.genres.movie_id = imdb.movies.id
  LEFT JOIN imdb.movie_directors ON imdb.movie_directors.movie_id = imdb.movies.id
  LEFT JOIN imdb.directors ON imdb.directors.id = imdb.movie_directors.director_id
)
GROUP BY id
ORDER BY movies DESC
LIMIT 5;

┌─────id─┬─name─────────┬─num_movies─┬───────────avg_rank─┬─unique_genres─┬─uniq_directors─┬──────────updated_at─┐
│  45332 │ Mel Blanc    │        909 │ 5.7884792542982515 │            19 │            148 │ 2024-11-11 12:01:35 │
│ 621468 │ Bess Flowers │        672 │  5.540605094212635 │            20 │            301 │ 2024-11-11 12:01:35 │
│ 283127 │ Tom London   │        549 │ 2.8057034230202023 │            18 │            208 │ 2024-11-11 12:01:35 │
│ 356804 │ Bud Osborne  │        544 │ 1.9575342420755093 │            16 │            157 │ 2024-11-11 12:01:35 │
│  41669 │ Adoor Bhasi  │        544 │                  0 │             4 │            121 │ 2024-11-11 12:01:35 │
└────────┴──────────────┴────────────┴────────────────────┴───────────────┴────────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.393 sec. Processed 5.45 million rows, 86.82 MB (13.87 million rows/s., 221.01 MB/s.)
Peak memory usage: 1.38 GiB.

返回结果并不需要太久，不过如果我们希望它更快、计算成本更低呢？ 再假设这个数据集还会持续更新——新电影不断上映，也会不断出现新的演员和导演。

这时就该使用可刷新materialized view了，因此我们先为结果创建一个目标表：

CREATE TABLE imdb.actor_summary
(
        `id` UInt32,
        `name` String,
        `num_movies` UInt16,
        `avg_rank` Float32,
        `unique_genres` UInt16,
        `uniq_directors` UInt16,
        `updated_at` DateTime
)
ENGINE = MergeTree
ORDER BY num_movies

现在我们可以定义这个视图了：

CREATE MATERIALIZED VIEW imdb.actor_summary_mv
REFRESH EVERY 1 MINUTE TO imdb.actor_summary AS
SELECT
        id,
        any(actor_name) AS name,
        uniqExact(movie_id) AS num_movies,
        avg(rank) AS avg_rank,
        uniqExact(genre) AS unique_genres,
        uniqExact(director_name) AS uniq_directors,
        max(created_at) AS updated_at
FROM
(
        SELECT
        imdb.actors.id AS id,
        concat(imdb.actors.first_name, ' ', imdb.actors.last_name) AS actor_name,
        imdb.movies.id AS movie_id,
        imdb.movies.rank AS rank,
        genre,
        concat(imdb.directors.first_name, ' ', imdb.directors.last_name) AS director_name,
        created_at
        FROM imdb.actors
    INNER JOIN imdb.roles ON imdb.roles.actor_id = imdb.actors.id
    LEFT JOIN imdb.movies ON imdb.movies.id = imdb.roles.movie_id
    LEFT JOIN imdb.genres ON imdb.genres.movie_id = imdb.movies.id
    LEFT JOIN imdb.movie_directors ON imdb.movie_directors.movie_id = imdb.movies.id
    LEFT JOIN imdb.directors ON imdb.directors.id = imdb.movie_directors.director_id
)
GROUP BY id
ORDER BY num_movies DESC;

该视图会立即执行一次，之后按配置每分钟执行一次，以确保源表的更新能够及时反映出来。这样一来，我们之前用于获取演员汇总的查询在语法上更简洁，执行速度也显著提升！

SELECT *
FROM imdb.actor_summary
ORDER BY num_movies DESC
LIMIT 5

┌─────id─┬─name─────────┬─num_movies─┬──avg_rank─┬─unique_genres─┬─uniq_directors─┬──────────updated_at─┐
│  45332 │ Mel Blanc    │        909 │ 5.7884793 │            19 │            148 │ 2024-11-11 12:01:35 │
│ 621468 │ Bess Flowers │        672 │  5.540605 │            20 │            301 │ 2024-11-11 12:01:35 │
│ 283127 │ Tom London   │        549 │ 2.8057034 │            18 │            208 │ 2024-11-11 12:01:35 │
│ 356804 │ Bud Osborne  │        544 │ 1.9575342 │            16 │            157 │ 2024-11-11 12:01:35 │
│  41669 │ Adoor Bhasi  │        544 │         0 │             4 │            121 │ 2024-11-11 12:01:35 │
└────────┴──────────────┴────────────┴───────────┴───────────────┴────────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.007 sec.

假设我们在源数据里新增了一位名叫 “Clicky McClickHouse” 的演员，而他恰好出演过很多部电影！

INSERT INTO imdb.actors VALUES (845466, 'Clicky', 'McClickHouse', 'M');
INSERT INTO imdb.roles SELECT
        845466 AS actor_id,
        id AS movie_id,
        'Himself' AS role,
        now() AS created_at
FROM imdb.movies
LIMIT 10000, 910;

不到 60 秒，我们的目标表就会更新，体现出 Clicky 在演艺事业上的高产：

SELECT *
FROM imdb.actor_summary
ORDER BY num_movies DESC
LIMIT 5;

┌─────id─┬─name────────────────┬─num_movies─┬──avg_rank─┬─unique_genres─┬─uniq_directors─┬──────────updated_at─┐
│ 845466 │ Clicky McClickHouse │        910 │ 1.4687939 │            21 │            662 │ 2024-11-11 12:53:51 │
│  45332 │ Mel Blanc           │        909 │ 5.7884793 │            19 │            148 │ 2024-11-11 12:01:35 │
│ 621468 │ Bess Flowers        │        672 │  5.540605 │            20 │            301 │ 2024-11-11 12:01:35 │
│ 283127 │ Tom London          │        549 │ 2.8057034 │            18 │            208 │ 2024-11-11 12:01:35 │
│  41669 │ Adoor Bhasi         │        544 │         0 │             4 │            121 │ 2024-11-11 12:01:35 │
└────────┴─────────────────────┴────────────┴───────────┴───────────────┴────────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.006 sec.