更新和删除

作为一个专门用于OLAP数据分析，且目前最快的数据库系统，Clickhouse之前是不支持更新和删除操作的，但迫于需求压力，还是增加了这两种功能。

为了兼顾存储效率和查询性能，Clickhouse为不同的应用场景提供了不同的删除方式。

• Lightweight Deletes，满足异步删除的需求。

• Mutation，满足立即删除和更新较少的需求。

• 基于分区，满足定期删除大量数据的需求。

• 基于TTL机制，满足定期删除的需求。

• 基于CollapsingMergeTree引擎，满足频繁更新或删除单独行的需求。

• 基于ReplacingMergeTree引擎，满足通过版本号实现Upsert的需求。

Clickhouse建议，对于更新整个表来说，创建一个新的列（和移除旧的列）可能是一种更高效的方式。

Lightweight Deletes

所谓Lightweight Deletes，其实就是和传统RDBMS系统类似的用于删除数据表的SQL语句。

:) DELETE FROM table WHERE col1 = 'hello world' AND col2 = 1;

在内部，Clickhouse将数据分为多个Part（片段），每个Part都包含列数据文件和索引。

Clickhouse会不定时merge（合并）和重写这些Part，merge操作考虑了Lightweight Deletes，在新生成的Part中不会包含已标记为被删除的行。

但即使是从搜索结果中排除这些被删除的数据，它们仍保留在磁盘上，直到它们所在的Part被合并为止，而触发合并以及合并所需的时间是不确定的。

Mutation

Clickhouse更改表格中数据的最简单方法是使用ALTER...UPDATE语句。

:) ALTER TABLE table UPDATE col1 = 'hello world' WHERE col2 = 1;

Mutation Deletes操作也是异步的，这种更新表数据的过程被称为Mutations

Clickhouse认为，更新不如删除高效，因为更新涉及到一系列的优化存储和处理，导致相当大的I/O和集群开销，而删除仅需重写一个标记。

但有时需要将数据立即清除，例如某些非法数据，可以使用ALTER...DELETE。

:) ALTER TABLE table DELETE WHERE col2 = 1;

Mutations是异步执行的，可以通过system.mutations表进行监测。

:) ALTER TABLE t_tuple UPDATE tags = ('javabook2', 0) WHERE id = 3;

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

┌─command─────────────────────────────────────┬─is_done─┐
│ UPDATE tags = ('javabook2', 0) WHERE id = 3 │       1 │
└─────────────────────────────────────────────┴─────────┘

is_done只有0和1两个值，0表示操作仍在执行中，否则就是已执行完成。

如果需要立即更新，可将mutations_sync参数设置为1。如果还想等待所有副本也更新完，则可以设置为2。

:) SET mutations_sync = 1;

这个参数也适用于Lightweight Deletes。

Clickhouse不允许不加过滤条件就直接更新整个表，因为这么做代价很大。

Clickhouse推荐创建一个以新值为默认值的新列，然后切换新旧列。

:) ALTER TABLE table ADD COLUMN col1_new String DEFAULT 'hello new world';

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

高效地删除大量数据

如果需要删除大量数据，Clickhouse建议对表进行分区，以便根据分区来删除数据。

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

通过project列对表进行分区，就可以通过删除整个分区来删除所有具有特定project值的行。

:) ALTER TABLE hits DROP PARTITION 'clickhouse';

表的分区可以在system.parts中查到。

:) SELECT partition FROM system.parts WHERE table = 'hits';

Clickhouse强调，表格不应按高基数（如毫秒粒度）的时间列进行分区，以避免产生过多的数据Part影响性能。

定期删除旧数据

Clickhouse提供了TTL功能来定期删除过时的数据。例如，如果想让hits表自动删除一个月以上的数据，就可以这么做。

:) ALTER TABLE hits MODIFY TTL time + INTERVAL 1 MONTH;

TTL还可以用来设置在列上。

:) CREATE TABLE ttl_table
   (
     createtime DateTime,
     prepared Int8 TTL createtime + INTERVAL 1 MONTH,
     deadline Int8 TTL createtime + INTERVAL 2 MONTH,
     name String
   ) ENGINE = MergeTree()
     PARTITION BY toYYYYMM(createtime)
     ORDER BY createtime;

通常，对于大多数TTL操作，按月来进行就足够了。

使用CollapsingMergeTree引擎更新和删除数据

如果需要频繁更新单独的行，Clickhouse推荐使用CollapsingMergeTree表引擎。

例如，有一个统计每篇文章阅读进度的表，要在一行显示每个用户阅读每篇文章的进度，而且当用户阅读文章时，必须实时更新阅读进度。

:) CREATE TABLE article_reads_collapsing
   (
     userid UInt32,
     articleid UInt32,
     readto UInt8,
     readstart DateTime,
     readend DateTime,
     sign Int8
   ) ENGINE = CollapsingMergeTree(sign)
     ORDER BY (readstart, articleid, userid);

这里sign作为一个特殊的列，它的作用是就告知CollapsingMergeTree引擎要更新的特定的行。

• 当在sign列中插入-1时，整行都将被删除。

• 当在sign列中插入1时，Clickhouse将保留该行。

CollapsingMergeTree引擎的机制在1.3 CollapsingMergeTree引擎里已经讨论过了。

使用版本号和ReplacingMergeTree引擎更新和删除数据

在前面的1.7 ReplacingMergeTree引擎已经讲过，ReplacingMergeTree引擎会删除所有参与排序的键值均相同的重复项。

但对于更复杂的情况，可以引入特殊版本（version）列来跟踪应该删除的行。

:) CREATE TABLE article_reads_replacing
   (
     userid UInt32,
     articleid UInt32,
     readto UInt8,
     readtime DateTime,
     version Int32
   ) ENGINE = ReplacingMergeTree(version)
     ORDER BY (articleid, userid);

:) INSERT INTO article_reads_replacing 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_replacing
   WHERE (userid = 1) AND (articleid = 12)
   ORDER BY version DESC
   LIMIT 1;

或使用LIMIT 1 BY来获取最新版本的数据。

:) SELECT * FROM article_reads_replacing
   ORDER BY version DESC
   LIMIT 1 BY (userid, articleid);

总的来说，Clickhouse侧重于读取分析，而非储存和更新。

Clickhouse对于几乎所有的引擎和数据类型都提供了DELETE操作，因为代价相对较小。但却只支持部分引擎和数据类型的更新操作，因为代价较大。

例如，Map类型的字段就无法通过UPDATE操作实现更新。

所以，要尽量发挥它的优势，避免它的不足。

---

感谢支持

更多内容，请移步《超级个体》。