ClickHouse

• 官方文档地址
• OLAP场景的关键特征
• ClickHouse的特点
• 接口
• 引擎
• • 数据库引擎
  • • 延时引擎Lazy
    • Atomic
    • MySQL
    • 数据库引擎
  • 表引擎
  • 合并树家族
  • • VersionedCollapsingMergeTree（版本折叠合并树）
    • AggregatingMergeTree（聚合合并树）
    • MergeTree （合并树）
    • ReplacingMergeTree（替换合并树）
    • SummingMergeTree(合计合并树)
    • CollapsingMergeTree(折叠树)

官方文档地址

https://clickhouse.tech/docs/zh/

本文档在表引擎中有删减引擎！！！ 想查看全部请点击官方文档。

OLAP场景的关键特征

绝大多数是读请求
数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。
已添加到数据库的数据不能修改。
对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
宽表，即每个表包含着大量的列
查询相对较少(通常每台服务器每秒查询数百次或更少)
对于简单查询，允许延迟大约50毫秒
列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
事务不是必须的
对数据一致性要求低
每个查询有一个大表。除了他以外，其他的都很小。
查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

ClickHouse的特点

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

数据压缩

在一些列式数据库管理系统中(例如：InfiniDB CE 和 MonetDB) 并没有使用数据压缩。但是, 若想达到比较优异的性能，数据压缩确实起到了至关重要的作用。除了在磁盘空间和CPU消耗之间进行不同权衡的高效通用压缩编解码器之外，ClickHouse还提供针对特定类型数据的专用编解码器，这使得ClickHouse能够与更小的数据库(如时间序列数据库)竞争并超越它们。

数据的磁盘存储

许多的列式数据库(如 SAP HANA, Google PowerDrill)只能在内存中工作，这种方式会造成比实际更多的设备预算。ClickHouse被设计用于工作在传统磁盘上的系统，它提供每GB更低的存储成本，但如果可以使用SSD和内存，它也会合理的利用这些资源。

多核心并行处理

ClickHouse会使用服务器上一切可用的资源，从而以最自然的方式并行处理大型查询。

多服务器分布式处理

上面提到的列式数据库管理系统中，几乎没有一个支持分布式的查询处理。
在ClickHouse中，数据可以保存在不同的shard上，每一个shard都由一组用于容错的replica组成，查询可以并行地在所有shard上进行处理。这些对用户来说是透明的

支持SQL

ClickHouse支持一种基于SQL的声明式查询语言，它在许多情况下与ANSI SQL标准相同。支持的查询GROUP BY, ORDER BY, FROM, JOIN, IN以及非相关子查询。相关(依赖性)子查询和窗口函数暂不受支持，但将来会被实现。

向量引擎

为了高效的使用CPU，数据不仅仅按列存储，同时还按向量(列的一部分)进行处理，这样可以更加高效地使用CPU。

实时的数据更新

ClickHouse支持在表中定义主键。为了使查询能够快速在主键中进行范围查找，数据总是以增量的方式有序的存储在MergeTree中。因此，数据可以持续不断地高效的写入到表中，并且写入的过程中不会存在任何加锁的行为。

索引

按照主键对数据进行排序，这将帮助ClickHouse在几十毫秒以内完成对数据特定值或范围的查找。

适合在线查询

在线查询意味着在没有对数据做任何预处理的情况下以极低的延迟处理查询并将结果加载到用户的页面中。

支持近似计算

ClickHouse提供各种各样在允许牺牲数据精度的情况下对查询进行加速的方法：1.用于近似计算的各类聚合函数，如：distinct values, medians, quantiles
2.基于数据的部分样本进行近似查询。这时，仅会从磁盘检索少部分比例的数据。
3.不使用全部的聚合条件，通过随机选择有限个数据聚合条件进行聚合。这在数据聚合条件满足某些分布条件下，在提供相当准确的聚合结果的同时降低了计算资源的使用。

Adaptive Join Algorithm
ClickHouse支持自定义JOIN多个表，它更倾向于散列连接算法，如果有多个大表，则使用合并-连接算法

支持数据复制和数据完整性

ClickHouse使用异步的多主复制技术。当数据被写入任何一个可用副本后，系统会在后台将数据分发给其他副本，以保证系统在不同副本上保持相同的数据。在大多数情况下ClickHouse能在故障后自动恢复，在一些少数的复杂情况下需要手动恢复。

接口

官方文档的接口：https://clickhouse.tech/docs/zh/interfaces/third-party/proxy/.

引擎

数据库引擎

延时引擎Lazy

在距最近一次访问间隔expiration_time_in_seconds时间段内，将表保存在内存中，仅适用于 *Log引擎表

由于针对这类表的访问间隔较长，对保存大量小的 *Log引擎表进行了优化，

创建数据库

CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);

Atomic

It is supports non-blocking DROP and RENAME TABLE queries and atomic EXCHANGE TABLES t1 AND t2 queries. Atomic database engine is used by default.

它支持非阻塞DROP和RENAME TABLE查询以及原子交换表t1和t2查询。默认情况下使用原子数据库引擎。
创建数据库

CREATE DATABASE test ENGINE = Atomic;

MySQL

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。

但您无法对其执行以下操作：

RENAME
CREATE TABLE
ALTER

创建数据库

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

MySQL数据库引擎参数

host:port — 链接的MySQL地址。
database — 链接的MySQL数据库。
user — 链接的MySQL用户。
password — 链接的MySQL用户密码。

支持的类型对应

MySQL	ClickHouse
UNSIGNED TINYINT	UInt8
TINYINT	Int8
UNSIGNED SMALLINT	UInt16
SMALLINT	Int16
UNSIGNED INT, UNSIGNED MEDIUMINT	UInt32
INT, MEDIUMINT	Int32
UNSIGNED BIGINT	UInt64
BIGINT	Int64
FLOAT	Float32
DOUBLE	Float64
DATE	日期
DATETIME, TIMESTAMP	日期时间
BINARY	固定字符串

其他的MySQL数据类型将全部都转换为字符串。
同时以上的所有类型都支持可为空
使用示例
在MySQL中创建表:

