ClickHouse CollapsingMergeTree插入异常致查询错误及原子性问询

问题背景 #

基于ClickHouse构建用户标签系统，使用CollapsingMergeTree（CMT）引擎处理实时更新去重，表结构如下：

CREATE TABLE user_table
(
    `tag_id` UInt64,
    `tag_value` Int64,
    `user_id` UInt32,
    `sign` Int8,
    `create_time` DateTime DEFAULT now()
)
ENGINE = CollapsingMergeTree(sign)
PARTITION BY tag_id % 200
ORDER BY (tag_id, tag_value, user_id)
SETTINGS index_granularity = 8192;

异常场景复现 #

执行以下插入序列后，查询结果不符合预期：

-- 初始插入标签值1
INSERT INTO user_table (tag_id, tag_value, user_id, sign)
VALUES (999, 1, 100, 1);

-- 尝试更新标签值（需插入撤销旧值+新增新值两条记录），但仅新增记录插入成功
INSERT INTO user_table (tag_id, tag_value, user_id, sign)
VALUES (999, 1, 2, 1);

-- 重试批量插入，两条记录均成功
INSERT INTO user_table (tag_id, tag_value, user_id, sign)
VALUES (999, 1, 1, -1), (999, 1, 2, 1);

-- 尝试删除标签值1对应的记录
INSERT INTO user_table (tag_id, tag_value, user_id, sign)
VALUES (999, 1, 2, -1);

-- 查询返回了user_id=2，结果错误
SELECT user_id FROM user_table
WHERE tag_id = 999 AND tag_value = 2
GROUP BY (tag_id, tag_value, user_id)
HAVING sum(sign) > 0

核心结论与解决方案 #

关于批量插入原子性 #

ClickHouse的单批次INSERT操作是原子性的——同一批次内的所有记录要么全部写入成功，要么全部失败，不会出现部分写入的情况。你遇到的部分插入成功，大概率是客户端逻辑错误（如将关联记录拆分为多次独立INSERT），而非ClickHouse本身的问题。

调整方案 #

1. 查询层面：增强一致性校验

当前查询依赖sum(sign) > 0判断有效记录，脏数据会导致误判，可通过两种方式优化：

• 引入版本控制字段
  修改表结构新增version字段，每次操作使用递增版本号，确保取最新状态：

  CREATE TABLE user_table
  (
      `tag_id` UInt64,
      `tag_value` Int64,
      `user_id` UInt32,
      `sign` Int8,
      `version` UInt64,
      `create_time` DateTime DEFAULT now()
  )
  ENGINE = CollapsingMergeTree(sign)
  PARTITION BY tag_id % 200
  ORDER BY (tag_id, tag_value, user_id, version)
  SETTINGS index_granularity = 8192;
  

  查询时先聚合用户的sign总和，再校验有效性：

  SELECT user_id
  FROM (
      SELECT tag_id, tag_value, user_id, sum(sign) AS total_sign
      FROM user_table
      WHERE tag_id = 999 AND tag_value = 2
      GROUP BY tag_id, tag_value, user_id
  )
  WHERE total_sign > 0
  

• 使用FINAL强制合并（仅适合离线场景）
  添加FINAL关键字强制CMT合并数据后返回结果，但会大幅降低查询性能，仅适合非实时分析：

  SELECT user_id FROM user_table FINAL
  WHERE tag_id = 999 AND tag_value = 2
  GROUP BY (tag_id, tag_value, user_id)
  HAVING sum(sign) > 0
  

2. 重试机制：保证操作幂等性

为避免重试产生脏数据，需确保操作幂等：

• 为每个业务操作生成唯一op_id（如UUID），将其加入表结构和ORDER BY主键：

  CREATE TABLE user_table
  (
      `tag_id` UInt64,
      `tag_value` Int64,
      `user_id` UInt32,
      `sign` Int8,
      `op_id` String,
      `create_time` DateTime DEFAULT now()
  )
  ENGINE = CollapsingMergeTree(sign)
  PARTITION BY tag_id % 200
  ORDER BY (tag_id, tag_value, user_id, op_id)
  SETTINGS index_granularity = 8192;
  

• 重试时复用同一个op_id，重复插入的相同记录会被CMT合并抵消，避免脏数据。

• 客户端必须将同一业务操作的关联记录（如更新时的撤销+新增记录）放在同一批次INSERT中提交，禁止拆分。

异常根源复盘 #

错误结果的核心原因是中间步骤的非原子操作：单独插入了新增记录，重试时又重复插入，导致user_id=2的sign总和为1+1-1=1>0，被误判为有效记录。通过原子批量插入和幂等性设计可彻底避免这类问题。

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