你晓得的越多，不晓得的就越多，业余的像一棵小草！
你来，咱们一块精进！你不来，我和你的竞争对手一块精进！
编辑：业余草
liuchenyang0515.blog.csdn.net
举荐：https://www.xttblog.com/?p=5317
1、表连接的简介create table t1(m1 int, n1char(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表数据如下 
t2表数据如下 
咱们晓得，所说表连接便是把各个表中的记录都取出来进行依次匹配，最后把匹配组合的记录一块发送给客户端。例如下面把t1表和t2表连接起来的过程如下图 
「什么是连接查找？」
例如上面t1和t2表的记录连接起来构成一个新的更大的记录，这个查找过程就叫作为连接查找。
「什么是笛卡尔积？」
倘若连接查找的结果集中包括一个表中的每一条记录与另一个表中的每一条记录相互匹配组合的记录，那样这般的结果集就能够叫作为笛卡尔积。 # 这三者效果同样，只要不写要求，就产生笛卡尔积，结果集的数量同样。select * from
t1, t2; # 内连接select * from t1 inner join
t2; # 全连接select * from t1 cross join
t2; 
表t1中有3条记录，表t2中亦有3条记录，两个表连接后的笛卡尔积就有3 x 3 = 9条记录，只要把两个表的记录数相乘，就能得到笛卡尔积的数量。
2、表连接的过程
笛卡尔积亦是一个很大的问题，不加限制要求，结果集的数量就会很大。例如你在研发过程中必须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，亦便是采用全表扫描的方式执行单表查找，筛选出符合要求的驱动表记录。 
这儿筛选出来的t1驱动表记录有2条。 从第1步中驱动表筛选出来的每一条记录，都要到t2表中查找匹配记录。匹配记录便是找到满足连接要求和过滤要求的记录。由于是按照t1表中的记录去找t2表中的记录，因此t2表亦能够叫作为「被驱动表」。上一步从驱动表筛选出了2条记录，寓意着必须从头到尾将t2表查找2次，此时就得看两表之间的连接要求了，这儿便是t1.m1 = t2.m2。
针对从t1表查找得到的第1条记录，而这条记录t1.m1=2，按照连接要求t1.m1 = t2.m2，就相当于在t2表加上过滤要求t2.m2 = 2，此时t2表相当于有了两个过滤要求t2.m2 = 2 and t2.n2 < d，而后到t2表执行单表查找，每当匹配到满足要求的一条记录后立即返回给MySQL客户端，以此类推。
因此全部连接查找的执行过程如下： 
最后连接查找的结果仅有2条记录。 
倘若把t1.m1 > 1这个过滤要求去掉了，那样从t1表查出的记录就有3条，就必须从头到尾扫3次t2表了。
「其实这个流程的招数便是用伪代码说明非常合适，你细品，看懂这个伪代码，你就理解了表连接的过程。」
for 筛选 驱动表 满足要求的每条记录 {
for 筛选 被驱动表 满足要求的每条记录 {
发送到MySQL客户端;
}
}
从这个伪代码能够看出，驱动表的每一条记录都会尝试遍历被驱动表的每条记录并匹配连接，每成功连接一条就返回给MySQL客户端。
总结：
在两表连接查找中，驱动表只需拜访一次，而被驱动表可能必须拜访多次。
并不是将所有满足过滤要求的驱动表记录先查找出来放到一个地区，而后再去被驱动表查找的（由于倘若满足过滤要求的驱动表很大，必须的临时存储空间就会非常大）。而是每得到一条满足过滤要求的驱动表记录，就立即到被驱动表中查找匹配的记录。
3、内连接和外连接
1.内连接
上面第二节所讲的，都是内连接。
先创立2张表，后续按照这2张表来讲解。 CREATE TABLE
student ( stu_no INT NOT NULL AUTO_INCREMENT COMMENT 学号
, name VARCHAR(5) COMMENT 姓名
, major VARCHAR(30) COMMENT 专业
,  RIMARY KEY
(stu_no) ) Engine=InnoDB CHARSET=utf8mb4 COMMENT 学生信息表
; CREATE TABLE
score ( stu_no INT COMMENT 学号
, subject VARCHAR(30) COMMENT 科目
, score TINYINT COMMENT 成绩
, RIMARY 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表数据如下： 
score表数据如下： 
倘若想要把学生的成绩都查出来，就必须表连接(score表中无姓名，因此不可只查score表)，连接过程便是从student表取出记录，而后在score表中查询number相同的成绩记录，连接要求是student.stu_no= score.stu_no; select * from student join score where
student.stu_no = score.stu_no; 
表连接的所有字段就在这里了，字段有点多，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; 
能够看到，学生的各科成绩都被查出来了。然则张小伟(学号为20210903的朋友)由于缺考，在score表中无记录。要是老师想查看所有学生的成绩(包含缺考的朋友)该怎么办呢？亦便是说，哪怕成绩为空，亦要表示这位朋友在这个表里面，咱们不可把他给踢了吧！
「这个问题就化为这个模型：针对驱动表中的某条记录，哪怕按照连接要求或过滤要求在被驱动表中无找到对应的记录，亦还是要把该驱动表的记录加到结果集。」
这便是内连接的局限性。
「其实咱们想要看到的结果集是这般的」 
为认识决这个问题，就有了「内连接」和「外连接」的区别。
针对内连接来讲，若驱动表中的记录根据「连接要求或过滤要求」在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集。
前面说到的都是内连接，例如前面例子中，当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 joinscoreas
s2 on
s1.stu_no = s2.stu_no;
从上面结果集能够看出，虽然张小伟缺考，然则还是在结果集中，只不外对应的科目成绩用NULL填充。
「右(外)连接的语法」 select * from
t1 right [outer] join
t2 on
要求 [where
普经过滤要求] # 重视这个on要求包含连接要求和驱动表与被驱动表的单表过滤要求。# []括号表率能够省略
右连接中，驱动表是右边的表，被驱动表是左边的表，右表所有记录都会有，左表无与之匹配的则用NULL填充。这儿就不举例了。
4、表连接的原理
1.简单的嵌套循环连接（Simple Nested-Loop Join）
咱们前边说过，针对两表连接来讲，驱动表只会拜访一遍，但被驱动表要被拜访到好多遍，详细拜访几遍取决于驱动表执行单表查找后满足要求的记录条数。
假设t1表和t2表都无索引，t1表和t2表内连接的大致过程如下：
过程1：选择驱动表t1，运用与驱动表t1关联的过滤要求，选择成本最低的单表拜访办法来执行对驱动表的单表查找。（按照你的索引和记录数量，查找优化器会选取成本最低的拜访办法，这儿无索引则全表扫描）
过程2：对上一步中查找驱动表得到的每一条满足要求的记录，都分别到被驱动表t2中查询匹配的记录。
详细细节在第二节说过，这儿就不细致展开。
倘若有第3个表t3进行连接的话，那样总体查找过程便是，查询t1表满足单表过滤要求的第1条记录，匹配连接t2表满足单表过滤要求的第1条记录(此时驱动表是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
;
这其实是嵌套循环连接算法执行的连接查找，再把上边那个查找执行过程拿下来给大众看一下：  fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)
查找驱动表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列，咱们能够进行如下尝试：
在m2列上创立索引，由于对m2列的要求是等值查询，例如t2.m2 = 2、t2.m2 = 3等，因此可能运用到ref的拜访办法，假设运用ref的拜访办法去执行对t2表的查找的话，必须回表之后再判断t2.n2 < d这个要求是不是成立。
在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的过程如下图所示：
「为何Join Buffer要装驱动表而不是被驱动表呢？上面说过，小表做为驱动表，Join Buffer装小表更易装得下，下一节会讲这个原由。」
其实很好记忆，想想笛卡尔积次序亦很奥妙。笛卡尔积的次序便是一条被驱动表记录匹配多条驱动表记录的次序，而不是一条驱动表记录去匹配被驱动表的记录的次序，你瞧瞧这个次序是不是很神奇，能够自动键两张表连接瞧瞧笛卡尔积，观察一下。
笛卡尔积次序是
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算法：」 驱动表的每一条记录都会去被驱动表逐一匹配，因此总的扫描行数是N * M（开头说了，扫描表便是把表从磁盘加载到内存中）；内存中的判断次数是N * M（扫描一次就会在内存中判断一次）。别纠结了，这种办法太笨了，不管选取哪个表做为驱动表，最后扫描和内存中判断的成本都是同样的。
「Index Nested-Loop Join算法」
该算法被驱动表的查找要求字段加上了合适的索引。 驱动表的每一条记录都会去被驱动表逐一匹配，因此总的扫描行数是N * log M（扫描行数不变，然则由于被驱动表有索引，扫描速度会大大增多）；内存中的判断次数是M * N（扫描一次就会在内存中判断一次）。「Block Nested-Loop Join算法：」
该算法又得区分Join Buffer装得下和装不下的状况。
「Join Buffer装得下的状况」 t1表和t2表都做一次全表扫描，将t1表记录都装入Join Buffer，总的扫描行数是M + N（开头说了，扫描表便是把表从磁盘加载到内存中，驱动表扫描M行一次性装到Join Buffer，被驱动表扫描一行会在Join Buffer进行比较，最后扫描N行）；内存中的判断次数是M * N，因为Join Buffer是以「无序数组」的方式组织的，因此呢对t2表中的每一行数据，都要与Join Buffer中的记录相比较。能够看到，调换这两个算式中的M和N没差别，因此呢此时候选取t1还是t2表做驱动表，成本都是同样的。
「Join Buffer装不下的状况」
咱们先用直观的数据述明过程，假如表t1是100行，而Join Buffer放不下，此时就分段放，执行过程就变成为了： 扫描表t1，次序读取数据行放入Join Buffer中，放完第80行Join Buffer满了，继续第2步；扫描表t2，把t2中的每一行取出来，跟Join Buffer中的所有记录做对比，满足join要求的，返回该条记录给MySQL客户端；清空Join Buffer；继续扫描表t1，次序读取最后的20行数据放入Join Buffer中，继续执行第2步。这个流程表现出了这个算法名字中“Block”的由来，暗示“分块的join”。
此刻总结一下这个过程。驱动表t1的数据行数是N，假设必须分K次才可完成算法流程，被驱动表t2的数据行数是M。
重视，这儿的K不是常数，N越大K就会越大。因此，在这个执行过程中： 扫描行数是N + K * M，每次装完一次Join Buffer，被驱动表t2的M条记录就会从头到尾去Join Buffer匹配，Join Buffer必须装K次，则扫描K次t2表；内存判断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，这般在内连接中便是强制左表为驱动表。」
欢迎一键三连~
| 
发表于 2024-8-4 10:51:37
