mysql减少join的几种通用方法

Wesley13
• 阅读 485

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 说明

  1. 虽然阿里规范要求join的数量不能多于3个,但对于一些中小型企业的数据库,数据量没有那么多,速度要求不是那么高的情况下,可以考虑4个甚至5个join,毕竟开发还是要讲效率的;
  2. 方法是否合适,如果不知道该选择什么的时候,就去测试下,实践是检验真理的唯一标准;
点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Easter79 Easter79
2年前
sql join
1.1.1摘要Join是关系型数据库系统的重要操作之一,SQLServer中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行匹配的数据,这时我们应该考虑使用Join,因为Join具体联接表或函数进行查询的特性本文将通过具体例子介绍SQL中的各种常用Join的特性和使
Wesley13 Wesley13
2年前
MySQL千万级别优化·中
MySQL千万级别的查询优化手段·中单列索引(假设在v\_record表中存在id列的索引)1、WHERE条件使用​EXPLAINSELECT\FROMv\_recordWHEREid2​结论:利用索引进行回表查询2、SELECT字段使用
Wesley13 Wesley13
2年前
MySQL总结(十一)子查询
!(https://oscimg.oschina.net/oscnet/upa344f41e81d3568e3310b5da00c57ced8ea.png)子查询1\.什么是子查询需求:查询开发部中有哪些员工selectfromemp;通
Wesley13 Wesley13
2年前
560字带你彻底搞懂:MySQL的索引优化分析
正文一、SQL分析性能下降、SQL慢、执行时间长、等待时间长查询语句写得差索引失效关联查询太多join(设计缺陷)单值索引:在user表中给name属性创建索引,createindexidx\_nameonu
Wesley13 Wesley13
2年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Wesley13 Wesley13
2年前
Mysql索引最佳实践笔记0524
mysql5.7innodb默认存储引擎一、关于索引二、最佳实践三、避坑实践一、关于索引1.索引的作用提高查询效率数据分组、排序避免回表查询优化聚集查询用于多表join关联查询利用唯一性约束、保证数据唯一性innodb行锁实现索引的“
Wesley13 Wesley13
2年前
ThinkPHP 根据关联数据查询 hasWhere 的使用实例
很多时候,模型关联后需要根据关联的模型做查询。场景:广告表(ad),广告类型表(ad\_type),现在需要筛选出广告类型表中id字段为1且广告表中status为1的列表先看关联的设置部分 publicfunctionadType(){return$thisbelongsTo('A
Wesley13 Wesley13
2年前
Hibernate常见知识汇总
1.在数据库中条件查询速度很慢的时候,如何优化?1.建索引2.减少表之间的关联3.优化sql,尽量让sql很快定位数据,不要让sql做全表查询,应该走索引,把数据量大的表排在前面4.简化查询字段,没用的字段不要,已经对返回结果的控制,尽量返回少量数据2.在Hibernate中进行多表查询,每个表中各取几个字段,也就是说查询出来的结果
3A网络 3A网络
1年前
StoneDB 子查询优化
StoneDB子查询优化摘要:说明如何优化exists的join查询优化器的处理核心函数:TwoDimensionalJoiner::ChooseJoinAlgorithmcppJoinAlgTy