这是一个创建于 841 天前的主题，其中的信息可能已经有所发展或是发生改变。
喜欢写 sql ，所以不想用 hibernate 类似的完全托管访问数据库的框架，一般用 mybatis 之类的将查询结果转换成 entity 。
这种情况下如果类之间有关联关系，在设计 api 的时候大家一般如何做呢。
比如 User 关联 Department ，在 User 里面是用 departmentId 还是 Department 实体，
如果用 id 属性，在一些情况下需要 department 表的信息，就需要二次查询。
用实体 Department 的情况下（查询 User 时增加 left join department ），是很方便访问关联表的数据，但是会遇到下面两个问题：
- 如果
Department又关联了Organization属性呢？Organization又关联了其他的 Entity 呢， 这样一个查询User表的数据的 sql 会关联到很多其他的表，而且很多的 sql 语句都是重复使用的，比如查询Department和Organization的列字段和直接查询Department实体时的字段是一样的，如果 department 表新增加了字段，这几处的 sql 都要改。
2.
第 1 条附言 · 2022-07-28 21:34:06 +08:00
看了这么多的回复，统一回复一下：
看了大家的回复，还是很有启发的，有很多的问题之前都没有考虑到，对以后设计上很有帮助。
任何设计都有优缺点，只能说适合当前项目才是最优的，没有银弹。
目前参加的项目是大企业内部的业务系统，内部员工使用的，基本不会有大的并发及海量的数据。发帖的目的是想问怎么设计出比较“优雅”的api，来适应不断变化的业务需求。至于我比喻的那个例子并不说那样的数据库设计是最优的，满足什么范式之类的，因为数据库里基本都会存在A->B->C这种关联关系吧。
如果都使用id，不用join的话，感觉会多出很多的代码，因为每一个列表页面的查询基本上都不止是一个表的数据。另外我还有一个疑问，如果entity里面都是id的话，当同时传两个或者更多的entity到方法里，这样还的需要创建另外一个类来关联这两个entity，感觉又会多出很多代码。
我目前项目里面的代码方案是entity包含另外一个entity，按需去join，比如 User 包含 Department ，因为业务就是用户必须在一个部门下。很多列表页面都要显示数据的创建人及部门，每个Entity都对应一个query类，如User对应UserQuery类，在UserQuery有queryDepartment(boolean)方法，当为true时，就会去join，这样user.getDepartment()就会获取department表的信息了，如果为false，就只使用user表的departmentid字段了，这样user.getDepartment()就只有id字段是有值的，其他的字段都是空。
大家如果有看到设计比较好的开源项目的话也可以分享哈。
62 条回复 • 2022-07-29 16:03:12 +08:00
 |
1
mazhiyuan 2022-07-28 09:57:28 +08:00
为啥有这表设计呢
|
 |
2
zhao1014 2022-07-28 09:58:35 +08:00
跟数据库保持一致
|
 |
4
zmal 2022-07-28 10:00:32 +08:00
不要在 sql 里写业务逻辑。
|
|
7
zhao1014 2022-07-28 10:03:53 +08:00
@hahaFck 你需要什么就查什么啊，你也说了有些时候需要其他表的信息，那就查第二次，总比你每次需要 user 时都做一次 left join 好吧。
|
 |
8
hcven 2022-07-28 10:04:36 +08:00
和数据库保持一致，不要在数据库中 join ，可以先查 user 列表，再获取 user 列表中所有的 departmentId ，departmentId 列表去数据库查，在业务层组装 UserBO
|
 |
9
youngforever 2022-07-28 10:06:19 +08:00
和数据库保持一致+1 ，返回给前端的另开 VO ，能不 join 的尽量不要 join ，根据 departmentId 再查一遍
|
 |
10
zhangleshiye 2022-07-28 10:08:21 +08:00
@hahaFck 提取业务服务把， 比如用户服务 权限服务, 服务内部可以用多表连接 ，服务之间组装业务对象就在代码层组装。22223
|
 |
11
james2013 2022-07-28 10:08:46 +08:00
有封装 mybatis 的框架,比如 Mapper,MyBatis-Plus,单表查询不需要写 sql,直接用 java 代码查出来,有复杂的 sql 再写到 xml 里
|
|
12
zhao1014 2022-07-28 10:12:02 +08:00
假如 User 的字段是 username ，Department 的字段是 departmentName ，前端需要在一次请求中返回 username 和 departmentName ，你需要做的是再封装一个 vo ，字段是 username 和 departmentName ，把 vo 作为返回值，而不是在 User 里面塞一个 Department 对象。
至于怎么为 Vo 填充数据，一句话能不要 left join 就不要 left join 。 |
 |
