接手一个MySQL性能问题十有八九都得先看缓冲池。不管你是开发、运维还是刚入门的新手只要碰上慢查询、内存居高不下、重启后服务发虚这一类问题最后都会绕回到InnoDB Buffer Pool这个点上。MySQL缓冲池就是InnoDB在内存里开辟出来的一块区域专门用来缓存数据页和索引页可以说它是整个MySQL快慢的关键磁盘IO和响应时间之间的差距基本全靠它来填。这篇内容我会从它的底层工作机制讲起再到怎么监控、怎么调参、怎么排查问题把我这些年处理线上MySQL故障时积累的经验和踩过的坑一起放进来。不管你是刚把MySQL装好还是正在给公司的库做性能调优只要你能运行MySQL这篇内容就能直接拿来用。1. 缓冲池是什么决定MySQL快慢的“内存版数据库”1.1 为什么所有查询都要经过缓冲池你写一条SQL去查数据MySQL第一步不是直接读磁盘而是先去缓冲池里找。缓冲池里存的是从磁盘加载进来的数据页默认一个页16KB你查一行记录实际加载的是一个16KB的页。如果这个页已经在内存里属于“命中”直接返回结果动作快得跟从内存数组里拿值一样如果不在就是一次磁盘随机读现在的NVMe盘单次延迟大概在几十到一百多微秒听着好像不慢但存储引擎一次查询可能要读几十个页累积起来就很可观。所以缓冲池命中率越高磁盘IO就越少整体响应时间就越稳。你可以把它理解成厨房里那个料理台——锅碗瓢盆、常用调料全部摆在手边厨师做菜就不用每次跑去储物间搬食材。MySQL也是一样把最常用的数据页和索引页搁在内存里查询引擎直接“就地取材”。1.2 缓冲池里装的到底都是些什么很多人以为缓冲池只存数据行其实它是个“多功能缓存区”主要装这几类东西数据页就是业务行数据表里真正存的内容。索引页B树的叶子节点、非叶子节点都在里面索引查询快靠的也是它。未提交事务相关的undo页事务回滚、MVCC多版本控制都依赖undo信息。数据字典页表的元数据信息。change buffer对二级索引的写操作如果不着急落盘会先缓存在这个区域之后再合并到索引页。自适应哈希索引AHIInnoDB根据高频查询自动构建的哈希索引结构内存也是从缓冲池里划分的。也就是说缓冲池不只是“缓存行数据”它在很大程度上就是数据库在内存中的一个缩影。调优缓冲池本质上就是在调整数据库的“内存工作集”。2. 核心机制拆解LRU链表、脏页刷新和预读2.1 双链表结构为什么MySQL不用简单LRU既然要做缓存就必然面临一个问题内存放不下所有数据到底淘汰谁、留下谁最朴素的做法是LRU最近最少使用的页优先淘汰。但对数据库来说朴素的LRU有一个严重缺陷——全表扫描。假设你的内存里有20个经常被访问的热门页这时候有人跑了一条全表扫描语句读进来10万个冷页。如果按简单LRU的逻辑这10万个新页会一股脑挤到链表最前面把原来的热门页全部挤到尾部然后淘汰掉。等全表扫描结束你会发现热数据全部“蒸发”了接下来所有业务查询都变成磁盘IO系统性能瞬间雪崩。InnoDB解决这个问题的办法是把LRU链表分成两段前面的young区大概占5/8后面的old区占3/8。新读入的页不会直接放到链表头部而是放到young区和old区的分界点上这个分界点默认在链表长度37.5%的位置。一个页如果待在old区超过一定时间innodb_old_blocks_time默认1000毫秒并且又被访问了一次才会被提升到young区头部如果进来没多久就不再被访问很快就会被淘汰。这样一来全表扫描产生的冷页只会待在old区互相淘汰动不了young区的热数据。你可以在状态变量里看到年轻页、非年轻页的统计如果发现not young的数量巨大往往说明系统里存在批量扫描型的SQL。2.2 脏页是怎么从内存回到磁盘的缓冲池里的页被修改之后并不是立刻写回磁盘而是先被标记为“脏页”挂在flush list链表上由后台线程异步刷盘。这个“延迟落盘”的设计能大大提升写性能因为内存写可以批量合并减少磁盘随机写。后台刷脏的关键参数有三个innodb_io_capacity告诉InnoDB你的磁盘每秒能处理多少次IO。机械盘一般给200SSD给1000到2000具体看磁盘型号和RAID策略。innodb_max_dirty_pages_pct脏页占缓冲池的比例上限超过这个值就会加速刷盘默认在5.7是75%8.0是90%。innodb_adaptive_flushing根据redo log生成速率动态调整刷脏速度通常保持默认开启。在实际运维场景里我见过很多突发性“卡顿”其实不是SQL的问题而是大量update/delete把脏页比例推到上限后台开始集中刷盘导致磁盘IO瞬时打满。这里面有个经验如果有监控系统最好把脏页比例和磁盘IO的曲线放在一起看比单纯盯慢查询日志更有价值。2.3 预读是个双刃剑InnoDB还有一套预读机制期望在读一个页的时候把周围的页也读进来减少后续IO。线性预读会基于extent顺序读取当顺序读取的页数超过innodb_read_ahead_threshold默认56时触发随机预读则是在同一extent里发现多个页被访问时触发。预读对顺序扫描场景非常友好比如报表类查询一次把相邻页都拉进缓冲池后续访问全走内存。但反过来如果你的业务频繁出现随机小范围扫描预读会读入大量根本用不到的冷页白白污染缓冲池。MySQL 8.0默认已经关闭了随机预读这个取舍我觉得挺合理如果你用的是5.7且内存很紧张可以考虑把innodb_random_read_ahead关掉。3. 三步看懂缓冲池健康状况监控命令与关键指标3.1 快速查看当前缓冲池规模与配置调优第一步先把当前配置摸清楚。连上MySQL执行SHOW VARIABLES LIKE innodb_buffer_pool%;你会得到一组和缓冲池相关的参数重点关注这几个参数名含义默认值5.7/8.0innodb_buffer_pool_size缓冲池总大小128Minnodb_buffer_pool_instances缓冲池实例数大于1G时为8innodb_buffer_pool_chunk_size每个实例的块大小128Minnodb_old_blocks_time新页在old区停留时间1000msinnodb_old_blocks_pctold区比例37表示37%innodb_buffer_pool_dump_at_shutdown关闭时记录页信息OFFinnodb_buffer_pool_load_at_startup启动时加载记录页OFF如果innodb_buffer_pool_size还是默认的128M说明你的数据库一直处于“吃不饱”的状态等于每查一次数据都在现烤现卖性能可想而知。3.2 用Show Engine InnoDB Status读内存段想看缓冲池内部状态最直接的方式是SHOW ENGINE INNODB STATUS\G输出里有一段叫“BUFFER POOL AND MEMORY”长这样BUFFER POOL AND MEMORY ---------------------- Buffer pool size 524288 Buffer pool size, bytes 8589934592 Free buffers 399000 Database pages 125000 Old database pages 12000 Modified db pages 200 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1000, not young 500 ...逐行解释一下Buffer pool size总页数这个数字乘以16KB就是缓冲池实际字节数524288页乘以16KB正好等于8GB。Free buffers空闲页数量如果长期接近0说明缓冲池压力很大。Database pages已经缓存的数据页数量。Old database pagesold区里有多少页。Modified db pages脏页数量重点关注如果持续很高说明刷脏跟不上。Pages made young被提升到young区的页数not young则是没被提升的数量如果not young特别大大概率有扫描型查询在“污染”old区。3.3 用SQL计算命中率和脏页比例状态变量是数值型的适合算出可量化的指标。查询两个关键计数SHOW GLOBAL STATUS LIKE Innodb_buffer_pool%;你会看到状态变量名含义Innodb_buffer_pool_read_requests逻辑读请求次数Innodb_buffer_pool_reads缓冲池未命中、需要走磁盘读的次数Innodb_buffer_pool_pages_dirty当前脏页数量Innodb_buffer_pool_pages_total缓冲池总页数命中率可以这样算逻辑读请求数-磁盘读次数/逻辑读请求数。比如read_requests是1亿reads是2000命中率就是99.998%。我个人经验OLTP在线交易业务的命中率正常应该在99.5%以上低于99%就要警觉低于95%基本说明缓冲池配置和业务模型严重不匹配。脏页比例的计算方式是Innodb_buffer_pool_pages_dirty除以Innodb_buffer_pool_pages_total如果长时间在30%以上震荡就需要关注刷盘能力。4. 缓冲池调优实操参数计算与动态调整4.1 缓冲池大小到底怎么定很多人一听“调优”就说把innodb_buffer_pool_size设成物理内存的70%、80%这个说法过于粗暴。我一般按几个步骤来先看机器总内存。给操作系统、连接线程、排序缓冲区、临时表、复制线程预留内存。估算热数据量查一下最大的几张表和对应索引的总数据量。在不超过可用内存上限的前提下尽量让缓冲池容纳下热数据。举一个典型的例子一台16GB内存的服务器MySQL主要服务一个在线商城用户表、订单表加索引一共约5GB这套系统平时有大量按用户查订单的请求。操作系统预留2GB连接线程并发200个、每个按2MB缓冲算大概0.4GB排序和临时表预留1GB那么可用给缓冲池的空间大约在12GB。考虑到还有一堆临时开销我会设置10GB到11GB再观察swap和命中率确认是否合适。如果你不想拍脑袋还可以借助表统计信息估算基础数据量SELECT table_schema, ROUND(SUM(data_length index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables GROUP BY table_schema ORDER BY total_mb DESC;这个查询展示的是表结构和索引的逻辑大小真实内存中还要算碎片、undo页、change buffer等但用作打底参考足够了。4.2 instances、chunk_size和预热参数的搭配规则缓冲池默认只有1个实例时所有线程都要竞争同一把锁。当缓冲池超过1GB拆成多个实例可以显著减少锁竞争。官方默认在pool size大于等于1GB时实例数为8建议你按照CPU核心数配合调整通常8、16都是合理区间。这三个参数之间有个硬约束innodb_buffer_pool_size必须是innodb_buffer_pool_chunk_size乘以innodb_buffer_pool_instances的整数倍。比如chunk_size是128Minstances是8那么缓冲池的大小就必须是1GB的整数倍。如果你设置了一个不是整数倍的值InnoDB会自动向上取整可能在日志里出现警告。chunk_size本身是静态参数想改必须重启而且如果你缩得太小比如小于16MInnoDB可能直接拒收这个配置。在8.0里innodb_buffer_pool_size支持在线动态调整执行SET GLOBAL innodb_buffer_pool_size 10 * 1024 * 1024 * 1024;系统会按照chunk粒度重新分配内存这个过程需要一段时间的页迁移在线业务做的时候不建议在高峰期操作否则会有短时性能和锁开销。如果你用的是SET PERSIST还能把它持久化到mysqld-auto.cnf重启不丢失这点比5.7体验好不少。另外8.0新增了一个参数innodb_buffer_pool_in_core_file默认ON。当MySQL崩溃生成core dump时会把整个缓冲池的内容都写进core文件如果你的缓冲池是几十GBcore dump可能直接把磁盘打爆。数据库崩溃恢复时其实用不到缓冲池里的页所以建议设成OFF减少无意义的磁盘占用。4.3 让重启后的MySQL快速回到最佳状态MySQL重启之后缓冲池是空的所有查询都要先走一遍磁盘这种状态叫“冷缓存”表现就是服务刚启动时各种慢查询等跑几小时才慢慢恢复正常。对早晚高峰不能中断的业务来说这个“预热期”相当难受。好在InnoDB自带了一套预热机制。开启下面两个参数[mysqld] innodb_buffer_pool_dump_at_shutdown ON innodb_buffer_pool_dump_pct 50 innodb_buffer_pool_load_at_startup ON关闭MySQL的时候InnoDB会把当前缓冲池里最频繁访问的页的编号记录下来先写到磁盘上的ib_buffer_pool文件默认记录比例由dump_pct控制8.0默认是25%。下次启动时InnoDB按这个文件批量预读这些页让热数据快速回到内存。要注意的是预热期间磁盘IO会有一个短暂的高峰相当于是把预热工作集中到了启动后的几分钟内整体上利大于弊。除了重启还有一个场景也很适合预热主从切换。新主库的缓冲池是冷的切换过去之后如果没有预热机制业务很可能会在切换后的半小时内频繁报慢查询。所以搭建高可用环境时建议主库也开启上面这几个参数并把dump和load机制纳入你的切换演练里。5. 常见问题与排查实录避坑现场5.1 命中率上不去问题到底在不在缓冲池有一次线上库缓冲池已经开到64GB但命中率还是卡在96%左右怎么调大小都不管用。后来查了performance_schema的语句统计发现有一个定时任务每小时全表扫一次三千万行的日志表。每一次全表扫描都要读入整张表缓冲池里的热数据每隔一小时就被冲掉一批。这种情况再怎么调大缓冲池也没意义直接把那个定时任务的SQL改成按索引范围查询命中率立刻回落到99.8%。排查命中率低的思路应该是这样的先看是不是有大量全表扫描或者大批量join用这条SQL定位SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10;找出扫描行数远大于返回行数的语句基本就能抓到元凶。如果扫描型语句确实没有再看是不是热数据总量本身就超过了缓冲池比如核心表数据量有80GB缓冲池只有32GB那么再精准的热点模式也装不下只能扩容或者做冷热分离。5.2 重启后MySQL慢得离谱有次半夜做安全补丁重启了数据库第二天早上业务方反馈说线上特别慢我自己去看监控发现磁盘读IOPS从平时的几百冲到接近上万慢查询日志一大片。当时没有开预热参数服务恢复冷缓存后所有热点页都是边查边加载大量磁盘读排队形成恶性循环。那次的教训让我养成了两个习惯只要是计划内重启就在维护窗口执行重启前确认innodb_buffer_pool_dump_at_shutdown和load_at_startup已经开启。如果是非计划重启比如机房断电、kill -9dump文件可能没来得及生成启动后可以手工做一次定向预热。对大表执行一句带LIMIT的select只会触发部分页加载效果有限所以重点要保热数据可以用mysqldump把热表结构导出再导入不那会干扰业务正确做法是让业务自然访问热区或者临时降低并发、接受一段时间的“热身期”。我个人的策略核心库一律开启预热参数同时把innodb_buffer_pool_dump_pct设到50宁可启动时多花一两分钟加载也不要让业务陪着一起煎熬。5.3 “内存泄漏”和“非分页缓冲池占用高”到底是什么很多人在网上搜“非分页缓冲池占用很高怎么解决”然后怀疑是MySQL的问题。实际上这里要区分系统内存池和MySQL缓冲池它们完全不是一个东西。Windows任务管理器里的“非分页缓冲池”是Windows内核和驱动使用的物理内存池占得高的原因多半是某些驱动异常比如网卡驱动、存储驱动或者杀毒软件的文件过滤驱动和MySQL没有直接关系。而在MySQL这一侧并没有所谓的“缓冲池内存泄漏”一说。缓冲池一旦分配好大小就基本固定页的分配和释放都在InnoDB内部循环不会无限增长。但MySQL整体内存却有可能持续上涨最常见的“隐性内存大胃王”是performance_schema。它在8.0默认是开启的会为每个等待事件、每个SQL摘要都分配内存如果监控维度开得过多消耗几个GB内存都不奇怪。排查这类问题时可以直接查MySQL内部的按事件统计SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10;那个“内存泄漏”的锅很多情况下是被上面的查询揪出来发现是某个事件类型占了几GB并不是缓冲池在涨。遇到这种情况合理裁剪监控项、调整innodb_buffer_pool_size上限比重新编译MySQL靠谱得多。5.4 改了缓冲池参数不生效有次同事告诉我他把innodb_buffer_pool_size调成了4GB但show variables看到的还是128M。原因非常常见他改的是运行时参数却忘了配料库ICP配置文件。这里需要说清楚区别动态参数修改后立即生效比如innodb_buffer_pool_size在8.0支持在线调整。静态参数必须写入my.cnf后重启比如innodb_buffer_pool_chunk_size、innodb_old_blocks_time的某些版本。如果你在命令行里SET GLOBAL修改了参数但配置文件没同步下次重启又会回到旧值。正确的做法是两边一起改8.0里可以用SET PERSIST innodb_buffer_pool_size 4 * 1024 * 1024 * 1024;这个命令会把值记入mysqld-auto.cnf同时让运行实例立刻生效避免“当前生效但重启失效”的坑。再提醒一个细节innodb_buffer_pool_size并不是越大越好。有次我把一台机器的缓冲池调到了物理内存的80%结果系统开始大量使用swap。因为每个连接线程、排序缓冲区、临时表、binlog缓存和复制线程都还需要额外内存全被挤压到swap后MySQL的响应时间比调优之前更差。所以调大缓冲池一定是要和整体内存规划一起考虑的最好调完之后盯几天的free、swap、命中率和慢查询趋势再下结论。最后再分享一个小技巧。调完参数之后我习惯每天跑一次状态采集把Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads、Innodb_buffer_pool_pages_dirty这几项拉成折线图配合磁盘IO使用率一起看。缓冲池调优不是一锤子买卖业务增长、数据量变化、新上线的SQL模式都会改变热数据分布。你只需记住命中率稳定在99%以上脏页比例不持续走高swap不增长这就是缓冲池健康的信号。剩下的交给时间验证就行。