概述

ClickHouse 和 OLTP 数据库中更新数据的差异

在处理更新时，ClickHouse 和 OLTP 数据库由于其底层设计理念和目标用例而差异很大。例如，PostgreSQL 是一种面向行、符合 ACID 标准的关系数据库，它支持强大的事务更新和删除操作，通过多版本并发控制 (MVCC) 等机制确保数据一致性和完整性。这允许在高度并发的环境中进行安全可靠的修改。

相反，ClickHouse 是一种面向列的数据库，针对高读取分析和高吞吐量只追加操作进行了优化。虽然它原生支持原地更新和删除，但必须谨慎使用它们以避免高 I/O。或者，可以将表重构为将删除和更新转换为追加操作，这些操作以异步方式和/或在读取时处理，从而反映了对高吞吐量数据摄取和高效查询性能而非实时数据操作的关注。

更新 ClickHouse 中数据的方法

有几种方法可以更新 ClickHouse 中的数据，每种方法都有其自身的优点和性能特征。您应该根据您的数据模型和您打算更新的数据量选择合适的方法。

对于这两种操作，如果提交的突变数量在一段时间内持续超过后台处理的突变数量，则需要应用的未实现突变的队列将继续增长。这将导致 SELECT 查询性能最终下降。

总而言之，应该谨慎发出更新操作，并使用 system.mutations 表密切跟踪突变队列。不要像在 OLTP 数据库中那样频繁发出更新。如果您需要频繁更新，请参阅 ReplacingMergeTree。

方法	语法	何时使用
更新突变	`ALTER TABLE [table] UPDATE`	当数据必须立即更新到磁盘时使用（例如，为了合规性）。对 `SELECT` 性能产生负面影响。
轻量级更新	`UPDATE [table] SET ... WHERE`	用于更新少量数据（最多 ~10% 的表）。创建补丁部分以立即显示，而无需重写整个列。为 `SELECT` 查询增加开销，但具有可预测的延迟。目前是实验性的。
实时更新	`ALTER TABLE [table] UPDATE`	使用 `SET apply_mutations_on_fly = 1;` 启用。用于更新少量数据。行会在所有后续 `SELECT` 查询中立即返回更新后的数据，但最初仅在磁盘上内部标记为已更新。
ReplacingMergeTree	`ENGINE = ReplacingMergeTree`	用于更新大量数据。此表引擎针对合并时的去重进行了优化。
CollapsingMergeTree	`ENGINE = CollapsingMergeTree(Sign)`	用于频繁更新单个行，或需要维护随时间变化的对象最新状态的场景。例如，跟踪用户活动或文章统计信息。

更新突变

可以通过 ALTER TABLE ... UPDATE 命令发出更新突变，例如：

ALTER TABLE posts_temp
        (UPDATE AnswerCount = AnswerCount + 1 WHERE AnswerCount = 0)

这些操作对 I/O 影响极大，会重写与 WHERE 表达式匹配的所有部分。此过程没有原子性 - 当它们准备就绪时，会替换突变后的部分，并且在突变期间开始执行的 SELECT 查询将看到已经突变的部分以及尚未突变的部分的数据。用户可以通过 systems.mutations 表跟踪进度状态。这些是 I/O 密集型操作，应谨慎使用，因为它们会影响集群 SELECT 性能。

了解更多关于 update mutations 的信息。

轻量级更新

轻量级更新是 ClickHouse 的一项功能，它使用“补丁部分”更新行 - 包含仅更新的列和行的特殊数据部分，而不是像传统突变那样重写整个列。轻量级 UPDATE 关键特性

使用标准的 UPDATE 语法，并在无需等待合并的情况下立即创建补丁部分
更新后的值通过应用补丁立即在 SELECT 查询中可见，但仅在后续合并期间物理实现
专为小更新（最多 ~10% 的表）设计，具有可预测的延迟
为需要应用补丁的 SELECT 查询增加开销，但避免重写整个列

有关更多详细信息，请参阅 “The Lightweight UPDATE Statement”

实时更新

实时更新提供了一种更新行，使其立即更新的机制，后续 SELECT 查询将自动返回更改后的值（这会产生开销并会降低查询速度）。这有效地解决了普通突变的原子性限制。我们下面展示一个例子

SET apply_mutations_on_fly = 1;

SELECT ViewCount
FROM posts
WHERE Id = 404346

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

1 row in set. Elapsed: 0.115 sec. Processed 59.55 million rows, 238.25 MB (517.83 million rows/s., 2.07 GB/s.)
Peak memory usage: 113.65 MiB.

-increment count
ALTER TABLE posts
        (UPDATE ViewCount = ViewCount + 1 WHERE Id = 404346)

SELECT ViewCount
FROM posts
WHERE Id = 404346

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

1 row in set. Elapsed: 0.149 sec. Processed 59.55 million rows, 259.91 MB (399.99 million rows/s., 1.75 GB/s.)

请注意，对于实时更新，仍然使用突变来更新数据；它只是没有立即实现，并在 SELECT 查询期间应用。它仍然会在后台作为异步过程应用，并产生与突变相同的巨大开销，因此是一种应谨慎使用的 I/O 密集型操作。此操作可以使用的表达式也有限制（有关详细信息）。

了解更多关于 on-the-fly updates 的信息。

`CollapsingMergeTree`

源于更新成本高昂但插入可用于执行更新的想法，CollapsingMergeTree 表引擎可以与 sign 列一起使用，作为一种告诉 ClickHouse 通过折叠（删除）具有符号 1 和 -1 的行对来更新特定行的手段。如果为 sign 列插入 -1，则整行将被删除。如果为 sign 列插入 1，ClickHouse 将保留该行。要更新的行是根据在创建表时用于 ORDER BY () 语句中的排序键标识的。

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8 -- A special column used with the CollapsingMergeTree table engine
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1) -- sign = -1 signals to update the state of this row
INSERT INTO UAct VALUES (4324182021466249494, 6, 185, 1) -- the row is replaced with the new state

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0

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

注意

上述更新方法需要用户在客户端维护状态。虽然这从 ClickHouse 的角度来看是最有效的，但它在规模上使用起来可能很复杂。

我们建议阅读 CollapsingMergeTree 的文档，以获得更全面的概述。

ClickHouse 和 OLTP 数据库中更新数据的差异​

更新 ClickHouse 中数据的方法​

更新突变​

轻量级更新​

实时更新​

CollapsingMergeTree​

更多资源​