多表 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 楼说的特别对