MySQL8 的 Hash join 算法

以前 MySQL 的 join 算法只有 nested loop 这一种，在 MySQL8 中推出了一种新的算法 hash join，比 nested loop 更加高效。

下面我就看看它是怎么工作的。

用这个SQL作为例子：

hash join 工作过程分为2个阶段：

• build 构建阶段
• probe 探测阶段

1. 构建阶段

从参与join的2个表中选一个，选择占空间小的那个表，不是行数少的，这里假设选择了 countries 表。

对 countries 表中每行的 join 字段值进行 hash 计算：

hash(countries.country_id)

计算后放入内存中 hash table 的相应位置。

所有行都存放到 hash table 之后，构建阶段完成。

2. 探测阶段

对 persons 表中每行中的 join 字段的值进行 hash 计算：

hash(persons.country_id)

拿着计算结果到内存 hash table 中进行查找匹配，找到一行就发给 client。

这样就完成了整个 join 操作，每个表只扫描一次就可以了，扫描匹配时间也是恒定的，非常高效。

这个例子中，countries 表顺利的全部放入了内存，可用内存的大小是由 join_buffer_size 控制的。

实际环境中，肯定会有比较大的表，那么超过了可用内存时怎么办呢？

需要溢出到磁盘了。

3. 溢出到磁盘

在构建阶段过程中，如果内存满了，会把表中剩余数据写到磁盘上。

不会只写入一个文件，会分成多个块文件。

MySQL 会保证每个块文件的大小都是适合可用内存的。

怎么决定某一行记录写入哪个块文件呢？也是通过hash计算join字段决定的：

hash_2(countries.country_id)

可以看到，对于大表，构建阶段分为了2步：

• 写入内存 hash table
• 写入块文件

然后是探测阶段，首先还是会走一遍和之前一样的流程，就是扫描一遍 persons 表的每一行，和内存中的 hash table 进行匹配。

但因为内存中的 hash table 不是全部数据，所以需要额外的处理：

把 persons 表的数据也写入多个块文件中。

怎么决定某一行记录写入哪个块文件呢？和构建阶段写入块文件的思路相同，这样，构建阶段的块文件和此处的块文件就是一一对应的关系了。

在正常的探测流程走完之后，开始处理这些块文件中的内容了。

逐一加载构建阶段的块文件到内存中，加载过程和正常的构建过程一致，对块文件中的每行数据进行 hash 计算，放入内存的 hash table 中。

构建好一个块文件之后，选择与其对应的探测块文件开始探测。

例如构建的是第0个构建块文件，那么就选择第0个探测块文件。

就这样一对一对的块文件进行处理，直到全部完成。

小结

hash join 算法先选一个小表，放入内存的 hash table，然后扫描另一个表，与 hash table 匹配出结果数据。

当表太大，无法一次放入内存时，就分而治之，写入块文件，再对每个块文件走一遍正常时的流程。

参考资料：

https://mysqlserverteam.com/hash-join-in-mysql-8/

来源：公众号 “性能与架构”