看完这篇文章，放心在数据库使用Join查询吧

精选原创

我爱哇哈哈 2024-05-30 17:59:51 博主文章分类：MySQL实战 ©著作权

文章标签 SQL MySQL 数据 文章分类 MySQL 数据库 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者我爱哇哈哈的原创作品，请联系作者获取转载授权，否则将追究法律责任

前面我们了解了些单表SQL的索引设计及调优技巧。但在日常开发中，除了单表的SQL外，还有更为复杂的多表join查询和子查询语句。这就需要在多表中创建索引，难度也提升了不少。很多开发人员下意识地认为 JOIN 会降低 SQL 的性能效率，所以就将一条多表 SQL 拆成单表的一条条查询，但这样反而会影响 SQL 执行的效率。究其原因，在于开发人员不了解 JOIN 的实现过程。

下面，我们就来一起看一下join的工作原理以及实现算法和应用场景，从而可以开心的使用join语句来连接查询数据。

一、join连接算法

MySQL 8.0 版本支持两种 JOIN 算法用于表之间的关联：

Nested Loop Join；
Hash Join。

通常认为，在 OLTP 业务中，因为查询数据量较小、语句相对简单，大多使用索引连接表之间的数据。这种情况下，优化器大多会用 Nested Loop Join 算法；而 OLAP 业务中的查询数据量较大，关联表的数量非常多，所以用 Hash Join 算法，直接扫描全表效率会更高。

注意，这里仅讨论最新的 MySQL 8.0 版本中 JOIN 连接的算法，同时也推荐在生产环境时优先用 MySQL 8.0。

接下来我们来看一下这两个算法：

1、Nested Loop Join

Nested Loop Join 之间的表关联是使用索引进行匹配的，假设表 R 和 S 进行连接，其算法伪代码大致如下：

for each row r in R with matching condition:

    lookup index idx_s on S where index_key = r

    if (found)

      send to client

在上述算法中，表 R 被称为驱动表，表 R 中通过 WHERE 条件过滤出的数据会在表 S 对应的索引上进行一一查询。如果驱动表 R 的数据量不大，上述算法非常高效。

接着，我们看一下，以下三种 JOIN 类型，驱动表各是哪张表：

SELECT ... FROM R LEFT JOIN S ON R.x = S.x WEHRE ...

SELECT ... FROM R RIGHT JOIN S ON R.x = S.x WEHRE ...

SELECT ... FROM R INNER JOIN S ON R.x = S.x WEHRE ...

对于上述 Left Join 来说，驱动表就是左表 R；Right Join中，驱动表就是右表 S。这是 JOIN 类型决定左表或右表的数据一定要进行查询。但对于 INNER JOIN，驱动表可能是表 R，也可能是表 S。

在这种场景下，谁需要查询的数据量越少，谁就是驱动表。比如如下SQL：

SELECT ... FROM R INNER JOIN S 

ON R.x = S.x 

WHERE R.y = ? AND S.z = ?

上面这条 SQL 语句是对表 R 和表 S 进行 INNER JOIN，其中关联的列是 x，WHERE 过滤条件分别过滤表 R 中的列 y 和表 S 中的列 z。优化器一般认为，通过索引进行查询的效率都一样，所以 Nested Loop Join 算法主要要求驱动表的数量要尽可能少。

所以，如果 WHERE R.y = ?过滤出的数据少，那么这条 SQL 语句会先使用表 R 上列 y 上的索引，筛选出数据，然后再使用表 S 上列 x 的索引进行关联，最后再通过 WHERE S.z = ?过滤出最后数据。

2、Hash Join

MySQL 中的第二种 JOIN 算法是 Hash Join，用于两张表之间连接条件没有索引的情况。没有连接，那创建索引不就可以了吗？可能是可以的，但是：

如果有些列是低选择度的索引，那么创建索引在导入数据时要对数据排序，影响导入性能；
二级索引会有回表问题，若筛选的数据量比较大，则直接全表扫描会更快。

对于 OLAP 业务查询来说，Hash Join 是必不可少的功能，MySQL 8.0 版本开始支持 Hash Join 算法，加强了对于 OLAP 业务的支持。

所以，如果查询数据量不是特别大，对于查询的响应时间要求为分钟级别，完全可以使用单个实例 MySQL 8.0 来完成大数据的查询工作。

Hash Join算法的伪代码如下：

foreach row r in R with matching condition:

    create hash table ht on r

foreach row s in S with matching condition:

    search s in hash table ht:

    if (found)

        send to client

Hash Join会扫描关联的两张表：

首先会在扫描驱动表的过程中创建一张哈希表；
接着扫描第二张表时，会在哈希表中搜索每条关联的记录，如果找到就返回记录。

Hash Join 选择驱动表和 Nested Loop Join 算法大致一样，都是较小的表作为驱动表。如果驱动表比较大，创建的哈希表超过了内存的大小，MySQL 会自动把结果转储到磁盘。

2、OLTP业务能使用join吗？

OLTP 业务是海量并发，要求响应非常及时，在毫秒级别返回结果，如淘宝的电商业务、支付宝的支付业务、美团的外卖业务等。

如果 OLTP 业务的 JOIN 带有 WHERE 过滤条件，并且是根据主键、索引进行过滤，那么驱动表只有一条或少量记录，这时进行 JOIN 的开销是非常小的。

比如在淘宝的电商业务中，用户要查看自己的订单情况，其本质是在数据库中执行类似如下的 SQL 语句：

SELECT o_custkey, o_orderdate, o_totalprice, p_name FROM orders,lineitem, part

WHERE o_orderkey = l_orderkey

  AND l_partkey = p_partkey

  AND o_custkey = ?

ORDER BY o_orderdate DESC

LIMIT 30;

很多开发同学会以为上述 SQL 语句的 JOIN 开销非常大，因此认为拆成 3 条简单 SQL 会好一些，比如：

SELECT * FROM orders 

WHERE o_custkey = ? 

ORDER BY o_orderdate DESC;



SELECT * FROM lineitem

WHERE l_orderkey = ?;



SELECT * FROM part

WHERE p_part = ?

其实完全不用人工拆分语句，因为你拆分的过程就是优化器的执行结果，而且优化器更可靠，速度更快，而拆成三条 SQL 的方式，本身网络交互的时间开销就大了 3 倍。

由于驱动表的数据是固定 30 条，因此不论表 orders、lineitem、part 的数据量有多大，哪怕是百亿条记录，由于都是通过主键进行关联，上述 SQL 的执行速度几乎不变。

所以，OLTP 业务完全可以大胆放心地写 JOIN，但是要确保 JOIN 的索引都已添加， Teamlader 们在业务上线之前一定要做 SQL Review，确保预期内的索引都已创建。