一文带你了解MySQL之连接原理

目录

• 一、连接简介
• 1.1 连接的本质
• 1.2 连接过程简介
• 1.3 内连接和外连接
• 1.4 左外连接
• 1.5 右外连接
• 1.6 内连接
• 小结
• 二、连接的原理
• 2.1 嵌套循环连接（Nested-Loop Join）
• 2.2 使用索引加快连接速度
• 2.3 基于块的嵌套循环连接（block Nested-Loop Join）
• 总结

搞数据库一个避不开的概念就是Join，翻译成中⽂就是连接。相信很多小伙伴初学连接的时候有些一脸懵，理解了连接的语义之后又可能不明白各个表中的记录到底是怎么连起来的，以至于在使用的时候常常陷入下边两种误区：

误区一：业务至上，管他三七二十一，再复杂的查询也用在一个连接语句中搞定误区二：敬而远之，慢查询可能就是因为使用了连接导致的，以后再也不敢乱用了

所以本章就来学习连接的原理。考虑到一部分小伙伴可能忘了连接是个啥或者压javascript根就不知道，为了节省他们百度或者看其他书的宝贵时间，我们先来介绍一下 mysql 中支持的一些连接语法。

一、连接简介

1.1 连接的本质

为了学习，我们创建两个简单的表，并给它们插入一些数据：

mysql> create table demo9 (m1 int, n1 char(1));
Query OK, 0 rows affected (0.01 sec)

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

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

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

我们成功创建了demo9、demo10两个表，这两个表都有两个列，一个是int类型的，一个是char(1)类型的，填充好数据的两个表是这样：

mysql> select * from demo9;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from demo10;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加⼊结果集并返回给用户。所以我们把demo9和demo10两个表连接起来的过程如下图所示：

这个过程看起来就是把demo9表的记录和demo10的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。因为表demo9中有3条记录，表demo10中也有3条记录，所以这两个表连接之后的笛卡尔积就有3×3=9条记录。在MySQL中，连接查询的语法也很随意，只要在from语句后边跟多个表名就好了，比如我们把demo9表和demo10表连接起来的查询语句可以写成这样：

mysql> select * from demo9,demo10;
+------+------+------+------+
| 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)

1.2 连接过程简介

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

• 涉及单表的条件

这种只设计单表的过滤条件我们之前都提到过千万万遍了，我们之前也一直称为搜索条件，比如demo9.m1 > 1是只针对t1表的过滤条件，demo10.n2 < 'd’是只针对t2表

的过滤条件。

• 涉及两表的条件

这种过滤条件我们之前没说过，比如demo9.m1 = demo10.m2、demo9.n1 > demo10.n2等，这些条件中涉及到了两个表，我们稍后会仔细分析这种过滤条件是如何使用的。

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

mysql> select * from demo9, demo10 where demo9.m1 > 1 and demo9.m1 = demo10.m2 and demo10.n2www.devze.com < 'd';

在这个查询中，我们指明了这三个过滤条件：

• demo9.m1 > 1
• demo9.m1 = demo10.m2
• demo10.n2 < ‘d’

那么这个查询的大致执行过程如下：

步骤一：

首先确定第一个需要查询的表，这个表称之为驱动表。怎样在单表中执行查询语句我们在前一章都讲过了，只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是说从const、ref、ref_or_null、range、index、all这些执行方法中选取代价最小的去执行查询）。此处假设使用demo9作为驱动表，那么就需要到demo9表中找满足demo9.m1>1的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询demo9表的访问方法就设定为all吧，也就是采用全表扫描的方式执行单表查询。关于如何提升连接查询的性能我们之后再说，现在先把基本概念捋清楚。所以查询过程就如下图所示：

我们可以看到，demo9表中符合demo9.m1 > 1的记录有两条。

步骤二：

针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到demo10表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据demo9表中的记录去找demo10表中的记录，所以demo10表也可以被称之为被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次demo10表。此时涉及两个表的列的过滤条件demo9.m1=demo10.m2就派上用场了：

• 当demo9.m1 = 2时，过滤条件demo9.m1 = demo10.m2就相当于demo10.m2 = 2，所以此时demo10表相当于有了demo10.m2 = 2、demo10.n2 < 'd’这两个过滤条件，然后到demo10表中执行单表查询
• 当demo9.m1 = 3时，过滤条件demo9.m1 = demo10.m2就相当于demo10.m2 = 3，所以此时demo10表相当于有了demo10.m2 = 3、demo10.n2<'d’这两个过滤条件，然后到demo10表中执行单表查询

