【ClickHouse】MergeTree案例，创建案例: 新增操作：在 /var/lib/clickhouse/da

这是我参与11月更文挑战的第12天，活动详情查看：2021最后一次更文挑战

一、概述

案例，创建案例:

create table mt_table(date Date,id UInt8,name String) engine = MergeTree partition by toYYYYMM(date) order by id;

linux121 :) create table mt_table(date Date,id UInt8,name String) engine = MergeTree partition by toYYYYMM(date) order by id;

CREATE TABLE mt_table
(
    `date` Date,
    `id` UInt8,
    `name` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY id

Ok.

0 rows in set. Elapsed: 0.016 sec.

新增操作：

insert into mt_table values ('2019-05-01', 1, 'zhangsan');
insert into mt_table values ('2019-06-01', 2, 'lisi');
insert into mt_table values ('2019-05-03', 3, 'wangwu');

在 /var/lib/clickhouse/data/default/mt_tree 下可以看到:

[root@linux121 mt_table]# pwd
/var/lib/clickhouse/data/default/mt_table
[root@linux121 mt_table]# ll
total 4
drwxr-x--- 2 clickhouse clickhouse 221 Apr 25 22:23 201905_1_1_0
drwxr-x--- 2 clickhouse clickhouse 221 Apr 25 22:23 201905_3_3_0
drwxr-x--- 2 clickhouse clickhouse 221 Apr 25 22:23 201906_2_2_0
drwxr-x--- 2 clickhouse clickhouse   6 Apr 25 22:22 detached
-rw-r----- 1 clickhouse clickhouse   1 Apr 25 22:22 format_version.txt

[root@linux121 201905_1_1_0]# pwd
/var/lib/clickhouse/data/default/mt_table/201905_1_1_0
[root@linux121 201905_1_1_0]# ll
total 48
-rw-r----- 1 clickhouse clickhouse 384 Apr 25 22:23 checksums.txt
-rw-r----- 1 clickhouse clickhouse  74 Apr 25 22:23 columns.txt
-rw-r----- 1 clickhouse clickhouse   1 Apr 25 22:23 count.txt
-rw-r----- 1 clickhouse clickhouse  28 Apr 25 22:23 date.bin
-rw-r----- 1 clickhouse clickhouse  48 Apr 25 22:23 date.mrk2
-rw-r----- 1 clickhouse clickhouse  27 Apr 25 22:23 id.bin
-rw-r----- 1 clickhouse clickhouse  48 Apr 25 22:23 id.mrk2
-rw-r----- 1 clickhouse clickhouse   4 Apr 25 22:23 minmax_date.idx
-rw-r----- 1 clickhouse clickhouse  35 Apr 25 22:23 name.bin
-rw-r----- 1 clickhouse clickhouse  48 Apr 25 22:23 name.mrk2
-rw-r----- 1 clickhouse clickhouse   4 Apr 25 22:23 partition.dat
-rw-r----- 1 clickhouse clickhouse   2 Apr 25 22:23 primary.idx

*.bin 是按列保存数据的文件
*.mrk 保存块偏移量
primary.idx 保存主键索引

文件详细简介：

checksums.txt：二进制的校验文件, 保存了余下文件的大小 size 和 size 的 Hash 值, 用于快速校验文件的完整和正确性。

columns.txt：明文的列信息文件。

[root@linux121 201905_1_1_0]# cat columns.txt 
columns format version: 1
3 columns:
`date` Date
`id` UInt8
`name` String

date.bin ：压缩格式(默认 LZ4)的数据文件, 保存了原始数据。以列名 .bin 命名。
date.mrk2：使用了自适应大小的索引间隔, 名字为 .mrk2
primary.idx：二进制的一级索引文件, 在建表的时候通过 OrderBy 或者 PrimaryKey 声明的稀疏索引。

数据分区

数据是以分区目录的形式组织的, 每个分区独立分开存储。

这种形式, 查询数据时, 可以有效的跳过无用的数据文件。

1）数据分区的规则

分区键的取值, 生成分区 ID, 分区根据 ID 决定。

根据分区键的数据类型不同, 分区 ID 的生成目前有四种规则:

不指定分区键
使用整形
使用日期类型 toYYYYMM(date)
使用其他类型

数据在写入时, 会对照分区 ID 落入对应的分区。

2）分区目录的生成规则

partitionID_MinBlockNum_MaxBlockNum_Level

BlockNum 是一个全局整型, 从1开始, 每当新创建一个分区目录, 此数字就累加1。

MinBlockNum : 最小数据块编号
MaxBlockNum : 最大数据块编号

对于一个新的分区, MinBlockNum 和 MaxBlockNum 的值相同

如: 2020_03_1_1_0, 2020_03_2_2_0

Level : 合并的层级, 即某个分区被合并过得次数。不是全局的, 而是针对某一个分区。

3）

MergeTree 的分区目录在数据写入过程中被创建。

不同的批次写入数据属于同一分区, 也会生成不同的目录, 在之后的某个时刻再合并(写入后的10-15分钟), 合并后的旧分区目录默认8分钟后删除。

同一个分区的多个目录合并以后的命名规则:

MinBlockNum: 取同一分区中 MinBlockNum 值最小的。
MaxBlockNum: 取同一分区中 MaxBlockNum 值最大的。
Level: 取同一分区最大的 Level 值加1。

索引

文件: primary.idx

MergeTree 的主键使用 Primary Key 定义, 主键定义之后, MergeTree 会根据 index_granularity 间隔(默认8192)为数据生成一级索引并保存至 primary.idx 文件中。这种方式是稀疏索引。

简化形式: 通过 order by 指代主键。

1）稀疏索引

primary.idx 文件的一级索引采用稀疏索引。

稀疏索引占用空间小, 所以 primary.idx 内的索引数据常驻内存, 取用速度快。

稠密索引: 每一行索引标记对应一行具体的数据记录
稀疏索引: 每一行索引标记对应一段数据记录(默认索引粒度为8192)

2）索引粒度

index_granularity 参数, 表示索引粒度。

新版本中 clickhouse提供了自适应索引粒度。

索引粒度在 MergeTree 引擎中很重要。

3）索引的数据的生成规则

借助 hits_v1 表中的真实数据观察:

primary.idx 文件

由于稀疏索引, 所以 MergeTree 要间隔 index_granularity 行数据才会生成一个索引记录, 其索引值会根据声明的主键字段获取。

4）索引的查询过程

索引是如何工作的?

对 primary.idx 文件的查询过程

MarkRange : 一小段数据区间

按照 index_granularity 的间隔粒度, 将一段完整的数据划分成多个小的数据段, 小的数据段就是 MarkRange, MarkRange 与索引编号对应。

案例: 共200行数据 index_granularity 大小为5 主键 ID 为 Int, 取值从 0 开始根据索引生成规则, primary.idx 文件内容为: 05101520253035404550...200

共 200 行数据 / 5 = 40 个 MarkRange

索引查询 where id = 3

第一步: 形成区间格式: [3,3] 第二步: 进行交集 [3,3]∩[0,199]

以 MarkRange 的步长大于8分块, 进行剪枝

第三步:合并

MarkRange: (start0, end 20)

在 ClickHouse 中, MergeTree 引擎表的索引列在建表时使用 ORDER BY 语法来指定。而在官方文档中, 用了下面一幅图来说明。

5）跳数索引

granularity 和 index_granularity 的关系。

index_granularity 定义了数据的粒度 granularity 定义了聚合信息汇总的粒度换言之, granularity 定义了一行跳数索引能够跳过多少个 index_granularity 区间的数据。

索引的可用类型：

minmax 存储指定表达式的极值(如果表达式是 tuple, 则存储 tuple 中每个元素的极值), 这些信息用于跳过数据块, 类似主键。
set(max_rows) 存储指定表达式的惟一值(不超过 max_rows 个, max_rows=0 则表示『无限制』)。这些信息可用于检查 WHERE 表达式是否满足某个数据块。
ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed) 存储包含数据块中所有 n 元短语的布隆过滤器。只可用在字符串上。可用于优化 equals , like 和 in 表达式的性能。 n – 短语长度。 size_of_bloom_filter_in_bytes – 布隆过滤器大小, 单位字节。(因为压缩得好, 可以指定比较大的值, 如 256 或 512)。number_of_hash_functions – 布隆过滤器中使用的 hash 函数的个数。 random_seed – hash 函数的随机种子。
tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed) 跟 ngrambf_v1 类似, 不同于 ngrams 存储字符串指定长度的所有片段。它只存储被非字母数据字符分割的片段。

