模式设计

理解有效的 Schema 设计是优化 ClickHouse 性能的关键，其中包含许多需要在权衡后做出的选择。最佳方法取决于所服务的查询，以及数据更新频率、延迟要求和数据量等因素。本指南概述了 Schema 设计的最佳实践和数据建模技术，以优化 ClickHouse 性能。

Stack Overflow 数据集

对于本指南中的示例，我们使用 Stack Overflow 数据集的一个子集。它包含从 2008 年到 2024 年 4 月在 Stack Overflow 上发生的所有帖子、投票、用户、评论和徽章。这些数据以 Parquet 格式存储在 S3 bucket s3://datasets-documentation/stackoverflow/parquet/ 下。

指示的主要键和关系不会通过约束强制执行（Parquet 是文件而非表格式），仅纯粹地指示数据之间的关系以及它所拥有的唯一键。

Stack Overflow 数据集包含许多相关的表。在任何数据建模任务中，我们建议用户首先加载主表。这不一定是非最大的表，而是您预计会收到最多分析查询的表。这将使您熟悉 ClickHouse 的主要概念和类型，这对于来自以 OLTP 为主的环境来说尤其重要。随着更多表的添加，此表可能需要进行重新建模，以充分利用 ClickHouse 的功能并获得最佳性能。

上述 Schema 故意不是本指南目的所要求的最佳 Schema。

建立初始 Schema

由于 posts 表将是大多数分析查询的目标，因此我们专注于为该表建立 Schema。这些数据可在公共 S3 bucket s3://datasets-documentation/stackoverflow/parquet/posts/*.parquet 中找到，每个年份一个文件。

以 Parquet 格式从 S3 加载数据是加载数据到 ClickHouse 的最常见和首选方法。ClickHouse 针对处理 Parquet 进行了优化，并且每秒可以从 S3 读取和插入数百万行数据。

ClickHouse 提供 Schema 推断功能，以自动识别数据集的类型。这支持所有数据格式，包括 Parquet。我们可以利用此功能通过 s3 表函数和 DESCRIBE 命令识别 ClickHouse 的数据类型。请注意，我们使用 glob 模式 *.parquet 来读取 stackoverflow/parquet/posts 文件夹中的所有文件。

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')
SETTINGS describe_compact_output = 1

┌─name──────────────────┬─type───────────────────────────┐
│ Id                    │ Nullable(Int64)               │
│ PostTypeId            │ Nullable(Int64)               │
│ AcceptedAnswerId      │ Nullable(Int64)               │
│ CreationDate          │ Nullable(DateTime64(3, 'UTC')) │
│ Score                 │ Nullable(Int64)               │
│ ViewCount             │ Nullable(Int64)               │
│ Body                  │ Nullable(String)              │
│ OwnerUserId           │ Nullable(Int64)               │
│ OwnerDisplayName      │ Nullable(String)              │
│ LastEditorUserId      │ Nullable(Int64)               │
│ LastEditorDisplayName │ Nullable(String)              │
│ LastEditDate          │ Nullable(DateTime64(3, 'UTC')) │
│ LastActivityDate      │ Nullable(DateTime64(3, 'UTC')) │
│ Title                 │ Nullable(String)              │
│ Tags                  │ Nullable(String)              │
│ AnswerCount           │ Nullable(Int64)               │
│ CommentCount          │ Nullable(Int64)               │
│ FavoriteCount         │ Nullable(Int64)               │
│ ContentLicense        │ Nullable(String)              │
│ ParentId              │ Nullable(String)              │
│ CommunityOwnedDate    │ Nullable(DateTime64(3, 'UTC')) │
│ ClosedDate            │ Nullable(DateTime64(3, 'UTC')) │
└───────────────────────┴────────────────────────────────┘

s3 表函数允许 ClickHouse 原地查询 S3 中的数据。此函数与 ClickHouse 支持的所有文件格式兼容。

这为我们提供了一个初始的未优化的 Schema。默认情况下，ClickHouse 将这些映射到等效的 Nullable 类型。我们可以使用这些类型通过简单的 CREATE EMPTY AS SELECT 命令创建 ClickHouse 表。

CREATE TABLE posts
ENGINE = MergeTree
ORDER BY () EMPTY AS
SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')

一些重要说明

在运行此命令后，我们的 posts 表是空的。没有加载任何数据。我们指定 MergeTree 作为我们的表引擎。MergeTree 是您可能使用的最常见的 ClickHouse 表引擎。它是您 ClickHouse 工具箱中的多功能工具，能够处理 PB 级的数据，并服务于大多数分析用例。其他表引擎存在于需要支持高效更新的用例，例如 CDC。