所以整个连接查询的执行过程就如下图所示：

也就是说整个连接查询最后的结果只有两条符合过滤条件的记录：

mysql> select * from demo9, demo10 where demo9.m1 > 1 and demo9.m1 = demo10.m2 and demo10.n2 < 'd';
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

从上边两个步骤可以看出来，我们上边的这个两表连接查询共需要查询1次demo9表，2次demo10表。当然这是在特定的过滤条件下的结果，如果我们把demo9.m1 > 1这个条件去掉，那么从demo9表中查出的记录就有3条，就需要查询3次demo10表了。也就是说在两表连接查询中，驱动表只需要访问⼀次，被驱动表可能被访问多次。

1.3 内连接和外连接

为了更好的学习后边的内容，我们先创建两个有现实意义的表：

mysql> create table student (
    number int not null auto_increment comment '学号',
    name varchar(5) comment '姓名',
    major varchar(30) comment '专业',
    primary key (number)
) comment '学生信息表';
Query OK, 0 rows affected (0.02 sec)

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

mysql> insert into student values(1,'张三','软件学院'),(2,'李四','计算机科学与工程'),(3,'王五','计算机科学与工程');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score values(1,'MySQL是怎样运行的',78),(1,'MySQL实战45讲',88),(2,'MySQL是怎样运行的',78),(2,'MySQL实战45讲',100);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

我们新建了一个学⽣信息表，一个学生成绩表，然后我们向上述两个表中插入一些数据，插入后两表中的数据如下：

mysql> select * from student;
+--------+--------+--------------------------+
| number | name   | major                    |
+--------+--------+--------------------------+
|      1 | 张三   | 软件学院                 |
|      2 | 李四   | 计算机科学与工程         |
|      3 | 王五   | 计算机科学与工程         |
+--------+--------+--------------------------+
3 rows in set (0.00 sec)

mysql> select * from score;
+--------+-------------------------+-------+
| number | subject                 | score |
+--------+-------------------------+-------+
|      1 | MySQL是怎样运行的       |    78 |
|      1 | MySQL实战45讲           |    88 |
|      2 | MySQL是怎样运行的       |    98 |
|      2 | MySQL实战45讲           |   100 |
+--------+-------------------------+-------+
4 rows in set (0.00 sec)

现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了（因为score中没有姓名信息，所以不能单纯只查询score表）。连接过程就是从student表中取出记录，在score表中查找number相同的成绩记录，所以过滤条件就是student.number =socre.number，整个查询语句就是这样：

mysql> select * from student,score where student.number=score.number;
+--------+--------+--------------------------+--------+-------------------------+-------+
| number | name   | major                    | number | subject                 | score |
+--------+--------+--------------------------+--------+-------------------------+-------+
|      1 | 张三   | 软件学院                 |      1 | MySQL是怎样运行的       |    78 |
|      1 | 张三   | 软件学院                 |      1 | MySQL实战45讲           |    88 |
|      2 | 李四   | 计算机科学与工程         |      2 | MySQL是怎样运行的       |    98 |
|      2 | 李四   | 计算机科学与工程         |      2 | MySQL实战45讲           |   100 |
+--------+--------+--------------------------+--------+-------------------------+-------+
4 rows in set (0.00 sec)

字段有点多，我们可以少查询几个字段：

mysql> select s1.number,s1.name,s2.subject,s2.score from student s1 ,score s2  where s1.number=s2.number;
+--------+--------+-------------------------+-------+
| number | name   | subject                 | score |
+--------+--------+-------------------------+-------+
|      1 | 张三   | MySQL是怎样运行的       |    78 |
|      1 | 张三   | MySQL实战45讲           |    88 |
|      2 | 李四   | MySQL是怎样运行的       |    98 |
|      2 | 李四   | MySQL实战45讲           |   100 |
+--------+--------+-------------------------+-------+
4 rows in set (0.00 sec)

从上述查询结果中我们可以看到，各个同学对应的各科成绩就都被查出来了，可是有个问题，王五同学，也就是学号为3的同学因为某些原因没有参加考试，所以在score表中没有对应的成绩记录。那如果老师想查看所有同学的考试成绩，即使是缺考的同学也应该展示出来，但是到目前为止我们介绍的连接查询是无法完成这样的需求的。我们稍微思考一下这个需求，其本质是想：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。为了解决这个问题，就有了内连接和外连接的概念：

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

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

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

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

• where子句中的过滤条件：where子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合where子句中的过滤条件的记录都不会被加入最后的结果集。
• ON子句中的过滤条件：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

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

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

