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'

那末这个连接查询的大致履行进程以下：

首先肯定第一个需要查询的表，这个表称之为驱动表。只需要选取代价最小的那种访问方法去履行单表查询语句就行了。此处假定使用t1作为驱动表，那末就需要到t1表中找满足t1.m1 > 1的记录，由于表中的数据太少，我们也没在表上建立二级索引，所以此处查询t1表的访问方法就是全表扫描。
针对上一步骤中从驱动表产生的结果集中的每条记录，分别需要到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子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

总结

到此这篇关于Mysql表连接的误区与原理的文章就介绍到这了,更多相关Mysql表连接原理内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！

本文来源：https://www.yuntue.com/post/151078.html | 云服务器网，转载请注明出处！

继续浏览有关： mysql 技术文档数据库的文章

Mysql表连接的误区与原理详析