【MySQL精通之路】SQL优化(1)-查询优化(4)-Hash联接查询

news/2024/7/20 17:51:40/文章来源:https://blog.csdn.net/qq_29519041/article/details/139120416

 主博客:

【MySQL精通之路】SQL优化(1)-查询优化-CSDN博客

上一篇:

【MySQL精通之路】SQL优化(1)-查询优化(3)-索引合并-CSDN博客

下一篇:

【MySQL精通之路】SQL优化(1)-查询优化(5)-引擎条件下推-CSDN博客


默认情况下,MySQL(8.0.18及更高版本)尽可能使用Hash散列联接。可以使用BNLNO_BNL优化器提示中的一个来控制是否使用散列联接

或者通过设置optimizer_switch服务器系统变量中的block_nested_loop=onblock_nested_roop=off为来控制是否采用Hash联接。

注意:

MySQL 8.0.18支持在optimizer_switch中设置hash_join标志,以及优化器提示HASH_JOIN和NO_HASH_JOIN。

在MySQL 8.0.19及更高版本中,这些都不再有任何效果。

从MySQL 8.0.18开始,MySQL对任何查询都使用散列联接,其中每个联接都有一个等联接条件,并且其中没有可应用于任何联接条件的索引,例如以下查询:

SELECT *FROM t1JOIN t2ON t1.c1=t2.c1;

当有一个或多个索引可用于单表谓词时,也可以使用散列联接。

散列联接通常比MySQL的早期版本中使用的块嵌套循环算法

(请参见块嵌套循环联接算法)

更快

从MySQL 8.0.20开始,删除了对块嵌套循环的支持,并且使用散列联接替代块嵌套循环

在刚刚显示的示例和本节中的其余示例中,我们假设使用以下语句创建了三个表t1、t2和t3:

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

您可以看到,使用EXPLAIN使用了散列联接,如下所示:

mysql> EXPLAIN-> SELECT * FROM t1->     JOIN t2 ON t1.c1=t2.c1\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1filtered: 100.00Extra: NULL
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: t2partitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1filtered: 100.00Extra: Using where; Using join buffer (hash join)

 (在MySQL 8.0.20之前,有必要包含FORMAT=TREE选项,以查看Hash联接是否用于给定联接。)

EXPLAIN ANALYZE还显示有关所使用的Hash联接的信息。

Hash联接也用于涉及多个联接的查询,只要每对表至少有一个联接条件是等联接,就像这里显示的查询一样:

SELECT * FROM t1JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)JOIN t3 ON (t2.c1 = t3.c1);

在如刚才所示的使用内联接的情况下,任何非等联接的额外条件都将在执行联接后作为过滤器应用。(对于外部联接,如左联接半联接反联接,它们被打印为联接的一部分。)这可以在EXPLAIN的输出中看到:

mysql> EXPLAIN FORMAT=TREE-> SELECT *->     FROM t1->     JOIN t2->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)->     JOIN t3->         ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)-> Table scan on t3  (cost=0.35 rows=1)-> Hash-> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)-> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)-> Table scan on t2  (cost=0.35 rows=1)-> Hash-> Table scan on t1  (cost=0.35 rows=1)

从刚刚显示的输出中也可以看出,多个Hash联接可以(并且)用于具有多个等联接条件的联接。

在MySQL 8.0.20之前,如果任何一对联接的表都没有至少一个等联接条件,则不能使用哈希联接,并且使用较慢的块嵌套循环算法。在MySQL 8.0.20及更高版本中,这种情况下会使用散列联接,如下所示:

mysql> EXPLAIN FORMAT=TREE-> SELECT * FROM t1->     JOIN t2 ON (t1.c1 = t2.c1)->     JOIN t3 ON (t2.c1 < t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t3.c1)  (cost=1.05 rows=1)-> Inner hash join (no condition)  (cost=1.05 rows=1)-> Table scan on t3  (cost=0.35 rows=1)-> Hash-> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)-> Table scan on t2  (cost=0.35 rows=1)-> Hash-> Table scan on t1  (cost=0.35 rows=1)

(本节稍后将提供其他示例。)

散列联接也适用于笛卡尔乘积——也就是说,当没有指定联接条件时,如图所示:

mysql> EXPLAIN FORMAT=TREE-> SELECT *->     FROM t1->     JOIN t2->     WHERE t1.c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join  (cost=0.70 rows=1)-> Table scan on t2  (cost=0.35 rows=1)-> Hash-> Filter: (t1.c2 > 50)  (cost=0.35 rows=1)-> Table scan on t1  (cost=0.35 rows=1)

 在MySQL 8.0.20及更高版本中,联接不再需要包含至少一个等联接条件才能使用哈希联接。这意味着可以使用哈希联接优化的查询类型包括以下列表中的查询类型(带示例):

非等-内连接:

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t2.c1)  (cost=4.70 rows=12)-> Inner hash join (no condition)  (cost=4.70 rows=12)-> Table scan on t2  (cost=0.08 rows=6)-> Hash-> Table scan on t1  (cost=0.85 rows=6)

半连接:

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 ->     WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G
*************************** 1. row ***************************
EXPLAIN: -> Hash semijoin (t2.c2 = t1.c1)  (cost=0.70 rows=1)-> Table scan on t1  (cost=0.35 rows=1)-> Hash-> Table scan on t2  (cost=0.35 rows=1)

 反联接:

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 ->     WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Hash antijoin (t1.c1 = t2.c1)  (cost=0.70 rows=1)-> Table scan on t2  (cost=0.35 rows=1)-> Hash-> Table scan on t1  (cost=0.35 rows=1)1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G
*************************** 1. row ***************************Level: NoteCode: 1276
Message: Field or reference 't3.t2.c1' of SELECT #2 was resolved in SELECT #1

左外连接:

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Left hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)-> Table scan on t1  (cost=0.35 rows=1)-> Hash-> Table scan on t2  (cost=0.35 rows=1)

右外联接(注意MySQL将所有右外联接重写为左外联接):

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Left hash join (t1.c1 = t2.c1)  (cost=0.70 rows=1)-> Table scan on t2  (cost=0.35 rows=1)-> Hash-> Table scan on t1  (cost=0.35 rows=1)

默认情况下,MySQL 8.0.18及更高版本尽可能使用散列联接。可以使用BNL和NO_BNL优化器提示之一来控制是否使用散列联接。

(MySQL 8.0.18支持hash_join=onhash_join=off作为optimizer_switch服务器系统变量设置的一部分,以及优化器提示HASH_JOIN 或NO_HASH_JOIN。在MySQL 8.0.19及更高版本中,这些不再有任何作用。)

散列联接的内存使用可以使用join_buffer_size系统变量进行控制;

哈希联接使用的内存不能超过这个数量。

当哈希连接所需的内存超过可用量时,MySQL会使用磁盘上的文件来处理。

如果发生这种情况,您应该注意,如果哈希联接无法放入内存,并且它创建的文件数超过了为open_files_limit设置的文件数,则联接可能不会成功。

为避免此类问题,请进行以下任一更改:

增加join_buffer_size,使散列联接不会溢出到磁盘。

增加open_files_limit。

从MySQL 8.0.18开始,哈希连接连接缓冲区递增分配的;

因此,您可以将join_buffer_size设置得更高,而不需要小查询分配大量RAM,但外部联接会分配整个缓冲区

在MySQL 8.0.20及更高版本中,散列联接也用于外部联接(包括反联接和半联接),因此这不再是问题。

补充:

博主PS:

上文内容来自官网,刚读的时候肯定会懵逼。

那Hash联接的存在意义和功能是什么呢?

我们知道连表查询的时候,我们会以on 某个字段 语句来连接两张表。

hash联接就是数据库在这里建立了一张hash表,用于存放这个on的条件结果,

那么循环到下一个关联记录的时候,如果还是相同的关联条件的时候,就直接从hash表里定位结果。

一般建立小表的hash映射。比如小表是1000条记录,大表是1万条记录。小表我如果做hash了查询小表的时间复杂度是不是O(1)?大表没有hash我的查找是不是O(n)。两表连接查,就是1万次for循环。

大小表要是循环嵌套的方式连表判断,时间复杂度是不是就O(n^2),就是1000万次查询。

