【5. MySQL 的执行原理】_rowid ordered retrieval-CSDN博客

本文链接：https://blog.csdn.net/yin18827152962/article/details/121915422

本文探讨了MySQL中的索引合并(Index Merge)优化策略，特别是Intersection合并，适用于等值匹配的多个二级索引场景。通过实例分析了如何在特定条件下减少回表操作，提高查询效率，同时对比了不同索引合并方式的成本与性能考量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL 的执行原理

单表访问之索引合并(本质是主键索引的合并)

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

Intersection 合并(交集合并)

Intersection 翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：

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

假设这个查询使用 Intersection 合并的方式执行的话，那这个过程就是这样的：

从 idx_order_no 二级索引对应的 B+树中取出 order_no= 'a’的相关记录。

从 idx_insert_time 二级索引对应的 B+树中取出 insert_time= 'b’的相关记录。

二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中 id 值的交集。

按照上一步生成的 id 值列表进行回表操作，也就是从聚簇索引中把指定 id 值的完整用户记录取出来，返回给用户。(这个)也类似MRR: Mutil Range Read , 只不过多范围读取的不是在同一个索引中而是在不同的索引中读取到的主键索引)

为啥不直接使用 idx_order_no 或者 idx_insert_time 只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？这里要分析一下两种查询执行方式之间需要的成本代价。

只读取一个二级索引的成本：

按照某个搜索条件读取一个二级索引，根据从该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件

读取多个二级索引之后取交集成本：

按照不同的搜索条件分别读取不同的二级索引，将从多个二级索引得到的主键值取交集，然后进行回表操作。

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

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

情况一：等值匹配

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

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

SELECT * FROM order_exp WHERE order_no = ‘a’ AND insert_time = ‘a’;

第一个查询是因为对 order_no 进行了范围匹配，第二个查询是因为联合索引u_idx_day_status中的order_status和expire_time列并没有出现在搜索条件中，所以这两个查询不能进行 Intersection 索引合并。

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

比方说下边这个查询可能用到主键和u_idx_day_status进行Intersection索引合并的操作：

SELECT * FROM order_exp WHERE id > 100 AND insert_time = ‘a’;

对于 InnoDB 的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引的用户记录是由索引列 + 主键构成的，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。(可以)使用索引条件下推)

所以重点来了，之所以在二级索引列都是等值匹配的情况下才可能使用 Intersection 索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。***(这句怎么理解: 其实我们联合索引是相对同一个前缀索引有序的, 我们主键也是相对联合索引的最后一个索引有序,主键索引的值也不是随便排序的***)

Intersection 索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很容易(时间复杂度为O(n))。

按照有序的主键值去回表取记录有个专有名词，叫：Rowid Ordered Retrieval，简称 ROR。

另外，不仅是多个二级索引之间可以采用 Intersection 索引合并，索引合并也可以有聚簇索引参加，也就是我们上边写的情况二：在搜索条件中有主键的范围匹配的情况下也可以使用 Intersection 索引合并索引合并。**为啥主键这就可以范围匹配了？**还是得回到应用场景里：(因为主键索引值也在二级索引里面)

SELECT * FROM order_exp WHERE id > 100 AND insert_time = ‘a’;

假设这个查询可以采用 Intersection 索引合并，我们理所当然的以为这个查询会分别按照 id > 100 这个条件从聚簇索引中获取一些记录，在通过 insert_time= 'a’这个条件从 idx_order_no 二级索引中获取一些记录，然后再求交集，其实这样就把问题复杂化了，没必要从聚簇索引中获取一次记录。别忘了二级索引的记录中都带有主键值的，所以可以在从 idx_order_no 中获取到的主键值上直接运用条件 id > 100 过滤就行了，这样多简单。所以涉及主键的搜索条件只不过是为了从别的二级索引得到的结果集中过滤记录罢了，是不是等值匹配不重要。

当然，上边说的情况一和情况二只是发生 Intersection 索引合并的必要条件，不是充分条件。也就是说即使情况一、情况二成立，也不一定发生 Intersection 索引合并，这得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过 Intersection 索引合并后需要回表的记录数大大减少时才会使用 Intersection 索引合并。(最终还是要看成本)

Union 合并(并集合并)

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是 OR 关系。有时候 OR 关系的不同搜索条件会使用到不同的索引，比方说这样：

SELECT * FROM order_exp WHERE order_no = ‘a’ OR expire_time = ‘b’

Intersection 是交集的意思，这适用于使用不同索引的搜索条件之间使用 AND 连接起来的情况；Union 是并集的意思，适用于使用不同索引的搜索条件之间使用 OR 连接起来的情况。与 Intersection 索引合并类似，MySQL 在某些特定的情况下才可能会使用到 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’);

