MySQL的JOIN到底是怎么玩的

本文涉及的产品
云数据库 Tair(兼容Redis),内存型 2GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 在MySQL中,查询操作通常会涉及到联结不同表格,而JOIN命令则在这一过程中扮演了关键角色。在JOIN操作中,我们通常会使用三种不同的方式,分别是内连接、左连接以及右连接。

高手回答

在MySQL中,查询操作通常会涉及到联结不同表格,而JOIN命令则在这一过程中扮演了关键角色。在JOIN操作中,我们通常会使用三种不同的方式,分别是内连接、左连接以及右连接。

  • INNER JOIN(内连接,或称为等值连接):此操作获取了两个表中字段相互匹配的记录,实质上是取得了这两个表的交集部分。
  • LEFT JOIN(左连接):相较于内连接,左连接获取了左表格的所有记录,即便在右表格中可能没有对应的匹配记录。这样,查询结果将包含两个表格的交集部分,以及左表格中的所有数据。
  • RIGHT JOIN(右连接):右连接与左连接相反,它主要用于获取右表格中的所有记录,即便在左表格中找不到对应的匹配数据。因此,RIGHT JOIN同样会取得两个表格的交集部分,以及右表格中的所有数据。

在实施JOIN操作时,还常常会搭配上关键字ON,用以明确指定关联查询的一些条件。

嵌套循环算法

MySQL通常采用嵌套循环(Nested-Loop Join)的方法来执行关联查询,具体而言,主要包括简单嵌套循环连接(Simple Nested Loop Join)、块状嵌套循环连接(Block Nested Loop Join)和索引嵌套循环连接(Index Nested Loop Join)这三种算法。

然而,这三种算法的效率均未能达到特别的高水平。

  • 简单嵌套循环:该算法直截了当,通过全面扫描连接两张表来进行逐一数据比对,因此其复杂度可以被视为N*M,其中N是驱动表的数量,而M是被驱动表的数量。
  • 索引嵌套循环:如果内循环表中的字段具有索引,索引嵌套循环会利用该索引来查询数据。由于索引是基于B+树的,因此复杂度近似为N*logM。
  • 块状嵌套循环:这种算法引入了一个缓冲区(Buffer),它会提前将外循环的一部分结果存放在JOIN BUFFER中,然后内循环中的每一行都与整个缓冲区中的数据进行比较。尽管比较次数仍为N*M,但由于JOIN BUFFER是基于内存的,因此效率大大提高。

尽管MySQL已经尽力优化这些算法,但这几种算法的复杂度仍然相对较高。这也是为何不建议在数据库中频繁进行多表JOIN的原因。随着表格数量和数据量的增加,JOIN操作的效率会指数级下降。

当无法使用JOIN进行关联查询时,可以考虑使用子查询、临时表或者联合查询等方式来实现相同的查询需求。

如果不能通过数据库做关联查询,那么需要查询多表的数据的时候要怎么做呢?

主要有两种做法:

1、在内存中自己做关联,即先从数据库中把数据查出来之后,我们在代码中再进行二次查询,然后再进行关联。

2、数据冗余,那就是把一些重要的数据在表中做冗余,这样就可以避免关联查询了。

3、宽表,就是基于一定的join关系,把数据库中多张表的数据打平做一张大宽表,可以同步到ES或者干脆直接在数据库中直接查都可以

若无法通过数据库进行关联查询,处理涉及多表数据的情况,常见的做法有两种:

  1. 在内存中自行关联:首先从数据库中检索数据,然后在程序中执行第二次查询,随后进行关联操作。
  2. 数据冗余:通过在表中存储一些重要数据的冗余副本,可以避免进行关联查询。
  3. 宽表设计:基于一定的连接关系,将数据库中多个表的数据打平形成一个庞大的宽表,这个宽表可以同步到Elasticsearch(ES),或者直接在数据库中进行查询操作。

MySQL的Hash Join是什么?

在MySQL 8.0中新增的 Hash Join 算法是一种用于多表连接的算法。在此之前,MySQL通常使用嵌套循环(Nested-Loop Join)的方法来执行关联查询,然而嵌套循环算法在性能方面并不理想。因此,引入了 Hash Join 算法,旨在优化 Nested-Loop Join 的性能表现。

所谓的 Hash Join 实际上底层利用了哈希表。

Hash Join 是针对等值连接场景的优化方法,其基本原则是将驱动表的数据加载到内存中,并构建哈希表,这样只需遍历一次非驱动表,然后通过哈希查找在哈希表中寻找匹配的行,就能完成连接操作。

举个例子:

在上述的 left join SQL 中,在进行 Hash Join 过程时,主要包括两个步骤:构建和探测。

在构建阶段中,如果优化器经过优化选择了 employee 作为驱动表,那么就会将该驱动表的数据构建到哈希表中:
image.png

