文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL Buffer Pool如何提高页的访问速度

2023-03-03 14:30

关注

如何提高SQL执行速度?

当我们想更新某条数据的时候,难道是从磁盘中加载出来这条数据,更新后再持久化到磁盘中吗?

如果这样搞的话,那一条sql的执行过程可太慢了,因为对一个大磁盘文件的读写操作是要耗费几百万毫秒的

真实的执行过程是,当我们想更新或者读取某条数据的时候,会把对应的页加载到Buffer Pool缓冲池中(Buffer Pool本质上就是一块连续的内存空间)

默认为128m,当然为了提高系统的并发度,你可以把这个值设大一点

之所以加载页到Buffer Pool中,是考虑到当你使用这个页的数据时,这个页的其他数据使用到的概率页很大,随机IO的耗时很长,所以多加载一点数据到Buffer Pool

Buffer Pool的数据结构是怎样的?

Buffer Pool中主要分为2部分,缓存页和描述数据,MySQL从磁盘加载的数据页会放入缓存页中

对于每个缓存页都有对应的描述信息,比如数据页所属于表空间,数据页的编号等

Buffer Pool中的描述数据大概相当于缓存页大小的5%左右,这部分内存是不包含在Buffer Pool中的

当更新数据的时候,如果对应的页在Buffer Pool中,则直接更新Buffer Pool中的页即可,对应的页不在Buffer Pool中时,才会从磁盘加载对应的页到Buffer Pool,然后再更新,此时Buffer Pool中的页和磁盘中的页数据是不一致的,被称为脏页。这些脏页是要被刷回到磁盘中的

这些脏页是多会刷回到磁盘中的? 有如下几个时机

Buffer Pool不够用了,要给新加载的页腾位置了,所以会利用改进的后的LRU算法,将一些脏页刷回磁盘后台线程会在MySQL不繁忙的时候,将脏页刷到磁盘中redolog写满时(redolog的作用后面会提到)数据库关闭时会将所有脏页刷回到磁盘

这样搞,效率是不是高很多了?

当需要更新的数据所在的页已经在Buffer Pool中时,只需要操作内存即可,效率不是一般的高

我们怎么知道哪些缓存页是空闲的?

MySQL为Buffer Pool设计了一个free链表,它是一个双向链表,每个节点就是一个空闲缓存页的描述数据

我们如何知道缓存页是否被加载到内存了?

很简单啊,建立一个哈希表不就行了,key为表空间号+页号,value为对应的缓存页

当把数据页读取到缓存页的时候,对应的描述数据会从free链表放到flush链表

当不停的把磁盘上的数据页加载到缓存页,free链表不停的移除空闲缓存页,当free链表上没有空闲缓存页,当你还要加载数据页到缓存页时,该怎么办呢?

如果要淘汰一些数据,该淘汰谁呢?

引入LRU链表来判断哪些缓存页是不常用的?

缓存淘汰策略在很多中间件中会被用到,其中用的最多的就是LRU算法,当每访问一个缓存页的时候就把缓存页移到链表的头部

我们只需要把链表尾部的缓存页刷到内存中,然后加载新的数据页即可。

这样的方式看似很完美,但是在实际运行过程中会存在巨大的隐患

首先就是mysql的预读,

哪些情况会触发MySQL的预读

当发生全表扫描的时候(比如 select * from users),会导致表里的数据页都加载到 Buffer Pool 中去。这样有可能导致LRU链表前面一大串数据页都是全表扫描加载进来的数据页,但是如果这次全表扫描过后后续几乎没用到这个表里面的数据呢?

这样就会导致经常被扫描的缓存页被淘汰了,留下的都是全表扫描加载进来的缓存页

为了解决这个问题,LRU链表改进了一下,采用了冷热分离的思想。

即LRU链表会被拆分为2部分,一部分是冷数据,一部分是热数据

改进后的链表是如何工作的?

当数据页第一次被加载到缓存的时候,缓存页会被放到冷数据区域的链表头部。

那么冷数据区的缓存页多会放到热数据区呢?

你可能会想,当冷数据区的缓存页再次被访问时,就放到热数据区可以不?

mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.02 sec)

当多线程访问Buffer Pool中的各种链表时,需要加锁保证线程安全,影响请求的处理速度,此时我们就可以将Buffer Pool分为多个,多线程访问事不会互相影响,提高了请求的处理速度

在MySQL 5.7.5之前,Buffer Pool不能动态扩展,动态扩展。为了增加动态扩展就增加了chunk机制,有兴趣的小伙伴可以看看其他资料,就不多做分析了

Buffer Pool的相关参数

学习了这么多理论知识,那么Buffer Pool应该调多大呢?

执行如下命令可以得到Buffer Pool的大小,名字,以及chunk的大小

SHOW VARIABLES LIKE '%innodb_buffer%'

innodb_buffer_pool_size的单位是字节,我们转成MB来看一下,默认是128M

-- 128m
SELECT @@innodb_buffer_pool_size / 1024 / 1024

执行如下命令可以得到buffer_pool的当前使用状态

SHOW STATUS LIKE '%buffer_pool%';

我们挑一些重要的参数来分析一下

Buffer Pool 读缓存命中率:

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / (Innodb_buffer_pool_read_requests) *100%

Buffer Pool 脏页比率:

Innodb_buffer_pool_pages_dirty / (Innodb_buffer_pool_pages_data)*100%

Buffer Pool 使用率:

innodb_buffer_pool_pages_data / ( innodb_buffer_pool_pages_data + innodb_buffer_pool_pages_free ) * 100%

缓存命中率比较低可以增大Buffer Pool的大小

使用率比较高时可以增大Buffer Pool的大小

你也可以执行如下命令获取一些关于Buffer Pool的其他参数,本篇文章就不多做介绍了

show engine innodb status;

参考博客

[1]https://www.cnblogs.com/FengGeBlog/p/10283095.html
[2]https://m.starcto.com/mysql/128.html

到此这篇关于MySQL Buffer Pool如何提高页的访问速度的文章就介绍到这了,更多相关MySQL Buffer Pool访问速度内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