引言

你是不是看到各种SQL的JOIN就会感慨，为什么JOIN就能匹配到我们想要的数据，它是怎么做到的？我们能利用这个原理做什么样的优化操作？

接下来，我们详细捋一捋，在了解分布式JOIN的底层原理之前，我们先了解一下单机版JOIN的底层原理，以mysql为例：

MYSQL的JOIN的底层原理

在研究JOIN之前，我们先了解什么是驱动表与被驱动表。

简单理解：筛选出来的数据量小就是驱动表，筛选出来数据量多的就是被驱动表

当我们执行一条最简单的表关联SQL时，就存在了驱动表与被驱动表：

select A.*, B.*
  from A
  join B on A.id = B.id
 where A.type = 1 and B.gen = 2
# 这里的区分逻辑在于：
# 例如A表为10W条，B表为1000条，如果没有任何过滤条件，则B作为驱动表，A作为被驱动表。
# A.type=1过滤后A只剩下100条，B.gen=2过滤后B还有500条，则驱动表变为A，被驱动表变为B。

MYSQL的JOIN模式

Simple Nested-Loop Join（简单的嵌套循环连接）

一个简单的嵌套循环联接算法：一次从循环中的第一个表读取一行，将每一行传递到一个嵌套循环，该循环处理联接中的下一个表。只要还有需要连接的表，这个过程就会重复多次。

# 伪代码如下
for A_row in A matching range: 
  for B_row in B matching reference key: 
    if row satisfies join conditions:
      send to client

开销统计：

外表扫描	内标扫描	读取记录数	JOIN比较次数	回表	得出的算法复杂度
1次	A次	A+A*B	A*B	0	A*B

Index Nested-Loop Join

一次循环中的第一个表读取一行，将每一行传递到一个嵌套循环中，该循环处理连接中的下一个表，这个表的关联字段存在索引，可极大减少内表的循环次数，如果索引是主键的话，不需要回表；如果是非主键，则需要回表。

# 伪代码如下
for A_row in A matching range:
  if A_row matching index:
    send to client

开销统计：

外表扫描	内表扫描	读取记录数	JOIN比较次数	回表	得出的算法复杂度
1次	A * TREE_B_HIGH()	A + A*TREE_B(find_A)	A*TREE_B_HIGH()	if(not primary) A*TREE_B(find_A)	A * TREE_B(find_A)

Block Nested-Loop Join

使用缓冲在外部循环中读取的行，以减少必须读取内部循环中的表的次数。

为了简单理解，也为写公式方便，我们把缓存大小理解成缓存行数(buf_size -> buf_rows) ，如果缓存10条数据至缓冲区并将该缓冲区传递给下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有10行进行比较。这可以将必须读取内部表的次数减少一个数量级。

#伪代码如下
for A_row in A matching range:
  store used columns from A in join buffer
  if buffer is full:
    for B_row in B:
      for each A combination in join buffer:
        if row satisfies join conditions:
          send to client
  
  empty join buffer

  if buffer is not empty:
    for B_row in B:
      for each A combination in join buffer:
        if row satisfies join conditions:
          send to client

开销统计：

外表扫描	内标扫描	读取记录数	JOIN比较次数	回表	得出的算法复杂度
1次	A/buf_rows + 1次	A+(A/buf_rows)*B + B	(A/buf_rows + 1)*B	0	A*B / buf_rows

Hash Join

看一下官方描述，大概意思就是说8.19版本以后，删除了对块嵌套循环的支持，服务器在以前使用块嵌套循环的地方使用Hash连接，：

    A hash join is usually faster than and is intended to be used in such cases instead of the block nested loop algorithm employed in previous versions of MySQL. Beginning with MySQL 8.0.20, support for block nested loop is removed, and the server employs a hash join wherever a block nested loop would have been used previously.
    8.0.20以后，block nested loop将被删除
    By default, MySQL 8.0.18 and later employs hash joins whenever possible. It is possible to control whether hash joins are employed using one of the BNL and NO_BNL optimizer hints.
    (MySQL 8.0.18 supported hash_join=on or hash_join=off as part of the setting for the optimizer_switch server system variable as well as the optimizer hints HASH_JOIN or NO_HASH_JOIN. In MySQL 8.0.19 and later, these no longer have any effect.)
    Memory usage by hash joins can be controlled using the join_buffer_size system variable; a hash join cannot use more memory than this amount. When the memory required for a hash join exceeds the amount available, MySQL handles this by using files on disk. If this happens, you should be aware that the join may not succeed if a hash join cannot fit into memory and it creates more files than set for open_files_limit. To avoid such problems, make either of the following changes:
    Increase join_buffer_size so that the hash join does not spill over to disk.
    Increase open_files_limit.
    hash join可以调整俩参数：join_buffer_size，增大该参数，数据不会溢出到磁盘中，open_files_limit：创建磁盘文件数量限制

    Beginning with MySQL 8.0.18, join buffers for hash joins are allocated incrementally; thus, you can set join_buffer_size higher without small queries allocating very large amounts of RAM, but outer joins allocate the entire buffer. 
    In MySQL 8.0.20 and later, hash joins are used for outer joins as well, so this is no longer an issue.
    hash join将被用于外连接

