ClickHouse Decimal(19,5)字段更新失效问题求助

问题背景 #

正在为新数据库调研ClickHouse，要求UPDATE操作占比极低（20亿行中占比<0.1%）。目前遇到问题：尝试将Decimal(19,5)字段值从19.39999更新为19.4，但操作执行后查询结果无变化。测试示例如下：

CREATE TABLE TestTable (ID Int64, Name String, Value Float64, val Decimal(19,5), Date Date ) ENGINE = MergeTree() ORDER BY ID;

INSERT INTO TestTable (ID, Name, Value, val, Date) VALUES (1, 'John Doe', 42.5, 19.39999, '2023-01-01');

ALTER TABLE TestTable UPDATE val = 19.4 WHERE ID = 1;

SELECT * FROM TestTable;

执行查询后结果仍为：

┌─ID─┬─Name─────┬─Value─┬──────val─┬───────Date─┐
│  1 │ John Doe │  42.5 │ 19.39999 │ 2023-01-01 │
└────┴──────────┴───────┴──────────┴────────────┘

原因分析 #

ClickHouse的ALTER TABLE UPDATE属于异步mutation操作，不会立即修改底层数据文件。执行UPDATE后，ClickHouse仅生成一条mutation日志，后台merge进程会在满足合并条件时才真正执行数据更新并合并版本。你执行完UPDATE后立即查询，看到的是未合并的旧版本数据。

另外，Decimal(19,5)类型的19.4实际存储为19.40000，更新时写19.4会自动转换为该值，但这不是操作不生效的核心原因，本质还是mutation的异步特性。

解决方法 #

方法1：等待或手动加速mutation执行 #

• 查看mutation进度：通过系统表确认mutation是否完成：

  SELECT * FROM system.mutations WHERE table = 'TestTable';
  

  当is_done字段变为1时，mutation执行完成，此时查询就能看到更新后的数据。
• 调整配置加速处理：如果需要mutation更快执行，可修改以下配置（在config.xml或用户自定义配置文件中）：
  • mutation_merge_threshold：触发mutation合并的最小数据片段行数，默认1000000，降低该值可让小片段更快触发合并。
  • mutation_max_threads：处理mutation的最大线程数，默认1，适当提高可加速处理。
  • background_pool_size：后台任务线程池大小，确保有足够线程分配给mutation任务。

方法2：采用删插替代UPDATE #

对于UPDATE占比极低（<0.1%）的场景，删插（DELETE+INSERT）是更高效的方案——MergeTree对写入和删除的优化更直接，避免了mutation带来的后台合并开销。

• 操作步骤：
  1. 删除目标行：

     ALTER TABLE TestTable DELETE WHERE ID = 1;
     

  2. 插入更新后的新行：

     INSERT INTO TestTable (ID, Name, Value, val, Date) VALUES (1, 'John Doe', 42.5, 19.40000, '2023-01-01');
     

• 差异阈值X的设定：
  阈值X取决于业务精度需求。Decimal(19,5)保留5位小数，当新旧值的差的绝对值大于0.000005时（超过半个最小精度单位），才需要执行删插。比如19.39999和19.4的差为0.00001，大于阈值，需要操作；若差值≤0.000005，四舍五入后Decimal(19,5)的存储值无变化，无需处理。

注意事项 #

• 无论采用哪种方案，都要确保表的ORDER BY字段（主键）唯一，避免出现重复行。
• 针对20亿行的超大规模表，删插操作必须用精准过滤条件（如ID这类唯一键），避免扫描大量无关数据。

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