ClickHouse引擎及原理解析

ClickHouse主要分为数据库引擎和数据表引擎两大类引擎，每类引擎下面又有很多分类：

引擎决定了数据的存储位置、存储结构、表的特征（DDL、DDL、是否支持并发操作）。

1、数据库引擎

数据库引擎 | ClickHouse文档

ClickHouse目前支持的数据库引擎主要有以下几种：

• Atomic：默认引擎，在绝大多数情况下我们都会使用默认引擎，它提供了可配置的table engines和SQL dialect。
• Lazy：日志引擎，在距最近一次访问间隔 expiration_time_in_seconds内，将表保存在内存中，只支持Log系列的表引擎。
• MySQL：MySQL引擎，MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，支持INSERT和SELECT查询，MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中。
• PostgreSQL：允许连接到远程PostgreSQL服务。支持读写操作，以在ClickHouse和PostgreSQL之间交换数据。
• SQLite：允许连接到SQLite数据库，并支持ClickHouse和SQLite交换数据， 执行 INSERT 和 SELECT 查询。

详情参考：
https://www.cnblogs.com/the-pig-of-zf/p/16771673.html

2、表引擎及应用

表引擎 | ClickHouse文档

ClickHouse支持的表引擎分为MergeTree、Log、Integration和Special四个系列，本文主要对这四类表引擎进行概要介绍，并通过示例介绍常用表引擎的功能。

2.1 Log系列

Log系列表引擎功能相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。

几种Log表引擎的共性是：

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

2.2 Integration系列

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

2.3 Special系列

Special系列的表引擎，大多是为了特定场景而定制的。

2.4 MergeTree系列

MergeTree系列是官方主推的存储引擎，支持ClickHouse大部分核心功能。

MegeTree家族：

MergeTree

MergeTree表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等，MergeTree支持所有ClickHouse SQL语法。

建表语句：

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],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    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, ...]

• PARTITION BY — 分区键 【选填】

用于指定表数据以什么标准进行分区。即可以是单个字段，也可以是元组形式的多个列字段，另外支持使用列表达式。如果不声明分区间，ClickHouse会默认生成名为all的分区。

• ORDER BY — 排序键【必填】

用于指定在一个数据片段内的数据排序方式，默认情况下与主键相同，可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。如果不需要排序，可以使用 ORDER BY tuple()。

• PRIMARY KEY — 主键【选填】

声明后会按照主键生成一级索引，用于加速表查询。若不指定则直接使用ORDER BY字段作为主键。

注意：PRIMARY KEY必须是ORDER BY字段的前缀。如ORDER BY（A，B），则PRIMARY A是对的，PRIMARY B是错的。

• SAMPLE BY - 用于采样的表达式【选填】

用于声明数据用什么汇总标准进行采样，使查询仅返回采样数据而不是全部数据。使用时主键也需要声明同样的表达式，例如：

PRIMARY (CounterID, EventDate, intHash32(UserID))

SAMPLE BY intHash32(UserID)

需要注意的是抽样表达式必须包含在主键声明内，且SAMPLE KEY必须是int类型。

• TTL -数据存活时间【选填】

指定表或者列存储的时间（生命周期），以及数据片段在硬盘和卷上的移动逻辑。表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：

过期类型：TTL date + INTERVAl 1 DAY 代表数据的存活时间是date的1天之后

规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。

默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。

• SETTINGS — 控制 MergeTree 行为的额外参数，可选项：
• index_granularity — 索引粒度。默认值8192 ，代表每间隔8192行数据才会生成一条索引。
• index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。
• min_index_granularity_bytes - 允许的最小数据粒度，默认值：1024b。该选项用于防止误操作，添加了一个非常低索引粒度的表。
• 。。。
• 参考：SETTINGS参数

示例：

--建表：
CREATE TABLE table1 (
  p_date String,
  id UInt16,
  name Nullable(String)
) 
ENGINE = MergeTree()
PARTITION BY p_date
PRIMARY KEY name;


--插入几条primary key相同的数据
insert into table1 
values
('2022-10-30', 1, 'aaa'),
('2022-10-30', 1, 'bbbb'),
('2022-10-31', 2, 'ccc'),
('2022-11-02', 3, 'dddd'),
('2022-11-02', 3, 'dddd');


--强制后台合并
optimize table table1 final;

select count(*) from table1;
┌─count()─┐
│      10 │
└─────────┘

select * from table1;
┌─p_date──┬──id───┬─name───┐
│ 2022-10-30 │       1 │ aaa       │
│ 2022-10-30 │       1 │ bbbb      │
│ 2022-10-31 │       2 │ ccc       │
│ 2022-11-02 │       3 │ dddd      │
│ 2022-11-02 │       3 │ dddd      │
└────────────┴─────────┴───────────┘

可以看到虽然主键id相同的数据只有3条数据，但是结果却有5行，强制后台合并执行完毕，再次查询，发现仍旧有5条数据。结合以上示例可以看到，MergeTree虽然有主键索引，但是其主要作用是加速查询，而不是类似MySQL等数据库用来保持记录唯一。

optimize执行原理

ReplacingMergeTree

为了解决MergeTree相同主键无法去重的问题，ClickHouse提供了ReplacingMergeTree引擎，它会删除同一分区内ORDER BY字段相同的重复数据。

建表SQL:

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

• ver — 版本列【选填】

类型为 UInt*、Date 或 DateTime。在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：

• 如果 ver 列未指定，保留最后一条。
• 如果 ver 列已指定，保留 ver 值最大的版本。

缺点：

• 在没有彻底optimize之前，可能无法达到排序去重的效果；
• 以数据分区为单位删除重复数据，不同分区之间的重复数据不会被删除；
• optimize是后台动作，无法预测具体执行时间点；
• 手动执行optimize在海量数据场景下要消耗大量时间，无法满足业务即时查询的需求。

因此ReplacingMergeTree多用于确保数据最终被去重，而无法保证查询过程中去重。

示例：

-- 建表
CREATE TABLE replace_table (
  create_time DateTime,
  id UInt16,
  name String
)
ENGINE = ReplacingMergeTree(create_time)
PARTITION BY toYYYYMMDD(create_time)
ORDER BY  (id, name)
PRIMARY KEY id;

-- 写入主键重复的数据
insert into replace_table 
values
('2022-10-30', 1, 'aaa'),
('2022-10-30', 1, 'aaa'),
('2022-10-30', 1, 'bbbb'),
('2022-10-31', 2, 'ccc'),
('2022-11-01', 3, 'dddd'),
('2022-11-02', 3, 'dddd');

-- 查询，可以看到未合并之前，排序键重复的数据，仍旧存在。
select count(*) from replace_table;
┌─count()─┐
│       6 │
└─────────┘

-- 强制后台合并：
optimize table replace_table final;


-- 再次查询：排序键重复的数据已经消失。
select count(*) from replace_table;
┌─count()─┐
│       5 │
└─────────┘

