部门组织设计

业务

• 实现多层级部门的最优化设计模型
• 高效的批量插入 SQL 写法
• 同样适用于其他数据量大，且层级深的树形结构数据的存储和检索优化

模型设计

嵌套查询

部门表中包含部门Id和上级部门Id字段，当查询指定部门Id的所有层级子部门列表时，需利用数据库特有的递归查询 SQL 语句，获取查询结果。

查询指定部门Id的所有层级子部门列表

SELECT * FROM zz_sys_dept START WITH dept_id = #{deptId} CONNECT BY PRIOR dept_id = parent_dept_id

模糊查询

部门表中包含部门Id和上级部门Id字段，同时新增上级部门Id路径字段 (parent_id_path)，并将所有上级部门Id存入该字段，部门Id之间使用分隔符隔开。查询指定部门Id的所有层级子部门列表时，使用模糊搜索。

查询指定部门Id的所有层级子部门列表

SELECT * FROM zz_sys_dept WHERE parent_id_path LIKE '%xxxxx%' AND dept_id = #{deptId}

优化

空间换时间，新增一张部门关联表，用于扁平化存储部门上下级之间的关联关系

部门表 (zz_sys_dept)，存储部门业务数据。而部门关联表 (zz_sys_dept_relation) ，会扁平化存储当前部门与所有下级子部门之间的关联关系数据。下面是部门表 zz_sys_dept 中存储的部门层级数据结构。

├── dept-one
│       └── dept-two-A
│              └── dept-three-A
│              └── dept-three-B
│       └── dept-two-B
│              └── dept-three-C
│              └── dept-three-D
│              └── dept-three-E

在部门关联表 zz_sys_dept_relation 中扁平化展开后的存储结构如下

parent_dept_id	dept_id
dept-one	dept-one
dept-one	dept-two-A
dept-one	dept-three-A
dept-one	dept-three-B
dept-one	dept-two-B
dept-one	dept-three-C
dept-one	dept-three-D
dept-one	dept-three-E
dept-two-A	dept-two-A
dept-two-A	dept-three-A
dept-two-A	dept-three-B
dept-two-B	dept-two-B
dept-two-B	dept-three-C
dept-two-B	dept-three-D
dept-two-B	dept-three-E
dept-three-A	dept-three-A
dept-three-B	dept-three-B
dept-three-C	dept-three-C
dept-three-D	dept-three-D
dept-three-E	dept-three-E

查询部门的所有层级子部门数据时

SELECT d.* FROM zz_sys_dept d, zz_sys_dept_relation r 
WHERE r.dept_id = d.dept_id AND r.parent_dept_id = #{deptId}

对比

	递归查询	数据库语法	索引优化	实现难度	查询性能	层级变更成本
嵌套查询	有	不兼容	具体看数据库实现	低	低	低
模糊查询	没有	全兼容	索引被抑制	低	低	极高
优化	没有	全兼容	索引优化	高	高	一般

代码实现

获取指定部门的多层级子部门

SELECT d.* FROM zz_sys_dept d, zz_sys_dept_relation r 
WHERE r.dept_id = d.dept_id AND r.parent_dept_id = #{deptId}

新增部门时，需要先在部门表 zz_sys_dept 中插入一条部门数据。然后再在同一事务内，在 zz_sys_dept_relation 表中同步添加，该部门Id和所有上级部门Id之间的关联数据，同时也要插入一条自己和自己的关联数据。

@Transactional(rollbackFor = Exception.class)
@Override
public SysDept saveNew(SysDept sysDept, SysDept parentSysDept) {
    // 这里会现在zz_sys_dept表中插入一条新的部门数据。
    sysDept.setDeptId(idGenerator.nextLongId());
    sysDept.setDeletedFlag(GlobalDeletedFlag.NORMAL);
    MyModelUtil.fillCommonsForInsert(sysDept);
    sysDeptMapper.insert(sysDept);
    if (parentSysDept == null) {
        // 如果新增的部门没有父部门，这里只需要在zz_sys_dept_relation插入一条自己和自己关联的记录。
        sysDeptRelationMapper.insert(new SysDeptRelation(sysDept.getDeptId(), sysDept.getDeptId()));
    } else {
        // 需要在zz_sys_dept_relation中，插入更多关联数据。该SQL的实现，详见下面的SQL片段。
        // 1. 父部门(parentSysDept.getDeptId)的所有上级部门Id，与当前部门(sysDept.getDeptId)的关联关系。
        // 2. 同时插入自己和自己的关联关系。
        sysDeptRelationMapper.insertParentList(parentSysDept.getDeptId(), sysDept.getDeptId());
    }
    return sysDept;
}

<insert id="insertParentList">
    -- 批量插入新增的部门与其所有上级部门的关联关系。
    INSERT INTO zz_sys_dept_relation(parent_dept_id, dept_id)
    -- t.parent_dept_id 是当前新增部门的所有上级部门Id。
    -- 而变量#{deptId}，就是新增的部门Id。
    -- 从而在这个SELECT中，将直接计算出当前新增部门Id和其所有上级部门Id的关联数据列表。
    SELECT t.parent_dept_id, #{myDeptId} 
    FROM zz_sys_dept_relation t
    -- 下面的条件将过滤出指定部门的所有父部门列表。
    -- 而这里指定的部门变量#{parentDeptId}，是当前新增部门的父部门Id。
    -- 这样的查询结果就将返回该父部门的所有上级部门，同时也包含自己(parentDeptId)
    WHERE t.dept_id = #{parentDeptId}
    UNION ALL
    -- union all 一下自己和自己关系。这样就可以在一条SQL中完成，减少了数据库和服务之间的网络开销。
    SELECT #{myDeptId}, #{myDeptId}
