写这篇东西之前我先把话说在前面MySQL的性能优化网上一搜一大把把innodb_buffer_pool_size调到物理内存的70%这种话说句不好听的全是正确的废话。为什么因为你连自己的瓶颈在哪都不知道上来就调一个最显眼的参数运气好能糊弄过去运气不好就是把问题从磁盘搬到了内存或者从内存搬到了CPU折腾一圈回到原点。我这些年处理过的MySQL性能问题少说也有几十个涉及的事包括电商订单库、日志分析库、内部管理系统库场景各不相同但最后沉淀下来的经验高度一致参数优化是性能提升里性价比最高的一环但前提是你得知道每个参数到底在干什么、动了它会发生什么连锁反应以及怎么验证它真的有效。这篇文章就专门聊MySQL参数优化这件事我会把最值得动手的参数按内存、会话、日志、临时表几个维度拆开讲还会把那些容易踩坑的伪优化点拎出来单独说。适合谁看已经过了会写增删改查阶段、开始接手生产库维护的开发者以及公司里没有专职DBA、但数据库出问题都得你上的兼职运维。1. 为什么性能优化要从参数开始1.1 参数是杠杆但很多人搞错了发力方向先想一个问题同样是MySQL为什么部署在8核16G的机器上能支撑每秒几千次查询换台32核64G的机器反而性能下降这就是典型的参数没跟着硬件走。MySQL默认配置是为开发环境准备的它追求的是在任何机器上都能启动而不是在你这台机器上跑得最快。参数优化的本质是让MySQL对你那台服务器的硬件资源做合理调配。这里面核心的资源就三类内存、CPU、磁盘IO。不同业务场景对这三类资源的敏感度完全不同。比如一个纯OLTP的订单系统90%的请求是主键查询和短事务它最需要的是把热数据尽量装进内存减少磁盘IO而一个跑报表的库动辄全表扫描加排序它最需要的是把排序缓冲、临时表策略调好否则内存再大也被排序临时文件耗光。所以我的习惯是拿到一台新库先不着急改任何参数先用一条SQL把当前实例的真实状态摸一遍SHOW GLOBAL STATUS; SHOW VARIABLES;再把status里的关键计数器和variables里的配置项对起来看尤其是下面几个Innodb_buffer_pool_read_requests和Innodb_buffer_pool_reads的比值决定缓冲池命中率Threads_connected和Max_used_connections看看连接数压力Created_tmp_disk_tables和Created_tmp_tables看临时表下盘率Innodb_log_waits看redo log是否有写入等待。这套先看状态、再动配置的习惯能帮你避开90%的瞎调。1.2 默认参数的几个反直觉事实很多刚接触MySQL调优的人会被默认参数的真实面貌惊到。举几个有代表性的例子第一innodb_buffer_pool_size的默认值是128M。在今天这个内存动辄32G起步的时代这个值小得离谱。如果你的库只有几百MB数据128M勉强能撑但数据量一旦到几个GB缓冲池不够用的话读取请求就会大量穿透到磁盘延迟从毫秒级变成几十毫秒甚至上百毫秒。第二max_connections默认151。很多内部系统业务量不大平时连接数也就几十但一旦某个深夜任务批量跑起来或者某个应用连接池忘了设上限瞬间就能打满151然后应用侧报Too many connections。这个报错我见得太多了但大多数情况下问题不是连接数太少而是有连接泄露光调大数值是治标不治本。第三query_cache_type在MySQL 5.7及之前默认开启但MySQL 8.0直接把整个查询缓存功能移除了。这就是为什么网上大量调优文章已经过时——那些教你把query_cache调大的建议在8.0上连参数都不存在了。你如果还在用5.7也建议把查询缓存关掉因为在高并发写入场景下维护查询缓存的锁竞争带来的开销远大于它省下的那点命中收益。理解这些反直觉事实之后接下来的所有调优动作才有意义。2. 内存类参数最优先调整的配置项2.1 innodb_buffer_pool_size是永远绕不开的第一参数如果你只想动一个参数那就动innodb_buffer_pool_size。它是InnoDB缓存数据页和索引页的地方相当于MySQL的热数据工作台。你查询的每一行数据都要先被读进这个缓冲池才能返回给客户端你修改的每一行数据也是在这个缓冲池里改完再由后台线程刷到磁盘。这个参数设置多少合适流传最广的说法是物理内存的70%左右但我的建议是分两步走。第一步先确认你的机器上除了MySQL还跑了什么。如果是专用数据库服务器没有别的应用抢内存那可以按以下区间估物理内存建议buffer pool8G4G ~ 5G16G10G ~ 12G32G20G ~ 24G64G40G ~ 48G这里没有给到70%是因为你还要给会话级的排序缓冲、连接线程、操作系统文件缓存留点空间全塞给buffer pool一旦并发上来排序临时文件、连接线程的内存分配就会出现抖动。第二步也是更准确的做法是观察实际的数据量。如果information_schema.tables里统计出来的所有表数据加索引总共有8G那你的buffer pool哪怕给到20G也只是让命中率更高并不会带来等比例的性能提升。反过来如果数据总量有50G你只有16G内存那无论如何也不可能把全部热数据装下这时就要靠索引设计和查询优化来降低IO量而不是单纯加buffer pool。修改方式在MySQL 5.7及以后可以直接在线调整不用重启SET GLOBAL innodb_buffer_pool_size 12884901888;注意这里的单位是字节而且设完后要把配置写进my.cnf的[mysqld]段否则重启就丢了。8.0还支持设置多个buffer pool实例参数是innodb_buffer_pool_instances在buffer pool大于1G时建议设置成CPU核心数减少并发访问时的内部锁竞争。2.2 别忽视key_buffer_size虽然你大概率用的是InnoDBkey_buffer_size是MyISAM引擎的索引缓存。现在的MySQL默认引擎是InnoDB很多人直接把MyISAM相关参数忽略了。但现实情况是mysql系统库里的表以及不少历史遗留业务表可能还是MyISAM。如果这些表被频繁读取key_buffer_size太小会直接导致索引页反复从磁盘读。这个参数不用给太大64M到256M足够。太大反而浪费内存因为MyISAM的索引缓存不像InnoDB的buffer pool那样有预读和淘汰算法上的精细管理。检查方式SHOW GLOBAL STATUS LIKE Key_reads; SHOW GLOBAL STATUS LIKE Key_read_requests;Key_reads / Key_read_requests如果大于0.01说明索引页在磁盘和缓存之间的交换太频繁可以调大如果长期接近0就维持现状。还有一个容易忽略的点所有MyISAM表的索引缓存是共享的所以一旦某个超大索引的扫描进来可能会把其他表的索引页全部挤出缓存。所以对还在用MyISAM的系统我的建议是尽快迁到InnoDB而不是在key_buffer_size上精打细算。2.3 排序和连接缓冲按需分配但不能失控除了buffer pool这个全局大户MySQL还会为每个会话分配一些私有内存其中最常用的两块是sort_buffer_size和join_buffer_size。先说sort_buffer_size。很多人有个误解以为它越大排序越快结果一上来就设成64M然后发现内存一会儿就爆了。为什么因为sort_buffer_size是每个连接独立分配的。你如果有100个并发连接同时做排序每个连接分配64M那就是6.4G内存瞬间没了。正确的设置思路是调高前先看有没有排序临时文件。SHOW GLOBAL STATUS LIKE Sort_merge_passes;Sort_merge_passes如果持续增长很大说明排序内存不够用了数据被分段写到临时文件再归并。合理做法是把sort_buffer_size从默认的256K逐步调到1M~2M之间观察Sort_merge_passes是否明显下降。不要一上来就是几十M很多场景下排序的数据量本来就不大256K到512K足够。join_buffer_size同理它是为无法使用索引的join分配的内存块默认256K。注意in case your joins are larger than bufferit may allocate multiple buffers。但这个参数如果过大同样会在高并发时吃掉大量内存。最靠谱的做法不是调大它而是通过改写SQL或者加索引把没有索引的join消掉。此外max_connections和table_open_cache也值得提一句。table_open_cache控制的是表描述符的缓存数量默认值2000在8.0里对多数场景够用但如果表特别多或者分区表很多可以按SHOW GLOBAL STATUS LIKE Open_tables的实际值往上调。这个参数调大后每个表描述符会占用少量内存但换来的是访问表时不用反复打开关闭文件描述符。3. 并发与会话类参数连接数不是越大越好3.1 连接数为什么不能盲目调大max_connections这个参数可能是被修改得最频繁、也最容易被误解的参数。当业务方看到Too many connections时第一反应就是把连接数调大啊然后直接从151改成1000甚至2000。先不评论这个思路对不对我们来算一笔账。每个MySQL连接哪怕只是空闲挂着也要占用一个线程加上线程栈、排序缓冲、网络缓冲等单连接占用的内存大致几MB到十几MB不等。如果有500个连接同时活跃并且每个连接都带着2M的sort_buffer那光排序缓冲就是1G。你如果还有100个连接在跑大排序每个分配了32M那直接就是3.2G。连接数真的不是越大越好它是一个共享内存和CPU时间的博弈。更关键的是连接数打满往往不是并发真有这么高而是有连接没释放。最常见的场景是应用层的连接池配置了maxActive200但某个慢查询把连接占住了几十秒新请求就只能排队最终把连接池和MySQL的连接数一起打爆。这种情况下你把max_connections调到5000也无济于事因为瓶颈是那几条慢SQL。所以正确的排查链路应该是先看SHOW PROCESSLIST统计每个连接的状态和耗时定位到耗时长的查询看是不是有全表扫描、锁等待、或者索引失效优化SQL和索引让连接快速释放最后才考虑调max_connections而且调完要监控内存和上下文切换。max_connections合理的设置参考可以先算一下你单条连接平均内存开销。一个粗估方式是thread_stack sort_buffer_size join_buffer_size net_buffer_length再乘以你期望的并发峰值加上innodb buffer pool等全局内存整体不超过物理内存的70%到80%就算安全。比如8G内存buffer pool给了4G剩余4G按每个连接平均2M算连接数上限大概在1500左右但如果你的业务SQL普遍带着大排序最好把max_connections压到800以内。3.2 thread_cache_size让线程复用而不是频繁起停thread_cache_size控制的是MySQL为连接线程缓存的空闲线程数量。连接断开时MySQL不会立刻销毁线程而是放入缓存等待复用新连接进来时如果能从缓存里拿到线程就不需要重新创建线程。线程创建的代价虽然不大但在每秒成百上千次连接建立的场景下累积起来也是可观的CPU开销。这个参数怎么调观察Threads_created和ConnectionsSHOW GLOBAL STATUS LIKE Threads_created; SHOW GLOBAL STATUS LIKE Connections;如果Threads_created相对Connections的比例很大说明大量连接都是新建线程就需要调大thread_cache_size。一般建议在64到256之间结合你的连接数峰值来定不需要太夸张。不过要注意thread_cache_size只对短连接场景有明显帮助。如果你那边的应用用的是长连接池连接是常驻的那么这个参数基本不起作用调不调都无所谓。我见过有人不管什么场景都把它调到500结果线程缓存里堆了一堆空闲线程纯属浪费内存。3.3 innodb_thread_concurrency和innodb_commit_concurrency这两个参数是控制InnoDB内部并发度的调的人不多但调错了影响却很大。innodb_thread_concurrency限制了InnoDB内部同时执行的线程数。默认值是0表示不限制。在CPU核心数较少的机器上比如4核如果不限制并发InnoDB内部的多个线程争抢CPU调度反而会导致大量的上下文切换吞吐量下降。这时可以设置成8或者16一般是CPU核心数的2倍让InnoDB内部的并发操作控制在合理范围。但要注意这个参数设置太小会直接限制并发能力。我见过有人把它设成2结果系统吞吐量直接腰斩。正确的做法是设置后压测观察以QUERY_RESPONSE_TIME和Innodb_row_lock_waits作为衡量指标而不是凭感觉。innodb_commit_concurrency控制的是提交阶段可以同时并行的线程数。它主要影响的是大量并发小事务提交时的锁竞争。如果SHOW ENGINE INNODB STATUS里能看到commit阶段的等待比较多可以尝试设置成CPU核心数2到4倍的值。但绝大多数中小业务场景这个参数保持默认0不限制就没问题不建议乱动。4. 日志与IO类参数让磁盘读写不再拖后腿4.1 innodb_flush_log_at_trx_commit安全和性能的拔河这个参数可能是InnoDB里性能提升最明显、但风险也最直接的参数我每次讲都得先强调它的副作用。innodb_flush_log_at_trx_commit有三个值1每次事务提交都把redo log刷到磁盘。最安全绝不会丢一个已提交事务的日志但也是最慢的。每次commit都伴随着一次磁盘fsync。0事务提交时不写磁盘只写到内存的log buffer由后台线程每秒刷一次磁盘。最快但如果MySQL进程崩溃可能丢失最近1秒内的事务日志。2事务提交时写到操作系统缓存但不主动fsync后台每秒刷一次磁盘。速度接近0安全性略好于0但如果是操作系统宕机同样可能丢最近1秒的数据。线上数据库如果对数据安全要求极高比如支付、订单类我强烈建议保持1不要动。就算你用各种手段把它改成2换取的那点性能提升根本扛不住一次事故带来的业务损失。那什么时候可以改成0或2我自己的标准是你非常清楚这个库丢了最近1秒的少量数据也能接受比如内部报表库、日志归档库、或者纯缓存性质的表。在这种场景下从1改成2在高并发写入时通常能带来20%到50%的写入性能提升效果立竿见影。如果你既不想丢数据又嫌fsync慢有个折中方案把innodb_flush_log_at_trx_commit保持1但把磁盘换成SSD。很多场景下机械硬盘的fsync延迟在5ms到10ms而SSD能压到1ms以内这种硬件层面的提升比改参数更健康。4.2 innodb_io_capacity和innodb_io_capacity_max这两个参数控制的是InnoDB后台线程的IO能力上限直接影响刷脏页和合并插入缓冲的速度。默认innodb_io_capacity是200innodb_io_capacity_max是2000。如果你的数据盘是普通机械硬盘200是合理的不用动。但如果是SSD默认值就太保守了。举个例子一个写密集型的表每秒产生大量脏页后台刷脏线程每秒钟最多只敢做200次IO那脏页队列会积压最终触发innodb_max_dirty_pages_pct的强制刷盘反而带来IO尖峰。这种情况下把innodb_io_capacity调到1000到2000innodb_io_capacity_max调到4000到6000可以让刷脏行为更加平滑。怎么判断你该不该调看SHOW ENGINE INNODB STATUS里的脏页比例Modified db pages: xx%如果这个比例长期超过70%并且Innodb_buffer_pool_pages_dirty持续走高说明刷脏跟不上就可以考虑调高innodb_io_capacity。当然前提是你的磁盘确实能扛住更高的IOPS否则就是把压力往后延。还有一个常被忽略的点innodb_adaptive_flushing默认是开启的它会根据redo log的生成速率动态调整刷脏速度。这个特性建议保持开启很多时候它自己就能把刷脏节奏调好你给innodb_io_capacity设个合理上限就行。4.3 sync_binlog和binlog相关参数sync_binlog控制binlog多久刷一次磁盘。默认值是1表示每次事务提交都把binlog刷到磁盘保证binlog不丢。如果你把sync_binlog改成0性能会提升但binlog可能丢失最近一段事务主从复制也就有风险。这里有一个组合策略当innodb_flush_log_at_trx_commit1且sync_binlog1时是最安全但也最慢的如果为了性能把innodb_flush_log_at_trx_commit改成2同时sync_binlog保持1那丢失数据的风险窗口主要在MySQL日志上。反过来如果两个参数都改成0写入性能确实好看但你可要掂量清楚。另外binlog_cache_size也是个大坑点。它会为每个连接分配binlog写入缓存默认32K。如果一个事务写入的binlog数据量超过这个值就会使用临时文件。观察Binlog_cache_use和Binlog_cache_disk_use如果后者比例很高说明经常有事务超出缓存可以适当调大比如到64K或128K。但注意这和sort_buffer_size一样的逻辑它是会话级的调太高同样会造成内存压力。5. 查询缓存、临时表和其他被过度讨论的参数5.1 为什么MySQL 8.0直接移除了查询缓存到现在还有不少老文章在推荐调大query_cache_size提升性能这在MySQL 5.6及更早版本里勉强算个办法但到了5.7和8.0这个建议已经过时了。查询缓存的问题在哪它虽然可以把SELECT结果缓存在内存里但每次表有写操作这个表相关的所有查询缓存都会被清空。在高并发写入场景下清空操作本身需要加锁这个锁会让其他SELECT也停下来等待结果是缓存命中率越高、写操作越频繁系统反而越慢。这也是MySQL官方最后决定在8.0里移除查询缓存的原因。如果你还在用5.7query_cache_type默认是开启的我建议直接关掉query_cache_type0 query_cache_size0关掉后那些原本被query_cache占用的内存会自动释放给InnoDB使用对大部分业务来说都是正收益。5.2 临时表下盘很多人看不懂的隐性问题临时表是优化器处理排序、分组、去重、子查询时的草稿纸。Created_tmp_disk_tables表示有多少临时表被放到了磁盘上。如果这个数字很大说明内存临时表空间不够或者临时表的数据太大超过tmp_table_size和max_heap_table_size的限制。这两个参数默认都偏小16M和16M具体哪个生效取两者中较小值。我的建议是在专用数据库服务器上可以把这个值调到64M左右但不要超过256M因为它同样是会话级别的在高并发场景下每个会话都可能开出64M的临时表内存积少成多。更重要的判断依据是临时表下盘不一定是配置问题更多时候是SQL写法问题。比如一个没有索引的join配合group by或者一个大字段的distinct都容易产生超大临时表。这种情况与其调tmp_table_size不如先看执行计划EXPLAIN SELECT ... GROUP BY ...如果Extra列出现Using temporary; Using filesort那就要么加索引要么改写SQL。调大tmp_table_size只能让你多走一段内存绕不开的根本问题还在。5.3 table_cache和open_files_limit文件描述符的隐形天花板这个知识点很少被拿出来讲但出问题的时候特别隐蔽。MySQL打开每个表都需要一个文件描述符而操作系统的open_files_limit往往限制了进程最多能打开多少文件。如果你的表很多或者使用了大量分区表table_open_cache设置过大但操作系统的文件描述符上限不够MySQL会直接报Too many open files。检查方法ulimit -n一般生产环境建议ulimit -n至少到65535MySQL的open_files_limit再根据这个值来设置。table_open_cache建议设置在2000到4000之间太大了没有意义因为表描述符也是要占内存的。针对分区表额外提醒每一个分区在打开时都被当成一张独立表占用描述符所以大分区表的table_open_cache消耗会非常快。遇到这种场景可以给table_open_cache_instances调大默认16让不同线程分摊打开表描述符的锁竞争。6. 从随机调参到系统性优化我验证参数效果的实战套路6.1 建立基线没有参照系就不要谈优化我给任何数据库做参数调整之前一定会先建立一个性能基线。基线的含义是在调整之前你已经知道当前系统在某个并发压力下QPS、延迟、磁盘IO、CPU占用分别是多少。没有这个参照系你调完参数说感觉变快了那叫自我安慰有基线你才能量化说话。压测工具方面我常用的有sysbench和mysqlslap。sysbench更专业一些可以模拟oltp_read_write、oltp_point_select等多种场景。压测时注意不要只压你能扛住的低并发一定要压到系统出现拐点看看极限在哪。举个实际例子某内部系统8核16G机器日常QPS大概800偶尔活动期间冲到2000。调整前先用sysbench跑了个128线程的oltp混合读写得到基线QPS约1800p99延迟45ms。然后我把innodb_buffer_pool_size从1G调到8G同样压测QPS变成了2600p99延迟降到18ms。这个提升是可见的、可复现的再接下去调整innodb_io_capacity和max_connections每次改动都重复压测才得到一套合理配置。6.2 用状态变量验证而不是用直觉验证参数改完之后很多人喜欢用打开页面变快了来做判断这个不够。真正要看的是MySQL的状态计数器和profile输出。举例你改了sort_buffer_size验证指标应该是Sort_merge_passes是否下降而不是排序结果出得快不快你改了max_connections验证指标应该是Connection_errors_max_connections是否仍然出现以及系统内存是否吃紧你改了innodb_buffer_pool_size验证指标是Innodb_buffer_pool_reads是否下降以及Innodb_buffer_pool_read_requests是否保持高位。我按这个思路整理了一个表格每次调参都对照着检查修改参数首要观察变量次要观察变量innodb_buffer_pool_sizeInnodb_buffer_pool_readsInnodb_buffer_pool_wait_freesort_buffer_sizeSort_merge_passes内存占用max_connectionsThreads_connectedConnection_errors_max_connectionsinnodb_io_capacity脏页比例Innodb_data_fsyncstmp_table_sizeCreated_tmp_disk_tablesCreated_tmp_tables6.3 每次只改一个参数不仅是规矩是智慧每次只改一个参数这个规矩我在处理线上问题时尤其坚持。原因很简单如果一次改了十个参数出问题时你根本不知道是哪一步引发的回滚也无从下手。我见过某优化方案把innodb_buffer_pool_size从4G改到16Gsort_buffer_size从256K改到4Mmax_connections从200改到800innodb_flush_log_at_trx_commit从1改到2一次性全改了。结果业务高峰期内存飙到90%部分请求超时但没人能说清是哪个参数引起的。最后只能全部回滚再一个个验证。所以我的流程是确认业务低谷期窗口做好配置备份修改一个参数并把它写进my.cnf重启或者在线调整参数跑一轮压测或观察10到30分钟生产流量记录状态变量和系统指标对比基线确认收益再动下一个参数。这套流程看起来慢实际上却最省时间因为每一步都有明确结论不会把时间浪费在反复回滚和试错上。6.4 一个我自己踩过的伪优化案例最后讲一个具体的坑。某次接到一个客户系统变慢的工单对方说已经做完参数优化buffer pool调到12G连接数调到600但系统依然慢。我一看服务器配置32G内存8核CPU业务只有大约20个并发用户数据总量不到3G。这个配置即使全部用默认参数都不可能慢到哪去。我先看SHOW PROCESSLIST发现几个会话都在执行同一条大查询每条耗时80秒以上而且这条查询每天固定时间启动。再看执行计划发现它join了五张表其中有大量字段没有索引。问题根源是每天跑的那个统计任务写的SQL性能极差跑起来能扫几个GB的数据并且还产生了大量磁盘临时表。参数优化在这个案例里几乎没帮上忙。真正解决问题的是给join条件上的字段加了两个索引同时给经常做group by的字段建了组合索引那条大查询从80秒降到了3秒。这个案子我一直记着是因为它把参数优化和性能优化的边界画得非常清楚参数优化是让系统在同等资源下跑得更高效但SQL质量差、索引缺失造成的慢查询光靠参数是救不回来的。你在做参数优化的同时一定要把慢查询日志开起来slow_query_log1 long_query_time2 slow_query_log_file/var/log/mysql/slow.log然后定期拿mysqldumpslow看看是什么SQL在拖后腿。参数优化和SQL优化配合起来才是完整的性能方案。最后分享一个实际经验我每到一个新环境第一件事不是调参数而是先查SHOW VARIABLES LIKE %version%确认MySQL的版本。因为不同版本的默认参数和功能差异很大5.7的合理配置在8.0里可能已经失效8.0的新特性比如innodb_dedicated_server自动配置它在专用服务器上能自动设定buffer pool和log file大小可能比手工调参更靠谱。如果你用的是MySQL 8.0并且你的MySQL是跑在专用机器上的可以先试试开启innodb_dedicated_serverON然后再根据需要微调。这条建议能让你少绕很多弯路。另外无论你怎么调改my.cnf之前记得先cp一份备份。追参数问题追到凌晨三点的时候你就知道这行命令有多重要了。