select * from replace_table;
┌─────────create_time─┬─id─┬─name─┐
│ 2022-11-02 00:00:00 │  3 │ dddd │
└─────────────────────┴────┴──────┘
┌─────────create_time─┬─id─┬─name─┐
│ 2022-11-01 00:00:00 │  3 │ dddd │
└─────────────────────┴────┴──────┘
┌─────────create_time─┬─id─┬─name─┐
│ 2022-10-31 00:00:00 │  2 │ ccc  │
└─────────────────────┴────┴──────┘
┌─────────create_time─┬─id─┬─name─┐
│ 2022-10-30 00:00:00 │  1 │ aaa  │
│ 2022-10-30 00:00:00 │  1 │ bbbb │
└─────────────────────┴────┴──────┘

-- 使用final语句查询，可以看到实现了按照order by去重展示：
select count(*) from replace_table final;
─count()─┐
│       4 │
└─────────┘
select * from replace_table final;
┌─────────create_time─┬─id─┬─name─┐
│ 2022-10-30 00:00:00 │  1 │ aaa  │
│ 2022-11-02 00:00:00 │  3 │ dddd │
└─────────────────────┴────┴──────┘
┌─────────create_time─┬─id─┬─name─┐
│ 2022-10-30 00:00:00 │  1 │ bbbb │
└─────────────────────┴────┴──────┘
┌─────────create_time─┬─id─┬─name─┐
│ 2022-10-31 00:00:00 │  2 │ ccc  │
└─────────────────────┴────┴──────┘

可以看到ReplacingMergeTree是根据排序键实现去重，而不是主键。

CollapsingMergeTree

CollapsingMergeTree折叠合并树，以新增数据来实现删除操作。

该引擎要求在建表语句中指定一个标记列Sign，后台Compaction时会将主键相同、Sign相反的行进行折叠，也即删除。Sign=1的行称之为状态行，Sign=-1的行称之为取消行。每次需要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。在后台Compaction时，状态行与取消行会自动做折叠（删除）处理。而尚未进行Compaction的数据，状态行与取消行同时存在。

建表语句：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
  sign Int8,
  ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

• sign — 类型列的名称【必填】

数据类型 ：Int8，1 是?状态?行，-1 是?取消?行。

缺点：

• 不是实时触发，由于后台合并时机无法预测，在发起查询时，状态行和取消行可能尚未被折叠；
• 只有相同分区内的数据才能被折叠。因此在进行count(*)、sum(col)等聚合计算时，可能会存在数据冗余的情况。
• 状态行必须要在取消行之前插入，多线程并行写入情况下导致插入顺序错乱，则无法进行折叠。

示例：

-- 建表
CREATE TABLE callpase_table
(
  p_date String,
  id UInt16,
  name String,
  grade UInt8,
  sign Int8
)
ENGINE = CollapsingMergeTree(sign)
PARTITION BY p_date
ORDER BY (id,name)
PRIMARY KEY id;

-- 插入状态行，注意sign一列的值为1
INSERT INTO callpase_table VALUES ('2022-11-01', 1, '小明', 85, 1);

-- 插入一行排序字段相同取消行，用于抵消上述状态行。
INSERT INTO callpase_table VALUES ('2022-11-01', 1, '小明', 80, -1);

-- 再插入一条新的状态行
INSERT INTO callpase_table VALUES ('2022-11-01', 1, '小红', 90, 1);

-- 更新刚插入的状态行
INSERT INTO callpase_table VALUES ('2022-11-01', 1, '小红', 85, 1);


-- 查询数据：可以看到未Compaction之前，状态行与取消行共存。
SELECT * FROM callpase_table;
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┐
│ 2022-11-01 │  1 │ 小明 │    85 │    1 │
└────────────┴────┴──────┴───────┴──────┘
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┐
│ 2022-11-01 │  1 │ 小明 │    80 │   -1 │
└────────────┴────┴──────┴───────┴──────┘
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┐
│ 2022-11-01 │  1 │ 小红 │    85 │    1 │
└────────────┴────┴──────┴───────┴──────┘
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┐
│ 2022-11-01 │  1 │ 小红 │    90 │    1 │
└────────────┴────┴──────┴───────┴──────┘

-- 使用final语句查询
SELECT * FROM callpase_table final;
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┐
│ 2022-11-01 │  1 │ 小红 │    85 │    1 │
└────────────┴────┴──────┴───────┴──────┘

-- 强制后台合并
optimize table callpase_table final;

-- 再次查询，可以看到状态行、取消行已经被折叠，只剩下最新的一行状态行。
SELECT * FROM callpase_table;
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┐
│ 2022-11-01 │  1 │ 小红 │    85 │    1 │
└────────────┴────┴──────┴───────┴──────┘

CollapsingMergeTree虽然解决了排序字段相同的数据即时删除的问题，但是状态持续变化且多线程并行写入情况下，状态行与取消行位置可能乱序，导致无法正常折叠。示例：

-- 建表
CREATE TABLE callpase_table1
(
  p_date String,
  id UInt16,
  name String,
  grade UInt8,
  sign Int8
)
ENGINE = CollapsingMergeTree(sign)
PARTITION BY p_date
ORDER BY (id,name)
PRIMARY KEY id;

-- 先插入取消行
INSERT INTO callpase_table1 VALUES ('2022-11-01', 1, '小强', 85, -1);
-- 后插入状态行
INSERT INTO callpase_table1 VALUES ('2022-11-01', 1, '小强', 85, 1);


-- 使用final语句查询
SELECT * FROM callpase_table1 final;
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┐
│ 2022-11-01 │  1 │ 小强 │    85 │    1 │
└────────────┴────┴──────┴───────┴──────┘

-- 强制合并
optimize table callpase_table1 final;

-- 可以看到即便Compaction之后也无法进行折叠: 2行数据仍旧都存在。
select * from callpase_table1;
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┐
│ 2022-11-01 │  1 │ 小强 │    85 │   -1 │
│ 2022-11-01 │  1 │ 小强 │    85 │    1 │
└────────────┴────┴──────┴───────┴──────┘

VersionedCollapsingMergeTree

为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，
VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且Version相同、Sign相反的行，在合并时会被删除。

建表语句：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
  sign Int8,
  version UInt16,
  ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

• sign — 类型列的名称【必填】

数据类型 ：Int8，1 是?状态?行，-1 是?取消?行。

• version — 指定对象状态版本的列名【必填】

数据类型： UInt*

缺点：

• 不是实时触发，由于后台合并时机无法预测，在发起查询时，状态行和取消行可能尚未被折叠；
• 插入取消行时需要事先知道原状态行的版本值，只支持折叠具有排序键和版本相同的数据。

可以通过FINAL关键字实现查询时数据去重。

示例：

-- 建表
CREATE TABLE ver_callpase_table
(
  p_date String,
  id UInt16,
  name String,
  grade UInt8,
  sign Int8,
  ver UInt8
)
ENGINE = VersionedCollapsingMergeTree(sign, ver)
PARTITION BY p_date
ORDER BY (id,name)
PRIMARY KEY id;

-- 先插入取消行
INSERT INTO ver_callpase_table VALUES ('2022-11-01', 1, '小强', 85, -1, 1);
INSERT INTO ver_callpase_table VALUES ('2022-11-01', 1, '小刚', 85, -1, 1);
-- 后插入状态行
INSERT INTO ver_callpase_table VALUES ('2022-11-01', 1, '小强', 90, 1, 1);
INSERT INTO ver_callpase_table VALUES ('2022-11-01', 1, '小刚', 85, 1, 2);

