多表 join 如何优化？

[CUMTProgrammer]

看阿里 JAVA 开发规范，禁止 3 表以上 join。

1. [强制] 超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致； 多表关联查询时， 保证被关联的字段需要有索引。

比如我有一张订单表，肯定只存相关的 id，比如用户 id，支付方式 id 等等。如果我想把这些转化为中文输出，我就得 join 多表。

select o.order_id,u.user_name... from order o left join user u on o.user_id=u.user_id...

如果我有很多属性，那么 join 肯定超过 3 个了，不符合上诉规范。请问如何优化。

[TommyLemon]

@abcbuzhiming
对的，抛开业务弹技术，抛开场景谈实现都是耍牛氓。
适合自己的技术才是好的技术，应用层 JOIN 能做到通用性能（小部分针对性场景确实可以更优）比优化了几十年的 MySQL 引擎都好，那也不是一般人能做到的

[TommyLemon]

这个问题知乎也有讨论
https://www.zhihu.com/question/56236190/answer/153450286

[TommyLemon]

对一致性要求不是很高的，可以通过冗余字段来减少 JOIN

[nooper]

sql explain

[TommyLemon]

有足够的开发资源的话，可以自己应用层 JOIN，先查主表，取出每条记录的关联字段 refKey，
组合成一个数组 refKeys,然后查副表时用 key IN($refKeys) 来优化，代价就是原本 SQL JOIN 几行解决的，
放到代码内要几十行，而且还多了数据库连接的开销，没加缓存的话还真不一定性能有 SQL JOIN 好。
好在 APIJSON 已经提供了自动化的 APP JOIN，就是自动用以上原理来优化，前端只要传一个 join 键值对就好了，
后端不用写任何代码，全自动解析为 userId IN($userIds) 这种 SQL。

