快上网专注成都网站设计 成都网站制作 成都网站建设
成都网站建设公司服务热线：028-86922220

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后，网站问题一站解决

MySQL中Innodb存储引擎的BufferPool详解-创新互联

MYSQL的InnoDB存储引擎为了提高性能，减少磁盘IO，而设计了缓冲池（Buffer Pool）。结构图如下：

创新互联长期为成百上千客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为滨湖企业提供专业的网站设计、成都网站建设，滨湖网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。5e346571f051480a86d3ccafc8cd7876.png

Buffer Pool

什么是Buffer Pool

Buffer Pool即缓冲池（简称BP），BP以Page页为单位，缓存最热的数据页(data page)与索引页(index page)，Page页默认大小16K，BP的底层采用链表数据结构管理Page。

InnoDB 会把存储数据划分为若干个页，磁盘与内存交互是以页为基本单位，一页默认为16kB。因此，Buffer Pool 是以页为划分的。

在MYSQL 启动时，InnoDB会为 Buffer Pool 申请一块连续的内存空间，然后按照16kB大小划分层一个个页，Buffer Pool 中的页就叫做缓存页。此时这些缓存页是空闲的，随着程序的运行才会慢慢的把磁盘上的页数据缓存到Buffer Pool 中。所以，在MYSQL启动的时候，我们会发现使用了很大一块虚拟内存空间，而物理内存空间使用不多，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。

为什么要有Buffer Pool

有Buffer Pool 最主要是为了提高数据库的读写性能。那么它是怎么样提高读写性能呢：

1. 当读取数据时，如果数据存在于  Buffer Pool 中，客户端就会直接读取  Buffer Pool 中的数据，否则再去磁盘中读取

2. 当修改数据时，首先是修改  Buffer Pool  中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘

Buffer Pool的大小

Buffer Pool是MYSQL启动时向系统申请的一块连续的内存，默认大小为128MB。但是也可以通过innodb_buffer_pool_size 参数控制，一般可以设置为物理内存的60%~80%。可以通过下面指令查看：

show variables like 'innodb_buffer%';

Buffer Pool缓存什么数据

Buffer Pool 是 InnoDB的数据缓存， 除了缓存「索引页」和「数据页」，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等。如下图：

6c0344f76d1f4318b20791744d9f5017.png

Buffer Pool存储数据

这里有个疑问，那么我们查询一条记录，该缓存多少数据呢？

答案：我们应该缓存一页的数据，即16kB。这是因为当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，因为，通过索引只能定位到磁盘中的页，而不能定位到页中的一条记录。将页加载到 Buffer Pool 后，再通过页里的页目录去定位到某条具体的记录。这里提到的页到底长什么样和怎么样进行索引的，可以阅读MYSQL索引数据结构B+树文章。

Buffer Pool的控制块

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。

控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页，如下图

3c767c03444eec1d9b29bdbb62ee3873.png

控制块与数据页的对应关系

上图展示了控制块与数据页的对应关系，可以看到在控制块和数据页之间有一个碎片空间。

为什么会有碎片空间呢？

上面说到，数据页大小为16KB，控制块大概为800字节，当我们划分好所有的控制块与数据页后，可能会有剩余的空间不够一对控制块和缓存页的大小，这部分就是多余的碎片空间。如果把 Buffer Pool 的大小设置的刚刚好的话，也可能不会产生碎片。

Buffer Pool的管理

Buffer Pool里有三个链表：LRU链表，free链表，flush链表，InnoDB正是通过这三个链表的使用来控制数据页的更新与淘汰的。

Buffer Pool的初始化

当启动 Mysql 服务器的时候，需要完成对 Buffer Pool 的初始化过程，即根据innodb_buffer_pool_size大小分配 Buffer Pool 的内存空间（注意这内存空间会比innodb_buffer_pool_size大小大一些，因为里面还要存放每个缓存页的控制块），把它划分为若干对控制块和缓存页。但是此时并没有真正的磁盘页被缓存到 Buffer Pool 中，之后随着程序的运行，会不断的有磁盘上的页被缓存到 Buffer Pool 中。

Free 链表

在Buffer Pool的初始化的时候，我们得到一些空闲的页，而使用了链表结构，将空闲缓存页的「控制块」作为链表的节点，一个一个串起来，这个链表称为 Free 链表（空闲链表）。如下图：

568a8ee2174c4d53a4b962b8a28beabf.png

图解析说明：

1. Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息

2. 头节点是一块单独申请的内存空间（约占40字节），并不在Buffer Pool的连续内存空间里

3. Free 链表节点是一个一个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于 Free 链表节点都对应一个空闲的缓存页

4. 每个控制块块里都有两个指针分别是：（pre）指向上一个节点，（next）指向下一个节点；而且还有一个（clt）数据页地址

所以， 有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free 链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除。

如何确定数据页是否被缓存？

在数据库中提供有一个数据页缓存哈希表，以表空间号+数据页号作为key，缓存页控制块的地址作为value，它的格式如下：

