MySQL多表连接查询原理

关系型数据库中至关重要的一点就是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’);

从本质上来说：连接就是把各个表中的记录取出来按照要求匹配，并把匹配后的组合发送到客户端。

连接过程简介

正常情况下，我们可以连接任意数量的表。但是如果不附加任何限制条件，这些表连接起来产生的笛卡儿积(两张表无条件相连接产生的结果)可能是非常巨大的。比如三张100行记录的表连接起来产生的笛卡儿积就有100 * 100 * 100 =1000000行记录。所以在连接时过滤掉特定的记录组合是很有必要的。

比如如下这个sql:

SELECT * FROM t1,t2 WHERE t1.m1 >1 AND t1.m1 = t2.m2 AND t2.n2 <'d' ; 

在这个查询中，指明了3个过滤条件，这个连接查询的执行过程大致如下：
1、首先确定一个需要查询的表，这个表称为驱动表，只需要选取代价最小的那种访问方法去执行单表查询语句就好了。这里假设使用t1作为驱动表，那么就需要到t1表中查找满足t1.m1>1的记录。因为表中的数据太少，我们也没在表中建立二级索引，所以我们将t1表所用的访问方法设定为all,也就是采用全表扫描的方式执行单表查询。
2、步骤1中从驱动表中每获取到一条记录，都需要到t2表中查找匹配的记录。
所谓的匹配记录，指的就是符合过滤条件的记录。因为是根据t1表中的几率去找t2表中的记录，所以t2表也可以称为被驱动表。步骤1从驱动表中得到了2条记录。也就意味着需要查询2次t2表。也就是说，在两表的连接查询中，驱动表组需要访问一次，被驱动表可能需要访问多次。而且是每获取到一条驱动表记录，就立即到被驱动表中寻找匹配的记录。

内连接和外连接

首先创建两张表：

CREATE TABLE student(
	number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
	name VARCHAR(10) COMMENT '姓名',
	major VARCHAR(30) COMMENT '专业',
	PRIMARY KEY (number)
) Engine=InnoDB CHARSET = utf8mb4 COMMENT '学生信息表';

CREATE TABLE score(
	number INT COMMENT '学号',
	subject VARCHAR(30)  COMMENT '科目',
	score TINYINT COMMENT '成绩',
	PRIMARY KEY (number, subject )
) Engine=InnoDB CHARSET = utf8mb4 COMMENT '学生成绩表';

INSERT INTO  student VALUES(20210401,'张三','软件学院'),(20210402,'李四','计算机科学与工程'),(20210403,'王五','计算机科学与工程');

INSERT INTO  score VALUES(20210401,'MySQL是怎样炼成的',78),(20210401,'深入浅出MySQL',88),(20210402,'MySQL是怎样炼成的',100),(20210402,'深入浅出MySQL',84);

现在想要把所有学生的考试成绩都查询出来，就需要两表连接了，具体sql语句如下：

SELECT * FROM student, score WHERE student.number = score.number 

这样是将两张表的所有字段都查询出来。这时有个问题，假设有学生缺考，也就是学生信息表中有信息，但是没有成绩，这样的数据通过上面的sql查询时会因为没有关联条件而被剔除掉。
为了解决这个问题，就有了内连接和外连接的概念：

–对于内连接的两张表，若驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集。
–对于外连接的两个表，即使驱动表中的记录在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

根据需要选取的驱动表的不同，外连接细分为两种：
左外连接：选取左侧的表作为驱动表。
右外连接：选取右侧的表作为驱动表。

而作为内连接，其语句中的where和on子句是等价的。

左外连接的语法：

我们要把t1表和t2表进行左外连接查询：

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 过滤条件] ；

通过上述语句 将t1换成student表，将t2换成score表：

SELECT * FROM student LEFT JOIN score ON  student.number = score.number;

如上语句就可以解决掉有学生缺考，而没有学生姓名的问题了。

右外连接的语法

其语法与左外连接很像：

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 过滤条件] ;

内连接的语法

内连接和外连接的根本区别是内连接会将不符合on后面条件的数据剔除掉，其语法如下：

SELECT * FROM t1 [INNER | CROSS] JOIN t2 ON 连接条件 [WHERE 过滤条件] ;

连接的原理

前面介绍各种连接的基本概念就是为了介绍其原理，这样我们才能知道为什么有些连接的语句很慢，而有些很快。

嵌套循环连接

对于两表连接：驱动表只会被访问一遍，但是被驱动表要被访问很多次，具体访问几次取决于对驱动表执行单表查询后的结果集中有多少条记录。对于内连接来说，选哪张表作为驱动表都没关系；而外连接的驱动表是固定的，其执行内连接查询过程大致如下：
1、选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
2、对于步骤1中查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录。

这个过程就像是一个嵌套的循环，所以这种“驱动表只访问一次，但是被驱动表却可能访问多次，且其访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录”的连接执行方式称为嵌套循环连接。
值得注意的一点是，每从驱动表中获取到一条记录，都需要立即去被驱动表中查询一次，而所谓的驱动表中获取到的结果集是一个抽象的概念。