mysql-11章（表连接）

Mr.Joden

已于 2023-03-28 01:31:55 修改

阅读量144

点赞数 1

分类专栏： mysql 文章标签： sql 数据库

于 2023-03-28 01:19:56 首次发布

本文链接：https://blog.csdn.net/m0_60633107/article/details/129807607

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

(1) 了解一些新概念：笛卡尔积、内连接和外连接、左外连接和右外连接、驱动表和被驱动表、嵌套循环连接、块嵌套循环连接、连接缓冲区
(2) 了解内连接和外连接的应用场景和语法
(3) 连接的原理
(4) 总结连接查询优化

连接

### 笛卡尔积
把多表连接后，不同表的记录间相互组合形成的组合记录结果集，即称为笛卡尔积。例如表1中有3条记录，表2中有3条记录，分别从表1和表2中取出一条记录，则可以组成3x3=9条记录，即笛卡尔积就是这9条记录结果集。
注意，笛卡尔积并不是包含条件过滤后的结果集，而是连接表的记录集合的组合运算得到的结果集。当然我们可以使用条件语句在笛卡尔积的基础上筛选出符合条件的结果集。

### 连接分类
(1) 分类缘由：
这和我们的业务需求有关，表连接后的结果集(笛卡尔积)很多，我们再根据条件筛选后，获取最终结果集。然而，在对笛卡尔积筛选的过程中(条件筛选)可能一个表的条件字段值并不能匹配到另一个表的相关字段值，那么你是否最终获取到这条记录。
场景举例：现有学生表和成绩表，学生表sid范围1-7，成绩表sid范围1-6，因为sid=7的学生缺考了没录入成绩到学生表，那么现在根据学生表中所有的sid(0-7)来查询学生成绩的结果集，那么在结果集的处理选择中，我们可以选择让sid=7的学生成绩显示为null，这样可以保证查询的完整性(这就是外链接)，当然也可以舍弃sid=7的结果集(这就是内连接)

(2) 分类：
综上在mysql中表连接分为：内连接和外连接。
内连接：select sid,sname,score from student,score where student.sid = score.id;
外连接：select sid,sname,score from student left join score on student.sid = score.id;
当然，这不是所有的连接语句形式，参考后面的内连接和外连接。


### 驱动表和被驱动表
       在了解了连接以及分类之后，这个两个概念需要明确。
示例：select sid,sname,score from student,score where student.id<10 and student.sid = score.id;
表连接中，被确定为第一个需要查询的表就是驱动表，比如上面示例语句中的student表可为驱动表，因为首先可以根据student.id=10这个调节过滤出student表中id<10的记录，然后再用这些记录去分别匹配score表中的记录最终得到笛卡尔积，那么sscore表就是被驱动表(为执行匹配被多次查询的表)。
当然，并不是第一个表就是驱动表，它取决于JOIN操作的语法和优化器的选择。通常情况下，在LEFT JOIN操作中，LEFT关键字前的表是驱动表，但如果使用了STRAIGHT_JOIN语法，则指定的表(STRAIGHT_JOIN前面的表)将作为驱动表。
       (1) 驱动表：被确定为第一个需要查询的表，仅查询一次。
   (2) 表驱动表：为执行匹配被多次查询的表，需要查询多次。

### 内连接和外链接
(1) 简要：
前面已经提到了，这里给出简单描述。
内连接：即舍弃笛卡尔积中条件无法匹配成功的结果集。
外连接：即保留笛卡尔积中条件无法匹配成功的结果集。
扩展：了解了外连接保留无法匹配成功的结果集，那么结果集是以哪个表为基准呢，所以mysql中对外连接又有了划分，以左表为基准的外连接称为左外连接(左连接)，以右表为基准的连接称为右外连接(右连接)。

