Mysql连接查询的原理

在使用Mysql时经常会使用到多表连接查询，虽然在使用时了解其语义，但是不明白各个表中的中记录到底是如何连接起来的。

在开始讲之前，我这里首先构建部分测试数据，SQL如下：

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t_student` (`user_name`) VALUES ('小明');
INSERT INTO `t_student` (`user_name`) VALUES ('小花');
INSERT INTO `t_student` (`user_name`) VALUES ('小黑');

CREATE TABLE `t_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t_score` (`student_id`, `score`) VALUES ('1', '100');
INSERT INTO `t_score` (`student_id`, `score`) VALUES ('2', '90');

这个示例中有两张表，其中t_student代表的是学生表，t_score代表的是学生的成绩表。例如现在我们需要查ID大于100且成绩大于等于60分的记录，其SQL语句如下：

SELECT
    *
FROM t_student st,t_score sc
WHERE st.id < 100 and st.id = sc.student_id AND sc.score >= 60

上面的SQL语句很简单，那么它的过程是怎样的呢？

笛卡尔积

连接的本质就是把各个表中的记录取出来依次匹配组合加入到结果集并返回给用户。例如下面的SQL语句：

select * from t_student,t_score

其连接过程如下图所示：

在上图中一个表中的每一条记录都与另一张表中的每一条记录相互匹配，像这样的结果集就称作为笛卡尔积。

连接过程

通过前面的内容我们知道了什么是笛卡尔积，如果在没有任何限制条件的情况下，t1、t2、t3三个表的记录都是100条，那么最后三个表连接查询最后生成的结果集条数为100*100*100，也就是1000000条，这个结果集是巨大的。所以在实际应用的时候，我们通常会搭配过滤条件使用。例如在文章开头说的那个需求SQL如下：

SELECT
    *
FROM t_student st,t_score sc
WHERE st.id < 100 and st.id = sc.student_id AND sc.score >= 60

其中的筛选条件有三个，分别为st.id < 100、st.id = sc.student_id、sc.score >= 60。对于这些筛选条件可以分成两类：

只与单表相关的。例如st.id < 100和sc.score >= 60
与两个表都有关。例如st.id = sc.student_id

那么整个连接查询大致过程如下：

驱动表查询

连接查询中首先需要确定第一个开始查询的表，这个表就是我们所说的驱动表。在我们的示例中，没有明确地指定驱动表，此刻我们假设t_student为驱动表。那么首先查询出t_student中符合st.id < 100的记录。根据我们的实际情况，t_student中的所有数据都符合，最后一共有三条数据。

被驱动表查询

通过上一步我们已经获得了驱动表的结果集，接着拿驱动表结果集中的每条数据与t_score中的记录做匹配，所谓的匹配就是过滤符合条件的记录，而t_score表我们通常就称为被驱动表。在上一步中我们一共产生了3条数据，所以需要查询3次t_score表。

当st.id=1时，过滤条件st.id = sc.student_id就相当于sc.student_id=1，所以此时表t_score相当于执行了sc.student_id=1 and sc.score >= 60这两个筛选条件。
当st.id=2时，过滤条件st.id = sc.student_id就相当于sc.student_id=2，所以此时表t_score相当于执行了sc.student_id=1 and sc.score >= 60这两个筛选条件。
当st.id=3时，过滤条件st.id = sc.student_id就相当于sc.student_id=1，所以此时表t_score相当于执行了sc.student_id=1 and sc.score >= 60这两个筛选条件。

其整个过程如下图所示：

从上面的步骤可以看出，整个连接查询过程中，查询t_student表也就是驱动表只有1次，而查询t_score表即被驱动表一共有3次。也就是说，在连接查询中驱动表只需要访问一次，而被驱动表需要访问多次，而这个次数跟驱动表的结果集大小有关。

内连接和外连接

现在我们的需求是需要查询出每个学生的考试成绩，我们的SQL如下：

SELECT
    *
FROM t_student st,t_score sc
WHERE st.id = sc.student_id

虽然上面的SQL能查出来结果，但是存在一个小问题。小黑同学可能因为没参加考试所以导致最后结果中小黑这条记录不存在。实际上我们是想看所有同学的考试成绩，即使小黑缺考了也应该出现在最后的结果集中。换而言之就是：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

为了解决这个问题，就有了内连接和外连接的概念。

对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录就不会加入到最后的结果集，我们上面的示例就是内连接。
对于外连接的两个表，驱动表中的记录即使在被驱动表中找不到匹配的记录，最后也应该加入到结果集中。

然后在Mysql中，对于外连接又区分左外连接和右外连接。其中左外连接以左侧表作为驱动表，右外连接则以右表作为驱动表。

那么改用外连接查询其SQL语句如下：

SELECT
    *
FROM t_student st
LEFT JOIN t_score sc ON st.id = sc.student_id

改用左外连接查询之后，即使小黑在t_score表中没有匹配的记录，其值也会加入到最后的结果集中。

where 和 on

where和on均可用于声明条件并实现连接表和筛选数据的功能，很多情况下，它们可以混用。但是实际上它们的用法并不相同，如果混用的话很可能会造成意料之外的结果。

对于内连接而言，where子句和on子句的效果是等价的。例如：

SELECT
    *
FROM
    t_student st
INNER JOIN t_score sc ON st.id = sc.student_id; 

SELECT
    *
FROM
    t_student st
INNER JOIN t_score sc
WHERE
    st.id = sc.student_id;

上面两个SQL最后的效果一样。但是对于外连接来说，如果混用效果就不一样了，例如：

SELECT
    *
FROM
    t_student st
LEFT JOIN t_score sc ON st.id = sc.student_id
WHERE st.id = 1;

SELECT
    *
FROM
    t_student st
LEFT JOIN t_score sc ON st.id = sc.student_id AND st.id = 1;

其最后的结果如下：

+----+-----------+----+------------+-------+
| id | user_name | id | student_id | score |
+----+-----------+----+------------+-------+
|  1 | 小明      |  1 |          1 |   100 |
+----+-----------+----+------------+-------+ 


+----+-----------+------+------------+-------+
| id | user_name | id   | student_id | score |
+----+-----------+------+------------+-------+
|  1 | 小明      |    1 |          1 |   100 |
|  2 | 小花      | NULL | NULL       | NULL  |
|  3 | 小黑      | NULL | NULL       | NULL  |
+----+-----------+------+------------+-------+

从结果中我们可以看出，它们的结果并不一致。对于where子句而言，不论是内连接还是外连接，凡是不符合where子句的过滤条件的记录都不会被加入到最后的结果集。

通常我们只把涉及到单表的过滤条件放在where子句中，把涉及两个表的过滤条件都放到on子句中，而on子句我们称之为连接条件。

内外连接语法

外连接的语法通常如下：

select * from t1 left [outer] join t2 on 连接条件 [where 普通过滤条件];
select * from t1 right [outer] join t2 on 连接条件 [where 普通过滤条件];

对于内连接的语法通常如下：

select * from t1 [inner | cross] join t2 on 连接条件 [where 普通过滤条件];

对于内连接，除了上面的写法，通常还支持下面这种写法：

select * from t1,t2 [where 普通过滤条件]

嵌套循环连接(Nested-Loop Join)

通过前面的介绍，对于两个表的连接来说，驱动表只会查询一次，而被驱动表会查询多次，具体次数由驱动表的结果集条数决定。其整个过程如下：

选取驱动表，使用驱动表相关过滤条件，对驱动表进行单表查询。
对于上一步得到的结果集中每一条记录，都从被驱动表中查找匹配的记录。

这个过程像一个嵌套循环，所以这种驱动表只访问一次，但是被驱动表却可能被多次访问，访问次数由驱动表执行结果集记录条数决定的连接查询执行方法就叫嵌套循环连接(Nested-Loop Join)。

很明显这种方式实现起来很简单，但是其效率也很低。如果驱动表的结果集很大，那么被驱动表的访问次数也会变得很大，可以简单的理解成如下公式：

被驱动表扫描次数 = 驱动表结果集大小

基于块的嵌套循环连接(Block Nested-Loop Join)

对于Mysql而言，如果需要访问数据那就必须先从磁盘中加载数据到内存中才行。如果要扫描一个表中的数据，那么就需要先将表中的数据加载到内存中。而内存的空间有限，在数据量大的情况下并不能一次性将数据全部加载到内存中。如果现在需要对表中的数据进行匹配，那么需要先把表中的一部分数据加载到内存中，然后将这部分数据进行匹配。在匹配结束之后，再次从磁盘中加载数据覆盖之前内存中的数据，再次进行匹配直到所有数据都匹配完毕。

对于嵌套循环连接这种方式，其I/O代价非常大。如果将一个被驱动表加载到内存需要N次I/O，那么使用该方式其I/O次数可以简单的计算为：

I/O次数 = N * 被驱动表扫描次数

如果I/O次数太多，性能自然会受到影响，那么减少I/O次数成了最主要的优化手段了。在硬件不变的情况下将一个表中的数据加载到内存的I/O次数这个通常是固定的，也就是上面的N这个通常不考虑优化，那么被驱动表扫描次数能不能减少呢？

在嵌套循环中我们知道，被驱动表的扫描次数就是驱动表结果集的大小。而驱动表的结果集大小这个是无法改变的，那是不是就无法优化了呢？

答案是否定的。嵌套循环每次只从驱动表的结果集中取一条数据与被驱动表做匹配，所以导致了被驱动表扫描次数就是驱动表结果集的大小。如果我们现在换一种思路，每次我们从驱动表的结果集中取一批记录，然后拿这一批数据与被驱动表中的数据进行匹配。假设设定这一批数据的大小为P，那么可以得到如下公式：

被驱动表扫描次数 = 驱动表结果集大小/P

实际上Mysql的确有这样一种方式来处理，在Mysql中存在join buffer的概念，join buffer就是在执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录加载到该内存，然后开始扫描被驱动表，被驱动表中的每一条记录都和join buffer中的多条记录同时匹配。而这种方式，我们通常称之为Block Nested-Loop Join算法。

从公式我们知道，在驱动表结果集不变的情况下，被驱动表的扫描次数与P成反比，根据这个我们知道join buffer中存放记录的条数越多，那么查询效率越高。默认情况下，join buffer的大小为256KB，如果在内存较大的机器上，该值可以调的更大以提高连接查询效率。

当然驱动表的记录并不是所有列都会放到join buffer中，只有查询列表中的列即select的列和过滤条件中的列才会被放到join buffer中，所有在实际开发中我们最好不要使用select *来偷懒了。

使用索引加快连接速度

在连接查询的第二个步骤需要多次访问被驱动表，如果每次访问被驱动表都是全表扫描的话，这个效率可想而知效率该多低。但是实际上第二布中访问被驱动表，其查询就相当于单表查询，我们可以使用索引来加快查询速度。通常情况下，我们可以在连接条件的字段上做索引，但是实际情况需要实际场景来建立索引，索引不是本文的重点，这里就不专门介绍了。