INDEX sample_index (u64 * length(s)) TYPE minmax GRANULARITY 4

INDEX sample_index2 (u64 * length(str), i32 + f64 * 100, date, str) TYPE set(100) GRANULARITY 4

INDEX sample_index3 (lower(str), str) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4

数据存储

表由按主键排序的数据片段组成。

当数据被插入到表中时, 会分成数据片段并按主键的字典序排序。例如, 主键是 (CounterID, Date) 时, 片段中数据按 CounterID 排序, 具有相同 CounterID 的部分按 Date 排序。

不同分区的数据会被分成不同的片段, ClickHouse 在后台合并数据片段以便更高效存储。不会合并来自不同分区的数据片段。这个合并机制并不保证相同主键的所有行都会合并到同一个数据片段中。

ClickHouse 会为每个数据片段创建一个索引文件, 索引文件包含每个索引行(『标记』)的主键值。索引行号定义为 n * index_granularity 。最大的 n 等于总行数除以 index_granularity 的值的整数部分。对于每列, 跟主键相同的索引行处也会写入『标记』。这些『标记』让你可以直接找到数据所在的列。

可以只用一单一大表并不断地一块块往里面加入数据 – MergeTree 引擎的就是为了这样的场景。

1）按列存储

在 MergeTree 中数据按列存储, 具体到每个列字段, 都拥有一个 .bin 数据文件, 是最终存储数据的文件。

