MySQL关联查询如何优化

news/2024/5/30 16:57:20/文章来源:https://blog.csdn.net/weixin_49065828/article/details/136658299

好久不见,关于这篇文章,我也是想了很久,还是决定写一篇文章,有很多同学问过 mysql 相关的问题,其实关联查询如何优化,首先我们要知道关联查询的原理是什么?

左连接 left join

SELECT 字段列表
FROMA表 
LEFT JOIN B表
ON 关联条件
WHERE 等其他子句

两表关联,以 left 左边的表为主表进行查询,除了返回满足连接条件的行以外,还返回左表中不满足条件的行。
如图所示:A 表是主表(驱动表),B 表是从表(被驱动表),颜色区域即所得结果集,结果集中返回匹配的行(交集),也返回 A 表中不匹配的行,不匹配字段用 NULL 表示。
在这里插入图片描述

右连接 right join

SELECT 字段列表
FROMA表 
RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句

两表关联,以 right 右边的表为主表进行查询,除了返回满足连接条件的行以外,还返回右表中不满足条件的行。
如图所示:B 表是主表(驱动表),A 表是从表(被驱动表),颜色区域即所得结果集,结果集中返回匹配的行(交集),也返回 B 表中不匹配的行,不匹配字段用 NULL 表示。(同 left join,只不过主表位置不同)
在这里插入图片描述

内连接 inner join

SELECT 字段列表
FROM A表 
INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

两表关联,返回符合 where 条件的结果集,即是 A 表 结果集,也是 B 表结果集,内联查询,没有左右主表之分,以哪张表为驱动表,取决于 MySQL service 层的优化器自己决定。
如图所示:
在这里插入图片描述

关联查询原理

前面讲解了连接查询的几种方式,现在谈谈 MySQL 底层是支持这几种连接查询的。
关联查询中涉及到多表的的查询,根据驱动类型分为驱动表和被驱动表,驱动表就是主表,被驱动表就是从表。
那么 MySQL 是如何进行join查询的呢?

1.Simple Nested-Loop Join (简单嵌套循环连接)

是从驱动表 A 中取出一条数据,遍历表 B,将匹配到的数据放到result,以此类推, 如下图所示:
在这里插入图片描述
比如驱动表A有10条,被驱动表B有100条,那么扫描次数是A+A*B, 每一次扫描其实就是从硬盘中读取数据加载到内存中,也就是一次IO,而IO是最大的瓶颈,所以效率低下,开销如下表:

开销统计简单嵌套循环连接
驱动表扫描次数1
被驱动表扫描次数A
读取记录数A+B*A
JOIN比较次数B*A
回表读取记录次数0

当然 MySQL 肯定不会这么粗暴的去进行表的连接,所以就出现了后面的两种对 Nested-Loop Join 优化算法。

2.Block Nested-Loop Join (块嵌套循环连接)

块嵌套循环连接是对上面一种算法的优化,简单嵌套是去驱动表中获取数据去匹配,和磁盘 IO 交互太多了,那么能否以一种批量的方式进行优化呢?mybatis 批量插入批量查询也是这个道理。而这种算法就是借鉴了这样的思想。
不再是逐条获取驱动表的数据,而是一块一块的获取,引入了 join buffer 缓冲区,将驱动表join相关的部分数据列、缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率。整体如下图所示:
在这里插入图片描述
需要注意的是:从驱动表中缓存的列不仅仅是关联的的列,select 后面的列也会缓存起来。因此,为了能让 join buffer 缓存更多的数据,我们的 SQL 尽量不要 select *, 而是 select 用到的字段。
开销如下表:

开销统计块嵌套循环连接
驱动表扫描次数1
被驱动表扫描次数A*used_column_size/join_buffer_size+1
读取记录数A+B*(A*used_column_size/join_buffer_size)
JOIN比较次数B*A
回表读取记录次数0

join buffer的大小是可以设置的,默认情况下 join_buffer_size=256k。
join_buffer_size 的最大值在32位操作系统可以申请4G,而在64位操作系统下可以申请大于4G的 Join Buffer 空间(64位Windows除外,其大值会被截断为4GB并发出警告)。

3.Index Nested-Loop Join (索引嵌套循环连接)

索引嵌套循环连接(Index Nested-Loop Join)就是效率最高的,前提条件是被驱动表的关联字段建立了索引。通过驱动表匹配条件直接与被驱动表的索引进行匹配,避免和内存表的每条记录去进行比较,这样极大的减少了对内存表的匹配次数。如下图所示:
在这里插入图片描述
因为索引查询的成本基本一样,为了降低开销,驱动表是小表更加合适。所以我们常说把小表当作主表是有原因的。
开销如下表:

开销统计索引嵌套循环连接
驱动表扫描次数1
被驱动表扫描次数0
读取记录数A+B(match)
JOIN比较次数A*Index(Height)
回表读取记录次数B(match)(if possible)

如果被驱动表加索引,效率是非常高的,但如果索引不是主键索引,所以还得进行一次回表查询。相比,被驱动表的索引是主键索引,效率会更高。

块嵌套循环连接:对于被连接的数据子集较小的情况下,它是个较好的选择。
Hash Join: 是做大数据集连接时的常用方式,优化器使用两个表中较小(相对较小)的表利用 Join Key 在内存中建立散列值,然后扫描较大的表并探测散列值,找出与 Hash 表匹配的行。它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。Hash Join 只能应用于等值连接,这是由 Hash 的特点决定的。
在这里插入图片描述

总结:优化建议

前面讲了原理,从原理出发,讲一下优化的建议

  1. 被驱动表的连接字段建立索引,因为建立索引的查询方式是效率最高的。
  2. left join 或者 right join 这种外连接的情况,要保证小表(小结果集)作为驱动表,大表(大结果集)作为被驱动表,这样性能更好。
  3. 在查询字段的话,要避免写出 select * ,而是根据业务需要,需要查询出来的 select 出来就行,因为这些字段也会加入到 join buffer 中,减少额外的内存消耗。
  4. 能够直接多表关联的尽量直接关联,不用子查询,因为子查询的效率更加低。
  5. 在 sql 的查询计划的 extra 中,尽量避免出现 Using join buffer,有这个表示使用了块嵌套循环连接算法,尽量通过索引去解决。
  6. 尽量避免超过 3 张表以上的关联查询。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_1006456.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Go——数组

Golang Array和以往认知的数组有很大的。 数组是同一种数据类型的固定长度的序列。数组定义:var a[len] int,比如:var a [5]int,数组长度必须是常量,且类型的组成部分。一旦定义,长度不能变。长度是数组类…

计算机网络-数据链路层

一、认识以太网 "以太网" 不是⼀种具体的网络,而是一种技术标准; 既包含了数据链路层的内容, 也包含了⼀些物理 层的内容。 例如:规定了网络拓扑结构,访问控制方式,传输速率等; 例如:以太网中的网线必须使用…

Java对接(BSC)币安链 | BNB与BEP20的开发实践(三)水龙头 WEB3

上一节我们用代码来实现BNB转账、BEP20转账、链上交易监控 这一节我们讲一个币安测试链如何获取到BNB、USDT等BEP20数字货币&#xff08;水龙头&#xff09;来让我们前期测试开发。 首先我们先来创建一个地址&#xff1a; /*** 创建地址(离线)*/Overridepublic Map<Strin…

python单例模式应用之pymongo连接

文章目录 单例模式介绍模块简介安装简单的连接使用单例模式的连接单例类的实现配置的使用单例模式的测试 单例连接的调用 单例模式介绍 适用场景&#xff1a; 单例模式只允许创建一个对象&#xff0c;因此节省内存&#xff0c;加快对象访问速度&#xff0c;因此对象需要被公用…

大数据开发 hadoop集群1. 概论

我不惧怕风暴&#xff0c; 因为我在学习如何驾驶风帆 ——《小妇人》 —— 24.3.10 一、大数据的概念 大数据&#xff1a;指无法在一定时间范围内用常规软件工具进行捕捉、管理和处理的数据集合&#xff0c;是需要新处理模式才能具有更强的决策力、洞察发现力和流程优化能力的海…

[JAVAEE]—进程和多线程的认识

文章目录 什么是线程什么是进程进程的组成什么是pcb 进程概括线程线程与进程的关系线程的特点 创建线程创建线程方法创建线程的第二种方法对比 其他的方式匿名内部类创建线程匿名内部类创建Runable的子类lambda表达式创建一个线程 多线程的优势 什么是线程 什么是进程 首先想…

Mysql锁与MVCC

文章目录 Mysql锁的类型锁使用MVCC快照读和当前读读视图【Read View】串行化的解决 exlpain字段解析ACID的原理日志引擎整合SpringBoot博客记录 Mysql锁的类型 MySQL中有哪些锁&#xff1a; 乐观锁&#xff08;Optimistic Locking&#xff09;&#xff1a;假设并发操作时不会发…

【PyTorch】进阶学习:BCEWithLogitsLoss在多标签分类任务中的正确使用---logits与标签形状指南

【PyTorch】进阶学习&#xff1a;BCEWithLogitsLoss在多标签分类任务中的正确使用—logits与标签形状指南 &#x1f308; 个人主页&#xff1a;高斯小哥 &#x1f525; 高质量专栏&#xff1a;Matplotlib之旅&#xff1a;零基础精通数据可视化、Python基础【高质量合集】、PyTo…

