多级分类的子分类查询优化方案

剑圣无痕
4,122 阅读4分钟

背景

项目中我们经常会遇到多级分类的子分类查询问题，例如组织结构、多级菜单、商品种类等，常见数据结构如下：

图片.png

这种设计存在的问题：

  • 针对多级分类的子分类查询,需要通过递归来实现，时间复杂度为0(n),效率比较低。

  • 递归太深容易造成堆栈的溢出。

    那么针对多级分类的子分类查询，有没有更好的实现方案呢？我们可以通过预排序遍历树算法(MPTT)来解决多级分类的子分类查询问题。

简介

MPTT(Modified Preorder Tree Taversal)预排序遍历树算法，主要应用于层级关系的存储和遍历。

基本原理

预排序遍历树算法原理：通过数的左右节点的值来表示树层级的关系。

如下图所示：

图片.png

树形结构转化成数据库的结构数据如下：

图片.png

说明：MPTT数据结构包含了左、右节点、等级、父节点等信息

  • treeid:树的id用来标识数据库中某一颗树。
  • level:表示树的层级，根节点的层级是1,子节点的层级是父节点层级加1。
  • parentId:父节点id,由于根节点没有父节点所以值为-1。
  • lftNode:左节点值。
  • rgtNode:右节点值。

基础应用

遍历整棵树

遍历整棵树只需要通过treeId的条件查询即可。

找到某节点下所有的子孙节点

查找Fruit下的所有子孙节点，只需要查找左节点值大于2且右节点值小于11即可

SELECT * FROM t_tree  t WHERE t.`lftNode`>2 AND t.`rgtNode`<11;

查询结果

图片.png

查询的效率为0(1)比递归查询效率优化了很多。

找到某节点下所有子节点

查找Fruit下的所有子点，只需要查询等级等于2的即可

SELECT * FROM t_tree  t  WHERE t.`level`='2';

查询结果

图片.png

查找某节点的路径

查找Banana节点的路径，只需要查询左节点小于8，右节点大于9即可。

SELECT * FROM t_tree  t WHERE t.`lftNode`<8 AND t.`rgtNode`>9;

查询结果

图片.png

通过查询结果，Banana的节点路径为:Food->Fruit->Yellow

某个节点到底有多少子孙节点

查找Fruit下的所有子孙节点数量，具体公式如下：

 子孙总数=(右值-左值-1)/2

通过公式计算我们知道Fruit的子孙节点的总数为(18-1-1)/2=8个

新增子节点

虽然在查询节点上，预编译排序的效率是非常高的，但是在新增和删除上其效率非常低，因为新增和删除节点都需要重新进行节点的计算和移动，所有速度会比较慢。

实现逻辑

  • 不存在的树中新增节点，创建一颗新树。那么parentid为-1,level是为1，treeId是根据已有树的最大值treeId加1。

  • 已存在的树中新增节点。parentId为父节点的id，level是父节点的level加1，treeId和父节点保持一致。

  • 修复被破坏平衡的其他节点的左值,大于parentId右值的所有节点的左值加2。

  • 修复被破坏平衡的其他节点的右值,大于parentId右值的所有节点的右值加2。

具体实现

DELIMITER $$

DROP PROCEDURE IF EXISTS `SP_TREE_ADD`$$

CREATE PROCEDURE `SP_TREE_ADD`(
IN p_oid INT (11),
IN p_name VARCHAR (30),
IN p_parentOid INT (11)
)
BEGIN
       #获取父节点的相关数据信息
        SELECT @myRight :=rgtNode,@oid :=oid,@level :=LEVEL,@treeid :=treeid FROM t_tree t WHERE oid = p_parentOid;
        #大于parentId右值的所有节点的右值加2。
	UPDATE t_tree SET rgtNode = rgtNode + 2 WHERE rgtNode > @myRight;
        #大于parentId右值的所有节点的左值加2
        UPDATE t_tree SET lftNode = lftNode + 2 WHERE lftNode > @myRight;
        
        UPDATE t_tree SET rgtNode=rgtNode+2 WHERE oid=@oid;
        #插入数据
	INSERT INTO t_tree(oid,NAME,parentOid,LEVEL,lftNode,rgtNode,treeid) 
	VALUES(p_oid, p_name, p_parentOid,@level+1,@myRight, @myRight +1,@treeid);
END$$

DELIMITER ;

测试

在Red的节点下添加一个apple子节点，执行上述存储过程即可

CALL SP_TREE_ADD('11','apple','3');

插入成功后结果如下

图片.png

重新验证Red的下的节点数，只需要查询左节点大于3右节点小于8即可

SELECT * FROM t_tree  t WHERE t.`lftNode`>3 AND t.`rgtNode`<8;

图片.png

删除子节点

实现逻辑

  • 删除大于父节点左值小于右值的节点
  • 修复被破坏平衡的其他节点的左值,大于parentId右值的所有节点的左值减差值。
  • 修复被破坏平衡的其他节点的右值,大于parentId右值的所有节点的右值减差值。

具体实现

DELIMITER $$
DROP PROCEDURE IF EXISTS `SP_TREE_DEETE`$$

CREATE PROCEDURE `SP_TREE_DEETE`(
       IN p_Oid INT (11)
    )
BEGIN      
        SELECT 
            @myleft := lftNode,
            @myright :=rgtNode,
            @oid :=oid,
            @mywidth := rgtNode-lftNode+1
        FROM t_tree WHERE oid = p_Oid;
        
        DELETE FROM t_tree WHERE lftNode BETWEEN @myleft AND @myright;
        UPDATE t_tree SET rgtNode = rgtNode - @mywidth WHERE rgtNode > @myright; 
        UPDATE t_tree SET lftNode = lftNode - @mywidth WHERE lftNode > @myright;
    END$$

DELIMITER ;

测试

删除Red的节点下的apple子节点，执行上述存储过程即可

CALL SP_TREE_DEETE(11);

删除后结果如下

图片.png

重新验证Red的下的节点数，只需要查询左节点大于3右节点小于6即可

SELECT * FROM t_tree  t WHERE t.`lftNode`>3 AND t.`rgtNode`<6;

图片.png

优缺点

优点

预排序遍历树查询效率高,查询的效率不受分类层级的增加的影响，适用于查询操作较多的场景。

缺点

新增和删除需要重新计算节点的左右值，所以执行的效率较低

总结

本文针对预排序算法进行讲解，任何的一种解决方案都不是完美的，我们需要掌握其优缺点，在实际的项目中需要根据具体的业务场景来选择合适的方案。

我正在参与掘金技术社区创作者签约计划招募活动，点击链接报名投稿。

avatar
文章
阅读
粉丝