-- 查询可以看到未compaction情况下，所有行都可见。
SELECT * FROM ver_callpase_table;
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┬─ver─┐
│ 2022-11-01 │  1 │ 小强 │    90 │    1 │   1 │
│ 2022-11-01 │  1 │ 小刚 │    85 │    1 │   2 │
└────────────┴────┴──────┴───────┴──────┴─────┘
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┬─ver─┐
│ 2022-11-01 │  1 │ 小强 │    85 │   -1 │   1 │
│ 2022-11-01 │  1 │ 小刚 │    85 │   -1 │   1 │
└────────────┴────┴──────┴───────┴──────┴─────┘

-- 使用final语句查询
SELECT * FROM ver_callpase_table final;
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┬─ver─┐
│ 2022-11-01 │  1 │ 小刚 │    85 │    1 │   2 │
└────────────┴────┴──────┴───────┴──────┴─────┘
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┬─ver─┐
│ 2022-11-01 │  1 │ 小刚 │    85 │   -1 │   1 │
└────────────┴────┴──────┴───────┴──────┴─────┘

-- 强制后台合并
optimize table ver_callpase_table final;

SELECT * FROM ver_callpase_table;
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┬─ver─┐
│ 2022-11-01 │  1 │ 小刚 │    85 │    1 │   2 │
└────────────┴────┴──────┴───────┴──────┴─────┘
┌─p_date─────┬─id─┬─name─┬─grade─┬─sign─┬─ver─┐
│ 2022-11-01 │  1 │ 小刚 │    85 │   -1 │   1 │
└────────────┴────┴──────┴───────┴──────┴─────┘

当ClickHouse合并数据部分时，它会删除具有相同排序字段和版本但Sign值不同的一对行数据，行的顺序并不重要。

当ClickHouse插入数据时，它会按排序字段对行进行排序。 如果 version 列不在排序键中，ClickHouse将其隐式添加到排序键作为最后一个字段并使用它进行排序。

SummingMergeTree

ClickHouse通过SummingMergeTree来支持对排序字段进行预先聚合。在后台合并时，会将排序字段相同的多行进行sum操作，然后将多行汇总结果到一行，从而大幅度降低存储空间占用，提升聚合计算性能。

建表SQL:

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    ...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

• columns — 包含了将要被汇总的列的列名的元组可选参数【选填】

所选的列必须是数值类型，并且不可位于主键中。

注意：

• SummingMergeTree引擎只支持sum聚合操作，且对聚合字段有类型要求，使用场景比较单一；
• 聚合操作不是实时触发，只在后台合并时才会进行数据的预先聚合，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，SQL中仍需要使用GROUP BY子句；
• 在预先聚合时，ClickHouse会对排序列之外的其他所有列进行预聚合。如果这些列是可聚合的（数值类型），则直接sum；如果不可聚合（如String类型），则随机选择一个值；
• 在定义引擎时若指定了columns汇总列（非主键的数据类型字段），则汇总这些指定字段；否则汇总所有的非主键数值类型字段。
• 只支持在同一分区内对相同排序Key的行进行汇总，若汇总字段非数值类型，则会取第一行数据的值；
• 建议将SummingMergeTree与MergeTree配合使用，使用MergeTree来存储具体明细，使用SummingMergeTree存储预先聚合的结果。

示例：

-- 建表
CREATE TABLE summing_table
(
  p_date String,
  id UInt16,
  name String,
  subject String,
  grade UInt16,
  full_grade UInt16
)
ENGINE = SummingMergeTree((grade, full_grade))
PARTITION BY p_date
ORDER BY (id,name)
PRIMARY KEY id;


-- 插入数据
INSERT INTO summing_table
values
('2022-10-30', 1, '小明', '语文', 88, 100),
('2022-10-30', 1, '小明', '数据', 95, 100),
('2022-10-30', 1, '小明', '英语', 80, 100),
('2022-10-30', 2, '小红', '语文', 95, 100),
('2022-10-30', 2, '小红', '数据', 85, 100),
('2022-10-30', 2, '小红', '英语', 90, 100);

-- 合并前查询，仍存在多行
select count(*) from summing_table;
┌─count()─┐
│       6 │
└─────────┘

-- 通过GROUP BY进行聚合计算
SELECT id,name,sum(grade) as grade, sum(full_grade) as full_grade 
FROM summing_table GROUP BY id,name
┌─id─┬─name─┬─grade─┬─full_grade─┐
│  1 │ 小明 │   263 │        300 │
│  2 │ 小红 │   270 │        300 │
└────┴──────┴───────┴────────────┘

-- 强制合并
optimize table summing_table final;

-- compaction后查询，可以看到数据已经被预先聚合
select * from summing_table;
┌─p_date──┬─id─┬─name─┬─subject─┬─grade─┬─full_grade─┐
│ 2022-10-30 │  1  │ 小明   │ 语文    │   263 │        300 │
│ 2022-10-30 │  2  │ 小红   │ 语文    │   270 │        300 │
└───────────┴────┴──────┴─────────┴───────┴────────────┘

--可以看到subject因为是字符串类型，所以默认取的第一行

AggregatingMergeTree

AggregatingMergeTree也是预先聚合引擎的一种，用于提升聚合计算的性能。与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数（）。

建表语句：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]

缺点：

• 语法比较复杂，需要结合物化视图或ClickHouse的特殊数据类型AggregateFunction一起使用；
• 在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法；
• 以数据分区为单位来聚合数据，不同分区之间的数据不会被计算。

以下通过示例进行介绍。

示例一：配合物化视图使用

-- 建立明细表
CREATE TABLE detail_table
(
  p_date String,
  id UInt16,
  name String,
  subject String,
  grade UInt16,
  full_grade UInt16
)
ENGINE = MergeTree()
PARTITION BY p_date
ORDER BY (id,name)
PRIMARY KEY id;

-- 对明细表建立物化视图，对明细数据预先聚合
-- 注意：预先聚合时使用的聚合函数为：xxxState
CREATE MATERIALIZED VIEW agg_table_view
ENGINE = AggregatingMergeTree() PARTITION BY p_date ORDER BY (id,name)
AS SELECT
		p_date,
    id,
    name,
    uniqState(subject) AS subject,
    sumState(grade)  AS total_grade,
    avgState(grade)  AS avg_grade,
    maxState(grade)  AS max_grade,
    minState(grade)  AS min_grade,
    sumState(full_grade)  AS full_grade
FROM detail_table
GROUP BY p_date,id,name;


-- 往明细表插入明细数据
INSERT INTO detail_table
values
('2022-10-30', 1, '小明', '语文', 88, 100),
('2022-10-30', 1, '小明', '数据', 95, 100),
('2022-10-30', 1, '小明', '英语', 80, 100),
('2022-10-30', 2, '小红', '语文', 95, 100),
('2022-10-30', 2, '小红', '数据', 85, 100),
('2022-10-30', 2, '小红', '英语', 90, 100);


