一文带你了解MySQL之InnoDB_Buffer_Pool

前言

通过前边的学习我们知道，对于使用InnoDB作为存储引擎的表来说，不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是InnoDB对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。但是各位也都知道，磁盘的速度慢的跟乌龟一样，怎么能配得上“快如风，疾如电”的CPU呢？所以InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了。

一、InnoDB架构

如图：

我们可以看出，InnoDB分为了内存结构和磁盘结构两大部分，Buffer Pool是内存结构中最为重要且核心的组件，今天就来一起了解一下Buffer Pool的工作原理。我们可以看到，内存结构中不仅有Buffer Pool，还有Adaptive Hash Index、Log Buffer、Change Buffer等等组件，后面会单独开辟的文章单独进行讲解

官档地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html
点击此处跳转

二、Buffer Pool

2.1 什么是缓冲池

官档介绍： 缓冲池是InnoDB在访问表和索引数据时缓存的主内存区域。缓冲池允许直接从内存访问频繁使用的数据，这加快了处理速度

从字面意思理解就是： MySQL InnoDB缓冲池，既然是缓冲池，那么它的作用就是缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。

专业人士介绍： Buffer Pool是MySQL中最重要的内存组件，介于外部系统和存储引擎之间的一个缓存区，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘 IO，加快处理速度。在缓冲池中不仅缓存了索引页和数据页，还包含了 undo 页、插入缓存(insert page)、自适应哈希索引以及 InnoDB 的锁信息等。

2.2 缓冲池大小的设置

缓冲池的配置通过变量innodb_buffer_pool_size来设置，通常它的大小占用内存60%-80%，MySQL默认是134217728字节，也就是128M。

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

我们可以通过set persist命令设置缓冲池的大小

[root@mysql2 ~]# free -h
              total        used        free      shared  buff/cache   available
Mem:            15G        1.1G         12G         13M        1.4G         14G
Swap:           15G          0B         15G

15X0.7X1024X1024X1024=11274289152

mysql> set persist innodb_buffer_pool_size=11274289152;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 11274289152 |
+-------------------------+-------------+
1 row in set (0.01 sec)

那我们如何判断缓冲池的大小是否合理，可以通过：

• show engine innodb status 如果Free buffers值为0，表示缓存池设置过小

• show status like '%buffer_pool_wait%' 如果value值大于0，表示缓存池设置过小

mysql> show engine innodb status \G;
**********忽略部分**********
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Total large memory allocated表示Buffer Pool向操作系统申请的连续内存空间大小，包括全部控制块、缓存页、以及碎片的字节大小

Dictionary memory allocated 1290731
Dictionary memory allocated表示数据字典信息分配的内存空间的字节大小，注意这个内存空间和Buffer Pool没啥关系，不包括在Total memory allocated中

Buffer pool size   688067
Buffer pool size 表示该Buffer Pool可以容纳多少缓存页，注意，单位是页！

Free buffers       680866
Free buffers表示当前Buffer Pool还有多少空闲缓存页，也就是free链表中还有多少个节点

Database pages     7194
Database pages表示LRU链表中的页的数量，包含young和old两个区域的节点数量

Old database pages 2650
Old database pages表示LRU链表old区域的节点数量

Modified db pages  0
Modified db pages表示脏页数量，也就是flush链表中节点的数量。

Pending reads      0
Pending reads表示正在等待从磁盘上加载到Buffer Pool中的页面数量，需要注意的s当准备从磁盘中加载某个页面时，会先为这个页面在Buffer Pool中分配一个缓存页以及它对应的控制块，然后把这个控制块添加到LRU的old区域的头部，但是这个时候真正的磁盘页并没有被加载进来，Pending reads的值会跟着加1。

Pending writes: LRU 0, flush list 0, single page 0
Pending writes表示即将从LRU、flush链表和单个页面刷新到磁盘中的页面数量


Pages made young 23621, not young 178247
Pages made young表示LRU链表中曾经从old区域移动到young区域头部的节点数量
not young表示在将innodb_old_blocks_time设置的值大于0时，首次访问或者后续访问某个处在old区域的节点时由于不符合时间间隔的限制而不能将其移动到young区域头部时，Page made not young的值会加1。

