MySQL 简单查询语句执行过程分析（三）从存储引擎读数据

作者 | 操盛春
来源 | 一树一溪（ID：lovespring52）
转载请联系授权（微信ID：csch52）

示例表及 SQL 如下：

-- 表结构
CREATE TABLE `t_recbuf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i1` int(10) unsigned DEFAULT '0',
  `str1` varchar(32) DEFAULT '',
  `str2` varchar(255) DEFAULT '',
  `c1` char(11) DEFAULT '',
  `e1` enum('北京','上海','广州','深圳','天津','杭州','成都','重庆','苏州','南京','洽尔滨','沈阳','长春','厦门','福州','南昌','泉州','德清','长沙','武汉') DEFAULT '北京',
  `s1` set('吃','喝','玩','乐','衣','食','住','行','前后','左右','上下','里外','远近','长短','黑白','水星','金星','地球','火星','木星','土星','天王星','海王星','冥王星') DEFAULT '',
  `bit1` bit(8) DEFAULT b'0',
  `bit2` bit(17) DEFAULT b'0',
  `blob1` blob,
  `d1` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8;
-- 查询语句
select * from t_recbuf where i1 > 49276

本文讲述从 InnoDB 存储引擎中读取数据，所以文中涉及到存储引擎的地方，都直接使用 InnoDB 代替了。

1. 创建 InnoDB 实例

MySQL 每打开一个表，就会创建一个对应的 InnoDB 实例。创建 InnoDB 实例时，主要会干 3 件事情：

• 打开 InnoDB 表(这个过程比较复杂，需要单独写文章讲述)
• 建立 MySQL 和 InnoDB 索引映射
• 创建 InnoDB 查询结构

创建 InnoDB 实例是在上一篇(查询准备阶段)中讲到的打开表的过程中进行的。

1.1 建立 MySQL 和 InnoDB 索引映射

MySQL 从 InnoDB 读取数据之前，词法分析、语法分析、查询准备、查询优化这些阶段都是 server 层的范围，在 server 层中需要使用索引信息时，使用的都是 MySQL 的索引信息，InnoDB 读取数据时会使用自己的索引信息，需要通过 MySQL 索引找到 InnoDB 索引，而这个找 InnoDB 索引的过程，是每执行一条使用索引进行查询的 SQL 都会用到的，又是一个频繁而重复的操作。为了更快的找到 InnoDB 索引，在创建 InnoDB 实例阶段就会建立 MySQL 索引和 InnoDB 索引之间的映射关系，这本质上也是个缓存(MySQL 中缓存思想无处不在)。

建立映射的过程是遍历 MySQL 表中的每一个索引，用索引的名字去对应的 InnoDB 表中找 InnoDB 索引，找到后把这个对应关系保存到数组中，数组 key 为 MySQL 表中的索引 ID，value 为 InnoDB 索引。

dict_index_t** index_mapping;
// id, idx_i1 指的是 InnoDB 中相应的索引，都是 dict_index_t 指针
index_mapping[0] = id
index_mapping[1] = idx_i1

建立映射之后，在以后的操作中，通过 MySQL 索引 ID 就可以很方便的找到 InnoDB 索引了。

1.2 创建 InnoDB 查询模板

InnoDB 会根据查询模板中的信息执行查询，查询模板中包含这些信息：

• 使用什么索引
• 索引筛选条件
• 索引条件下推
• 是否需要回表
• server 层需要 InnoDB 返回哪些字段

还有很多其它信息，就不一一列举了。

创建 InnoDB 实例过程中，只是创建了查询模板实例并给其中的属性设置了初始值。在填充查询模板阶段会为这些属性设置实际的值。

2. 填充查询模板

查询模板中的属性很多，我们只介绍示例 SQL 执行过程中使用到的比较重要的属性：

• 使用什么索引
• 是否需要回表
• server 层需要 InnoDB 返回哪些字段

2.1 使用什么索引

InnoDB 中有两种索引：主键索引(也叫聚簇索引)、二级索引，在执行过程中使用哪种类型的索引，使用哪个索引，是在查询优化阶段决定的。

根据查询优化阶段确定的要使用的索引 ID 从 MySQL 和 InnoDB 索引映射中找到 InnoDB 索引，后面执行读取数据操作时，就直接使用这个索引。

2.2 是否需要回表

先来说说什么是回表?

使用二级索引读取数据时，如果 server 层要求 InnoDB 返回的字段，在二级索引中并不存在，那么就需要再去读主键索引，以获取二级索引中不存在的那些字段，这个过程就叫回表。

回表是 InnoDB 的自主操作，当使用二级索引读取数据并需要回表时，InnoDB 就直接使用二级索引中读取到的主键字段值去主键索引中读取完整的记录，并返回给 server 层，server 层对于回表无感知。

