ReplacingMergeTree

虽然事务数据库针对事务性更新和删除工作负载进行了优化，但 OLAP 数据库对这些操作提供的保证较少。相反，它们针对以批处理方式插入的不可变数据进行优化，从而显著提高分析查询的速度。虽然 ClickHouse 通过 mutations 提供更新操作，以及一种轻量级的删除行方式，但其列式结构意味着这些操作应谨慎安排，如上所述。这些操作是异步处理的，使用单个线程处理，并且需要（在更新的情况下）在磁盘上重写数据。因此，不应将其用于大量的小更改。为了处理更新和删除行的流，同时避免上述使用模式，我们可以使用 ClickHouse 表引擎 ReplacingMergeTree。

插入行的自动 upsert

ReplacingMergeTree 表引擎允许将更新操作应用于行，而无需使用低效的 ALTER 或 DELETE 语句，方法是允许您插入同一行的多个副本，并指定一个作为最新版本。反过来，后台进程异步删除同一行的旧版本，通过使用不可变插入有效地模拟更新操作。这依赖于表引擎识别重复行的能力。这是使用 ORDER BY 子句来确定唯一性来实现的，即如果两行在 ORDER BY 中指定的列上具有相同的值，则它们被认为是重复的。在定义表时指定的 version 列，允许在识别两行是重复项时保留该行的最新版本，即保留版本值最高的行。我们在下面的示例中说明了这个过程。在这里，行由 A 列唯一标识（表的 ORDER BY）。我们假设这些行已作为两个批处理插入，从而在磁盘上形成两个数据部分。稍后，在异步后台过程中，这些部分被合并在一起。

ReplacingMergeTree 还可以指定一个已删除的列。这可以包含 0 或 1，其中值为 1 表示该行（及其重复项）已被删除，否则使用零。注意：删除的行不会在合并时被删除。

在此过程中，在数据部分合并期间会发生以下情况

• 由 A 列的值 1 标识的行同时具有版本 2 的更新行和版本 3 的删除行（以及删除列值为 1）。最新的行，标记为已删除，因此被保留。
• 由 A 列的值 2 标识的行具有两个更新行。后面的行保留了价格列的值为 6。
• 由 A 列的值 3 标识的行具有版本 1 的行和版本 2 的删除行。此删除行被保留。

作为此合并过程的结果，我们有四个行表示最终状态

请注意，已删除的行永远不会被删除。可以使用 OPTIMIZE table FINAL CLEANUP 强制删除它们。这需要实验设置 allow_experimental_replacing_merge_with_cleanup=1。只有在以下条件下才应发出此命令

1. 您可以确定在发出操作后不会插入具有旧版本（对于正在删除的版本）的任何行。如果插入这些行，它们将被错误地保留，因为已删除的行将不再存在。
2. 在发出清理之前，请确保所有副本都同步。可以使用以下命令实现：

SYSTEM SYNC REPLICA table

我们建议在保证 (1) 后暂停插入，直到此命令和随后的清理完成。

仅建议对具有少量删除（小于 10%）的表使用 ReplacingMergeTree 进行删除处理，除非可以安排用于使用上述条件进行清理的周期。

提示：您还可以对不再发生更改的选择性分区发出 OPTIMIZE FINAL CLEANUP。

选择主/去重键

上面，我们强调了 ReplacingMergeTree 的情况下也必须满足的一个重要附加约束：ORDER BY 列的值在更改中唯一标识一行。如果从像 Postgres 这样的事务数据库迁移，则应将原始 Postgres 主键包含在 Clickhouse 的 ORDER BY 子句中。

ClickHouse 的用户将熟悉选择表中 ORDER BY 子句中的列以 优化查询性能。通常，应根据您的 频繁查询和按递增基数排序来选择这些列。重要的是，ReplacingMergeTree 施加了一个额外的约束 - 这些列必须是不可变的，即，如果从 Postgres 复制，只有在底层 Postgres 数据中未更改时才将列添加到此子句中。虽然其他列可以更改，但需要保持这些列的一致性以进行唯一的行标识。对于分析工作负载，Postgres 主键通常用处不大，因为您很少执行点行查找。鉴于我们建议按递增基数对列进行排序，以及 ORDER BY 较早列中的匹配通常会更快，因此 Postgres 主键应附加到 ORDER BY 的末尾（除非它具有分析价值）。如果 Postgres 中的多个列形成主键，则应将它们附加到 ORDER BY，同时尊重基数和查询值的可能性。您还可以使用 MATERIALIZED 列通过值的串联生成唯一主键。

