数据模型问题：当具备多个一对多关联副表的时候，如何多条件查询

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 503 天前的主题，其中的信息可能已经有所发展或是发生改变。

数据模型和查询业务

+--------+   1        N  +------+
|        |   +-------->  | Sub1 |
|        |               +------+
|        |
|        |   1        N  +------+
|  Main  |   +-------->  | Sub2 |
|        |               +------+
|        |
|        |   1        N  +------+
|        |   +-------->  | SubN |
+--------+               +------+

查询业务： 以动态条件筛选数据，条件即有 Mian 的字段，又有 Sub1 、Sub2 、SubN 的字段。

当前查询逻辑

select MAIN.ID, MAIN.OTHER
from MAIN
where MAIN.MAIN_CONDITON_FIELD=?
    and MAIN.ID in (select SUB_1.MAIN_ID from SUB_1 WHERE SUB_1.SUB_1_CONDITION_FIELD = ?)
    and MAIN.ID in (select SUB_2.MAIN_ID from SUB_2 WHERE SUB_2.SUB_2_CONDITION_FIELD = ?)
    and MAIN.ID in (select SUB_3.MAIN_ID from SUB_3 WHERE SUB_3.SUB_3_CONDITION_FIELD = ?)

注 1： 副表字段再另行做查询补上，此处不讨论 1+N 查询。

注 2： 该查询逻辑，当副表条件筛选出来的 MAIN_ID 多的时候，性能严重下降

问

即使不考虑性能问题，只是在数据模型设计上，当前查询逻辑是否合理
如果当前查询逻辑不合理，正确的逻辑是什么
如果单纯从 SQL 层面上考虑，如何做性能优化
如果再结合整体设计，代码可维护性上的考虑，如何做性能优化

注：
数据模型不可变更；
查询业务不可变更；
不要求单 SQL ；
可以考虑增加专用与查询的辅助表或缓存表；
单纯从程序层面的缓存技术也可以，但要是能长期维护的，即不考虑临时黑科技。

main

查询

main_id

select

9 条回复 • 2023-07-07 14:09:55 +08:00

Ib3b

2023-07-07 10:36:21 +08:00

join 查询不行吗

nothingistrue

2023-07-07 10:47:14 +08:00

这是多个一对多，不是单个一对多，没法 join

joApioVVx4M4X6Rf

2023-07-07 12:14:19 +08:00

1.数据写入模型没问题，但是查询模型有问题
2. 大致看还是挺合理的，要是数据量太多就不合理了，要考虑写入模型和查询模型分开
3. 单纯从 sql 考虑，无非就是建索引，另外不知道你用的什么数据库版本
4. 拍脑袋了两个方案，优化查询性能
方案一：搞个 sub 查询条件缓存表，所有查询条件字段都搞里头，查询表 join 主表
方案二：分开拼接 sql ，sub1sub2 sub3 条件不一定同时出现吧？那就动态拼 sql ，sub join main

happyxhw101

2023-07-07 12:37:45 +08:00

(none)> select * from m
-> left join b on m.b_id = b.id
-> left join c on m.c_id = c.id
-> left join d on m.d_id = d.id
-> where b.xx = 'xx' and c.yy = 'yy' and d.zz = 'zz';

这种不行嘛？还是我理解不对！

justfindu

2023-07-07 13:26:42 +08:00

laravel 里面的 whereHas 是这样表述的:

select * from Main where Main.main_condition_field =? and exists(select * from Sub_1 where Sub_1.main_id = Main.id and ******)

justfindu

2023-07-07 13:36:10 +08:00

刚查了下
1 、如果查询的两个表大小相当，那么用 in 和 exists 差别不大。
2 、如果两个表中一个表大，另一个是表小，那么 IN 适合于外表大而子查询表小的情况。
3 、如果两个表中一个表大，另一个是表小，EXISTS 适合于外表小而子查询表大的情况。

yule111222

2023-07-07 14:01:28 +08:00

Main 和 Sub 表都追加上同一个范围约束字段，比如 orgId,userId 之类的，代表他们属于某个范围。
所有的索引都以这个范围字段作为左前缀弄联合索引，所有的查询条件都带上这个字段，可以缩小 in 的范围

nothingistrue

2023-07-07 14:08:48 +08:00

@v2exblog #3 读写分开是想过的，这个难点是查询模型不知道怎么设计。
@happyxhw101 #4 主副表是一对多，不是一对一的关系，不能 join 的。你这种 join 方式，如果 b.xx 这些不是唯一的，会导致查询里面有重复记录的。

nothingistrue

2023-07-07 14:09:55 +08:00

@justfindu 这种方式是一种选择，感谢。不过还是没解决根本性问题。