0.00 youngs/s, 0.00 non-youngs/s
youngs/s表示每秒从old区域被移动到young区域头部的节点数量
non-youngs/s表示每秒由于不满足时间限制而不能从old区域移动到young区域头部的节点数量

Pages read 7056, created 29120, written 45996
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
表示读取，创建，写入了多少页。后边跟着读取、创建、写入的速率

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Buffer pool hit rate表示在过去某段时间，平均访问1000次页面，有多少次该页面已经被缓存到Buffer Pool了
young-making rate表示在过去某段时间，平均访问1000次页面，有多少次访问使页面移动到young区域的头部了
not (young-making rate)表示在过去某段时间，平均访问1000次页面，有多少次访问没有使页面移动到young区域的头部

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead表示每秒读入的pages
evicted without access表示每秒读出的pages
Random read ahead表示随机读人的pages

LRU len: 7194, unzip_LRU len: 0
LRU len表示LRU链表中节点的数量

I/O sum[5]:cur[0], unzip sum[0]:cur[0]
I/O sum表示最近50s读取磁盘页的总数
I/O cur表示现在正在读取的磁盘页数量
I/O unzip sum表示最近50s解压的页面数量
I/O unzip cur表示正在解压的页面数量

mysql> show status like '%buffer_pool_wait%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0     |
+------------------------------+-------+
1 row in set (0.00 sec)

或者通过分析InnoDB缓冲池的性能来验证。

可以使用以下公式计算InnoDB缓冲池性能：
Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。
例如，在我的服务器上，检查当前InnoDB缓冲池的性能

mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 839   |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
| Innodb_buffer_pool_read_requests      | 62567 |
| Innodb_buffer_pool_reads              | 3043  |
+---------------------------------------+-------+
5 rows in set (0.01 sec)

Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests*100 即 3043 /62567 *100=4.86

意味着InnoDB可以满足缓冲池本身的大部分请求。从磁盘完成读取的百分比非常小。因此无需增加innodb_buffer_pool_size值。

InnoDB buffer pool 命中率：

InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

此值低于99%，则可以考虑增加innodb_buffer_pool_size。

InnoDB缓冲池状态变量有哪些？

可以运行以下命令进行查看：show status like '%innodb_buffer_pool_pages%'

mysql> show status like '%innodb_buffer_pool_pages%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 4025   |
| Innodb_buffer_pool_pages_dirty   | 0      |
| Innodb_buffer_pool_pages_flushed | 215    |
| Innodb_buffer_pool_pages_free    | 684034 |
| Innodb_buffer_pool_pages_misc    | 69     |
| Innodb_buffer_pool_pages_total   | 688128 |
+----------------------------------+--------+
6 rows in set (0.01 sec)

说明：

• Innodb_buffer_pool_pages_dataInnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。 使用压缩表时，报告的Innodb_buffer_pool_pages_data值可能大于Innodb_buffer_pool_pages_total。

• Innodb_buffer_pool_pages_dirty显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量（脏页刷新）。

• Innodb_buffer_pool_pages_flushed表示从InnoDB缓冲池中刷新脏页的请求数。

• Innodb_buffer_pool_pages_free显示InnoDB缓冲池中的空闲页面

• Innodb_buffer_pool_pages_misc InnoDB缓冲池中的页面数量很多，因为它们已被分配用于管理开销，例如行锁或自适应哈希索引。此值也可以计算为Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data。

• Innodb_buffer_pool_pages_totalInnoDB缓冲池的总大小，以page为单位。

• innodb_buffer_pool_reads表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

• innodb_buffer_pool_read_requests它表示从内存中逻辑读取的请求数。

• innodb_buffer_pool_wait_free通常，对InnoDB缓冲池的写入发生在后台。 当InnoDB需要读取或创建页面并且没有可用的干净页面时，InnoDB首先刷新一些脏页并等待该操作完成。 此计数器计算这些等待的实例。 如果已正确设置innodb_buffer_pool_size，则此值应该很小。如果大于0，则表示InnoDb缓冲池太小。

• innodb_buffer_pool_write_request表示对缓冲池执行的写入次数。

2.3 缓冲池的管理

