InnoDB在SQL查询中的关键功能和优化策略

前言

通过上篇文章《MySQL的体系结构与SQL的执行流程》了解了SQL语句的执行流程以及MySQL体系结构中「连接器」、「SQL接口」、「解析器」、「优化器」、「执行器」的功能以及在整个流程中的作用。不过上篇文章留了个尾巴，在执行器调用存储引擎后，存储引擎内部做了什么事没有进一步说明，本文会对此展开介绍，使得我们对SQL整体的执行流程有更加清晰的认识。

存储引擎介绍

存储引擎是干嘛的

先了解下存储引擎是干什么的。

在MySQL的体系结构中，存储引擎是负责和磁盘交互的，当执行一条SQL语句，最终是通过存储引擎获取结果，不论是查询语句、插入语句还是更新语句，所以存储引擎是用来查询、存储、管理数据的。

在MySQL中，存储引擎是可插拔的，可以根据需求卸载或安装存储引擎。现在MySQL支持很多种存储引擎，在5.5版本后InnoDB被设置为默认的存储引擎，所以本文围绕InnoDB展开说明。下图可以看到可替代的存储引擎。

InnoDB的体系结构

还是老样子，想知道一个系统有什么功能，先了解一下它的体系结构，然后了解每个部分在整个系统中起到什么作用。这里贴一张官网上5.7版本和8.0版本的InnoDB存储引擎结构。

两个版本最大的区别就是把系统表空间的几个文件摘了出来，这里不展开说明。接下来看一下InnoDB存储引擎在接收到「执行器」的调用请求后做了什么事吧。

InnoDB的查询操作

通过结构图可以看到InnoDB存储引擎有两部分内容，一个是内存结构，另一个是物理结构。很显然，当InnoDB收到一个查询SQL的请求后会有两个操作：

1. 先去内存中查找有没有符合条件的数据，有，直接将数据返回给执行器。
2. 如果内存中符合条件的数据，此时需要去磁盘中查找并加载到内存，然后将数据返回给执行器。

没错，在查询数据时InnoDB干的活就是这么简单。当然，我们还是要深入内部了解一下原理。

InnoDB的查询原理

InnoDB是怎么找到符合条件的数据的？

引入 Buffer Pool

这个问题，我们不得不了解一下内存结构中的「Buffer Pool」了。

「Buffer Pool」是InnoDB的缓冲区，用来缓存数据页的（结构图中的一个小方块就代表缓存的一个数据页），目的就是为了避免频繁的I/O操作，用来提高效率的。

什么是数据页?

引入数据页

在数据库中，每一行记录落到磁盘上都是按照某种格式存储的，InnoDB引擎是按照自己的「行格式」进行存储的。如果每一次存储和读取一行记录都要和磁盘交互（也就是一次I/O操作），毋庸置疑，对于MySQL这样的存储级别的数据库来说，效率是非常低的。

所以，InnoDB是按照「数据页」为单位和磁盘交互，一页默认大小是16KB，每次I/O操作可以存储或读取很多行数据，这样可以大大减少I/O次数，从而提高效率。「数据页」大概长这样：

页中的每一个部分都是逻辑中需要的，比如，通过「页类型」就知道数据页不仅存储了表数据，还有索引数据、Undo Log以及该页属于B+Tree索引上的叶子节点还是非叶子节点。当然，表空间、页号、这些信息就更不用说了。

Buffer Pool 的结构

除了数据页，缓冲区中还有个一区域存储了数据页的元数据，比如表空间、页号、表名称、索引等。元数据可以通过执行SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 1\G 查看。例如下图

存储这些元数据的区域叫法比较多，有叫控制块的，有叫缓存页描述的，这里就暂且叫「控制块」吧。通过控制块，InnoDB可以根据请求的SQL表名、索引快速定位到对应的缓存页上。因为Buffer Pool是一个连续的内存空间，所以控制块和缓存页在Buffer Pool中的结构如下

了解Buffer Pool后继续往下看是怎么找到符合条件的数据。

数据页的加载

Buffer Pool 初始状态是没有缓存页的，所以当InnoDB第一次接收到查询请求后会去磁盘加载数据页。

数据页是怎么加载的呢？

在「InnoDB Data Dictionary」数据字典中存储了表、列、索引这些元数据以及索引根节点的页号，有了页号就好办了，我们知道InnoDB默认会以ID为主键索引构建一个B+Tree，所以，找到了根节点的页号，其他数据页也都可以找到了。

以select * from table1 where id=10为例。InnoDB会先把第一页加载到Buffer Pool中，当然也会维护对应的控制块。然后在页中开始遍历查找id为10的行记录，为了快速定位行数据，数据页中维护了一个最小记录和最大记录以及页目录。当ID不存在最小和最大的范围，就可以直接去加载下一页了，以此类推。

页目录的作用是什么呢？

可以理解成给数据页中的用户数据分了个组，比如ID为1～4为一组，5～8是一组，以此类推。页目录是由一个一个槽组成的，分别指向了每一组的最大记录。如下图，id为10的记录可以直接去第四个槽去查找数据，不需要一行一行遍历查找了。

至此，InnoDB就找到符合id为10的行记录了，然后将此数据响应给「执行器」。

那如果全表扫描会将所有数据页加载到Buffer Pool吗？容量够吗？

Buffer Pool 的管理

理论上只要内存容量足够大，所有的数据页都能存储在内存中，当然成本太高，容量有限。所以，通常都是将热点数据、访问频繁的数据页缓存起来，这一点InnoDB是如何做的？

InnoDB采用LRU算法将缓存的数据页通过链表的形式存储，很多地方都用到了LRU算法，这里就不过多赘述。总之，当缓冲池容量满了就会移除链表尾部数据，这样就可以确保访问频繁的数据一直在缓冲区了。

Buffer Pool 的优化

为了尽可能的缓存更多的数据页，我们可以通过配置innodb_buffer_pool_size，将缓冲区设置尽可能的大。相关命令如下

-- 查看当前缓冲区大小
SELECT @@innodb_buffer_pool_size;
-- 在线设置缓冲区大小（2G）
SET GLOBAL innodb_buffer_pool_size=2147483648;

同时我们可以通过命令show global status like '%innodb%wait%';观察Innodb_buffer_pool_wait_free的数量，当这个值大于0时意味着缓冲区没有可用的页了，此时就需要考虑增加缓冲区的大小了。

这也是MySQL优化的一部分，下次面试再被问到MySQL如何优化，不要只知道索引了。关于buffer_pool的优化详见MySQL官网

总结

最后，再通过一张图总结一下在执行器调用存储引擎后，InnoDB做了什么事。

1. InnoDB根据SQL请求去Buffer Pool中查找「行数据」。
2. 为了避免频繁的I/O操作，InnoDB将「行数据」存放在「数据页」中。
3. 为了快速定位到数据页，Buffer Pool 中还存储了数据页的元数据，可以根据SQL的表、索引快速定位到数据页。
4. 在Buffer Pool中没有找到数据后去磁盘加载数据页。通过「InnoDB Data Dictionary」可以找到索引的根节点页号并加载对应的数据页。
5. 将数据页加载到Buffer Pool中开始查找数据，为了快速找到行记录，数据页中还存放了当前页最小记录、最大记录和页目录。
6. 由于Buffer Pool容量有限，InnoDB采用LRU算法管理缓存的数据页，确保频繁访问的数据页会一直保留，从而减少去磁盘加载的次数，而那些不经常使用的数据页就会被淘汰。
7. 我们还可以通过观察Buffer Pool的情况从而进行调整。