权限查询遇到部门树,whereIn过多, 架构+sql 优化

权限查询遇到部门树,whereIn过多, 架构+sql 优化

最近遇到一个比较头疼的事情,企微部门越来越多,层级越来越深,当遇到权限查询:获取我能看到哪些部门,找部门子级,whereIn 的 value 会非常多,某个人的部门较高,变会出现如下面的语句

SELECT 
	a.staff_id 
FROM  ww_staff_dept_union as a  // 员工部门关系表,一个员工有多个部门,单独抽了一张表
	left join ww_staff as s on s.id = a.staff_id   // 员工表
WHERE 
	s.delete_time = 0 and 
	a.delete_time = 0 and 
	a.corp_id = 'ww*******27' 
	and a.dept_id in (722,2432,4889,2686,305...,...,...352,5349,5356,5357)

a表中有上百万数据,whereIn的查询就是个灾难,因为 in了太多的部门
怎么解决
开始加了缓存,查询过一次,就入缓存,但发现作用不大,部门查询一直在变,缓存的意义非常有限
然后开始考虑其它种方案,发现企微后台的查询比较有趣,就看对方返回的结果 ,脑洞开了一下.
发现有个dept_path 的东西,里面存了每个部门的路径,也就是所有上级
深思了一下,发现非常有借鉴意义

如果把每个部门向上的路径存起来(也就是存上级),我如果要查询某个部门下的所有员工,那我只需要找到路径经过查询的部门的部门即可.有点绕口
直接上 sql 设计

CREATE TABLE `ww_dept_tree_ww*******` (   // 再次优化,分表
  `id` varchar(128) NOT NULL,
  `dept_id` int(11) NOT NULL DEFAULT '0',
  `dept_name` varchar(255) NOT NULL DEFAULT '',
  `parentid` int(11) NOT NULL DEFAULT '0',
  `sort` int(11) NOT NULL DEFAULT '0',
  `create_time` int(11) NOT NULL DEFAULT '0',
  `update_time` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_1` (`dept_id`,`parentid`) USING BTREE,
  KEY `index_parentid` (`parentid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

每个部门存起来的数据会像是这样

sort值越大,部门越顶级
原本的 sql即可改动为这样

SELECT
	a.staff_id,a.userid,s.`name`
FROM
	ww_staff_dept_union AS a
	LEFT JOIN ww_dept_tree_ww******* as dtree on a.dept_id = dtree.dept_id
	LEFT JOIN ww_staff AS s ON s.id = a.staff_id 
WHERE
	s.delete_time = 0 
	AND a.delete_time = 0 
	AND a.corp_id = 'ww********' 
	// 下面的 or dtree.dept_id是为了解决有员工在100部门下,如果不加,则查不出在100部门下的员工
	AND (dtree.parentid  = 100 or dtree.dept_id = 100)  
	// 必须 group,否则重复
	GROUP BY a.staff_id

THE END