全部标签
写点什么
开源架构OpenHarmony算法元宇宙学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

大数据 -139 ClickHouse MergeTree 最佳实践：Replacing 去重、Summing 求和、分区设计与物化视图替代方案

作者：武子康
  • 2025-10-30
    山东

  • 本文字数：3938 字

    阅读完需：约 13 分钟

大数据-139 ClickHouse MergeTree 最佳实践：Replacing 去重、Summing 求和、分区设计与物化视图替代方案

TL;DR

  • 场景：解决去重/更新与按键求和两类常见“准实时明细表”需求。

  • 结论：ReplacingMergeTree 用于按排序键去重/按版本取最新；SummingMergeTree 用于按排序键合并数值列之和，二者都在合并（merge）阶段生效，读即见最终结果需 SELECT … FINAL。

  • 产出：最小可跑示例（MRE）、OPTIMIZE/FINAL 使用时机、常见坑定位清单、回滚与数据修复脚本。



ReplacingMergeTree

简介

这个引擎是在 MergeTree 的基础上，添加了处理重复数据的功能，该引擎和 MergeTree 的不同之处在于它会删除具有相同主键的重复项。

特点

以下是扩写后的内容：

Repeater 去重机制详解

  1. 排序键作为唯一键

  2. 系统使用 ORDER BY 子句中指定的列作为判断数据是否重复的唯一键。这些列的组合值相同时会被视为重复数据。

  3. 示例：如果设置 ORDER BY (user_id, event_time)，那么具有相同用户 ID 和事件时间的数据将被视为重复。

  4. 合并时触发的去重

  5. 去重操作仅在后台合并(Merge)数据文件时执行，不会在数据插入时立即处理。

  6. 这种设计避免了实时去重的性能开销，能够批量处理大量数据。

  7. 分区级别的去重范围

  8. 去重以数据分区(Partition)为单位进行，每个分区独立处理。

  9. 跨分区的重复数据不会被自动删除，例如上个月分区和本月分区可能存在重复数据。

  10. 应用场景：适合按时间分区的场景，保留历史分区数据完整性。

  11. 依赖预先排序的顺序

  12. 系统依赖数据已经按照 ORDER BY 键预先排好顺序的特性来高效识别重复数据。

  13. 实现原理：利用已排序的数据可以线性扫描就能发现连续重复项，无需额外计算。

  14. 无版本号时的处理策略

  15. 如果没有配置版本号(ver)列：

  16. 保留重复项中的最后一行数据(按照物理存储顺序)

  17. 示例：对于 3 条重复记录 A、B、C，最终保留 C

  18. 有版本号时的处理策略

  19. 如果设置了版本号列：

  20. 系统会保留版本号最大的数据行

  21. 应用场景：可以实现乐观锁机制，例如用时间戳或版本号标记数据更新

  22. 示例：对于 3 条重复记录(ver=1)、(ver=3)、(ver=2)，最终保留 ver=3 的记录

案例

创建新表

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;
复制代码


运行结果如下图所示：


插入数据

INSERT INTO replace_table VALUES ('A001', 'C1', '2024-08-01 08:00:00');INSERT INTO replace_table VALUES ('A001', 'C1', '2024-08-02 08:00:00');INSERT INTO replace_table VALUES ('A001', 'C8', '2024-08-03 08:00:00');INSERT INTO replace_table VALUES ('A001', 'C9', '2024-08-04 08:00:00');INSERT INTO replace_table VALUES ('A002', 'C2', '2024-08-05 08:00:00');INSERT INTO replace_table VALUES ('A003', 'C3', '2024-08-06 08:00:00');
复制代码


运行结果如下所示：


optimize