{表空间号+数据页号:控制块的地址}

当我们使用某个数据页时，先会在数据页缓存哈希表中查找，如果找到那就是缓存中，反正亦然。

有了数据页缓存哈希表之后，那么一条语句大致执行过程就是：

  • 通过sql语句中的数据库名和表名可以知道要加载的数据页处于哪个表空间。
  • 根据表空间号，表名称本身通过一致性算法得到索引根节点数据页号。
  • 进而根据根节点数据页号，找到下一层的数据页，可以从数据页缓存哈希表得到对应缓存页地址。
  • 通过缓存页地址就可以在Buffer Pool池中定位到缓存页。

注意：上面说的一致性哈希算法「指在数据字典中【根节点的页号，不是当前查找的数据的数据页号】」，当我们得到根节点页号后，通过B+tree一层一层往下找，在找下一层之前会通过数据缓存哈希表去buffer pool里面看看这个层的数据页存不存在，不存在则去磁盘加载

Flush 链表

设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。而为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。如下：

61168f9958e647378804e8937c31be5d.png

Flush 链表

有了 Flush 链表后，后台线程就可以遍历 Flush 链表，将脏页写入到磁盘。

LRU 链表

Buffer pool 作为一个innodb自带的一个缓存池，数据的读写都是buffer pool中进行的，操作的都是Buffer pool中的数据页，但是Buffer Pool 的大小是有限的（默认128MB），所以对于一些频繁访问的数据是希望能够一直留在 Buffer Pool 中，而一些访问比较少的数据，我们希望能将它够释放掉，空出数据页缓存其他数据。基于此，InnoBD采用了LRU（Least recently used）算法，将频繁访问的数据放在链表头部，而不怎么访问的数据链表末尾，空间不够的时候就从尾部开始淘汰，从而腾出空间。

简单的 LRU 算法的实现思路是这样的：

  • 当访问的页在 Buffer Pool  里，就直接把该页对应的 LRU 链表节点移动到链表的头部。

  • 当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰  LRU 链表末尾的节点。

假如 LRU 链表长度为 5，LRU 链表从左到右有 1，2，3，4，5 的页，如下图：

2e659bffe36349659d075fc52fc07102.png

LRU 的实现过程如下：

  • 假如我们要访问2号页数据，因为2号页在Buffer Pool 中，所以会把2号页移动到头部即可

  • 假如我们要访问6号页数据，但是6号页不在Buffer Pool 中，所以会淘汰了5号页，然后在头部加入6号页数据

Buffer Pool的管理的总结

到这里我们可以知道，Buffer Pool 里有三种页和链表来管理数据。如下：

656a094327af4a659ebde9433448eb30.png

图解析：

  • Free Page（空闲页），表示此页未被使用，位于 Free 链表；

  • Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。

  • Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表

但是，MYSQL并没有使用简单的LRU算法，因为它无法解决下面问题：

  • 预读失效；

  • Buffer Pool  污染

预读失效

先来说说 MySQL 的预读机制。程序是有空间局部性的，靠近当前被访问数据的数据，在未来很大概率会被访问到。所以，MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。

但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，这个就是预读失效。

如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当  Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉。

如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率。

怎么解决预读失效而导致缓存命中率降低的问题？

我们不能因为害怕预读失效，而将预读机制去掉，大部分情况下，局部性原理还是成立的。

要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。

那到底怎么才能避免呢？

MySQL 是这样做的，它改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域 和 young 区域。

young 区域在 LRU 链表的前半部分，old 区域则是在后半部分，如下图：

83ab2a10134a2676d099ac5105c659dd.png

old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pc参数来设置，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37。

划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。

例子，假设有一个长度为 10 的 LRU 链表，其中 young 区域占比 70 %，old 区域占比 20 %。如下：

6cbea21f5ec54a54badc839bb50e4473.png

过程说明：

  • 假如我们有一个15号页被预读了，这个页号会被插入到old区域头部，而old区域10号页给淘汰

  • 如果15号页一直没有被访问到，那么就不会占用young区域的位置，而且会给young区域的数据更早被淘汰

  • 如果 15 号页被预读后，立刻被访问了，那么就会将它插入到 young 区域的头部，young 区域末尾的页（7号），会被挤到 old 区域，作为 old 区域的头部，这个过程并不会有页被淘汰。

如果 15 号页被预读后，立刻被访问了，那么就会将它插入到 young 区域的头部，young 区域末尾的页（7号），会被挤到 old 区域，作为 old 区域的头部，这个过程并不会有页被淘汰。

Buffer Pool  污染

当某一个 SQL 语句扫描了大量的数据时，在  Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool  污染。

注意， Buffer Pool  污染并不只是查询语句查询出了大量的数据才出现的问题，即使查询出来的结果集很小，也会造成 Buffer Pool  污染。

比如，在一个数据量非常大的表，执行了这条语句：

