MySQL 表连接原理

表连接介绍

表连接也就是我们常说的 JOIN，连接的结果是两张表中符合条件的记录的笛卡尔积，即一张表中符合条件的每一条记录和另一张中符合条件的每一条记录相互匹配的组合。假设有两张表，分别是表 A 和表 B，其中分别有 3 条和 4 条符合连接条件的记录，则这两张表连接的笛卡尔积如下：

对应的 SQL 为：`SELECT * FROM A, B;`

表连接的查询条件

多表连接的笛卡尔积容易变得非常大，如连接 3 张 1000 条记录的表的笛卡尔积为：1000*1000*1000=10亿，因此通常我们的查询都会加上查询条件。

在连接查询中，根据涉及的表数量，查询条件可以分成两种：

只涉及单表的查询条件，如 A.c1 > 10
涉及两张表的查询条件，如 A.c1 = B.c2 或 A.c1 > B.c2

表连接的查询过程

以这个查询为例：

SELECT * FROM A, B WHERE A.c1 > 1 AND A.c1 = B.d1 AND B.d2 < 10;

这个连接查询的大致执行过程如下:

确定驱动表，即第一个查询的表。假设 A 是驱动表，则执行从 A 中找 c1 > 1 的所有记录的单表查询，假设找到了两条记录：A.c1 = 2 和 A.c1 = 3 的记录
针对从驱动表查询到的结果集中的每一条记录，分别到另一张表 B 中查找匹配的记录（这里表 B 是被驱动表），需要查询两次表 B：
1. A.c1 = 2：则 A.c1 = B.d1 转化为 B.d1 = 2，接着拿着 B.d1 = 2 AND B.d2 < 10 的查询条件在表 B 执行单表查询，得到结果集 R1
2. A.c1 = 3：则 A.c1 = B.d1 转化为 B.d1 = 3，接着拿着 B.d1 = 3 AND B.d2 < 10 的查询条件在表 B 执行单表查询，得到结果集 R2
合并结果集 R1 和 R2，返回给客户端

可以看出，在两表连接查询中，驱动表只查询一次，被驱动表可能会查询多次。

表连接分类

我们现创建一张测试表：

-- 创建测试表
CREATE TABLE student (   number INT NOT NULL AUTO_INCREMENT COMMENT '学号', 
  name VARCHAR(5) COMMENT '姓名',   major VARCHAR(30) COMMENT '专业',   PRIMARY KEY (number) 
) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表'; 
CREATE TABLE score (   number INT COMMENT '学号',   subject VARCHAR(30) COMMENT '科目', 
  score TINYINT COMMENT '成绩', 
  PRIMARY KEY (number, score) 
) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表'; 
-- 插入测试数据
insert into student values(150146117, '张三', '计算机科学与技术'), (150146118, '李四', '土木工程'), (150146119, '王五', '飞行器制造工程');
insert into score values(150146117, '数学', 60), (150146117, '英语', 88), (150146118, '数学', 98), (150146118, '英语', 100);

执行一个查询学生成绩的 SQL：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score AS s2 WHERE s1.number = s2.number;

结果如下：

+-----------+--------+---------+-------+
| number    | name   | subject | score |
+-----------+--------+---------+-------+
| 150146117 | 张三    | 数学    |    60 |
| 150146117 | 张三    | 英语    |    88 |
| 150146118 | 李四    | 数学    |    98 |
| 150146118 | 李四    | 英语    |   100 |
+-----------+--------+---------+-------+

可以看到王五并没有出现在结果中，这是因为驱动表 student 中的王五这条记录在被驱动表 score 中没有找到匹配的记录。

但是有时候我们又想要：即使驱动表中的记录在被驱动表中没有匹配的记录，也仍然要加入到结果集。为此，表连接有了内连接（Inner Join）和外连接（Outer Join）的概念：

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

根据选取的驱动表的不同，外连接又可以分为左外连接（Left Outer Join）和右外连接（Right Outer Join）：

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

外连接专门引入了一个 ON 关键字，用于在执行连接操作时指定两个表之间的匹配条件，即定义了表连接的条件。不同于 WHERE 主要用于过滤结果集，即从连接后的结果中筛选符合条件的记录。

下面我们创建一张表来介绍下几种连接方式：

内连接

我们上面接触的就是一种内连接查询方式，其语法如下：

SELECT * FROM t1, t2 [WHERE 连接条件];

但是我们更推荐使用下面更现代的内连接方式，其语法如下：

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

内连接的例子：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 INNER JOIN score AS s2 ON s1.number = s2.number;

结果如下：