首先说一下，optimize 的作用是：


  • 合并数据块：ClickHouse 是一个列式的数据库，它的数据是以数据块（parts）的形式存储在磁盘上，OPTIMIZE TABLE 语句通过较小的数据块来减少块的数量，从而提高查询性能和磁盘利用率。

  • 删除标记的行：如果表中有被标记为删除的行，这些行将在优化过程中被真正删除，释放相应的空间。

  • 分区管理：可以对表的指定分区进行优化，以减少分区内的碎片。


SELECT  *FROM  replace_table;
复制代码


运行结果如下图所示，通过观察，去重是根据 ORDER BY 来的，并非 PRIMARY KEY：



继续插入一条数据：


INSERT INTO replace_table VALUES('A001', 'c1', '2024-01-01 08:00:00')
复制代码


执行结果如下所示：



观察上图可以看出，不同分区的数据不会去重。

SummingMergeTree

简介

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

特点

  • 用 ORDER BY 排序键作为聚合数据的条件 KEY

  • 合并分区的时候触发汇总逻辑

  • 以数据分区为单位聚合数据，不同分区的数据不会被汇总

  • 如果在定义引擎时指定了 Columns 汇总列（非主键）则 SUM 汇总这些字段

  • 如果没有指定，则汇总所有非主键的数值类型字段

  • SUM 汇总相同的聚合 KEY 的数据，依赖 ORDER BY 排序

  • 同一分区的 SUM 汇总过程，非汇总字段的数据保留第一行取值

  • 支持嵌套结构，但列字段名称必须以 Map 后缀结束

案例 1

创建新表

CREATE TABLE smt_table (  date Date,  name String,  a UInt16,  b UInt16) ENGINE = SummingMergeTree(date, (date, name), 8192, (a));
复制代码


运行的结果如下图所示：


插入数据

insert into smt_table (date, name, a, b) values ('2024-08-10', 'a', 1, 2);insert into smt_table (date, name, a, b) values ('2024-08-10', 'b', 2, 1);insert into smt_table (date, name, a, b) values ('2024-08-11', 'b', 3, 8);insert into smt_table (date, name, a, b) values ('2024-08-11', 'b', 3, 8);insert into smt_table (date, name, a, b) values ('2024-08-11', 'a', 3, 1);insert into smt_table (date, name, a, b) values ('2024-08-12', 'c', 1, 3);
复制代码


运行结果如下所示：


optimize

等待一段时间，或者手动 optimize table 来触发合并，再查询信息：


OPTIMIZE TABLE smt_table;SELECT   *FROM   smt_table;
复制代码


执行结果如下图所示：



通过观察，我们会发现，2024-08-11， b 和 a 列合并相加了，b 列取了 8（因为 b 列为 8 的数据最先插入的）

案例 2

创建新表

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);
复制代码


执行结果如下图：


插入数据

insert into table summing_table values('A000','beijing',10,20,'2024-08-20 08:00:00');insert into table summing_table values('A000','beijing',20,30,'2024-08-30 08:00:00');insert into table summing_table values('A000','shanghai',10,20,'2024-08-20 08:00:00');insert into table summing_table values('A000','beijing',10,20,'2024-06-20 08:00:00');insert into table summing_table values('A001','beijing',50,60,'2024-02-20 08:00:00');
复制代码


执行结果如下图所示：


optimize

OPTIMIZE TABLE summing_table;SELECT   *FROM  summing_table;
复制代码


执行结果如下图所示：



通过观察，根据 ORDER BY 排序键（id, city）作为聚合 KEY，因为没有在建表时指定 SummingMergeTree 的 SUM 列，所以把所有非主键数值类型的列都进行了 SUM 处理。

案例 3

SummingMergeTree 支持嵌套类型的字段，但列字段名称必须以 Map 后缀结束。

创建新表

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


执行的结果如下图所示：


插入数据

