mysql中的join算法

mysql中的join算法

 

本文转载自：https://blog.csdn.net/wudongxu/article/details/6683846，https://www.cnblogs.com/zuochanzi/p/10409752.html，https://blog.csdn.net/horses/article/details/102690076，感谢大神！

mysql的join涉及的主要函数包括sub_select、evaluate_join_record、do_select、optimize。

然后我们直接从mysql_select开始，它主要包括三个操作join:: prepare，join:: optimize，join::exec，其中prepare完成一些判断准备工作如对通配符的处理、having条件、order等的判断初始化等；optimize就是负责优化检索，简单的说就是为了确定一个mysql认为最优的执行计划；而exec则负责执行optimize确定的执行计划。下面我们主要介绍exec过程，它涉及的主要函数包括sub_select、evaluate_join_record、do_select。

join_tab->read_first_record：读取第一条记录使用的方法
info->read_record：读取非第一条记录使用的方法，该方法是根据optimize选择的join type来指定的。
join_tab->next_select：join_tab连接下一个table的方法，这里除了最后一个表使用end_select，其它的都使用sub_select。
下面通过两个简单的例子说明一下nest loop的过程：

例子1：select emp_no from salaries  where salary = 90930; //salary没有索引

这里因为只有一个表，所以join_tab只有一个，并且是全表扫描，所以join_tab->read_first_record、info->read_record都被指定为rr_sequential；join_tab->next_select就为end_send，又因为这是直接返回给客户端所以最终select_send::send_data再调用item->send(protocol, &buffer)通过网络传输给客户端。

例子2：select * from salaries s join  employees e on (s.emp_no=e.emp_no) where e.gender='F' and salary=90930;

注：e.gender，s.sqlary都不是索引
 

该语句的执行情况：
首先e【join->join_tab[0]->table->alias】表执行sub_select，它通过rr_sequential，获得它的每一条记录，然后通过evaluate_join_record判断这个记录是否满足e.gender=’F’条件【using where】，如果没有满足则接着取下一条，满足的话，则把它的e.emp_no传递给s表，即接下来s【join->join_tab[1]->table->alias】执行sub_select，它的join type是ref，即它是通过索引来获得记录而不是通过全表扫描的方式，即拿e.emp_no的值来检索s的PRIMARY KEY来获得记录【可能有多条，组合key】，最后再通过s的evaluate_join_record判断是否满足salary=90930这个条件，如果满足是直接发送给客户端，否则获得ref 的下一条记录【上面写的多条】，同样进行evaluate_join_record判断。

通过上面的例子我们可以看到Mysql Join的大概过程，该过程简单描述就是依次把每个join tab拿出来，根据索引或全表扫描获得记录，再判断是否满足该表的限制条件，如果满足的话则传递给下一个join tab，如此循环递归直到所有的join tab都join结束，如果该记录满足所有的条件则返回到客户端，否则读取下一条。同时对于const join tab Mysql会进行优化，直接在optimize阶段获得它的数据，以减少nest loop的次数。

Const：表最多有一个匹配行，一般表示直接通过Primarykey或UNIQUE获得数据

ref:它一般是通过联合索引来获得数据，即来自前一个表的join条件是该表的联合索引的一部分。

从MySQL 8.0.20开始，删除了对块嵌套循环的支持，并且服务器在以前曾使用过块嵌套循环的任何地方都采用了哈希联接。

但我们依然对MySQL以往的连接算法进行分析，以便更好的理解

一、Simple Nested-Loops Join算法

外表（驱动表）中的每一条记录与内表（被驱动表）中的记录进行比较判断。对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。

用伪代码表示一下这个过程就是这样：

1 2 3 4

For each row r in R do       -- 扫描R表（驱动表）     For each row s in S do    -- 扫描S表（被驱动表）         If r and s satisfy the join condition-- 如果r和s满足join条件               Then output the tuple <r, s>   -- 返回结果集

 

下图能更好地显示整个SNLJ的过程：

二、Index Nested-Loops Join（INLJ，基于索引的嵌套循环联接）

 

