搜索
  • 有几千万的数据量的层级权限下的列表展示页查询应该怎么设计？
  • 发布于 2小时前
  • 20 热度
    12 评论
各位大佬，想请教一下这个关于查询用户拥有权限的数据的问题，感觉这个挺常见的需求
业务场景：

假设一个业务表，数据量在几千万级。 需要为这个表提供一个列表展示页，要求按创建时间倒序分页。 主要是权限问题导致查询慢： 

1 、用户可以查看自己创建的数据。 

2 、用户可以查看自己所属群组的数据。 

3 、群组的权限是可继承的、层级的：如果一个用户属于某个上级群组，那么他自动拥有查看其所有下级、下下级...群组内数据的权限。


问题： 

如果权限简单，比如只看自己的数据，查询非常简单： WHERE user_id = ? ORDER BY create_time DESC LIMIT N 这种查询用索引就好解决。

但如果加入群组权限，查询的逻辑就变成了： SELECT * FROM a_large_table WHERE user_id = ? OR group_id IN (用户所属群组以及所有下级群组的 ID 列表) ORDER BY create_time DESC LIMIT N

这个查询就比较慢了 比如假定结构是这样：

查询就变成了

SELECT *
FROM project
         JOIN `group` ON project.group_id = `group`.id
WHERE `group`.id IN (SELECT 用户关联的群组及其子群组 id)
   OR user_id = 20
ORDER BY project.created_at DESC
LIMIT 10;
这时候 (group_id, user_id, created_at) 也不好使;
问了 AI ，说了几个方案： 

1 、应用层聚合/union user_id 和 group_id 的，建两个索引； 

2 、冗余一张 用户能访问数据的表，直接查这个表； 

3 、引入 es 之类的中间件；
想问一下实际大家是怎么处理的？

用户评论
  • Pigeon
  • 如果有个超大表和其他小表进行关联查询，建议先在小表上处理出来大表索引可以搜到的数据，然后再单独查大表。大数据最忌讳 JOIN 来 JOIN 去的。
  • 2025/10/14 11:42:00 [ 0 ] [ 0 ] 回复
  • APAC
  • 时间加索引，然后 group 的逻辑可以用右 like ，这样能用索引覆盖到,最好业务上也加一下限制？比如很久以前的历史项目就从业务表清理掉？
  • 2025/10/14 11:39:00 [ 0 ] [ 0 ] 回复
  • 弄潮儿
  • 尽量避免 or
    在 group_id 可控的情况下（假设数量不大），为每个用户设定默认 group_id （和 user_id 一样即可）
    举个例子：

    设定 user_id 范围 [0-2**40] 也就是用户最大数量：1099511627776
    剩下的 64 - 40 = 24 ，group_id 范围 [2**40-2**64]
    40/24 自己定义，前 40 bit 给 user_id ，后 24 bit 给 group_id
    这样
    WHERE user_id = ? OR group_id IN (用户所属群组以及所有下级群组的 ID 列表)
    转化为：
    WHERE group_id IN (用户所属群组以及所有下级群组的 ID 列表，user_id)
    user_id 同时也是每个用户默认的 group_id
  • 2025/10/14 11:36:00 [ 0 ] [ 0 ] 回复
  • 原木风
  • 已经将近 5 年没干过后端了, 发表下自己的拙见, 大体逻辑就是空间换时间:
    1. 复杂 sql 拆分成单条高性能 sql(加索引), 代码里边做数据合并处理, 这样做的好处是, 多条 sql 可同时查询且都是毫秒级, 权限逻辑、过滤规则都能写在业务层逻辑中, 便于维护, 但这样做会出现分页精度问题
    2. 分页精度问题处理, 游标分页
    3. 用户群组关系做缓存
  • 2025/10/14 11:33:00 [ 0 ] [ 0 ] 回复
  • 张蜚
  • 能不能把过滤逻辑放到 es 做，关键字段比如 created_at 、user_id 、xxx ，经过业务过滤出需要的结果集 id ，然后返回 id[]，直接库里根据 id 查数据，然后返回？
  • 2025/10/14 11:11:00 [ 0 ] [ 0 ] 回复
  • 时光浅巷
  • 数据到底属于群组还是用户？你这 group_id 要跟随 user_group 变吗?这里 project.group_id 如果实际意义是 project.user_id 指向的用户的当前 group_id 的话，这属于冗余字段了!如果你能保证 project.group_id 是可信任的，直接 (user_id = xxx OR group_id in (xxx,xxx,xxx))，提前算好 group_id 列表就好了（可以放 redis 缓存里），层级结构总不至于有几千个成员吧!
  • 2025/10/14 9:19:00 [ 0 ] [ 0 ] 回复
  • 煮酒慰风尘
  • 如果确定是 or 导致的索引的话其实很好解决,

    ``` 

    SELECT *
FROM project

WHERE user_id in (
-- 这里直接把用户组以及下级组的用户查询出来
select uid from user_group where `group_id` IN ('','',...)

)
ORDER BY project.created_at DESC
LIMIT 10;

    ```
  • 2025/10/14 9:13:00 [ 0 ] [ 0 ] 回复
可能感兴趣的话题