数据库查询优化中的相关子查询（Correlated Subquery）优化技术

一、知识点描述
相关子查询是指子查询的执行依赖于外层查询的每一行数据的查询。与普通子查询不同，相关子查询需要对外层查询的每一行都执行一次子查询，这种"嵌套循环"式的执行方式往往导致性能低下。优化相关子查询是数据库查询优化中的重要课题。

二、问题演示
我们先看一个典型的相关子查询示例：

SELECT e1.employee_name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id  -- 相关性条件
);

这个查询要找出每个部门中工资高于该部门平均工资的员工。

三、执行过程分析

1. 原始执行方式（性能较差）：

   • 遍历外层employees表的每一行e1
   • 对于每一行e1，执行子查询：计算e1所在部门的平均工资
   • 比较e1的工资是否大于该平均值
   • 这种N+1查询模式在数据量大时性能极差

2. 性能瓶颈：

   • 子查询被执行N次（N为员工数量）
   • 每次子查询都需要全表扫描或索引查找
   • 大量的上下文切换和重复计算

四、优化技术详解

技术1：使用派生表（Derived Table）重写

SELECT e.employee_name, e.salary
FROM employees e
INNER JOIN (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

优化原理：

• 将相关子查询转换为非相关子查询
• 先计算所有部门的平均工资（只执行一次）
• 通过JOIN操作进行关联，避免N次子查询执行

执行步骤：

1. 先执行派生表查询：计算每个部门的平均工资
2. 将结果物化为临时表dept_avg
3. 执行主查询：employees表与dept_avg表进行JOIN
4. 应用过滤条件

技术2：使用窗口函数（现代数据库支持）

SELECT employee_name, salary
FROM (
    SELECT employee_name, salary, department_id,
           AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
    FROM employees
) emp_with_avg
WHERE salary > dept_avg_salary;

优化原理：

• 使用窗口函数一次性计算所有行的部门平均工资
• 避免多次查询和JOIN操作
• 通常比派生表方式更高效

技术3：使用CTE（公共表表达式）

WITH dept_salaries AS (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_name, e.salary
FROM employees e
INNER JOIN dept_salaries ds ON e.department_id = ds.department_id
WHERE e.salary > ds.avg_salary;

五、优化效果对比

性能提升因素：

1. 查询次数减少：从O(N)次减少到O(1)次
2. 避免重复计算：每个部门的平均工资只计算一次
3. 更好的执行计划：优化器可以选用更高效的JOIN算法
4. 索引利用：派生表方式可以更好地利用索引

执行计划差异：

• 原始方式：Nested Loop × Filter
• 优化后：Hash Join/Hash Aggregate 或 Window Function

六、适用场景与限制

推荐使用优化的情况：

• 外层查询结果集较大时
• 子查询本身比较复杂时
• 需要处理大量数据的报表查询

注意事项：

1. 确保GROUP BY字段有合适的索引
2. 派生表可能产生临时表，注意内存使用
3. 窗口函数需要数据库版本支持
4. 不同的优化技术在不同数据分布下效果可能不同

七、实践建议

1. 优先尝试窗口函数方式（如果数据库支持）
2. 对于复杂子查询，使用CTE提高可读性
3. 通过EXPLAIN分析执行计划选择最优方案
4. 在相关条件上建立合适索引

这种优化技术可以将相关子查询的性能提升数个数量级，特别是在大数据量场景下效果尤为显著。