小提示：

左外连接和右外连接简称左连接和右连接。

1.4 左外连接

左外连接的语法还是挺简单的，比如我们要把demo9和demo10两个表进行左外连接，可以这么写：

select * from demo9 left [outer] join demo10 on 连接条件 [where 普通过滤条件]

其中中括号里的outer单词是可以省略的。对xwrGoqs于left join类型的连接来说，我们把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。所以上述例子中demo9就是外表或者驱动表，demo10就是内表或者被驱动表。需要注意的是，对于左外连接和右外连接来说，必须使用on子句来指出连接条件。了解了左外连接的基本语法之后，再次回到我们上边那个现实问题中来，看看怎样写查询语句才能把所有的学生的成绩信息都查询出来，即使是缺考的考生也应该被放到结果集中：

mysql> select s1.number,s1.name,s2.subject,s2.score from student s1 left join score s2 on s1.number=s2.number;
+--------+--------+-------------------------+-------+
| number | name   | subject                 | score |
+--------+--------+-------------------------+-------+
|      1 | 张三   | MySQL是怎样运行的       |    78 |
|      1 | 张三   | MySQL实战45讲           |    88 |
|      2 | 李四   | MySQL是怎样运行的       |    98 |
|      2 | 李四   | MySQL实战45讲           |   100 |
|      3 | 王五   | NULL                    |  NULL |
+--------+--------+-------------------------+-------+
5 rows in set (0.01 sec)

从结果http://www.devze.com集中可以看出来，虽然王五并没有对应的成绩记录，但是由于采用的是连接类型为左外连接，所以仍然把她放到了结果集中，只不过在对应的成绩记录的各列使用null值填充而已。

1.5 右外连接

右外连接和左外连接的原理是一样一样的，语法也只是把left换成right而已：

select * from demo9 right [outer] join demo10 on 连接条件 [where 普通过滤条件]

只不过驱动表是右边的表，被驱动表是左边的表，具体就不唠叨了。

1.6 内连接

内连接和外连接的根本区别就是在驱动表中的记录不符合on子句中的连接条件时不会把该记录加入到最后的结果集，我们最开始唠叨的那些连接查询的类型都是内连接。不过之前仅仅提到了一种最简单的内连接语法，就是直接把需要连接的多个表都放到from子句后边。其实针对内连接，mysql提供了好多不同的语法，我们以demo9和demo10表为例瞅瞅：

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

也就是说在mysql中，下边这⼏种内连接的写法都是等价的：

select * from demo9 join demo10;
select * from demo9 inner join demo10;
select * from demo9 cross join demo10;

上边的这些写法和直接把需要连接的表名放到from语句之后，用逗号,分隔开的写法是等价的：

select * from demo9,demo10;

现在我们虽然介绍了很多种内连接的书写方式，不过熟悉一种就好了，这里我们推荐inner join的形式书写内连接（因为inner join语义很明确嘛，可以和left join和right join很轻松的区分开）。这里需要注意的是，由于在内连接中on子句和where子句是等价的，所以内连接中不要求强制写明on子句。

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

小结

上边说了很多，给大家的感觉不是很直观，我们直接把表demo9和demo10的三种连接方式写在一起，这样大家理解起来就很easy了：

mysql> select * from demo9 inner join demo10 on demo9.m1 = demo10.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows inset (0.00 sec)

mysql> select * from demo9 left join demo10 on demo9.m1 = demo10.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | null | null |
+------+------+------+------+
3 rows inset (0.00 sec)

mysql> select * from demo9 right join demo10 on demo9.m1 = demo10.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| null | null |    4 | d    |
+------+------+------+------+
3 rows inset (0.00 sec)

二、连接的原理

上边的介绍都只是为了唤醒大家对连接、内连接、外连接这些概念的记忆，这些基本概念是为了真正进入本章主题做的铺垫。真正的重点是MySQL采用了什么样的算法来进行表与表之的连接，了解了这个之后，大家才能明白为啥有的连接查询运行的快如闪电，有的却慢如蜗牛。

2.1 嵌套循环连接（Nested-Loop Join）

我们前边说过，对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。我们上边已经大致介绍过demo9表和demo10表执行内连接查询的大致过程，我们温习一下：

• 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
• 对上述步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

通过的两表连接过程如下图所示：

如果有3个表进行连接的话，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上边过程，也就是步骤2中得到的结果集中的每一条记录都需要到demo11表中找一找有没有匹配的记录，用伪代码表示一下这个过程就是这样：

