使用CTE优化查询

今天我们要深入（有点吓人但很有趣）的CTE（Common Table Expressions）优化世界。如果你已经会写CTE（我们在前面讲过啦），现在正好来聊聊它们的内部细节、隐藏的坑，还有怎么把效率榨到极致。

乍一看，CTE好像很完美：结构清晰，写起来简单，还能把代码拆成逻辑块。但其实有个小（也可能不小）细节。PostgreSQL对CTE有自己的一套处理策略，这会影响性能。

当PostgreSQL遇到WITH时，通常会把CTE的结果物化。意思是，CTE返回的数据会先算出来，然后存成临时表，主查询再用这个表。这样方便复用，但有时候会出问题，比如：

1. CTE里的数据量超大，但主查询只用到一小部分。
2. CTE被调用太多次，导致开销变大。
3. 我们写了一堆复杂但其实没必要的CTE。

认识物化

物化就是PostgreSQL把CTE结果存到内存或者磁盘（看数据量大小）。这样数据只会被提取一次，但如果你只在一个地方用CTE，物化其实没啥必要。比如：

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

在这个例子里，PostgreSQL会先生成一个完整CTE结果的临时表（grade > 60），然后再过滤grade > 90的行。这样多了个没必要的中间步骤，性能就受影响了。

怎么避免多余的物化？

从PostgreSQL 12开始，可以在不需要的时候避免CTE物化。用MATERIALIZED（默认）或者NOT MATERIALIZED关键字就行。例子：

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

这里我们告诉PostgreSQL不要物化large_set，而是把查询直接嵌进主表达式。这样不会生成中间表，效率更高。

什么时候物化有用？

别以为物化就一定不好！如果CTE在查询里被多次用到，或者需要独立计算，物化其实很有用。比如：

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

这里物化能避免多次重复计算grade > 60的过滤。

用索引优化CTE查询

想让CTE更快，最好在底层表上建索引。比如：

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

给grade列建索引后，PostgreSQL能更快查出grade > 90的行。大表时尤其重要。

把大CTE拆成小块

如果CTE返回很多数据，后面还要过滤或聚合，最好分成几个步骤。别写一个超大的CTE，拆成小的更好：

不推荐（大CTE）：

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

推荐（分步骤）：

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

这样PostgreSQL能更好地优化查询执行。

实战例子：结构分析和优化

来看个复杂点的例子。有学生、课程和成绩表。我们想找出平均分高的学生，并列出他们的课程：

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

如果给grades和enrollments表加索引，过滤和关联会更快。

监控：性能分析

想知道查询效率咋样，用EXPLAIN或者EXPLAIN ANALYZE。比如：

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

这个查询会显示每一步花了多少时间，帮你找出还能怎么提速。

关于EXPLAIN ANALYZE的细节，我们下次再详细聊 :P

CTE优化常见坑

1. 忘了加索引。如果CTE里过滤数据，但底层表没索引，性能会很差。
2. CTE太大太复杂。一个查询做太多事，可能导致物化超大数据量。
3. 滥用NOT MATERIALIZED。有时候还是得物化，避免重复执行CTE。
4. 不做监控。不用EXPLAIN分析，可能根本没发现查询慢。

现在你已经可以用CTE优化查询，避开坑点提升性能啦！记住CTE只是工具，不是万能药。用得巧，它会成为你在PostgreSQL里的好帮手。

2

任务

SQL SELF, 第 28 级, 课程 2

已锁定

多个CTE组合实现复杂查询

多个CTE组合实现复杂查询

打开