> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 可刷新materialized view

> 如何使用 materialized view 加速查询

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

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

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

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/0xkAyEEn8ANRFZGQ/images/materialized-view/refreshable-materialized-view-diagram.png?fit=max&auto=format&n=0xkAyEEn8ANRFZGQ&q=85&s=701d9ad764003f9a24759155d518972c" size="lg" alt="可刷新materialized view示意图" width="1800" height="410" data-path="images/materialized-view/refreshable-materialized-view-diagram.png" />

你也可以观看以下视频：

<Frame>
  <iframe src="https://www.youtube.com/embed/-KhFJSY8yrs?si=VPRSZb20vaYkuR_C" title="YouTube 视频播放器" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen />
</Frame>

<div id="when-should-refreshable-materialized-views-be-used">
  ## 何时应使用可刷新materialized view？
</div>

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

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

> 可刷新materialized view 可以运行批处理任务，执行诸如反规范化之类的操作。你还可以在可刷新materialized view 之间创建依赖关系，使一个视图依赖另一个视图的结果，并且仅在后者完成后才执行。这可以替代定时工作流或简单的 DAG，例如 [dbt](https://www.getdbt.com/) 作业。要了解如何在可刷新materialized view 之间设置依赖关系，请参阅 [CREATE VIEW](/zh/reference/statements/create/view#refresh-dependencies) 的 `Dependencies` 部分。

<div id="how-do-you-refresh-a-refreshable-materialized-view">
  ## 如何刷新可刷新materialized view？
</div>

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

```sql theme={null}
CREATE MATERIALIZED VIEW table_name_mv
REFRESH EVERY 1 MINUTE TO table_name AS
...
```

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

```sql theme={null}
SYSTEM REFRESH VIEW table_name_mv;
```

您还可以取消、停止或启动视图。
更多详情，请参阅[管理可刷新materialized view](/zh/reference/statements/system#managing-refreshable-materialized-views)文档。

<div id="when-was-a-refreshable-materialized-view-last-refreshed">
  ## 可刷新materialized view 最近一次刷新是在什么时候？
</div>

要查看可刷新materialized view 最近一次刷新的时间，可以查询下方所示的 [`system.view_refreshes`](/zh/reference/system-tables/view_refreshes) 系统表：

```sql theme={null}
SELECT database, view, status,
       last_success_time, last_refresh_time, next_refresh_time,
       read_rows, written_rows
FROM system.view_refreshes;
```

```text theme={null}
┌─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 │
└──────────┴──────────────────┴───────────┴─────────────────────┴─────────────────────┴─────────────────────┴───────────┴──────────────┘
```

<div id="how-can-i-change-the-refresh-rate">
  ## 如何更改刷新频率？
</div>

要更改可刷新materialized view的刷新频率，请使用 [`ALTER TABLE...MODIFY REFRESH`](/zh/reference/statements/alter/view#alter-table--modify-refresh-statement) 语法。

```sql theme={null}
ALTER TABLE table_name_mv
MODIFY REFRESH EVERY 30 SECONDS;
```

完成后，你可以使用[可刷新materialized view 上次是什么时候刷新的？](/zh/concepts/features/materialized-views/refreshable-materialized-view#when-was-a-refreshable-materialized-view-last-refreshed)查询来检查速率是否已更新：

```text theme={null}
┌─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 │
└──────────┴──────────────────┴───────────┴─────────────────────┴─────────────────────┴─────────────────────┴───────────┴──────────────┘
```

<div id="using-append-to-add-new-rows">
  ## 使用 `APPEND` 添加新行
</div>

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

此功能的一个用途是获取某一时刻的值快照。例如，假设我们有一个 `events` 表，其中的数据来自 [Kafka](https://kafka.apache.org/)、[Redpanda](https://www.redpanda.com/) 或其他流式数据平台的消息流。

```sql theme={null}
SELECT *
FROM events
LIMIT 10
```

```response theme={null}
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` 个值。我们可以编写如下查询，找出总计数最高的那些值：

```sql theme={null}
SELECT
    uuid,
    sum(count) AS count
FROM events
GROUP BY ALL
ORDER BY count DESC
LIMIT 10
```

```response theme={null}
┌─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 如下：

```sql theme={null}
CREATE TABLE events_snapshot (
    ts DateTime32,
    uuid String,
    count UInt64
)
ENGINE = MergeTree
ORDER BY uuid;
```

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

```sql theme={null}
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` 的计数随时间变化的情况：

```sql theme={null}
SELECT *
FROM events_snapshot
WHERE uuid = 'fff'
ORDER BY ts ASC
FORMAT PrettyCompactMonoBlock
```

```response theme={null}
┌──────────────────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 │
└─────────────────────┴──────┴─────────┘
```

<div id="examples">
  ## 示例
</div>

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

<div id="stack-overflow">
  ### Stack Overflow
</div>

[数据反规范化指南](/zh/guides/clickhouse/data-modelling/denormalization) 介绍了使用 Stack Overflow 数据集进行数据反规范化的多种方法。我们将数据写入以下表：`votes`、`users`、`badges`、`posts` 和 `postlinks`。

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

```sql theme={null}
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 的用武之地，我们可以使用以下查询创建一个：

```sql theme={null}
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;
```

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

<Note>
  此处的语法与增量materialized view 完全相同，只是我们添加了一个 [`REFRESH`](/zh/reference/statements/create/view#refreshable-materialized-view) 子句：
</Note>

<div id="imdb">
  ### IMDb
</div>

在 [dbt 和 ClickHouse 集成指南](/zh/integrations/connectors/data-ingestion/etl-tools/dbt) 中，我们用以下表填充了一个 IMDb 数据集：`actors`、`directors`、`genres`、`movie_directors`、`movies` 和 `roles`。

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

```sql theme={null}
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;
```

```text theme={null}
┌─────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.)
峰值内存占用: 1.38 GiB.
```

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

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

```sql theme={null}
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
```

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

```sql theme={null}
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;
```

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

```sql theme={null}
SELECT *
FROM imdb.actor_summary
ORDER BY num_movies DESC
LIMIT 5
```

```text theme={null}
┌─────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" 的演员，而他恰好出演过很多部电影！

```sql theme={null}
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 在演艺事业上的高产：

```sql theme={null}
SELECT *
FROM imdb.actor_summary
ORDER BY num_movies DESC
LIMIT 5;
```

```text theme={null}
┌─────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.
```