创建你的第一个 materialized view

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

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

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

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;

现在创建一个 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。

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;

两个表的行数应当相同。

现在，在目标表上运行一个按 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 子句。

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) 这种排序方式的压缩效果可能与原始表不同。