-- 查询物化视图，普通函数 sum, uniq不再可以使用
SELECT
 		id,
    name,
    uniqMerge(subject) AS subject,
    sumMerge(total_grade)  AS total_grade,
    avgMerge(avg_grade)  AS avg_grade,
    maxMerge(max_grade)  AS max_grade,
    minMerge(min_grade)  AS min_grade,
    sumMerge(full_grade)  AS full_grade
FROM agg_table_view
GROUP BY id, name

┌─id┬─name─┬─subject┬─total_grade─┬───avg_grade─┬─max_grade─┬─min_grade─┬─full_grade─┐
│  1 │ 小明   │    3   │     263     │ 87.66666666666667 │        95 │        80 │        300 │
│  2 │ 小红   │    3   │     270     │                90 │        95 │        85 │        300 │
└──┴─────┴─────┴───────┴────────────┴──────┴───────┴─────────┘

示例二：使用特殊数据类型AggregateFunction

-- 建立明细表，插入明细数据
-- 使用上面的detail_table


-- 建立预先聚合表，
-- 注意：其中聚合字段类型为：AggregateFunction(xxx, UInt64)
CREATE TABLE agg_table
(   
  p_date String,
  id UInt16,
  name String,
  subject AggregateFunction(uniq, String),
  total_grade AggregateFunction(sum, UInt16),
  avg_grade AggregateFunction(avg, UInt16),
  max_grade AggregateFunction(max, UInt16),
  min_grade AggregateFunction(min, UInt16),
  full_grade AggregateFunction(sum, UInt16)
)
ENGINE = AggregatingMergeTree() 
PARTITION BY p_date 
ORDER BY (id, name);


-- 从明细表中读取数据，插入聚合表。
-- 注意：子查询中使用的聚合函数为 xxxState
INSERT INTO agg_table
select p_date, id, name,
	uniqState(subject) AS subject,
  sumState(grade)  AS total_grade,
  avgState(grade)  AS avg_grade,
  maxState(grade)  AS max_grade,
  minState(grade)  AS min_grade,
  sumState(full_grade)  AS full_grade
from detail_table
group by p_date,id, name


-- 从聚合表中查询。
-- 注意：select中使用的聚合函数为xxxMerge
SELECT
 		id,
    name,
    uniqMerge(subject) AS subject,
    sumMerge(total_grade)  AS total_grade,
    avgMerge(avg_grade)  AS avg_grade,
    maxMerge(max_grade)  AS max_grade,
    minMerge(min_grade)  AS min_grade,
    sumMerge(full_grade)  AS full_grade
FROM agg_table
GROUP BY id, name

┌─id┬─name─┬─subject┬─total_grade─┬───avg_grade─┬─max_grade─┬─min_grade─┬─full_grade─┐
│  1 │ 小明   │    3   │     263     │ 87.66666666666667 │        95 │        80 │        300 │
│  2 │ 小红   │    3   │     270     │                90 │        95 │        85 │        300 │
└──┴─────┴─────┴───────┴────────────┴──────┴───────┴─────────┘


--直接查询agg_table，无法展示，因为AggregateFunction的字段是使用二进制存储的
select * from agg_table;
┌─p_date─┬─id─┬─name─┬─subject─┬total_grade┬─avg_grade┬─max_grade┬─min_grade┬─full_grade┐
│ 2022-10-30 │  1 │ 小明 │ K?p#K?p???	 │          │          │ _        │ P         │ ,         │
│ 2022-10-30 │  2 │ 小红 │ K?p#K?p???	 │          │          │ _        │ U         │ ,         │
└───────┴──┴────┴───────┴──────┴──────┴───────┴──────┴───────┘

GraphiteMergeTree

该引擎用来对 Graphite数据进行瘦身及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用。

如果不需要对Graphite数据做汇总，那么可以使用任意的CH表引擎；但若需要，那就采用 GraphiteMergeTree 引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。

Graphite是什么？

https://graphite.readthedocs.io/en/latest/overview.html

https://z.itpub.net/article/detail/F1A8EBF41FE083F49923CC5FC40B3054

具体使用参考：GraphiteMergeTree

总结

从表可以看出：

• VersionedCollapsingMergeTree和ReplacingMergeTree+sign引擎可以实现增删改，并且并发更新、删除时可以保证数据一致性。
• VersionedCollapsingMergeTree通过sign+version字段实现，在更新或者删除时需要事先知道version值，建议将version值设置为主键id。
• ReplacingMergeTree+sign是通过手动的方式，实现了VersionedCollapsingMergeTree的功能，同时支持跨分区去重。为了保证数据的一致性，可以将记录的create_time或update_time设置为version值，在查询数据时需要指定sign != -1。

FINAL关键字：

通过在SELECT语句末尾添加FINAL关键字，Clickhouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。

注意：

1、MergeTree引擎不支持final查询

2、final会对整张表的order字段进行排序去重，若想根据分区展示去重后的数据，需要在order by字段中加上分区字段。

3、join 去重表 final 这样不会去重；join (select * from 去重表 final) 这样才可以实现去重。

4、final查询是非常耗时的操作，数据量大的情况下不建议使用。

3、MergeTree实现原理

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

存储结构

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

从图中可以看出，一张数据表的完整物理结构分为3个层级，依次是数据表目录、分区目录及各分区下具体的数据文件：

• checksums.txt：校验文件，使用二进制格式存储。它保存了余下各类文件(primary. idx、count.txt等)的size大小及size的哈希值，用于快速校验文件的完整性和正确性。
• columns.txt：列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息。
• count.txt：计数文件，使用明文格式存储。用于记录当前数据分区目录下数据的总行数。
• primary.idx：一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引（通过ORDER BY或者PRIMARY KEY）。借助稀疏索引，在数据查询时能够排除主键条件范围之外的数据文件，减少数据扫描范围。
• [Column].bin：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据。由于MergeTree采用列式存储，所以每一个列字段都拥有独立的．bin数据文件，并以列字段名称命名（例如CounterID.bin、EventDate.bin等）。
• [Column].mrk：列字段标记文件，使用二进制格式存储。标记文件中保存了．bin文件中数据的偏移量信息。标记文件与稀疏索引对齐，又与．bin文件一一对应，所以MergeTree通过标记文件建立了primary.idx稀疏索引与．bin数据文件之间的映射关系。即首先通过稀疏索引（primary.idx）找到对应数据的偏移量信息（.mrk），再通过偏移量直接从．bin文件中读取数据。
• [Column].mrk2：如果使用了自适应大小的索引间隔，则标记文件会以．mrk2命名。它的工作原理和作用与．mrk标记文件相同。
• partition.dat与minmax_[Column].idx：如果使用了分区键，例如PARTITION BY EventTime，则会额外生成partition.dat与minmax索引文件，它们均使用二进制格式存储。partition.dat用于保存当前分区下分区表达式最终生成的值；而minmax索引用于记录当前分区下分区字段对应原始数据的最小和最大值。在分区索引的作用下，进行数据查询时能够快速跳过不必要的数据分区目录，从而减少扫描的数据范围。
• skp_idx_[Column].idx与skp_idx_[Column].mrk：如果在建表语句中声明了二级索引，则会额外生成相应的二级索引与标记文件，它们同样也使用二进制存储。二级索引在ClickHouse中又称跳数索引，目前拥有minmax、set、ngrambf_v1和tokenbf_v1四种类型。这些索引的最终目标与一级稀疏索引相同，都是为了进一步减少所需扫描的数据范围，以加速整个查询过程。

