【MYSQL】Join 连接 : Nested-Loop Join 和 Hash Join

作用

生成临时表，存放左右两表生成的详细数据。

比如A表是记录用户数据，B表是记录金钱数据，就可以使用JOIN让它们的数据一起展示。

ON

除了cross join，join必须搭配ON使用，否则内连接查询相当于cross join，左右连接会报错

---

1、CROSS JOIN : 交叉连接

SELECT * FROM A_table CROSS JOIN B_table

也叫笛卡尔连接，显示左右两表数据，产生记录数量为左右两表的笛卡儿积（乘积）。

疑问 ：不应该跟ON搭配使用，但实际又能和ON搭配使用，搞的CROSS JOIN和 INNER JOIN没啥区别，难道只是概念上的区别？求大佬解惑

2、INNER JOIN + ON：内连接

SELECT * FROM A_table INNER JOIN B_table ON A_table.id = B_table.id

获取左右两表符合条件的记录。

内连接可以省略INNER，只用JOIN。

3、LEFT JOIN + ON：左连接

SELECT * FROM A_table LEFT JOIN B_table ON A_table.id = B_table.id

获取左表所有记录，即使右表没有符合ON条件的记录。

4、RIGHT JOIN + ON：右连接

SELECT * FROM A_table RIGHT JOIN B_table ON A_table.id = B_table.id

获取右表所有记录，即使左表没有符合ON条件的记录。

---

Mysql执行顺序

from -> join,on -> where -> group by,having -> select,distinct -> order by,limit

---

Join原理

首先了解什么驱动表（外表）和匹配表（内表） 。

驱动表就是主表：

• 如果是left join ，左表就是驱动表
• 如果是right join，右表是驱动表。

---

Nested-Loop Join ( NLJ、嵌套循环连接 )

1、Simple Nested-Loop Join ( 简单嵌套循环连接 )

简单粗暴，就是外表和内表逐行匹配生成结果。

但是效率较低，所以Mysql衍生出Index Nested-LoopJoin、Block Nested-Loop Join两种NLJ算法。

• 有索引使用Index Nested-Loop Join*；

• 无索引使用Block Nested-Loop Join。

2、Index Nested-Loop Join ( INLJ，索引嵌套循环连接 )

外表根据索引字段匹配内表数据。

用索引去匹配内表数据，减少内层表的循环次数，已达到提高内表匹配效率。

与Simple Nested-Loop Join 的效率区别

• Simple Nested-Loop Join： a表行数 * b表行数
• Index Nested-Loop Join： a表行数 * b表索引树行数

3、Block Nested-Loop Join( BNL，阻塞嵌套循环连接 )

外表一次取出多条数据，存进join buffer，批量匹配内表数据。

一次取出外表多条数据，减少外表循环次数，提高匹配效率。

join_buffer：默认值大小是256K。
Explain Extra : 提示Using join buffer (Block Nested Loop)

相关：小表驱动大表

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

背景：

INLJ效率比较高，但是非主键索引匹配内表数据 发生回表，这会降低Join效率。
所以MySQL对此进行优化，在MySQL5.6开始Batched Key Access Join。

理论：

索引是有序，但回表访问索引树是无序的、是随机IO。
BKA将随机IO，变成顺序IO，从而提高效率。

实现（我看不太懂，先复制）

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

大致的过程如下:

1. BKA使用join buffer保存由join的第一个操作产生的符合条件的数据
2. 然后BKA算法构建key来访问被连接的表，并批量使用MRR接口提交keys到数据库存储引擎去查找查找。
3. 提交keys之后，MRR使用最佳的方式来获取行并反馈给BKA

Explain Extra : 提示 Using join buffer (Batched Key Access)

---

Hash Join

问题：

在使用Join 且是非索引的情况，执行计划Explain Extra 应该提示Using join buffer (Block Nested Loop) ，
但是它提示的是Using join buffer (hash join)。

这是因为MYSQL8开始支持hash join，比BNL更高效。

大致原理：

1、将内表所有参加JOIN ON运算的列 进行Hash运算 ，保存到join_buffer的Hash表；

2、若是join_buffer满了，就保存到磁盘的多个快文件里（ 上限128个块文件 ）；

3、外表根据ON条件去进行Hash匹配