clickhouse概述

前言

随着数据科技的进步，数据分析师早已不再满足于传统的T+1式报表或需要提前设置好维度与指标的OLAP查询。数据分析师更希望使用可以支持任意指标、任意维度并秒级给出反馈的大数据Ad-hoc查询系统。这对大数据技术来说是一项非常大的挑战，传统的大数据查询引擎根本无法做到这一点。由俄罗斯的Yandex公司开源的ClickHouse脱颖而出。在第一届易观OLAP大赛中，在用户行为分析转化漏斗场景里，ClickHouse比Spark快了近10倍。

数仓架构图

数据主要分为二种：

• 离线（图中黄级表示）：数仓统计数据结果一般是T+1

• 实时（图中黄线表示）：实时数仓统计结果一般是分钟级别、秒级别

红色框架部分可以用 clickhouse代替

多维度分析（数据立方体）

维度可以看成是观察数据的一种视角，例如人类能看到的世界是三维的，它包含长、宽、高三个维度。直接一点理解，维度就好比是一张数据表的字段，而多维分析则是基于这些字段进行聚合查询。

• 下钻：从高层次向低层次明细数据进行穿透。例如从 “省” 下钻到 “市”，从 “湖北省” 穿透到 “武汉” 和 “宜昌” 。
• 上卷：和下钻相反，从低层次向高层次汇聚。例如从 “市” 汇聚到 “省”，将 “武汉” 和 “宜昌” 汇聚成 “湖北”。
  
• 切片（切块）：观察立方体的一层，将一个或多个温度设为单个固定的值，然后观察剩余的维度，例如将商品维度固定为 “足球”。
  
• 旋转：旋转立方体的一面，如果要将数据映射到一张二维表，那么就要进行旋转，等同于行列转换。
  

说白了，就是group by + where

解决方案

为了实现上述操作，解决的方式大约分为三种。

• 实时计算，上SQL

SELECT SUM(价格) FROM 销售数据表 GROUP BY 省;

但是这种架构对数据的实时处理能力要求很高，试想一下，如果对一张存有上亿条记录的表同时执行数十个字段的GROUP BY查询

• 预先聚合

说白了就是提前先算好，然后将结果保存起来，使用空间换取时间的形式从而提升查询性能。

1. 首先，对需要分析的数据进行建模，框定需要分析的维度字段；
2. 然后，通过预处理的形式，对各种维度进行组合并事先聚合；
3. 最后，将聚合结果以某种索引或者缓存的形式保存起来（通常只保留聚合后的结果，不存储明细数据），这样一来，在随后的查询过程中，可以直接利用结果返回数据。

但是这种架构显然并不完美，原因如下：

• 预聚合只能支持固定的分析场景，所以它无法满足自定义分析的需求。
• 维度的预处理会导致数据膨胀，这里可以做一次简单的计算，以上面的销售明细表为例，如果数据立方体包含了 5 个维度（字段），那么维度的组合方式就有 25 种。维度组合爆炸会导致数据膨胀，这样会造成不必要的计算和存储开销。此外，用户并不一定会用到所有维度的组合，那么没有被用到的组合将会浪费。
• 另外，由于使用了预聚合的方式，数据立方体会有一定的滞后性，不能实时地进行数据分析。所以对于在线实时接收的流量数据，预聚合还需要考虑如何及时更新数据。而且立方体只保留了聚合后的结果数据，因此明细数据是无法查询的。

• 预先聚合 + 实现

就是预先聚合一部份数据，然后有些数据再实时的计算

初识 ClickHouse

ClickHouse 使用了列式存储，并具有完整的DBMS，开箱即用。特别是它那夸张的实时查询性能

ck为什么查询快

1. 列式存储与数据压缩, 一次扫描可以加载更多数据的数据进内存
2. 向量化执行引擎，SIMD 的全称是：Single Instruction Multiple Data，即用单条指令操作多条数据。现代计算机系统概念中，它是通过数据并行以提高性能的一种实现方式（其它的还有指令级并行和线程级并行），它的原理是在 CPU 寄存器层面实现数据的并行计算。
3. ClickHouse 的设计则采用了自底向上的方式，即从硬件功能层面着手设计String、HashTable、Vetcor等数据结构

在预先聚合的方面，CK也提供了多样化的表引擎。来帮助我们实现聚合操作。

核心引擎MergeTree

在这众多的表引擎中，又属合并树（MergeTree）表引擎及其家族系列(*MergeTree)最为强大，在生产环境的绝大部分场景中，都会使用此系列的表引擎。MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成一个新的片段。这种数据片段往复合并的特点，也正是合并树名称的由来。

