在 ClickHouse 中处理更新和删除

作为全球最快的实时分析数据库，许多 ClickHouse 工作负载涉及大量数据，这些数据只写入一次且不常修改（例如，物联网设备生成的遥测事件或电子商务网站生成的客户点击）。虽然这些数据通常是不可变的，但在分析期间提供上下文的其他关键数据集（例如，根据设备或客户 ID 的信息进行的查找表）可能需要修改。

从历史上看，在 ClickHouse 中更新和删除数据有多种方法，具体取决于您的目标和性能要求。本文的其余部分将描述每种方法及其权衡，以及最近解决一些常见挑战的轻量级删除的进展。我们建议最佳实践，并突出显示考虑方法时的一些重要事项。

在继续之前，请确定更新是否为解决问题的最佳方法。例如，对于不常更改的数据，对数据进行版本控制可能是一个更好的选择。ClickHouse 是存储效率和查询性能方面排名第一的分析数据库，因此在许多情况下，仅保存数据的多个版本而不是更新可能效果更好。

轻量级删除 代表从 ClickHouse 中删除数据的首选且最有效的方法。通过 DELETE FROM 表语法，用户可以指定条件以删除特定行，如下所示

DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2

此操作默认情况下是异步的，除非设置 mutations_sync 为 1（见下文）。执行删除操作时，ClickHouse 会为每行保存一个掩码，指示它是否已在 _row_exists 列中删除。后续查询将反过来排除那些已删除的行，如下所示。

在内部，ClickHouse 将数据排序到部分，每个部分包含列数据文件和索引。常规合并循环负责组合（合并）和重写这些部分。这确保了随着更多数据的插入，文件数量不会继续增长，从而保持查询速度。这些合并会考虑轻量级删除，在新建的部分中排除那些标记为删除的行。

轻量级删除已在 22.8 版本中发布，截至撰写本文时，它仍处于实验阶段，预计将在下一个版本中正式发布。在此之前，使用轻量级删除需要设置 allow_experimental_lightweight_delete=true。

用户应注意，通过依赖正常的后台合并循环，行将仅最终从磁盘中删除。虽然已从搜索结果中排除，但这些行将驻留在磁盘上，直到其部分被合并。发生这种情况所花费的时间不是确定性的。这有一些影响

• 节省的空间不会像通过变异发出删除那样立即可见——见下文。如果节省空间至关重要，例如磁盘空间不足，请考虑使用变异。
• 由于无法保证删除，具有合规性要求的用户可能希望使用变异来确保数据被删除。

轻量级删除操作的成本取决于 WHERE 子句中匹配行的数量和当前的数据部分数量。当匹配少量行时，此操作效率最高。用户还应注意，轻量级删除在宽部分上表现最佳，在宽部分中，列数据文件分别存储，而紧凑部分则使用单个文件存储所有列数据。前者允许将掩码 _row_exists 存储为单独的文件，从而允许它独立于其他列进行写入。通常，紧凑部分将在插入后形成。一旦部分超过 一定大小（例如，由于合并），就会使用宽格式。对于大多数工作负载，这应该不是问题。

最后，请注意，轻量级删除使用与我们将在下面描述的相同的变异队列和后台线程。我们建议参考 此处 的文档以了解有关内部实现的更多详细信息。

在 ClickHouse 表中更新数据的最简单方法是使用 ALTER…UPDATE 语句。

ALTER TABLE table
    UPDATE col1 = 'Hi' WHERE col2 = 2

此查询将使用给定的过滤器更新 table 表上的 col1。

与某些数据库不同，ClickHouse 的 ALTER UPDATE 语句默认情况下是异步的。这意味着更新将在后台进行，您将不会对表产生即时影响。此更新表的处理称为 变异。

这里需要注意的是，更新数据是一个繁重的查询，因为 ClickHouse 需要执行大量工作来优化存储和处理。变异操作会强制删除包含那些行的所有数据部分以重新写入，并在形成新部分时将目标行排除在外。这会导致相当大的 I/O 和集群开销，因此请谨慎使用或考虑下面讨论的替代方案。

与更新一样，删除也可以使用变异进行，并为轻量级删除提供替代方案。在大多数情况下，由于重写所有列的变异成本，轻量级删除更适合数据删除。更具体地说，与轻量级删除不同，所有列都被重写，而不是仅重写 _row_exists 掩码列。

