MySQL 的底层执行原理之索引合并与连接查询

内容概述

MySQL 的底层执行原理之索引合并与连接查询
- [1] 单表访问之索引合并
- [2] 连接查询的原理
  - [2.1] 连接查询简介
  - [2.2] MySQL 对连接查询的执行

# [1] 单表访问之索引合并

我们知道MySQL 在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二级索引， MySQL 中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并(Index merge)，具体的索引合并算法有下边三种。

# [1.1] Intersection 合并

Intersection 中文为交集的意思。因此，Intersection 合并是指某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集。
例如：

SELECT *
FROM order_exp
WHERE order_no = 'a'
  AND expire_time = 'b';

1
2
3
4

假设这个查询使用Intersection 合并的方式执行，则整个过成如下：

从idx_order_no 二级索引对应的B+树中取出order_no= 'a'的相关记录；
从idx_insert_time 二级索引对应的B+树中取出insert_time= 'b'的相关记录；
由二级索引的记录都是由索引列+主键构成的，所以我们可以计算出这两个结果集中id 值的交集；
然后按照上一步生成的id 值列表进行回表操作，也就是从聚簇索引中把指定id 值的完整用户记录取出来，返回给用户。

思考

我们思考一个问题：为啥不直接使用idx_order_no 或者idx_insert_time 只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？

下面我们来分析一下两种查询执行方式之间需要的成本代价。

只读取一个二级索引的成本：
按照某个搜索条件读取一个二级索引，根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件。
读取多个二级索引之后取交集成本：
按照不同的搜索条件分别读取不同的二级索引，将从多个二级索引得到的主键值取交集，然后进行回表操作。

一般地，读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

# Intersection 索引合并的使用

MySQL 在某些特定的情况下才可能会使用到Intersection 索引合并，哪些情况呢？

情况一：等值匹配

二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

以下情况的查询不支持Intersection 索引合并：

查询条件中包含范围查询不能使用：

SELECT *
FROM order_exp
WHERE order_no > 'a'
  AND insert_time = 'a'
  AND order_status = 'b'
  AND expire_time = 'c';

1
2
3
4
5
6

原因：order_no 进行了范围匹配。

如果查询条件中有联合索引，则联合索引不全时不能使用：

SELECT *
FROM order_exp
WHERE order_no = 'a'
  AND insert_time = 'a';

1
2
3
4

前提：u_idx_day_status联合索引包含字段insert_time、expire_time、order_status；原因：联合索引u_idx_day_status 中的order_status 和expire_time 列并没有出现在搜索条件中。

情况二：主键列可以是范围匹配

# [1.2] Union 合并

Intersection 是交集的意思，这适用于使用不同索引的搜索条件之间使用AND 连接起来的情况；

Union 是并集的意思，适用于使用不同索引的搜索条件之间使用OR 连接起来的情况。

与Intersection 索引合并类似，MySQL 在某些特定的情况下才可能会使用到Union 索引合并。

# Union 合并的使用

情况一：等值匹配

分析同Intersection 合并

情况二：主键列可以是范围匹配

分析同Intersection 合并

情况三：使用Intersection 索引合并的搜索条件

简而言之，就是搜索条件的某些部分使用Intersection 索引合并的方式得到的主键集合和其他方式得到的主键集合取交集。
例如：

SELECT *
FROM order_exp
WHERE insert_time = 'a' AND order_status = 'b' AND
      expire_time = 'c'
   OR (order_no = 'a' AND expire_time = 'b');

1
2
3
4
5

对于上面这个SQL，优化器可能采用这样的方式来执行这个查询：

先按照搜索条件order_no = 'a' AND expire_time = 'b'从索引idx_order_no 和idx_expire_time 中使用Intersection 索引合并的方式得到一个主键集合；
再按照搜索条件insert_time = 'a' AND order_status = 'b' AND expire_time = 'c' 从联合索引u_idx_day_status 中得到另一个主键集合；
采用Union 索引合并的方式把上述两个主键集合取并集，然后进行回表操作，最终将结果返回给用户。

当然，查询条件符合了这些情况也不一定就会采用Union 索引合并，也得看优化器的最终选择。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union 索引合并后进行访问的代价比全表扫描更小时才会使用Union 索引合并。

# [1.3] Sort-Union 合并

Union 索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union 索引合并：