数据分区

在MergeTree中数据是以分区目录的形式进行组织的，每个分区独立分开存储。借助这种形式，在对MergeTree进行数据查询时，可以有效跳过无用的数据文件，只使用最小的分区目录子集。

注意：数据分区（partition）和数据分片（shard）是完全不同的概念。数据分区是针对本地数据而言的，是对数据的一种纵向切分。而数据分片（shard）提供的是横向切分的能力，将一张表的数据分布到多个ClickHouse服务节点。

数据分区规则

MergeTree数据分区的规则由分区ID（分区键）决定，而具体到每个数据分区所对应的ID，则是由分区键的取值决定的。

分区ID生成逻辑四种规则：

（1）不指定分区键：如果不使用分区键，即不使用PARTITION BY声明任何分区表达式，则分区ID默认取名为all，所有的数据都会被写入这个all分区。

（2）使用整型：如果分区键取值属于整型(兼容UInt64，包括有符号整型和无符号整型)，且无法转换为日期类型YYYYMMDD格式，则直接按照该整型的字符形式输出，作为分区ID的取值。

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

（4）使用其他类型：如果分区键取值既不属于整型，也不属于日期类型，例如String、Float等，则通过128位Hash算法取其Hash值作为分区ID的取值。数据在写入时，会对照分区ID落入相应的数据分区，下表列举了分区ID在不同规则下的一些示例：

分区目录的命名规则

数据与公式的对照关系如下图所示：

• PartitionID：分区ID
• MinBlockNum：最小数据块编号（默认和MaxBlockNum从1开始），每当新创建一个分区目录时，累积加1。
• MaxBlockNum：最大数据块编号。每当新创建一个分区目录时，累积加1，当发生合并时取合并时的最大数据块编号。
• Level：合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄。

数据分区合并过程

• MergeTree的分区目录并不是在数据表被创建之后就存在的，而是在数据写入过程中被创建的。其次，它的分区目录在建立之后也并不是一成不变的，伴随着每一批数据的写入（一次INSERT语句），MergeTree都会生成一批新的分区目录，即使不同批次写入的数据属于相同分区，也会生成不同的分区目录。
• 在数据写入之后的某个时刻（也可以手动执行optimize查询语句）, ClickHouse会通过后台任务将属于相同分区的多个目录合并成一个新的目录。已经存在的旧分区目录并不会立即被删除，而是在之后的某个时刻通过后台任务被删除（默认8分钟）。
• 属于同一个分区的多个目录，在合并之后会生成一个全新的目录，索引和数据文件也会相应地进行合并。新目录名称的合并方式遵循以下规则：

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

一级索引

MergeTree的主键使用PRIMARY KEY定义，待主键定义之后，MergeTree会依据index_granularity间隔（默认8192行），为数据表生成一级索引并保存至primary.idx文件内，索引数据按照PRIMARY KEY排序。相比使用PRIMARY KEY定义，更为常见的简化形式是通过ORDER BY指代主键。在此种情形下，PRIMARY KEY与ORDER BY定义相同，所以索引（primary.idx）和数据（.bin）会按照完全相同的规则排序。

稀疏索引

primary.idx文件内的一级索引采用稀疏索引实现

• ? 稠密索引：每一行索引标记对应一行具体的数据记录
• ? 稀疏索引：每一行索引标记对应一段具体的数据记录

索引粒度

数据以index_granularity的粒度（默认固定索引粒度8192）被标记成多个小空间，其中每个空间最多8192行数据。这段空间的具体区间就是MarkRange，并且通过start和end表示具体的范围。

索引数据生成规则

?由于是稀疏索引，所以MergeTree需要间隔index_granularity行数据才会生成一条索引记录，其索引值会依据声明的主键字段获取。

如使用CounterID作为主键，则每间隔8192行数据就会取一次CounterID的值作为索引值，索引数据最终会被写入primary.idx文件进行保存。

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

从图中也能够看出，MergeTree对于稀疏索引的存储是非常紧凑的，索引值前后相连，按照主键字段顺序紧密地排列在一起。

索引查询过程

MergeTree按照index_granularity的间隔粒度，将一段完整的数据划分成多个小的间隔数据段，一个具体的数段就是MarkRange区间。

?查询过程：

• 首先将基于主键的查询条件转换成条件区间；如 where id > 100 转换成 (100, +inf)区间
• 然后以递归的形式与MarkRange对应的有序数值区间做交集判断
• 若不存在交集，则直接通过剪枝的方式优化整段MarkRange
• 若窜在交集，且MarkRange步长大于8（end-start）,则将区间进一步拆成8个区间，继续进行递归判断
• 若存在交集，且MarkRange不可在分隔（步长小于8），记录MarkRange并返回
• 最后合并MarkRange区间，将最终匹配的MarkRange聚在一起，合并范围

二级索引

二级索引又称跳数索引，跳数索引的目的与一级索引一样，也是帮助查询时减少数据扫描的范围。与一级索引一样，如果在建表语句中声明了跳数索引，则会额外生成相应的索引与标记文件（skp_idx_[Column].idx与skp_idx_[Column].mrk）。

跳数索引的创建语法：

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

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

跳数索引的数据生成规则：

（1）按照index_granularity粒度间隔将数据划分成n段，总共有[0 ,n-1]个区间（n = total_rows / index_granularity，向上取整）

（2）根据索引定义时声明的表达式，从0区间开始，每次向前移动1步(n+1)，聚合信息逐步累加。

（3）当移动granularity次区间时，则汇总并生成一行跳数索引数据。

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

跳数索引的类型

目前，MergeTree共支持4种跳数索引，分别是minmax、set、ngrambf_v1和tokenbf_v1。一张数据表支持同时声明多个跳数索引，例如:

CREATE TABLE skip_test (
    ID String,
    URL String,
    Code String,
    EventTime Date,
    INDEX a ID TYPE minmax GRANULARITY 5,
    INDEX b (length(ID) * 8) TYPE set(2) GRANULARITY 5,
    INDEX c (ID,Code) TYPE ngrambf_v1(3,256,2,0) GRANULARITY 5,
    INDEX d ID TYPE tokenbf_v1(256,2,0) GRANULARITY 5
) ENGINE = MergeTree()
ORDER BY ID ;

（1）minmax索引记录了一段数据内的最小和最大极值，其索引的作用类似分区目录的minmax索引，能够快速跳过无用数据区间。上述示例中minmax索引会记录这段数据区间内ID字段的极值。极值的计算涉及每5个index_granularity区间中的数据。

（2）set索引直接记录了声明字段或表达式的取值（唯一值，无重复），其完整形式为set(max_rows)，其中max_rows是一个阈值，表示在一个index_granularity内，索引最多记录的数据行数。如果max_rows=0，则表示无限制。上述示例中set索引会记录数据中ID的长度 * 8后的取值。其中，每个index_granularity内最多记录100条。

