ClickHouse 中的更新

在处理更新时，分析型数据库和事务型数据库由于底层设计理念和目标用例不同，采用了不同的更新处理方式。 ClickHouse 是一种列式数据库，针对以读为主的分析场景和高吞吐的仅追加操作进行了优化。 在实际使用中，通常会通过重构表结构，将删除和更新转换为追加操作，并以异步方式和/或在读取时处理，从而充分发挥 ClickHouse 在高吞吐数据摄取方面的优势。 ClickHouse 也支持功能完备的更新和删除操作。

本指南概述了 ClickHouse 中可用的更新方法，并帮助您根据工作负载选择合适的更新策略。

选择更新策略

在 ClickHouse 中更新数据大致有两种基本方法：

1. 使用专用表引擎，通过插入操作来处理更新
2. 使用诸如 UPDATE ... SET 或 ALTER TABLE ... UPDATE 之类的声明式更新语句

在上述两大类别中，各自都有多种更新数据的方式。 每种方式都有其各自的优点和性能特征，你应根据数据模型以及计划更新的数据量来选择合适的方法。

何时使用专用表引擎

当存在大量更新、频繁的行级变更，或需要处理持续不断的更新与删除事件流时，专用表引擎是更好的选择。

通常会遇到的引擎包括：

Engine	Syntax	When to use
ReplacingMergeTree	ENGINE = ReplacingMergeTree	在更新大量数据时使用。此表引擎针对合并过程中的数据去重进行了优化。
CoalescingMergeTree	ENGINE = CoalescingMergeTree	当数据以碎片形式到达，并且你需要在列级进行合并而不是整行替换时使用。
CollapsingMergeTree	ENGINE = CollapsingMergeTree(Sign)	在需要频繁更新单行记录，或需要维护随时间变化对象的最新状态的场景中使用。例如，用于跟踪用户活动或文章统计数据。

由于 MergeTree 系列表引擎会在后台合并分区片段，它们提供的是_最终一致性_，因此在查询表时，需要使用 FINAL 关键字，以确保在此期间能够正确完成去重。 还有其他引擎类型，但以上这些是最常用的几种。

何时使用声明式更新

对于无需管理去重逻辑的简单更新操作，声明式 UPDATE 语句会更直接，但与专用引擎相比，它通常更适合以较低频率更新较少数量的行。

方法	语法	使用场景
Lightweight updates	UPDATE [table] SET ... WHERE	在大多数场景中都使用此方法，尤其是在将频繁的小规模 UPDATE (最多约占整张表的 10%) 作为应用或工作流的一部分运行时。例如，某个用户想要删除自己的事件历史记录，而这些事件分散在一个包含许多用户的多租户表中。此方法会创建补丁分区片段以实现立即可见，而无需重写整个列。会为 SELECT 查询增加开销，但延迟可预测。
Update 变更	ALTER TABLE [table] UPDATE	在执行更大规模的数据管理时使用此方法，尤其是在更新与表的分区方式一致时。例如，你需要更新按月分区的表中某一个月内所有行的某一列。

使用专用表引擎进行数据更新

ReplacingMergeTree

ReplacingMergeTree 在后台合并过程中，会对具有相同排序键的行进行去重，只保留最新版本。

CREATE TABLE posts
(
    Id UInt32,
    Title String,
    ViewCount UInt32,
    Version UInt32
)
ENGINE = ReplacingMergeTree(Version)
ORDER BY Id

此引擎非常适合对由稳定键标识的单行数据进行高频更新。 基准测试表明，在单行更新场景下，它的速度最高可比使用变更的方式快 4,700 倍。

要更新一行，只需插入一个具有相同排序键值且版本号更高的新版本。旧版本会在后台合并过程中被移除。由于去重是最终完成的（仅在合并期间发生），应使用 FINAL 修饰符或等效的查询逻辑来获得正确且已去重的结果。FINAL 修饰符会带来 21–550% 的查询开销，具体取决于数据情况。

ReplacingMergeTree 无法更新排序键的值。它还支持用于逻辑删除的 Deleted 列。

延伸阅读： ReplacingMergeTree 指南 | ReplacingMergeTree 参考

CoalescingMergeTree

CoalescingMergeTree 在合并过程中通过对每一列保留最新的非空值来整合稀疏记录。这样可以在列级别执行 upsert 操作，而无需替换整行数据。

CREATE TABLE electric_vehicle_state
(
    vin String, -- vehicle identification number
    last_update DateTime64 Materialized now64(), -- optional (used with argMax)
    battery_level Nullable(UInt8), -- in %
    lat Nullable(Float64), -- latitude (°)
    lon Nullable(Float64), -- longitude (°)
    firmware_version Nullable(String),
    cabin_temperature Nullable(Float32), -- in °C
    speed_kmh Nullable(Float32) -- from sensor
)
ENGINE = CoalescingMergeTree
ORDER BY vin;

该引擎专为数据以碎片形式从多个来源到达，或不同列在不同时间被写入的场景而设计。常见用例包括来自碎片化子系统的 IoT 遥测、用户画像丰富，以及存在延迟维度的 ETL 管道。