mysql> USE test;
Database changedmysql> CREATE TABLE `mysql_table` (->   `int_id` INT NOT NULL AUTO_INCREMENT,->   `float` FLOAT NOT NULL,->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)mysql> insert into mysql_table (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)mysql> select * from mysql_table;
+--------+-------+
| int_id | value |
+--------+-------+
|      1 |     2 |
+--------+-------+
1 row in set (0,00 sec)

在ClickHouse中创建MySQL类型的数据库，同时与MySQL服务器交换数据：

CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')

SHOW DATABASES
┌─name─────┐
│ default  │
│ mysql_db │
│ system   │
└──────────┘

SHOW TABLES FROM mysql_db
┌─name─────────┐
│  mysql_table │
└──────────────┘

SELECT * FROM mysql_db.mysql_table
┌─int_id─┬─value─┐
│      1 │     2 │
└────────┴───────┘

INSERT INTO mysql_db.mysql_table VALUES (3,4)SELECT * FROM mysql_db.mysql_table┌─int_id─┬─value─┐
│      1 │     2 │
│      3 │     4 │
└────────┴───────┘

数据库引擎

您使用的所有表都是由数据库引擎所提供的默认情况下，ClickHouse使用自己的数据库引擎，该引擎提供可配置的表引擎和所有支持的SQL语法.除此之外，您还可以选择使用以下的数据库引擎：MySQL

表引擎

合并树家族

VersionedCollapsingMergeTree（版本折叠合并树）

这个引擎:

允许快速写入不断变化的对象状态。
删除后台中的旧对象状态。 这显着降低了存储体积。

引擎继承自 MergeTree 并将折叠行的逻辑添加到合并数据部分的算法中。 VersionedCollapsingMergeTree 用于相同的目的 折叠树 但使用不同的折叠算法，允许以多个线程的任何顺序插入数据。 特别是， Version 列有助于正确折叠行，即使它们以错误的顺序插入。 相比之下, CollapsingMergeTree 只允许严格连续插入。

创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

引擎参数

VersionedCollapsingMergeTree(sign, version)

sign — 指定行类型的列名: 1 是一个 “state” 行, -1 是一个 “cancel” 行列数据类型应为 Int8.version — 指定对象状态版本的列名。列数据类型应为 UInt*.

查询 Clauses

当创建一个 VersionedCollapsingMergeTree 表时，跟创建一个 MergeTree表的时候需要相同 Clause

折叠
数据

考虑一种情况，您需要为某个对象保存不断变化的数据。 对于一个对象有一行，并在发生更改时更新该行是合理的。 但是，对于数据库管理系统来说，更新操作非常昂贵且速度很慢，因为它需要重写存储中的数据。 如果需要快速写入数据，则不能接受更新，但可以按如下顺序将更改写入对象。

使用 Sign 列写入行时。 如果 Sign = 1 这意味着该行是一个对象的状态（让我们把它称为 “state” 行）。 如果 Sign = -1 它指示具有相同属性的对象的状态的取消（让我们称之为 “cancel” 行）。 还可以使用 Version 列，它应该用单独的数字标识对象的每个状态。

例如，我们要计算用户在某个网站上访问了多少页面以及他们在那里的时间。 在某个时间点，我们用用户活动的状态写下面的行:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         5 │      146 │    1 │       1 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

在稍后的某个时候，我们注册用户活动的变化，并用以下两行写入它。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         5 │      146 │   -1 │       1 |
│ 4324182021466249494 │         6 │      185 │    1 │       2 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

第一行取消对象（用户）的先前状态。 它应该复制已取消状态的所有字段，除了 Sign.

第二行包含当前状态。

因为我们只需要用户活动的最后一个状态，行

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         5 │      146 │    1 │       1 |
│ 4324182021466249494 │         5 │      146 │   -1 │       1 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

可以删除，折叠对象的无效（旧）状态。 VersionedCollapsingMergeTree 在合并数据部分时执行此操作。

要了解为什么每次更改都需要两行，请参阅 算法.

使用注意事项

1.写入数据的程序应该记住对象的状态以取消它。 该 “cancel” 字符串应该是 “state” 与相反的字符串 Sign. 这增加了存储的初始大小，但允许快速写入数据。
2.列中长时间增长的数组由于写入负载而降低了引擎的效率。 数据越简单，效率就越高。
3.SELECT 结果很大程度上取决于对象变化历史的一致性。 准备插入数据时要准确。 不一致的数据将导致不可预测的结果，例如会话深度等非负指标的负值。

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

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

选择数据
ClickHouse不保证具有相同主键的所有行都将位于相同的结果数据部分中，甚至位于相同的物理服务器上。 对于写入数据和随后合并数据部分都是如此。 此外，ClickHouse流程 SELECT 具有多个线程的查询，并且无法预测结果中的行顺序。 这意味着，如果有必要从VersionedCollapsingMergeTree 表中得到完全 “collapsed” 的数据，聚合是必需的。

要完成折叠，请使用 GROUP BY 考虑符号的子句和聚合函数。 例如，要计算数量，请使用 sum(Sign) 而不是 count(). 要计算的东西的总和，使用 sum(Sign * x) 而不是 sum(x)，并添加 HAVING sum(Sign) > 0.

聚合 count, sum 和 avg 可以这样计算。 聚合 uniq 如果对象至少具有一个非折叠状态，则可以计算。 聚合 min 和 max 无法计算是因为 VersionedCollapsingMergeTree 不保存折叠状态值的历史记录。

如果您需要提取数据 “collapsing” 但是，如果没有聚合（例如，要检查是否存在其最新值与某些条件匹配的行），则可以使用 FINAL 修饰 FROM 条件这种方法效率低下，不应与大型表一起使用。

使用示例
示例数据:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         5 │      146 │    1 │       1 |
│ 4324182021466249494 │         5 │      146 │   -1 │       1 |
│ 4324182021466249494 │         6 │      185 │    1 │       2 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

创建表:

CREATE TABLE UAct
(UserID UInt64,PageViews UInt8,Duration UInt8,Sign Int8,Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID

插入数据:

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1, 1)

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1, 1),(4324182021466249494, 6, 185, 1, 2)

我们用两个 INSERT 查询以创建两个不同的数据部分。 如果我们使用单个查询插入数据，ClickHouse将创建一个数据部分，并且永远不会执行任何合并。

