第 11 章两个表的亲密接触——连接的原理连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用

11.1 连接简介

11.1.1 连接的本质

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');

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。

11.1.2 连接过程简介

如果没有连接条件，就会产生笛卡尔集，连接条件可以分为两种：

涉及单表的条件

比如 t1.m1 > 1 或者 t2.n2 < 'd'

涉及两表的条件

比如 t1.m1 = t2.m2

11.1.3 内连接和外连接

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

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

INSERT INTO `how_mysql_run`.`student`(`number`, `name`, `major`) VALUES (20180101, '肚子腾', '软件学院');
INSERT INTO `how_mysql_run`.`student`(`number`, `name`, `major`) VALUES (20180102, '范统', '计算机科学与工程');
INSERT INTO `how_mysql_run`.`student`(`number`, `name`, `major`) VALUES (20180103, '史珍香', '计算机科学与工程');

INSERT INTO `how_mysql_run`.`score`(`number`, `subject`, `score`) VALUES (20180101, '母猪的产后护理', 78);
INSERT INTO `how_mysql_run`.`score`(`number`, `subject`, `score`) VALUES (20180101, '论萨达姆的战争准备', 88);
INSERT INTO `how_mysql_run`.`score`(`number`, `subject`, `score`) VALUES (20180102, '论萨达姆的战争准备', 98);
INSERT INTO `how_mysql_run`.`score`(`number`, `subject`, `score`) VALUES (20180102, '母猪的产后护理', 100);

每个学生的考试成绩都查询出来

SELECT
	s1.number,
	s1.NAME,
	s2.SUBJECT,
	s2.score
FROM
	student AS s1, score AS s2
WHERE s1.number = s2.number;

WHERE 子句中的过滤条件

WHERE 子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合 WHERE 子句中的过滤条件的记录，都不会被加入最后的结果集。

ON 子句中的过滤条件

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。

在内连接场景下，WHERE 和 ON 是等价。

一般情况下，我们都把只涉及单表的过虑条件放到 WHERE 子句中，把涉及两表的过虑条件都放到 ON 子句中，我们一般把放到 ON 子句中的过虑条件称为 连接条件。

11.2 连接的原理

11.2.1 嵌套循环连接（Nested-Loop Join）

for each row in t1 { # 此处表示遍历满足对 t1 单表查询结果集中的每一条记录
    for each row in t2 { # 此处表示对于某条 t1 表的记录来说，遍历满足对 t2 单表查询结果集中的每一条记录
        for each row in t3 { # 此处表示对于某条 t1 和 t2 表的记录组合来说，对 t3 进行单表查询
        }
    }
}

这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称为 嵌套循环连接（Nested-Loop Join），这是最简单，也是最笨拙的一种连接查询算法。

11.2.2 使用索引加快连接速度

使用嵌套循环连接算法执行的连接查询，在查询 t2 时实际是是一个单表查询，所以可以使用索引进行查询优化。

11.2.3 基于块的嵌套循环连接（Blocke Nested-Loop Join）

join buffer：执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个 join buffer 中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的 I/O 代价。

在这里插入图片描述

最好的情况是 join buffer 足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。可以通过启动参数或系统变量 join_buffer_size进行配置，默认大小为 262144 字节（256K）

11.3 总结

连接分为内连接和外连接，外连接又分为左外连接和右外连接。
嵌套循环连接算法是指驱动表只访问一次，被驱动表可能访问多次。
如果被驱动表非常大，多次访问可能导致多次I/O，可以使用基于块的嵌套循环连接算法来缓解由此造成的性能损耗。

第 11 章 两个表的亲密接触——连接的原理