面试之前，MySQL表连接必须过关！——表连接的原理

以你之姓@ 2022-10-19 08:07 161阅读 0赞

### 文章目录 ###

*  一、表连接的简介
 *  二、表连接的过程
 *  三、内连接和外连接
 *   *  1.内连接
     *  2.外连接
 *  四、表连接的原理
 *   *  1.简单的嵌套循环连接（Simple Nested-Loop Join）
     *  2.基于索引的嵌套循环连接（Index Nested-Loop Join）
     *  3.基于块的嵌套循环连接（Block Nested-Loop Join）
     *  4.Nested-Loop Join和Block Nested-Loop Join对比说明

# 一、表连接的简介 #

create table t1(m1 int, n1 char(1));
    create table t2(m2 int, n2 char(1));
    
    insert into t1 values(1,'a'),(2,'b'),(3,'c');
    insert into t2 values(2,'b'),(3,'c'),(4,'d');

`t1`表数据如下

![20210710121748540.png][]

`t2`表数据如下

![20210710121944134.png][]

我们知道，所谓表连接就是把各个表中的记录都取出来进行依次匹配，最后把匹配组合的记录一起发送给客户端。比如下面把`t1`表和`t2`表连接起来的过程如下图

![20210710123205848.png][]

**什么是连接查询？**

比如上面`t1`和`t2`表的记录连接起来组成一个新的更大的记录，这个查询过程就称为连接查询。

**什么是笛卡尔积？**

如果连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配组合的记录，那么这样的结果集就可以称为笛卡尔积。

# 这三者效果一样，只要不写条件，就产生笛卡尔积，结果集的数量一样。
    select * from t1, t2;
    # 内连接
    select * from t1 inner join t2;
    # 全连接
    select * from t1 cross join t2;

![20210710124222831.png][]

表`t1`中有`3`条记录，表`t2`中也有`3`条记录，两个表连接后的笛卡尔积就有`3 x 3 = 9`条记录，只要把两个表的记录数相乘，就能得到笛卡尔积的数量。

--------------------

# 二、表连接的过程 #

笛卡尔积也是一个很大的问题，不加限制条件，结果集的数量就会很大。比如你在开发过程中需要`2`个表的连接，表`1`有`20000`条记录，表`2`有`10000`条记录，表`3`有`100`条记录，那么`3`张表连接后产生的笛卡尔积就有`20000 x 10000 x 100 = 20000000000`条记录（两百亿条记录）。

所以在连接时过滤掉特定的记录组合是很有必要的，为了避免笛卡尔积，一定要在表连接的时候加上条件！

下面来看一下有过滤条件的表连接的执行过程。

# 下面两种写法都一样，执行效率没有区别，看看自己习惯于哪种写法
    select * from t1 join t2 on t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';
    
    select * from t1, t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';

注意：先说明条件的概念，要区分什么是**连接条件**和**过滤条件**！！

**连接条件**是针对两张表而言的，比如`t1.m1 = t2.m2`、`t1.n1 > t2.n2`，表达式两边是两个表的字段比较。

**过滤条件**是针对单表而言的，比如`t1.m1 > 1`是针对`t1`表的过滤条件，`t2.n2 < 'd'`是针对`t2`表的过滤条件。

1.首先确定第一个需要查询的表，这个表称之为**驱动表**。

在单表中选择代价最小的查询方式，简单理解就是走合适的索引即可。此处假设使用`t1`作为驱动表，那么就需要到`t1`表中找满足过滤条件`t1.m1 > 1`的记录，因为表中的数据太少，我们也没在表上建立索引，所以此处查询`t1`表的查询的方式就是`all`，也就是采用全表扫描的方式执行单表查询，筛选出符合条件的驱动表记录。

![20210710130655867.png][]

这里筛选出来的`t1`驱动表记录有`2`条。

2.从第`1`步中驱动表筛选出来的每一条记录，都要到`t2`表中查询匹配记录。

