数据建模技术
这是从 PostgreSQL 迁移到 ClickHouse 的指南的**第 3 部分**。此内容可以被视为入门级内容，旨在帮助用户部署一个符合 ClickHouse 最佳实践的初始功能系统。它避免了复杂主题，并且不会产生完全优化的模式；而是为用户构建生产系统并作为其学习的基础提供了一个坚实的基础。
我们建议从 Postgres 迁移的用户阅读ClickHouse 数据建模指南。本指南使用相同 Stack Overflow 数据集，并探索使用 ClickHouse 功能的多种方法。
分区
Postgres 用户熟悉表分区的概念，它可以通过将表分成更小、更易于管理的部分（称为分区）来提高大型数据库的性能和可管理性。此分区可以使用指定列上的范围（例如，日期）、定义的列表或通过键上的哈希来实现。这允许管理员根据特定标准（如日期范围或地理位置）组织数据。分区有助于提高查询性能，因为它可以通过分区剪枝和更有效的索引来启用更快的访问数据。它还有助于维护任务（如备份和数据清除），因为它允许对单个分区而不是整个表进行操作。此外，分区可以通过将负载分布到多个分区来显着提高 PostgreSQL 数据库的可扩展性。
在 ClickHouse 中，分区是在表最初定义时通过PARTITION BY子句指定的。此子句可以包含任何列上的 SQL 表达式，其结果将定义将行发送到哪个分区。
数据部分在磁盘上与每个分区逻辑关联，并且可以独立查询。对于下面的示例，我们使用表达式toYear(CreationDate)按年份对posts表进行分区。当行插入到 ClickHouse 时，此表达式将针对每一行进行评估，如果存在则路由到结果分区（如果行是某一年的第一行，则将创建该分区）。
CREATE TABLE posts
(
`Id` Int32 CODEC(Delta(4), ZSTD(1)),
`PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
`AcceptedAnswerId` UInt32,
`CreationDate` DateTime64(3, 'UTC'),
...
`ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate)
PARTITION BY toYear(CreationDate)
分区的应用
ClickHouse 中的分区与 Postgres 中的分区具有类似的应用，但有一些细微差别。更具体地说
- 数据管理 - 在 ClickHouse 中，用户应主要将分区视为数据管理功能，而不是查询优化技术。通过根据键逻辑分离数据，每个分区可以独立操作，例如删除。这允许用户在时间上或存储层级之间有效地移动分区（以及子网）过期数据/有效地从集群中删除。例如，下面我们删除 2008 年的帖子。
SELECT DISTINCT partition
FROM system.parts
WHERE `table` = 'posts'
┌─partition─┐
│ 2008 │
│ 2009 │
│ 2010 │
│ 2011 │
│ 2012 │
│ 2013 │
│ 2014 │
│ 2015 │
│ 2016 │
│ 2017 │
│ 2018 │
│ 2019 │
│ 2020 │
│ 2021 │
│ 2022 │
│ 2023 │
│ 2024 │
└───────────┘
17 rows in set. Elapsed: 0.002 sec.
ALTER TABLE posts
(DROP PARTITION '2008')
Ok.
0 rows in set. Elapsed: 0.103 sec.
- 查询优化 - 虽然分区可以帮助提高查询性能，但这在很大程度上取决于访问模式。如果查询仅针对少数几个分区（理想情况下为一个），则性能可能会提高。这通常仅在分区键不在主键中并且您正在按其过滤时才有用。但是，需要覆盖多个分区的查询的性能可能比不使用分区时更差（因为分区可能会导致更多部分）。如果分区键已经是主键中的早期条目，则针对单个分区的优势将变得不那么明显，甚至不存在。如果每个分区中的值是唯一的，则分区也可用于优化 GROUP BY 查询。但是，通常情况下，用户应确保主键已优化，并且仅在访问模式访问特定可预测的数据子集（例如，按天分区，大多数查询在最后一天）的特殊情况下才将分区视为查询优化技术。
分区建议
用户应将分区视为一种数据管理技术。当需要从集群中过期数据时，例如处理时间序列数据时，它是理想的选择，例如，可以简单地删除最旧的分区。
重要提示：确保您的分区键表达式不会导致高基数集，即应避免创建超过 100 个分区。例如，不要按客户端标识符或名称等高基数列对数据进行分区。相反，请在ORDER BY表达式中将客户端标识符或名称作为第一列。
在内部，ClickHouse为插入的数据创建部分。随着更多数据的插入，部分数量会增加。为了防止部分数量过高，这会降低查询性能（需要读取更多文件），因此部分会在后台异步进程中合并。如果部分数量超过预配置的限制，则 ClickHouse 会在插入时抛出异常 - 作为“太多部分”错误。这在正常操作下不应该发生，并且仅在 ClickHouse 配置错误或使用不正确（例如许多小的插入）时才会发生。
由于部分是针对每个分区独立创建的，因此增加分区数量会导致部分数量增加，即它是分区数量的倍数。因此，高基数分区键会导致此错误，应避免。
物化视图与投影
Postgres 允许在单个表上创建多个索引，从而能够针对各种访问模式进行优化。这种灵活性允许管理员和开发人员根据特定的查询和操作需求来调整数据库性能。ClickHouse 的投影概念虽然与之不完全相同，但允许用户为表指定多个ORDER BY子句。
在 ClickHouse数据建模文档中，我们探讨了如何在 ClickHouse 中使用物化视图来预先计算聚合、转换行以及优化不同访问模式的查询。
对于后一点，我们提供了一个示例，其中物化视图将行发送到与接收插入的原始表具有不同排序键的目标表。
例如，考虑以下查询
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
└─────────────────────┘
1 row in set. Elapsed: 0.040 sec. Processed 90.38 million rows, 361.59 MB (2.25 billion rows/s., 9.01 GB/s.)
Peak memory usage: 201.93 MiB.
此查询需要扫描所有 9000 万行（当然速度很快），因为UserId不是排序键。之前，我们使用充当 PostId 的查找表的物化视图解决了这个问题。相同的问题可以用投影来解决。以下命令添加了ORDER BY user_id的投影。
ALTER TABLE comments ADD PROJECTION comments_user_id (
SELECT * ORDER BY UserId
)
ALTER TABLE comments MATERIALIZE PROJECTION comments_user_id
请注意，我们必须首先创建投影，然后将其物化。此后一个命令导致数据以两种不同的顺序存储在磁盘上两次。也可以在创建数据时定义投影，如下所示，并且在插入数据时将自动维护。
CREATE TABLE comments
(
`Id` UInt32,
`PostId` UInt32,
`Score` UInt16,
`Text` String,
`CreationDate` DateTime64(3, 'UTC'),
`UserId` Int32,
`UserDisplayName` LowCardinality(String),
PROJECTION comments_user_id
(
SELECT *
ORDER BY UserId
)
)
ENGINE = MergeTree
ORDER BY PostId
如果通过ALTER创建投影，则在发出MATERIALIZE PROJECTION命令时，创建操作是异步的。用户可以使用以下查询确认此操作的进度，等待is_done=1。
SELECT
parts_to_do,
is_done,
latest_fail_reason
FROM system.mutations
WHERE (`table` = 'comments') AND (command LIKE '%MATERIALIZE%')
┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
1. │ 1 │ 0 │ │
└─────────────┴─────────┴────────────────────┘
1 row in set. Elapsed: 0.003 sec.
如果我们重复上述查询，我们可以看到性能有了显着提高，但代价是增加了存储空间。
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
└─────────────────────┘
1 row in set. Elapsed: 0.008 sec. Processed 16.36 thousand rows, 98.17 KB (2.15 million rows/s., 12.92 MB/s.)
Peak memory usage: 4.06 MiB.
使用EXPLAIN命令，我们还可以确认投影已用于服务此查询
EXPLAIN indexes = 1
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
┌─explain─────────────────────────────────────────────┐
1. │ Expression ((Projection + Before ORDER BY)) │
2. │ Aggregating │
3. │ Filter │
4. │ ReadFromMergeTree (comments_user_id) │
5. │ Indexes: │
6. │ PrimaryKey │
7. │ Keys: │
8. │ UserId │
9. │ Condition: (UserId in [8592047, 8592047]) │
10. │ Parts: 2/2 │
11. │ Granules: 2/11360 │
└─────────────────────────────────────────────────────┘
11 rows in set. Elapsed: 0.004 sec.
何时使用投影
投影对于新用户来说是一个有吸引力的功能，因为它们在插入数据时会自动维护。此外，查询可以简单地发送到单个表，其中尽可能利用投影来加快响应时间。
这与物化视图形成对比，在物化视图中，用户必须根据过滤器选择合适优化的目标表或重写其查询。这更强调用户应用程序并增加了客户端的复杂性。
尽管有这些优势，但投影也存在一些固有的局限性，用户应该意识到这一点，因此应该谨慎部署。
- 投影不允许对源表和（隐藏的）目标表使用不同的 TTL，物化视图允许不同的 TTL。
- 投影目前不支持（隐藏的）目标表的
optimize_read_in_order。 - 对于带有投影的表，不支持轻量级更新和删除。
- 物化视图可以进行链式操作：一个物化视图的目标表可以作为另一个物化视图的源表，依此类推。投影不支持这种操作。
- 投影不支持连接；物化视图支持。
- 投影不支持过滤（WHERE 子句）；物化视图支持。
我们建议在以下情况下使用投影：
- 需要对数据进行完全重新排序。虽然理论上投影中的表达式可以使用
GROUP BY，但物化视图更有效地维护聚合。查询优化器也更有可能利用使用简单重新排序的投影，例如SELECT * ORDER BY x。用户可以在此表达式中选择列的子集以减少存储占用。 - 用户可以接受存储占用和写入数据两次带来的额外开销。测试对插入速度的影响并评估存储开销。