如何高效查询MySQL中指定部门及其所有子部门下的所有员工？

要高效查询MySQL中指定部门及其所有子部门下的所有员工，通常需要处理部门之间的层级关系。假设你有一个部门表 departments 和一个员工表 employees，其中 departments 表包含部门的层级关系，employees 表包含员工信息。

1. 表结构假设

• departments 表结构：

  CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT
  );
  

  • id: 部门ID
  • name: 部门名称
  • parent_id: 上级部门ID（根部门的 parent_id 为 NULL）

• employees 表结构：

  CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
  );
  

  • id: 员工ID
  • name: 员工姓名
  • department_id: 所属部门ID

2. 递归查询所有子部门

MySQL 8.0 及以上版本支持递归查询（CTE，Common Table Expressions），可以使用递归查询来获取指定部门及其所有子部门。

假设你要查询部门ID为 1 的部门及其所有子部门的员工，可以使用以下SQL：

WITH RECURSIVE sub_departments AS (
    SELECT id, name
    FROM departments
    WHERE id = 1  -- 指定部门ID
    UNION ALL
    SELECT d.id, d.name
    FROM departments d
    INNER JOIN sub_departments sd ON d.parent_id = sd.id
)
SELECT e.*
FROM employees e
JOIN sub_departments sd ON e.department_id = sd.id;

3. 解释

• WITH RECURSIVE sub_departments AS (...)：定义一个递归CTE，命名为 sub_departments。
• 初始查询部分 SELECT id, name FROM departments WHERE id = 1：获取指定部门的初始记录。
• UNION ALL：递归部分，获取所有子部门。
• INNER JOIN sub_departments sd ON d.parent_id = sd.id：递归地获取子部门。
• 最后，将 employees 表与 sub_departments 表连接，获取所有符合条件的员工。

4. 性能优化

• 索引：确保 departments 表的 parent_id 列和 employees 表的 department_id 列上有索引，以加快查询速度。

  CREATE INDEX idx_departments_parent_id ON departments(parent_id);
  CREATE INDEX idx_employees_department_id ON employees(department_id);
  

• 层级深度限制：如果部门的层级深度较大，递归查询可能会导致性能问题。可以考虑在应用层处理递归逻辑，或者使用其他数据结构（如闭包表）来优化查询。

5. 其他版本MySQL的解决方案

如果你使用的是MySQL 5.x 或更低版本，不支持递归CTE，可以考虑以下方法： - 应用层递归：在应用层递归查询子部门。 - 闭包表：使用闭包表（Closure Table）来存储部门之间的层级关系，这样可以避免递归查询。

6. 闭包表示例

闭包表的结构如下：

CREATE TABLE department_closure (
    ancestor INT,
    descendant INT,
    depth INT
);

• ancestor: 祖先部门ID
• descendant: 后代部门ID
• depth: 层级深度

查询时，可以直接通过闭包表获取所有子部门：

SELECT e.*
FROM employees e
JOIN department_closure dc ON e.department_id = dc.descendant
WHERE dc.ancestor = 1;  -- 指定部门ID

总结

• 对于MySQL 8.0及以上版本，推荐使用递归CTE来查询指定部门及其所有子部门的员工。
• 对于MySQL 5.x及以下版本，可以考虑使用闭包表或在应用层处理递归逻辑。
• 确保相关列上有索引以提高查询性能。