ClickHouse 中的更新

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

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

在 ClickHouse 中，更新数据主要有两种基本方式：

1. 使用专用表引擎，通过插入操作完成更新
2. 使用声明式更新，例如 UPDATE ... SET 或 ALTER TABLE ... UPDATE 语句

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

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

你最常遇到的引擎有：

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

对于无需处理去重逻辑复杂性的简单更新操作，声明式 UPDATE 语句通常更直观；但与专用引擎相比，它们整体上更适合以较低频率更新较少数量的行。

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

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

该引擎非常适合按稳定键识别、对单行进行高频更新的场景。 基准测试表明，对于单行更新，它的速度最高可比变更快 4,700 倍。

要更新一行，只需插入一个具有相同 排序键 值且 version number 更高的新版本。旧版本会在后台合并期间被移除。由于去重最终才会完成 (仅在合并期间发生) ，因此你应使用 FINAL modifier 或等效的查询逻辑，以获得正确的去重结果。FINAL modifier 会增加查询开销，具体增加 21% 到 550%，取决于数据情况。

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

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

CoalescingMergeTree 会在合并过程中为每一列保留最新的非 NULL 值，从而整合稀疏记录。这样就能实现列级 upsert，而不是整行替换。

CREATE TABLE electric_vehicle_state
(
    vin String, -- 车辆识别码
    last_update DateTime64 Materialized now64(), -- 可选（与 argMax 配合使用）
    battery_level Nullable(UInt8), -- 单位：%
    lat Nullable(Float64), -- 纬度（°）
    lon Nullable(Float64), -- 经度（°）
    firmware_version Nullable(String),
    cabin_temperature Nullable(Float32), -- 单位：°C
    speed_kmh Nullable(Float32) -- 来自传感器
)
ENGINE = CoalescingMergeTree
ORDER BY vin;

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

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

该引擎自 ClickHouse 25.6 起可用。

阅读更多：CoalescingMergeTree

基于这样一种思路：更新的代价较高，但可以借助 insert 来实现更新，CollapsingMergeTree 使用 Sign 列来告诉 ClickHouse 在 merge 过程中如何处理各行。如果向 sign 列插入 -1，那么当该行与对应的 +1 行配对时，就会被折叠 (删除) 。需要更新的行，是根据创建表时 ORDER BY 子句中使用的 排序键 来识别的。

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

-- 初始状态
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, 1)

-- 取消旧行并插入新状态
INSERT INTO user_activity VALUES (4324182021466249494, 5, 146, -1)
INSERT INTO user_activity VALUES (4324182021466249494, 6, 185, 1)

-- 使用正确的聚合方式进行查询
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 允许你修改排序键的值。它非常适合带有抵消语义的可逆操作，例如金融交易或游戏状态追踪。

阅读更多：CollapsingMergeTree

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

变更 (ALTER TABLE ... UPDATE) 会重写所有包含符合 WHERE 表达式的行的 parts。

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

变更是 I/O 密集型操作，会重写所有匹配 WHERE expression 的 parts。 这一过程不具备原子性。 parts 会在变更后的 parts 准备就绪后立即被替换，而在变更过程中开始执行的 SELECT 查询，会同时看到已完成变更的 parts 中的数据和仍未变更的 parts 中的数据。 你可以通过 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';

-- 显式禁用即时变更
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;

┌─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() 函数) 。

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

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

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

这种方法使用标准的 UPDATE 语法，会立即创建补丁分区片段，而无需等待合并。更新后的值会通过补丁应用立即反映在 SELECT 查询结果中，但只有在后续合并时才会被实际物化到存储中。这使得轻量级更新非常适合更新少量行 (约占整张表的 10% 以内) ，并且能够提供可预测的延迟。基准测试表明，其速度最高可比变更快 23 倍。

代价是，SELECT 查询在应用补丁时会产生额外开销，而且补丁分区片段也会占用 parts 数量限制。超过约 10% 这一阈值后，读取时应用补丁的开销会按比例增长，因此对于更大规模的更新，同步变更会更高效。

更多信息：轻量级 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 设置。变更条件存储在 ClickHouse Keeper 中；它将所有内容保存在内存中，并在查询期间即时应用。

请注意，更新数据仍然是通过变更完成的——只是不会立即物化。变更仍会作为异步过程在后台应用，并产生与常规变更相同的高额开销。此操作可使用的表达式也有一定限制 (请参见详情) 。

阅读更多：即时变更

下表基于基准测试总结了查询性能开销。由于变更完成后数据会被物理重写，届时查询可恢复全速运行，因此以变更作为基线。

如果你想深入了解 ClickHouse 中的更新功能如何逐步演进，以及相关的基准测试分析，请参阅：

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