clickhouse mergeTree表引擎解析

cyhysr

于 2024-05-09 14:39:23 发布

阅读量890

点赞数 25

文章标签： clickhouse

本文链接：https://blog.csdn.net/cyhysr/article/details/138613259

版权

参照

https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/mergetree

https://clickhouse.com/docs/en/optimize/skipping-indexes

Clickhouse中最强大的表引擎当属MergeTree（合并树）引擎及该系列（*MergeTree）中的其他引擎。

MergeTree 系列的引擎被设计用于将极大量的数据插入到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

主要特点

存储的数据按主键排序，因此可以创建一个小型的稀疏索引来加快数据检索。
可以指定分区键进行分区，查询中指定来分区键时Clickhouse会自动截取分区数据，提高查询性能。
支持数据副本
支持数据采样

建表语句

建表语句语法如下

子句解析

ENGINE —— 引擎名和参数。ENGINE = MergeTree()，MergeTree引擎没有参数。
ORDER BY —— 排序键。可以是一组列的元组或任意的表达式，例如ORDER BY(CounterID, EventDate)。如果没有使用PRIMARY KEY显式指定主键，ClickHouse会使用排序键作为主键。如果不需要排序，可以使用ORDER BY tuple()。
PARTITION BY —— 分区键，可选项。大多数情况下，不需要使用分区键。即使需要，也不需要使用比月更细粒度的分区键。分区不会加快查询（这与ORDER BY表达式不同）。永远也别使用过细粒度的分区键。不要使用客户端指定分区标识符或分区字段名称来对数据进行分区（而是将分区字段标识或名称作为 ORDER BY 表达式的第一列来指定分区）。要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。
PRIMARY KEY —— 如果要选择与排序键不同的主键，在这里指定，可选项。默认情况下主键跟排序键相同。因此，大部分情况下不需要再专门指定一个PRIMARY KEY子句。
SAMPLE BY —— 用于抽样的表达式，可选项。如果要用抽样表达式，主键中必须包含这个表达式。例如SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。
TTL —— 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。表达式中必须至少有一个Date或DateTime类型的列，比如： TTL date + INTERVAL 1 DAY。
SETTINGS —— 控制MergeTree行为的额外参数，可选项。

表数据存储

表由按主键排序的数据片段（DATA PART）组成。

当数据被插入到表中时，会创建多个数据片段并按主键的字典序排序。例如，主键是（CounterID, Date）时，片段中数据首先按CounterID排序，具有相同CounterID的部分按Date排序。

不同分区的数据会被分成不同的片段，ClickHouse在后台合并数据片段以便高效存储。不同分区的数据片段不会进行合并。合并机制并不保证具有相同主键的行全都合并到一个数据片段中。

数据片段可以以 Wide 或 Compact 格式存储。在 Wide 格式下，每一列都会在文件系统中存储为单独的文件，在 Compact 格式下所有列都存储在一个文件中。Compact 格式可以提高插入量少插入频率频繁时的性能。

主键和索引

以(CounterID, Date)为主键。排序好的索引效果如下图所示：

如果指定查询如下：

ClickHouse不要求主键唯一，所以可以插入多条具有相同主键的行。

索引存储的就是标记、标记号，上述示意图就是set类型索引。

主键的选择

主键中列的数量并没有明确的限制。依据数据结构，您可以在主键包含多些或少些列。这样可以：

改善索引的性能
如果当前主键是(a, b)，在下列情况下添加另一个c列会提升性能：

查询会使用c列作为条件
很长的数据范围（index_granularity的数倍）里（a，b）都是相同的值，并且这样的情况很普遍。换言之，就是加入另一列后，可以让查询略过很长的数据范围。

改善数据压缩。clickhouse以主键排序片段数据，所以，数据的一致性越高，压缩越好。

长的主键会对插入性能和内存消耗有负面影响，但主键中额外的列并不影响SELECT查询的性能。

