业务(1):无限级分类 数据表的设计与最优实现

        公司业务发展迅速,以前的业务成员有 业务员、代理、医生,现在变成大区经理、业务员、代理、医生,并且有可能还会再增加上级,业务运行过程中,会时常用到,查询某一个级别的业务数据和转移某一级别等,由于以前只有三级分类的时候,用的是父id记录关系,现在分类增加,业务代码写起来很复杂费力,考虑的扩展性和无限级分类的本质,现把所有的方式列出来 找出最优的办法。


一:无限级分类数据上的本质

        分类的关系可以表述为一父多子的继承关系,正好对应数据结构中的树,那么问题就转化成了如何在数据库中存储一棵树,并且对分类所需要的操作有较好的支持。这就是无限制分类表现在数据上的本质。

        

        分类在业务中要进行的操作有:

                1:单个分类的增删改查操作

                2:查询一个分类的直属下级和所有下级

                3:查询顶级分类到分类之间的路径,如:王医生路径:A经理-B业务-C代理

                4:查询分类是那一级别的,如:医生级别是1、代理级别是2、业务员级别是3、大区经理是4

                5:查询某一级的所有分类

                6:移动一个分类,如:业务员B原先在A经理下,现在移动到B经理下

        在实际业务中,查询的操作使用的更加频繁,性能考虑要以查询操作优先,另外每个分类除了继承关系外,还有名字、电话、地址、性别、年龄等熟悉,也要存储到数据表中。针对以上的需求,列出以下几个设计方案。


二:直接记录父分类

    每个分类上添加上直属上级的ID,存储它们之间的继承关系。简单易懂,没有冗余字段,存储空间占用极小,在数据库层面是一种很好的设计。


时光疯子博客

        需求1:单个分类的增删改查操作

                增改查一条语句可以实现,删除需要对下级分类做处理,对下级的parent_id做update

        需求2:查询一个分类的直属下级和所有下级

                查所有的下级麻烦些,需先查出直属下级,再查询所有直属下级的直属下级,这意味着递归,性能一下子差了很多

        需求3:查询顶级分类到分类之间的路径,如:王医生路径:A经理-B业务-C代理

                同样需要递归,数据表只存储了直属父类,需通过递归到顶级分类才能获取他们之间所有分类的信息

        需求4:查询分类是那一级别的

                同样需要递归或循环,查出所有上级分类的数量即为分类的层级

        需求5:查询某一级的所有分类

                需要先找出所有一级分类,然后循环一遍,找出所有一级分类的子类就是二级分类...如此循环直到所需的级数为止,这个功能基本是废了。

        需求6:移动一个分类

                更新一下该分类的父id即可


       总结: 这个方式,一开始就能想到,在层级不深的情况下(最好2级),因为其简单直观的特点,不失为一个好的选择。


三:路径列表

        从直接记录父类我们可以看出,之所以查询慢,因为在分类中仅仅存储了直属上级的关系,需求是查出非直属上级,针对这点,可以增加一个到顶级分类之间的路径,叫做path


时光疯子博客

        需求1:单个分类的增删改查操作

                增改查一条语句可以实现,删除需要对下级分类做处理,对下级的path做update

        需求2:查询一个分类的直属下级和所有下级

                查赵业务直属下级:select * from cart where path like '%2';

                查赵业务所有下级:select * from cart where path like '1,%';

        需求3:查询顶级分类到分类之间的路径,如:王医生路径:A经理-B业务-C代理

                 已存路径,简单方便

        需求4:查询分类是那一级别的

                根据path的长度计算分类的类别

        需求5:查询某一级的所有分类

                用mysql的length的函数,如查询所有的医生  select * from cate1 where length(path) = 5;

        需求6:移动一个分类

                需要递归,因为每一个分类的path都是他父类path加上父类的id,将分类及其所有子类的path设为其父类的path并在最后追加父类的id即可。


    总结:各方面都具有可以接受的性能,理解起来比较容易,有两点不足:1️⃣不遵守数据库范式,将列表数据直接作为字符串来存储,这导致一些操作需要在上层解析path字段的值。2️⃣字段长度是有限的,不能真正达到无限深度,并且大字段对索引不利。如果不在乎范式,分类层级也远达不到字段长度的限制,这个方案是可行的。


四:前序遍历树

    这是一种在数据库里存储一棵树的解决方案。它的思想不是直接存储父节点的id,而是以前序遍历中的顺序来判断分类直接的关系。

