ethan

第 4 章数据定义#

数据分区的基本操作#

查询分区信息#

clickhouse 内置了许多 system 系统表，用于查询自身的状态信息。其中 parts 系统表专门用于查询数据表的分区信息。

select partition_id, name, table, database from system.parts where table = 'partition_v2'

删除指定分区#

合理的设计分区键并利用分区的删除功能，就能够达到数据更新的目的。

alter table tb_anme drop partition partition_expr

复制分区数据#

clickhouse 支持将 A 表的分区数据复制到 B 表，这项特性可以用语快速数据写入、多表之间数据同步和备份等场景。

alter table B replace partition partition_expr from A

注意：需要满足以下两个前提条件：

• 两张表需要拥有相同的分区键
• 他们的表结构完全相同

重置分区数据#

如果数据表某一列的数据有误，需要将其重置为初始值，此时可以使用下面的语句实现：

alter table tb_name clear column column_name in partition partition_expr

准则：如果声明了默认值表达式，则以表达式为准；否则以相应的数据类型的默认值为准。

卸载和装载分区#

表分区可以通过 DETACH 语句卸载，分区被卸载后，物理数据并未删除，而是转移至表目录的 detached 子目录下。装载分区 (ATTACH) 为其反向操作。常用于分区数据的迁移和备份场景。

alter table tb_name detache partition partition_expr

分布式 DDL 执行#

clickhouse 支持集群模式，一个集群拥有 1 到多个节点。CREATE, ALTER, DROP, RENAME 以及 TRUNCATE 这些 DDL 语句，都支持分布式执行。如果在集群中任意一个节点上执行 DDL 语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。只需加上 ON CLUSTER cluster_name 声明即可。

数据的写入#

INSERT 语句支持三种语法范式。

第一种：values 语法

INSERT INTO [db.]table [(c1,c2,c3...)] VALUES (v11,v12,c13),(v21,v22,v23),...

第二种：指定格式的语法

INSERT INTO [db.]table [(c1,c2,c3...)] FORMAT format_anme data_set

第三种：使用 select 子句形式的语法

INSERT INTO [db.]table [(c1,c2,c3...)] SELECT ...

VALUES 和 SELECT 子句的形式都支持表达式或函数，但是表达式和函数会带来额外的性能开销。

数据的删除和修改#

clickhouse 提供了 DELETE 和 UPDATE 的能力，这类操作被称为 Mutation 查询，它可以看作 ALTER 语句的变种。

不同点：Mutation 语句是一种 “很重” 的操作，更适用于批量数据的修改和删除，其次，她不支持事物，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚；最后，该语句的执行是一个异步的后台过程，语句被提交后就会立刻返回。所以这并不代表具体逻辑已经执行完毕，它的具体执行进度需要通过 system.mutations 系统表查询。

SELECT database, table, mutation_id, block_numbers as num, is_done FROM system.mutations

第 5 章数据字典#

数据字典是以键值和属性映射的形式定义数据。数据会在 clickhouse 启动时主动加载或者首次查询时惰性被动加载 (由参数设置决定) 到内存，并支持动态更新。它非常适合保存敞亮或经常使用的维度表数据，可以避免不必要的 JOIN 查询。

数据字典有内置（默认自带的字典）和扩展（通过自定义配置实现的）两种。

内置字典#

第 6 章 MergeTree 原理解析#

MergeTree 的创建方式与存储结构#

MergeTree 的创建方式#

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

(1) PARTITION BY [选填]: 分区键，用于指定表数据以各种标准进行分区。可以是单个列字段，也可以是元组形式的多列字段，还可以是列表达式。如果不声明则会以 all 命名。合理使用数据分区，可以有效减少查询时数据文件的扫描范围

(2) ORDER BY [必填]：排序键，用于指定在一个数据片段内，数据以各种标准排序。默认为主键。可以是单个列也可以是元组形式的多个列。

(3) PRIMARY KEY [选填]: 主键，按照主键字段生成一级缩阴，用于加速表查询。默认情况为主键和排序键相同.MergTree 主键允许存在重复数据。

(4) SAMPLE BY [选填]：抽样表达式，用于声明数据的采样标准。主键的配置中也需要声明同样的表达式。

(5) SETTINGS：index_granularity [选填] 表示索引的力度，默认为 8192。每隔 8192 行数据才生成一条索引。

(6) SETTINGS: index_granularity_bytes [选填], 19.12 版本后支持。增加了自适应间隔大小的特性（根据每一批次写入数据的体量大小，动态划分间隔大小），默认为 10M, 设置为 0 表示不启动自适应功能。

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

(8) SETTINGS：merge_with_ttl_timeout [选填]: 19.6 之后提供的数据 TTL 的功能

(9) SETTINGS: storage_policy [选填]： 多路径存储策略

数据分区#

数据的分区规则#

