如何让SQL主查询仅基于CTE结果生成执行计划，规避大表查询性能问题

问题本质 #

绝大多数关系型数据库的优化器默认会将非递归CTE等价改写为嵌套子查询，和外层查询的关联逻辑、过滤条件合并执行，你原本想提前过滤超大表table1的逻辑会被优化器的谓词下推、查询改写逻辑打破，导致最终执行计划还是全量扫描table1后再关联。

通用方案（全数据库兼容） #

直接使用临时表替代CTE，预先将table1过滤后的结果写入临时表，再和table2关联，优化器100%不会合并逻辑，完全符合你提前过滤的需求：

-- 第一步：预先过滤超大表写入临时表
CREATE TEMPORARY TABLE temp_aux AS
SELECT col1, col4
FROM table1
WHERE col2 IS NULL AND col3 > 0;

-- 第二步：基于过滤后的临时表关联查询
SELECT * FROM temp_aux t1
WHERE EXISTS (
    SELECT NULL FROM table2 t2
    WHERE t2.col4 = t1.col4 AND t2.col5 > 100
);

各数据库专属Hint方案 #

如果你不想改临时表，也可以用对应数据库的专属Hint强制CTE物化、禁止展开：

PostgreSQL（12及以上版本） #

给CTE加MATERIALIZED关键字即可强制物化：

WITH aux AS MATERIALIZED (
    SELECT col1, col4
    FROM table1
    WHERE col2 IS NULL AND col3 > 0
)
SELECT * FROM aux t1
WHERE EXISTS (
    SELECT NULL FROM table2 t2
    WHERE t2.col4 = t1.col4 AND t2.col5 > 100
);

Oracle #

在CTE的查询中加/*+ MATERIALIZE */提示：

WITH aux AS (
    SELECT /*+ MATERIALIZE */ col1, col4
    FROM table1
    WHERE col2 IS NULL AND col3 > 0
)
SELECT * FROM aux t1
WHERE EXISTS (
    SELECT NULL FROM table2 t2
    WHERE t2.col4 = t1.col4 AND t2.col5 > 100
);

MySQL（8.0及以上版本） #

在外层查询加/*+ NO_MERGE(aux) */提示禁止CTE合并：

WITH aux AS (
    SELECT col1, col4
    FROM table1
    WHERE col2 IS NULL AND col3 > 0
)
SELECT /*+ NO_MERGE(aux) */ * FROM aux t1
WHERE EXISTS (
    SELECT NULL FROM table2 t2
    WHERE t2.col4 = t1.col4 AND t2.col5 > 100
);

SQL Server #

在CTE中加无意义的OFFSET 0 ROWS即可阻止优化器将CTE逻辑和外层合并：

WITH aux AS (
    SELECT col1, col4
    FROM table1
    WHERE col2 IS NULL AND col3 > 0
    OFFSET 0 ROWS -- 阻止谓词下推、查询改写
)
SELECT * FROM aux t1
WHERE EXISTS (
    SELECT NULL FROM table2 t2
    WHERE t2.col4 = t1.col4 AND t2.col5 > 100
);

额外性能优化建议 #

建议给table1建覆盖索引，进一步降低CTE过滤阶段的扫描成本，不需要回表就能拿到所有需要的字段：

CREATE INDEX idx_table1_filter ON table1 (col2, col3) INCLUDE (col1, col4);

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