+-----------+--------+---------+-------+
| number    | name   | subject | score |
+-----------+--------+---------+-------+
| 150146117 | 张三   | 数学     |    60 |
| 150146117 | 张三   | 英语     |    88 |
| 150146118 | 李四   | 数学     |    98 |
| 150146118 | 李四   | 英语     |   100 |
+-----------+--------+---------+-------+

左外连接

左外连接也叫左连接，其语法如下：

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

对于 LEFT JOIN 来说，我们把左边的表称为外表或者驱动表，右边的表称为内表或者被驱动表。

左连接的例子：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number;

结果如下：

+-----------+--------+---------+-------+
| number    | name   | subject | score |
+-----------+--------+---------+-------+
| 150146117 | 张三   | 数学     |    60 |
| 150146117 | 张三   | 英语     |    88 |
| 150146118 | 李四   | 数学     |    98 |
| 150146118 | 李四   | 英语     |   100 |
| 150146119 | 王五   | NULL    |  NULL |
+-----------+--------+---------+-------+

右外连接

右外连接也叫右连接，其语法如下：

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

对于 LEFT JOIN 来说，我们把右边的表称为外表或者驱动表，左边的表称为内表或者被驱动表。

右连接的例子：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 RIGHT JOIN score AS s2 ON s1.number = s2.number;

结果如下：

+-----------+--------+---------+-------+
| number    | name   | subject | score |
+-----------+--------+---------+-------+
| 150146117 | 张三    | 数学    |    60 |
| 150146117 | 张三    | 英语    |    88 |
| 150146118 | 李四    | 数学    |    98 |
| 150146118 | 李四    | 英语    |   100 |
+-----------+--------+---------+-------+

表连接原理

上面我们回顾了表连接的基本概念，下面介绍下 MySQL 采用了什么算法来进行表与表之间的连接。

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

Nested-Loop Join 是一种非常简单的连接算法，其基本思想是：

遍历驱动表：首先，扫描驱动表的每一行。
遍历被驱动表：对驱动表的每一行，遍历被驱动表的每一行。
比较条件：检查驱动表和被驱动表的每一行是否满足连接条件，如果满足，就把它们组合成一行输出结果。

使用索引加快连接速度

我们继续用上面的例子：

SELECT * FROM A, B WHERE A.c1 > 1 AND A.c1 = B.d1 AND B.d2 < 10;

我们知道在嵌套循环连接过程中，要查询多次被驱动表，每一次都是一次单表查询，因此非常有必要优化被驱动表单表查询的速度。上面我们已经知道查询驱动表的结果集共有 2 条记录：

当 A.c1 = 2 时，被驱动表相当于查询：`SELECT * FROM B WHERE d1 = 2 AND d2 < 10;`
当 A.c1 = 3 时，被驱动表相当于查询：`SELECT * FROM B WHERE d1 = 3 AND d2 < 10;`

可以看到，被驱动表查询时涉及 d1 和 d2 这两列，我们可以：

(1) 在 d1 列建立索引

此时 d1 = 2 和 d1 = 3 可能使用 ref 访问方法，回表后再过滤 d2 < 10 的记录。但是有个例外情况，如果 d1 是主键列或者唯一索引列，则d1 = 2 和 d1 = 3 可能使用 eq_ref 访问方法。在连接查询中，MySQL 把对被驱动表使用主键或者唯一索引进行等值查询的查询执行方式称为 eq_ref。

(2) 在 d2 列建立索引

此时 d1 < 10 可能使用 range 访问方法，回表后再过滤 d1 = 2 或 d1 = 3 等记录。

如果 d1 和 d2 列上都存在索引的话，那么就可能选一个执行成本更低的索引去执行对被驱动表的查询，当然也可能使用全表扫描。

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

扫描一个表其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。生产环境中的表通常有几万、几十万甚至几百万条记录，内存里很可能无法完全存放被驱动表中的所有记录，因此 MySQL 引入了Block Nested-Loop Join 连接算法，他是对普通 Nested-Loop Join 的一种改进，可以大大减少被驱动表的 IO 代价，其基本思想是：

将驱动表分块：将驱动表的数据划分为多个块（块数据存储在 join buffer 中），一次只处理一个块。
遍历块：对于每个块，扫描整个被驱动表。
比较条件：在扫描被驱动表时，只需要对当前块的数据进行连接操作，而不是对整个驱动表的数据进行操作。

join buffer 默认大小为 256KB，内存比较大的机器可以适当调大 join_buffer_size 的值来提高连接查询的效率。

MySQL

License: CC BY 4.0