ClickHouse引擎详解

一、引擎详解
二、视图
- 1 、普通视图
- 2 、物化视图

一、引擎详解

表引擎是ClickHouse设计实现中的一大特色 ,数据表拥有何种特性、数据以何种形式被存储以及如何被加载都是由表引擎决定。ClickHouse拥有非常庞大的表引擎体系，其共拥有合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎。而在这众多的表引擎中，又属合并树（MergeTree）表引擎及其家族系列（*MergeTree）最为强大，在生产环境的绝大部分场景中，都会使用此系列的表引擎。

因为只有合并树系列的表引擎才支持主键索引、数据分区、数据副本和数据采样这些特性，同时也只有此系列的表引擎支持ALTER相关操作。

合并树家族自身也拥有多种表引擎的变种。

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

。

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

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

1、Log系列引擎

Log家族具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。

（1）TinyLog引擎

最简单的表引擎，用于将数据存储在磁盘上。

每列都存储在单独的压缩文件中，写入时，数据将附加到文件末尾。该引擎没有并发控制

最简单的引擎
没有索引，没有标记块
写是追加写
数据以列字段文件存储
不允许同时读写

-- 建表 
create table test_tinylog(
	id UInt8 ,
    name String ,
    age UInt8
)engine=TinyLog ;
-- 查看表结构
desc test_tinylog ;
-- 查看建表语句 
SHOW CREATE TABLE test_tinylog ;
-- 插入数据 
insert into test_tinylog values(1,'liubei',45),(2,'guanyu',43),(3,'zhangfei',41) ;

SELECT *
FROM test_tinylog

┌─id─┬─name─────┬─age─┐
│  1 │ liubei   │  45 │
│  2 │ guanyu   │  43 │
│  3 │ zhangfei │  41 │
└────┴──────────┴─────┘

查看数据底层存储

[root@doit01 test_tinylog]# pwd
/var/lib/clickhouse/data/default/test_tinylog
-rw-r-----. 1 clickhouse clickhouse 29 May 19 15:29 age.bin
-rw-r-----. 1 clickhouse clickhouse 29 May 19 15:29 id.bin
-rw-r-----. 1 clickhouse clickhouse 50 May 19 15:29 name.bin
-rw-r-----. 1 clickhouse clickhouse 90 May 19 15:29 sizes.json
-- 当再次插入数据以后 , 在每个文件中追加写入的
-rw-r-----. 1 clickhouse clickhouse  58 May 19 15:31 age.bin
-rw-r-----. 1 clickhouse clickhouse  58 May 19 15:31 id.bin
-rw-r-----. 1 clickhouse clickhouse 100 May 19 15:31 name.bin
-rw-r-----. 1 clickhouse clickhouse  91 May 19 15:31 sizes.json

（2）StripeLog引擎

data.bin存储所有数据
index.mrk 对数据建立索引
size.json 数据大小
并发读写

create table test_stripelog(
	id UInt8 ,
    name String ,
    age UInt8
)engine=StripeLog ;
-- 插入数据 
insert into test_stripelog values(1,'liubei',45),(2,'guanyu',43),(3,'zhangfei',41) ;

查看底层数据

/var/lib/clickhouse/data/default/test_stripelog
-rw-r-----. 1 clickhouse clickhouse 167 May 19 15:43 data.bin  存储所有列的数据
-rw-r-----. 1 clickhouse clickhouse  75 May 19 15:43 index.mrk  记录数据的索引信息
-rw-r-----. 1 clickhouse clickhouse  68 May 19 15:43 sizes.json  记录文件内容的大小

（3）Log引擎

日志与 TinyLog 的不同之处在于，«标记» 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。对于并发数据访问，可以同时执行读取操作，而写入操作则阻塞读取和其它写入。Log 引擎不支持索引。同样，如果写入表失败，则该表将被破坏，并且从该表读取将返回错误。Log 引擎适用于临时数据，write-once 表以及测试或演示目的。

数据不多做一些测试存一些中间结果可以使用log引擎

*.bin文件存储每个字段的数据
mark.mrk 数据块标记
支持多线程处理
并发读写

drop table if exists test_log ;
create table test_log(
	id UInt8 ,
    name String ,
    age UInt8
)engine=Log ;

insert into test_log values(1,'liubei',45),(2,'guanyu',43),(3,'zhangfei',41) ;

查看底层数据

-rw-r-----. 1 clickhouse clickhouse  29 May 19 15:46 age.bin
-rw-r-----. 1 clickhouse clickhouse  29 May 19 15:46 id.bin
-rw-r-----. 1 clickhouse clickhouse  48 May 19 15:46 __marks.mrk  *** 
-rw-r-----. 1 clickhouse clickhouse  50 May 19 15:46 name.bin
-rw-r-----. 1 clickhouse clickhouse 120 May 19 15:46 sizes.json

（4）三种Log系列引擎对比

Log 和 StripeLog 引擎支持：

并发访问数据的锁。
INSERT
请求执行过程中表会被锁定，并且其他的读写数据的请求都会等待直到锁定被解除。如果没有写数据的请求，任意数量的读请求都可以并发执行。
并行读取数据。在读取数据时，ClickHouse 使用多线程。每个线程处理不同的数据块。

Log 引擎为表中的每一列使用不同的文件。StripeLog 将所有的数据存储在一个文件中。因此 StripeLog 引擎在操作系统中使用更少的描述符，但是 Log 引擎提供更高的读性能。

TinyLog 引擎是该系列中最简单的引擎并且提供了最少的功能和最低的性能。TingLog 引擎不支持并行读取和并发数据访问，并将每一列存储在不同的文件中。它比其余两种支持并行读取的引擎的读取速度更慢，并且使用了和 Log 引擎同样多的描述符。你可以在简单的低负载的情景下使用它。

2、MergeTree系列引擎

