ClickHouse（四）表引擎

最新推荐文章于 2024-02-29 09:44:39 发布

Yuan_CSDF

最新推荐文章于 2024-02-29 09:44:39 发布

阅读量2.4k

点赞数

分类专栏： # ClickHouse 文章标签：数据库 database

本文链接：https://blog.csdn.net/Yuan_CSDF/article/details/117601042

版权

ClickHouse 专栏收录该内容

10 篇文章 6 订阅

订阅专栏

官网：表引擎 | ClickHouse文档

表引擎在 ClickHouse 中的作用十分关键，直接决定了数据如何存储和读取、是否支持并发读写、是否支持 index、支持的 query 种类、是否支持主备复制等。

1.表引擎概述

ClickHouse 提供了大约 28 种表引擎，各有各的用途，比如有 Log 系列用来做小表数据分析，MergeTree 系列用来做大数据量分析，而 Integration 系列则多用于外表数据集成。再考虑复制表Replicated 系列，分布式表 Distributed 等，纷繁复杂，新用户上手选择时常常感到迷惑。

ClickHouse表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交，根据场景组合使用。最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。对于大多数正式的任务，推荐使用MergeTree族中的引擎。

Log、Special、Integration 主要用于特殊用途，场景相对有限。MergeTree 系列才是官方主推的存储引擎，支持几乎所有 ClickHouse 核心功能。

一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交。

表引擎（即表的类型）决定了：

数据的存储方式和位置，写到哪里以及从哪里读取数据
支持哪些查询以及如何支持。
并发数据访问。
索引的使用（如果存在）。
是否可以执行多线程请求。
数据复制参数。

2.表引擎类型

2.1.Log系列

Log系列表引擎功能相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。当你需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。

几种Log表引擎的共性是：

数据被顺序append写到磁盘上；
不支持delete、update；
不支持index；
不支持原子性写；
insert会阻塞select操作。

该类型的引擎有：

TinyLog
StripeLog
Log

主要特点：

数据存储在磁盘上。
写入时将数据追加在文件末尾。
不支持突变操作。
不支持索引。意味着 SELECT 在范围查询时效率不高。
非原子地写入数据。如果某些事情破坏了写操作，例如服务器的异常关闭，你将会得到一张包含了损坏数据的表。

它们彼此之间的区别是：是否支持并发读写，性能问题，列存储问题

TinyLog：不支持并发读取数据文件，查询性能较差；格式简单，适合用来暂存中间数据；
StripLog：支持并发读取数据文件，查询性能比 TinyLog 好；将所有列存储在同一个大文件中，减少了文件个数；
Log：支持并发读取数据文件，查询性能比 TinyLog 好；每个列会单独存储在一个独立文件中。

2.2.Integration系列

该系统表引擎主要用于将外部数据导入到 ClickHouse 中，或者在 ClickHouse 中直接操作外部数据源。

Kafka：将Kafka Topic中的数据直接导入到ClickHouse；
MySQL：将Mysql作为存储引擎，直接在ClickHouse中对MySQL表进行select等操作；
JDBC/ODBC：通过指定jdbc、odbc连接串读取数据源；
HDFS：直接读取HDFS上的特定格式的数据文件；

用于与其他的数据存储与处理系统集成的引擎。支持JDBC表引擎、ODBC、HDFS、Kafka、MySQL。

2.3.Special系列

Special系列的表引擎，大多是为了特定场景而定制的。这里也挑选几个简单介绍，不做详述。

Memory：将数据存储在内存中，重启后会导致数据丢失。查询性能极好，适合于对于数据持久性没有要求的1亿一下的小表。在ClickHouse中，通常用来做临时表。
Buffer：为目标表设置一个内存buffer，当buffer达到了一定条件之后会flush到磁盘。
File：直接将本地文件作为数据存储；
Null：写入数据被丢弃、读取数据为空；

包含：关联表引擎、随机数生成、MaterializedView、Null、URL(URL,格式)、内存表、分布（Distributed）、合并（Merge）、字典、文件(输入格式)、用于查询处理的外部数据、缓冲区、视图、设置