考虑来自 Stack Overflow 数据集的 posts 表。

CREATE TABLE stackoverflow.posts_updateable
(
       `Version` UInt32,
       `Deleted` UInt8,
        `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'),
        `Score` Int32,
        `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)),
        `Body` String,
        `OwnerUserId` Int32,
        `OwnerDisplayName` String,
        `LastEditorUserId` Int32,
        `LastEditorDisplayName` String,
        `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)),
        `LastActivityDate` DateTime64(3, 'UTC'),
        `Title` String,
        `Tags` String,
        `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)),
        `CommentCount` UInt8,
        `FavoriteCount` UInt8,
        `ContentLicense` LowCardinality(String),
        `ParentId` String,
        `CommunityOwnedDate` DateTime64(3, 'UTC'),
        `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = ReplacingMergeTree(Version, Deleted)
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate, Id)

我们使用 ORDER BY 键 (PostTypeId, toDate(CreationDate), CreationDate, Id)。Id 列对于每个帖子都是唯一的，可确保可以对行进行去重。根据需要，将 Version 和 Deleted 列添加到架构中。

查询 ReplacingMergeTree

在合并时，ReplacingMergeTree 识别重复的行，使用 ORDER BY 列的值作为唯一标识符，并仅保留最高版本或如果最新版本指示删除则删除所有重复项。但是，这仅提供最终正确性 - 它不能保证行将被去重，您不应依赖它。因此，由于更新和删除的行被考虑在查询中，查询可能会产生不正确的结果。

要获得正确的结果，您需要将后台合并与查询时间去重和删除删除结合使用。这可以使用 FINAL 运算符来实现。

考虑上面的 posts 表。我们可以使用加载此数据集的正常方法，但指定一个删除列和一个版本列，以及值 0。为了举例说明，我们仅加载 10000 行。

INSERT INTO stackoverflow.posts_updateable SELECT 0 AS Version, 0 AS Deleted, *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet') WHERE AnswerCount > 0 LIMIT 10000

0 rows in set. Elapsed: 1.980 sec. Processed 8.19 thousand rows, 3.52 MB (4.14 thousand rows/s., 1.78 MB/s.)

让我们确认行数

SELECT count() FROM stackoverflow.posts_updateable

┌─count()─┐
│   10000 │
└─────────┘

1 row in set. Elapsed: 0.002 sec.

现在我们更新我们的 post-answer 统计信息。与其更新这些值，我们插入 5000 行的新副本，并将其版本号加 1（这意味着表中将存在 150 行）。我们可以使用简单的 INSERT INTO SELECT 模拟此操作

INSERT INTO posts_updateable SELECT
        Version + 1 AS Version,
        Deleted,
        Id,
        PostTypeId,
        AcceptedAnswerId,
        CreationDate,
        Score,
        ViewCount,
        Body,
        OwnerUserId,
        OwnerDisplayName,
        LastEditorUserId,
        LastEditorDisplayName,
        LastEditDate,
        LastActivityDate,
        Title,
        Tags,
        AnswerCount,
        CommentCount,
        FavoriteCount,
        ContentLicense,
        ParentId,
        CommunityOwnedDate,
        ClosedDate
FROM posts_updateable --select 100 random rows
WHERE (Id % toInt32(floor(randUniform(1, 11)))) = 0
LIMIT 5000

0 rows in set. Elapsed: 4.056 sec. Processed 1.42 million rows, 2.20 GB (349.63 thousand rows/s., 543.39 MB/s.)

此外，我们通过重新插入行但将删除列值设置为 1 来删除 1000 个随机帖子。同样，可以使用简单的 INSERT INTO SELECT 模拟此操作。

INSERT INTO posts_updateable SELECT
        Version + 1 AS Version,
        1 AS Deleted,
        Id,
        PostTypeId,
        AcceptedAnswerId,
        CreationDate,
        Score,
        ViewCount,
        Body,
        OwnerUserId,
        OwnerDisplayName,
        LastEditorUserId,
        LastEditorDisplayName,
        LastEditDate,
        LastActivityDate,
        Title,
        Tags,
        AnswerCount + 1 AS AnswerCount,
        CommentCount,
        FavoriteCount,
        ContentLicense,
        ParentId,
        CommunityOwnedDate,
        ClosedDate
FROM posts_updateable --select 100 random rows
WHERE (Id % toInt32(floor(randUniform(1, 11)))) = 0 AND AnswerCount > 0
LIMIT 1000

0 rows in set. Elapsed: 0.166 sec. Processed 135.53 thousand rows, 212.65 MB (816.30 thousand rows/s., 1.28 GB/s.)

上述操作的结果将是 16,000 行，即 10,000 + 5000 + 1000。这里的正确总数是，实际上我们应该比原始总数少 1000 行，即 10,000 - 1000 = 9000。

SELECT count()
FROM posts_updateable

┌─count()─┐
│   10000 │
└─────────┘
1 row in set. Elapsed: 0.002 sec.

您的结果会因发生的合并而异。我们可以看到这里的总数不同，因为我们有重复的行。将 FINAL 应用于表可以获得正确的结果。

SELECT count()
FROM posts_updateable
FINAL

┌─count()─┐
│    9000 │
└─────────┘

1 row in set. Elapsed: 0.006 sec. Processed 11.81 thousand rows, 212.54 KB (2.14 million rows/s., 38.61 MB/s.)
Peak memory usage: 8.14 MiB.

FINAL 性能

FINAL 运算符对查询有一定的性能开销。当查询未过滤主键列时，这将最为明显，导致读取更多数据并增加去重开销。如果您使用 WHERE 条件过滤键列，则加载和传递进行去重的数据将减少。

如果 WHERE 条件不使用键列，ClickHouse 在使用 FINAL 时当前不会利用 PREWHERE 优化。此优化旨在减少非过滤列的读取行数。可以在 此处 找到模拟此 PREWHERE 并从而可能提高性能的示例。

利用 ReplacingMergeTree 的分区

ClickHouse 中的数据合并发生在分区级别。当使用 ReplacingMergeTree 时，我们建议用户根据最佳实践对表进行分区，前提是可以确保此 分区键不会随行更改。这将确保与同一行相关的更新将发送到相同的 ClickHouse 分区。如果您遵守此处概述的最佳实践，可以使用相同的分区键作为 Postgres。

如果这样，您可以使用设置 do_not_merge_across_partitions_select_final=1 来提高 FINAL 查询性能。此设置导致分区独立合并和处理，在使用 FINAL 时。

考虑以下 posts 表，我们不使用任何分区

CREATE TABLE stackoverflow.posts_no_part
(
        `Version` UInt32,
        `Deleted` UInt8,
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        ...
)
ENGINE = ReplacingMergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate, Id)

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

0 rows in set. Elapsed: 182.895 sec. Processed 59.82 million rows, 38.07 GB (327.07 thousand rows/s., 208.17 MB/s.)

为了确保 FINAL 需要做一些工作，我们更新 1m 行 - 通过插入重复的行来增加它们的 AnswerCount。

INSERT INTO posts_no_part SELECT Version + 1 AS Version, Deleted, Id, PostTypeId, AcceptedAnswerId, CreationDate, Score, ViewCount, Body, OwnerUserId, OwnerDisplayName, LastEditorUserId, LastEditorDisplayName, LastEditDate, LastActivityDate, Title, Tags, AnswerCount + 1 AS AnswerCount, CommentCount, FavoriteCount, ContentLicense, ParentId, CommunityOwnedDate, ClosedDate
FROM posts_no_part
LIMIT 1000000

使用 FINAL 计算每年答案的总和

SELECT toYear(CreationDate) AS year, sum(AnswerCount) AS total_answers
FROM posts_no_part
FINAL
GROUP BY year
ORDER BY year ASC

┌─year─┬─total_answers─┐
│ 2008 │        371480 │
...
│ 2024 │        127765 │
└──────┴───────────────┘

17 rows in set. Elapsed: 2.338 sec. Processed 122.94 million rows, 1.84 GB (52.57 million rows/s., 788.58 MB/s.)
Peak memory usage: 2.09 GiB.

重复相同的步骤，对按年份进行分区的表，并重复上述查询，并使用 do_not_merge_across_partitions_select_final=1。

CREATE TABLE stackoverflow.posts_with_part
(
        `Version` UInt32,
        `Deleted` UInt8,
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        ...
)
ENGINE = ReplacingMergeTree
PARTITION BY toYear(CreationDate)
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate, Id)

// populate & update omitted

SELECT toYear(CreationDate) AS year, sum(AnswerCount) AS total_answers
FROM posts_with_part
FINAL
GROUP BY year
ORDER BY year ASC

┌─year─┬─total_answers─┐
│ 2008 │       387832  │
│ 2009 │       1165506 │
│ 2010 │       1755437 │
...
│ 2023 │       787032  │
│ 2024 │       127765  │
└──────┴───────────────┘

17 rows in set. Elapsed: 0.994 sec. Processed 64.65 million rows, 983.64 MB (65.02 million rows/s., 989.23 MB/s.)

如所示，通过允许去重过程在分区级别并行发生，分区已显著提高查询性能。

合并行为注意事项

ClickHouse 的合并选择机制超越了简单的部分合并。下面，我们在 ReplacingMergeTree 的上下文中检查此行为，包括配置选项以启用对旧数据的更积极合并以及对较大部分的考虑。

合并选择逻辑

虽然合并旨在最小化部分数量，但它也会平衡此目标与写入放大的成本。因此，如果某些部分范围被排除在合并之外，因为它们会导致过度的写入放大，基于内部计算。此行为有助于防止不必要的资源使用并延长存储组件的寿命。

大型部分的合并行为

ClickHouse 中的 ReplacingMergeTree 引擎针对管理重复行进行了优化，它通过合并数据部分，仅保留基于指定唯一键的最新版本行。但是，当合并的部分达到 max_bytes_to_merge_at_max_space_in_pool 阈值时，即使设置了 min_age_to_force_merge_seconds，它将不再被选中进行进一步合并。因此，自动合并不再可靠地用于删除可能在持续数据插入过程中累积的重复项。

为了解决这个问题，您可以调用 OPTIMIZE FINAL 手动合并部分并删除重复项。与自动合并不同，OPTIMIZE FINAL 绕过 max_bytes_to_merge_at_max_space_in_pool 阈值，仅基于可用资源（特别是磁盘空间）合并部分，直到每个分区中只剩下一个部分。但是，这种方法对于大型表来说可能占用大量内存，并且可能需要重复执行，因为会添加新数据。

为了获得更可持续的解决方案，同时保持性能，建议对表进行分区。这有助于防止数据部分达到最大合并大小，并减少对持续手动优化的需求。

分区和跨分区的合并

如“使用 ReplacingMergeTree 利用分区”中所讨论的，我们建议将表分区作为最佳实践。分区隔离数据以进行更高效的合并，并避免在查询执行期间跨分区合并。从 23.12 版本开始，此行为得到了增强：如果分区键是排序键的前缀，则在查询时不会执行跨分区合并，从而提高查询性能。

调整合并以提高查询性能

默认情况下，min_age_to_force_merge_seconds 和 min_age_to_force_merge_on_partition_only 分别设置为 0 和 false，从而禁用这些功能。在此配置中，ClickHouse 将应用标准的合并行为，而不会根据分区年龄强制合并。

如果指定了 min_age_to_force_merge_seconds 的值，ClickHouse 将忽略对超过指定期限的部分的常规合并启发式方法。虽然这通常只有在目标是最小化部分总数时才有效，但它可以通过减少查询时需要合并的部分数量来提高 ReplacingMergeTree 中的查询性能。

可以通过将 min_age_to_force_merge_on_partition_only=true 设置为 true 来进一步调整此行为，要求分区中的所有部分都比 min_age_to_force_merge_seconds 更旧才能进行激进的合并。此配置允许较旧的分区随着时间的推移合并到一个部分，从而整合数据并保持查询性能。

推荐设置

注意

调整合并行为是一项高级操作。我们建议在生产工作负载中启用这些设置之前，咨询 ClickHouse 支持团队。

在大多数情况下，最好将 min_age_to_force_merge_seconds 设置为较低的值——明显小于分区周期。这可以最大限度地减少部分数量，并防止在查询时使用 FINAL 运算符进行不必要的合并。

例如，考虑一个已经合并到一个部分中的月度分区。如果一个小的、流离失所的插入操作在此分区内创建了一个新的部分，查询性能可能会受到影响，因为 ClickHouse 必须读取多个部分，直到合并完成。设置 min_age_to_force_merge_seconds 可以确保这些部分被积极合并，从而防止查询性能下降。