在 ClickHouse 中处理更新和删除

轻量级删除被公认为：从 ClickHouse 中删除数据的首选和最有效的方式。通过 DELETE FROM table 语法，用户可以指定一个条件，用来删除特定的行，如下所示：

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

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

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

在 22.8 中发布的，截至写作时仍处于实验阶段，轻量级删除有望在接下来的某个版本中成为生产就绪功能。在此之前，使用轻量级删除需要设置 allow_experimental_lightweight_delete=true 的参数。

用户应该意识到，通过依赖正常的后台合并周期，最终行只会从磁盘上被删除。虽然从搜索结果中排除了这些行，但这些行会一直驻留在磁盘上，直到它们的部分（part）被合并。合并发生的时间是不确定的。这有几个含义：

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

最后，请注意，轻量级删除使用到了，我们下面要描述的相同的变异（Mutation）队列和后台线程。我们建议您在（https://clickhouse.com/docs/en/guides/developer/lightweght-delete/#lightweight-delete-internals）查看文档，以获取有关内部实现的更多详细信息。

ClickHouse 表中的数据，使用 ALTER...UPDATE 语句是最简单的用法。

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

此查询将根据给定的过滤条件更新表 table 中的 col1 。

与某些数据库不同，ClickHouse 的 ALTER UPDATE 语句默认是异步的。这意味着更新是在后台进行的，您将不会立即在表上看到更新的效果。这种更新表的过程称为 mutations。

这里需要注意的重要一点是，更新数据是一个重型查询，因为 ClickHouse 必须做很多工作，用来优化存储和处理结果。一个 mutation 操作强制所有包含那些要删除的行的数据部分被重写，当形成新部分时排除目标行。这可能会导致相当大的 I/O 和集群开销，所以使用时要小心，或者考虑下面讨论的其他方法。

像更新一样，删除也可以通过 mutation 进行，并为轻量级删除提供了一种替代方式。在大多数情况下，由于重写所有列的mutation成本，轻量级删除更适合于数据删除。更具体地说，与轻量级删除不同的是：所有列都将被重写，而不仅仅是一个 _row_exists 掩码列。

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

ALTER TABLE table    DELETE WHERE col2 = 3

在此查询中， col2 值为 3 的所有行都会被删除。与其他mutation类似，删除也默认是异步的。可以使用上面描述的相同的 mutations_sync 参数设置来使其同步。

由于mutation是异步运行的，可以通过 system.mutations 表进行监控。这允许用户按其需要：检查表上特定mutation 的进度。

SELECT    command,    is_doneFROM system.mutationsWHERE table = 'tablename'
┌─command───────────────────────────────┬─is_done─┐│ UPDATE col1 = 'Hi' WHERE col2 = 2     │       1 ││ UPDATE col1 = 'All hi' WHERE col2 > 0 │       0 │└───────────────────────────────────────┴─────────┘

如果特定mutation的 is_done 值为 0 ，则仍在执行。突变是为每个表部分执行的，其中mutation部分立即变得可用。

对于需要同步更新的用户，可以将 mutations_sync 参数的值设置为 1 （或者如果我们还想等到所有副本都更新了，就设置为 2 ）：

SET mutations_sync = 1

现在，我们的更新查询将等待mutation 的完成：

ALTER TABLE table    UPDATE col1 = 'bye' WHERE col2 > 0
0 rows in set. Elapsed: 1.182 sec.

请注意，当 ClickHouse 等待后台的mutation完成时，这个查询花费了1秒钟。请注意，此参数也适用于轻量级删除。

在某些情况下，用户需要更新整个列的值。最初，用户可能尝试使用没有 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 接受此操作的一种方法是使用always-true的过滤器：

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 列的默认值，来指定我们要用作更新值的内容。这是安全的，而且效率更高，因为我们在这里跳过了重型的mutation操作。

有时，我们需要基于关系删除或更新行，因此我们必须联接表格。这在 ClickHouse 中最好使用 Join 表引擎和 joinGet 函数来实现。假设我们有两个表格 - 一个是所有的页面查看次数，另一个是所有的登录跟踪：

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

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

ALTER TABLE logins    ADD COLUMN `session_id` UInt64

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

SELECT *FROM logins AS lJOIN 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_joinENGINE = Join(ANY, LEFT, user_id, time) ASSELECT *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 = MergeTreePARTITION BY projectORDER BY (project, path, time)

通过按 project 列分区此表，我们可以通过删除整个分区来删除具有特定 project 值的行。让我们删除 project = c 的所有内容：

ALTER TABLE hits    DROP PARTITION 'c'

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

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

SELECT partitionFROM system.partsWHERE table = 'hits'
┌─partition─┐│ c         ││ a         ││ b         │└───────────┘

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

在您的 DDL 中设置分区时，要注意避免场景的陷阱：按具有高基数的列，或表达式分区。这可能会导致创建许多部分（part），从而导致性能问题。

在时间序列数据的情况下，我们可能想要定期删除过时的旧数据。ClickHouse为这个具体的用例提供了TTL功能。这需要配置一个表，并指定我们想要删除哪些数据，以及何时删除。假设我们想从 hits 表中删除一个月之前的数据：

ALTER TABLE hits    MODIFY TTL time + INTERVAL 1 MONTH

这里我们要求ClickHouse删除所有 time 列值早于当前时间一个月的行。TTL也可以设置在列上，在一段时间后重置其值为默认值。通过按日期分区，四舍五入为适当的时间单位（例如，天），可以使此操作更为高效。当执行TTL规则时，ClickHouse将自动以最有效的方式删除数据。再次强调，表不应该按时间列的高基数（例如，毫秒粒度）进行分区，以避免高产生大量的部分（part）。对于大多数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%时，我们插入以下2行：

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 列上进行过滤以获得正确的结果：