ClickHouse Join查询内存溢出优化求助：日数据查询内存超限

问题根源 #

原查询中，ClickHouse优化器未按预期优先过滤calls表的单日数据，反而可能尝试将全量20GB的temp_calls加载到内存做哈希连接，直接触发10GB内存限制导致溢出。以下是针对性优化方案：

优化方案 #

1. 强制提前过滤并使用临时表锁定数据量

先将calls的单日数据写入临时表，明确限定关联的左表规模，避免优化器调整执行顺序：

-- 先导出单日calls数据到临时表（仅1GB，内存可容纳）
CREATE TEMPORARY TABLE temp_daily_calls 
AS SELECT * FROM calls 
WHERE create_date >= '2023-09-07 00:00:00' AND create_date < '2023-09-08 00:00:00';

-- 用临时表关联temp_calls
INSERT INTO new_calls_with_new_column
SELECT * FROM temp_daily_calls a
LEFT JOIN (SELECT id, new_column FROM temp_calls) b USING id;

2. 切换为嵌套循环连接，避免加载大表到内存

ClickHouse默认哈希连接会把右表全量加载到内存，这里temp_calls是20GB，远超内存限制。强制使用嵌套循环连接，以1GB的左表为驱动，逐条查询右表：

INSERT INTO new_calls_with_new_column
SELECT * FROM 
    (SELECT * FROM calls WHERE create_date >= '2023-09-07 00:00:00' AND create_date < '2023-09-08 00:00:00') a
LEFT JOIN (SELECT id, new_column FROM temp_calls) b 
USING id
SETTINGS join_algorithm = 'nested_loop';

3. 给temp_calls的id字段加索引，加速关联查询

如果temp_calls未对id建索引，关联时会全表扫描，加剧内存和IO消耗：

-- 添加minmax索引（轻量且适合等值查询）
ALTER TABLE temp_calls ADD INDEX idx_id id TYPE minmax GRANULARITY 8192;

-- 若表结构允许，直接设置id为主键（关联效率更高）
ALTER TABLE temp_calls MODIFY PRIMARY KEY id;

4. 用字典表存储id-new_column映射（长期稳定场景适用）

如果temp_calls数据更新不频繁，将其转为内存字典表，后续关联直接从内存读取映射，避免重复扫描大表：

-- 创建内存哈希字典
CREATE DICTIONARY dict_temp_calls (
    id UInt64,
    new_column String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
    DB 'your_database'
    TABLE 'temp_calls'
))
LAYOUT(HASHED())
LIFETIME(MIN 3600 MAX 7200); -- 1-2小时自动刷新

-- 通过字典函数关联查询
INSERT INTO new_calls_with_new_column
SELECT *, dictGet('dict_temp_calls', 'new_column', id) AS new_column
FROM calls 
WHERE create_date >= '2023-09-07 00:00:00' AND create_date < '2023-09-08 00:00:00';

5. 临时调高单查询内存限制（应急方案）

若上述优化仍无法快速生效，可临时放宽当前查询的内存配额（不推荐长期依赖）：

INSERT INTO new_calls_with_new_column
SELECT * FROM 
    (SELECT * FROM calls WHERE create_date >= '2023-09-07 00:00:00' AND create_date < '2023-09-08 00:00:00') a
LEFT JOIN (SELECT id, new_column FROM temp_calls) b USING id
SETTINGS max_memory_usage = 15000000000; -- 临时设置为15GB

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