【clickhouse】ClickHouse表引擎 MergeTree 建表方式与分区规则

1.概述

转载：ClickHouse表引擎 1.MergeTree 建表方式与分区规则

2.MergeTree 介绍

表引擎是 ClickHouse 设计实现中的一大特色。可以说，是表引擎决定了一张数据表最终的“性格”，比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。

ClickHouse拥有非常庞大的表引擎体系，其共拥有合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎。而在这众多的表引擎中，又属合并树（MergeTree）表引擎及其家族系列最为强大，在生产环境的绝大部分场景中，都会使用此系列的表引擎。因为只有合并树系列的表引擎才支持主键索引、数据分区、数据副本和数据采样这些特性，同时也只有此系列的表引擎支持ALTER相关操作。

MergeTree在写入一批数据时，数据总以片段的形式写入磁盘，且数据片段不可修改，为了避免片段过多，clickhouse 后台线程会定期合并片段，属于同一分区的片段会合并成一个新的片段，这种数据片段往复合并的特点也是合并树名称的由来。

合并树家族自身也拥有多种表引擎的变种。其中MergeTree作为家族中最基础的表引擎，提供了主键索引、数据分区、数据副本和数据采样等基本能力，而家族中其他的表引擎则在MergeTree的基础之上各有所长。

ReplacingMergeTree表引擎：具有删除重复数据的特性，
SummingMergeTree表引擎：会按照排序键自动聚合数据。

如果给MergeTree系列的表引擎加上 Replicated 前缀，又会得到一组支持数据副本的表引擎，如下表格中的三列进行组合。

Replicated 支持数据副本	Replacing Summing Aggregating Collapsing VersionedCollapsing Graphite	MergeTree 基础表引擎

Replicated+Replacing+MergeTree = ReplicatedReplacingMergeTree
Replicated+Summing+MergeTree = ReplicatedSummingMergeTree

虽然合并树的变种很多，但MergeTree表引擎才是根基。作为合并树家族系列中最基础的表引擎，MergeTree具备了该系列其他表引擎共有的基本特征，所以将MergeTree表引擎的原理梳理清楚，才能够掌握该系列引擎的精髓。

3.建表语法