MergeTree 数据分区的规则由分区 ID 决定。分区 ID 的生成逻辑有 4 中规则：

(1) 不指定分区键：分区的 ID 默认取名为 all，所有的数据都会被写入 all 分区

(2) 使用整型：如果分区键取值属于整型，无法转换为日期类型 YYYMMDD 格式，则直接按照该整型的字符形式输出，作为分区 ID 的取值。

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

(4) 使用其它类型：如果分区键取值既不属于整形也不属于日期类型，则通过 128 位 Hash 算法生成 ID。

分区目录的命名规则#

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

PartitionID_MinBlockNum_MaxBlockNum_Level

(1) PartitionID：分区 ID

(2) MinBlockNum_MaxBlockNum: 最小数据块和最大数据块编号。这里的 BlockNum 是一个整型的自增长编号。

(3) Level：合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄。

分区目录的合并过程#

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

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

• MinBlockNum：取同一分区内所有目录中最小的值
• MaxBlockNum: 取同一分区内所有目录中的最大值
• Level：取同一分区内最大 Level 值并加 1

一级索引#

MergeTree 会依据 index_granularity 间隔，为数据表生成一级索引并保存至 primary.idx 文件内，索引数据按照 PRIMARY_KEY 排序。

稀疏索引#

在稠密索引中每一行索引标记都会对应一行具体的数据记录，在稀疏索引中，每一行索引标记对应的是一段数据，而不是一行。

稀疏索引的又是在于，仅需使用少量的索引标记就能够记录大量数据的区间位置信息，且数据量越大优势为明显。由于稀疏索引占用空间小，所以 primary.idx 内的索引数据常驻内存。

索引粒度#

索引粒度会根据数据的长度对数据进行标注，最终将数据以 index_granualarity 为粒度标记成多个间隔的小段。MergeTree 使用 MarkRange 表示一个具体的区间，并通过 start 和 end 表示其具体的范围。这个参数不仅作用于一级索引同时也会影响数据标记和数据文件。

仅靠一级索引无法完成查询工作，需要借助数据标记才能定位数据。数据文件也会按照 index_granualarity 的间隔粒度生成压缩数据块。

索引数据的生成规则#

MergeTree 需要间隔 index_granularity 行数据才会生成一条索引记录，其索引值会依据声明的主键字段获取。稀疏索引的存储是非常紧凑的，索引值前后凉凉，按照主键字段顺序紧密的排列在一起。

索引的查询过程#

MarkRange 在 ClickHouse 中是用于定义标记区间的对象。MergeTree 按照 index_granularity 的间隔力度，将一段完整的数据划分成多个小的间隔数据段，一个具体的数据段就是一个 MarkRange.

整个索引查询过程可以大致分为 3 个步骤：

(1) 生成查询条件区间：首先将查询条件转换为条件区间，即便是单个值得查询条件，也会被转换成区间的形式。

(2) 递归交集判断：以递归的形式，依次对 MarkRange 的数值区间与条件区间做交集判断。

• 如果不存在交集，则直接通过剪枝算法优化此整段 MarkRange
• 如果存在交集，且 MarkRange 步长大于 8 (end-start), 则此区间进一步拆分成 8 个自区间 (由 merge_tree_coarse_index_granularity 指定)，并重复此规则，继续做递归交集判断
• 如果存在交集，且 MarkRange 不可在分解 (步长小于 8), 则记录 MarkRange 并返回

(3) 合并 MarkRange 区间：将最终匹配的 MarkRange 聚合在一起，合并他们的范围。

MergeTree 通过递归的形式持续向下拆分区间，最终将 MarkRange 定位到最细的粒度，以帮助在后续读取数据的时候，能够最小化扫描数据的范围。

二级索引#

二级索引又称跳数索引，是由数据的聚合信息构建的。索引类型不同，聚合信息的内容也不同。目的也是帮助查询时减少数据扫描的范围。在默认的情况下是关闭的，需要设置 allow_experimental_data_skipping_indices 才能使用。

SET allow_experimental_data_skipping_indices = 1

二级索引需要在 CREATE 语句内定义，它支持使用元组和表达式的形式声明，其完整的定义语法如下所示：

INDEX index_name expr TYPE index_type (...) GRANULARITY granularity

会额外生成相应的索引 (skp_idx_[column].idx) 和标记文件 (skp_idx_[column].mrk)

granularity 与 index_granularity 的关系#

对于跳数索引，index_granularity 定义了数据的粒度，granularity 定义了聚合信息汇总的粒度（一行跳数索引能够跳过多少个 index_granularity 区间的数据）。

跳数索引的类型#

MergeTree 共支持 4 中跳数索引，分别是 minmax,set,ngrambf_v1 和 tokenbf_v1。

跳数索引的用法：

(1) minmax: 记录一组数据内的最小和最大极值，其索引的作用类似分区目录的 minmax 索引，能够快速跳过无用的数据区间。