MergeTree系列的表引擎是ClickHouse数据存储功能的核心。

它们提供了用于弹性和高性能数据检索的大多数功能：列存储，自定义分区，稀疏的主索引，辅助数据跳过索引等。

基本

MergeTree

表引擎可以被认为是单节点ClickHouse实例的默认表引擎，因为它在各种用例中通用且实用。

除了基础表引擎MergeTree之外，常用的表引擎还有ReplacingMergeTree、SummingMergeTree、 AggregatingMergeTree、CollapsingMergeTree和 VersionedCollapsingMergeTree。每一种合并树的变种，在继承了基础MergeTree的能力之后，又增加了独有的特性。其名称中的“合并” 二字奠定了所有类型MergeTree的基因，它们的所有特殊逻辑，都是在触发合并的过程中被激活的。

主要特点：

存储按

主键排序

的数据。

这使您可以创建一个小的稀疏索引，以帮助更快地查找数据。
如果指定了[分区键]则可以使用[分区]。

ClickHouse支持的某些分区操作比对相同数据，相同结果的常规操作更有效。ClickHouse还会自动切断在查询中指定了分区键的分区数据。这也提高了查询性能。
数据复制支持。

ReplicatedMergeTree表族提供数据复制。
数据采样支持。

如有必要，可以在表中设置数据采样方法。

（一）MergeTree引擎

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

在建表时，只设置主键（可设置多个），不设置排序字段，则排序字段为主键（主键为多个时，排序字段也为多个）。

如果不配置primary key，clickhouse会将order by的字段作为primary key。

主键和排序字段必须设置一个。

主键和排序字段这两个属性只设置一个时，另一个默认与它相同。

当两个都设置时，PRIMARY KEY必须为ORDER BY的前缀，这种约定保障了即使在ORDER BY与PRIMARY KEY不同的时候，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。

根据个人猜测，primary key的意义可能是为了控制primary.idx文件的大小。order by 决定了数据写入文件的顺序，primary key 决定了索引文件primary.idx的大小。如果order by指定的字段过大，可能会导致索引文件超级大，影响查询效率。此时可以通过单独指定primary key来解决这个问题。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster1](
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1, --索引粒度，默认8192
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2) 
ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...][SETTINGS name=value, ...]

MergeTree表引擎除了常规参数之外，还拥有一些独有的配置选项。接下来会着重介绍其中几个重要的参数

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

（2）

ORDER BY [必填]：排序键，用于指定在一个数据片段内（区内排序），数据以何种标准排序

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

（3）PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键字段生成一级索引，用于加速表查询。

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

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

与其他数据库不同，MergeTree 主键允许存在重复数据（ReplacingMergeTree可以去重）

。

（4）SAMPLE BY [选填]：抽样表达式，用于声明数据以何种标准进行采样。如果使用了此配置项，那么在主键的配置中也需要声明同样的表达式，例如：

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

（5）SETTINGS：index_granularity [选填]： index_granularity对于MergeTree而言是一项非常重要的参数，它表示索引的粒度，默认值为

8192

。也就是说，

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

，其具体声明方式如下所示：

index1 —–> data1

index2——>data2

index3——>data3

省略... 
 ) 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表示不启动自适应功能。

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

（8）SETTINGS：merge_with_ttl_timeout [选填]：从19.6版本开始，MergeTree提供了数据TTL的功能。

（9）SETTINGS：storage_policy [选填]：从19.15版本开始， MergeTree提供了多路径的存储策略。

（1）创建表

查看建表语句：show create table tb_orders3;

drop table if exists tb_merge_tree ;
create table tb_merge_tree(
id Int8 ,
city String ,
ctime Date 
)
engine=MergeTree()
order by id 
partition by city ;
-- 查看建表语句
│ CREATE TABLE default.tb_merge_tree
(
    `id` Int8,
    `city` String,
    `ctime` Date
)
ENGINE = MergeTree()
PARTITION BY city
ORDER BY id
SETTINGS index_granularity = 8192 │

（2）导入数据

insert into tb_merge_tree values(1,'BJ',now()) ,(2,'NJ',now()),(3,'DJ',now());
insert into tb_merge_tree values(4,'BJ',now()) ,(5,'NJ',now()),(6,'DJ',now());
insert into tb_merge_tree values(7,'BJ',now()) ,(8,'NJ',now()),(9,'DJ',now());
insert into tb_merge_tree values(10,'BJ',now()) ,(11,'NJ',now()),(12,'DJ',now());
┌─id─┬─city─┬──────ctime─┐
│  9 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  2 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  5 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│ 12 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  8 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│ 11 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  1 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  3 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  4 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  6 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  7 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│ 10 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘

（3）合并数据

每次插入数据都会生成一个文件，系统默认每个分区5个文件一合并，也可用以下命令手动合并。

optimize table tb_merge_tree final ;

optimize：优化

final ：所有分区合并，不加 final 只合并一个分区

MergeTree引擎合并数据并不会去重

optimize table tb_merge_tree final ;  一次性按照分区合并所有的数据
SELECT *
FROM tb_merge_tree
┌─id─┬─city─┬──────ctime─┐
│  3 │ DJ   │ 2021-05-19 │
│  6 │ DJ   │ 2021-05-19 │
│  9 │ DJ   │ 2021-05-19 │
│ 12 │ DJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  2 │ NJ   │ 2021-05-19 │
│  5 │ NJ   │ 2021-05-19 │
│  8 │ NJ   │ 2021-05-19 │
│ 11 │ NJ   │ 2021-05-19 │
└────┴──────┴────────────┘
┌─id─┬─city─┬──────ctime─┐
│  1 │ BJ   │ 2021-05-19 │
│  4 │ BJ   │ 2021-05-19 │
│  7 │ BJ   │ 2021-05-19 │
│ 10 │ BJ   │ 2021-05-19 │
└────┴──────┴────────────┘

