数据库哈希连贯详解（MySQL新特性）

概述

很长一段时间，MySQL 施行 连接 的独一算法是 嵌套轮回算法 ( nested loop algorithm) 的变体 ，但是 嵌套轮回算法 在某些场景下非常低效，也是 MySQL 不断被诟病的一个问题。

随着 MySQL 8.0.18 的公布，MySQL Server 可以使用哈希连接（hash join），这篇文章将会简便介绍下哈希连接怎样实现，看看在 MySQL 中它是怎样工作的，何时使用它，有什么限制。

引荐学习：MySQL教程

哈希连接简介

什么是哈希连接？

哈希连接是一种用于关系型数据库中的连接算法，只能用于有等连接前提的连接中（on a.b = c.b）。它平常比 嵌套轮回 算法 更高效（探测端非常非常小除外），特别是在没有命中索引的状况下。

简便来说，哈希连接算法就是先把一张小表加载到内存哈希表里，然后遍历大表的数据，逐行去哈希表中匹配相符前提的数据，返回到客户端。

（哈希表只是示例，方面懂得，实际 hash 的 key 是连接的值，value 是数据行链表）

平常将 哈希连接 分为两个阶段，构建阶段（build phase）和探测阶段（probe phase）。在构建阶段，先选中适宜的表作为「构建输入」，构建哈希表，然后再顺次遍历另一个「探测输入」表记载去探测哈希表查寻相符连接前提的记载。

以上图为例，查询城市对应的省份。我们假设 city 为 构建输入，在构建阶段，效劳器构建一个 city 哈希 表 ，遍历 city 表，将行顺次放进 哈希表，键为 hash(province_id)，值为对应的 城市行。`

在探测阶段，效劳器开端从 探测输入（province） 读取行。关于每一行都使用 hash(province.province_id) 值作为查寻键探测哈希表以匹配行。

也就是，构建输入能全部被加载到内存的状况下，仅扫每个探测行一次，使用常数时间查寻就可以查寻到两个输入之间匹配的行。

数据太多不克不及放入内存如何办？

将 构建输入 全部加载到内存中无疑是效力最高的，但在有些状况下，内存不足以将整张表加载到内存中，就需要分批来处置。

常见的做法有两种：

分批加载到内存处置

1.读取最大内存可以容纳的记载创立哈希表 构建输入 生成哈希表；

2.遍历 探测输入 对这部分哈希表停止一次全量探测；

3.清算掉哈希表从新停止这个流程，直至全部处置完成。

这种方式会致使探测输入全表被扫描屡次。

写到文件处置

1.当在构建哈希表阶段内存用完时，效劳器将会把剩余的构建输入写到磁盘上的很多小文件中，小文件块经过运算可以全部被读入内存并创立哈希表（幸免文件块太大后续没法加载到内存还需要再次分隔）；

2.在探测阶段，由于探测行大概与写入磁盘的构建输入的某行匹配，所以也需要将探测输入写入到磁盘中；

3.探测阶段完成后，从磁盘读取块文件并加载到内存散列表中，再从探测输入读取响应的块文件并探测匹配项；

4.处置完后，移动到下一对块文件，直至全部处置完成。

MySQL 中的哈希连接实现

MySQL 会选中两个输入中较小的一个作为构建输入(以字节运算)，在内存足够的状况下将构建输入加载到内存处置，不足的状况下使用写入文件的方式处置。

可以使用 join_buffer_size 系统变量操纵 哈希连接 的内存使用，哈希连接 使用的内存不克不及超越这个数目，当超越这个数目时，MySQL 将使用文件来处置。

假如内存超越 join_buffer_size，并且文件超越 open_files_limit ，施行大概失败。

可以使用如下两个解决方案：

● 增大 join_buffer_size 来幸免 哈希连接 溢出到磁盘

● 增大 open_files_limit

MySQL 什么状况下会使用哈希连接？

在 MySQL 8.0.18 版本中，假如使用一个或多个等连接前提将表连接在一起，并且没有可用于连接前提的索引，将使用哈希连接。假如索引可用，MySQL 倾向于使用索引查寻来支撑嵌套轮回。

默许状况下，MySQL 会尽大概使用哈希连接 ，可以通过以下两种方式启用或关闭：

● 设定全局或 session 变量 (hash_join = on or hash_join = off)；

SET optimizer_switch="hash_join=off";

● 使用 hints (HASH_JOIN or NO_HASH_JOIN)。

我们将使用以下查询作为示例：

EXPLAIN FORMAT = tree
SELECT
  city.name AS city_name,
  province.name AS province_name
FROM
  city
  JOIN province
    ON city.province_id = province.province_id;

输出为：

| -> Inner hash join (city.province_id = province.province_id)  (cost=1333.82 rows=1329)
    -> Table scan on city  (cost=0.14 rows=391)
    -> Hash
        -> Table scan on province  (cost=3.65 rows=34)

哈希连接 也可以用到多个 join 的查询中，只要存在等值连接，就可以使用哈希连接。

例如以下查询：

EXPLAIN FORMAT= TREE
SELECT
  city.name AS city_name,
  province.name AS province_name,
  country.name AS country_name
FROM
  city
  JOIN province
    ON city.province_id = province.province_id
    AND city.id < 50
  JOIN country
    ON province.province_id = country.id

输出为：

| -> Inner hash join (city.province_id = country.id)  (cost=23.27 rows=2)
    -> Filter: (city.id < 50)  (cost=5.32 rows=5)
        -> Index range scan on city using PRIMARY  (cost=5.32 rows=49)
    -> Hash
        -> Inner hash join (province.province_id = country.id)  (cost=4.00 rows=3)
            -> Table scan on province  (cost=0.59 rows=34)
            -> Hash
                -> Table scan on country  (cost=0.35 rows=1)

哈希连接也一样适用于 「笛卡尔积」，即没有指定查询前提，如下：

EXPLAIN FORMAT= TREE
SELECT
  *
FROM
  city
  JOIN province;

输出为：

| -> Inner hash join  (cost=1333.82 rows=13294)
    -> Table scan on city  (cost=1.17 rows=391)
    -> Hash
        -> Table scan on province  (cost=3.65 rows=34)

MySQL 什么状况下不会使用哈希连接？

1.当前 MySQL 哈希连接只支撑内连接，反连接、半连接和外连接依然使用块嵌套轮回施行。

2.假如索引可用，MySQL 会更倾向于使用索引查寻来支撑嵌套轮回；

3.当不存在等值查询时，会使用嵌套轮回。

如下：

EXPLAIN FORMAT=TREE
SELECT
  *
FROM
  city
  JOIN province
    ON city.province_id < province.province_id;

输出为：

| <not executable by iterator executor>

怎样查看语句施行可否使用哈希连接？

EXPLAIN FORMAT= TREE 在 MySQL 8.0.16 及之后的版本可以使用，TREE 供给了相似于树的输出，对查询处置的描写比传统格局愈加准确，它是独一显示 哈希连接 用途的格局。

除此之外，也可以使用 EXPLAIN ANALYZE 查看 哈希连接 信息。

<hr/> 以上基于 MySQL community Server 8.0.18。

以上就是数据库哈希连接详解（MySQL新特性）的具体内容，更多请关注百分百源码网其它相关文章！