但是，鉴于轻量级删除的“最终从磁盘删除数据”属性，用户可能更喜欢这种基于变异的方法来实现保证的磁盘空间节省。此外，当用户需要保证数据从磁盘中删除时（例如，由于合规性原因），这种方法可能是合适的。

ALTER TABLE table
    DELETE WHERE col2 = 3

在此查询中，将删除所有 col2 值为 3 的行。与其他变异一样，删除默认情况下也是异步的。可以使用上面描述的相同 mutations_sync 设置使其同步。

由于变异是异步运行的，因此可以通过 system.mutations 表进行监控。这使用户能够检查表上特定变异的进度。

SELECT
    command,
    is_done
FROM system.mutations
WHERE table = 'tablename'

┌─command───────────────────────────────┬─is_done─┐
│ UPDATE col1 = 'Hi' WHERE col2 = 2     │       1 │
│ UPDATE col1 = 'All hi' WHERE col2 > 0 │       0 │
└───────────────────────────────────────┴─────────┘

如果特定变异的 is_done 值为 0，则它仍在执行中。变异针对每个表部分执行，其中变异部分会立即变得可用

对于需要同步更新的用户，可以将 mutations_sync 参数设置为 1（如果我们还希望等待所有副本更新，则为 2）。

SET mutations_sync = 1

现在我们的更新查询将等待变异完成

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE col2 > 0

0 rows in set. Elapsed: 1.182 sec. 

注意此查询完成耗时 1 秒，而 ClickHouse 在后台等待变异完成。请注意，此参数也适用于轻量级删除。

在某些情况下，用户需要更新整列的值。最初，用户可能会尝试使用不带 `WHERE` 子句的 `ALTER TABLE` 查询来实现此目的。但是，这将失败，如下所示

ALTER TABLE table UPDATE col1 = 'bye';

Syntax error: failed at position 38 (end of query):
ALTER TABLE table UPDATE col1 = 'bye';

ClickHouse 不会让你更新整个表，因为更新很重。一种强制 ClickHouse 接受此操作的方法是使用始终为真的过滤器

ALTER TABLE table
    UPDATE col1 = 'bye' WHERE true

但是，更优化的方式是创建一个新的列，并将新值作为默认值，然后交换旧列和新列。例如

ALTER TABLE table ADD COLUMN col1_new String DEFAULT 'global hi';

ALTER TABLE table
    RENAME COLUMN col1 TO col1_old,
    RENAME COLUMN col1_new TO col1,
    DROP COLUMN col1_old;

我们使用 `col1_new` 列的默认值来指定我们想用作更新值的內容。这很安全，而且效率更高，因为我们跳过了这里繁重的变异操作。

有时，我们需要根据关系删除或更新行；因此，我们必须联接表。这在 ClickHouse 中使用 Join 表引擎 和 joinGet 函数来实现。假设我们有两个表 - 一个包含所有页面浏览量，另一个包含所有登录跟踪

CREATE TABLE pageviews
(
    `user_id` UInt64,
    `time` DateTime,
    `session_id` UInt64
)
ENGINE = MergeTree
ORDER BY time;

CREATE TABLE logins
(
    `user_id` UInt64,
    `time` DateTime
)
ENGINE = MergeTree
ORDER BY time;

这两个表之间的区别在于 `logins` 表只存储每个会话的一个事件。假设在某个时间点，我们决定在 `logins` 表中添加 `session_id` 列

ALTER TABLE logins
    ADD COLUMN `session_id` UInt64

现在，我们需要使用 `user_id` 和 `time` 进行 JOIN，用 `pageviews` 表中的相应值更新 `logins.session_id` 列

SELECT *
FROM logins AS l
JOIN pageviews AS p ON (p.user_id = l.user_id) AND (p.time = l.time)

┌─user_id─┬────────────────time─┬─p.user_id─┬──────────────p.time─┬─session_id─┐
│       2 │ 2023-01-09 12:23:16 │         2 │ 2023-01-09 12:23:16 │ 2752888102 │
│       1 │ 2023-01-09 13:23:16 │         1 │ 2023-01-09 13:23:16 │ 4135462640 │
└─────────┴─────────────────────┴───────────┴─────────────────────┴────────────┘

首先，我们需要创建并填充一个特殊的 `Join` 表

CREATE TABLE pageviews_join
ENGINE = Join(ANY, LEFT, user_id, time) AS
SELECT *
FROM pageviews

此表将允许我们在执行更新查询时使用 `joinGet` 函数根据 JOIN 获取值

ALTER TABLE logins
    UPDATE session_id = joinGet('pageviews_join', 'session_id', user_id, time) WHERE session_id = 0

