做数据库运维这些年经常被问到同一个问题你们DBA天天调优化到底在调什么每次我都会回一句90%的MySQL性能问题最后都能追到两个参数上。一个是决定“读多快”的innodb_buffer_pool_size一个是决定“写多快”的innodb_flush_log_at_trx_commit。这话听着有点狂但真不是标题党。我处理过的慢库案例里要么是内存没给够数据页在磁盘和内存之间来回搬运查询全卡在IO上要么是日志刷盘太频繁事务提交全在等 fsync。这两类问题占了绝大多数。其他几十个参数当然也重要但在OLTP场景下属于“配套修正”不是“胜负手”。如果你是刚接手数据库的运维被慢SQL折磨得头疼的后端开发或者正准备给业务系统做一次性能体检的架构师这篇文章值得看完。我会把这俩参数的原理、设置方法、踩坑经历一次讲清楚最后还会给一份可以照着改的参数清单。1. 为什么敢断言“只有两个参数”1.1 扒开MySQL的“性能账本”一条SQL进来InnoDB先查缓冲池数据页在内存里就直接返回不在内存里才去磁盘读。写操作也一样事务提交要写redo log默认情况下每次提交都要把日志刷到磁盘才肯返回成功。所以整个MySQL的性能账本说白了就是两本账一本是读路径上的内存命中率一本是写路径上的日志刷盘频率。我习惯用一个生活化的类比来解释innodb_buffer_pool_size相当于你家冰箱的容量菜全塞冰箱里做饭随时取冰箱太小每次炒菜都得下楼买菜。innodb_flush_log_at_trx_commit相当于你下单后是让外卖员送到家还是每次都要亲自去店里取——前者省时间后者更可控但代价是腿跑断。MySQL 的所有性能调优本质都是在平衡这两件事。1.2 默认参数是为“能跑”设计的不是为“跑得快”很多人不知道MySQL 官方默认参数有一个很大的特点它面向的是“一台配置很烂的机器也能装上并跑起来”而不是“让你的业务跑得飞快”。拿innodb_buffer_pool_size来说官方默认值只有128MB。现在一台云服务器动辄16G、32G内存业务数据随随便便几十G你拿128M的缓存去扛这不叫调优这叫硬撑。还有innodb_flush_log_at_trx_commit默认值是1意思是每个事务提交都必须把redo log刷到磁盘。这个配置安全到极致但代价是每一次提交都在等磁盘IO确认。在高并发写入场景下这条路径就是最大的瓶颈。很多团队上线前根本没仔细调过参数全是安装向导下一步下一步点出来的结果一到流量高峰就出事。DBA的第一课就是先跳出“默认值能用”的思维。默认参数是保底用的不是给你上生产用的。1.3 二八法则管住读与写就管住了性能性能问题的表象千变万化有的CPU高有的IO高有的连接数被打满有的死锁连环爆。但真正去追根因绝大多数逃不出两类读路径上缓冲池命中率太低或者写路径上日志刷盘太频繁。抓住这两个核心参数你就有了一套解题框架。遇到任何慢MySQL先把这两个参数查一遍、调一遍很多时候问题就解决了大半。剩下的那些比如连接数、慢查询日志、SQL写法、索引设计都是在“骨架”之上做增补优化。这就是我常说的二八法则——20%的参数解决80%的问题。2. 参数一innodb_buffer_pool_size读性能的命根子2.1 这个参数到底在解决什么问题innodb_buffer_pool_size是InnoDB存储引擎最重要的内存区域它缓存数据页、索引页、插入缓冲、锁信息等一堆关键数据。MySQL一启动热数据会慢慢往这块地方放。如果这块地方够大查询大部分都能在内存里完成如果太小页面会被LRU算法不断换出下次访问又得去磁盘。磁盘随机读和内存读的差距有多大内存延迟是几十纳秒级别普通SSD随机读是几十微秒级别机械盘直接到毫秒级别。整整差了几个数量级。所以读性能好不好先看缓冲池给得够不够。怎么查当前的命中率执行这条SQLSHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;重点看两个值Innodb_buffer_pool_read_requests表示从缓冲池读取的逻辑读请求次数Innodb_buffer_pool_reads表示从磁盘物理读取的次数。命中率用公式算命中率 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests健康状态下OLTP业务的命中率应该在99%以上。要是低于95%基本可以断定缓冲池设置不合理或者有大量全表扫描SQL在污染缓存。2.2 生产环境的推荐值怎么算这个参数没有固定答案但有一套可以复用的计算方法。首先确认这台机器是不是MySQL独占然后根据物理内存按比例划定。我一般分三步走第一步看物理内存总量。第二步预留操作系统本身、MySQL线程栈、排序缓冲、连接缓冲需要的空间。第三步把剩余内存的70%左右给innodb_buffer_pool_size。举例来说16G内存的机器innodb_buffer_pool_size建议给8G到10G32G内存的机器建议给18G到24G64G内存的机器建议给40G到48G128G内存的机器建议给80G到96G。注意不建议超过物理内存的80%。我之前见过有人把128G机器的缓冲池直接设成120G结果系统OOMMySQL直接挂了。原因很简单除了缓冲池MySQL还需要为每个连接分配内存排序操作、临时表、线程栈全都要吃内存。OS自己也要活page cache也要空间。如果机器上还部署了Redis、其他应用实例那更要留出邻居的口粮。我见过同机跑了四个MySQL实例的奇葩架构每个实例都想抢内存最后全在swap里挣扎。2.3 改完怎么验证效果修改方式分两种。临时生效执行SET GLOBAL innodb_buffer_pool_size 21474836480;注意单位是字节20G就是21474836480。MySQL 5.7以上支持在线调整不需要重启。但要持久化还是得改配置文件里的my.cnf[mysqld] innodb_buffer_pool_size 20G改完之后别急着下结论。建议在业务流量稳定的情况下观察半小时左右再查一次命中率。我通常会记录改动前和改动后的命中率、平均响应时间、物理读次数这三个指标放在一起对比。有次给一个订单系统调参改动前命中率只有88%业务接口平均耗时260ms。把缓冲池从2G加到24G后命中率直接干到99.7%接口平均耗时降到70ms。这个效果不是我编的是实打实刷出来的数据。另外如果缓冲池设置得比较大建议带上innodb_buffer_pool_instances参数。这个参数控制缓冲池拆分成多少个实例默认情况下缓冲池超过1G时官方会默认拆成8个。对于大内存机器多实例可以减少并发访问时的锁竞争。2.4 这条路上的坑我都替你踩过了第一个坑一次性调太大。新手最容易犯的错就是把几十G的机器缓冲池直接调到90%内存。结果MySQL内存分配成功后业务一跑连接缓冲一涨操作系统直接触发OOM Killer。正确的做法是分步调整比如16G的机器先给8G跑两天看稳定再加到10G。第二个坑忽略了max_connections对内存的消耗。每个连接都有自己的排序缓冲、join缓冲、临时表空间连接数越多额外内存越大。缓冲池算得再好连接数爆了也一样完蛋。第三个坑改完没写配置文件。在线SET GLOBAL只能生效到下次重启之前。一旦MySQL重启一切回到解放前。我在线上见过太多“当时好了重启就傻了”的案例所以每次改参数我都会同步更新配置文件两边保持一致。3. 参数二innodb_flush_log_at_trx_commit写性能的胜负手3.1 三档取值到底差在哪innodb_flush_log_at_trx_commit控制了redo log的刷盘策略它有三个取值0、1、2。很多人记不住我用一张表给你讲明白。取值事务提交时的行为系统崩溃时可能丢失的数据性能特征0事务提交不写日志文件由后台每秒刷一次盘最多丢1秒的数据最快但最不安全1默认每次提交都把redo log刷到磁盘基本不丢最安全但最慢2每次提交写入操作系统缓存由后台每秒刷一次盘最多丢1秒且操作系统层面故障也会丢快安全性和性能较均衡关键区别在于0和2。0是事务提交时连操作系统缓存都不写redo log只留在MySQL的内存里2是事务提交时先把日志写到操作系统的page cache真正刷盘交给后台每秒一次。所以2比0多一道保险即使MySQL进程崩溃日志也已经在操作系统里了最多丢1秒。3.2 为什么“双1”最安全也最贵默认情况下如果没开binlog写路径上就是innodb_flush_log_at_trx_commit1。如果开了binlog还有一个配套参数sync_binlog1每次提交把binlog也刷盘。这两个参数同时为1就是DBA圈里说的“双1”。双1带来的安全性是顶级的任何一个事务提交redo log和binlog都实实在在落盘了系统宕机也不会丢数据。但代价是什么每提交一个事务至少要等两次fsync完成。在高并发写入下这个等待时间会被无限放大。打个比方双1就像你每次点外卖都要亲自跑到店里跟老板确认“这单我确实付了钱”然后端着饭回家。安全是安全但时间全耗在路上了。SSD还好一些机械盘直接灾难现场。我优化过一个跑在机械盘上的老系统默认双1配置下写入TPS只有几百改成2之后瞬间到几千。物理硬件没动一行只是调整了刷盘策略差距就是这么大。3.3 生产环境到底怎么选这个参数没有绝对正确的答案只有适合当前业务的答案。我一般按业务类型给建议第一步判断业务是否可以接受极端情况下丢失最多1秒的事务。如果能接受直接用2配合sync_binlog1这是互联网OLTP场景下最主流的选择。第二步如果是金融、账务、交易等强一致场景那就老老实实保持1保持双1。安全和性能冲突时安全优先。这时候优化手段就转向硬件升级比如换更强的SSD、加内存降低落盘频率。第三步海量日志、临时数据、可重放任务这类非关键写入可以设成0。但我会明确警告0的安全性最差一旦MySQL异常崩溃最近一秒的事务全部蒸发了必须有完善的数据重建机制才行。同步复制场景下还有一个细节从库可以设置成2不影响主库的持久性还能降低从库的写入延迟。主库则根据业务诉求决定。这些权衡都要写在变更单里让团队所有人都知道风险边界在哪。3.4 实测数据一个写入服务的“复活”过程说一个真实案例。之前帮一个电商订单中心做优化这个库的数据量不算大但写入吞吐上不去。每天高峰时段订单服务超时告警不断。当时配置就是典型的双1刷新率极高每秒几百次提交把SSD的写IO全耗在fsync上了。我先执行了在线修改SET GLOBAL innodb_flush_log_at_trx_commit 2;然后在配置文件里同步持久化[mysqld] innodb_flush_log_at_trx_commit 2 sync_binlog 1这里保留sync_binlog1是因为我们开了binlog而且希望尽量保证binlog不丢同时硬件的刷盘压力已经从两次降到一次。再配合把innodb_log_file_size从默认的48M调大到1G减少日志文件切换频率。改完之后写入TPS从峰值1500提升到接近6000高峰期的写入延迟从200ms降到40ms左右。最直观的表现是订单服务不超时了监控面板上一片绿。整个过程只动了两个核心参数加一个日志容量参数没有改一行业务代码。4. 只有两个参数还不够配套参数这样搭4.1 连接数它不是放大器是保护阀经常有人问那max_connections不重要吗连接都被打满了啊。我的回答是连接数很重要但它不是“性能放大器”而是“保护阀”。默认的max_connections只有151确实不够用。但它调大并不能让单条SQL跑得更快只能让更多请求同时进来。如果后端SQL本来就慢调大连接数只会让一堆请求排队等待甚至把内存吃光。所以我把它放进配套参数而不是核心参数。实际设置要看机器资源一般中小业务给500到1000足够超过2000就要掂量一下内存了。每个连接平均可能占几百KB到几MB内存连接数乘以单连接内存这笔账要会算。4.2 日志链路redo log 容量别忽略写性能优化里除了刷盘策略redo log的容量也常被忽略。MySQL 8.0.30之前用innodb_log_file_size控制一个文件默认48M配合innodb_log_files_in_group组成一组日志文件。8.0.30之后引入了innodb_redo_log_capacity逻辑更简单直接设定redo log总容量。redo log太小会导致什么日志频繁切换checkpoint频繁推进脏页还没来得及刷盘就被迫写。表现就是磁盘写放大严重性能波动明显。我一般建议在1G到4G之间起步大写入业务直接上8G。这个操作因为涉及日志文件通常要重启实例才生效所以建议在变更窗口内一起改。4.3 慢查询日志优化之旅的真正起点两条核心参数调完之后真正的长期优化才开始。我强烈建议把long_query_time从默认的10秒改小设成1秒甚至0.5秒。原因很简单等一个SQL慢到10秒才发现业务早就凉了。long_query_time不是性能参数它是体检报告。让它把慢SQL捞出来你再针对这些SQL做EXPLAIN、补索引、改写语句。很多团队以为慢查询日志只要开着就行其实默认10秒的阈值等于形同虚设。在一个日活百万的系统里0.5秒以上的查询可能都够用户感知到卡顿了。把阈值设小你才能看清MySQL每天都在为什么样的SQL“加班”。4.4 一份可以直接落地的起步参数清单以一台16核64G内存、纯OLTP业务、MySQL 8.0实例为例我会给这样的起步配置[mysqld] innodb_buffer_pool_size 40G innodb_buffer_pool_instances 8 innodb_flush_log_at_trx_commit 2 sync_binlog 1 max_connections 500 long_query_time 1 innodb_redo_log_capacity 4G注意这是“起步”不是“最优”。每个业务都有自己的特性参数只是工具最终要以压测结果和线上监控数据为准。我的习惯是一次只改一到两个参数改完观察半天到一天出问题才能快速定位是哪个变更引起的。最忌讳的就是一次性把十个参数全改了到时候性能降了都不知道该回滚哪个。5. 实战复盘两个参数救回一个慢库5.1 现象CPU不高业务却慢得离谱有一次接手一个内部系统的优化现象很典型接口平均响应时间从50ms涨到了300ms数据库CPU才用到20%磁盘IO也不高。当时业务方怀疑是代码问题但我们DBA第一反应是看数据库内部状态。这种“CPU不高、IO不高、业务却慢”的案子十有八九是内存命中率出问题了或者锁等待严重。先排除锁SHOW ENGINE INNODB STATUS里没有明显的锁等待那就重点查缓冲池。5.2 摸底从状态变量里拿证据执行查询后拿到了两组关键数据SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%;Innodb_buffer_pool_read_requests的值很高但Innodb_buffer_pool_reads的值同样不低。简单算了一下命中率只有88%。这个数字意味着大约12%的读请求都打到磁盘上了。系统里低频SQL一多物理读就飙升整个读路径的延迟全被拉高。再查写入侧SHOW ENGINE INNODB STATUS里redo log相关的写入等待很高fsync次数非常密集。而且当时的参数就是默认的innodb_flush_log_at_trx_commit1写每次提交都在等盘。5.3 第一刀先把缓冲池喂饱当时的实例配置是16G内存innodb_buffer_pool_size居然还是官方的128M。这种配置跑生产不慢才怪。我先做了评估这台机器上没有部署其他应用属于MySQL独占理论上可以给出比较激进的内存分配。实际给到了12G因为还要留一些给连接缓冲、排序缓冲和操作系统。修改后观察了一个小时命中率从88%涨到了99.6%大部分读请求都在内存里解决了。读接口的响应时间果然先降了下来从300ms掉到150ms左右。但写入侧的问题还在高峰期依然有慢请求。于是准备动第二刀。5.4 第二刀把刷盘策略从“每次都刷”改成“每秒刷”经过业务确认这个系统可以接受极端情况下丢失大约1秒的事务于是把innodb_flush_log_at_trx_commit从1改成2同时保留sync_binlog1来尽量保证binlog不丢。这个改动的影响是立竿见影的。提交事务不用再等磁盘fsync返回写入路径上的等待时间大幅缩短。再配合把redo log容量加大减少日志切换频率整个写入通道顺畅了很多。5.5 优化效果一张表看清收益指标调整前调整后平均响应时间300ms80ms缓冲池命中率88%99.6%写入TPS峰值15006000高峰超时告警频繁0这个案例并不复杂没有用到什么高深技术就是把两个核心参数调对了。但就是这么简单的调整把业务从“每天告警不断”拉回到“平稳运行”。很多数据库问题真的就是基础参数没配好而已。6. 改参数的高频坑与排查速查表6.1 动态参数和静态参数一定要分清MySQL参数分两种动态参数可以直接在线修改立即生效静态参数只能改配置文件重启后生效。这两个核心参数恰好在MySQL 5.7以上都支持动态修改这给了我们很大的操作空间。但注意能用SET GLOBAL不等于可以只在线改不改配置文件。我经常强调一句口诀在线修改是临时方案配置文件持久化才是最终方案。否则哪天机房断电MySQL一重启所有性能优化全部归零业务绝对炸锅。有些参数是静态的比如老版本的innodb_log_file_size千万别以为也能在线改。改完配置文件不重启MySQL不会报错但也压根不会生效。最好每次改参数前都去官方文档确认一下这个参数是动态还是静态。6.2 参数值不是越大越好改出OOM是怎么收场的参数调优最容易犯的错就是“贪心”。我见过一个真实事故运维同学看监控里内存充足把innodb_buffer_pool_size直接设成物理内存的95%然后执行在线扩容。结果MySQL内存占用一路疯涨操作系统留不住page cache最终内核OOMMySQL进程被杀了。事故恢复全靠快速回滚先把配置文件改回安全值重启MySQL再逐步把缓冲池一点点加回来。从那以后我对所有变更都要求有回滚方案。改参数必须要有退路没想好怎么回滚就不要在线上乱动。还有一点大内存机器上开大缓冲池建议配合innodb_buffer_pool_instances分片。比如40G缓冲池拆成8个实例每个5G并发访问时减少锁竞争对大并发读有明显帮助。6.3 云数据库上参数改不了怎么办现在很多业务跑在云数据库上托管实例往往不暴露全部参数。比如有的云厂商只允许你在控制台的参数组里改几个“安全参数”innodb_flush_log_at_trx_commit这种关键项直接锁死。遇到这种情况我的建议是不要把精力浪费在“怎么绕过限制”上而是换个思路。既然没法改刷盘策略就通过提升实例规格来换取IO能力或者用只读实例把读流量分流减轻主库压力。SQL优化和索引设计什么时候都不亏在云上甚至比调参更划算。云数据库也有一个好处它背后的基础设施通常已经做了很多优化默认参数比自建MySQL要合理一些。所以也别把云数据库一棍子打死先看看控制台里哪些参数能改、哪些能调已经在合理范围内的就不必折腾。6.4 疑难杂症速查表现象优先排查方向CPU不高但接口就是慢缓冲池命中率、锁等待逻辑读高物理读也高innodb_buffer_pool_size、是否有全表扫描写入TPS低磁盘写等待高innodb_flush_log_at_trx_commit、redo log容量用户连接被拒绝max_connections、thread_cache_size慢查询一堆响应时快时慢慢日志阈值、SQL索引、CPU争抢大促前压测性能上不去备份监控、参数预期验证、压测数据确认遇到性能问题不要慌先按表里对应关系定位。大多数情况下问题都能收敛到“读”和“写”这两条主线中的一条。抓不住主线改再多个参数也是白搭。做了这么多年DBA我最大的体会是参数调优不是炫技而是把基础打牢。接手一个新数据库我永远先看这两个参数对不对再看慢查询日志里藏着哪些SQL。这两个参数一天不调对后面所有的索引优化、读写分离、分库分表都是在沙子上盖楼。最后再分享一个小经验每次调完参数别急着走。把变更前后的监控截图保存下来把效果数据记录在案。三个月后再翻出来看你会发现自己对MySQL性能的理解比当初又深了一层。