Mysql join 正确的使用姿势

目录

前言

JOIN 类型

驱动表和被驱动表

关联算法 Nest Loop Join

Index Nested-Loop Join（NLJ）

Block Nested-Loop Join（BNL）

关联算法优化

Multi-Range Read （MRR）

Batched Key Access（BAK）

Hash Join 算法

小结

---

前言

在实际生产中，大部分业务都需要从多张数据表中读取数据。在实现上，我们可以使用单表查询，在代码层实现关联；当然更多的时候，我们会使用 关键字 join。 join 用于多表联合查询，直接在数据库层将多张表的数据拼接起来，不用再去代码层实现关联。

我们先创建如下两张表，方便后文的介绍：

-- 创建 t2 表
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

-- 创建 和 t2 表有相同表结构的 t1 表
create table t1 like t2;

JOIN 类型

在 MySQL 中有如下几种常见的 JOIN 类型：

• INNER JOIN：内连接，INNER 可以省略 ，返回两个表中字段匹配关系的记录；
• LEFT JOIN：左连接，返回左表中的所有记录，即使在右表没有对应匹配的记录；
• RIGHT JOIN：右连接，返回右表中的所有记录，即使左表没有对应匹配的记录。

驱动表和被驱动表

MySQL 表使用的关联算法是 Nest Loop Join，它通过将驱动表的结果集作为循环基础数据，然后从结果集中取出数据到被驱动表中继续查询，最后合并到最终的结果集中。

对应不同的 join 类型，驱动表的选择如下：

1、在左连接中，一般情况下，左表是驱动表，右表是被驱动表；

2、在右连接中，一般情况下，右表是驱动表，左表是被驱动表；

3、内连接比较特殊，优化器会自己选择 数据量较小的表 作为驱动表，而将数据量较大的表作为被驱动表，即 小表驱动大表。如果我们要指定驱动表，需要使用关键字 straight_join 。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算要放入 join_buffer 的各个字段的总数据量，数据量小的那个表，就是 “小表”，应该作为驱动表。

如果我们没有指定驱动表，怎么判别优化器选择的驱动表呢？

我们可以通过查看执行计划来判别，在输出的执行计划中，排在第一行的表是驱动表，排在第二行的表是被驱动表。

比如我们执行 explain select * from t1 join t2 on (t1.a = t2.a) ;

可以看到，优化器选择了 t1 作为驱动表， t2 作为被驱动表。

为了验证内连接是由优化器自己选择驱动表的效果，我们再执行 explain select * from t2 join t1 on (t1.a = t2.a) ； 看下：

可以看到，结果是一样的，优化器还是选择了 t1 作为驱动表， t2 作为被驱动表。

那为什么优化器会选择让小表驱动大表呢？这就必须聊聊 Nest Loop Join 的算法实现了。
 

关联算法 Nest Loop Join

根据被驱动表是否可以使用索引，关联算法可以分为 Index Nested-Loop Join 和 Block Nested-Loop Join：

• Index Nested-Loop Join：被驱动表上有索引可以使用；
• Block Nested-Loop Join：被驱动表上无索引可以使用。

我们以 t1 和 t2 表做例，通过执行不同的 SQL 语句来认识下两种算法。

Index Nested-Loop Join（NLJ）

对于 SQL 语句 select * from t1 straight_join t2 on (t1.a = t2.a) ; 被驱动表 t2 的 a 字段是一个索引字段，执行过程如下：

1、从 t1 表中读入一行数据 R；

2、从 R 中 取出字段 a 去 t2 表中查询；

3、取出 t2 表中满足查询条件 t2.a = t1.a 的行，然后跟 R 组成一行，添加到结果集中；

4、重复步骤 1 到 3，直到 t1 表的结尾。

我们来粗略计算一下以上执行过程的时间复杂度；不失一般性，我们暂时假设 t1 表有 N 行记录， t2 表有 M 行记录：

第一步需要执行 N 次，时间复杂度即为 N；

第二步也需要执行 N 次；对于被驱动表 t2 的查询，因为字段 a 是一个索引字段，对 a 的查询走的是树搜索过程。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M；再考虑到回表操作，我们可以得到，每次执行步骤 2 的时间复杂度是 2* log2M，因此，这第二步的时间复杂度为 2N * log2M；

