sql优化(3)-表连接原理
表连接介绍
为了比较好地理解，先创建一张学生信息表和学生成绩表作为例子：
CREATE TABLE student (
stu_no INT NOT NULL comment '学号',
stu_name VARCHAR(5) COMMENT '姓名',
stu_major VARCHAR(5) COMMENT '专业',
PRIMARY KEY (stu_no)
） COMMENT '学生信息表';
CREATE TABLE score (
stu_no INT comment '学号',
subject VARCHAR(30) comment '科目',
score INT comment '成绩',
PRIMARY KEY (stu_no, subject)
) COMMENT '学生成绩表';
向这两个表中插入一些数据。
对于表连接，SQL语法上可以连接任意数量的表。但是如果不附加任何限制条件，这些表连接产生的笛卡儿积可能是非常巨大的。比如 100 行记录的表连接起来产生的笛卡儿积就有 100 X 100 X 100 = 1 000 000 行记录。所以在表连接时过滤掉特定的记录组合是有必要的。
例如下面这个查询：
---查询除王五之外的学生成绩及格的信息
select * from student , score
where student.name!='王五'
and student.stu_no=score.stu_no
and score.score>60
在连接查询中的过滤条件可以分成下面两种：
- 涉及单表的条件: 比如 student.name!=‘王五’ 和 score.score>60
- 涉及两表的条件：比如 student.stu_no=score.stu_no
这个连接查询的执行过程大致如下：
**步骤1：**首先确定第一个需要查询的表，这个表称为驱动表。假设驱动表是 学生表 student ,对于 学生表 student 的单表筛选条件就是 student.name!=‘王五’ 。单表访问方法有很多，这里因为数据量太少，直接全表扫描，即扫描方法为 all。
**步骤2：**步骤1中从驱动表每获取到一条记录，都需要到成绩表 score 中查找匹配的记录。匹配的记录，指的是符合过滤条件的记录。因为是根据 student 表中的记录去找 score 表中的记录，所以 score 表也可以称为被驱动表。步骤1从驱动表中得到了 2条记录，也就意味着需要查询2次socre表。
? 当从student表查询到的第一条记录，也就是当 name!=‘王五’ ，过滤条件 student.stu_no=score.stu_no 相当于 score.stu_no=‘20180101’ ，score表相当于有了 stu_no=‘20180101’ 和 score>60 这两个过滤条件，然后到score表执行单表查询。
? 当从student表查询到的第二条记录，也就是当 name!=‘王五’ ，过滤条件 student.stu_no=score.stu_no 相当于 score.stu_no=‘20180102’ ，score表相当于有了 stu_no=‘20180102’ 和 score>60 这两个过滤条件，然后到score表执行单表查询。
最终获取的查询结果就是：
王五同学（也就是学号为20180103的同学）因为某些原因没有参加考试，所以在成绩表中没有对应的成绩记录。如果老师想查看所有学生的考试成绩，即使是缺考的学生，他们的成绩也应该展示出来。
这个需求的本质是这样的：针对驱动表中的某条记录，即使在被驱动表中没有找到与之匹配的记录，也仍然需要把该驱动表记录加入到结果集。为了解决这个问题，就有了内连接和外连接的概念。
- 对于内连接的两个表，若驱动表中的记录在被驱动表找不到匹配的记录，则该记录不会加入到最后的结果集
- 对于外连接的两个表，即使驱动表中的记录在被驱动表中没有匹配的记录，也仍然需要加入到结果集
MySQL 中，根据选取的驱动表的不同 ，外连接可以细分为 ：
- 左外连接：选取左侧的表为驱动表 ( A left join B A为左侧)
- 右外连接：选取右侧的表为驱动表 ( A right join B B为右侧)
对于外连接来说，有时候不想把驱动表的全部记录都加入到最后的结果集。 为了解决这个问题，过滤条件在不同的地方是有不同的语义的。
WHERE 子句中的过滤条件
WHERE子句中的过滤条件，不论是内连接还是外连接，凡是不符合WHERE 子句中过滤条件的记录都不会被加入到最后的结果集 。
ON 子句中的过滤条件
对于外连接的驱动表中的记录来说，如果无法在被驱动表中找到匹配 ON子句中过滤条件的记录，那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充
需要注意 ，这个 ON 子句是专门为"外连接驱动表中的记录在被驱动表找不到匹配记录时是否应该把该驱动表记录加入结果集中"这个场景提出的。所以，如果把 ON 子句放到内连接中，MySQL 会把它像 WHERE 子句一样对待。 也就是说内连接中的 WHERE 子句和 ON 子句是等价的
连接的原理
嵌套循环连接
对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问好多遍; 具体访问几遍取决于对驱动表执行单表查询后的结果集中有多少条记录。对于内连接来说，选取哪个表为驱动表都没关系；而外连接的驱动表是固定的，也就是说左(外)连接的驱动表就是左边的那个表，右(外)连接的驱动表就是右边的那个表。前面已经介绍过表连接查询的大致过程：
步骤1：选取驱动衰，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询
步骤2：对步骤1 中查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录 (也是选择代价最低的单表访问方法)
如果有3个表进行连接，那么步骤2中得到的结果集就像是新的驱动表，然后第3个表就成为了被驱动表，然后重复上面的过程。也就是针对步骤 2中得到的结果集中的每一条记录，都需要~到第3个表中找一找有没有匹配的记录。
---伪代码
for each row in t1 satisfying conditions about tl {
for each rQW in t2 satisfying cond tions about t2 {
for each row in t3 satisfying conditions about t3 {
send to client;
}
}
}
这个过程就像是一个嵌套的循环，所以这种" 驱动表只访问1次，但被驱动表却可能访问多次，且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录"的连接执行方式 称为嵌套循环连接（Nested-Loop Join) ，这是最简单也是最笨拙的一种连接查询算法。
需要注意的是,对于嵌套循环连接算法来说，每当从驱动表中得到了一条记录时，就根据这条记录立时到被驱动表中查询一次。如果得到了匹配的记录， 就把组合后的记录发送给客户端。然后再到驱动表中获取下一条记录；这个过程将重复进行。不是把驱动表中所有的记录都先查出来放到某个地方(比如内存或者磁盘中) ，然后再遍历这些记录。
另外对于全套循环连接，本质上也是嵌套的单表查询，所以可以利用索引进行查询优化，这里就是在单表方法方法中有详细说明
基于块的嵌套循环连接
现实中的表数据量一般比较大，几百万、几千万甚至几亿条记录的表到处都是。假设不能使用索引加快被驱动表的查询过程，所以对于驱动表结果集中的每一条记录， 需要对被驱动表执行全表扫描。这样在对被驱动表进行全表扫描时，可能表前面的记录还在内存中，而表后面的记录还在磁盘上。而等到扫描表中后面的记录时，有可能由于内存不足，需要把表前面的记录从内存中释放掉，给现在正在扫描的记录腾地方。在采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次。如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读这个表好多次，这个 磁盘I/O 的代价就非常大了.所以得想办法，尽量减少被驱动表的访问次数。
驱动表结果集中有多少条记录，就可能把被驱动表从磁盘加载到内存中多少次。是否可以在把被驱动表中的记录加载到内存时，一次性地与驱动表中的多条记录进行匹配呢？这样就可以大大减少重复从磁盘上加载被驱动表的代价了 。MySQL 中有一个名为 Join Buffer (连接缓冲区）的概念。
Join Buffer 就是在执行连接查询前申请的一块固定大小的内存。先把若干条驱动表结果集中的记录装在这个 Join Buffer 中，然后开始扫描被驱动表，每一条被驱动表的记录一次性地与 Join Buffer 中的多条驱动表记录进行匹配。由于匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的磁盘I/O 代价。
最好的情况是 Join Buffer 够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了把这加入了 Join Buffe 的嵌套循环连接算法称为基于块的嵌套循环连接 (Block Nested-Loop Join） 算法。
这个 Join Buffer 的大小可以通过启动选项或者系统变量 join_buffe _size 进行配置, 默认大小为 262144 字节(也就是 255KB) ，最小可以设置为128 字节.当然，在优化对被驱动表的查询时，最好是为被驱动表加上高效率的索引。如果实在不能使用索引，并且自己机器的内存也比较大 ，则可以尝试调大 join_buffer_size 的值来对连接查询进行优化。
另外需要注意的是， Join Buffer 中并不会存放驱动表记录的所有列，只有查询列表中的列和过滤条件中的列才会被放到 Join Buffer 中，所以最好不要把*作为查询列表，只需要把真正想要查询的列放到查询列表，这样可以 Join Buffer 中放置更多的记录
参考：MySql 是怎样运行的_从根上理解Mysql
