ClickHouse时间维度对比查询优化疑问：改写后性能为何下降？

背景 #

有一张events表，包含维度字段dim1和时间列time，表采用PARTITION BY toStartOfDay(time)分区、ORDER BY (time, dim1)排序。2024-11-10 00:00:00至2024-11-11 23:00:00时间范围内约有40亿行数据，需求是对比两天dim1维度的请求计数。

原始全外连接查询 #

最初执行的全外连接查询可正常运行，但需扫描几乎全部目标时间范围的数据：

SELECT
    COALESCE(base.dim1, comparison.dim1) AS dim1,
    base.req_count AS req_count,
    comparison.req_count AS req_count_prev,
    base.req_count - comparison.req_count AS req_count_delta
FROM (
        SELECT
            dim1,
            COUNT(request) AS req_count
        FROM events
        WHERE time >= toDateTime('2024-11-11 00:00:00') 
          AND time < toDateTime('2024-11-11 23:00:00')
        GROUP BY dim1
    ) base
FULL OUTER JOIN
(
    SELECT
        dim1,
        COUNT(request) AS req_count
    FROM events
    WHERE time >= toDateTime('2024-11-10 00:00:00') 
      AND time < toDateTime('2024-11-11 00:00:00')
    GROUP BY dim1
) AS comparison ON isNotDistinctFrom(base.dim1, comparison.dim1)
ORDER BY req_count DESC
LIMIT 8

注：原始查询存在字段别名重复问题，已修正为合理别名，避免语法错误。

优化尝试的问题查询（反而更慢） #

为了让对比子查询仅筛选base查询中存在的dim1值，改写后的查询扫描行数达到原表的1.5倍，速度反而下降：

WITH base AS
    (
        SELECT
            dim1,
            COUNT(request) AS req_count
        FROM events
        WHERE time >= toDateTime('2024-11-11 00:00:00') 
          AND time < toDateTime('2024-11-11 23:00:00')
        GROUP BY dim1
        ORDER BY req_count DESC
        LIMIT 8
    )
SELECT
    COALESCE(base.dim1, comparison.dim1) AS dim1,
    base.req_count AS req_count,
    comparison.req_count AS req_count_prev,
    base.req_count - comparison.req_count AS req_count_delta
FROM base
LEFT JOIN
(
    SELECT
        dim1,
        COUNT(request) AS req_count
    FROM events
    WHERE (dim1 IS NULL OR dim1 IN (SELECT dim1 FROM base))
      AND time >= toDateTime('2024-11-10 00:00:00') 
      AND time < toDateTime('2024-11-11 00:00:00')
    GROUP BY dim1
) AS comparison ON isNotDistinctFrom(base.dim1, comparison.dim1)
ORDER BY req_count DESC
LIMIT 8

注：修正了原查询中括号优先级错误和字段别名重复问题。

改写查询变慢的原因 #

1. 逻辑条件优先级错误：原改写查询的WHERE子句未加正确括号，AND优先级高于OR，导致实际逻辑为dim1 IS NULL OR (dim1 IN (...) AND time ...)，会扫描所有分区中dim1 IS NULL的数据，而非仅目标日期范围，大幅增加扫描行数。
2. 执行计划未有效利用分区：虽然base是仅8行的小结果集，但ClickHouse可能未将IN (SELECT ... FROM base)优化为常量列表过滤，加上dim1 IS NULL的条件，引擎无法仅通过time分区裁剪数据，被迫扫描更多内容。
3. JOIN的额外开销：改写后的查询看似缩小了对比范围，但实际因过滤逻辑失效，对比子查询扫描数据量远超预期，加上JOIN的计算开销，整体性能下降。

优化方案 #

方案1：修正过滤逻辑并优化子查询 #

先修正WHERE条件的括号，再将小结果集转为数组，帮助ClickHouse生成更优执行计划：

WITH base AS
    (
        SELECT
            dim1,
            COUNT(request) AS req_count
        FROM events
        WHERE time >= toDateTime('2024-11-11 00:00:00') 
          AND time < toDateTime('2024-11-11 23:00:00')
        GROUP BY dim1
        ORDER BY req_count DESC
        LIMIT 8
    ),
    base_dims AS (SELECT groupArray(dim1) AS dims FROM base)
SELECT
    base.dim1,
    base.req_count,
    comparison.req_count AS req_count_prev,
    base.req_count - comparison.req_count AS req_count_delta
FROM base
LEFT JOIN
(
    SELECT
        dim1,
        COUNT(request) AS req_count
    FROM events
    WHERE dim1 IN (SELECT dims FROM base_dims)
      AND time >= toDateTime('2024-11-10 00:00:00') 
      AND time < toDateTime('2024-11-11 00:00:00')
    GROUP BY dim1
) AS comparison ON isNotDistinctFrom(base.dim1, comparison.dim1)
ORDER BY base.req_count DESC

• 使用groupArray将base的dim1转为数组，让引擎更容易优化为常量过滤条件。
• 若业务不需要对比dim1 IS NULL的情况，可直接去掉该条件，进一步缩小扫描范围。

方案2：用条件聚合替代JOIN（最优） #

避免JOIN操作，在单查询中通过条件函数直接计算两天的统计值，仅需扫描一次目标分区：

SELECT
    dim1,
    SUM(CASE WHEN time >= toDateTime('2024-11-11 00:00:00') AND time < toDateTime('2024-11-11 23:00:00') THEN 1 ELSE 0 END) AS req_count,
    SUM(CASE WHEN time >= toDateTime('2024-11-10 00:00:00') AND time < toDateTime('2024-11-11 00:00:00') THEN 1 ELSE 0 END) AS req_count_prev,
    SUM(CASE WHEN time >= toDateTime('2024-11-11 00:00:00') AND time < toDateTime('2024-11-11 23:00:00') THEN 1 ELSE 0 END) - 
    SUM(CASE WHEN time >= toDateTime('2024-11-10 00:00:00') AND time < toDateTime('2024-11-11 00:00:00') THEN 1 ELSE 0 END) AS req_count_delta
FROM events
WHERE time >= toDateTime('2024-11-10 00:00:00') 
  AND time < toDateTime('2024-11-11 23:00:00')
GROUP BY dim1
ORDER BY req_count DESC
LIMIT 8

ClickHouse的列式存储和分区裁剪会高效处理这类条件聚合，扫描行数仅为目标分区的总数据量，性能远高于JOIN写法。

方案3：创建物化视图预聚合 #

如果这类对比查询是高频操作，可创建按日和dim1预聚合的物化视图，提前完成计算：

CREATE MATERIALIZED VIEW events_agg
ENGINE = AggregatingMergeTree()
PARTITION BY toStartOfDay(time)
ORDER BY (toStartOfDay(time), dim1)
AS
SELECT
    toStartOfDay(time) AS day,
    dim1,
    COUNT(request) AS req_count
FROM events
GROUP BY day, dim1

后续查询直接基于物化视图，仅需读取聚合后的少量数据：

SELECT
    COALESCE(base.dim1, comparison.dim1) AS dim1,
    base.req_count AS req_count,
    comparison.req_count AS req_count_prev,
    base.req_count - comparison.req_count AS req_count_delta
FROM (
        SELECT dim1, req_count
        FROM events_agg
        WHERE day = toDate('2024-11-11')
    ) base
FULL OUTER JOIN
(
    SELECT dim1, req_count
    FROM events_agg
    WHERE day = toDate('2024-11-10')
) AS comparison ON isNotDistinctFrom(base.dim1, comparison.dim1)
ORDER BY req_count DESC
LIMIT 8

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