你点菜我配菜！MySQL 的 Hash Join 就是这么高效！

后端出路在何方
664 阅读8分钟

Hash Join 是什么？

Hash Join 是一种数据库用来高效关联两张表（也叫做“连接”，比如 tableA 和 tableB）的技术，尤其是在处理 等值连接（例如 tableA.id = tableB.id）时非常高效。它的核心思想是：用“哈希表”（Hash Table）来快速找到匹配的数据。

基本思想就是将驱动表数据加载到内存，并建立hash表，这样只要遍历一遍非驱动表，在通过hash查找hash表中匹配行，就可完成join操作。

以下是它的运行过程，用一两个简单的比喻来说明：

假设场景一理解：Hash Join 就像配对钥匙和锁

假设你有两堆物品：

  1. 一堆是“钥匙”（表A的id）。
  2. 另一堆是“锁”（表B的id）。

你的任务是找到哪些钥匙可以打开哪些锁，也就是将两堆数据“匹配”起来。

正常匹配方式：

假如你不知道任何规律，你只能从第一个钥匙开始，尝试去配第一个锁、第二个锁、第三个锁……直到找到匹配的锁。这种方法就像传统的 Nested Loop Join（嵌套循环连接），需要一个一个比对，非常慢。

Hash Join 的方式：

Hash Join 更聪明：

  1. 先建个“钥匙索引” ：把每把钥匙放进一个“字典”（哈希表），并记住它的特征，比如“钥匙 101 对应 Alice”。

    • 这样，当你拿到一把锁时，可以直接查字典，秒知道这把锁能不能打开某个钥匙。

  2. 然后逐个查找“锁” ：拿到每把锁时，直接去刚才的“字典”里查匹配，而不用一个一个去试。

用 Hash Join 的方法，就能非常快地完成匹配任务！

假设场景二理解：Hash Join 就像词典索引跟词典页

我们有两个装着数据的箱子：

  • 箱子A：装着一堆员工的 "员工编号" 和 "员工姓名"。
  • 箱子B：装着 "员工编号" 和 "部门信息"。

现在我们要通过 "员工编号" 把这两个箱子里的信息对上，得出每位员工的 "姓名" 和 "部门信息"。

Hash Join 的工作流程

  1. 构建哈希表：

    • 先从箱子A开始，把它的 "员工编号" 制作成一个哈希表（像一本“超级快速查找的字典”）。
    • 在哈希表中，"员工编号" 就是"钥匙"，用来快速定位对应的 "员工姓名"。

  2. 查找匹配：

    • 再看箱子B，逐个取出它的 "员工编号"，然后用这个编号去刚刚制作的哈希表里找匹配。
    • 如果找到匹配，就把 "员工编号" 对应的 "员工姓名" 和箱子B里的 "部门信息" 合并起来。

这种方法通过哈希表的特性，使得匹配的速度非常快。

Step 1：Hash 表构建（Build Phase）

  1. 选择较小表作为哈希表的构建源：

    • 通常选择较小的表（如 countries 表）作为哈希表，将其加载到内存中以节省内存消耗。
    • 选择较小表的优势是减少缓存压力，并提高查找效率。

  2. 确定哈希键（Hash Key）：

    • 哈希键是连接条件中涉及的列（如 country_id），用于在哈希表中组织和查找数据。
    • 比如在连接条件 persons.country_id = countries.country_id 中，countries.country_id 是哈希键。

  3. 构建哈希表：

    • 将较小表的每一条数据按照哈希键（country_id）的值加载到内存中的哈希表结构中。
    • 哈希表的键（Key）为 country_id，值（Value）为表中的相关数据行。

示例：构建哈希表

以 countries 表为例，countries.country_id 是哈希键，构建的哈希表可能如下：

Hash Table:
Key: 1 -> Value: {country_id: 1, country_name: "USA"}
Key: 2 -> Value: {country_id: 2, country_name: "China"}
Key: 3 -> Value: {country_id: 3, country_name: "Germany"}

构建完成标志：

当把较小表中所有符合条件的数据行都加载到哈希表中后，构建阶段完成。

Step 2：Hash 表探测（Probe Phase）

原理：

  1. 选择需要探测的数据表：

    • 通常选择较大的表（如 persons 表）进行探测。
    • 在探测阶段，逐行扫描探测表的数据。

  2. 逐行匹配：

    • 对于探测表中每一行，根据其连接条件（如 persons.country_id）的值，计算哈希键，然后在内存中的哈希表中查找匹配的记录。
    • 匹配成功时，输出连接结果；匹配失败时，可能舍弃该数据（或根据连接类型处理）。

示例：探测哈希表

以 persons 表为例，假设 persons.country_id 是 2，则通过哈希键查找：

  • Hash Table 中查找键为 2 的记录。
  • 找到匹配的值 {country_id: 2, country_name: "China"}。

优点：

探测阶段中，每一行只需要 O(1) 的时间在哈希表中查找匹配记录，因此整体效率较高。

