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

[nothingistrue]

数据模型和查询业务

+--------+   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 多的时候，性能严重下降

问

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

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

[Ib3b]

join 查询不行吗

[nothingistrue]

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

[0x0208v0]

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

[happyxhw101]

(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]

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]

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

[yule111222]

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

[nothingistrue]

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

[nothingistrue]

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