例如：Merge引擎：在同一个server上，多个相同结构的物理表，可以被整合成一张大的逻辑表，这张逻辑表的数据，就是包含了这些物理表中的所有数据。

create table my_table_merge(
	id UInt16,
	name String
) ENGINE = Merge(currentDatbase(),'^my_merge');

distributed：在不同的server上，多个相同结构的物理表，可以被整合成一张大的逻辑表，这张逻辑表的数据，就是包含了这些物理表中的所有数据。类似于分布式。插入的数据会分布在不同的server上。

create table my_table_distributed(
	id UInt16,
	name String
) ENGINE = Distributed(perftest_3shards_1replicas,defaultdb1,my_aidtributed,id);

表引擎是ClickHouse设计实现中的一大特色。可以说，是表引擎决定了一张数据表最终的“性格”，比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。ClickHouse拥有非常庞大的表引擎体系，其共拥有合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎。而在这众多的表引擎中，又属合并树（MergeTree）表引擎及其家族系列（*MergeTree）最为强大，在生产环境的绝大部分场景中，都会使用此系列的表引擎。因为只有合并树系列的表引擎才支持主键索引（一级索引，二级索引，跳数索引/稀疏索引）、数据分区、数据副本和数据采样这些特性，同时也只有此系列的表引擎支持ALTER相关操作。

合并树家族自身也拥有多种表引擎的变种。其中MergeTree作为家族中最基础的表引擎，提供了主键索引、数据分区、数据副本和数据采样等基本能力，而家族中其他的表引擎则在MergeTree的基础之上各有所长。例如ReplacingMergeTree表引擎具有删除重复数据的特性，而SummingMergeTree表引擎则会按照排序键自动聚合数据。如果给合并树系列的表引擎加上Replicated前缀，又会得到一组支持数据副本的表引擎，例如ReplicatedMergeTree、ReplicatedReplacingMergeTree、ReplicatedSummingMergeTree等。

合并树表引擎家族如表所示：正交

项目	类别	基础
Replicated 支持数据副本	Replacing Summing Aggregating Collapsing VersionedCollapsing Graghite	MergeTree 基础表引擎

第一：MergeTree 表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。MergeTree 支持所有ClickHouse SQL 语法，但是有些功能与 MySQL 并不一致，比如在MergeTree 中主键并不用于去重。

第二：为了解决 MergeTree 相同主键无法去重的问题，ClickHouse 提供了 ReplacingMergeTree 引擎，用来做去重。ReplacingMergeTree 确保数据最终被去重，但是无法保证查询过程中主键不重复。因为相同主键的数据可能被 shard 到不同的节点，但是 compaction 只能在一个节点中进行，而且optimize 的时机也不确定。

        第三：CollapsingMergeTree 引擎要求在建表语句中指定一个标记列 Sign（插入的时候指定为1，删除的时候指定为-1），后台 Compaction 时会将主键相同、Sign 相反的行进行折叠，也即删除。来消除ReplacingMergeTree 的限制。
        第四：为了解决 CollapsingMergeTree 乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree 表引擎在建表语句中新增了一列 Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且 Version 相同、Sign 相反的行，在 Compaction 时会被删除。
        第五：ClickHouse 通过 SummingMergeTree 来支持对主键列进行预先聚合。在后台 Compaction时，会将主键相同的多行进行 sum 求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能。
        第六：AggregatingMergeTree 也是预先聚合引擎的一种，用于提升聚合计算的性能。与SummingMergeTree 的区别在于：SummingMergeTree 对非主键列进行 sum 聚合，而AggregatingMergeTree 则可以指定各种聚合函数。

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

2.4.1. MergeTree的创建方式

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

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

create table my_table_m1(
    id UInt8,
    name String
) engine = MergeTree()
partition by (id,name)
order by id;

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

3、PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键字段生成一级索引，用于加速表查询。默认情况下，主键与排序键(ORDER BY)相同，所以通常直接使用ORDER BY代为指定主键，无须刻意通过PRIMARY KEY声明。所以在一般情况下，在单个数据片段内，数据与一级索引以相同的规则升序排列。与其他数据库不同，MergeTree主键允许存在重复数据（ReplacingMergeTree可以去重）。

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

