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

如何提高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部分，一部分是冷数据，一部分是热数据

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

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

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

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

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

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

Buffer Pool的相关参数

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

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

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

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

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

• Innodb_buffer_pool_read_requests：读的请求次数
• Innodb_buffer_pool_reads：从物理磁盘中读取数据的次数
• Innodb_buffer_pool_pages_data：有数据的缓存页
• Innodb_buffer_pool_pages_free：空闲缓存页
• Innodb_buffer_pool_pages_total：总共的缓存页

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的其他参数，本篇文章就不多做介绍了

参考博客

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

到此这篇关于MySQL Buffer Pool如何提高页的访问速度的文章就介绍到这了