（3）ngrambf_v1索引记录的是数据短语的布隆表过滤器，只支持String和FixedString数据类型。ngrambf_v1只能够提升in、notIn、like、equals和notEquals查询的性能，其完整形式为ngrambf_v1(n,
size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)。这些参数是一个布隆过滤器的标准输入它们具体的含义如下：

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

（4）tokenbf_v1索引是ngrambf_v1的变种，同样也是一种布隆过滤器索引。tokenbf_v1除了短语token的处理方法外，其他与ngrambf_v1是完全一样的。tokenbf_v1会自动按照非字符的、数字的字符串分割token。布隆过滤器

数据存储

列式存储

在MergeTree中，数据按列存储。而具体到每个列字段，数据也是独立存储的，每个列字段都拥有一个与之对应的.bin数据文件。数据文件以分区目录的形式被组织存放，所以在.bin文件中只会保存当前分区片段内的这一部分数据。按列独立存储的设计优势显而易见：1）可以更好地进行数据压缩（相同类型的数据放在一起，对压缩更加友好），2）能够最小化数据扫描的范围。

而对应到存储的具体实现方面，MergeTree也并不是一股脑地将数据直接写入.bin文件，而是经过了一番精心设计：

• 数据是经过压缩的，目前支持LZ4（默认）、ZSTD、Multiple和Delta几种算法，
• 数据会事先依照ORDER BY的声明排序，
• 数据是以压缩数据块的形式被组织并写入.bin文件中的。压缩数据块就好比一本书的文字段落，是组织文字的基本单元。

压缩数据块

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

从图所示中能够看到，.bin压缩文件是由多个压缩数据块组成的，而每个压缩数据块的头信息则是基于CompressionMethod_
CompressedSize_UncompressedSize公式生成的。每个压缩数据块的体积，按照其压缩前的数据字节大小，都被严格控制在64KB～1MB，其上下限分别由min_compress_block_size（默认65536）与max_compress_block_size（默认1048576）参数指定。而一个压缩数据块最终的大小，则和一个间隔（index_granularity）内数据的实际大小相关。

MergeTree在数据具体写入过程中，会按照索引粒度，按批次获取数据并进行处理。如下图：

.bin文件引入压缩块的目的：

• ?数据被压缩后能有效减少数据大小，降低存储空间，加速数据传输效率；但是压缩、解压效率也会影响性能。
• ?具体读取某一列数据时（.bin文件），首先需要将压缩数据加载到内存中解压读取。通过压缩块可以在不读取整个.bin文件的情况下将读取粒度降低到压缩数据块级别，从而进一步缩小数据读取的范围。

数据标记

如果把MergeTree比作一本书，primary.idx一级索引好比这本书的一级章节目录，.bin文件中的数据好比这本书中的文字，那么数据标记(.mrk)会为一级章节目录和具体的文字之间建立关联。对于数据标记而言，它记录了两点重要信息：

• 一级章节对应的页码信息；
• 一段文字在某一页中的起始位置信息。

这样一来，通过数据标记就能够很快地从一本书中立即翻到关注内容所在的那一页，并知道从第几行开始阅读。

数据标记生成规则

数据标记作为衔接一级索引和数据的桥梁，其像极了做过标记小抄的书签，而且书本中每个一级章节都拥有各自的书签。它们之间的关系如图所示：

数据标记特征：

• 数据标记文件和索引区间是对齐的。都是按照index_granularity的粒度间隔划分；
• ? 数据标记文件和.bin文件也是一一对应。每一个列字段[column].bin文件都有一个对应的[column].mrk数据标记文件，用于记录数据在.bin文件中偏移量信息。

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

数据标记的工作方式

在MergeTree读取数据时，必须通过标记数据的位置信息找到所需要的数据。

查找过程大致分为读取压缩数据块和读取数据两个步骤。

• JavaEnable字段的数据类型为UInt8,所以每行数据占用1字节。
• ? 数据表的index_granularity粒度为8192，所以每一个索引片段大小正是8192B。
• ? 按照数据压缩块规则，8192B<64KB,当等于64KB压缩为下一个数据块。（64KB/8192B=8,也就是8行数据为一个数据压缩块）

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

• ?读取压缩数据块：在查询某一列数据时，MergeTree无须一次性加载整个.bin文件。借助标记文件中的压缩文件偏移量加载指定的数据压缩块。
• ?读取数据：在读取解压后的数据时，MergeTree并不需要一次性扫描整段解压数据，它可以根据需要以index_granularity的粒度加载特定的一小段。为了实现这项特性，需要借助标记文件中保存的解压数据块中的偏移量。

写入过程

1. 生成分区目录（伴随每一次insert操作，生成一个新的分区目录），在后续的某个时刻，合并相同分区的目录；
2. ? 按照index_granularity索引粒度，分别生成primary.idx索引文件、二级索引、每一列字段的.mrk数据标记和.bin压缩数据文件。

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

查询过程

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

数据标记与数据块对应关系

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

多对一

多个数据标记对应一个压缩数据块，当一个间隔（index_granularity）内的数据未压缩大小size小于64KB时，会出现这种对应关系。以hits_v1测试表的JavaEnable字段为例。JavaEnable数据类型为UInt8，大小为1B，则一个间隔内数据大小为8192B。所以在此种情形下，每8个数据标记会对应同一个压缩数据块，如图所示：

一对一

一个数据标记对应一个压缩数据块，当一个间隔（index_granularity）内的数据未压缩大小size大于等于64KB且小于等于1MB时，会出现这种对应关系。以hits_v1测试表的URLHash字段为例。URLHash数据类型为UInt64，大小为8B，则一个间隔内数据大小为65536B，恰好等于64KB。所以在此种情形下，数据标记与压缩数据块是一对一的关系，如图所示：

一对多

一个数据标记对应多个压缩数据块，当一个间隔（index_granularity）内的数据未压缩大小size直接大于1MB时，会出现这种对应关系。以hits_v1测试表的URL字段为例。URL数据类型为String，大小根据实际内容而定。如图所示，编号45的标记对应了2个压缩数据块：

4、Replication机制

Replication仅对于MergeTree引擎族提供支持, 两者是正交的:

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

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

工作机制

在Clickhouse中, Replication的机制工作在表级别, 而不是库, 或者是节点层级。

Replication机制不依赖于分片(shard), 每个分片有自己独立的Replication机制，不同分片之间的数据不会进行同步。

在同一个节点上，Create、Drop、Attach、Detach以及Rename等查询并不会触发Replication机制：

• 当创建表语句(CREATE TABLE)在一个节点上被执行时, 会创建一个新的Replication表。如果在该集群中其他节点上已经存在该表, 则会添加一个新的副本；
• 删除表语句(DROP TABLE)会删除当前节点上的副本表；
• 重命名表语句(RENAME TABLE)会重命名当前节点的表名，但不会修改该集群其他节点的表名，即Replicated表在不同的副本上可以有不同的名字。

依赖Zookeeper

