MySQLNotes

第 22 章：Buffer Pool 与存储引擎

zjc 于 2026-01-22 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 Buffer Pool 是 InnoDB 的内存缓存区，也是 OLTP 性能的核心。数据页、索引页、Undo 页、Change Buffer 和自适应哈希都在这里协作，决定了请求是否需要访问磁盘。

22.1 InnoDB 磁盘结构回顾

InnoDB 逻辑对象包括：

System Tablespace：系统元数据、Change Buffer、Doublewrite 等历史区域
File-Per-Table Tablespace：每张表一个 .ibd 文件
General Tablespace：多表共享表空间
Undo Tablespace：回滚和 MVCC 版本
Temporary Tablespace：临时表和内部排序聚合
Redo Log：崩溃恢复日志

查看：

SELECT
  space,
  name,
  file_type
FROM information_schema.INNODB_TABLESPACES
LIMIT 20;

现代部署通常开启：

innodb_file_per_table = 1

这样单表空间独立，DDL、迁移和空间治理更清晰。

22.2 Buffer Pool 的作用

InnoDB 以页为单位读写数据，默认 16KB：

SHOW VARIABLES LIKE 'innodb_page_size';

读取路径：

SQL -> 访问页 -> Buffer Pool 命中 -> 直接返回
                 -> 未命中 -> 从磁盘读入 -> 挂到 LRU

修改路径：

修改 Buffer Pool 中的页 -> 生成 Redo -> 页变成脏页 -> 后台异步刷盘

Buffer Pool 缓存的不只是业务数据：

页类型 说明
Data / Index 数据和索引页
Undo 回滚和 MVCC
Change Buffer 二级索引写缓存
AHI 自适应哈希相关结构
System 数据字典等系统页

22.3 改进型 LRU

朴素 LRU 的问题：

  1. 一次性全表扫描可能把热点页挤出去；
  2. 偶发查询污染缓存；
  3. 大报表影响在线查询；
  4. 缓存命中率抖动。

InnoDB 将 LRU 分为两段：

new sublist：热点区
old sublist：冷区

新读入的页先进入 old 区头部，满足以下条件才移动到 new 区：

  1. 第二次访问；
  2. 距第一次访问超过 innodb_old_blocks_time。

查看：

SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
SHOW VARIABLES LIKE 'innodb_old_blocks_time';

这让短时间连续访问的大扫描仍可能停留在 old 区，减少对热点的冲击。

22.4 Buffer Pool 配置

查看大小：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size';

在线调整：

SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;

大小建议：

场景 建议
专用数据库服务器 可用内存的 50% 到 70%
混部服务器 根据其他组件内存上限评估
小规格容器 保守设置，避免 OOM
云托管 结合连接内存、排序和临时表限制

不要把机器内存全部给 Buffer Pool，还需要保留：

  1. 连接内存；
  2. Sort Buffer；
  3. Join Buffer；
  4. 临时表；
  5. 操作系统和文件系统缓存；
  6. 备份与监控进程；
  7. 内存增长余量。

22.5 命中率与监控

查看状态：

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

关键指标：

指标 含义
Innodb_buffer_pool_read_requests 逻辑读请求
Innodb_buffer_pool_reads 必须从磁盘读页
Innodb_buffer_pool_pages_dirty 脏页数
Innodb_buffer_pool_pages_free 空闲页
Innodb_buffer_pool_pages_total 总页数

粗略命中率：

WITH reads AS (
  SELECT
    MAX(CASE WHEN VARIABLE_NAME='Innodb_buffer_pool_reads'
             THEN CAST(VARIABLE_VALUE AS DECIMAL(20,0)) END) AS disk_reads,
    MAX(CASE WHEN VARIABLE_NAME='Innodb_buffer_pool_read_requests'
             THEN CAST(VARIABLE_VALUE AS DECIMAL(20,0)) END) AS logical_reads
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME IN (
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_read_requests'
  )
)
SELECT
  ROUND(100 * (1 - disk_reads / NULLIF(logical_reads, 0)), 2)
    AS buffer_pool_hit_rate_pct
