如何优化SQL中嵌套子查询的GROUP BY效率 通过派生表或CTE重构逻辑

SQL查询中，嵌套子查询与GROUP BY结合使用是较为常见的写法，但这类查询很容易出现性能问题，尤其是在数据量较大的场景下，执行时间会明显变长。导致这类性能问题的核心原因通常是嵌套子查询会先执行并返回全部结果集，外层再对结果集做GROUP BY聚合，中间会产生大量临时数据，增加数据库的IO和计算开销。

嵌套子查询GROUP BY的性能瓶颈

我们先看一个典型的低效查询示例，需求是统计每个部门中薪资高于部门平均薪资的员工数量：

SELECT 
    d.dept_name,
    COUNT(*) AS high_salary_count
FROM 
    employees e
JOIN 
    departments d ON e.dept_id = d.dept_id
WHERE 
    e.salary > (
        SELECT 
            AVG(e2.salary)
        FROM 
            employees e2
        WHERE 
            e2.dept_id = e.dept_id
        GROUP BY 
            e2.dept_id
    )
GROUP BY 
    d.dept_name;

这个查询中，子查询会对每个员工的部门都执行一次平均薪资计算，相当于对employees表做了多次扫描，外层再对结果做GROUP BY，当员工表数据量达到百万级时，执行时间会非常长。我们可以通过执行计划看到，这类查询通常会出现多次全表扫描和临时表创建的操作。

通过派生表重构优化

派生表的核心思路是先把子查询中的聚合逻辑提前计算出来，生成一个临时的派生表，再和主表做关联，避免重复扫描和计算。我们用派生表重构上面的查询：

SELECT 
    d.dept_name,
    COUNT(*) AS high_salary_count
FROM 
    employees e
JOIN 
    departments d ON e.dept_id = d.dept_id
JOIN 
    -- 派生表：提前计算每个部门的平均薪资
    (
        SELECT 
            dept_id,
            AVG(salary) AS avg_salary
        FROM 
            employees
        GROUP BY 
            dept_id
    ) dept_avg ON e.dept_id = dept_avg.dept_id
WHERE 
    e.salary > dept_avg.avg_salary
GROUP BY 
    d.dept_name;

重构后的查询中，派生表只会执行一次，扫描一次employees表计算出所有部门的平均薪资，之后再和员工表、部门表做关联，减少了大量的重复扫描操作。如果部门表数据量不大，还可以在dept_id字段上建立索引，进一步提升关联效率。

通过CTE重构优化

CTE也就是公用表表达式，和派生表的逻辑类似，但是可读性更好，还支持递归等高级特性。同样的需求用CTE重构的写法如下：

-- 定义CTE，计算部门平均薪资
WITH dept_avg_salary AS (
    SELECT 
        dept_id,
        AVG(salary) AS avg_salary
    FROM 
        employees
    GROUP BY 
        dept_id
)
SELECT 
    d.dept_name,
    COUNT(*) AS high_salary_count
FROM 
    employees e
JOIN 
    departments d ON e.dept_id = d.dept_id
JOIN 
    dept_avg_salary das ON e.dept_id = das.dept_id
WHERE 
    e.salary > das.avg_salary
GROUP BY 
    d.dept_name;

CTE的写法和派生表相比，逻辑拆分更清晰，尤其是当有多个类似的聚合逻辑需要复用时，CTE可以避免重复编写相同的子查询。大部分现代数据库对CTE都有优化，执行效率和派生表相当，部分场景下甚至会更优。

两种重构方式的对比

我们可以通过下面的表格对比两种重构方式的特点：

优化注意事项

除了重构查询逻辑，还可以配合以下操作进一步提升效率：

• 对关联字段和过滤字段建立合适的索引，比如employees表的dept_id、salary字段，departments表的dept_id字段。
• 避免在子查询或派生表中返回不必要的字段，只保留后续逻辑需要用到的列，减少临时数据的大小。
• 如果数据量极大，可以考虑先对原始数据做分区，再执行聚合和关联操作，减少单表扫描的范围。
• 定期分析查询执行计划，确认优化后的查询是否按照预期的方式执行，避免索引失效等问题。

通过以上两种方式重构嵌套子查询的GROUP BY逻辑，能够有效解决大部分相关性能问题，开发者可以根据实际的查询复杂度和可读性需求选择合适的重构方式。

SQL优化嵌套子查询GROUP_BY派生表CTE修改时间：2026-06-09 18:06:24