youyichannel

志于道，据于德，依于仁，游于艺！

0%

重拾MySQL —— 连接的原理

发表于 更新于 分类于 ，
本文字数： 4.6k 阅读时长 ≈ 4 分钟

《MySQL是怎样运行的 —— 从跟上理解MySQL》—— 第十一章

一、连接简介

1.1 连接的本质

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

【🌰栗子】

mysql> CREATE TABLE t1 (m1 int, n1 char(1));
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t2 (m2 int, n2 char(1));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

把t1和t2两个表连接起来的过程如下图所示：

上述的结果集即笛卡尔积。

1.2 连接过程

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的记录（此处表的访问方法设定为all）：

步骤2：针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。

因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。上一步骤从驱动表中得到了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时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。

在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

1.3 内连接和外连接

外连接解决的场景问题：针对驱动表中的某条记录，即使在被驱动表中没有找到与之匹配的记录，也仍然需要把该驱动表记录加入到结果集中。

  • 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集。
  • 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：
    • 左外连接：选取左侧的表为驱动表
    • 右外连接：选取右侧的表为驱动表

过滤条件的不同语义：

  • WHERE子句中的过滤条件：不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。
  • ON子句中的过滤条件：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

⚠️注意：ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，对于内连接中的WHERE子句和ON子句是等价的。

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

二、连接的原理

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

t1表和t2表执行内连接查询的大致过程：

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

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

for each row in t1 {   #此处表示遍历满足对t1单表查询结果集中的每一条记录
    for each row in t2 {   #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录
        for each row in t3 {   #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询
            if row satisfies join conditions, send to client
        }
    }
}
# 这个过程就像是一个嵌套的循环

这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join）。

需要注意的是，对于嵌套循环连接算法来说，每当从驱动表中得到一条记录时，就根据这条记录立即到被驱动表中查询一次，如果得到了匹配到记录，就把组合后到记录发送给客户端，然后再到驱动表中获取下一条记录，这个过程将重复进行。并不是将驱动表中所有的记录都先查询出来放到某个地方（内存或者磁盘），然后再遍历这些记录。

2.2 使用索引加快连接速度

在嵌套循环连接的步骤2中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，那就需要扫描多次了。但是，查询t2表其实就相当于一次单表扫描，可以利用索引来加快查询速度。

查询驱动表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列的条件是等值查找，比如t2.m2 = 2、t2.m2 = 3等，所以可能使用到ref的访问方法，假设使用ref的访问方法去执行对t2表的查询的话，需要回表之后再判断t2.n2 < d这个条件是否成立。

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

  • 在n2列上建立索引，涉及到的条件是t2.n2 < 'd'，可能用到range的访问方法，假设使用range的访问方法对t2表的查询的话，需要回表之后再判断在m2列上的条件是否成立。

假设m2和n2列上都存在索引的话，那么就需要从这两个里边儿挑一个代价更低的去执行对t2表的查询。当然，建立了索引不一定使用索引，只有在二级索引 + 回表的代价比全表扫描的代价更低时才会使用索引。

另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null或者range这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是index的访问方法来查询被驱动表。因此建议在真实工作中最好不要使用*作为查询列表，最好把真实用到的列作为查询列表。

2.3 基于块的嵌套循环连接

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。但是表的数据量可能非常的大，内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前面记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前面的记录从内存中释放掉。

采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。

因此可以想到可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

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

最优的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。

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

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

⚠️注意：驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次建议最好不要把*作为查询列表，只需要把关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录。

相关文章