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

2023-12-13 04:23:08

前言

通过上篇文章《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的情况从而进行调整。

文章来源:https://blog.csdn.net/qq_28314431/article/details/134955529
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。