按列存储的好处:

更好的压缩
最小化数据扫描范围

MergeTree 往 .bin 文件存数据的步骤:

对数据进行压缩
根据 OrderBy 排序
数据以压缩数据块的形式写入 .bin 文件

2）压缩数据块

CompressionMethod_CompressedSize_UnccompressedSize

一个压缩数据块有两部分组成:

头信息
压缩数据

头信息固定使用 9 位字节表示, 1个 UInt8(1字节) + 2个 UInt32 (4字节), 分别表示压缩算法、压缩后数据大小、压缩前数据大小：

如: 0x821200065536 0x82: 是压缩方法 12000: 压缩后数据大小 65536: 压缩前数据大小

clickhouse-compressor --stat 命令

[root@hdp-1 20180301_20180330_1_100_20]# clickhouse-compressor --stat <./date.bin > out.log
[root@hdp-1 20180301_20180330_1_100_20]# cat out.log
200
207

out1.log 文件中显示的数据前面的是压缩的, 后面是未压缩的。

如果按照默认 8192 的索引粒度把数据分成批次, 每批次读入数据的规则:

设 x 为批次数据的大小
如果单批次获取的数据 x < 64k, 则继续读下一个批次, 找到 size > 64k 则生成下一个数据块
如果单批次数据 64k < x < 1M 则直接生成下一个数据块
如果 x>1M, 则按照 1M 切分数据, 剩下的数据继续按照上述规则执行。

数据标记

.mrk 文件

作用：将以及索引 primary.idx 和数据文件 .bin 建立映射关系。

数据标记和索引区间是对齐的, 根据索引区间的下标编号, 就能找到数据标记 --- 索引编号和数据标记数值相同。
每一个 [Column].bin 都有一个 [Column].mrk 与之对应 --- .mrk 文件记录数据在 .bin 文件中的偏移量。

拿 JavaEnable 字段说明: 1 b * 8192 = 8192b 8192b * 8 = 64k

`.mrk` 文件内容的生成规则

数据标记和区间是对齐的。

均按照 index_granularity 粒度间隔。可以通过索引区间的下标编号找到对应的数据标记。

