面试官：你们的项目中竟然还在用多表关联！

在上一篇文章中《面试官：你们项目中用到多表关联了吗？》，我们着重介绍了MySQL 8.0 数据库中的四种Nested-Loops Join（嵌套循环连接）。

本文中我们继续介绍Hash Join（哈希连接），以及从技术原理的角度上给出最终结论 —— 我们还是应该在数据库中合理使用多表关联查询，而不是放到应用程序中。

我们在上一篇文章中说过，从MySQL 8.0.20开始，就不再使用缓存块嵌套循环连接了，将以前使用缓存块嵌套循环连接的场景全部改为哈希连接。

因为在绝大多数被驱动表没有创建索引的场景，哈希连接比缓存块嵌套循环连接性能更高。

MySQL表连接算法

5、哈希连接（Hash Join）

市面上所有支持哈希连接算法的数据库，无论是OLTP数据库中的Oracle，还是OLAP数据库中的ClickHouse、Doris，其实现方式都是将小表加载到内存形成哈希表，再通过遍历大表数据的方式与哈希表进行匹配，并返回匹配结果。

MySQL的实现方式也不例外，我们以下面的SQL语句举例：

SELECT * FROM product p INNER JOIN order o ON p.id = o.product_id WHERE 
p.id in(1,2);

其对应的详细执行步骤为：

（1）哈希表构建阶段，通过将product表在（小表）中的连接列（id）计算哈希值的方式，在内存中构建哈希表。

（2）循环遍历order表（大表）中的记录，以计算连接列（product_id）哈希值的方式，找到哈希表中所对应的桶，并进行匹配。

（3）将product表中的记录和order表中的匹配记录进行关联，放到结果集中。

如果查询优化器选择了批量键访问连接的实现方式，我们可以在执行计划中的Extra列中看到如下信息：

Using where; Using join buffer (hash join)

说到这里，聪明的同学肯定会想到一个问题，那就是“如果product表中的记录在内存中装不下该怎么办？”

没错儿，在MySQL中有个叫 join_buffer_size 的系统变量，如果哈希连接使用的内存超过它，那将会转为使用文件进行处理，对应的执行步骤也变了。

我们还是以下面的SQL语句举例：

SELECT * FROM product p INNER JOIN order o ON p.id = o.product_id WHERE 
p.id in(1,2);

其对应的详细执行步骤为：

（1）先将product表（小表）和order表（大表）的连接列进行Hash计算，把两表中的记录切分到不同的磁盘文件中，且保证切分后product表可以在内存中构建哈希表。

（2）将切分后的product文件中的连接列（id）计算哈希值的方式，在内存中构建哈希表。

（3）循环遍历切分后对应的order文件中的记录，以计算连接列（product_id）哈希值的方式，找到哈希表中所对应的桶，并进行匹配。

（4）将切分后的product文件中的记录和切分后的order文件中的匹配记录进行关联，放到结果集中。

（5）重复执行步骤（2）（3）（4），直到所有切分后的文件全部匹配连接。

此外，在MySQL 8.0.20及更高版本中，不再仅通过上文中equi-join的方式才能使用哈希连接了，下面的这些方式也同样可以。

Inner non-equi-join：

SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1

Semijoin：

SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c2 FROM t2)

Antijoin：

SELECT * FROM t2 WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1)

Left outer join：

SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1

Right outer join：

SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1

应用程序表连接算法

接下来开始聊重头戏了，我们看一下，如果真的禁止使用SQL语句进行多表关联，而是把各表的数据读到应用程序中来，再由程序进行数据merge操作，这该如何实现呢？

步骤其实很简单，从product表和order表中分别读出数据后，在应用程序代码中要么通过一个大嵌套循环来进行表数据的连接匹配，要么通过一个HashMap进行表的连接列匹配，跟MySQL中的实现基本上大同小异。

数据库 VS 应用程序

那么，到底是放在应用程序中实现多表关联查询比较好，还是合理地放到数据库中比较好呢？

赞成前者的同学会说，数据库是一个极为宝贵稀缺的资源，而应用服务器我们可以以集群部署的方式无限扩容。因此，将多表关联操作放到应用服务器中可以减轻数据库的压力。

对于这种说法，我只能说：“呵呵，数据库的主从复制了解一下？不仅可以搭从库，还可以搭二级从库呢。”

还有一种赞成前者说法是，单表查询在应用程序中进行merge，这样更有利于后续的维护，并且代码复用性高。

原因在于，SQL语句写得越简单，那就越容易被复用，而多表关联的复杂SQL只能有一种应用场景。

这种说法就相当于，把香河肉饼变成面粉、肉馅和食用油之后复用性高了，因为面粉可以蒸馒头烙饼、肉馅可以做包子和饺子，而食用油则可以炒任何菜。

最离谱的一种说法是，放到应用服务器中进行merge操作，性能会高一些。

对于这种说法，我只想说，到底是梁静茹给你的勇气，张信哲给你的信仰，还是五月天给你的倔强呢？

你能在表连接算法优化上比数据库研发者做得更好？我怎么就这么不信呢？

接下来，我说说赞成后者的原因吧，有如下三点：

（1）研发效率更高，毕竟一条SQL语句就可以搞定的事情，没必要写完SQL再写代码，两边儿一起忙活。

（2）代码性能更快，如果驱动表有1万条数据，而被驱动表也有1万条，两者关联到一起后返回10条数据。这种场景下，到底是哪种方式更快一些，应该显然易见了吧？

毕竟把两表中的各1万条数据通过网络返回给应用服务器，再加载到内存中进行merge，这些都是比较耗费性能的操作。

（3）可用性提升，如果驱动表有10万条数据，而被驱动表也有10万条，哪怕在QPS为个位数的情况下，都可能将数据库的网卡打满，从而出现系统不可用的情况。

这种故障场景，我还真的在实际工作中遇到过，当时整整挂了一个多小时呢。