phpclick局部更新技巧_ClickHouse 中的更新和删除操作

作为环球最快的实时剖析数据库，许多 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 = 2

Mutations利用突变更新数据

在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_doneFROM system.mutationsWHERE 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 列的默认值来指定我们要用作更新值的内容。
这种方法安全且更高效，由于我们在这里跳过了繁重的突变操作。

有时，我们须要基于关系删除或更新行；因此，我们必须联接表。
在 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

现在，我们须要利用 JOIN 在 user_id 和 time 上从 pageviews 表中获取相应的值，并更新 logins.session_id 列：

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 语句在表之间移动分区（例如，如果我们想将数据移动到一个垃圾桶表而不是删除它）。

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

对付韶光序列数据，我们可能希望定期删除过期数据。
ClickHouse 刚好有 TTL 功能来办理这个问题。
这须要配置一个表，并指定我们希望在何时删除哪些数据。
假设我们希望从我们的 hits 表中删除一个月之前的数据：

ALTER TABLE hits MODIFY TTL time + INTERVAL 1 MONTH

在这里，我们哀求 ClickHouse 删除所有 time 列值早于一个月前的行。
TTL 也可以设置在列上，以在一段韶光后将其值重置为默认值。
通过按日期进行分区，并将其舍入到适当的韶光单位（例如天），可以使此操作更有效。
实行 TTL 规则时，ClickHouse 将自动以最高效的办法删除数据。
再次强调，不应将表按高基数的韶光列（例如毫秒级粒度）分区，以避免产生大量分区。
常日按天或月进行分区对付大多数 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 列进行过滤以获取精确的结果：

SELECT article_id, user_id, max(read_end), max(read_to)FROM article_readsWHERE sign = 1GROUP 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 引擎进行版本掌握。
该引擎通过利用一个分外的版本列来实现其他数据库管理系统中所谓的 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_readsWHERE (user_id = 1) AND (article_id = 12)ORDER BY version DESCLIMIT 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_toFROM article_readsORDER BY version DESCLIMIT 1 BY user_id, article_id┌─user_id─┬─article_id─┬─read_to─┐│ 1 │ 12 │ 80 │└─────────┴────────────┴─────────┘

而且，我们无需关心旧版本的移除 - 这由 ClickHouse 在后台自动完成。

在剖析环境中更新和删除数据可能具有寻衅性，并且可能会显著影响数据库性能。
为理解决这个问题，ClickHouse 供应了多种强大的办法来高效地更新和删除数据，以知足不同的需求：