跳到主要内容
跳到主要内容

概述

ClickHouse与OLTP数据库中更新数据的区别​

在处理更新时，ClickHouse与OLTP数据库在其基础设计理念和目标使用案例上有显著区别。例如，PostgreSQL作为一种面向行的、符合ACID的关系型数据库，支持强大且事务性的更新和删除操作，通过多版本并发控制（MVCC）等机制确保数据的一致性和完整性。这允许在高并发环境中进行安全可靠的修改。

相反，ClickHouse是一个面向列的数据库，针对重读分析和高吞吐量追加操作进行了优化。尽管它确实原生支持原地更新和删除，但需要谨慎使用，以避免高I/O。或者，可以对表进行重构，将删除和更新转换为追加操作，在此过程中以异步方式处理，并/或在读取时处理，从而反映出对高吞吐量数据摄取和有效查询性能的重点，而不是实时数据操作。

在ClickHouse中更新数据的方法​

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

对于这两种操作，如果提交的变更数量持续超过在某个时间区间内后台处理的变更数量，则必须应用的非物化变更队列将继续增长。这将导致最终的 SELECT 查询性能下降。

总之，更新操作应谨慎发出，并且应通过 system.mutations 表密切跟踪变更队列。不要像在OLTP数据库中那样频繁发布更新。如果您有频繁更新的需求，查看 ReplacingMergeTree。

方法语法使用时机
Update mutationALTER TABLE [table] UPDATE当数据必须立即更新到磁盘时使用（例如，为了合规性）。对SELECT性能有负面影响。
Lightweight updatesUPDATE [table] SET ... WHERE用于更新少量数据（最多约10% 的表）。创建补丁部分以实现即时可见性，而无需重写整个列。虽然对SELECT查询增加了额外开销，但具有可预测的延迟。目前实验性。
On-the-fly updatesALTER TABLE [table] UPDATE启用SET apply_mutations_on_fly = 1;。在更新少量数据时使用。行会立即在随后的SELECT查询中返回更新后的数据，但最初仅在磁盘上被内部标记为已更新。
ReplacingMergeTreeENGINE = ReplacingMergeTree当更新大量数据时使用。此表引擎针对合并时的数据去重进行了优化。
CollapsingMergeTreeENGINE = 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 语法，并立即创建补丁部分，而不必等待合并
  • 更新的值在通过补丁应用时在 SELECT 查询中立即可见，但在随后的合并中才会物理化
  • 为小规模更新（最多约10% 的表）设计，具有可预测的延迟
  • 对需要应用补丁的 SELECT 查询增加了额外开销，但避免重写整个列

详细信息请参见 "轻量级更新语句"。

即时更新​

即时更新提供了一种机制，用于更新行，使其立即更新，随后的 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密集型操作。此操作中可以使用的表达式也有限（有关详细信息，请参见 这里）。

了解更多关于 即时更新。

CollapsingMergeTree​

基于这样的理念：更新成本高昂，但插入可以利用来执行更新，CollapsingMergeTree 表引擎可以和一个 sign 列一起使用，以告知ClickHouse通过合并（删除）一对带有 1 和 -1 的行来更新特定行。 如果 -1 被插入到 sign 列中，整行将被删除。 如果 1 被插入到 sign 列中，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 的文档，以获得更全面的概述。

更多资源​