匹配记录就是找到满足连接条件和过滤条件的记录。因为是根据`t1`表中的记录去找`t2`表中的记录，所以`t2`表也可以称为**被驱动表**。上一步从驱动表筛选出了`2`条记录，意味着需要从头到尾将`t2`表查询`2`次，此时就得看两表之间的连接条件了，这里就是`t1.m1 = t2.m2`。

对于从`t1`表查询得到的第一条记录，而这条记录`t1.m1=2`，根据连接条件`t1.m1 = t2.m2`，就相当于在`t2`表加上过滤条件`t2.m2 = 2`，此时`t2`表相当于有了两个过滤条件`t2.m2 = 2 and t2.n2 < 'd'`，然后到`t2`表执行单表查询，每当匹配到满足条件的一条记录后立即返回给`MySQL`客户端，以此类推。

所以整个连接查询的执行过程如下：

![20210710133141511.png][]

最后连接查询的结果只有`2`条记录。  
![20210710133313307.png][]

如果把`t1.m1 > 1`这个过滤条件去掉了，那么从`t1`表查出的记录就有`3`条，就需要从头到尾扫`3`次`t2`表了。

**其实这个流程的套路就是用伪代码说明非常合适，你细品，看懂这个伪代码，你就理解了表连接的步骤。**

for  筛选 驱动表 满足条件的每条记录 {
    	for 筛选 被驱动表 满足条件的每条记录 {
    		发送到MySQL客户端;
    	}
    }

从这个伪代码可以看出，驱动表的每一条记录都会尝试遍历被驱动表的每条记录并匹配连接，每成功连接一条就返回给`MySQL`客户端。

总结：

1.在两表连接查询中，驱动表只需访问一次，而被驱动表可能需要访问多次。

2.并不是将所有满足过滤条件的驱动表记录先查询出来放到一个地方，然后再去被驱动表查询的（因为如果满足过滤条件的驱动表很大，需要的临时存储空间就会非常大）。而是每获得一条满足过滤条件的驱动表记录，就立即到被驱动表中查询匹配的记录。

--------------------

# 三、内连接和外连接 #

## 1.内连接 ##

上面第二节所讲的，都是内连接。

先建立`2`张表，后续根据这`2`张表来讲解。

CREATE TABLE student (
        stu_no INT NOT NULL AUTO_INCREMENT COMMENT '学号',
        name VARCHAR(5) COMMENT '姓名',
        major VARCHAR(30) COMMENT '专业',
        PRIMARY KEY (stu_no)
    ) Engine=InnoDB CHARSET=utf8mb4 COMMENT '学生信息表';
    
    CREATE TABLE score (
        stu_no INT COMMENT '学号',
        subject VARCHAR(30) COMMENT '科目',
        score TINYINT COMMENT '成绩',
        PRIMARY KEY (stu_no, subject)
    ) Engine=InnoDB CHARSET=utf8mb4 COMMENT '学生成绩表';

插入一些数据

insert into student values(20210901, '王大个', '软件工程');
    insert into student values(20210902, '刘帅哥', '物联网工程');
    insert into student values(20210903, '张小伟', '电子工程');
    
    insert into score values(20210901, '数据结构', 92);
    insert into score values(20210901, '计算机网络', 94);
    insert into score values(20210902, '计算机网络', 88);
    insert into score values(20210902, '数据结构', 80);

`student`表数据如下：

![20210710143226390.png][]

`score`表数据如下：

![20210710143252497.png][]

如果想要把学生的成绩都查出来，就需要表连接(`score`表中没有姓名，所以不能只查`score`表)，连接过程就是从`student`表取出记录，然后在`score`表中查找`number`相同的成绩记录，连接条件是`student.stu_no= score.stu_no;`

select * from student join score where student.stu_no = score.stu_no;

![20210710143455331.png][]

表连接的全部字段就在这里了，字段有点多，`stu_no`是重复的，我们修改一下

