《阿里巴巴JAVA开发手册》超过三张表禁止join

淩亂°似流年 2023-10-03 13:43 21阅读 0赞

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

**原文如下：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70][]**

计算机解决问题，要么用空间换时间，要么用时间换空间，此二法基本上能解决大多数疑难杂症

1、该问题下，阿里大佬李晨曦的回答，通过空间换时间的方案来设计表，虽然数据冗余了，但查询性能显著提升了，该大佬的回答如下：

**一：为什么做这种限制？**

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

如果数据库的性能无限强大，多个表的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了，查询的性能就可以提高很多了。

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

2、聿明leslie回答如下：

看了前面几位高分的回答，都说得没错，尤其是我的同事，数据库大神

@李晨曦

的回答生动详尽，但看了下大家主要从数据库的角度来分析的。  
从数据库实现角度来说确实也不建议太多表连接，从优化器的角度而言，选择连接路径的算法目前比较主流的是动态规划和贪心算法，在连接表数比较少的时候选择动态规划，一阶段一阶段的去分析各种可能得连接顺序得到一个最优的执行计划，但动态规划场景下，随表的增加，计划也会爆炸式增加，优化器在选择最优计划的前提下，消耗的内存和CPU是不能不考虑的，所以当表的数量太多，数据库会退化成贪心算法，尽快，尽量少的消耗计算资源前提下出一个计划，这个计划可能不是执行最优的计划。OB的规定是10表以上的星型连接就使用贪心算法，PG还在学术角度实现了基因算法，但工程上，大家很少使用这个。

从应用的角度来说，这个约束没有出现在使用小型机+Oracle的时代，而是在阿里发起去IOE行动，转而选择用MySQL+PC服务器后，数据库服务也从垂直拓展的集中式架构变成了水平拓展的分布式架构，Oracle对于多表连接的选择会做得更好，执行性能也更优，我觉得主要原因还是在它的使用场景中用户的使用习惯导致的，MySQL成熟于互联网爆发时期，很多小公司单PC够了，数据规模对性能得影响也没那么大，这不是它使用场景的痛点所在，自然没必要去费劲解决这方面的问题，同多表连接相比，大家更在乎的一个问题是当表在MySQL中数据量太大，由于它的索引结构设计没有针对大表，所以查询性能会断崖式下滑。

从阿里的角度而言，由于数据规模太大，不得不考虑分库分表+中间件的模型，在分库分表场景下，能在数据库层面做join的场景自然也不多，所以大家更多的是将数据库当成一个带多行事务能力的KV系统去用，这是轻DB重应用的思路，跟银行等传统行业重DB轻应用的思路刚好相反，如果将join放到中间件去实现，由于中间件拿到数据sharding信息更难，成本肯定更大，所以这个问题自然就落到了应用上，对于应用而言，一般的数据表设计会是一个很大的数据表集，一个或多个字典表，相对于数据表，字典表的数据增长速度会很小，两个表之间的关联一般有索引或者主键，这很适合nested loop join，而且应用也担心数据库给我把路径选错了，把join方式选错了，而在应用的角度，我的需求我会更了解，在应用层实现特定的join也容易得多，所以join一般被分解成先查一个数据集，然后用一个常量in来查另一堆数据集

在分布式数据库的角度，我们希望数据库再次回到重DB的应用思路上，在分布式场景下，如何高效的选择和执行连接查询，是和单机相比一个更大的难题，也是OB的努力方向之一。

参考文章

[https://www.zhihu.com/question/56236190][https_www.zhihu.com_question_56236190]

[https://www.studyjava.cn/post/428][https_www.studyjava.cn_post_428]

[http://blog.itpub.net/30393770/viewspace-2650450/][http_blog.itpub.net_30393770_viewspace-2650450]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210815213701808.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbmdnZVFpbmdjaHVu,size_16,color_FFFFFF,t_70
[https_www.zhihu.com_question_56236190]: https://www.zhihu.com/question/56236190
[https_www.studyjava.cn_post_428]: https://www.studyjava.cn/post/428
[http_blog.itpub.net_30393770_viewspace-2650450]: http://blog.itpub.net/30393770/viewspace-2650450/