ClickHouse中排序键成员可变时的数据去重方案咨询

方案1：调整表结构为ReplacingMergeTree（适配分布式场景） #

核心去重键是唯一列id，可将本地表改为ReplacingMergeTree，排序键设为(id)，同时指定timestamp为版本列（保留最新时间戳的记录）：

-- 本地表定义
CREATE TABLE tracks_local ON CLUSTER your_cluster
(
    event String,
    flowGroup String,
    flowId String,
    timestamp DateTime64(3),
    originalTimestamp DateTime64(3),
    id String
)
ENGINE = ReplacingMergeTree(timestamp)
ORDER BY (id)
PRIMARY KEY (event, flowGroup, flowId, timestamp); -- 保留原主键保障查询性能

分布式表指向该本地表：

CREATE TABLE tracks ON CLUSTER your_cluster
AS tracks_local
ENGINE = Distributed(your_cluster, default, tracks_local, rand());

• 原理：ReplacingMergeTree在后台合并数据时，自动保留同一id下timestamp最大的记录，无需插入前检查，彻底规避写入性能损耗。
• 注意：合并为异步操作，查询时可加FINAL关键字强制返回去重后数据，或等待后台自动完成合并。

方案2：前置Redis布隆过滤器快速去重 #

在写入ClickHouse前，用Redis布隆过滤器过滤已存在的id，大幅减少写入阶段的查询验证压力：

1. 初始化布隆过滤器：根据数据量和可接受误判率（如0.01）配置参数
2. 写入流程：
   • 先查询Redis布隆过滤器判断id是否存在
   • 若不存在，直接插入ClickHouse并将id加入布隆过滤器
   • 若存在，再执行ClickHouse的exists查询做最终验证（规避布隆误判）

• 优势：布隆过滤器查询性能达毫秒级，可过滤99%以上重复请求，显著降低ClickHouse的查询负载。

方案3：给id列添加二级索引优化现有插入逻辑 #

若不想修改表结构，可给id列添加布隆过滤器索引，加速exists子查询性能：

ALTER TABLE tracks ON CLUSTER your_cluster
ADD INDEX idx_id id TYPE bloom_filter(0.01) GRANULARITY 1;

简化插入SQL（id唯一，直接检查即可）：

insert into tracks
select
  'xxx' as event,
  'yyy' as flowGroup,
  'zzz' as flowId,
  '2021-01-01 00:00:00.123' as timestamp,
  '2021-01-01 00:00:00.012' as originalTimestamp,
  '1234' as id
where not exists (
  select 1 from tracks where id = '1234'
);

• 原理：布隆索引可快速判断id是否存在，将原全表扫描转为索引查询，性能提升明显。

方案4：异步去重的物化视图方案 #

若写入性能优先级最高，可采用「先写入临时表，异步去重到目标表」的方式：

1. 创建临时写入表：

CREATE TABLE tracks_staging ON CLUSTER your_cluster
AS tracks
ENGINE = MergeTree
ORDER BY (event, flowGroup, flowId, timestamp);

2. 创建物化视图，后台异步将临时表数据去重后写入目标表：

CREATE MATERIALIZED VIEW tracks_mv ON CLUSTER your_cluster
TO tracks
AS
SELECT
    event,
    flowGroup,
    flowId,
    max(timestamp) as timestamp,
    any(originalTimestamp) as originalTimestamp,
    id
FROM tracks_staging
GROUP BY id, event, flowGroup, flowId;

• 优势：写入临时表无性能损耗，物化视图后台完成去重，适配高并发写入场景。
• 注意：需定期清理临时表历史数据，避免存储浪费。

内容的提问来源于stack exchange，提问作者Alexandre Couret