《阿里巴巴JAVA开发手册》里面写超过三张表禁止join 这是为什么？这样的话那sql要怎么写？

《阿里巴巴Java开发手册》中关于“禁止超过三张表JOIN”的规定，并非绝对的硬性限制，而是一种强烈建议和最佳实践的提倡。它背后蕴含着对性能、可维护性和复杂度的考量。

为什么会有这样的建议？

想象一下，当你准备 join 超过三张表的时候，你正在构建一个非常复杂的 SQL 查询。这背后通常意味着：

1. 性能瓶颈的风险急剧增加：
笛卡尔积的膨胀： 尽管我们通常会通过 JOIN 条件来避免完全的笛卡尔积（即所有行的组合），但随着 JOIN 表数量的增加，中间结果集的规模可能呈指数级增长。即使有索引，也可能需要扫描大量数据，或者产生非常大的临时表。
查询优化器的负担： 数据库的查询优化器非常聪明，它会尝试找到最佳的执行计划。但是，当 JOIN 的表数量很多时，可供优化的路径会变得极其复杂，优化器可能需要花费更长的时间来分析，甚至可能做出次优的决策，导致查询性能下降。
锁竞争与死锁： 复杂的 JOIN 操作可能需要锁定更多的表或行，这增加了并发访问时发生锁竞争和死锁的概率，对系统的稳定性和可用性产生负面影响。
网络传输与内存消耗： 较大的中间结果集需要更多的内存来处理，也可能需要通过网络传输更多的数据，这都会成为性能的瓶颈。

2. 可读性与可维护性的灾难：
SQL 语句的“意大利面条化”： 一个包含五张、六张甚至更多表 JOIN 的 SQL 语句，读起来就像一团乱麻。你很难快速理解数据之间的关联关系，也很难判断每个 JOIN 条件的正确性。
修改的困难： 当业务需求变更，需要调整 JOIN 的条件或增加新的过滤条件时，一个复杂的 JOIN SQL 将变得非常难以修改。一个小小的改动，可能会引入新的 bug，或者导致之前正常的逻辑失效。
测试的挑战： 编写单元测试或集成测试来覆盖如此复杂的 SQL 语句，其工作量和难度都会指数级增长。

3. 业务逻辑的复杂化：
数据模型设计问题： 如果一个查询需要 JOIN 很多张表才能获取所需数据，这往往暗示着你的数据模型设计可能存在反范式的情况，或者实体之间的关联关系过于复杂，应该考虑拆分或重构。
单次请求的负担过重： 这种复杂的 SQL 语句通常出现在一个请求中，意味着这个请求需要从数据库一次性拉取大量、多维度的数据。这可能违反了“高内聚、低耦合”的设计原则，将本应由不同服务或模块负责的数据获取逻辑，强行塞入一个地方。

那么，如果SQL需要关联多于三张表，应该怎么写？

手册并非让你放弃关联这些表，而是建议不要在一个 SQL 语句中直接完成所有关联。正确的做法是分步进行，化繁为简。具体来说，你可以考虑以下几种方式：

方式一：分步查询，多次 SQL 请求

这是最常见也是最推荐的方式。将一个复杂的 JOIN 分解成一系列更简单、更聚焦的 SQL 查询，并在应用层将结果进行组装。

核心思想： 先获取核心数据，然后根据这些核心数据，再去查询关联表。

举例说明：
假设我们有一个场景，需要查询某个用户的订单信息，订单中包含商品信息，而商品信息又关联了供应商信息，最后还需要查询用户的收货地址。如果直接 JOIN，可能涉及 `users`, `orders`, `order_items`, `products`, `suppliers`, `addresses` 这六张表。

不推荐的（可能在六张表以上）：
```sql
SELECT
u.username,
o.order_id,
oi.quantity,
p.product_name,
s.supplier_name,
a.address_detail
FROM
users u
JOIN
orders o ON u.user_id = o.user_id
JOIN
order_items oi ON o.order_id = oi.order_id
JOIN
products p ON oi.product_id = p.product_id
JOIN
suppliers s ON p.supplier_id = s.supplier_id
JOIN
addresses a ON u.address_id = a.address_id
WHERE
u.user_id = 123;
```

推荐的（分步查询）：