省略...
)ENGINE= MergeTree(
ORDER BY(CounterID，EventDate,intHash32(UserID)
SAMPLE BY intHash32(UserID)

5、SETTINGS: index_granularity [选填]：index_granularity对于MergeTree而言是一项非常重要的参数，它表示索引的粒度，默认值为 8192。也就是说，MergeTree的索引在默认情况下，每间隔8192行数据才生成一条索引，其具体声明方式如下所示：

省略...
)ENGINE= MergeTree(
省略...
SETTINGS index_granularity = 8192;

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

6、SETTINGS: index_granularity_bytes [选填]：在19.11版本之前，ClickHouse只支持固定大小的索引间隔，由index_granularity控制，默认为8192。在新版本中，它增加了自适应间隔大小的特性，即根据每一批次写入数据的体量大小，动态划分间隔大小。而数据的体量大小，正是由index_granularity_bytes参数控制的，默认为10M(10×1024×1024)，设置为0表示不启动自适应功能。每条记录 1kb

7、SETTINGS: enable_mixed_granularity_parts [选填]：设置是否开启自适应索引间隔的功能，默认开启。

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

9、SETTINGS: storage_policy [选填]：从19.15 版本开始，MergeTree 提供了多路径的存储策略，为应对大数据量的存储提供了方案。

案例：

create database if not exists mydb;

use mydb;

drop table if exists mydb.my_table_merge1;

create table mydb.my_table_merge1(
    id UInt8,
    name String,
    date DateTime
) engine = MergeTree()
partition by toYYYYMM(date)
order by id;

insert into mydb.my_table_merge1 values (1, 'aa', '2021-01-02 22:14:52'), (2,'bb', '2021-02-02 16:14:52'), (3, 'cc', '2021-01-02 12:45:52');
insert into mydb.my_table_merge1 values (4, 'aa', '2021-03-02 22:14:52');
insert into mydb.my_table_merge1 values (5, 'bb', '2021-03-03 22:14:52');
insert into mydb.my_table_merge1 values (6, 'cc', '2021-03-04 22:14:52');
select * from mydb.my_table_merge1;
insert into mydb.my_table_merge1 values (4, 'aa', '2021-01-02 22:14:52'), (5,'bb', '2021-02-02 16:14:52'), (6, 'cc', '2021-01-02 12:45:52');

结果如图一图二所示：

hadoop1 :) select * from mydb.my_table_merge1;

SELECT * FROM mydb.my_table_merge1

┌─id─┬─name─┬────────────────date─┐
│  1 │ aa   │ 2021-01-02 22:14:52 │
│  3 │ cc   │ 2021-01-02 12:45:52 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────────date─┐
│  4 │ aa   │ 2021-01-02 22:14:52 │
│  6 │ cc   │ 2021-01-02 12:45:52 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────────date─┐
│  2 │ bb   │ 2021-02-02 16:14:52 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────────date─┐
│  5 │ bb   │ 2021-02-02 16:14:52 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────────date─┐
│  4 │ aa   │ 2021-03-02 22:14:52 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────────date─┐
│  5 │ bb   │ 2021-03-03 22:14:52 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────────date─┐
│  6 │ cc   │ 2021-03-04 22:14:52 │
└────┴──────┴─────────────────────┘

9 rows in set. Elapsed: 0.003 sec.

[root@hadoop1 my_table_merge1]# pwd
/var/lib/clickhouse/data/mydb/my_table_merge1
[root@hadoop1 my_table_merge1]# ll -a
total 4
drwxr-x---. 10 root root 188 Jun  5 22:43 .
drwxr-x---.  4 root root  41 Jun  5 22:42 ..
drwxr-x---.  2 root root 221 Jun  5 22:42 202101_1_1_0
drwxr-x---.  2 root root 221 Jun  5 22:43 202101_6_6_0
drwxr-x---.  2 root root 221 Jun  5 22:42 202102_2_2_0
drwxr-x---.  2 root root 221 Jun  5 22:43 202102_7_7_0
drwxr-x---.  2 root root 221 Jun  5 22:42 202103_3_3_0
drwxr-x---.  2 root root 221 Jun  5 22:42 202103_4_4_0
drwxr-x---.  2 root root 221 Jun  5 22:42 202103_5_5_0
drwxr-x---.  2 root root   6 Jun  5 22:42 detached
-rw-r-----.  1 root root   1 Jun  5 22:42 format_version.txt

当执行 optimize table mydb.my_table_merge1 final;后，如下图所示，会新生成202101_1_6_1、202102_2_7_1、202103_3_5_1文件夹。查询合并后的数据显示也会有所变化。

hadoop1 :) select * from mydb.my_table_merge1;