13
Suddoo 2022-07-28 10:14:25 +08:00 via iPhone
@james2013 那还不如写 SQL ，他那个封装的看不到生成的 SQL 语句，后期业务逻辑变更，改动量更大更麻烦
|
 |
14
LeegoYih 2022-07-28 10:16:55 +08:00
实体应该是独立的，不应该直接关联另一个实体，不同实体应该划分好边界，也就是 domain 的概念，通过关联表间接关联。
> user > department > user_department_relational |
 |
16
hahaFck OP @youngforever 这样的话，有的时候一次能查询出来的数据就要 2-3 次去查数据库了，不知道哪个总时间用的更少。
|
|
17
hahaFck OP @LeegoYih 这样感觉也不是很好吧，如果我一个方法需要 user ，department ，organization 三个参数，那就应该建一个 user_department_ organization_relational 类了，这样的话这种类会很多。
|
 |
18
sundev 2022-07-28 10:46:17 +08:00
性能的话肯定是直接 SQL 好，但是论扩展性、可复用性的话还是不如多次取值好的
|
 |
19
wxf666 2022-07-28 10:47:50 +08:00  3
怎么感觉有的在说数据库的设计，有的在说 API 的设计……
|
|
20
zhao1014 2022-07-28 10:50:59 +08:00
@hahaFck 假如都像你这样写，且不说业务与数据库混淆的问题，你想拿一个 User ，却需要联表查询其他的表，按你说的，“如果我一个方法需要 user ，department ，organization”，难道要在 User 里塞 department 和 organization ？假如需要的参数更多呢？岂不是要联十几张表？那你这查询得花多长时间？你这么做就算是你只想要 User 不想要其他得东西，你也得联表查。
底层得东西越简单，上层越容易复用，你如果为了一个方法去造一个融合怪，那岂不是会为了更多得方法造更多得融合怪？结果就是冗余一堆垃圾代码，重复得东西到处都是。另外为什么宁愿多次单表也不要联表得问题网上解答多的是。 |
 |
21
Vaspike 2022-07-28 10:58:54 +08:00 1
数据库怎么设计请参考三大范式...
业务代码的话如果不是统计类的需求大概率不需要直接 join,通过关联 key 流式查询相关联实体比较好,用哪个查哪个 |
 |
