创建第一个投影

了解如何在 ClickHouse 中使用投影，在同一张表中存储一份额外的已排序数据副本，从而加快对主键未覆盖列的查找。

所有快速入门

实时分析数据仓储可观测性AI/MLCloud开源

前置条件

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.

你还应完成以下快速入门，因为本指南将直接基于 uk_price_paid 表以及其中介绍的相关概念展开：

• 创建你的第一个 MergeTree 表
• 创建你的第一个 materialized view

你将实现的内容

在 MergeTree 快速入门中，你已经看到，按 town 或 county 查询 uk_price_paid 时需要执行全表扫描，因为该表按 (postcode, addr1, addr2) 排序。 在本快速入门中，你将通过创建一个投影来解决这个问题——投影是在同一张表内部存储的一种额外的已排序数据表示。与 materialized view 不同，投影不需要单独的目标表，能够与变更 (删除和更新) 保持同步，并且会由查询优化器透明地使用——你仍然查询同一个表名。 完成后，你将了解如何添加并 materialize 投影、ClickHouse 如何自动选择它，以及何时应选择投影而不是 materialized view。

1. 了解为什么需要 projection

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

   projection 会在同一张表内额外存储一份按序排列的 (部分或全部) 列副本。当你查询这张表时，查询优化器会自动检查：如果从 projection 读取比从基础数据读取涉及更少的粒度，就会透明地改用它。

   与 materialized view 的主要区别：

   • 没有单独的表 - projection 就存在于 uk_price_paid 表内部
   • 透明的查询优化 - 你像平常一样查询 uk_price_paid；ClickHouse 会自动选择 projection
   • 与变更保持同步 - 应用于该表的删除和更新也会同步反映到 projection 中

   更多信息，请参阅projection 参考文档。

2. 为表添加投影

   在 uk_price_paid 表上定义一个投影，用于存储按 (town, date) 排序的 town、date、price 和 type：

   ALTER TABLE uk_price_paid
       ADD PROJECTION uk_price_paid_by_town
       (
           SELECT town, date, price, type
           ORDER BY (town, date)
       );
   

   这会在表的元数据中注册该投影，但不会为现有数据将其物化——只有后续插入的数据才会填充它。

   验证该投影是否出现在表定义中：

   SHOW CREATE TABLE uk_price_paid;
   

   你应该会在输出结果中看到 PROJECTION uk_price_paid_by_town 块。

3. 为现有数据物化该投影

   与 materialized view 类似，新添加的投影仅对后续插入生效。要用表中已有的 3000 万行来填充它，需要显式将其物化：

   ALTER TABLE uk_price_paid
       MATERIALIZE PROJECTION uk_price_paid_by_town;
   

   这将作为后台变更执行。你可以查看其进度：

   SELECT
       mutation_id,
       command,
       is_done
   FROM system.mutations
   WHERE table = 'uk_price_paid'
   ORDER BY create_time DESC
   LIMIT 5;
   

   当 is_done = 1 时，投影就已完全 materialized。你也可以通过查看 system.projection_parts 来验证：

   SELECT
       name,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
   FROM system.projection_parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY name;
   
4. 查询该表并观察自动 PROJECTION 的使用

   现在运行一个按 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;
   

   检查查询统计信息——与创建投影之前相比，读取的行数大幅减少，因为 ClickHouse 自动选择从 uk_price_paid_by_town 投影读取，而不是扫描基础数据。

   你可以使用 `EXPLAIN“ 确认已使用该投影：

   EXPLAIN
   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;
   

   在输出中查找引用该投影名称的 ReadFromMergeTree。如果要明确比较性能，可以针对单个查询禁用投影优化：

   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
   SETTINGS optimize_use_projections = 0;
   

   这会强制进行一次全表扫描，这样你就能看出读取行数的差异。

5. 比较 projections 与 materialized views

   projections 和 materialized views 都是在解决同一个问题——针对不同访问模式提升 reads 性能——但两者的取舍不同。简而言之，如果你只是需要在相同数据上使用不同的 sort 顺序，projections 通常是更好的选择；而如果你需要转换、聚合数据，或将数据路由到不同的 schema，materialized views 则更灵活。详细对比请参见 Materialized views versus projections。

6. 查看存储开销

   投影会在同一张表中存储所选列的第二份副本，因此会增加磁盘占用。查询 system.parts，查看 uk_price_paid 的总大小 (现在也包含投影数据) ：

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

   您还可以查看 projection 的存储情况：

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

   这与 materialized view 的基本权衡相同：用更多磁盘空间换取更快的读取速度。由于投影只包含选定的四列，且压缩效果会因排序顺序而异，因此它可能比完整副本更小。

后续步骤

在本快速入门中，你为 uk_price_paid 添加了一个投影，它按 (town, date) 排序存储数据，无需创建单独的表即可实现按 town 的快速查找。你已经了解到，投影会由查询优化器自动选用，与变更保持同步，并以磁盘空间换取读取性能。

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

或者进一步参考以下文档：

• materialized view 与投影对比
• 投影参考
• MergeTree 表引擎

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

所有快速入门