InnoDB 缓冲池与内存调优

数据访问路径：
  应用 → Buffer Pool（内存，纳秒级）
           │ 未命中 ↓
          磁盘页（随机 I/O，微秒~毫秒级）

调优目标：让绝大部分读命中内存，而不是访问磁盘。

一个页的生命周期：
  磁盘 → Free List 取空页 → 读入成为 LRU 页
      → 被 UPDATE 修改 → 进入 Flush List（脏页）
      → 后台刷盘 → 变干净，留在 LRU 或回 Free
      → LRU 冷端被淘汰 → 页被重用/释放

# my.cnf：实例数与缓冲池总大小
innodb_buffer_pool_size = 8G        # 总大小
innodb_buffer_pool_instances = 8    # 实例数 = 8（8G / 1G 每个实例）

一页 16KB：
  ├── 页头（文件头/页头，约 38+56 字节）
  ├── 用户记录区（行数据，按主键序）
  ├── 页目录（槽位索引，加速行定位）
  └── 页尾（校验和，防损坏检测）

行大小与页容量：
  行均长 200 字节 → 一页约 80 行
  行均长 1KB      → 一页约 15 行
  （存在碎片/页填充率，实际略少）

LRU List（按新→旧排列）
┌─────────────────────────────────────────────────────────┐
│ 新子列表（5/8）              │ 旧子列表（3/8）            │
│ 热点数据，长期驻留          │ 一次扫描的冷数据，快速淘汰  │
└─────────────────────────────────────────────────────────┘
midpoint 插入点 ← 新读入的页从这里进入，而不是头部

# 扫描型业务（报表/数仓抽取）可调大 old_blocks_time
innodb_old_blocks_time = 1000

# 纯 OLTP 热点明确，可调小旧区占比
innodb_old_blocks_pct = 30

-- 粗略估算：每个缓冲池页的额外控制块约等于页大小的一小部分
-- 常见经验：Buffer Pool 实际内存 ≈ innodb_buffer_pool_size * 1.10

-- InnoDB 内存相关的状态变量一览
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- Innodb_buffer_pool_size          → 配置的缓冲池大小
-- Innodb_buffer_pool_pages_total   → 页总数（×16KB ≈ 缓冲池大小）
-- Innodb_buffer_pool_pages_free    → 空闲页
-- Innodb_buffer_pool_pages_data    → 含数据页数
-- Innodb_buffer_pool_bytes_data    → 实际数据字节数

-- 看实例级分布（MySQL 5.7+/8.0）
SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES
FROM performance_schema.innodb_buffer_pool_stats;

# 64G 物理内存机器的示例配置
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 32        # 每实例 1G
innodb_buffer_pool_chunk_size = 128M     # 扩展粒度，需在启动前定

# 启动后立即预热（利用之前的缓冲池统计）
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25         # 只转储最热的 25%

# SSD 服务器典型组合
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = OFF
innodb_flush_method = O_DIRECT

-- 查看 Buffer Pool 整体状态
SHOW ENGINE INNODB STATUS\G
-- 关注 "Buffer pool hit rate" 一段：
--   Buffer pool hit rate 995 / 1000   （命中率 99.5%）
--   young-making rate 21 / 1000        （新页进入热区频率）

-- 命中率骤降时，先用慢日志+performance_schema 找全表扫描
-- performance_schema 中的表 IO 统计
SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_READ, SUM_TIMER_READ
FROM performance_schema.file_summary_by_instance
WHERE OBJECT_NAME LIKE '%ibd'
ORDER BY COUNT_READ DESC LIMIT 10;

-- 查看预读状态
SHOW STATUS LIKE 'Innodb_buffer_pool_read_ahead%';

调优闭环：
  1. 基准：压测 + 记录 P99 与命中率
  2. 调参：一次只改一个参数
  3. 验证：对比命中率 / 物理读 / P99
  4. 固化：写入 my.cnf，监控观察一周
  5. 复盘：是否有新的冷读源出现

innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 10
innodb_old_blocks_time = 1500
innodb_io_capacity = 2000
innodb_flush_method = O_DIRECT