我们可以看到，`logins` 表已根据 JOIN 进行了相应更新

SELECT * FROM logins

┌─user_id─┬────────────────time─┬─session_id─┐
│       2 │ 2023-01-09 12:23:16 │ 2752888102 │
│       1 │ 2023-01-09 13:23:16 │ 4135462640 │
└─────────┴─────────────────────┴────────────┘

因为我们通过添加 `session_id` 列修改了 `logins` 表，所以我们可以在更改完成后删除 `pageviews_join` 表（在删除之前，请确保检查 `system.mutations` 表）

DROP TABLE pageviews_join

相同的方法可用于使用轻量级或变异删除来删除数据。

如果我们必须删除大量数据，用户可以对表进行分区，以便可以根据需要删除分区。这是一个轻量级操作。假设我们有以下表

CREATE TABLE hits
(
    `project` String,
    `url` String,
    `time` DateTime,
    `hits` UInt32
)
ENGINE = MergeTree
PARTITION BY project
ORDER BY (project, path, time)

通过 `project` 列对该表进行分区，我们可以通过删除整个分区来删除具有特定 `project` 值的行。让我们删除所有 `project` = `c` 的內容

ALTER TABLE hits
    DROP PARTITION 'c'

这里，`c` 是我们要删除的 `project` 列值

可以在 `system.parts` 表中找到可用分区的列表

SELECT partition
FROM system.parts
WHERE table = 'hits'

┌─partition─┐
│ c         │
│ a         │
│ b         │
└───────────┘

我们还可以使用 DETACH 和 ATTACH 语句在表之间移动分区（例如，如果我们想将数据移动到 `trash` 表而不是删除它）。

在 DDL 中设置分区时，请注意通过具有高基数的列或表达式进行分区的常见陷阱。这会导致创建许多部分，从而导致性能问题。

在 时间序列数据 的情况下，我们可能希望定期删除过时数据。ClickHouse 具有 TTL 功能，专门用于此用例。这需要配置一个表并指定我们想要删除哪些数据以及何时删除。假设我们希望从 `hits` 表中删除一个月以上的数据

ALTER TABLE hits
    MODIFY TTL time + INTERVAL 1 MONTH

这里，我们要求 ClickHouse 删除所有 `time` 列值比当前时间早一个月以上的所有行。TTL 也可以 设置在列上，以便在一段时间后将其值重置为默认值。通过按日期（四舍五入到适当的时间单位，例如天）进行分区，可以提高此操作的效率。ClickHouse 将在执行 TTL 规则时以最有效的方式自动删除数据。同样，表 **不应** 按具有高基数的时间列（例如毫秒粒度）进行分区，以避免出现高分区数。通常，按天或月进行分区足以满足大多数 TTL 操作。

如果我们必须频繁更新单个行，我们可以使用 CollapsingMergeTree 引擎来有效地管理数据更新。