CK内部会自动的合并分区的数据, 也会删除多余的文件夹中的数据

（4）数据存储原理

在这里插入图片描述

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

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

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

（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 BY EventTime，则会额外生成partition.dat与 minmax索引文件，它们均使用二进制格式存储。partition.dat用于保存当前分区下分区表达式最终生成的值；而minmax索引用于记录当前分区下分区字段对应原始数据的最小和最大值。例如EventTime字段对应的原始数据为2019-05-01、2019-05-05，分区表达式为PARTITION BY toYYYYMM(EventTime)。partition.dat中保存的值将会是2019-05，而 minmax索引中保存的值将会是2019-05-012019-05-05。

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

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

（二）ReplacingMergeTree引擎

这个引擎是在 MergeTree 的基础上，

添加了“处理重复数据”的功能

，该引擎和MergeTree的不同之处在于它会删除具有相同(区内)

排序一样

的重复项。数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行(手动合并)，所以你无法预先作出计划。有一些数据可能仍未被处理。因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，

但是它不保证没有重复的数据出现

。

ReplacingMergeTree引擎以去重实现了数据更新，但它做不到删除数据

若排序字段为两个，则两个字段都相同时才会去重

可以在查询数据时在表名后面加上final关键字，就会在查询数据时对数据去重

（1）无版本参数

根据数据的插入时间 , 后插入的数据保留

由于系统对CK的操作是多线程执行的, 所以不能保证数据插入的顺序 , 就可能出现数据删除错乱的现象

drop table if  exists test_replacingMergeTree1 ;
create table test_replacingMergeTree1(
	oid Int8 ,
	ctime DateTime ,
    cost Decimal(10,2)
)engine = ReplacingMergeTree()
order by oid 
partition by toDate(ctime) ;
-- 天分区 同一天的oid相同的数据会被去重

-- 插入数据 
insert into test_replacingMergeTree1 values(3,'2021-01-01 11:11:11',30) ;
insert into test_replacingMergeTree1 values(1,'2021-01-01 11:11:14',40) ;
insert into test_replacingMergeTree1 values(1,'2021-01-01 11:11:11',10);
insert into test_replacingMergeTree1 values(2,'2021-01-01 11:11:11',20) ;
insert into test_replacingMergeTree1 values(1,'2021-01-02 11:11:11',41) ;

-- 优化合并
optimize table test_replacingMergeTree1 final ;
┌─oid─┬───────────────ctime─┬──cost─┐
│   1 │ 2021-01-02 11:11:11 │ 41.00 │
└─────┴─────────────────────┴───────┘
┌─oid─┬───────────────ctime─┬──cost─┐
│   1 │ 2021-01-01 11:11:11 │ 10.00 │
│   2 │ 2021-01-01 11:11:11 │ 20.00 │
│   3 │ 2021-01-01 11:11:11 │ 30.00 │
└─────┴─────────────────────┴───────┘

（2）有版本参数

设置版本参数后，在分区合并时会去重并留下版本号最大的那份数据

版本字段可以是数值
版本字段可以是时间

drop table if  exists test_replacingMergeTree3 ;
create table test_replacingMergeTree3(
	oid Int8 ,
	ctime DateTime ,
    cost Decimal(10,2)
)engine = ReplacingMergeTree(ctime)
order by oid 
partition by toDate(ctime) ;

insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:11',10) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:12',20) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:10',30);
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:19',40) ;
insert into test_replacingMergeTree3 values(1,'2021-01-01 11:11:13',50) ;
-- 合并数据以后 保留的是时间最近的一条数据
┌─oid─┬───────────────ctime─┬──cost─┐
│   1 │ 2021-01-01 11:11:19 │ 40.00 │
└─────┴─────────────────────┴───────┘

（3）总结

（1）使用ORDER BY排序键作为判断重复数据的唯一依据。

（2）只有在合并分区的时候才会触发删除重复数据的逻辑。

（3）以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。

（4）在进行数据去重时，因为分区内的数据已经基于ORBER BY进行了排序，所以能够找到那些相邻的重复数据。

（5）数据去重策略有两种：

如果没有设置ver版本号，则保留同一组重复数据中的最后一行。
如果设置了ver版本号，则保留同一组重复数据中ver字段取值最大的那一行。

使用这个引擎可以实现数据的更新

（三） CollapsingMergeTree引擎

CollapsingMergeTree就是一种通过

以增代删

的思路，支持行级数据修改和删除的表引擎。

它通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除

。这种1和-1相互抵消的操作，犹如将一张瓦楞纸折叠了一般。这种直观的比喻，想必也正是折叠合并树（CollapsingMergeTree）名称的由来，

多行的排序相同的状态为1的数据会折叠成一行

,

保留最后一行

多行的排序相同的状态为-1的数据会折叠成一行

,

保留第一行

两行排序相同的数据, 状态为 1 和 -1 删除这两行数据

两行排序相同的数据, 状态为 -1 和 1 不会删除

sign标记的字段只能插入1或-1，否则报错

与 ReplacingMergeTree 引擎一样，CollapsingMergeTree 也是使用ORDER BY排序键作为判断重复数据的唯一依据。若排序字段为两个，则两个字段都相同时才会删除、去重

ENGINE = CollapsingMergeTree(sign)

