使用 ReplacingMergeTree 引擎

在 ClickHouse 中使用 ReplacingMergeTree 引擎的指南

虽然事务型数据库针对事务性更新和删除工作负载进行了优化，但 OLAP 数据库对此类操作提供的保证较弱。相反，它们针对以批次插入的不可变数据进行了优化，以显著提升分析查询的速度。虽然 ClickHouse 通过 mutations 提供更新操作，也提供了轻量级的行删除方式，但其面向列的结构意味着这些操作应按上文所述谨慎安排。这些操作以异步方式处理，由单个线程执行，并且 (对于更新而言) 需要在磁盘上重写数据。因此，不应将它们用于大量零散的小规模变更。 为了在避免上述使用模式的同时处理更新行和删除行的 stream，我们可以使用 ClickHouse 表引擎 ReplacingMergeTree。

​

已插入行的自动 upsert

ReplacingMergeTree 表引擎 支持将更新操作应用于行，而无需使用低效的 ALTER 或 DELETE 语句。它的实现方式是允许你插入同一行的多个副本，并将其中一个标记为最新版本。随后，后台进程会异步移除同一行的旧版本，从而通过不可变插入高效地模拟更新操作。 这依赖于表引擎识别重复行的能力。具体来说，它通过 ORDER BY 子句来判断唯一性：如果两行在 ORDER BY 指定列上的值相同，就会被视为重复行。在定义表时指定的 version 列，则用于在两行被识别为重复时保留该行的最新版本，也就是保留 version 值最高的那一行。 我们将在下面的示例中说明这一过程。这里，行通过 A 列 (即该表的 ORDER BY) 唯一标识。我们假设这些行分两个批次插入，因此在磁盘上形成了两个 parts。随后，在异步后台处理过程中，这些 parts 会被合并。

ReplacingMergeTree 还允许指定一个 deleted 列。该列的值只能是 0 或 1，其中 1 表示该行 (及其重复行) 已被删除，0 则表示未删除。注意：已删除的行不会在合并时被移除。

在这一过程中，parts 合并期间会发生以下情况：

• 对于由 A 列值 1 标识的行，既有一条版本为 2 的更新行，也有一条版本为 3 的删除行 (其 deleted 列值为 1) 。因此，最新的那一行会被保留，而该行已被标记为删除。
• 对于由 A 列值 2 标识的行，有两条更新行。后插入的那一行会被保留，price 列的值为 6。
• 对于由 A 列值 3 标识的行，有一条版本为 1 的行和一条版本为 2 的删除行。最终会保留这条删除行。

经过这一合并过程后，我们得到四行来表示最终状态：

请注意，已删除的行永远不会被移除。可以通过 OPTIMIZE table FINAL CLEANUP 强制删除它们。这需要启用 Experimental 设置 allow_experimental_replacing_merge_with_cleanup=1。只有在以下条件下才应执行此操作：

1. 你能够确保，在执行该操作之后，不会再插入旧版本的行 (即那些将通过 cleanup 删除的行) 。否则，这些行会被错误地保留下来，因为对应的已删除行已经不存在了。
2. 在执行 cleanup 之前，确保所有副本都已同步。可以通过以下命令实现：

SYSTEM SYNC REPLICA table

我们建议在确保满足 (1) 后暂停 insert，并一直保持暂停，直到此命令及后续清理完成。

只有在删除量较低到中等 (少于 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)

我们使用 (PostTypeId, toDate(CreationDate), CreationDate, Id) 作为 ORDER BY 键。每篇帖子唯一的 Id 列可确保行能够去重。还会按要求在 schema 中添加 Version 和 Deleted 列。

​

查询 ReplacingMergeTree

在合并时，ReplacingMergeTree 会识别重复行，将 ORDER BY 列的值用作唯一标识符，并且要么只保留最高版本，要么在最新版本表示删除时移除所有重复项。然而，这只能提供最终一致的正确性——并不能保证行一定会被去重，因此不应依赖它。

使用 FINAL 读取去重后的数据

由于去重只会在 background 合并 期间发生，普通的 SELECT 仍然可能返回重复行或已删除的行。要在查询时读取正确结果，请使用 FINAL 修饰符，它会在查询执行时完成去重并移除已删除的行。

以上面的 posts 表为例。我们可以使用加载该数据集的常规方法，但额外指定 deleted 列和 version 列，并将它们的值设为 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.

我们现在更新回答后的帖子统计信息。我们不直接更新这些值，而是插入 5000 行的新副本，并将它们的版本号加一 (这意味着表中将有 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.)

此外，我们还通过重新插入这些行，并将 deleted 列的值设为 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 确实有事可做，我们通过插入重复行来增加 100 万行数据的 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 的合并选择机制并不仅仅是简单地合并 parts。下面我们将结合 ReplacingMergeTree 说明这种行为，包括可用于对较旧数据执行更激进合并的配置选项，以及针对较大 parts 的相关注意事项。

​

合并选择逻辑

虽然合并的目标是尽量减少 parts 的数量，但也需要在这一目标与写入放大带来的成本之间取得平衡。因此，根据内部计算结果，如果某些 parts 范围会导致过高的写入放大，就会被排除在合并之外。这种机制有助于避免不必要的资源消耗，并延长存储组件的使用寿命。

​

大型 parts 上的合并行为

ClickHouse 中的 ReplacingMergeTree 引擎经过优化，可通过合并 parts 来管理重复行，并仅保留基于指定唯一键的每一行最新版本。不过，当某个已合并 parts 达到 max_bytes_to_merge_at_max_space_in_pool 阈值时，即使设置了 min_age_to_force_merge_seconds，它也不会再被选中进行后续合并。因此，随着数据持续插入而不断累积的重复项，将无法再依赖自动合并来清除。

为了解决这个问题，可以调用 OPTIMIZE FINAL 手动合并 parts 并移除重复项。与自动合并不同，OPTIMIZE FINAL 会绕过 max_bytes_to_merge_at_max_space_in_pool 阈值，仅根据可用资源 (尤其是磁盘空间) 合并 parts，直到每个分区中只剩下一个 part。不过，这种方法在大型表上可能非常耗费内存，而且随着新数据不断写入，可能需要反复执行。

如果希望采用一种更可持续且兼顾性能的解决方案，建议对表进行分区。这有助于避免 parts 达到最大合并大小，并减少持续手动优化的需要。

​

分区以及跨分区合并

如 Exploiting Partitions with 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 将对早于该时间阈值的 parts 忽略常规的合并启发式规则。虽然这种做法通常只在目标是尽量减少 parts 总数时才有明显效果，但在 ReplacingMergeTree 中，它可以通过减少查询时需要合并的 parts 数量来提升查询性能。

还可以通过设置 min_age_to_force_merge_on_partition_only=true 进一步调优这一行为。这样一来，只有当分区中的所有 parts 都早于 min_age_to_force_merge_seconds 时，才会执行更激进的合并。这种配置可使较旧的分区随着时间推移逐步合并为单个 part，从而整合数据并维持查询性能。

​

推荐设置

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

在大多数情况下，建议将 min_age_to_force_merge_seconds 设为较低的值——即明显小于分区周期。这样可以尽量减少 parts 的数量，并避免在查询时使用 FINAL 运算符进行不必要的合并。

例如，假设某个按月分区的数据已经合并成一个单独的 part。如果一次零散的小规模插入在该分区中又创建了一个新的 part，那么在合并完成前，ClickHouse 就必须读取多个 parts，从而可能影响查询性能。设置 min_age_to_force_merge_seconds 可以促使这些 parts 更积极地合并，避免查询性能下降。