部门数据权限控制方案 - 闭包表（Closure Table）

一、方案概述

闭包表是处理树形结构最高效的方案之一，通过空间换时间的策略，将所有祖先-后代关系存储在独立的关系表中，实现O(1)复杂度的树形查询。

核心优势

查询性能极高：无需递归或复杂计算，直接通过索引查询
灵活性强：支持获取任意节点的祖先、后代、兄弟节点
维护相对简单：只在节点增删时更新关系表
支持多种查询：层级查询、路径查询、子树移动等

性能对比

操作类型	闭包表	物化路径	邻接表
查询所有子孙	O(1)	O(n)	O(n)
查询所有祖先	O(1)	O(1)	O(n)
插入节点	O(k)	O(1)	O(1)
移动子树	O(k²)	O(n)	O(1)

注：k为祖先数量，n为子孙数量

二、数据库设计

2.1 表结构设计

sql

-- 部门主表
CREATE TABLE sys_dept (
    dept_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    parent_id BIGINT DEFAULT 0 COMMENT '直接父部门ID',
    dept_name VARCHAR(50) COMMENT '部门名称',
    order_num INT DEFAULT 0 COMMENT '显示顺序',
    status CHAR(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
    create_time DATETIME,
    INDEX idx_parent_id (parent_id)
) COMMENT='部门表';

-- 部门闭包表（核心）
CREATE TABLE sys_dept_closure (
    ancestor_id BIGINT NOT NULL COMMENT '祖先部门ID',
    descendant_id BIGINT NOT NULL COMMENT '后代部门ID',
    distance INT NOT NULL DEFAULT 0 COMMENT '层级距离(0表示自身)',
    PRIMARY KEY (ancestor_id, descendant_id),
    INDEX idx_descendant (descendant_id),
    INDEX idx_distance (distance),
    FOREIGN KEY (ancestor_id) REFERENCES sys_dept(dept_id) ON DELETE CASCADE,
    FOREIGN KEY (descendant_id) REFERENCES sys_dept(dept_id) ON DELETE CASCADE
) COMMENT='部门关系闭包表';

2.2 数据示例

部门结构：

总公司(1)
├─ 深圳分公司(100)
│   ├─ 研发部(200)
│   │   ├─ 前端组(300)
│   │   └─ 后端组(301)
│   └─ 市场部(201)
└─ 北京分公司(101)

闭包表数据：

ancestor_id	descendant_id	distance
1	1	0
1	100	1
1	200	2
1	300	3
1	301	3
100	100	0
100	200	1
100	300	2
200	200	0
200	300	1

三、核心实现

3.1 Service层实现

java

@Service
@Transactional(rollbackFor = Exception.class)
public class SysDeptServiceImpl implements ISysDeptService {
    
    @Autowired
    private SysDeptMapper deptMapper;
    
    @Autowired
    private SysDeptClosureMapper closureMapper;
    
    /**
     * 新增部门
     */
    @Override
    public int insertDept(SysDept dept) {
        // 1. 插入部门主表
        deptMapper.insert(dept);
        
        // 2. 插入闭包表 - 自身关系
        SysDeptClosure selfClosure = new SysDeptClosure();
        selfClosure.setAncestorId(dept.getDeptId());
        selfClosure.setDescendantId(dept.getDeptId());
        selfClosure.setDistance(0);
        closureMapper.insert(selfClosure);
        
        // 3. 插入闭包表 - 与所有祖先的关系
        if (dept.getParentId() != null && dept.getParentId() > 0) {
            closureMapper.insertAncestorRelations(dept.getDeptId(), dept.getParentId());
        }
        
        return 1;
    }
    
    /**
     * 删除部门（级联删除所有子部门）
     */
    @Override
    public int deleteDept(Long deptId) {
        // 检查是否有子部门
        if (hasChildDept(deptId)) {
            throw new ServiceException("存在下级部门，不允许删除");
        }
        
        // 删除部门（闭包表通过外键级联删除）
        return deptMapper.deleteById(deptId);
    }
    
    /**
     * 移动部门
     */
    @Override
    public int moveDept(Long deptId, Long newParentId) {
        // 1. 删除原有的祖先关系（保留自身和子孙关系）
        closureMapper.deleteAncestorRelations(deptId);
        
        // 2. 建立新的祖先关系
        closureMapper.insertNewAncestorRelations(deptId, newParentId);
        
        // 3. 更新部门主表
        SysDept dept = new SysDept();
        dept.setDeptId(deptId);
        dept.setParentId(newParentId);
        return deptMapper.updateById(dept);
    }
}

3.2 Mapper实现

xml

<!-- SysDeptClosureMapper.xml -->
<mapper namespace="com.ruoyi.system.mapper.SysDeptClosureMapper">
    
    <!-- 插入部门与所有祖先的关系 -->
    <insert id="insertAncestorRelations">
        INSERT INTO sys_dept_closure (ancestor_id, descendant_id, distance)
        SELECT ancestor_id, #{deptId}, distance + 1
        FROM sys_dept_closure
        WHERE descendant_id = #{parentId}
    </insert>
    
    <!-- 删除部门的所有祖先关系（移动部门时使用） -->
    <delete id="deleteAncestorRelations">
        DELETE FROM sys_dept_closure
        WHERE descendant_id IN (
            SELECT descendant_id FROM (
                SELECT descendant_id FROM sys_dept_closure 
                WHERE ancestor_id = #{deptId}
            ) AS tmp
        )
        AND ancestor_id IN (
            SELECT ancestor_id FROM (
                SELECT ancestor_id FROM sys_dept_closure 
                WHERE descendant_id = #{deptId} 
                AND ancestor_id != #{deptId}
            ) AS tmp2
        )
    </delete>
    
    <!-- 建立新的祖先关系（移动部门时使用） -->
    <insert id="insertNewAncestorRelations">
        INSERT INTO sys_dept_closure (ancestor_id, descendant_id, distance)
        SELECT supertree.ancestor_id, subtree.descendant_id, 
               supertree.distance + subtree.distance + 1
        FROM sys_dept_closure AS supertree
        CROSS JOIN sys_dept_closure AS subtree
        WHERE supertree.descendant_id = #{newParentId}
        AND subtree.ancestor_id = #{deptId}
    </insert>
    
    <!-- 查询部门的所有子孙部门ID -->
    <select id="selectDescendantIds" resultType="Long">
        SELECT descendant_id 
        FROM sys_dept_closure 
        WHERE ancestor_id = #{deptId}
        AND descendant_id != #{deptId}
    </select>
    
    <!-- 查询部门的所有祖先部门ID -->
    <select id="selectAncestorIds" resultType="Long">
        SELECT ancestor_id 
        FROM sys_dept_closure 
        WHERE descendant_id = #{deptId}
        AND ancestor_id != #{deptId}
        ORDER BY distance DESC
    </select>
</mapper>

3.3 数据权限实现

java

@Aspect
@Component
public class DataScopeAspect {
    
    public static void dataScopeFilter(JoinPoint joinPoint, SysUser user, 
                                     String deptAlias, String userAlias) {
        StringBuilder sqlString = new StringBuilder();
        
        for (SysRole role : user.getRoles()) {
            String dataScope = role.getDataScope();
            
            if ("4".equals(dataScope)) { // 本部门及以下
                // 使用闭包表的高性能查询
                sqlString.append(String.format(
                    " OR %s.dept_id IN (" +
                    "   SELECT descendant_id FROM sys_dept_closure " +
                    "   WHERE ancestor_id = %d" +
                    " )",
                    deptAlias, user.getDeptId()
                ));
            }
            // ... 其他数据权限类型
        }
        
        BaseEntity baseEntity = (BaseEntity) joinPoint.getArgs()[0];
        baseEntity.getParams().put("dataScope", " AND (" + sqlString.substring(4) + ")");
    }
}

四、常用查询操作

4.1 基础查询

sql

-- 1. 查询所有子孙部门（包含自己）
SELECT d.* FROM sys_dept d
JOIN sys_dept_closure c ON d.dept_id = c.descendant_id
WHERE c.ancestor_id = 100;

-- 2. 查询所有子孙部门（不包含自己）
SELECT d.* FROM sys_dept d
JOIN sys_dept_closure c ON d.dept_id = c.descendant_id
WHERE c.ancestor_id = 100 AND c.distance > 0;

-- 3. 查询直接子部门
SELECT d.* FROM sys_dept d
JOIN sys_dept_closure c ON d.dept_id = c.descendant_id
WHERE c.ancestor_id = 100 AND c.distance = 1;

-- 4. 查询所有祖先部门
SELECT d.* FROM sys_dept d
JOIN sys_dept_closure c ON d.dept_id = c.ancestor_id
WHERE c.descendant_id = 300
ORDER BY c.distance DESC;

-- 5. 查询部门层级
SELECT distance FROM sys_dept_closure
WHERE ancestor_id = 1 AND descendant_id = 300;

4.2 业务查询示例

xml

<!-- 带数据权限的用户查询 -->
<select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
    SELECT u.*, d.dept_name 
    FROM sys_user u
    LEFT JOIN sys_dept d ON u.dept_id = d.dept_id
    <where>
        <if test="userName != null and userName != ''">
            AND u.user_name LIKE CONCAT('%', #{userName}, '%')
        </if>
        <!-- 数据权限过滤 -->
        ${params.dataScope}
    </where>
</select>

<!-- 统计部门及子部门的用户数 -->
<select id="countUsersByDept" resultType="Map">
    SELECT d.dept_id, d.dept_name, COUNT(u.user_id) as user_count
    FROM sys_dept d
    JOIN sys_dept_closure c ON d.dept_id = c.descendant_id
    LEFT JOIN sys_user u ON d.dept_id = u.dept_id
    WHERE c.ancestor_id = #{deptId}
    GROUP BY d.dept_id, d.dept_name
</select>

五、性能优化

5.1 索引优化

sql

-- 1. 闭包表索引（已在建表时创建）
-- PRIMARY KEY (ancestor_id, descendant_id) - 覆盖查询子孙
-- INDEX idx_descendant (descendant_id) - 覆盖查询祖先
-- INDEX idx_distance (distance) - 层级查询

-- 2. 查询计划分析
EXPLAIN SELECT descendant_id FROM sys_dept_closure WHERE ancestor_id = 100;
-- 结果：Using index，说明使用了覆盖索引

-- 3. 统计信息更新
ANALYZE TABLE sys_dept_closure;

5.2 缓存策略

java

@Service
public class DeptCacheService {
    
    @Autowired
    private RedisTemplate<String, Set<Long>> redisTemplate;
    
    private static final String DEPT_DESCENDANTS_KEY = "dept:descendants:";
    private static final String DEPT_ANCESTORS_KEY = "dept:ancestors:";
    
    /**
     * 获取部门的所有子孙部门ID（使用缓存）
     */
    @Cacheable(value = "deptDescendants", key = "#deptId")
    public Set<Long> getDescendantIds(Long deptId) {
        List<Long> ids = closureMapper.selectDescendantIds(deptId);
        return new HashSet<>(ids);
    }
    
    /**
     * 清除部门缓存（部门变更时调用）
     */
    @CacheEvict(value = {"deptDescendants", "deptAncestors"}, allEntries = true)
    public void clearDeptCache() {
        // 清除所有部门相关缓存
    }
    
    /**
     * 预热缓存
     */
    public void warmupCache() {
        List<SysDept> allDepts = deptMapper.selectList(null);
        for (SysDept dept : allDepts) {
            getDescendantIds(dept.getDeptId());
        }
    }
}

5.3 批量操作优化

java

@Service
public class DeptBatchService {
    
    /**
     * 批量插入部门
     */
    @Transactional
    public void batchInsertDepts(List<DeptDTO> deptDTOs) {
        // 1. 批量插入部门主表
        List<SysDept> depts = convertToDepts(deptDTOs);
        deptMapper.insertBatch(depts);
        
        // 2. 构建闭包关系
        List<SysDeptClosure> closures = new ArrayList<>();
        for (SysDept dept : depts) {
            // 添加自身关系
            closures.add(new SysDeptClosure(dept.getDeptId(), dept.getDeptId(), 0));
            
            // 添加祖先关系
            if (dept.getParentId() > 0) {
                List<SysDeptClosure> ancestorClosures = buildAncestorClosures(dept);
                closures.addAll(ancestorClosures);
            }
        }
        
        // 3. 批量插入闭包表
        if (!closures.isEmpty()) {
            closureMapper.insertBatch(closures);
        }
    }
}

六、数据迁移方案

6.1 从邻接表迁移到闭包表

sql

-- 创建存储过程，递归构建闭包表
DELIMITER $$

CREATE PROCEDURE migrate_to_closure_table()
BEGIN
    -- 清空闭包表
    TRUNCATE TABLE sys_dept_closure;
    
    -- 插入所有自身关系
    INSERT INTO sys_dept_closure (ancestor_id, descendant_id, distance)
    SELECT dept_id, dept_id, 0 FROM sys_dept;
    
    -- 递归插入祖先关系
    INSERT INTO sys_dept_closure (ancestor_id, descendant_id, distance)
    WITH RECURSIVE dept_tree AS (
        -- 初始化：直接父子关系
        SELECT parent_id as ancestor_id, dept_id as descendant_id, 1 as distance
        FROM sys_dept WHERE parent_id > 0
        
        UNION ALL
        
        -- 递归：祖先关系
        SELECT t.parent_id, d.descendant_id, d.distance + 1
        FROM dept_tree d
        JOIN sys_dept t ON d.ancestor_id = t.dept_id
        WHERE t.parent_id > 0
    )
    SELECT * FROM dept_tree;
END$$

DELIMITER ;

-- 执行迁移
CALL migrate_to_closure_table();

6.2 数据一致性校验

sql

-- 1. 检查闭包表完整性
SELECT d.dept_id, d.dept_name
FROM sys_dept d
WHERE NOT EXISTS (
    SELECT 1 FROM sys_dept_closure c 
    WHERE c.descendant_id = d.dept_id AND c.ancestor_id = d.dept_id
);

-- 2. 检查父子关系一致性
SELECT d.dept_id, d.parent_id
FROM sys_dept d
WHERE d.parent_id > 0
AND NOT EXISTS (
    SELECT 1 FROM sys_dept_closure c
    WHERE c.ancestor_id = d.parent_id 
    AND c.descendant_id = d.dept_id 
    AND c.distance = 1
);

-- 3. 统计每个部门的层级深度
SELECT d.dept_id, d.dept_name, MAX(c.distance) as depth
FROM sys_dept d
JOIN sys_dept_closure c ON d.dept_id = c.descendant_id
GROUP BY d.dept_id, d.dept_name
ORDER BY depth DESC;

七、触发器自动维护（可选）

sql

-- 插入部门时自动维护闭包表
DELIMITER $$

CREATE TRIGGER dept_closure_insert_trigger
AFTER INSERT ON sys_dept
FOR EACH ROW
BEGIN
    -- 插入自身关系
    INSERT INTO sys_dept_closure (ancestor_id, descendant_id, distance)
    VALUES (NEW.dept_id, NEW.dept_id, 0);
    
    -- 插入祖先关系
    IF NEW.parent_id > 0 THEN
        INSERT INTO sys_dept_closure (ancestor_id, descendant_id, distance)
        SELECT ancestor_id, NEW.dept_id, distance + 1
        FROM sys_dept_closure
        WHERE descendant_id = NEW.parent_id;
    END IF;
END$$

DELIMITER ;

八、使用示例

8.1 Controller层

java

@RestController
@RequestMapping("/system/user")
public class SysUserController {
    
    /**
     * 查询用户列表（支持数据权限）
     */
    @GetMapping("/list")
    @DataScope(deptAlias = "u")
    public TableDataInfo list(SysUser user) {
        startPage();
        List<SysUser> list = userService.selectUserList(user);
        return getDataTable(list);
    }
    
    /**
     * 获取部门树形结构
     */
    @GetMapping("/dept/tree")
    public AjaxResult deptTree() {
        List<TreeNode> tree = deptService.buildDeptTree();
        return AjaxResult.success(tree);
    }
}

8.2 复杂查询示例

java

@Service
public class DeptQueryService {
    
    /**
     * 获取部门的完整路径
     */
    public List<SysDept> getDeptPath(Long deptId) {
        return deptMapper.selectDeptPath(deptId);
    }
    
    /**
     * 获取同级部门
     */
    public List<SysDept> getSiblingDepts(Long deptId) {
        SysDept dept = deptMapper.selectById(deptId);
        if (dept == null || dept.getParentId() == 0) {
            return Collections.emptyList();
        }
        
        return deptMapper.selectList(new LambdaQueryWrapper<SysDept>()
            .eq(SysDept::getParentId, dept.getParentId())
            .ne(SysDept::getDeptId, deptId));
    }
    
    /**
     * 获取部门的第N级子部门
     */
    public List<SysDept> getNthLevelDescendants(Long deptId, int level) {
        return deptMapper.selectNthLevelDescendants(deptId, level);
    }
}

九、优缺点总结

优点

查询性能极高：O(1)复杂度，直接通过索引查询
支持复杂查询：轻松实现各种树形查询需求
无递归查询：避免了递归带来的性能问题
事务安全：所有操作都是简单的INSERT/DELETE

缺点

空间占用大：需要存储所有祖先-后代关系
写入成本高：插入/移动节点需要维护多条记录
数据冗余：存在大量冗余数据

适用场景

读多写少的系统
部门层级较深（>5层）
需要频繁查询子树
对查询性能要求极高

不适用场景

频繁重组部门结构
部门数量巨大（>10万）
存储空间有限

十、总结

闭包表是处理树形结构数据权限的最优方案之一，特别适合读多写少、对查询性能要求高的场景。通过牺牲一定的存储空间和写入性能，换取了极致的查询性能，是大型企业系统的理想选择。

部门数据权限控制方案 - 闭包表（Closure Table） ​

一、方案概述 ​

核心优势 ​

性能对比 ​

二、数据库设计 ​

2.1 表结构设计 ​

2.2 数据示例 ​

三、核心实现 ​

3.1 Service层实现 ​

3.2 Mapper实现 ​

3.3 数据权限实现 ​

四、常用查询操作 ​

4.1 基础查询 ​

4.2 业务查询示例 ​

五、性能优化 ​

5.1 索引优化 ​

5.2 缓存策略 ​

5.3 批量操作优化 ​

六、数据迁移方案 ​

6.1 从邻接表迁移到闭包表 ​

6.2 数据一致性校验 ​

七、触发器自动维护（可选） ​

八、使用示例 ​

8.1 Controller层 ​

8.2 复杂查询示例 ​

九、优缺点总结 ​

优点 ​

缺点 ​

适用场景 ​

不适用场景 ​

十、总结 ​

部门数据权限控制方案 - 闭包表（Closure Table）

一、方案概述

核心优势

性能对比

二、数据库设计

2.1 表结构设计

2.2 数据示例

三、核心实现

3.1 Service层实现

3.2 Mapper实现

3.3 数据权限实现

四、常用查询操作

4.1 基础查询

4.2 业务查询示例

五、性能优化

5.1 索引优化

5.2 缓存策略

5.3 批量操作优化

六、数据迁移方案

6.1 从邻接表迁移到闭包表

6.2 数据一致性校验

七、触发器自动维护（可选）

八、使用示例

8.1 Controller层

8.2 复杂查询示例

九、优缺点总结

优点

缺点

适用场景

不适用场景

十、总结