(黑马出品_高级篇_03)SpringCloud+RabbitMQ+Docker+Redis+搜索+分布式

&#xff08;黑马出品_高级篇_03&#xff09;SpringCloudRabbitMQDockerRedis搜索分布式 微服务技术——多级缓存 今日目标1.什么是多级缓存2.JVM进程缓存2.1.导入案例2.1.1.安装MySQL2.1.1.1.准备目录2.1.1.2.运行命令2.1.1.3.修改配置 2.1.1.4.…

Redis-自动过期

1 EXPIRE、PEXPIRE&#xff1a;设置生存时间 用户可以通过执行EXPIRE命令或者PEXPIRE命令为键设置一个生存时间&#xff08;Time To Live, TTL&#xff09;&#xff1a;键的生存时间在设置之后就会随着时间的流逝而不断地减少&#xff0c;当一个键的生存时间被消耗殆尽时&#…

新IDEA电脑环境设置

1.设置UTF-8 2.Maven 3.JRE选对

Java EE之wait和notify

一.多线程的执行顺序 由于多个线程执行是抢占式执行&#xff0c;就会导致顺序不同&#xff0c;同时就会导致出现问题&#xff0c;就比如俩个线程同时对同一个变量进行修改&#xff0c;我们难以预知执行顺序。 但在实际开发中&#xff0c;我们希望代码按一定的逻辑顺序执行&am…

Vite为什么比Webpack快

本文作者为 360 奇舞团前端开发工程师 一.引言 Vite和Webpack作为两个主流的前端构建工具&#xff0c;在近年来备受关注。它们的出现使得前端开发变得更加高效和便捷。然而&#xff0c;随着前端项目规模的不断增大和复杂度的提升&#xff0c;构建工具的性能优化也成为了开发者关…

四川宏博蓬达法律咨询有限公司:法律服务的行业翘楚

在当今社会&#xff0c;法律服务已经成为人们生活中不可或缺的一部分。随着法律意识的提高&#xff0c;选择一家专业、可靠的法律咨询公司显得尤为重要。四川宏博蓬达法律咨询有限公司&#xff0c;作为业内的佼佼者&#xff0c;以其卓越的服务质量和广泛的业务范围&#xff0c;…

基于遗传算法GA的机器人栅格地图最短路径规划,可以自定义地图及起始点(提供MATLAB代码)

一、原理介绍 遗传算法是一种基于生物进化原理的优化算法&#xff0c;常用于求解复杂问题。在机器人栅格地图最短路径规划中&#xff0c;遗传算法可以用来寻找最优路径。 遗传算法的求解过程包括以下几个步骤&#xff1a; 1. 初始化种群&#xff1a;随机生成一组初始解&…

STM32 利用FlashDB库实现在线扇区数据管理不丢失

STM32 利用FlashDB库实现在线扇区数据管理不丢失 &#x1f4cd;FalshDB地址:https://gitee.com/Armink/FlashDB ✨STM32没有片内EEPROM这样的存储区&#xff0c;虽然有备份寄存器&#xff0c;仅可以实现对少量数据的频繁存储&#xff0c;但是依赖备份电源&#xff08;BAT引脚&a…

vs2022的下载及安装教程(Visual Studio 2022)

vs简介 Visual Studio在团队项目开发中使用非常多且功能强大&#xff0c;支持开发人员编写跨平台的应用程序;Microsoft Visual C 2022正式版(VC2022运行库)&#xff0c;具有程序框架自动生成&#xff0c;灵活方便的类管理&#xff0c;强大的代码编写等功能&#xff0c;可提供编…

RabbitMQ - 06 - Topic交换机

目录 控制台创建队列与交换机 编写消费者方法 编写生产者测试方法 结果 Topic交换机与Direct交换机基本一致 可参考 这篇帖子 http://t.csdnimg.cn/AuvoK topic交换机与Direct交换机的区别是 Topic交换机接收的消息RoutingKey必须是多个单词&#xff0c;以 . 分割 Topic交…

前端 - 笔记 - JavaScript - WebAPI【DOM + 事件类型 + Date+ 节点操作 + window + 本地存储 + 正则表达式】

前言 Web API&#xff1a;是一套操作 网页内容&#xff08;DOM&#xff09; 与 浏览器窗口&#xff08;BOM&#xff09; 的 对象&#xff1b; API&#xff1a;就是一些预定义好的方法&#xff0c;这些方法可以实现特定的功能&#xff0c;开发人员可以直接使用&#xff1b;Web …

2.案例、鼠标时间类型、事件对象参数

案例 注册事件 <!-- //disabled默认情况用户不能点击 --><input type"button" value"我已阅读用户协议(5)" disabled><script>// 分析&#xff1a;// 1.修改标签中的文字内容// 2.定时器// 3.修改标签的disabled属性// 4.清除定时器// …