MySQL连接查询

本文简单总结一下MySQL中常用的连接查询，以及连接查询的底层原理。

常用的连接查询

连接的本质

我们先建立两张简单的表t1和t2：

CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(2));
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES (2, 'b'), (3, 'c'), (4, 'd');

如上图所示，连接的本质就是：把t1表中的记录和t2表中的记录连起来组成一个新的更大的记录，这个查询过程称之为连接查询。

在MySQL中连接的使用非常简单，把两个表同时放到FROM关键字后面即可。比如，把t1和t2连接起来可以写成这样：

从前文我们可以看出，如果不加条件进行表连接，可能产生非常巨大的笛卡尔乘积。比如，3个100行记录的表连接到一起就是1 000 000行记录！因此在连接时，我们通常需要加过滤条件。过滤条件可以分为两类：

• 涉及单表的过滤条件：比如t1.m1 > 1和t2.n2 < 'd'
• 涉及两表的过滤条件：比如t1.m1 = t2.m2

以select * from t1, t2 where t1.m1 = t2.m2为例，这种带过滤条件的连接查询，执行过程大致如下：

• 步骤1：首先确定第一个需要查询的表，称之为驱动表

  选择最优查询方法，按照过滤条件，执行单表查询语句。

• 步骤2：步骤1从驱动表中每获取到一条记录，都需要到t2表（被驱动表）中查找匹配的记录

整个连接查询的执行过程大致如下：

内连接

内连接，使我们经常使用的一种连接，前文提到的“将所有表添加到FROM关键字后面”就是内连接的一种实现方式。

内连接的特点是：若驱动表中的记录在被驱动表中没有找到与之匹配的记录，则该记录不会加入到最后的结果集。

内连接的语法格式如下：

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

1. INNER和CROSS可有可无，即INNER JOIN == CROSS JOIN == JOIN

2. ON后面添加连接条件，等价于WHERE中添加普通条件，即下面两条语句等价

   SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
   --等价于
   SELECT * FROM t1 INNER JOIN t2 WHERE t1.m1 = t2.m2;

外连接

有了内连接，为什么还有搞个外连接呢？前面提到过“对于内连接，若驱动表中的记录在被驱动表中没有找到与之匹配的记录，则该记录不会加入到最后的结果集”。

但是，有时候我们希望把驱动表中没匹配到的记录页加到结果集中，这时候就需要外连接了。下面举个简单例子：

创建两张表，并填充数据，如下图所示：

现在要查询每位学生的得分信息，使用内连接查询，结果如下图所示：

观察上图结果，我们发现：王五同学因为某些原因没有参加考试，所以score表中没有他的成绩。

如果老师想要查看所有同学的考试成绩，即使是缺考的学生，它们的成绩也应该展现出来。这样内连接就无法完成该任务了，需要使用外连接。

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

• 左外连接：LEFT [OUTER] JOIN，选取左侧的表作为驱动表
• 右外连接：RIGHT [OUTER] JOIN，选取右侧的表作为驱动表

在内连接中，我们提到ON中的连接条件和WHERE中的过滤条件，效果是等价的。但是在外连接中，两者是有区别的：

• WHERE中的过滤条件

  不论是内连接还是外连接，凡是不符合条件的记录都不会加入结果集

• ON中的连接条件

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

为了更直观的对比内、左外、右外三种连接方式，下图展示了将t1和t2以这三种连接方式查询的结果：

其他

前面提到的3种连接方式是最常用的，还有一些不常用的连接：

• 自然连接：一种特殊的等值连接， 要求两个关系表中进行比较的属性组必须是名称相同的属性组，并且在结果中把重复的属性列去掉 。
• 交叉连接： 用于生成两张表的笛卡尔结果集， select * from A , B语句就是返回笛卡尔结果集，等同于 select * from A cross join B 。

连接查询底层原理

嵌套循环连接

前面提到过，对于两表连接查询，驱动表只会访问一遍，但是被驱动表却要访问好多遍，具体访问多少遍取决于驱动表中有多少条符合条件的记录。

查询过程大致为：每从驱动表中找到一条符合条件的记录，就遍历一次被驱动表，查找被驱动表中符合条件的记录。

这个过程称之为“嵌套循环连接”。

使用索引加快连接速度

在嵌套循环连接中可能要多次访问被驱动表。如果访问被驱动表的方式都是全表扫描，那得扫描好多次。

我们可以利用索引来加快查询速度，即在连接条件和过滤条件上建立索引。

这样在扫描被驱动表时，就可以利用索引加快查询速度。

基于块的嵌套循环连接

如果我们不能使用索引加快被驱动表的扫描速度，还有其他方法减少被驱动表全表扫描次数吗？

答案是有的，即基于块的嵌套循环连接。

具体来说，在InnoDB中设计了一个名为Join Buffer的缓冲区，先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表记录一次性地与Join Buffer中的多条驱动表记录进行匹配。这样可以显著减少被驱动表的I/O代价。

大致过程如下图所示：

Join Buffer默认大小为256KB，最小可以设置成128字节。

当我们要优化对被驱动表的查询时，最好是为被驱动表加上高效率索引。如果实在不能使用索引，并且机器内存比较大，则可以尝试调大Join Buffer的大小。

不过需要注意的是，Join Buffer不会存放驱动表记录的所有列，只有查询列表中的列和过滤条件中的列才会被放到Join Buffer中。因此，最好不要把*作为查询列表，这样Join Buffer中可以存放更多的记录。

打赏