select s1.stu_no, s1.name, s2.subject, s2.score from student as s1 join score as s2 on s1.stu_no = s2.stu_no;

![20210710171327946.png][]

可以看到，学生的各科成绩都被查出来了。但是张小伟(学号为`20210903`的同学)因为缺考，在`score`表中没有记录。要是老师想查看所有学生的成绩(包括缺考的同学)该怎么办呢？也就是说，哪怕成绩为空，也要显示这位同学在这个表里面，咱们不能把他给踢了吧！

**  这个问题就化为这个模型：对于驱动表中的某条记录，哪怕根据连接条件或者过滤条件在被驱动表中没有找到对应的记录，也还是要把该驱动表的记录加到结果集。**

这就是内连接的局限性。

**其实我们想要看到的结果集是这样的**

![20210710182636308.png][]

为了解决这个问题，就有了**内连接**和**外连接**的区别。

对于内连接来说，若驱动表中的记录按照**连接条件或者过滤条件**在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集。

前面提到的都是内连接，比如前面例子中，当`t1.m1 = 2`时，根据连接条件`t1.m1 = t2.m2`，在被驱动表中如果没有记录满足过滤条件`t2.m2 = 2 and t2.n2 < 'd'`，驱动表的记录就不会加到最后的结果集。

注意：我们说过，内连接语法有很多种。对于内连接来说，连接条件选择`on`或者`where`都可以，凡是不符合`on`子句或者`where`子句条件的记录都会被过滤掉，不会被连接，更不会在最后的结果集。

# 以下三者效果一样，当用join进行内连接时，条件用on或者where连接都可以。
    select * from student join score on student.stu_no= score.stu_no;
    
    select * from student join score where student.stu_no= score.stu_no;
    
    select * from student, score where student.stu_no= score.stu_no;

## 2.外连接 ##

对于外连接来说，即使驱动表中的记录按照**连接条件和过滤条件**在被驱动表中找不到匹配的记录，该记录也仍然需要加入到结果集。

对于外连接来说，又有左(外)连接和右(外)连接的区别

左(外)连接：选取左侧的表为驱动表。

右(外)连接：选取右侧的表为驱动表。

> **重点强调：对于内连接来说，选取哪个表为驱动表都没关系。而外连接的驱动表是固定的，左(外)连接的驱动表就是左边那个表，右(外)连接的驱动表就是右边那个表。**

**左(外)连接的语法：**

比如要把`t1`表和`t2`表进行左连接查询。

select * from t1 
    left [outer] join t2
    on 条件
    [where 普通过滤条件]
    
    # 注意这个on条件包括连接条件和驱动表与被驱动表的单表过滤条件。
    # []括号代表可以省略

左表所有记录都会有，右表没有与之匹配的则用`NULL`填充。

对于外连接来说，`on`和`where`是有区别的。

即使被驱动表中的记录无法匹配`on`子句的条件，该驱动表的记录仍然是满足条件的一条记录，对应被驱动表的各个字段用`NULL`填充。

**简言之，对于外连接，驱动表的记录一定都有，被驱动表不匹配就用`NULL`填充。**

而`where`过滤条件是在记录连接过后的普通过滤条件，即连接的记录会再次判断是否符合条件，不符合就从结果集中剔除。

回到刚刚的问题，要把所有学生成绩显示出来(包括缺考的学生)

select s1.stu_no, s1.name, s2.subject, s2.score from student as s1 
    left join 
    score as s2 
    on s1.stu_no = s2.stu_no;

![20210710182636308.png][]

从上面结果集可以看出，虽然张小伟缺考，但是还是在结果集中，只不过对应的科目成绩用`NULL`填充。

**右(外)连接的语法**

select * from t1 
    right [outer] join t2
    on 条件
    [where 普通过滤条件]
    
    # 注意这个on条件包括连接条件和驱动表与被驱动表的单表过滤条件。
    # []括号代表可以省略

