跳至主要内容

轻量级 DELETE 语句

轻量级 DELETE 语句从 [db.]table 表中删除与表达式 expr 匹配的行。它仅适用于 *MergeTree 表引擎系列。

DELETE FROM [db.]table [ON CLUSTER cluster] [IN PARTITION partition_expr] WHERE expr;

它被称为“轻量级 DELETE”是为了与 ALTER TABLE ... DELETE 命令形成对比,后者是一个重量级过程。

示例

-- Deletes all rows from the `hits` table where the `Title` column contains the text `hello`
DELETE FROM hits WHERE Title LIKE '%hello%';

轻量级 DELETE 不会立即删除数据

轻量级 DELETE 实现为 修改,它将行标记为已删除,但不会立即物理删除它们。

默认情况下,DELETE 语句会在标记行已删除后立即返回。如果数据量很大,这可能需要很长时间。或者,您可以使用设置 lightweight_deletes_sync 在后台异步运行它。如果禁用,DELETE 语句将立即返回,但数据在后台修改完成之前仍可能对查询可见。

修改不会物理删除已标记为已删除的行,这将只发生在下次合并期间。因此,在不可预知的时间段内,数据实际上可能没有从存储中删除,而只是被标记为已删除。

如果您需要保证数据在可预测的时间内从存储中删除,请考虑使用表设置 min_age_to_force_merge_seconds。或者您可以使用 ALTER TABLE ... DELETE 命令。请注意,使用 ALTER TABLE ... DELETE 删除数据可能会消耗大量资源,因为它会重新创建所有受影响的部分。

删除大量数据

大量删除可能会对 ClickHouse 性能产生负面影响。如果您要尝试从表中删除所有行,请考虑使用 TRUNCATE TABLE 命令。

如果您预计会频繁删除,请考虑使用 自定义分区键。然后,您可以使用 ALTER TABLE ... DROP PARTITION 命令快速删除与该分区关联的所有行。

轻量级 DELETE 的限制

带有投影的轻量级 DELETE

默认情况下,DELETE 不适用于带有投影的表。这是因为投影中的行可能会受到 DELETE 操作的影响。但有一个 MergeTree 设置 lightweight_mutation_projection_mode 可以更改行为。

使用轻量级 DELETE 时需要考虑的性能问题

使用轻量级 DELETE 语句删除大量数据可能会对 SELECT 查询性能产生负面影响。

以下情况也会对轻量级 DELETE 性能产生负面影响

  • DELETE 查询中的 WHERE 条件很复杂。
  • 如果修改队列中充满了许多其他修改,这可能会导致性能问题,因为表上的所有修改都是按顺序执行的。
  • 受影响的表有大量数据部分。
  • 在紧凑部分中有很多数据。在紧凑部分中,所有列都存储在一个文件中。

删除权限

DELETE 需要 ALTER DELETE 权限。要为给定用户启用特定表的 DELETE 语句,请运行以下命令

GRANT ALTER DELETE ON db.table to username;

轻量级 DELETE 在 ClickHouse 中的内部工作原理

  1. 对受影响的行应用“掩码”

    当执行 DELETE FROM table ... 查询时,ClickHouse 会保存一个掩码,其中每行都被标记为“存在”或“已删除”。这些“已删除”的行在后续查询中会被省略。但是,行实际上只会被后续的合并操作删除。写入此掩码比 ALTER TABLE ... DELETE 查询所做的要轻量级得多。

    掩码实现为一个隐藏的 _row_exists 系统列,它为所有可见行存储 True,为已删除行存储 False。此列仅在部分中存在,如果部分中的一些行被删除。当部分的所有值都等于 True 时,此列不存在。

  2. SELECT 查询将被转换为包含掩码的查询

    当查询中使用掩码列时,SELECT ... FROM table WHERE condition 查询在内部被扩展为 _row_exists 上的谓词,并被转换为

    SELECT ... FROM table PREWHERE _row_exists WHERE condition

    在执行时,会读取 _row_exists 列以确定哪些行不应该被返回。如果有许多已删除的行,ClickHouse 可以确定在读取其他列时哪些颗粒可以完全跳过。

  3. DELETE 查询将被转换为 ALTER TABLE ... UPDATE 查询

    DELETE FROM table WHERE condition 将被转换为 ALTER TABLE table UPDATE _row_exists = 0 WHERE condition 修改。

    在内部,此修改在两个步骤中执行

    1. 对每个单独的部分执行 SELECT count() FROM table WHERE condition 命令以确定部分是否受影响。

    2. 根据上面的命令,然后修改受影响的部分,并为不受影响的部分创建硬链接。在宽部分的情况下,会更新每行的 _row_exists 列,并且所有其他列的文件都是硬链接。对于紧凑部分,所有列都将被重新写入,因为它们都存储在一个文件中。

    从上面的步骤中,我们可以看到使用掩码技术的轻量级 DELETE 比传统的 ALTER TABLE ... DELETE 性能更高,因为它不需要为受影响的部分重新写入所有列的文件。