ClickHouse系列--Mutations操作：数据的删除和修改

clickhouse提供了update和delete的删除能力，但是和常规的例如mysql，redis这种立即见效的能力不一样。在clickhouse中这种操作称为mutation操作。

1.mutation操作有3个特点：

• 1.Mutations是一类允许对表的行记录进行删除或更新的ALTER操作。相较于标准的 UPDATE 和 DELETE 用于少量行操作而言，Mutations用来对表的很多行进行重量级的操作。 该操作支持 MergeTree系列表，包含支持复制功能的表。
• 2.在首次对表进行mutation操作以后，它的元数据格式变得和和之前的版本不兼容，并且不能回退到之前版本。
• 3.对于 *MergeTree引擎表，mutation操作通过重写整个数据块来实现。
• 4.该操作没有原子性保证：被mutation操作的数据会被替换，在mutation期间开始执行的SELECT查询能看到所有已经完成mutation的数据，以及还没有被mutation替换的数据。 所以这时数据的查询操作可能会拿到预料之外的结果。
• 5.mutation总是按照它们的创建顺序来排序并以同样顺序在每个数据块中执行。
• 6.和insert的关系：mutation操作也会部分的和Insert操作一起排序 - 在mutation提交之前插入的数据会参与mutation操作，在mutation提交之后的插入的数据则不会参与mutation。注意：mutation从来不会阻塞插入操作。
• 7.该操作是异步操作，在提交后立即返回。已经成功提交的mutation操作在服务重启后仍会继续执行。一旦mutation完成提交，就不能回退了，但是如果因为某种原因操作被卡住了，可以通过 KILL MUTATION操作来取消它的执行。
• 8.要跟踪mutation的进度，可以使用系统表 system.mutations。
• 9.已完成的mutations记录不会立即删除（要保留的记录数量由 finished_mutations_to_keep 这一参数决定）。之前的mutation记录会被删除。

命令

ALTER TABLE [db.]table DELETE WHERE filter_expr

2.示例

ALTER table test delete where id = 111;
ALTER table test update name = 'aa',age = 18 where id IN (SELECT id FROM test where eventTime = '2020-02-22');

3.过程

当执行了update或者delete操作后，数据的存储目录会发生变化。每一个原有的数据存储目录都会新增一个同名目录，这个同名目录后面会加上一个后缀，并且会多出一个txt文件。 例如： 202102_1_1_0目录会多出一个202102_1_1_0_2目录； 多个一个mutation_2.txt文件。

这个txt文件是一个日志文件，记录了update或者delete操作的执行语句和时间。

以数据删除为例：数据的删除过程，是以数据表的每个分区目录为单位，将所有目录重写为新的目录。数据在重写的过程中会将需要删除的数据移除掉。旧的数据目录并不会立即删除，而是会被标记为非激活状态（active为0）。当到MergeTree引擎下一次合并动作触发时，这些非激活目录才会被真正的物理删除。

因此，删除和更新操作，是一个很重的操作。不适合单条处理。

4.实践

亲测一条sql更新400万条记录中的一个字段时，数据库会崩溃。在后续超过2小时的时间，数据库访问都是超时，偶尔可以执行最简单的sql。没办法，只能把表删除了重建。

原因：

执行了批量更新字段 — 这是作死的操作

ALTER table java4all.atable on cluster ck update sampleTag = ‘white’ where sequenceId IN (

SELECT raba.sequenceId FROM java4all.atable raba where raba.partnerCode = ‘demo’ and raba.appName = ‘autoTest’

);

sql效果：更新400万条数据，每条数据更新一个字段值。

后果：ck集群在3小时内，无法响应请求，sql执行都是time out。

查询：SELECT * FROM system.mutations where database = ‘forseti’;时is_done一直是0；三小时内查询都是这样子。

删除： KILL MUTATION where database = ‘forseti’; 执行此操作，试图kill掉此次mutation操作。

结果：kill掉后，查看日志，发现依旧再刷错误日志。

删除表：直接把表删除。

结果：还是持续报错，此时一直报找不到分区parts，merge parts出错。意味着，尽管kill 了mutation和删了表，后台还在持续去执行mutation操作。

集群情况：双副本都挂掉了。

重启解决。

结论：彻底禁用update操作！！！