MySQL8.0 新特性学习之 Hash Join

小咪咪 2023-06-18 04:54 4阅读 0赞

### 概述&背景 ###

MySQL因为没有实现hashjoin而受到批评。最新的8.0.18版本带来了这一功能，令人欣慰。有时候我想知道为什么MySQL不支持hashjoin？我认为这可能是因为MySQL主要用于简单的OLTP场景，而且它广泛应用于Internet应用程序中，所以需求并不那么迫切。另一方面，这可能是因为以前完全依赖社区。毕竟MySQL的进化速度是有限的。甲骨文收购mysql后，mysql发布的演进速度明显加快。

*  源点：提供流的节点（入度为0），类比成为一个无限放水的水厂
 *  汇点：接受流的节点（出度为0），类比成为一个无限收水的小区
 *  弧：类比为水管
 *  弧的容量：类比为水管的容量；用函数c(x,y)c(x,y)表示弧(x,y)(x,y)的容量
 *  弧的流量：类比为当前在水管中水的量；用函数f(x,y)f(x,y)表示弧(x,y)(x,y)的流量
 *  弧的残量：即容量-流量
 *  容量网络：对于一个网络流模型，每一条弧都给出了容量，则构成一个容量网络。
 *  流量网络：对于一个网络流模型，每一条弧都给出了流量，则构成一个流量网络。
 *  残量网络：对于一个网络流模型，每一条弧都给出了残量，则构成一个残量网络。最初的残量网络就是容量网络。

hashjoin本身的算法实现并不复杂。要说它很复杂，可能是优化器选择执行计划时，是否选择hashjoin，选择外观，内部表可能更复杂。无论如何，现在使用hashjoin，优化器在选择join算法时还有另一个选择。MySQL基于实用主义。我相信这个增强也回答了一些问题。有些职能并非无能，而是有优先权。

在8.0.18之前，MySQL只支持nestlopjoin算法。最简单的是简单的nestloop连接。MySQL对该算法进行了一些优化，包括块嵌套循环连接、索引嵌套循环连接和批密钥访问。通过这些优化，可以在一定程度上缓解hashjoin的紧迫性。接下来，我们将用一个单独的章节来讨论MySQL的这些连接优化。接下来，我们将讨论hashjoin。

### Hash Join算法 ###

> Nestloopjoin algorithm is simply a double loop, which traverses the surface (drive table), for each row of records on the surface, then traverses the inner table, and then determines whether the join conditions are met, and then determines whether to spit out the records to the last execution node. In terms of algorithm, this is a complexity of M \* n. Hash join is an optimization for equal join scenarios. The basic idea is to load the external data into memory and establish a hash table. In this way, you can complete the join operation and output the matching records only by traversing the internal table once. If all the data can be loaded into memory, of course, the logic is simple. Generally speaking, this kind of join is called CHJ (classic hash join). MariaDB has implemented this kind of hash join algorithm before. If all the data cannot be loaded into memory, it needs to be loaded into memory in batches, and then joined in batches. The following describes the implementation of these join algorithms.

In-Memory Join(CHJ)

HashJoin一般包括两个过程，创建hash表的build过程和探测hash表的probe过程。

1).build phase

遍历外表，以join条件为key，查询需要的列作为value创建hash表。这里涉及到一个选择外表的依据，主要是评估参与join的两个表(结果集)的大小来判断，谁小就选择谁，这样有限的内存更容易放下hash表。

2).probe phase

hash表build完成后，然后逐行遍历内表，对于内表的每个记录，对join条件计算hash值，并在hash表中查找，如果匹配，则输出，否则跳过。所有内表记录遍历完，则整个过程就结束了。过程参照下图，来源于[MySQL官方博客][MySQL]

![format_png][]

左侧是build过程，右侧是probe过程，country\_id是equal\_join条件，countries表是外表，persons表是内表。

On-Disk Hash Join

CHJ的局限性在于需要内存来适应整个曲面。在mysql中，join可以使用的内存由join buffer size参数控制。如果一个连接所需的内存超过了连接缓冲区的大小，CHJ会忍不住将曲面分成几个段，逐个构建每个段，然后遍历内部表，再次探测每个段。假设表面被分成n块，然后扫描内表n次。当然，这种方式比较弱。在MySQL 8.0中，如果一个join所需的内存超过了join缓冲区的大小，那么构建阶段将首先使用哈希计算来划分外表面并生成一个临时的磁盘分区；然后在探测阶段，使用相同的哈希算法来划分内表。由于相同的哈希函数，相同的键（相同的连接条件）必须在相同的分区号中。接下来，对外部表和内部表中具有相同分区号的数据执行CHJ。在所有的CHJ片段完成之后，整个连接过程就完成了。该算法的代价是外部表读IO两次，内部表写IO一次。与以往的n扫描内表IO相比，目前的处理方法更好。

