MySQL多表关联查询效率高点还是多次单表查询效率高,为什么?

MySQL多表关联查询对比多次单表查询,哪个效率高?

疑问:

比如有a,b两张表,两张表都差不多有十几w行记录,需要查询的字段在两张表中,那么是关联查询效率高点还是分别查a,b两张表,再在service层合并数据效率高点?想问问大家在实际开发中都是怎么考量的?谢谢!

高手解答:

先说效率哪个更高:A,B两个表数据规模十几万,数据规模都不大,单机MySQL够用了。
在单机的基础上要关联两表的数据,

  • 先说一个极端情况,A,B两个表都没有索引,并且关联是笛卡尔积,那关联结果会爆炸式增长,可能到亿级别,这个时候网络IO成了瓶颈,这个时候两次十万行结果集的拉取可能远小于1次亿级别的结果集的拉取,那么将关联合并拉到service层做更快。
  • 但实际业务中一般不会有这么蠢的行为,一般关联会有连接条件,并且连接条件上会有索引,一般是有一个结果集比较小,拿到这个结果集去另一张表去关联出其它信息。
    如果放到service层去做,最快的方式是,先查A表,得到一个小的结果集,一次rpc(RPC一般指远程过程调用。RPC是远程过程调用(Remote Procedure Call)的缩写形式),再根据结果集,拼凑出B表的查询条件,去B表查到一个结果集,再一次rpc,再把结果集拉回service层,再一次rpc,然后service层做合并,3次rpc获得结果
    如果用数据库的join,关联结果拉回来,一次rpc,帮你省了两次rpc,当然数据库上做关联更快,对应到数据库就是一次blk nested loop join,这是业务常用情况。
    但是确实大多数业务都会考虑把这种合并操作放到service层,我觉得有几方面考虑:
    第一:单机数据库计算资源很贵,数据库同时要服务写和读,都需要消耗CPU,为了能让数据库的吞吐变得更高,而业务又不在乎那几百微妙到毫秒级的延时差距,业务会把更多计算放到service层做,毕竟计算资源很好水平扩展,数据库很难啊,所以大多数业务会把纯计算操作放到service层做,而将数据库当成一种带事务能力的kv系统(KV数据库:Key-Value,让人想到Mongo数据库和JSON数据交换格式。 数据存储,存储的数据主要分两种:结构数据(关系表),非结构数据(大文件,杂数据(模式和内容揉合(HTML)))来使用,这是一种重业务,轻DB的架构思路
    第二:很多复杂的业务可能会由于发展的历史原因,一般不会只用一种数据库,一般会在多个数据库上加一层中间件,多个数据库之间还能做FetchMode.join,自然业务会抽象出一个service层,降低对数据库的耦合。
    第三:对于一些大型公司由于数据规模庞大,不得不对数据库进行分库分表,这个问题我在《阿里为什么要禁用三表以上的join》上也回答过,对于分库分表的应用,使用join也受到了很多限制,除非业务能够很好的根据sharding key(分区key)明确要join的两个表在同一个物理库中。而中间件一般对跨库join都支持不好。举一个很常见的业务例子,在分库分表中,要同步更新两个表,这两个表位于不同的物理库中,为了保证数据一致性,一种做法是通过分布式事务中间件将两个更新操作放到一个事务中,但这样的操作一般要加全局锁,性能很捉急,而有些业务能够容忍短暂的数据不一致,怎么做?让它们分别更新呗,但是会存在数据写失败的问题,那就起个定时任务,扫描下A表有没有失败的行,然后看看B表是不是也没写成功,然后对这两条关联记录做订正,这个时候同样没法用join去实现,只能将数据拉到service层应用自己来合并了。

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

一:为什么做这种限制?

打个比方,如果我有无限的钱,我想买个豪华别墅,想买个跑车,想买个直升飞机,但现实是我没钱,只能租房住,只能走路上下班。。
如果数据库的性能无限强大,多个表的join肯定是需要的,尤其是复杂的分析型(OLAP)查询,甚至可能涉及10几个表的join,但现实是大部分数据库的性能都太弱了,尤其是涉及到多表join的查询。
规范一看就是在使用MySQL时的限制(这种规范实际上迫不得已的限制),做这个限制有两个原因:一是优化器很弱,涉及多个表的查询,往往得不到很好的查询计划;二是执行器很弱,只有nested loop join,block nested loop join和index nested loop join。

  1. nested loop join就是分别从两个表读一行数据进行两两对比,复杂度是n^22.
  2. block nested loop join是分别从两个表读很多行数据,然后进行两两对比,复杂度也是n^2,只是少了些函数调用等overhead
  3. index nested loop join是从第一个表读一行,然后在第二个表的索引中查找这个数据,索引是B+树索引,复杂度可以近似认为是n log n,比上面两个好很多,这就是要保证关联字段索引的原因
  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了,查询的性能就可以提高很多了。任何的规范都是在特定情况下的某种妥协,脱离了这个环境,就不一定成立了。

  • 3
    点赞
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PHP语言开发的ERP进销存管理系统多仓库全开源无限制优化版,扫描入库+库存预警+仓库管理+商品管理+供应商管理。有购货,销货,仓库管理,商品管理,供应商管理,职员管理等非常多的功能。 本系统开发 PHP + MySQL 采用CI2.x框架 运行环境 php5.6 、MYSQL5.6 、Linux、apache、nginx 该ERP系统部署安装非常简单,只需要空间支持PHP上传到根目录即可简单安装即可。运行速度和数据处理效率都非常高,非常适合中小企业仓库管理使用。 更新日志: 2019年12月03日: 1.修复销货单修改状态下选择商品后无法带出单价的问题 2.将所有单据,点击行新增按钮后由从行前添加改为从行位添加空白行,防止选择多个商品时将后面的已选商品覆盖 3.商品增加生产日期、有效期天数、货位字段,并可带入销货单支持EXCEL导入 4.修复已知小bug 2019年07月04日: 1.修复销货单偶尔因网络延迟获取不到联系人的bug 2.增加销售合同、采购合同附件对word,excel,pdf文件上传的支持 3.导入商品资料增加备注等字段的导入 4.修复一部分小bug 2018年12月30日: 1.新增管理员强制重置密码功能 2018年08月10日: 1.新增客户等级价格功能(在 客户订单、销售单中生效) 2018年4月30日: 1.修复调拨单无法删除 2.修复销售单修改时无法 自动带出 联系人、地址、电话 问题 3.销货单 打印增加 左上角显示Logo、右上角显示说明文字、右边多联说明 4.销货单支持A5纸多页连打 5.购货单、销货单、收款单、付款单 增加选择打印行数 6.增加收款单打印功能 7.增加付款单打印功能 2017年12月25日: 1.新增购货单附件上传,销售单附件上传,此附件不仅用于图片,可上传word,excel等各种文件。 2.新增重磅级功能->销售利润,可以按客户和日期范围查询销货单的利润明细和统计,并且支持根据销售人员查询该员工的销售业绩,见下图。 3.增加收款单、付款单在收付款时,做指定销货单或购货单的收付款,进行了关联。 4.增加供应商、客户、商品模块的excel导入功能。 5.商品增加套餐(组合商品)功能,销售套餐,对应的子商品立减库存。 6.增加客户联系人“职位”字段标注。 7.销货单、退货单增加“序列号”字段的录入。 8.修复后台商品管理不稳定问题,造成任何用户多次操作才能生效。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值