需要多次访问内表（每一次都是全表扫描）。因此，在Join的优化时候，通常都会建议在内表建立索引，以此降低Nested-Loop Join算法的开销，减少内表扫描次数，MySQL数据库中使用较多的就是这种算法，以下称为INLJ。来看这种算法的伪代码：

 

1 2 3 4

For each row r in R do – 扫描R表      lookup s in S index-- 查询S表的索引（固定3~4次IO，B+树高度）          If find s == r    -- 如果r匹配了索引s              Then output the tuple <r, s> -- 返回结果集

 

由于内表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。整个过程如下图所示：

{InnoDB中主键索引B+树是如何组织数据、查询数据的，我们总结一下：

1、InnoDB存储引擎的最小存储单元是页(16K)，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。

2、索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

通常一棵B+树可以存放多少行数据？

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。

单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。

那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。所以在InnoDB中B+树高度一般为3层左右，它就能满足千万级的数据存储。在查找数据时 一次页的查找代表一次IO， 所以通过主键索引查询通常 只需要3左右次IO操作 即可查找到数据。

}

可以看到外表中的每条记录通过内表的索引进行访问，就是读取外部表一行数据，然后去内部表索引进行二分查找匹配；而一般B+树的高度为3~4层，也就是说匹配一次的io消耗也就3~4次，因此索引查询的成本是比较固定的，故优化器都倾向于使用记录数少的表作为外表（这里是否又会存在潜在的问题呢？）。通过索引可以大幅降低内表的Join的比较次数，每次比较1条外表的记录，其实就是一次indexlookup（索引查找），而每次index lookup的成本就是树的高度，即IndexHeight。

INLJ的算法并不复杂，也算简单易懂。但是效率是否能达到用户的预期呢？其实如果是通过表的主键索引进行Join，即使是大数据量的情况下，INLJ的效率亦是相当不错的。因为索引查找的开销非常小，并且访问模式也是顺序的（假设大多数聚集索引的访问都是比较顺序的）。

大部分人诟病MySQL的INLJ慢，主要是因为在进行Join的时候可能用到的索引并不是主键的聚集索引，而是辅助索引，这时INLJ的过程又需要多一步Fetch的过程，而且这个过程开销会相当的大：

由于访问的是辅助索引，如果查询需要访问聚集索引上的列，那么必要需要进行回表取数据，看似每条记录只是多了一次回表操作，但这才是INLJ算法最大的弊端。首先，辅助索引的index lookup是比较随机I/O访问操作。其次，根据index lookup再进行回表又是一个随机的I/O操作。所以说，INLJ最大的弊端是其可能需要大量的离散操作，这在SSD出现之前是最大的瓶颈。而即使SSD的出现大幅提升了随机的访问性能，但是对比顺序I/O，其还是慢了很多，依然不在一个数量级上。

另外，在INNER JOIN中，两张联接表的顺序是可以变换的，即R INNER JOIN S ON Condition P等效于S INNER JOIN R ON Condition P。根据前面描述的Simple Nested-Loops Join算法，优化器在一般情况下总是选择将联接列含有索引的表作为内部表。如果两张表R和S在联接列上都有索引，并且索引的高度相同，那么优化器会选择记录数少的表作为外部表，这是因为内部表的扫描次数总是索引的高度，与记录的数量无关。所以，联接列只要有一个字段有索引即可，但最好是数据集多的表有索引；但是，但有WHERE条件的时候又另当别论了。

三、Block Nested-Loops Join（BNL，基于块的嵌套循环联接）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。但内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边又说过，采用Simple Nested-Loop Join算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。这也就是Block Nested-Loop Join算法的思想。

 

也就是说在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，因为那个算法太粗暴，不忍直视。数据量大些的复杂SQL估计几年都可能跑不出结果。而Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数，甚至可以和Hash Join算法一样，仅需扫描内表一次。其使用Join Buffer（联接缓冲）来减少内部循环读取表的次数。

 

 

1 2 3 4 5