右连接中，驱动表是右边的表，被驱动表是左边的表，右表所有记录都会有，左表没有与之匹配的则用`NULL`填充。这里就不举例了。

--------------------

# 四、表连接的原理 #

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

我们前边说过，对于两表连接来说，驱动表只会访问一遍，但被驱动表要被访问到好多遍，具体访问几遍取决于驱动表执行单表查询后满足条件的记录条数。

假设`t1`表和`t2`表都没有索引，`t1`表和`t2`表内连接的大致过程如下：

步骤1：选取驱动表`t1`，使用与驱动表`t1`相关的过滤条件，选取成本最低的单表访问方法来执行对驱动表的单表查询。（根据你的索引和记录数量，查询优化器会选择成本最低的访问方法，这里没有索引则全表扫描）

步骤2：对上一步中查询驱动表得到的每一条满足条件的记录，都分别到被驱动表`t2`中查找匹配的记录。

具体细节在第二节说过，这里就不细致展开。

如果有第`3`个表`t3`进行连接的话，那么总体查询过程就是，查找`t1`表满足单表过滤条件的第一条记录，匹配连接`t2`表满足单表过滤条件的第一条记录(此时驱动表是`t1`，被驱动表是`t2`)，然后匹配连接`t3`表满足单表过滤条件的第`1`条记录(此时驱动表是`t2`，被驱动表是`t3`)，将这条满足所有条件的一条记录返回给`MySQL`客户端；前面条件不变，接着匹配连接`t3`表满足单表过滤条件的第`2`条记录…

这个过程最适合用伪代码来说明了

for  筛选t1表满足条件的每条记录 {
    	for 筛选t2表满足条件的每条记录 {
    		for 筛选t3表满足条件的每条记录 {
    			发送到MySQL客户端;
    		}
    	}
    }

这个过程就像是一个嵌套的循环，驱动表每一条记录，都要从头到尾扫描一遍被驱动表去尝试匹配。这种连接执行方式称之为简单的嵌套循环连接（`Simple Nested-Loop Join`），这是比较笨拙的一种连接查询算法。

注意：对于嵌套循环连接算法来说，每当从驱动表获得一条记录，就根据这条记录立即到被驱动表查一次，如果得到匹配连接记录，那就把这条连接的记录立即发送给`MySQL`客户端，而不是等查询完所有结果后才返回。然后再到被驱动表获取下一条符合条件的记录，直到被驱动表遍历完成，就切换到驱动表的下一条记录再次遍历被驱动表的每条记录，以此类推。

## 2.基于索引的嵌套循环连接（Index Nested-Loop Join） ##

在上一小节嵌套循环连接的步骤`2`中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描，扫描次数就非常多。

幸好`MySQL`优化器会找出所有可以用来执行该语句的方案，并会对比之后找出成本最低的方案，简单理解就是使用哪个索引最好。所以既然会多次访问被驱动表，索引好不好就是性能的瓶颈。

查询被驱动表其实就相当于一次单表扫描，那么我们可以利用索引来加快查询速度。

回到最开始介绍的`t1`表和`t2`表进行内连接的例子：

select * from t1 join t2 on t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';

这其实是嵌套循环连接算法执行的连接查询，再把上边那个查询执行过程拿下来给大家看一下：

![20210710133141511.png][]

查询驱动表`t1`后的结果集中有`2`条记录，嵌套循环连接算法需要查询被驱动表`2`次：

当`t1.m1 = 2`时，去查询一遍`t2`表，对`t2`表的查询语句相当于：

select * from t2 where t2.m2 = 2 and t2.n2 < 'd';

当`t1.m1 = 3`时，再去查询一遍`t2`表，此时对`t2`表的查询语句相当于：

select * from t2 where t2.m2 = 3 and t2.n2 < 'd';