SELECT *
FROM mydb.my_table_merge1

┌─id─┬─name─┬────────────────date─┐
│  1 │ aa   │ 2021-01-02 22:14:52 │
│  3 │ cc   │ 2021-01-02 12:45:52 │
│  4 │ aa   │ 2021-01-02 22:14:52 │
│  6 │ cc   │ 2021-01-02 12:45:52 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────────date─┐
│  2 │ bb   │ 2021-02-02 16:14:52 │
│  5 │ bb   │ 2021-02-02 16:14:52 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────────date─┐
│  4 │ aa   │ 2021-03-02 22:14:52 │
│  5 │ bb   │ 2021-03-03 22:14:52 │
│  6 │ cc   │ 2021-03-04 22:14:52 │
└────┴──────┴─────────────────────┘

9 rows in set. Elapsed: 0.003 sec.

[root@hadoop1 my_table_merge1]# ll -a
total 4
drwxr-x---. 13 root root 248 Jun  5 22:49 .
drwxr-x---.  4 root root  41 Jun  5 22:42 ..
drwxr-x---.  2 root root 221 Jun  5 22:42 202101_1_1_0
drwxr-x---.  2 root root 221 Jun  5 22:49 202101_1_6_1
drwxr-x---.  2 root root 221 Jun  5 22:43 202101_6_6_0
drwxr-x---.  2 root root 221 Jun  5 22:42 202102_2_2_0
drwxr-x---.  2 root root 221 Jun  5 22:49 202102_2_7_1
drwxr-x---.  2 root root 221 Jun  5 22:43 202102_7_7_0
drwxr-x---.  2 root root 221 Jun  5 22:42 202103_3_3_0
drwxr-x---.  2 root root 221 Jun  5 22:49 202103_3_5_1
drwxr-x---.  2 root root 221 Jun  5 22:42 202103_4_4_0
drwxr-x---.  2 root root 221 Jun  5 22:42 202103_5_5_0
drwxr-x---.  2 root root   6 Jun  5 22:42 detached
-rw-r-----.  1 root root   1 Jun  5 22:42 format_version.txt

在这里需要了解下这种目录名称的含义：其组成为PartitionID_ MinBlockNum_MaxBlockNum_Level，以202101_1_6_1为例，202101表示分区目录的ID; 1_6分别表示最小的数据块编号与最大的数据块编号；而最后的_1则表示目前合并的层级。接下来开始分别解释它们的含义：

PartitionID：分区ID
MinBlockNum和MaxBlockNum：顾名思义，最小数据块编号与最大数据块编号。ClickHouse在这里的命名似乎有些歧义，很容易让人与稍后会介绍到的数据压缩块混淆。但是本质上它们毫无关系，这里的BlockNum是一个整型的自增长编号。如果将其设为n的话，那么计数n在单张MergeTree数据表内全局累加，n从1开始，每当新创建一个分区目录时，计数n就会累积加1。对于一个新的分区目录而言，MinBlockNum与MaxBlockNum取值一样，同等于n，例如201905_1_1_0、201906_2_2_0以此类推。但是也有例外，当分区目录发生合并时，对于新产生的合并目录MinBlockNum与MaxBlockNum有着另外的取值规则。
Level：合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄。数值越高表示年龄越大。Level计数与BlockNum有所不同，它并不是全局累加的。对于每一个新创建的分区目录而言，其初始值均为0。之后，以分区为单位，如果相同分区发生合并动作，则在相应分区内计数累积加1。