For each tuple r in R do  -- 扫描外表R    store used columns as p from R in Join Buffer-- 将部分或者全部R的记录保存到Join Buffer中，记为p    For each tuple s in S do   -- 扫描内表S          If p and s satisfy the join condition  -- p与s满足join条件               Then output the tuple  -- 返回为结果集

可以看到相比Simple Nested-Loop Join算法，Block Nested-LoopJoin算法仅多了一个所谓的Join Buffer，为什么这样就能减少内表的扫描次数呢？下图相比更好地解释了Block Nested-Loop Join算法的运行过程：

可以看到Join Buffer用以缓存联接需要的列（所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢），然后以Join Buffer批量的形式和内表中的数据进行联接比较。就上图来看，记录r1，r2 … rT的联接仅需扫内表一次，如果join buffer可以缓存所有的外表列，那么联接仅需扫描内外表各一次，从而大幅提升Join的性能。

MySQL数据库使用Join Buffer的原则如下：

* 系统变量Join_buffer_size决定了Join Buffer的大小。

* Join Buffer可被用于联接是ALL、index、和range的类型。

* 每次联接使用一个Join Buffer，因此多表的联接可以使用多个Join Buffer。

* Join Buffer在联接发生之前进行分配，在SQL语句执行完后进行释放。

* Join Buffer只存储要进行查询操作的相关列数据，而不是整行的记录。

Join_buffer_size变量

所以，Join Buffer并不是那么好用的。首先变量join_buffer_size用来控制Join Buffer的大小，调大后可以避免多次的内表扫描，从而提高性能。也就是说，当MySQL的Join有使用到Block Nested-Loop Join，那么调大变量join_buffer_size才是有意义的。而前面的Index Nested-Loop Join如果仅使用索引进行Join，那么调大这个变量则毫无意义。

变量join_buffer_size的默认值是256K，显然对于稍复杂的SQL是不够用的。好在这个是会话级别的变量，可以在执行前进行扩展。建议在会话级别进行设置，而不是全局设置，因为很难给一个通用值去衡量。另外，这个内存是会话级别分配的，如果设置不好容易导致因无法分配内存而导致的宕机问题。

Join Buffer缓存对象

另外，Join Buffer缓存的对象是什么，这个问题相当关键和重要。然在MySQL的官方手册中是这样记录的：Only columns of interest to the join are stored in the join buffer, not whole rows.

可以发现Join Buffer不是缓存外表的整行记录，而是缓存“columns of interest”，具体指所有参与查询的列都会保存到Join Buffer，而不是只有Join的列。比如下面的SQL语句，假设没有索引，需要使用到Join Buffer进行链接： 

 

1 2 3 4 5 6

SELECT a.col3 FROM a,   b WHERE a.col1 = b.col2 AND a.col2 > …. AND b.col2 = …

 

假设上述SQL语句的外表是a，内表是b，存放在Join Buffer中的列是所有参与查询的列，在这里就是（a.col1，a.col2，a.col3）。Scaninner_table = (RN * used_column_size) / join_buffer_size + 1

join Buffer的分配

需要牢记的是，Join Buffer是在Join之前就进行分配，并且每次Join就需要分配一次Join Buffer，所以假设有N张表参与Join，每张表之间通过Block Nested-Loop Join，那么总共需要分配N-1个Join Buffer。

MySQL 5.6将Join Buffer分为Regular join buffer和Incremental join buffer。假设B1是表t1和t2联接使用的Join Buffer，B2是t1和t2联接产生的结果和表t3进行联接使用的join buffer，那么：

* 如果B2是Regular join buffer，那么B2就会包含B1的Join Buffer中r1相关的列，以及表t2中相关的列。

* 如果B2是Incremental join buffer，那么B2包含表t2中的数据及一个指针，该指针指向B1中r1相对应的数据。

因此，对于第一次联接的表，使用的都是Regular join buffer，之后再联接，则使用Incremental join buffer。又因为Incremental join buffer只包含指向之前Join Buffer中数据的指针，所以Join Buffer的内存使用效率得到了大幅的提高。

SELECT * FROM t1 INNER JOIN t2 on t1.m1 = t2.m2 WHERE t2.n2>'c';