drop table if exists tb_cps_merge_tree1 ;
CREATE TABLE tb_cps_merge_tree1
(
    user_id UInt64,
    name String,
    age UInt8,
    sign Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY user_id;
-- 插入数据 
insert into tb_cps_merge_tree1 values(1,'xiaoluo',23,1),(2,'xiaoyu',24,1),(3,'xiaofeng',25,1) ;
insert into tb_cps_merge_tree1 values(1,'xiaoluo_',23,-1),(2,'xiaoyu_',24,-1),(3,'xiaofeng2',25,1) ;
-- 合并优化
optimize table tb_cps_merge_tree1 ;
-- 实现了数据的删除和已经存在数据的更新
SELECT *
FROM tb_cps_merge_tree1
┌─user_id─┬─name──────┬─age─┬─sign─┐
│       3 │ xiaofeng2 │  25 │    1 │
└─────────┴───────────┴─────┴──────┘

CollapsingMergeTree虽然解决了主键相同的数据即时删除的问题，但是状态持续变化且多线程并行写入情况下，状态行与取消行位置可能乱序，导致无法正常折叠。只有保证老的状态行在在取消行的上面, 新的状态行在取消行的下面! 但是多线程无法保证写的顺序!

（四）VersionedCollapsingMergeTree引擎

为了

解决CollapsingMergeTree乱序写入情况下无法正常折叠(删除)问题

，

VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键(排序)相同，且Version相同、Sign相反的行，在Compaction时会被删除。

与CollapsingMergeTree类似，为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

drop table if exists tb_vscmt ;
CREATE TABLE tb_vscmt
(
    uid UInt64,
    name String,
    age UInt8,
    sign Int8,
    version UInt8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY uid;

INSERT INTO tb_vscmt VALUES (1001, 'ADA', 18, -1, 1);
INSERT INTO tb_vscmt VALUES (1001, 'ADA', 18, 1, 1),(101, 'DAD', 19, 1, 1),(101, 'DAD', 11, 1, 3); 
INSERT INTO tb_vscmt VALUES(101, 'DAD', 11, 1, 2) ;
-- 可以保证要删除的数据会被删除, 没有折叠标记的数据会被保留
optimize table tb_vscmt ; 
┌─uid─┬─name─┬─age─┬─sign─┬─version─┐
│ 101 │ DAD  │  19 │    1 │       1 │
│ 101 │ DAD  │  11 │    1 │       2 │
│ 101 │ DAD  │  11 │    1 │       3 │
└─────┴──────┴─────┴──────┴─────────┘

--不会去重
INSERT INTO tb_vscmt VALUES (1001, 'ADA', 18, 1, 1);
INSERT INTO tb_vscmt VALUES (1001, 'ADA', 18, 1, 1);
INSERT INTO tb_vscmt VALUES (1001, 'ADA', 18, 1, 2);
┌──uid─┬─name─┬─age─┬─sign─┬─version─┐
│ 1001 │ ADA  │  18 │    1 │       1 │
│ 1001 │ ADA  │  18 │    1 │       1 │
│ 1001 │ ADA  │  18 │    1 │       2 │
└──────┴──────┴─────┴──────┴─────────┘

版本一致排序相同且标记相反的数据会被删除。相同排序相同标记相同版本的数据不会去重。要更新数据只能先插入标记相反的数据将原来数据删掉，在插入新版本数据。

（五）SummingMergeTree引擎

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

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

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

SummingMergeTree就是为了应对这类查询场景而生的。顾名思义，

它能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分区下的相同排序的多行数据汇总合并成一行，这样既减少了数据行，又降低了后续汇总查询的开销。

drop table summing_table ;
CREATE TABLE summing_table( 
id String, 
city String, 
sal UInt32, 
comm Float64, 
ctime DateTime 
)ENGINE = SummingMergeTree() 
PARTITION BY toDate(ctime) 
ORDER BY (id, city) 
PRIMARY KEY id ;
-- 在合并的时候 ,分区内, 相同排序的行数据的所有的数值字段都会求和(sum)
-- 插入数据
insert into summing_table
values 
(1,'shanghai',10,20,'2021-06-12 01:11:12'),
(1,'shanghai',20,30,'2021-06-12 01:11:12'),
(3,'shanghai',10,20,'2021-11-12 01:11:12'),
(3,'Beijing',10,20,'2021-11-12 01:11:12') ;

optimize table summing_table ;

┌─id─┬─city─────┬─sal─┬─comm─┬───────────────ctime─┐
│ 3  │ Beijing  │  10 │   20 │ 2021-11-12 01:11:12 │
│ 3  │ shanghai │  10 │   20 │ 2021-11-12 01:11:12 │
└────┴──────────┴─────┴──────┴─────────────────────┘
┌─id─┬─city─────┬─sal─┬─comm─┬───────────────ctime─┐
│ 1  │ shanghai │  30 │   50 │ 2021-06-12 01:11:12 │
└────┴──────────┴─────┴──────┴─────────────────────┘

上面的例子中没有指定sum的字段 ,那么表中符合要求的所有的数值字段都会进行求和 ,我们可以在建表的时候指定求和的字段

drop table summing_table2 ;
CREATE TABLE summing_table2( 
id String, 
city String, 
money UInt32, 
num UInt32, 
ctime DateTime 
)ENGINE = SummingMergeTree(money) --指定一个字段
PARTITION BY toDate(ctime) 
ORDER BY city ;
--每个城市每天的销售总额 
insert into summing_table2 values(1,'BJ',100,11,now()),
(2,'BJ',100,11,now()),
(3,'BJ',100,11,now()),
(4,'NJ',100,11,now()),
(5,'NJ',100,11,now()),
(6,'SH',100,11,now()),
(7,'BJ',100,11,'2021-05-18 11:11:11'),
(8,'BJ',100,11,'2021-05-18 11:11:11') ;

SELECT * 
FROM summing_table2 ;
┌─id─┬─city─┬─money─┬─num─┬───────────────ctime─┐
│ 1  │ BJ   │   300 │  11 │ 2021-05-19 21:53:49 │
│ 4  │ NJ   │   200 │  11 │ 2021-05-19 21:53:49 │
│ 6  │ SH   │   100 │  11 │ 2021-05-19 21:53:49 │
└────┴──────┴───────┴─────┴─────────────────────┘
┌─id─┬─city─┬─money─┬─num─┬───────────────ctime─┐
│ 7  │ BJ   │   200 │  11 │ 2021-05-18 11:11:11 │
└────┴──────┴───────┴─────┴─────────────────────┘

drop table summing_table2 ;
CREATE TABLE summing_table2( 
id String, 
city String, 
money UInt32, 
num UInt32, 
num2 UInt32,
ctime DateTime 
)ENGINE = SummingMergeTree((money,num)) --指定多个字段  注意用两个括号
PARTITION BY toDate(ctime) 
ORDER BY city ;

insert into summing_table2 values('1','shanghai',10,10,10,now()),('1','shanghai',10,10,10,now())
--只对指定字段做了聚合
┌─id─┬─city─────┬─money─┬─num─┬─num2─┬───────────────ctime─┐
│ 1  │ shanghai │    20 │  20 │   10 │ 2021-09-27 21:12:06 │
└────┴──────────┴───────┴─────┴──────┴─────────────────────┘

支持嵌套格式的求和操作

CREATE TABLE summing_table_nested( 
id String, 
nestMap Nested( 
id UInt32, 
key UInt32, 
val UInt64 
), 
create_time DateTime 
)ENGINE = SummingMergeTree() 
PARTITION BY toYYYYMM(create_time) 
ORDER BY id ;

总结:

（1）用ORBER BY排序键作为聚合数据的条件Key。

（2）只有在合并分区的时候才会触发汇总的逻辑。

（3）以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。

（4）如果在定义引擎时指定了columns汇总列（非主键的数值类型字段）SummingMergeTree((col1,col2,…))，则SUM汇总这些列字段；如果未指定，则聚合所有非主键的数值类型字段。

（5）在进行数据汇总时，因为分区内的数据已经基于ORBER BY排序，所以能够找到相邻且拥有相同聚合Key的数据。

（6）在汇总数据时，同一分区内，相同聚合Key的多行数据会合并成一行。其中，汇总字段会进行SUM计算；对于那些非汇总字段，则会使用第一行数据的取值。

（7）支持嵌套结构，但列字段名称必须以Map后缀结尾。嵌套类型中，默认以第一个字段作为聚合Key。除第一个字段以外，任何名称以Key、Id或Type为后缀结尾的字段，都将和第一个字段一起组成复合 Key。

（8）主键、排序字段、分区字段不会参与运算

在这里插入图片描述

（六）AggregatingMergeTree引擎

AggregatingMergeTree就有些许

数据立方体

的意思，它能够在合并分区的时候，按照预先定义的条件聚合数据。同时，根据预先定义的聚合函数计算数据并通过二进制的格式存入表内。将同一分组下的多行数据聚合成一行，既减少了数据行，又降低了后续聚合查询的开销。可以说，AggregatingMergeTree 是

SummingMergeTree的升级版

，它们的许多设计思路是一致的，例如同时定义 ORDER BY与PRIMARY KEY的原因和目的。但是在使用方法上，两者存在明显差异，应该说AggregatingMergeTree的定义方式是MergeTree家族中最为特殊的一个。

SummingMergeTree只能对数据进行求和，而AggregatingMergeTree还可以指定其他聚合函数

NGINE = AggregatingMergeTree()

AggregatingMergeTree没有任何额外的设置参数，在分区合并时，在每个数据分区内，会按照ORDER BY聚合。而使用何种聚合函数，以及针对哪些列字段计算，则是通过定义AggregateFunction数据类型实现的。在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法。

AggregateFunction(arg1 , arg2) ;

参数一聚合函数

参数二数据类型

sum_cnt AggregateFunction(sum, Int64) ;

先创建原始表 —插入数据—> 创建预先聚合表 –通过Insert的方式导入数据, 数据会按照指定的聚合函数聚合预先数据!

预先聚合表字段可以比原始表少，但是字段类型必须对上。

排序字段、分区字段不能当作聚合字段。

往聚合表插入数据时，字段类型必须对上，除了聚合字段，聚合表的其余字段都必须加到group by条件里，也可以加上原始表的其他字段。聚合字段固定写法：聚合函数State(聚合字段)

查询聚合表数据时，除了聚合字段，聚合表的其余字段都必须加到group by条件里，聚合字段固定写法：聚合函数Merge(聚合字段)。

定义表时字段定义的 AggregateFunction(sum, UInt64) 这种类型，在插入数据时必须得用相同函数的sumState聚合

在这里插入图片描述

-- 1)建立明细表
CREATE TABLE detail_table
(id UInt8,
 ctime Date,
 money UInt64
) ENGINE = MergeTree() 
PARTITION BY toDate(ctime) 
ORDER BY id;

-- 2)插入明细数据
INSERT INTO detail_table VALUES(1, '2021-08-06', 100);
INSERT INTO detail_table VALUES(1, '2021-08-06', 100);
INSERT INTO detail_table VALUES(2, '2021-08-07', 200);
INSERT INTO detail_table VALUES(2, '2021-08-07', 200);

