ClickHouse不同数据类型列关联超时问题求助

问题分析 #

核心问题是关联列类型不匹配导致关联时无法利用索引，加上查询逻辑的小错误，引发了超时。虽然单独查询两张表数据量只有几万条，但关联时的执行计划可能因为类型转换、过滤条件失效等原因，变成了低效的全量关联。

具体优化步骤 #

1. 修正过滤条件的明显错误 #

原查询中PREWHERE的日期条件存在逻辑错误：

datetimeCreated BETWEEN toDateTime(today()) - INTERVAL 5 MONTH AND datetimeCreated

这等价于仅限制datetimeCreated >= 过去5个月，但冗余的AND datetimeCreated会干扰ClickHouse的过滤优化，可能导致实际返回数据量远超预期。正确写法应为：

datetimeCreated BETWEEN toDateTime(today()) - INTERVAL 5 MONTH AND toDateTime(today())

确保只取过去5个月内的数据，有效缩小关联基数。

2. 提前完成类型转换，避免关联时动态转换 #

在CTE阶段就完成类型转换，让关联时两边列类型完全一致，这样ClickHouse可以利用索引或更高效的关联算法：

• 左表accId已通过replaceRegexpAll过滤为纯数字字符串，直接在CTE t中转换为int64，并以此列作为关联键；
• 右表accId是Nullable(int64)，提前过滤掉NULL值，避免关联时空值判断的额外开销。

3. 使用MATERIALIZED CTE避免重复计算 #

ClickHouse默认会内联CTE，导致相同子查询被多次执行。加上MATERIALIZED关键字让CTE只计算一次并缓存结果，大幅减少计算量：

WITH
    t AS MATERIALIZED (
        SELECT 
            ticketId, 
            toInt64(accId) AS accountId, -- 提前转换类型
            datetimeCreated,
            os AS platform -- 统一字段名，避免原查询中platform字段的笔误
        FROM(
            SELECT
                ticketId, 
                accId,
                tsToDateTime(entryCreateTime) AS datetimeCreated, 
                os
            FROM myTab.table 
            PREWHERE 
                datetimeCreated BETWEEN toDateTime(today()) - INTERVAL 5 MONTH AND toDateTime(today())
                AND status = 'new' 
                AND role = 'end-user'
                AND project = 'pj' 
                AND replaceRegexpAll(accId,'\\D', '') = accId -- 过滤非数字ID
            GROUP BY ticketId, accId, datetimeCreated, os
        )
    ),
    p AS MATERIALIZED (
        SELECT 
            tsToDateTime(ts) AS datetimePaid, 
            accId,
            amount
        FROM otherTab.tab2  
        PREWHERE 
            datetimePaid >= today() - 365
            AND accId IS NOT NULL -- 过滤Nullable空值
    )

4. 优化关联逻辑，调整表顺序 #

ClickHouse默认将右表作为哈希表的构建方，把数据量更小的表放在右边，能减少哈希表的内存占用和构建时间。如果p表数据量更小，保持当前顺序；如果t表更小，可交换关联顺序：

-- 若t表更小，交换关联顺序示例
SELECT 
    t.ticketId, 
    t.accountId, 
    t.datetimeCreated,
    t.platform,
    p.datetimePaid,
    p.amount
FROM p
INNER JOIN t ON p.accId = t.accountId
WHERE p.datetimePaid BETWEEN t.datetimeCreated - INTERVAL 4 MONTH AND t.datetimeCreated

5. 查看执行计划定位瓶颈 #

用EXPLAIN ANALYZE执行原查询，定位具体耗时阶段：

EXPLAIN ANALYZE
-- 插入你的原查询语句

重点关注：

• 是否存在Full Scan（全表扫描），说明索引未被利用；
• 关联类型是HashJoin还是MergeJoin，大数据量场景下MergeJoin（需两边排序）可能更高效；
• 数据过滤是否在PREWHERE阶段生效，是否提前过滤了大部分数据。

修改后的完整查询示例 #

WITH
    t AS MATERIALIZED (
        SELECT 
            ticketId, 
            toInt64(accId) AS accountId,
            datetimeCreated,
            os AS platform
        FROM(
            SELECT
                ticketId, 
                accId,
                tsToDateTime(entryCreateTime) AS datetimeCreated, 
                os
            FROM myTab.table 
            PREWHERE 
                datetimeCreated BETWEEN toDateTime(today()) - INTERVAL 5 MONTH AND toDateTime(today())
                AND status = 'new' 
                AND role = 'end-user'
                AND project = 'pj' 
                AND replaceRegexpAll(accId,'\\D', '') = accId
            GROUP BY ticketId, accId, datetimeCreated, os
        )
    ),
    p AS MATERIALIZED (
        SELECT 
            tsToDateTime(ts) AS datetimePaid, 
            accId,
            amount
        FROM otherTab.tab2  
        PREWHERE 
            datetimePaid >= today() - 365
            AND accId IS NOT NULL
    )
SELECT 
    ticketId, 
    accountId, 
    datetimeCreated,
    COUNT(amount) AS payms_cnt,
    ROUND(SUM(amount)) AS payms_sum
FROM (
    SELECT 
        t.ticketId, 
        t.accountId, 
        t.datetimeCreated,
        t.platform,
        p.datetimePaid,
        p.amount
    FROM t
    INNER JOIN p ON t.accountId = p.accId
    WHERE p.datetimePaid BETWEEN t.datetimeCreated - INTERVAL 4 MONTH AND t.datetimeCreated
)
GROUP BY accountId, ticketId, datetimeCreated, platform
ORDER BY accountId, datetimeCreated 
;

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