23
KevinBlandy 2022-07-28 11:21:54 +08:00
我一直都是用 jpa ，但是不用 oop 建模，各种关联，延迟加载，更新策略。。。坑太多了。你就按照一张表，一个对象。再配合 QueryDsl 。简直不要太棒了。
我写过一篇关于 querydsl 案例文章，你也许可以参考一下：[https://springboot.io/t/topic/4424]( https://springboot.io/t/topic/4424) |
 |
24
NoKey 2022-07-28 11:29:50 +08:00
按照常规的开发模式，所有表独立，不加关联关系，不加外键，各表之间使用关键字关联，关联关系在代码逻辑体现，查询方式就是 2 种，一种是 join 多表查询；一种是代码里一个一个查出来，再组合
|
|
25
LeegoYih 2022-07-28 11:44:41 +08:00
@hahaFck 如果设计成 user 表中加 department_id ，表示 user 对 department 有依赖，显然，业务上他们之间并不是这种关系。而且一个 user 可能会对应多个 department 。
至于你说的 organization ，和 user 不是直接关系，而是通过 department 间接关联，那么应该是 organization_department_relational 。 |
 |
26
OnTheBerlinWall 2022-07-28 11:46:08 +08:00
|
 |
27
fkdog 2022-07-28 11:58:18 +08:00 4
项目规模不大的话，数据库直接 join 什么其实还是很好用的。
瞅了一眼楼上为什么好多不推荐 join 的感觉都没说到点上。 我自己的经验就是： 1.大项目需要分表分库，join 在分库分表的情况下处理起来很麻烦。 2.缓存问题。比如某个订单列表里包含有用户昵称，这个昵称因为是通过 join 查询因此也被缓存起来了，假如某个用户改了昵称，那么很容易会忽略掉这些缓存造成不一致的问题。 3.用业务代码替代数据库 join ，也能方便日后服务化。比如用户系统和订单系统以后做拆分了，vo 需要同时展示订单信息+用户信息，那么就可以根据关联的 id 调用其他服务来获取数据，减少重构成本。（ UserDAO.findById -> UserRemoteService->findById ） 4.一般情况下，join 是不会直接 select *所有字段的，一般都是按需选字段。不同的业务需要取不同字段的话就要写不同的 join 了，还不如直接 id 直查关联数据，按需选取。 至于你说的多表关联，一般是要求不超过三张表 join 的。 像商品-订单-用户，就是典型的用户-商品多对多关系，订单可以看作是关联表并且额外维护了其他的信息。 但是像角色-角色权限关联表-权限，也是一个多对多关系，但是由于角色-权限本身是紧密耦合的，不会像商品用户那样可以独立作为一个系统，他们的关联表也不需要维护额外的信息，所以这种场景肯定还是 join 更合适。 |
 |
29
superbai 2022-07-28 13:19:07 +08:00 4
看了下大家说的好像都是 C 端的？如果是内部运营系统或者 B 端的场景，在列表页就是有跨实体筛选、排序或者分页的需求，这种情况下不连表能实现吗？
|
 |
30
tvp100 2022-07-28 13:27:59 +08:00
为什么不能 join 多个表呢？如果我根据一个 List 的 User 的 ID 去查另一个东西，那查询不是要查十几次？而我 join 只需要一条 sql 一次查询
|
 |
31
yinzhili 2022-07-28 13:50:07 +08:00
都用 mybatis 了，那就是尽量使用 join 来查询你要的字段数据了
|
 |
32
TWorldIsNButThis 2022-07-28 13:50:54 +08:00 via iPhone
表小而查询复杂的联表很正常
数据再多的话就上 es |
 |
33
timethinker 2022-07-28 13:52:25 +08:00
思路放开一点，将查询和命令分离开来，这样会清晰很多，表结构也不用为了查询而影响到 OLTP 的处理。基本的思路就是，你不知道将来会有什么查询的需求，在设计表结构的时候，应当根据实际的业务操作来进行设计，这样你在建模 Entity 的时候，就是跟数据库的表结构相对一致的。
我也建议用 JPA 来进行 OLTP 业务类型的建模和操作，用 MyBatis 来进行自定义查询相关的处理，是的，你不用只使用一种技术或者框架，虽然好像你会写更多的代码，但实际上这样做了以后你会发现思想负担会减少很多，也更有信心进行修改和重构。 |
 |
34
dqzcwxb 2022-07-28 14:17:23 +08:00 1
又是一个想把业务逻辑写 sql 里而且疯狂 join 的新开发,这条路每个人都得走一遍哈哈哈哈哈哈哈
|
|
35
zmal 2022-07-28 15:20:07 +08:00
当你在 3 张表以上的 join 加一堆 where...on 时，已经是在 SQL 里写业务了。
联查最大的问题是制约了数据库设计，一般认为 mysql 单表几千万是上限，超过该规模考虑分表。联查的索引不易设计，很容易在业务规模还未扩大时就整出慢 SQL 。 所以三大范式在实际业务中很少遵守，往往是弱耦合 + 宽表的设计思路。数据规模再扩大就上列式存储，不会尝试在关系型数据库死磕。 当然一切都不是绝对的，如果你的 User 表几千条，Department 几十条，Organization 几十条，想怎么 join 都行。但还是建议在代码层解决问题，除非涉及到性能问题。 |
 |
36
notwaste 2022-07-28 15:40:12 +08:00
软件设计没有银弹，抛开场景来说没有意义，性能上没有问题的情况下两次或多次查询也没有什么问题，即使避开多次查询进行连表 sql 也一样会出现问题，若性能存在问题的情况下按照描述在数据库设计时就应考虑是否反范式设计存在适当冗余
|
|
37
LeegoYih 2022-07-28 16:38:19 +08:00
@wxf666
根据 OP 的业务场景，一般是如此。应该考虑到「用户系统」和「组织架构」的隔离性，即「用户系统」可以拆出来单独使用，「组织架构」也可以拆出来可以与其他的「用户系统」配合使用，而不需要改造代码（或者说只需要微调）。 如果是商品（ SPU ）和商品库存（ SKU ）这种业务，则应该用直接关联，如：在 sku 表中存 spu_id ，因为他们业务上本身就是强关联的，而且一般是处于同一个 domain 中。 @hahaFck 不推荐使用 join ，是因为互联网业务的很多场景下都可能无法使用 join ，例如： - 微服务：物理隔离 - 分库分表：数据库实例不同 - 数据中台：数据存在中台，只能通过接口方案 - 更换数据库（多数据库）：去 Oracle 化、MySQL -> TiDB 、RBD -> KVDB 等，不同数据库语法可能不兼容。 如果没有使用 join ，只需要替换 Repository 层即可，如果使用的是 JPA ，甚至可以不改代码。 |
 |
38
l0ve1o24 2022-07-28 16:50:14 +08:00
请教，不用 join 的话，是遍历去查相关数据，还是 in 查相关数据再拼凑回去？
|
 |
39
freebird1994 2022-07-28 16:53:14 +08:00 via Android 2
@l0ve1o24 in 可以走索引，遍历 io 开销大得多
|
|
41
wxf666 2022-07-28 21:02:36 +08:00
@LeegoYih 数据库新手说下看法，有不对的请指正
这是另外一种什么范式吗？ 以前学过的数据库范式说， 1. 每个字段都必须是不可再分割的原子数据项，不能是集合、数组、另一行记录等 2. 其他字段必须完全依赖整个主键组（如：用户部门表『「用户 ID ，部门 ID 」，用户名，省份名』，用户名只依赖用户 ID ，省份名不依赖任何其他字段，故不符） 3. 非主键不依赖其他非主键（如：学生表『「学号」，学院 ID ，学院名称』，学院名称依赖学院 ID ，故不符） 然而实际出于性能、分库分表难 join 等原因，总会冗余一些字段 回到你的回复，你说 用户表『「用户 ID 」，……，部门 ID 』不妥， 1. 部门 ID 业务不依赖 用户 ID （是说违反第二范式？但感觉实际上是依赖的？用户所属部门？） 2. 或用户可所属多个部门（部门 IDs ？但这违反第一范式？） 总的看起来，感觉你像是在说违反了数据库范式 所以，若有性能（你 join 多一个表，肯定性能差些）、分库分表难 join 等问题，你会妥协地加入『部门 ID 』？ |
 |
42
chenshun00 2022-07-28 21:57:47 +08:00
这个其实是领域的问题，OP 描述的很容易就让人陷入到面向表结构开发，需求才开始过，表结构就想好了，我要建三个表，巴拉巴拉。
|
 |
43
ychost 2022-07-28 22:03:39 +08:00
ModeFirst or DBFirst
|
 |
44
oneisall8955 2022-07-28 22:50:15 +08:00 via Android
怎么感觉你说的是 jpa ？
|
|
45
LeegoYih 2022-07-29 00:01:57 +08:00
@wxf666 按我的理解，数据库范式只是一种建议，而且其过于学院派，并不适合所有实际场景。
尤其是互联网行业，更倾向于实用派，例如 MySQL ，其作用仅为储存数据，计算基本上都放在应用服务，有些公司的设计规范中，甚至连外键都禁止使用。 |
 |
46
shiyanfei5 2022-07-29 02:21:27 +08:00
假设有表 TA ，其有三个字段 A ，B ，C ，表中有 100W 数据。有表 TB ，其有三个字段 C D E ，表中有 800W 数据。
TA 和 TB 通过 C 字段关联后具有业务含义，TA 的 C 列是 TB 的 C 列的数据子集。 需求： 返回表 TA 的数据并实现分页（页大小 10 条）， 且筛选 条件为 D 字段=某个值（可能为热点数据）。。 这个不走 sql 的 exists 或 join 语法怎么做呢。。 难点： 1.筛选 条件为 D 字段=某个值 为 热点数据，其对应的 C 可能非常多 2.因为要做分页，但是这是对结果分页 |
|
47
shiyanfei5 2022-07-29 02:21:56 +08:00
谁来看下这种场景不走 sql 咋解决呢
|
|
48
wxf666 2022-07-29 03:49:13 +08:00
|
 |
49
siweipancc 2022-07-29 09:03:34 +08:00 via iPhone
尽信书不如无书，网友同理
|
 |
50
fox0001 2022-07-29 09:51:26 +08:00 via Android
首先，规范是规范，一切从实际出发吧。
一般，对于变化不大的关系，并且是一对多的，我会用外键这种方式。例如用户表会加上部门 ID ，做查询会方便很多，至于外键限制，一般不加。 entity 设计一般对应数据库，除了枚举类做转换。主要是清晰，新增或修改只关注自己的数据。 至于查询两次的问题，都是要查两次的，只是手工查，还是框架自动查。例如用户表含有部门 ID 列，想要获得部门信息，肯定要再查一次数据库。 |
|
51
fox0001 2022-07-29 09:56:15 +08:00 via Android
不好意思，补充一下，如果使用 join ，把用户信息和部门信息一并查出来，这种我没用过。因为我用 Hibernate 比较多。
我们偏向于根据 id 做查询，也方便做缓存。比如你的例子，用户信息与部门信息，部门的信息一般变化小，可以缓存起来，不用每次都查数据库。 |
|
52
LeegoYih 2022-07-29 10:29:26 +08:00
@wxf666 不会，多 join 一张表会有一点开销，但是不至于差；分库分表不允许使用 join 。
示例：user 、department 、user_department_rel 各 100 万条，共 300 万条数据，两张表联查和三张表联查的开销几乎无差别。 两张表联查结果： test> select * from user u join department d on u.department_id = d.id where u.id = 100000 [2022-07-29 10:24:46] 1 row retrieved starting from 1 in 116 ms (execution: 76 ms, fetching: 40 ms) test> select * from user u join department d on u.department_id = d.id where u.id > 100000 limit 10000,10 [2022-07-29 10:24:47] 10 rows retrieved starting from 1 in 127 ms (execution: 89 ms, fetching: 38 ms) 三张表联查结果： test> select * from user u join user_department_rel udr on u.id = udr.user_id join department d on d.id = udr.department_id where u.id = 100000 [2022-07-29 10:24:47] 1 row retrieved starting from 1 in 125 ms (execution: 76 ms, fetching: 49 ms) test> select * from user u join user_department_rel udr on u.id = udr.user_id join department d on d.id = udr.department_id where u.id > 100000 limit 10000,10 [2022-07-29 10:24:48] 10 rows retrieved starting from 1 in 138 ms (execution: 96 ms, fetching: 42 ms) |
 |
53
levon 2022-07-29 10:30:12 +08:00
判断楼主是从.net 转过来的
|
 |
54
micean 2022-07-29 10:39:37 +08:00
类似这样，解决好 N+1 即可
select( User.user_id, User.department_id, select(Department.department_id, Department.department_name) .from(Department) .where(Department.department_id.equals(User.department_id)) .as("department") ).from(User) 返回同样的 List<User>，不需要去写这个 Query 那个 Query 我自己弄了一个用来写后台查询很方便 |
 |
55
uselessVisitor 2022-07-29 10:40:24 +08:00
什么业务需要一次性全都关联在一个对象里？按理来说详情页是单独查
|
 |
56
nothingistrue 2022-07-29 12:34:40 +08:00
带关联的 Entity 是纯对象层面的设计，不用 hibernate 这种全 ORM ，你能设计个蛋蛋。你下面的关联问题，光用 mybatis 压根就不会有。光用 mybatis 你压根设计不出“User 关联 Department”。当你基于 mybatis 加了好多框架层代码把 “User 关联 Department” 设计出来的时候，那么恭喜你，你造了个新的 Hibernate 。
|
|
57
wxf666 2022-07-29 12:37:12 +08:00
@LeegoYih 这数据量小，可能都缓存至内存了也说不定
有试过每张表的 B+树三 /四 /五层高（视行记录长度的不同，可能分别能容纳几千万、几百亿、几十兆行记录）时，俩 /仨表 join 的耗时差异吗？ |
|
58
nothingistrue 2022-07-29 12:42:18 +08:00
当你回到全 ORM 的基础上的时候，才会有 User 是关联 Department ，还是关联 departmentId 的选择。然而这个并没有选择，对象层面没有外键关联，只能是 User 关联 Department ，User.departmentId 只是 User 的一个属性，不是 Department 的外键。
这个实际上能做得选择是，User 要不要关联 Department ，这个选择取决于业务和性能，而不是技术细节。业务上 User 跟 Department 是必须在一个事务当中的，那么就得关联。业务上二者没有紧密的事务结合，或者说虽然是紧密结合但是因为性能不得不放弃事务一致性而改为最终一致性，那么就不能关联，这时候可能需要 User.departmentId 来维持业务（而非技术）上的弱关联关系。 |
|
59
tvp100 2022-07-29 15:21:38 +08:00
请问如果多次查询，不用 JOIN ，怎么分页？
|
 |
61
chihiro2014 2022-07-29 15:54:30 +08:00
jpa 有个东西叫投影
|
 |
62
ccppgo 2022-07-29 16:03:12 +08:00
只用过 mybatis
表设计和 entity 都是存 id 比如 user 里面有 department_id 用 join 查出来之后可以直接使用 ResultMap 返回 VO 的， 不一定要返回 entity, 配合 association 功能， 效果很强大 https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#%E9%AB%98%E7%BA%A7%E7%BB%93%E6%9E%9C%E6%98%A0%E5%B0%84 |
Select Language