SELECT *
FROM order_exp
WHERE order_no < 'a'
   OR expire_time > 'z';

1
2
3
4

因为根据order_no< 'a'从idx_order_no 索引中获取的二级索引记录的主键值不是排好序的，根据expire_time> 'z'从idx_expire_time 索引中获取的二级索引记录的主键值也不是排好序的，但是order_no< 'a'和expire_time> 'z''这两个条件我们又特别想利用起来，所以我们可以这样：

先根据order_no< 'a'条件从idx_order_no 二级索引中获取记录，并按照记录的主键值进行排序；
再根据expire_time> 'z'条件从idx_expire_time 二级索引中获取记录，并按照记录的主键值进行排序；
因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union 索引合并方式就一样了。

上述这种先按照二级索引记录的主键值进行排序，之后按照Union 索引合并方式执行的方式称之为Sort-Union 索引合并，很显然，这种Sort-Union 索引合并比单纯的Union 索引合并多了一步对二级索引记录的主键值排序的过程。

# [1.4] 联合索引替代Intersection 索引合并

我们来看一个例子：

SELECT *
FROM order_exp
WHERE order_no = 'a'
  And expire_time = 'z';

1
2
3
4

这个查询之所以可能使用Intersection 索引合并的方式执行，主要是因为idx_order_no 和idx_expire_time 是两个单独的B+树索引，要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，就不用索引合并了。于是：

ALTER TABLE order_exp drop index idx_order_no, idx_expire_time,
add index idx_order_no_expire_time(order_no, expire_time);

1
2

这样我们把idx_order_no, idx_expire_time 都干掉，再添加一个联合索引idx_order_no_expire_time，使用这个联合索引进行查询简直是又快又好，既不用多读一棵B+树，也不用合并结果。

# [2] 连接查询的原理

# [2.1] 连接查询简介

# 连接查询的本质

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

为了方便讲述，我们建立两个简单的演示表并给它们写入数据：

CREATE TABLE e1
(
    m1 int,
    n1 char(1)
);
CREATE TABLE e2
(
    m2 int,
    n2 char(1)
);
INSERT INTO e1
VALUES (1, 'a'),
       (2, 'b'),
       (3, 'c');
INSERT INTO e2
VALUES (2, 'b'),
       (3, 'c'),
       (4, 'd');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

mysql-01

mysql-02
所以我们把 e1 和 e2 两个表连接起来的过程如下图所示： mysql-03

这个过程看起来就是把 e1表的记录和e2 的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。

因为表 e1 中有 3 条记录，表 e2 中也有 3 条记录，所以这两个表连接之后的笛卡尔积就有 3×3=9 行记录。在 MySQL 中，连接查询的语法很随意，只要在 FROM 语句后边跟多个表名就好了，比如我们把 e1 表和 e2 表连接起来的查询语句可以写成这样：

SELECT *
FROM e1,
     e2;

1
2
3

mysql-04

# 连接过程简介

通过上面学习，发现我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说 3 个 100 行记录的表连接起来产生的笛卡尔积就有 100×100×100=1000000 行数据！

所以在连接的时候过滤掉特定记录组合是有必要的，在连接查询中的过滤条件可以分成两种：涉及单表条件和涉及多表条件。

我们来看一个查询语句例子：

SELECT *
FROM e1,
     e2
WHERE e1.m1 > 1
  AND e1.m1 = e2.m2
  AND e2.n2 < 'd';

1
2
3
4
5
6

涉及单表的条件
比如 e1.m1 > 1 是只针对 e1 表的过滤条件，e2.n2 < 'd'是只针对 e2 表的过滤条件。
涉及多（两）表的条件
比如上面的 e1.m1 = e2.m2，这中条件中涉及到了两个表。

下面，我们一起来看一下携带过滤条件的连接查询的大致执行过程是怎么样的？
在这个查询中我们指明了这三个过滤条件：

e1.m1 > 1
e1.m1 = e2.m2
e2.n2 < 'd'

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