当具有相同排序键的行被合并时，CoalescingMergeTree 会为每一列保留最新的非空值，而不是替换整行。为使其按预期工作，非键列应为 Nullable。与 ReplacingMergeTree 一样，使用 FINAL 以获得正确且合并后的结果。

该引擎从 ClickHouse 25.6 版本开始可用。

进一步阅读：CoalescingMergeTree

CollapsingMergeTree

基于这样一种思路：更新开销很大，但可以利用插入操作来完成更新，CollapsingMergeTree 使用一个 Sign 列来告诉 ClickHouse 在合并期间如何处理行。如果为 Sign 列插入 -1，则当其与匹配的 +1 行配对时，该行会被折叠（删除）。要更新的行是根据在创建表时 ORDER BY 子句中使用的排序键来识别的。

CREATE TABLE user_activity
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

-- Initial state
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, 1)

-- Cancel old row and insert new state
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, -1)
INSERT INTO user_activity VALUES (4324182021466249494, 6, 185, 1)

-- Query with proper aggregation
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM user_activity
GROUP BY UserID
HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

与 ReplacingMergeTree 不同，CollapsingMergeTree 允许你修改排序键的值。它非常适合具有可撤销语义的可逆操作，例如金融交易或游戏状态跟踪。

注意

上述更新方法要求你的应用程序在客户端维护状态，以便插入撤销行（cancellation row）。虽然从 ClickHouse 的角度来看这是最高效的，但在大规模场景下使用可能会比较复杂。查询还需要结合符号相乘的聚合才能生成正确结果。

延伸阅读：CollapsingMergeTree

声明式更新

这些方法适用于使用 MergeTree family 引擎的表。

方法	语法	最佳适用场景	权衡
变更	ALTER TABLE ... UPDATE	不频繁的大批量更新；特别适合更新与表分区方式对齐的场景	I/O 开销大；重写列数据
Lightweight updates	UPDATE ... SET ... WHERE	小规模更新 (约 0.1–10% 的行) ；对性能要求较高的高频更新	增加 SELECT 开销；补丁分区片段数量计入分区片段数量限制

变更操作 (变更)

变更操作 (ALTER TABLE ... UPDATE) 会重写所有包含匹配 WHERE 表达式的行的分区片段。

ALTER TABLE posts UPDATE AnswerCount = AnswerCount + 1 WHERE AnswerCount = 0

变更 的 I/O 开销较大，会重写所有匹配 WHERE 表达式的分区片段。 该过程不具备原子性。 一旦变更完成，相应分区片段就会被替换，且在 变更 执行期间启动的 SELECT 查询会同时看到已经被变更的分区片段数据和尚未被变更的分区片段数据。 你可以通过 system.mutations 表跟踪其进度状态。

注意

变更 是 I/O 密集型操作，应谨慎使用，因为它们会影响集群的 SELECT 性能。如果 变更 入队速度快于处理速度，查询性能将会下降。请通过 system.mutations 监控队列。

延伸阅读：ALTER TABLE UPDATE

实时变更

通过 ALTER TABLE ... UPDATE 执行变更时，你可能需要等待后台进程完成变更应用，才能在查询结果中看到变更后的值。 ClickHouse 提供了通过 "实时变更" 改变这一行为的方式。 启用实时变更后，已更新的行会立即被标记为已更新，后续的 SELECT 查询会自动返回变更后的值。

对于 MergeTree 系列表，可通过启用查询级设置 apply_mutations_on_fly 来启用实时变更。

SET apply_mutations_on_fly = 1;

示例

让我们创建一个表，并执行一些变更操作：

CREATE TABLE test_on_fly_mutations (id UInt64, v String)
ENGINE = MergeTree ORDER BY id;

-- 禁用变更的后台物化处理，以展示
-- 未启用实时变更时的默认行为
SYSTEM STOP MERGES test_on_fly_mutations;
SET mutations_sync = 0;

-- 向新表中插入一些行
INSERT INTO test_on_fly_mutations VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- 更新这些行的值
ALTER TABLE test_on_fly_mutations UPDATE v = 'd' WHERE id = 1;
ALTER TABLE test_on_fly_mutations DELETE WHERE v = 'd';
ALTER TABLE test_on_fly_mutations UPDATE v = 'e' WHERE id = 2;
ALTER TABLE test_on_fly_mutations DELETE WHERE v = 'e';

让我们通过 SELECT 查询来检查更新结果：

-- 显式禁用实时变更
SET apply_mutations_on_fly = 0;

SELECT id, v FROM test_on_fly_mutations ORDER BY id;

请注意，在查询这个新表时，这些行的值尚未更新：

┌─id─┬─v─┐
│  1 │ a │
│  2 │ b │
│  3 │ c │
└────┴───┘

现在，让我们看看启用实时变更后会发生什么：

-- 启用实时变更
SET apply_mutations_on_fly = 1;

SELECT id, v FROM test_on_fly_mutations ORDER BY id;

现在，SELECT 查询会立即返回正确结果，无需等待变更实际应用完成：

