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

[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 之类的中间件；

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

[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 做