作为全球最快的实时分析数据库，许多 ClickHouse 工作负载涉及大量数据，这些数据仅写入一次，不经常修改（例如，由物联网设备生成的遥测事件或电子商务网站生成的客户点击）。尽管这些数据通常是不可变的，但在分析过程中提供上下文的其他关键数据集（例如，基于设备或客户 ID 的信息的查找表）可能需要修改。
在 ClickHouse 中，历史上有多种更新和删除数据的方法，具体取决于您的目标和性能要求。本文将描述每种方法及其权衡，以及一些最新的轻量级删除技术，这些技术解决了一些常见挑战。我们将推荐最佳实践，并强调在选择方法时需要考虑的一些重要因素。
在继续之前，请确定更新是否是解决您问题的最佳方式。例如，对于不经常更改的数据，版本化数据可能是更好的选择。ClickHouse 在存储效率和查询性能方面处于领先地位，因此在许多情况下，仅保存多个数据版本而不是进行更新可能效果更好。
轻量级删除
轻量级删除是从ClickHouse中删除数据的首选方式。通过DELETE FROM table语法，用户可以指定条件删除特定行。这个操作默认是异步的，除非将mutations_sync设置为1。执行删除时，ClickHouse会为每一行保存一个掩码，指示其是否被删除。然后，查询时会排除这些已被删除的行。
在内部，ClickHouse将数据排序为多个部分，每个部分包含列数据文件和索引。定期的合并周期负责合并和重写这些部分，以确保查询速度。这些合并过程会考虑轻量级删除，排除那些被标记为删除的行。
轻量级删除是从ClickHouse 22.8版本开始引入的，目前仍处于实验阶段。直到轻量级删除功能正式发布前，需要将
allow_experimental_lightweight_delete设置为true。
需要注意的是，通过正常的后台合并周期，行只会最终从磁盘中删除。这意味着，尽管在搜索结果中排除了这些行，但它们仍会保留在磁盘上，直到它们所在的部分被合并。对于空间节省至关重要的情况，可能需要考虑使用mutations。
DELETE FROM table WHERE col1 = 'Hi' AND col2 = 2Mutations
使用突变更新数据
在ClickHouse表中更新数据的最简单方法是使用ALTER...UPDATE语句。
ALTER TABLE tableUPDATE 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 tableUPDATE col1 = 'bye' WHERE col2 0注意，这个查询花费了 1.182 秒完成，因为 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 列的默认值来指定我们要用作更新值的内容。这种方法安全且更高效，因为我们在这里跳过了繁重的突变操作。
使用 JOIN 更新和删除
有时，我们需要基于关系删除或更新行；因此，我们必须联接表。在 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现在，我们需要使用 JOIN 在 user_id 和 time 上从 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 语句在表之间移动分区（例如，如果我们想将数据移动到一个垃圾桶表而不是删除它）。
在设置 DDL 中的分区时，请注意常见陷阱，即通过具有高基数的列或表达式进行分区。这可能会导致创建许多分区，从而引起性能问题。
定期删除旧数据
对于时间序列数据，我们可能希望定期删除过期数据。ClickHouse 刚好有 TTL 功能来解决这个问题。这需要配置一个表，并指定我们希望在何时删除哪些数据。假设我们希望从我们的 hits 表中删除一个月之前的数据：
ALTER TABLE hits
MODIFY TTL time + INTERVAL 1 MONTH在这里，我们要求 ClickHouse 删除所有 time 列值早于一个月前的行。TTL 也可以设置在列上，以在一段时间后将其值重置为默认值。通过按日期进行分区，并将其舍入到适当的时间单位（例如天），可以使此操作更有效。执行 TTL 规则时，ClickHouse 将自动以最高效的方式删除数据。再次强调，不应将表按高基数的时间列（例如毫秒级粒度）分区，以避免产生大量分区。通常按天或月进行分区对于大多数 TTL 操作来说是足够的。
使用 CollapsingMergeTree 删除和更新
如果我们需要频繁更新单个行，可以使用 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 列进行过滤以获取正确的结果：
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 进行更新插入
对于更复杂的情况，我们可能希望基于 ReplacingMergeTree 引擎进行版本控制。该引擎通过使用一个特殊的版本列来实现其他数据库管理系统中所谓的 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（Time-To-Live）：定期删除（过时的）数据，根据日期/时间进行操作。
- 使用 CollapsingMergeTree：频繁地更新或删除单个行。
- 使用 ReplacingMergeTree：实现基于版本控制的插入/更新（upsert）操作。
- 删除分区：定期删除大块数据时，通过删除分区来实现。
- 创建新列：创建新列（并删除旧列）可能也是更新整个表的更有效方式。