但是这里我小表hash计算位置了。那时间复杂度就只有大表的O(n)。你可以理解为双层for循环,变成单层了。

这就是Hash联接查询的意义。

当然也可以两表都建立hash。只是会耗费hash计算时间,和内存而已。但是查询更快。

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

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

相关文章

设计模式基础——设计原则介绍

1.概述 ​ 对于面向对象软件系统的设计而言&#xff0c;如何同时提高一个软件系统的可维护性、可复用性、可拓展性是面向对象设计需要解决的核心问题之一。面向对象设计原则应运而生&#xff0c;这些原则你会在设计模式中找到它们的影子&#xff0c;也是设计模式的基础。往往判…

数据结构的希尔排序(c语言版)

一.希尔排序的概念 1.希尔排序的基本思想 希尔排序是一种基于插入排序算法的优化排序方法。它的基本思想如下: 选择一个增量序列 t1&#xff0c;t2&#xff0c;......&#xff0c;tk&#xff0c;其中 ti > tj, 当 i < j&#xff0c;并且 tk 1。 按增量序列个数k&#…

音乐系统java在线音乐网站基于springboot+vue的音乐系统带万字文档

文章目录 音乐系统一、项目演示二、项目介绍三、万字项目文档四、部分功能截图五、部分代码展示六、底部获取项目源码和万字论文参考&#xff08;9.9&#xffe5;带走&#xff09; 音乐系统 一、项目演示 在线音乐系统 二、项目介绍 基于springbootvue的前后端分离在线音乐系…

创新指南|降低 TikTok CPA 的 9 项专家策略

企业在 TikTok 上投放广告&#xff0c;往往最想确保获得最佳的投资回报。然而&#xff0c;这往往说起来容易做起来难。您需要了解如何利用不同的营销工具、定位策略和创意执行来实现您的业务目标并提高成本效率。本文将分享 9 个行之有效的策略&#xff0c;助您有效降低 TikTok…

蓝桥杯备赛——DP续【python】

一、小明的背包2 试题链接&#xff1a;https://www.lanqiao.cn/problems/1175/learning/ 输入示例 5 20 1 6 2 5 3 8 5 15 3 3 输出示例 120 问题分析 这题是完全背包&#xff0c;每个物品有无数个&#xff0c;所以对于任意dp[i][j]&#xff08;其表示的意思为选到第i个…

关于我转生从零开始学C++这件事:升级Lv.25

❀❀❀ 文章由不准备秃的大伟原创 ❀❀❀ ♪♪♪ 若有转载&#xff0c;请联系博主哦~ ♪♪♪ ❤❤❤ 致力学好编程的宝藏博主&#xff0c;代码兴国&#xff01;❤❤❤ OK了老铁们&#xff0c;又是一个周末&#xff0c;大伟又来继续给大家更新我们的C的内容了。那么根据上一篇博…

【ai】LiveKit Agent 的example及python本地开发模式工程实例

title: ‘LiveKit Agent Playground’ playgroundLiveKit Community playground的环境变量&#xff1a;LiveKit API # LiveKit API Configuration LIVEKIT_API_KEYYOUR_API_KEY LIVEKIT_API_SECRETYOUR_API_SECRET# Public configuration NEXT_PUBLIC_LIVEKIT_URLwss://YOUR_…

论文阅读笔记:Task-Customized Mixture of Adapters for General Image Fusion

论文阅读笔记&#xff1a;Task-Customized Mixture of Adapters for General Image Fusion 1 背景2 创新点3 方法4 模块4.1 任务定制混合适配器4.2 提示生成4.3 提示驱动融合4.4 互信息正则化MIR4.5 任务定制化损失 5 实验5.1 VIF任务5.2 MEF任务5.3 MFF任务5.4 消融实验5.5 性…

IDEA社区版创建并运行maven管理的web项目的基本流程

一、前言 注意&#xff0c;这是社区版&#xff0c;旗舰版可以绕路。 二、过程 1、下载安装社区版 2、安装jdk&#xff0c;tomcat&#xff0c;maven 3、创建并启动项目 注意选择的骨架是maven-archetype-webapp&#xff0c;然后next&#xff0c;设置项目名&#xff0c;存放…

5.27作业

