Clickhouse多表关联查询内存超限问题及解决方案咨询

一、查询优化方法 #

1. 调整JOIN顺序，优先关联小维度表 #

ClickHouse按JOIN语句的顺序构建哈希表，优先关联数据量小的维度表（如branch_client、branch_product），可大幅减小哈希表的内存占用。调整后的查询结构示例：

SELECT
    s.branch_id AS branch_id,
    pa.sharer_id as sharer_id,
    si.commission_type_id as commission_type_id,
    bp.product_id as product_id,
    bc.client_id as client_id,
    SUM(si.amount_exclusive) AS production,
    SUM(si.premium)          AS premuim,
    SUM(si.fees)             AS fee,
    SUM(si.commission)       AS commission,
    'Y' as rp_indicator,
    date_trunc('month',s.statement_date) as statement_date
FROM branch_client bc
JOIN branch_product bp ON bc.id = bp.branch_client_id
JOIN product_agreement pa ON bp.id = pa.branch_product_id
RIGHT JOIN statement_item si ON pa.id = si.product_agreement_id
JOIN statement s ON si.statement_id = s.id
GROUP BY
    date_trunc('month',s.statement_date),
    s.branch_id,
    pa.sharer_id,
    si.commission_type_id,
    bp.product_id,
    bc.client_id

2. 预聚合大表数据，减少JOIN行数 #

对数据量最大的statement_item表提前按关联字段和聚合维度做预聚合，缩小后续JOIN的数据集：

WITH agg_statement_item AS (
    SELECT
        statement_id,
        product_agreement_id,
        commission_type_id,
        SUM(amount_exclusive) AS production,
        SUM(premium) AS premuim,
        SUM(fees) AS fee,
        SUM(commission) AS commission
    FROM statement_item
    GROUP BY statement_id, product_agreement_id, commission_type_id
)
SELECT
    s.branch_id,
    pa.sharer_id,
    agg.commission_type_id,
    bp.product_id,
    bc.client_id,
    agg.production,
    agg.premuim,
    agg.fee,
    agg.commission,
    'Y' as rp_indicator,
    date_trunc('month',s.statement_date) as statement_date
FROM statement s
JOIN agg_statement_item agg ON agg.statement_id = s.id
LEFT JOIN product_agreement pa ON pa.id = agg.product_agreement_id
JOIN branch_product bp ON bp.id = pa.branch_product_id
JOIN branch_client bc ON bc.id = bp.branch_client_id
GROUP BY
    date_trunc('month',s.statement_date),
    s.branch_id,
    pa.sharer_id,
    agg.commission_type_id,
    bp.product_id,
    bc.client_id

3. 添加数据过滤条件 #

如果业务不需要全量历史数据，在查询中添加WHERE子句过滤时间范围，直接减少参与计算的数据量：

-- 示例：仅查询2023年的数据
WHERE s.statement_date >= '2023-01-01' AND s.statement_date < '2024-01-01'

4. 优化关联字段索引 #

确保所有JOIN关联字段（如s.id、si.statement_id、pa.id等）配置了主键或二级索引，加速数据定位，避免全表扫描：

• 主键示例：PRIMARY KEY (id)
• 二级索引示例：INDEX idx_statement_id (statement_id) TYPE minmax GRANULARITY 8192

二、启用内存溢出到磁盘 #

ClickHouse支持将JOIN哈希表溢出到磁盘，通过以下参数配置：

1. 单查询临时配置 #

在查询开头添加参数设置，指定内存阈值（超过则写入磁盘）：

SET join_use_nulls = 1; -- 启用外部JOIN必须开启此参数
SET max_bytes_before_external_join = 1073741824; -- 1GB，可根据实际内存调整
-- 执行你的查询语句
SELECT ...

2. 全局配置（永久生效） #

在users.xml的目标用户配置中添加如下参数，对所有查询生效：

<user>
    <name>your_username</name>
    <!-- 其他配置 -->
    <max_bytes_before_external_join>1073741824</max_bytes_before_external_join>
    <join_use_nulls>1</join_use_nulls>
</user>

注意：确保ClickHouse的临时目录（tmp_path配置）有足够磁盘空间存放溢出数据。

内容的提问来源于stack exchange，提问作者Gerrit van Zyl