MergeTree创建方式

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 = 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. ENGINE：引擎名字，MergeTree引擎无参数。
  2. ORDER BY：排序键，可以由一列或多列组成，决定了数据以何种方式进行排序，例如ORDER BY（CounterID, EventDate）。 如果没有显示指定PRIMARY KEY，那么将使用ORDER BY作为PRIMARY KEY。通常只指定ORDER BY即可。
• 选填选项
  1. PARTITION BY：分区键，指明表中的数据以何种规则进行分区。分区是在一个表中通过指定的规则划分而成的逻辑数据集。分区可以按任意标准进行，如按月、按日或按事件类型。为了减少需要操作的数据，每个分区都是分开存储的。
  2. PRIMARY KEY：主键，设置后会按照主键生成一级索引（primary.idx），数据会依据索引的设置进行排序，从而加速查询性能。默认情况下，PRIMARY KEY与ORDER BY设置相同，所以通常情况下直接使用ORDER BY设置来替代主键设置。
  3. SAMPLE BY：数据采样设置，如果显示配置了该选项，那么主键配置中也应该包括此配置。例如 ORDER BY CounterID / EventDate / intHash32（UserID）、SAMPLE BY intHash32（UserID）。
  4. TTL：数据存活时间，可以为某一字段列或者一整张表设置TTL，设置中必须包含Date或DateTime字段类型。如果设置在列上，那么会删除字段中过期的数据。如果设置的是表级的TTL，那么会删除表中过期的数据。如果设置了两种类型，那么按先到期的为准。例如，TTL createtime + INTERVAL 1 DAY，即一天后过期。使用场景包括定期删除数据，或者定期将数据进行归档。
  5. index_granularity：索引间隔粒度。MergeTree索引为稀疏索引，每index_granularity个数据产生一条索引。index_granularity默认设置为8092。
  6. enable_mixed_granularity_parts：是否启动index_granularity_bytes来控制索引粒度大小。
  7. index_granularity_bytes：索引粒度，以字节为单位，默认10Mb
  8. merge_max_block_size：数据块合并最大记录个数，默认8192。
  9. merge_with_ttl_timeout：合并频率最小时间间隔，默认1天。

MergeTree的存储结构

MergeTree表引擎中的数据是拥有物理存储的，数据会按照分区目录的形式保存到磁盘之上，其完整的存储结构如图所示：

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

partition

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

CREATE TABLE test.test 
( 
 id        UInt64, 
 type      UInt8, 
 create_time DateTime 
) ENGINE = MergeTree() 
 PARTITION BY toYYYYMMDD(create_time) 
 ORDER BY (id) 
 SETTINGS index_granularity = 4;

insert into test.test(id, type, create_time) VALUES (1, 1, toDateTime('2021-03-01 00:00:00'));

会出现目录名为20210301_8_8_0。

PartitionID:分区id，例如20210301。
MinBlockNum:最小分区块编号，自增类型，从1开始向上递增。每产生一个新的目录分区就向上递增一个数字。
MaxBlockNum:最大分区块编号，新创建的分区MinBlockNum等于MaxBlockNum的编号。
Level:合并的层级，被合并的次数。合并次数越多，层级值越大。

此分区的分区id为20210301，当前分区的MinBlockNum和MinBlockNum均为8，而level为0，表示此分区没有合并过。

数据分区ID生成规则

数据分区规则由分区ID决定，分区ID由PARTITION BY分区键决定。根据分区键字段类型，ID生成规则可分为：

1. 未定义分区键:没有定义PARTITION BY，默认生成一个目录名为all的数据分区，所有数据均存放在all目录下。
2. 整型分区键:分区键为整型，那么直接用该整型值的字符串形式做为分区ID。
3. 日期类分区键:分区键为日期类型，或者可以转化成日期类型。
4. 其他类型分区键:String、Float类型等，通过128位的Hash算法取其Hash值作为分区ID。

分区目录的合并过程

1. MergeTree的分区目录和传统意义上其他数据库有所不同。首先，MergeTree的分区目录并不是在数据表被创建之后就存在的，而是在数据写入过程中被创建的。也就是说如果一张数据表没有任何数据，那么也不会有任何分区目录存在。
2. 伴随着每一批数据的写入（一次INSERT语句）,MergeTree都会生成一批新的分区目录。即便不同批次写入的数据属于相同分区，也会生成不同的分区目录。也就是说，对于同一个分区而言，也会存在多个分区目录的情况
3. 在之后的某个时刻（写入后的10～15分钟，也可以手动执行optimize查询语句）, ClickHouse会通过后台任务再将属于相同分区的多个目录合并成一个新的目录。已经存在的旧分区目录并不会立即被删除，而是在之后的某个时刻通过后台任务被删除（默认8分钟）。