FROM reads;

解读：

  1. 命中率高不代表没有慢 SQL；
  2. 命中率突然下降要找大扫描；
  3. 新实例预热期命中率低是正常的；
  4. 重启后冷缓存需要预热；
  5. 读密集负载通常希望长期命中率高。

22.6 脏页与刷盘

查看：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL STATUS LIKE 'Innodb_data_pending_fsyncs';
SHOW GLOBAL STATUS LIKE 'Innodb_data_pending_writes';

相关参数：

SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'innodb_io_capacity_max';
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct';
SHOW VARIABLES LIKE 'innodb_flush_neighbors';

脏页过多的后果：

  1. 后台刷盘压力大；
  2. 用户线程可能被迫刷脏；
  3. 查询延迟尖刺；
  4. checkpoint 推进受阻；
  5. 恢复时间变长。

innodb_io_capacity 应与磁盘能力匹配，最终以压测和监控为准，而不是照抄模板。

22.7 Change Buffer 与 AHI

Change Buffer

查看：

SHOW VARIABLES LIKE 'innodb_change_buffering';
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

适合写多读少和非唯一二级索引。写后立即读、热点页常驻内存或唯一索引场景收益有限。

Adaptive Hash Index

查看：

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
SHOW STATUS LIKE 'Innodb_adaptive_hash%';

AHI 由 InnoDB 自动维护，不能手动指定索引。高并发下可能成为争用点，是否关闭应通过对比测试决定。

22.8 表空间与碎片

查看表大小：

SELECT
  TABLE_NAME,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
  ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop'
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC;

碎片常见来源：

  1. 随机主键；
  2. 大量删除；
  3. 可变长度频繁更新；
  4. 页分裂；
  5. 导入后重建索引。

处理方式：

OPTIMIZE TABLE orders;

OPTIMIZE TABLE 会重建表并占用 IO，不要在高峰执行。更推荐容量规划、归档和新表迁移。

22.9 存储引擎选择

查看引擎：

SELECT ENGINE, SUPPORT, COMMENT
FROM information_schema.ENGINES;
引擎 特点 建议
InnoDB 事务、MVCC、行锁、崩溃恢复 默认选择
MyISAM 表锁、无事务、全文历史方案 不建议新系统使用
Memory 内存表、重启丢失 临时性场景
CSV CSV 文件 数据交换
Archive 压缩插入、查询受限 冷归档可评估
Blackhole 丢弃写入 复制测试

绝大多数互联网业务应默认 InnoDB，不要在核心链路混用非事务引擎。

22.10 预热与重启

重启后 Buffer Pool 是冷的，常见现象：

  1. 延迟升高；
  2. CPU 等待 IO 增加；
  3. 命中率低；
  4. 磁盘读放大。

预热方式：

  1. 逐步放开流量；
  2. 执行核心表主键和索引范围读取；
  3. 使用预热脚本加载热点数据；
  4. 云服务启用 Buffer Pool 预热能力；
  5. 主从滚动重启并等待追平；
  6. 避免同时重启多个节点。

示例预热思路：

SELECT id FROM orders WHERE id > 0 ORDER BY id LIMIT 1000;

应用按主键范围分批执行，并控制 QPS。

本章小结

Buffer Pool 是 InnoDB 的内存核心，通过改进型 LRU、脏页管理和后台刷盘平衡内存命中与持久化。专用实例应重点规划 Buffer Pool 大小，同时保留连接、排序、临时表和系统内存。存储引擎默认选择 InnoDB，它的事务、MVCC、行锁和崩溃恢复能力是生产可靠性的基础。

思考题

  1. InnoDB 为什么将 LRU 分为 new 和 old 两段？
  2. 命中率高是否代表系统没有慢查询？为什么？
  3. 脏页过多会带来哪些问题？
  4. 为什么 Buffer Pool 不应设置为机器全部内存？
  5. 设计一个主从滚动重启和预热方案。