MySQL 实战

Buffer Pool

Buffer Pool参数

InnoDB Buffer Pool 就是 MySQL 的内存“磁盘缓存”，用于缓存表数据（Data Pages），索引（Index Pages），插入缓冲（Insert Buffer），undo 日志页，adaptive hash index 等结构

自我理解

Buffer Pool 是 InnoDB 存储引擎中的一块内存缓冲区，用于缓存数据库中的数据页（如表数据和索引页）。由于磁盘访问速度远慢于内存，若每次查询都直接从磁盘读取数据，性能会受到严重影响。

因此，InnoDB 在执行查询时会优先从 Buffer Pool 中查找所需数据：

• 如果命中（数据已在内存中），则直接从 Buffer Pool 读取，速度非常快；

• 如果未命中（数据不在内存中），则从磁盘读取数据页，并将其加载进 Buffer Pool，以便后续访问加速。

这种机制大大减少了磁盘 I/O 的次数，提高了数据库查询性能。只有在数据首次访问或缓存被淘汰的情况下，才需要访问磁盘。

Buffer Pool相关概念

参数名	含义
innodb_buffer_pool_size	Buffer Pool 总大小（最重要参数）
innodb_buffer_pool_instances	Buffer Pool 分成几个子池，提高并发
innodb_buffer_pool_chunk_size	每个内存 Chunk 的大小
chunk per instance	每个实例中包含多少个 Chunk

---

结构图

Buffer Pool
├── Instance 1
│   ├── Chunk 1 (128MB)
│   ├── Chunk 2 (128MB)
│   └── ... 共 18 个
├── Instance 2
│   └── ...
└── Instance 4

参数之间关系:Total Buffer Pool Size = chunk_size × chunks_per_instance × instances

例如：已知 chunk_size = 128MB、 instances = 4、 total_size = 9GB

解：9GB / 128MB = 72 个 chunk
72 / 4 = 18 个 chunk per instance

查询对应参数

SQL 语句	查询结果示例	参数名称	含义说明
SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024;	9（单位：GB）	innodb_buffer_pool_size	InnoDB 缓冲池总大小，用于缓存数据页、索引页等，提高访问率
SELECT @@innodb_buffer_pool_instances;	4	innodb_buffer_pool_instances	缓冲池实例个数，将大块内存划分为多个实例以提高并发性与可扩性
SELECT @@innodb_buffer_pool_chunk_size / 1024 / 1024 / 1024;	0.125（即128MB）	innodb_buffer_pool_chunk_size	每个内存 Chunk 的大小，Buffer Pool 动态扩容的基本单位，默认 128MB

参数设置

• 在线配置缓冲区的大小

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

注意：缓冲池大小必须等于 或 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances。更改这些变量设置需要重新启动服务器。

• 在MySQL 5.7及之前的版本中，InnoDB Buffer Pool的默认大小是128MB。从MySQL 5.7.7版本开始，InnoDB引入了一个新的启动参数innodb_buffer_pool_size，允许用户根据服务器的内存资源来调整Buffer Pool的大小；默认的大小占用内存60%-80%。专用数据库服务器：如果服务器专门用于MySQL，可以设置为物理内存的80%左右

Buffer Pool的内存淘汰策略

LRU链表：改良的缓存淘汰机制

InnoDB 采用了改进版的 LRU(Least Recently Used) 算法来管理活跃页面，这不是传统的 LRU 算法，而是经过优化的分区 LRU 设计：

• Young 区域：存放频繁访问的"热"数据，约占 LRU 链表的 5/8

• Old 区域：存放不常访问的"冷"数据，约占 LRU 链表的 3/8

优化的LRU工作流程：

将 LRU 链表分为 young 区域和 old 区域，新读取的页面首先进入 old 区域的头部；只有当页面在 old 区域停留时间超过阈值(默认1秒)后被再次访问，才会晋升到 young 区域；对 young 区域的页面访问，会将其移动到链表头部。

解释：这种设计有点像现实生活中的"见习期"制度，新人不会立刻获得正式员工的所有待遇，需要经过一段时间的考察

页面如何在新老区域中流转

InnoDB LRU 算法的另一个关键创新是引入了复杂的页面晋升规则：

• 新页加载策略：从磁盘新读取的页不是直接放入Young List 头部，而是放入 Old List 的头部

• 时间窗口控制：新加载到 Old List 的页面需要在其中停留一段时间（由参数innodb_old_blocks_time控制，默认为1000毫秒）

• 条件晋升：只有当页面在"观察期"后被再次访问，才会被晋升到 Young List 的头部

• 页面老化：随着新页面不断加入，未被访问的页面会逐渐向链表尾部移动，最终可能被淘汰。

这种设计有效防止了全表扫描等操作导致的缓存污染，保证了频繁使用的页面能长时间留在 Buffer Pool 中。

一次性访问的大量数据页会停留在 Old List 中，而不会挤占 Young List 中的热点数据。随着新页面不断加入，这些一次性访问的页面最终会从缓存中淘汰。

解释：可以把这比喻为"试用期"机制——新员工不会立刻获得正式工位，而是要经过一段时间的考察，证明自己确实有价值，才能获得"新生代"的位置。

Buffer Pool中脏页刷盘

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

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

Buffer Pool的应用

分析数据库的Buffer Pool信息

✅ 一、查询 MySQL InnoDB 引擎中各个 Buffer Pool 实例的运行状态统计信息

SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;
SELECT POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES,OLD_DATABASE_PAGES,MODIFIED_DATABASE_PAGES FROM information_schema.INNODB_BUFFER_POOL_STATS;

✅ 二、常配合分析的字段（关键）