合并目录名称的变化过程如图所示：

数据分区文件组织结构

目录中的文件主要包括bin文件、mrk文件、primary.idx文件以及其他相关文件

• bin文件:数据文件，存储的是某一列的数据。数据表中的每一列都对应一个与其字段名相同的bin文件，例如id.bin存储的是表test中id列的数据。
• mrk文件:标记文件，每一列都对应一个与其字段名相同的标记文件，标记文件在idx索引文件和bin数据文件之间起到了桥梁作用。以mrk2结尾的文件，表示该表启用了自适应索引间隔。
• primary.idx文件:主键索引文件，用于加快查询效率。
• count.txt:数据分区中数据总记录数。上述20210301_8_8_0的数据分区中，该文件中的记录总数为1。
• columns.txt:表中所有列数的信息，包括字段名和字段类型。
• partion.dat:用于保存分区表达式的值。上述20210301_8_8_0的数据分区中该文件中的值为20210301。
• minmax_create_time.idx:分区键的最大最小值。
• checksums.txt:校验文件，用于校验各个文件的正确性。存放各个文件的size以及hash值。

数据文件

• 数据文件是按数据会事先依照ORDER BY的声明排序
• 在MergeTree中数据是按列存储的
• 数据是以压缩数据块的形式被组织并写入.bin文件中的

假设根据一索引查找一行数据id在id.bin的移偏量p，如何确定此行其它列c1的数据？

1. 可以根据columns获取id列的大小，用现在p去计算出当前行数n。
2. 可以根据columns获取c1列的大小,再结果行数n，算出当前行对应c1.bin的偏移量，即可获取数据

一级索引

MergeTree的主键使用PRIMARY KEY定义，待主键定义之后，MergeTree会依据index_granularity间隔（默认8192行），为数据表生成一级索引并保存至primary.idx文件内，索引数据按照PRIMARY KEY排序。主键不指定默认为ORDER BY。

ps: ORDER BY = PRIMARY KEY + 其它字段。 当你有些字段仅用于排序，不想用于搜索。来分别设置不同此，以节省空间

如果使用多个主键，例如ORDER BY (CounterID, EventDate)，则每间隔8192行可以同时取CounterID与EventDate两列的值作为索引值，具体如图所示。

数据标记

如果把MergeTree比作一本书，primary.idx一级索引好比这本书的一级章节目录，.bin文件中的数据好比这本书中的文字，那么数据标记(.mrk)会为一级章节目录和具体的文字之间建立关联。

即各个数据文件xxx.bin，都会根据index_granularity（默认8192行）去生成一个mark，分别记录在xxx.mrk里面。这样子在primary中如果确定了mark,就可以分别在xxx.mrk获取不同列在xxx.bin的偏移量

二级索引

除了一级索引之外，MergeTree同样支持二级索引。二级索引又称跳数索引，由数据的聚合信息构建而成。根据索引类型的不同，其聚合信息的内容也不同。跳数索引的目的与一级索引一样，也是帮助查询时减少数据扫描的范围。

如果在建表语句中声明了跳数索引，则会额外生成相应的索引与标记文件（skp_idx_[Column].idx与skp_idx_[Column].mrk）. 并理解index_granularity和index_granularity两参数，其规则大致是如下

• 首先，按照index_granularity粒度间隔将数据划分成n段，总共有[0 ,n-1]个区间（n = total_rows / index_granularity，向上取整）。
• 接着，根据索引定义时声明的表达式，从0区间开始，依次按index_granularity粒度从数据中获取聚合信息，每次向前移动1步(n+1)，聚合信息逐步累加。最后，当移动granularity次区间时，则汇总并生成一行跳数索引数据。

以minmax索引为例，它的聚合信息是在一个index_granularity区间内数据的最小和最大极值。以下图为例，假设index_granularity=8192且granularity=3，则数据会按照index_granularity划分为n等份，MergeTree从第0段分区开始，依次获取聚合信息。当获取到第3个分区时（granularity=3），则汇总并会生成第一行minmax索引（前3段minmax极值汇总后取值为[1 , 9]），如图所示。

查询过程

数据查询的本质，可以看作一个不断减小数据范围的过程。在最理想的情况下，MergeTree首先可以依次借助分区索引、一级索引和二级索引，将数据扫描范围缩至最小。然后再借助数据标记，将需要解压与计算的数据范围缩至最小。