n2有索引：这条 SQL 先根据索引进行了条件过滤，然后拿过滤后的结果集作为驱动表，也是为了减少被驱动表扫描次数。如果 t2.n2 没有索引呢？使用 BNL 算法来 join 的话，这个语句的执行流程是这样的，假设表 t1 是驱动表，表 t2 是被驱动表：

1. 把表 t1 的所有字段取出来，存入 join_buffer 中。

2. 扫描表 t2，取出每一行数据跟 join_buffer 中的数据进行对比；如果不满足 t1.m1=t2.m2，则跳过； 如果满足 t1.m1=t2.m2，再判断其他条件，也就是是否满足 t2.n2>’c’ 的条件，如果是，就作为结果集的一部分返回，否则跳过。

对于表 t2 的每一行，判断 join 是否满足的时候，都需要遍历 join_buffer 中的所有行。因此判断等值条件的次数是 t1表行数*t2表行数，数据量稍微大点时，这个判断的次数都是上亿次。如果不想在表 t2 的字段 n2 上创建索引，又想减少比较次数。那么，有没有两全其美的办法呢？这时候，我们可以考虑使用临时表。使用临时表的大致思路是：

1. 把表 t2 中满足条件的数据放在临时表 tmp_t 中；

2. 为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 n2 加上索引；

3. 让表 t1 和 tmp_t 做 join 操作。

Block Nested-Loop Join影响

BNL 算法对系统的影响主要包括三个方面： 可能会多次扫描被驱动表，占用磁盘 IO 资源； 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源； 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

四、Batched Key Access Join（BKA，批量键访问联接）

Index Nested-Loop Join虽好，但是通过辅助索引进行联接后需要回表，这里需要大量的随机I/O操作。若能优化随机I/O，那么就能极大的提升Join的性能。为此，MySQL 5.6（MariaDB 5.3）开始支持Batched Key Access Join算法（简称BKA），该算法通过常见的空间换时间，随机I/O转顺序I/O，以此来极大的提升Join的性能。

在说明Batched Key Access Join前，首先介绍下MySQL 5.6的新特性mrr——multi range read。因为这个特性也是BKA的重要支柱。MRR优化的目的就是为了减少磁盘的随机访问，InnoDB由于索引组织表的特性，如果你的查询是使用辅助索引，并且有用到表中非索引列（投影非索引字段，及条件有非索引字段），因此需要回表读取数据做后续处理，过于随机的回表会伴随着大量的随机I/O。这个过程如下图所示：

而mrr的优化在于，并不是每次通过辅助索引读取到数据就回表去取记录，范围扫描（range access）中MySQL将扫描到的数据存入由read_rnd_buffer_size 变量定义的内存大小中，默认256K。然后对其按照Primary Key（RowID）排序，然后使用排序好的数据进行顺序回表，因为我们知道InnoDB中叶子节点数据是按照PRIMARY KEY（ROWID）进行顺序排列的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

【MRR的使用过程：先通过二级索引取出满足条件的二级索引和主键放到缓冲区（大小由参数read_rnd_buffer_size控制）中，当该缓冲区满了之后，再通过主键进行排序，最后按照排序后的结果集取表中的数据。（由于此时表是顺序的，就将随机IO变成了顺序IO，多页数据记录可一次性读入或根据此次的主键范围分次读入，以减少IO操作，提高查询效率）此外，MRR还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据。】

MRR 能够提升性能的核心在于，这条查询语句在索引上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。所以MRR优化可用于range，ref，eq_ref类型的查询，工作方式如下图：

要开启mrr还有一个比较重的参数是在变量optimizer_switch中的mrr和mrr_cost_based选项。mrr选项默认为on，mrr_cost_based选项默认为off。mrr_cost_based选项表示通过基于成本的算法来确定是否需要开启mrr特性。

我们知道 join_buffer 在 BNL 算法里的作用，是暂存驱动表的数据。但是在 NLJ 算法里并没有用。那么，我们刚好就可以复用 join_buffer 到 BKA 算法中。NLJ 算法优化后的 BKA 算法的流程，整个过程如下所示：