首先确定第一个需要查询的表，这个表称之为驱动表。单表中执行查询语句只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是说从 const、ref、ref_or_null、range、index、all 等等这些执行方法中选取代价最小的去执行查询）;
此处假设使用 e1 作为驱动表，那么就需要到 e1 表中找满足 e1.m1 > 1 的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询 e1 表的访问方法就设定为 ALL，也就是采用全表扫描的方式执行单表查询。
很明显，e1 表中符合 e1.m1 > 1 的记录有两条。
针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到 e2 表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据 e1 表中的记录去找 e2 表中的记录，所以 e2 表也可以被称之为被驱动表。上一步骤从驱动表中得到了 2 条记录，所以需要查询 2 次 e2 表。
此时涉及两个表的列的过滤条件 e1.m1 = e2.m2 就派上用场了：
1、当 e1.m1 = 2 时，过滤条件 e1.m1 = e2.m2 就相当于 e2.m2 = 2，所以此时 e2 表相当于有了 e2.m2 = 2、e2.n2 < 'd'这两个过滤条件，然后到 e2 表中执行单表查询。
2、当 e1.m1 = 3 时，过滤条件 e1.m1 = e2.m2 就相当于 e2.m2 = 3，所以此时 e2 表相当于有了 e2.m2 = 3、e2.n2 < 'd'这两个过滤条件，然后到 e2 表中执行单表查询。
所以整个连接查询的执行过程就如下图所示：也就是说整个连接查询最后的结果只有两条符合过滤条件的记录：

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

# 内连接和外连接

数据准备（为了大家更好理解后边内容，我们创建两个有现实意义的表，并插入一些数据）：

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 '客户信息表';

CREATE TABLE score
(
    number  INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score   TINYINT COMMENT '成绩',
    PRIMARY KEY (number, subject)
) ENGINE = InnoDB CHARSET = utf8 COMMENT '客户成绩表';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看数据结果：

SELECT *
FROM student;
SELECT *
FROM score;

1
2
3
4

mysql-07

现在我们想把每个学生的考试成绩都查询出来，SQL语句如下：

SELECT s1.number, s1.name, s2.subject, s2.score
FROM student AS s1,
     score AS s2
WHERE s1.number = s2.number;

1
2
3
4

mysql-08

从上述查询结果中我们可以看到，各个同学对应的各科成绩就都被查出来了，可是有个问题，King 同学，也就是学号为 20200904 的同学因为某些原因没有参加考试，所以在 score 表中没有对应的成绩记录。

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

内连接： 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。

外连接： 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

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

左外连接，选取左侧的表为驱动表。

右外连接，选取右侧的表为驱动表。

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

WHERE 子句中的过滤条件： WHERE 子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。

ON 子句中的过滤条件： 对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。

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

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

# 左（外）连接的语法

左（外）连接的语法还是挺简单的，比如我们要把 e1 表和 e2 表进行左外连接查询可以这么写：

SELECT *
FROM e1 LEFT [OUTER] JOIN e2
ON 连接条件 [
WHERE 普通过滤条件];

1
2
3
4

其中中括号里的 OUTER 单词是可以省略的。对于 LEFT JOIN 类型的连接来说，**我们把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。 ** 所以上述例子中 e1 就是外表或者驱动表，e2 就是内表或者被驱动表。需要注意的是，对于左（外）连接和右（外）连接来说，必须使用 ON 子句来指出连接条件。

了解了左（外）连接的基本语法之后，再次回到我们上边那个问题中来，看看怎样写查询语句才能把所有的客户的成绩信息都查询出来，即使是缺考的考生也应该被放到结果集中：

SELECT s1.number, s1.name, s2.subject, s2.score
FROM student AS s1
         LEFT JOIN score AS s2 ON s1.number = s2.number;

1
2
3

mysql-09 从结果集中可以看出来，虽然 King 并没有对应的成绩记录，但是由于采用的是连接类型为左（外）连接，所以仍然把她放到了结果集中，只不过在对应的成绩记录的各列使用 NULL 值填充而已。

# 右（外）连接的语法

右（外）连接和左（外）连接的原理是一样的，语法也只是把 LEFT 换成 RIGHT 而已：

SELECT *
FROM e1 RIGHT [OUTER] JOIN e2
ON 连接条件 [
WHERE 普通过滤条件];

1
2
3
4

右（外）连接的驱动表和被驱动表，相对于左（外）连接来说互换了身份，即驱动表是右边的表 e2，被驱动表是左边的表 e1。

# 内连接的语法

