ClickHouse入门实践--MergeTree表引擎

MergeTree系列表引擎

目前在ClickHouse中，按照特点可以将表引擎大致分成6个系列，分别是合并树、外部存储、内存、文件、接口和其他，每一个系列的表引擎都有着独自的特点与使用场景。在它们之中，最为核心的当属MergeTree系列，因为它们拥有最为强大的性能和最广泛的使用场合。

大家应该已经知道了MergeTree有两层含义：

其一，表示合并树表引擎家族；

其二，表示合并树家族中最基础的MergeTree表引擎。

而在整个家族中，除了基础表引擎MergeTree之外，常用的表引擎还有ReplacingMergeTree、SummingMergeTree、AggregatingMergeTree、CollapsingMergeTree和VersionedCollapsingMergeTree。每一种合并树的变种，在继承了基础MergeTree的能力之后，又增加了独有的特性。其名称中的“合并”二字奠定了所有类型MergeTree的基因，它们的所有特殊逻辑，都是在触发合并的过程中被激活的。在本章后续的内容中，会逐一介绍它们的特点以及使用方法。

MergeTree

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

数据TTL

TTL即Time To Live，顾名思义，它表示数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据；如果同时设置了列级别和表级别的TTL，则会以先到期的那个为主。无论是列级别还是表级别的TTL，都需要依托某个DateTime或Date类型的字段，通过对这个时间字段的INTERVAL操作，来表述TTL的过期时间，例如：

TTL time_col + INTERVAL 3 DAY

上述语句表示数据的存活时间是time_col时间的3天之后。又例如：

TTL time_col + INTERVAL 1 MONTH 

上述语句表示数据的存活时间是time_col时间的1月之后。INTERVAL完整的操作包括SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR。

列级别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 toYYYYMM(create_time)
ORDER BY id ;

其中，create_time是日期类型，列字段code与type均被设置了TTL，它们的存活时间是在create_time的取值基础之上向后延续10秒。现在写入测试数据，其中第一行数据create_time取当前的系统时间，而第二行数据的时间比第一行增加10分钟：

SELECT * FROM ttl_table_v1;

接着心中默数10秒，然后执行optimize命令强制触发TTL清理：

OPTIMIZE TABLE ttl_table_v1 FINAL;

再次查询ttl_table_v1则能够看到，由于第一行数据满足TTL过期条件（当前系统时间 >= create_time + 10秒），它们的code和type列会被还原为数据类型的默认值：

如果想要修改列字段的TTL，或是为已有字段添加TTL，则可以使用ALTER语句，示例如下：

ALTER TABLE ttl_table_v1 MODIFY column code String TTL create_time + INTERVAL 1 DAY

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

表级别TTL

如果想要为整张数据表设置TTL，需要在MergeTree的表参数中增加TTL表达式，例如下面的语句：

CREATE TABLE tt1_table_v2(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 1 MINUTE ,
    type UInt8
) ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY ;

ttl_table_v2整张表被设置了TTL，当触发TTL清理时，那些满足过期时间的数据行将会被整行删除。同样，表级别的TTL也支持修改，修改的方法如下：

ALTER TABLE tt1_table_v2 MODIFY TTL create_time + INTERVAL 3 DAY;

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

TTL的运行机理

在知道了列级别与表级别TTL的使用方法之后，现在简单聊一聊TTL的运行机理。如果一张MergeTree表被设置了TTL表达式，那么在写入数据时，会以数据分区为单位，在每个分区目录内生成一个名为ttl.txt的文件。以刚才示例中的ttl_table_v2为例，它被设置了列级别TTL：

code String TTL create_time + INTERVAL 1 MINUTE

同时被设置了表级别的TTL：

TTL create_time + INTERVAL 1 DAY

那么，在写入数据之后，它的每个分区目录内都会生成ttl.txt文件：

进一步查看ttl.txt的内容：

通过上述操作会发现，原来MergeTree是通过一串JSON配置保存了TTL的相关信息，其中：
❑ columns用于保存列级别TTL信息；
❑ table用于保存表级别TTL信息；
❑ min和max则保存了当前数据分区内，TTL指定日期字段的最小值、最大值分别与INTERVAL表达式计算后的时间戳。

如果将table属性中的min和max时间戳格式化，并分别与create_time最小与最大取值对比：

则能够印证，ttl.txt中记录的极值区间恰好等于当前数据分区内create_time最小与最大值增加1天（1天= 86400秒）所表示的区间，与TTL表达式create_time +INTERVAL 1 DAY的预期相符。

