文章目录
部门常见需求
PM添加以下需求:
(1)查找某个部门的某个level的父级部门
(2)找所有的子孙部门节点
(3)找所有的子孙部门节点数量
(4)判断是否是叶子部门节点
(5)新增部门
(6)删除部门
效率问题描述
通常部门parent结构设计
通常部门结构如下图
一般采用parent_id的方式去表达上级部门,因此表结构设计如下:
CREATE TABLE `ts_dept_parent_struct` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门id',
`parent_id` int(11) NOT NULL DEFAULT '0' COMMENT '上级部门id,没有上级部门为0',
`dept_name` varchar(100) NOT NULL COMMENT '部门名称',
`level` int(4) NOT NULL DEFAULT '1' COMMENT '部门层级,顶级部门为1',
`is_leaf` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否是叶子部门 0-不是 1-是',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COMMENT='部门表-parent_id结构'
各节点数据值如下:
id | parent_id | dept_name | level | is_leaf |
---|---|---|---|---|
1 | 0 | 董事长 | 1 | 0 |
2 | 1 | 总经理 | 2 | 0 |
3 | 2 | 产品部 | 3 | 0 |
4 | 2 | 行政总监 | 3 | 0 |
5 | 3 | 设计 | 4 | 1 |
6 | 3 | 研发部门 | 4 | 0 |
7 | 4 | 财务部 | 4 | 0 |
8 | 4 | 行政部 | 4 | 1 |
9 | 7 | 出纳 | 5 | 1 |
10 | 7 | 会计 | 5 | 1 |
11 | 6 | 架构部 | 5 | 1 |
12 | 6 | 码农部 | 5 | 1 |
13 | 2 | 外包产品部 | 3 | 0 |
14 | 13 | 生日部 | 4 | 1 |
15 | 13 | 摸鱼部 | 4 | 0 |
16 | 15 | 闲聊部 | 5 | 1 |
17 | 15 | 奶茶部 | 5 | 1 |
此处提供SQL插入语句以便测试:
INSERT INTO test.ts_dept_parent_struct (id,parent_id,dept_name,`level`,is_leaf) VALUES
(1,0,'董事长',1,0),
(2,1,'总经理',2,0),
(3,2,'产品部',3,0),
(4,2,'行政总监',3,0),
(5,3,'设计',4,1),
(6,3,'研发部门',4,0),
(7,4,'财务部',4,0),
(8,4,'行政部',4,1),
(9,7,'出纳',5,1),
(10,7,'会计',5,1);
INSERT INTO test.ts_dept_parent_struct (id,parent_id,dept_name,`level`,is_leaf) VALUES
(11,6,'架构部',5,1),
(12,6,'码农部',5,1),
(13,2,'外包产品部',3,0),
(14,13,'生日部',4,1),
(15,13,'摸鱼部',4,0),
(16,15,'闲聊部',5,1),
(17,15,'奶茶部',5,1);
这样的方式看起来很直观,但是如果数据量一大或者部门的层级很多,就会影响效率。
实现方法及缺点
首先来说说以上需求的实现方法:
找所有的子孙部门节点
找所有的子孙部门,根据以下sql查询,然后收集所有的deptId作为parentid,递归查询,直到集合为空
select tdps.* from ts_dept_parent_struct tdps where tdps.parent_id = #{deptId}
查找某个部门的某个level的父级部门
找某个level级别的父级部门,根据以下sql查询后,判断level是否等于请求中指定的level,是则返回,否则继续往上查询
select tdps.* from ts_dept_parent_struct tdps where tdps.id = #{parentId}
找所有的子孙部门节点数量
这个可以根据上面查所有子孙部门的SQL查找,然后收集所有的deptId作为parentid,再次查询,直到集合为空,统计所有子部门的数量
select tdps.* from ts_dept_parent_struct tdps where tdps.parent_id = #{deptId}
判断是否是叶子节点
如果不添加冗余字段,那么就要进行额外的查询,判断是否有parent_id=当前部门节点的部门,没有则是叶子节点,有则不是叶子节点。
这里我更加推荐是添加一个is_leaf的字段,标志当前部门是否是叶子节点,虽然是维护起来就要多一个字段,但是却少了一次查询,我觉得值得
新增部门/删除部门
比较简单,新增直接插入即可,不过删除部门还要改动一下parentId才行。这里要看产品怎么设计。
遍历方法
表结构设计
CREATE TABLE `ts_dept_point_struct` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门id',
`dept_name` varchar(100) NOT NULL COMMENT '部门名称',
`lft` int(11) NOT NULL COMMENT '部门左值',
`rgt` int(11) NOT NULL COMMENT '部门右值',
`level` int(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='部门表-指针结构';
设计图如下图:
或许看上去就会比较复杂,可以先按线条序号走一遍,其实就是先序遍历树(我相信大家都接触过),
同时,节点上去掉了parent_id,使用左值和右值代替,按照上面的走法,从1开始,给每个节点加上左值,然后遇到叶子节点的话,就给右值赋值,然后再遍历兄弟节点,兄弟节点遍历完再回到上级节点给右值赋值。
遍历完后,各节点数据值如下:
id | dept_name | lft | rgt | level |
---|---|---|---|---|
1 | 董事长 | 1 | 34 | 1 |
2 | 总经理 | 2 | 33 | 2 |
3 | 自营产品部 | 3 | 12 | 3 |
4 | 行政总监 | 13 | 22 | 3 |
5 | 设计部 | 4 | 5 | 4 |
6 | 研发部门 | 6 | 11 | 4 |
7 | 架构部 | 7 | 8 | 5 |
8 | 码农部 | 9 | 10 | 5 |
9 | 财务部 | 14 | 19 | 4 |
10 | 行政部 | 20 | 21 | 4 |
11 | 出纳 | 15 | 16 | 5 |
12 | 会计 | 17 | 18 | 5 |
13 | 外包产品部 | 23 | 32 | 3 |
14 | 生日部 | 24 | 25 | 4 |
15 | 摸鱼部 | 26 | 31 | 4 |
16 | 闲聊部 | 27 | 28 | 5 |
17 | 奶茶部 | 29 | 30 | 5 |
这里提供一下插入语句方便测试:
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(1, '董事长', 1, 34, 1);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(2, '总经理', 2, 33, 2);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(3, '自营产品部', 3, 12, 3);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(4, '行政总监', 13, 22, 3);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(5, '设计部', 4, 5, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(6, '研发部门', 6, 11, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(7, '架构部', 7, 8, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(8, '码农部', 9, 10, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(9, '财务部', 14, 19, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(10, '行政部', 20, 21, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(11, '出纳', 15, 16, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(12, '会计', 17, 18, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(13, '外包产品部', 23, 32, 3);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(14, '生日部', 24, 25, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(15, '摸鱼部', 26, 31, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(16, '闲聊部', 27, 28, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(17, '奶茶部', 29, 30, 5);
实现方法
查找某个部门的某个level的父级部门
可以通过左值和右值来判断,某个节点的所有父级部门,它的左值必定是小于当前部门的左值,它的右值必定是大于当前部门的右值。
因此,可通过这个sql来查找到所有的父级部门:
select tdps.*
from test.ts_dept_point_struct tdps
where tdps.lft < #{dept.lft}
and tdps.rgt > #{dept.rgt}
order by tdps.lft asc;
我这里根据左值排序的意思,其实就是从上往下给父级部门排序,这里我认为也可以用level进行排序的,而且每级只有一个部门(不可能有两个父亲部门)
然后遍历查找出来的部门,找到指定的level即可,或者在sql中指定level,相比于之前parent的结构,可以避免了递归查询。
找所有的子孙部门节点/找所有的子孙部门节点数量
可以通过左值和右值来判断,某个节点的所有子孙部门,它的左值必定是大于当前部门的左值,它的右值必定是小于当前部门的右值。
select tdps.*
from test.ts_dept_point_struct tdps
where tdps.lft > #{dept.lft}
and tdps.rgt < #{dept.rgt}
这个查询只需要一次查询,就可以查出所有子部门
找所有的子孙部门节点数量
判断数量不必select count(*)查询子孙部门,可以用公式来计算
子孙部门数量 = (右值-左值-1)/2,可以找上面的图验证一下,这里省略了一个sql查询
例如:
摸鱼部 下有两个部门
(31-26 -1) /2= 2
判断是否是叶子部门节点
可以看上面的图分析,叶子部门的特点,就是左值+1 = 右值
例如:
奶茶部 : 30-29 = 1 是叶子部门
摸鱼部: 31-26 >1 不是叶子部门
新增部门
对于新增单个部门,例如在摸鱼部下增加一个零食部,新增请求里面必须要指定父级部门是哪个
select tdps.*
from test.ts_dept_point_struct tdps where id = #{parentId}
然后,得到父级部门的右值parentRgt,基于右值给新部门赋值,新部门左值为parentRgt,右值为parentRgt+1,然后所有大于等于parentRgt的值都要+2
-- 将插入的后续边缘的节点左右数+2
UPDATE ts_dept_point_struct SET lft=lft+2 WHERE lft > #{lft};
UPDATE ts_dept_point_struct SET rgt=rgt+2 WHERE rgt >= #{rgt};
-- 插入数据
INSERT INTO department(name,lft,rgt,level) VALUES('零食部',#{lft},#{rgt},level);
以上操作需要放在一个事务里面
删除部门
做这个之前,先确定方案,到底删除部门时,如果不是叶子部门时怎么办?
第一种方法:全部移除,我觉得现实生活中是比较合理的,例如在删除摸鱼部,下面的闲聊部和奶茶部也一并删除。
第二种方法:把后面的子部门移到上上级。例如在删除摸鱼部,下面的闲聊部和奶茶部放到外包产品部下。
如果是第二种方案的话,这种指针式的就不适用,只能删掉叶子部门,所以还是建议你用parent方式。因为怎么控制level的修改是一个问题,我现在还没有想到方案。
第一种方式:可以先移除这个部门下的所有子孙部门,例如删除摸鱼部:
# 查询所有子孙部门
select tdps.*
from test.ts_dept_point_struct tdps
where tdps.lft > 26
and tdps.rgt < 31
#根据主键删除
对删除节点的后续边缘节点减2操作
/**更新节点值**/
UPDATE test.ts_dept_point_struct SET lft=lft-2 WHERE lft > 26;
UPDATE test.ts_dept_point_struct SET rgt=rgt-2 WHERE rgt > 31;
/*删除节点*/
DELETE FROM department WHERE lft=26 AND rgt=31;
个人感觉
我当前所在的公司,还是使用parent结构的,因为数据量不大,效率问题还没有显示出来。
这个方案也不是最优的,缺点是不太直观,维护起来有点麻烦,还是要看产品怎么设计界面的。
如果增加删除一个不部门,是那种弹窗式的话,那么可以考虑一下先序遍历树的方案。如果是全量推到后台,那我建议还是用回parent结构比较稳妥
引用
参考文章:https://mp.weixin.qq.com/s/GDmrIwo89WVfDyAWYSBWQA