至此可以得到，整个执行过程，近似复杂度是 N + 2N*log2M。

从这个时间复杂度表达式可以看到，驱动表的行数对整体的时间复杂度影响比被驱动表更大；也就是说，在被驱动表可以使用索引的时候，采用小表驱动大表会更优。

Block Nested-Loop Join（BNL）

现在我们执行 SQL语句: select * from t1 straight_join t2 on (t1.a=t2.b)； b 字段不是索引字段，此时的执行过程如下：

1、把表 t1 的数据读入 线程内存 join_buffer 中，这里会将整个表 t1 放入了内存；

2、扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

对于以上过程的时间复杂度计算很简单，我们同样假设 t1 表有 N 行记录， t2 表有 M 行记录；那该过程的时间复杂度就是 N * M。

此时，你会发现，无论使用哪张表作为驱动表，时间复杂度都是 N * M。那小表驱动大表这个原则是否还成立呢？

我们重新看下上面的执行过程，你应该会有一个疑问，在第一步中，要将 t1 的数据全部放到 join_buffer 中，那如果数据量很大的话能放的下吗？

其实，join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下驱动表表 t1 所有的数据，就需要对 t1 表进行分段处理。

分段处理其实很简单，我们直接通过一个查询过程来阐述一下：

还是以上 SQL 语句，假设 join_buffer 此时只够存放 50 行 t1 表的数据，此时执行过程如下：

1、扫描表 t1，顺序读取数据行放入 join_buffer 中，放完 50 行数据后发现 join_buffer 满了，此时不能再往 join_buffer 中添加数据了，只能执行第 2 步；

2、扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，对于满足 join 条件的，作为结果集的一部分返回；扫描完 t2 表以后执行第三步；

3、清空 join_buffer；

4、重复步骤 1 到 3，直到 t1 表的结尾。

可以看到，该过程的执行时间复杂度和不分段是一样的，都是 N*M。但是会对被驱动表进行更多次地扫描，因为每次分段后都要对 t2 表进行一次全量扫描，且扫描次数和 t1 表的分段数成正比。

至此，我们得到，驱动表分的段数越多，需要扫描被驱动表的次数也就越多。

而分段次数由驱动表的行数 和 join_buffer_size 决定，即 驱动表行数越少，join_buffer_size 越大都能让被驱动表扫描次数越少。

从这个角度考虑，我们看到，即使被驱动表不能使用索引，采用小表驱动大表依然是一个更好的选择。

关联算法优化

上节已经介绍了两种关联算法，对于开发人员而言，我们要做的就是尽量利用 NLJ 算法来降低关联查询的时间复杂度。而其实对于 NLJ 算法，在 MySQL 5.6 版本后，还引入了一个更优的算法 - Batched Key Access（BKA）算法。

在介绍 BKA 算法之前，我们先来了解一个知识点 Multi-Range Read （MRR）。

Multi-Range Read （MRR）

还记得 回表 的概念吗？我们回顾一下，回表是指，InnoDB 通过二级索引查询数据，在二级索引上只能查到主键值，而后只能再根据主键值去主键索引上去查整行数据。

如果我们执行的是一个范围查询语句，那你会不会有疑问，这个回表过程是要一条一条的回表查询，还是可以批量地回表查询呢？

其实是只能一条一条回表查询的，因为通过二级索引关联到的主键值不是一定有序的，因此，整个回表查询只能一条一条的查询，并且是个随机查询过程。

MRR 优化的设计思路就是优化这个随机查询过程：

因为大多数的数据都是按照主键递增顺序插入得到的，所以，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

它的实现方法就是暂存主键值，排序后再去主键索引查询。

比如 SQL 语句 select * from t1 where a>=1 and a<=100; 执行流程如下：

1、根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;

2、将 read_rnd_buffer 中的 id 进行递增排序；

3、排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

我们看到，MRR 能够提升性能的核心在于，查询语句在二级索引上做的是一个范围查询，可以得到足够多的主键值。这样通过排序以后，再去主键索引查数据，才能体现出 “顺序性” 的优势。

