深入SQL Join

Join是SQL中两张或多张表建立连接的基本操作，阿里禁止超过三张表的Join，甚至有互联网企业禁止使用Join，本文将以MySQL5.7为例介绍Join的底层原理。

 

01 准备 

CREATE TABLE t (
　　id INT PRIMARY KEY AUTO_INCREMENT,    
　　a INT,    
　　b INT,    
　　KEY k1(a))ENGINE=INNODB;
INSERT INTO t VALUES(1,1,1),(2,2,2),...,(1000,1000,1000);
CREATE TABLE t1 LIKE t;
INSERT INTO t1 SELECT * FROM t WHERE id<=100;

02 嵌套循环连接

 

 SELECT * FROM t1 straight_join t ON t1.a = t.a;

straight_join告诉执行器必须按照t1 Join t的顺序执行，t1称为驱动表，t称为被驱动表，看一下执行计划（先执行的是驱动表，后执行的是被驱动表）

调换一下连接顺序

SELECT * FROM t JOIN t1 ON t1.a = t.a;

再看一下执行计划

 

 当采用非straight_join连接时，优化器将连接优化为小表驱动大表

 

嵌套循环连接执行流程

1. 从t1取出一行R1；

2. 在t的非聚簇索引匹配t.a=t1.a的列；

3. 如果匹配成功，回表在t的聚簇索引查找满足Join条件的记录S1（如果t的非聚簇索引包含需要的列，则不需要回表），S1可能包含多行匹配记录；

4. 将<R1，S1>组成一行或多行，加入结果集；

5. 重复执行1，2，3，直到t1全表扫描结束。

整个过程如下下图所示：

• 对于驱动表，无论如何都是全表扫描

• 对于被驱动表，是否全表扫描取决于连接列是否有索引

上面在t表（被驱动表）查询连接条件时使用了索引，因此称为Index Nested Loop Join。

时间复杂度

假设驱动表的行数为M，被驱动表行数为N，被驱动表连接列有非聚簇索引。根据执行计划，驱动表全表扫描复杂度为M；对于驱动表的每一行，被驱动表需要做2次索引树查找（一次非聚簇索引，一次回表），复杂度为2MLogN，所以整体时间复杂度是：

M + 2MLogN

可以看到驱动表影响更大，所以尽量选择小表进行作为驱动表。如果在没有索引的列上进行连接，那么被驱动表也是全表扫描，时间复杂度是：

M + MN

 

BNL

即Block Nested Loop Join，对于被驱动表无索引的情况，MySQL默认采用这种算法。其核心思想是在内存开辟一个Join Buffer，将部分数据缓存使得匹配在内存进行，从而提升效率。
BNL执行流程

1. 扫描表 t1，顺序读取数据行放入 Join Buffer 中。如果join_buffer不够容纳t1表的全部记录，则分段进行：依次扫描t1缓存N行记录，完成第2步匹配后再清空缓存，重新缓存t1的后续N行记录，直到t1表扫描完毕；

2. 扫描表 t，把 t2 中的每一行取出来，跟 Join Buffer 中的数据做对比，满足 join条件的，作为结果集的一部分返回。

在没有索引的b列上进行连接，查看执行计划，可以看到采用的是BNL，这种连接的效率较低，应当尽量在需要连接的被驱动表列上建立索引。

  

03 Hash连接

 

MySQL 8引入了Hash连接，对于a Join b，它会选择一个占用空间较小的表，在Join Buffer中建立一张Hash表（如果内存不够，会将部分数据写入磁盘），接着对大表进行全表扫描，用每一行数据跟Hash表中的数据进行匹配。理想情况下，Hash表中的匹配复杂度为常数级别，且又是内存操作，效率非常高。

• 在被驱动表相应的列存在索引的情况下，MySQL默认采用嵌套循环连接

• 没有索引可用时，采用Hash连接 

Hash连接开启状态查询

SHOW VARIABLES LIKE '%optimizer_switch%';

状态开启与关闭

SET optimizer_switch="hash_join=off/on";