2.3.1 Buffer Pool的初始化

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着执行增删改查操作时，才会有磁盘上的数据页被缓存到 Buffer Pool 中。

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页的最前面都创建了一个内存大小一样的控制块，里面包括缓存页的表空间、页号、缓存页地址、链表节点等。

每一个控制块都对应一个缓存页，在分配控制块和缓存页后，剩余的空间不够一对控制块和缓存页的大小，就被称为碎片空间

2.3.2 如何管理空闲页

我们知道Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的，为了能够快速找到空闲的缓存页，可以使用链表结构。MySQL将空闲缓存页的控制块作为链表的节点，这个链表称为 Free 链表（空闲链表），如图

• Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息，头节点是一块单独申请的内存空间（约占40字节），并不在Buffer Pool的连续内存空间里
• Free 链表节点是一个一个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于 Free 链表节点都对应一个空闲的缓存页
• 每个控制块块里都有两个指针分别是：（pre）指向上一个节点，（next）指向下一个节点；而且还有一个（clt）数据页地址

buffer pool在寻找空闲数据页的时候直接用free链表可以直接找到。只要有一页数据空闲出来之后，直接把该数据页的地址追加到free链表即可。反之每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除

2.3.3 如何管理脏页

设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。

那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。

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

2.3.4 如何提高缓存命中率？

Buffer Pool 的大小是有限的，对于一些频繁访问的数据我们希望可以一直留在 Buffer Pool 中，而一些很少访问的数据希望可以在某些时机可以淘汰掉，从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还能保证常用数据留在 Buffer Pool 中。要实现这个，最容易想到的就是 LRU（Least recently used）算法。

该算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。

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

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

LRU 的实现过程如下：

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

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

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

• Free Page（空闲页），表示此页未被使用，位于 Free 链表；
• Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。
• Dirty Page（脏页），表示此页已被使用且已经被修改，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。

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

• 预读失效
• Buffer Pool 污染

2.3.5 预读失效

先来说说 MySQL 的预读机制

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

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

如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉。如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率。

怎么避免预读失效带来影响？

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

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

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

old 区域占整个 LRU 链表长度的比例可以通 innodb_old_blocks_pc 变量来设置，默认是 37

mysql> show variables like '%innodb_old_blocks_pc%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.01 sec)

代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37，划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。如果预读的页一直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数据。这个变量是可以根据我们实际情况修改

mysql> set persist innodb_old_blocks_pct = 40;
Query OK, 0 rows affected (0.00 sec)

举个例子

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

• 假如我们有两个11和12号页被预读了，这个页号会被插入到old区域头部，而old区域9和10号页给淘汰，如果9和10号页一直没有被访问到，那么就不会占用young区域的位置，而且会给young区域的数据更早被淘汰

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

2.3.6 Buffer Pool污染

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

注意， Buffer Pool 污染并不只是查询语句查询出了大量的数据才出现的问题，即使查询出来的结果集很小，也会造成 Buffer Pool 污染。比如，在一个数据量非常大的表，执行了这条语句：select * from t_user where name like '%a%'可能这个查询出来的结果就几条记录，但是由于这条语句会发生索引失效，所以这个查询过程是全表扫描的，接着会发生如下的过程：

• 从磁盘读到的页加入到 LRU 链表的 old 区域头部；
• 当从页里读取行记录时，也就是页被访问的时候，就要将该页放到 young 区域头部；
• 接下来拿行记录的 name 字段和字符串 ian 进行模糊匹配，如果符合条件，就加入到结果集里；
• 如此往复，直到扫描完表中的所有记录。

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

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

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

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

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

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

这个间隔时间是由 innodb_old_blocks_time 控制的,默认1000毫秒，也就是1秒

mysql> show variables like '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |
+------------------------+-------+
1 row in set (0.01 sec)

也就说，只有同时满足被访问与在 old 区域停留时间超过 1 秒两个条件，才会被插入到 young 区域头部，这样就解决了 Buffer Pool 污染的问题 ，这个变量是可以根据我们实际情况进行修改

mysql> set persist innodb_old_blocks_time  = 2000;
Query OK, 0 rows affected (0.01 sec)

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

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

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

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

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

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

