面试题：MySQL复杂查询场景下避免全表扫描的优化策略

SQL查询语句：

-- 创建索引
CREATE INDEX idx_employees_department_id_salary ON employees(department_id, salary);
CREATE INDEX idx_projects_employee_id ON projects(employee_id);

-- 查询语句
WITH EmployeeProject AS (
    SELECT e.department_id, e.salary
    FROM employees e
    JOIN projects p ON e.employee_id = p.employee_id
),
DepartmentAvgSalary AS (
    SELECT department_id, AVG(salary) AS dept_avg_salary
    FROM employees
    GROUP BY department_id
),
EmployeeProjectAvgSalary AS (
    SELECT department_id, AVG(salary) AS project_avg_salary
    FROM EmployeeProject
    GROUP BY department_id
)
SELECT d.department_name
FROM departments d
JOIN DepartmentAvgSalary das ON d.department_id = das.department_id
JOIN EmployeeProjectAvgSalary epas ON d.department_id = epas.department_id
WHERE epas.project_avg_salary > das.dept_avg_salary;

优化思路：
- 索引创建：
  - 在employees表的department_id和salary列上创建复合索引idx_employees_department_id_salary。这是因为在查询中需要根据department_id进行分组以及对salary进行计算，复合索引可以加速这一过程。MySQL可以利用这个索引快速定位到相关部门的员工数据，并按照索引顺序读取salary列，提高计算平均工资时的效率。
  - 在projects表的employee_id列上创建索引idx_projects_employee_id。因为需要通过employee_id将employees表和projects表进行关联，这个索引能加快连接操作，避免全表扫描projects表来查找匹配的员工。
- 查询结构优化：
  - 使用公共表达式（CTE）来分解复杂查询，使逻辑更加清晰。EmployeeProject CTE用于获取参与项目的员工及其所在部门和工资。DepartmentAvgSalary CTE计算每个部门的平均工资。EmployeeProjectAvgSalary CTE计算每个部门中参与项目的员工的平均工资。
  - 最后通过连接这几个CTE和departments表，筛选出满足条件（参与项目的员工平均工资高于本部门平均工资）的部门名称。
利用MySQL执行计划辅助优化：
- 使用EXPLAIN关键字查看SQL查询的执行计划，例如：EXPLAIN WITH EmployeeProject AS ( ... ) ...。
- 查看id列，确认CTE和主查询之间的执行顺序是否合理。如果id值相同，MySQL会按照书写顺序执行；如果不同，值大的先执行。
- 关注select_type列，确保每个CTE和主查询的类型正确，例如DERIVED表示CTE，SIMPLE表示简单的SELECT查询。
- 查看table列，确认每个操作涉及的表。
- 重点关注type列，理想情况下，连接操作的类型应该是index或range，而不是ALL（全表扫描）。如果是ALL，说明索引没有被正确使用，需要检查索引是否创建正确或者查询语句是否可以进一步优化。
- 查看key列，确认是否使用了预期创建的索引。如果没有显示预期的索引，可能需要调整索引或者查询逻辑。
- 观察rows列，这表示MySQL估计要扫描的行数。尽量减少这个值可以提高查询效率。通过合理的索引和查询优化，可以使MySQL更准确地估算并减少扫描行数。

星途面试题库

面试题：MySQL复杂查询场景下避免全表扫描的优化策略

知识考点

面试题答案