INDEX a ID TYPE minmax GRANULARITY 5

(2) set: 记录声明字段或表达式的取值 (唯一值，无重复), 完整形式为 set (max_rows)，表示在一个 index_granularity 内，索引最多记录的数据行数，如果 max_rows=0 表示无限制。

INDEX b (length(ID) * 8) TYPE set(100) GRANULARITY 5

(3) ngrambf_v1: 记录数据短语的布隆过滤器，支持 string 和 fixedstring 数据类型。且只对 in,notIn,like,equals 和 notEquals 有用。

INDEX c (ID, Code) TYPE ngrambf_v1(3,256,2,0) GRANULARITY 5

• n: token 长度，依据 n 的长度将数据切割为 token 短语
• size_of_bloom_filter_in_bytes: 布隆过滤器的大小
• number_of_hash_functions: 布隆过滤器中使用 Hash 函数的个数
• random_seed: Hash 函数的随机种子

(4) tokenbf_v1: 属于 ngrambf_v1 的变种。变更了 token 的处理方法，会自动按照非字符的，数字的字符串分割 token。

INDEX d ID TYPE tokenbf_v1(256,2,0) GRANULARITY 5

数据存储#

各列独立存储#

每个列对应的字段都有对应的.bin 数据文件，以分区目录的形式被组织存放。

按列独立存储的设计优势：

1. 可以更好的进行数据压缩（相同类型的数据放在一起对压缩友好）
2. 可以最小化数据扫描的范围

数据是经过压缩的，目前支持 LZ4, ZSTD, Multiple 和 Delta 几种算法，默认使用 LZ4。其次数据会按照 ORDER BY 的声明排序；最后，数据是以压缩数据块的形式被组织写入.bin 文件中。

压缩数据块#

一个压缩数据块由头部信息和压缩数据两部分组成。头部信息固定使用 9 位字节表示，具体由 1 个 UInt8（1 字节）整形和 2 个 UInt32（4 字节）整型组成，分别代表使用的压缩算法类型，压缩后的数据大小和压缩前的数据大小。

.bin 压缩文件是由多个压缩数据块组成的，每个压缩数据块的头信息是基于 CompressionMethod_CompressedSize_UncompressedSize 公式生成。

clickhouse-compressor 工具，可以查询.bin 文件中压缩数据的统计信息。

每个压缩数据块的体积，按照其压缩前的数据字节大小，都被严格控制在 64KB~1MB, 上下限有 min_compress_block_size（65536）与 max_compress_block_size（1048576）指定。一个压缩数据快最终的大小，和一个间隔内 (index_granularity) 数据的实际大小相关。

MergeTree 的数据具体写入过程，会依照索引粒度，按批次获取数据并进行处理。如果把未压缩大小设为 size, 则整个写入过程遵循以下原则：

（1）单个批次数据 size<64KB: 则继续获取下一批次数据，直到累积到 size>64KB, 生成下一个压缩数据快。

（2）单个批次数据 64KB <=size<=1MB: 直接生成下一个压缩数据块

（3）单个批次数据 size>=1MB: 则首先按照 1MB 大小截断并生成下一个压缩数据块。剩余数据继续依照上述规则执行，此时会出现一个批次数据生成多个压缩数据块的情况

在.bin 文件中引入压缩数据块的目的至少有以下两个：

一、虽然数据被压缩后能过有效减少数据大小，降低存储空间并加速数据传输效率，但数据的压缩和解压动作，也会带来额外的性能损耗，所以需要控制被压缩数据的大小，以求在性能损耗和压缩率之间寻求一种平衡。

二、在具体读取某一列数据时，首先需要将压缩数据加载到内存并解压。通过压缩数据块，可以在不读整个.bin 的情况下将读取粒度降低到压缩数据块界别，从而进一步缩小数据读取的范围

数据标记#

数据标记的生成原则#

数据标记和索引区间是对齐的，均按照 index_granularity 的粒度间隔。因此只需要通过索引区间的下标编号就可以直接找到对应的数据标记

数据标记文件和 .bin 文件一一对应，用于记录数据在.bin 文件中的偏移量信息。

一行标记数据使用一个元组表示，元组内包含两个整形数值的偏移量信息。分别表示此段数据区间内，在对应的 .bin 压缩文件中，压缩数据快的起始偏移量；以及将该数据压缩块解压后，其未压缩数据的其实偏移量。

标记数据与一级索引数据不同，它不能常驻内存，而是使用 LRU 缓存策略加快其去用速度。

数据标记的工作方式#

在读取数据时，必须通过标记数据的位置信息才能够找到所需要的数据。整个查找过程大致分为读取压缩数据快和读取数据两个步骤。

MergeTree 是如何定位压缩数据块，并读取数据的：

