多表关联场景下查询Top N最高薪员工的SQL性能优化问题

• 自连接查询逻辑冗余低效：用左自连接找每组最大值/最新值的写法，时间复杂度为O(n²)，在百万级的salaries、titles表上会产生大量的关联计算开销，完全没有必要。
• 索引与查询逻辑不匹配：
  • 你创建的salary_emp_no_index索引顺序是(salary, emp_no)，但自连接是先按emp_no关联再比较薪资，该索引无法被命中；同时你最终要按薪资降序取前10，索引顺序也没有利用排序优化。
  • titles表的titles_emp_title索引没有包含判断最新职位的to_date字段，查询时需要回表扫描该字段，额外增加了IO开销。
• 全量计算后取数浪费算力：现有逻辑需要先计算所有员工的最高薪资和最新职位，再排序取前10，99%以上的计算资源都浪费在了不需要的员工数据上。

1. 索引调整 #

先删除无效索引，再创建适配查询逻辑的覆盖索引：

-- 删除无效索引
drop index salary_emp_no_index on salaries;
drop index titles_emp_title on titles;
-- 薪资表覆盖索引：支持按员工分组取最高薪资，也支持按薪资排序直接取前10
create index idx_salaries_emp_salary on salaries (emp_no, salary desc, to_date);
create index idx_salaries_salary_emp on salaries (salary desc, emp_no, to_date);
-- 职位表覆盖索引：支持按员工分组取最新职位，无需回表
create index idx_titles_emp_date_title on titles (emp_no, to_date desc, title);

注：你创建的employees表索引emp_first_last_name_index是覆盖索引，查询时无需回表，可以保留。

2. SQL逻辑改写 #

方案A：适配employees数据集特性（最高效） #

该测试数据集的设计规则为：to_date = '9999-01-01'代表当前有效的薪资和职位记录，直接过滤即可，无需额外计算：

select e.emp_no, e.first_name, e.last_name, e.gender, s.salary, t.title 
from employees e
inner join salaries s on e.emp_no = s.emp_no and s.to_date = '9999-01-01'
inner join titles t on e.emp_no = t.emp_no and t.to_date = '9999-01-01'
order by s.salary desc
limit 10;

配合idx_salaries_salary_emp索引，数据库可以直接按薪资降序的索引顺序取前10条记录，无需全表扫描和排序，执行效率可以达到毫秒级。

方案B：通用场景适配（兼容无to_date标识的业务场景） #

如果需要通用的取每个员工最高薪资、最新职位的逻辑，使用窗口函数替代自连接，时间复杂度为O(n)，性能提升非常明显（MySQL 8.0+支持）：

select e.emp_no, e.first_name, e.last_name, e.gender, s.salary, t.title 
from employees e
inner join (
    select emp_no, salary,
           row_number() over(partition by emp_no order by salary desc) as rn
    from salaries
) s on e.emp_no = s.emp_no and s.rn = 1
inner join (
    select emp_no, title,
           row_number() over(partition by emp_no order by to_date desc) as rn
    from titles
) t on e.emp_no = t.emp_no and t.rn = 1
order by s.salary desc
limit 10;

如果存在同员工多笔相同最高薪资的情况，可将row_number替换为rank或dense_rank，灵活控制返回规则。

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