知道了回表的原理，我们可以想到回表并不复杂，只需要进行两个逻辑判断就可以了：

• 如果使用主键索引，不需要回表，因为主键索引中本身就包含完整记录。
• 如果使用二级索引，需要再进一步判断：如果 server 层要求 InnoDB 返回的字段，都在二级索引中(就是覆盖索引了)，不需要回表，否则就需要回表。

举例说明：

假设 test 表的字段 a 上有个二级索引 idx_a，当执行以下 SQL 使用 idx_a 索引进行查询时，就需要回表

select * from test where a > 1024

2.3 server 层需要 InnoDB 返回哪些字段

InnoDB 每次从 Buffer Pool 中读取一条记录时，都会读取完整的记录(字段内容有溢出时例外)，读取记录之后，会拷贝字段内容到 server 层和引擎层交换内容的记录缓冲区中，而这个拷贝字段内容的过程也是需要时间的，如果 server 层只需要 InnoDB 返回记录中的部分字段，那么拷贝所有字段内容就存在浪费了。

• 如果字段内容有溢出，溢出内容会存储到单独的溢出页，只有当 server 层需要该字段时，InnoDB 才会去溢出页读取其中的内容，和该字段在记录中的部分内容一起组成字段的完整内容。
• 关于 server 层和 InnoDB 之间的数据交换，可以参考这篇文章：MySQL server 层和存储引擎层是怎么交互数据的?

举例说明：

假设 test 表有 6 个字段：d、e、f、x、y、z，当执行以下 SQL 时(表上没有索引)，server 层会要求 InnoDB 返回哪些字段?

select x, y, z from test
where e > 1024 order by d

select 子句中有 x, y, z 3 个字段，server 层只会要求 InnoDB 返回这 3 个字段吗?

不不不，没这么简单，server 层还想要更多，它还需要字段 e 来做 where 条件筛选，需要字段 d 来做排序，所以它会要求 InnoDB 返回 d, e, x, y, z 这 5 个字段。

经过上面的讲述，相信大家对 server 层需要 InnoDB 返回哪些字段的逻辑已经明白了，咱们来总结一下：

只有 server 层需要用到的字段，才会要求 InnoDB 返回。那是为什么呢?两个字：效率。

接下来就要开始从 InnoDB 读取记录了，由于 InnoDB 记录存储本身也是个很大的话题，要说清楚怎么定位到要查找的记录，怎么读取下一条记录这些细节，需要很多关于 InnoDB 的文件存储以及 Buffer Pool 相关的内容作铺垫，要用一个系列的文章才能讲清楚，所以本文不会涉及 InnoDB 文件存储及 Buffer Pool 相关的细节。

3. 从 InnoDB 读取记录

为了提升执行效率，InnoDB 在读取时也是煞费苦心的，用了 2 种优化手段：

• 预读缓存
• 自适应哈希索引

自适应哈希索引的使用有很多条件限制，本文示例 SQL 不能使用自适应哈希索引来加快读取记录的速度，所以暂时先不介绍。接下来我们来看看预读缓存是什么以及它是怎么工作的?

3.1 预读缓存

InnoDB 读取数据的第一步，就是先看看预读缓存里有没有记录，如果有就直接取出 1 条记录返回给 server 层，如果没有，才会去 Buffer Pool 中读取记录。

预读缓存又是个新概念，我们来看看它是什么?

预读缓存是 InnoDB 丰富的内心戏的产物，当 InnoDB 心里想象着 server 层会要它读取很多记录时，它就会在读取某一条记录的时候，偷偷的多读几条缓存起来，等下次 server 层再找它要数据的时候，它就可以愉快的把已经缓存起来的记录快速的 取 1 条出来返回给 server 层，直到预读缓存中的全部记录都取出来返回给 server 层之后，再读下一条记录时，又接着偷偷的多读几条缓存起来，循环往复，直到读完所有符合条件的记录，这个缓存就是预读缓存。

如果 server 层和 InnoDB 是两个人的话，InnoDB 从预读缓存里读记录这么快，server 层还不得给加鸡腿?

InnoDB 想象的依据是什么呢?

虽说是靠想象，但是也不能凭空瞎想吧，所以还得有个规则，规则有 2 条：

• where 条件使用了索引，并且是 i1 = 1024 这种形式，也就是用等号比较。
• 连续读取了大于等于 4 条记录。

上面说的 2 条规则是主要的，但是要想使用预读缓存，还会有一些其它隐密条件的限制，导致不能使用预读缓存，不再一一列举了。

上面的 2 条规则，只需要满足其中 1 条，外加满足其它隐密条件的限制，就会使用预读缓存。