在知道了TTL信息的记录方式之后，现在看看它的大致处理逻辑。
（1）MergeTree以分区目录为单位，通过ttl.txt文件记录过期时间，并将其作为后续的判断依据。
（2）每当写入一批数据时，都会基于INTERVAL表达式的计算结果为这个分区生成ttl. txt文件。
（3）只有在MergeTree合并分区时，才会触发删除TTL过期数据的逻辑。
（4）在选择删除的分区时，会使用贪婪算法，它的算法规则是尽可能找到会最早过期的，同时年纪又是最老的分区（合并次数更多，MaxBlockNum更大的）。
（5）如果一个分区内某一列数据因为TTL到期全部被删除了，那么在合并之后生成的新分区目录中，将不会包含这个列字段的数据文件（.bin和．mrk）。

这里还有几条TTL使用的小贴士。
（1）TTL默认的合并频率由MergeTree的merge_with_ttl_timeout参数控制，默认86400秒，即1天。它维护的是一个专有的TTL任务队列。有别于MergeTree的常规合并任务，如果这个值被设置的过小，可能会带来性能损耗。
（2）除了被动触发TTL合并外，也可以使用optimize命令强制触发合并。例如，触发一个分区合并：

optimize TABLE table_name;

触发所有分区合并：

optimize TABLE table_name FINAL;

（3）ClickHouse目前虽然没有提供删除TTL声明的方法，但是提供了控制全局TTL合并任务的启停方法：

SYSTEM STOP/START TTL MERGES;

虽然还不能做到按每张MergeTree数据表启停，但聊胜于无吧。

ReplacingMergeTree

虽然MergeTree拥有主键，但是它的主键却没有唯一键的约束。这意味着即便多行数据的主键相同，它们还是能够被正常写入。在某些使用场合，用户并不希望数据表中含有重复的数据。ReplacingMergeTree就是在这种背景下为了数据去重而设计的，它能够在合并分区时删除重复的数据。它的出现，确实也在一定程度上解决了重复数据的问题。为什么说是“一定程度”？此处先按下不表。

创建一张ReplacingMergeTree表的方法与创建普通MergeTree表无异，只需要替换Engine：

ENGINE = ReplacingMergeTree(ver)

其中，ver是选填参数，会指定一个UInt*、Date或者DateTime类型的字段作为版本号。这个参数决定了数据去重时所使用的算法。

接下来，用一个具体的示例说明它的用法。首先执行下面的语句创建数据表：

CREATE TABLE replace_table(
    id String,
    code String,
    create_time DateTime
) ENGINE = ReplacingMergeTree()
partition by toYYYYMM(create_time)
ORDER BY(id,code)
PRIMARY KEY id ;

注意这里的ORDER BY是去除重复数据的关键，排序键ORDER BY所声明的表达式是后续作为判断数据是否重复的依据。在这个例子中，数据会基于id和code两个字段去重。假设此时表内的测试数据如下：

那么在执行optimize强制触发合并后，会按照id和code分组，保留分组内的最后一条（观察create_time日期字段）：

optimize TABLE replace_table FINAL;

将其余重复的数据删除：

从执行的结果来看，ReplacingMergeTree在去除重复数据时，确实是以ORDERBY排序键为基准的，而不是PRIMARY KEY。因为在上面的例子中，ORDER BY是(id, code)，而PRIMARY KEY是id，如果按照id值去除重复数据，则最终结果应该只剩下A001、A002和A003三行数据。

到目前为止，ReplacingMergeTree看起来完美地解决了重复数据的问题。事实果真如此吗？现在尝试写入一批新数据：

insert into replace_table
values
('A001','C1','2020-07-02 12:01:01');

写入之后，执行optimize强制分区合并，并查询数据：

再次观察返回的数据，可以看到A001:C1依然出现了重复。这是怎么回事呢？这是因为ReplacingMergeTree是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。这就是上面说ReplacingMergeTree只是在一定程度上解决了重复数据问题的原因。

现在接着说明ReplacingMergeTree版本号的用法。以下面的语句为例：

CREATE TABLE replace_table_v (
    id String,
    code String,
    create_time DateTime
) ENGINE = ReplacingMergeTree(create_time)
PARTITION  BY toYYYYMM(create_time)
ORDER BY id ;

replace_table_v基于id字段去重，并且使用create_time字段作为版本号，假设表内的数据如下所示：