• 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
• Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
• MySQL 认为空闲时，后台线程回定期将适量的脏页刷入到磁盘；
• MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

在我们开启了慢 SQL 监控后，如果你发现偶尔会出现一些用时稍长的 SQL，这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

后台有专门的线程每隔一段时间负责把脏页刷新到磁盘，这样可以不影响用户线程处理正常的请求。主要有两种刷新路径：

• 从LRU链表的冷数据中刷新一部分页面到磁盘。后台线程会定时从LRU链表尾部开始扫描一些页面，扫描的页面数量可以通过系统变量innodb_lru_scan_depth来指定，如果从里边儿发现脏页，会把它们刷新到磁盘。这种刷新页面的方式被称之为BUF_FLUSH_LRU。

• 从flush链表中刷新一部分页面到磁盘。后台线程也会定时从flush链表中刷新一部分页面到磁盘，刷新的速率取决于当时系统是不是很繁忙。这种刷新页面的方式被称之为BUF_FLUSH_LIST。

有时候后台线程刷新脏页的进度比较慢，导致用户线程在准备加载一个磁盘页到Buffer Pool时没有可用的缓存页，这时就会尝试看看LRU链表尾部有没有可以直接释放掉的未修改页面，如果没有的话会不得不将LRU链表尾部的一个脏页同步刷新到磁盘（和磁盘交互是很慢的，这会降低处理用户请求的速度）。这种刷新单个页面到磁盘中的刷新方式被称之为BUF_FLUSH_SINGLE_PAGE。

当然，有时候系统特别繁忙时，也可能出现用户线程批量的从flush链表中刷新脏页的情况，很显然在处理用户请求过程中去刷新脏页是一种严重降低处理速度的行为（毕竟磁盘的速度满的要死），这属于一种迫不得已的情况，不过这得放在后边唠叨redo⽇志的checkpoint时说了。

2.4 缓冲池的高并发

如果 InnoDB 存储引擎只有一个 Buffer Pool，当高并发时，多个请求进来，那么为了保证数据的一致性（缓存页、free 链表、flush 链表、lru 链表等多种操作），必须得给缓冲池加锁了，每一时刻只能有一个请求获得锁去操作 Buffer Pool，其他请求只能排队等待锁释放，那么此时 MySQL 的性能是有多么的低。

我们是可以通过修改变量innodb_buffer_pool_instances 给 MySQL 设置多个 Buffer Pool 来提升 MySQL 的并发能力。

innodb_buffer_pool_instances 是一个持久化只读系统变量，需要授予persist_ro_variables_admin（启用持久化只读系统变量）和system_variables_admin（启用修改或保留全局系统变量）的权限，

mysql> set persist_only innodb_buffer_pool_instances=4;
Query OK, 0 rows affected (0.01 sec)

修改完成后，我们需要重启mysql

mysql> show variables like '%innodb_buffer_pool_instances%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 4     |
+------------------------------+-------+
1 row in set (0.02 sec)

每个 Buffer Pool 负责管理着自己的控制块和缓存页，有自己独立一套 free 链表、flush 链表和 LRU链表

假设给 Buffer Pool 调整到 16 G，就是说变量innodb_buffer_pool_size 改为 17179869184，此时，MySQL 会为 Buffer Pool 申请一块大小为16G 的连续内存，然后分成 4块，接着将每一个 Buffer Pool 的数据都复制到对应的内存块里，最后再清空之前的内存区域。那这是相当耗费时间的操作

为了解决上面的问题，Buffer Pool 引入一个机制：chunk 机制

• 每个 Buffer Pool 其实是由多个 chunk 组成的。每个 chunk 的大小由参数 innodb_buffer_pool_chunk_size 控制，默认值是 128M。

mysql> show variables like '%innodb_buffer_pool_chunk_size%';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
+-------------------------------+-----------+
1 row in set (0.01 sec)

innodb_buffer_pool_chunk_size 这个变量如同innodb_buffer_pool_instances 一样，是一个持久化只读系统变量，修改完成后需要重启MySQL

mysql> set persist_only innodb_buffer_pool_chunk_size  = 132417728;
Query OK, 0 rows affected (0.00 sec)

