SQL ClickHouse 的 OPTIMIZE TABLE FINAL vs PARTITION 的合并与去重策略

optimize table … final 会阻塞写入且不保证立刻生效：它强制全表合并，锁表、耗时随数据量增长，执行后查询仍可能返回旧结果；应仅在低峰期手动触发。

OPTIMIZE TABLE FINAL 会阻塞写入且不保证立刻生效

clickhouse 的 OPTIMIZE TABLE ... FINAL 是强制触发全表合并的手段，但它不是“立刻去重”，而是把所有分区里标记为 delete 或旧版本的数据一并重写进新分区。这个过程会锁住整张表（对 MergeTree 系列引擎），写入暂停，且执行时间随数据量线性增长——100GB 表可能卡住几分钟。

常见错误现象：OPTIMIZE TABLE t FINAL 执行完查 select count() FROM t 还是老结果；或者应用突然报 Table is locked。

• 只在低峰期手动触发，别放进定时任务自动跑
• 确认表引擎是 MergeTree 或其变种（ReplacingMergeTree、CollapsingMergeTree），Log 或 Memory 引擎不支持 FINAL
• 如果只是想查最新状态，优先用 SELECT ... FROM t FINAL，它不改数据，只做查询时去重

OPTIMIZE PARTITION 只合并指定分区，但不会跨分区去重

OPTIMIZE TABLE t PARTITION '202401' 只处理该分区内的 parts，把小 part 合成大 part，提升查询效率，但不会把 202401 分区里的重复行和 202402 分区里的同 key 行合并掉——ReplacingMergeTree 的去重逻辑只在同一个分区内部生效。

使用场景：分区表按天/月切分，某天数据写入异常导致碎片多，查得慢；或刚做完 INSERT SELECT 导入一批历史数据，需要快速整理。

• 分区名必须严格匹配 partition_id，比如 toYYYYMMDD(event_time) 生成的是 20240101，不能写成 '2024-01-01'
• 对 ReplacingMergeTree，OPTIMIZE PARTITION 不会重新排序或应用 version 字段逻辑，只做物理合并
• 执行后仍要靠 SELECT ... FINAL 查去重结果，否则普通查询看到的还是未合并前的多份数据

FINAL 查询开销大，别在高频接口里直接用

SELECT * FROM t FINAL 是最轻量的“实时去重”方式，但它会在查询时临时合并所有相关 parts，CPU 和内存消耗陡增，尤其当表有几十个分区、每个分区又有上百个小 parts 时，一次查询可能扫几 GB 内存。

容易踩的坑：开发测试时数据少，FINAL 很快；上线后数据量涨十倍，接口 P99 延迟从 50ms 暴涨到 2s，还拖垮整个节点。

• 只在后台报表、离线核对等低频场景用 FINAL
• 高频接口改用预聚合视图（MATERIALIZED VIEW）或定期跑 OPTIMIZE PARTITION + 缓存最新状态
• 确认 final 能力是否真被需要：如果业务只要“最近一次写入”，用 argMax(value, version) 配合 GROUP BY 更稳

ReplacingMergeTree 的去重依赖排序键和 version 字段，不是无脑删重复

ReplacingMergeTree 不会单纯按整行去重，它只在 **同一分区 + 同一排序键前缀** 的数据块内，按 version 取最大值保留一行。如果两行排序键不同（比如 (user_id, event_time)，但 event_time 差一秒），它们永远不会被当成重复行处理。

典型误用：以为插入两条 user_id=123 就能自动去重，结果发现查出来还是两条——因为没设 ORDER BY (user_id)，或者 event_time 被包含进排序键导致 key 实际不同。

• 建表时明确 ORDER BY (key)，不要带时间戳等高基数字段
• version 字段必须是 UInt64 或可比较类型，且每次更新都要递增（不能用 now()）
• 合并行为不可预测：即使满足条件，ClickHouse 也不保证“马上合并”，可能等下一次后台 merge 或人工 OPTIMIZE

事情说清了就结束。真正难的不是语法怎么写，是判断哪次写入该用 OPTIMIZE PARTITION，哪次该忍着不去碰 FINAL，还有——什么时候该换引擎。