子句 ORDER BY () 表示我们没有索引，更具体地说，我们的数据没有顺序。稍后会详细介绍。现在，只需知道所有查询都需要线性扫描即可。

要确认已创建表

SHOW CREATE TABLE posts

CREATE TABLE posts
(
        `Id` Nullable(Int64),
        `PostTypeId` Nullable(Int64),
        `AcceptedAnswerId` Nullable(Int64),
        `CreationDate` Nullable(DateTime64(3, 'UTC')),
        `Score` Nullable(Int64),
        `ViewCount` Nullable(Int64),
        `Body` Nullable(String),
        `OwnerUserId` Nullable(Int64),
        `OwnerDisplayName` Nullable(String),
        `LastEditorUserId` Nullable(Int64),
        `LastEditorDisplayName` Nullable(String),
        `LastEditDate` Nullable(DateTime64(3, 'UTC')),
        `LastActivityDate` Nullable(DateTime64(3, 'UTC')),
        `Title` Nullable(String),
        `Tags` Nullable(String),
        `AnswerCount` Nullable(Int64),
        `CommentCount` Nullable(Int64),
        `FavoriteCount` Nullable(Int64),
        `ContentLicense` Nullable(String),
        `ParentId` Nullable(String),
        `CommunityOwnedDate` Nullable(DateTime64(3, 'UTC')),
        `ClosedDate` Nullable(DateTime64(3, 'UTC'))
)
ENGINE = MergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
ORDER BY tuple()

在定义了初始 Schema 后，我们可以使用 INSERT INTO SELECT 填充数据，使用 s3 表函数读取数据。以下查询在 8 核 ClickHouse Cloud 实例上大约需要 2 分钟加载 posts 数据。

INSERT INTO posts SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')

0 rows in set. Elapsed: 148.140 sec. Processed 59.82 million rows, 38.07 GB (403.80 thousand rows/s., 257.00 MB/s.)

上述查询加载了 60m 行。虽然对于 ClickHouse 来说很小，但互联网连接较慢的用户可能希望加载数据的子集。可以通过简单地指定他们希望通过 glob 模式加载的年份来实现，例如 https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2008.parquet 或 https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/{2008, 2009}.parquet。有关如何使用 glob 模式来定位文件子集的信息，请参阅此处。

优化类型

ClickHouse 查询性能的秘诀之一是压缩。

磁盘上的数据越少，I/O 越少，查询和插入速度就越快。任何压缩算法相对于 CPU 的开销在大多数情况下都将小于 I/O 的减少。因此，在努力确保 ClickHouse 查询速度快时，首先关注数据压缩的改进。

有关 ClickHouse 压缩数据的原因，我们推荐这篇文章。简而言之，作为面向列的数据库，值将按列顺序写入。如果这些值已排序，则相同的值将彼此相邻。压缩算法利用连续的数据模式。在此基础上，ClickHouse 具有编解码器和粒度数据类型，可让您进一步调整压缩技术。

ClickHouse 中的压缩受 3 个主要因素影响：排序键、数据类型和使用的编解码器。所有这些都通过 Schema 进行配置。

通过简单的类型优化过程可以获得压缩和查询性能的最初的改进。可以应用一些简单的规则来优化 Schema