-- 3)建立预先聚合表，
-- 注意：其中UserID一列的类型为：AggregateFunction(uniq, UInt64)
CREATE TABLE agg_table
(id UInt8,
ctime Date,
money AggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree() 
PARTITION BY  toDate(ctime) 
ORDER BY id;

-- 4) 从明细表中读取数据，插入聚合表。
-- 注意：子查询中使用的聚合函数为 uniqState， 对应于写入语法<agg>-State
INSERT INTO agg_table
select id, ctime, uniqState(money)
from detail_table
group by id, ctime ;

-- 不能使用普通insert语句向AggregatingMergeTree中插入数据。
-- 本SQL会报错：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)
INSERT INTO agg_table VALUES(1, '2020-08-06', 1);

-- 5) 从聚合表中查询。
-- 注意：select中使用的聚合函数为uniqMerge，对应于查询语法<agg>-Merge
SELECT
id, ctime ,
uniqMerge(uid) AS state 
FROM agg_table 
GROUP BY id, ctime;

使用物化视图同步聚合数据

-- 建立明细表
drop table orders ;
CREATE TABLE orders
(
    uid UInt64,
    money UInt64,
    ctime Date,
    Sign Int8
)
ENGINE = MergeTree()
ORDER BY uid;

--插入数据 
insert into orders values(1,100,toDate(now()),1) ;
insert into orders values(1,100,toDate(now()),1) ;
insert into orders values(1,100,toDate(now()),1) ;
insert into orders values(2,200,toDate(now()),1) ;
insert into orders values(2,200,toDate(now()),1) ;
insert into orders values(2,200,toDate(now()),1) ;
insert into orders values(2,100,toDate(now()),1) ;
-- 将聚合逻辑创建成物化视图
CREATE MATERIALIZED VIEW orders_agg_view
ENGINE = AggregatingMergeTree() 
PARTITION BY toDate(ctime)
ORDER BY uid 
populate
as select 
uid ,
ctime ,
sumState(money) as mm  -- 注意别名 
from 
orders
group by uid , ctime;