1. 查询用户及其地址信息：
```sql
SELECT username, address_id FROM users WHERE user_id = 123;
```
应用层获取用户 `username` 和 `address_id`。

2. 查询用户的订单信息：
```sql
SELECT order_id FROM orders WHERE user_id = 123;
```
应用层获取用户的 `order_id` 列表。

3. 查询订单对应的商品项（含商品和供应商信息）：
假设上一步得到了 `[order1, order2]`，我们需要查询这些订单的所有商品项。
```sql
SELECT
oi.order_id,
oi.quantity,
p.product_name,
s.supplier_name
FROM
order_items oi
JOIN
products p ON oi.product_id = p.product_id
JOIN
suppliers s ON p.supplier_id = s.supplier_id
WHERE
oi.order_id IN (order1, order2);
```
应用层获取订单详情，包含商品和供应商信息。

4. 根据地址ID查询详细地址：
根据第一步获取的 `address_id`，查询详细地址。
```sql
SELECT address_detail FROM addresses WHERE address_id = <用户地址ID>;
```
应用层获取详细地址。

最后，在应用代码中，将这些分散的数据按照逻辑进行组装，形成最终的返回结果。

优点：
性能可控： 每条 SQL 都更简单，更容易被数据库优化器高效执行。
可读性强： SQL 语句短小精悍，易于理解和维护。
易于调试： 如果某个部分出错了，可以单独运行对应的 SQL 来排查问题。
灵活： 可以根据实际需要，选择性地查询某些关联数据，避免一次性加载过多不必要的字段。

缺点：
增加网络交互次数： 相比一次性 JOIN，会产生多次数据库交互。
应用层代码复杂度： 需要在应用层进行数据组装和逻辑处理。

方式二：使用视图（View）

如果某些复杂的 JOIN 关系是经常被使用的，可以考虑将它们封装成一个视图。

核心思想： 将复杂的 JOIN SQL 隐藏在视图定义中，然后在应用层像查询单张表一样查询这个视图。

举例说明：
将上面涉及 `order_items`, `products`, `suppliers` 的 JOIN 封装成一个视图：
```sql
CREATE VIEW order_item_details AS
SELECT
oi.order_item_id,
oi.order_id,
oi.quantity,
p.product_name,
s.supplier_name,
p.supplier_id
FROM
order_items oi
JOIN
products p ON oi.product_id = p.product_id
JOIN
suppliers s ON p.supplier_id = s.supplier_id;
```
然后，在应用层查询时，可以这样写：
```sql
SELECT
u.username,
o.order_id,
oid.quantity,
oid.product_name,
oid.supplier_name
FROM
users u
JOIN
orders o ON u.user_id = o.user_id
JOIN
order_item_details oid ON o.order_id = oid.order_id 查询视图
WHERE
u.user_id = 123;
```
这里，我们只直接 JOIN 了三张表（`users`, `orders`, `order_item_details` 视图），将原本的复杂 JOIN 隐藏到了视图定义里。

优点：
简化应用层 SQL： 应用层的 SQL 看起来更简单。
逻辑复用： 方便在多个地方复用同一组复杂的关联逻辑。

缺点：
视图可能导致性能问题： 数据库如何处理视图的查询优化仍然取决于具体的数据库系统。有些数据库可能会将视图的查询“铺平”到主查询中，导致原有的性能问题依然存在；有些数据库则可能将视图视为一个独立的查询单元，反而影响优化。
数据一致性： 如果视图定义复杂，且涉及多个表的更新，可能会影响数据更新的性能和复杂性。
同样不建议过度依赖： 如果一个视图就包含了五六张表的 JOIN，那实际上只是把复杂性转移了，并没有根本解决问题。

方式三：存储过程或函数

对于某些非常固定的、复杂的关联查询，可以将其写成数据库的存储过程或函数。

核心思想： 将复杂的 SQL 逻辑封装在数据库端，通过调用存储过程或函数来执行。