内连接和外连接的根本区别就是在驱动表中的记录不符合ON 子句中的连接条件时不会把该记录加入到最后的结果集，一种最简单的内连接语法，就是直接把需要连接的多个表都放到 FROM 子句后边。其实针对内连接，MySQL 提供了好多不同的语法：

SELECT *
FROM e1 [INNER | CROSS] JOIN e2 [
ON 连接条件] [
WHERE 普通过滤条件];

1
2
3
4

简单来说，在 MySQL 中，下边这几种内连接的写法都是等价的：

SELECT *
FROM e1,
     e2;
SELECT *
FROM e1
         JOIN e2;
SELECT *
FROM e1
         INNER JOIN e2;
SELECT *
FROM e1
         CROSS JOIN e2;

1
2
3
4
5
6
7
8
9
10
11
12

注意，由于在内连接中 ON 子句和 WHERE 子句是等价的，所以内连接中不要求强制写明 ON 子句。

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

但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合 ON 子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

# [2.2] MySQL 对连接查询的执行

在了解学习了连接、内连接、外连接这些基本概念后，我们需要理解 MySQL 怎么样来进行表与表之间的连接，才能明白有的连接查询运行的快，有的却很慢。

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

我们前边说过，对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。

对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。

如果有 3 个表进行连接的话，那么首先两表连接得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，可以用伪代码表示一下这个过程就是这样：

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

1
2
3
4
5
6
7
8
9
10
11
12

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

# 使用索引加快连接速度

我们知道在嵌套循环连接的步骤 2 中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，对于大数量表的性能压力是非常大的！但是查询 e2 表其实就相当于一次单表查询，我们可以利用索引来加快查询速度。回顾一下最开始介绍的 e1 表和 e2 表进行内连接的例子：

SELECT *
FROM e1,
     e2
WHERE e1.m1 > 1
  AND e1.m1 = e2.m2
  AND e2.n2 < 'd';

1
2
3
4
5
6

我们使用的其实是嵌套循环连接算法执行的连接查询，查询执行过大致是查询驱动表 e1 后的结果集中有两条记录，嵌套循环连接算法需要对被驱动表查询 2 次：当 e1.m1 = 2 时，去查询一遍 e2 表，对 e2 表的查询语句相当于：

SELECT *
FROM e2
WHERE e2.m2 = 2
  AND e2.n2 < 'd';

1
2
3
4

当 e1.m1 = 3 时，再去查询一遍 e2 表，此时对 e2 表的查询语句相当于：

SELECT *
FROM e2
WHERE e2.m2 = 3
  AND e2.n2 < 'd';

1
2
3
4

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

在 m2 列上建立索引，因为对 m2 列的条件是等值查找，比如 e2.m2 = 2、e2.m2 = 3 等，所以可能使用到 ref 的访问方法，假设使用 ref 的访问方法去执行对 e2 表的查询的话，需要回表之后再判断 e2.n2 < d 这个条件是否成立。

这里有一个比较特殊的情况，就是假设m2 列是e2 表的主键或者唯一二级索引列，那么使用e2.m2 = 常数值这样的条件从e2 表中查找记录的过程的代价就是常数级别的。我们知道在单表中使用主键值或者唯一二级索引列的值进行等值查找的方式称之为：const，而MySQL 把在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref。

在n2 列上建立索引，涉及到的条件是e2.n2 < 'd'，可能用到range 的访问方法，假设使用range 的访问方法对e2 表的查询的话，需要回表之后再判断在m2 列上的条件是否成立。

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

另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null 或者range 这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是 index(索引覆盖) 的访问方法来查询被驱动表。

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

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。

现实生活中的表成千上万条记录都是少的，几百万、几千万甚至几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。

而采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O 代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。

所以，可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。所以MySQL 提出了一个 join buffer 的概念，join buffer 就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer 中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer 中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O 代价。使用join buffer 的过程如下图所示： mysql-10

最最好的情况是join buffer 足够大，能容纳驱动表结果集中的所有记录。

这种加入了join buffer 的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

join buffer 的大小是可以通过启动参数或者系统变量join_buffer_size 进行配置，默认大小为262144 字节（也就是256KB），最小可以设置为128 字节。

show
variables like 'join_buffer_size' ;

1
2

mysql-11

当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size 的值来对连接查询进行优化。

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

#底层原理 #索引优化

最近更新: 2025/03/03, 06:23:53

← MySQL 性能优化 MySQL 的底层执行原理2→