各位大佬，想请教一下这个关于查询用户拥有权限的数据的问题，感觉这个挺常见的需求
业务场景：
假设一个业务表，数据量在几千万级。 需要为这个表提供一个列表展示页，要求按创建时间倒序分页。 主要是权限问题导致查询慢： 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 之类的中间件；
想问一下实际大家是怎么处理的？
 |
1
evan1 PRO 给群组加个层级 ID 字段，把群组的层级 ID 拼起来。
查的时候 like 一下层级 ID 。 |
 |
2
lying500 OP 主要是 project 表的 or 导致索引不好设计，实际没法利用索引
 |
 |
3
kxg3030 6 小时 55 分钟前
使用 nestedset 做无限分类或者像 1L 那样 加个 path 字段 like 一下 但是最好还是我说的那个方案 一开始就要那么设计才行
|
 |
4
chanlk 6 小时 54 分钟前
一个不成熟的想法，如果最顶层的 group 不多，那么按照每个顶层 group 对应一个 project 表进行分表。
|
|
6
lying500 OP @JYii 都有，问了 AI 也说用 union all 这样，但是写到代码里会不会比较奇怪，主要是想了解大家有没有类似的场景，感觉挺场景的，想学习一下怎么设计比较好
|
 |
7
vvtf 6 小时 40 分钟前
如果确定是 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; ``` |
|
8
lying500 OP @vvtf 嗯确实，但是我们这这还有一个忘记说了，就是我们用户量少，但是单个用户产生的数据多，就算 in (user_id)这一步有索引，由于需要 created_at 排序，这里还是会 filesort ，还是很慢
 |
 |
9
pony2335 6 小时 33 分钟前
增加脏字段，比如 path 之类的，记录多级组织 id 用；隔开。
|
|
10
vvtf 6 小时 33 分钟前
那就是业务上取舍了,
加上时间区间, 分区. |
 |
11
sagnitude 6 小时 18 分钟前
数据到底属于群组还是用户？你这 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 缓存里），层级结构总不至于有几千个成员吧 |
 |
12
dake0805 6 小时 14 分钟前
给方案 1 投一票，应用层在查 project 之前和之后，来做额外处理，db 只支持 id 简单查询就好了。userid/groupid 和创建时间各单独加个索引
|
|
13
lying500 OP @sagnitude 分两个是考虑用户可能离开了某个群组，但是希望他能看到自己的数据
(user_id = xxx OR group_id in (xxx,xxx,xxx)) 是可以的，只是说这里 SQL 查起来很慢，不知道怎么优化 |
 |
14
litchinn 6 小时 12 分钟前
使用 like path% 和 in (user_ids)哪个好得做测试，影响条件很多，
排序给 created_at 也加上索引试试 |
 |
15
DavZhn 6 小时 6 分钟前
能不能把过滤逻辑放到 es 做，关键字段比如 created_at 、user_id 、xxx ，经过业务过滤出需要的结果集 id ，然后返回 id[]，直接库里根据 id 查数据，然后返回？
|
 |
16
xmh51 6 小时 4 分钟前
这种需求应该使用列存储数据库或者 es 解决。
|
 |
17
RandomJoke 6 小时 3 分钟前
要么按时间分区呗，要么冗余一张近一年的表，这种翻页列表数据一般不会翻到很后面，真翻到了可以接受稍微慢点。
|
|
18
xmh51 6 小时 3 分钟前
mysql 的查询非常依赖索引，多条件查询对 mysql 是弱势，不能穷举所有的检索条件组合。
|
 |
19
issakchill 5 小时 46 分钟前
有同样的场景 来蹲个解决方案
|
 |
20
ranfenghs 5 小时 22 分钟前
同意使用 es 做
|
 |
21
micean 4 小时 52 分钟前
权限并不是大问题
添加时间索引+业务必须提供时间区间 数据量大是绕不过的 |
 |
22
snail88xin 3 小时 23 分钟前
已经将近 5 年没干过后端了, 发表下自己的拙见, 大体逻辑就是空间换时间:
1. 复杂 sql 拆分成单条高性能 sql(加索引), 代码里边做数据合并处理, 这样做的好处是, 多条 sql 可同时查询且都是毫秒级, 权限逻辑、过滤规则都能写在业务层逻辑中, 便于维护, 但这样做会出现分页精度问题 2. 分页精度问题处理, 游标分页 3. 用户群组关系做缓存 |
 |
23
vencent00 3 小时 14 分钟前
给个邪修方案吧，位图存储 group_id,有多少最底层 group 就给多少字节，字节为 1 表示拥有这个 group 权限
|
 |
24
soap0X 3 小时 6 分钟前 via Android
自己能把握项目的话建议试着用 olap 库处理，增加个实践。我这有个老系统 2kw 数据量周周挂，我把握不了项目被迫在优化 sql 加限制
|
 |
25
masterclock 2 小时 58 分钟前
参考 zanzibar ？但也未必需要，性能在可接受范围内大概就先用着简单的方式
OpenFGA 、SpiceDB 等都还没有 materialize 的实现，自己搞有点搞不定，也未必需要 |
 |
26
lbprivateacc 2 小时 0 分钟前
倾向于方案 1 ，列表分页的话，一般只会看前几页吧，用 union all ，然后两个子 sql 都加上时间倒排+分页大小限制，最后 union all 再来一个倒排+分页（或者应用层处理）
|
 |
27
cloudzhou 1 小时 45 分钟前
尽量避免 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 |
 |
28
siweipancc 1 小时 39 分钟前
created_at 是不稳定排序,你还是要加 ID , 或者 ID 是 ULID UUID7 按 ID 排;
群组如果是闭包表 left join 消耗大内存就完事了 |
Select Language