对于多表join语句，当MySQL使用索引访问第二个join表的时候，使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后，批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的，这样，MRR使得查询更有效率。

如果外部表扫描的是主键，那么表中的记录访问都是比较有序的，但是如果联接的列是非主键索引，那么对于表中记录的访问可能就是非常离散的。因此对于非主键索引的联接，Batched Key Access Join算法将能极大提高SQL的执行效率。BKA算法支持内连接，外连接和半连接操作，包括嵌套外连接。

Batched Key Access Join算法的工作步骤如下：

1. 将外部表中相关的列放入Join Buffer中。

2. 批量的将Key（索引键值）发送到Multi-Range Read（MRR）接口。

3. Multi-Range Read（MRR）通过收到的Key，根据其对应的ROWID进行排序，然后再进行数据的读取操作。

4. 返回结果集给客户端。

Batched Key Access Join算法的本质上来说还是Simple Nested-Loops Join算法，其发生的条件为内部表上有索引，并且该索引为非主键，并且联接需要访问内部表主键上的索引。这时Batched Key Access Join算法会调用Multi-Range Read（MRR）接口，批量的进行索引键的匹配和主键索引上获取数据的操作，以此来提高联接的执行效率，因为读取数据是以顺序磁盘IO而不是随机磁盘IO进行的。

因为BKA算法的本质是通过MRR接口将非主键索引对于记录的访问，转化为根据ROWID排序的较为有序的记录获取，所以要想通过BKA算法来提高性能，不但需要确保联接的列参与match的操作（联接的列可以是唯一索引或者普通索引，但不能是主键），还要有对非主键列的search操作。

select a.gender, b.dept_no from employees a, dept_emp b where a.birth_date=b.from_date;

列a.gender是表employees的数据，但不是通过搜索idx_birth_date索引就能得到数据，还需要回表访问主键来获取数据。因此这时可以使用BKA算法。但是如果联接不涉及针对主键进一步获取数据，内部表只参与联接判断，那么就不会启用BKA算法，因为没有必要去调用MRR接口。比如search的主键（a.emp_no），那么肯定就不需要BKA算法了，直接覆盖索引就可以返回数据了（二级索引有主键值）。

select a.emp_no, b.dept_no from employees a, dept_emp b where a.birth_date=b.from_date;

五、Hash Join算法

在8.0.18之前，MySQL只支持NestLoopJoin算法，最简单的就是Simple NestLoop Join，MySQL针对这个算法做了若干优化，实现了Block NestLoop Join，Index NestLoop Join和Batched Key Access等，有了这些优化，在一定程度上能缓解对HashJoin的迫切程度。下面讲HashJoin。

NestLoopJoin算法简单来说，就是双重循环，遍历外表(驱动表)，对于外表的每一行记录，然后遍历内表，然后判断join条件是否符合，进而确定是否将记录吐出给上一个执行节点。从算法角度来说，这是一个M*N的复杂度。HashJoin是针对equal-join场景的优化，基本思想是，将外表数据load到内存，并建立hash表，这样只需要遍历一遍内表，就可以完成join操作，输出匹配的记录。如果数据能全部load到内存当然好，逻辑也简单，一般称这种join为CHJ(Classic Hash Join)，之前MariaDB就已经实现了这种HashJoin算法。如果数据不能全部load到内存，就需要分批load进内存，然后分批join，下面具体介绍这几种join算法的实现。

（1）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官方博客

左侧是build过程，右侧是probe过程，country_id是equal_join条件，countries表是外表，persons表是内表。

（2）On-Disk Hash Join