了解了 MRR 后，我们来看看 NLJ 的优化算法 BKA 算法。

Batched Key Access（BAK）

对于 SQL 语句 select * from t1 straight_join t2 on (t1.a = t2.a) ;

NLJ 的执行过程是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。

BKA 的优化思路是，想办法利用 MRR 来提高对被驱动表 t2 的查询效率。

实现方法：

利用 join_buffer 暂存从驱动表 t1 取出来的查询字段 a，这样就不用一行一行的去被驱动表 t2 进行查询了，而是可以一次发送多条数据到 t2 表，这样，t2 表就可以利用 MRR 来优化回表查询的过程了。

Hash Join 算法

对于驱动表没有索引可以使用的场景，从 MySQL 8.0.18 版本开始，正式使用 Hash Join 算法来取代 BNL 算法，到 8.0.20 的时候 BNL 就直接被移除了。

对于 SQL 语句: select * from t1 straight_join t2 on (t1.b=t2.b);

我们回顾下 BNL 算法执行过程：它会先将 t1 表的数据全部存入到 join_buffer，然后扫描 t2 表，一行一行地取出数据和 t1 表做数据比对，最后将符合条件的数据保存起来。这个执行复杂度是 t1 表的总行数 * t2 的总行数。

Hash Join 算法的优化策略是，将 t1 表的数据存放到一个 hash 表中，该 hash 表的 key 为 join 字段的 hash 值，比如前面我们举例的 SQL 语句，key 就是 hashFun(t1.b)；而后扫描 t2 表，取出 join 字段做哈希计算 hashFunc(t2.b)，得到 hash 值后就可以去 hash 表查询，最后将比对成功的数据保存到结果集中。

内存中 hash 表的大小是由参数 join_buffer_size 决定，同样的问题，如果在构建 hash 表时，驱动表的数据超过了 join_buffer_size ，该怎么办？

答案还是分段，但是会将多余的数据存放到磁盘文件，文件数由参数 open_files_limit 决定。

还以上面 SQL 为例，看下执行过程，我们假设 join_buffer_size 够存 t1 表的前 50 行数据，同时假设 open_files_limit = 10：

1、扫描表 t1，顺序读取数据行构建内存 hash 表，key = hashFunc1(t1.b) ，在加入50 行数据后，内存 hash 表已满，以后 t1 表的数据将分发到 10 个文件中（块文件），分发到哪个文件由 hashFunc2(t1.b) 决定；

2、扫描表 t2，对于 t2 中的每一行计算 hashFunc1(t2.b)，和内存 hash 表中数据进行匹配；

3、匹配完内存数据，还要匹配位于文件中 t1 表的数据，具体步骤为：

3.1、将 t2 表的数据通过 hashFunc2(t2.b) 分发到10个文件中（探测文件）；

3.2、循环处理10个块文件：用 hashFunc1(t1.b) 构建内存哈希数据，选择对应的探测文件逐行计算 hashFunc1(t2.b) 进行匹配。

这个过程有两个注意点：

1、磁盘文件大小不能超过 join_buffer_size，因为要将文件加入到 join_buffer 中；

2、计算落入哪个文件使用的哈希函数 hashFunc2() 和生成内存 hash 表的 key 的那个哈希函数 hashFunc1() 是不同的；原因也很简单，如果他们是相同的，那么在步骤 3.2 载入文件到内存 hash 表的时候，这一个文件内所有的数据都将得到同样的 key 值，这样这个内存 hash 表就失去意义了。

小结

本文主要介绍了 MySQL JOIN 的底层算法实现，按照被驱动表是否可以使用索引，MySQL 分别使用了 NLJ 和 BNL 算法 来关联数据；当然，最新版本的 MySQL 对两种算法都有了不同的替代优化算法，它们分别是 BKA 算法 和 HASH JOIN 算法。两种算法时间复杂度相差较大，我们应该尽量为被驱动表建立相应索引，使用 NLJ 或 BKA 关联算法。最后，不管哪种算法，都应遵守小表驱动大表的原则。

本文来源于《Mysql 45 讲》，个人笔记。