（1）读取压缩数据快：在查询某一列数据是， MergeTree 无须一次性加载整个.bin 文件，而是可以根据需要，只加载特定的压缩数据快，而这项特性需要借助标记文件中所保存的压缩文件中的偏移量。

（2）读取数据：在读取解压后的数据是，MergeTree 并不需要一次性扫描整段解压数据，它可以根据需要，从 index_granularity 的粒度加载特定的一小段。

对于分区、索引、标记和压缩数据的协同总结#

写入过程#

数据写入的第一步是生成分区目录，伴随着每一批数据的写入，都会生成一个新的分区目录。在后续的某一时刻，属于相同分区的目录会依照规则合并到一起；接着，按照 index_granularity 索引粒度，会分成生成 primary.idx 一级索引或二级索引，每一个列字段的.mrk 数据标记和.bin 压缩数据文件。

查询过程#

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

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

数据标记与压缩数据块的对应关系#

由于压缩数据块的划分，与一个间隔（index_granularity）内的数据大小相关，每个压缩数据块的体积都被严格控制在 64KB-1MB. 而一个间隔的数据，又只会产生一行数据标记。那么根据一个间隔内数据的实际字节大小，数据标记和压缩数据块之间会产生三种不同的对应关系。

多对一#

多个数据标记对应一个压缩数据块，当一个间隔内的数据未压缩大小 size 小于 64 KB 时，会出现这种对应关系。

一对一#

一个数据标记对应一个压缩数据块，当一个间隔内的数据未压缩大小 size 大于等于 64 KB 且小于等于 1 MB 时，会出现这种关系。

一对多#

一个数据标记对应多个压缩数据快，当一个间隔内的压缩数据为压缩大小 size 直接大于 1 MB 时，会出现这种关系。

第 7 章 MergeTree 系列表引擎#

目前在 ClickHouse 中，按照特点可以将表引擎大致分成 6 个系列，分别是合并树，外部存储，内存，文件，接口和其它，每一个系列的表引擎都有着独自的特点和使用场景。

MergeTree#

MergeTree 作为家族系列最基础的表引擎，提供了数据分区，一级索引和二级索引等功能。

数据 TTL#

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

TTL 都需要依赖某个 DateTime 或 Date 类型的字段，通过对这个时间字段的 INTERVAL 操作，来表示 TTL 的过期时间。

列级别 TTL#

如果想要设置列级别的 TTL，则需要在定义表字段的时候，为他们声明 TTL 表达式，主键字段不能被声明 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 toYYYMM(create_time)
ORDER BY id

目前 ClickHouse 没有提供取消列级别 TTL 的方法

表级别 TTL#

如果想要为整张表设置 TTL，需要在 MergeTree 的表参数中增加 TTL 表达式。

create table ttl_table_v2 (
	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 toYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY

表级别 TTL 目前也没有取消的办法

TTL 的运行机理#

如果设置了 TTL 表达式，那么在写入数据时，会以数据分区为单位，在每个分区目录生成一个名为 ttl.txt 的文件，通过 JSON 配置保存了 TTL 的相关信息：

• columns 用于保存列级别的 TTL 信息
• table 用于保存表级别的 TTL 信息
• min 和 max 则保存了当前数据分区内，TTL 指定日期字段的最小值，最大值分别与 INTERVAL 表达式计算后的时间戳。
  大致的处理逻辑为：

1. MergeTree 以分区目录为单位，通过 ttl.txt 文件记录过期时间，并将作为后续的判断依据。
2. 每当写入一批数据时，都会基于 INTERVAL 表达式的计算结果为这个分区生成 ttl.txt 文件
3. 只有在 MergeTree 合并分区时，才会触发删除 TTL 过期数据的逻辑
4. 在选择删除的分区时，会使用贪婪算法，它的算法规则是尽可能找到会最早过期的，同时年纪又是最老的分区（合并次数更多，MaxBlockNum 更大的）
5. 如果一个分区内某一列数据因为 TTL 到期全部被删除了，那么在合并之后生成的新分区目录中，将不会包含这个列字段的数据文件（.bin 和.mrk).
   注意：
6. TTL 默认的合并频率由 MergeTree 的 merge_with_ttl_timeout 参数控制，默认 86400 秒，即一天。它维护的是一个专有的 TTL 任务队列。有别于 MergeTree 的常规合并任务，如果这个值被设置的过小，可能会带来性能损耗。
7. 除了被动触发 TTL 合并外，也可以使用 optimize 命令强制触发合并。

optimize table table_name
optimize table table_name FINAL   # 触发所有分区合并

3. clickhouse 目前没有提供删除 TTL 声明的方法，但是提供了控制全局 TTL 合并任务的启停方法。

SYSTEM STOP/START TTL MERGES

多路径存储策略#

MergeTree 实现了自定义存储策略的功能，支持以数据分区为最小移动单位，将分区目录写入多块磁盘目录。
目前有三类存储策略：

