几千万的数据量，层级权限下的列表展示页查询应该怎么设计？

[lying500]

各位大佬，想请教一下这个关于查询用户拥有权限的数据的问题，感觉这个挺常见的需求

业务场景：

假设一个业务表，数据量在几千万级。 需要为这个表提供一个列表展示页，要求按创建时间倒序分页。 主要是权限问题导致查询慢： 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 之类的中间件；

想问一下实际大家是怎么处理的？

非常感谢大家的回复，我自己也通过 AI 找到了一些相关的解决方案，提供给大家参考：
主要是用 deepwiki 问了下 gitlab 对类似问题的解决方案

1 、deepwiki 原始提问 https://deepwiki.com/search/_baff8620-a27c-419c-a23b-615a48e7eeda 可以简单参考和追问

2 、gitlab 关于层级群组的设计，使用 冗余子级群组 + 缓存更新策略 处理： https://github.com/gitlabhq/gitlabhq/blob/efaaea34/doc/development/database/group_hierarchy_optimization.md

3 、gitlab 关于 in 后 order by 的排序问题处理，通过把大的“多个群组下数据排序”，拆分成 N 次快而小的“索引查找”，来高效地找出前 N 条记录 https://github.com/gitlabhq/gitlabhq/blob/efaaea34/doc/development/database/efficient_in_operator_queries.md

有兴趣可以阅读一下内容，感觉还是不错的，文章长可以让 AI 帮助理解一下

[evan1]

给群组加个层级 ID 字段，把群组的层级 ID 拼起来。

查的时候 like 一下层级 ID 。

[lying500]

主要是 project 表的 or 导致索引不好设计，实际没法利用索引

[kxg3030]

使用 nestedset 做无限分类或者像 1L 那样 加个 path 字段 like 一下 但是最好还是我说的那个方案 一开始就要那么设计才行

[chanlk]

一个不成熟的想法，如果最顶层的 group 不多，那么按照每个顶层 group 对应一个 project 表进行分表。

[JYii]

@lying500 #2 只是 or 的原因吗，那大可以拆成两个在 union all 。我猜还是 in 的参数太多导致的。

[lying500]

@JYii 都有，问了 AI 也说用 union all 这样，但是写到代码里会不会比较奇怪，主要是想了解大家有没有类似的场景，感觉挺场景的，想学习一下怎么设计比较好

[vvtf]