Clickhouse使用zookeeper来存储副本的元数据信息(zk版本要求3.4.5或者更高)，当表引擎被指定为Replication引擎族时，Clickhouse将使用设定zk集群的一个目录作为自己元数据的存储地点(这个目录可以通过具体的表引擎语句来指定)。

• 如果在clickhouse的配置文件中没有配置zk集群信息, 将不能创建使用Replication引擎族的表, 并且已存在的使用Replication引擎族的表将会被锁定在只读状态(read-only)。
• 对于INSERT插入语句而言，所有被插入的数据会根据一定的规则划分为几个数据块Block，每个数据块的相关元数据信息会通过事务来被提交到Zookeeper(一个数据块具体可以最多容纳多少条数据是由参数max_insert_block_size决定的)。这个机制导致了数据插入时会有一些延迟，合理的数据写入机制能缓解这个延迟的影响。
• 如果Clickhouse集群的规模非常大，可以为不同的分片使用不同的Zookeeper集群，即一套Clickhouse集群搭配多套Zookeeper集群。

数据同步

Replication副本机制采用多主架构(同一个集群上每个节点都可以作为Master节点提供服务)，同步数据和写入数据的过程是异步执行。

• INSERT查询(ALTER查询也是)可以被发送到当前集群的所有正常工作的节点上，数据会被插入到运行INSERT语句的节点上，然后被同步到同一分片的其他节点上。

• 因为这个数据同步操作是异步执行的，其他节点上的数据会存在一定的延迟时间(这个时间由不同节点间传输压缩数据块的时间决定)。
• 同步时如果遇到部分节点不可用(挂掉了)，会当这些节点重新可用时再写入。对复制表进行数据同步时所使用的线程数可以通过background_schedule_pool_size参数来设置.
• 默认情况下，INSERT只会对一个副本写入数据进行等待确认，之后数据会自动同步至其它副本。如果数据仅成功写入一个副本，并且具有该副本的服务器不复存在，则存储的数据将丢失。要启用对来自多个副本的数据写入的确认，可使用insert_quorum选项。

INSERT的原子性

一个INSERT插入不一定是原子性的，这需要考虑到这个ISNERT查询被分成了多少个数据块，但对于数据块来说，是可以保证原子性的。换句话说，如果一个INSERT插入的条数小于max_insert_block_size设定的值，那么这个INSERT查询就是原子性的。

INSERT的幂等性

数据块是遵循重复删除原则的，对于同一数据块(大小相同, 条数相同, 数据的顺序相同)的多次写入，实际只会被写入一次，多余的次数会被丢弃。这样做的原因是当由于网络故障，客户端不知道数据是否成功被写入到Clickhouse中，可以不考虑其他, 直接重复执行INSERT操作(就算多写了一次, 也会因为相同数据块而实际上没有写入到CK中)。这样做就保证了多次INSERT操作的幂等性。

数据保障

在replication机制执行过程中，只有被插入的源数据在各个节点间传输，数据转换(Merge操作)也是以同样的方式在所有的副本上协调和执行的。这最大限度的降低了对于网络传输的压力，意味着当Clickhouse集群跨越了多个数据中心时，可以最大程度的提升replication机制的执行效率。

Clickhouse系统会监控每个副本上的数据一致性，在发生故障后能够自动恢复，在不同副本数据差异较小的情况下，故障转移是自动或半自动的，但如果数据差异过大(例如表结构不同)，那么就需要手动处理。

具体实践

1.在多台节点上安装clickhouse-server

2.分别在每台server上面配置zookeeper，默认配置文件位置
/etc/clickhouse-server/config.xml，示例如下：

    
        10.190.60.230
        2181
    
    
        10.190.60.228
        2181
    
    
        10.190.60.231
        2181
    

3.在配置文件中配置宏（macro），这里的参数在建表的时候会被引用到。

    01
    02

shard是分片标识，replication模式采用一个分片，不同server分片相同

replica是副本标识，不同副本该值不能相同，可以配置为本机的hostname

4.分别在不同的server上面执行建表语句

CREATE TABLE default.flow_provider_replicated
(
    deviceType String,
    providerCode String,
    thirdCategoryId UInt32,
    brandId UInt32,
    divisionId String,
    browserUniqId String,
    pv UInt64,
    date Date,
    ver UInt32
) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/flow', '{replica}', date, (providerCode, date, brandId, thirdCategoryId, divisionId, deviceType, browserUniqId), 8192, ver)

这里有几点说明：

• 其中{shard}和{replica}的值会被宏中所配置的值代替
• table engins必须使用MergeTree族，并且加上Replicated前缀，如：ReplicatedReplacingMergeTree
• 第一个参数'/clickhouse/tables/{shard}/flow'是表在zookeeper上的路径，第二个参数即副本名称

5、Distributed引擎

Distributed引擎是分布式表的代名词，它自身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据至数据集群中的各个节点，所以Distributed引擎需要与其他数据表引擎一起协同工作。

建表语句：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]])
[SETTINGS name=value, ...]


--当 Distributed 表指向当前服务器上的一个表时，你可以采用以下语句:
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] AS [db2.]name2 ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]]) [SETTINGS name=value, ...]

Distributed引擎参数

• cluster - 服务为配置中的集群名，在对分布式表执?读写的过程中，它会使?集群的配置信息来找到相应的host节点。
• database - 远程数据库名，用于将分布式表映射到本地表上
• table - 远程数据表名，用于将分布式表映射到本地表上
• sharding_key - (可选) 分片key，分布式表会按照这个规则，将数据分发到各个本地表中
• policy_name - (可选) 规则名，它会被用作存储临时文件以便异步发送数据

从实体表层面来看，一张分片表由两部分组成：

• 本地表：通常以_local为后缀进行命名。本地表是承接数据的载体，可以使用非Distributed的任意表引擎，一张本地表对应了一个数据分片。
• 分布式表：通常以_all为后缀进行命名。分布式表只能使用Distributed表引擎，它与本地表形成一对多的映射关系，日后将通过分布式表代理操作多张本地表。

对于分布式表与本地表之间表结构的一致性检查，Distributed表引擎采用了读时检查的机制，这意味着如果它们的表结构不兼容，只有在查询时才会抛出错误，而在创建表时并不会进行检查。不同ClickHouse节点上的本地表之间，使用不同的表引擎也是可行的，但是通常不建议这么做，保持它们的结构一致，有利于后期的维护并避免造成不可预计的错误。

写入数据

向集群写数据的方法有两种：

• 自已指定要将哪些数据写入哪些服务器，并直接在每个分片上执行写入。换句话说，在分布式表上SELECT，在本地表上 INSERT。你可以使用任何分片方案，来满足复杂业务需求。 这也是最灵活的方案，因为数据可以完全独立地写入不同的分片。
• 在分布式表上执行 INSERT。在这种情况下，分布式表会跨服务器分发插入数据。 为了写入分布式表，必须要配置分片键。当然，如果只有一个分片，则写操作在没有分片键的情况下也能工作，因为这种情况下分片键没有意义。

分片规则：

每个分片都可以在配置文件中定义权重，默认情况下，权重等于1。数据依据分片权重按比例分发到分片上。例如，如果有两个分片，第一个分片的权重是9，而第二个分片的权重是10，则发送 9 / 19 的行到第一个分片， 10 / 19 的行到第二个分片。

