南山区做网站公司,p2p网站审批,适合30岁短期培训班,wordpress目的OLAP 数据库设计的宗旨在于分析适合一次插入多次查询的业务场景#xff0c;市面上成熟的 AP 数据库在更新和删除操作上支持的均不是很好#xff0c;当然 clickhouse 也不例外。但是不友好不代表不支持#xff0c;本文主要介绍在 clickhouse 中如何实现数据的删除#xff0c…OLAP 数据库设计的宗旨在于分析适合一次插入多次查询的业务场景市面上成熟的 AP 数据库在更新和删除操作上支持的均不是很好当然 clickhouse 也不例外。但是不友好不代表不支持本文主要介绍在 clickhouse 中如何实现数据的删除以及最新版本中 clickhouse 所做的一些技术突破 一、mutation
刚接触 clickhouse 的小伙伴或许对 mutation 就很熟悉了mutation 查询可以看成 alter 语句的变种。虽然 mutation 能够最终实现修改和删除的需求但不能完全用通常意义的 delete 和 update 来理解我们需要清醒的认识到它的不同
mutation 是一个很重的操作适合批量数据操作不支持事务、一旦操作立刻生效无法回滚mutation 为异步操作
1.1 实操
创建一张表用于测试 mutation 操作
create table mutations_operate
(UserId UInt64,Score UInt64,CreateTime DateTime
) engine MergeTree()partition by toYYYYMMDD(CreateTime)order by UserId;接下来分别插入两批不同分区的数据
insert into mutations_operate
select number,abs(number - 100),2023-08-08 00:00:00
from system.numbers
limit 1000000;insert into mutations_operate
select number,abs(number - 100),2023-08-09 00:00:00
from system.numbers
limit 1000000;尝试删除 20230808 分区中 1000-10000 之间的所有数据sql 如下
alter table mutations_operate delete where toYYYYMMDD(CreateTime) 20230808 and UserId between 1000 and 10000;可以统计一下该分区的数据条数来确认是否成功删除从体验来说目前的数据规模感受不到 mutation 的“重”感觉像是瞬间完成的。
当然我们也可以查看system.mutations表来监控 mutation 操作的进度
select table, mutation_id, block_numbers.number as num, is_done
from system.mutations;Query id: 0878a0f1-a5ff-474c-8f84-518ce5dc5e1d┌─table─────────────┬─mutation_id────┬─num─┬─is_done─┐
│ mutations_operate │ mutation_3.txt │ [3] │ 1 │
└───────────────────┴────────────────┴─────┴─────────┘1 row in set. Elapsed: 0.002 sec.mutation_id 是一个日志文件可以在表存储目录中查看完整记录了本次操作的语句和时间例如
format version: 1
create time: 2023-08-09 18:54:06
commands: DELETE WHERE (toYYYYMMDD(CreateTime) 20230808) AND ((UserId 1000) AND (UserId 10000))而其中的 3 以及block_numbers.number是 mutation 号每执行一条 delete 或 update 语句都会对应一个唯一的编号
id_done 表示本次 mutation 操作是否执行完成1 表示已经完成
1.2 原理
为了探寻 mutation 操作的原理和执行流程重置一下表数据删除重建即可在插入两批数据后查看磁盘目录
» du -h0B ./detached
7.7M ./20230809_2_2_0
7.7M ./20230808_1_1_015M .可以看到两个分区目录均是 7.7M
尝试执行删除操作后可以在日志中看到下面的查询信息
Debug delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Key condition: unknown, (column 0 in [1000, Inf)), (column 0 in (-Inf, 10000]), and, and
Debug delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Key condition: unknown, (column 0 in [1000, Inf)), (column 0 in (-Inf, 10000]), and, and
Debug delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): MinMax index condition: (toYYYYMMDD(column 0) in [20230808, 20230808]), unknown, unknown, and, and
Debug delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): MinMax index condition: (toYYYYMMDD(column 0) in [20230808, 20230808]), unknown, unknown, and, and
Trace delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Running binary search on index range for part 20230808_1_1_0 (124 marks)
Trace delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Found (LEFT) boundary mark: 0
Debug delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Selected 0/1 parts by partition key, 0 parts by primary key, 0/0 marks by primary key, 0 marks to read from 0 ranges
Trace delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Found (RIGHT) boundary mark: 2
Trace delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Found continuous range in 13 steps
Debug delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Selected 1/1 parts by partition key, 1 parts by primary key, 2/123 marks by primary key, 2 marks to read from 1 ranges
Trace delete_operate.mutations_operate (7f120927-b71e-4f85-a06c-21a94b7f89e3) (SelectExecutor): Spreading mark ranges among streams (default reading)
Trace MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part 20230808_1_1_0, approx. 16384 rows starting from 0
Trace Aggregator: Aggregation method: without_key
Trace AggregatingTransform: Aggregated. 0 to 1 rows (from 0.00 B) in 0.000570041 sec. (0.000 rows/sec., 0.00 B/sec.)
Trace Aggregator: Merging aggregated data
Trace MutateTask: Part 20230809_2_2_0 doesnt change up to mutation version 3首先clickhouse 会使用我们执行的删除语句中附带的 where 条件在每个分区中执行 count 查询为了判断哪些分区有需要被删除的数据从日志可以看出Reading 1 ranges in order from part 20230808_1_1_0, approx. 16384 rows starting from 0以及Part 20230809_2_2_0 doesnt change up to mutation version 3。注意日志中所说 20230808 的范围是 016384 并不是实际删除的范围而是索引的范围。我们知道 mergeTree 引擎默认的跳数索引的间隔是 8192 而我们删除的数据范围是 1000-10000显然作为一个整周期自然是 0-16384(2x8192)
当我们再次查看磁盘目录
» du -h0B ./detached
7.7M ./20230809_2_2_0
7.7M ./20230808_1_1_00B ./20230809_2_2_0_3
7.6M ./20230808_1_1_0_323M .总目录从 15M 变成了 23M而两个分区也都各自生成了一个以 mutation version 为后缀的新分区
因此接下来的逻辑如下
clickhouse 会创建一个 tmp_mut_ 为前缀、mutation version 为后缀的临时分区目录例如这里的就是 tmp_mut_20230808_1_1_0_3
对于需要删除的分区会在 tmp_mut 目录中生成全新的 .bin 和 .mrk 文件
对于无需删除的分区clickhouse 会创建一个 tmp_clone_ 为前缀、mutation version 为后缀的临时分区目录并将原分区里面的数据以硬链接的方式拷贝过去并修改目录名称为正确的格式
下面是执行的日志情况
Debug delete_operate.mutations_operate (4351d317-2cd6-4328-85fe-49d5beeff5c3): Clone part /opt/homebrew/var/lib/clickhouse/store/435/4351d317-2cd6-4328-85fe-49d5beeff5c3/20230809_2_2_0/ to /opt/homebrew/var/lib/clickhouse/store/435/4351d317-2cd6-4328-85fe-49d5beeff5c3/tmp_clone_20230809_2_2_0_3
Trace delete_operate.mutations_operate (4351d317-2cd6-4328-85fe-49d5beeff5c3): Renaming temporary part tmp_clone_20230809_2_2_0_3 to 20230809_2_2_0_3 with tid (1, 1, 00000000-0000-0000-0000-000000000000).
Trace MergedBlockOutputStream: filled checksums 20230808_1_1_0_3 (state Temporary)
Trace delete_operate.mutations_operate (4351d317-2cd6-4328-85fe-49d5beeff5c3): Renaming temporary part tmp_mut_20230808_1_1_0_3 to 20230808_1_1_0_3 with tid (1, 1, 00000000-0000-0000-0000-000000000000)从磁盘目录也可以佐证这一点首先上面的 20230809_2_2_0_3 占用空间为 0B当然这是 mac 独有的现实方式在其它 linux 系统不一定是这么显示进入各个分区查看一下
wjun :: data/delete_operate/mutations_operate ‹stable› » ll 20230808_1_1_0_3
total 15632
-rw-r----- 1 wjun admin 17863 Aug 9 19:36 CreateTime.bin
-rw-r----- 1 wjun admin 369 Aug 9 19:36 CreateTime.cmrk2
-rw-r----- 1 wjun admin 3968891 Aug 9 19:36 Score.bin
-rw-r----- 1 wjun admin 409 Aug 9 19:36 Score.cmrk2
-rw-r----- 1 wjun admin 3969011 Aug 9 19:36 UserId.bin
-rw-r----- 1 wjun admin 409 Aug 9 19:36 UserId.cmrk2
-rw-r----- 1 wjun admin 490 Aug 9 19:36 checksums.txt
-rw-r----- 1 wjun admin 90 Aug 9 19:36 columns.txt
-rw-r----- 1 wjun admin 6 Aug 9 19:36 count.txt
-rw-r----- 1 wjun admin 10 Aug 9 19:36 default_compression_codec.txt
-rw-r----- 1 wjun admin 1 Aug 9 19:36 metadata_version.txt
-rw-r----- 1 wjun admin 8 Aug 9 19:36 minmax_CreateTime.idx
-rw-r----- 1 wjun admin 4 Aug 9 19:36 partition.dat
-rw-r----- 1 wjun admin 188 Aug 9 19:36 primary.cidx
wjun :: data/delete_operate/mutations_operate ‹stable› » ll 20230809_2_2_0_3
total 15768
-rw-r----- 2 wjun admin 18042 Aug 9 19:35 CreateTime.bin
-rw-r----- 2 wjun admin 375 Aug 9 19:35 CreateTime.cmrk2
-rw-r----- 2 wjun admin 4004938 Aug 9 19:35 Score.bin
-rw-r----- 2 wjun admin 415 Aug 9 19:35 Score.cmrk2
-rw-r----- 2 wjun admin 4004915 Aug 9 19:35 UserId.bin
-rw-r----- 2 wjun admin 415 Aug 9 19:35 UserId.cmrk2
-rw-r----- 2 wjun admin 490 Aug 9 19:35 checksums.txt
-rw-r----- 2 wjun admin 90 Aug 9 19:35 columns.txt
-rw-r----- 2 wjun admin 7 Aug 9 19:35 count.txt
-rw-r----- 2 wjun admin 10 Aug 9 19:35 default_compression_codec.txt
-rw-r----- 2 wjun admin 8 Aug 9 19:35 minmax_CreateTime.idx
-rw-r----- 2 wjun admin 4 Aug 9 19:35 partition.dat
-rw-r----- 2 wjun admin 173 Aug 9 19:35 primary.cidx20230809_2_2_0_3 分区 inode 被连接次数为 2 表示建立了硬链接。
因此 mutation 的删除逻辑如下
每个分区执行附带删除操作的 where 条件的 count 查询获取需要执行删除操作的分区对于需要执行删除操作的分区会创建一个临时目录并生成全新删除需要删除的行的文件随后 rename 分区对于无需执行删除操作的分区会创建一个临时目录并以硬链接的方式拷贝文件随后 rename 分区原分区在system.parts中会被置为 inactive 状态在下一次 merge 是删除原分区
而对于更新操作基本逻辑一致需要注意的是需要执行更新操作的分区会有如下两种情况
分区类型为 wide只会重新生成受影响行的 bin 和 mrk 文件不受影响的文件以硬链接的方式拷贝分区类型为 compact因为所有列都是一个文件因此会重新生成 bin 和 mrk 文件
更新和删除操作流程不一致的原因是删除影响全部列而更新只影响部分列 mergeTree 表的分区类型分为 wide 和 compact 两种受min_bytes_for_wide_part和min_rows_for_wide_part参数影响。wide 类型的分区一个列一个文件compact 类型的分区所有列公用一个文件当分区数据的行数和字节较小时为 compact 类型不管是查询所有字段或某个字段相对较快当数据量很大时就会以列式存储来追求 AP 查询性能 1.3 不足
当我们走一遍 mutation 时发现在删除任务完成后表 merge 前的这一段时间磁盘空间不减反增这个就让用户很难接受了。因此就可能会出现因为磁盘空间不足想要删除数据结果删除操作导致空间进一步不足的窘境。同时 mutation 会重写受影响的分区这也是 mutation 操作重的原因所在。
二、mergeTree
对于 clickhouse 这类高性能分析型数据库而言修改源文件是一件非常奢侈且代价昂贵的操作相对于直接修改源文件我们将修改和新增操作都转换为新增操作即以增代删将是一个非常不错的选择。是不是和 Hbase 的思路十分接近。在 mergeTree 家族中有一个特殊的表引擎叫 CollapsingMergeTree翻译过来叫折叠合并树引擎就是提供了这样的功能。它通过定义一个 sign 标记字段来记录数据行的状态。如果 sign 为 1 表示这是一行有效的数据如果 sign 为 -1 表示这行数据被删除。当 CollapsingMergeTree 分区合并时同一分区的 1、-1 将会被抵消犹如一张纸折叠一般。
2.1 实操
创建 CollapsingMergeTree 表
create table collapsing_table
(Id String,Code Int32,CreateTime DateTime,Sign Int8
) engine CollapsingMergeTree(Sign)partition by toYYYYMMDD(CreateTime)order by Id;注和其它 mergeTree 引擎一样 CollapsingMergeTree 依然是以 order by 字段作为后续数据唯一性的依据 插入一批原始数据
insert into collapsing_table values (A000, 100, 2023-08-09 00:00:00, 1);
insert into collapsing_table values (A001, 100, 2023-08-09 00:00:00, 1);
insert into collapsing_table values (A002, 100, 2023-08-09 00:00:00, 1);修改 A000 的 Code 为 200 并删除 A002 的数据
# 修改 A000 的 Code 为 200
insert into collapsing_table values (A000, 100, 2023-08-09 00:00:00, -1);
insert into collapsing_table values (A000, 200, 2023-08-09 00:00:00, 1);
# 删除 A002 的数据
insert into collapsing_table values (A002, 100, 2023-08-09 00:00:00, -1);
# 手动执行一下分区合并操作
optimize table collapsing_table final;可以观察到数据已经被删除和修改。
CollapsingMergeTree 在分区合并折叠数据的时候遵循下面规则
如果 sign 1 比 sign -1 多一行最后保留 sign 1 的数据如果 sign 1 比 sign -1 少一行最后保留 sign -1 的数据如果 sign 1 和 sign -1 一样多且最后一行时 sign 1则保留第一行的 sign -1 和最后一行 sign 1如果 sign 1 和 sign -1 一样多且最后一行时 sign -1则什么也不保留其余情况 clickhouse 会打印告警日志但不会报错且查询情况不可预知
2.2 不足
当前表的数据如下
select *
from collapsing_table;Query id: 4b1da757-d02a-4b88-92e5-1fe659ca462c┌─Id───┬─Code─┬──────────CreateTime─┬─Sign─┐
│ A002 │ 300 │ 2023-08-09 00:00:00 │ -1 │
└──────┴──────┴─────────────────────┴──────┘
┌─Id───┬─Code─┬──────────CreateTime─┬─Sign─┐
│ A002 │ 300 │ 2023-08-09 00:00:00 │ 1 │
└──────┴──────┴─────────────────────┴──────┘
┌─Id───┬─Code─┬──────────CreateTime─┬─Sign─┐
│ A000 │ 200 │ 2023-08-09 00:00:00 │ 1 │
│ A001 │ 100 │ 2023-08-09 00:00:00 │ 1 │
└──────┴──────┴─────────────────────┴──────┘4 rows in set. Elapsed: 0.003 sec.从操作来看 A002 是要被删除的
但是如果查询sql如下
select Id, sum(Code), count(Code), avg(Code)
from collapsing_table
group by Id;Query id: 610f6503-1344-4ba0-9564-6327277ffe95┌─Id───┬─sum(Code)─┬─count(Code)─┬─avg(Code)─┐
│ A001 │ 100 │ 1 │ 100 │
│ A000 │ 200 │ 1 │ 200 │
│ A002 │ 600 │ 2 │ 300 │
└──────┴───────────┴─────────────┴───────────┘3 rows in set. Elapsed: 0.005 sec.此时的结果是不对的因此需要改写 sql
select Id, sum(Code * Sign), count(Code * Sign), avg(Code * Sign)
from collapsing_table
group by Id
having sum(Sign) 0;Query id: a3fe84d0-33a5-4287-bd02-49ab03df1852┌─Id───┬─sum(multiply(Code, Sign))─┬─count(multiply(Code, Sign))─┬─avg(multiply(Code, Sign))─┐
│ A001 │ 100 │ 1 │ 100 │
│ A000 │ 200 │ 1 │ 200 │
└──────┴───────────────────────────┴─────────────────────────────┴───────────────────────────┘2 rows in set. Elapsed: 0.005 sec.当然还有一种方式就是在查询数据前执行分区合并操作optimize table collapsing_table final;但这种方式效率极低在生产中慎用
同时 CollapsingMergeTree 还存在一些问题例如在分区合并前用户是可以看到所有数据的。当然上面所说的问题都不是最致命的CollapsingMergeTree 最致命点在于对于 sign 的写入顺序有严格的要求对于一个删除操作正常的顺序应该是先写入 1 再写入 -1
insert into collapsing_table values (A002, 300, 2023-08-09 00:00:00, 1);
insert into collapsing_table values (A002, 300, 2023-08-09 00:00:00, -1);但如果颠倒顺序
insert into collapsing_table values (A002, 300, 2023-08-09 00:00:00, -1);
insert into collapsing_table values (A002, 300, 2023-08-09 00:00:00, 1);则不会被删除。而在生产环境一旦 CollapsingMergeTree 在多线程中处理就无法保证写入顺序了。
当然幸运的是 clickhouse 也注意到 CollapsingMergeTree 的缺点并推出了新的表引擎 VersionedCollapsingMergeTree在 CollapsingMergeTree 的基础上将按照写入顺序折叠修改为按照版本号顺序进行折叠而版本号交由用户来管理。VersionedCollapsingMergeTree 引擎的操作就交给读者来体验毕竟下面还有一种更贴合 TP 数据库操作的删除操作
三、lightweight
上面介绍了通过 mutation 和 mergeTree 来实现删除操作但是 mutation 操作太重mergeTree 则需要修改 sql 且删除操作受分区合并时机影响。从 clickhouse v22.8 开始提供了一个轻量级删除功能且语法为标准 sql
3.1 实操
准备表和数据
create table lightweight_operate
(UserId UInt64,Score UInt64,CreateTime DateTime
) engine MergeTree()partition by toYYYYMMDD(CreateTime)order by UserId;insert into lightweight_operate
select number,abs(number - 100),2023-08-08 00:00:00
from system.numbers
limit 1000000;insert into lightweight_operate
select number,abs(number - 100),2023-08-09 00:00:00
from system.numbers
limit 1000000;同样删除 20230808 分区中 1000-10000 之间的所有数据sql 如下
delete from lightweight_operate where toYYYYMMDD(CreateTime) 20230808 and UserId between 1000 and 10000;验证一下
select count() from lightweight_operate where toYYYYMMDD(CreateTime) 20230808;Query id: 0344da3b-5ea5-436d-ba29-cfb1a8e3420e┌─count()─┐
│ 990999 │
└─────────┘1 row in set. Elapsed: 0.008 sec. Processed 1.00 million rows, 5.00 MB (128.59 million rows/s., 642.93 MB/s.)成功删除
3.2 原理
查看磁盘目录
» ll
total 16
drwxr-x--- 16 wjun admin 512 Aug 9 21:09 20230808_1_1_0
drwxr-x--- 18 wjun admin 576 Aug 9 21:10 20230808_1_1_0_3
drwxr-x--- 16 wjun admin 512 Aug 9 21:09 20230809_2_2_0
drwxr-x--- 15 wjun admin 480 Aug 9 21:10 20230809_2_2_0_3
drwxr-x--- 2 wjun admin 64 Aug 9 21:09 detached
-rw-r----- 1 wjun admin 1 Aug 9 21:09 format_version.txt
-rw-r----- 1 wjun admin 171 Aug 9 21:10 mutation_3.txt» du -h0B ./detached
7.7M ./20230809_2_2_0
7.7M ./20230808_1_1_00B ./20230809_2_2_0_328K ./20230808_1_1_0_315M .可以看出轻量删除依然是一个 mutation 操作从system.mutations表也可以验证但轻量删除生成的新的分区 20230808_1_1_0_3 仅 28K那么轻量删除和 mutation 删除的区别在哪
查看 20230808_1_1_0_3 磁盘目录
wjun :: data/delete_operate/lightweight_operate ‹stable› » ll 20230808_1_1_0_3
total 15800
-rw-r----- 2 wjun admin 18042 Aug 9 21:09 CreateTime.bin
-rw-r----- 2 wjun admin 375 Aug 9 21:09 CreateTime.cmrk2
-rw-r----- 2 wjun admin 4004938 Aug 9 21:09 Score.bin
-rw-r----- 2 wjun admin 415 Aug 9 21:09 Score.cmrk2
-rw-r----- 2 wjun admin 4004915 Aug 9 21:09 UserId.bin
-rw-r----- 2 wjun admin 415 Aug 9 21:09 UserId.cmrk2
-rw-r----- 1 wjun admin 4493 Aug 9 21:10 _row_exists.bin
-rw-r----- 1 wjun admin 236 Aug 9 21:10 _row_exists.cmrk2
-rw-r----- 1 wjun admin 589 Aug 9 21:10 checksums.txt
-rw-r----- 1 wjun admin 110 Aug 9 21:10 columns.txt
-rw-r----- 2 wjun admin 7 Aug 9 21:09 count.txt
-rw-r----- 1 wjun admin 10 Aug 9 21:10 default_compression_codec.txt
-rw-r----- 1 wjun admin 1 Aug 9 21:10 metadata_version.txt
-rw-r----- 2 wjun admin 8 Aug 9 21:09 minmax_CreateTime.idx
-rw-r----- 2 wjun admin 4 Aug 9 21:09 partition.dat
-rw-r----- 2 wjun admin 173 Aug 9 21:09 primary.cidx发现多了一组 _row_exists 文件而其余文件的 inode 连接数均为 2也就是说轻量删除是真正的给字段添加了一个标记。
在查询的时候过滤 在分区合并的时候删除 比 mutation 轻的点在于轻量删除不会重构整个分区目录而是重写 _row_exists 文件这样涉及到的修改会少很多至于分区的拷贝和不涉及删除操作的分区操作逻辑则和上面介绍的 mutation 流程一致
3.3 不足
轻量删除的设计思路相比之前的会好上很多但 clickhouse 毕竟不是 TP 数据库目前轻量删除依然存在一些问题和限制如
轻量删除是异步的只有在分区合并的时候才会被真正删除轻量删除执行完是逻辑上删除对 wide 类型分区友好对于 compact 类型分区会产生较大的磁盘 IO会修改分区在磁盘中的名称可能会影响备份
对于 mutation 是否为异步操作可以通过参数进行配置只需将mutations_sync置为 true 即可
set mutations_sync true;至于其它的不足需要用户结合实际场景进行取舍