-- 查询物化视图数据
select uid,ctime,sumMerge(mm) from orders_agg_view group by uid, ctime ;

-- 更新明细数据, 物化视图中的数据实时计算更新
insert into orders values(1,100,toDate(now()),1);
┌─uid─┬──────ctime─┬─sumMerge(mm)─┐
│   2 │ 2021-05-19 │          400 │
│   1 │ 2021-05-19 │          200 │
└─────┴────────────┴──────────────┘
┌─uid─┬──────ctime─┬─sumMerge(mm)─┐
│   2 │ 2021-05-19 │          400 │
│   1 │ 2021-05-19 │          300 │
└─────┴────────────┴──────────────┘
┌─uid─┬──────ctime─┬─sumMerge(mm)─┐
│   2 │ 2021-05-19 │          400 │
│   1 │ 2021-05-19 │          400 │
└─────┴────────────┴──────────────┘

总结:

（1）用ORBER BY排序键作为聚合数据的条件Key。

（2）使用AggregateFunction字段类型定义聚合函数的类型以及聚合的字段。

（3）只有在合并分区的时候才会触发聚合计算的逻辑。

（4）以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合 Key相同的数据会被合并计算，而不同分区之间的数据则不会被计算。

（5）在进行数据计算时，因为分区内的数据已经基于ORBER BY排序，所以能够找到那些相邻且拥有相同聚合Key的数据。

（6）在聚合数据时，同一分区内，相同聚合Key的多行数据会合并成一行。对于那些非主键、非AggregateFunction类型字段，则会使用第一行数据的取值。

（7）AggregateFunction类型的字段使用二进制存储，在写入数据时，需要调用*State函数；而在查询数据时，则需要调用相应的*Merge函数。其中，* 表示定义时使用的聚合函数。

（8）AggregatingMergeTree通常作为物化视图的表引擎，与普通 MergeTree搭配使用。

3、外部存储引擎

（一）HDFS引擎

Clickhouse可以直接从HDFS中指定的目录下加载数据 , 自己根本不存储数据, 仅仅是读取数据

HDFS引擎可以与不存在的文件建立连接，此时可以插入数据，插入数据即相当于创建了这个文件。之后再插入数据会报错“该文件已存在。”

ENGINE = HDFS(hdfs_uri,format) 
·hdfs_uri表示HDFS的文件存储路径； 
·format表示文件格式（指ClickHouse支持的文件格式，常见的有 CSV、TSV和JSON等）。

我们一般期望的是数据有其他方式写入到HDFS系统中, 使用CK的HDFS引擎加载处理分析数据.

这种形式类似Hive的外挂表，由其他系统直接将文件写入HDFS。通过HDFS表引擎的hdfs_uri和format参数分别与HDFS的文件路径、文件格式建立映射。其中，hdfs_uri支持以下几种常见的配置方法：

绝对路径：会读取指定路径的单个文件，例如/clickhouse/hdfs_table1。
通配符：匹配所有字符，例如路径为/clickhouse/hdfs_table/

，则会读取/click-house/hdfs_table路径下的所有文件。
?通配符：匹配单个字符，例如路径为/clickhouse/hdfs_table/organization_?.csv，则会读取/clickhouse/hdfs_table路径下与organization_?.csv匹配的文件，其中?代表任意一个合法字符。
{M…N}数字区间：匹配指定数字的文件，例如路径为/clickhouse/hdfs_table/organization_{1…3}.csv，则会读取/clickhouse/hdfs_table/路径下的文件organization_1.csv、organization_2.csv和organization_3.csv。

create table test_hdfs1(
id Int8 ,
name String ,
age Int8
)engine=HDFS('hdfs://linux01:8020/ck/test1/*' ,CSV) ;

创建文件,将文件上传到指定的目录下
1.txt
1,zss,21
2,ww,22

2.txt 
3,lss,33
4,qaa,32

3.txt 
5,as,31
6,ghf,45
--匹配单个字符 
create table test_hdfs2(
id Int8 ,
name String ,
age Int8
)engine=HDFS('hdfs://linux01:8020/ck/test1/?.txt' ,CSV) ;

-- 匹配数字之间的文件
create table test_hdfs3(
id Int8 ,
name String ,
age Int8
)engine=HDFS('hdfs://linux01:8020/ck/test1/a_{1..2}.txt' ,CSV) ;

（二）MySql表引擎

MySQL表引擎可以与MySQL数据库中的数据表建立映射，并通过SQL向其发起远程查询，包括SELECT和INSERT

