聊到MySQL性能优化缓存永远是绕不开的话题但也是误解最多的地方。我见过不少同学一听到“缓存”这两个字第一反应要么是query cache要么就是往Redis里塞业务缓存反而把MySQL自己那套庞大的缓存体系给忽略了。实际上MySQL自带的缓存至少横跨了引擎层、服务层和会话层三层而其中最核心的InnoDB Buffer Pool才是决定你查询效率高低的头号选手。MySQL的缓存说白了就是用内存换磁盘IO的那套把戏但这套把戏玩明白的人真不多。这篇文章我会把MySQL缓存这件事从用户态讲到内核态覆盖InnoDB Buffer Pool、查询缓存的前世今生、各种容易被忽略的“小缓存”、缓存失效与一致性最后附上几个我实际跳过的坑和排查工具。适合正在调优路上抓耳挠腮的DBA、被慢查询折磨的后端工程师以及刚把MySQL装上、对“缓存”只有一个模糊概念的新手。1. 先搞明白我说的MySQL缓存到底指哪几层1.1 很多人把query cache当成了MySQL缓存的全部我早年刚接触MySQL的时候也犯过这个经典错误。当时听人说“MySQL有查询缓存开起来之后效果立竿见影”于是兴冲冲地把query_cache_typeON打开结果发现数据库不但没变快反而在高并发写入的场景下越来越卡。后来才明白query cache只是把“SQL语句文本查询结果集”原封不动地存下来属于服务层的一个结果集缓存。它有一个非常致命的机制只要对应表发生了任何写操作整张表的查询缓存全部失效。写入频繁的业务会导致缓存建立、失效的循环往复还要在全局范围内加锁保护缓存结构等于自己给自己上了道枷锁。MySQL 8.0直接把query cache从代码库里删掉了MySQL 5.7里默认也是关闭状态。所以我希望你先忘掉“缓存query cache”这个刻板印象MySQL缓存的大头根本不在这一层。1.2 MySQL缓存家族全景8.x版本里你真正要管的东西按作用位置划分MySQL缓存大致可以分成下面这几类缓存名所属层级缓存内容核心参数/机制InnoDB Buffer Pool存储引擎层数据页、索引页、undo页、自适应哈希索引、数据字典innodb_buffer_pool_sizeChange Buffer存储引擎层二级索引变更操作innodb_change_buffer_max_sizeRedo Log Buffer存储引擎层事务重做日志innodb_log_buffer_size表缓存服务层打开的表对象、表定义table_open_cache、table_definition_cache线程缓存服务层会话线程对象thread_cache_size语句级会话缓存会话层排序、Join、临时表等sort_buffer_size、join_buffer_size、tmp_table_sizeMyISAM键缓存存储引擎层MyISAM表的索引块key_buffer_sizeInnoDB为主时几乎用不上MySQL 8.0之后每次执行一条查询从磁盘加载数据页、在内存里找索引页这些全部走Buffer Pool。你用SHOW GLOBAL STATUS里看到的Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads这些计数器才是评估“查询效率红利”最直接的指标。理解这层以后才会明白为什么同样的SQL第一次跑要几百毫秒第二次只要几毫秒。1.3 为什么同一个SQL时快时慢冷热数据的本质差距很多新手问我为什么同一套数据库、同一个SQL白天跑得飞快到夜里例行统计的时候就慢得离谱答案就藏在缓存命中率里。MySQL磁盘IO是微秒到毫秒级内存访问是纳秒级中间差了三到四个数量级。Buffer Pool就是那堵把磁盘和内存隔开的水坝。首次读取某个数据页磁盘数据被拉进Buffer Pool后续同样页面的读取就直接从内存里拿。如果内存池子足够大理论上热数据可以一直待在里面查询稳定在低延迟池子太小或者有大量一次性扫描的数据涌进来热点数据被挤出去下一次查询又回到磁盘IO的慢车道上性能自然忽高忽低。这也就是为什么缓存诊断为MySQL优化第一课的原因。2. 重头戏InnoDB Buffer Pool——MySQL缓存的核心战场2.1 Buffer Pool到底在缓存什么数据页、索引页、undo页和自适应哈希索引InnoDB会把数据按16KB一页的组织形式存放Buffer Pool里面装的就是这些页的一段内存副本。当你执行SELECT * FROM user WHERE id 100InnoDB会先在Buffer Pool里找主键索引包含这条记录的页找不到才去磁盘用一次页面读取把它捞回来。这里要补一个关键细节Buffer Pool不只在缓存用户数据。假如某条更新语句要修改二级索引而二级索引页并不在内存里Change Buffer会先把变更记录下来后续再合并到索引页避免每次修改都强制读取磁盘索引页。undo页也常驻在Buffer Pool里支撑MVCC多版本并发控制。还有自适应哈希索引AHI是根据热点查询自动给某个索引页维护的内存哈希表出发点是让BTree查找退化成哈希查找。如果你在SHOW ENGINE INNODB STATUS里看到hash searches / non-hash searches的比率就能判断AHI有没有起作用。可以这样说数据页和索引页的命中共识是Buffer Pool价值的主线而Change Buffer和AHI是同一套内存空间上的两个“支线任务”。这些机制全被纳入了Buffer Pool这一套管理框架里所以它才是MySQL缓存体系的核心。2.2 怎么定size一块内存怎么分系统才不OOMinnodb_buffer_pool_size是MySQL调优里最出名的参数但真的不等于“调得越大越好”。我把生产环境里踩过的一次教训告诉你一台32GB内存的机器数据总量大概150GB我一度把Buffer Pool开到27GB结果系统可用内存被吃掉大半操作系统没有足够page cache最后连mysqld都因为分配不到内存触发OOM数据库直接重启。后来我重新按这个逻辑去算首先要保证操作系统有2GB到4GB的余量其次要预留给会话层的内存缓冲一个活跃连接排序可能吃掉几十MB按300个并发连接粗算就是小10GB最后才是Buffer Pool可以吞下去的部分。纯MySQL实例的通用经验值是内存的60%到75%之间同时要留意swap和系统日志。如果你机器上还跑了Nginx、Java应用、监控Agent这个比例还要往下压。MySQL 8.0还支持innodb_buffer_pool_instances把大块缓冲拆成多个实例减少高并发下的内部锁竞争。当Buffer Pool超过1GB时默认分为8个实例过小的池子拆实例反而浪费内存别盲目照抄DBA文章里“一上来就设16个亿”的做法。2.3 命中率、LRU冷热分区与在线调整的玩法给个最朴素的命中率计算方式在SQL里直接跑SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;把Innodb_buffer_pool_read_requests看作总读取次数把Innodb_buffer_pool_reads看作真正落到磁盘的次数命中率就是命中率 (read_requests - reads) / read_requests * 100%正常业务的命中率在99%以上才算健康。低于95%要么是池子太小要么是数据访问存在严重冷热不均。这里必然要聊到LRU淘汰算法。InnoDB的LRU不是一条简单的链表它把链表分成new区和old区新读入的页先放到old区头部再次被访问且满足时间条件后才会晋升到new区。这个设计的价值是为了挡住“全表扫描定时任务”这类一次性大查询——如果新页直接进入热端几GB数据扫一遍就能把真正的热点页全部顶出内存。实操上innodb_old_blocks_time默认值是1000毫秒表示页面进入old区后必须停留至少1秒再被访问才会晋升到new区。这个时间窗口基本能让大扫描自动“降温”保护线上热数据。如果你碰到全表扫描打崩命中率的情况先检查这个参数有没有被人改成0。在线调整方面MySQL 5.7和8.0都支持动态修改Buffer Pool大小SET GLOBAL innodb_buffer_pool_size 12 * 1024 * 1024 * 1024;调整过程是后台异步完成的但生产环境还是要放在凌晨低峰期并且分几档逐步调不要一次从8G跳到40G。2.4 预热问题MySQL重启后性能暴跌的真相数据库实例一重启Buffer Pool直接清零看起来只是丢掉了缓存实际上等于把一台“内存中跑着的数据库”打回了“每次查磁盘的原始状态”。应用流量突然涌进来所有查询都得走磁盘IO慢查询日志瞬间被刷屏这个状态可能要持续几十分钟甚至几小时直到最热的数据页被访问又回填进Buffer Pool。MySQL官方提供了一个补救机制innodb_buffer_pool_dump_at_shutdownON和innodb_buffer_pool_load_at_startupON。开启后关库时会把Buffer Pool里每个页的标识记录到文件里启动时再把对应数据页主动加载回内存。我在一台16GB Buffer Pool的实例上做过测试开启后重启性能恢复到正常水平的时间从40分钟缩短到几分钟热SQL的响应时间几乎没出现明显波动。注意这个load过程本身也会占IO启动期间建议把应用流量做成灰度放量同时观察SHOW ENGINE INNODB STATUS里的Buffer pool load是否已经结束别等到业务方打电话来才发现还没加载完。3. 容易被忽视的“小”缓存从表缓存到线程缓存3.1 table_open_cache与表定义缓存文件句柄别打光很多慢查询排查了半天最后竟然是“打开表”这个动作拖慢了速度。MySQL执行SQL时需要先打开表、拿到表对象和元数据如果每个查询进来都重新做一遍这个动作开销其实不小。table_open_cache控制的正是服务层能缓存的打开表对象数量table_definition_cache则负责缓存表的结构定义。怎么判断当前值够不够看两个状态变量Open_tables和Opened_tables。如果Open_tables经常接近上限而且Opened_tables一直在涨说明表不断被打开、关掉缓存明显不够。经典的调法是把table_open_cache从默认的4000往上加但加之前必须同步检查操作系统的open_files_limit否则很容易撞上“Too many open files”的硬限制。我实际处理过一个case开发框架里有几百张分表并发一上来全是表缓存未命中把table_open_cache提到12000配合系统句柄上限调整后相关SQL的响应时间直接下降了一个数量级。3.2 thread_cache_size短连接场景下的隐形加速器这个参数容易被忽略但它对高并发短连接的业务特别值钱。当一个新的数据库连接建立时MySQL主线程需要创建一个连接线程来处理该连接的请求断开后如果线程缓存够大线程会被回收并复用而不是销毁重建。判断方式也简单看Threads_created和Connections。Threads_created创建过的线程数远大于并发连接数而且增长很猛说明每次都是新造线程。对连接池场景比如Java应用用HikariCP保持长连接影响不大但如果是PHP这种每次请求创建短连接的模式thread_cache_size的收益会非常明显。一般设成8到32之间就够了设太大只是预占内存实际并发根本用不了那么多。3.3 排序、Join和临时表会话级内存缓存怎么取舍排序和Join慢先别急着怪CPU先查这些操作的“工作台”够不够大。sort_buffer_size是每个会话在排序时分配的内存缓冲join_buffer_size是连接操作使用的内存read_buffer_size、read_rnd_buffer_size则影响顺序和随机读。这些参数都是按连接分配的不是共享池所以设置得过大乘上几百个并发连接内存会瞬间爆炸。我的经验是sort_buffer_size不要一上来就调几GB优先去看SQL有没有触发filesort——尤其是ORDER BY的字段有没有索引。如果索引完全覆盖排序排序缓冲根本不顶用反之索引一定用不上光靠加大缓冲只是延缓问题。临时表则要看tmp_table_size和max_heap_table_size内存临时表超限后会落到磁盘用Created_tmp_disk_tables状态值可以确认落盘比例一般这个数字别超过Created_tmp_tables的25%。3.4 两个“边角料”参数的关键配置参考key_buffer_size很多文章还在推荐调大但那是MyISAM时代的经验。现在绝大多数业务都改成InnoDB了有几个生产实例会把myisam表当系统表大多只是维护少数内部表16MB到64MB足够再多就是浪费。innodb_log_buffer_size事务在提交前redo log会先写在Log Buffer里默认16MB。一般小事务根本用不到多少只有当大量写入并发提交、binlog group commit频繁时这个值才有上调空间。我在32核心、写入量很大的实例上调到64MB之后写入毛刺现象有明显改善。参数建议值InnoDB为主的OLTP实例判断指标table_open_cache4000-12000Open_tables、Opened_tablesthread_cache_size8-32Threads_createdsort_buffer_size2MB-4MB起步Sort_merge_passesjoin_buffer_size256KB-2MB慢日志中Block Nested Looptmp_table_size16MB-64MBCreated_tmp_disk_tableskey_buffer_size16MB-64MB仅MyISAM相关innodb_log_buffer_size16MB-64MBlog waits、写入毛刺4. 查询缓存的前世今生为什么8.0把它移除了4.1 Query Cache的机制与致命设计Query Cache把SQL语句文本做精确匹配命中后直接返回缓存的结果集。这个设计本身看起来很美好但它在高并发场景下有四个倾向致命的问题第一SQL只要多一个空格、大小写不同缓存key就对不上命中率极低第二只要表一发生写操作整张表在缓存里的所有条目全部作废写频繁等于天天给缓存“清场”第三读写竞争同一把全局锁读缓存的线程和写缓存的线程互相阻塞第四MySQL 8.0之前Table Cache和Query Cache绑定在一起更新了一个表还要同步清理相关Query Cache条目清理大缓存本身就是一个慢操作。我见过最夸张的一个业务场景服务器每隔十秒向一张表写入一条数据结果一张大表上的Query Cache每分钟要因写操作清空几十次命中率长期在10%以下CPU反而被缓存维护给吃掉了。这种机制下等于是拿宝贵的CPU和锁资源去换一个本就不太可能命中的结果集。4.2 5.7里怎么正确关掉它如果你还在用MySQL 5.7建议直接检查参数SHOW VARIABLES LIKE query_cache%;看到query_cache_typeDEMAND或ON时改成OFF并顺手把query_cache_size调成0。千万只改query_cache_typeOFF而不清理query_cache_size因为内存块还在某些版本下仍然有维护开销的残留。MySQL 8.0则直接把相关参数从系统表中移除想关都关不了这也算是官方变相承认了这条设计路线走不通。装错版本的教训提醒一下看教程安装MySQL时一定要分清楚你装的是5.7还是8.0很多老旧教程教的“开启查询缓存”动作在8.0里根本不存在。4.3 该用什么替代方案结果集缓存的需求并不过时过时的只是“在数据库服务层手里做全局缓存”这个思路。真正靠谱的方案是在它原来的位置两侧各加一层向上在应用层或Proxy层做业务数据缓存向下把热点页留在Buffer Pool里、把热索引留在内存里。MySQL负责的应当始终是“数据页访问效率”而不要指望它帮你省去复杂查询的计算。当业务把目光转向Redis这类外部缓存时新的问题又会浮现“缓存和MySQL的数据一致性怎么解决”这也是下一章的重点。5. 缓存失效与一致性一个贯穿数据库与业务层的老大难5.1 先梳理MySQL内部的缓存失效表结构变更、LRU淘汰、实例重启MySQL自身的缓存失效有三个最常见的触发点LRU淘汰导致某个热数据页被刷出内存表结构变更ALTER TABLE导致相关表的数据字典和Table Cache失效实例重启导致整个Buffer Pool清零。这些失效都是“物理性”的调参、预热、优化访问模式都能改善但无法完全避免。比如LRU淘汰只要工作集大于Buffer Pool永远会存在表结构变更可以尽量安排在低峰期减少对热SQL的扰动。5.2 引入Redis之后双写一致性问题为什么这么难MySQL缓存解决不到的问题很多人会往上面加Redis。然后问题就从“数据库缓存命不命中”变成了“缓存和数据库到底谁先写”。经典答案叫Cache Aside Pattern读的时候先读缓存缓存没有再去读数据库并回填写的时候先更新数据库再删除缓存。为什么是“删缓存”而不是“更新缓存”因为更新缓存有并发写脏的快照问题。假设线程A和线程B同时要更新同一行的用户昵称数据库执行顺序是A后再B最终库里是B的新值但缓存侧的写入顺序如果反过来A反手把旧值写进缓存那这个旧值会一直存在直到下一次失效。删除缓存则没有这个负担读线程发现自己落空再去数据库取回最新值就行天然更安全。联合起来就是三条铁律先更新数据库后删缓存删除失败要重试或补偿缓存一定要有TTL兜底防止各种异常路径导致长期数据不一致。5.3 延迟双删与binlog订阅我的实际治理套路我先说结论单纯靠“先更库再删缓存”不是绝对可靠因为中间存在窗口期。线程A更新数据库后还没删缓存线程B读到旧缓存把旧值返回等线程A把缓存删掉之后线程B又因为之前没读到立刻回去查数据库并回填缓存回填的却是旧数据因为B的读动作发生在A写入之后但在某些隔离级别下可能读的是快照。这种窗口期很窄但在高并发核心链路上还是会被放大的。有两条实测有效的处理路径。第一条是延迟双删删缓存之后等几百毫秒再删一遍把第二次窗口期里其他线程回填的脏值也清掉。这里的等待时长要大于“读写并发交叉窗口”的估算时间宁可多等一下也别删早了。第二条是订阅binlog用Canal这类工具监听MySQL的binlog数据库提交成功之后再异步删除/更新缓存。这个方案的好处是彻底解耦主流程不用额外处理缓存逻辑冷数据一致性问题也能被延迟删键兜住符合“最终一致”的业务可接受范围。5.4 业务层缓存MyBatis等与MySQL缓存的关系有些项目在应用层用了MyBatis一级缓存和二级缓存这里要泼一盆冷水一级缓存默认是SqlSession级别的一个SqlSession内如果多次执行同一条SQL可以直接命中但一旦跨SqlSession一级缓存就废了没有任何共享价值。二级缓存是跨SqlSession的但多个应用实例部署时每个实例各自维护一份本地缓存数据库里数据更新后这些实例的缓存可能还是旧的。而且很多团队把三级缓存的重任挂在MyBatis身上却忽略了一个事实MyBatis缓存只对单条SQL语句级别的相同入参生效稍微涉及动态SQL拼接出的不同SQL缓存key就完全对不上。所以我的经验是业务层缓存只用来缓存那些变化频率低、读取频率极高的“准静态数据”比如配置项、类目树、行政区划不要在业务主链路上依赖它来追求实时一致更别让它替代MySQL自身的Buffer Pool优化。5.5 缓存穿透、击穿、雪崩的一次性治理清单不管你在用Redis还是其他分销式缓存以下几个问题一定会碰到我整理成一张速查表问题现象实测有效的解决方案穿透大量查询不存在的key每次都打到MySQL布隆过滤器拦截空值缓存TTL设短一点击穿单个热点key过期瞬间请求全部打到MySQL互斥锁重建缓存热点key设置永不过期后台异步更新雪崩大量key同一时间过期流量全部落库过期时间加随机化多级缓存兜底限流降级不一致数据库更新了Redis还是旧值删缓存优先于更新缓存binlog订阅TTL兜底注意空值缓存这个方案我见过很多翻车例子——每个穿透请求都往Redis写一个10分钟的过期空值看起来简洁但恶意攻击时查询无数个不同key的随机id流量照样穿透到MySQL。空值缓存只能救业务逻辑上的低频穿透救不了攻击型穿透后者必须用布隆过滤器这类结构化的方式。6. 调优实录三个真实案例与排查工具箱6.1 案例一Buffer Pool命中率明明很高为什么查询还是慢有一次接手一个慢查询工单业务方说“MySQL已经被DBA调到飞起了Buffer Pool明明命中率99.9%查询还是动不动几百毫秒”。我检查后发现慢SQL长这样对一个几百GB的大表做ORDER BY create_time LIMIT 10000, 10。把条件套到索引上之后发现排序字段没有索引导致每一页数据都要回表取数还要在内存里做一次全量排序。Buffer Pool命中率再高也只是保证读取数据页不被磁盘卡住但SQL本身的计算成本、排序成本、回表次数完全不归缓存管。这个问题最后是通过改写SQL把它从深分页改成基于游标的模式才解决。这是个很典型的案例缓存命中的数字好看不代表SQL就优。遇到“命中率正常但慢”的情况先看执行计划再查慢日志最后再回头审视缓存配置这个顺序不能乱。6.2 案例二全表扫描把热数据“冲”出缓存另一个兄弟项目的典型症状是每天早上分析师跑一个全量报表SQL扫完一张2亿行的日志表后线上核心业务出现一波明显的响应延迟持续十几分钟然后又自己恢复。一开始怀疑IO被拖垮看了监控发现IO不算高但Buffer Pool读取性能指标大幅下滑。根因就是LRU保护没生效。该实例的innodb_old_blocks_time被人改成了0全表扫描的数据页一进旧区立刻就能“晋升”到热区把线上的高频热数据页全部挤到了淘汰边缘。把值改回默认的1000之后报表任务照样跑核心业务性能波动消失了。在这个场景里你几乎不需要换硬件、不需要加内存一个小小的LRU参数就是灵药。6.3 案例三重启实例后首小时慢查询扎堆这个案例来自一次版本升级。升级完后应用验证正常但过了不到30分钟监控报出大量慢查询。一看SHOW ENGINE INNODB STATUSBuffer Pool从0开始一点点回填所有SQL都在等磁盘页加载。虽然监控图表看起来像“数据库变差了”本质上只是冷缓存阶段。处理方式是两条腿走路第一把innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup打开升级重启时让缓冲池快速恢复第二重启前提前准备一个“预热SQL脚本”启动后用最典型的线上查询按主键批量查核心用户跑一轮。实测下来这两招配合后重启后5分钟内的热SQL响应时间就回到了平时的水平。顺带一提innodb_buffer_pool_load_at_startup加载过程会占用IO别在业务高峰期重启尽量停机窗口操作。6.4 常用命令与状态指标速查表最后把我运维中会反复用的诊断命令和关键指标整理如下-- 查看Buffer Pool相关状态 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool%; SHOW ENGINE INNODB STATUS\G; -- 查看表缓存、线程缓存情况 SHOW GLOBAL STATUS LIKE Open%; SHOW GLOBAL STATUS LIKE Threads_created; -- 查看排序和临时表落盘情况 SHOW GLOBAL STATUS LIKE Sort%; SHOW GLOBAL STATUS LIKE Created_tmp%; -- 在线调整Buffer Pool8.0支持 SET GLOBAL innodb_buffer_pool_size 8 * 1024 * 1024 * 1024;排查场景核心状态变量健康标准Buffer Pool大小是否够Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests命中率95%LRU是否被大查询冲垮innodb_old_blocks_time是否为0不应为0建议1000表缓存是否不足Open_tables/Opened_tablesOpen_tables不持续增长线程缓存是否不足Threads_created增速趋缓排序缓冲是否不足Sort_merge_passes数值长期为0或不增长临时表是否落盘Created_tmp_disk_tables占比25%个人经验来说MySQL缓存的调优百分之八十的工作量都在“观察现状”而不是“改参数”把慢查询日志打开观察Innodb_buffer_pool_*计数器的变化趋势再结合业务访问模式做对应调整。这比网上那些“凭直觉给一长串神仙参数”的做法要靠谱得多。最后一个建议别小看performance_schema和sys库里的视图按内存排序、按IO排序的统计信息往往能比状态变量更快地帮你定位瓶颈。缓存是MySQL的利器但用错方向反而是伤害希望这篇实战总结能让你少走几个弯路。