for each row in demo9 {   #此处表示遍历满足对demo9单表查询结果集中的每一条记录
    for each row in demo10 {   #此处表示对于某条demo9表的记录来说，遍历满足对demo10单表查询结果集中的每一条记
        for each row in demo11 {   #此处表示对于某条demo9和demo10表的记录组合来说，对demo11表进行单表查询
            if row satisfies join conditions, send to client
        }
    }
}

这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果中的记录条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join），这是最简单，也是最笨拙的一种连接查询算法。

2.2 使用索引加快连接速度

我们知道在嵌套循环连接的步骤2中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，那得要扫描好多次呀～但是别忘了，查询demo10表其实就相当于一次单表扫描，我们可以利用索引来加快查询速度。回顾一下最开始介绍的demo9表和demo10表进行内连接的例子：

mysql> select * from demo9, demo10 where demo9.m1 > 1 and demo9.m1 = demo10.m2 and demo10.n2 < 'd';

我们使用的其实是嵌套循坏连接算法执行的连接查询，再把上边那个查询执行过程表拉下来给大家看一下：

查询驱动表demo9后的结果集有两条记录，嵌套循坏连接算法需要对被驱动表查询两次：

第一次：

当demo9.m1 = 2时，去查询一遍demo10表，对demo10查询的语句相当于：

select * from demo10 where demo10.m2 = 2 and demo10.m2 < 'd';

第二次：

当demo9.m1 =3时，去查询一遍demo10表，对demo10查询的语句相当于：

select * from demo10 where demo10.m2 = 3 and demo10.m2 < 'd';

可以看到，原来的demo9.m1 = demo10.m2这个涉及两个表的过滤条件在针对demo10表做查询时关于demo9表的条件就已经确定了，所以我们只需要单单优化对demo10表的查询了，上述两个对demo10表的查询语句中利用到的列是m2和n2列，我们可以：

• 在m2列上建立索引，因为对m2列的条件是等值查找，比如demo10.m2 = 2、demo10.m2 = 3等，所以可能使用到ref的访问方法，假设使用ref的访问方法去执行对demo10表的查询的话，需要回表之后再判断demo10.n2 < d这个条件是否成立。
• 这里有一个比较特殊的情况，就是假设m2列是demo10表的主键或者唯一二级索引列，那么使用demo10.m2 = 常数值这样的条件从demo10表中查找记录的过程的代价就是常数级别的。我们知道在单表中使用主键值或者唯一二级索引列的值进行等值查找的方式称之为const，MySQL把在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref。
• 在n2列上建立索引，涉及到的条件是demo10.n2 < ‘d’，可能用到range的访问方法，假设使用range的访问方法对demo10表的查询的话，需要回表之后再判断在m2

列上的条件是否成立。

假设m2和n2列上都存在索引的话，那么就需要从这两个里边挑一个代价更低的去执行对demo10表的查询。当然，建立了索引不一定使用索引，只有在二级索引 +回表的代价比全表扫描的代价更低时才会使用索引。

另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null或者range这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是index的访问方法来查询被驱动表。所以我们建议在真实工作中最好不要使用*作为查询列表，最好把真实用到的列作为查询列表。

2.3 基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。现实生活中的表可不像demo9、demo11这种只有3条记录，成千上万条记录都是少的，几百万、几千万甚几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边说过，采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好多次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据⾮常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。所以MySQL的提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。使用join buffer的过程如下图所示：

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。MySQL把这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进⾏配置，默大小为262144字节（也就是256KB），最小可以设置为128字节。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

mysql> set persist join_buffer_size=524288;
Query OK, 0 rows affected (0.01 sec)

小提示：

不建议在系统级别对该值设置过大，一般可以设置512K以内，因为最终解决方案还是要依靠索引来解决，当然不排除有时候两个表关联，的确是没有索引可用

另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录。

总结

今天我们学习了有关连接的知识。知道了连接的本质、连接的过程、内连接、外连接的使用方法及连接的原理。在原始NLJ算法的基础上，MySQL又设计出了更优BNL算法，被驱动表我们可以通过添加关联字段索引的方式来提高查询效率，如果实在不能使用索引的情况，可以尝试调大Join Buffer的值（join_buffer_size）。在使用内连接时，需要注意：

• on子句和where子句是等价的，所以内连接中不要求强制写明ON子句
• 对于内连接来说，由于凡是不符合on子句或where子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换，并不会影响最后的查询结果。

以上就是一文带你了解MySQL之连接原理的详细内容，更多关于MySQL 连接原理的资料请关注我们其它相关文章！