接下来进入到其中一个目录，以202101_1_6_1为例：

[root@hadoop1 202101_1_6_1]# ll -a
total 48
drwxr-x---.  2 root root 221 Jun  5 22:49 .
drwxr-x---. 13 root root 248 Jun  5 22:49 ..
-rw-r-----.  1 root root 384 Jun  5 22:49 checksums.txt
-rw-r-----.  1 root root  78 Jun  5 22:49 columns.txt
-rw-r-----.  1 root root   1 Jun  5 22:49 count.txt
-rw-r-----.  1 root root  43 Jun  5 22:49 date.bin
-rw-r-----.  1 root root  48 Jun  5 22:49 date.mrk2
-rw-r-----.  1 root root  30 Jun  5 22:49 id.bin
-rw-r-----.  1 root root  48 Jun  5 22:49 id.mrk2
-rw-r-----.  1 root root   8 Jun  5 22:49 minmax_date.idx
-rw-r-----.  1 root root  38 Jun  5 22:49 name.bin
-rw-r-----.  1 root root  48 Jun  5 22:49 name.mrk2
-rw-r-----.  1 root root   4 Jun  5 22:49 partition.dat
-rw-r-----.  1 root root   2 Jun  5 22:49 primary.idx

一张数据表的完整物理结构分为3个层级，依次是数据表目录、分区目录及各分区下具体的数据文件。接下来就逐一介绍它们的作用。

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

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

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

columns format version: 1
3 columns:
`id` UInt8
`name` String
`date` DateTime

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

（5）primary.idx：一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引（通过ORDER BY或者PRIMARY KEY）。借助稀疏索引，在数据查询的时能够排除主键条件范围之外的数据文件，从而有效减少数据扫描范围，加速查询速度。

（6）[Column].bin：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据。由于MergeTree采用列式存储，所以每一个列字段都拥有独立的 .bin 数据文件，并以列字段名称命名（例如CounterID.bin、EventDate.bin等）。

（7）[Column].mrk：列字段标记文件，使用二进制格式存储。标记文件中保存了．bin文件中数据的偏移量信息。标记文件与稀疏索引对齐，又与 .bin 文件一一对应，所以MergeTree通过标记文件建立了primary.idx稀疏索引与 .bin 数据文件之间的映射关系。即首先通过稀疏索引（primary.idx）找到对应数据的偏移量信息（.mrk），再通过偏移量直接从．bin文件中读取数据。由于．mrk标记文件与．bin文件一一对应，所以MergeTree中的每个列字段都会拥有与其对应的．mrk标记文件（例如CounterID.mrk、EventDate.mrk等）。

（8）[Column].mrk2：如果使用了自适应大小的索引间隔，则标记文件会以 .mrk2 命名。它的工作原理和作用与 .mrk 标记文件相同。

