多表 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 个了，不符合上诉规范。请问如何优化。

[shench]

查询后合并，都是这么干的。

[chitanda]

不知道你有没有了解过 join 的工作原理。
多表 join 我就知道两个，
1 用小表做驱动表。
2 一定要用上被驱动表的索引

[chitanda]

对了，还可以把 join buffer 改大一点😂

[CRVV]

你可以直接忽略掉这一条，直接 JOIN 就好了，没什么不行的
不过，你需要知道自己写的 SQL 到底做了什么；或者如果你不用 JOIN，你也需要知道自己的多次查询再合并到底做了什么

如果你用的数据库不是 SQLite 或者 MySQL 这一类的，那么 JOIN 通常会更快

[JxQg597]

JOIN 表中的参数没有被用于 WHERE 过滤可以不用添加.
例如:你只想需要用户名称而不是过滤用户名查询,你就可以先把基本数据取出,然后通过 Java 来构建你需要的数据参数.
1.获取订单基本信息 orders
2.Java8 可以用 Stream 中的 map 方法来获取用户 ID 的列表 userIds
3.通过用户的单表查询通过 IN 用户 ID 列表的主键索引查询,来获取用户数据 users
4.通过映射用户 ID 和用户名称 userNameByUserId
5.遍历订单基本数据 orders 用 BeanUtils 等工具类 copy 成 orderDetail(类似 OrderDetail 这么取名的实体类),通过 userNameByUserId.get(order.getUserId)来获取关联的用户名 并设置进对应的 orderDetail.userName 中
6.其他同理

一点愚见..希望有大佬指点

[CUMTProgrammer]

@CRVV #4 数据库用的 mysql，阿里的规范肯定是有原因的。如果多次查询合并，我觉得开销更大，光连接的开销就肯定大于 join 了

[Raymon111111]

尽量不要做 join, 能在代码里干的代码里干

数据库的资源要比业务机器资源宝贵的多

[CUMTProgrammer]

@Raymon111111 #7 请问如何在代码里干。我要用户名称，肯定要查询用户表的。除非我先通过 userId 查询出 userName，但是我觉得这样就多了一次数据库的连接，和 join 比开销更大。希望有大佬指点指点

[abcbuzhiming]

阿里的那个开发规范是有前提的（这个前提阿里自己是不会说的，本来就是给自己的企业培养预备军），这个前提就是“阿里有另外的 OLAP （联机分析事务）解决方案”，人家大数据强的很，不需要用关系数据库（阿里的主力 MySQL ）来做分析业务，Join 之所以有时候必须存在就是因为大量的中小型企业要依赖关系数据库来做 OLAP 业务，这个时候 Join 是必不可少的，甚至有时候是没办法的。1 楼说查询后合并？嗯，说的倒是没错，不过说到底就是自己做 join，自己做 join 的话，简单的业务无所谓，复杂一点的业务，多连几张表，在来点排序，统计什么的。此时你自己实际是在实现关系数据库的连接引擎！ MySQL 的连接引擎写的是不咋地，远远的不如 Oracle 这样的商业数据库性能高，不过我觉得把大部分连自己动手实现过数据库的一部分功能的程序员按在地上摩擦还是轻松愉快的。所以，你真要统计复杂业务？你还是老老实实 join 吧。
多说一句，要相信阿里总结的规范的科学性，但是不要迷信阿里当成圣经，技术都是有场景的，你不到人家的规模，盲目照搬人家的东西，是要吃瘪的。人家有些东西（大数据处理解决方案）你是没有的。你要面对的需求（没钱没人条件下用关系数据库解决分析需求）也未必是别人需要面对的

[no1xsyzy]

@CUMTProgrammer 所以连接池……
且不说这个，JOIN 很多实在太容易是由糟糕的表设计导致的了。
这就像说如果你 for(;;) 套两层几乎必然存在流程失误，并且必然可以归约成一层。

[huijiewei]

ORM 里面都有不用 join 的实现。都 2019 年了，还用 join ？

[abcbuzhiming]

@CUMTProgrammer 人家就是叫你在代码里实现 MySQL 的连接器引擎，明白不，人家认为是个人都能写出和 MySQL 不相上下的连接算法来。哈哈哈哈

[CRVV]

@CUMTProgrammer
这是一个常见的话题，早就有很多讨论了

MySQL 的 planner 基本上只能处理简单的 SQL，稍微复杂一点的 SQL 就崩了，所以通常的建议是只写简单的 SQL
3 这个数字没什么神奇的，想一下也知道 3 是个随便定的数
另外，Java 规范凭啥要定 SQL 该怎么写，是不是在阿里巴巴写 C 代码就可以随便 JOIN 了

如果需要保证的是每个表上的操作都是 O(log(n)) 的查询，至于用不用 JOIN 根本不重要

[aidoudou]