举例说明：
可以创建一个存储过程，接受用户 ID 作为参数，内部执行前面提到的复杂 JOIN SQL，然后返回结果集。
```sql
示例（具体语法因数据库而异）
CREATE PROCEDURE GetUserDetailsWithOrdersAndProducts(IN userId INT)
BEGIN
SELECT
u.username,
o.order_id,
oi.quantity,
p.product_name,
s.supplier_name,
a.address_detail
FROM
users u
JOIN
orders o ON u.user_id = o.user_id
JOIN
order_items oi ON o.order_id = oi.order_id
JOIN
products p ON oi.product_id = p.product_id
JOIN
suppliers s ON p.supplier_id = s.supplier_id
JOIN
addresses a ON u.address_id = a.address_id
WHERE
u.user_id = userId;
END;
```
在应用层，只需要调用这个存储过程即可：
```java
// 假设使用 JDBC 调用
CallableStatement cs = connection.prepareCall("{call GetUserDetailsWithOrdersAndProducts(?)}");
cs.setInt(1, userId);
ResultSet rs = cs.executeQuery();
// 处理结果集...
```

优点：
封装复杂性： 将复杂 SQL 逻辑封装起来，应用层接口清晰。
性能： 数据库可以对存储过程进行更深入的优化。
减少网络传输： 可能比多次 SQL 请求的往返次数少。

缺点：
数据库依赖性强： 存储过程是数据库特定的，迁移数据库时可能需要重写。
可测试性差： 数据库存储过程的可测试性通常不如 Java 代码。
调试困难： 存储过程的调试通常比应用层代码更复杂。
同样需要谨慎： 如果存储过程内部还是一个包含大量 JOIN 的 SQL，并没有解决根本的性能和可维护性问题。

总结一下：

《阿里巴巴Java开发手册》提出“超过三张表禁止JOIN”的建议，核心是为了规避潜在的性能问题，提升代码的可读性和可维护性，并促使开发者反思数据模型设计。

面对超过三张表的需求时，请优先考虑分步查询，在应用层组装数据。这是一种平衡了性能、可读性和开发效率的策略。只有在非常特殊且经过充分性能测试的情况下，才考虑使用视图或存储过程来封装复杂的关联，但也要警惕这些方式可能带来的其他问题。

最重要的是，要理解这个建议背后的“为什么”，而不是死板地遵守“三张表”的数字限制。关键在于编写简单、高效、易于维护的代码。

一：为什么做这种限制？

打个比方，如果我有无限的钱，我想买个豪华别墅，想买个跑车，想买个直升飞机，但现实是我没钱，只能租房住，只能走路上下班。。

如果数据库的性能无限强大，多个表的join肯定是需要的，尤其是复杂的分析型(OLAP)查询，甚至可能涉及10几个表的join，但现实是大部分数据库的性能都太弱了，尤其是涉及到多表join的查询。给@韩飞点个赞，国内懂这个做这个的太少了，以后就靠他们了

规范一看就是在使用MySQL时的限制（这种规范实际上迫不得已的限制），做这个限制有两个原因：一是优化器很弱，涉及多个表的查询，往往得不到很好的查询计划，这块比较复杂，感兴趣的朋友可以关注我，我以后会写文章专门介绍；二是执行器很弱，只有nested loop join，block nested loop join和index nested loop join。

1. nested loop join就是分别从两个表读一行数据进行两两对比，复杂度是n^2

2. block nested loop join是分别从两个表读很多行数据，然后进行两两对比，复杂度也是n^2，只是少了些函数调用等overhead

3. index nested loop join是从第一个表读一行，然后在第二个表的索引中查找这个数据，索引是B+树索引，复杂度可以近似认为是nlogn，比上面两个好很多，这就是要保证关联字段有索引的原因

4. 如果有hash join，就不用做这种限制了，用第一个表（小表）建hash table，第二个表在hash table中查找匹配的项，复杂度是n。缺点是hash table占的内存可能会比较大，不过也有基于磁盘的hash join，实现起来比较复杂

二：在这种限制下SQL怎么写？

可是我确实需要两个表里的数据链接在一起啊，我们可以做个冗余，建表的时候，就把这些列放在一个表里，比如一开始有student(id, name)，class(id, description)，student_class(student_id, class_id)三张表，这样是符合数据库范式的(第一范式，第二范式，第三范式，BC范式等)，没有任何冗余，但是马上就不符合“编程规范“了，那我们可以用一张大表代替它，student_class_full(student_id, class_id, name, description)，这样name和description可能要被存储多份，但是由于不需要join了，查询的性能就可以提高很多了。

任何的规范都是在特定情况下的某种妥协，脱离了这个环境，就不一定成立了。