（9）partition.dat 与 minmax_[Column].idx：如果使用了分区键，例如 PARTITION BYtoYYYYMM(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。在这些分区索引的作用下，进行数据查询时能够快速跳过不必要的数据分区目录，从而减少最终需要扫描的数据范围。

（10）skp_idx_[Column].idx与skp_idx_[Column].mrk：如果在建表语句中声明了二级索引，则会额外生成相应的二级索引与标记文件，它们同样也使用二进制存储。二级索引在ClickHouse中又称跳数索引，目前拥有minmax、set、ngrambf_v1和tokenbf_v1四种类型。这些索引的最终目标与一级稀疏索引相同，都是为了进一步减少所需扫描的数据范围，以加速整个查询过程。

2.4.2.数据TTL

TTL： time to live 生命周期TTL ，顾名思义，它表示数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据；如果同时设置了列级别和表级别的TTL，则会以先到期的那个为主。

注意：使用当前系统时间来判断时间间隔。比方说今天是2021-06-06。有设置过期时间是1天。有插入2021-06-01的数据，会自动被清理

针对单独的列指定 TTL ：如果超过了 TTL之后，当前类的数据被清空，全部置为默认值
针对表指定 TTL：如果这个TTL到期了，整个表清空

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;

触发TTL的命令：optimize TABLE ttl_table_v1 FINAL;

可以对TTL进行修改：ALTER TABLE ttl_table_v1 MODIFY COLUMN code String TTL create_time + INTERVAL 1 DAY;

2.表级别的TTL

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

可以对TTL进行修改：

同样也可以对表级别的TTL进行修改：ALTER TABLE ttl_table_v2 MODIFY TTL create_time + INTERVAL 3 DAY;

虽然现在没有提供删除 TTL 声明的方法，但是提供了控制全局 TTL 合并任务的启停方法：SYSTEM STOP/START TTL MERGES;

2.4.3.ReplacingMergeTree(去重)

虽然 MergeTree 拥有主键，但是它的主键却没有唯一键的约束。这意味着即便多行数据的主键相同，它们还是能够被正常写入。在某些使用场合，用户并不希望数据表中含有重复的数据。ReplacingMergeTree 就是在这种背景下为了数据去重而设计的，它能够在合并分区时删除重复的数据。它的出现，确实也在一定程度上解决了重复数据的问题。为什么说是“一定程度”？-->基于同一分区，才会进行去重。

创建一张 ReplacingMergeTree 表的方法与创建普通 MergeTree 表无异，只需要替换 Engine：

ENGINE = ReplacingMergeTree(ver);

例如：

CREATE TABLE mydb.replace_table (
    id UInt16,
	name String,
    create_time Date,
    comment Nullable(String)
) ENGINE = ReplacingMergeTree()
PARTITION BY create_time
ORDER BY (id,name)
primary key(id)
SETTINGS index_granularity=8192;
 
 
insert into mydb.replace_table values(0,'张三','2019-12-12', 'a');
insert into mydb.replace_table values(0,'张三','2019-12-12', 'b');
insert into mydb.replace_table values(1,'张三','2019-12-13', 'c');
insert into mydb.replace_table values(1,'李四','2019-12-13', 'd');
insert into mydb.replace_table values(2,'张三','2019-12-14', 'e');

使用optimize TABLE mydb.replace_table FINAL;

总结如下：

使用ORBER BY排序键作为判断重复数据的唯一键，注意primary key只是用来加索引的。例如这里，只会删除第二条数据，第四条数据不会被删除。
只有在合并分区的时候才会触发删除重复数据的逻辑。
以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。
在进行数据去重时，因为分区内的数据已经基于ORBER BY进行了排序，所以能够找到那些相邻的重复数据。
数据去重策略有两种：
1. 如果没有设置ver版本号，则保留同一组重复数据中的最后一行
2. 如果设置了ver版本号，则保留同一组重复数据中ver字段取值最大的那一行

2.4.4.SummingMergeTree

假设有这样一种查询需求：终端用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的（GROUP BY条件明确，且不会随意改变）。

对于这样的查询场景，在ClickHouse中如何解决呢？最直接的方案就是使用 MergeTree 存储数据，然后通过GROUP BY 聚合查询，并利用 SUM 聚合函数汇总结果。这种方案存在两个问题。

存在额外的存储开销：终端用户不会查询任何明细数据，只关心汇总结果，所以不应该一直保存所有的明细数据。
存在额外的查询开销：终端用户只关心汇总结果，虽然MergeTree性能强大，但是每次查询都进行实时聚合计算也是一种性能消耗。

SummingMergeTree 就是为了应对这类查询场景而生的。顾名思义，它能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总合并成一行，这样既减少了数据行，又降低了后续汇总查询的开销。

在先前介绍 MergeTree 原理时曾提及，在 MergeTree 的每个数据分区内，数据会按照 ORDER BY 表达式排序。主键索引也会按照 PRIMARY KEY 表达式取值并排序。而 ORDER BY 可以指代主键，所以在一般情形下，只单独声明 ORDER BY 即可。此时，ORDER BY 与 PRIMARY KEY 定义相同，数据排序与主键索引相同。

如果需要同时定义 ORDER BY 与 PRIMARY KEY，通常只有一种可能，那便是明确希望 ORDER BY 与PRIMARY KEY不同。这种情况通常只会在使用 SummingMergeTree 或 AggregatingMergeTree 时才会出现。这是为何呢？这是因为 SummingMergeTree 与 AggregatingMergeTree 的聚合都是根据ORDER BY 进行的。由此可以引出两点原因：主键与聚合的条件定义分离，为修改聚合条件留下空间。

CREATE TABLE summing_table(
    id String,
    city String,
    v1 UInt32,
    v2 Float64,
    create_time DateTime
) ENGINE=SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id,city)
PRIMARY KEY id;

