面试题：MySQL子查询优化：简单场景

Question

假设有两个表，`employees`表包含员工信息（`employee_id`、`name`、`department_id`），`departments`表包含部门信息（`department_id`、`department_name`）。现在要查询每个部门中薪资最高的员工姓名。请写出使用子查询实现的SQL语句，并阐述如何对这个子查询进行优化。

Answer 1

1. 使用子查询实现的SQL语句：

SELECT
    e.name
FROM
    employees e
JOIN
    (
        SELECT
            department_id,
            MAX(salary) AS max_salary
        FROM
            employees
        GROUP BY
            department_id
    ) sub
ON
    e.department_id = sub.department_id AND e.salary = sub.max_salary;

2. 优化子查询的方法：
   • 添加索引：
     • 在employees表的department_id和salary列上添加联合索引。例如：CREATE INDEX idx_department_salary ON employees (department_id, salary); 这样在执行子查询中的 GROUP BY 和主查询中的 JOIN 操作时，可以大大提高查询效率，因为索引可以快速定位到相关数据。
   • 避免使用子查询嵌套过深：如果有更复杂的查询需求，尽量减少子查询的嵌套层数，因为多层嵌套会增加查询优化器解析和执行的难度，导致性能下降。在这种情况下，上述的单层次子查询结构相对简单，有利于优化。
   • 使用窗口函数替代子查询（这不属于严格的子查询优化，但提供一种性能更好的替代方案）：

WITH RankedSalaries AS (
    SELECT
        e.employee_id,
        e.name,
        e.salary,
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank
    FROM
        employees e
)
SELECT
    name
FROM
    RankedSalaries
WHERE
    salary_rank = 1;

窗口函数通常在处理这类分组排名问题时性能更好，因为它可以在一次扫描中完成计算，而不需要像子查询那样多次扫描表数据。