</insert>

删除部门时，先从部门表 zz_sys_dept 中删除该部门数据，同时再在同一事务内，同步删除 zz_sys_dept_relation 表中，所有与该部门Id关联的数据，被删除的关联关系数据，均为与当前部门Id关联的上级部门Id。

@Transactional(rollbackFor = Exception.class)
@Override
public boolean remove(Long deptId) {
    // 先从部门表中删除当前部门Id。
    if (sysDeptMapper.deleteById(deptId) == 0) {
        return false;
    }
    // 这里删除当前部门Id与其所有父部门Id的关联关系数据。
    // 当前部门和子部门的关系无需在这里删除，因为包含子部门时不能删除父部门。
    SysDeptRelation deptRelation = new SysDeptRelation();
    deptRelation.setDeptId(deptId);
    sysDeptRelationMapper.delete(new QueryWrapper<>(deptRelation));
    return true;
}

更新部门时，如果没有涉及到部门层级的变化 (parent_id不变)，只需在 zz_sys_dept 表中直接更新部门数据即可。否则，就需要在同一事务，同步修改 zz_sys_dept_relation 表中，所有与该部门Id关联的上下级部门关联关系数据。

@Transactional(rollbackFor = Exception.class)
@Override
public boolean update(SysDept sysDept, SysDept originalSysDept) {
    MyModelUtil.fillCommonsForUpdate(sysDept, originalSysDept);
    UpdateWrapper<SysDept> uw = this.createUpdateQueryForNullValue(sysDept, sysDept.getDeptId());
    // 先在zz_sys_dept表中，更新部门的业务数据。
    if (sysDeptMapper.update(sysDept, uw) == 0) {
        return false;
    }
    // 判断部门的层级是否变化，如果变化了，就需要在zz_sys_dept_relation中，先移除该部门Id
    // 与原上级部门Id之间的关联关系，以及该部门的所有子部门，与当前部门原上级部门Id之间的关联关系，
    // 再重新计算并保存，当前部门及其子部门，与新父部门Id列表之间的关联关系。
    if (ObjectUtils.notEqual(sysDept.getParentId(), originalSysDept.getParentId())) {
        this.updateParentRelation(sysDept, originalSysDept);
    }
    return true;
}

private void updateParentRelation(SysDept sysDept, SysDept originalSysDept) {
    List<Long> originalParentIdList = null;
    // 1. 因为层级关系变化了，所以要先遍历出，当前部门的原有父部门Id列表。
    if (originalSysDept.getParentId() != null) {
        LambdaQueryWrapper<SysDeptRelation> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(SysDeptRelation::getDeptId, sysDept.getDeptId());
        List<SysDeptRelation> relationList = sysDeptRelationMapper.selectList(queryWrapper);
        originalParentIdList = relationList.stream()
                .filter(c -> !c.getParentDeptId().equals(sysDept.getDeptId()))
                .map(SysDeptRelation::getParentDeptId).collect(Collectors.toList());
    }
    // 2. 毕竟当前部门的上级部门变化了，所以当前部门和他的所有子部门，与当前部门的原有所有上级部门
    // 之间的关联关系就要被移除。
    // 这里先移除当前部门的所有子部门，与当前部门的所有原有上级部门之间的关联关系。
    if (CollUtil.isNotEmpty(originalParentIdList)) {
        sysDeptRelationMapper.removeBetweenChildrenAndParents(originalParentIdList, sysDept.getDeptId());
    }
    // 这里更进一步，将当前部门Id与其原有所有上级部门Id之间的关联关系删除。
    SysDeptRelation filter = new SysDeptRelation();
    filter.setDeptId(sysDept.getDeptId());
    sysDeptRelationMapper.delete(new QueryWrapper<>(filter));
    // 3. 重新计算当前部门的新上级部门列表。
    List<Long> newParentIdList = new LinkedList<>();
    // 这里要重新计算出当前部门所有新的上级部门Id列表。
    if (sysDept.getParentId() != null) {
        LambdaQueryWrapper<SysDeptRelation> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(SysDeptRelation::getDeptId, sysDept.getParentId());
        List<SysDeptRelation> relationList = sysDeptRelationMapper.selectList(queryWrapper);
        newParentIdList = relationList.stream()
                .map(SysDeptRelation::getParentDeptId).collect(Collectors.toList());
    }
    // 4. 先查询出当前部门的所有下级子部门Id列表。
    LambdaQueryWrapper<SysDeptRelation> queryWrapper = new LambdaQueryWrapper<>();
    queryWrapper.eq(SysDeptRelation::getParentDeptId, sysDept.getDeptId());
    List<SysDeptRelation> childRelationList = sysDeptRelationMapper.selectList(queryWrapper);
    // 5. 将当前部门及其所有子部门Id与其新的所有上级部门Id之间，建立关联关系。
    List<SysDeptRelation> deptRelationList = new LinkedList<>();
    deptRelationList.add(new SysDeptRelation(sysDept.getDeptId(), sysDept.getDeptId()));
    for (Long newParentId : newParentIdList) {
        deptRelationList.add(new SysDeptRelation(newParentId, sysDept.getDeptId()));
        for (SysDeptRelation childDeptRelation : childRelationList) {
            deptRelationList.add(
                    new SysDeptRelation(newParentId, childDeptRelation.getDeptId()));
        }
    }
    // 6. 执行批量插入SQL语句，插入当前部门Id及其所有下级子部门Id，与所有新上级部门Id之间的关联关系。
    sysDeptRelationMapper.insertList(deptRelationList);
}