模式设计

理解有效的模式设计是优化 ClickHouse 性能的关键，它包括经常涉及权衡取舍的选择，最佳方法取决于要服务的查询以及数据更新频率、延迟要求和数据量等因素。本指南概述了模式设计最佳实践和数据建模技术，以优化 ClickHouse 性能。

Stack Overflow 数据集

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

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

Stack Overflow 数据集包含许多相关的表。在任何数据建模任务中，我们建议用户首先专注于加载其主表。这可能不一定是最大的表，而是您期望接收大多数分析查询的表。这将使您能够熟悉主要 ClickHouse 概念和类型，尤其是在来自以 OLTP 为主的背景时这一点非常重要。随着添加其他表，此表可能需要重塑以充分利用 ClickHouse 功能并获得最佳性能。

上述模式在本文档的目的下并非有意优化。

建立初始模式

由于 posts 表将成为大多数分析查询的目标，因此我们专注于为此表建立一个模式。这些数据可在公共 S3 存储桶 s3://datasets-documentation/stackoverflow/parquet/posts/*.parquet 中获取，每个文件代表一年。

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

ClickHouse 提供了一种模式推断功能，可以自动识别数据集的类型。这适用于所有数据格式，包括 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 支持的所有文件格式兼容。

这为我们提供了一个初始的非优化模式。默认情况下，ClickHouse 会将其映射到等效的可空类型。我们可以使用这些类型通过简单的 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()

在定义了初始模式后，我们可以使用 INSERT INTO SELECT 将数据填充到表中，并使用 s3 表函数读取数据。以下操作在 8 核 ClickHouse 云实例上大约需要 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.)

以上查询加载了 6000 万行。虽然对于 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 中的压缩将受三个主要因素影响：排序键、数据类型和使用的任何编解码器。所有这些都是通过模式配置的。

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

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

提示：要查找所有列的范围和唯一值的个数，用户可以使用简单的查询 SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical。我们建议在较小的数据集上执行此操作，因为它可能会消耗大量资源。此查询需要数字列至少定义为数字类型才能获得准确的结果，即不能是字符串类型。

通过将这些简单的规则应用于我们的帖子表，我们可以为每一列确定最佳的数据类型。

以上操作将得到以下 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 语句，从之前的表中读取数据并插入到新表中。

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

选择排序键

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

在 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 的核心概念，但不幸的是，大多数 Schema 并不像这一个这么简单。

在下面列出的其他指南中，我们将探讨一些技术来重新构建更广泛的 Schema 以优化 ClickHouse 查询。在此过程中，我们的目标是使 Posts 仍然是我们执行大多数分析查询的中心表。虽然其他表仍然可以单独查询，但我们假设大多数分析都希望在 posts 的上下文中执行。

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

以下方法都旨在最大程度地减少使用 JOIN 以优化读取和提高查询性能。虽然 ClickHouse 完全支持 JOIN，但我们建议谨慎使用（JOIN 查询中 2 到 3 个表是可以的）以获得最佳性能。

ClickHouse 没有外键的概念。这并不禁止连接，但意味着引用完整性留给用户在应用程序级别进行管理。在像 ClickHouse 这样的 OLAP 系统中，数据完整性通常在应用程序级别或数据摄取过程中进行管理，而不是由数据库本身强制执行，因为这样会产生很大的开销。这种方法提供了更大的灵活性并加快了数据插入速度。这符合 ClickHouse 关注速度和可扩展性的目标，尤其是在处理非常大的数据集时进行读取和插入查询。

为了最大程度地减少在查询时使用 JOIN，用户有几个工具/方法：

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

我们在每个指南中都探讨了每种方法，重点介绍了每种方法何时适用，并通过示例展示了如何将其应用于解决 Stack Overflow 数据集的问题。

Stack Overflow 数据集​

建立初始模式​

优化类型​

选择排序键​

示例​

下一节：数据建模技术​

Stack Overflow 数据集

建立初始模式

优化类型

选择排序键

示例

下一节：数据建模技术