它的声明方式如下：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

其中各参数的含义分别如下：

host:port表示MySQL的地址和端口。
database表示数据库的名称。
table表示需要映射的表名称。
user表示MySQL的用户名。
password表示MySQL的密码。
replace_query默认为0，对应MySQL的REPLACE INTO语法。如果将它设置为1，则会用REPLACE INTO代替INSERT INTO。
on_duplicate_clause默认为0，对应MySQL的ON DUPLICATE KEY语法。如果需要使用该设置，则必须将replace_query设置成0。

那么在正式使用MySQL引擎之前首先当前机器要有操作MySQL数据的权限 ,开放MySQL的远程连连接权限操作如下:

1)  set global validate_password_policy=0;
2)  set global validate_password_length=1;   这个两个设置以后 密码很简单不会报错
3)  grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
4)  flush privileges;

-- 在mysql中建表 
create table tb_x(id int, name varchar(25), age int) ;
insert into tb_x values(1,'zss',23),(2,'lss',33) ;
-- 在clickhouse中建表 
CREATE TABLE tb_mysql
(
    `id` Int8,
    `name` String,
    `age` Int8
)
ENGINE = MySQL('linux1:3306', 'test', 'tb_x', 'root', '123456');
-- 查看数据
-- 插入数据
insert into tb_mysql values(3,'ww',44) ;

支持查询数据
支持插入数据
不支持删除和更新操作

（三）MySql数据库引擎

语法

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

create database db_ck_mysql 
engine=MySQL('linux1:3306','test','root','123456') ;

常用于数据的合并 , 加载mysql中的数据和ck中的数据合并 , 不做数据的修改和建表

（四）File引擎

File表引擎能够直接读取本地文件的数据，通常被作为一种扩充手段来使用。例如：它可以读取由其他系统生成的数据文件，如果外部系统直接修改了文件，则变相达到了数据更新的目的；它可以将 ClickHouse数据导出为本地文件；它还可以用于数据格式转换等场景。除此以外，File表引擎也被应用于clickhouse-local工具

ENGINE = File(format)

drop table if exists test_file1 ;
create table test_file1(
id String ,
name String ,
age UInt8
)engine=File("CSV") ;

在默认的目录下回生成一个文件夹 , 文件夹中可以写入文件 ,但是文件的名字必须是data.CSV

insert into  test_file1 values('u001','hangge',33) ;

这种的处理本地数据的方式是将指定的文件存储在指定的表目录下 , 名字有约束 ,且没有对数据进行二次加工

可以使用导入的方式处理本地数据。先建好表，表可以有任意引擎，导入数据，数据被二次加工，且有对应引擎的数据组织结构，高效

cat /data/a.csv  | clickhosue-client -q 'insert into tb_name format CSV'
#或
clickhouse-client  -q 'insert into default.test_load1 format CSV'  <  user.txt
#还可以指定分隔符 
clickhouse-client --format_csv_delimiter='-' -q 'insert into default.test_load1 format CSV'  <   user.txt

4、内存引擎

（一）Memory引擎

Memory表引擎直接将数据保存在内存中，数据既不会被压缩也不会被格式转换，数据在内存中保存的形态与查询时看到的如出一辙

。正因为如此，当ClickHouse服务
重启
的时候，
Memory表内的数据会全部丢失
。所以在一些场合，会将Memory作为测试表使用，很多初学者在学习ClickHouse的时候所写的Hello World程序很可能用的就是Memory表。由于不需要磁盘读取、序列化以及反序列等操作，所以Memory表引擎支持并行查询，并且在简单的查询场景中能够达到与MergeTree旗鼓相当的查询性能（一亿行数据量以内）。Memory表的创建方法如下所示:

CREATE TABLE memory_1 ( 
id UInt64 
)ENGINE = Memory() ;

Memory表更为广泛的应用场景是在ClickHouse的内部，它会作为集群间分发数据的存储载体来使用。例如在分布式IN查询的场合中，会利用Memory临时表保存IN子句的查询结果，并通过网络将它传输到远端节点。

（二）Set引擎

Set表引擎是拥有物理存储，

数据首先会被写至内存，然后被同步到磁盘文件中

。所以当服务重启时，它的数据不会丢失，当数据表被重新装载时，文件数据会再次被全量加载至内存。众所周知，在Set 数据结构中，所有元素都是唯一的。

Set表引擎具有去重的能力，在数据写入的过程中，重复的数据会被自动忽略。

然而Set表引擎的使用场景既特殊又有限，它虽然支持正常的INSERT写入，但并不能直接使用SELECT对其进行查询，Set表引擎只能间接作为IN查询的右侧条件被查询使用

Set表引擎的存储结构由两部分组成，它们分别是：

[num].bin数据文件：保存了所有列字段的数据。其中，num是一个自增id，从1开始。伴随着每一批数据的写入（每一次INSERT），都会生成一个新的.bin文件，num也会随之加1。

tmp临时目录：数据文件首先会被写到这个目录，当一批数据写入完毕之后，数据文件会被移出此目录。

create table test_set(
id Int8 ,
name String
)engine=Set();

发现在数据库的目录下是还有对应的目录的,可见数据会被存储到磁盘上的

在这里插入图片描述

插如数据 ;

CREATE TABLE x
(
  `id` Int8,
  `name` String
)
ENGINE =Set
insert into x values(1,'zss'),(4,'ww') ;

但是这种表不允许我们直接查询，正确的查询方法是将Set表引擎作为IN查询的右侧条件

select * from x where (id,name) in test_set ;

CREATE TABLE test_set_source
(
    `id` Int8,
    `name` String,
    `age` Int8
)
ENGINE = Log ;
insert into test_set_source values(1,'lss',21),(2,'ww',33),(3,'zl',11) ;
-- 以set表中的数据为依据 筛选数据
select * from test_set_source where id in  test_set;
注意 :  in的条件各表的字段一致

