SQL多表JOIN查询优化：如何降低薪资查询的执行成本？

我关联了hr.employees、hr.departments、hr.locations、hr.countries四张表，表结构如下：

hr.employees表结构 #

hr.departments表结构 #

hr.locations表结构 #

hr.countries表结构 #

我的查询需求是：展示薪资大于等于所在国家平均薪资的员工信息，需包含员工姓名、所属国家、薪资、所在国家平均薪资。

我编写的SQL能得到正确结果，但执行成本为27，而教授的查询成本仅为16，求优化方案：

SELECT 
e.first_name || ' ' || e.last_name AS name,
TRIM(CAST(con.country_name AS CHAR(25))) AS country,
e.salary AS salary,
(SELECT ROUND(AVG(e1.salary)) FROM hr.employees e1
    JOIN hr.departments dep1 ON e1.department_id = dep1.department_id
    JOIN hr.locations loc1 ON dep1.location_id = loc1.location_id 
    WHERE country_id = loc.country_id
)AS avg_sal_country
FROM hr.employees e
JOIN hr.departments dep ON e.department_id = dep.department_id
JOIN hr.locations loc ON dep.location_id = loc.location_id
JOIN HR.countries con ON loc.country_id = con.country_id
WHERE (salary >= (SELECT AVG(e1.salary) FROM hr.employees e1
    JOIN hr.departments dep1 ON e1.department_id = dep1.department_id
    JOIN hr.locations loc1 ON dep1.location_id = loc1.location_id 
    WHERE country_id = loc.country_id) );

查询结果示例：

你的原SQL核心问题是重复计算：SELECT和WHERE子句中各执行了一次完全相同的子查询，用来计算对应国家的平均薪资，相当于每条员工数据都要重复跑两次关联计算，直接拉高了执行成本。

优化思路是一次性预计算所有国家的平均薪资，再将员工信息与预计算结果关联，同时过滤薪资达标的数据。

优化后的SQL如下：

WITH country_avg_sal AS (
    SELECT 
        loc.country_id,
        ROUND(AVG(e.salary)) AS avg_sal_country
    FROM hr.employees e
    JOIN hr.departments dep ON e.department_id = dep.department_id
    JOIN hr.locations loc ON dep.location_id = loc.location_id
    GROUP BY loc.country_id
)
SELECT 
    e.first_name || ' ' || e.last_name AS name,
    TRIM(CAST(con.country_name AS CHAR(25))) AS country,
    e.salary AS salary,
    cas.avg_sal_country
FROM hr.employees e
JOIN hr.departments dep ON e.department_id = dep.department_id
JOIN hr.locations loc ON dep.location_id = loc.location_id
JOIN hr.countries con ON loc.country_id = con.country_id
JOIN country_avg_sal cas ON loc.country_id = cas.country_id
WHERE e.salary >= cas.avg_sal_country;

优化点说明 #

1. CTE预计算：用WITH子句一次性计算所有国家的平均薪资，避免重复执行子查询，减少了多次JOIN的开销。
2. 关联过滤：将预计算的平均薪资结果与员工主查询关联，直接在WHERE子句中对比薪资，逻辑更清晰，执行效率更高。
3. 消除重复逻辑：原SQL中两次相同的子查询被合并为一次计算，大幅降低了执行成本，应该能接近教授的16成本值。

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