面试题：MySQL复杂查询语句中如何优化多表连接结构

Question

假设有三张表，`employees`表存储员工信息（包含`employee_id`、`department_id`等字段），`departments`表存储部门信息（包含`department_id`、`department_name`等字段），`salaries`表存储员工薪资信息（包含`employee_id`、`salary`等字段）。现在要查询每个部门的平均薪资，并按平均薪资降序排列。请写出该复杂查询语句，并说明如何对其多表连接结构进行优化。

Answer 1

查询语句

SELECT 
    d.department_name,
    AVG(s.salary) AS average_salary
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id
JOIN 
    salaries s ON e.employee_id = s.employee_id
GROUP BY 
    d.department_name
ORDER BY 
    average_salary DESC;

多表连接结构优化

1. 索引优化：
   • 在employees表的department_id和employee_id字段上创建索引。可以使用以下语句创建：

     CREATE INDEX idx_employees_department_id ON employees(department_id);
     CREATE INDEX idx_employees_employee_id ON employees(employee_id);
     

   • 在departments表的department_id字段上创建索引：

     CREATE INDEX idx_departments_department_id ON departments(department_id);
     

   • 在salaries表的employee_id字段上创建索引：

     CREATE INDEX idx_salaries_employee_id ON salaries(employee_id);
     

   索引可以加快连接条件的匹配速度，从而提升查询性能。
2. 查询执行计划分析： 使用数据库提供的工具（如MySQL的EXPLAIN关键字）分析查询执行计划。例如：

   EXPLAIN SELECT 
       d.department_name,
       AVG(s.salary) AS average_salary
   FROM 
       employees e
   JOIN 
       departments d ON e.department_id = d.department_id
   JOIN 
       salaries s ON e.employee_id = s.employee_id
   GROUP BY 
       d.department_name
   ORDER BY 
       average_salary DESC;
   

   根据执行计划的结果，进一步调整查询结构或者索引策略。例如，如果发现某个表的扫描方式不合理，可以尝试调整连接顺序或者添加合适的索引。
3. 减少数据传输： 如果查询涉及到大数据量的表，可以考虑在连接之前对表进行筛选，减少参与连接的数据量。例如，如果employees表中有一个is_active字段表示员工是否在职，可以先筛选出在职员工：

   SELECT 
       d.department_name,
       AVG(s.salary) AS average_salary
   FROM 
       (SELECT * FROM employees WHERE is_active = true) e
   JOIN 
       departments d ON e.department_id = d.department_id
   JOIN 
       salaries s ON e.employee_id = s.employee_id
   GROUP BY 
       d.department_name
   ORDER BY 
       average_salary DESC;
   

   这样可以减少中间结果集的大小，提升查询性能。