可以看到，原来的`t1.m1 = t2.m2`这个涉及两个表的过滤条件在针对`t2`表进行查询时，选出`t1`表的一条记录之后，`t2`表的条件就已经确定了，即`t2.m2 = 常数值`，所以我们只需要优化对`t2`表的查询即可，上述两个对`t2`表的查询语句中利用到的列是`m2`和`n2`列，我们可以进行如下尝试：

1.  在`m2`列上建立索引，因为对`m2`列的条件是等值查找，比如`t2.m2 = 2`、`t2.m2 = 3`等，所以可能使用到`ref`的访问方法，假设使用`ref`的访问方法去执行对`t2`表的查询的话，需要回表之后再判断`t2.n2 < 'd'`这个条件是否成立。
2.  在`n2`列上建立索引，涉及到的条件是`t2.n2 < 'd'`，可能用到`range`的访问方法，假设使用`range`的访问方法对`t2`表进行查询，需要在回表之后再判断在`m2`列的条件是否成立。

假设`m2`和`n2`列上都存在索引，那么就需要从这两个里面挑一个代价更低的索引来查询`t2`表。也有可能不使用`m2`和`n2`列的索引，只有在非聚集索引 + 回表的代价比全表扫描的代价更低时才会使用索引。

`Index Nested-Loop Join`与`Simple Nested-Loop Join`的不同就是被驱动表加了索引，后面只说`Index Nested-Loop Join`。

> 扩展思考：假设驱动表全表扫描，行数是`N`，被驱动表走索引，行数是`M`。那么
> 
>  *  1.每次在被驱动表查一行数据，则要先搜索索引，再回表查找主键索引。
>  *  2.每次被驱动表查找次数是以`2`为底的`M`的对数，记为`log M`，所以在被驱动表上查一行的扫描次数是 `2*log M`（因为要回表查找利用到主键索引）。 驱动表执行过程就要扫描驱动表`N`行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，查找的总次数是 `N+N*2*log M`。
>  *  3.显然`N`对扫描行数的影响更大，因此应该让小表来做驱动表。`N`扩大`1000`倍的话，扫描行数就会扩大 `1000`倍；而`M`扩大`1000`倍，扫描行数扩大不到`10`倍。  
>       
>     **总结：如果被驱动表可以使用索引，那么驱动表一定要选择数据量小的小表。**

## 3.基于块的嵌套循环连接（Block Nested-Loop Join） ##

**扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。**

实际开发中的表可不像`t1`、`t2`这种只有`3`条记录，几千万甚至几亿条记录的表到处都是。现在假设我们不能使用索引加快被驱动表的查询过程，所以对于驱动表的每一条记录，都需要对被驱动表进行全表扫描。而对被驱动表全表扫描时，可能表前面的记录还在内存中，表后边的记录可能还在磁盘上。等扫描到后边记录的时候，可能由于内存不足，所以需要把表前面的记录从内存中释放掉给正在扫描的记录腾地方，这样就非常消耗性能。

采用嵌套循环连接算法的两表连接过程中，被驱动表是要被访问好多次的，所以我们得想办法，**尽量减少被驱动表的访问次数。**

**驱动表中满足筛选条件的有多少条记录，就得把被驱动表中的所有记录从磁盘上加载到内存中多少次。**

读磁盘代价太大，能不能在内存中操作呢？于是一个`Join Buffer`（连接缓冲区）的概念就出现了，`Join Buffer`就是执行连接查询前申请的一块固定大小的内存（默认`256K`），先把满足条件的若干条驱动表的记录装在这个`Join Buffer`中，然后开始扫描被驱动表，每一条**被驱动表**的记录一次性与`Join Buffer`中的所有记录进行匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的`I/O`代价。使用`Join Buffer`的过程如下图所示：

![20210715161914106.png][]

**为什么`Join Buffer`要装驱动表而不是被驱动表呢？上面说过，小表作为驱动表，`Join Buffer`装小表更容易装得下，下一节会讲这个原因。**