定义自己的命名空间my_sapce&#xff0c;在my_sapce中定义string类型的变量s1&#xff0c;再定义一个函数完成对字符串的逆置。 #include <iostream> #include <string.h>using namespace std; namespace my_space {string s1;void RevString(string &s1); } v…

香橙派 AIpro开发板初上手

一、香橙派 AIpro开箱 最近拿到了香橙派 AIpro&#xff08;OrangePi AIpro&#xff09;&#xff0c;下面就是里面的板子和相关的配件。包含主板、散热组件、电源适配器、双C口电源线、32GB SD卡。我手上的这个是8G LPDDR4X运存的版本。 OrangePi AIpro开发板是一款由香橙派与华…

操作系统实验--终极逃课方法

找到图片里的这个路径下的文件 &#xff0c;结合当前题目名称&#xff0c;把文件内容全部删除&#xff0c;改为print print的内容为下图左下角的预期输出的内容

Java入门基础学习笔记43——包

什么是包&#xff1f; 包是用来分门别类的管理各种不同程序的&#xff0c;类似文件夹&#xff0c;建包有利于程序的管理和维护。 建包的语法规则&#xff1a; package cn.ensource.javabean;public class Car() {} 在自己的程序中调用其他包下的程序的注意事项&#xff1a; 1…

若依ruoyi-vue element-ui 横向滚动条 动态横向滚动条

动态横向滚动条 因为每次横向滑动都要到底部&#xff0c;引入插件 https://github.com/mizuka-wu/el-table-horizontal-scroll //动态横向滚动条移入样式 .el-table-horizontal-scrollbar :hover{//高度 变大10%transform: scaleY(1.5) translateY(-10%);//百分之八十亮度&a…

大模型时代的具身智能系列专题(四)

google deepmind团队 谷歌旗下最大的两个 AI 研究机构——地处伦敦 DeepMind 与位于硅谷的 Google Brain 合并成立新部门 Google DeepMind。其将机器学习和系统神经科学的最先进技术结合起来&#xff0c;建立强大的通用学习算法。代表作有AlphaGo&#xff0c;AlphaStar&#x…

编程零基础,如何学习Python?

初学者选择Python入手着实是一个不错的方向&#xff0c;入手简单且广泛的运用是它最显著的特色了。 那有几个问题&#xff0c;我想是开始学习Python之前应该了解的&#xff0c; python能做什么&#xff1f; 发展前景与工作机会有哪些&#xff1f; 需要学习哪些内容&#xf…

【多态】(超级详细!)

【多态】&#xff08;超级详细&#xff01;&#xff09; 前言一、 多态的概念二、重写1. 方法重写的规则2. 重写和重载的区别 三、多态实现的条件四、 向上转型五、动态绑定 前言 面向对象的三大特征&#xff1a;封装性、继承性、多态性。 extends继承或者implements实现&…

Vue状态管理深度剖析:Vuex vs Pinia —— 从原理到实践的全面对比

&#x1f525; 个人主页&#xff1a;空白诗 文章目录 &#x1f44b; 引言&#x1f4cc; Vuex 基础知识核心构成要素示例代码 &#x1f4cc; Pinia 基础知识核心构成要素示例代码 &#x1f4cc; Vuex与Pinia的区别&#x1f4cc; 使用示例与对比&#x1f4cc; 总结 &#x1f44b;…

60. UE5 RPG 使用场景查询系统(EQS,Environment Query System)实现远程敌人寻找攻击位置

UE的Environment Query System&#xff08;EQS&#xff09;是环境查询系统&#xff0c;它是UE4和UE5中用于AI决策制定过程中的数据采集和处理的一个强大工具。EQS可以收集场景中相关的数据&#xff0c;利用生成器&#xff08;Generator&#xff09;针对用户的测试&#xff08;T…

Spring6基础笔记

Spring6 Log4j2 1、概述 1.1、Spring是什么&#xff1f; Spring 是一款主流的 Java EE 轻量级开源框架 &#xff0c;Spring 由“Spring 之父”Rod Johnson 提出并创立&#xff0c;其目的是用于简化 Java 企业级应用的开发难度和开发周期。Spring的用途不仅限于服务器端的开发…