MySQL 连接的原理

Posted on In

连接有内连接和外连接之分，而外连接又分为左外连接和右外连接，这些我们都很熟悉。提到连接就不得不提到驱动表和被驱动表的概念，因为是多表联查，肯定需要先查询一张表，然后拿着查询到的记录分别到另一张表中去匹配，这个过程中，首先查询的那张表就是驱动表，而另一张表就是被驱动表。而内连接与外连接的区别，本质就是驱动表中的记录即使在被驱动表中没有与之相匹配的记录时，我们是否仍要将其加入到最终的结果集当中。

对于左外连接来说，驱动表就是连接符号左侧的那张表，而被驱动表就是连接符号右侧的那张表，右外连接与之相反。对于内连接来说，谁是驱动表，谁是被驱动表，并不会对最终结果产生影响。在一般情况下，MySQL 会将数据量比较小的表作为驱动表，而数据量相对较大的表作为被驱动表。至于为什么会这么做，这就涉及到连接的原理了。

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

对于使用连接的两张表来说，驱动表只会访问一遍，而被驱动表却要访问很多遍，具体多少遍取决于对驱动表执行单表查询后结果集的大小，之所以这么说，主要与连接的原理有关。我们以两张表 t1 和 t2 进行内连接查询为例，简单说一下连接的查询过程。

1
SELECT * FROM t1, t2 WHERE t1.f1 > 1 AND t1.f2 = t2.m1 AND t2.m2 < 12;

首先需要选取一个驱动表，我们假设选择 t1 为驱动表，那么就需要使用与驱动表 t1 相关的过滤条件，选取代价最低的单表访问方法（access method）对驱动表进行单表查询，然后每查询到一条匹配的记录，就拿着它到被驱动表中去寻找相匹配的记录。需要强调的是，这种方式中的 MySQL 并不存储驱动表的查询结果，而是查到一条就操作一条。假设结果集中某条记录的 f2 = 2，那么就相当于执行 SELECT * FROM t2 WHERE t2.m1 = 2 AND t2.m2 < 12。整个过程如果用伪代码表示，大概类似这样：

1
2
3
4
5
for each row in t1 {
  for each row in t2 {
    if row match, send to client
  }
}

当然如果有三张表进行连接的话，会将前两张表得到的结果集作为新的驱动表，第三张表作为被驱动表，重复上面的步骤，以此类推，我们可以知道 N 张表进行连接的查询过程。整个过程就像是一个嵌套的循环，这也解释了为什么驱动表只会访问一次，而被驱动表却会访问多次。这种连接执行的查询方式称为嵌套循环连接，这是一种最笨拙但也是最简单的连接查询方式。有人可能会说，被驱动表访问这么多次，会不会影响查询性能啊，与普通的单表查询相比，连接查询肯定是要更慢的，并且这在被驱动表数据量大且没有使用索引的情况下会更加严重。

现在回到一开始我们提出的那个问题，即为什么在进行内连接查询时，MySQL 会选择数据量相对较少的那张表作为驱动表，而选择数据量相对较大的那张表作为被驱动表。原因很简单，前面也说了，连接就是一个嵌套循环，驱动表是外循环，被驱动表是内循环。如果驱动表是那张大表，那么外循环的次数会变多，这也就导致了加载被驱动表数据页的 I/O 次数增多；如果驱动表是小表，那么外循环的次数会变少，加载被驱动表数据页的 I/O 次数也会减少。

我们假设被驱动表在数据量大的时候执行单表查询需要 3 次 I/O，那么可能在数据量较小的时候需要 2 次 I/O，驱动表在数据量较大时的结果集为 100 万条，数据量较小时的结果集为 2 万条，我们可以计算得出：在选择大表为驱动表，小表为被驱动表时的 I/O 次数为 100 w * 2 = 200 w；而选择小表为驱动表，大表为被驱动表时的 I/O 次数为 2 w * 3 = 6 w。

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

嵌套循环的方式虽然简单，但是明显还有很大的优化空间。由于在驱动表中每查询到一条匹配的记录就要再到被驱动表中去查询，所以最终驱动表匹配的记录有多少条，就需要查询几次被驱动表，如果我们将驱动表查询到的结果先存起来，等达到一定的量之后，再一次性地到被驱动表中去查询，这样不就可以减少加载被驱动表的 I/O 次数了吗？（将多条驱动表记录合并在一起作为条件到被驱动表中进行查询，很大概率会出现符合条件的记录在一个数据页中）这就是基于块的嵌套循环连接所采用的思想，它提供了一个称为 join buffer 的缓存，用来存储驱动表查询得到的部分记录，该缓存的大小可以通过参数 join_buffer_size 设置，默认大小为 256 KB。当缓存已满或者是最后一条记录的时候，才一次性地和被驱动表进行匹配，这样就可以显著减少加载被驱动表的 I/O 次数。