Step 3：数据表拆分

为什么需要拆分？

当较小表的数据量过大，无法完全加载到内存中构建哈希表时（超过 join_buffer_size 设置），需要将数据进行拆分。

拆分的步骤：

  1. 按哈希函数分块：

    • 使用哈希函数将数据表按照键值分组为多个块（hash partitions），并写入多个磁盘文件。
    • 每块数据的大小由 join_buffer_size 限制，以确保后续能一次性加载到内存中。

  2. 分块存储：

    • 将较小表（如 countries）的数据写入磁盘文件。例如：
      • 数据 {country_id: 1, country_name: "USA"} 写入文件块 H1。
      • 数据 {country_id: 2, country_name: "China"} 写入文件块 H2。
    • 同时，对探测表（如 persons）也按照相同的哈希函数将数据分块存储。

新哈希函数的使用原因：

为了避免哈希冲突导致大量数据集中在某个块，新哈希函数会重新分配数据，从而减少冲突和提高探测效率。

Step 4：拆分后的探测阶段

内存中的数据匹配：

  1. 按哈希值加载某一块数据到内存中，构建哈希表（如 H1 块）。
  2. 探测表中，同样加载相应哈希块的数据（如 H1），然后进行匹配。

磁盘中的数据匹配：

当内存中数据匹配完成后，读取磁盘中对应的哈希块数据，再重复进行构建和探测。

  • 例如：
    • countries 表的 H1 块与 persons 表的 H1 块匹配。
    • countries 表的 H2 块与 persons 表的 H2 块匹配。

示例（分块探测匹配）

假设：

  • countries 表分为两个块：H1（country_id: 1）、H2（country_id: 2）。
  • 探测时：
    • 加载 H1 块数据到内存中，匹配 persons 表的 H1 块。
    • 加载 H2 块数据到内存中，匹配 persons 表的 H2 块。

结果：

通过分块探测，避免一次性加载过多数据到内存，并且确保每块数据都能匹配到对应的哈希块。

举个实际例子

假设有两张表：

  • 表A（较小的表，记为“钥匙”）： id name 1 Alice 2 Bob 3 Charlie

  • 表B（较大的表，记为“锁”）： id department 1 HR 2 IT 4 Marketing

我们要连接这两张表，查询结果是 ON A.id = B.id。

正常匹配（慢）：

逐行比对：

  • 表A第一行（id=1）和表B所有行比对。
  • 表A第二行（id=2）和表B所有行比对。
  • 如此类推……

这种方法需要多次循环，效率低。

Hash Join（快）：

  1. 构建哈希表：
    • 把表A的 id 列放进哈希表： 哈希表： Key: 1 -> Value: Alice Key: 2 -> Value: Bob Key: 3 -> Value: Charlie
  2. 遍历表B，探测哈希表：
    • 表B第一行（id=1）：在哈希表中找到，返回结果。
    • 表B第二行（id=2）：在哈希表中找到，返回结果。
    • 表B第三行（id=4）：在哈希表中没找到，跳过。

最终结果：

id   name      department
1    Alice     HR
2    Bob       IT

未命名绘图.drawio.png

Hash Join 的步骤：

  1. 构建哈希表（Build Phase） ：选择较小的表，根据哈希键构建哈希表。
  2. 探测哈希表（Probe Phase） ：扫描探测表数据，根据哈希键查找匹配记录。
  3. 数据拆分：当内存不足时，将数据分块存储到磁盘，并逐块加载到内存中处理。
  4. 拆分后的探测：分块匹配探测表和哈希表中的数据，逐步完成所有连接。

Hash Join 的优势和适用场景

优势：

  • 速度快： 构建哈希表和查找的效率很高，尤其在大数据量时，远快于传统嵌套循环方法。
  • 适合等值连接： Hash Join 对 ON A.col = B.col 的情况特别高效。

Hash Join 适用于如下场景：

  • 连接条件明确：比如基于两个表的某些列（如 ON 条件）进行等值连接。连接条件是等值连接（如 =,==）。
  • 数据量较大：比起嵌套循环连接，Hash Join 更适合处理大数据量的表。数据量较大，且一张表明显比另一张表小。
  • 内存足够的情况下：可以将较小表完全加载到内存中构建哈希表。有足够的内存用于构建哈希表（Hash Join 通常需要内存支持）。
  • 内存不足时：通过数据拆分（分块）技术将数据分组到磁盘文件中，逐步加载到内存处理。

MySQL 中的情况

在 MySQL 中，Hash Join 并不是默认启用的连接方法。MySQL 在很多情况下默认使用的是 Nested Loop Join（嵌套循环连接）。不过在某些版本（如 MySQL 8.0）及场景下，可以通过优化器选择或引导来使用 Hash Join。它更适用于中大型数据集、等值连接条件（如 ON a.id = b.id）的情况。

文字先行，图示后补上

avatar
文章
阅读
粉丝