MySQL 的 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 作为驱动表，那么就会将该驱动表的数据构建到哈希表中：

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

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

基于磁盘的 Hash Join

基于磁盘的哈希连接

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

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

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

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

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

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

如有问题，欢迎加微信交流：w714771310，备注- 技术交流  。或微信搜索【码上遇见你】。

免费的Chat GPT可微信搜索【AI贝塔】进行体现，无限使用。早用早享受

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