前 言
最近在看《阿里巴巴开发手册》,发现一个很有趣的编程规约,即《阿里巴巴开发手册中》第五章第二节第二条中明确规定了:
超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。
不信你看:
这让人突然想起了这张神图:
woc,看来以前多表狂 join的骚操作该停一停了。。。
为什么做这种限制
打个比方,如果我有无限的钱,我想买个豪华别墅,想买个跑车,想买个直升飞机,但现实是我没钱,只能租房住,只能走路上下班。
如果数据库的性能无限强大,多个表的join肯定是需要的,尤其是复杂的分析型(OLAP)查询,甚至可能涉及10几个表的join,但现实是大部分数据库的性能都太弱了,尤其是涉及到多表join的查询。
规范一看就是在使用 MySQL时的限制(这种规范实际上迫不得已的限制),做这个限制有两个原因:
一是优化器很弱,涉及多个表的查询,往往得不到很好的查询计划,这块比较复杂,需要看一些MySQL深层次的书籍;二是执行器很弱,只有 nested loop join
, block nested loop join
和 index nested loop join
。
- nested loop join 就是分别从两个表读一行数据进行两两对比,复杂度是
n^2
- block nested loop join 是分别从两个表读很多行数据,然后进行两两对比,复杂度也是
n^2
,只是少了些函数调用等overhead - index nested loop join 是从第一个表读一行,然后在第二个表的索引中查找这个数据,索引是B+树索引,复杂度可以近似认为是
nlogn
,比上面两个好很多,这就是要保证关联字段有索引的原因 - 如果有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了,查询的性能就可以提高很多了。
除此之外,常见的还能思考到的解决方法比如有:
- 可以尝试拆解复杂查询语录成为多条查询语句,将1拆解为n+1,其中每条简单的大表查询尽量走索性,提高查询效率。当然也要参考隔离级别是否会产生数据不一致的情况。
- join顺序适当优化,尽量优化
- 自建应用层缓存,但涉及到增删查改可能额外代价比较大。
当然多插一句,不同项目的业务不同,能定制化解决的方案也不尽相同,还是得根据实际情况来。
多BB两句
这种问题没有对错,属于中立性问题,无论从正面或反面都能解释出一堆原因。
任何规范都有自己合适的应用场景,很明显 超过三张表禁join是阿里中的场景,而不是你的场景,写代码需要有自己的思维,不能因为阿里说禁join 谷歌说不推荐try catch,然后你就按图索骥全线禁止join禁止try catch。要根据实际情况,该join的join,该catch的catch,其实现代的商业数据库对sql的优化能力比你自己写的可能还是要高不少的。
所以小伙伴们,如果超过三张表不让你join,你会怎么办呢?