#include<cstdio>
    #include<algorithm>
    #include<queue>
    #include<cstring>
    using namespace std;
    struct data
    {
        int to,next,val;
    }e[2*100005];
    int cnt,head[10005],prep[10005],pree[10005],flow[10005],ans;
    queue<int> que;
    int n,m,s,t,u,v,w;
    void add(int u,int v,int w)
    {
        e[++cnt].to=v;
        e[cnt].next=head[u];
        head[u]=cnt;
        e[cnt].val=w;
    }
    int bfs(int s,int t)
    {
        while (!que.empty()) que.pop();
        flow[s]=0x3f3f3f3f;//flow记录的是在增广路上经过该点的流量
        que.push(s);
        for (int i=1;i<=n;i++) 
        {
            prep[i]=-1;//用于记录前驱节点
            pree[i]=0;//用于记录前驱边的编号
        }
        prep[s]=0;
        while (!que.empty())
        {
            int now=que.front();
            que.pop();
            if (now==t) break;
            for (int i=head[now];i;i=e[i].next)
            {
                if (e[i].val>0&&prep[e[i].to]==-1)
                {
                    que.push(e[i].to);
                    flow[e[i].to]=min(flow[now],e[i].val);
                    pree[e[i].to]=i;
                    prep[e[i].to]=now;
                }
            }
        }
        if (prep[t]!=-1) return flow[t];
        else return -1;
    }
    void EK(int s,int t)
    {
        int delta=bfs(s,t);//寻找最短增广路的最大流量
        while (delta!=-1)
        {
            ans+=delta;
            for (int j=t;j;j=prep[j])
            {
                e[pree[j]].val-=delta;
                e[pree[j]^1].val+=delta;
                //链式前向星存边从编号2开始存储可以通过异或1快速取得反向边的编号。
            }
            delta=bfs(s,t);
        }
    }
    int main()
    {
        scanf("%d%d%d%d",&n,&m,&s,&t);
        cnt=1;
        for (int i=1;i<=m;i++)
        {
            scanf("%d%d%d",&u,&v,&w);
            add(v,u,0);
            add(u,v,w);
            //加入正反边
        }
        EK(s,t);
        printf("%d",ans);
        return 0;

![format_png 1][]                     ![format_png 2][]

![format_png 3][]

左上侧图是外表的分片过程，右上侧图是内表的分片过程，最下面的图是对分片进行build+probe过程。

Grace Hash Join

与GraceHashJoin的区别在于，如果缓存能缓存足够多的分片数据，会尽量缓存，那么就不必像GraceHash那样，严格地将所有分片都先读进内存，然后写到外存，然后再读进内存去走build过程。这个是在内存相对于分片比较充裕的情况下的一种优化，目的是为了减少磁盘的读写IO。目前Oceanbase的HashJoin采用的是这种join方式。

GraceHash在遇到这种情况时，会继续分片进行二次Hash，直到内存足够放下一个hash表为止。但是，这里仍然有极端情况，如果输入join条件都相同，那么无论进行多少次Hash，都没法分开，那么这个时候GraceHashJoin也退化成和MySQL的处理方式一样。

主流数据库Oracle、SQL server和PostgreSQL长期以来都支持hashjoin。连接算法类似。这里我们介绍Oracle使用的grace散列连接算法。实际上，整个过程与MySQL的hashjoin类似，有一个主要区别。当连接缓冲区大小不足时，MySQL对外观进行分段并执行CHJ进程。但是，在极端情况下，如果数据分布不均匀，经过哈希处理后会有大量数据分布在一个bucket中，导致分段后连接缓冲区大小不足。MySQL的处理方法是一次读取多条记录，并读取它们建立一个哈希表，然后相应出现的探针就会被碎片化。处理批处理后，清除哈希表并重复上述过程，直到处理完此分区的所有数据。当连接缓冲区大小不足时，此过程与CHJ的处理逻辑相同。

hybrid hash join

### MySQL-Join算法优化 ###

BlockNestLoopJoin(BNLJ)

MySQL采用了批量技术，即一次利用join\_buffer\_size缓存足够多的记录，每次遍历内表时，每条内表记录与这一批数据进行条件判断，这样就减少了扫描内表的次数，如果内表比较大，间接就缓解了IO的读压力。

在MySQL 8.0.18之前，也就是说，MySQL数据库中很久没有hashjoin了。主要的连接算法是嵌套环连接。SimpleNetLoopJoin显然效率低下。它需要对内部表进行n次全表扫描。实际的复杂度是n\*m，n是外部记录的数目，m是记录的数目，它代表内部表的一次扫描的成本。为此，MySQL对simplenetloopjoin做了一些优化，下面的图片都是来自网络的。

IndexNestLoopJoin(INLJ)

如果我们能对内表的join条件建立索引，那么对于外表的每条记录，无需再进行全表扫描内表，只需要一次Btree-Lookup即可，整体时间复杂度降低为N\*O(logM)。对比HashJoin，对于外表每条记录，HashJoin是一次HashTable的search，当然HashTable也有build时间，还需要处理内存不足的情况，不一定比INLJ好。

Batched Key Access

IndexNestLoopJoin利用join条件的索引，通过Btree-Lookup去匹配减少了遍历内表的代价。如果join条件是非主键列，那么意味着大量的回表和随机IO。BKA优化的做法是，将满足条件的一批数据按主键排序，这样回表时，从主键的角度来说就相对有序，缓解随机IO的代价。BKA实际上是利用了MRR特性(MultiRangeRead)，访问数据之前，先将主键排序，然后再访问。主键排序的缓存大小通过参数read\_rnd\_buffer\_size控制。

![format_png 4][]

### 总结 ###

在MySQL 8.0之后，对服务器层代码进行了大量重构。尽管优化器仍然远远落后于Oracle，但它一直在改进。在hashjoin的支持下，MySQL优化器有了更多的选择，SQL的执行路径也会更好，特别是在等价连接的情况下。尽管MySQL以前对join做过一些优化，如nblj、inlj、BKA等，但是它们不能替代hashjoin的角色。一个好的数据库应该具有丰富的基本功能。使用优化器分析适当的场景，然后拿出相应的基本功能以最有效的方式响应请求。

### 参考： ###

1.  [https://en.wikipedia.org/wiki/Hash\_join][https_en.wikipedia.org_wiki_Hash_join]
2.  [https://mysqlserverteam.com/hash-join-in-mysql-8/][MySQL]
3.  [https://dev.mysql.com/worklog/task/?id=2241][https_dev.mysql.com_worklog_task_id_2241]
4.  [https://www.cnblogs.com/qixinbo/p/10524142.html][https_www.cnblogs.com_qixinbo_p_10524142.html]
5.  [https://zhuanlan.zhihu.com/p/35040231][https_zhuanlan.zhihu.com_p_35040231]

[MySQL]: https://mysqlserverteam.com/hash-join-in-mysql-8/
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2ktYmV0YS8xMzMyOTA3LzIwMTkxMS8xMzMyOTA3LTIwMTkxMTMwMTA0MzM1NjkzLTEwMTkxMTI2NjAucG5n?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTc2NTM5LzIwMTkxMS8xNzY1MzktMjAxOTExMzAxMTI2NTQ4ODUtODQ4NDYzMzg3LnBuZw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTc2NTM5LzIwMTkxMS8xNzY1MzktMjAxOTExMzAxMTI3MjkwMjgtMjg4MDUwMzAxLnBuZw?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTc2NTM5LzIwMTkxMS8xNzY1MzktMjAxOTExMzAxMTI4NDA4NjAtMzQwMTEzMDI0LnBuZw?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2ktYmV0YS8xMzMyOTA3LzIwMTkxMS8xMzMyOTA3LTIwMTkxMTMwMTA0NzMzMTUyLTIwODMzNDQzOTAucG5n?x-oss-process=image/format,png
[https_en.wikipedia.org_wiki_Hash_join]: https://en.wikipedia.org/wiki/Hash_join
[https_dev.mysql.com_worklog_task_id_2241]: https://dev.mysql.com/worklog/task/?id=2241
[https_www.cnblogs.com_qixinbo_p_10524142.html]: https://www.cnblogs.com/qixinbo/p/10524142.html
[https_zhuanlan.zhihu.com_p_35040231]: https://zhuanlan.zhihu.com/p/35040231