由于业务需要，数据需要转移到ClickHouse中，但是ClickHouse对数据的更新和删除并不友好，历经很多坑，最终测试出较为合适的方案。

1.关于ClickHouse数据合并引擎的选择

我们首次设计的数据合并方案是：使用`ReplacingMergeTree`引擎，插入相同主键的记录（主键需设置为排序键），`ClickHouse`会删除排序键值相同的重复项，但是删除时间并不能确定，效率问题在测试时并没有很凸显，但是当使用预发布环境大量数据涌入，`ClickHouse`会十分吃力，同一个数据表中插入和更新同时进行，导致插入效率极速下降，内网测试时`1W`条数据插入只需`200～500ms`，预发布环境则达到了数十秒，这个效率显然是不能接受的，所以我们放弃了`ReplacingMergeTree`引擎。

后续又测试了`ReplacingMergeTree`和`AggregatingMergeTree`都不能，满足业务需求，刚接触`ClickHouse`的同学可能对他的存储引擎比较陌生，大家可以参考一下：https://blog.csdn.net/qq_42651904/article/details/112802212，这位大佬对存储引擎的介绍还是挺好的，个人认为`ClickHouse`官网的介绍有些简单，不过可以做个参考，官网地址：https://clickhouse.com/docs/zh/。

最终经过测试，我们选择的存储引擎是：`AggregatingMergeTree`+`SimpleAggregateFunction`数据函数类型+`partition`分区存储，组合使用。

2.关于AggregatingMergeTree的使用

下面内容大部分是从官网照抄，因为官网对一些陌生的名词会有解释，也做了一些修改。

该引擎继承自 [MergeTree](https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/mergetree/)，并改变了数据片段的合并逻辑。 `ClickHouse` 会将一个数据片段内所有具有相同主键（准确的说是 [排序键](https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/mergetree/)）的行替换成一行，这一行会存储一系列聚合函数的状态。

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

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

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

建表语句：

1
2
3
4
5
6
7
8
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]

建表参数介绍：

ENGINE 指定的存储引擎

PARTITION BY 指定分区键（会根据该列中的数据分别存储）

ORDER BY 排序列（一般是指定的主键 id）

上述建表语句中对于AggregateFunction和SimpleAggregateFunction并没有介绍，想要详细了解的话需要点击链接查看详情，下面是我的建表语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE test.test
(

    `pk` Int64,

    `age` Nullable(String),

    `last_update` SimpleAggregateFunction(max,
 DateTime),

    `partionId` Int32
)
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/test/test/{shard}',
 '{replica}')
PARTITION BY partionId
ORDER BY pk
SETTINGS index_granularity = 8192

其中的 SETTINGS index_granularity 是索引粒度。索引中相邻的『标记』间的数据行数。默认值8192

建表语句的参数详细介绍:https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-creating-a-table

细心的话应该能注意到我的建表语句中指定的存储引擎和上面的不一样，这是因为我用来测试的ClickHouse数据库是多副本的，索引引擎有些区别，副本之间存取元数据使用的是ZooKeeper，官网对于数据副本的介绍：https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/replication/。如果是自己部署的单机服务，只需将引擎修改为`AggregatingMergeTree()`即可。

我的建表语句中还用到了SimpleAggregateFunction函数，其含义就是如果出现两条重复的数据，要保留哪一条。SimpleAggregateFunction(max, DateTime) 中 max代表要取时间最大的那一条记录，DateTime则是指定该记录的数据类型。

3.关于数据合并

对于 MergeTree 表，数据通过以下不同的方式写入到磁盘当中：

我们主要了解数据的后台合并，当后台发现有重复数据时，会对重复数据创建合并计划，旧的数据记录会在一定时间后被移除 (old_parts_lifetime)。在这期间，他们不能被移动到其他的卷或磁盘。也就是说，直到数据片段被完全移除，它们仍然会被磁盘占用空间计算在内。

后台主动合并数据的行为和时间是不可控的，如果业务不能兼容重复数据就需要对数据做手动合并，手动合并一般会用到optimize命令。

1
OPTIMIZE TABLE [db.]name [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE]

此查询尝试初始化 MergeTree家族的表引擎的表中未计划合并数据部分。

该 OPTMIZE 查询也支持 MaterializedView 和 Buffer 引擎。 不支持其他表引擎。

当 OPTIMIZE 与 ReplicatedMergeTree 家族的表引擎一起使用时，ClickHouse将创建一个合并任务，并等待所有节点上的执行（如果 replication_alter_partitions_sync 设置已启用）。

  • 如果 OPTIMIZE 出于任何原因不执行合并，它不通知客户端。 要启用通知，请使用 optimize_throw_if_noop 设置。
  • 如果您指定 PARTITION，仅优化指定的分区。 如何设置分区表达式.
  • 如果您指定 FINAL，即使所有数据已经在一个部分中，也会执行优化。
  • 如果您指定 DEDUPLICATE，则将对完全相同的行进行重复数据删除（所有列进行比较），这仅适用于MergeTree引擎。

警告

OPTIMIZE 无法修复 “Too many parts” 错误。

4.总结

  • 在建表的时候需要注意除了指定要保留记录的条件即SimpleAggregateFunction指定的列，其他列不要使用SimpleAggregateFunction修饰，否则可能会导致数据排序和数据合并异常。

  • 我们原有业务的数据库使用的是mysql 5.6数据库，并不包含分区列，在导入数据的时候踩了不少坑，最后是使用了一个中间库，中间库是mysql 5.7，之所以采用这个版本的mysql是因为新增了计算列，这样就可以避免数据重洗，新增计算列的数据就可以直接导入到ClickHouse中，导入语句：

    1
    2
    3
    insert into test.test
    SELECT * 
      FROM mysql('127.0.0.1:3306', 'db', 'table', 'user', 'password') where pk=111

    ClickHouse中数据备份（使用备份表的方式）：

    1
    insert into test_bk SELECT * FROM test

在业务新增ClickHouse数据库的过程中踩了很多坑，每个坑还都不一样，对耐心是个考验，不停的变换思路，不断思考，坚持不懈，解决bug，只是时间问题。