前语

咱们搞数据库一个都避不开的概念便是衔接（join）。信任许多小伙伴初学衔接的时分有些一脸懵，了解了衔接的语义之后又或许搞不明白各个表中的记载到底是怎么连起来的，以至于在后期运用数据库的时分常常陷入下边两种误区：

  • 误区一：事务至上，不管三七二十一，再复杂的查询也在一个衔接句子中搞定
  • 误区二：敬而远之，慢查询或许便是因为运用了衔接导致的

所以这篇文章咱们就来体系的学习一下衔接的原理。考虑到一部分小伙伴是新手的原因，咱们先来介绍一下 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'), (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两个表衔接起来的进程如下图所示：

一文带你了解MySQL之连接原理
这个进程便是把demo9表的记载和demo10的记载连起来组成新的更大的记载，所以这个查询进程称之为衔接查询。衔接查询的成果会集包括一个表中的每一条记载与另一个表中的每一条记载相互匹配的组合，像这样的成果集就能够称之为笛卡尔积。因为表demo9中有3条记载，表demo10中也有3条记载，所以这两个表衔接之后的笛卡尔积就有33=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条记载的表衔接起来发生的笛卡尔积就有100100100=1000000条数据！所以在衔接的时分过滤掉特定记载组合是有必要的，在衔接查询中的过滤条件能够分成两种：

  • 触及单表的条件 这种只规划单表的过滤条件咱们之前都提到过千万万遍了，咱们之前也一向称为查找条件，比方demo9.m1 > 1是只针对demo9表的过滤条件，demo10.n2 < 'd'是只针对demo10表的过滤条件。

  • 触及两表的条件 这种过滤条件咱们之前没说过，比方demo9.m1 = demo10.m2、demo9.n1 > demo10.n2等，这些条件中触及到了两个表，咱们稍后会仔细分析这种过滤条件是怎么运用的。

下边咱们就要看一下带着过滤条件的衔接查询的大致履行进程了，比方说下边这个查询句子：

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

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

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

那么这个查询的大致履行进程如下：

进程一：

首先确定第一个需求查询的表，这个表称之为驱动表。怎样在单表中履行查询句子咱们在前一篇现已讲过了，MySQL只需求选取价值最小的拜访办法去履行单表查询句子就好了（也便是说从const、ref、ref_or_null、range、index、all这些履行办法中选取价值最小的去履行查询）。此处假定运用demo9作为驱动表，那么就需求到demo9表中找满意demo9.m1>1的记载，因为表中的数据太少，咱们也没在表上树立二级索引，所以此处查询demo9表的拜访办法就设定为all吧，也便是选用全表扫描的办法履行单表查询。关于怎么提高衔接查询的性能咱们之后再说，现在先把基本概念捋清楚。所以查询进程就如下图所示：

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

咱们能够看到，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之连接原理

也便是说整个衔接查询最终的成果只要两条契合过滤条件的记载：

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单词是能够省掉的。关于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)

从成果会集能够看出来，尽管王五并没有对应的成果记载，可是因为选用的是衔接类型为左外衔接，所以依然把她放到了成果会集，只不过在对应的成果记载的各列运用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的三种衔接办法写在一同，这样咱们了解起来就很简略了：

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表履行内衔接查询的大致进程，咱们温习一下：

  • 选取驱动表，运用与驱动表相关的过滤条件，选取价值最低的单表拜访办法来履行对驱动表的单表查询。
  • 对上述进程中查询驱动表得到的成果会集每一条记载，都分别到被驱动表中查找匹配的记载。

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

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

假如有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';

咱们运用的其实是嵌套循坏衔接算法履行的衔接查询，再把上边那个查询履行进程表拉下来给咱们看一下：

一文带你了解MySQL之连接原理
查询驱动表demo9后的成果集有两条记载，嵌套循坏衔接算法需求对被驱动表查询两次：

第一次：

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

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

第2次：

当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、demo10这种只要3条记载，不计其数条记载都是少的，几百万、几千万甚几亿条记载的表到处都是。内存里或许并不能完全寄存的下表中一切的记载，所以在扫描表前边记载的时分后边的记载或许还在磁盘上，等扫描到后边记载的时分或许内存不足，所以需求把前边的记载从内存中释放掉。咱们前边说过，选用嵌套循环衔接算法的两表衔接进程中，被驱动表可是要被拜访许屡次的，假如这个被驱动表中的数据特别多而且不能运用索引进行拜访，那就相当于要从磁盘上读许屡次这个表，这个I/O价值就非常大了，所以咱们得想办法：尽量削减拜访被驱动表的次数。

当被驱动表中的数据非常多时，每次拜访被驱动表，被驱动表的记载会被加载到内存中，在内存中的每一条记载只会和驱动表成果集的一条记载做匹配，之后就会被从内存中清除去。然后再从驱动表成果会集拿出另一条记载，再一次把被驱动表的记载加载到内存中一遍，周而复始，驱动表成果会集有多少条记载，就得把被驱动表从磁盘上加载到内存中多少次。所以咱们可不能够在把被驱动表的记载加载到内存的时分，一次性和多条驱动表中的记载做匹配，这样就能够大大削减重复从磁盘上加载被驱动表的价值了。所以MySQL的提出了一个join buffer的概念，join buffer便是履行衔接查询前申请的一块固定巨细的内存，先把若干条驱动表成果会集的记载装在这个join buffer中，然后开端扫描被驱动表，每一条被驱动表的记载一次性和join buffer中的多条驱动表记载做匹配，因为匹配的进程都是在内存中完结的，所以这样能够明显削减被驱动表的I/O价值。运用join buffer的进程如下图所示：

一文带你了解MySQL之连接原理
最好的状况是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子句中的条件的记载都会被过滤掉，其实也就相当于从两表衔接的笛卡尔积中把不契合过滤条件的记载给踢出去，所以关于内衔接来说，驱动表和被驱动表是能够交换，并不会影响最终的查询成果。

至此今日的学习就到此结束了，愿您成为坚不可摧的自己~~~ ​

You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something – your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life

假如我的内容对你有帮助，请 点赞、谈论、保藏，创作不易，咱们的支撑便是我坚持下去的动力！

本文章参阅：小孩子《MySQL是怎样运转的》

作者选用诙谐诙谐的表达办法，对MySQL的底层运转原理进行了介绍，内容涵盖了运用MySQL的同学在求职面试和工作中常见的一些核心概念。尽管作者在写作时参阅的MySQL源代码版本是5.7.22，可是大部分内容与具体的版本号并没有多大联系。无论是很早之前就已身居MySQL专家的人员，仍是希望进一步提高技术的DBA，甚至是三五年后才会入行的萌新，本书都是他们完全了解MySQL运转原理的优异图书。

最终推荐对 MySQL 有兴趣的同学 点击此处 能够买来翻翻看，收成肯定是有的。

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