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

ゝ一世哀愁。 2024-02-05 14:47 11阅读 0赞

#### 文章目录 ####

*  前言
 *  存储引擎介绍
 *   *  存储引擎是干嘛的
     *  InnoDB的体系结构
 *  InnoDB的查询操作
 *  InnoDB的查询原理
 *   *  引入 Buffer Pool
     *  引入数据页
     *  Buffer Pool 的结构
     *  数据页的加载
     *  Buffer Pool 的管理
     *  Buffer Pool 的优化
 *  总结

## 前言 ##

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

## 存储引擎介绍 ##

### 存储引擎是干嘛的 ###

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

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

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

![在这里插入图片描述][0a8ee18a388549fe94ab5b977157beed.png]

### InnoDB的体系结构 ###

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

![在这里插入图片描述][a6c5d593fc0049b594186dbb40061980.png]

两个版本最大的区别就是把系统表空间的几个文件摘了出来，这里不展开说明。接下来看一下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次数，从而提高效率。「**数据页**」大概长这样：

![在这里插入图片描述][e0b2f94faf614e7ea77e870cd609322d.png]

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

### Buffer Pool 的结构 ###

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

![在这里插入图片描述][a42d416ce23c443bb73e5b63ada2fa8a.png]

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

![在这里插入图片描述][4c8c09c70c884680aafb6a26d3564478.png]

了解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的记录可以直接去第四个槽去查找数据，不需要一行一行遍历查找了。

![在这里插入图片描述][02f53afac1624cbe96023825664d6f5d.png]

至此，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官网][MySQL]

## 总结 ##

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

![在这里插入图片描述][f933922cbb7d4798b0a96b28b55ac6e0.png]

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的情况从而进行调整。

[MySQL_SQL]: https://wangbinguang.blog.csdn.net/article/details/134599526
[0a8ee18a388549fe94ab5b977157beed.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/a3be4ac5f61a4ccc9a45c0230a2be589.png
[a6c5d593fc0049b594186dbb40061980.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/36b81cc9f39c4fa89a58766af32ea2f7.png
[e0b2f94faf614e7ea77e870cd609322d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/04a4bb14f52b4aaaa86a498a49b73bf6.png
[a42d416ce23c443bb73e5b63ada2fa8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/1146118b20344af0bc414fef2ca7c4ce.png
[4c8c09c70c884680aafb6a26d3564478.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/576cf04bc9134e2ea60734180100a20f.png
[02f53afac1624cbe96023825664d6f5d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/092c221fd36447059e14631c25fd78bb.png
[MySQL]: https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-optimization.html
[f933922cbb7d4798b0a96b28b55ac6e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/9a1d66ea3ced4a648e2b055cedccd452.png