连接的原理(基本概念、内连接与外连接)

为了方便理解先新增几个测试资料

mysql> create table t1 (m1 int, n1 char(1));
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
No connection. Trying to reconnect...
Connection id:    12
Current database: ryan_demo_db

Query OK, 0 rows affected (1.28 sec)

mysql> create table t2 (m2 int, n2 char(1));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(2,'b'),(3,'c'),(4,'d');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

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)

基本概念

select * from t1,t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';

这查询中很明显有三个过滤条件

1. t1.m1 > 1
2. t1.m1 = t2.m2
3. t2.n2 < 'd'

首先第一个需要查询的表叫驱动表，我们以t1开始查询的话，t1就是驱动表。
从此表选取代价最小的存取方法(const、ref、range、index...等)去执行单表查询，再来每从驱动表获取一笔纪录就立即去t2表寻找匹配的纪录(也就是t2.m2=3及t2.m2=4两笔纪录)，因此可知道这两表连接查询共需要查询1次t1表、2次t2表，也就是驱动表只需要存取一次，被驱动表可能需要存取多次。

内连接与外连接

mysql> 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 '学生资讯表';
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> create table score(
    -> number int comment '学号',
    -> subject varchar(30) comment '科目',
    -> score tinyint comment '成绩',
    -> primary key (number, subject)
    -> ) engine=InnoDB charset=utf8 comment '学生成绩表';
Query OK, 0 rows affected, 1 warning (0.02 sec)

新增一些测试资料如下

mysql> select * from student;
+----------+--------+--------+
| number   | name   | major  |
+----------+--------+--------+
| 20210920 | 子瑜   | 射箭   |
| 20210921 | 均甯   | 演戏   |
| 20210922 | 水晶   | 演戏   |
+----------+--------+--------+
3 rows in set (0.05 sec)

mysql> select * from score;
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20210920 | 2016奥运              |    99 |
| 20210920 | 2020奥运              |   100 |
| 20210921 | 我在垦丁天气晴        |    96 |
| 20210921 | 痞子英雄              |    97 |
+----------+-----------------------+-------+
4 rows in set (0.00 sec)

现在要想把女同学们的考试成绩都查询出来，就需要两表连接如下：

mysql> 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 |
+----------+--------+-----------------------+-------+
| 20210920 | 子瑜   | 2016奥运              |    99 |
| 20210920 | 子瑜   | 2020奥运              |   100 |
| 20210921 | 均甯   | 我在垦丁天气晴        |    96 |
| 20210921 | 均甯   | 痞子英雄              |    97 |
+----------+--------+-----------------------+-------+
4 rows in set (0.01 sec)

发现有个问题，由於水晶同学没有考试成绩，所以不会显示。但老师可能想要看到的是所有同学的考试成绩。
因此为了解决这问题出现了内外连接的概念。

• 对於内连接的两个表，若驱动表中的纪录在被驱动表中找不到，则不会加到最终结果，所以前面提到的几个例子就是内连接。
• 对於外连接的两个表，即使驱动表中的纪录在被驱动表中找不到，也仍然需要加到最终结果。外连接有两种：左外连接(选取左侧的表为驱动表)和右外连接(选取右侧的表为驱动表)

但这样有个问题是我们有时候并不希望外连接中驱动表的所有纪录都加到最终结果里。这就困惑了，有时候需要加入结果，有时候又不想加入结果，那要怎麽做呢？
这边就要透过把过滤条件分为两种来实现

• where子句的过滤条件
  不论是内或外连接，不符合过滤条件的纪录都不会被加入到最後的结果集。
• on子句的过滤条件
  对外连接的驱动表，如果无法在被驱动表找到匹配的纪录，仍然会被加到最终结果集，以null填充。

接下来我们直接以前面的例子来看看如果要看到所有同学的考试成绩，该怎麽下语法呢？

mysql> 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 |
+----------+--------+-----------------------+-------+
| 20210920 | 子瑜   | 2016奥运              |    99 |
| 20210920 | 子瑜   | 2020奥运              |   100 |
| 20210921 | 均甯   | 我在垦丁天气晴        |    96 |
| 20210921 | 均甯   | 痞子英雄              |    97 |
| 20210922 | 水晶   | NULL                  |  NULL |
+----------+--------+-----------------------+-------+
5 rows in set (0.01 sec)

这是一个左外连接。left join其实就是left[outer] join，我们通常把outer省略。
放在左边的表就是外表(驱动表)，放在右边的表就是内表(被驱动表)，以上面的语法来看t1就是外表，t2就是内表，对於外连接来说一定要有on来指出连接条件。

内连接的语法：
内连接与外连接的根本区别就是内连接驱动表中的纪录不符合on子句中的条件时，不会把纪录加入到最终结果中。以下都是内连接的写法

• select * from t1 join t2;
• select * from t1 inner join t2;[建议用这个写法，较清楚]
• select * from t1 cross join t2;
• select * from t1,t2;
  这些写法的的结果都是一样的。

直接看下面的例子大家就会清楚许多。

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 inner join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1 right join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+
3 rows in set (0.01 sec)

再来要了解的是一些重要观念：

• 巢状结构回圈连接
  我们已经知道会先选取驱动表，以相关条件过滤後再分别到被驱动表中一笔一笔寻找匹配的纪录，这个过程就像是一个巢状结构的回圈，这是最简单也最慢的连接查询演算法。

• 使用索引加快速度
  尽量不要使用*作为查询列表，有必要且有索引的列再作为查询条件的话可以大幅地加快速度。

• 基於区块的巢状结构回圈连接
  Mysql工程师提出join buffer的概念，先申请一块记忆体，在驱动表的结果纪录储存在里面，一次性的比对被驱动表与其里面的多笔纪录，大幅地加快比对速度。此外由於有专属的记忆体空间也可以避免掉可能的大量I/O（当资料太多，记忆体不足，所以会分次从磁碟上读取资料到记忆体好多次，造成大量I/O）。要特别注意的是join buffer不会存放驱动表纪录的所有列，只有查询列表中的列和过滤条件中的列才会被放进去，所以这也再次提醒我们非必要不要把*作为查询列表，尽量以必要且有索引的列放入就好。
  join buffe的大小可以透过启动选项或系统变数join_buffer_size进行设定，预设为256kb。