那么在删除重复数据的时候，会保留同一组数据内create_time时间最长的那一行：

在知道了ReplacingMergeTree的使用方法后，现在简单梳理一下它的处理逻辑。
（1）使用ORBER BY排序键作为判断重复数据的唯一键。
（2）只有在合并分区的时候才会触发删除重复数据的逻辑。
（3）以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。
（4）在进行数据去重时，因为分区内的数据已经基于ORBER BY进行了排序，所以能够找到那些相邻的重复数据。
（5）数据去重策略有两种：
❑ 如果没有设置ver版本号，则保留同一组重复数据中的最后一行。
❑ 如果设置了ver版本号，则保留同一组重复数据中ver字段取值最大的那一行。

SummingMergeTree

假设有这样一种查询需求：终端用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的（GROUP BY条件明确，且不会随意改变）。
对于这样的查询场景，在ClickHouse中如何解决呢？最直接的方案就是使用MergeTree存储数据，然后通过GROUP BY聚合查询，并利用SUM聚合函数汇总结果。这种方案存在两个问题。

❑ 存在额外的存储开销：终端用户不会查询任何明细数据，只关心汇总结果，所以不应该一直保存所有的明细数据。

❑ 存在额外的查询开销：终端用户只关心汇总结果，虽然MergeTree性能强大，但是每次查询都进行实时聚合计算也是一种性能消耗。

SummingMergeTree就是为了应对这类查询场景而生的。顾名思义，它能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总合并成一行，这样既减少了数据行，又降低了后续汇总查询的开销。

在先前介绍MergeTree原理时曾提及，在MergeTree的每个数据分区内，数据会按照ORDER BY表达式排序。主键索引也会按照PRIMARY KEY表达式取值并排序。而ORDER BY可以指代主键，所以在一般情形下，只单独声明ORDER BY即可。此时，ORDER BY与PRIMARY KEY定义相同，数据排序与主键索引相同。

如果需要同时定义ORDER BY与PRIMARY KEY，通常只有一种可能，那便是明确希望ORDER BY与PRIMARY KEY不同。这种情况通常只会在使用SummingMergeTree或AggregatingMergeTree时才会出现。这是为何呢？这是因为SummingMergeTree与AggregatingMergeTree的聚合都是根据ORDER BY进行的。由此可以引出两点原因：主键与聚合的条件定义分离，为修改聚合条件留下空间。

现在用一个示例说明。假设一张SummingMergeTree数据表有A、B、C、D、E、F六个字段，如果需要按照A、B、C、D汇总，则有：

ORDER BY (A,B,C,D)

但是如此一来，此表的主键也被定义成了A、B、C、D。而在业务层面，其实只需要对字段A进行查询过滤，应该只使用A字段创建主键。所以，一种更加优雅的定义形式应该是：

ORDER BY (A,B,C,D) PRIMARY KEY A

如果同时声明了ORDER BY与PRIMARY KEY, MergeTree会强制要求PRIMARYKEY列字段必须是ORDER BY的前缀。例如下面的定义是错误的：

ORDER BY(B,C) PRIMARY KEY A

PRIMARY KEY必须是ORDER BY的前缀：

ORDER BY (B,C) PRIMARY KEY B

这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。

假设现在业务发生了细微的变化，需要减少字段，将先前的A、B、C、D改为按照A、B聚合汇总，则可以按如下方式修改排序键：

ALTER TABLE table_name MODIFY ORDER BY (A,B)

在修改ORDER BY时会有一些限制，只能在现有的基础上减少字段。如果是新增排序字段，则只能添加通过ALTER ADD COLUMN新增的字段。但是ALTER是一种元数据的操作，修改成本很低，相比不能被修改的主键，这已经非常便利了。

现在开始正式介绍SummingMergeTree的使用方法。表引擎的声明方式如下所示：

ENGINE = SummingMergeTree((col1,col2,...))

其中，col1、col2为columns参数值，这是一个选填参数，用于设置除主键外的其他数值类型字段，以指定被SUM汇总的列字段。如若不填写此参数，则会将所有非主键的数值类型字段进行SUM汇总。接来下用一组示例说明它的使用方法：

CREATE TABLE summing_table(
    id String,
    city String,
    v1 UInt32,
    v2 Float64,
    create_time DateTime
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id,city)
PRIMARY KEY id ;