接下来我们重点梳理一下hash join的流程，基本思想是，将外表数据load到内存，并建立hash表，这样只需要遍历一遍内表，就可以完成join操作，输出匹配的记录。如果数据能全部load到内存当然好，逻辑也简单，一般称这种join为CHJ(Classic Hash Join)。如果数据不能全部load到内存，就需要分批load进内存，然后分批join。

In-Memory Join(CHJ)

# 伪代码
# 1. build phase
mem_hash_table = {}
  for A_row in A:
    build_mem_hash_table(A_row)

  # 2. probe phase
  for B_row in B {
    int hash_val = calulate_hash_val(B_row)
    if (find(mem_hash_table, hash_val)):
       send to client

开销统计：其中：build(A_row)和find(B_row)都是常数级复杂度

外表扫描	内标扫描	读取记录数	JOIN比较次数	回表	得出的算法复杂度
1次	1次	A*build(A_row) + B	B * find(B_row)	0	A + B

On-Disk Hash Join

CHJ的限制在于，需要内存能装下整个A表的数据，小表这样是没问题的；大表的话，就超出限制，所以需要把外表分成N份，对hash_val分片，按照分片顺序写入到磁盘文件中；在probe阶段，同样使用遍历内表记录，去匹配结果。

# 伪代码如下：
int val = Integer.MAX_VALUE / N

hash_table_file_A = hash_table_file_A[N]
for A_row in A:
  int hash_val = calulate_hash_val(A_row)
  hash_table = build_hash_table(A_row)
  write_hash_table_file(hash_table_file_A[hash_val / val], hash_table)

hash_table_file_B = hash_table_file_B[N]
for B_row in B:
  int hash_val = calulate_hash_val(B_row)
  hash_table = build_hash_table(B_row)
  write_hash_table_file(hash_table_file_B[hash_val / val], hash_table)

for i in 0 .. N:
  hash_table_A = read_mem_hash_table(hash_table_file_A[i])
  hash_table_B = read_mem_hash_table(hash_table_file_B[i])
  for hash_detail_A in hash_table_A:
    if find(hash_detail_A, hash_table_B):
      send to client

开销统计：其中：build(row), write(hash_table)和find(row)都是常数级复杂度

外表扫描	内标扫描	读取记录数	JOIN比较次数	回表	得出的算法复杂度
1次	1次	A * build(A_row) + B * build(B_row) + (A + B) * write(hash_table) + A + B	E(Hash_Table_A)* find(A_row)	0	A + B

总结

	SNLJ	INLJ	BNLJ	HJ-CHJ	HJ-ODHJ
外表扫描	1	1	1	1	1
内标扫描	A	A * TREE_B(find_A)	A/buf_rows + 1	1	1
读取记录数	A + A * B	A + A * TREE_B(find_A)	A + A * B / buf_rows + B	A * build(A_row) + B	A * build(A_row) + B * build(B_row)
写入记录数	0	0	0	0	(A + B) * write(hash_table)
JOIN比较次数	A * B	A*TREE_B_HIGH()	A * B / buf_rows + B	B * find_from_A(B_row)	E(Hash_Table_A)* find_from_B(A_row)
回表	0	0		A*TREE_B(find_A)	0	0
总开销	2A + 2A*B + 1	A* (2 * TREE_B(find_A) + TREE_B_HIGH()) + 1	A + 2AB/buf_rows + 2B + A/buf_rows + 1	2 + A * build(A_row) + B * find_from_A(B_row)	A * build(A_row) + B * build(B_row) + (A + B) * write(hash_table) + E(Hash_Table_A)* find_from_B(A_row) + 2
算法复杂度	A*B	A * TREE_B(find_A)	A * B / buf_rows	A + B	A + B

从上可知，在有索引的前提下，走INLJ的方式，复杂度最优。因为A表较小，再加之被驱动表的索引高度有限，搜索的代价不会很大；如果是查全量数据，则还有个问题，走索引虽然复杂度很低，但是因为需要反复驱动磁盘，所以全量查询时，依然是走hash-join方式最优。

在没有索引的前提下，HASH-JOIN的方式最优，只有线性的复杂度。

集群场景下的JOIN的底层原理

上面花了那么大的代价，研究和梳理了单机版数据库的JOIN的底层实现原理，相信都有了一定的掌握，接下来分析集群场景下的各种JOIN的实现原理。

集群场景下的JOIN策略模式

Shuffle Hash Join

它主要有以下两个步骤：

在mapper阶段，两张join的表按照关联字段重新分区，这里涉及到shuffle的过程，目的是为了将同样分区的记录发送到一台节点，方便分区内JOIN。
在reduce阶段，对于shuffle之后的分区数据，然后按照HJ-CHJ的方式进行数据匹配。这种操作，有什么缺点：
仅支持等值连接，不需要对字段进行排序；
除了full outer join，其他join均支持；
分区之后数据量依旧很大，易发生OOM；
当shuffle数据分布不均匀，产生数据倾斜，发生长尾效应；
网络资源消耗很大；

伪代码

# 以某个节点为例：
# mapper
for A_row in A:
  hash_val = calulate_hash_val(A_row)
  shuffle_rows_join_key(A_row, hash_val)
  if buffer is full:
    write(buffer)
    send(file)
    
for B_row in B:
  hash_val = calulate_hash_val(B_row)
  shuffle_rows_join_key(B_row, hash_val)
  if buffer is full:
    write(buffer)
    send(file)

# reducer
small_table = choose_small(A, B)
hash_table = build_hash_table(small_table)
for row in big_table:
  if row in hash_table:
     join(row, small_row)

开销统计：

MAPPER阶段	网络传输阶段	REDUCER阶段
文件扫描次数	读次数	写次数	传输大小	文件个数	文件扫描次数	读次数	JOIN次数
files+(A+B)/buf	A+B	A+B	A+B	(A+B)/buf * 分区个数	(A+B)/buf	A+B	A

Broadcast hash Join

该模式下，一般用于小表关联大表的场景，也是最常见的场景之一，可以分为两步：

broadcast阶段：将小表广播分发到大表所在的节点。广播算法有很多，最简单的是先发给driver，driver再统一分发给所有executor；后期有时间可以进一步梳理常见的广播算法文章。
hash join阶段：在每个executor上执行单机版hash join；

# mapper:
small_rdd = read_small_table(A)
broadcast(small_rdd)

hash_table = build_hash_table(small_rdd)
big_table.parallel(
  foreach big_row in big_table:
    if big_row exists hash_table:
       merge(big_row, small_row)
)

广播模式下，有什么缺点：

只适合大表+小表模式，小表得足够小，否则广播的网络开销会大于shuffle的开销；
主表不能被广播；优点：
几乎不会出现长尾效应，不会产生OOM，网络压力较低
非等值JOIN也支持
join操作提前发生在mapper阶段；

开销统计：

MAPPER阶段	网络传输阶段	REDUCER阶段
文件扫描次数	读次数	写次数	传输大小	文件个数	文件扫描次数	读次数	JOIN次数
big_files+N*small_files	A+N*B	0	N*B	N*small_files	N*small_files	A+B*N	A

Sort Merge Join

主要用于大表与大表关联时，用于代替Shuffle Hash Join，防止出现性能不稳定的情况；主要思想如下：

shuffle阶段：跟shuffle hash join一样，两张join的表按照关联字段重新分区，但是这里做了一步改进，增加了sort逻辑，即：buffer中的key是有序的，生成了多个文件后，继续整合成一个有序的大文件。
merge阶段：对排序的两张分区表数据执行join操作。分别遍历两个有序序列，碰到相同join key就merge输出，否则就丢弃，重新获取更小一边。

# shuffle
for row in A:
  buf = write_and_sort_buf(row)
  if buf is full:
    write_file(buf)

# 读取所有buf文件排序整合，最后输出成一个file
while files.count > 1 :
  for file1 ... fileN in buf_files:
     write_one_big_sort_file(file, file1, file2, ..., fileN)
remove(file1 ... fileN)
send(file)

# reducer:
while key_A, key_B:
  if key_A > key_B:
    key_B = key_B.next
  else if key_A = key_B:
    merge(row_A, row_B)
  else:
    key_A = key_A.next

开销统计：

MAPPER阶段	网络传输阶段	REDUCER阶段
文件扫描次数	读次数	写次数	传输大小	文件个数	文件扫描次数	读次数	JOIN次数
(A+B)/buf + files	A+B+(A+B)log(buf)+(A+B)log((A+B)/buf)	2*(A+B)	A+B	2*分区个数	2*分区个数	A+B	A+B

排序合并的模式下有什么优缺点呢？缺点：

计算复杂度相比之下，相比shuffle hash join会有一定幅度的增加；
仅支持等值操作；优点：
网络传输效率大大增加；
内存损耗低；

Colocate Hash Join

本地Join，主要目的是去除JOIN时的网络传输过程的消耗，主要有以下两点：

以一张主表为准，所有跟主表ID关联的表，都以主表分区和分片规则进行分区；
分区分片结束后，根据Hash Join的规则去进行hash关联；

如下图(来自于网络)：例如：在商家域，以商家信息表的商家ID为主表，所有跟商家相关的事实表，都以商家ID计算hashcode，而后根据商家信息表的Bucket规则进行分桶，那相同商家ID必然会落在同一个节点中，这样在做商家相关的JOIN时，就没有任何网络开销了。

但是也有一定的缺点：

数据分布方式的侵入性较强；
数据分布不均匀；

学习了JOIN的一些底层原理之后，接下来我们来根据JOIN的底层原理来尝试着优化多表JOIN的SQL语句

案例优化

在阿里云dataworks平台生产上已上线的ODPS-SQL为例。案例优化，仅从JOIN的底层技术角度考虑，不考虑业务场景的优化，也不考虑参数层面的优化，比如开启压缩，增加map内存，增加JOIN内存，增加并发数等操作；

由于阿里云本身也做了很多关于cost优化的工作，例如：谓词下推，字段下推，无效字段忽略，自动将小表设置为广播模式，自动将大表转成merge join模式等，这些自动优化的手段大大的简化了我们的工作量；

现在SQL优化的重点，可能就集中在SQL本身的写法和数据倾斜上。

案例1：小表LEFT JOIN N张大表的模式，且结果集以小表为主

-- 简化了部分查询字段，随机从表中各抽了2字段
SELECT  auc_df.activity_id
        ,auc_df.sku_id
        ,spu_df.spu_category_lv1_id
        ,spu_df.spu_category_lv1_name
        ,merc_df.merc_type_id
        ,merc_df.merc_type_name
        ,NVL(auction_buyer.up_bid_cnt, 0) AS up_bid_cnt
        ,NVL(auction_buyer.proxy_bid_cnt, 0) AS agent_bid_cnt
        ,sf.trade_buyer_deliver_datetime AS auc_trd_dlvry_datetime
FROM    acu_df auc_df
LEFT JOIN spu_df spu_df  ON auc_df.spu_id = spu_df.spu_id
                        AND spu_df.pt = '20221120'
LEFT JOIN merc_df merc_df ON auc_df.merchant_user_id = merc_df.merc_user_id
                         AND merc_df.merc_status_code = 1 
                         AND merc_df.pt = '20221120'
LEFT JOIN subord_flow_df sf ON auc_df.sub_order_no = sf.subord_no
                           AND sf.pt = '20221120'
LEFT JOIN 
(SELECT activity_no
        ,auction_type
        ,ROW_NUMBER() OVER (PARTITION BY activity_no ORDER BY create_time DESC ) AS rn
   FROM auction_buyer
  WHERE pt = '20221120') auction_buyer ON auc_df.activity_id = auction_buyer.activity_no AND auction_buyer.rn = 1
WHERE auc_df.pt = '20221120';

未优化前的SQL成本如下，3个JOIN操作，读写IO达到20G:

先分析一下各表数据量：acu_df

--acu_df: 1000
--spu_df: 3000000
--merc_df: 9000000
--subord_flow_df: 100000000
--auction_buyer: 9000

从上可以看出，主表为小表，而从表有2张表为百万级的数据量，一张表有9亿的数据量

leftjoin时，小表作为主表，是无法使用mapjoin的，所以优化的思路就是改造成小表的广播模式。

把小表left join 大表的模式拆分成两个部分：

Part 1：大表join小表；
Part 2：小表 left join (Part 1)

优化后SQL：

with tp_1 as (
SELECT auc_df.activity_id
       ,subord_flow_df.t_buyer_del_datetime
FROM   subord_flow_df
JOIN   auc_df ON auc_df.sub_order_no = subord_flow_df.sub_order_no
              AND subord_flow_df.pt = '20221120'
WHERE  auc_df.pt = '20221120'),
tp_2 as (
SELECT  auc_df.activity_id
        ,spu_df.spu_category_lv1_id
        ,spu_df.spu_category_lv1_name
FROM    spu_df
JOIN    auc_df ON auc_df.spu_id = spu_df.spu_id AND spu_df.pt = '20221120'
WHERE   auc_df.pt = '20221120'
),
tp_3 as (
SELECT  auc_df.activity_id
        ,merc_df.merc_type_id
        ,merc_df.merc_type_name
FROM    merc_df
JOIN    auc_df
ON      auc_df.user_id = merc_df.user_id
AND     merc_df.merc_status_code = 1
AND     merc_df.pt = '20221120'
WHERE   auc_df.pt = '20221120'
)
select auc_df.activity_id
        ,auc_df.sku_id
        ,tp_2.spu_category_lv1_id
        ,tp_2.spu_category_lv1_name
        ,tp_3.merc_type_id
        ,tp_3.merc_type_name
        ,NVL(auction_buyer.up_bid_cnt,0) AS up_bid_cnt
        ,NVL(auction_buyer.proxy_bid_cnt,0) AS agent_bid_cnt
        ,tp_1.trade_buyer_deliver_datetime
  from auc_df
left join tp_1 on auc_df.activity_id = tp_1.activity_id
left join tp_2 on auc_df.activity_id = tp_2.activity_id
left join tp_3 on auc_df.activity_id = tp_3.activity_id
LEFT JOIN 
(SELECT activity_no
        ,auction_type
        ,ROW_NUMBER() OVER (PARTITION BY activity_no ORDER BY create_time DESC ) AS rn
   FROM auction_buyer
  WHERE pt = '20221120') auction_buyer ON auc_df.activity_id = auction_buyer.activity_no
                                      AND acu_df.rn = 1
WHERE auc_df.pt = '20221120'
;

优化后的SQL成本如下，3个mapjoin，1个hashjoin，IO读写降至10G：

案例2：JOIN时的数据倾斜

本质上数据倾斜不是算法的问题，算法上可能已经最优了，是资源闲置浪费的问题。所以解决数据倾斜，是需要额外付出一些算法上的代价，来解决资源闲置的问题。

假设均不过滤数据，join时的数据倾斜有两种场景：

关联存在空值字段join
关联字段非空，但是分布不均匀，例如热门商品数据量超大，非热门商品数据量很小

例如A表为基表，优化手段如下：

关联存在空值的场景，相对容易解决一些，原理就是让空值行不参与join，直接在map阶段处理完成：

select A.*, B.*
from A
left join B on A.active_id = B.active_id
where ifnull(A.active_id, '') != ''
union all
select A.*, ..., ...
from A 
where ifnull(A.active_id, '') = '';

关联字段非空，但分布不均匀的场景，map端和reduce端均容易倾斜： map端倾斜，是因为在map端执行了预聚合，导致map端倾斜，可以不在map端聚合，打散放到reduce端聚合

select
s.s_id,s2.c_id,s2.s_score,s.s_sex
from (
select s_id,s_sex
from student distribute by rand()--选取数据时分区，避免mapjoin
)s 
left join score s2
on s.s_id=s2.s_id 
where s2.s_score>70

数据仓库大数据的JOIN原理解析

引言

MYSQL的JOIN的底层原理

MYSQL的JOIN模式

Simple Nested-Loop Join（简单的嵌套循环连接）

Index Nested-Loop Join

Block Nested-Loop Join

Hash Join

In-Memory Join(CHJ)

On-Disk Hash Join

总结

集群场景下的JOIN的底层原理

集群场景下的JOIN策略模式

Shuffle Hash Join

Broadcast hash Join

Sort Merge Join

Colocate Hash Join

案例优化

案例1：小表LEFT JOIN N张大表的模式，且结果集以小表为主

案例2：JOIN时的数据倾斜