1 关于join
只要参与过后台开发,必然都对join有一定的了解.
我们使用join查询,主要为满足两方面的需求:
No.
需求说明
典型相似操作
效果对比
1
查询关联表内容,如主从表之间内容
子查询
不考虑索引的情况下,join查询效率一般优于前者;即使考虑索引,多数情况子查询的索引并不好设计
2
多表关系限制
in限制等
效率方面,只要不用not in,差不太多.但有些限制较难以in的方式直接限制,如典型的模糊查询
join的综合效果较佳,算是比较万金油的一种用法,这使得很多程序员对join有一些滥用.
过多的join使用(有时系统可能会查询超过十几个join)反而会带来极低的查询效率,所以阿里开发规范有规定:
join的数量不允许超过3个.
要求是有了,但关于如何减少,有些人不知道该怎么做,本文给出3个较为通用的方法供大家参考.
2 减少join的方法
由于是通用方法,三种方法的思想是一样的,即通过冗余的方式.
2.1 数据库表格增加冗余
顾名思义,一般在设计关联字段的时候,我们只会考虑增加该字段的编码或者id,如某商品的颜色是红色,往往仅会记录该商品的颜色的数据字典id.
如果有要求查询商品列表,且显示颜色的时候,一般做法就是left join数据字典查询出来商品颜色.
而该法则会在商品记录颜色的数据字典id之外,同时记录数据字典的内容:'红色',此时再查询商品列表,就不必再join数据字典了,且可直接通过'红色'名称模糊查询到该商品.
2.2 后台处理
很多时候我们遇到一种情况,想要查询某种表单必须先查询到另一类符合条件的表单编码(有时这也是数据库表格设计不合理造成的,我们姑且不考虑优化表格设计).
譬如查询某客户的已付款商品内容,需查询其对应销售开单中已被核销完成(全部收款)的单据的明细.此时应先查询该客户对应的销售收款单,再查询这批收款单中对应的销售开单,再查询销售开单对应的商品明细内容.
此时最直观的想法是用in+子查询,如果你真的这么用,在数据量达到几千条的时候就会发现查询速度无比的慢.
再有一种方法,就是利用join查询和限制,以此方式查询,想要把join的数量控制在3个以内基本上是不可能的事情,因为销售开单和销售收款单就各有主表和明细表,这些均需join上,这样就已经有4个join了.
此时最好的解决方法是,不要想着一条sql解决问题,先仅查询该客户销售收款单中记录的销售开单单号集合,在java后台中接收,在利用in查询的方式仅查询销售开单,这样,利用两次查询,每次查询也各自为两个join即可达成查询的目的.在数据量大的时候,查询效率是绝对高于前两者方法的.
2.3 前台处理
一些系统数据和基础数据内容,本来就要传到前台显示,如采购开单主页面需要选择采购哪些商品,这时在显示已有采购明细的时候,就没有必要将采购明细join商品数据了,只需将明细中的商品编码带到前台,在前台中中的商品编码查询到商品各种详细信息,再回填即可.
很多偏显示的冗余内容均可通过这种方式来实现,达到后台减少join的目的,除了商品,还有如数据字典,客户,业务员,供应商等等信息.除此之外,还有减少数据传输总量,减少后台计算压力等附加优化.
2.4 三种方法对比
方法
优点
缺点
适用情况
数据库表格冗余
既可适用于查询内容,又适用于冗余内容本身的查询限制
1.需设计冗余更新代码,灵活性欠佳;2.增加了数据库存储内容;
适用于冗余内容不多且更新不频繁的情况,考虑到需增加冗余更新代码,此种设计不宜过多
后台处理
1.能够较好的处理一些逻辑较复杂的情况;2.无前台协作情况亦可处理;
当中介数据量较大且计算要求较高时,会给后台较大的存储压力和计算压力;
适用于一些逻辑较复杂的情况,很多时候需要与in联合操作,故in后面跟着的数据不宜过多
前台处理
1.减少后台计算压力;2.多数情况可以减少数据传输压力;
1.需要前台参与,对前台代码设计有一定的设计要求,使用freemarker实现的页面很难使用该法;2.对于较复杂的限制查询不太适用;
适用于简单型冗余方案(多数情况均是如此),且前台页面最好是根据vue框架设计
实际设计时,需根据具体情况选择合适的方案.
3 其他
3.1 其他方案
以上仅列举了一些较为通用的方法,除了这些通用方法,在一些特殊场合,还有一些特殊方法同样可以减少join的使用数量.
如某些情况,利用索引的子查询效率反而会高于join,但这是基于对索引的充分理解上才可使用的方法,切忌盲目使用,细节这里就不讲了.
3.2 说明
- 虽然阿里规范要求join的数量不能多于3个,但对于一些中小型企业的数据库,数据量没有那么多,速度要求不是那么高的情况下,可以考虑4个甚至5个join,毕竟开发还是要讲效率的;
- 方法是否合适,如果不知道该选择什么的时候,就去测试下,实践是检验真理的唯一标准;