如果确定是 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;
```

[lying500]

@vvtf 嗯确实，但是我们这这还有一个忘记说了，就是我们用户量少，但是单个用户产生的数据多，就算 in (user_id)这一步有索引，由于需要 created_at 排序，这里还是会 filesort ，还是很慢

[pony2335]

增加脏字段，比如 path 之类的，记录多级组织 id 用；隔开。

[vvtf]

那就是业务上取舍了,
加上时间区间, 分区.

[sagnitude]

数据到底属于群组还是用户？你这 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 缓存里），层级结构总不至于有几千个成员吧

[dake0805]

给方案 1 投一票，应用层在查 project 之前和之后，来做额外处理，db 只支持 id 简单查询就好了。userid/groupid 和创建时间各单独加个索引

[lying500]

@sagnitude 分两个是考虑用户可能离开了某个群组，但是希望他能看到自己的数据

(user_id = xxx OR group_id in (xxx,xxx,xxx)) 是可以的，只是说这里 SQL 查起来很慢，不知道怎么优化

[litchinn]

使用 like path% 和 in (user_ids)哪个好得做测试，影响条件很多，
排序给 created_at 也加上索引试试

[DavZhn]

能不能把过滤逻辑放到 es 做，关键字段比如 created_at 、user_id 、xxx ，经过业务过滤出需要的结果集 id ，然后返回 id[]，直接库里根据 id 查数据，然后返回？

[xmh51]

这种需求应该使用列存储数据库或者 es 解决。

[RandomJoke]

要么按时间分区呗，要么冗余一张近一年的表，这种翻页列表数据一般不会翻到很后面，真翻到了可以接受稍微慢点。

[xmh51]

mysql 的查询非常依赖索引，多条件查询对 mysql 是弱势，不能穷举所有的检索条件组合。

[issakchill]

有同样的场景 来蹲个解决方案

[ranfenghs]

同意使用 es 做

[micean]

权限并不是大问题
添加时间索引+业务必须提供时间区间
数据量大是绕不过的

[snail88xin]

已经将近 5 年没干过后端了, 发表下自己的拙见, 大体逻辑就是空间换时间:
1. 复杂 sql 拆分成单条高性能 sql(加索引), 代码里边做数据合并处理, 这样做的好处是, 多条 sql 可同时查询且都是毫秒级, 权限逻辑、过滤规则都能写在业务层逻辑中, 便于维护, 但这样做会出现分页精度问题
2. 分页精度问题处理, 游标分页
3. 用户群组关系做缓存

[vencent00]

给个邪修方案吧，位图存储 group_id,有多少最底层 group 就给多少字节，字节为 1 表示拥有这个 group 权限

[soap0X]

自己能把握项目的话建议试着用 olap 库处理，增加个实践。我这有个老系统 2kw 数据量周周挂，我把握不了项目被迫在优化 sql 加限制

[masterclock]

参考 zanzibar ？但也未必需要，性能在可接受范围内大概就先用着简单的方式
OpenFGA 、SpiceDB 等都还没有 materialize 的实现，自己搞有点搞不定，也未必需要

[lbprivateacc]

倾向于方案 1 ，列表分页的话，一般只会看前几页吧，用 union all ，然后两个子 sql 都加上时间倒排+分页大小限制，最后 union all 再来一个倒排+分页（或者应用层处理）

[cloudzhou]

尽量避免 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

[siweipancc]

created_at 是不稳定排序,你还是要加 ID , 或者 ID 是 ULID UUID7 按 ID 排;
群组如果是闭包表 left join 消耗大内存就完事了

[NoneUndefined]

时间加索引，然后 group 的逻辑可以用右 like ，这样能用索引覆盖到

最好业务上也加一下限制？比如很久以前的历史项目就从业务表清理掉？

[huijiewei]

如果有个超大表和其他小表进行关联查询

建议先在小表上处理出来大表索引可以搜到的数据，然后再单独查大表。

大数据最忌讳 JOIN 来 JOIN 去的。

[newtype0092]

去掉 user_id 统一成 group_id 才是正道。

@cloudzhou 这种规则维护性挺糟糕的，直接给每个用户默认新建一个 group 就好了，查询时加上自己的 group_id 就行，现在的逻辑都不用动。

[MIUIOS]

在业务层拆分 sql 逻辑比较好，你这 in 估计很大
或者上 es ，我之前也遇到过这个问题，我最后上了 es ，缺点是真的重。

[lizuoqiang]

我有个表结构数据和你挺像，有近 5 千万数据。
建立 user_id,created_at 联合索引 idx_user_create
查询 select * from t force index(idx_user_create) where user_id in (a,b,c...) order by created_at desc limit 100 offset 110000
耗时 400ms 左右，如果是后台应用这个时间应该可以接受

[cloudzhou]

@newtype0092 对，你说的合理，我的意思就是每个 user_id 有个 group_id ，不需要强行一样
hack 手段虽然取巧，长期看不适合，需要维护潜在规则（虽然难度不大，group id 从预定最大 user_id 起始开始就好了）

[Pythoner666666]

@lying500 7 楼的方案没有问题，索引调整一下就哈了。 建联合索引 uid,created_at ，然后查询的时候 FORCE INDEX (uid_created_at)

[chaoschick]

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

你这个例子里面的 user_id 是多余的条件吧 因为 group_id 后面已经跟了 用户所属群组了
如果某个记录是用户自己创建的 那么这条记录里的 user_id 与 group_id 就是从当前用户上获取的吧