假设我们有一个包含文章统计信息表，用于跟踪每篇文章的阅读深度。我们希望有一行显示每个用户阅读每篇文章的深度。这里的挑战是我们必须在用户阅读文章时更新实际的阅读进度。让我们为我们的数据创建一个表

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_start` DateTime,
    `read_end` DateTime,
    `sign` Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY (read_start, article_id, user_id)

特殊的 `sign` 列用于 CollapsingMergeTree，作为一种方式告诉 ClickHouse 我们希望更新特定行。如果我们为 `sign` 列插入 `-1`，则整行将被删除。如果我们插入一行，`sign = 1`，ClickHouse 将保留该行。要更新的行是根据创建表时 `ORDER BY ()` DDL 语句中使用的排序键识别的

为了满足排序键上的重复数据删除条件，我们必须为 `read_start, article_id, user_id` 列插入相同的值以更新行。例如，当用户开始阅读文章时，我们会插入以下行

INSERT INTO article_reads
            VALUES(1, 12, 0, now(), now(), 1);

现在，表中只有一行

SELECT *
FROM article_reads

┌─user_id─┬─article_id─┬─read_to─┬──────────read_start─┬────────────read_end─┬─sign─┐
│       1 │         12 │       0 │ 2023-01-06 15:20:32 │ 2023-01-06 15:20:32 │    1 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────────────────┴──────┘

一分钟后，当用户到达我们文章的 70% 时，我们会插入以下两行

INSERT INTO article_reads
            VALUES(1, 12, 0, '2023-01-06 15:20:32', now(), -1),
                  (1, 12, 70, '2023-01-06 15:20:32', now(), 1);

第一行 `sign=-1` 用于告诉 ClickHouse 它应该删除现有行（基于 `ORDER BY` 元组中的值 - `read_start`、`article_id` 和 `user_id` 列）。第二行（`sign=1`）是新的行，其中 `read_to` 列设置为新的值 `70`。

由于数据更新 在后台进行，并且结果最终一致，因此我们应该筛选 `sign` 列以获得正确的结果

SELECT
    article_id,
    user_id,
    max(read_end),
    max(read_to)
FROM article_reads
WHERE sign = 1
GROUP BY
    user_id,
    article_id

┌─article_id─┬─user_id─┬───────max(read_end)─┬─max(read_to)─┐
│         12 │       1 │ 2023-01-06 15:21:59 │           70 │
└────────────┴─────────┴─────────────────────┴──────────────┘

1 row in set. Elapsed: 0.004 sec.

现在，`CollapsingMergreTree` 引擎将负责在后台有效地从存储中删除已取消的行，因此我们不必手动删除它们。您可以找到使用 CollapsingMergeTree 引擎的更多示例 此处.

对于更复杂的情况，我们可能希望使用基于 ReplacingMergeTree 引擎的版本控制。该引擎实现了一种高效的方式来执行其他 DBMS 中的 `UPSERT`，它使用特殊的版本列来跟踪应删除哪些行。如果存在具有相同排序键的多行，则只有版本最大的那一行保留在存储中，而其他行则被删除

对于我们之前有关文章阅读的示例，我们可以使用以下结构

CREATE TABLE article_reads
(
    `user_id` UInt32,
    `article_id` UInt32,
    `read_to` UInt8,
    `read_time` DateTime,
    `version` Int32
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (article_id, user_id)

请注意特殊的 `version` 数字列，它将由 ReplacingMergeTree 引擎用来标记要删除的行。让我们模拟用户从 0 到 80% 阅读文章的时间

INSERT INTO article_reads
           VALUES(1, 12, 0, '2023-01-06 15:20:32', 1),
                 (1, 12, 30, '2023-01-06 15:21:42', 2),
                 (1, 12, 45, '2023-01-06 15:22:13', 3),
                 (1, 12, 80, '2023-01-06 15:23:10', 4);

这里，我们随着阅读进度的跟踪而增加了 `version` 列的值。行删除过程也在后台通过正常的合并周期执行，因此我们需要根据查询时的最新版本进行筛选

SELECT *
FROM article_reads
WHERE (user_id = 1) AND (article_id = 12)
ORDER BY version DESC
LIMIT 1

┌─user_id─┬─article_id─┬─read_to─┬───────────read_time─┬─version─┐
│       1 │         12 │      80 │ 2023-01-06 15:23:10 │       5 │
└─────────┴────────────┴─────────┴─────────────────────┴─────────┘

或者，我们可以使用 LIMIT 1 BY 获取具有最新版本的行列表

SELECT
    user_id,
    article_id,
    read_to
FROM article_reads
ORDER BY version DESC
LIMIT 1 BY
    user_id,
    article_id

┌─user_id─┬─article_id─┬─read_to─┐
│       1 │         12 │      80 │
└─────────┴────────────┴─────────┘

同样，我们不必关心旧版本的删除 - 这由 ClickHouse 在后台自动完成。

在分析环境中更新和删除数据可能具有挑战性，并会严重影响数据库性能。为了解决这个问题，ClickHouse 提供了多种强大的方法来有效地更新和删除不同情况下的数据

• 通过 `DELETE FROM` 语法进行轻量级删除，用于从 ClickHouse 中删除数据。这是删除数据的最有效方法，前提是无需立即节省磁盘空间，并且用户可以容忍已删除的数据“存在”于磁盘上。
• 在需要立即节省磁盘空间的情况下，通过 `ALTER…DELETE` 进行基于变异的删除。例如，合规性要求需要围绕从磁盘删除数据的保证。
• 在不规则和不频繁更改的情况下，使用 `ALTER…UPDATE` 进行基于变异的更新
• 使用 TTL 定期删除（过时）数据，具体取决于日期/时间。
• 使用 CollapsingMergeTree 频繁更新或删除单个行。
• 使用 ReplacingMergeTree 实现基于版本控制的 Upsert（插入/更新）。
• 定期删除大量数据时，删除分区。
• 创建新列（并删除旧列）也可能是更新整个表的更有效方法。