1、背景介绍

笔者在日常项目开发中，遇到一个比较常见的问题。举个例子，公司组织架构是一棵树，部门层级是可以随意增加的。这基本上会把部门这个表设计成一个支持多级的模型。

但业务需求经常需要查询某一个部门下的员工信息，那么这个时候，就需要查询归属于该部门的所有下级部门信息。

2、模型设计

基本上表都会设计成如下模型：

字段	名称
id	部门编号
name	部门名称
parent_id	父部门编号

create table dept
(
    id         bigint auto_increment comment '部门编号'
        primary key,
    parent_id  bigint                             not null comment '父部门id，0表示是一级分组，其他的填写父分组id',
    name       varchar(63)                        null comment '部门名称',
    deleted    int                                not null comment '删除标记，1-表示删除，0表示未删除',
    creator    varchar(20)                        null comment '创建人。描述：记录操作人的账号编号。',
    modifier   varchar(20)                        null comment '修改人',
    gmt_create datetime default CURRENT_TIMESTAMP null comment '创建时间',
    gmt_modify datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '修改时间'
)
    comment '项目分组';

同样会根据parent_id做索引。

3、查询所有子部门的方案

这里总结一下，查询部门以及所有子部门的编号。

3.1 递归方案

根据父部门查询一级子部门，然后递归查询子部门，直到查询为空。

3.2 类存储过程方案

利用FIND_IN_SET函数，直接看SQL

SELECT rg.id
FROM (SELECT id, name, parent_id FROM innovate_group WHERE parent_id != 0) rg,
     (SELECT @pgroupId := #{groupId}) pd
WHERE FIND_IN_SET(parent_id, @pgroupId) > 0
  AND @pgroupId := concat( @pgroupId, ',', id )

定义一个变量 pgroupId，将每条结果的id拼接进去，最后类似于【1,2,3,4】，这样的结果。相当于将查询结果的id都作为父id，这样就可以将所有子部门的id都查询出来。

3.3 对比验证效果

在小数据量的情况下，效果不明显。那么设想造40万条记录，部门层级先分成2级，第一级40个，每个部门下有1万子部门。

3.3.1 存储过程

存储过程方案，执行太慢了，直接报错。

### Cause: java.sql.SQLException: Read timed out
; Read timed out; nested exception is java.sql.SQLException: Read timed out

查看下执行计划如下：

3.3.2 递归方案

@Test
public void testQueryLevel(){
    Stopwatch stopwatch = Stopwatch.createStarted();
    List<Long> result = Lists.newArrayList();
    queryId(414239L, result);
    stopwatch.stop();
    System.out.println(stopwatch.elapsed(TimeUnit.MILLISECONDS));
}

private void queryId(Long parentId, List<Long> result){
    InnovateGroupPO condition = new InnovateGroupPO();
    condition.setParentId(parentId);
    List<InnovateGroupPO> groupPOList = innovateGroupMapper.query(condition);
    List<Long> childList = groupPOList.stream().map(InnovateGroupPO::getId).collect(Collectors.toList());
    result.addAll(childList);

    System.out.println(parentId);

    childList.forEach(pId -> {
        queryId(pId, result);
    });
}

如上代码，在单个父节点下有10000条记录的情况下，查询速度非常慢。

3.4 优化方案

3.4.1 递归方案优化

递归方案里面，由于造的数据里面子部门非常多，针对这种情况，可以考虑合并子部门查询下级部门，节约SQL查询的次数。


@Test
public void testQueryLevel(){
    Stopwatch stopwatch = Stopwatch.createStarted();
    List<Long> result = Lists.newArrayList();
    queryIdBatch(Lists.newArrayList(414239L), result);
    stopwatch.stop();
    System.out.println(stopwatch.elapsed(TimeUnit.MILLISECONDS));
}

private void queryIdBatch(List<Long> parentIds, List<Long> result){
    List<InnovateGroupPO> groupPOList = innovateGroupMapper.queryByParentId(parentIds);
    List<Long> childList = groupPOList.stream().map(InnovateGroupPO::getId).collect(Collectors.toList());
    result.addAll(childList);

    System.out.println(childList.size());
    List<List<Long>> splitList = Lists.partition(childList, 100);
    splitList.forEach(pId -> {
        queryIdBatch(pId, result);
    });
}

优化效果非常明显。

3.4.2 类存储过程方案优化

暂时无优化方案，需要重新设计业务模型。这个下篇文章继续介绍了。

MySQL树形结构查询研究(上篇)