【MySQL精通之路】SQL优化(1)-查询优化(12)-块嵌套循环和批处理Key访问联接
在MySQL中,可以使用批处理Key访问(BKA)联接算法,该算法使用对联接表的索引访问和联接缓冲区。
BKA算法支持内联接、外联接和半联接操作,包括嵌套的外部联接。
BKA的优点包括由于更高效的表扫描而提高了联接性能。
此外,以前仅用于内部联接的块嵌套循环(BNL)联接算法得到了扩展,可以用于外部联接和半联接操作,包括嵌套的外部联接。
以下部分讨论连接缓冲区管理,它是原始BNL算法、扩展的BNL算法和BKA算法扩展的基础。
有关半联接策略的信息,请参阅“使用半联接转换优化IN和EXISTS子查询语句”
1.连接缓冲区管理
MySQL不仅可以使用联接缓冲区来执行内部联接,而无需对内部表进行索引访问,还可以使用子查询展开后出现的外部联接和半联接。此外,当存在对内部表的索引访问时,可以有效地使用联接缓冲区。
连接缓冲区管理代码在存储感兴趣的行列的值时稍微更有效地利用连接缓冲区空间:
如果行列的数值为NULL,则不会在缓冲区中为其分配额外的字节,并且为VARCHAR类型的任何数值分配最小字节数。
该代码支持两种类型的缓冲区,常规缓冲区和增量缓冲区。假设联接缓冲器B1用于联接表t1和t2,并且该操作的结果使用联接缓冲器B2与表t3联接:
常规联接缓冲区包含来自每个联接操作数的列。如果B2是常规联接缓冲器,则放入B2的每一行r由B1中的行r1的列和表t3中的匹配行r2的感兴趣列组成。
增量联接缓冲区仅包含由第二个联接操作数生成的表的行中的列。也就是说,它是从第一个操作数缓冲区向一行递增的。如果B2是增量联接缓冲区,则它包含行r2的感兴趣的列以及从B1到行r1的链接。
增量连接缓冲区相对于早期连接操作的连接缓冲区始终是增量的,因此第一次连接操作的缓冲区始终为常规缓冲区。在刚刚给出的示例中,用于连接表t1和t2的缓冲器B1必须是常规缓冲器。
用于联接操作的增量缓冲区的每一行只包含要联接的表中一行中感兴趣的列。通过引用第一个联接操作数生成的表中匹配行中感兴趣的列来扩充这些列。增量缓冲区中的几行可以引用同一行r,只要所有这些行都与行r匹配,则该行的列存储在先前的联接缓冲区中。
增量缓冲区可以减少从用于先前联接操作的缓冲区复制列的频率。这提供了缓冲区空间的节省,因为在一般情况下,第一联接操作数产生的行可以与第二联接操作数生成的若干行相匹配。没有必要从第一个操作数复制一行的多个副本。由于复制时间的减少,增量缓冲区还可以节省处理时间。
在MySQL 8.0中,optimizer_switch系统变量的block_nested_roop标志的工作原理如下:
在MySQL 8.0.20之前,它控制优化器如何使用块嵌套循环连接算法。
在MySQL 8.0.18及更高版本中,它还控制散列联接的使用(请参阅第10.2.1.4节“散列联接优化”)。
从MySQL 8.0.20开始,该标志仅控制哈希联接,不再支持块嵌套循环算法。
batched_key_access标志控制优化器如何使用batched key access联接算法。
默认情况下,block_nested_roop处于打开状态,batched_key_access处于关闭状态。
请参阅“可切换优化”。
也可以应用优化器提示;请参阅 块嵌套循环和批量密钥访问算法的优化器提示。
有关半联接策略的信息,请参阅“使用半联接转换优化IN和EXISTS子查询语句”
2.外联接和半联接的块嵌套循环算法
MySQL BNL算法的原始实现被扩展为支持外部联接和半联接操作
(后来被哈希联接算法取代;请参阅“哈希联接优化”)。
【MySQL精通之路】SQL优化(1)-查询优化(4)-Hash联接查询-CSDN博客
当使用联接缓冲区执行这些操作时,放入缓冲区的每一行都会被提供一个匹配标志。
如果使用联接缓冲区执行外部联接操作,则会检查第二个操作数生成的表的每一行是否与联接缓冲区中的每一行都匹配。当找到匹配项时,将形成一个新的扩展行(原始行加上第二个操作数中的列),并通过剩余的联接操作发送以进行进一步的扩展。此外,缓冲区中匹配行的匹配标志被启用。在检查了要联接的表的所有行之后,扫描联接缓冲区。缓冲区中未启用匹配标志的每一行都将通过NULL补码(第二个操作数中每列的NULL值)进行扩展,并通过剩余的联接操作进行进一步扩展。
在MySQL 8.0中,optimizer_switch系统变量的block_nested_roop标志的工作原理如下:
在MySQL 8.0.20之前,它控制优化器如何使用块嵌套循环连接算法。
在MySQL 8.0.18及更高版本中,它还控制散列联接的使用(请参阅“Hash联接优化”)。
【MySQL精通之路】SQL优化(1)-查询优化(4)-Hash联接查询-CSDN博客
从MySQL 8.0.20开始,该标志仅控制哈希联接,不再支持块嵌套循环算法。
有关更多信息,请参见第10.9.2节“可切换优化”。也可以应用优化器提示;请参阅块嵌套循环和批量密钥访问算法的优化器提示。
在EXPLAIN输出中,当Extra值包含Using join buffer(块嵌套循环)并且类型值为ALL、index或range时,表示表使用BNL。
有关半联接策略的信息,请参阅第10.2.2.1节“使用半联接转换优化IN和EXISTS子查询谓词”
3.批量Key访问联接
MySQL实现了一种连接表的方法,称为批处理密钥访问(BKA)连接算法。当对第二个联接操作数生成的表进行索引访问时,可以应用BKA。与BNL联接算法一样,BKA联接算法使用联接缓冲区来累积由联接运算的第一个操作数产生的行的感兴趣的列。然后,BKA算法为缓冲区中的所有行构建访问要连接的表的键,并将这些键分批提交给数据库引擎进行索引查找。钥匙通过多量程读取(MRR)接口提交给发动机(参见第10.2.1.11节“多量程读取优化”)。提交键后,MRR引擎功能以最佳方式在索引中执行查找,获取由这些键找到的联接表的行,并开始向BKA联接算法提供匹配的行。每个匹配行都与连接缓冲区中一行的引用相耦合。
当使用BKA时,join_buffer_size的值定义了向存储引擎发出的每个请求中密钥批的大小。缓冲区越大,对联接操作的右侧表的顺序访问就越多,这可以显著提高性能。
对于要使用的BKA,优化器切换系统变量的batched_key_access标志必须设置为on。BKA使用MRR,因此MRR标志也必须为on。目前,MRR的成本估计过于悲观。因此,也有必要关闭mrr_cost_based以使用BKA。以下设置启用BKA:
mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
执行MRR功能有两种情况:
第一种场景用于传统的基于磁盘的存储引擎,如InnoDB和MyISAM。对于这些引擎,通常会将连接缓冲区中所有行的密钥一次提交到MRR接口。特定于引擎的MRR函数对提交的键执行索引查找,从中获取行ID(或主键),然后根据BKA算法的请求逐个获取所有这些选定行ID的行。每一行都返回一个关联引用,该引用允许访问联接缓冲区中匹配的行。MRR函数以最佳方式提取行:按行ID(主键)顺序提取行。这提高了性能,因为读取是按磁盘顺序而不是随机顺序进行的。
第二种场景用于NDB等远程存储引擎。MySQL Server(SQL节点)将联接缓冲区中一部分行的密钥包及其关联发送到MySQL Cluster数据节点。作为回报,SQL节点接收与相应关联耦合的匹配行的包(或多个包)。BKA联接算法获取这些行并构建新的联接行。然后,将一组新的键发送到数据节点,并使用返回包中的行来构建新的连接行。该过程一直持续到连接缓冲区中的最后一个键被发送到数据节点,并且SQL节点已经接收并连接了所有与这些键匹配的行。这提高了性能,因为SQL节点向数据节点发送的密钥承载包更少,意味着它与数据节点之间执行联接操作的往返次数更少。
在第一种情况下,联接缓冲区的一部分被保留来存储由索引查找选择的行ID(主键),并作为参数传递给MRR函数。
没有特殊的缓冲区来存储为联接缓冲区中的行构建的键。相反,为缓冲区中的下一行构建键的函数将作为参数传递给MRR函数。
在EXPLAIN输出中,当Extra值包含Using join buffer(Batched Key Access)并且类型值为ref或eq_ref时,表示表使用BKA。
4.优化器提示
除了使用optimizer_switch系统变量来控制优化器在会话范围内使用BNL和BKA算法之外,MySQL还支持优化器提示,以在每条语句的基础上影响优化器。
参见“优化器提示”。
若要使用BNL或BKA提示为外部联接的任何内部表启用联接缓冲,则必须为该外部联接的所有内部表启用联接缓冲。
相关文章:
【MySQL精通之路】SQL优化(1)-查询优化(12)-块嵌套循环和批处理Key访问联接
在MySQL中,可以使用批处理Key访问(BKA)联接算法,该算法使用对联接表的索引访问和联接缓冲区。 BKA算法支持内联接、外联接和半联接操作,包括嵌套的外部联接。 BKA的优点包括由于更高效的表扫描而提高了联接性能。 此…...
SQL使用函数给多个分表添加同一字段
数据库中分表时,往往需要向多个分表中添加同一个字段,可以定义一个函数,每次调用这个函数向多个份表中添加同意字段。 1、创建函数示例: 在PostgreSQL中创建一个简单的函数 以下是一个在PostgreSQL中创建函数的简单示例&#x…...
OpenAI 再次刷新认知边界:GPT-4 颠覆语音助手市场,流畅度直逼真人互动?
前言 近日,美国人工智能研究公司 OpenAI 发布了其最新旗舰模型 GPT-4o,这一革命性的进展不仅标志着人工智能领域的新突破,更预示着即将步入一个全新的交互时代?GPT-4o 的发布,对于我们来说,意味着人工智能…...
UE5 使用外置摄像头进行拍照并保存到本地
连接外置摄像头功能:https://docs.unrealengine.com/4.27/zh-CN/WorkingWithMedia/IntegratingMedia/MediaFramework/HowTo/UsingWebCams/ 核心功能:UE4 相机拍照功能(图片保存)_ue 移动端保存图片-CSDN博客 思路是: …...
【C++】从零开始map与set的封装
送给大家一句话: 今日的事情,尽心、尽意、尽力去做了,无论成绩如何,都应该高高兴兴地上床恬睡。 – 三毛 《亲爱的三毛》 🌃🌃🌃🌃🌃🌃🌃&#x…...
Python可以声明并赋值一个hash类型变量吗?
在Python中,不能直接声明一个变量为hash类型,因为Python是一种动态类型语言,不需要(也不能)在声明变量时指定其类型。变量的类型是根据赋给它的值自动推断的。 将一个哈希值(即一个整数)赋值给…...
苗情灾情监控系统—提高农业生产效率
TH-MQ2苗情灾情监控系统是一种用于监测农作物生长状况和灾情的设备,通过实时监测和数据分析,帮助农民及时了解作物生长情况,采取相应的管理措施,提高农业生产效率和降低生产成本。 该系统通常由多种传感器、摄像头、数据传输模块等…...
wpf自定义按钮样式
在WPF中,自定义按钮样式可以通过创建一个ControlTemplate来实现。以下是一个简单的自定义按钮样式的例子: 首先,在你的WPF项目资源字典中定义按钮的ControlTemplate。 <Window.Resources><ControlTemplate x:Key"CustomButto…...
Meme币总市值突破630亿美元 以太坊ETF获批意味着代币化资产“完全安全”
近日,数字货币市场再次掀起轩然大波。一方面,Meme币总市值突破了630亿美元,令人瞠目结舌;另一方面,以太坊ETF的获批也引发了市场的广泛关注,被视为代币化资产的“完全安全”标志。 Meme币总市值飙升 Meme币…...
MySQL数据库语法(二)
一、数据库的创建 创建数据库CRATE DATABASE语法:CREATE DATABASE [IF NOT EXISTS]数据库名;功能:用给定的名字创建一个数据库如果数据库已经存在,发生一个错误。查看创建数据库:SHOW CREATE DATABASE <数据库名>ÿ…...
Linux makefile
Linux makefile 用makefile去自动编译和删除静态库和动态库 在实际开发中,项目的源代码文件比较多,按类型、功能、模块分别存放在不同的目录和文件中,哪些文件需要先编译,那些文件后编译,那些文件需要重新编译…...
信息安全基础知识
信息安全基础知识 安全策略表达模型是一种对安全需求与安全策略的抽象概念表达,一般分为自主访问控制模型(HRU)和强制访问控制模型(BLP、Biba)IDS基本原理是通过分析网络行为(访问方式、访问量、与历史访问…...
【数据结构】链式二叉树(超详细)
文章目录 前言二叉树的链式结构二叉树的遍历方式二叉树的深度优先遍历前序遍历(先根遍历)中序遍历(中根遍历)后序遍历(后根遍历) 二叉树的广度优先遍历层序遍历 二叉树链式结构接口实现二叉树结点个数二叉树叶子结点个数二叉树的深度(高度)二叉树第k层结…...
排序题目:最小绝对差
文章目录 题目标题和出处难度题目描述要求示例数据范围 解法思路和算法代码复杂度分析 题目 标题和出处 标题:最小绝对差 出处:1200. 最小绝对差 难度 2 级 题目描述 要求 给定整数数组 arr \texttt{arr} arr,其中每个元素都不相同&…...
沃飞携AE200真机亮相澳门,全方位赋能城市低空出行
5月22日-25日,第四届BEYOND国际科技创新博览会(BEYOND Expo 2024)在澳门盛大举行。吉利沃飞长空携旗下全自研产品AE200真机亮相,吸引了现场众多领导嘉宾以及媒体、观众的关注。 作为亚洲顶尖的年度科技盛会,本届BEYOND…...
判断当前系统是linux、windows还是MacOS (python)
在很多情况下,需要在python中获取当前系统的类型,用于判断是unix/windows/mac或者java虚拟机等,python中提供了os.name, sys.platform, platform.system等方式 sys sys.platform会返回当前系统平台的标识符ÿ…...
Minikube部署单节点Kubernetes
1.1 Minikube部署单节点K8s Minikube是由Kubernetes社区维护的单机版的Kubernetes集群,支持macOS, Linux, andWindows等多种操作系统平台,使用最新的官方stable版本,并支持Kubernetes的大部分功能,从基础的容器编排管理࿰…...
leetcode-顺时针旋转矩阵-111
题目要求 思路 1.假设现在有一个矩阵 123 456 789 2.我们可以根据19这个对角线将数据进行交换,得到矩阵 147 258 369 3.然后将矩阵每一行的数据再翻转,得到矩阵 741 852 963 代码实现 class Solution { public:vector<vector<int> > rot…...
解决GoLand无法Debug
goland 调试的的时候提示如下错误 WARNING: undefined behavior - version of Delve is too old for Go version 1.22.3 (maximum supported v 其实个原因是因为正在使用的Delve调试器版本太旧,无法兼容当前的Go语言版本1.22.3。Delve是Go语言的一个调试工具&#…...
云原生周刊:K8s 上的 gRPC 名称解析和负载平衡
开源项目推荐 Kraken Kraken 是一个基于 P2P 的 Docker 注册表,专注于可扩展性和可用性。它专为混合云环境中的 Docker 镜像管理、复制和分发而设计。借助可插拔的后端支持,Kraken 可以轻松集成到现有的 Docker 注册表设置中作为分发层。 E2E Framewo…...
Linux链表操作全解析
Linux C语言链表深度解析与实战技巧 一、链表基础概念与内核链表优势1.1 为什么使用链表?1.2 Linux 内核链表与用户态链表的区别 二、内核链表结构与宏解析常用宏/函数 三、内核链表的优点四、用户态链表示例五、双向循环链表在内核中的实现优势5.1 插入效率5.2 安全…...
CVPR 2025 MIMO: 支持视觉指代和像素grounding 的医学视觉语言模型
CVPR 2025 | MIMO:支持视觉指代和像素对齐的医学视觉语言模型 论文信息 标题:MIMO: A medical vision language model with visual referring multimodal input and pixel grounding multimodal output作者:Yanyuan Chen, Dexuan Xu, Yu Hu…...
mongodb源码分析session执行handleRequest命令find过程
mongo/transport/service_state_machine.cpp已经分析startSession创建ASIOSession过程,并且验证connection是否超过限制ASIOSession和connection是循环接受客户端命令,把数据流转换成Message,状态转变流程是:State::Created 》 St…...
【大模型RAG】Docker 一键部署 Milvus 完整攻略
本文概要 Milvus 2.5 Stand-alone 版可通过 Docker 在几分钟内完成安装;只需暴露 19530(gRPC)与 9091(HTTP/WebUI)两个端口,即可让本地电脑通过 PyMilvus 或浏览器访问远程 Linux 服务器上的 Milvus。下面…...
linux arm系统烧录
1、打开瑞芯微程序 2、按住linux arm 的 recover按键 插入电源 3、当瑞芯微检测到有设备 4、松开recover按键 5、选择升级固件 6、点击固件选择本地刷机的linux arm 镜像 7、点击升级 (忘了有没有这步了 估计有) 刷机程序 和 镜像 就不提供了。要刷的时…...
NLP学习路线图(二十三):长短期记忆网络(LSTM)
在自然语言处理(NLP)领域,我们时刻面临着处理序列数据的核心挑战。无论是理解句子的结构、分析文本的情感,还是实现语言的翻译,都需要模型能够捕捉词语之间依时序产生的复杂依赖关系。传统的神经网络结构在处理这种序列依赖时显得力不从心,而循环神经网络(RNN) 曾被视为…...
Android第十三次面试总结(四大 组件基础)
Activity生命周期和四大启动模式详解 一、Activity 生命周期 Activity 的生命周期由一系列回调方法组成,用于管理其创建、可见性、焦点和销毁过程。以下是核心方法及其调用时机: onCreate() 调用时机:Activity 首次创建时调用。…...
Java + Spring Boot + Mybatis 实现批量插入
在 Java 中使用 Spring Boot 和 MyBatis 实现批量插入可以通过以下步骤完成。这里提供两种常用方法:使用 MyBatis 的 <foreach> 标签和批处理模式(ExecutorType.BATCH)。 方法一:使用 XML 的 <foreach> 标签ÿ…...
人工智能(大型语言模型 LLMs)对不同学科的影响以及由此产生的新学习方式
今天是关于AI如何在教学中增强学生的学习体验,我把重要信息标红了。人文学科的价值被低估了 ⬇️ 转型与必要性 人工智能正在深刻地改变教育,这并非炒作,而是已经发生的巨大变革。教育机构和教育者不能忽视它,试图简单地禁止学生使…...
【SSH疑难排查】轻松解决新版OpenSSH连接旧服务器的“no matching...“系列算法协商失败问题
【SSH疑难排查】轻松解决新版OpenSSH连接旧服务器的"no matching..."系列算法协商失败问题 摘要: 近期,在使用较新版本的OpenSSH客户端连接老旧SSH服务器时,会遇到 "no matching key exchange method found", "n…...