字段名	说明
POOL_ID	Buffer Pool 实例编号
POOL_SIZE	总页数（1页 = 16KB）
FREE_BUFFERS	当前未使用的空闲页数
DATABASE_PAGES	已被用来缓存数据（如表数据、索引）的页数
MODIFIED_DATABASE_PAGES	脏页数量（已修改但未写入磁盘）
OLD_DATABASE_PAGES	处于 LRU 尾部（即淘汰区域）的数据页

✅ 三、实例分析

SELECT POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES,OLD_DATABASE_PAGES,MODIFIED_DATABASE_PAGES FROM information_schema.INNODB_BUFFER_POOL_STATS;

🔍 分析结论：

• Buffer Pool 利用率：

使用页数 = POOL_SIZE - FREE_BUFFERS

使用率 = 使用页数 ÷ POOL_SIZE

示例：

实例 0: (160226 - 119327) / 160226 ≈ 25.5%

实例 1: (160225 - 119328) / 160225 ≈ 25.5%

分析：当前 Buffer Pool 利用率约为 25%，说明缓存还有大量空间。

• 数据页命中情况：

DATABASE_PAGES 占了几乎全部使用页，说明主要用于缓存数据页。

DATABASE_PAGES ≈ 使用页数(使用页数 = POOL_SIZE - FREE_BUFFERS)，说明未缓存太多其他内容（如索引页、undo 页等）。

• 脏页情况：

MODIFIED_DATABASE_PAGES = 0，表示当前没有脏页，可能：

当前未有大量写操作。

或者之前的脏页已被刷新（flush）到磁盘。

• 老年代页：

OLD_DATABASE_PAGES ≈ 15,000，用于衡量长期驻留内存的数据页；

说明 LRU 算法中部分页处于老年代，但比例不高（相对 48k+ 页）。

更新数据的完整过程（基于 InnoDB）

当你执行一条 UPDATE 语句时，InnoDB 的处理流程如下：

UPDATE user SET name = 'Tom' WHERE id = 1;

🔄 更新流程： 步骤 操作

步骤	操作
①	InnoDB 首先检查 Buffer Pool 中是否已缓存该数据页（通过主键/二级索引查找）
②	如果缓存中没有，就从磁盘中加载这个数据页到 Buffer Pool
③	在 Buffer Pool 中修改数据（页中的记录）
④	把修改标记为 脏页（Dirty Page），暂时不写磁盘
⑤	把这次更新写入 Redo Log（重做日志） 和 Undo Log（撤销日志）
⑥	当提交事务时，先刷 Redo Log 到磁盘（顺序写，效率高）
⑦	后台线程（Flush线程）再异步把 Buffer Pool 中的脏页刷回磁盘（延迟写）

BinLog 恢复日志

Binlog（Binary Log）是 MySQL 中以二进制形式记录所有涉及数据库修改操作的日志文件。它记录了诸如 INSERT、UPDATE、DELETE 等语句，以及表结构的变更，用于数据的复制、恢复和审计。

查看和配置 binlog

• 查看当前格式（下列表格的三种格式）

SHOW VARIABLES LIKE 'binlog_format';

格式	含义	特点说明
STATEMENT	基于 SQL 语句的格式（默认的旧模式）	日志记录的是执行的 SQL 语句
ROW	基于行的格式	日志记录的是每一行数据的变化
MIXED	混合模式：STATEMENT + ROW 的结合	MySQL 会根据情况自动选择最合适的格式

• 查看是否开启binlog

SHOW VARIABLES LIKE 'log_bin';

• 查看主库（Master）当前二进制日志（Binary Log）的状态

SHOW MASTER STATUS;

字段名	含义
File	当前正在写入的 binlog 文件名，如：mysql-bin.000017
Position	当前写入的位置偏移量（字节位置），从这个位置之后的新数据会记录进 binlog 文件
Binlog_Do_DB	设置了要包含在 binlog 的数据库（通过 binlog_do_db 参数）
Binlog_Ignore_DB	设置了不包含在 binlog 的数据库（通过 binlog_ignore_db 参数）
Executed_Gtid_Set	当前已执行的 GTID（全局事务 ID），用于 GTID 复制模式

• 查看主库（Master）上所有的二进制日志（Binary Logs）文件列表及其大小

show master logs;

字段名	含义
Log_name	二进制日志文件的名称。日志文件名通常以 mysql-bin. 为前缀，后跟六位递增编号，例如：mysql-bin.000001。这些文件按照顺序记录了数据库的变化操作。
File_size	日志文件的大小（以字节为单位），反映该 binlog 文件当前的存储空间占用情况。

• 指定 binlog 保留的时间（单位：秒），超过时间后旧的 binlog 会被自动清理

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

• 查看当前二进制日志文件和文件位置

show variables like 'log_%';

• 配置方式（my.cnf）

[mysqld]
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days=7

binlog恢复数据

• 将数据备份

• 将binlog 内容解析为sql

# window 安装应该在cmd 页面进行操作，而不需要进入mysql中
# 先进入binlog的存放位置，然后执行命令
mysqlbinlog --no-defaults binlog.000107 > binlog_dump_7_15.sql

# 参数说明
# --no-defaults: 忽略配置文件，避免权限问题
# --base64-output=DECODE-ROWS:解码基于 row 的 binlog
# --verbose:输出更多解析信息

• 通过position恢复

# 根据SHOW MASTER STATUS定位到日志文件
SHOW MASTER STATUS 

# 查看事件
show binlog events in 'binlog.000107';

# 恢复数据，利用事件开始结束位置进行恢复
mysqlbinlog --start-position=1191 --stop-position=1413 binlog.000107 | mysql -uroot -p123456

注意: 此命令是使用终端，进入MySQL时的路径下的 data 目录下执行