将具体某一行数据发送到分片的方法是：

• 首先根据制定的分片表达式计算结果，
• 然后将这个计算结果除以所有分片的权重总和得到余数，
• 将行会发送到那个包含该余数的[prev_weight，prev_weights + weight）区间对应的分片上，其中 prev_weights 是该分片前面的所有分片的权重和，weight 是该分片的权重。

例如，如果有两个分片，第一个分片权重为9，而第二个分片权重为10，则余数在 [0,9) 中的行发给第一个分片，余数在 [9,19) 中的行发给第二个分片。

分片表达式可以是由常量和表列组成的任何返回整数表达式。例如，您可以使用表达式 ‘rand()’ 来随机分配数据，或者使用xxxID等字段来按ID 的余数分布。

不建议写分布式表：

• 分布式表接收到数据后会将数据拆分成多个parts, 并转发数据到其它服务器, 会引起服务器间网络流量增加，服务器merge的工作量增加, 导致写入速度变慢，
• 数据的一致性问题, 先在分布式表所在的机器进行落盘, 然后异步的发送到本地表所在机器进行存储，中间没有一致性的校验, 而且在分布式表所在机器时如果机器出现down机, 会存在数据丢失风险，
• 数据写入默认是异步的，短时间内可能造成不一致，
• 对zookeeper的压力比较大。

查询流程

查询Distributed表时，SELECT查询被发送到所有分片，并且无论数据如何在分片中分布（随机分布）都可以工作。添加新分片时，不必将旧数据传输到其中，相反，可以通过使用更的权重向其写入新数据（数据将稍微不均匀地分布）。

在分布式表上执行查询的流程如图所示，发出查询后各个实例之间会交换自己持有分片的表数据，最终汇总到同一个实例上返回给用户。

针对多分片多副本的情况：

读取数据分布式查询遵循多副本的路由规则，该配置项为：load_balance=
random/nearest_hostname/in_order/first_or_random

多副本的路由规则

查询数据时，如果一个分片shard有多个副本repIica，那么Distributed表引擎就需要面对副本选择的问题，选择查询究竟在哪个副本上执行。ck的负载均衡算法有以下四种：

• random，这是默认的负载均衡算法。在ck的服务节点中，有一个errors_count全局计数器，当服务发生任何异常时，技术器加1。random算法会选择errors_count最小的那个repIica，如果多个repIica的errors_count相同，则在这几个里随机选择一个。
• nearest_hostname，选择errors_count最小的那个，如果多个errors_count相同，则选择集群配置中host名称和当前host名称最相似的那个。相似比较的规则是与当前host的名称，按字节进行逐位对比，找到不同字节最少的那个。 例如当前host是a.bc.de，那么，a.bc.df就比a.bf.hh要更加相似。 a.bc.de a.bc.df a.bf.hh
• in_order，选择errors_count最小的那个，如果多个errors_count相同，则按照集群配置顺序选择。
• first_or_random，选择errors_count最小的那个，如果多个errors_count相同，则按照集群配置顺序选择第一个，如果第一个不可用，则随意选择一个其他的。

　　总结起来，其实这4个负载算法中，都是优先选择errors_count最小的那个，如果多个errors_count相同时，再根据不同的负载算法来选择。

6、ClickHouse为什么快？

官方性能对比：
https://benchmark.clickhouse.com/

官方解释：
https://clickhouse.com/docs/en/faq/general/why-clickhouse-is-so-fast

1. 列式存储

对于 OLAP 技术来说，一般都是这对大量行少量列做聚合分析，所以列式存储技术基本可以说是 OLAP 必用的技术方案。

分析场景中往往需要读大量行但是少数几个列。在行存模式下，数据按行连续存储，所有列的数据都存储在一个block中，不参与计算的列在IO时也要全部读出。而列存模式下，只需要读取参与计算的列即可，极大的减低了IO消耗，每一列由一个线程来处理，即查询的并发处理性能高。

面向行的 DBMS：

面向列的数据库管理系统：

详细参考：

https://clickhouse.com/docs/en/intro/

https://developer.aliyun.com/article/792679

https://codeantenna.com/a/TclcKUEKuM

2. 数据压缩

• 同一列中的数据属于同一类型，压缩效果显著，可以达到十倍甚至更高的压缩比
• 更高的压缩比意味着更小的data size，从磁盘中读取相应数据耗时更少。
• 自由的压缩算法选择。可以针对不同列类型，选择最合适的压缩算法。
• 高压缩比，意味着同等大小的内存能够存放更多数据，系统cache效果更好。

ClickHouse默认使用LZ4算法压缩，在Yandex.Metrica的生产环境中，数据总体的压缩比可以达到8:1 ( 未压缩前17PB，压缩后2PB )。

压缩算法参考：

https://developer.aliyun.com/article/780586

压缩算法测试：
https://www.percona.com/blog/2016/04/13/evaluating-database-compression-methods-update/

3. 向量化引擎

向量化执行可以看做是消除程序中循环的优化，实现多行数据并行计算，为了实现向量化执行，需要利用CPU的SIMD（Single Instruction Multiple Data）指令，即用单条指令操作多条数据，底层是通过CPU寄存器层面实现数据并行操作以提高性能。

现代计算机系统概念中，SIMD是通过数据并行以提高性能的一种实现方式 ( 其他的还有指令级并行和线程级并行 )，它的原理是在CPU寄存器层面实现数据的并行操作。在计算机系统的体系结构中，存储系统是一种层次结构。典型服务器计算机的存储层次结构如下图所示:

从上图中可以看到，从左向右，距离CPU越远，则数据的访问速度越慢。从寄存器中访问数据的速度，是从内存访问数据速度的300倍，是从磁盘中访问数据速度的3000万倍。所以利用CPU向量化执行的特性，对于程序的性能提升意义非凡。

ClickHouse目前利用SSE4.2指令集实现向量化执行。

4. 三层索引结构

-分区索引

-一级索引

-二级索引

5. 多线程与分布式处理

多线程处理就是通过线程级并行的方式实现了性能的提升，ClickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity，然后通过多个CPU核心分别处理其中的一部分来实现并行数据处理。这种设计下，可以使得ClickHouse单条Query就能利用整机所有CPU，极致的并行处理能力，极大的降低了查询延时。

而分布式数据属于基于分而治之的基本思想，如果一台服务器性能吃紧，那么就利用多台服务的资源协同处理。这个前提是需要在数据层面实现数据的分布式，因为计算移动比数据移动更加划算，在各服务器之间，通过网络传输数据的成本是高昂的，所以预先将数据分布到各台服务器，将数据的计算查询直接下推到数据所在的服务器。

参考文献

官方文档：
https://clickhouse.com/docs/en/intro/

官方中文文档：
https://clickhouse.com/docs/zh/

朱凯.《ClickHouse原理解析与应用时间》

https://www.hnbian.cn/posts/e6ed3197.html

https://www.alibabacloud.com/help/zh/clickhouse/latest/table-engines

https://hellowac.github.io/database_doc/clickhouse/distribute_principle.html