获取数据:

SELECT * FROM UAct
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         5 │      146 │    1 │       1 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         5 │      146 │   -1 │       1 │
│ 4324182021466249494 │         6 │      185 │    1 │       2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

我们在这里看到了什么，折叠的部分在哪里？
我们使用两个创建了两个数据部分 INSERT 查询。 该 SELECT 查询是在两个线程中执行的，结果是行的随机顺序。
由于数据部分尚未合并，因此未发生折叠。 ClickHouse在我们无法预测的未知时间点合并数据部分。

这就是为什么我们需要聚合:

SELECTUserID,sum(PageViews * Sign) AS PageViews,sum(Duration * Sign) AS Duration,Version
FROM UAct
GROUP BY UserID, Version
HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Version─┐
│ 4324182021466249494 │         6 │      185 │       2 │
└─────────────────────┴───────────┴──────────┴─────────┘

如果我们不需要聚合，并希望强制折叠，我们可以使用 FINAL 修饰符 FROM 条款

SELECT * FROM UAct FINAL
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         6 │      185 │    1 │       2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

这是一个非常低效的方式来选择数据。 不要把它用于数据量大的表。

AggregatingMergeTree（聚合合并树）

该引擎继承自 MergeTree，并改变了数据片段的合并逻辑。 ClickHouse 会将一个数据片段内所有具有相同主键（准确的说是 排序键）的行替换成一行，这一行会存储一系列聚合函数的状态。

可以使用 AggregatingMergeTree 表来做增量数据的聚合统计，包括物化视图的数据聚合。

引擎使用以下类型来处理所有列：（列类型）

AggregateFunction
SimpleAggregateFunction

AggregatingMergeTree 适用于能够按照一定的规则缩减行数的情况。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]

SELECT 和 INSERT
要插入数据，需使用带有 -State- 聚合函数的 INSERT SELECT 语句。
从 AggregatingMergeTree 表中查询数据时，需使用 GROUP BY 子句并且要使用与插入时相同的聚合函数，但后缀要改为 -Merge 。

对于 SELECT 查询的结果， AggregateFunction 类型的值对 ClickHouse 的所有输出格式都实现了特定的二进制表示法。在进行数据转储时，例如使用 TabSeparated 格式进行 SELECT 查询，那么这些转储数据也能直接用 INSERT 语句导回。

聚合物化视图的示例
创建一个跟踪 test.visits 表的 AggregatingMergeTree 物化视图：

CREATE MATERIALIZED VIEW test.basic
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECTCounterID,StartDate,sumState(Sign)    AS Visits,uniqState(UserID) AS Users
FROM test.visits
GROUP BY CounterID, StartDate;

向 test.visits 表中插入数据。

INSERT INTO test.visits ...

数据会同时插入到表和视图中，并且视图 test.basic 会将里面的数据聚合。

要获取聚合数据，我们需要在 test.basic 视图上执行类似 SELECT … GROUP BY … 这样的查询 ：

SELECTStartDate,sumMerge(Visits) AS Visits,uniqMerge(Users) AS Users
FROM test.basic
GROUP BY StartDate
ORDER BY StartDate;

MergeTree （合并树）

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。

MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

主要特点:

存储的数据按主键排序。这使得你能够创建一个小型的稀疏索引来加快数据检索。支持数据分区，如果指定了 分区键 的话。在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。支持数据副本。ReplicatedMergeTree 系列的表提供了数据副本功能。更多信息，请参阅 数据副本 一节。支持数据采样。需要的话，你可以给表设置一个采样方法。

注意
合并 引擎并不属于 *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],...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, ...]

子句

一、ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。二、ORDER BY — 排序键。可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。如果没有使用 PRIMARY KEY 显式的指定主键，ClickHouse 会使用排序键作为主键。如果不需要排序，可以使用 ORDER BY tuple(). 参考 选择主键三、PARTITION BY — 分区键 。要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。四、PRIMARY KEY - 主键，如果要 选择与排序键不同的主键，可选。默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。
因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。五、SAMPLE BY — 用于抽样的表达式。如果要用抽样表达式，主键中必须包含这个表达式。例如：
SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。六、TTL 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选。表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：TTL date + INTERVAl 1 DAY规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。更多细节，请查看 表和列的 TTL七、SETTINGS — 控制 MergeTree 行为的额外参数：index_granularity — 索引粒度。索引中相邻的『标记』间的数据行数。默认值，8192 。参考数据存储。index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。enable_mixed_granularity_parts — 是否启用通过 index_granularity_bytes 控制索引粒度的大小。在19.11版本之前, 只有 index_granularity 配置能够用于限制索引粒度的大小。当从具有很大的行（几十上百兆字节）的表中查询数据时候，index_granularity_bytes 配置能够提升ClickHouse的性能。如果你的表里有很大的行，可以开启这项配置来提升SELECT 查询的性能。use_minimalistic_part_header_in_zookeeper — 是否在 ZooKeeper 中启用最小的数据片段头 。如果设置了 use_minimalistic_part_header_in_zookeeper=1 ，ZooKeeper 会存储更少的数据。更多信息参考『服务配置参数』这章中的 设置描述 。min_merge_bytes_to_use_direct_io — 使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。合并数据片段时，ClickHouse 会计算要被合并的所有数据的总存储空间。如果大小超过了 min_merge_bytes_to_use_direct_io 设置的字节数，则 ClickHouse 将使用直接 I/O 接口（O_DIRECT 选项）对磁盘读写。如果设置 min_merge_bytes_to_use_direct_io = 0 ，则会禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024 字节。merge_with_ttl_timeout — TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。write_final_mark — 是否启用在数据片段尾部写入最终索引标记。默认值: 1（不建议更改）。merge_max_block_size — 在块中进行合并操作时的最大行数限制。默认值：8192storage_policy — 存储策略。 参见 使用具有多个块的设备进行数据存储.min_bytes_for_wide_part,min_rows_for_wide_part 在数据片段中可以使用Wide格式进行存储的最小字节数/行数。你可以不设置、只设置一个，或全都设置。参考：数据存储

示例配置

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

在这个例子中，我们设置了按月进行分区。

同时我们设置了一个按用户 ID 哈希的抽样表达式。这使得你可以对该表中每个 CounterID 和 EventDate 的数据伪随机分布。如果你在查询时指定了 SAMPLE 子句。 ClickHouse会返回对于用户子集的一个均匀的伪随机数据采样。

index_granularity 可省略因为 8192 是默认设置 。

数据存储
表由按主键排序的数据片段（DATA PART）组成。

当数据被插入到表中时，会创建多个数据片段并按主键的字典序排序。例如，主键是 (CounterID, Date) 时，片段中数据首先按 CounterID 排序，具有相同 CounterID 的部分按 Date 排序。

不同分区的数据会被分成不同的片段，ClickHouse 在后台合并数据片段以便更高效存储。不同分区的数据片段不会进行合并。合并机制并不保证具有相同主键的行全都合并到同一个数据片段中。

数据片段可以以 Wide 或 Compact 格式存储。在 Wide 格式下，每一列都会在文件系统中存储为单独的文件，在 Compact 格式下所有列都存储在一个文件中。Compact 格式可以提高插入量少插入频率频繁时的性能。

数据存储格式由 min_bytes_for_wide_part 和 min_rows_for_wide_part 表引擎参数控制。如果数据片段中的字节数或行数少于相应的设置值，数据片段会以 Compact 格式存储，否则会以 Wide 格式存储。

每个数据片段被逻辑的分割成颗粒（granules）。颗粒是 ClickHouse 中进行数据查询时的最小不可分割数据集。ClickHouse 不会对行或值进行拆分，所以每个颗粒总是包含整数个行。每个颗粒的第一行通过该行的主键值进行标记，
ClickHouse 会为每个数据片段创建一个索引文件来存储这些标记。对于每列，无论它是否包含在主键当中，ClickHouse 都会存储类似标记。这些标记让你可以在列文件中直接找到数据。

颗粒的大小通过表引擎参数 index_granularity 和 index_granularity_bytes 控制。取决于行的大小，颗粒的行数的在 [1, index_granularity] 范围中。如果单行的大小超过了 index_granularity_bytes 设置的值，那么一个颗粒的大小会超过 index_granularity_bytes。在这种情况下，颗粒的大小等于该行的大小。

一、
我们以 (CounterID, Date) 以主键。排序好的索引的图示会是下面这样：

全部数据  :     [-------------------------------------------------------------------------]
CounterID:      [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
Date:           [1111111222222233331233211111222222333211111112122222223111112223311122333]
标记:            |      |      |      |      |      |      |      |      |      |      |a,1    a,2    a,3    b,3    e,2    e,3    g,1    h,2    i,1    i,3    l,3
标记号:          0      1      2      3      4      5      6      7      8      9      10

如果指定查询如下：

CounterID in ('a', 'h')，服务器会读取标记号在 [0, 3) 和 [6, 8) 区间中的数据。
CounterID IN ('a', 'h') AND Date = 3，服务器会读取标记号在 [1, 3) 和 [7, 8) 区间中的数据。
Date = 3，服务器会读取标记号在 [1, 10] 区间中的数据。

上面例子可以看出使用索引通常会比全表描述要高效。

稀疏索引会引起额外的数据读取。当读取主键单个区间范围的数据时，每个数据块中最多会多读 index_granularity * 2 行额外的数据。

稀疏索引使得你可以处理极大量的行，因为大多数情况下，这些索引常驻与内存（RAM）中。

ClickHouse 不要求主键惟一，所以你可以插入多条具有相同主键的行。

主键的选择
主键中列的数量并没有明确的限制。依据数据结构，你可以在主键包含多些或少些列。这样可以：

改善索引的性能。如果当前主键是 (a, b) ，在下列情况下添加另一个 c 列会提升性能：查询会使用 c 列作为条件
很长的数据范围（ index_granularity 的数倍）里 (a, b) 都是相同的值，并且这样的情况很普遍。换言之，就是加入另一列后，可以让你的查询略过很长的数据范围。
改善数据压缩。ClickHouse 以主键排序片段数据，所以，数据的一致性越高，压缩越好。在CollapsingMergeTree 和 SummingMergeTree 引擎里进行数据合并时会提供额外的处理逻辑。在这种情况下，指定与主键不同的 排序键 也是有意义的。

长的主键会对插入性能和内存消耗有负面影响，但主键中额外的列并不影响 SELECT 查询的性能。

可以使用 ORDER BY tuple() 语法创建没有主键的表。在这种情况下 ClickHouse 根据数据插入的顺序存储。如果在使用 INSERT … SELECT 时希望保持数据的排序，请设置 max_insert_threads = 1。

想要根据初始顺序进行数据查询，使用 单线程查询

选择与排序键不同主键
指定一个跟排序键不一样的主键是可以的，此时排序键用于在数据片段中进行排序，主键用于在索引文件中进行标记的写入。这种情况下，主键表达式元组必须是排序键表达式元组的前缀。

当使用 SummingMergeTree 和 AggregatingMergeTree 引擎时，这个特性非常有用。通常在使用这类引擎时，表里的列分两种：维度 和 度量 。典型的查询会通过任意的 GROUP BY 对度量列进行聚合并通过维度列进行过滤。由于 SummingMergeTree 和 AggregatingMergeTree 会对排序键相同的行进行聚合，所以把所有的维度放进排序键是很自然的做法。但这将导致排序键中包含大量的列，并且排序键会伴随着新添加的维度不断的更新。

在这种情况下合理的做法是，只保留少量的列在主键当中用于提升扫描效率，将维度列添加到排序键中。

对排序键进行 ALTER 是轻量级的操作，因为当一个新列同时被加入到表里和排序键里时，已存在的数据片段并不需要修改。由于旧的排序键是新排序键的前缀，并且新添加的列中没有数据，因此在表修改时的数据对于新旧的排序键来说都是有序的。

索引和分区在查询中的应用
对于 SELECT 查询，ClickHouse 分析是否可以使用索引。如果 WHERE/PREWHERE 子句具有下面这些表达式（作为谓词链接一子项或整个）则可以使用索引：包含一个表示与主键/分区键中的部分字段或全部字段相等/不等的比较表达式；基于主键/分区键的字段上的 IN 或 固定前缀的LIKE 表达式；基于主键/分区键的字段上的某些函数；基于主键/分区键的表达式的逻辑表达式。
因此，在索引键的一个或多个区间上快速地执行查询都是可能的。下面例子中，指定标签；指定标签和日期范围；指定标签和日期；指定多个标签和日期范围等执行查询，都会非常快。

当引擎配置如下时：

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate) SETTINGS index_granularity=8192

这种情况下，这些查询：

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse 会依据主键索引剪掉不符合的数据，依据按月分区的分区键剪掉那些不包含符合数据的分区。

上文的查询显示，即使索引用于复杂表达式。因为读表操作是组织好的，所以，使用索引不会比完整扫描慢。

下面这个例子中，不会使用索引。

SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

要检查 ClickHouse 执行一个查询时能否使用索引，可设置 force_index_by_date 和 force_primary_key 。

按月分区的分区键是只能读取包含适当范围日期的数据块。这种情况下，数据块会包含很多天（最多整月）的数据。在块中，数据按主键排序，主键第一列可能不包含日期。因此，仅使用日期而没有带主键前几个字段作为条件的查询将会导致需要读取超过这个指定日期以外的数据。

部分单调主键的使用
考虑这样的场景，比如一个月中的几天。它们在一个月的范围内形成一个单调序列 ，但如果扩展到更大的时间范围它们就不再单调了。这就是一个部分单调序列。如果用户使用部分单调的主键创建表，ClickHouse同样会创建一个稀疏索引。当用户从这类表中查询数据时，ClickHouse 会对查询条件进行分析。如果用户希望获取两个索引标记之间的数据并且这两个标记在一个月以内，ClickHouse 可以在这种特殊情况下使用到索引，因为它可以计算出查询参数与索引标记之间的距离。

如果查询参数范围内的主键不是单调序列，那么 ClickHouse 无法使用索引。在这种情况下，ClickHouse 会进行全表扫描。

ClickHouse 在任何主键代表一个部分单调序列的情况下都会使用这个逻辑。

跳数索引
此索引在 CREATE 语句的列部分里定义。

INDEX index_name expr TYPE type(...) GRANULARITY granularity_value

*MergeTree 系列的表可以指定跳数索引。

这些索引是由数据块按粒度分割后的每部分在指定表达式上汇总信息 granularity_value 组成（粒度大小用表引擎里 index_granularity 的指定）。
这些汇总信息有助于用 where 语句跳过大片不满足的数据，从而减少 SELECT 查询从磁盘读取的数据量，

这些索引会在数据块上聚合指定表达式的信息，这些信息以 granularity_value 指定的粒度组成 （粒度的大小通过在表引擎中定义 index_granularity 定义）。这些汇总信息有助于跳过大片不满足 where 条件的数据，从而减少 SELECT 查询从磁盘读取的数据量。

示例

CREATE TABLE table_name
(u64 UInt64,i32 Int32,s String,...INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4
) ENGINE = MergeTree()
...

上例中的索引能让 ClickHouse 执行下面这些查询时减少读取数据量。

SELECT count() FROM table WHERE s < 'z'
SELECT count() FROM table WHERE u64 * i32 == 10 AND u64 * length(s) >= 1234

索引的可用类型

minmax存储指定表达式的极值（如果表达式是 tuple ，则存储 tuple 中每个元素的极值），这些信息用于跳过数据块，类似主键。set(max_rows)存储指定表达式的不重复值（不超过 max_rows 个，max_rows=0 则表示『无限制』）。这些信息可用于检查 数据块是否满足 WHERE 条件。ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)存储一个包含数据块中所有 n元短语（ngram） 的 布隆过滤器 。只可用在字符串上。可用于优化 equals ， like 和 in 表达式的性能。n – 短语长度。size_of_bloom_filter_in_bytes – 布隆过滤器大小，单位字节。（因为压缩得好，可以指定比较大的值，如 256 或 512）。number_of_hash_functions – 布隆过滤器中使用的哈希函数的个数。random_seed – 哈希函数的随机种子。tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)跟 ngrambf_v1 类似，不同于 ngrams 存储字符串指定长度的所有片段。它只存储被非字母数字字符分割的片段。bloom_filter(bloom_filter([false_positive]) – 为指定的列存储布隆过滤器可选的参数 false_positive 用来指定从布隆过滤器收到错误响应的几率。取值范围是 (0,1)，默认值：0.025支持的数据类型：Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString, Array, LowCardinality, Nullable。以下函数会用到这个索引： equals, notEquals, in, notIn, has

INDEX sample_index (u64 * length(s)) TYPE minmax GRANULARITY 4
INDEX sample_index2 (u64 * length(str), i32 + f64 * 100, date, str) TYPE set(100) GRANULARITY 4
INDEX sample_index3 (lower(str), str) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4

函数支持
WHERE 子句中的条件包含对列的函数调用，如果列是索引的一部分，ClickHouse 会在执行函数时尝试使用索引。不同的函数对索引的支持是不同的。

set 索引会对所有函数生效，其他索引对函数的生效情况见下表

函数 (操作符) / 索引	primary key	minmax	ngrambf_v1	tokenbf_v1	bloom_filter
equals (=, ==)	✔	✔	✔	✔	✔
notEquals(!=, <>)	✔	✔	✔	✔	✔
like	✔	✔	✔	✔	✔
notLike	✔	✔	✗	✗	✗
startsWith	✔	✔	✔	✔	✗
endsWith	✗	✗	✔	✔	✗
multiSearchAny	✗	✗	✔	✗	✗
in	✔	✔	✔	✔	✔
notIn	✔	✔	✔	✔	✔
less (<)	✔	✔	✗	✗	✗
greater (>)	✔	✔	✗	✗	✗
lessOrEquals (<=)	✔	✔	✗	✗	✗
greaterOrEquals (>=)	✔	✔	✗	✗	✗
empty	✔	✔	✗	✗	✗
notEmpty	✔	✔	✗	✗	✗
hasToken	✗	✗	✗	✔	✗

常量参数小于 ngram 大小的函数不能使用 ngrambf_v1 进行查询优化。

注意

布隆过滤器可能会包含不符合条件的匹配，所以 ngrambf_v1, tokenbf_v1 和 bloom_filter 索引不能用于负向的函数，例如：

可以用来优化的场景s LIKE '%test%'NOT s NOT LIKE '%test%'s = 1NOT s != 1startsWith(s, 'test')
不能用来优化的场景NOT s LIKE '%test%'s NOT LIKE '%test%'NOT s = 1s != 1NOT startsWith(s, 'test')

并发数据访问
应对表的并发访问，我们使用多版本机制。换言之，当同时读和更新表时，数据从当前查询到的一组片段中读取。没有冗长的的锁。插入不会阻碍读取。

对表的读操作是自动并行的。

列和表的 TTL
TTL 可以设置值的生命周期，它既可以为整张表设置，也可以为每个列字段单独设置。表级别的 TTL 还会指定数据在磁盘和卷上自动转移的逻辑。

TTL 表达式的计算结果必须是 日期 或 日期时间 类型的字段。

示例：

TTL time_column
TTL time_column + interval

要定义interval, 需要使用 时间间隔 操作符。

TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR

列 TTL
当列中的值过期时, ClickHouse会将它们替换成该列数据类型的默认值。如果数据片段中列的所有值均已过期，则ClickHouse 会从文件系统中的数据片段中删除此列。

TTL子句不能被用于主键字段。

示例:

创建表时指定 TTL

CREATE TABLE example_table
(d DateTime,a Int TTL d + INTERVAL 1 MONTH,b Int TTL d + INTERVAL 1 MONTH,c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

为表中已存在的列字段添加 TTL

ALTER TABLE example_tableMODIFY COLUMNc String TTL d + INTERVAL 1 DAY;

修改列字段的 TTL

ALTER TABLE example_tableMODIFY COLUMNc String TTL d + INTERVAL 1 MONTH;

表 TTL
表可以设置一个用于移除过期行的表达式，以及多个用于在磁盘或卷上自动转移数据片段的表达式。当表中的行过期时，ClickHouse 会删除所有对应的行。对于数据片段的转移特性，必须所有的行都满足转移条件。

TTL expr [DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'], ...

TTL 规则的类型紧跟在每个 TTL 表达式后面，它会影响满足表达式时（到达指定时间时）应当执行的操作：

DELETE - 删除过期的行（默认操作）;
TO DISK 'aaa' - 将数据片段移动到磁盘 aaa;
TO VOLUME 'bbb' - 将数据片段移动到卷 bbb.

示例:

创建时指定 TTL

CREATE TABLE example_table
(d DateTime,a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],d + INTERVAL 1 WEEK TO VOLUME 'aaa',d + INTERVAL 2 WEEK TO DISK 'bbb';

修改表的 TTL

ALTER TABLE example_tableMODIFY TTL d + INTERVAL 1 DAY;

删除数据

ClickHouse 在数据片段合并时会删除掉过期的数据。当ClickHouse发现数据过期时, 它将会执行一个计划外的合并。要控制这类合并的频率, 你可以设置 merge_with_ttl_timeout。如果该值被设置的太低, 它将引发大量计划外的合并，这可能会消耗大量资源。如果在合并的过程中执行 SELECT 查询, 则可能会得到过期的数据。为了避免这种情况，可以在 SELECT 之前使用 OPTIMIZE 查询。

使用具有多个块的设备进行数据存储
介绍
MergeTree 系列表引擎可以将数据存储在多块设备上。这对某些可以潜在被划分为“冷”“热”的表来说是很有用的。近期数据被定期的查询但只需要很小的空间。相反，详尽的历史数据很少被用到。如果有多块磁盘可用，那么“热”的数据可以放置在快速的磁盘上（比如 NVMe 固态硬盘或内存），“冷”的数据可以放在相对较慢的磁盘上（比如机械硬盘）。

数据片段是 MergeTree 引擎表的最小可移动单元。属于同一个数据片段的数据被存储在同一块磁盘上。数据片段会在后台自动的在磁盘间移动，也可以通过 ALTER 查询来移动。

术语

磁盘 — 挂载到文件系统的块设备
默认磁盘 — 在服务器设置中通过 path 参数指定的数据存储
卷 — 磁盘的等效有序集合 （类似于 JBOD）
存储策略 — 卷的集合及他们之间的数据移动规则

配置

磁盘、卷和存储策略应当在主文件 config.xml 或 config.d 目录中的独立文件中的 <storage_configuration> 标签内定义。

配置结构：

<storage_configuration><disks><disk_name_1> <!-- disk name --><path>/mnt/fast_ssd/clickhouse/</path></disk_name_1><disk_name_2><path>/mnt/hdd1/clickhouse/</path><keep_free_space_bytes>10485760</keep_free_space_bytes></disk_name_2><disk_name_3><path>/mnt/hdd2/clickhouse/</path><keep_free_space_bytes>10485760</keep_free_space_bytes></disk_name_3>...</disks>...
</storage_configuration>

标签：

<disk_name_N> — 磁盘名，名称必须与其他磁盘不同.
path — 服务器将用来存储数据 (data 和 shadow 目录) 的路径, 应当以 ‘/’ 结尾.
keep_free_space_bytes — 需要保留的剩余磁盘空间.

磁盘定义的顺序无关紧要。

存储策略配置：

<storage_configuration>...<policies><policy_name_1><volumes><volume_name_1><disk>disk_name_from_disks_configuration</disk><max_data_part_size_bytes>1073741824</max_data_part_size_bytes></volume_name_1><volume_name_2><!-- configuration --></volume_name_2><!-- more volumes --></volumes><move_factor>0.2</move_factor></policy_name_1><policy_name_2><!-- configuration --></policy_name_2><!-- more policies --></policies>...
</storage_configuration>

标签：

policy_name_N — 策略名称，不能重复。
volume_name_N — 卷名称，不能重复。
disk — 卷中的磁盘。
max_data_part_size_bytes — 任意卷上的磁盘可以存储的数据片段的最大大小。
move_factor — 当可用空间少于这个因子时，数据将自动的向下一个卷（如果有的话）移动 (默认值为 0.1)。

配置示例：

<storage_configuration>...<policies><hdd_in_order> <!-- policy name --><volumes><single> <!-- volume name --><disk>disk1</disk><disk>disk2</disk></single></volumes></hdd_in_order><moving_from_ssd_to_hdd><volumes><hot><disk>fast_ssd</disk><max_data_part_size_bytes>1073741824</max_data_part_size_bytes></hot><cold><disk>disk1</disk></cold></volumes><move_factor>0.2</move_factor></moving_from_ssd_to_hdd></policies>...
</storage_configuration>

在给出的例子中， hdd_in_order 策略实现了 循环制 方法。因此这个策略只定义了一个卷（single），数据片段会以循环的顺序全部存储到它的磁盘上。当有多个类似的磁盘挂载到系统上，但没有配置 RAID 时，这种策略非常有用。请注意一个每个独立的磁盘驱动都并不可靠，你可能需要用 3 或更大的复制因此来补偿它。

如果在系统中有不同类型的磁盘可用，可以使用 moving_from_ssd_to_hdd。hot 卷由 SSD 磁盘（fast_ssd）组成，这个卷上可以存储的数据片段的最大大小为 1GB。所有大于 1GB 的数据片段都会被直接存储到 cold 卷上，cold 卷包含一个名为 disk1 的 HDD 磁盘。
同样，一旦 fast_ssd 被填充超过 80%，数据会通过后台进程向 disk1 进行转移。

存储策略中卷的枚举顺序是很重要的。因为当一个卷被充满时，数据会向下一个卷转移。磁盘的枚举顺序同样重要，因为数据是依次存储在磁盘上的。

在创建表时，可以将一个配置好的策略应用到表：

CREATE TABLE table_with_non_default_policy (EventDate Date,OrderID UInt64,BannerID UInt64,SearchPhrase String
) ENGINE = MergeTree
ORDER BY (OrderID, BannerID)
PARTITION BY toYYYYMM(EventDate)
SETTINGS storage_policy = 'moving_from_ssd_to_hdd'

default 存储策略意味着只使用一个卷，这个卷只包含一个在 中定义的磁盘。表创建后，它的存储策略就不能改变了。

可以通过 background_move_pool_size 设置调整执行后台任务的线程数。

详细说明

对于 MergeTree 表，数据通过以下不同的方式写入到磁盘当中：作为插入（INSERT查询）的结果
在后台合并和数据变异期间
当从另一个副本下载时
作为 ALTER TABLE … FREEZE PARTITION 冻结分区的结果

除了数据变异和冻结分区以外的情况下，数据按照以下逻辑存储到卷或磁盘上：

首个卷（按定义顺序）拥有足够的磁盘空间存储数据片段（unreserved_space > current_part_size）并且允许存储给定数据片段的大小（max_data_part_size_bytes > current_part_size）
在这个数据卷内，紧挨着先前存储数据的那块磁盘之后的磁盘，拥有比数据片段大的剩余空间。（unreserved_space - keep_free_space_bytes > current_part_size）

更进一步，数据变异和分区冻结使用的是 硬链接。不同磁盘之间的硬链接是不支持的，所以在这种情况下数据片段都会被存储到初始化的那一块磁盘上。

在后台，数据片段基于剩余空间（move_factor参数）根据卷在配置文件中定义的顺序进行转移。数据永远不会从最后一个移出也不会从第一个移入。可以通过系统表 system.part_log (字段 type = MOVE_PART) 和 system.parts (字段 path 和 disk) 来监控后台的移动情况。同时，具体细节可以通过服务器日志查看。

用户可以通过 ALTER TABLE … MOVE PART|PARTITION … TO VOLUME|DISK … 强制移动一个数据片段或分区到另外一个卷，所有后台移动的限制都会被考虑在内。这个查询会自行启动，无需等待后台操作完成。如果没有足够的可用空间或任何必须条件没有被满足，用户会收到报错信息。

数据移动不会妨碍到数据复制。也就是说，同一张表的不同副本可以指定不同的存储策略。

在后台合并和数据变异之后，就的数据片段会在一定时间后被移除 (old_parts_lifetime)。在这期间，他们不能被移动到其他的卷或磁盘。也就是说，直到数据片段被完全移除，它们仍然会被磁盘占用空间计算在内。

ReplacingMergeTree（替换合并树）

该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。

数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。

因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

ReplacingMergeTree 的参数

ver — 版本列。类型为 UInt*, Date 或 DateTime。可选参数。在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：如果 ver 列未指定，保留最后一条。如果 ver 列已指定，保留 ver 值最大的版本。

SummingMergeTree(合计合并树)

该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

我们推荐将该引擎和 MergeTree 一起使用。例如，在准备做报告的时候，将完整的数据存储在 MergeTree 表中，并且使用 SummingMergeTree 来存储聚合数据。这种方法可以使你避免因为使用不正确的主键组合方式而丢失有价值的数据。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

SummingMergeTree 的参数
columns – 包含了将要被汇总的列的列名的元组。可选参数。
所选的列必须是数值类型，并且不可位于主键中。

如果没有指定 columns，ClickHouse 会把所有不在主键中的数值类型的列都进行汇总。

用法示例
考虑如下的表：

CREATE TABLE summtt
(key UInt32,value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key

向其中插入数据：

:) INSERT INTO summtt Values(1,1),(1,2),(2,1)

ClickHouse可能不会完整的汇总所有行（见下文）,因此我们在查询中使用了聚合函数 sum 和 GROUP BY 子句。

SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

数据处理

当数据被插入到表中时，他们将被原样保存。ClickHouse 定期合并插入的数据片段，并在这个时候对所有具有相同主键的行中的列进行汇总，将这些行替换为包含汇总数据的一行记录。

ClickHouse 会按片段合并数据，以至于不同的数据片段中会包含具有相同主键的行，即单个汇总片段将会是不完整的。因此，聚合函数 sum() 和 GROUP BY 子句应该在（SELECT）查询语句中被使用，如上文中的例子所述。

汇总的通用规则
列中数值类型的值会被汇总。这些列的集合在参数 columns 中被定义。

如果用于汇总的所有列中的值均为0，则该行会被删除。

如果列不在主键中且无法被汇总，则会在现有的值中任选一个。

主键所在的列中的值不会被汇总。

AggregateFunction 列中的汇总
对于 AggregateFunction 类型的列，ClickHouse 根据对应函数表现为 AggregatingMergeTree 引擎的聚合。

嵌套结构
表中可以具有以特殊方式处理的嵌套数据结构。

如果嵌套表的名称以 Map 结尾，并且包含至少两个符合以下条件的列：

第一列是数值类型 (Int, Date, DateTime)，我们称之为 key,
其他的列是可计算的 (Int, Float32/64)，我们称之为 (values…),
然后这个嵌套表会被解释为一个 key => (values…) 的映射，当合并它们的行时，两个数据集中的元素会被根据 key 合并为相应的 (values…) 的汇总值。

示例：

[(1, 100)] + [(2, 150)] -> [(1, 100), (2, 150)]
[(1, 100)] + [(1, 150)] -> [(1, 250)]
[(1, 100)] + [(1, 150), (2, 150)] -> [(1, 250), (2, 150)]
[(1, 100), (2, 150)] + [(1, -100)] -> [(2, 150)]

请求数据时，使用 sumMap(key,value) 函数来对 Map 进行聚合。

对于嵌套数据结构，你无需在列的元组中指定列以进行汇总。

CollapsingMergeTree(折叠树)

该引擎继承于 MergeTree，并在数据块合并算法中添加了折叠行的逻辑。

CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。更多的细节请看本文的折叠部分。

因此，该引擎可以显著的降低存储量并提高 SELECT 查询效率。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

CollapsingMergeTree 参数

sign — 类型列的名称： 1 是«状态»行，-1 是«取消»行。列数据类型 — Int8。

折叠
数据
考虑你需要为某个对象保存不断变化的数据的情景。似乎为一个对象保存一行记录并在其发生任何变化时更新记录是合乎逻辑的，但是更新操作对 DBMS 来说是昂贵且缓慢的，因为它需要重写存储中的数据。如果你需要快速的写入数据，则更新操作是不可接受的，但是你可以按下面的描述顺序地更新一个对象的变化。

在写入行的时候使用特定的列 Sign。如果 Sign = 1 则表示这一行是对象的状态，我们称之为«状态»行。如果 Sign = -1 则表示是对具有相同属性的状态行的取消，我们称之为«取消»行。

例如，我们想要计算用户在某个站点访问的页面页面数以及他们在那里停留的时间。在某个时候，我们将用户的活动状态写入下面这样的行。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

一段时间后，我们写入下面的两行来记录用户活动的变化。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

第一行取消了这个对象（用户）的状态。它需要复制被取消的状态行的所有除了 Sign 的属性。

第二行包含了当前的状态。

因为我们只需要用户活动的最后状态，这些行

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
└─────────────────────┴───────────┴──────────┴──────┘

可以在折叠对象的失效（老的）状态的时候被删除。CollapsingMergeTree 会在合并数据片段的时候做这件事。

这种方法的特殊属性

1.写入的程序应该记住对象的状态从而可以取消它。«取消»字符串应该是«状态»字符串的复制，除了相反的 Sign。它增加了存储的初始数据的大小，但使得写入数据更快速。
2.由于写入的负载，列中长的增长阵列会降低引擎的效率。数据越简单，效率越高。
3.SELECT 的结果很大程度取决于对象变更历史的一致性。在准备插入数据时要准确。在不一致的数据中会得到不可预料的结果，例如，像会话深度这种非负指标的负值。

算法
当 ClickHouse 合并数据片段时，每组具有相同主键的连续行被减少到不超过两行，一行 Sign = 1（«状态»行），另一行 Sign = -1 （«取消»行），换句话说，数据项被折叠了。

对每个结果的数据部分 ClickHouse 保存：

1. 第一个«取消»和最后一个«状态»行，如果«状态»和«取消»行的数量匹配和最后一个行是«状态»行
2. 最后一个«状态»行，如果«状态»行比«取消»行多一个或一个以上。
3. 第一个«取消»行，如果«取消»行比«状态»行多一个或一个以上。
4. 没有行，在其他所有情况下。合并会继续，但是 ClickHouse 会把此情况视为逻辑错误并将其记录在服务日志中。这个错误会在相同的数据被插入超过一次时出现。

因此，折叠不应该改变统计数据的结果。
变化逐渐地被折叠，因此最终几乎每个对象都只剩下了最后的状态。

Sign 是必须的因为合并算法不保证所有有相同主键的行都会在同一个结果数据片段中，甚至是在同一台物理服务器上。ClickHouse 用多线程来处理 SELECT 请求，所以它不能预测结果中行的顺序。如果要从 CollapsingMergeTree 表中获取完全«折叠»后的数据，则需要聚合。

要完成折叠，请使用 GROUP BY 子句和用于处理符号的聚合函数编写请求。例如，要计算数量，使用 sum(Sign) 而不是 count()。要计算某物的总和，使用 sum(Sign * x) 而不是 sum(x)，并添加 HAVING sum(Sign) > 0 子句。

聚合体 count,sum 和 avg 可以用这种方式计算。如果一个对象至少有一个未被折叠的状态，则可以计算 uniq 聚合。min 和 max 聚合无法计算，因为 CollaspingMergeTree 不会保存折叠状态的值的历史记录。

如果你需要在不进行聚合的情况下获取数据（例如，要检查是否存在最新值与特定条件匹配的行），你可以在 FROM 从句中使用 FINAL 修饰符。这种方法显然是更低效的。

示例
示例数据:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

建表:

CREATE TABLE UAct
(UserID UInt64,PageViews UInt8,Duration UInt8,Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID

插入数据:

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1)

我们使用两次 INSERT 请求来创建两个不同的数据片段。如果我们使用一个请求插入数据，ClickHouse 只会创建一个数据片段且不会执行任何合并操作。

获取数据：

SELECT * FROM UAct┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

我们看到了什么，哪里有折叠？

通过两个 INSERT 请求，我们创建了两个数据片段。SELECT 请求在两个线程中被执行，我们得到了随机顺序的行。没有发生折叠是因为还没有合并数据片段。ClickHouse 在一个我们无法预料的未知时刻合并数据片段。

因此我们需要聚合：

SELECTUserID,sum(PageViews * Sign) AS PageViews,sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

如果我们不需要聚合并想要强制进行折叠，我们可以在 FROM 从句中使用 FINAL 修饰语。

SELECT * FROM UAct FINAL

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

这种查询数据的方法是非常低效的。不要在大表中使用它。