注意，这里的ORDER BY是一项关键配置，SummingMergeTree在进行数据汇总时，会根据ORDER BY表达式的取值进行聚合操作。假设此时表内的数据如下所示：

执行optimize强制进行触发和合并操作：

optimize TABLE summing_table FINAL

再次查询，表内数据会变成下面的样子：

至此能够看到，在第一个分区内，同为A001:wuhan的两条数据汇总成了一行。其中，v1和v2被SUM汇总，不在汇总字段之列的create_time则选取了同组内第一行数据的取值。而不同分区之间，数据没有被汇总合并。

SummingMergeTree也支持嵌套类型的字段，在使用嵌套类型字段时，需要被SUM汇总的字段名称必须以Map后缀结尾，例如：

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

在使用嵌套数据类型的时候，默认情况下，会以嵌套类型中第一个字段作为聚合条件Key。假设表内的数据如下所示：

上述示例中数据会按照第一个字段id聚合，汇总后的数据会变成下面的样子：

数据汇总的逻辑示意如下所示：

在使用嵌套数据类型的时候，也支持使用复合Key作为数据聚合的条件。为了使用复合Key，在嵌套类型的字段中，除第一个字段以外，任何名称是以Key、Id或Type为后缀结尾的字段，都将和第一个字段一起组成复合Key。例如将上面的例子中小写key改为Key：

上述例子中数据会以id和Key作为聚合条件。在知道了SummingMergeTree的使用方法后，现在简单梳理一下它的处理逻辑。

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

AggregatingMergeTree

有过数据仓库建设经验的读者一定知道“数据立方体”的概念，这是一个在数据仓库领域十分常见的模型。它通过以空间换时间的方法提升查询性能，将需要聚合的数据预先计算出来，并将结果保存起来。在后续进行聚合查询的时候，直接使用结果数据。

AggregatingMergeTree就有些许数据立方体的意思，它能够在合并分区的时候，按照预先定义的条件聚合数据。同时，根据预先定义的聚合函数计算数据并通过二进制的格式存入表内。将同一分组下的多行数据聚合成一行，既减少了数据行，又降低了后续聚合查询的开销。可以说，AggregatingMergeTree是SummingMergeTree的升级版，它们的许多设计思路是一致的，例如同时定义ORDER BY与PRIMARY KEY的原因和目的。但是在使用方法上，两者存在明显差异，应该说AggregatingMergeTree的定义方式是MergeTree家族中最为特殊的一个。

声明使用AggregatingMergeTree的方式如下：

ENGINE = AggrefatingMergeTree()

AggregatingMergeTree没有任何额外的设置参数，在分区合并时，在每个数据分区内，会按照ORDER BY聚合。而使用何种聚合函数，以及针对哪些列字段计算，则是通过定义AggregateFunction数据类型实现的。以下面的语句为例：

