连接查询的原理

一、连接简介

先创建两个表，再插入数据：

-- 创建表t1
CREATE TABLE t1 (
	m1 int, 
	n1 char(1)
);
-- 创建表t2
CREATE TABLE t2 (
    m2 int, 
    n2 char(1)
);
-- 插入数据
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

-- 表t1
+------+------+
| m1   | n1   |
+------+------+
| 1    | a    |
| 2    | b    |
| 3    | c    |
+------+------+
-- 表t2
+------+------+
| m2   | n2   |
+------+------+
| 2    | b    |
| 3    | c    |
| 4    | d    |
+------+------+

1.1 连接的本质

连接：就是把各个表的记录都取出来进行一次匹配，并把匹配后的结果组合发送给客户端。

如果把表t1与t2进行连接：

这个过程看起来就是把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'。

执行过程：

• 确定驱动表（第一个需要查询的表）。

选取代价最小的查询方法去执行单表查询（const、ref…）。假设使用t1作为驱动表，那么需要查找满足条件 t1.m1 > 1 的记录。

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

所谓匹配的记录，指的是符合过滤条件的记录，这里指：t1.m1 = t2.m2 AND t2.n2 < 'd'。因为t2表也是根据驱动表的结果来找记录的，所以t2表也被称为被驱动表。查询过程如下：

从上图可以看出，在过滤条件下，这两表连接查询共需要查询1次t1 表，2次t2 表。所以，在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

1.3 内连接与外连接

为了解决驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集的情况，引入了内连接与外连接。

• 内连接：对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集。
• 外连接：对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

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

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

对于外连接来说，有时候并不想把驱动表的全部记录都加入到最后的结果集。可以根据过滤条件分类来实现：

• WHERE 子句中的过滤条件：

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

• ON 子句中的过滤条件：

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

需要注意，内连接中的WHERE子句和ON子句是等价的，ON子句只在外连接下有效。

1.4 连接的语法

• 左外连接

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

• 右外连接

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

• 内连接

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

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];
-- 下面几种写法等价的
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;
-- 也等价于
SELECT * FROM t1, t2;

在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。

对于内连接来说，由于凡是不符合ON 子句或WHERE 子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。

但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON 子句连接条件的记录，也会被加入到结果集，所以此时驱动表和被驱动表的关系就很重要。也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

二、连接的原理

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

前面说过两表内连接查询的过程，

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

如果有3个表进行连接的话，那么步骤2 中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上边过程。

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

2.2 使用索引加快连接速度

最开始介绍的t1 表和t2 表进行内连接的例子：

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

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

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

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

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

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

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

为了减少被驱动表的访问次数，可以尝试一次性让被驱动表与驱动表中的多条记录进行匹配，然后再把被驱动表中的记录加载到内存。完成这个任务的是 Join Buffer（连接缓冲区）。

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

最好的情况是Join Buffer 足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作。这种加入了Join Buffer 的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

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

另外需要注意，驱动表的记录并不是所有列都会被放到Join Buffer 中，只有查询列表中的列和过滤条件中的列才会被放到Join Buffer。

三、总结

从本质上来说，连接就是把整个表中的记录都取出来依次进行匹配，并把匹配后的组合发送给客户端。如果不加任何过滤条件，产生的结果集就是笛卡儿积。

在 MySOL 中，连接分为内连接和外连接，其中外连接又可以被细分为左（外）连接和右（外）连接。内连接和外连接的根本区别就是，在驱动表中的记录不符合ON子句中的连接条件时，内连接不会把该记录加入到最后的结果集中，而外连接会。

嵌套循环连接算法是指驱动表只访问一次，但被驱动表却可能会访问多次，被驱动表访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录。大致过程如下。

• 步骤1．选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。

• 步骤2．对步骤1中查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录。

由于被驱动表可能会访问多次，因此可以为被驱动表建立合适的索引以加快查询速度。

如果被驱动表非常大，多次访问被驱动表可能导致很多次的磁盘I/O，此时可以使用基于块的嵌套循环连接算法来缓解由此造成的性能损耗。