预读缓存可以存几条记录?

在其它隐密的限制条件都满足的前提下：如果当前查询的 where 条件使用了索引，并且是 i1 = 1024 这种形式，InnoDB 从 Buffer Pool 中读取 1 条记录之后，会再去读取 8 条记录，这 8 条记录都存到预读缓存里，然后把开始单独读的 1 条记录返回给 server 层(此时预读缓存中有 8 条记录)。

如果当前查询的 where 条件没有使用索引，或者是使用了索引，但是索引字段使用的是 IN、>、>=、<、<=、!= 等等这些非等号比较操作符，那就会判断是不是已经连续读取了大于等于 4 条记录，如果是，InnoDB 从 Buffer Pool 中读取 1 条记录之后，会再去读取 8 条记录，这 8 条记录都存到预读缓存里，然后把开始单独读的 1 条记录返回给 server 层(此时预读缓存中有 8 条记录)。

读取 8 条记录存到预读缓存里，这个 8 是在代码里写死的，如果想修改，只能改代码。

如果是从预读缓存读取记录，不会再从 Buffer Pool 多读 8 条记录存到预读缓存里，只有从 Buffer Pool 中读取记录并满足条件时，才会多读 8 条记录存到预读缓存里。

3.2 从 Buffer Pool 读记录

InnoDB 读取记录时，都是从 Buffer Pool 中读的，如果数据页不在 Buffer Pool 中，会先把数据页从磁盘上加载到 Buffer Pool 中，然后再从 Buffer Pool 中读取记录(这是不是跟我们日常写代码时使用缓存的思路一样?)。

InnoDB 的增、删、改、查语句，都必须在事务中执行，本文示例 SQL 执行时，设置的事务隔离级别为 REPEATABLE-READ，可以通过 show variables like 'transaction_isolation' 查看事务隔离级别。

如果我们没有显式开启事务，InnoDB 会为每条语句默认开启一个事务，语句执行完，事务就结束了。

可重复读隔离级别下，在一个事务中多次执行同一条查询语句时，得到的结果应该是完全一样的(不考虑幻读的话)。那么可重复读是通过什么东西实现的呢?答案就是：一致性视图。

3.3 一致性视图

创建一致性视图(ReadView)时，会记录 InnoDB 中此刻的活跃事务信息:

• 所有活跃事务的 IDs
• 活跃事务的最小 ID
• 即将分配给下一个事务的 ID
• 当前事务的 ID

上面只列出了示例 SQL 执行过程，进行可重复读时需要使用到的信息。

在可重复读隔离级别下，一个事务中只会创建一个 ReadView，创建的时机为：事务中第 1 条 SQL 执行的时候。

InnoDB 的每条记录中都有一个隐藏的字段用来表示最后修改记录的事务 ID(DB_TRX_ID)，还有一个隐藏字段(DB_ROLL_PTR)，是个指针，指向记录的历史版本。

当 InnoDB 从 Buffer Pool 中读到一条记录时，这条记录可能在我们创建 ReadView 之后，读取这条记录之前被修改过，这条记录的最新版本对 ReadView 不可见，此时，需要通过 DB_ROLL_PTR 找到记录的历史版本，进行可见性判断。

如果上一个历史版本对 ReadView 也不可见，就顺着上一个历史版本中的 DB_ROLL_PTR 接着找，直到找到最早的历史记录，如果还是对 ReadView 不可见，说明这条记录对于 ReadView 来说就是不可见的。

查找历史版本的过程中，只要找到了可见版本就停下来，找到的这个历史版本的记录就是需要返回给 server 层的。

判断一条记录对于 ReadView 是否可见的逻辑是这样的：

• 如果记录中的 DB_TRX_ID 等于 ReadView 所属事务 ID，该记录对于 ReadView 可见(自己改的自己当然可以看到)。
• 如果记录中的 DB_TRX_ID 小于活跃事务的最小 ID，说明修改记录的事务在 ReadView 创建之前就已经提交了，该记录对于 ReadView 可见。
• 如果记录中的 DB_TRX_ID 大于等于即将分配给下一个事务的 ID，说明修改记录的事务在 ReadView 创建之后才开启，该记录对于 ReadView 不可见。
• 如果记录中的 DB_TRX_ID 是所有活跃事务的 IDs 中的一个，说明修改记录的事务在 ReadView 创建时还没有提交，记录对于 ReadView 不可见
• 如果记录中的 DB_TRX_ID 不是所有活跃事务的 IDs 中的一个，说明修改记录的事务在 ReadView 创建之前已经提交，记录对于 ReadView 可见。

上面说的记录包含最新记录和历史版本中的记录。