建设银行网站怎么看交易记录,汽车之家官网网页版入口,公众号平台登陆,重庆网站平台文章目录 前言存储引擎介绍存储引擎是干嘛的InnoDB的体系结构 InnoDB的查询操作InnoDB的查询原理引入 Buffer Pool引入数据页Buffer Pool 的结构数据页的加载Buffer Pool 的管理Buffer Pool 的优化 总结 前言
通过上篇文章《MySQL的体系结构与SQL的执行流程》了解了SQL语句的执… 文章目录 前言存储引擎介绍存储引擎是干嘛的InnoDB的体系结构 InnoDB的查询操作InnoDB的查询原理引入 Buffer Pool引入数据页Buffer Pool 的结构数据页的加载Buffer Pool 的管理Buffer Pool 的优化 总结 前言
通过上篇文章《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的请求后会有两个操作
先去内存中查找有没有符合条件的数据有直接将数据返回给执行器。如果内存中符合条件的数据此时需要去磁盘中查找并加载到内存然后将数据返回给执行器。
没错在查询数据时InnoDB干的活就是这么简单。当然我们还是要深入内部了解一下原理。
InnoDB的查询原理 InnoDB是怎么找到符合条件的数据的 引入 Buffer Pool
这个问题我们不得不了解一下内存结构中的「Buffer Pool」了。
「Buffer Pool」是InnoDB的缓冲区用来缓存数据页的结构图中的一个小方块就代表缓存的一个数据页目的就是为了避免频繁的I/O操作用来提高效率的。 什么是数据页? 引入数据页
在数据库中每一行记录落到磁盘上都是按照某种格式存储的InnoDB引擎是按照自己的「行格式」进行存储的。如果每一次存储和读取一行记录都要和磁盘交互也就是一次I/O操作毋庸置疑对于MySQL这样的存储级别的数据库来说效率是非常低的。
所以InnoDB是按照「数据页」为单位和磁盘交互一页默认大小是16KB每次I/O操作可以存储或读取很多行数据这样可以大大减少I/O次数从而提高效率。「数据页」大概长这样 页中的每一个部分都是逻辑中需要的比如通过「页类型」就知道数据页不仅存储了表数据还有索引数据、Undo Log以及该页属于BTree索引上的叶子节点还是非叶子节点。当然表空间、页号、这些信息就更不用说了。
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为主键索引构建一个BTree所以找到了根节点的页号其他数据页也都可以找到了。
以select * from table1 where id10为例。InnoDB会先把第一页加载到Buffer Pool中当然也会维护对应的控制块。然后在页中开始遍历查找id为10的行记录为了快速定位行数据数据页中维护了一个最小记录和最大记录以及页目录。当ID不存在最小和最大的范围就可以直接去加载下一页了以此类推。 页目录的作用是什么呢 可以理解成给数据页中的用户数据分了个组比如ID为14为一组58是一组以此类推。页目录是由一个一个槽组成的分别指向了每一组的最大记录。如下图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_size2147483648;同时我们可以通过命令show global status like %innodb%wait%;观察Innodb_buffer_pool_wait_free的数量当这个值大于0时意味着缓冲区没有可用的页了此时就需要考虑增加缓冲区的大小了。
这也是MySQL优化的一部分下次面试再被问到MySQL如何优化不要只知道索引了。关于buffer_pool的优化详见MySQL官网
总结
最后再通过一张图总结一下在执行器调用存储引擎后InnoDB做了什么事。 InnoDB根据SQL请求去Buffer Pool中查找「行数据」。为了避免频繁的I/O操作InnoDB将「行数据」存放在「数据页」中。为了快速定位到数据页Buffer Pool 中还存储了数据页的元数据可以根据SQL的表、索引快速定位到数据页。在Buffer Pool中没有找到数据后去磁盘加载数据页。通过「InnoDB Data Dictionary」可以找到索引的根节点页号并加载对应的数据页。将数据页加载到Buffer Pool中开始查找数据为了快速找到行记录数据页中还存放了当前页最小记录、最大记录和页目录。由于Buffer Pool容量有限InnoDB采用LRU算法管理缓存的数据页确保频繁访问的数据页会一直保留从而减少去磁盘加载的次数而那些不经常使用的数据页就会被淘汰。我们还可以通过观察Buffer Pool的情况从而进行调整。