┌─id─┬─v─┐
│  3 │ c │
└────┴───┘

性能影响

启用实时变更后，变更不会立即物化，而是只会在执行 SELECT 查询时应用。不过请注意，变更仍会在后台异步物化，而这一过程开销较大。

如果在一段时间内，已提交的变更数量持续超过后台处理的变更数量，那么待应用的未物化变更队列就会不断增长。这最终会导致 SELECT 查询性能下降。

我们建议将设置 apply_mutations_on_fly 与其他 MergeTree 级别设置 (如 number_of_mutations_to_throw 和 number_of_mutations_to_delay) 一并启用，以限制未物化变更无限增长。

对子查询和非确定性函数的支持

实时变更对使用子查询和非确定性函数的情况支持有限。仅支持结果大小合理的标量子查询 (由设置 mutations_max_literal_size_to_replace 控制) 。仅支持常量型非确定性函数 (例如 now() 函数) 。

这些行为由以下设置控制：

设置	描述	默认值
mutations_execute_nondeterministic_on_initiator	如果为 true，则会在发起副本上执行非确定性函数，并在 UPDATE 和 DELETE 查询中将其替换为字面量。	false
mutations_execute_subqueries_on_initiator	如果为 true，则会在发起副本上执行标量子查询，并在 UPDATE 和 DELETE 查询中将其替换为字面量。	false
mutations_max_literal_size_to_replace	在 UPDATE 和 DELETE 查询中可替换的序列化字面量的最大大小 (以字节为单位) 。	16384 (16 KiB)

轻量级更新

轻量级更新使用“patch 分区片段”——这些仅包含已更新列和行的特殊数据分区片段——而不是像传统 mutation 那样重写整个列。

UPDATE posts SET AnswerCount = AnswerCount + 1 WHERE Id = 404346

这种方法使用标准的 UPDATE 语法，无需等待合并即可立即创建补丁分区片段。更新后的值通过应用补丁在 SELECT 查询中会立即可见，但只有在后续合并期间才会真正写入存储。这使得轻量级更新非常适合在可预测的延迟下更新较小比例的行（最高约为整张表的 10%）。基准测试表明，其速度最多可比 mutations 快 23 倍。

其代价在于，SELECT 查询在应用补丁时会产生额外开销，并且补丁分区片段会计入分区片段数量上限。超过约 10% 的阈值后，读时打补丁的开销会按比例增长，对于更大规模的更新，同步 mutations 会更高效。

阅读更多：Lightweight UPDATE

实时变更

实时变更提供了一种机制，用于更新行，使得后续的 SELECT 查询无需等待后台处理完成即可自动返回更新后的值，从而有效弥补普通变更在原子性方面的限制。

SET apply_mutations_on_fly = 1;

SELECT ViewCount FROM posts WHERE Id = 404346

┌─ViewCount─┐
│     26762 │
└───────────┘

-- Increment the count
ALTER TABLE posts UPDATE ViewCount = ViewCount + 1 WHERE Id = 404346

-- The updated value is immediately visible
SELECT ViewCount FROM posts WHERE Id = 404346

┌─ViewCount─┐
│     26763 │
└───────────┘

变更操作和后续的 SELECT 查询都需要启用 apply_mutations_on_fly = 1 SETTING。变更条件存储在 ClickHouse Keeper 中 (Keeper 将所有内容保存在内存中) ，并在查询时按需实时应用。

请注意，更新数据依然是通过变更来完成——只是不会立即物化。该变更仍会在后台以异步方式执行，并带来与常规变更相同的较大开销。可与此操作一起使用的表达式也受到限制 (参见详细说明) 。

注意

实时变更应仅用于少量操作——最多可能几十个。Keeper 将条件存储在内存中，因此过度使用会影响集群稳定性。Keeper 负载过高可能导致会话超时，并影响到无关的表。

延伸阅读：实时变更

对比总结

下表基于基准测试总结了查询性能开销。以变更为基线，因为一旦变更完成且数据在物理层面被重写，查询就能以全速运行。

Method	Query slowdown	Memory overhead	Notes
变更	基线	基线	完成后可全速运行；数据在物理层面被重写
实时变更	可变	可变	结果即时可见；若累计大量更新则性能下降
轻量级更新	7–18% (平均约 12%)	+20–210%	对查询最为高效；最适合更新不超过表的 10%
ReplacingMergeTree + FINAL	21–550% (平均约 280%)	为基线的 20–200 倍	必须读取所有行版本；查询开销最大
CoalescingMergeTree + FINAL	与 ReplacingMergeTree 类似	与 ReplacingMergeTree 类似	列级合并带来相近的开销
CollapsingMergeTree	取决于聚合	取决于聚合	开销取决于查询复杂度

更多资源

如果你对 ClickHouse 中更新机制如何随着时间演进，以及相关的基准测试分析感兴趣，请参阅：

• ClickHouse 更新（第 1 部分）：专用引擎
• ClickHouse 更新（第 2 部分）：SQL 风格的更新
• ClickHouse 更新（第 3 部分）：基准测试