为什么HAVING中的子查询会导致性能瓶颈
having条件中包含子查询时,数据库通常会对每一行结果都执行一次子查询,导致全表扫描和重复计算。这种写法在数据量大时效率极低,因为子查询无法利用索引进行快速查找,而是退化为嵌套循环。

SELECT department_id, COUNT(*) as emp_count FROM employees WHERE salary > 5000 GROUP BY department_id HAVING COUNT(*) > (SELECT AVG(emp_count) FROM (SELECT department_id, COUNT(*) as emp_count FROM employees GROUP BY department_id) AS dept_counts);
上述代码中,子查询在HAVING子句中反复执行,每次都要重新计算所有部门的平均人数,造成严重的性能浪费。
如何通过JOIN改写提升查询速度
将子查询提取到FROM子句或CTE中,先计算出聚合值,再通过JOIN连接主查询,可以显著减少重复计算。这种方法让数据库能够一次性完成子查询的计算,并利用索引加速连接操作。

WITH dept_avg AS (
SELECT department_id, COUNT(*) as emp_count
FROM employees
WHERE salary > 5000
GROUP BY department_id
)
SELECT e.department_id, COUNT(*) as emp_count
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > 5000
GROUP BY e.department_id
HAVING COUNT(*) > d.emp_count;
通过CTE `dept_avg` 预先计算每个部门的人数,然后在主查询中直接引用这个预计算的结果,避免了在HAVING中重复执行子查询。这种改写方式利用了临时表的缓存机制,大幅提升了查询效率。
不同输入条件下改写效果的具体表现
当数据量较小(如少于1万行)时,两种写法的性能差异不明显,因为数据库优化器可能自动识别并优化子查询。但当数据量超过10万行时,JOIN改写方式的执行时间通常能缩短70%以上,因为它减少了子查询的重复调用次数。










