为了故事的顺利发展，我们先建立两个简单的表并给表填充数据：

  1. mysql> create table t1 (m1 int, n1 char(1));
  2. Query OK, 0 rows affected (0.02 sec)
  4. mysql> create table t2 (m2 int, n2 char(1));
  5. Query OK, 0 rows affected (0.02 sec)
  7. mysql> insert into t1 values(1, 'a'), (2, 'b'), (3, 'c');
  8. Query OK, 3 rows affected (0.00 sec)
  9. Records: 3  Duplicates: 0  Warnings: 0
  11. mysql> insert into t2 values(2, 'b'), (3, 'c'), (4, 'd');
  12. Query OK, 3 rows affected (0.00 sec)
  13. Records: 3  Duplicates: 0  Warnings: 0

t1、t2 两个表，这两个表都有两个列，一个列是 int 类型的，另一个列是 char(1) 类型的，
填充好数据的两个表如下图所示：
图片.png图片.png

连接的介绍

连接的本质就是：把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回结果集给用户。
所以把 t1 和 t2 两个表连接起来的过程如下图所示：
图片.png
连接查询的结果集中包含：一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，
像这样的结果集被称为笛卡尔积。
表 t1 中有 n 条记录，表 t2 中有 m 条记录，这两个表连接之后的笛卡尔积就有 n × m 行记录。

连接的过程
在连接查询中的过滤条件可以分成两种：
涉及单表的条件：比如：t1.m1 > 1 这个条件是只针对 t1 表的过滤条件。
涉及两表的条件：比如 t1.m1 = t2.m2 这个条件涉及到了两个表。

  1. select * from t1, t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';

在上面这个查询中我们指明了这三个过滤条件：

  • t1.m1 > 1
  • t1.m1 = t2.m2
  • t2.n2 < ‘d’

以上面查询为例，介绍带有过滤条件的连接查询的大致执行过程：

  1. 首先确定第一个需要查询的表。

在连接查询中，第一个被查询的表被称为驱动表。
假设使用 t1 作为驱动表，即首先到 t1 表中查询满足 t1.m1 > 1 条件的记录，
可以看到，t1 表中符合 t1.m1 > 1 条件的记录有两条。图片.png

  1. 针对第 1 步中，从驱动表产生的结果集中的每一条记录，分别到 t2 表中查找匹配的记录。

所谓匹配的记录，指的是：符合过滤条件的记录。
因为是根据 t1 表中的记录去找 t2 表中的记录，所以 t2 表也被称为被驱动表。
第 1 步中，从驱动表中得到了 2 条记录，所以需要查询 2 次 t2 表。
此时涉及两个表的列的 t1.m1 = t2.m2 过滤条件就派上用场了：

  • 当 t1.m1 = 2 时，过滤条件 t1.m1 = t2.m2 就相当于 t2.m2 = 2，

所以此时 t2 表相当于有了 t2.m2 = 2、t2.n2 < ‘d’ 这两个过滤条件，然后到 t2 表中执行单表查询。

  • 当 t1.m1 = 3 时，同理。

也就是说，整个连接查询最后的结果只有两条符合过滤条件的记录：
图片.png
从上边 2 个步骤可以看出，上面的连接查询共查询 1 次 t1 表，2 次 t2 表。
也就是说：在两表连接查询中，只需要访问一次驱动表，可能访问多次被驱动表。

内连接 & 外连接

该小节用以下表做演示

  1. create table student (
  2.     number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  3.     name VARCHAR(5) COMMENT '姓名',
  4.     major VARCHAR(30) COMMENT '专业',
  5.     PRIMARY KEY (number)
  6. ) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';
  8. create table score (
  9.     number INT COMMENT '学号',
  10.     subject VARCHAR(30) COMMENT '科目',
  11.     score TINYINT COMMENT '成绩',
  12.     PRIMARY KEY (number, score)
  13. ) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';

图片.png图片.png