使用严格类型 - 我们的初始 Schema 许多列都使用了 Strings，而这些显然是数值。使用正确的类型将确保在过滤和聚合时获得预期的语义。日期类型也是如此，这些类型在 Parquet 文件中已正确提供。
避免可为空的列 - 默认情况下，上述列被假定为 Null。Nullable 类型允许查询确定空值和 Null 值之间的区别。这将创建一个单独的 UInt8 类型列。每次用户使用可为空的列时，都必须处理此附加列。这会导致额外的存储空间使用，并且几乎总是会影响查询性能。仅当空值和 Null 值之间存在差异时才使用 Nullable。例如，对于 ViewCount 列中的空值，0 值可能足以满足大多数查询需求，而不会影响结果。如果应以不同的方式处理空值，通常也可以使用过滤器从查询中排除它们。使用数字类型的最小精度 - ClickHouse 具有许多数字类型，专为不同的数字范围和精度而设计。始终努力最小化用于表示列的位数。除了不同大小的整数（例如 Int16），ClickHouse 还提供无符号变体，其最小值是 0。如果可能，这些可以允许为列使用更少的位数。例如，UInt16 的最大值为 65535，是 Int16 的两倍。
日期类型的最小精度 - ClickHouse 支持多种日期和日期时间类型。Date 和 Date32 可用于存储纯日期，后者以牺牲更多位数的情况下支持更大的日期范围。DateTime 和 DateTime64 提供对日期时间的支持。DateTime 限制为秒级粒度并使用 32 位。DateTime64，顾名思义，使用 64 位，但提供高达纳秒级的粒度。一如既往，选择可接受的更粗略的版本以用于查询，从而最大限度地减少所需的位数。
使用 LowCardinality - 具有少量唯一值的数字、字符串、Date 或 DateTime 列可以使用 LowCardinality 类型进行编码。这会以字典编码值，从而减小磁盘上的大小。考虑对具有少于 10k 个唯一值的列使用此选项。固定字符串用于特殊情况 - 具有固定长度的字符串可以使用 FixedString 类型进行编码，例如语言和货币代码。当数据恰好为 N 字节时，这很有效。在所有其他情况下，它可能会降低效率，并且 LowCardinality 是首选。
枚举用于数据验证 - Enum 类型可用于有效地编码枚举类型。枚举可以是 8 位或 16 位，具体取决于它们需要存储的唯一值的数量。如果您需要关联的插入时验证（未声明的值将被拒绝）或希望执行利用 Enum 值中自然顺序的查询，请考虑使用此选项。例如，一个反馈列包含用户响应 Enum(':(' = 1, ':|' = 2, ':)' = 3)。

提示：要查找所有列的范围和不同值的数量，可以使用简单的查询 SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical。我们建议对数据的较小子集执行此操作，因为这可能会很昂贵。此查询要求至少定义数字为这样才能获得准确的结果，即不是字符串。

通过将这些简单的规则应用于我们的 posts 表，我们可以识别每个列的最佳类型

列	是否为数值	最小值，最大值	唯一值	空值	注释	优化后的类型
`PostTypeId`	是	1, 8	8	否		`Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8)`
`AcceptedAnswerId`	是	0, 78285170	12282094	是	用 0 值区分空值	UInt32
`CreationDate`	否	2008-07-31 21:42:52.667000000, 2024-03-31 23:59:17.697000000	-	否	不需要毫秒级粒度，使用 DateTime	DateTime
`Score`	是	-217, 34970	3236	否		Int32
`ViewCount`	是	2, 13962748	170867	否		UInt32
`Body`	否	-	-	否		String
`OwnerUserId`	是	-1, 4056915	6256237	是		Int32
`OwnerDisplayName`	否	-	181251	是	将空值视为空字符串	String
`LastEditorUserId`	是	-1, 9999993	1104694	是	0 是一个未使用的值，可用于空值	Int32
`LastEditorDisplayName`	否	-	70952	是	将空值视为空字符串。测试了 LowCardinality，没有好处	String
`LastEditDate`	否	2008-08-01 13:24:35.051000000, 2024-04-06 21:01:22.697000000	-	否	不需要毫秒级粒度，使用 DateTime	DateTime
`LastActivityDate`	否	2008-08-01 12:19:17.417000000, 2024-04-06 21:01:22.697000000	-	否	不需要毫秒级粒度，使用 DateTime	DateTime
`Title`	否	-	-	否	将空值视为空字符串	String
`Tags`	否	-	-	否	将空值视为空字符串	String
`AnswerCount`	是	0, 518	216	否	将空值和 0 视为相同	UInt16
`CommentCount`	是	0, 135	100	否	将空值和 0 视为相同	UInt8
`FavoriteCount`	是	0, 225	6	是	将空值和 0 视为相同	UInt8
`ContentLicense`	否	-	3	否	LowCardinality 优于 FixedString	LowCardinality(String)
`ParentId`	否	-	20696028	是	将空值视为空字符串	String
`CommunityOwnedDate`	否	2008-08-12 04:59:35.017000000, 2024-04-01 05:36:41.380000000	-	是	考虑将默认值设置为 1970-01-01 以表示空值。不需要毫秒级粒度，使用 DateTime	DateTime
`ClosedDate`	否	2008-09-04 20:56:44, 2024-04-06 18:49:25.393000000	-	是	考虑将默认值设置为 1970-01-01 以表示空值。不需要毫秒级粒度，使用 DateTime	DateTime

上述内容为我们提供了以下 Schema

