MySQL的JOIN到底是怎么玩的

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: 在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

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

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

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

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
13天前
|
SQL 关系型数据库 MySQL
MySQL 之 LEFT JOIN 避坑指南
MySQL 之 LEFT JOIN 避坑指南
35 1
|
13天前
|
关系型数据库 MySQL
mysql join 实践
mysql join 实践
23 0
|
7月前
|
SQL 算法 关系型数据库
深入理解MySQL中的Join算法
在数据库处理中,Join操作是最基本且最重要的操作之一,它能将不同的表连接起来,实现对数据集的更深层次分析。
371 8
深入理解MySQL中的Join算法
|
8月前
|
关系型数据库 MySQL
Mysql join 连接查询
Mysql join 连接查询
25 0
|
13天前
|
关系型数据库 MySQL
MySQL left join 查询 多条数据
MySQL left join 查询 多条数据
51 0
|
13天前
|
关系型数据库 MySQL 数据库
MySQL 数据库操作指南:LIMIT,OFFSET 和 JOIN 的使用
您可以通过使用"LIMIT"语句来限制查询返回的记录数量。以下是一个示例,获取您自己的Python服务器中"customers"表中的前5条记录:
106 1
|
6月前
|
关系型数据库 MySQL 索引
mysql join
mysql join
43 0
|
9月前
|
关系型数据库 MySQL
关于MySQL中的LEFT JOIN和LEFT OUTER JOIN的区别
LEFT JOIN是LEFT OUTER JOIN的简写版;
122 0
|
9月前
|
SQL 算法 关系型数据库
MySQL中的Join 的算法(NLJ、BNL、BKA)
MySQL中的Join 的算法(NLJ、BNL、BKA)
156 0
|
9月前
|
关系型数据库 MySQL
MySQL left join 查询 多条数据
MySQL left join 查询 多条数据
183 0

相关产品

  • 云数据库 RDS MySQL 版