数组关键词，可自定义
④ "join":"&/Table0/key0@,</Table1/key1@"
多表连接方式：
"<" - LEFT JOIN
">" - RIGHT JOIN
"&" - INNER JOIN
"|" - FULL JOIN
"!" - OUTTER JOIN
"@" - APP JOIN
其中 @ APP JOIN 为应用层连表，会从已查出的主表里取得所有副表 key@ 关联的主表内的 refKey 作为一个数组 refKeys: [value0, value1...]，然后把原来副表 count 次查询 key=$refKey 的 SQL 用 key IN($refKeys) 的方式合并为一条 SQL 来优化性能；
其它 JOIN 都是 SQL JOIN，具体功能和 MySQL,PostgreSQL 等数据库的 JOIN 一一对应，
"ViceTable":{ "key@:".../MainTable/refKey" }
会对应生成
MainTable ... JOIN ViceTable ON ViceTable.key=MainTable.refKey。

https://github.com/TommyLemon/APIJSON/blob/master/Document.md#3.2


Node.ts, Python, Go, PHP, Java, C# 多种语言实现，Android, iOS, JavaScript 多端 Demo，
还有 各种文档、视频教程、自动化接口管理工具 等。

码云最有价值开源项目：后端接口和文档自动化，前端(客户端) 定制返回 JSON 的数据和结构！
GitHub 右上角点 Star 支持下吧 ^_^
https://github.com/TommyLemon/APIJSON

[yoshiyuki]

我在业务中的实践，倾向于用缓存系统构建个 hash map，替代掉一部分需要 join 的表
当然这个方案好不好使和业务相关

[abcbuzhiming]

@qiyuey OLTP 一般不会需要 JOIN，OLAP 才是 JOIN 的大头

[huijiewei]

@abcbuzhiming ORM 里面是是消除 JOIN 的方式，不是替代 JOIN 的。至于你说的 Google 用 JOIN，我也用啊。但是你要明白，这个是不得不用，考虑表大小，逻辑复杂度等等情况下的妥协，而不是我真 TMD 牛逼，搞了 20 个 JOIN 的 SQL 语句出来

[TommyLemon]

@abcbuzhiming @huijiewei 对的，OLAP 统计报表经常写一屏以上的 SQL，各种 JOIN 和 子查询，写完几天后自己都看不懂了，各种加注释。。。

[TommyLemon]

@TommyLemon 话说 APIJSON 每一层数组对象，里面默认限制最多 5 个表对象 getMaxObjectCount，
也就是说 JOIN 也最多能写 4 个（副表），很好地做了这个事情。
可以看下 #25 楼的回答

[TommyLemon]

@TommyLemon 每一层最多 4 个 JOIN，最多 5 层，自由组合和嵌套

[lofbat]

要怎么处理楼上的说的很清楚了，我不多做补充。
我要说的是，任何规范任何原则，都有自己的适用范围，很多人在自己公司按一套标准做事，就觉得这一套一定是对的，就要让别人都照着这样来，这是不对的。
先搞明白“为什么要这么做”，就明白“要不要做，要怎么做了”。

[abcbuzhiming]

@huijiewei 你没有消除 Join 好吗，你实际是让 ORM 做了 Join 的工作，如果 Join 不复杂，ORM hold 的住，看上去这个工作就被屏蔽了，程序员不需要管。问题是有的时候 join 很复杂，ORM 根本 hold 不住，这就是为啥国内提到 hibernate 这种重 ORM 总会说它是容易入门精通困难，复杂查询性能下降的可怕

[abcbuzhiming]

@TommyLemon 我之前就看到你这个东西了，其实我很看好你的思想，但是，我得说，为啥你就不愿意专注做成一个 ORM 工具+DSL 呢，你非要从控制层一路吃到数据层，对业务的侵入太重了，其实介于原生 SQL 和重度 ORM 之间的中等程度 ORM 工具+领域设计语言在所有语言里都是重要需求。如果你能从 API 层退出来，回到数据层里去我，想用上你这个工具的人会多很多的。比如我很想用你的数据层，但是我一看连 controller 层都侵入了我就没法用了

[TommyLemon]

@abcbuzhiming 感谢关注。
APIJSON 首先是一个 JSON 网络传输协议，然后根据这个协议做了核心地 ORM 库实现，叫 APIJSONORM
https://github.com/TommyLemon/APIJSON/tree/master/APIJSON-Java-Server/
它只有 Request JSON -> 解析成 SQL 并交给子类执行（完全可以自定义） -> 封装 Response JSON 这样的 ORM 功能，再加上 基于角色的自动化权限控制、自动化的数据和结构校验、访问数量和层级限制。
至于控制层，那是基于 SpringBoot 做的 APIJSONBoot 和 基于 JFinal 做的 APIJSONFinal 两个 Demo。
完全可以只用 APIJSONORM，搭配其它库 /自己的工程 来使用，
APIJSONORM 只依赖 fastjson，才 47 个 Java 类，非常轻量。
所以是不存在对业务入侵重的，压根就没入侵哈，Demo 可以不用，也可以拿来改下再用。
开源不易，可以点 Star 支持下哦 ^_^
https://github.com/TommyLemon/APIJSON/

[akira]

冗余数据啊 你们从来不冗余数据的么？

[akira]

举个栗子 ，一个订单表可能核心数据 5 列，但是做订单信息展示的时候 可能需要从另外几个表获取 10 列数据，那我们针对这个订单信息展示的需求，另外做一个表，所有需要的数据都冗余进来，这样 这个表在展示的时候 就不怎么需要连表了

join 操作说到底还是时间复杂度的问题，所有的算法的选择最终无非就是时间复杂度和空间复杂度的转换

[2bin]

歪个楼，难得遇见校友

[reus]

要怪就怪 MySQL 旧版本的傻逼计划器吧，换了 oracle、PostgreSQL 等等稍微靠谱的，用 JOIN 肯定比你分几次去查询快。

现在阿里的 oceanbase 之类肯定也有靠谱的计划器，还遵守这种傻叉规则的，只有一个原因就是还用 MySQL 旧版。MySQL 8 都没这么蠢了。

[theOneMe]

我感觉大部分人都是生搬硬套，说什么阿里规范什么的，网上圣经的，完全不管自己的业务场景，本来 join 很简单或者设计冗余表，本来自己的系统多少人用都知道，就非得多次查询，写这么多垃圾代码，最怕不懂装懂。见过这种人，很烦。