MySQL Buffer Pool 讲透：InnoDB 内存结构、冷热 LRU、脏页刷盘与线上调参

个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL Buffer Pool 讲透InnoDB 内存结构、冷热 LRU、脏页刷盘与线上调参一、为什么需要 Buffer Pool二、InnoDB 内存结构全景2.1 ⚠️ 澄清MySQL 8.0 已经没有 Query Cache 了三、Buffer Pool 里到底装着哪些东西四、三条链表Free / LRU / Flush五、传统 LRU 在数据库里是不够用的5.1 坑一预读失效5.2 坑二缓冲池污染Buffer Pool Pollution六、InnoDB 的解法冷热分离的改进 LRU七、预读机制线性预读与随机预读八、脏页是怎么刷回磁盘的8.2 刷盘速度相关的参数8.3 为什么会出现写入一会儿卡一下九、Change Buffer为什么它只对二级索引生效十、自适应哈希索引AHI十一、Log Bufferredo log 的内存缓冲区十二、怎么观察 Buffer Pool 的健康度12.1 命中率最常用12.2 更精细的统计 / 状态页十三、配置建议13.1 重启后的冷启动问题十四、常见误区十五、小结MySQL Buffer Pool 讲透InnoDB 内存结构、冷热 LRU、脏页刷盘与线上调参很多人以为MySQL 缓存就是 Buffer Pool也有人还在找 Query Cache 的配置——后者在 MySQL 8.0 已经被彻底删除了。本篇把 InnoDB 的内存到底怎么组织、数据页怎么进进出出、脏页什么时候刷盘讲清楚最后给一份可以直接带去生产环境的检查清单。一、为什么需要 Buffer Pool先看一组数量级的差距大致值用来建立直觉存储介质随机读延迟相对倍率L1/L2 缓存~1ns1内存 DRAM~100ns100×NVMe SSD~100μs100,000×机械硬盘 HDD~10ms10,000,000×InnoDB 的数据以16KB 的页为单位存磁盘。如果每次读写都去碰磁盘哪怕一次只改一行也要一次随机 IO。所以 InnoDB 在内存里划了一大块区域做缓存读到的页先放进来下次读同一页直接命中内存修改只改内存里的页变成脏页由后台线程慢慢刷回磁盘通过 redo log 保证内存改了但没刷盘时崩溃数据安全这块区域就是Buffer Pool。它是 InnoDB 里最重要、也是你最应该给足资源的内存。二、InnoDB 内存结构全景整体上 InnoDB 的内存可以分为两大块Buffer Pool放数据页、Change Buffer、自适应哈希索引、锁信息等占绝大部分和相对独立的Log Bufferredo log 缓冲区。其中 Change Buffer 是从 Buffer Pool 里划走的一部分不是额外再申请的内存。一句话区分这几个组件组件解决什么问题Buffer Pool缓存数据页和索引页避免每句话都读盘Change Buffer缓存二级索引的变更避免写操作时把索引页读进来Adaptive Hash Index为热点等值查询建立哈希捷径绕过 BTree 的高度Log Buffer攒 redo log减少 fsync 次数2.1 ⚠️ 澄清MySQL 8.0 已经没有 Query Cache 了老资料里大量出现query_cache_size、have_query_cache这类参数。Query Cache 在 MySQL 5.7.20 被废弃8.0 中彻底移除。它和 Buffer Pool 完全不是一回事对比点Query Cache已废弃Buffer Pool层级MySQL Server 层InnoDB 引擎层缓存对象SQL 文本 → 结果集数据页 / 索引页命中条件SQL 逐字节相同页是否被读到过失效方式表有任何一行改动该表所有缓存全失效页粒度淘汰很精细生产建议早就建议关闭必须给足内存Query Cache 被淘汰的根本原因是在写多一点的库里它的失效粒度太粗维护缓存带来的全局锁争用比它省下的查询开销还大。所以别再试图在 8.0 上找它了你要关心的只有 Buffer Pool。三、Buffer Pool 里到底装着哪些东西不要以为它只缓存数据页和索引页实际上还包括页类型说明数据页聚簇索引叶子占大头就是表里的一行行记录索引页二级索引的 BTree 节点undo 页undo log 也会被缓存自适应哈希索引AHI内存的哈希表结构锁信息、数据字典InnoDB 的元数据和锁结构Change Buffer 结构从 Buffer Pool 里划走一块每一个缓存页还配一个控制块记录表空间号、页号、是否被修改、链表指针等控制块本身也占内存大约每页 800 字节左右所以实际占用会略大于你设置的大小。四、三条链表Free / LRU / FlushBuffer Pool 内部靠三条链表组织所有页链表管理什么作用Free List空闲页还没装数据需要新页时从这里取LRU List已使用的页cleandirty决定谁被淘汰Flush List被修改过的脏页按修改顺序决定谁该被刷盘关键点一个脏页同时挂在 LRU List 和 Flush List 上。LRU 管淘汰谁Flush 管刷谁出去两者目的不同被淘汰的页如果被改过必须先刷盘才能释放。读取一个页的流程先在 Buffer Pool 里按space_id page_no哈希查找命中则直接用并调整 LRU 位置未命中则从磁盘读入 16KB从Free List取一个空闲页装载并挂到 LRU List如果 Free List 已空就从 LRU 尾部淘汰若是脏页则先刷盘再释放。五、传统 LRU 在数据库里是不够用的朴素 LRU 的想法是新访问的页放链表头淘汰时从尾部走。这在 OS 页缓存里够用但在数据库里会踩两个大坑。5.1 坑一预读失效InnoDB 会做预读read-ahead顺序扫描时顺便把接下来可能用到的页提前读进来。问题是预读进来的页未必真的会被访问。如果预读页直接进 LRU 头部它们会把真正的热数据挤到尾部并淘汰掉——预读前 LRU[热A][热B][热C][热D] ... [热Z] 预读后 LRU[预读页1][预读页2][热A][热B] ... [热D] ← 热Z 被挤出去淘汰了 而预读页1/2全程没人访问 —— 纯亏5.2 坑二缓冲池污染Buffer Pool Pollution比预读失效更常见、更致命。一条慢 SQL 做全表扫描SELECT*FROMordersWHEREremarkLIKE%异常%;-- 无法走索引全表扫描结果可能只有几行但过程中会把整表的页依次读进 Buffer Pool按朴素 LRU 规则它们全排到头部把积累已久的热数据一次性冲光。后果是这次查询跑完之后正常业务的查询全部变慢——因为它们的数据都不在内存里了要重新从磁盘读。一次慢 SQL 拖垮整个实例就是这个机制。六、InnoDB 的解法冷热分离的改进 LRUInnoDB 把 LRU 链表拆成两段┌─────────────── young 区热数据────────────┐┌────── old 区冷数据────┐ │ 被多次访问的热点页 ││ 新读入的页先放这里 │ └──────────────────────────────────────────────┘└──────────────────────────┘ 淘汰 ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← ← 从尾部淘汰规则是这样的新页包括预读页只进 old 区的头部不直接进 young 区old 区的页被访问时判断距离它上次进来的时间是否超过innodb_old_blocks_time默认1000ms没超过 → 不动因为这种进来就被访问一次的模式多半是全表扫描超过了 → 挪到 young 区头部young 区内部也有优化只有处于 young 区后 3/4 的页被访问时才挪到头部前面 1/4 的页被访问不动位置省掉大量链表调整开销空间不够时从old 区尾部开始淘汰用一次全表扫描验证一下这套规则为什么有效全表扫描读进 old 区的第 5 页 ↓ 立刻被 LIMIT/OFFSET 逻辑访问了 ↓ 但距进来不到 1 秒 → 不挪到 young 区 ↓ 扫描继续推进这页再没人访问 ↓ 最终从 old 区尾部被淘汰 → 热数据毫发无损相关参数SHOWVARIABLESLIKEinnodb_old_blocks%;-- innodb_old_blocks_pct 默认 37old 区占比 37%young 占 63%-- innodb_old_blocks_time 默认 1000毫秒⚠️ 有人为了解决大表扫描污染去调大innodb_old_blocks_time。这确实是官方认可的做法比如临时执行备份或报表任务前设成几秒但不要把它当常规配置长期设成很大值——那会让真正的二次访问也无法升温反而降低命中率。更好的做法是治本别让全表扫描的慢 SQL 上生产。七、预读机制线性预读与随机预读类型粒度触发条件参数线性预读以extent64 个连续页为单位一个 extent 里被顺序访问的页数 ≥ 阈值innodb_read_ahead_threshold默认 56随机预读以 extent 内的 page 为单位一个 extent 里有多页被读到innodb_random_read_ahead默认 OFF已废弃线性预读对真正的顺序扫描范围扫描、备份有收益阈值调低如 8更激进调高到 64 基本等于关掉一般保持默认即可。八、脏页是怎么刷回磁盘的修改只发生在内存里被改过的页就是脏页dirty page刷脏页由 checkpoint 机制驱动触发场景说明后台线程定期刷page cleaner 线程做生产环境的主要来源redo log 写满必须推进 checkpoint强制刷脏页腾出空间脏页比例超阈值innodb_max_dirty_pages_pct8.0 默认 905.7 默认 75空闲自适应刷根据 redo 生成速率动态调整强度关闭实例sharp checkpoint全部刷完才关LRU 淘汰时要淘汰的页若是脏页先刷盘再释放8.2 刷盘速度相关的参数SHOWVARIABLESLIKEinnodb_io_capacity%;SHOWVARIABLESLIKEinnodb_max_dirty_pages_pct%;SHOWVARIABLESLIKEinnodb_flush_neighbors;参数默认值建议innodb_io_capacity200必须改按磁盘随机写 IOPS 填写SSD 通常 1000~10000innodb_io_capacity_max2000一般取io_capacity的 2 倍innodb_max_dirty_pages_pct908.0写密集场景可下调到 60~70让脏页更平缓地刷innodb_flush_neighbors1SSD 建议设 0不必因为物理相邻顺带刷邻居页innodb_io_capacity是最常被漏配的参数默认 200 是按几十年前的机械硬盘设的。在 SSD 上保持 200会导致刷脏页的速度严重跟不上写入脏页堆积、最终在 redo 写满时触发强制 checkpoint表现为写入突然卡死几秒。8.3 为什么会出现写入一会儿卡一下典型链路写入压力大 → 脏页生成快 →io_capacity太小后台刷不过来 →脏页比例涨到阈值 / redo 逼近写满 → 触发强制 flush前台线程自己刷脏页→业务请求被 IO 阻塞卡顿→ 刷完恢复 → 循环。排查方向就是上面那几个参数外加确认 redo log 总容量是否太小。九、Change Buffer为什么它只对二级索引生效有一类操作特别吃亏往一个二级索引里插入非顺序的键值。比如KEY idx_name(name)插入顺序是按 id 飞的name 却毫无规律那么每次插入都要把对应的二级索引页从磁盘读进来改完再等待它被刷回去——一次随机 IO。Change Buffer 的做法如果这个二级索引页不在 Buffer Pool 里就不读它把要在某某页插入某某记录这件事记到 Change Buffer 里等以后这个页被读进内存、或者后台线程空闲时再合并merge进去。-- UPDATE t SET name张三 WHERE id1; 的执行差别-- 有 change buffer聚簇索引页在内存直接改name 索引页不在内存 → 只在 CB 记一笔直接返回-- 无 change buffer先把 name 索引页从磁盘读进来 → 修改 → 变成脏页等刷盘一次随机读 IO为什么必须是非唯一的二级索引因为唯一索引插入前必须校验唯一性而校验就一定要把索引页读出来——既然读了顺手改了即可缓存就没意义了。这也是为什么唯一索引的写入一定比普通索引慢一点多了读页和校验的开销。相关参数与适用场景SHOWVARIABLESLIKEinnodb_change_buffer%;-- innodb_change_buffer_max_size 默认 25最多占 BP 的 25%最大 50-- innodb_change_buffering 默认 allinserts / deletes / purges / changes场景建议写多读少、二级索引多典型 OLTP保持默认收益明显写完立刻读同一批数据关闭或调小none/ 5 白白多了 merge 开销SSD 且二级索引不多可考虑关闭innodb_change_bufferingnone收益有限反而占内存⚠️ Change Buffer 是从 Buffer Pool 里划地盘的设innodb_change_buffer_max_size50意味着一半缓存可能被索引变更占着谨慎。十、自适应哈希索引AHIInnoDB 会观察到某个等值查询模式反复访问同一个索引页就在 BTree 之上建一个哈希映射key - 页位置让后续的等值查询跳过树的高度。SHOWVARIABLESLIKEinnodb_adaptive_hash_index%;-- innodb_adaptive_hash_index 默认 ON-- innodb_adaptive_hash_index_parts 默认 8分区数减少锁争用可适当调大什么时候该关掉它大量范围查询 / LIKE 扫描AHI 只对等值查询有效遇到 AHI 相关的 latch 争用SHOW ENGINE INNODB STATUS里出现大量btr0sea.cc等待高并发负载下维护哈希表的开销 收益如果拿不准做一次压测对比ON/OFF的 QPS比拍脑袋强。十一、Log Bufferredo log 的内存缓冲区redo log 也不是每次直接写文件而是先写 Log BufferSHOW VARIABLES LIKE innodb_log_buffer_size;默认 16MB。普通事务的 redo 全程待在缓冲区、提交时才 fsync大事务一次改动几百万行超过缓冲区就得中途把 redo 写盘会明显变慢——这类场景可以调到 64MB~256MB。刷盘策略innodb_flush_log_at_trx_commit的细节在三大日志那篇讲过这里不重复。十二、怎么观察 Buffer Pool 的健康度12.1 命中率最常用SHOWGLOBALSTATUSLIKEInnodb_buffer_pool_read%;-- Innodb_buffer_pool_read_requests 逻辑读请求数-- Innodb_buffer_pool_reads 未命中、真正发生磁盘读的次数-- 命中率 1 - reads / read_requestsSELECTMAX(CASEWHENVARIABLE_NAMEInnodb_buffer_pool_readsTHENVARIABLE_VALUEEND)/1.0ASv_reads,MAX(CASEWHENVARIABLE_NAMEInnodb_buffer_pool_read_requestsTHENVARIABLE_VALUEEND)/1.0ASv_requestsFROMperformance_schema.global_statusWHEREVARIABLE_NAMEIN(Innodb_buffer_pool_reads,Innodb_buffer_pool_read_requests);参考标准OLTP 系统命中率应在 99% 以上低于 98% 说明内存不够或有大范围扫描在捣乱。12.2 更精细的统计 / 状态页SELECT*FROMinformation_schema.INNODB_BUFFER_POOL_STATS\G-- POOL_SIZE 总页数、DATABASE_PAGES 已用页数、MODIFIED_DATABASE_PAGES 脏页数-- PAGES_MADE_YOUNG / PAGES_NOT_MADE_YOUNG成功升温的页 / 未满时间窗口被驳回的页SHOWENGINEINNODBSTATUS\G-- Buffer pool size / Free buffers / Database pages / Modified db pages-- Buffer pool hit rate 998 / 1000PAGES_NOT_MADE_YOUNG突然变大、Free buffers长期接近 0通常分别意味着正在发生全表扫描和缓冲池偏紧都是很有用的预警指标。十三、配置建议项目建议innodb_buffer_pool_size独占机器给物理内存的 50%~70%注意留给 OS page cache、连接缓冲、临时表innodb_buffer_pool_instancesBP ≥ 16GB 时设8~16 个实例减少链表互斥锁争用innodb_io_capacity按磁盘真实随机写能力设置SSD 常见 2000~20000innodb_flush_neighborsSSD 设 0innodb_log_buffer_size有大事务时调到 64MBinnodb_change_buffer_max_size默认 25写完立刻读的场景调小甚至关在线调整MySQL 5.7.5 支持动态调整BP 大小无需重启-- 在线调整单位字节8.0 支持调整时涉及 chunk 重分配会有短暂性能抖动SETGLOBALinnodb_buffer_pool_size8589934592;-- 8GB13.1 重启后的冷启动问题实例重启后 Buffer Pool 是空的刚上线那段时间会格外慢。解决办法是预热[mysqld] innodb_buffer_pool_dump_at_shutdown ON # 关闭时把热点页的元信息 dump 出来 innodb_buffer_pool_load_at_startup ON # 启动时异步加载回来 innodb_buffer_pool_dump_pct 25 # 只 dump 最热的 25%默认也可以手动触发SETGLOBALinnodb_buffer_pool_dump_nowON;-- 立即 dumpSETGLOBALinnodb_buffer_pool_load_nowON;-- 立即 load十四、常见误区误区 1Buffer Pool 越大越好——它只是 InnoDB 占用内存的一部分。每个连接还有sort_buffer、join_buffer、read_buffer、tmp_table_size等按连接分配的内存还有 OS page cache。给它 90% 的物理内存OOM 是迟早的事。50%~70% 是普遍稳妥的区间。误区 2命中率低就是内存不够——也可能是有 SQL 在做全表扫描。用PAGES_NOT_MADE_YOUNG、slow query log、performance_schema定位慢 SQL 优先。误区 3加了innodb_buffer_pool_instances就一定更快——它只在 BP 较大一般 ≥ 几个 G时才有意义。小 BP 拆多个实例每个实例太小反而降低管理效率BP 1GB 时 MySQL 会自动忽略这个配置。误区 4Change Buffer 一定提升性能——写后立即读的业务里它是负优化增加了 merge 开销还占内存。误区 5脏页一定要立刻刷干净才安全——不需要也不应该。只要 redo log 落盘了脏页迟早刷。强行全量刷盘innodb_max_dirty_pages_pct设极低会造成持续的 IO 压力。十五、小结Buffer Pool 是 InnoDB 最核心的内存区域缓存数据页、索引页、undo 页等Query Cache 已在 MySQL 8.0 被彻底移除别再混淆内部靠三条链表Free List空闲页、LRU List在用页、Flush List脏页脏页同时在两个链表上朴素 LRU 有两个坑预读失效和全表扫描导致的缓存污染InnoDB 用冷热分离 LRU解决新页进 old 区超过innodb_old_blocks_time默认 1s再被访问才升温到 young 区young 区只在后 3/4的页被访问时才挪到头部减少链表调整开销脏页通过checkpoint刷回磁盘innodb_io_capacity默认 200 太小是导致写入周期性卡顿的常见元凶SSD 上建议innodb_flush_neighbors 0Change Buffer 只对非唯一二级索引生效唯一索引必须读页校验没法缓存AHI 对等值查询加速明显遇到 latch 争用可关闭日常巡检观察命中率应 ≥ 99%SHOW ENGINE INNODB STATUS的Buffer pool hit rate与Modified db pages是每日巡检项大小配置给物理内存的50%~70%大 BP 配多个 instance开启预热 dump/load缓解冷启动下一篇聊一个会直接摧毁 Buffer Pool 效果的话题长事务。它会让 undo 膨胀、锁长时间不释放、history list 暴涨是很多莫名其妙变慢的元凶。