每一个列字段的 .bin 文件都有一个 .mrk 数据标记文件, 用于记录数据在 .bin 文件中的偏移量信息。

标记数据采用 LRU 缓存策略加快其取用速度。

分区、索引、标记和压缩协同

写入过程

步骤：

生成分区目录
合并分区目录
生成 primary.idx 索引文件、每一列的 .bin 和 .mrk 文件

查询过程

步骤：

根据分区索引缩小查询范围
根据数据标记, 缩小查询范围
解压压缩块

数据标记与压缩数据块的对应关系

多对一:
一对一:
一对多:

`MergeTree` 的 `TTL`

TTL: time to live 数据存活时间。

TTL 既可以设置在表上, 也可以设置在列上。
TTL 指定的时间到期后则删除相应的表或列, 如果同时设置了 TTL, 则根据先过期时间删除相应数据。

用法: TTL time_col + INTERVAL 3 DAY

表示数据存活时间是 time_col 时间的3天后

INTERVAL 可以设定的时间: SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR

1）`TTL` 设置在列上

创建数据库

create table ttl_table_v1 ( \
id String, \
create_time DateTime, \
code String TTL create_time + INTERVAL 10 SECOND, \
type UInt8 TTL create_time + INTERVAL 10 SECOND \
) \
ENGINE = MergeTree \
PARTITION BY toYYYYMM(create_time) \
ORDER BY id;


- 实操如下：
linux121 :) create table ttl_table_v1 ( \
:-] id String, \
:-] create_time DateTime, \
:-] code String TTL create_time + INTERVAL 10 SECOND, \
:-] type UInt8 TTL create_time + INTERVAL 10 SECOND \
:-] ) \
:-] ENGINE = MergeTree \
:-] PARTITION BY toYYYYMM(create_time) \
:-] ORDER BY id;

CREATE TABLE ttl_table_v1
(
    `id` String,
    `create_time` DateTime,
    `code` String TTL create_time + toIntervalSecond(10),
    `type` UInt8 TTL create_time + toIntervalSecond(10)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id

Ok.

0 rows in set. Elapsed: 0.015 sec.

新增数据

insert into ttl_table_v1 values('A000',now(),'C1',1),('A000', now() + INTERVAL 10 MINUTE,'C1',1);

- 实操如下：
linux121 :) insert into ttl_table_v1 values('A000',now(),'C1',1),('A000', now() + INTERVAL 10 MINUTE,'C1',1);

INSERT INTO ttl_table_v1 VALUES

Ok.

2 rows in set. Elapsed: 0.006 sec.

查询数据

SELECT * FROM ttl_table_v1;


- 实操如下：

linux121 :) SELECT * FROM ttl_table_v1;

SELECT *
FROM ttl_table_v1

┌─id───┬─────────create_time─┬─code─┬─type─┐
│ A000 │ 2021-04-26 03:44:36 │      │    0 │
│ A000 │ 2021-04-26 03:54:36 │ C1   │    1 │
└──────┴─────────────────────┴──────┴──────┘

2 rows in set. Elapsed: 0.007 sec.

优化

optimize table ttl_table_v1 FINAL;

2）`TTL` 设置在表上

时间到后，整张表被删除。

create table ttl_table_v2 ( \
id String,  \
create_time DateTime,  \
code String TTL create_time + INTERVAL 10 SECOND, \
type UInt8 \
) \
ENGINE = MergeTree \
PARTITION BY toYYYYMM(create_time) \
ORDER BY create_time \
TTL create_time + INTERVAL 1 DAY;

修改列：

ALTER TABLE ttl_table_v1 MODIFY TTL create_time + INTERVAL + 3 DAY;

TTL 目前没有取消方法。

3）`TTL` 文件说明

`MergeTree` 的存储策略

19.15之前, 只能单路径存储, 存储位置为在 config.xml 配置文件中指定。

<!-- Path to data directory, with trailing slash. -->
<path>/var/lib/clickhouse/</path>

19.15之后, 支持多路径存储策略的自定义存储策略, 目前有两类策略:

JBOD 策略
HOT/COLD 策略

【ClickHouse】MergeTree