其实很好记忆，想想笛卡尔积顺序也很奇妙。笛卡尔积的顺序就是一条被驱动表记录匹配多条驱动表记录的顺序，而不是一条驱动表记录去匹配被驱动表的记录的顺序，你看看这个顺序是不是很神奇，可以自行键两张表连接看看笛卡尔积，观察一下。  
![2021071920013522.png][]

笛卡尔积顺序是
    1 a 2 b
    2 b 2 b
    3 c 2 b
    .....
    而不是
    1 a 2 b
    1 a 3 c
    1 a 4 d
    ...
    你发现了吗？

其实最好的情况是`Join Buffer`足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。这种加入了`Join Buffer`的嵌套循环连接算法称之为基于块的嵌套连接（`Block Nested-Loop Join`）算法。

这个`Join Buffer`的大小是可以通过启动参数或者系统变量`join_buffer_size`进行配置，默认大小为`262144`字节（也就是`256KB`），最小可以设置为`128`字节。对于被驱动表，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，可以尝试调大`join_buffer_size`的值来对连接查询进行优化。

另外需要注意的是，只有满足条件的`select`中的列才会被放到`Join Buffer`中，所以再次提醒我们，最好不要把`*`作为查询列表，这样还可以在`Join Buffer`中放置更多的记录。

## 4.Nested-Loop Join和Block Nested-Loop Join对比说明 ##

假设`t1`表的行数是`N`，`t2`表的行数是`M`，`t1`表是小表，即`N < M`

**`Simple Nested-Loop Join`算法：**

1.  驱动表的每一条记录都会去被驱动表逐一匹配，所以总的扫描行数是`N * M`（开头说了，扫描表就是把表从磁盘加载到内存中）；
2.  内存中的判断次数是`N * M`（扫描一次就会在内存中判断一次）。

别纠结了，这种方法太笨了，不管选择哪个表作为驱动表，最后扫描和内存中判断的成本都是一样的。

**`Index Nested-Loop Join`算法**

该算法被驱动表的查询条件字段加上了合适的索引。

1.  驱动表的每一条记录都会去被驱动表逐一匹配，所以总的扫描行数是`N * log M`（扫描行数不变，但是因为被驱动表有索引，扫描速度会大大增加）；
2.  内存中的判断次数是`M * N`（扫描一次就会在内存中判断一次）。

**`Block Nested-Loop Join`算法：**

该算法又得区分`Join Buffer`装得下和装不下的情况。

**  `Join Buffer`装得下的情况**

1.  `t1`表和`t2`表都做一次全表扫描，将`t1`表记录都装入`Join Buffer`，总的扫描行数是`M + N`（开头说了，扫描表就是把表从磁盘加载到内存中，驱动表扫描`M`行一次性装到`Join Buffer`，被驱动表扫描一行会在`Join Buffer`进行比较，最终扫描`N`行）；
2.  内存中的判断次数是`M * N`，由于`Join Buffer`是以**无序数组**的方式组织的，因此对`t2`表中的每一行数据，都要与`Join Buffer`中的记录相比较。

可以看到，调换这两个算式中的`M`和`N`没差别，因此这时候选择`t1`还是`t2`表做驱动表，成本都是一样的。

**  `Join Buffer`装不下的情况**

我们先用直观的数据说明过程，假如表`t1`是`100`行，而`Join Buffer`放不下，此时就分段放，执行过程就变成了：

1.  扫描表`t1`，顺序读取数据行放入`Join Buffer`中，放完第`80`行`Join Buffer`满了，继续第`2`步；
2.  扫描表`t2`，把`t2`中的每一行取出来，跟`Join Buffer`中的所有记录做对比，满足`join`条件的，返回该条记录给`MySQL`客户端；
3.  清空`Join Buffer`；
4.  继续扫描表`t1`，顺序读取最后的`20`行数据放入`Join Buffer`中，继续执行第`2`步。

这个流程体现出了这个算法名字中“`Block`”的由来，表示“分块的`join`”。