在探测阶段,当从 company 表中取出记录后,会到哈希表中查询匹配的数据,然后进行聚合操作。
image.png

需要注意的是,上述提到的哈希表是存在于内存中的。然而,内存是有限的(受到 join_buffer_size 的限制)。那么,如果内存无法容纳驱动表的数据怎么处理呢?那就不得不说一说基于磁盘的Hash Join了。

基于磁盘的Hash Join

基于磁盘的哈希连接

当驱动表中的数据量较大,无法一次性加载到内存中时,就需要考虑将数据存储在磁盘上。通过将哈希表的部分内容存储在磁盘上,可以分批加载和处理数据,减少对内存的需求。

在这种算法中,为了避免一个大型哈希表无法完全存储在内存中,可以采用分表的方法来解决。即通过哈希算法将驱动表分割成多个片段,并将临时分片写入磁盘。

这意味着将一个驱动表拆分成多个哈希表,并分别存储在磁盘上。

image.png

接下来是进行连接操作,在这个过程中,对被驱动表也会使用相同的哈希算法进行分区,以确定在哪个分区中。在确定分区后,首先要确认该分区是否已经被加载到内存中,如果已加载,则可以直接在内存中的哈希表中查找匹配的行。
image.png

如果哈希值对应的分区尚未加载到内存中,则需要从磁盘上读取该分区的数据到内存中的哈希表,并进行匹配。

这样不断重复进行,直至完成所有数据的连接操作,然后返回结果集。

如有问题,微信搜索【码上遇见你】。

好了,本章节到此告一段落。希望对你有所帮助,祝学习顺利。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
SQL 算法 关系型数据库
深入理解MySQL中的Join算法
在数据库处理中,Join操作是最基本且最重要的操作之一,它能将不同的表连接起来,实现对数据集的更深层次分析。
1529 8
深入理解MySQL中的Join算法
|
SQL 关系型数据库 MySQL
MySQL 之 LEFT JOIN 避坑指南
MySQL 之 LEFT JOIN 避坑指南
578 1
|
关系型数据库 MySQL
mysql join 实践
mysql join 实践
77 0
|
关系型数据库 MySQL
Mysql join 连接查询
Mysql join 连接查询
127 0
|
关系型数据库 MySQL
MySQL left join 查询 多条数据
MySQL left join 查询 多条数据
275 0
|
8月前
|
算法 关系型数据库 MySQL
join查询可以⽆限叠加吗?MySQL对join查询有什么限制吗?
大家好,我是 V 哥。本文详细探讨了 MySQL 中 `JOIN` 查询的限制及其优化方法。首先,`JOIN` 查询不能无限叠加,存在资源(CPU、内存、磁盘 I/O)、性能和语法等方面的限制。过多的 `JOIN` 操作会导致数据库性能急剧下降。其次,介绍了三种常见的 `JOIN` 查询算法:嵌套循环连接(NLJ)、索引嵌套连接(INL)和基于块的嵌套循环连接(BNL),并分析了它们的触发条件和性能特点。最后,分享了优化 `JOIN` 查询的方法,包括 SQL 语句优化、索引优化、数据库配置调整等。关注 V 哥,了解更多技术干货,点赞👍支持,一起进步!
170 3
|
算法 关系型数据库 MySQL
浅析MySQL Join Reorder算法
本文浅析了MySQL Join Reorder算法的流程,cost计算,剪枝算法等,希望通过本文能帮助大家了解MySQL优化器生成执行计划的具体流程。
|
存储 关系型数据库 MySQL
mysql中的left join、right join 、inner join的详细用法
【8月更文挑战第16天】在MySQL中,`INNER JOIN`、`LEFT JOIN`与`RIGHT JOIN`用于连接多表。`INNER JOIN`仅返回两表中匹配的行;`LEFT JOIN`保证左表所有行出现于结果中,右表无匹配时以NULL填充;`RIGHT JOIN`则相反,保证右表所有行出现于结果中。例如,查询学生及其成绩时,`INNER JOIN`仅显示有成绩的学生;`LEFT JOIN`显示所有学生及他们对应的成绩,无成绩者成绩列为空;`RIGHT JOIN`显示所有成绩及对应学生信息,无学生信息的成绩条目则为空。
781 1
|
SQL 关系型数据库 MySQL
Mysql中from多表跟join表的区别
Mysql中from多表跟join表的区别
917 0
|
SQL 存储 关系型数据库
深入理解MySQL中的UPDATE JOIN语句
MySQL的UPDATE JOIN语句用于根据关联表的条件更新数据。示例中,历史记录表有用户账号字段,新增列用于存储用户名。通过UPDATE JOIN,一次性将账号转换为用户名。关键点包括准确的连接条件、谨慎使用WHERE子句以及在更新前进行测试。此操作提高了数据处理效率,但也需小心操作以防止数据错误。
639 4
深入理解MySQL中的UPDATE JOIN语句

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多