创建你的第一个 materialized view

了解如何在 ClickHouse 中使用 materialized view，以不同的排序顺序预先计算并存储查询结果，从而支持对主键未覆盖列进行快速查找。

所有快速入门

实时分析数据仓储可观测性AI/MLCloudOss

前置条件

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

你还应完成创建你的第一个 MergeTree 表快速入门，因为本指南会直接基于其中创建的 uk_price_paid 表展开。

你将构建的内容

在 MergeTree 快速入门中，你已经看到，按 town 或 county 查询 uk_price_paid 时需要全表扫描，因为该表按 (postcode, addr1, addr2) 排序。 在本快速入门中，你将通过创建一个按 (town, date) 排序、存储相同数据的 materialized view 来解决这个问题，从而在不更改原始表的情况下实现按 town 的快速查找。 完成后，你将了解 materialized view 如何作为插入触发器工作、如何回填现有数据，以及将数据存储两次带来的磁盘空间权衡。

1. 理解为什么需要 materialized view

   你的 uk_price_paid 表按 (postcode, addr1, addr2) 排序。这意味着，当你按 postcode、addr1 或 addr2 过滤时，ClickHouse 可以跳过大量数据块；但按 town 过滤的查询则必须扫描每一行——整整 3000 万行。

   你可以再创建一张使用不同 ORDER BY 的表，但这样一来，每次有新数据到达时，你都得记得同时向两张表插入数据。materialized view 可以将这个过程自动化：它会监视源表中的插入操作，对这些行进行转换，并自动将结果写入目标表。

   可以把 materialized view 看作一种插入触发器——每当有行被插入源表时，MV 的 SELECT 查询都会针对新插入的数据块运行，并将结果插入目标表。

2. 创建目标表

   materialized view 需要一个地方来存储其输出。这只是一个普通的 MergeTree 表——你可以完全控制它的 schema、ORDER BY 和 PARTITION BY。

   创建一个按 (town, date) 排序、只包含按 town 查询所需列的表：

   CREATE TABLE uk_price_paid_by_town
   (
       town       LowCardinality(String),
       date       Date,
       price      UInt32,
       type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (town, date);
   

   这个表并没有什么特别之处——它就是一个标准的 MergeTree 表。你接下来要创建的 materialized view 只是将数据写入其中。

   确认该表已创建：

   SHOW CREATE TABLE uk_price_paid_by_town;
   
3. 创建 materialized view

   现在创建一个 materialized view，将源表 (uk_price_paid) 连接到目标表 (uk_price_paid_by_town) ：

   CREATE MATERIALIZED VIEW uk_price_paid_by_town_mv
   TO uk_price_paid_by_town
   AS SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   TO uk_price_paid_by_town 子句会告诉 ClickHouse 将 SELECT 的输出写入目标表。从现在起，每当有行插入到 uk_price_paid 时，这个 MV 都会触发，并将转换后的行插入到 uk_price_paid_by_town。

   这里有一个重要的注意事项：materialized views 只会在 inserts 时触发。如果你删除或更新源表中的行，目标表并不会感知到——MV 不会与删除或更新保持同步。如果你需要这种同步机制，请考虑改用 projections。

4. 回填现有数据

   materialized view 只会处理后续的插入操作。uk_price_paid 中现有的 3000 万行是在 MV 创建之前插入的，因此目标表目前还是空的。

   请手动回填：

   INSERT INTO uk_price_paid_by_town
   SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   这会直接插入目标表中 - 此步骤不会经过 MV。完成后，验证行数是否一致：

   SELECT
       'uk_price_paid' AS table,
       count() AS rows
   FROM uk_price_paid
   UNION ALL
   SELECT
       'uk_price_paid_by_town' AS table,
       count() AS rows
   FROM uk_price_paid_by_town;
   

   两个表的行数应当相同。

5. 查询 materialized view 的目标表

   现在，在目标表上运行一个按 town 过滤的查询，并将其与直接查询源表的结果进行比较。

   首先，查询源表：

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   查看查询统计信息——由于 town 不在源表的 ORDER BY 中，因此会读取全部 3000 万行。

   现在在 materialized view 的目标表上运行相同的查询：

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid_by_town
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   再次查看查询统计信息——读取的行数明显更少，因为目标表按 (town, date) 排序，ClickHouse 可以跳过所有与 LONDON 不匹配的数据。

   运行 SHOW TABLES，查看已创建的内容：

   SHOW TABLES;
   

   你会同时看到 uk_price_paid_by_town (目标表) 和 uk_price_paid_by_town_mv (视图) 。由于你使用了 CREATE MATERIALIZED VIEW ... TO，因此可以自行控制目标表的名称。如果省略 TO 子句，ClickHouse 会创建一个使用隐式名称的目标表 (.inner.xxx) ，这会让直接操作变得更困难。 因此，建议在创建 materialized view 时使用 TO 子句。

6. 观察数据被存储了两遍

   materialized views 以占用额外磁盘空间为代价，换来更快的读取速度。查询 system.parts，查看每个表占用了多少空间：

   SELECT
       table,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS compressed_size
   FROM system.parts
   WHERE table IN ('uk_price_paid', 'uk_price_paid_by_town')
     AND active = true
   GROUP BY table;
   

   数据在物理上会存储两次——一次存储在按 (postcode, addr1, addr2) 排序的 uk_price_paid 中，另一次存储在按 (town, date) 排序的 uk_price_paid_by_town 中。这就是最根本的权衡：你需要用更多磁盘空间，来换取针对不同访问模式时更快的读取性能。

   目标端表在磁盘上可能会更小，因为它包含的列更少，而且 (town, date) 这种排序方式的压缩效果可能与原始表不同。

后续步骤

在本快速入门中，你创建了一个 materialized view，以不同的排序顺序存储英国房产交易数据，因此无需修改原始表，就能按城镇快速查找。你还了解到，MV 会充当插入触发器，现有数据必须手动回填，而代价是需要额外的磁盘空间。

接下来可以查看以下快速入门：

• 创建你的第一个 projection

或者通过参考文档进一步深入了解：

• materialized view 参考
• 增量 materialized view
• Projections

Check out the ClickHouse academy for on-demand and live training

所有快速入门