面试题：MySQL复杂关联查询的执行计划分析与优化

Question

已知有三张表 `employees`（员工表，包含 `employee_id`，`department_id` 等字段），`departments`（部门表，包含 `department_id`，`department_name` 等字段），`projects`（项目表，包含 `project_id`，`employee_id` 等字段）。现在要查询出每个部门参与项目的员工总数，并且按部门名称排序。给出实现该查询的 SQL 语句，并使用 `EXPLAIN` 分析该查询的执行计划，根据执行计划说明可能存在的性能问题及优化措施。

Answer 1

SQL 语句

SELECT d.department_name, COUNT(DISTINCT e.employee_id) AS employee_count
FROM departments d
         LEFT JOIN employees e ON d.department_id = e.department_id
         LEFT JOIN projects p ON e.employee_id = p.employee_id
GROUP BY d.department_name
ORDER BY d.department_name;

使用 EXPLAIN 分析执行计划

EXPLAIN SELECT d.department_name, COUNT(DISTINCT e.employee_id) AS employee_count
FROM departments d
         LEFT JOIN employees e ON d.department_id = e.department_id
         LEFT JOIN projects p ON e.employee_id = p.employee_id
GROUP BY d.department_name
ORDER BY d.department_name;

可能存在的性能问题

1. 笛卡尔积问题：如果表之间没有合适的索引，在进行 JOIN 操作时可能会产生笛卡尔积，导致数据量剧增，查询性能下降。例如，如果 employees 表的 department_id 字段和 departments 表的 department_id 字段没有索引，projects 表的 employee_id 字段和 employees 表的 employee_id 字段没有索引，JOIN 操作可能会全表扫描，性能较差。
2. COUNT(DISTINCT) 性能问题：COUNT(DISTINCT) 操作通常比 COUNT(*) 性能差，因为数据库需要对每个值进行比较和去重，这会消耗更多的资源，特别是在数据量较大的情况下。
3. 排序性能问题：ORDER BY 操作如果没有利用到索引，会进行全表扫描后再排序，性能较低。

优化措施

1. 添加索引：
   • 在 employees 表的 department_id 字段上添加索引：

     CREATE INDEX idx_employees_department_id ON employees(department_id);
     

   • 在 projects 表的 employee_id 字段上添加索引：

     CREATE INDEX idx_projects_employee_id ON projects(employee_id);
     

2. 优化 COUNT(DISTINCT)：如果业务允许，可以通过先分组再计数的方式优化。例如，如果 employee_id 在 projects 表中是唯一的，可以先在 projects 表按 employee_id 分组，再与其他表 JOIN 并计数，这样可以避免直接使用 COUNT(DISTINCT) 在大表上的性能问题。
3. 利用索引排序：确保 departments 表的 department_name 字段上有索引，这样 ORDER BY 操作可以利用索引直接进行排序，而不需要全表扫描后再排序。

   CREATE INDEX idx_departments_department_name ON departments(department_name);