INSERT INTO summing_table_nested VALUES ('1', [101, 102], [201, 202], [1001, 1002], '2024-08-01 10:00:00');INSERT INTO summing_table_nested VALUES ('2', [103, 104], [203, 204], [1003, 1004], '2024-08-01 10:00:00');INSERT INTO summing_table_nested VALUES ('1', [105, 106], [205, 206], [1005, 1006], '2024-08-01 10:00:00');INSERT INTO summing_table_nested VALUES ('2', [107, 108], [207, 208], [1007, 1008], '2024-08-02 10:00:00');INSERT INTO summing_table_nested VALUES ('3', [109, 110], [209, 210], [1009, 1010], '2024-08-02 10:00:00');INSERT INTO summing_table_nested VALUES ('4', [111, 112], [211, 212], [1011, 1012], '2024-08-02 10:00:00');
复制代码


执行过程如下图所示：



执行过程如下图所示：



可以看到，我们插入了 6 条数据，但是查询到的只有 4 条，而且此外也进行计算的 SUM 处理。

错误速查

回滚与数据修复脚本

示例如下：


-- 1) 误操作后回收旧分区（示例）ALTER TABLE replace_table DETACH PARTITION 202408;-- 导出/修复后再 ATTACH-- 2) 统一重写：将明细重放到一张干净表，再原子切换CREATE TABLE replace_table_fixed AS replace_table;INSERT INTO replace_table_fixed SELECT * FROM replace_table FINAL; -- 得到去重后的明细EXCHANGE TABLES replace_table AND replace_table_fixed; -- 原子切表（新版支持）
复制代码

FAQ

  • PRIMARY KEY 会参与去重吗？ 不会。它只影响稀疏索引；去重/求和看 ORDER BY。

  • 能跨分区去重/求和吗？ 不能，需在离线层归并或改分区策略。

  • 为什么 FINAL 很慢？ 它会在线合并 parts；仅用于验证或小量报表。

  • Summing 对浮点安全吗？ 普通的加法可能有精度误差；金融场景用 Decimal。

  • Replacing 的版本列怎么选？ 用单调递增时间戳/版本号；不要用非单调字段。

其他系列

🚀 AI 篇持续更新中（长期更新）

AI 炼丹日志-29 - 字节跳动 DeerFlow 深度研究框斜体样式架 私有部署 测试上手 架构研究，持续打造实用 AI 工具指南！AI-调查研究-108-具身智能 机器人模型训练全流程详解：从预训练到强化学习与人类反馈🔗 AI模块直达链接

💻 Java 篇持续更新中（长期更新）

Java-154 深入浅出 MongoDB 用 Java 访问 MongoDB 数据库 从环境搭建到 CRUD 完整示例 MyBatis 已完结，Spring 已完结，Nginx 已完结，Tomcat 已完结，分布式服务正在更新！深入浅出助你打牢基础！🔗 Java模块直达链接

📊 大数据板块已完成多项干货更新（300 篇）：

包括 Hadoop、Hive、Kafka、Flink、ClickHouse、Elasticsearch 等二十余项核心组件，覆盖离线+实时数仓全栈！大数据-278 Spark MLib - 基础介绍 机器学习算法 梯度提升树 GBDT 案例 详解🔗 大数据模块直达链接

划线
评论
复制
发布于: 刚刚阅读数: 5

版权声明: 本文为 InfoQ 作者【武子康】的原创文章。

原文链接:【http://xie.infoq.cn/article/497c3dc74a48868b454ddef59】。文章转载请联系作者。

大数据flinkspark分布式Clickhouse
用户头像

武子康

关注

永远好奇 无限进步 2019-04-14 加入

Hi, I'm Zikang，好奇心驱动的探索者 | INTJ / INFJ 我热爱探索一切值得深究的事物。对技术、成长、效率、认知、人生有着持续的好奇心和行动力。 坚信「飞轮效应」，相信每一次微小的积累，终将带来深远的改变。

评论

发布
暂无评论
Copyright © 2025, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
大数据-139 ClickHouse MergeTree 最佳实践：Replacing 去重、Summing 求和、分区设计与物化视图替代方案_大数据_武子康_InfoQ写作社区