ClickHouse 中的 CTE：宏而非优化

ClickHouse 中的 CTE：宏而非优化

从 OLTP 系统转向 ClickHouse 的开发者，往往会延续使用 WITH 公用表表达式（CTE）的习惯。ClickHouse 支持这种语法，但它仅作为解析器宏：CTE 会被内联，每次引用都会执行。这会导致子查询重复执行、负载增加以及结果不可预测。

来看一个典型查询，突出问题所在：

WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)

你可能期望它统计所有 100 万行，但实际结果约 60 万，且每次运行都不同。罪魁祸首？generateRandom 运行两次，生成不同数据集。EXPLAIN 显示两次独立源读取。

实际上，解析器会展开 CTE 为：

SELECT
    count()
FROM
    (
        SELECT
            num
        FROM
            generateRandom('num UInt64', NULL)
        LIMIT 1000000
    )
WHERE
    num IN (
        SELECT
            num
        FROM
            generateRandom('num UInt64', NULL)
        LIMIT 1000000
    )

CTE 的可靠性能替代方案

首选方案是 CREATE TEMPORARY TABLE。这些内存表仅物化一次数据：

CREATE TEMPORARY TABLE cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
;

SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)
;

现在结果稳定为 100 万。临时表会话期有效、无磁盘存储，在复杂 ETL 流程中大放异彩。

其他选项：

• 带物化的子查询：使用 ARRAY JOIN 或 MATERIALIZED 视图处理重复计算。
• 实验性 CTE 物化：较新版本（截至 2026 年 4 月）支持 WITH 的 MATERIALIZED，但实验状态需生产环境彻底测试。

| 方案 | 物化 | 性能 | 稳定性 |

|---------|-----------------|-------------|-----------|

| CTE WITH | 无 | N 倍增加 | 低 |

| TEMP TABLE | 有 | O(1) | 高 |

| 子查询 | 部分 | 中等 | 中等 |

避开陷阱的查询优化

对于中高级开发者，效率关键在于掌握执行计划。生产前始终运行 EXPLAIN PIPELINE：

• 检查 Read 操作次数。
• 避免 WHERE/JOIN 中的非物化子查询。
• 大数据集搭配 GROUP BY 和聚合函数。

以下是使用临时表和索引的优化版：

• 按 num 排序创建表。
• CTE 中加 ORDER BY num 加速 IN。
• 集群上用 DISTRIBUTED 扩展。

生产环境中，热查询避开 CTE：仅适合无重复的一次性替换。

关键要点

• CTE 即宏：每次引用内联、多执行——始终查 EXPLAIN。
• 临时表：可靠重用物化方式。
• 实验特性：谨慎测试 MATERIALIZED WITH，监控负载。
• 性能：重复 CTE 导致时间资源 2 倍以上差异。
• 最佳实践：ETL 用显式表，简单场景用子查询。

— Editorial Team