连接原理

Note

文中缺少的部分：

HashJoin，8.0加入的特性
Batched Key Access

为什么BNLJ在理想状态下是查询一次被驱动表？
为什么小表驱动大表？
- 小表查出来的记录少，那么遵循记录次数进入被驱动表查询时，这个次数会少很多。

连接简介

简单介绍以下join关键字的使用。

case：

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

+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+

+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+

连接本质

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

因此，如下的语句，对应的结构化映射如图：

select * from t1 join t2
#或
select * from t1,t2

两表各有3条数据，因此最终数据集中出现了 3x3=9条数据，这个结果集称之为 笛卡尔积。

连接过程

因为笛卡尔积的性质，因此如果不带任何条件，那么在多表连接的情形下，笛卡尔积就会非常巨大。

例如，三个100行记录的表，不做任何条件筛选的情形下就会产生10^6^个笛卡尔积

因此，在非必要的情形下，一般都会使用过滤条件来让减小笛卡尔积。过滤条件有以下两种：

单表：t1.m1<1 and t2.n2<d
多表：t1.m1 = t2.m2

例如，以下的查询语句：

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

指明了三个过滤条件：

t1.m1>1 , t2.n2<'d;
t1.m1=t2.m2

这个连接的执行过程如下：

确定第一个需要查询的表（这个表称为驱动表）。此处假设驱动表为t1
1. 从驱动表中，通过代价最低的查询方式找到满足对应单表查询的记录（语句中为t1.m1>1）
根据上一步骤的结果集中的每一条记录，到t2表（此处称为被驱动表）中查找匹配的记录（符合过滤条件的记录）。

因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次t2表。(也就是说查询被驱动表的次数，是根据驱动表的查询结果确定的)
- 根据上一步骤的结果集和多表的过滤条件拼接额外的查询条件
  - 例如：上述SQL中的：t1.m1 = t2.m2
    
    这里就变成了：t2.m2 = 2 and ...和 t2.m2 = 3 and ...
- 根据拼接后得到的所有条件，到t2表中进行单表查询

内连接、外连接

case:

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, subject)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';

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

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

case:

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score AS s2 WHERE s1.number = s2.number;

+----------+-----------+-----------------------------+-------+
| number   | name      | subject                     | score |
+----------+-----------+-----------------------------+-------+
| 20180101 | 杜子腾    | 母猪的产后护理              |    78 |
| 20180101 | 杜子腾    | 论萨达姆的战争准备          |    88 |
| 20180102 | 范统      | 论萨达姆的战争准备          |    98 |
| 20180102 | 范统      | 母猪的产后护理              |   100 |
+----------+-----------+-----------------------------+-------+

根据上述查询可以看到每位同学以及对应课程的成绩。但是由于史珍香没有参加考试，因此score表中没有记录，因此也无法找到对应的数据。

如果需要该同学的数据，本质上是：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

为了解决这个问题，引出内连接和外连接的概念。

对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接（用,或者join或者inner join或者cross join）。
对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集（使用left/right （outer） join）。
- left/right join的含义，是取左/右表为驱动表。
- 对外连接而言，过滤条件分成2种：
  - where中：不符合条件的不加入结果集。
  - on中：无法找到的话，对应被驱动表的各个字段用NULL值填充。
    - 在内连接中，on的查询子句和where是等价的。
    - on子句中的过滤条件称为连接条件。

连接原理

嵌套循环连接(Nested-Loop Join)

在上述描述中可以得知：

驱动表查询完后，需要根据驱动表查询的结果集，一条一条地从被驱动表中查询出结果集。

多表的连接和两表是相同的方式：（驱动表- > 被驱动表 - > 结果集）as 驱动表，加入这个递归中。

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

使用索引加快连接速度

根据上面描述的NLJ（嵌套循环连接），查询完驱动表后，会根据结果集中的每条数据，根据连接条件以及查询条件，去被驱动表中依次做单表查询。

既然本质上是单表查询，那么NLJ的查询可以从以下的方式着手进行优化：

被驱动表中的数据尽量在索引中，省去回表的操作
连接条件和查询条件尽量走索引

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

根据嵌套循环连接的执行顺序，如果不能使用索引加速连接速度，同时数据量特别大的时候，每次在被驱动表中做查询基本都相当于随机IO，那么IO代价就非常大。

此时，需要从减少被驱动表的访问次数上入手。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

为了解决这个问题，MySQL提出了join buffer的概念：

join buffer就是执行连接查询前申请的一块固定大小的内存
- 把若干条驱动表结果集中的记录装在join buffer中
- 扫描被驱动表
- 每一条被驱动表的记录一次性和join buffer中的多条驱动表记录匹配
由于匹配过程都在内存中完成，因此显著减少被驱动表的随机IO次数。

理想情况下join buffer是够大，能够容纳下驱动表结果集的所有记录，那么被驱动表就只需要访问一次了。这种加入了join buffer的嵌套循环连接算法称为 基于块的嵌套循环连接（Block Nested-Loop Join）。

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

另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中。

[mySQL]小册笔记 - join