现在我们想把每个学生的名字和考试成绩都查询出来，这就需要进行两表连接查询了。
查询语句如下：

  1. select student.number, student.name, score.subject, score.score 
  2. from student, score 
  3. where student.number = score.number;

查询结果如下：
图片.png
从上述查询结果可以看到，各个同学对应的各科成绩就都被查出来了。
由于 score 表中没有史珍香同学的对应的成绩记录，所以它的信息并未被展示。
现在的需求是：即使没有对应成绩，学生的学号和姓名也要展示出来。
这个需求的本质是：驱动表中的记录即使在被驱动表中没有匹配的记录，仍然需要加入到结果集。
为了解决这个问题，就有了内连接 和 外连接的概念：

  • 对于内连接的两个表，驱动表中的记录在被驱动表中没有匹配的记录时，

该记录不会被加入到结果集中。

  • 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录时，

该记录仍然仍然会被加入到结果集中。
内连接和外连接的区别是：当驱动表中的记录不符合 on 子句中的连接条件时会不会把该记录加入到结果集。
在 MySQL 中，根据选取驱动表的不同，外连接可以细分为 2 种：

  • 左外连接：选取左侧的表为驱动表。
  • 右外连接：选取右侧的表为驱动表。

现在仍然存在一个问题：对于外连接来说，有时候我们并不想把驱动表的全部记录都加入到结果集中。
所以 MySQL 把过滤条件分为两种类型，放在不同地方的过滤条件有不同语义：

  • where 子句中的过滤条件：

不论是内连接还是外连接，凡是不符合 where 子句中的过滤条件的记录都不会被加入到结果集中。

  • on 子句中的过滤条件：

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 on 子句中的过滤条件的记录，
那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 null 值填充。
需要注意的是：
on 子句是专门为外连接驱动表中的记录在被驱动表中找不到匹配的记录时，仍然把该记录加入结果集这个场景下提出的，
所以如果把 on 子句放到内连接中，MySQL 会把 on 子句和 where 子句一样对待，
也就是说：内连接中的 where 子句和 on 子句是等价的。

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

外连接的语法

  1. # 左外连接
  2. select * from t1 left [outer] join t2 on 连接条件 [where 普通过滤条件];
  3. # 右外连接
  4. 把 left 换成 right

上面查询成绩的例子对应的左外连接查询语句为

  1. select student.number, student.name,score.subject, score.score 
  2. from student 
  3. left join score 
  4. on student.number = score.number;

图片.png

内连接的语法

  1. select * from t1 [inner | cross] join t2 [on 连接条件] [where 普通过滤条件];
  3. # 在 MySQL 中，下边这几种内连接的写法是等价的
  4. select * from t1 join t2;
  5. select * from t1 inner join t2;（推荐这种写法，比较简明）
  6. select * from t1 cross join t2;
  7. select * from t1, t2;

连接的原理

嵌套循环连接

对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到很多遍，
具体访问几遍取决于：对驱动表执行单表查询后的结果集中的记录条数。
对于内连接来说，选取哪个表为驱动表都没关系，
而外连接的驱动表是固定的，也就是说：左外连接的驱动表就是左边的那个表，
右外连接的驱动表就是右边的那个表。

t1 表和 t2 表执行内连接查询的大致过程，温习一下：

  1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
  2. 对第 1 步中查询驱动表得到的结果集中每一条记录，分别到被驱动表中查找匹配的记录。

如果有 3 个表进行连接的话，那么步骤 2 中得到的结果集就像是一个新的驱动表，
然后第 3 个表就成为了新的被驱动表，重复上边过程，
也就是步骤 2 中得到的结果集中的每一条记录都需要到 t3 表中查找匹配的记录。
用伪代码表示这个过程，如下：

  1. for each row in t1 {                # 此处表示遍历满足对 t1 单表查询结果集中的每一条记录
  2.     for each row in t2 {            # 此处表示对于某条 t1 表的记录来说，遍历满足对 t2 单表查询结果集中的每一条记录
  3.         for each row in t3 {        # 此处表示对于某条 t1 和 t2 表的记录组合来说，对 t3 表进行单表查询
  4.             if row satisfies join conditions, send to client
  5.         }
  6.     }
  7. }

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