创建 MergeTree 数据表的方法，与普通的数据表的方法大致相同，但需要将 ENGINE 参数声明为 MergeTree() ，其完整的语法如下所示：

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name(
  name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  ...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

MergeTree表引擎除了常规参数之外，还拥有一些独有的配置选项。接下来会着重介绍其中几个重要的参数，包括它们的使用方法和工作原理。但是在此之前，还是先介绍一遍它们的作用。

3.1 ORDER BY

• 排序键
• 必填
• 用于指定在一个数据片段内，数据以 何种标准排序 。默认情况下主键（PRIMARY KEY）与排序键相同。
• 排序键既可以是单个列字段，也可以通过元组的形式使用多个列字段。例如 ORDER BY ID 或 ORDER BY（ID, EventDate）。
• 当使用多个列字段排序时，以ORDER BY（ID, EventDate）为例，在单个数据片段内，数据首先会以 ID 排序，相同ID的数据再按EventDate排序。

create table t_merge_tree_t1(
  id UInt8,
  name String
) engine = MergeTree()
order by id; -- 设置排序键

3.2 PARTITION BY

分区键

选填

用于指定表数据以何种标准进行分区。分区键既可以是单个列字段，也可以通过元组的形式使用多个列字段，同时它也支持使用列表达式。如果不声明分区键，则ClickHouse会生成一个名为 all 的分区。合理使用数据分区，可以有效减少查询时数据文件的扫描范围。

create table t_merge_tree_t2(
  id UInt8,
  name String
) engine = MergeTree()
partition by (id,name) -- 设置分区键
order by id;

3.3 PRIMARY KEY

主键

选填

声明后会依照主键字段生成一级索引，用于加速表查询。

默认情况，主键与排序键 (ORDER BY) 相同，通常直接使用ORDER BY 代为指定主键，无须刻意通过PRIMARY KEY声明。

在一般情况下，在单个数据片段内，数据与一级索引以相同的规则升序排列。

MergeTree引擎允许主键存在重复数据（ReplacingMergeTree可以去重）。

create table t_merge_tree_t3(
  id UInt8,
  name String
) engine = MergeTree()
partition by (id,name)
order by id
primary key id; -- 设置主键

3.4 SAMPLE BY

抽样表达式
选填
用于声明数据以何种标准进行采样。如果使用了此配置项，那么在主键的配置中也需要声明同样的表达式，抽样表达式需要配合SAMPLE 子查询使用，这项功能对于选取抽样数据十分有用。例如：

create table t_merge_tree_t4_2(
  id UInt8,
  name String
) engine = MergeTree()
partition by (id,name)
order by intHash32(id)
primary key intHash32(id)
sample by intHash32(id);

3.5 index_granularity

选填

SETTINGS : index_granularity

对于MergeTree而言是一项非常重要的参数，它表示索引的粒度，默认值为 8192。

MergeTree的索引在默认情况下，每间隔8192行数据才生成一条索引

create table t_merge_tree_t5(
  id UInt8,
  name String
) engine = MergeTree()
order by id
settings index_granularity = 8192;

8192是一个神奇的数字，在 ClickHouse 中大量数值参数都有它的影子，可以被其整除（例如最小压缩
块大小min_compress_block_size:65536）。通常情况下并不需要修改此参数，但理解它的工作原理有
助于我们更好地使用MergeTree。

3.6 index_granularity_bytes

选填

SETTINGS:index_granularity_bytes

在19.11版本之前，ClickHouse只支持固定大小的索引间隔，由index_granularity控制，默认为8192。

在新版本中，它增加了自适应间隔大小的特性，即根据每一批次写入数据的体量大小，动态划分间隔大小。

数据的体量大小，由index_granularity_bytes参数控制的，默认为10M(10×1024×1024)，设置为0表示不启动自适应功能。

3.7 enable_mixed_granularity_parts

选填

SETTINGS: enable_mixed_granularity_parts

设置是否开启自适应索引间隔的功能，默认开启。

3.8 merge_with_ttl_timeout

选填

SETTINGS: merge_with_ttl_timeout

从19.6 版本开始，MergeTree 提供了数据 TTL 的功能，可以选择性的让某个列，或者某个表设置自动过期时间。

3.9 SETTINGS

选填

SETTINGS: storage_policy

从19.15 版本开始，MergeTree 提供了多路径的存储策略，为应对大数据量的存储提供了方案。

4.存储结构

MergeTree表引擎的表是拥有物理存储的，数据会按照分区目录的形式保存在磁盘上，完整物理结构分为3个层级，依次是数据表目录、分区目录及各分区下具体的数据文件。如下图所示：

4.1 partition

partition：分区目录，余下各类数据文件（primary.idx、[Column].mrk、[Column]. bin等）都是以分区目录的形式被组织存放的，属于相同分区的数据，最终会被合并到同一个分区目录，而不同分区的数据，永远不会被合并在一起。

4.2 checksums.txt

checksums.txt：校验文件，使用二进制格式存储。它保存了余下各类文件 (primary. idx、count.txt等) 的 size 大小及 size 的哈希值，用于快速校验文件的完整性和正确性。

4.3 columns.txt

columns.txt：列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息

# cat columns.txt
columns format version: 1
3 columns:
`id` UInt8
`name` String
`date` DateTime

4.4 count.txt

count.txt：计数文件，使用明文格式存储。用于记录当前数据分区目录下数据的总行数

bash
# cat count.txt
2

4.5 primary.idx

primary.idx：一级索引文件，使用二进制格式存储。

用于存放稀疏索引，一张MergeTree表只能声明一次一级索引（ 通过ORDER BY 或者PRIMARY KEY）。

借助稀疏索引，在数据查询的时能够排除主键条件范围之外的数据文件，从而有效减少数据扫描范围，加速查询速度。

4.6 [Column].bin

[Column].bin：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据。

由于MergeTree采用列式存储，每一个列字段都拥有独立的 .bin 数据文件，以列字段名称命名（例如ID.bin、EventDate.bin等）。

4.7 [Column].mrk

[Column].mrk：列字段标记文件，使用二进制格式存储。
标记文件中保存了 .bin 文件中数据的偏移量信息。
标记文件与稀疏索引对齐，又与 .bin 文件一一对应，所以MergeTree通过标记文件建立了 primary.idx 稀疏索引与 .bin 数据文件之间的映射关系。

即先通过稀疏索引（primary.idx）找到对应数据的偏移量信息（.mrk），再通过偏移量直接从 .bin 文件中读取数据。
由于 .mrk 标记文件与 .bin 文件一一对应，所以MergeTree中的每个列字段都会拥有与其对应的 .mrk 标记文件（例如ID.mrk、EventDate.mrk等）

4.8 [Column].mrk2

[Column].mrk2：如果使用了自适应大小的索引间隔，则标记文件会以 .mrk2 命名。

它的工作原理和作用与 .mrk 标记文件相同。

4.9 partition.dat 、 minmax_[Column].idx

partition.dat 与 minmax_[Column].idx：如果使用了分区键，例如 PARTITION BY toYYYYMM(date) ，则会额外生成 partition.dat 与 minmax 索引文件 ( minmax_date.idx )，它们均使用二进制格式存储。

partition.dat: 用于保存当前分区下分区表达式最终生成的值

minmax_date.idx: 用于记录当前分区下分区字段对应原始数据的最小和最大值。

在这些分区索引的作用下，进行数据查询时能够快速跳过不必要的数据分区目录，从而减少最终需要扫描的数据范围。

如果 date 字段对应的原始数据为 2019-05-01、2019-05-05，分区表达式为 PARTITION BY toYYYYMM(date)。

• partition.dat 中保存的值将会是2019-05
• minmax_date.idx 中保存的值将会是 2019-05-012019-05-05

4.10 skp_idx_[Column].idx与skp_idx_[Column].mrk

skp_idx_[Column].idx与skp_idx_[Column].mrk：如果在建表语句中声明了二级索引，则会额外生成相应的二级索引与标记文件，它们同样也使用二进制存储。

• 二级索引在ClickHouse中又称跳数索引，目前拥有 minmax、set、ngrambf_v1和tokenbf_v1四种类型。
• 这些索引的最终目标与一级稀疏索引相同，都是为了进一步减少所需扫描的数据范围，以加速整个查询过程。

5.数据分区

通过先前的介绍已经知晓在MergeTree中，数据是以分区目录的形式进行组织的，每个分区独立分开存储。借助这种形式，在对MergeTree 进行数据查询时，可以有效跳过无用的数据文件，只使用最小的分区目录子集。

这里还需要明确另外一点，在 clickhouse 中数据分区（partition）与数据分片（shard）是完全不同的概念。

• 数据分区是真对本地数据而言是对数据的一种纵向切分，MergeTree 并不能依靠分区的特性将数据分布到多个 clickhouse 节点
• 数据分片是横向切分数据的能力，这个后面会讲到。

5.1 数据的分区规则

MergeTree数据分区的规则由分区ID决定，而具体到每个数据分区所对应的ID，则是由分区键的取值决定的。

分区键支持使用任何一个或一组字段表达式声明，其业务语义可以是年、月、日或者组织单位等任何一种规则。

针对取值数据类型的不同，分区ID的生成逻辑目前拥有四种规则：

• 不指定分区键：如果不使用分区键，即不使用PARTITION BY声明任何分区表达式，则分区ID默认取名为all，所有的数据都会被写入这个all分区。

• 使用整型：如果分区键取值属于整型 (兼容UInt64，包括有符号整型和无符号整型)，且无法转换为日期类型YYYYMMDD格式，则直接按照该整型的字符形式输出，作为分区ID的取值。

• 日期类型：如果分区键取值属于日期类型，或者是能够转换为YYYYMMDD格式的整型，则使用按照YYYYMMDD进行格式化后的字符形式输出，并作为分区ID的取值。

• 使用其他类型：如果分区键取值既不属于整型，也不属于日期类型，例如String、Float等，则通过128位Hash算法取其Hash值作为分区ID的取值。数据在写入时，会对照分区ID落入相应的数据分区。
  如果通过元组的方式使用多个分区字段，则分区ID依旧是根据上述规则生成的，只是多个ID之间通过 “-” 符号依次拼接。

下表格是一些分区的使用案例：

类型	样例数据	分区表达式	分区 ID
无分区键		无	all
整型	18，19，20	partition by age	分区 1：18 分区 2：19 分区 3：20
整型	‘a0’，’a10’，’a101’	partition by length(code)	分区 1：2 分区 2：3 分区 3：4
日期	2020-05-01,2020-06-01	partition by EventTime	分区 1：20200501 分区 2：20200601
日期	2020-05-01,2020-06-01	partition by toYYYYMM(EventTime)	分区 1：202005 分区 2：202006
其他	‘clickhouse’	partition by name	分区1：38b2e79cdb32fa73595ffefdae7a7014
元组	(‘a0’,2020-05-01), (‘a10’,2020-06-01)	partition by ( length(code),EventTime )	分区 1：2-20200501 分区 2：3-20200601

5.2 创建分区表

-- 建库
create database if not exists db_merge;

-- 切换库
use db_merge;

-- 建表
create table t_merge_tree_partition(
  id UInt8,
  name String,
  date DateTime
) engine = MergeTree()
partition by toYYYYMM(date)
order by id;

查看刚刚建表之后存储路径结构

cd /var/lib/clickhouse/data/db_merge/t_merge_tree_partition

[root@node3 t_merge_tree_partition]# ll
总用量 4
drwxr-x--- 2 clickhouse clickhouse 6 5月  13 10:53 detached # 卸载分区保存路径
-rw-r----- 1 clickhouse clickhouse 1 5月  13 10:53 format_version.txt 

插入数据后查看路径结构

-- 插入数据
insert into t_merge_tree_partition values 
(1, 'aa', '2020-01-08 22:33:06'), 
(2, 'bb', '2020-02-08 22:33:06'), 
(3, 'cc', '2020-01-09 22:33:06');


[root@node3 t_merge_tree_partition]# ll
# 新建了两个分区文件夹
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:00 202001_1_1_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:00 202002_2_2_0
drwxr-x--- 2 clickhouse clickhouse   6 5月  13 10:53 detached
-rw-r----- 1 clickhouse clickhouse   1 5月  13 10:53 format_version.txt

再次插入数据后查看结构

-- 每次执行一个SQL语句，执行数据插入，就是一个批次，每个批次中的数据就算是同一个分区的，
-- 但是由于是不同的执行批次，所以会生成当前这个分区的其他不同名称的目录

insert into t_merge_tree_partition values (4, 'dd', '2020-04-08 22:33:06');
insert into t_merge_tree_partition values (5, 'ee', '2020-04-09 22:33:06');
insert into t_merge_tree_partition values (6, 'ff', '2020-04-10 22:33:06');



[root@node3 t_merge_tree_partition]# ll
# 新建的分区创建了 3个分区文件夹
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:00 202001_1_1_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:00 202002_2_2_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:01 202004_3_3_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:01 202004_4_4_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:01 202004_5_5_0
drwxr-x--- 2 clickhouse clickhouse   6 5月  13 10:53 detached
-rw-r----- 1 clickhouse clickhouse   1 5月  13 10:53 format_version.txt

5.3 分区目录的命名规则

通过上一小节的介绍，我们已经知道了分区ID的生成规则。但是如果进入数据表所在的磁盘目录后，会发现MergeTree分区目录的完整物理名称并不是只有ID而已，在ID之后还跟着一串数字，例如 202103_2_2_0。那么这些数字又代表着什么呢？
众所周知，对于MergeTree而言，它最核心的特点是其分区目录的合并动作。但是我们可曾想过，从分区目录的命名中便能够解读出它的合并逻辑。在这一小节，我们会着重对命名公式中各分项进行解读，而关于具体的目录合并过程将会留在后面小节讲解。

一个完整分区目录的命名公式如下所示：

上图中：

• 202103表示分区目录的ID;

• 2_2分别表示最小的数据块编号与最大的数据块编号；

• 而最后的_0 则表示目前合并的层级。

接下来开始分别解释它们的含义：

构成部分	功能说明
PartitionID	分区ID
MinBlockNum和MaxBlockNum	最小数据块编号与最大数据块编号， BlockNum是一个整型的自增长编号。如果将其设为n的话，那么计数n在单张MergeTree数据表内全局累加，n从1开始，每当新创建一个分区目录时，计数n就会累积加1。 对于一个新的分区目录而言，MinBlockNum与MaxBlockNum取值一样，同等于n，例如202103_2_2_0、202104_2_2_0以此类推。 当分区目录发生合并时，对于新产生的合并目录MinBlockNum与MaxBlockNum有着另外的取值规则。
Level	合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄，数值越高表示年龄越大。 Level计数与BlockNum有所不同，它并不是全局累加的。对于每一个新创建的分区目录而言，其初始值均为0。之后，以分区为单位，如果相同分区发生合并动作，则在相应分区内计数累积加1。

5.4 分区目录的合并过程

MergeTree的分区目录和传统意义上其他数据库有所不同。

MergeTree的分区目录并不是在数据表被创建之后就存在的，而是在数据写入过程中被创建的。

也就是说如果一张数据表没有任何数据，那么也不会有任何分区目录存在。

分区目录在建立之后也并不是一成不变的。在其他某些数据库的设计中，追加数据后目录自身不会发生变化，只是在相同分区目录中追加新的数据文件。而MergeTree完全不同，伴随着每一批数据的写入（一次INSERT语句）,MergeTree都会生成一批新的分区目录。

即便不同批次写入的数据属于相同分区，也会生成属于同一个分区的不同目录。也就是说，对于同一个分区而言，也会存在多个分区目录的情况。

在之后的某个时刻（写入后的10～15分钟，也可以手动执行optimize查询语句）, ClickHouse会通过后台任务再将属于相同分区的多个目录合并成一个新的目录。

已经存在的旧分区目录并不会立即被删除，而是在之后的某个时刻通过后台任务被删除（默认8分钟）。

属于同一个分区的多个目录，在合并之后会生成一个全新的目录，目录中的索引和数据文件也会相应地进行合并。

新目录名称的合并方式遵循以下规则，其中：

MinBlockNum：取同一分区内所有目录中最小的MinBlockNum值。

MaxBlockNum：取同一分区内所有目录中最大的MaxBlockNum值。

Level：取同一分区内最大Level值并加1。

先查看分区目录结构

cd /var/lib/clickhouse/data/db_merge/t_merge_tree_partition

[root@node3 t_merge_tree_partition]# ll
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:00 202001_1_1_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:00 202002_2_2_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:01 202004_3_3_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:01 202004_4_4_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:01 202004_5_5_0
drwxr-x--- 2 clickhouse clickhouse   6 5月  13 10:53 detached
-rw-r----- 1 clickhouse clickhouse   1 5月  13 10:53 format_version.txt

自动合并分区后目录结构的变化

[root@node3 t_merge_tree_partition]# ll
总用量 4
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:00 202001_1_1_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:00 202002_2_2_0
drwxr-x--- 2 clickhouse clickhouse 196 5月  13 11:41 202004_3_5_1
drwxr-x--- 2 clickhouse clickhouse   6 5月  13 10:53 detached
-rw-r----- 1 clickhouse clickhouse   1 5月  13 10:53 format_version.txt

-- 再次插入数据
insert into t_merge_tree_partition values (7, 'gg', '2020-04-11 22:33:06');

至此，大家已经知道了分区ID、目录命名和目录合并的相关规则。最后，再用一张完整的示例图作为总结，描述MergeTree分区目录从创建、合并到删除的整个过程，如下图所示：

从上图中应当能够发现，分区目录在发生合并之后，旧的分区目录并没有被立即删除，而是会存留一段时间。但是旧的分区目录已不再是激活状态(active=0)，所以在数据查询时，它们会被自动过滤掉。