现在总结一下这个过程。驱动表`t1`的数据行数是`N`，假设需要分`K`次才能完成算法流程，被驱动表`t2`的数据行数是`M`。

注意，这里的`K`不是常数，`N`越大`K`就会越大。所以，在这个执行过程中：

1.  扫描行数是`N + K * M`，每次装完一次`Join Buffer`，被驱动表`t2`的`M`条记录就会从头到尾去`Join Buffer`匹配，`Join Buffer`需要装`K`次，则扫描`K`次`t2`表；
2.  内存判断`N * M`次，由于`Join Buffer`是以**无序数组**的方式组织的，因此对`t2`表中的每一行数据，都要与`Join Buffer`中的记录相比较。

显然，内存判断次数是不受选择哪个表作为驱动表影响的。而扫描行数考虑到`Join Buffer`的大小，在`M`和`N`大小确定的情况下，驱动表的数据行数`N`小一些，则分段`K`就少一些，那么整个表达式的结果会更小。

>   总结：如果`Join Buffer`能装任意一张表里的所有数据，那么不管选择哪个表作为驱动表，执行成本都一样。对于`Join Buffer`一次装不下驱动表的情况下，应该让小表当驱动表，因为小表记录总行数`N`越小，`Join Buffer`装完所需的次数`K`就越小，在`N + K * M`这个式子里，表达式的值越小。  
>

刚刚我们说了`N`越大，分段数`K`越大。那么`N`固定的时候，什么参数会影响`K`的大小呢？答案是`join_buffer_size`。`join_buffer_size`越大，`Join Buffer`中一次可以放入的行越多，分成的段数`K`也就越少，对被驱动表的全表扫描次数就越少。

`join_buffer_size`默认`256K`，我所在的公司配置的是`4M`。

> 1.不能使用被驱动表的索引，只能使用`Block Nested-Loop Join`算法，这样的语句就尽量不要使用  
> 2.`Explain`下，没用`Index Nested-Loop` 的全要优化

**综上：从上面1234小节来看，无论哪种情况，总是应该选择小表作为驱动表。并且两张表有个各自的索引，这样表连接才能达到更好的性能。在内连接中，你可以使用`STRAIGHT_JOIN`替换`JOIN`，这样在内连接中就是强制左表为驱动表。**

--------------------

欢迎一键三连~  
  
有问题请留言，大家一起探讨学习  
  
\----------------------Talk is cheap, show me the code-----------------------

[20210710121748540.png]: /images/20221005/9390ae3c08c14378a6eed35653c849e2.png
[20210710121944134.png]: /images/20221005/54b6763f7f5c45f8b9a2846875bf294f.png
[20210710123205848.png]: /images/20221005/3bd50aaca25f43c2996f28c136632beb.png
[20210710124222831.png]: /images/20221005/2c84be8264074ee7b477af34b56b460d.png
[20210710130655867.png]: /images/20221005/abb92045f2354704a12bc69b95cd0ebf.png
[20210710133141511.png]: /images/20221005/c654c72431c44b65b9d235a1af80aeb5.png
[20210710133313307.png]: /images/20221005/49d26d2dba984499a67b2bdb945d5220.png
[20210710143226390.png]: /images/20221005/f07bc46f54144696b4db690db8e48308.png
[20210710143252497.png]: /images/20221005/32866cae5734425e87de3cf90800c258.png
[20210710143455331.png]: /images/20221005/ef93f8a1daf646e7985da6f7783b7c95.png
[20210710171327946.png]: /images/20221005/25eda366dd43416f8ec9428873130766.png
[20210710182636308.png]: /images/20221005/70aef03664264260a60c626a2c52281c.png
[20210715161914106.png]: /images/20221005/f9315999be2a4cdb9de9671cff9d3e9c.png
[2021071920013522.png]: /images/20221005/6ad510e122834ac291ee59b6b32ce001.png