insert into summing_table values('A001', 'wuhan', 10, 20, '2019-08-10 17:00:00');
insert into summing_table values('A001', 'wuhan', 20, 30, '2019-08-20 17:00:00');
insert into summing_table values('A001', 'zhuhai', 20, 30, '2019-08-10 17:00:00');
insert into summing_table values('A001', 'wuhan', 10, 20, '2019-02-10 09:00:00');
insert into summing_table values('A002', 'wuhan', 60, 50, '2019-10-10 17:00:00');

执行：optimize TABLE mydb.summing_table FINAL；

┌─id───┬─city──┬─v1─┬─v2─┬─────────create_time─┐
│ A001 │ wuhan │ 10 │ 20 │ 2019-02-10 09:00:00 │
└──────┴───────┴────┴────┴─────────────────────┘
┌─id───┬─city───┬─v1─┬─v2─┬─────────create_time─┐
│ A001 │ wuhan  │ 30 │ 50 │ 2019-08-10 17:00:00 │
│ A001 │ zhuhai │ 20 │ 30 │ 2019-08-10 17:00:00 │
└──────┴────────┴────┴────┴─────────────────────┘
┌─id───┬─city──┬─v1─┬─v2─┬─────────create_time─┐
│ A002 │ wuhan │ 60 │ 50 │ 2019-10-10 17:00:00 │
└──────┴───────┴────┴────┴─────────────────────┘

可以看到，第一条数据和第三条数据进行了汇总。而不同分区之间，数据没有被汇总合并。

总结如下：

用ORBER BY排序键作为聚合数据的条件Key。
只有在合并分区的时候才会触发汇总的逻辑。
以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。
如果在定义引擎时指定了columns汇总列（非主键的数值类型字段），则SUM汇总这些列字段；如果未指定，则聚合所有非主键的数值类型字段。
在进行数据汇总时，因为分区内的数据已经基于ORBER BY排序，所以能够找到相邻且拥有相同聚合Key的数据。
在汇总数据时，同一分区内，相同聚合Key的多行数据会合并成一行。其中，汇总字段会进行SUM计算；对于那些非汇总字段，则会使用第一行数据的取值。
支持嵌套结构，但列字段名称必须以Map后缀结尾。嵌套类型中，默认以第一个字段作为聚合Key。除第一个字段以外，任何名称以Key、Id或Type为后缀结尾的字段，都将和第一个字段一起组成复合Key。

2.4.5.AggregatingMergeTree

AggregatingMergeTree没有任何额外的设置参数，在分区合并时，在每个数据分区内，会按照ORDERBY聚合。而使用何种聚合函数，以及针对哪些列字段计算，则是通过定义AggregateFunction数据类型实现的。以下面的语句为例：

CREATE TABLE agg_table(
    id String,
    city String,
    code AggregateFunction(uniq, String),
    value AggregateFunction(sum, UInt32),
    create_time DateTime
)ENGINE= AggregatingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id,city)
PRIMARY KEY id;

由于单独使用会比较繁琐，所有在实际情况中一般使用AggregatingMergeTree的物化视图。如下：

CREATE TABLE agg_table_basic(
    id String,
    city String,
    code String,
    value UInt32
)ENGINE=MergeTree()
PARTITION BY city
ORDER BY (id,city);

通常会使用MergeTree作为底表，用于存储全量的明细数据，并以此对外提供实时查询。接着，新建一张物化视图：

CREATE MATERIALIZED VIEW agg_view
ENGINE= AggregatingMergeTree()
PARTITION BY city
ORDER BY (id,city)
AS SELECT
id,
city,
uniqState(code) AS code,
sumState(value) AS value
FROM agg_table_basic
GROUP BY id,city;

