今日关键词Buffer Pool、Change Buffer、Log Buffer、Adaptive Hash Index、LRU 算法大家好我是数据库小学妹前几天我们把 InnoDB 的三大日志Redo Log、Undo Log、Binlog拆了个底朝天。写完那篇之后我就在想日志管的是出了事怎么恢复但数据在内存里到底是怎么跑的一条 SELECT 到底经历了什么为什么有的查询飞快有的却慢得要命说实话我之前对 Buffer Pool 的理解就停留在它是个缓存——直到被同事追问Buffer Pool 的 LRU 和普通 LRU 有什么区别整个人当场卡住。翻了好久的资料才搞明白今天把这些全整理出来省得你们再踩我踩过的坑。一、为什么不能直接读磁盘在讲 Buffer Pool 之前先说一个让我特别有感触的事。我以前做设计师的时候打开一个 2GB 的 PSD 文件如果素材都在本地 SSD 上几秒钟就出来了但如果素材放在公司的 NAS 网盘上光加载就要等半天。数据库也是一样的道理——磁盘 IO 是数据库最贵的操作。贵到什么程度呢从机械硬盘读一条数据大概要 10 毫秒听起来不多对吧但从内存读只要 100 纳秒中间差了整整 10 万倍。SSD 比机械硬盘快不少大概 100 微秒左右但跟内存比还是差两个数量级。所以 InnoDB 搞了一个大内存区域来缓存磁盘上的数据页避免每次查询都去仓库里翻。这个区域叫 Buffer Pool。二、Buffer Pool给数据页安个家2.1 Buffer Pool 缓存了什么Buffer Pool 缓存的不是一条条的数据行而是数据页Page——InnoDB 的最小 IO 单位默认 16KB 一个。读取任何一行数据之前InnoDB 会先检查“这个行所在的数据页是不是已经在 Buffer Pool 里了”命中Hit直接从内存返回飞快未命中Miss从磁盘读取数据页放入 Buffer Pool再返回所以 Buffer Pool 命中率基本就代表了你的缓存效率。SHOWENGINEINNODBSTATUS\G-- 在 BUFFER POOL AND MEMORY 段找到-- Buffer pool hit rate XXX / 1000-- 990以上为健康低于950说明Buffer Pool太小或存在全表扫描2.2 LRU 不是朴素 LRU——young 区 old 区这是面试经常问到的问题也是一般人最容易忽略的地方。朴素 LRU 的问题每次访问一个数据页就把它放到链表头部。问题是——一次全表扫描会把大量只用一次的冷数据页全部推到链表前面把真正的热数据挤出去。这就是预读失效和Buffer Pool 污染。InnoDB 的解决方案是把 LRU 链表分成两段┌─────────────────────────────────────────────────────────────┐ │ LRU 链表 │ │ │ │ ◀── old 区 (3/8) ──▶│◀──── young 区 (5/8) ────▶│ │ │ │ │ │ │ 新页先放这里 │ 被再次访问后才晋升到这里 │ │ │ 只访问一次就淘汰 │ 热数据驻留区 │ │ │ │ │ │ │ midpoint tail │ └─────────────────────────────────────────────────────────────┘工作流程一个数据页从磁盘读入后先放到 old 区的头部不是 young 区如果这个页在 old 区被再次访问且距离第一次访问超过 1 秒由innodb_old_blocks_time控制才会被移到 young 区头部如果在 1 秒内又被访问了——不移动防止全表扫描的冷数据污染 young 区其实就是个考察期制度新来的先在老区待着证明自己是常客之后才给升到核心区。-- 查看 Buffer Pool 的 LRU 配置SHOWVARIABLESLIKEinnodb_old_blocks_time;-- 默认 1000msSHOWVARIABLESLIKEinnodb_old_blocks_pct;-- 默认 37old区占3/82.3 脏页什么时候刷回磁盘Buffer Pool 里的数据页被修改后就变成了脏页Dirty Page——内存里是新数据磁盘上还是旧数据。脏页不可能永远留在内存里需要刷回磁盘。但什么时候刷刷盘触发条件说明Redo Log 写满了InnoDB 必须停下来把一部分脏页刷盘腾出 Redo Log 空间这种情况要尽量避免Buffer Pool 不够用了LRU 淘汰时发现是脏页需要先刷盘再释放后台线程定期刷Master Thread 每秒/每 10 秒的定时任务数据库正常关闭把所有脏页都刷回磁盘相关参数SHOWVARIABLESLIKEinnodb_io_capacity;-- 告诉 InnoDB 磁盘的 IO 能力SSD建议2000-20000SHOWVARIABLESLIKEinnodb_max_dirty_pages_pct;-- 脏页比例上限默认90踩坑提醒机械硬盘的话innodb_io_capacity别设太高不然后台刷盘会跟前台查询抢 IO。我之前在一台 HDD 测试机上设了 2000查询抖得不行调回 200 才好。三、Change Buffer非唯一索引的延迟写入3.1 它解决什么问题假设你有一张千万级的订单表建了个非唯一二级索引idx_user_id。现在执行一条UPDATE orders SET status1 WHERE user_id12345这条语句不仅要改聚簇索引还得改idx_user_id这个二级索引。如果idx_user_id对应的索引页不在 Buffer Pool 里怎么办不用 Change Buffer把索引页从磁盘读进来 → 在内存里修改 → 写回磁盘。一次操作就是 3 次 IO。用 Change Buffer不读索引页直接把这次修改记录到 Change Buffer 里 → 后台线程等这个页被其他查询读取时顺手合并进去。一次操作只需要 1 次 IO。3.2 唯一索引为什么不能用因为唯一索引需要判断改完之后是否违反唯一性约束——这个判断必须读取索引页本身。既然都读进来了直接改就是了没必要再走 Change Buffer。简单说唯一索引要先读页才能判断有没有重复既然都读了就顺手改了非唯一索引不用判断重复能不读就不读记到 Change Buffer 里等合并。3.3 相关配置SHOWVARIABLESLIKEinnodb_change_buffer_max_size;-- 默认 25表示 Change Buffer 最多占 Buffer Pool 的 25%-- 写入密集场景可以适当调大最大50读密集场景可以调小3.4 什么时候会合并后台合并Master Thread 定期把 Change Buffer 中的记录合并到对应的数据页读取合并当一条查询恰好要用到某个索引页时发现 Change Buffer 里有它的待合并记录顺手合并数据库关闭时也会触发合并四、Log BufferRedo Log 的中转站上一篇讲 Redo Log 时提到过InnoDB 不是每写一条 Redo Log 就刷一次磁盘而是先写到内存中的Log Buffer再择机刷盘。这里的关键参数是innodb_flush_log_at_trx_commit三个值代表三种策略值写入时机刷盘时机性能安全性0每秒写入 Log Buffer每秒由 OS 刷盘⭐⭐⭐ 最快❌ 最差最多丢1秒数据1每次事务提交时写入每次事务提交时刷盘⭐ 最慢✅ 最好不丢数据2每次事务提交时写入每秒由 OS 刷盘⭐⭐ 居中⚠️ 居中OS崩溃可能丢1秒我个人的建议生产环境老老实实用默认值 1。多那点性能真比不上丢数据的代价。之前在测试环境为了压测好看改成 2 跑了一下TPC-C 确实高了不少。但你想想银行转了一笔账你告诉客户钱可能丢了——这画面太恐怖了。-- 生产环境确认SHOWVARIABLESLIKEinnodb_flush_log_at_trx_commit;-- 确保是 1五、Adaptive Hash IndexInnoDB 的自动加速5.1 它是什么InnoDB 的索引是 B 树查找一条数据需要从根节点一路走到叶子节点一般要 3-4 次磁盘 IO即使在 Buffer Pool 里也需要 3-4 次内存查找。Adaptive Hash IndexAHI是 InnoDB 自己搞的一个优化如果发现某些索引页被高频等值查询命中会自动在内存中为这些页建立一个哈希索引把 B 树的 3-4 次查找降到1 次。说白了B 树查找像翻字典——从目录到章节到具体词条要翻好几页AHI 就像 CtrlF 搜索——直接跳到目标位置。5.2 怎么看它有没有生效SHOWENGINEINNODBSTATUS\G-- 找到 INSERT BUFFER AND ADAPTIVE HASH INDEX 段-- Hash table size: XXXX-- hash searches/s, non-hash searches/s-- 如果 hash searches/s 远大于 non-hash searches/s说明 AHI 在高效工作5.3 什么时候该关AHI 并不是所有场景都好使场景建议等值查询为主✅ 保留 AHI范围查询为主⚠️ AHI 帮不上忙但也没坏处写多读少、高并发写入❌ 关掉 AHI锁争用严重会拖性能CPU 核心很多64⚠️ 观察btr_search相关的 mutex 争用-- 关闭 AHISETGLOBALinnodb_adaptive_hash_indexOFF;-- 不确定就别动让 InnoDB 自己决定六、内存全景图一条查询的完整路径把上面四个组件串起来一条 SELECT 的完整内存路径是这样的客户端发起查询: SELECT * FROM orders WHERE id 10086 │ ▼ ┌─── SQL 层解析 ───┐ │ 生成执行计划 │ └──────────────────┘ │ ▼ ┌─── InnoDB 存储引擎 ───────────────────────────────────────┐ │ │ │ 1. 先查 Adaptive Hash Index │ │ ├── 命中 → 1次内存查找直接返回 ✅ │ │ └── 未命中 → 走 B 树查找 │ │ │ │ 2. B 树从根节点到叶子节点 │ │ ├── 每个节点先查 Buffer Pool │ │ │ ├── 命中Buffer Pool Hit→ 继续往下 │ │ │ └── 未命中Buffer Pool Miss→ 磁盘IO读入Buffer Pool │ │ └── 到达叶子节点找到数据行 │ │ │ │ 3. 检查数据是否满足 MVCC 可见性 │ │ ├── 可见 → 返回数据 │ │ └── 不可见 → 沿 Undo Log 版本链找到可见版本 │ │ │ │ 4. 如果查询还涉及二级索引 │ │ ├── 索引页在 Buffer Pool → 直接查 │ │ └── 索引页不在 → 读盘同时检查 Change Buffer 是否有待合并│ │ └── 有 → 先合并 Change Buffer再返回结果 │ └───────────────────────────────────────────────────────────┘可以看到Buffer Pool 是整个内存体系的核心AHI、Change Buffer 都是围着它转的。七、实战监控与调优7.1 Buffer Pool 命中率SHOWENGINEINNODBSTATUS\G-- BUFFER POOL AND MEMORY 段-- Buffer pool hit rate 998 / 1000 ← 99.8%非常好-- 如果低于 950 / 1000考虑-- 1. 加大 innodb_buffer_pool_size-- 2. 排查是否有全表扫描的大查询7.2 Buffer Pool 大小设置SHOWVARIABLESLIKEinnodb_buffer_pool_size;-- 建议物理内存的 60%-80%-- 比如 16G 内存的服务器设 10G-12G-- 留足够空间给 OS、连接线程、其他进程-- MySQL 5.7 支持在线调整SETGLOBALinnodb_buffer_pool_size12*1024*1024*1024;-- 12GB7.3 脏页比例监控-- 当前脏页比例SELECT(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_pages_dirty)/(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_pages_total)*100ASdirty_pct;-- 如果经常超过 70%考虑调小 innodb_max_dirty_pages_pct-- 或者调大 innodb_io_capacity 让后台刷盘更积极7.4 Change Buffer 监控SHOWENGINEINNODBSTATUS\G-- INSERT BUFFER AND ADAPTIVE HASH INDEX 段-- seg size: Change Buffer 当前大小-- inserts: 合并的插入操作数-- merges: 合并操作数-- 如果 merges 远小于 inserts说明 Change Buffer 堆积了太多未合并的记录八、生产避坑清单Buffer Pool 命中率低于 95%先检查是不是有不带 WHERE 条件的全表扫描再考虑加大 Buffer Poolinnodb_flush_log_at_trx_commit生产环境必须是 1不要为了性能改 0 或 2innodb_io_capacity要匹配实际磁盘能力HDD 设 200SATA SSD 设 2000NVMe SSD 设 5000-20000Change Buffer 的innodb_change_buffer_max_size默认 25 就够了写入密集场景可以提到 50但别超过这个数AHI 默认开启不确定就别关。只有在SHOW ENGINE INNODB STATUS中看到明显的 mutex 争用时才考虑关闭Buffer Pool 别设太大给操作系统和其他进程留够内存不然触发 swap 性能直接崩盘MySQL 8.0 支持多个 Buffer Pool 实例innodb_buffer_pool_instances高并发场景下可以减少锁争用学习心得写这篇的时候我自己也晕了好几次尤其是 AHI 那块反复看了好几遍才理清楚。总结几个我觉得最容易忘的点Buffer Pool 用的不是朴素 LRU有个 young old 双区设计新页有 1 秒考察期。这个设计细节挺巧的专门防全表扫描污染缓存。Change Buffer 只对非唯一索引生效页不在内存的时候先记下来等被读取时再合并省掉了随机 IO。唯一索引因为要判重所以走不了这条路。innodb_flush_log_at_trx_commit三个值各有各的道理但生产环境用 1 就对了别手痒去改。AHI 是 InnoDB 自动干的活把热点等值查询从 B 树的多次查找降到 1 次。不过高并发写入的时候可能反而拖后腿这个我还没实际踩过坑先记下来。日常监控的话盯着 Buffer Pool 命中率别低于 95%、脏页比例别超过 70%、Change Buffer 合并效率这三个数就行。说白了大部分问题都能从这仨数里看出端倪。 我是数据库小学妹一个用设计师思维学数据库的转行人。我们一起把复杂的技术变得简单有趣
InnoDB内存架构解密：Buffer Pool与性能优化实战
发布时间：2026/6/1 12:12:30
今日关键词Buffer Pool、Change Buffer、Log Buffer、Adaptive Hash Index、LRU 算法大家好我是数据库小学妹前几天我们把 InnoDB 的三大日志Redo Log、Undo Log、Binlog拆了个底朝天。写完那篇之后我就在想日志管的是出了事怎么恢复但数据在内存里到底是怎么跑的一条 SELECT 到底经历了什么为什么有的查询飞快有的却慢得要命说实话我之前对 Buffer Pool 的理解就停留在它是个缓存——直到被同事追问Buffer Pool 的 LRU 和普通 LRU 有什么区别整个人当场卡住。翻了好久的资料才搞明白今天把这些全整理出来省得你们再踩我踩过的坑。一、为什么不能直接读磁盘在讲 Buffer Pool 之前先说一个让我特别有感触的事。我以前做设计师的时候打开一个 2GB 的 PSD 文件如果素材都在本地 SSD 上几秒钟就出来了但如果素材放在公司的 NAS 网盘上光加载就要等半天。数据库也是一样的道理——磁盘 IO 是数据库最贵的操作。贵到什么程度呢从机械硬盘读一条数据大概要 10 毫秒听起来不多对吧但从内存读只要 100 纳秒中间差了整整 10 万倍。SSD 比机械硬盘快不少大概 100 微秒左右但跟内存比还是差两个数量级。所以 InnoDB 搞了一个大内存区域来缓存磁盘上的数据页避免每次查询都去仓库里翻。这个区域叫 Buffer Pool。二、Buffer Pool给数据页安个家2.1 Buffer Pool 缓存了什么Buffer Pool 缓存的不是一条条的数据行而是数据页Page——InnoDB 的最小 IO 单位默认 16KB 一个。读取任何一行数据之前InnoDB 会先检查“这个行所在的数据页是不是已经在 Buffer Pool 里了”命中Hit直接从内存返回飞快未命中Miss从磁盘读取数据页放入 Buffer Pool再返回所以 Buffer Pool 命中率基本就代表了你的缓存效率。SHOWENGINEINNODBSTATUS\G-- 在 BUFFER POOL AND MEMORY 段找到-- Buffer pool hit rate XXX / 1000-- 990以上为健康低于950说明Buffer Pool太小或存在全表扫描2.2 LRU 不是朴素 LRU——young 区 old 区这是面试经常问到的问题也是一般人最容易忽略的地方。朴素 LRU 的问题每次访问一个数据页就把它放到链表头部。问题是——一次全表扫描会把大量只用一次的冷数据页全部推到链表前面把真正的热数据挤出去。这就是预读失效和Buffer Pool 污染。InnoDB 的解决方案是把 LRU 链表分成两段┌─────────────────────────────────────────────────────────────┐ │ LRU 链表 │ │ │ │ ◀── old 区 (3/8) ──▶│◀──── young 区 (5/8) ────▶│ │ │ │ │ │ │ 新页先放这里 │ 被再次访问后才晋升到这里 │ │ │ 只访问一次就淘汰 │ 热数据驻留区 │ │ │ │ │ │ │ midpoint tail │ └─────────────────────────────────────────────────────────────┘工作流程一个数据页从磁盘读入后先放到 old 区的头部不是 young 区如果这个页在 old 区被再次访问且距离第一次访问超过 1 秒由innodb_old_blocks_time控制才会被移到 young 区头部如果在 1 秒内又被访问了——不移动防止全表扫描的冷数据污染 young 区其实就是个考察期制度新来的先在老区待着证明自己是常客之后才给升到核心区。-- 查看 Buffer Pool 的 LRU 配置SHOWVARIABLESLIKEinnodb_old_blocks_time;-- 默认 1000msSHOWVARIABLESLIKEinnodb_old_blocks_pct;-- 默认 37old区占3/82.3 脏页什么时候刷回磁盘Buffer Pool 里的数据页被修改后就变成了脏页Dirty Page——内存里是新数据磁盘上还是旧数据。脏页不可能永远留在内存里需要刷回磁盘。但什么时候刷刷盘触发条件说明Redo Log 写满了InnoDB 必须停下来把一部分脏页刷盘腾出 Redo Log 空间这种情况要尽量避免Buffer Pool 不够用了LRU 淘汰时发现是脏页需要先刷盘再释放后台线程定期刷Master Thread 每秒/每 10 秒的定时任务数据库正常关闭把所有脏页都刷回磁盘相关参数SHOWVARIABLESLIKEinnodb_io_capacity;-- 告诉 InnoDB 磁盘的 IO 能力SSD建议2000-20000SHOWVARIABLESLIKEinnodb_max_dirty_pages_pct;-- 脏页比例上限默认90踩坑提醒机械硬盘的话innodb_io_capacity别设太高不然后台刷盘会跟前台查询抢 IO。我之前在一台 HDD 测试机上设了 2000查询抖得不行调回 200 才好。三、Change Buffer非唯一索引的延迟写入3.1 它解决什么问题假设你有一张千万级的订单表建了个非唯一二级索引idx_user_id。现在执行一条UPDATE orders SET status1 WHERE user_id12345这条语句不仅要改聚簇索引还得改idx_user_id这个二级索引。如果idx_user_id对应的索引页不在 Buffer Pool 里怎么办不用 Change Buffer把索引页从磁盘读进来 → 在内存里修改 → 写回磁盘。一次操作就是 3 次 IO。用 Change Buffer不读索引页直接把这次修改记录到 Change Buffer 里 → 后台线程等这个页被其他查询读取时顺手合并进去。一次操作只需要 1 次 IO。3.2 唯一索引为什么不能用因为唯一索引需要判断改完之后是否违反唯一性约束——这个判断必须读取索引页本身。既然都读进来了直接改就是了没必要再走 Change Buffer。简单说唯一索引要先读页才能判断有没有重复既然都读了就顺手改了非唯一索引不用判断重复能不读就不读记到 Change Buffer 里等合并。3.3 相关配置SHOWVARIABLESLIKEinnodb_change_buffer_max_size;-- 默认 25表示 Change Buffer 最多占 Buffer Pool 的 25%-- 写入密集场景可以适当调大最大50读密集场景可以调小3.4 什么时候会合并后台合并Master Thread 定期把 Change Buffer 中的记录合并到对应的数据页读取合并当一条查询恰好要用到某个索引页时发现 Change Buffer 里有它的待合并记录顺手合并数据库关闭时也会触发合并四、Log BufferRedo Log 的中转站上一篇讲 Redo Log 时提到过InnoDB 不是每写一条 Redo Log 就刷一次磁盘而是先写到内存中的Log Buffer再择机刷盘。这里的关键参数是innodb_flush_log_at_trx_commit三个值代表三种策略值写入时机刷盘时机性能安全性0每秒写入 Log Buffer每秒由 OS 刷盘⭐⭐⭐ 最快❌ 最差最多丢1秒数据1每次事务提交时写入每次事务提交时刷盘⭐ 最慢✅ 最好不丢数据2每次事务提交时写入每秒由 OS 刷盘⭐⭐ 居中⚠️ 居中OS崩溃可能丢1秒我个人的建议生产环境老老实实用默认值 1。多那点性能真比不上丢数据的代价。之前在测试环境为了压测好看改成 2 跑了一下TPC-C 确实高了不少。但你想想银行转了一笔账你告诉客户钱可能丢了——这画面太恐怖了。-- 生产环境确认SHOWVARIABLESLIKEinnodb_flush_log_at_trx_commit;-- 确保是 1五、Adaptive Hash IndexInnoDB 的自动加速5.1 它是什么InnoDB 的索引是 B 树查找一条数据需要从根节点一路走到叶子节点一般要 3-4 次磁盘 IO即使在 Buffer Pool 里也需要 3-4 次内存查找。Adaptive Hash IndexAHI是 InnoDB 自己搞的一个优化如果发现某些索引页被高频等值查询命中会自动在内存中为这些页建立一个哈希索引把 B 树的 3-4 次查找降到1 次。说白了B 树查找像翻字典——从目录到章节到具体词条要翻好几页AHI 就像 CtrlF 搜索——直接跳到目标位置。5.2 怎么看它有没有生效SHOWENGINEINNODBSTATUS\G-- 找到 INSERT BUFFER AND ADAPTIVE HASH INDEX 段-- Hash table size: XXXX-- hash searches/s, non-hash searches/s-- 如果 hash searches/s 远大于 non-hash searches/s说明 AHI 在高效工作5.3 什么时候该关AHI 并不是所有场景都好使场景建议等值查询为主✅ 保留 AHI范围查询为主⚠️ AHI 帮不上忙但也没坏处写多读少、高并发写入❌ 关掉 AHI锁争用严重会拖性能CPU 核心很多64⚠️ 观察btr_search相关的 mutex 争用-- 关闭 AHISETGLOBALinnodb_adaptive_hash_indexOFF;-- 不确定就别动让 InnoDB 自己决定六、内存全景图一条查询的完整路径把上面四个组件串起来一条 SELECT 的完整内存路径是这样的客户端发起查询: SELECT * FROM orders WHERE id 10086 │ ▼ ┌─── SQL 层解析 ───┐ │ 生成执行计划 │ └──────────────────┘ │ ▼ ┌─── InnoDB 存储引擎 ───────────────────────────────────────┐ │ │ │ 1. 先查 Adaptive Hash Index │ │ ├── 命中 → 1次内存查找直接返回 ✅ │ │ └── 未命中 → 走 B 树查找 │ │ │ │ 2. B 树从根节点到叶子节点 │ │ ├── 每个节点先查 Buffer Pool │ │ │ ├── 命中Buffer Pool Hit→ 继续往下 │ │ │ └── 未命中Buffer Pool Miss→ 磁盘IO读入Buffer Pool │ │ └── 到达叶子节点找到数据行 │ │ │ │ 3. 检查数据是否满足 MVCC 可见性 │ │ ├── 可见 → 返回数据 │ │ └── 不可见 → 沿 Undo Log 版本链找到可见版本 │ │ │ │ 4. 如果查询还涉及二级索引 │ │ ├── 索引页在 Buffer Pool → 直接查 │ │ └── 索引页不在 → 读盘同时检查 Change Buffer 是否有待合并│ │ └── 有 → 先合并 Change Buffer再返回结果 │ └───────────────────────────────────────────────────────────┘可以看到Buffer Pool 是整个内存体系的核心AHI、Change Buffer 都是围着它转的。七、实战监控与调优7.1 Buffer Pool 命中率SHOWENGINEINNODBSTATUS\G-- BUFFER POOL AND MEMORY 段-- Buffer pool hit rate 998 / 1000 ← 99.8%非常好-- 如果低于 950 / 1000考虑-- 1. 加大 innodb_buffer_pool_size-- 2. 排查是否有全表扫描的大查询7.2 Buffer Pool 大小设置SHOWVARIABLESLIKEinnodb_buffer_pool_size;-- 建议物理内存的 60%-80%-- 比如 16G 内存的服务器设 10G-12G-- 留足够空间给 OS、连接线程、其他进程-- MySQL 5.7 支持在线调整SETGLOBALinnodb_buffer_pool_size12*1024*1024*1024;-- 12GB7.3 脏页比例监控-- 当前脏页比例SELECT(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_pages_dirty)/(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_pages_total)*100ASdirty_pct;-- 如果经常超过 70%考虑调小 innodb_max_dirty_pages_pct-- 或者调大 innodb_io_capacity 让后台刷盘更积极7.4 Change Buffer 监控SHOWENGINEINNODBSTATUS\G-- INSERT BUFFER AND ADAPTIVE HASH INDEX 段-- seg size: Change Buffer 当前大小-- inserts: 合并的插入操作数-- merges: 合并操作数-- 如果 merges 远小于 inserts说明 Change Buffer 堆积了太多未合并的记录八、生产避坑清单Buffer Pool 命中率低于 95%先检查是不是有不带 WHERE 条件的全表扫描再考虑加大 Buffer Poolinnodb_flush_log_at_trx_commit生产环境必须是 1不要为了性能改 0 或 2innodb_io_capacity要匹配实际磁盘能力HDD 设 200SATA SSD 设 2000NVMe SSD 设 5000-20000Change Buffer 的innodb_change_buffer_max_size默认 25 就够了写入密集场景可以提到 50但别超过这个数AHI 默认开启不确定就别关。只有在SHOW ENGINE INNODB STATUS中看到明显的 mutex 争用时才考虑关闭Buffer Pool 别设太大给操作系统和其他进程留够内存不然触发 swap 性能直接崩盘MySQL 8.0 支持多个 Buffer Pool 实例innodb_buffer_pool_instances高并发场景下可以减少锁争用学习心得写这篇的时候我自己也晕了好几次尤其是 AHI 那块反复看了好几遍才理清楚。总结几个我觉得最容易忘的点Buffer Pool 用的不是朴素 LRU有个 young old 双区设计新页有 1 秒考察期。这个设计细节挺巧的专门防全表扫描污染缓存。Change Buffer 只对非唯一索引生效页不在内存的时候先记下来等被读取时再合并省掉了随机 IO。唯一索引因为要判重所以走不了这条路。innodb_flush_log_at_trx_commit三个值各有各的道理但生产环境用 1 就对了别手痒去改。AHI 是 InnoDB 自动干的活把热点等值查询从 B 树的多次查找降到 1 次。不过高并发写入的时候可能反而拖后腿这个我还没实际踩过坑先记下来。日常监控的话盯着 Buffer Pool 命中率别低于 95%、脏页比例别超过 70%、Change Buffer 合并效率这三个数就行。说白了大部分问题都能从这仨数里看出端倪。 我是数据库小学妹一个用设计师思维学数据库的转行人。我们一起把复杂的技术变得简单有趣