（三）Buffer引擎

Buffer表引擎完全使用内存装载数据，不支持文件的持久化存储，所以当服务重启之后，表内的数据会被清空。

Buffer表引擎不是为了面向查询场景而设计的，它的作用是充当缓冲区的角色

。假设有这样一种场景，我们需要将数据写入目标MergeTree表A，由于写入的

并发数很高

，这可能会导致MergeTree表A的合并速度慢于写入速度（因为每一次INSERT都会生成一个新的分区目录）。此时，可以引入Buffer表来缓解这类问题，将Buffer表作为数据写入的缓冲区。数据首先被写入Buffer表，当满足预设条件时，Buffer表会自动将数据刷新到目标表。

在这里插入图片描述

ENGINE = Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)

其中，参数可以分成基础参数和条件参数两类，首先说明基础参数的作用：

database：目标表的数据库。
table：目标表的名称，Buffer表内的数据会自动刷新到目标表。
num_layers：可以理解成线程数，Buffer表会按照num_layers的数量开启线程，以并行的方式将数据刷新到目标表，官方建议设为16。

Buffer表并不是实时刷新数据的，只有在阈值条件满足时它才会刷新。阈值条件由三组最小和最大值组成。接下来说明三组极值条件参数的具体含义：

min_time和max_time：时间条件的最小和最大值，单位为秒，从第一次向表内写入数据的时候开始计算；
min_rows和max_rows：数据行条件的最小和最大值；
min_bytes和max_bytes：数据体量条件的最小和最大值，单位为字节。

Buffer表刷新的判断依据有两个，满足其中任意一个，Buffer表就会刷新数据，它们分别是：

1. 如果三组条件中所有的最小阈值都已满足，则触发刷新动作；

2. 如果三组条件中至少有一个最大阈值条件满足，则触发刷新动作；

还有一点需要注意，上述三组条件在每一个num_layers中都是单独计算的。假设num_layers=16，则Buffer表最多会开启16个线程来响应数据的写入，它们以轮询的方式接收请求，在每个线程内，会独立进行上述条件判断的过程。也就是说，假设一张Buffer表的 max_bytes =100000000（约100 MB），num_layers=16，那么这张Buffer表能够同时处理的最大数据量约是1.6 GB。

1) 创建一个目标表
create table tb_user_target(uid Int8 , name String) engine=TinyLog ;
2) 创建一个缓存表
CREATE TABLE tb_user_buffer AS tb_user_target ENGINE = Buffer(doit26, tb_user_target, 16, 10, 100, 10000, 1000000, 10000000, 100000000) ;
CREATE TABLE tb_user_buffer2 AS tb_user_target ENGINE = Buffer(doit26, tb_user_target, 16, 10, 100, 2, 10, 10000000, 100000000) ;

3) 向缓存表中插入数据
insert into tb_user_buffer values(1,’Yang’),(2,'Haha') ,(3,'ADA') ;
4) 等待以后查看目标表中的数据
select * from tb_user ;

您可以在数据库名称和表名称的单引号中设置空字符串。这表明没有目标表。在这种情况下，当达到数据刷新条件时，只需清除缓冲区。这对于将数据窗口保留在内存中可能很有用。

二、视图

ClickHouse拥有普通和物化两种视图，其中物化视图拥有独立的存储**，而普通视图只是一层简单的查询代理**

1 、普通视图

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...

普通视图不会存储任何数据，它只是一层单纯的SELECT查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强。

create view  test3_view as select id , upper(name) , role from tb_test3 ;
┌─name────────────┐
│ tb_test3        │
│ test3_view      │
│ test_partition1 │
│ test_partition2 │
│ test_rep        │
│ tt1             │
└─────────────────┘
drop view test3_view ;   -- 删除视图

删除视图

drop view test3_view ; 
drop table test3_view ;

2 、物化视图

物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的完整语法如下所示

create materialized view mv_log engine=Log

populate

as select * from log ;

物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。

POPULATE

修饰符决定了物化视图的初始化策略：

如果使用了POPULATE修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一并导入，如同执行了INTO SELECT 一般

；反之，
如果不使用POPULATE修饰符，那么物化视图在创建之后是没有数据的，它只会同步在此之后被写入源表的数据
。

物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留。

删除基表, 物化视图仍然可以查询。这是符合预期的, 因为物化视图是存储了数据的

create materialized view test3_view engine = Log populate as select * from tb_test3 ;
-- 建表的时候同步数据 , 当数据更新以后 物化视图中的数据会同步更新 , 但是当删除数据以后,物化视图中的数据不会被删除
SELECT * FROM test3_view ;
┌─id─┬─name───┬─role─┐
│  1 │ HANGGE │ VIP  │
│  2 │ BENGE  │ VIP  │
│  3 │ PINGGE │ VIP  │
└────┴────────┴──────┘
-- 向源表中擦混入数据
SELECT * FROM test3_view
┌─id─┬─name──┬─role─┐
│  4 │ TAOGE │ VIP  │
└────┴───────┴──────┘
┌─id─┬─name───┬─role─┐
│  1 │ HANGGE │ VIP  │
│  2 │ BENGE  │ VIP  │
│  3 │ PINGGE │ VIP  │
└────┴────────┴──────┘
-- 删除源表中的数据 , 物化视图中的数据 不会变化   ****  
注意: 数据删除语法只适用于MergeTree引擎的表   基本语法如下
ALTER TABLE db_name.table_name DROP PARTITION '20210601'
ALTER TABLE db_name.table_name DELETE WHERE day = '20210618'
ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>

创建物化视图，桥接两表数据

create MATERIALIZED VIEW eagle_view TO eagle_detail

原文链接：https://blog.csdn.net/weixin_52189442/article/details/120535638