CREATE TABLE posts_v2
(
   `Id` Int32,
   `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   `AcceptedAnswerId` UInt32,
   `CreationDate` DateTime,
   `Score` Int32,
   `ViewCount` UInt32,
   `Body` String,
   `OwnerUserId` Int32,
   `OwnerDisplayName` String,
   `LastEditorUserId` Int32,
   `LastEditorDisplayName` String,
   `LastEditDate` DateTime,
   `LastActivityDate` DateTime,
   `Title` String,
   `Tags` String,
   `AnswerCount` UInt16,
   `CommentCount` UInt8,
   `FavoriteCount` UInt8,
   `ContentLicense`LowCardinality(String),
   `ParentId` String,
   `CommunityOwnedDate` DateTime,
   `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
COMMENT 'Optimized types'

我们可以使用简单的 INSERT INTO SELECT 填充此 Schema，从我们的先前表读取数据并将其插入到此表中

INSERT INTO posts_v2 SELECT * FROM posts

0 rows in set. Elapsed: 146.471 sec. Processed 59.82 million rows, 83.82 GB (408.40 thousand rows/s., 572.25 MB/s.)

我们的新 Schema 中不保留任何 Null 值。上述插入将其隐式转换为各自类型的默认值 - 整数为 0，字符串为空值。ClickHouse 还会自动将任何数字转换为目标精度。ClickHouse 中的主（排序）键来自 OLTP 数据库的用户通常会在 ClickHouse 中寻找等效的概念。

选择排序键

在 ClickHouse 经常使用的规模下，内存和磁盘效率至关重要。数据以称为 parts 的块写入 ClickHouse 表，并应用规则以在后台合并这些 parts。在 ClickHouse 中，每个 part 都有自己的主索引。当 parts 合并时，合并的 part 的主索引也会合并。part 的主索引每组行都有一个索引条目 - 此技术称为稀疏索引。

ClickHouse 中选择的键将确定索引，以及数据写入磁盘的顺序。因此，它可以极大地影响压缩级别，进而影响查询性能。导致大多数列的值按连续顺序写入的排序键将允许所选的压缩算法（和编解码器）更有效地压缩数据。

表中的所有列都将根据指定的排序键的值进行排序，无论它们是否包含在键本身中。例如，如果使用 CreationDate 作为键，则所有其他列中的值的顺序将对应于 CreationDate 列中的值的顺序。可以指定多个排序键 - 这将以与 SELECT 查询中的 ORDER BY 子句相同的语义进行排序。

可以应用一些简单的规则来帮助选择排序键。以下内容有时可能存在冲突，因此请按此顺序考虑这些内容。您可以从这个过程中识别出几个键，通常 4-5 个就足够了

选择与您的常见过滤器对齐的列。如果某个列经常在 WHERE 子句中使用，请优先将其包含在您的键中，而不是较少使用的列。优先选择在过滤时可以排除总行数的很大百分比的列，从而减少需要读取的数据量。
优先选择可能与其他表列高度相关的列。这将有助于确保这些值也以连续的方式存储，从而提高压缩率。对排序键中的列进行的 GROUP BY 和 ORDER BY 操作可以更有效地利用内存。

在确定排序键的列子集时，请以特定顺序声明这些列。此顺序会显著影响查询中二级键列上的过滤效率以及表数据文件的压缩比。通常，最好按基数升序排列键。这应与以下事实相平衡：对出现在排序键中较后列的过滤效率低于对出现在元组中较早列的过滤效率。平衡这些行为并考虑您的访问模式（最重要的是测试变体）。

示例

将上述准则应用于我们的 posts 表，假设我们的用户希望执行按日期和帖子类型过滤的分析，例如：

"哪些问题在过去 3 个月内收到的评论最多？"

使用我们之前优化了类型但没有排序键的 posts_v2 表，针对此问题的查询：

SELECT
    Id,
    Title,
    CommentCount
FROM posts_v2
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
ORDER BY CommentCount DESC
LIMIT 3

┌───────Id─┬─Title─────────────────────────────────────────────────────────────┬─CommentCount─┐
│ 78203063 │ How to avoid default initialization of objects in std::vector?     │               74 │
│ 78183948 │ About memory barrier                                               │               52 │
│ 77900279 │ Speed Test for Buffer Alignment: IBM's PowerPC results vs. my CPU │        49 │
└──────────┴───────────────────────────────────────────────────────────────────┴──────────────

10 rows in set. Elapsed: 0.070 sec. Processed 59.82 million rows, 569.21 MB (852.55 million rows/s., 8.11 GB/s.)
Peak memory usage: 429.38 MiB.

即使扫描了所有 6000 万行，此查询也非常快 - ClickHouse 就是很快 :) 您需要相信我们，排序键在 TB 和 PB 级别上是值得的！

让我们选择 PostTypeId 和 CreationDate 作为我们的排序键。