使用索引加快连接速度

在嵌套循环连接的步骤 2 中可能需要访问多次被驱动表，
查询 t2 表相当于一次单表扫描，可以利用索引来加快查询速度。

t1 表和 t2 表进行内连接的例子：

  1. select * from t1, t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';

查询驱动表 t1 后的结果集中有两条记录，嵌套循环连接算法需要对被驱动表查询 2 次：

  • 当 t1.m1 = 2 时，去查询一遍 t2 表，

对 t2 表的查询语句相当于：select * from t2 where t2.m2 = 2 and t2.n2 < ‘d’;

  • 当 t1.m1 = 3 时，去查询一遍 t2 表，

此时对t2表的查询语句相当于：select from t2 where t2.m2 = 3 and t2.n2 < ‘d’;
可以看到，原来的 t1.m1 = t2.m2 这个涉及两个表的过滤条件在针对 t2 表做查询时，
关于 t1 表的条件就已经确定了，
所以我们只需要优化对 t2 表的查询了，
上述两个对 t2 表的查询语句中利用到的列是 m2 和 n2 列，我们可以：
*在 m2 列上建立索引：
因为对 m2 列的条件是等值查找，所以可能使用到 ref 的访问方法，
假设使用 ref 访问方法 去执行对 t2 表的查询的话，需要回表之后再判断 t2.n2 < d 这个条件是否成立。

这里有一个比较特殊的情况，假设 m2 列是 t2 表的主键或者唯一二级索引列，
那么使用 t2.m2 = 常数值 这样的条件从 t2 表中查找记录的代价是常数级别的。
在单表中使用主键值 或者 唯一二级索引列的值进行等值查找的方式被称为 const。
在连接查询中，
对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式被称为：eq_ref。

在 n2 列上建立索引：
同理

基于块的嵌套循环连接

扫描一个表的过程是：先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。
现实场景中的表里记录可能上千万条条，可能内存里不能完全存放下表中所有的记录，
所以在扫描表的前边的记录的时候，可能后边的记录还在磁盘上，
等扫描到后边的记录的时候可能就内存不足了，需要把前边的记录从内存中释放掉。
如果采用嵌套循环连接算法的两表连接，被驱动表可能要被访问好多次，
如果这个被驱动表中的数据特别多而且不能使用索引进行访问的话，
那就相当于要从磁盘上读好几次这个表，这个 I/O 代价就非常大了，
所以得想办法尽量减少访问被驱动表的次数。

所以设计 MySQL 的人提出了 join buffer 的概念。
join buffer 就是执行连接查询前申请的一块固定大小的内存，
先把若干条驱动表结果集中的记录装在 join buffer 中，然后开始扫描被驱动表，
每一条被驱动表的记录一次性和 join buffer 中的多条驱动表记录做匹配，这样减少被驱动表的 I/O 代价。使用 join buffer 的过程如下图所示：
图片.png
这种加入了 join buffer 的嵌套循环连接算法被称为基于块的嵌套连接 (Block Nested-Loop Join) 算法。
最好的情况是：join buffer 足够大，能容纳驱动表结果集中的所有记录，
这样只需要访问一次被驱动表就可以完成连接查询操作了。
这个 join buffer 的大小是可以通过启动参数 或者 系统变量 join_buffer_size 进行配置的，
join buffer 的默认大小为 262144 字节（也就是 256 KB），最小可以设置为 128 字节。

对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引。
如果实在不能使用索引，并且机器的内存也比较大的情况下，可以尝试调大 join_buffer_size 的值来对连接查询进行优化。
需要注意的是：
驱动表的记录并不是所有列都会被放到 join buffer 中，
只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中，
所以再次提醒我们，最好不要把 * 作为查询列表，这样还可以在 join buffer 中放置更多的记录。