• 默认策略： MergeTree 原本的存储策略，无须任何配置，所有分区会自动保存到 config.xml 配置中 path 指定的路径下
• JBOD 策略：这种策略适合服务器挂载了多块磁盘，但没有做 RAID 的场景。JBOD 是一种轮训策略，每次执行一次 INSERT 或者 MERGE，所产生的新分区会轮训写入各个磁盘。这种策略的效果类似 RAID 0, 可以降低单块磁盘的负载，在一定条件下能够增加数据并行读写的性能。如果单块磁盘发生故障，则会丢掉应用 JBOD 策略写入的这部分数据。
• HOT/COLD 策略：这种策略适合服务器挂载了不同类型磁盘的场景。将存储磁盘分为 HOT 与 COLD 两类区域。HOT 区域使用 SSD 这类高性能存储媒介，注重存取性能；COLD 区域则使用 HDD 这类高容量存储媒介，注重存取经济性。数据在写入累积到阈值时，数据会自行移动到 COLD 区域。而在每个区域的内部，也支持定义多个磁盘，所以在单个区域的写入过程中，也能应用 JBOD 策略。

ReplacingMergeTree#

拥有主键，但是它的主键没有唯一键的约束。意味着即便多行数据的主键相同，他们还是能够被正常写入。ReplacingMergeTree 是为了数据去重而设计的，能够在合并分区时删除重复的数据。

排序键 ORDER BY 所声明的表达式是后续作为判断数据是否重复的依据。
ReplacingMergeTree 是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。
处理逻辑：

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

SummingMergeTree#

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

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

AggregatingMergeTree#

处理逻辑：

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

第 9 章 数据查询#

ClickHouse 对于 SQL 语句的解析是大小写敏感的，这意味着 SELECT a 和 SELECT A 表示的语义是不相同的。

WITH 子句#

ClickHouse 支持 CTE（Common Table Expression，公共表表达式），以增强查询语句的表达。此形式，可以极大的提高语句的可读性和可维护型。
CTE 通过 WITH 子句表示，目前支持四中用法

1. 定义变量
   可以定义变量，这些变量能够在后续的查询子句中被直接访问。

WITH 10 AS start
SELECT number FROM system.numbers
WHERE number > start
LIMIT 5

2. 调用函数

WITH SUM（data_uncompressed_bytes) AS bytes
SELECT database, formatReadableSize(bytes) AS format FROM system.columns
GROUP BY database
ORDER BY bytes DESC

3. 定义子查询

WITH(
	SELECT SUM(data_uncompressed_bytes) FROM system.columns
) AS total_bytes
SELECT database, (SUM(data_uncompressed_bytes)/total_bytes) * 100 AS database_disk_usage
FROM system.columns
GROUP BY database
ORDER BY database_disk_usage DESC

在 WITH 中使用子查询时，需要注意：该查询语句只能返回一行数据，如果结果集的数据大于一行则会抛出异常。
4. 在子查询中重复使用 WITH

WITH（round(database_disk_usage))AS database_disk_usage_v1 SELECT database, database_disk_usage, database_disk_usage_v1 FROM (WITH (SELECT SUM(data_uncompressed_bytes) FROM system.columns) AS total_bytes SELECT database, (SUM(data_uncompressed_bytes)/total_bytes)*100 AS database_disk_usage FROM system.columns GROUP BY database ORDER BY database_disk_usage DESC)

FROM 子句#

FROM 子句表示从何处读取数据，目前支持 3 中形式

1. 从数据表中取数

SELECT watch_id FROM hits_v1

2. 从子查询中取数

SELECT max_watch_id from (SELECT MAX(watch_id) from hits_v1)

3. 从表函数中取数

SELECT number FROM numbers(5)

在 FROM 子句后，可以使用 Final 修饰符，它可以配合 CollapsingMergeTree 和 VersionedCollapsingMergeTree 等表引擎进行查询操作，以强制在查询过程中合并，但由于 Final 修饰符会降低查询性能，所以应该尽可能避免使用它。

SAMPLE 子句#

SAMPLE 子句能够实现数据采样的功能，使查询仅返回采样数据而不是全部数据，从而有效减少负载。SAMPLE 子句的采样机制是一种幂等设计，也就是说在数据不发生变化的情况下，使用相同的采样规则总是能够返回相同的数据，所以这项特性非常适合在那些可以接受近似查询结果的场景使用。
SAMPLE 子句只能用于 MergeTree 系列引擎的数据表，并且要求在 create table 时声明 sample by 抽样表达式。

