面试题：MySQL高级查询性能优化：复杂查询的执行计划分析

SQL查询：

WITH EmployeeAvgSalary AS (
    SELECT
        e.department_id,
        AVG(e.salary) AS avg_salary
    FROM
        employees e
    GROUP BY
        e.department_id
),
ProjectEmployeeAvgSalary AS (
    SELECT
        e.department_id,
        AVG(e.salary) AS project_avg_salary
    FROM
        employees e
    JOIN
        projects p ON e.employee_id = p.employee_id
    GROUP BY
        e.department_id
)
SELECT
    d.department_name,
    ea.avg_salary
FROM
    departments d
JOIN
    EmployeeAvgSalary ea ON d.department_id = ea.department_id
JOIN
    ProjectEmployeeAvgSalary pea ON d.department_id = pea.department_id
WHERE
    ea.avg_salary > pea.project_avg_salary;

通过分析执行计划来优化该复杂查询：
- 获取执行计划：在不同数据库中获取执行计划的方式略有不同。例如在MySQL中，可以使用EXPLAIN关键字，在Oracle中使用EXPLAIN PLAN FOR语句，然后用SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);查看。
- 分析执行计划：
  - 索引使用：
    - 检查employees表的department_id、salary和employee_id列，departments表的department_id列，projects表的employee_id列是否有索引。如果没有，适当添加索引可以加速连接操作。例如，在employees表的department_id列上添加索引可以加快按部门分组计算平均工资的速度。
  - 连接顺序：
    - 查看执行计划中表的连接顺序。优化器会根据统计信息选择它认为最优的连接顺序，但有时可能并非最佳。例如，如果departments表数据量较小，将其作为驱动表进行连接可能会提高效率。
  - 子查询优化：
    - 对于EmployeeAvgSalary和ProjectEmployeeAvgSalary这两个CTE（公共表表达式），确保其计算过程高效。如果子查询中有复杂的计算，可以考虑是否可以将部分计算提前到外层查询，减少重复计算。
  - 统计信息更新：
    - 数据库优化器依赖统计信息来生成执行计划。如果表数据发生了较大变化，要及时更新统计信息，确保优化器能生成更合理的执行计划。例如在MySQL中可以使用ANALYZE TABLE语句更新表的统计信息。

星途面试题库

面试题：MySQL高级查询性能优化：复杂查询的执行计划分析

知识考点

面试题答案