如果一条查询语句没有指定任何WHERE条件，或是指定了WHERE条件，但条件没有匹配到任何索引（分区索引、一级索引和二级索引），那么MergeTree就不能预先减小数据范围。在后续进行数据查询时，它会扫描所有分区目录，以及目录内索引段的最大区间。虽然不能减少数据范围，但是MergeTree仍然能够借助数据标记，以多线程的形式同时读取多个压缩数据块，以提升性能。

写入过程

下图所示是一张MergeTree表在写入数据时，它的分区目录、索引、标记和压缩数据的生成过程。

1. 第一步是生成分区目录，伴随着每一批数据的写入，都会生成一个新的分区目录。
2. 在后续的某一时刻，属于相同分区的目录会依照规则合并到一起；
3. 接着，按照index_granularity索引粒度，会分别生成primary.idx一级索引（如果声明了二级索引，还会创建二级索引文件）、每一个列字段的．mrk数据标记和．bin压缩数据文件。
   
   从分区目录201403_1_34_3能够得知，该分区数据共分34批写入，期间发生过3次合并。在数据写入的过程中，依据index_granularity的粒度，从要合并的老区间按批次读取数据(可借助索引)，然后依次为每个区间的数据生成索引、标记和压缩数据块。其中，索引和标记区间是对齐的，而标记与压缩块则根据区间数据大小的不同，会生成多对一、一对一和一对多三种关系。

MergeTree 家族

MergeTree（合并树）系列表引擎是ClickHouse核心的存储引擎。其及其扩展提供各种功能

• ReplacingMergeTree：在后台数据合并期间，对具有相同排序键的数据进行去重操作。
• SummingMergeTree：当合并数据时，会把具有相同主键的记录合并为一条记录。根据聚合字段设置，该字段的值为聚合后的汇总值，非聚合字段使用第一条记录的值，聚合字段类型必须为数值类型
• AggregatingMergeTree：在同一数据分区下，可以将具有相同主键的数据进行聚合。
• CollapsingMergeTree：在同一数据分区下，对具有相同主键的数据进行折叠合并。
• VersionedCollapsingMergeTree：基于CollapsingMergeTree引擎，增添了数据版本信息字段配置选项。在数据依据ORDER BY设置对数据进行排序的基础上，如果数据的版本信息列不在排序字段中，那么版本信息会被隐式的作为ORDER BY的最后一列从而影响数据排序。
• GraphiteMergeTree：用来存储时序数据库Graphites的数据。

架构

ClickHouse并不像其他分布式系统那样，拥有高度自动化的分片功能；它提供了本地表与分布式表的概念；一张本地表等同于一个数据分片。而分布式表是张逻辑表，本身不存储任何数据，它是本地表的访问代理，其作用类似分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询。当然，也可以在应用层实现数据分发。

ps: ClickHouse的集群是表维度的。

副本

ClickHouse的数据副本一般通过ReplicatedMergeTree复制表系列引擎实现，副本之间借助ZooKeeper实现数据的一致性。