(2) 语法：
以下皆为内连接的语法：
select * from t1,t2 [where 筛选条件];
select * from t1 join t2 on 连接条件 [where 筛选条件];
select * from t1 inner join t2 连接条件 [where 筛选条件];
select * from t1 cross join t2 (注意该连接方式又称为交叉连接，不能有连接条件，结果集即为笛卡尔积);
以下皆为外连接的语法：
select * from t1 left [outer] join t2 on 连接条件 [where 筛选条件];   【左外连接】
select * from t1 right [outer] join t2 on 连接条件 [where 筛选条件];【右外连接】
以下为指定驱动表的连接语法：
select * from t1 straight_join t2 连接条件 [where 筛选条件]; (指定t1为驱动表)
以下为推荐语法(语义明确)：
inner join ：内连接
left join：左外连接
right join：右外连接
语法总结：
-- on只能出现在join语句中，不能直接出现在select from 语句中。
-- 外连接分为左外连接和右外连接。
-- 外连接语法中的outer可以省略。
-- 笛卡尔积可以在on(连接条件)的基础上过滤，再在where(筛选条件)的基础上又一次过滤。

### where子句的内连接
        使用where子句的内连接形式容易出现笛卡尔积问题，是因为它没有明确指定连接条件，而是依靠where子句中的条件来筛选符合要求的行。如果没有正确地指定连接条件，就很容易出现笛卡尔积问题。
        笛卡尔积问题是指，当两个表进行连接时，结果集中的每一行都来自于表1和表2中的某一行组合。如果两个表中都有100行数据，那么笛卡尔积将返回10000行数据，这是一个非常大的结果集。如果查询中的where条件不足以筛选掉不需要的行，那么结果集将会非常的庞大，导致查询非常缓慢，甚至耗尽系统资源。
        因此，在使用内连接时，应该明确指定连接条件，以避免笛卡尔积问题。inner join语法和逗号（,）作为连接符号的内连接形式是比较安全的，因为必须指定连接条件，而且语法比较清晰，易于理解和维护。

连接的原理

### 前言：
       首先，连接查询是由驱动表到被驱动表的过程，且每从驱动表获取一条记录，就立刻到被驱动表查询匹配记录(即行策略)。
了解了驱动表到被驱动表的过程之后，考虑多个表的连接时，首个被驱动表被执行完成返回的结果集后，可以作为下一个被执行匹配的被驱动表的驱动表，这样类推就会形成一个在mysql中称为Nested-Loop Join(嵌套循环连接)的连接操作算法。
当然，驱动表每次查询一条记录后，就会根据该记录到被驱动表做匹配的单表查询，而对于单表查询我们知道默认的访问方法是all(不设主键和其他索引)，所以我们可以在这里考虑建立适当的索引，来优化被驱动表的单表查询，从而来优化连接查询。
接下来就要思考一个上限问题了，如果被驱动表记录很庞大，要知道获取数据和IO有关，查询匹配这个过程是在内存中完成的，所以如果每获取的一条驱动表记录的同时都要立刻去到被驱动表中做匹配查询，那么此时IO代价很大且内存也有压力，所以对于这个上限问题，mysql中有了Join Buffer(连接缓冲区)的概念。
有了Join Buffer概念，跟随的连接算法又诞生Block Nested-Loop Join(基于块的嵌套循环连接)。同时要知道Block Nested-Loop Join并不是默认的连接操作算法，而是可选的连接操作算法，默认的连接操作算法是Nested-Loop Join(嵌套循环连接)，当然这都是mysql查询优化器自动选择的。


### 连接缓冲区
Join Buffer即连接缓冲区，正如前言中为解决的上限问题，它的实现原理也可以简单理解。
申请一块固定大小的内存区域作为当前连接的连接缓冲区，然后先把若干条驱动表的记录加载到这个缓冲区，然后开始扫描被驱动表，扫描的同时，让被驱动表的每一条记录和缓冲区中的多条驱动表记录进行匹配。这样通过在内存中匹配多条驱动表的记录就可以减少被驱动表的IO了，同时也可以减少在内存中的匹配次数。
另外重要的是，Join Buffer中并不是存储驱动表的所有列，而是只存储我们查询语句中所需要的列，所以这里可以通过指定列(避免使用*)，从而节约Join Buffer空间进而存放更多的驱动表记录，从而来优化连接查询。
最后，与之相关的系统变量为join_buffer_size，默认大小为256KB，我们可以根据需求进行适当调大配置从而来优化连接查询。