Mysql之buffer pool

什么是buffer pool？

缓冲池，缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。

磁盘是按页读取，一次至少读取一页数据(一般是4K)。数据访问通常都遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据，这就是所谓的“局部性原理”，它表明提前加载是有效的，确实能够减少磁盘IO。

InnoDB的缓冲池一般也是按页读取数据，存储结构如下：

• 新老生代收尾相连，很好的解决了“预读失败”的问题
  • 首次读取从老生代头部插入，如果一直不被再次读取，即预读失败，按照LRU淘汰策略，会比新生代数据更早淘汰出缓冲池
  • 如果老生代数据被再次读取，会被加入新生代头部。如果后续不被使用，按照LRU淘汰策略，向后逐步移动到老生代直到尾部被移除
• 老生代停留时间窗口，很好的解决了缓存池污染的问题

select * from user where name like "%shenjian%";

-- 不满足最左匹配，不能命中索引，必须全表扫描，需要访问大量数据页，步骤如下： 
-- 1. 把页加载到缓冲池，插入到老生代头部
-- 2. 从页中读取相关的row进行过滤，这时会把数据插入新生代头部
-- 综上，所有的数据都会加载到新生代头部，且只会访问一次，真正的热数据被大量换出

-- 如果加上“老生代停留时间窗口”T，只有满足“被访问”且“在老生代停留时间”大于T，才会放入新生代头部

参数设置

-- 不同版本参数有变化，以下基于8.0.26
mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
-- 缓冲池增加或减少innodb_buffer_pool_size时，操作以块（chunk）形式执行 
| innodb_buffer_pool_chunk_size       | 134217728      | -- 128M
-- 在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面，以便在下次重新启动时缩短预热过程
| innodb_buffer_pool_dump_at_shutdown | ON             | 
-- 立刻记录在InnoDB缓冲池中缓存的页面
| innodb_buffer_pool_dump_now         | OFF            |
-- 按比例持久化每个缓冲池实例最近使用的页面，例如：每个缓冲池100个page，默认dump每个缓冲池最近使用的25个page
| innodb_buffer_pool_dump_pct         | 25             |
-- 缓冲池中的热数据持久化的文件名，默认文件名为ib_buffer_pool，位于datadir下，默认basedir/data下
| innodb_buffer_pool_filename         | ib_buffer_pool |
-- 可以设置为OFF，用于将innodb buffer pool从coredump中排除，用于减小coredump的体积
| innodb_buffer_pool_in_core_file     | ON             |
-- 缓冲池实例数，可提高并发性能。innodb_buffer_pool_size大于1G时生效，因此，建议每个不小于1GB
| innodb_buffer_pool_instances        | 1              |
-- 中断由innodb_buffer_pool_load_at_startup或innodb_buffer_pool_load_now触发的缓冲池内容恢复过程
| innodb_buffer_pool_load_abort       | OFF            |
-- MySQL服务器启动时，通过加载先前保存的数据实现自动预热，通常与innodb_buffer_pool_dump_at_shutdown结合使用
| innodb_buffer_pool_load_at_startup  | ON            |
-- 立即通过加载一组数据页面来加热缓冲池，通常与innodb_buffer_pool_dump_now一起使用
| innodb_buffer_pool_load_now         | OFF            |
-- 1.缓冲池的大小，允许动态调整，必须是：innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数，如果不是自动调整
-- 2.建议调大这个参数，在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的80%
| innodb_buffer_pool_size             | 134217728      | -- 128M 
+-------------------------------------+----------------+


mysql> show variables like '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
-- 老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37
| innodb_old_blocks_pct  | 37    |
-- 老生代停留时间窗口，默认1s，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部
| innodb_old_blocks_time | 1000  | 
+------------------------+-------+

change buffer

毫无疑问，对于读请求，缓冲池能够减少磁盘IO，提升性能。问题来了，那写请求呢？

change buffer：在非唯一普通索引页(non-unique secondary index page)不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术，降低写操作的磁盘IO，提升数据库性能。

修改页在缓冲池内	修改页不在缓冲池内	修改页不在缓冲池内-change buffer优化

根据以上三幅对比图，当修改页不在缓冲池内时，使用change buffer可以减少一次磁盘读取操作，与修改页在缓冲池近似

如果此时有请求查询索引页40的数据，如何处理？

• 载入索引页，缓冲池未命中，这次磁盘IO不可避免
• 从写缓冲读取相关信息
• 恢复索引页，放到缓冲池LRU里

什么时候才会触发写缓冲数据合并？

• 数据页被访问
• 数据库空闲，后台线程触发
• 数据库缓冲池不够用
• 数据库正常关闭时
• redo log写满时

写缓冲机制适用场景

非唯一索引且写多读少

参数设置

mysql> show variables like '%innodb_change_buffer_%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
-- 配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%
| innodb_change_buffer_max_size | 25    |
-- 配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等
| innodb_change_buffering       | all   |
+-------------------------------+-------+

change buffer 与 redo log

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_k` (`k`) USING BTREE
) ENGINE=InnoDB;

insert into t(id,k) values(id1,k1),(id2,k2)	select * from t where k in (k1, k2)

• redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写）
• change buffer 主要节省的则是随机读磁盘的 IO 消耗

脏页、干净页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

当MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush），场景如下：

• redo log写满了，尽量避免，此种情况，系统不能再接受更新（此处只是触发了buffer pool的flush，redo log没有能力落盘）
• 系统内存不足，当需要新的内存页，淘汰数据页之前，需要把脏页写到磁盘
• 系统空闲
• Mysql正常关闭

脏页刷新需要考虑的因素：

• 脏页比例： innodb_max_dirty_pages_pct，默认75%
• 写盘速度：innodb_io_capacity