CREATE TABLE agg_table (
    id String,
    city String,
    code AggregateFunction(uniq,String),
    value AggregateFunction(sum,UInt32),
    create_time DateTime
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY(id,city)
PRIMARY KEY id ;

上例中列字段id和city是聚合条件，等同于下面的语义：

GROUP BY id,city

而code和value是聚合字段，其语义等同于：

UNIQ(code),SUM(value)

AggregateFunction是ClickHouse提供的一种特殊的数据类型，它能够以二进制的形式存储中间状态结果。其使用方法也十分特殊，对于AggregateFunction类型的列字段，数据的写入和查询都与寻常不同。在写入数据时，需要调用State函数；而在查询数据时，则需要调用相应的Merge函数。其中，*表示定义时使用的聚合函数。例如示例中定义的code和value，使用了uniq和sum函数：

code AggregateFunction(uniq,String),
value AggregateFunction(sum,UInt32),

那么，在写入数据时需要调用与uniq、sum对应的uniqState和sumState函数，并使用INSERT SELECT语法：

INSERT INTO TABLE agg_table
SELECT 'A000','wuhan',
       uniqState('code'),
       sumState(toUInt32(200)),
       now();

在查询数据时，如果直接使用列名访问code和value，将会是无法显示的二进制形式。此时，需要调用与uniq、sum对应的uniqMerge、sumMerge函数：

SELECT id,city,uniqMerge(code),sumMerge(value) FROM agg_table GROUP BY id,city;

讲到这里，你是否会认为AggregatingMergeTree使用起来过于烦琐了？连正常进行数据写入都需要借助INSERT…SELECT的句式并调用特殊函数。如果直接像刚才示例中那样使用AggregatingMergeTree，确实会非常麻烦。不过各位读者并不需要忧虑，因为目前介绍的这种使用方法，并不是它的主流用法。

AggregatingMergeTree更为常见的应用方式是结合物化视图使用，将它作为物化视图的表引擎。而这里的物化视图是作为其他数据表上层的一种查询视图

现在用一组示例说明。首先，建立明细数据表，也就是俗称的底表：

CREATE TABLE agg_table_basic(
    id String,
    city String,
    code String,
    value UInt32
) ENGINE = MergeTree()
PARTITION BY city
ORDER BY(id,city);

通常会使用MergeTree作为底表，用于存储全量的明细数据，并以此对外提供实时查询。接着，新建一张物化视图：

CREATE MATERIALIZED VIEW agg_view
ENGINE = AggregatingMergeTree()
PARTITION BY city
ORDER BY (id,city)
AS SELECT
id,city,
uniqState(code) AS code,
sumState(value) as value
FROM agg_table_basic
group by id,city;

物化视图使用AggregatingMergeTree表引擎，用于特定场景的数据查询，相比MergeTree，它拥有更高的性能。
在新增数据时，面向的对象是底表MergeTree：

INSERT INTO TABLE agg_table_basic
VALUES('A000','wuhan','code1',100),
      ('A000','wuhan','code2',200),
      ('A000','zhuhai','code1',200) ;

数据会自动同步到物化视图，并按照AggregatingMergeTree引擎的规则处理。

在查询数据时，面向的对象是物化视图AggregatingMergeTree：

SELECT  id,sumMerge(value),uniqMerge(code) FROM agg_view GROUP BY id,city ;

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

CollapsingMergeTree

假设现在需要设计一款数据库，该数据库支持对已经存在的数据实现行级粒度的修改或删除，你会怎么设计？一种最符合常理的思维可能是：首先找到保存数据的文件，接着修改这个文件，删除或者修改那些需要变化的数据行。然而在大数据领域，对于ClickHouse这类高性能分析型数据库而言，对数据源文件修改是一件非常奢侈且代价高昂的操作。相较于直接修改源文件，它们会将修改和删除操作转换成新增操作，即以增代删。

CollapsingMergeTree就是一种通过以增代删的思路，支持行级数据修改和删除的表引擎。它通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。这种1和-1相互抵消的操作，犹如将一张瓦楞纸折叠了一般。这种直观的比喻，想必也正是折叠合并树（CollapsingMergeTree）名称的由来，其折叠的过程如图所示。

声明CollapsingMergeTree的方式如下：

ENGINE = CollapsingMergeTree(sign)

其中，sign用于指定一个Int8类型的标志位字段。一个完整的使用示例如下所示：

CREATE TABLE collpase_table(
    id String,
    code Int32,
    create_time DateTime,
    sign Int8
) ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(create_time)
ORDER BY id ;

与其他的MergeTree变种引擎一样，CollapsingMergeTree同样是以ORDER BY排序键作为后续判断数据唯一性的依据。按照之前的介绍，对于上述collpase_table数据表而言，除了常规的新增数据操作之外，还能够支持两种操作。

其一，修改一行数据：

--修改前的源数据，它需要被修改
INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',1);
--镜像数据，ORDER BY字段与源数据相同(其他字段可以不同),sign取反为-1,它会和源数据折叠
INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',-1);
--修改后的数据，sign为1
INSERT INTO TABLE collpase_table VALUES('A000',120,'2019-02-20 00:00:00',1);

其二，删除一行数据：

--修改前的源数据，它需要被修改
INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',1);
--镜像数据，ORDER BY字段与源数据相同(其他字段可以不同),sign取反为-1,它会和源数据折叠
INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',-1);

CollapsingMergeTree在折叠数据时，遵循以下规则。
❑ 如果sign=1比sign=-1的数据多一行，则保留最后一行sign=1的数据。
❑ 如果sign=-1比sign=1的数据多一行，则保留第一行sign=-1的数据。❑ 如果sign=1和sign=-1的数据行一样多，并且最后一行是sign=1，则保留第一行sign=-1和最后一行sign=1的数据。
❑ 如果sign=1和sign=-1的数据行一样多，并且最后一行是sign=-1，则什么也不保留。
❑ 其余情况，ClickHouse会打印警告日志，但不会报错，在这种情形下，查询结果不可预知。在使用CollapsingMergeTree的时候，还有几点需要注意。

在使用CollapsingMergeTree的时候，还有几点需要注意。
（1）折叠数据并不是实时触发的，和所有其他的MergeTree变种表引擎一样，这项特性也只有在分区合并的时候才会体现。所以在分区合并之前，用户还是会看到旧的数据。解决这个问题的方式有两种。
❑ 在查询数据之前，使用optimize TABLE table_name FINAL命令强制分区合并，但是这种方法效率极低，在实际生产环境中慎用。
❑ 需要改变我们的查询方式。以collpase_table举例，如果原始的SQL如下所示：

SELECT id,SUM(code),COUNT(code),AVG(code),uniq(code)
    FROM collpase_table
    GROUP BY id ;

则需要改写成如下形式：

SELECT id,SUM(code * sign),COUNT(code * sign),AVG(code * sign),UNIQ(code * sign)
    FROM collpase_table 
    GROUP BY  id 
    HAVING SUM(sign) > 0;

（2）只有相同分区内的数据才有可能被折叠。不过这项限制对于CollapsingMergeTree来说通常不是问题，因为修改或者删除数据的时候，这些数据的分区规则通常都是一致的，并不会改变。

（3）最后这项限制可能是CollapsingMergeTree最大的命门所在。CollapsingMergeTree对于写入数据的顺序有着严格要求。现在用一个示例说明。如果按照正常顺序写入，先写入sign=1，再写入sign=-1，则能够正常折叠：

INSERT INTO TABLE collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1);
INSERT INTO TABLE collpase_table VALUES('A000',101,'2019-02-20 00:00:00',-1);