也许在我们的例子中，我们期望用户始终按 PostTypeId 过滤。它的基数是 8，代表了排序键中第一个条目的逻辑选择。考虑到日期粒度过滤可能就足够了（它仍然会受益于日期时间过滤器），因此我们将 toDate(CreationDate) 作为我们键的第二个组成部分。这将产生更小的索引，因为日期可以用 16 位表示，从而加快过滤速度。我们的最终键条目是 CommentCount，以帮助找到评论最多的帖子（最终排序）。

CREATE TABLE posts_v3
(
        `Id` Int32,
        `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime,
        `Score` Int32,
        `ViewCount` UInt32,
        `Body` String,
        `OwnerUserId` Int32,
        `OwnerDisplayName` String,
        `LastEditorUserId` Int32,
        `LastEditorDisplayName` String,
        `LastEditDate` DateTime,
        `LastActivityDate` DateTime,
        `Title` String,
        `Tags` String,
        `AnswerCount` UInt16,
        `CommentCount` UInt8,
        `FavoriteCount` UInt8,
        `ContentLicense` LowCardinality(String),
        `ParentId` String,
        `CommunityOwnedDate` DateTime,
        `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)
COMMENT 'Ordering Key'

--populate table from existing table

INSERT INTO posts_v3 SELECT * FROM posts_v2

0 rows in set. Elapsed: 158.074 sec. Processed 59.82 million rows, 76.21 GB (378.42 thousand rows/s., 482.14 MB/s.)
Peak memory usage: 6.41 GiB.

Our previous query improves the query response time by over 3x:

SELECT
    Id,
    Title,
    CommentCount
FROM posts_v3
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
ORDER BY CommentCount DESC
LIMIT 3

10 rows in set. Elapsed: 0.020 sec. Processed 290.09 thousand rows, 21.03 MB (14.65 million rows/s., 1.06 GB/s.)

对于有兴趣了解使用特定类型和适当排序键所实现的压缩改进的用户，请参阅 ClickHouse 中的压缩。如果您需要进一步提高压缩率，我们还建议阅读选择正确的列压缩编解码器。

接下来：数据建模技术

到目前为止，我们只迁移了一个表。虽然这使我们能够介绍一些核心 ClickHouse 概念，但大多数模式不幸的是并不那么简单。

在下面列出的其他指南中，我们将探索多种重构更广泛模式的技术，以实现最佳的 ClickHouse 查询。在此过程中，我们旨在使 Posts 保持我们的中心表，通过该表执行大多数分析查询。虽然其他表仍然可以独立查询，但我们假设大多数分析都希望在 posts 的上下文中执行。

在本节中，我们使用其他表的优化变体。虽然我们提供了这些模式，但为了简洁起见，我们省略了所做的决策。这些基于前面描述的规则，并将推断决策留给读者。

所有这些方法都旨在最大限度地减少使用 JOIN 的需要，以优化读取并提高查询性能。虽然 ClickHouse 完全支持 JOIN，但我们建议谨慎使用它们（JOIN 查询中 2 到 3 个表是可以的）以实现最佳性能。

ClickHouse 没有外键的概念。这并不禁止 JOIN，但意味着引用完整性由用户在应用程序级别管理。在 ClickHouse 等 OLAP 系统中，数据完整性通常在应用程序级别或数据摄取过程中管理，而不是由数据库本身强制执行，因为这会产生显著的开销。这种方法允许更大的灵活性和更快的插入速度。这与 ClickHouse 对大型数据集的读取和插入查询的速度和可扩展性的关注一致。

为了最大限度地减少查询时使用 JOIN，用户可以使用多种工具/方法：

反规范化数据 - 通过组合表并对非 1:1 关系使用复杂类型来反规范化数据。这通常涉及将任何 JOIN 从查询时间移动到插入时间。
字典 - ClickHouse 特有的功能，用于处理直接 JOIN 和键值查找。
增量物化视图 - ClickHouse 功能，用于将计算成本从查询时间转移到插入时间，包括增量计算聚合值的能力。
可刷新物化视图 - 与其他数据库产品中使用的物化视图类似，该功能允许定期计算查询结果并将结果缓存。

我们在每个指南中探讨这些方法中的每一种，突出显示何时适合使用每一种方法，并提供一个示例，说明如何将其应用于解决 Stack Overflow 数据集中的问题。

Stack Overflow 数据集​

建立初始 Schema​

优化类型​

选择排序键​

示例​

接下来：数据建模技术​

Stack Overflow 数据集

建立初始 Schema

优化类型

选择排序键

示例

接下来：数据建模技术