CREATE TABLE repl_test123
(
    `id` String,
    `price` Float64,
    `create_time` DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/repl_test123','{replica}')
PARTITION BY toYYYYMM(create_time)
ORDER BY id

• repl_test123 表示要复制的表名
• {shard} 表示要复制哪分片上的表
• {replica}表示当前备份表的编号

分片

分片是指将数据拆分，将其分散在不同的实例上的过程。每个分片只负责总数据的一部分，通过一个名为Distributed的引擎进行操作。

CREATE TABLE test_clsuter_all on cluster test_cluster_0_repl
(
    `id` String,
    `price` Float64,
    `create_time` DateTime
)
ENGINE = Distributed('test_cluster_0_repl','default','test_clsuter_a', rand())

• Distributed(‘集群名’,‘数据库名’,‘表名’, ‘分片键’)
• test_clsuter_all对外表名

Distributed引擎表，其自身不存储数据，而是作为数据分片的代理，自动路由数据到集群中的各个节点，其中<分片键>参数要求返回整型类型的取值，即按照分片键的规则将数据分布到各个节点，如：

• userid：按照用户id余数拆分
• rand()：按照随机数拆分
• intHash64(userid)：按照用户id散列值划分

扩容&缩容

1. 副本缩容:副本节点作为一个独立的节点，直接下线缩容即可。
2. 分片缩容: 把要下分片的数据写入下，不下分片中
3. 副本扩容：副本扩容比较简单，只需要在新实例上创建表，修改表结构中的{replica_name}，{zk_path}不需要改变。数据会自动通过ZooKeeper来协调获取主信息，从主上下载数据到本地。
4. 分片扩容：增加分片节点，集群的分片数将增加，这样会让新的分片表和老的分片表的分片数量不一致。因为扩容需要用到新分片，所以需要先新增一个集群，让扩容的分片表可以使用到新的分片节点，然后将旧集群中的数据迁移至新集群，最后删除旧集群的数据与集群配置信息。

常用方案

以四节点实现多分片和双副本为例：

在每个节点创建一个数据表，作为一个数据分片，使用ReplicatedMergeTree表引擎实现数据副本，而分布表作为数据写入和查询的入口。这是最常见的集群实现方式。如果资源紧缺，也可采用

Join算法

Join 的常见算法实现包含以下几种《SQL执行过程》：

• Nested Loop Join
• Sort Merge Join
• Hash Join

分布式系统实现 Join 数据分布的常见策略有：

• Broadcast Join
• Shuffle Join
• Colocate/Local Join

Broadcast Join

把A表广播到所有B表分区所在节点，然后根据A的join key构建Hash Table，把每一行记录都存进HashTable. 适用A表是小表的情况

Shuffle Join

将A，B表 具有相同性质的（如Hash值相同）join key 进行Shuffle到同一个分区。

Colocate/Local Join

通人数据合理的分区， 让多个节点 Join 时没有数据移动和网络传输，每个节点只在本地进行 Join，能够本地进行 Join 的前提是相同 Join Key 的数据分布在相同的节点。

ClickHouse集群

• 未实现完整意义上的Shuffle JOIN，
• 实现了类Broadcast JOIN，
• 通过事先完成数据重分布，能够实现Colocate JOIN。

如果ck集群过大，使用Broadcast JOIN会造成查询放大，而且放大倍数非常大的问题？

表会广播，n*m次，n表示当前表的分区数，m主表的分区数

为了解决这个问题，引入了GLOBAL关键字，使用GLOBAL修饰后，会将子查询在初始执行节点进行查询汇总，存储为临时表，并在SQL分发时携带该临时表数据到各个节点进行查询，最终汇总结果到初始查询节点。

这种情况下，如果有n个节点，就会仅有2*n次查询操作。大限度的降低了查询放大问题。

虽然最大限度的降低了查询放大，但是如果数据量过大，产生的临时表就会很大，也会受到网络稳定性和网络带宽的限制。

物化视图

数据库中的 视图(View) 指的是通过一张或多张表查询出来的 逻辑表 ，本身只是一段 SQL 的封装并 不存储数据。
而 物化视图(Materialized View) 与普通视图不同的地方在于它是一个查询结果的数据库对象(持久化存储)，非常趋近于表；

ClickHouse中的物化视图可以挂接在任意引擎的基础表上，而且会自动更新数据，它可以借助 MergeTree 家族引擎(SummingMergeTree、Aggregatingmergetree等)，得到一个实时的预聚合，满足快速查询；但是对 更新 与 删除 操作支持并不好，更像是个插入触发器。

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

场景

假设有一个日志表 login_user_log 来记录每次登录的用户信息，现在需要按用户所属地为维度来统计每天的登录次数。正常的聚合SQL如下：city为用户所属地，login_date为登录时间

select city, login_date, count(1) login_cnt
from login_user_log
group by city, login_date

改成物化视图

CREATE TABLE login_user_log_base
(
    city String,
        login_date Date,
    login_cnt UInt32
)
ENGINE = SummingMergeTree()
ORDER BY (city, login_date)

CREATE MATERIALIZED VIEW if not exists login_user_log_mv 
TO login_user_log_base 
AS 
SELECT city, login_date, count(1) login_cnt
from login_user_log
group by city, login_date

使用 TO 关键字关联 物化视图 与 基础表，需要自己初始化历史数据。

1. 物化视图是基本表引挚来提前聚合的，有些left jion 的语句是做不到物化视图的。
2. 物化视图原理是增量表，值全部是在新增数据的时候斌值统计的

主要参考

《官方文档》
《clickhouse》
《ClickHouse入门实践–MergeTree原理解析》
《常见ClickHouse集群部署架构》
《Spark难点 | Join的实现原理》
《【ClickHouse 极简教程】分布式下的 IN/JOIN 及 GLOBAL关键字》
《Apache Doris Colocate Join 原理与实践》
《ClickHouse 的基本介绍，什么是 ClickHouse？ 》