如何强制SummingMergeTree执行聚合？大规模数据导入后聚合延迟问题

在导入数百GB数据到SummingMergeTree表时，即使执行OPTIMIZE TABLE ... FINAL，查询返回的聚合结果仍存在延迟（出现重复数据、求和结果不符），需要确保最终查询能返回正确的求和结果。

重现步骤对应的SQL代码 #

CREATE TABLE IF NOT EXISTS temp.table
(
    date     Date, 
    session  String, 
    total    Int32
)
ENGINE = SummingMergeTree
ORDER BY ( date, session );

-- Importing '2024-01-01'
INSERT INTO temp.table
SELECT date, session, sum(n) AS total FROM temp.data WHERE date = '2024-01-01' GROUP BY date, session
SETTINGS async_insert = 1;

-- Importing '2024-01-02'
INSERT INTO temp.table
SELECT date, session, sum(n) AS total FROM temp.data WHERE date = '2024-01-02' GROUP BY date, session
SETTINGS async_insert = 1;

-- date = '2024-01-03'
-- date = '2024-01-04'
-- 大量后续数据导入...

-- 执行强制优化
OPTIMIZE TABLE temp.table FINAL;

-- 查询最终聚合结果
SELECT * FROM temp.table;

解决方法 #

1. 查询时显式执行聚合（最可靠） #

SummingMergeTree的后台合并是异步优化手段，不保证数据实时合并完成。直接在查询时显式聚合，无论底层数据是否合并，都能得到正确的求和结果：

SELECT date, session, sum(total) AS total
FROM temp.table
GROUP BY date, session;

2. 分分区执行OPTIMIZE操作 #

给表添加分区键（比如按date分区），然后逐个分区执行OPTIMIZE，避免全表大体积数据合并的压力，提升合并完成率：

• 先修改表结构（如果未分区）：

ALTER TABLE temp.table ADD PARTITION BY date;

• 逐个分区执行优化：

OPTIMIZE TABLE temp.table PARTITION '2024-01-01' FINAL;
OPTIMIZE TABLE temp.table PARTITION '2024-01-02' FINAL;
-- 依次处理其他日期分区

3. 确保异步插入完全落盘后再执行OPTIMIZE #

使用async_insert=1时，插入请求是异步处理的，可能存在数据未完全写入就执行OPTIMIZE的情况。先执行以下命令等待所有异步插入完成：

SYSTEM FLUSH ASYNC INSERT;

之后再执行OPTIMIZE TABLE ... FINAL。

4. 调整合并相关配置（辅助优化） #

针对超大表，可以调整ClickHouse的合并线程数和阈值，提升后台合并效率：

• 临时调整会话级配置：

SET merge_max_threads = 8; -- 根据服务器CPU核心数调整
SET max_bytes_to_merge_at_max_space_in_pool = 10000000000; -- 增大合并阈值

• 也可以在config.xml中修改全局配置，长期生效。

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