做MySQL调优这些年被问得最多的不是“索引怎么建”而是“MySQL怎么优化”。几乎每个人手里都有一份从网上抄来的my.cnf密密麻麻改了几十个参数问起来却说不清每个参数为什么存在。参数优化这件事难的不是把数值调大而是搞清楚你的业务吃的是内存、磁盘还是CPU然后对症下药。这篇文章只聊MySQL自身的参数优化不涉及索引、SQL改写和架构层面。内容更适合两类人一类是刚接手MySQL或者正在准备性能排查的开发工程师另一类是已经有几年经验、想系统梳理调优思路的从业者。读完你会明白哪些参数值得花时间调哪些参数改了等于没改以及为什么别人那套配置不能直接抄。1. 先想清楚参数优化到底在优化什么1.1 性能瓶颈的分类与定位思路MySQL的性能问题通常可以分成三类内存型、磁盘IO型、CPU型。参数优化本质上是把资源往瓶颈方向倾斜而不是让每个参数都“看起来很大”。拿最常见的线上场景举例。一个典型的电商订单库读多写少热点数据大概占总量20%那么InnoDB缓冲池命中率通常在99%以上。这时候你加再大的innodb_buffer_pool_size也提升不了吞吐因为大部分请求已经在内存里完成了。反过来一个日志采集库每秒写入上万行缓冲池命中率可能只有80%这时先看刷盘策略和redo log容量效果比无脑加大内存更明显。定位瓶颈有两条常用路径。第一看操作系统层。用top、iostat、vmstat观察CPU idle、磁盘util、swap情况。如果si和so长期不为0说明内存已经吃紧此时调任何MySQL参数都只是拆东墙补西墙。第二看MySQL内部状态。执行SHOW GLOBAL STATUS重点看Innodb_buffer_pool_read_requests与Innodb_buffer_pool_reads的比值、Threads_running、Created_tmp_disk_tables。这些变量会直接告诉你数据是不是大量走磁盘、线程是不是堆积、临时表是不是落盘了。1.2 为什么别一上来就改参数这是我在实际排查里见过最多的错误。某次帮一个团队看订单服务对方说“慢查询多感觉是参数不行”正准备把innodb_buffer_pool_size从8G改到32G。我让他先跑一条EXPLAIN结果发现核心查询根本没走到索引全表扫了几百万行。这种情况改了参数也是杯水车薪。参数优化的前置条件是慢SQL已经处理过、索引已经合理、数据模型没有大的设计缺陷。换句话说参数优化是最后一步不是第一步。还有个容易忽略的点参数改动需要压测验证。MySQL很多参数是“重启生效”或“在高并发下才体现差异”的改完不做对比测试你根本不知道改动是正向还是负向。我曾经把innodb_flush_log_at_trx_commit从1改成2写入QPS确实涨了一倍但压测到第20分钟暴露了主从延迟因为binlog的刷盘策略没跟上。所以每次只改一到两个参数改完立刻压测这是最稳妥的节奏。1.3 性能评估的基准线没有基准线就谈不上优化。至少要在改动前记录以下数据QPS、TPS、平均查询延迟、慢查询数量、磁盘IO util、swap使用量。建议准备一套压测脚本用工具模拟线上读多写少的比例固定压测10到15分钟。注意压测时间不能太短MySQL有缓存预热和连接池建立的过程压测前3分钟数据往往是虚低的。我习惯的做法是压测15分钟取最后5分钟的数据作为有效样本因为这个阶段连接数和缓存已经稳定。有了基准线再改参数改完跑同一套脚本对比才有意义。别用“感觉快了”来评估结果要用数字说话。2. 存储引擎核心参数InnoDB是性能的基本盘2.1 innodb_buffer_pool_size缓存池大小怎么算这个参数可能是MySQL里最值得优先调的。它决定InnoDB能缓存多少数据页和索引页直接影响磁盘IO次数。计算公式不复杂总内存 × 70% ~ 80%但要先扣掉其他组件占用。一台机器如果同时跑着Redis、ES等中间件MySQL能用的内存不会超过一半这时强行给缓冲池分太多反而会导致操作系统swap。我的经验是专机专用机器只跑MySQL缓冲池可以给到物理内存的70%左右。混布环境缓冲池建议控制在总内存的40%~50%留足余量给OS文件缓存和其他进程。以一个16G内存的专机为例innodb_buffer_pool_size可以设为11G到12G。换算成页数量大约是12G × 1024 × 1024 × 1024 / 16384 ≈ 786432个页这个值不需要记但你心里要有概念缓冲池是按16KB页为单位管理的。还有一个参数容易被忽略innodb_buffer_pool_instances。缓冲池默认拆成多个实例减少高并发下的锁竞争。经验值是缓冲池大于1G时instance数量设为8再大的池子可以到16。但别超过innodb_buffer_pool_size / 128M这个上限否则每个实例太小反而没有意义。改完这个参数后要做的第一件事不是看QPS而是查看命中率变化SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%。如果缓冲池命中率已经高于99%再调大也只是浪费内存。2.2 redo log与刷盘策略innodb_flush_log_at_trx_commit这是写入性能的关键参数也是最容易引发数据安全争议的参数之一。innodb_flush_log_at_trx_commit有三个取值取值行为安全性适用场景0每秒刷一次redo log事务提交时不刷最低最多丢1秒数据日志类、允许少量丢失的场景1每次事务提交都刷盘最高不会丢已提交事务金融、订单等强一致业务2每次提交写入OS缓存每秒刷盘中等OS宕机会丢多数互联网业务可接受很多人为了写入性能直接调成0或2但没意识到它会影响主从复制和崩溃恢复。最稳的做法是默认保持1如果写入确实是瓶颈才考虑调成2并且同步评估binlog的sync_binlog设置避免“redo安全但是binlog丢”的尴尬局面。我实际测过的数据一台普通SSD的机器取值为1时写入TPS大约1500改成2后能到4000左右提升明显。但压测环境模拟断电后确实出现了redo log部分丢失导致的启动恢复时间变长。所以如果业务不能接受任何已提交事务丢失就别动这个参数。2.3 innodb_io_capacity与后台刷页这个参数决定了InnoDB后台线程每秒最多能刷多少脏页到磁盘。默认值是200对于现在主流的SSD来说太保守了。调太小会怎样脏页堆积缓冲池里可用的空闲页越来越少最终当需要加载新页时前台线程被迫去刷脏页造成查询抖动。调太大又会怎样后台刷盘占用过多IO带宽和业务读写抢资源。我常用的参考值SATA SSD400~600NVMe SSD1000~2000普通机械盘保持默认200刷太高反而拖垮整体IO配合innodb_max_dirty_pages_pct使用。默认75表示脏页比例超过75%才开始加速刷对于高写入场景建议调到50甚至40让刷盘更均匀减少周期性IO尖峰。这两参数要一起调只调一个往往看不出效果。2.4 文件大小与增长方式避免碎片和等待innodb_data_file_path决定系统表空间的大小和增长方式。生产环境建议把autoextend保留但初始大小不能给太小否则频繁扩展会引发性能抖动。日志文件方面innodb_log_file_size是很多旧教程容易给低的参数。以前默认只有48M对现在动辄每秒几千写入的业务来说太小会导致日志切换频繁每次切换还要做一次检查点磁盘写入出现毛刺。现在8.0的默认值已经到100M以上但实际生产建议直接给到1G~2G。注意修改这个参数不是简单的SET GLOBAL需要先干净关闭实例再改否则启动时会报错。我见过一个案例某支付系统的写入量日均5000万innodb_log_file_size只有256M高峰期每两分钟就触发一次日志切换检查点跟不上磁盘util飙到90%。把这个参数调到2G后检查点频率大幅下降写入P95延迟从120ms降到30ms。这就是典型的“参数小到拖垮性能”的案例。3. 连接、并发与临时资源容易被忽视的“软”参数3.1 max_connections 与连接风暴max_connections默认151很多团队一遇到“Too many connections”就直接改成2000甚至5000。但连接数本身不是越高越好每个线程都要占用内存和CPU调度资源连接过多反而会让上下文切换爆炸。判断连接数是否合理看两个状态值Threads_connected当前打开的连接数Threads_running正在执行的线程数正常情况下Threads_running应该是个位数到几十远远小于Threads_connected。如果两者接近说明连接都在干活系统处于过载状态这时候调大max_connections只会让情况更糟。正确做法是处理慢SQL、加缓存、做读写分离把并发降下来。我一般这样估算max_connections可用内存 / 单连接平均内存占用 × 安全系数。单连接内存大约在几百KB到几MB之间取决于排序缓冲、连接缓冲等参数没法精确算保守做法是先按3MB估算。8G内存给了InnoDB 5G后剩3G那max_connections大概给800比较稳。另外要配合wait_timeout和interactive_timeout使用。很多连接数飙升其实是应用侧连接池忘了关连接导致的把wait_timeout从默认28800秒调低到300秒能快速回收空闲连接比无脑调大上限更健康。3.2 thread_cache_size线程复用的价值创建和销毁线程是有代价的。thread_cache_size控制MySQL缓存多少空闲线程供新连接复用默认值是8对于频繁短连接的业务来说偏低。我见过一个报表系统每次查询都新建连接高峰期每秒创建百来个线程。thread_cache_size设为64后Threads_created明显下降CPU使用率下降了10%左右。要判断这个参数是否够用看状态变量Threads_created。如果这个数字增长很快说明线程缓存命中率低需要调大thread_cache_size。但也不是越大越好一般64~128足够再高意义不大。3.3 sort_buffer_size / join_buffer_size / tmp_table_size这三个参数有个共同陷阱都写着“buffer”让人误以为越大越好。sort_buffer_size是每个线程排序时分配的内存不是全局共享的。连接数一多每个连接都可能分配这么多内存。我一个朋友把max_connections设为2000同时把sort_buffer_size调成64M结果MySQL还没扛住流量操作系统先OOM了。正确的思路是保持较小值2M~4M同时优化SQL让排序走索引而不是靠调大内存掩盖问题。join_buffer_size同理默认256K一般够用出现大量Using join buffer时优先检查连接字段有没有索引而不是调大它。tmp_table_size决定内存临时表的上限。Created_tmp_disk_tables如果很高确实可以适当调大从默认16M到64M但如果SQL本身产生了超大临时表调多大都白搭。先看EXPLAIN有没有Using temporary有的话优先改SQL。3.4 table_open_cache 与元数据锁table_open_cache控制表描述符的缓存数量。默认4000在表数量特别多的库上容易触发“Too many open files”错误。判断是否够用Open_tables如果长期接近table_open_cache说明缓存不够了。此时可以调大同时注意操作系统层open_files_limit也要跟着调不然MySQL调大了也没用。关于元数据锁很多运维只知道metadata_locks这个性能字典却不知道它和table_open_cache的间接关系。表频繁打开关闭会让MDL的压力变大高并发DDL时尤其明显。解决方向不是只调参数而是规范业务大表变更尽量用在线DDL工具避免长事务持有MDL。4. 日志、慢查询与监控参数让问题无处可藏4.1 slow_query_log 与 long_query_time慢查询日志是最基本的性能观测入口。默认关闭生产环境建议开启但要注意别在生产高峰期随时开日志量太大会反而拖累磁盘。我常用的一组配置slow_query_log ON slow_query_log_file /data/mysql/log/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time设为1秒对于多数OLTP业务已经够用不用设到0.5那么严苛否则日志量会很吓人。log_queries_not_using_indexes很有价值能帮你在早期发现SQL索引使用异常。但光开日志还不够要定期做慢查询分析。我习惯每周把慢日志拿出来跑一遍聚合相同模板的SQL按总耗时排序。注意是“按总耗时”不是“按单次耗时”排序。一个单次耗时300ms但每天跑几万次的查询比一个单次耗时3秒但每天跑一次的查询更值得优化。4.2 binlog 与 sync_binlogsync_binlog控制binlog每次提交后是否刷盘。默认值为1意味着每次事务提交都刷盘这是最安全的配置但会牺牲一定写入性能。很多优化MySQL写入性能的文章喜欢建议把sync_binlog改成0或100理由是减少磁盘fsync次数。但我必须提醒主从复制环境下innodb_flush_log_at_trx_commit1配合sync_binlog0一旦主机宕机可能出现“binlog没刷盘从库缺事务”的情况。要做这类调优请确保能接受这个风险。我的折中方案高写入场景设sync_binlog100配合innodb_flush_log_at_trx_commit2性能和安全取个平衡。主库上如果有强一致需求还是老老实实两个都是1。4.3 performance_schema 与状态变量观察MySQL自带的performance_schema默认在5.7之后开启但它本身也占用一定内存和CPU。很多低配机器为了省资源会把它关掉我反而建议保留因为排查问题时的收益远大于那点开销。真正需要的是会用。比如查看等待事件SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE wait/io/file/innodb% ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;这条SQL能快速定位“是不是InnoDB文件IO拖了后腿”。相比拍脑袋猜用数据说话是最有效率的调优方式。4.4 常用监控SQL与观察节奏我维护的MySQL实例日常只盯四个指标Threads_running判断当前并发是否健康Innodb_rows_read和Innodb_rows_inserted判断业务读写比例Created_tmp_disk_tables判断临时表落盘情况Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads判断缓存命中率建议做一张监控脚本每小时采样一次存成历史表。这样出了问题可以通过对比历史数据快速判断是突发还是渐进劣化。延展到参数调整时也能回看改动前后的指标曲线。5. 实操记录一次典型的三步调优过程5.1 第一步压测和基准数据收集讲一个我最近处理过的模拟项目X一个典型的内容管理后台MySQL版本8.0跑在16G内存、4核CPU、SSD磁盘的机器上总数据量约500G活跃数据约80G。第一轮压测数据如下读多写少共500并发读写比例7:3QPS12000TPS3500平均查询延迟35msP99210msThreads_running峰值180明显过高缓冲池命中率96.8%磁盘util70%看到这组数据后我先没有动参数而是查了慢日志。发现有一类分页查询用了ORDER BY create_time LIMIT 10000, 20每次要扫前面一万行这就是Threads_running高的主因之一。先把这类SQL改成“只查上一页最大ID”的方式第一轮优化后P99降到了160ms。先处理SQL问题这步太关键了。5.2 第二步内存与IO参数调整SQL优化后开始调参数。我的目标是让缓冲池容量覆盖活跃数据同时让后台刷盘更平滑。改动清单如下innodb_buffer_pool_size 12G innodb_buffer_pool_instances 8 innodb_io_capacity 1000 innodb_max_dirty_pages_pct 50 innodb_flush_log_at_trx_commit 2 sync_binlog 100 max_connections 800 thread_cache_size 64改动理由是活跃数据80G12G缓冲池加操作系统文件缓存能覆盖主要热点IO能力按NVMe SSD设为1000flush_log_at_trx_commit2配合sync_binlog100提高写入吞吐。这里的前提是业务方明确表示可以接受极端断电时丢少量事务如果业务不接受我会保持1而不动。5.3 第三步验证效果与回滚方案改完参数后重启MySQL再跑同一套压测。第二次结果QPS14800TPS5600平均查询延迟22msP99130msThreads_running峰值75缓冲池命中率99.2%磁盘util45%效果符合预期。但我也准备了一份回滚方案所有参数改动记录在案如果压测期间出现异常直接恢复旧配置重启。实际执行时没有回滚不过在调整innodb_flush_log_at_trx_commit的当天我备份了全量binlog防止万一需要追数据。这里给一个忠告不要在生产环境改参数的当天不做备份、不准备回滚脚本。我见过有人改参数后实例起不来花了两小时才排查出是innodb_log_file_size改大但没做干净关闭导致的。每次改动要像上线代码一样对待有变更记录、有回滚计划、有验证方案。6. 常见问题与排查技巧实录6.1 参数改了没生效先看这个改参数后没有效果大概率是两种情况。第一种用SET GLOBAL改的动态参数只对改后新建的会话生效已有的连接还是旧值。最典型的是long_query_time很多人在一个老连接里执行SHOW VARIABLES看到的还是旧值就以为没改成功。解决方法是重新连接客户端或者用SET PERSIST让配置固化。第二种改的是需要重启的参数比如innodb_buffer_pool_size、innodb_log_file_size。这类参数改了my.cnf但没重启自然不生效。建议统一用SHOW VARIABLES确认当前实际运行值而不是凭记忆判断。6.2 OOM与swap问题MySQL被OOM Killer杀掉是调参时最高频的“翻车现场”。尤其是同时调大多个per-thread buffer参数时内存需求会成倍放大。算一笔账max_connections1000sort_buffer_size16Mjoin_buffer_size16M每个连接仅这两项就32M1000个连接就是32G还没算tmp_table_size和InnoDB缓冲池。机器内存不够不OOM才奇怪。我的避坑原则是per-thread类参数保持小值全局缓存类参数根据机器内存分配。sort_buffer_size在大多数OLTP场景2M~4M足够join_buffer_size保持256K~1M只有tmp_table_size可以放松到64M左右。另外要不要关闭swap很多老资料建议vm.swappiness0但如果你没给MySQL预留足够的大页内存完全关swap反而容易OOM。我建议是保留少量swap作为兜底同时通过监控确保swap不为增长趋势。6.3 参数冲突与版本差异MySQL不同版本对参数的默认值差异很大网上很多优化文章是5.6时代写的直接套到8.0上会出问题。比如5.7开始支持innodb_buffer_pool_size动态调整8.0里部分参数改名innodb_log_file_size行为也变了旧配置里写query_cache_size在8.0会直接报错。我的建议是升级MySQL版本后不要沿用旧my.cnf而是用官方默认配置做基准再按业务逐步调整。别迷信网上“照着配就行”的文章多看一眼官方文档的参数说明省得踩版本坑。6.4 哪些参数改了也没用最后列几个我很少碰的参数query_cache_typeMySQL 8.0已移除旧版本中它的命中率在写入多的场景极低基本是负面优化。innodb_file_per_table建议保持ON但线上存量环境修改后需要手动做表整理不是“改完立即生效”的优化。key_buffer_size只影响MyISAMInnoDB引擎下调了也白调。performance_schema中的绝大多数开关不要为了“优化”随意关闭它会破坏你可观测性。调优参数这件事更多时候是“做减法”确认每个参数为何存在删掉无关的改动只保留真正影响当前瓶颈的那几个。长期维护下来比拿着一份大而全的配置到处抄要靠谱得多。结尾一点个人体会如果只让我说一个最深刻的经验那就是参数优化永远排在SQL优化和索引优化之后。同一个查询建一条合适索引能把延迟从1秒降到10毫秒而调半天参数可能只改善几十毫秒。先把SQL和索引做扎实再来动参数性价比最高。顺带分享一个日常小习惯每次变更参数我都会在配置里写清楚改动日期、原因和预期影响。这样半年后回头看配置每一行都知道当初为什么存在。MySQL的参数不是越多越好而是每一处改动都心里有数。能把“为什么改”讲清楚比会背一百个参数名更值钱。