@CUMTProgrammer 我的理解，7#正解
其次，如果是频繁查询，用空间换时间，把这些单独搞出来一张表（加缓存），或者现在完全可以接受冗余字段
非频繁查询，7#这样在代码里干就够了，业务机器资源比服务器资源更廉价，多表 join 的资源也不见得比建立连接少多少

[abcbuzhiming]

@no1xsyzy 欢迎去见识一下国内的金融报表，你与其说糟糕的表设计，不如说需求方怎么就会有那么多奇葩的脑洞，搞出那么多奇葩的查询条件来

@huijiewei ORM 那是自己实现的 Join 的算法啊，你以为 Join 的逻辑消失了吗？连 google 都有业务不得不用 MySQL 的 Join，很多不用关系数据库 Join 的分析系统，自己实现了连接引擎。你们真觉得基于关系代数的的 Join 这么容易打发啊。你不用数据库的就麻烦你自己实现一套
我这么说，谁能彻底驱逐关系代数中的 Join，谁立马就能拿图领奖

[CRVV]

@no1xsyzy
请去学习一下关系型数据库的范式
在一个符合范式的数据库上做查询，一定会用一堆 JOIN

当然我没有说符合范式的数据库适合用在所有的地方

[micean]

电商的这些都比较简单
用户信息、支付信息、订单信息这些拆开查询没啥问题，因为变化少的信息大都会缓存起来
但是在传统应用软件中，join 四五个表都是很正常的

[qiyuey]

我在美团和阿里的感觉是，OLTP 是完全不写 JOIN 的。

[xuanbg]

SQL 优化的首要原则就是：让数据集尽可能小！不仅列数要少(不用*)，行数更要少。on 的时候可以加的条件都加上，当然，该有的索引一定要有，全表扫描是不能被接受的。

[Raymon111111]

@CUMTProgrammer 多一次链接开销并不一定大

不过还是具体看场景吧

这个楼里面已经有很多有用的讨论了

18 楼说的特别对

[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 很简单或者设计冗余表，本来自己的系统多少人用都知道，就非得多次查询，写这么多垃圾代码，最怕不懂装懂。见过这种人，很烦。

[fox0001]

@TommyLemon #35 很好奇，搞这么复杂，为什么不用 MongoDB ？取出来就是 JSON

[no1xsyzy]

@abcbuzhiming 呃…… 不知道为什么，尽管从来没听说过 OLAP，但我脑中默认应该这么处理……
我好像从听说关系型数据库就不觉得是拿来做分析的……

@CRVV 你理解我的话时把因果搞反了。好像是我的语文问题。
应该是 “少数糟糕设计的表造成了绝大多数的 JOIN ”。

[TommyLemon]

@fox0001 首先 MongoDB 使用场景比较有限，你看下数据库排名，它最辉煌的时候排第 4，
远低于前 3 的关系型数据库 Oracle, MySQL, SQL Server，后面又被关系型数据库 PostgreSQL 超过了，
所以真的做常规业务，还是关系型数据库更合适。
其次 MongoDB 虽然能用 JSON 查询，返回的也是 JSON，
但很多时候前端需要的是各种组合嵌套、且对象间有外键关联关系的数据结构，
MongoDB 给出的很难满足需求，更不用说 JOIN、子查询 等这种复杂的功能了。
还有权限，你真的打算用 MongoDB 数据库去做，而不是应用层控制？那就一套业务全都得在数据库做了。

APIJSON 目前仍然专注于实现 JSON 转 SQL 的 ORM 功能，对接关系型数据库满足大部分的业务场景，
实现完全自动化的 CRUD，不需要后端写代码，直接自动化解析，自动封装返回结果 JSON，
期间自动校验权限、数据、结构，自动防 SQL 注入，自动限流过载保护等。

为什么要用自动化接口与文档 ORM 库 APIJSON ？
前后端接口的 沟通、文档、联调 等 10 大痛点解析
https://github.com/TommyLemon/APIJSON/wiki/

[cyhulk]

@TommyLemon 真几把烦，这不是广告吗？自己去开帖子去

[cyhulk]

@fox0001 mongo 的多对多是鸡肋

[TommyLemon]

@cyhulk 解决问题的广告就是好广告，你看不惯拉黑，屏蔽就是了，何必自己还要看

[cyhulk]

@TommyLemon 怎么了，看不惯还不能 diss ？

[TommyLemon]

@cyhulk 哈哈，随你吧

[TommyLemon]

@cyhulk 已经发帖了
https://www.v2ex.com/t/559388/

[TommyLemon]

@TommyLemon 我去，最后多了 / 就 404 了

[troywinter]

你这是 OLAP 吧，MySQL 只适合做 OLTP，OLAP 应该用另外的专业数据库来做，比如 Hive，这种 join 需求说明你们的架构有很大问题，不要在 OLTP 系统上做 OLAP。。。

[zh81]

@akira 请问这种冗余表的话一般是怎么做数据同步比较好呢

[bz5314520]

@zh81 触发器😂