Java高效率查询Mysql节点树数据

示例

目前有一个功能：任务计划管理，必然存在多级子任务的父子级关系，每个任务还会存在其它数据的关联表。

mysql无法一次性递归查出想要的数据结构，想必很多人都会是通过根目录递归查询数据库的方式查出树结构数据。如果节点数较多，就会造成大量请求Mysql查询，效率会很低。

那么如何优化节点树数据查询效率？？？

表设计

# task(任务主表) [id,name,...]
[110,"XXX项目排期",...]

# task_tree(节点树子任务表) [id,task_id,parent_id,task_name,level,task_num,...]
[1, 110, 110, "XXX项目排期",  0, 0, ...]
[2, 110, 1, "一级主任务1",     1, 1, ...]
[5, 110, 2, "二级子任务1.1",   2, 1, ...]
[6, 110, 2, "二级子任务1.2",   2, 2, ...]
[3, 110, 1, "一级主任务2",     1, 2, ...]
[4, 110, 1, "一级主任务3",     1, 3, ...]
[7, 110, 4, "二级子任务3.1",   2, 1, ...]
[8, 110, 7, "三级子任务3.1.1", 3, 1, ...]
[8, 110, 7, "三级子任务3.1.2", 3, 2, ...]

# task_tree_pre(前置任务表) 关联节点任务表 [id,task_id,task_tree_id,val,...]
# 同时关联任务主表的好处是在删除数据的时候减少不必要的关联查询
[1, 110, 6 , "2SF", ...]
[1, 110, 6 , "7SS", ...]

Java使用Map处理

原理：仅发起一次sql请求把所需数据全查出来，使用Map进行逻辑封装处理，效率非常之快。

public class Test {
    /**
     * 根据主表id查询节点树子任务数据
     */
    public List<TaskTree> tree()
    {
        // 根据主表id查询所有的前置任务
        List<TaskTreePre> listPre = "select * from task_tree_pre where task_id = 110";
        Map<String, List<TaskTreePre>> mapPre = new HashMap<>();
        for (TaskTreePre pre : listPre) {
            // 对所有taskTreeId用map封装对应的前置任务列表
            List<TaskTreePre> list = new ArrayList<>();
            if (mapPre.containsKey(pre.getTaskTreeId())) {
                // 先获取到map中已存在的listPre，再继续添加
                list = mapPre.get(pre.getTaskTreeId());
            }
            list.add(pre);
            mapPre.put(pre.getTaskTreeId(), list);
        }
        // 根据主表id查询所有的节点树任务数据
        List<TaskTree> listTree = "select * from task_tree where task_id = 110";
        Map<String, List<TaskTree>> map = new HashMap<>();
        // 设置前置任务
        for (TaskTree task : listTree) {
            // 根据任务表id获取前置任务并添加
            task.setTaskTreePreList(mapPre.get(task.getId()));
            // 对所有parentId用map封装所属的子级任务
            List<TaskTree> list = new ArrayList<>();
            if (map.containsKey(task.getParentId())) {
                list = map.get(task.getParentId());
            }
            list.add(task);
            map.put(task.getParentId(), list);
        }
        // 递归封装节点树结构数据
        return initTree("110", map);
    }

    /**
     * 递归封装节点树结构数据
     * @param map   key包含了所有的parentId, value=所属子级列表
     * @param id    父级id
     * @return
     */
    public List<TaskTree> initTree(String parentId, Map<String, List<TaskTree>> map) {
        List<TaskTree> list = new ArrayList<>();
        if (map.containsKey(parentId)) {
            list = map.get(parentId);
            if (list.size() > 0) {
                for (TaskTree task : list) {
                    task.setChildren(initTree(map, task.getId()));
                }
            }
        }
        return list;
    }
}

Java使用Lambda处理

public class Test {
    public List<TaskTree> tree(){
        // 根据主表id查询所有的节点树任务数据
        List<TaskTree> listTree = "select * from task_tree where task_id = 110";
        return initTree("110", listTree);
    }
    /**
     * 通过递归算法实现树
     * @param parentId 父Id
     * @param menuList 当前所有菜单
     * @return
     */
    private List<TaskTree> initTree(String parentId,List<TaskTree> taskList){
        List<TaskTree> list = new ArrayList<>();
        /**
        * Optional.ofNullable(menuList).orElse(new ArrayList<>())  如果menuList是空的则返回一个new ArrayList<>()
        * .stream() 返回List中的流
        * .filter(task -> task.getParentId().equals(parentId)) 筛选List，返回只有条件成立的元素（当前元素的parentId必须等于父id）
        * .forEach 遍历这个list
        */
        Optional.ofNullable(taskList).orElse(new ArrayList<>())
        .stream()
        .filter(task -> task.getParentId().equals(parentId))
        .forEach(task -> {
            task.setChildren(initTree(task.getId(), taskList));
            list.add(task);
        });
        return list;
    }
}

Java使用Hutool工具处理

public class Test {
    public List<SysTree> list(String id) {
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.eq("report_id", id);
        List<SysTree> list = sysTreeService.list(wrapper);
        return treeList("0", list);
    }

    private List<Tree<String>> treeList(String parentId, List<SysTree> list) {
        TreeNodeConfig config = new TreeNodeConfig();
        List<Tree<String>> treeNodes = TreeUtil.build(
            list, parentId, config,
            (t, tree) -> {
                tree.setId(t.getId());
                tree.setName(t.getChapterName());
                tree.setParentId(t.getParentId());
                // 权重值用于排序
                tree.setWeight(Convert.toInt(t.getSortNum()));
                // 扩展属性
                tree.putExtra("level", t.getLevel());
                tree.putExtra("content", t.getContent());
            }
        );
        return treeNodes;
    }
}

MySQL 递归查询

SELECT a.level, b.id, b.task_id, b.parent_id, b.task_name
FROM(
    SELECT
        @ids AS pids,
        (SELECT @ids := GROUP_CONCAT(id) FROM task_tree WHERE task_id = '110' and FIND_IN_SET(parent_id, @ids)) AS cids,
        (@l := @l + 1) as level
    FROM task_tree, (SELECT @ids := #{parentId}, @l := 0) c
    WHERE @ids IS NOT NULL
) a, task_tree b
WHERE FIND_IN_SET(b.id, a.pids)
ORDER BY a.level

MySQL8 使用WITH RECURSIVE递归查询

MySQL8新增CTE（公共表表达式）写法。比上面的方式更直观、简单易懂。

WITH RECURSIVE  cte_name  AS (
    初始语句（非递归部分）
    UNION ALL
    递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE]

-- WITH RECURSIVE t：t 就是相当于一个临时表的概念
-- WITH AS () 后面必须跟着 [ SELECT| INSERT | UPDATE | DELETE] 语句，否则报错。

-- 示例
WITH RECURSIVE t AS (
    SELECT id, name, parent_id FROM task_tree WHERE id = #{parentId}
    UNION ALL
    SELECT a.id, a.name, a.parent_id FROM task_tree a
    JOIN task_tree b ON b.id = a.parent_id
)
SELECT * FROM t;