关系型DB存储如何优化树形结构的查询? MPTT

原创已于 2022-12-06 18:31:01 修改 · 725 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #预排序树 #mptt

于 2022-12-06 18:19:18 首次发布

本文深入探讨MPTT树算法在多租户环境下的实现细节，包括节点增删改等核心操作，并针对常见问题提出解决方案。

考虑下日常开发的实际使用所以整理了此文,建议了解mptt树先参考下基本介绍再来看此文.此外笔者也是第一次尝试实现这个算法,并且网上没有找到特别舒服的资料,因此写了此文,如有错误烦请指正,感谢.

现有资料的坑

1. 网上写这个的资料还挺少的,大多数还是写的存储过程,大多数场景下还是不用这个了,因此写个日常编码的形式
2. 其次大多数资料都是基于存在一个根节点的前提下去写的,我们平常用的业务场景可能根本就没有一个根节点,你可能说可以加一个,不对业务展示就行了,但是考虑到多租户又会有一些租户隔离的问题,总之确实能解,但是我想尝试能不能在设计层就给他解决了,本文使用的也是假设不存在根节点(或者说可以创建多个根节点的形式)
3. 另外一个大坑就是更新操作,网上都是一笔带过,因为更新可以看成删除和新增,确实如此,但是删除肯定不可能真删除(因为你只是移动了节点,你删了后再新增id如果变了就完了),而且我们希望新增能快一点,不是一个个节点的新增,因此也将更新单独写了一遍

概念

每个节点有一个左右值,可以看成一条蛇从根节点左值开始,顺着最左树枝向下爬到末级节点后过渡到右值然后向上到汇集节点后兄弟节点继续重复上述动作,每次加1,最终爬一遍整棵树后回到根节点.

节点定义

我们的项目要求实现多租户,我们实现的方式是表里加租户id,不需要多租户的可以不考虑这个字段

public class MpttNode {
    private String tenantId;

    private Long id;

    private String name;

    private Long parentId;

    private Integer level;

    private Integer mpttLeft;

    private Integer mpttRight;

}

提供接口

首先查询大多能找到资料且都是简单sql这里就不提了,最关键的一个接口就是查询某个节点下的子节点,即查询大于该节点左值小于该节点右值的节点

private List<MpttNode> findAllSubMpttNode(String tenantId, int left, int right) {
        return list(new QueryWrapper<MpttNode>().lambda().eq(MpttNode::getTenantId, tenantId)
                .gt(MpttNode::getMpttLeft, left).lt(MpttNode::getMpttRight, right));
    }

关键就是写操作,尤其更新

