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

谁借莪１个温暖的怀抱￠ 2022-10-08 02:40 407阅读 0赞

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。