类别树查询高性能解决方案

采用mysql递归查询，内联的方式从类别表中查询出整个类别树 

<select id="selectTreeNodes" resultType="com.study.content.model.dto.CourseCategoryTreeDto">
        with recursive t1 as (
            select c1.* from course_category c1 where id=#{id}
            union all
            select c2.* from course_category c2 join t1
                                                     on c2.parentid=t1.id
        )
        select t1.* from t1
        order by t1.id;
    </select>

 用sream流解析类别树，具体情况根据业务更改，这里提供参考代码

 public List<CourseCategoryTreeDto> queryTreeNodes(String id) {
        List<CourseCategoryTreeDto> courseCategoryTreeDtos = courseCategoryMapper.selectTreeNodes(id);
        Map<String, CourseCategoryTreeDto> treeMap = courseCategoryTreeDtos.stream().filter(item -> !id.equals(item.getId())).collect(
                Collectors.toMap(CourseCategory::getId, value -> value, (key1, key2) -> key2)
        );
        ArrayList<CourseCategoryTreeDto> result = new ArrayList<>(courseCategoryTreeDtos.size());
        courseCategoryTreeDtos.stream().forEach(item -> {
            //处理二级节点
            if (item.getParentid().equals(id)) {
                result.add(item);
            }
            //处理三级及以上节点,子找父,再加入父节点
            CourseCategoryTreeDto courseCategoryTreeDto = treeMap.get(item.getParentid());
            if (courseCategoryTreeDto != null) {
                if (courseCategoryTreeDto.getChildrenTreeNodes() == null) {
                    courseCategoryTreeDto.setChildrenTreeNodes(new ArrayList<>());
                }
                courseCategoryTreeDto.getChildrenTreeNodes().add(item);
            }
        });
        return result;
    }

因为只创建一次数据库连接，mysql底层又是c语言，所以比起用java进行递归多次查询性能要高出不少