首先写操作必须加锁, 基本就是表级别加锁,我这用分布式锁取代了,一个是方便操作,二是我们多租户没必要锁一整个表,这里的写操作也只有三种: 新增,更新,删除

 //distribute lock for tenantId
        //这里锁的粒度不好控制，因为mptt本身是树级别的链式转化，那么涉及的子节点数量就不好判断
        // 比如假设新增的时候只锁父节点，但是删除其下的某个隔代节点时
        // 即便使用crabbing协议好像也只能从上往下锁，而无法锁住该节点后续的right值大于父节点right值的节点
        // 所以索性干脆锁整棵树了
        RLock lock = locker.lock(String.format(MPTT_KEY, tenantId);
        try {
            write(mpttNode);
        } finally {
            // release distribute lock
            lock.unlock();
        }

准备必要的sql

因为这些sql用mybatis plus 不好表示,所以写成mapper了,后面调用的baseMapper都是这几个sql,这几个sql可以先不看,看到下面的代码实现后再回来找即可.

 <update id="decrLeft">
        update mptt_tree
        set mptt_left = mptt_left - #{diff}
        where tenant_id = #{tenantId} and mptt_left > #{left}
    </update>

    <update id="decrRight">
        update mptt_tree
        set mptt_right = mptt_right - #{diff}
        where tenant_id = #{tenantId} and mptt_right > #{right}
    </update>


    <update id="incrLeft">
        update mptt_tree
        set mptt_left = mptt_left + #{diff}
        where tenant_id = #{tenantId} and mptt_left > #{incrBase}
    </update>


    <update id="incrRight">
        update mptt_tree
        set mptt_right = mptt_right + #{diff}
        where tenant_id = #{tenantId} and mptt_right > #{incrBase}
    </update>

    
    <select id="findMaxRight" resultType="java.lang.Integer">
        select max(mptt_right) from mptt_tree
        where tenant_id = #{tenantId} and parent_id = #{parentId}
    </select>

插入

  public MpttNode createMpttNode(MpttNodeReq req) {
        MpttNode parentMpttNode = getOne(new QueryWrapper<MpttNode>().lambda()
                .eq(MpttNode::getTenantId, req.getTenantId()).eq(MpttNode::getId, req.getParentId()));

        final MpttNode mpttNode = new MpttNode(req);

        // set level
        final int level = Optional.ofNullable(parentMpttNode).map(MpttNode::getLevel).orElse(0) + 1;
        mpttNode.setLevel(level);

        // 假设每次添加新节点都是往右侧添加

        // 取父节点下直接子节点的最右节点
        Integer maxRight = baseMapper.findMaxRight(req.getTenantId(), req.getParentId());
        // 如果 没有 最大的 right 的
        if (maxRight == null) {
            // 空树, parentId = -1
            if (parentMpttNode == null) {
                mpttNode.setMpttLeft(0);
                mpttNode.setMpttRight(1);
                save(mpttNode);
            } else {
                // 该 parent 没有任何子节点
                // 更新所有后继节点 + 2
                baseMapper.incrLeft(req.getTenantId(), parentMpttNode.getMpttLeft(), 2);
                baseMapper.incrRight(req.getTenantId(), parentMpttNode.getMpttLeft(), 2);
                mpttNode.setMpttLeft(parentMpttNode.getMpttLeft() + 1);
                mpttNode.setMpttRight(mpttNode.getMpttLeft() + 1);
                save(mpttNode);
            }
        } else {
            // 否则为同级情况
            // + 1作为左节点 + 2 作为右节点
            mpttNode.setMpttLeft(maxRight + 1);
            mpttNode.setMpttRight(maxRight + 2);
            baseMapper.incrLeft(req.getTenantId(), maxRight, 2);
            baseMapper.incrRight(req.getTenantId(), maxRight, 2);
            save(mpttNode);
        }
        return mpttNode;

    }

删除

public void deleteMpttNode(String tenantId, Long id) {
        // 校验是否关联简历
        // 查处所有子节点
        MpttNode mpttNode = getOne(new QueryWrapper<MpttNode>().lambda()
                .eq(MpttNode::getTenantId, tenantId).eq(MpttNode::getId, id));
        List<MpttNode> allSubMpttNodeList = findAllSubMpttNode(tenantId, mpttNode.getMpttLeft(), mpttNode.getMpttRight());

        List<Long> relatedIds = new ArrayList<>();
        relatedIds.add(mpttNode.getId());
        relatedIds.addAll(allSubMpttNodeList.stream().map(MpttNode::getId).collect(Collectors.toList()));
 

        Integer mpttLeft = mpttNode.getMpttLeft();
        Integer mpttRight = mpttNode.getMpttRight();
        int diff = mpttRight - mpttLeft + 1;
		// 删除当前节点,影响的是这条蛇往后爬的节点
        baseMapper.decrLeft(tenantId, mpttLeft, diff);
        baseMapper.decrRight(tenantId, mpttRight, diff);

        removeByIds(relatedIds);

    }

更新

public MpttNode updateMpttNode(MpttNodeReq req) {
        // 仅更新名称之类的，不改变结构关系
        final MpttNode mpttNode = new MpttNode(req);
        MpttNode existOne = getOne(new QueryWrapper<MpttNode>().lambda()
                .eq(MpttNode::getId, req.getId()).eq(MpttNode::getTenantId, req.getTenantId()));
        if (existOne.getParentId().equals(mpttNode.getParentId())) {
            updateById(mpttNode);
            return mpttNode;
        }
        // 先查出该节点以及改节点的子节点是哪些
        List<MpttNode> allSubMpttNode = findAllSubMpttNode(req.getTenantId(), existOne.getMpttLeft(), existOne.getMpttRight());


        MpttNode newParentNode = getOne(new QueryWrapper<MpttNode>().lambda()
                .eq(MpttNode::getTenantId, req.getTenantId()).eq(MpttNode::getParentId, req.getParentId()));


        // 相当于先删除 后新建
        Integer mpttLeft = existOne.getMpttLeft();
        Integer mpttRight = existOne.getMpttRight();
        int diff = mpttRight - mpttLeft + 1;
        // 该节点摘掉，后继节点肯定 decr 了
        baseMapper.decrLeft(mpttNode.getTenantId(), mpttLeft, diff);
        baseMapper.decrRight(mpttNode.getTenantId(), mpttRight, diff);

        // 然后更新新的talentPool, 相当于在新 parent 下新增，然后改掉之前的 allSubTalentPool left right
        // 因为 mptt 本质是链式的，所以只要求出 变更前后 节点的左值变化，其所有子节点的左右值都应用这个变化即可


        // 取父节点下直接子节点的最右节点
        Integer maxRight = baseMapper.findMaxRight(req.getTenantId(), req.getParentId());
        // 如果 没有 最大的 right 的
        if (maxRight == null) {
            // 因为是更新，所以一定不是空树
            // 空树： maxRight == null && newParentNode == null
            // 就是说 maxRight == null 的时候 newParentNode 一定不等于null
            // 因为 newParentNode 为null， 只有为根节点的时候才存在
            // 而这种情况下，又只有 非根节点直接子节点 以外的节点迁移过来才算，根直接子节点再迁移相当于没改parentId，不参与更新了
            // 既然有这种节点，说明 非根节点直接子节点 必不为空，那么maxRight 必不等于 null
            //
            // 综上， newParentNode 这里一定不为 null，并且其不存在直接子节点
            mpttNode.setMpttLeft(newParentNode.getMpttLeft() + 1);
            compareAndSetChange(mpttNode, existOne, allSubMpttNode);

            // 后续节点直接加diff即可
            baseMapper.incrLeft(req.getTenantId(), newParentNode.getMpttRight(), diff);
            // 注意的是该父节点添加新节点后其本身的右值也变化了，所以是 EQ
            baseMapper.incrRight(req.getTenantId(), newParentNode.getMpttLeft(), diff);
            // 最后强刷算好的新位置
            updateById(mpttNode);
            if (CollectionUtils.isNotEmpty(allSubMpttNode)) {
                updateBatchById(allSubMpttNode);
            }
        } else {
            // 否则为同级情况
            // + 1作为左节点 + 2 作为右节点
            mpttNode.setMpttLeft(maxRight + 1);
            compareAndSetChange(mpttNode, existOne, allSubMpttNode);
            // 后续节点直接加diff即可
            baseMapper.incrLeft(req.getTenantId(), maxRight, diff);
            // 注意的是该父节点添加新节点后其本身的右值也变化了，所以是 EQ
            baseMapper.incrRight(req.getTenantId(), maxRight, diff);
            // 最后强刷算好的新位置
            updateById(mpttNode);
            if (CollectionUtils.isNotEmpty(allSubMpttNode)) {
                updateBatchById(allSubMpttNode);
            }
        }
        return mpttNode;
    }


    private void compareAndSetChange(MpttNode mpttNode, MpttNode existOne, List<MpttNode> allSubMpttNode) {
        mpttNode.setMpttRight(mpttNode.getMpttLeft() + (existOne.getMpttRight() - existOne.getMpttLeft()));
        // 子节点应用这个 change
        int change = mpttNode.getMpttLeft() - existOne.getMpttLeft();
        int changeLevel = mpttNode.getLevel() - existOne.getLevel();
        for (MpttNode subNode : allSubMpttNode) {
            subNode.setMpttLeft(subNode.getMpttLeft() + change);
            subNode.setMpttRight(subNode.getMpttRight() + change);
            subNode.setLevel(subNode.getLevel() + changeLevel);
        }
    }