select * from t_user where name like "%ian%";

可能这个查询出来的结果就几条记录，但是由于这条语句会发生索引失效，所以这个查询过程是全表扫描的，接着会发生如下的过程：

  • 从磁盘读到的页加入到 LRU 链表的 old 区域头部；

  • 当从页里读取行记录时，也就是页被访问的时候，就要将该页放到 young 区域头部；

  • 接下来拿行记录的 name 字段和字符串 ian 进行模糊匹配，如果符合条件，就加入到结果集里；

  • 如此往复，直到扫描完表中的所有记录。

经过这一番折腾，原本 young 区域的热点数据都会被替换掉。

举个例子，假设需要批量扫描：21，22，23，24，25 这五个页，这些页都会被逐一访问（读取页里的记录）。

e3cdf9da5a1d226a812e05094869f3c9.png

在批量访问这些数据的时候，会被逐一插入到 young 区域头部。

3f8068916f52e5a4d9d4e56b9b727122.png

可以看到，原本在 young 区域的热点数据 6 和 7 号页都被淘汰了，这就是  Buffer Pool  污染的问题。

怎么解决出现   Buffer Pool  污染而导致缓存命中率下降的问题？

像前面这种全表扫描的查询，很多缓冲页其实只会被访问一次，但是它却只因为被访问了一次而进入到 young 区域，从而导致热点数据被替换了。

LRU 链表中 young 区域就是热点数据，只要我们提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。

MySQL 是这样做的，进入到 young 区域条件增加了一个停留在 old 区域的时间判断。

具体是这样做的，在对某个处在 old 区域的缓存页进行第一次访问时，就在它对应的控制块中记录下来这个访问时间：

  • 如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；

  • 如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

这个间隔时间是由 innodb_old_blocks_time控制的，默认是 1000 ms。

也就说，只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool  污染的问题 。

另外，MySQL 针对 young 区域其实做了一个优化，为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会。

脏页什么时候会被刷入磁盘？

引入了 Buffer Pool  后，当修改数据时，首先是修改  Buffer Pool  中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。

因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。

可能大家担心，如果在脏页还没有来得及刷入到磁盘时，MySQL 宕机了，不就丢失数据了吗？

这个不用担心，InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

下面几种情况会触发脏页的刷新：

  • 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；

  • Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；

  • MySQL 认为空闲时，后台线程回定期将适量的脏页刷入到磁盘；

  • MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

在我们开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

Buffer Pool的配置

我们先来理解一下一个配置项：innodb_buffer_pool_chunk_size

「innodb_buffer_pool_chunk_size」

  • 默认值128MB。可以按照1MB的单位进行增加或减小。
  • 可以简单的理解成是Buffer Pool的总大小增加或缩小最小单位。

「innodb_buffer_pool_size的调整」

「Buffer Pool的总大小，必须是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数」。

当innodb_buffer_pool_size不等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数时，服务器会自动把innodb_buffer_pool_size的值调整为【innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances】结果的整数倍

如果配置

# buffer_pool最小单位为128MB
innodb_buffer_pool_chunk_size=128MB
# Buffer Pool实例的个数为16
innodb_buffer_pool_instances=16
# buffer_pool总大小为3GB
innodb_buffer_pool_size=3GB

由于

innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances =128MB * 16 = 2GB

而2GB 不等于 innodb_buffer_pool_size=3GB

则InnoDB会调整

# InnoDB会调整buffer_pool总大小为4GB
innodb_buffer_pool_size = 4GB

「innodb_buffer_pool_chunk_size的调整」

在服务启动的时候，会进行如下计算，并判断结果调整innodb_buffer_pool_chunk_size的大小：

如果不等式成立：

「innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances >innodb_buffer_pool_size」

则修改：

「innodb_buffer_pool_chunk_size = innodb_buffer_pool_size/innodb_buffer_pool_instances」

例如：如果配置

# buffer_pool最小单位为128MB
innodb_buffer_pool_chunk_size=256MB
# Buffer Pool实例的个数为16
innodb_buffer_pool_instances=16
# buffer_pool总大小为3GB
innodb_buffer_pool_size=3GB

由于

innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances =256MB * 16 = 4GB

而4GB 大于 innodb_buffer_pool_size=3GB

则InnoDB会调整

# InnoDB会调整innodb_buffer_pool_chunk_size的大小为192MB
innodb_buffer_pool_chunk_size = innodb_buffer_pool_size / innodb_buffer_pool_instances = 3GB / 16 = 192MB

你是否还在寻找稳定的海外服务器提供商？创新互联www.cdcxhl.cn海外机房具备T级流量清洗系统配攻击溯源，准确流量调度确保服务器高可用性，企业级服务器适合批量采购，新人活动首月15元起，快前往官网查看详情吧


网站名称：MySQL中Innodb存储引擎的BufferPool详解-创新互联
网站路径：http://6mz.cn/article/cedjch.html

其他资讯