时光疯子博客

            假设从根节点开始以前序遍历的方式依次访问这棵树中的节点,最开始的节点(“Food”)第一个被访问,将它左边设为1,然后按照顺序到了第二个阶段“Fruit”,给它的左边写上2,每访问一个节点数字就递增,访问到叶节点后返回,在返回的过程中将访问过的节点右边写也写上数字。这样,在遍历中给每个节点的左边和右边都写上了数字。最后,我们回到了根节点“Food”在右边写上18。下面是标上了数字的树,同时把遍历的顺序用箭头标出来了。

            称这些数字为左值和右值(如,“Meat”的左值是12,右值是17),这些数字包含了节点之间的关系。因为“Red”有3和6两个值,所以,它是有拥有1-18值的“Food”节点的后续。同样的,可以推断所有左值大于2并且右值小于11的节点,都是有2-11的“Fruit” 节点的后续。这样,树的结构就通过左值和右值储存下来了。

        这里就不贴表结构了,这种方式不如前面两种直观。效率上,查询全部下级的需求被很好的解决,而直属下级也可以通过添加父节点id的parent字段来解决。

        因为左值更大右值更小的节点是下级节点,反之左值更小、右值更大的就是上级,故需求3:查询两个分类直接的所有分类可以通过左右值作为条件来解决,同样是一次查询即可。

        添加新分类和删除分类需要修改在前序遍历中所有在指定节点之后的节点,甚至包括非父子节点。而移动分类也是如此,这个特性就非常不友好,在数据量大的情况下,改动一下可是很要命的。

        查询某一级的所有分类,和查询分类是哪一级的,这两个需求无法解决,只能通过parent字段想第一种方式一样慢慢遍历。

综上所述,对于本项目而言,它还不如第二种,所以这个很复杂的方案也得否决掉。



五:基于ClosureTable的无限级分类存储

        在路径列表的设计中,关键字段path的本质是存储了两种信息:一是所有上级分类的id,二是从顶级分类到每个父分类的距离。 所以另增一张表,含有三个字段:一个是本分类的所有上级的id,一个是本分类的id,再加上该分类到每个上级分类的距离。这样这张表就能够起到与path字段相同的作用,而且还不违反数据库范式,最关键的是它不存在字段长度的限制!

        ClosureTable以一张表存储节点之间的关系、其中包含了任何两个有关系(上下级)节点的关联信息

时光疯子博客

  • ancestor 祖先:上级节点的id

  • descendant 子代:下级节点的id

  • distance 距离:子代到祖先中间隔了几级

        1:子节点查询

            查询id为5的节点的直属子节点:SELECT descendant FROM CategoryTree WHERE ancestor=5 AND distance=1

            查询所有子节点:SELECT descendant FROM CategoryTree WHERE ancestor=5 AND distance>0

        2:路径查询

            1️⃣查询由根节点到id为10的节点之间的所有节点,按照层级由小到大排序

                SELECT ancestor FROM CategoryTree WHERE descendant=10 ORDER BY distance DESC

            2️⃣查询id为10的节点(含)到id为3的节点(不含)之间的所有节点,按照层级由小到大排序

                SELECT ancestor FROM CategoryTree WHERE descendant=10 AND 

                distance<(SELECT distance FROM CategoryTree WHERE descendant=10 AND ancestor=3) 

                ORDER BY distance DESC

        3:查询节点所在的层级

            1️⃣查询id为5的节点是第几级的

                SELECT distance FROM CategoryTree WHERE descendant=5 AND ancestor=0

            2️⃣查询id为5的节点是id为10的节点往下第几级

                SELECT distance FROM CategoryTree WHERE descendant=5 AND ancestor=10

        4:查询某一层的所有节点

            查所有在第三层的节点:SELECT descendant FROM CategoryTree WHERE ancestor=0 AND distance=3

        5:插入

                当一个节点插入到某个父节点下方时,它将具有与父节点相似的路径,然后再加上一个自身连接即可。

                所以插入操作需要两条语句,第一条复制父节点的所有记录,并把这些记录的distance加一,因为子节点到每个上级节点的距离都比它的父节点多一。当然descendant也要改成自己的


            如:把id为10的节点插入到id为5的节点下方

                    INSERT INTO CategoryTree(ancestor,descendant,distance) (SELECT ancestor,10,distance+1 FROM CategoryTree WHERE descendant=5)

                然后就是加入自身连接的记录。

                    INSERT INTO CategoryTree(ancestor,descendant,distance) VALUES(10,10,0)

        6:移动

            节点的移动没有很好的解决方法,因为新位置所在的深度、路径都可能不一样,这就导致移动操作不是仅靠UPDATE语句能完成的,这里选择删除+插入实现移动。

            另外,在有子树的情况下,上级节点的移动还将导致下级节点的路径改变,所以移动上级节点之后还需要修复下级节点的记录,这就需要递归所有下级节点。


        总结:这种既有查询简单、效率高等优点,也符合数据库设计范式,而且是真正的无限级分类的设计。本方案的写入操作虽然需要递归,但相比于前序遍历树效率仍高出许多,并且在本博客系统中分类不会频繁修改。可见对于在关系数据库中存储一棵树的需求,ClosureTable是一种比较完美的解决方案,如果查询和修改都比较频繁的建议使用路径列表的方式




白俊遥博客
请先登录后发表评论
  • 最新评论
  • 总共0条评论