Session 11 连接的原理

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。所以我们把t1和t2两个表连接起来的过程如下图所示：

1
mysql> SELECT * FROM t1, t2; # 只要在FROM语句后边跟多个表名就好了(内连接)

image-20230729212455019

这个过程看起来就是把t1表的记录和t2的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。

比如对于如下语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
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作为驱动表，那么就需要到t1表中找满足t1.m1 > 1的记录。

image-20230729212921674

针对上一步骤中从驱动表产生的【结果集】中的每一条记录，分别需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为==被驱动表==。

image-20230729213043163

可以理解为将驱动表结果集的每一条记录的值代入连接条件，并以此去过滤被驱动表的记录。这意味着在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

上述介绍的连接无法解决该问题：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。为了解决这个问题，就有了==内连接==和==外连接==的概念：

对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集。

对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：

  • 左外连接

    选取左侧的表为驱动表。

  • 右外连接

    选取右侧的表为驱动表。

需要理解WHERE和ON的区别：

  • WHERE子句中的过滤条件

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

  • ON子句中的过滤条件

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

ON子句是为外连接而提出的。在内连接中，WHERE子句和ON子句是等价的。

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

外连接语法：

1
2
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件]; #左(外)连接
SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件]; #右(外)连接

需要注意的是，对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件。

内连接

内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集。

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

下面这几种内连接的写法都是等价的：

1
2
3
4
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2; # 推荐，语义很明确
SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1, t2; # 这也是内连接

这里需要注意的是，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。左外连接和右外连接的驱动表和被驱动表不能轻易互换。

连接的原理

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

内连接查询的大致过程：

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

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

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

使用索引加快连接速度

无论是过滤条件还是连接条件，都可以使用建好的索引。

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

由于表可能很大，所以在扫描表前面记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前面的记录从内存中释放掉。采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

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

image-20230729215801107

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以最好不要把*作为查询列表。

这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

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