SQL为什么不建议执行超过3表以上的多表关联查询
作者:happytaohaha 发布时间:2024-01-28 07:58:00
概述:前段时间在跟其他公司DBA交流时谈到了mysql跟PG之间在多表关联查询上的一些区别,相比之下mysql只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join),而PG是都支持的,而且mysql是往简单化方向去设计的,如果多个表关联查询(超过3张表)效率上是比不上PG的。
摘要:
不超过3层是为了效率。更通用 ,更好为了分布式做准备。
下面也对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);
为什么会这样做呢?原本一条查询,这里却变成了多条查询,返回结果又是一模一样。
事实上,用分解关联查询的方式重构查询具有如下优势:
让缓存的效率更高。
许多应用程序可以方便地缓存单表查询对应的结果对象。另外对于MySQL的查询缓存来说,如果关联中的某个表发生了变化,那么就无法使用查询缓存了,而拆分后,如果某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了。
将查询分解后,执行单个查询可以减少锁的竞争。
在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
查询本身效率也可能会有所提升
可以减少冗余记录的查询。
更进一步,这样做相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套环关联,某些场景哈希关联的效率更高很多。
解释: RPC(Remote Procedure Call):远程过程调用,它是一种通过网络从远程计算机程序上请求服务,而不需要了解底层网络技术的思想
来源:https://blog.csdn.net/NumberOneStudent/article/details/102776289
猜你喜欢
- SVM支持向量机是建立于统计学习理论上的一种分类算法,适合与处理具备高维特征的数据集。SVM算法的数学原理相对比较复杂,好在由于SVM算法的
- 依赖库flask安装,使用豆瓣源加速。pip install flask -i https://pypi.douban.com/simple
- vscode配置ruby开发环境vscode近年来发展迅速,几乎在3年之间就抢占了原来vim、sublime text的很多份额,犹记得在2
- 一、什么是super1.super也是一个类,是的。他不是一个方法也不是一个内置的关键字。class A: pas
- 五一在家写的,和大家分享,支持所有浏览器,添加了左侧菜单点击变色效果<!DOCTYPE html PUBLIC "-//W3
- 在之前的文章中,我们介绍了PyQt5和PySide2中主窗口控件MainWindow的使用、窗口控件的4中基础布局管理。从本篇开始,我们来了
- 我就废话不多说了,大家还是直接看代码吧!import requests, jsonr = requests.get('http://
- 在代码首行添加:%matplotlib inline即可。补充知识:jupyter不能显示Matplotlib 动画看莫烦老师的matplo
- 图中图准备数据import matplotlib.pyplot as pltfig = plt.figure()x = [1, 2, 3,
- 1.关闭浏览器全部标签页driver.quit()2.关闭当前标签页(从标签页A打开新的标签页B,关闭标签页A)driver.close()
- 题目描述682. 棒球比赛你现在是一场采用特殊赛制棒球比赛的记录员。这场比赛由若干回合组成,过去几回合的得分可能会影响以后几回合的得分。比赛
- TCP协议用在python和wifi模块之间python建立TCP连接需要用到socket协议 如果是TCP Server,建立T
- 对于一个Dict:test_dict = {1:5, 2:4, 3:3, 4:2, 5:1}想要求key值大于等于3的所有项:print({
- Python----OS 文件目录处理import osimport time# 获取当前文件的绝对路径dir_1 = os.path.ab
- 为什么要对URL进行encode在写网络爬虫时,发现提交表单中的中文字符都变成了TextBox1=%B8%C5%C2%CA%C2%DB这种样
- 豆瓣电影排行榜前250 分为10页,第一页的url为https://movie.douban.com/top250,但实际上应该是https
- 序言这不是圣诞节快到了,准备让让女朋友开心开心,也算是亲手做的,稍稍花了点心思。话不多说,咱们直接来展示吧,学会了赶紧画给你的那个她吧!本文
- 一、闭包闭包从形式上来说是在外部函数中定义内部函数,并且内部函数引用了外部函数的变量,此变量叫做自由变量。或者说是将组成函数的语句和这些语句
- 简介要建立一个允许过滤和分页的列表页,你必须让一些独立的东西一起工作。Django的对象关系映射器(ORM)和内置的分页类使开发者在不了解如
- 今天,在完成一个小的python习题,习题的主要内容是读取一个帮助模块,并保存到本地文件。知道是用pydoc进行模块的读取,但是在windo