优化器可能采用这样的方式来执行这个查询：

先按照搜索条件 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 索引合并。

Sort-Union 合并(对union的一种兼容处理)

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

SELECT * FROM order_exp WHERE order_no< ‘a’ OR expire_time> ‘z’

这是因为根据 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 索引合并多了一步对二级索引记录的主键值排序的过程。

联合索引替代 Intersection 索引合并(如果追求性能你就自己去建立联合索引)

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

这个查询之所以可能使用 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);

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

连接查询

搞数据库一个避不开的概念就是 Join，翻译成中文就是连接。使用的时候常常陷入下边两种误区：

误区一：业务至上，管他三七二十一，再复杂的查询也用在一个连接语句中搞定。

误区二：敬而远之，上次慢查询就是因为使用了连接导致的，以后再也不敢用了。

所以我们来学习一下连接的原理，才能在工作中用好 SQL 连接。

连接简介

连接的本质

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

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');

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

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

在这里插入图片描述

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

在 MySQL 中，连接查询的语法很随意，只要在 FROM 语句后边跟多个表名就好了，比如我们把 e1 表和 e2 表连接起来的查询语句可以写成这样：

SELECT * FROM e1, e2;

连接过程简介

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

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

涉及单表的条件

比如 e1.m1 > 1 是只针对 e1 表的过滤条件，e2.n2 < 'd’是只针对 e2 表的过滤条件。

涉及两表的条件

比如类似 e1.m1 = e2.m2、e1.n1 > e2.n2 等，这些条件中涉及到了两个表。

看一下携带过滤条件的连接查询的大致执行过程在这个查询中我们指明了这三个过滤条件：

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 就派上用场了：

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

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

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

在这里插入图片描述

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

mysql> SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

从上边两个步骤可以看出来，这个两表连接查询共需要查询 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 '客户成绩表';

mysql> SELECT * FROM student;
+----------+-------+-----------------+
| number   | name  | major           |
+----------+-------+-----------------+
| 20200901 | aaa  | 网络工程        |
| 20200902 | bbb  | 计算机科学      |
| 20200903 | ccc | 计算机科学      |
| 20200904 | ddd  | 软件工程        |
+----------+-------+-----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM score;
+----------+-----------------------+-------+
| number   | subject               | score |
+----------+-----------------------+-------+
| 20200901 | 数据结构和算法        |    88 |
| 20200901 | 网络通信原理          |    90 |
| 20200902 | 数据结构和算法        |    95 |
| 20200902 | 网络通信原理          |    89 |
| 20200903 | 离散数学              |    70 |
+----------+-----------------------+-------+
5 rows in set (0.01 sec)

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

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 |
+----------+-------+-----------------------+-------+
| 20200901 | aaa  | 数据结构和算法        |    88 |
| 20200901 | aaa  | 网络通信原理          |    90 |
| 20200902 | bbb  | 数据结构和算法        |    95 |
| 20200902 | bbb  | 网络通信原理          |    89 |
| 20200903 | ccc | 离散数学              |    70 |
+----------+-------+-----------------------+-------+
5 rows in set (0.00 sec)

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

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

左外连接

右外连接

太过基础,连接省略

左（外）连接的语法

内连接的语法

略

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

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

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 
        }
    }
}

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

使用索引加快连接速度

我们知道在嵌套循环连接的步骤 2 中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，那酸爽不敢想象！

但是查询 e2 表其实就相当于一次单表查询，我们可以利用索引来加快查询速度。

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

可以看到，原来的 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）

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

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

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

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

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

在这里插入图片描述

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

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

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

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

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

另外需要注意的是，驱动表的记录并不是所有列都会被放到 join buffer 中， 只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在 join buffer 中放置更多的记录。(所以使用*不仅会导致cpu,内存,网络不必要的开销,还会导致join buffer可放入的数据量更少,导致缓存使用效率降低)

MySQL 的查询成本

什么是成本

MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在 MySQL 中一条查询语句的执行成本是由下边这两个方面组成的：

I/O 成本

我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。

CPU 成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。

对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL 规定读取一个页面花费的成本默认是 1.0，读取以及检测一条记录是否符合搜索条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数

注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.2。

单表查询的成本

基于成本的优化步骤实战

在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

3、计算使用不同索引执行查询的代价

4、对比各种执行方案的代价，找出成本最低的那一个

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

mysql> show create table order_exp\G
*************************** 1. row ***************************
       Table: order_exp
Create Table: CREATE TABLE `order_exp` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
  `order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
  `expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
  `expire_time` datetime NOT NULL COMMENT '订单的过期时间',
  `order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
  KEY `idx_order_no` (`order_no`) USING BTREE,
  KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT