专栏首页Java架构筑基面试官:为什么mysql不建议执行超过3表以上的多表关联查询?
原创

面试官:为什么mysql不建议执行超过3表以上的多表关联查询?

点关注,不迷路;持续更新Java架构相关技术及资讯热文!!!

概述

前段时间在跟其他公司DBA交流时谈到了mysql跟PG之间在多表关联查询上的一些区别,相比之下mysql只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join),而PG是都支持的,而且mysql是往简单化方向去设计的,如果多个表关联查询(超过3张表)效率上是比不上PG的。

下面也对mysql多表关联这个特性简单探讨下~

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

A,B两个表数据规模十几万,数据规模都不大,单机MySQL够用了,在单机的基础上要关联两表的数据,先说一个极端情况,A,B两个表都没有索引,并且关联是笛卡尔积,那关联结果会爆炸式增长,可能到亿级别,这个时候网络IO成了瓶颈,这个时候两次十万行结果集的拉去可能远小于1次亿级别的结果集的拉取,那么将关联合并拉到service层做更快。

但实际业务中一般不会有这么蠢的行为,一般关联会有连接条件,并且连接条件上会有索引,一般是有一个结果集比较小,拿到这个结果集去另一张表去关联出其它信息,如果放到service层去做,最快的方式是,先查A表,得到一个小的结果集,一次rpc,再根据结果集,拼凑出B表的查询条件,去B表查到一个结果集,再一次rpc,再把结果集拉回service层,再一次rpc,然后service层做合并,3次rpc,如果用数据库的join,关联结果拉回来,一次rpc,帮你省了两次rpc,当然数据库上做关联更快,对应到数据库就是一次blk nested loop join,这是业务常用情况。

但是确实大多数业务都会考虑把这种合并操作放到service层,一般是有以下几方面考虑:

  • 第一:单机数据库计算资源很贵,数据库同时要服务写和读,都需要消耗CPU,为了能让数据库的吞吐变得更高,而业务又不在乎那几百微妙到毫秒级的延时差距,业务会把更多计算放到service层做,毕竟计算资源很好水平扩展,数据库很难啊,所以大多数业务会把纯计算操作放到service层做,而将数据库当成一种带事务能力的kv系统来使用,这是一种重业务,轻DB的架构思路
  • 第二:很多复杂的业务可能会由于发展的历史原因,一般不会只用一种数据库,一般会在多个数据库上加一层中间件,多个数据库之间就没办法join了,自然业务会抽象出一个service层,降低对数据库的耦合
  • 第三:对于一些大型公司由于数据规模庞大,不得不对数据库进行分库分表,对于分库分表的应用,使用join也受到了很多限制,除非业务能够很好的根据sharding key明确要join的两个表在同一个物理库中。而中间件一般对跨库join都支持不好。

举一个很常见的业务例子,在分库分表中,要同步更新两个表,这两个表位于不同的物理库中,为了保证数据一致性,一种做法是通过分布式事务中间件将两个更新操作放到一个事务中,但这样的操作一般要加全局锁,性能很捉急,而有些业务能够容忍短暂的数据不一致,怎么做?让它们分别更新呗,但是会存在数据写失败的问题,那就起个定时任务,扫描下A表有没有失败的行,然后看看B表是不是也没写成功,然后对这两条关联记录做订正,这个时候同样没法用join去实现,只能将数据拉到service层应用自己来合并了。。。

到这里答案就很清楚了~

对关联查询进行分解

很多高性能的应用都会对关联查询进行分解。

简单地,可以对每个表进行一次单表查询,然后将结果在应用程序中进行关联。例如,下面这个查询:

select * from tag
join tag_post on tag_post.tag_id=tag.id
join post on tag_post.post_id=post.id
where tag.tag=’mysql’;

可以分解成下面这些查询来代替:

Select * from tag where tag=’mysql’;
Select * from tag_post where tag_id=1234;
Select * from post where id in(123,456,567,9989,8909);

为什么会这样做呢?原本一条查询,这里却变成了多条查询,返回结果又是一模一样。

事实上,用分解关联查询的方式重构查询具有如下优势:

  1. 让缓存的效率更高。 许多应用程序可以方便地缓存单表查询对应的结果对象。另外对于MySQL的查询缓存来说,如果关联中的某个表发生了变化,那么就无法使用查询缓存了,而拆分后,如果某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了。
  2. 将查询分解后,执行单个查询可以减少锁的竞争。
  3. 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
  4. 查询本身效率也可能会有所提升
  5. 可以减少冗余记录的查询。
  6. 更进一步,这样做相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套环关联,某些场景哈希关联的效率更高很多。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 本人秃顶程序员】分库分表怎么才能无限扩容,看这篇文章就对了

    像我这样的菜鸟,总会有各种疑问,刚开始是对 JDK API 的疑问,对 NIO 的疑问,对 JVM 的疑问,当工作几年后,对服务的可用性,可扩展性也有了新的疑问...

    本人秃顶程序员
  • 我来告诉你解决死锁的100种方法

    死锁是多线程编程或者说是并发编程中的一个经典问题,也是我们在实际工作中很可能会碰到的问题。相信大部分读者对“死锁”这个词都是略有耳闻的,但从我对后端开发岗位的面...

    本人秃顶程序员
  • 你有成为互联网团队管理者的潜力吗?

    上个星期,某朋友在朋友圈发了一张在医院挂水的照片,并自嘲地写到“连续996一个月了,在互联网团队工作,真心扛不住啊”,有个调皮的小伙伴在评论区嘲讽道:“一个月?...

    本人秃顶程序员
  • MongoDB入门系列——4.基本操作(增删改查)

    上面我们已经安装MongoDB数据库,并且也用Studio 3T连接上了,那么接下来我们就要正式的学习他啦。

    陈琛
  • 硬编码,常量,枚举类

    假如有一笔业务需要审核,审核状态分:未审核,审核中,审核通过,审核不通过。我们在程序里是否可以直接这么写: if(state==1){//1代表未操作 //...

    java达人
  • 集思广益-生存分析可以随心所欲根据表达量分组吗

    根据基因表达量的中位值把样本分成高低表达量的组别,然后做生存分析是比较符合大家的直觉的。

    生信技能树
  • 图解正向代理、反向代理、透明代理

    套用古龙武侠小说套路来说,代理服务技术是一门很古老的技术,是在互联网早期出现就使用的技术。一般实现代理技术的方式就是在服务器上安装代理服务软件,让其成为一个代理...

    菲宇
  • linux下正向代理/反向代理/透明代理使用说明

    代理服务技术对于网站架构部署时非常重要的,一般实现代理技术的方式就是在服务器上安装代理服务软件,让其成为一个代理服务器,从而实现代理技术。 常用的代理技术分为正...

    洗尽了浮华
  • 图解正向代理、反向代理、透明代理

    一、正向代理(Forward Proxy)    一般情况下,如果没有特别说明,代理技术默认说的是正向代理技术。关于正向代理的概念如下: 正向代理(forwar...

    庞小明
  • flv格式详解+实例剖析

    FLV(Flash Video)是现在非常流行的流媒体格式,由于其视频文件体积轻巧、封装播放简单等特点,使其很适合在网络上进行应用,目前主流的视频网站无一例外地...

    用户2929716

扫码关注云+社区

领取腾讯云代金券