SELECT * FROM collpase_table;

optimize table collpase_table FINAL;
SELECT * FROM collpase_table;

现在将写入的顺序置换，先写入sign=-1，再写入sign=1，则不能够折叠：

INSERT INTO TABLE collpase_table VALUES('A000',101,'2019-02-20 00:00:00',-1);
INSERT INTO TABLE collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1);

这种现象是CollapsingMergeTree的处理机制引起的，因为它要求sign=1和sign=-1的数据相邻。而分区内的数据基于ORBER BY排序，要实现sign=1和sign=-1的数据相邻，则只能依靠严格按照顺序写入。

如果数据的写入程序是单线程执行的，则能够较好地控制写入顺序；如果需要处理的数据量很大，数据的写入程序通常是多线程执行的，那么此时就不能保障数据的写入顺序了。在这种情况下，CollapsingMergeTree的工作机制就会出现问题。为了解决这个问题，ClickHouse另外提供了一个名为VersionedCollapsingMergeTree的表引擎。

VersionedCollapsingMergeTree

VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同，它们的不同之处在于，VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作。VersionedCollapsingMergeTree是如何做到这一点的呢？其实从它的命名各位就应该能够猜出来，是版本号。

在定义VersionedCollapsingMergeTree的时候，除了需要指定sign标记字段以外，还需要指定一个UInt8类型的ver版本号字段：

ENGINE = VersionedCollapsingMergeTree(sign,ver)

一个完整的例子如下：

CREATE TABLE ver_collpase_table(
    id String,
    code Int32,
    create_time DateTime,
    sign Int8,
    ver UInt8
) ENGINE = VersionedCollapsingMergeTree(sign,ver)
PARTITION BY toYYYYMM(create_time)
ORDER BY id ;

VersionedCollapsingMergeTree是如何使用版本号字段的呢？其实很简单，在定义ver字段之后，VersionedCollapsingMergeTree会自动将ver作为排序条件并增加到ORDER BY的末端。以上面的ver_collpase_table表为例，在每个数据分区内，数据会按照ORDER BY id , ver DESC排序。所以无论写入时数据的顺序如何，在折叠处理时，都能回到正确的顺序。

可以用一组示例证明，首先是删除数据：

--删除
INSERT INTO TABLE ver_collpase_table VALUES('A000',101,'2019-02-20 00:00:00',-1,1);
INSERT INTO TABLE ver_collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1,1);
select * from  ver_collpase_table;

接着是修改数据：

optimize table ver_collpase_table FINAL;
select * from  ver_collpase_table;

INSERT INTO TABLE ver_collpase_table VALUES('A000',101,'2019-02-20 00:00:00',-1,1);
INSERT INTO TABLE ver_collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1,1);
INSERT INTO TABLE ver_collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1,2);
select * from  ver_collpase_table;

optimize table ver_collpase_table FINAL;
select * from  ver_collpase_table;