mysql 树形结构表设计与优化

短命女 2022-09-12 03:47 319阅读 0赞

## 前言 ##

在诸多的管理类，办公类等系统中，树形结构展示随处可见，以“部门”或"机构"来说，接触过的同学应该都知道，最终展示到页面的效果就是层级结构的那种，下图随机列举了一个部门的树型结构展示图

![在这里插入图片描述][41c6fcb1bc064649ac9445823b2716d5.png]

## 设计考虑因素 ##

**1、表结构设计**

稍稍有点开发和表结构设计经验的同学，设计出这样一张表，应该很容易，只需要在depart表中，添加一个pid/字段即可满足要求，参考下表：

CREATE TABLE `depart` (
      `depart_id` varchar(32) NOT NULL COMMENT '部门ID',
      `pid` varchar(32) NOT NULL DEFAULT '0' COMMENT '组织父ID',
      `name` varchar(64) NOT NULL COMMENT '部门名称',
      `description` varchar(512) DEFAULT NULL COMMENT '部门描述',
      `code` varchar(64) DEFAULT NULL COMMENT '部门编码',
      PRIMARY KEY (`depart_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**2、业务设计**

上图是一个通用的树状结构示意图，适合大多数的业务场景，以此为例，如果“部门”不是单独的存在，与部门相关的业务主要包括下面几点：

*  部门是否单独的存在，可能与部门存在直接关联的业务，比如：部门下面可以关联用户
 *  部门的效果展示，加载部门树形列表时候，是动态加载呢？还是一次性返回呢？
 *  和部门增删改查相关的业务
 *  替他关联部门的业务，即部门是被关联的业务对象

**3、性能考虑**

*  全量加载不可取，没有办法的最后考虑，层级一旦过深，数据量一旦过大，查询性能将会成为噩梦
 *  动态加载效果比较好，服务端开销和压力相对全量加载小很多，但动态加载的问题也很明显，层级一旦深了，用户操作体验不佳
 *  层级结构的功能，对数据导入即excel导入功能实现的编码复杂度增加

关于第一点，第二点再做几点额外的补充，全量加载和动态加载的实现都可以，在小编历经的项目或产品中都有见到，实在是要看产品的设计和客户的要求，因为全量和动态加载的不同设计也会带来与之相对结果

举例来说，全量加载的好处是，数据一次性的返回给页面，页面做了渲染之后存缓存，后续再次加载的时候速度非常快，同时，类似下面这种搜索效率就非常高，因为不需要与接口交互啊  
![在这里插入图片描述][2bb58abfd595420fb3584ec211763ff1.png]

但问题也随之而来了，部门数据不是一成不变的，增删改的操作也是常有的事情，设计成全量加载，意味着初次查询的时候，一旦数据量超大，层级非常深，假如页面还需要渲染部门下关联的用户数据时，这个对服务端的压力就非常大了，稍有经验的同学应该能大概想到这个服务端的返回数据结构了吧  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP56CB5Yac5Y-U5Y-U_size_17_color_FFFFFF_t_70_g_se_x_16]  
下面给出初步的实现思路

function(currentDepart_id){
      
      1、查找当前部门
    
          DB ......
    
      2、查找当前部门的子级部门
          DB ......
      
      3、以当前部门的子部门列表为根基进行遍历，递归查询，包装返回数据
          DB ......
    
    }

从以上的代码实现来看，数据量上去之后，预估查询将会成为性能瓶颈，而且在小编的项目开发中，做过类似的测试，3个层级，每层1000条数据（未计算部门下关联用户的数据加载），在4核16G的服务器上（CPU性能普通），完成一次全量的数据加载平均在3秒左右，这个对于B端的产品，这种设计加上这种延时，用户还能接受（1000个部门，这种数据量是比较大的了）

上面分析到，全量加载的性能瓶颈在于数据库的IO，试想，查询的时候，从顶级节点或者某个节点算起，数据量越大，层级越深，查询的次数就越多，IO的开销自然就越大

解决的办法是什么呢？实践过程中，有2个经验可以参考：

*  设计合理的缓存存储结构
 *  改进表结构

关于第一点，也是大家容易想到的，但如何设计才比较合理呢？以下面的这张图为例，我们可以考虑以非叶子节点为key，而叶子节点下面的集合为value，将所有的value存入一个redis的集合中，这种考虑来源于实际业务中，用户的需求验证，即真正那些具有实际意义的部门或机构数据都分布在叶子节点上面  
![在这里插入图片描述][f4eb8c6a98ef42b49c94dddbbf7e26e6.png]  
如此一来，编码的实现上面，也许可以改造成下面这样，

1、部门新增
    
    functiob add(params){
        
        1、depart入库
            DB ......
        2、判断当前的depart的层级，是否叶子节点（是否即将成为叶子节点）
    
          if(叶子节点){
            3、寻找上级节点ID，并查询redis中的key
    
            4、取出上级key对应的缓存集合，加入当前新增的part_id
    
          } else {
            5、创建一个新的key，即一个新的缓存空集合，等待后续数据添加（也可以不创建）
          }
    
    }
    
    
    2、删除部门
    
    functiob delete(params){
        
        1、depart自身的删除
            DB ......
    
        2、如果当前部门下存在子集部门，是否需要一起删除子部门（结合自身的产品业务）
            DB......
    
            获取所有的非叶子节点集合
    
        3、假设第二步成立，那么还需要以当前部门节点创建的key，并取出key中的list集合，一起进行删掉
    
            Redis操作
            拿到第二步中的所有非叶子节点集合，组装成key，循环遍历删除key（内存型操作，性能不是问题，也可以做异步）
    
    }

全量加载结合redis是突破性能瓶颈的关键步骤，但从上面的实现上看，从编码的复杂性上确实有所提升，而且对开发者的编码要求有一定高度，但这种实现之后，可以说很大程度上将会提升查询的性能

**优化查询性能的第二种考虑，表结构的改造**

不少同学有疑问，表结构的改造对于性能影响能有多大呢？说出来可能你不信，模拟数据压测的时候，不采用改造后的实现，利用 5个层级的部门，每个部门1000的数据量（我指的是每个层级的每个部门数据量都是1000，大家可以计算下数据总量），每个部门下关联了500个用户，这样的数据量的最后表现是5分钟左右

看来，数据量上去了之后，查询压力确实很大，利用改造后的设计和测试效果，最终同样的数据表现，平均在15~20秒之间，这个直接是10倍多的提升，或许在我说出答案之前，也有不少同学用过，但是没有真正体会到它的妙处

在本文开始的表结构基础上面，我们加一个path字段，这样改造后的表如下：

这个path字段意义重大，通常的表现是，从第一个层级开始，每个层级假设最多可容纳10000个部门，这样第一层的数据大概长这样， 00001 ，00002，00003 … 往上依次累加，而第二层级，假如我们在00002这个部门下新增第二层级的部门时，数据表现大概长这样， 00002/00001 , 00002/00002 , 00002/00003 …往上依次累加

那么更深的层级，我就算不举例想必大家也能自行列举出后面的结构来

**这么做有什么好处呢？**

我们知道,mysql是支持正则表达式函数的，还有就是like，试想，我们要想一次性查询出从某个层级开始下面的所有的层级数据时，假如没有path这个字段，会怎么做呢？很明显，就是上文所说的通过递归了

但是有了path字段之后，我们可以直接利用mysql的正则表达式函数,，仍以上面的数据为例，通过下面的这两种sql，一次性的可以将一级部门（测试）这条数据的所有子集数据全部查出来，这样一来，可以说大大减少了与数据库的交互次数  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP56CB5Yac5Y-U5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16]  
此种实现容易踩坑，或者实际操作中比较容易出问题的地方在路径规则的生成上面，通常需要提前自定义一个函数，专门用户生成path，只要确保生成的path字段数据准确无误，这种实现从优化查询的性能提升上面，是很大的突破，小编所在的开发项目中，使用的便是这种方式

function generatePath(pid){
        
      1、pid是否为顶级
    
      2、获取父级部门的depart
    
      3、列举出父级部门下与当前即将新增的部门同级的所有path字段
    
      4、取出第三步中的path最大值
    
      5、根据第四步的path最大值生成新的path
    
    }

另外一个比较难啃的业务是，以path字段的设计之后，做部门数据的excel导入时，这个path的处理仍然是个比较复杂的实现点，这一点留待大家思考。

以上探讨了全量加载下，从业务实现到代码设计层面的优化 ， 以及表结构设计层面优化的2个方面做了比较深入的探讨，而动态加载的实现，相对来说，可以说在上面这两种实现方案的基础上，稍作引用即可，难度更小

**总结下来，这里推荐一个关于这种带有层级结构形状的业务设计上的最佳实践，**

*  表结构上，采用path字段
 *  数据加载上，尽量使用动态加载
 *  如果部门（层级结构的业务）变动不大，可以考虑引入缓存，具体实践参考本文上面所说

以上是本篇的全部内容，最后感谢观看！

[41c6fcb1bc064649ac9445823b2716d5.png]: /images/20220828/a817bb23b7244471b9cd50400a644626.png
[2bb58abfd595420fb3584ec211763ff1.png]: /images/20220828/3e69b154104446cba29c9a470523b7bc.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP56CB5Yac5Y-U5Y-U_size_17_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/c8c5d642f9f14bd0983d4e2014504bf1.png
[f4eb8c6a98ef42b49c94dddbbf7e26e6.png]: /images/20220828/0a7b451948d24b8a9e4a4d89d7683262.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP56CB5Yac5Y-U5Y-U_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/c9a3dd47ccf546ce935ab75f2bf271fb.png