CHJ的限制条件在于，要求内存能装下整个外表。在MySQL中，Join可以使用的内存通过参数join_buffer_size控制。如果join需要的内存超出了join_buffer_size，那么CHJ将无能为力，只能对外表分成若干段，每个分段逐一进行build过程，然后遍历内表对每个分段再进行一次probe过程。假设外表分成了N片，那么将扫描内表N次。这种方式当然是比较弱的。在MySQL8.0中，如果join需要内存超过了join_buffer_size，build阶段会首先利用hash算将外表进行分区，并产生临时分片写到磁盘上；然后在probe阶段，对于内表使用同样的hash算法进行分区。由于使用分片hash函数相同，那么key相同(join条件相同)必然在同一个分片编号中。接下来，再对外表和内表中相同分片编号的数据进行CHJ的过程，所有分片的CHJ做完，整个join过程就结束了。这种算法的代价是，对外表和内表分别进行了两次读IO，一次写IO。相对于之之前需要N次扫描内表IO，现在的处理方式更好。

左侧图是外表的分片过程，中间图是内表的分片过程，右侧图是对分片进行build+probe过程。

（3）Grace Hash Join

主流的数据库Oracle，SQLServer，PostgreSQL早就支持了HashJoin。Join算法都类似，这里介绍下Oracle使用的Grace Hash Join算法。其实整个过程与MySQL的HashJoin类似，主要有一点区别。当出现join_buffer_size不足时，MySQL会对外表进行分片，然后再进行CHJ过程。但是，极端情况下，如果数据分布不均匀，导致大量的数据hash后都分布在一个分桶中，导致分片后，join_buffer_size仍然不够，MySQL的处理方式是一次读分片读若干记录构建hash表，然后probe对应的外表分片。处理完一批后，清理hash表，重复上述过程，直到这个分片的所有数据处理完为止。这个过程与CHJ在join_buffer_size不足时，处理逻辑相同。

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

（4）hybrid hash join

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

（5）MySQL-Join算法优化（https://blog.csdn.net/horses/article/details/102690076）

MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本，带来了一些新特性和增强功能，多表连接查询支持 hash join 方式了。

默认配置时，MySQL 所有可能的情况下都会使用 hash join。同时提供了两种控制是否使用 hash join 的方法：

1. 在全局或者会话级别设置服务器系统变量 optimizer_switch 中的 hash_join=on 或者 hash_join=off 选项。默认为 hash_join=on。
2. 在语句级别为特定的连接指定优化器提示 HASH_JOIN 或者 NO_HASH_JOIN。

可以通过系统变量 join_buffer_size 控制 hash join 允许使用的内存数量；hash join 不会使用超过该变量设置的内存数量。如果 hash join 所需的内存超过该阈值，MySQL 将会在磁盘中执行操作。需要注意的是，如果 hash join 无法在内存中完成，并且打开的文件数量超过系统变量 open_files_limit 的值，连接操作可能会失败。为了解决这个问题，可以使用以下方法之一：

1. 增加 join_buffer_size 的值，确保 hash join 可以在内存中完成。
2. 增加 open_files_limit 的值。

从 MySQL 8.0.18 开始以下查询可以使用 hash join 进行连接查询：

必须使用 EXPLAIN 命令的 FORMAT=TREE 选项才能看到节点中的 hash join。另外，EXPLAIN ANALYZE命令也可以显示 hash join 的使用信息。这也是该版本新增的一个功能。

多个表之间使用等值连接的的查询也会进行这种优化。任何其他非等值连接的条件将会在连接操作之后作为过滤器使用。例如以下查询：

但是，如果任何连接语句（ON）中没有使用等值连接条件，将不会采用 hash join 连接方式，此时，将会采用性能更慢的 block nested loop 连接算法。这与 MySQL 8.0.18 之前版本中没有索引时的情况一样：

Hash join 连接同样适用于不指定查询条件时的笛卡尔积（Cartesian product），例如：

六、总结：

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

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控制。

MySQL8.0以后，Server层代码做了大量的重构，虽然优化器相对于Oracle还有很大差距，但一直在进步。HashJoin的支持使得MySQL优化器有更多选择，SQL的执行路径也能做到更优，尤其是对于等值join的场景。虽然MySQL之前对于Join做过若干优化，比如NBLJ，INLJ以及BKA等，但这些代替不了HashJoin的作用。一个好用的数据库就应该具备丰富的基础能力，利用优化器分析出合适场景，然后拿出对应的基础能力以最高效的方式响应请求。