Mysql 表连接的原理

搞后端的肯定要经常接触到数据库,搞数据库一个避免不了的地方就是 ​​join​​​, ​​join​​的语法很简单,但是在使用时常常陷入一下两种误区:

  • 误区一: 业务至上,管他三七二十一,再复杂的查询一个连接语句搞定
  • 误区二: 敬而远之,上次写的慢查询sql就是使用了​​join​​导致的,以后再也不敢用了

先来举个栗子:

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

现在我们对这张表进行连接:

mysql> SELECT * FROM t1, t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | c    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    3 | c    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    |    4 | d    |
|    3 | c    |    4 | d    |
+------+------+------+------+
9 rows in set (0.00 sec)

这个过程看起来就是把​​t1​​​表的记录和​​t2​​​的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为​​笛卡尔积​​​。因为表​​t1​​​中有3条记录，表​​t2​​​中也有3条记录，所以这两个表连接之后的笛卡尔积就有​​3×3=9​​行记录。

连接过程简介

如果我们乐意，我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的​​笛卡尔积​​​可能是非常巨大的。比方说3个100行记录的表连接起来产生的​​笛卡尔积​​​就有​​100×100×100=1000000​​行数据！所以在连接的时候过滤掉特定记录组合是有必要的

下边我们就要看一下携带过滤条件的连接查询的大致执行过程了，比方说下边这个查询语句：

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​​的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询​​t1​​表的访问方法就是全表扫描。
  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次​​t1​​​表，2次​​t2​​​表。当然这是在特定的过滤条件下的结果，如果我们把​​t1.m1 > 1​​​这个条件去掉，那么从​​t1​​​表中查出的记录就有3条，就需要查询3次​​t2​​表了。也就是说在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

内连接与外连接

如果驱动表中的记录即使在被驱动表中没有匹配的记录，但我们也仍然需要加入到结果集。为了解决这个问题，就有了​​内连接​​​和​​外连接​​的概念：

  • 对于​​内连接​​​的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的​​内连接​​。
  • 对于​​外连接​​的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

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

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

where 与 on

可是这样仍然存在问题，即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。这就犯难了，有时候匹配失败要加入结果集，有时候又不要加入结果集，这咋办，有点儿愁啊。。。噫，把过滤条件分为两种不就解决了这个问题了么，所以放在不同地方的过滤条件是有不同语义的：

  • ​​WHERE​​子句中的过滤条件

​​WHERE​​​子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合​​WHERE​​子句中的过滤条件的记录都不会被加入最后的结果集。

  • ​​ON​​子句中的过滤条件

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

  • 需要注意的是，这个​​ON​​​子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把​​ON​​​子句放到内连接中，​​MySQL​​​会把它和​​WHERE​​子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

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

左外连接和右外连接简称左连接和右连接，所以下边提到的左外连接和右外连接中的​​外​​字都用括号扩起来，以表示这个字儿可有可无。

我们前边说过，连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说，由于凡是不符合​​ON​​​子句或​​WHERE​​子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。