CREATE TABLE hits_v1 (
	counterid UInt64,
	EventDate DATE,
	UserID UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM（EventDate）
ORDER BY (counterid, intHash32(UserID))
SAMPLE BY intHash32(UserID)

声明 sample key 的时候需要注意：

• sample by 所声明的表达式必须同事包含在主键的声明内；
• sample key 必须是 Int 类型，如果不是，clickhouse 在进行 create table 操作是不会报错，但是在查询是会出现异常。
  子句的三种用法：

1. SAMPLE factor
   SAMPLE factor 表示按因子系数采样，其中 factor 表示采样因子，它的取值支持 0～1 之间的小数。如果 factor 设置为 0 或者 1, 则效果等同于不进行数据采样。
2. SAMPLE rows
   SAMPLE rows 表示按样本数量采样，其中 rows 表示至少采样多少行数据，它的取值必须是大于 1 的整数。如果 rows 的取值大于表内数据的总行数，则效果等于 rows=1 (即不使用采样)
3. SAMPLE factor OFFSET n
   表示按因子系数和偏移量采样，其中 factor 表示采样因子，n 表示偏移多少数据后才开始采样，他们两个的取值都是 0~1 之间的小数。

ARRAY JOIN 子句#

ARRAY JOIN 子句允许在数据表的内部，与数组或嵌套类型的字段进行 JOIN 操作，从而将一行数组展开为多行。目前支持 inner 和 left 两种 join 策略：

1. inner array join
   ARRAY JOIN 在默认情况下使用的是 INNER JOIN 策略。
   数据基于 value 数组被展开成了多行，并且排除掉了空数组。

2. LEFT ARRAY JOIN
   不会排除掉空数组，对多个数组字段进行 array join 操作时，查询的计算逻辑是按行合并而不是产生笛卡儿积。

JOIN 子句#

语法包含连接精度和连接类型两部分。
连接精度分为 ALL, ANY 和 ASOF 三种，而连接类型也可分为外连接、内连接和交叉连接三种。

连接精度#

连接精度决定了 JOIN 查询在连接数据时所使用的策略，目前支持 ALL，ANY 和 ASOF 三种类型。默认是 ALL，可以通过 join_default_strictness 配置参数修改默认的连接精度类型。

ALL#

如果左表内的一行数据，在有表中有多行数据与之连接匹配，则返回有表中全部连接的数据，而判断连接匹配的依据是左表与右表内的数据，基于连接键的取值完全相等，等同于 left.key=right.key。

ANY#

如果坐标内的一行数据，在有表中有多行数据与之连接匹配，则进返回有表中第一行连接的数据。ANY 和 ALL 判断连接匹配的依据相同。

ASOF#

ASOF 是一种模糊连接，它允许在连接键之后追加定义一个模糊连接的匹配条件 asof_column。

SELECT a.id, a.name, b.rate, a.time, b.time FROM join_tab1 AS a ASOF INNER JOIN join_tb2 AS B ON a.id=b.id AND a.time = b.time

其中 a.id = b.id 是寻常的连接键，a.time=b.time 则是 asof_column 模糊连接条件，语义等同于a.time>= b.time
注意：asof_column 必须是整型，浮点型和日期这类有序序列的数据类型。asof_column 不能是数据表内的唯一字段，换言之，连接键 (JOIN_KEY) 和 asof_column 不能是同一字段。

连接类型#

连接类型决定了 JOIN 查询组合左右两个数据集合要用的策略，他们所行程的结果是交集，并集，笛卡儿积或者其它形式。

INNER#

INNER JOIN 表示内连接，在查询时会以左表为基础支行遍历数据，然后从右表中找出与左边连接的行，它只会返回左表与右表两个数据集合中交集的部分，其余部分都会被排除。

OUTER#

表示外连接，它可以进一步细分为左外链接（LEFT），右外连接（RIGHT）和全外连接（FULL）三种形式。根据连接形式不同，其返回数据集合的逻辑也不尽相同。

1. LEFT
   在进行左外连接查询时，会以左表为基础逐行遍历数据，然后从有表中找出与左边连接的行以补齐属性。如果右表中没有找到连接的行，则采用相应字段数据类型的默认值填充。换言之，对于左连接查询而言，左表的数据总是能够全部返回。
2. RIGHT
   右外连接查询的效果与左连接恰好相反，右表的数据总是能够全部返回，而左表不能连接的数据则使用默认值补全。
   在进行右外连接查询时，内部的执行逻辑大致如下：
   (1) 在内部进行类似 INNER JOIN 的内连接查询，在计算交集部分的同时，顺带记录有表中那些未能被连接的数据行。
   (2) 将那些未能被连接的数据航追加到交集的尾部
   (3) 将追加数据中那些属于左表的列字段用默认值补全。
3. FULL
   全外连接内部的执行逻辑大致如下：
   (1) 会在内部进行类似 LEFT JOIN 的查询，在左外连接的过程中，顺带记录右表中已经被连接的数据行。
   (2) 通过在右表中记录已被连接的数据航，得到未被连接的数据行
   (3) 将右表中未被连接的数据追加至结果集，并将那些属于左表中的列字段以默认值补全。

CROSS#

表示交叉连接，它会返回左表与右表两个数据集合的笛卡儿积。

多表连接#

在进行多张数据表的连接查询时，ClickHouse 会将它们转为两两连接的形式。

注意事项#

关于 JOIN 查询的注意事项

1. 关于性能
   为了能够优化 JOIN 查询性能，首先应该遵循左大右小的原则。其次，JOIN 查询目前没有缓存的支持。最后，如果是在大量维度属性补全的查询场景中，则建议使用字典代替 JOIN 查询。
2. 关于空值策略与简写形式

连接查询的空值是由默认值填充的。连接查询的空值策略是通过 join_use_nulls 参数指定的，默认为 0。当参数值为 0 时，空值由数据类型的默认值填充；而当参数值为 1 时，空值由 Null 填充。

WHERE 与 PREWHERE 子句#

WHERE 子句基于条件表达式来实现数据过滤。如果过滤条件恰好是主键字段，则能够进一步借助索引加速查询，所以 WHERE 子句是一条查询语句是否启用索引的判断依据。
PREWHERE 目前只能用于 MergeTree 系列的表引擎，它可以看作对 WHERE 的一种优化，其作用于 WHERE 相同，均是用来过滤数据。不同之处在于，使用 PREWHERE 时，首先只会读取 SELECT 声明的列字段以补全其余属性。在一些场合下，PREWHERE 相比 WHERE，处理数据量更少，性能更高。
在以下情形下 WHERE 不会自动优化：

• 使用了常量表达式
• 使用了默认值为 ALIAS 类型的字段
• 包含了 arrayJoin, globalIn, globalNotIn 或者 indexHint 的查询
• SELECT 查询的列字段与 WHERE 谓词相同
• 使用了主键字段

当使用 prewhere 进行主键查询时，首先会通过稀疏索引过滤数据区间，接着会读取 prewhere 指定的条件列以进一步过滤，这样依赖就有可能截掉数据区间的尾巴，从而返回低于 index_granularity 粒度的数据范围，即便如此相比其它场合移动谓词所带来的性能提升，这类效果还是有限的。

GROUP BY 子句#

表示聚合查询。是让 clickhouse 最凸显卓越性能的地方，在 GROUP BY 后声明的表达式，成为聚合键或者 key, 数据会按照聚合键进行聚合。
在某些场合下，可以借助 any, max 和 min 等聚合函数访问聚合键之外的列字段。

WITH ROLLUP#

ROLLUP 能够按照聚合见从右向上卷数据，基于聚合函数依次生成分组小计和总计。

WITH CUBE#

CUBE 会想立方体模型一样，基于聚合键之间所有的组合生成小计信息。

WITH TOTALS#

会基于聚合函数对所有数据进行总计。

HAVING 子句#

需要与 GROUP BY 同时出现，不能单独使用。它能够在聚合计算之后实现二次过滤数据。

ORDER BY 子句#

ORDER BY 子句通过声明排序键来指定查询数据返回时的顺序。在 MergeTree 中指定 ORDER BY 后，数据在各个分区内会按照其定义的规则排序，这是一种分区内的局部排序。如果在查询时数据跨越了多个分区，则他们的返回顺序是无法预知的，每一次查询返回的顺序都可能不同。在这种情况下，如果需要数据总是能够按照期望的顺序返回，就需要 ORDER BY 子句来指定全局顺序。

ORDER BY 在使用时可以定义多个排序键，每个排序键后需紧跟 ASC（升序）或 DESC（降序）来确定排序顺序。默认 ASC

1. NULLS LAST NULL 值放在最后，默认行为
2. NULLS FIRST NULL 值放在最前。

LIMIT BY 子句#

LIMIT BY 运行与 ORDER BY 之后和 LIMIT 之前，能够按照指定分区，最多返回前 n 行数据。常用于 TOP N 的查询场景。

LIMIT 子句#

LIMIT 子句用于返回指定的前 N 行数据，常用于分页场景。三种语法格式如下：

limit n
limit n offset m
limit m,n

在使用 limit 子句是注意，如果数据跨越了多个分区，在没有使用 ORDER BY 指定全局顺序的情况下，每次 LIMIT 查询返回的数据有可能不同。

SELECT 子句#

决定了查询语句最终返回哪些列字段或表达式。虽然 SELECT 位于 SQL 语句的起始位置，但它却是在上述一众子句之后执行的。在其它子句执行后，SELECT 会将选取的字段或表达式作用于每行数据上。如果使用*通配符，则会返回数据表的所有字段。

DISTINCT 子句#

用于去除重复数据。能够和 GROUP BY 同时使用，它们是互补而不是互斥关系。

UNION ALL 子句#

UNION ALL 子句能够联合左右两边的两组子查询，将结果一并返回。一次查询中，可以声明多次 UNION ALL 以便联合多组穿，但 UNION ALL 不能直接使用其他子句。

查询 SQL 执行计划#

（1）通过将 ClickHouse 服务日志设置到 DEBUG 或者 TRACE 级别，可以变相实现 EXPLAIN 查询，以分析 SQL 的执行日志
（2）需要真正执行了 SQL 查询，CH 才能打印计划日志，所以如果表的数据量很大最好借助 LIMIT 子句减小查询返回的数量
（3）不要使用 SELECT * 全字段查询
（4） 尽可能利用各种索引（分区索引，一级索引，二级索引），这样可避免全表扫描。

clickhouse-client -h 127.0.0.1 -u uts --port 9100 --password ~Uts2020db --send_logs_level=trace <<< "select timestamp,sip4,sip6,sport,dip4,dip6,dport,protocol,app_proto,app_desc_cn,dmac,s_card_name,s_device_hash,tx_bytes,tx_pkts,rx_bytes,rx_pkts,isIPv4,direct,first_time,last_time,sid from uts.storage_session prewhere sid global in (select sid from uts.storage_session prewhere timestamp > 1698826475 and timestamp <= 1699337215 and msgtype = 12 order by timestamp desc limit 10 offset 2357) order by timestamp desc" > /dev/null

第 10 章 副本与分片#

集群是副本和分片的基础，他将 clickhouse 的服务拓扑由单点延伸到多个节点，但它并不像 Hadoop 生态的某些系统那样，要求所有节点组成一个单一的大集群。ClickHouse 的集群配置非常灵活，用户既可以将所有节点组成一个单一集群，也可以按照业务的诉求，把节点划分为多个小的集群。在每一个小的集群区域之间，他们的节点，分区和副本 数量可以各不相同。

从作用来看，clickhouse 集群的工作更多是针对逻辑层面的。集群定义了多个节点的拓扑关系，这些节点在后续服务过程中可能会协同工作，而执行层面的具体工作则交给了副本和分片来执行。
副本和分片的区分方法
一：从数据层面来分，加入 clickhouse 的 N 个节点组成了一个集群，在集群的各个节点上，都有一张结果相同的数据表 Y。如果 N1 的 Y 和 N2 的 Y 中的数据完全不同，则 N1 和 N2 互为分片。如果他们的数据完全相同，则他们互为副本。
二：从功能作用层面区分，使用副本的主要目的是防止数据丢失，增加数据存储的荣誉；而使用分片的主要目的是实现数据的水平切分。

数据副本#

只有使用了 ReplicatedMergeTree 复制表系列引擎，才能应用副本的能力。或者使用 ReplicatedMergeTree 的数据表就是副本。

副本的特点#

作为数据副本的主要实现载体，ReplicatedMergeTree 在设计上有一些显著特点。

• 依赖 ZooKeeper: 在执行 INSET 和 ALTER 查询的时候，ReplicatedMergeTree 需要借助 ZooKeeper 的分布式协同能力，以实现多个副本之间的同步，但是在查询副本的时候，并不需要使用 ZooKeeper.
• 表级别的副本：副本是在表级别定义的，所以每张表的副本配置都可以按照它的实际需求进行个性化定义，包括副本的数量，以及副本在集群内的分布位置等。
• 多主架构：可以在任意一个副本上执行 INSERT 和 ALTER 查询，他们的效果是相同的。这些操作会借助 ZooKeeper 的协同能力被分发到每个副本以本地形式执行。
• Block 数据快：在执行 INSERT 命令写入数据时，会依据 max_insert_block_size 的大小（默认 1048576 行）将数据切分成若干个 Block 数据块，所以 Block 数据块是数据写入的基本单元，并且具有写入的原子性和唯一性
• 原子性：在数据写入时，一个 block 块内的数据要么全部写入成功，要么全部失败
• 唯一性：在写入一个 Block 数据块的时候，会按照当前 Block 数据快的数据顺序，数据行和数据大小等制表，计算 Hash 信息摘要并记录在案。在此之后，如果某个写入的 Block 数据快与之前写入的 Block 块拥有相同的 Hash 摘要（Block 数据块内数据顺序，数据大小和数据行均相同），则该 Block 数据块会被忽略。

副本的定义形式#

使用副本的好处：

• 增加了数据的冗余存储，所以降低了数据丢失的风险
• 副本采用了多主架构，所以每个副本实例都可以作为数据读，写的入口，分摊了节点的负载

ReplicatedMergeTree 原理解析#

数据结构#

核心逻辑中，大量的使用了 ZooKeeper 的能力，以实现多个 ReplicatedMergeTree 副本实例之间的协同，包括主副本选举，副本状态感知，操作日志分发，任务队列和 BlockID 去重判断等。在执行 INSERT 数据写入，MERGE 分区和 MUTATION 操作的时候，都会涉及与 ZooKeeper 的通信。但是在通信过程中并不会设计任何表数据的传输，在查数据的时候也不会访问 ZooKeeper。