插入语句：

INSERT INTO TABLE agg_table_basic VALUES('A000', 'wuhan', 'code1', 100), ('A000', 'wuhan', 'code2', 200), ('AO00', 'zhuhai', 'code1', 200);

查询操作与结果：

select id ,sumMerge(value),uniqMerge(code) from agg_view GROUP BY id,city;

┌─id───┬─sumMerge(value)─┬─uniqMerge(code)─┐
│ A000 │             300 │               2 │
│ AO00 │             200 │               1 │
└──────┴─────────────────┴─────────────────┘

总结如下：

用ORBER BY排序键作为聚合数据的条件Key。
使用AggregateFunction字段类型定义聚合函数的类型以及聚合的字段。
只有在合并分区的时候才会触发聚合计算的逻辑。
以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并计算，而不同分区之间的数据则不会被计算。
在进行数据计算时，因为分区内的数据已经基于ORBER BY排序，所以能够找到那些相邻且拥有相同聚合Key的数据。
在聚合数据时，同一分区内，相同聚合Key的多行数据会合并成一行。对于那些非主键、非AggregateFunction类型字段，则会使用第一行数据的取值。
AggregateFunction类型的字段使用二进制存储，在写入数据时，需要调用*State函数；而在查询数据时，则需要调用相应的*Merge函数。其中，*表示定义时使用的聚合函数。
AggregatingMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使用。

2.4.6.VersionedCollapsingMergeTree

在定义 VersionedCollapsingMergeTree 的时候，除了需要指定 sign 标记字段以外，还需要指定一个UInt8 类型的 ver 版本号字段：

CREATE TABLE ver_collpase_table(
    id String,
    code Int32,
    create_time DateTime,
    sign Int8,
    ver UInt8
)ENGINE = VersionedCollapsingMergeTree(sign,ver)
PARTITION BY toYYYYMM(create_time)
ORDER BY id;

在定义 ver 字段之后，VersionedCollapsingMergeTree 会自动将ver作为排序条件并增加到 ORDER BY 的末端。以上面的ver_collpase_table 表为例，在每个数据分区内，数据会按照 ORDER BY id , ver DESC排序。所以无论写入时数据的顺序如何，在折叠处理时，都能回到正确的顺序。

案例：这里是根据order by字段进行merge。

INSERT INTO TABLE ver_collpase_table VALUES('A000',101,'2019-02-20 00:00:00',-1, 1);
INSERT INTO TABLE ver_collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1, 1);
INSERT INTO TABLE ver_collpase_table VALUES('A000',101,'2019-02-20 00:00:00',-1, 1);
INSERT INTO TABLE ver_collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1, 1);
INSERT INTO TABLE ver_collpase_table VALUES('A000',103,'2019-02-20 00:00:00',1, 2);
INSERT INTO TABLE ver_collpase_table VALUES('A000',103,'2019-02-20 00:00:00',-1, 2);

即在同一版本中的数据才能被删除。例如，只运行第五条数据和第一条数据，则不会被删除。

总结：

支持数据去重的ReplacingMergeTree
支持预先聚合计算的SummingMergeTree与AggregatingMergeTree，
以及支持数据更新且能够折叠数据的CollapsingMergeTree与VersionedCollapsingMergeTree。
这些MergeTree系列的表引擎，都用ORDER BY作为条件Key，在分区合并时触发各自的处理逻辑。

Yuan_CSDF

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
ClickHouse（四）表引擎

官网：https://clickhouse.tech/docs/zh/engines/table-engines/表引擎在 ClickHouse 中的作用十分关键，直接决定了数据如何存储和读取、是否支持并发读写、是否支持 index、支持的 query 种类、是否支持主备复制等。1.表引擎概述ClickHouse 提供了大约 28 种表引擎，各有各的用途，比如有 Log 系列用来做小表数据分析，MergeTree 系列用来做大数据量分析，而 Integration 系列则多用于外表数据集成。再考
复制链接

扫一扫