可以使用ORDER BY tuple()语法创建没有主键的表。在这种情况下ClickHouse根据数据插入的顺序存储。如果在使用INSERT 。。。SELECT时希望保持数据的排序，请重置max_insert_threads=1。

想要根据初始顺序进行数据查询，使用单线程查询

选择与排序键不同的主键

Clickhouse可以做到指定一个跟排序键不一样的主键，此时排序键用于在数据片段中进行排序，主键用于在索引文件中进行标记的写入。这种情况下，主键表达式元祖必须是排序键表达式元祖的前缀（即主键为（a，b）），排序列必须为（a，b，**））。

当使用 SummingMergeTree 和 AggregatingMergeTree 引擎时，这个特性非常有用。通常在使用这类引擎时，表里的列分两种：维度和度量。典型的查询会通过任意的 GROUP BY 对度量列进行聚合并通过维度列进行过滤。由于 SummingMergeTree 和 AggregatingMergeTree 会对排序键相同的行进行聚合，所以把所有的维度放进排序键是很自然的做法。但这将导致排序键中包含大量的列，并且排序键会伴随着新添加的维度不断的更新。

在这种情况下合理的做法是，只保留少量的列在主键当中用于提升扫描效率，将维度列添加到排序键中。

对排序键进行 ALTER 是轻量级的操作，因为当一个新列同时被加入到表里和排序键里时，已存在的数据片段并不需要修改。由于旧的排序键是新排序键的前缀，并且新添加的列中没有数据，因此在表修改时的数据对于新旧的排序键来说都是有序的。

索引和分区在查询中的应用

对于SELECT查询，Clickhouse分析是否可以使用索引。如果WHERE/PREWHERE子句具有下面这些表达式（作为完整WHERE条件的一部分或全部）则可以使用索引：进行相等/不相等的比较；对主键列或分区列进行IN运算、有固定前缀的LIKE运算（如name like 'test%'）、函数运算（部分函数适用），以及对上述表达式进行逻辑运算。

因此，在索引键的一个或多个区间上快速地执行查询是可能的。下面例子中，指定标签；指定标签和日期范围；指定标签和日期；指定多个标签和日期范围等执行查询，都会非常快。

当引擎配置如下时：

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate) SETTINGS index_granularity=8192

以下查询

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34

SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)

SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse会依据主键索引剪掉不符合的数据，依据按月分区的分区键剪掉那些不包含符合数据的分区。

下面这个例子中，不会使用索引。

SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

要检查 ClickHouse 执行一个查询时能否使用索引，可设置如下两个参数force_index_by_date 和 force_primary_key 。

使用按月分区的分区列允许只读取包含适当日期区间的数据块，这种情况下，数据块会包含很多天（最多整月）的数据。在块中，数据按主键排序，主键第一列可能不包含日期。因此，仅使用日期而没有用主键字段作为条件的查询将会导致需要读取超过这个指定日期以外的数据。

跳数索引

此索引在CREATE语句的列部分里定义，如下所示

INDEX index_name expr TYPE type(...) GRANULARITY granularity_value

*MergeTree 系列的表可以指定跳数索引。跳数索引是指数据片段按照粒度(建表时指定的index_granularity)分割成小块后，将上述SQL的granularity_value数量的小块组合成一个大的块，对这些大块写入索引信息，这样有助于使用where筛选时跳过大量不必要的数据，减少SELECT需要读取的数据量。

示例

CREATE TABLE table_name

(

u64 UInt64,

i32 Int32,

s String,

...

INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,

INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4

) ENGINE = MergeTree()

...

示例中的索引能让ClickHouse执行下面这些查询时减少读取的数据量。

SELECT count() FROM table WHERE s < 'z'

SELECT count() FROM table WHERE u64 * i32 == 10 AND u64 * length(s) >= 1234

可用的索引类型

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元短语（ngram）的布隆过滤器。只可用在字符串上。可用于优化 equals ， like 和 in 表达式的性能。