• 每个 chunk 就是一系列的描述数据块和对应的缓存页。
• 每个 Buffer Pool 里的所有 chunk 共享一套 free、flush、lru 链表。

得益于 chunk 机制，通过增加 Buffer Pool 的chunk个数就能避免了上面说到的问题。当扩大 Buffer Pool 内存时，不再需要全部数据进行复制和粘贴，而是在原本的基础上进行增减内存。

下面举个例子，介绍一下 chunk 机制下，Buffer Pool 是如何动态调整大小的：

• 调整前 Buffer Pool 的总大小为 8G，调整后的 Buffer Pool 大小为 16 G。
• 由于 Buffer Pool 的实例数是不可以变的，所以是每个 Buffer Pool 增加 2G 的大小，此时只要给每个 Buffer Pool 申请 （2048M/128M）个chunk就行了，但是要注意的是，新增的每个 chunk 都是连续的128M内存。

缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。如果将缓冲池大小更改为不等于或等于innodb_buffer_pool_chunk_size **innodb_buffer_pool_instances的倍数的值，
则缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值

2.5 缓冲池数据预热（了解）

我们关闭数据库的时候，想将缓冲池中的数据保存到ib_Buffer_pool中，可以调整如下变量
innodb_buffer_pool_dump_pct：指定每个缓冲池最近使用的页面读取和转储的百分比。 范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。
innodb_buffer_pool_dump_at_shutdown：默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程。
innodb_buffer_pool_load_at_startup：默认启用。指定在MySQL服务器启动时，InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用

2.6 缓冲池的案例

2.6.1 大量的全表扫描

如果在业务中做了大量的全表扫描，那么你就可以将innodb_old_blocks_pct设置减小，增大innodb_old_blocks_time的时间，不让这些无用的查询数据进入old区域，尽量不让缓存再young 区域的有用的数据被立即刷掉。（这也是治标的方法，大量全表扫描就要优化sql和表索引结构了）

mysql> set persist innodb_old_blocks_time=4000;
Query OK, 0 rows affected (0.00 sec)

mysql> set persist innodb_old_blocks_pct=20;
Query OK, 0 rows affected (0.01 sec)

2.6.2 没有大量的全表扫描

如果在业务中没有做大量的全表扫描，那么你就可以将innodb_old_blocks_pct增大，减小innodb_old_blocks_time的时间，让有用的查询缓存数据尽量缓存在innodb_buffer_pool_size中，减小磁盘io，提高性能。

mysql> set persist innodb_old_blocks_time=1000;
Query OK, 0 rows affected (0.00 sec)

mysql> set persist innodb_old_blocks_pct=37;
Query OK, 0 rows affected (0.01 sec)

总结

今天理论的知识很多，下面简单做一下总结：

• 缓冲池（Buffer Pool）是一种常见的降低磁盘访问的机制；
• 缓冲池通常以页（Page）为单位缓存数据，OS的Page大小一般为4KB，MySQL的Page大小一般为16KB；
• Page可以分为Free Page（空闲页）、Clean Page（干净页）、Dirty Page（脏页）；
• 缓冲池中含有3个链表：LRU链表（LRU List）、Free链表（Free List）、Flush链表（Flush List），以及LRU链表和Flush链表的区别；
• 缓冲池常见的管理算法是LRU，Memcache、OS、MySQL的InnoDB存储引擎都使用了这种最近、最少使用原则算法（Least Rrecently Used）；
• MySQL的InnoDB存储引擎对普通的LRU进行了优化：
  • 将缓冲池分为New Sublist（新生代/Young）和Old Sublist（老生代/Old），入缓冲池的Page，优先从Midpoint进入Old Sublist，Page被访问，才进入New Sublist，以解决预读（Read-Ahead）失效的问题。
  • Page被访问，且在Old Sublist停留时间超过配置innodb_old_blocks_time阀值时，才进入New Sublist，以解决批量数据访问，大量数据淘汰的问题。

至此今天的学习就到此结束了，愿您成为坚不可摧的自己~~~
​

You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something - your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life

如果我的内容对你有帮助，请 点赞、评论、收藏，创作不易，大家的支持就是我坚持下去的动力