MySQL 优化这个话题聊的人多懂的人少。每次一说到性能问题总有同事甩给我一份网上抄来的“万能 my.cnf”几百行带注释的配置恨不得把每个参数都改一遍。结果呢数据库没快多少反而把本该自己吃饭的内存让给了各种无意义的缓存重启之后一脸懵。做了这么多年数据库相关的工作我越来越确信一件事MySQL 真正重要的变量一只手数得过来剩下的绝大多数保持默认值就是最好的选择。这篇不聊那些花哨的“优化一百条”就聚焦在真正影响性能、真正值得你花时间去理解和调整的几个核心变量上掰开揉碎讲清楚它们为什么重要、怎么判断该调多大、以及我实践中踩过哪些坑。如果你是刚接手 MySQL 运维的开发者或者正被线上数据库性能问题折磨这篇文章应该能帮你理清头绪。我不会给你一份抄作业的配置清单而是告诉你如何根据自己机器的实际情况把资源用在刀刃上。1. 先想明白为什么多数参数不用调接触过太多项目一上来就甩配置文档这其实搞错了顺序。在动任何参数之前要先搞清楚 MySQL 慢在哪里。1.1 调参是最后一步不是第一步理解性能问题本质上是理解一条查询从客户端发出到结果返回中间经历了什么。数据在磁盘上内存里有 Buffer Pool数据变更要写 Redo Log查询要解析、优化、执行引擎层和 Server 层各有各的资源消耗。调参的目的就是减少不必要的重复劳动让资源花在最值得的地方。但很多人的做法是反向的。业务慢了第一反应就是“改参数”。实际上慢查询最常见的原因往往不在参数而是 SQL 本身写得太烂、索引没建对、数据量涨了没做归档。参数只是压死骆驼的最后一根稻草前面几根全都是查询设计问题。所以我的经验是先开慢查询日志抓到慢 SQL用 EXPLAIN 分析执行计划确认是内存、IO、锁还是 SQL 层面的问题再决定要不要动参数。1.2 很多“经典优化项”已经名存实亡MySQL 发展到现在很多早期的参数早已内化或者被更好的机制取代。比如 Query Cache在 8.0 里已经彻底移除之前版本开启它还会因为全局锁争用导致性能雪崩。再比如各种 buffer 的默认值在 MySQL 5.7 之后已经对普通场景做了充分适配盲目调大反而可能弄巧成拙。所以真正值得去动手的一定是那些对性能影响倍数级放大、并且能通过现有监控指标反推合理值的参数。它们有这么几个特征数量少、影响大、且“不够”的症状非常明显。我调了这么多年值得长期跟踪的变量其实就是内存缓冲相关、日志刷盘相关、连接管理相关这三类。2. 内存与缓冲性能的基石数据库性能的第一大支柱就是内存。磁盘 IO 和内存延迟差着好几个数量级如何让热点数据尽可能留在内存里是所有优化的核心。2.1 innodb_buffer_pool_size第一优先级没有之一这个参数几乎决定了 InnoDB 的读性能上限。Buffer Pool 不仅缓存数据页还缓存索引页、插入缓冲、自适应哈希索引等。它的大小直接决定了查询命中的概率也直接决定了磁盘 IO 的压力。我见过不少机器内存有 128GBuffer Pool 只配了 1G数据库天天慢如蜗牛。也见过机器内存 16GBuffer Pool 配到 12G直接把操作系统和 MySQL 的内部管理结构挤爆触发了内存交换反而更慢。Buffer Pool 该多大有一个比较公认的经验给操作系统和 MySQL 其他结构留够余量后尽量大但不建议超过机器物理内存的 70% 左右。剩下的内存要给操作系统做文件缓存、给 MySQL 的各个会话 buffer、给排序和临时表留空间。怎么确认自己该调多大了不要猜看指标。MySQL 提供了命中率的数据来源。在 5.7 上可以用SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%看累计的物理读次数和逻辑读次数。命中率大致可以算成1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)。如果命中率低于 95%同时磁盘读的 IOPS 已经接近饱和那就是 Buffer Pool 不够的信号。此时再看看机器的空闲内存确认充足就可以加大。注意还有一点如果 Buffer Pool 很大比如超过几十 G建议开启innodb_buffer_pool_instances拆成多个实例减少高并发下的缓冲池锁竞争。这个参数我个人倾向于在调整 Buffer Pool 时一起配上一般 8 个实例是比较稳妥的起点。提示调整 Buffer Pool 前观察当前Innodb_buffer_pool_wait_free状态值。如果持续非零说明有线程在等待刷脏页这往往是 Buffer Pool 过小或刷盘能力不足引起的光加大缓冲池还不够还得看刷盘相关配置。另外Buffer Pool 不是永久保存的内容。数据库重启后需要重新预热这在冷启动阶段会有一段时间的“性能陡降”。5.7 之后有个innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup开启后可以把 Buffer Pool 中的页信息注意是页的引用不是数据本身存下来重启后按图加载能明显缩短预热期。这个参数反正我是一定会开的代价很小收益却很明显。2.2 innodb_log_file_size写多读少场景的隐藏瓶颈Buffer Pool 解决的是读的问题而写入场景的关键在 Redo Log。InnoDB 为了保证持久性在真正把脏页刷到磁盘之前会先把修改记录写入 Redo Log这个日志是以追加方式写的顺序写性能远高于随机写。Redo Log 的大小决定了你在崩溃恢复时需要回放多少日志更重要的是它决定了“写满之前脏页能积攒多少”这直接影响刷盘行为。如果innodb_log_file_size设置得太小日志很快写满后台线程就要频繁地将脏页强制刷盘来腾出日志空间。这种“日志切换导致的强迫刷盘”会让 IO 出现毛刺写性能像心电图一样忽高忽低。我遇到过一个案例某业务写请求量很大磁盘是 SSDIO 延迟却隔几十秒就飙升一下。排查发现日志文件只有 256M每次切换都引发一次刷盘风暴。在 8.0 里Redo Log 的配置方式变了直接指定日志文件大小和数量。根据官方文档默认总共约 32M这对生产环境偏小。我个人的经验写密集型业务可以先把 Redo Log 总大小配到 1G 到 4G 之间。为什么是这个范围太小了频繁切换太大了崩溃恢复时间变长要平衡。InnoDB 崩溃恢复时需要回放日志里尚未刷盘的操作日志文件越大恢复时间越久但对绝大多数业务来说总大小 4G 以内恢复时间都可接受。实在不放心可以逐步加大观察SHOW ENGINE INNODB STATUS里Log sequence number和Log flushed up to的差距如果差距经常很大说明日志写入压力高可以适度加大。注意调整日志大小需要干净地关闭实例最好在低峰期操作提前确认没有长事务。2.3 定位性能视图用数据说话动手改之前花几分钟看一下以下关键状态值可以让调整目标明确很多Innodb_buffer_pool_read_requests和Innodb_buffer_pool_reads判断逻辑读和物理读的比值。Innodb_buffer_pool_wait_free如果持续增长说明 Buffer Pool 刷脏跟不上。Innodb_os_log_written和Innodb_log_write_requests判断日志写入频率。Innodb_data_reads/Innodb_data_writes总体 IO 情况。这些指标可以通过SHOW GLOBAL STATUS直接查看也可以用系统表把数据拉出来画成趋势图看它增长是稳定的还是脉冲式的。没有监控图就不好判断所以做 MySQL 运维的人把这几个状态值存进时序库是基本功。3. 连接与会话级别把资源耗在不该耗的地方很多人只盯着 Buffer Pool 和日志结果把连接数、临时表、排序缓冲全忽略了。这些参数单个看起来不大但在并发场景下累积效应非常惊人。3.1 max_connections 与 thread_cache_size连接风暴的第一道防线max_connections这个参数几乎是新手最爱调的一发生Too many connections就立刻调大。这是典型治标不治本的思路。连接本身不重重的是每个连接背后分配的线程栈、排序缓冲、临时表空间等资源。调大连接数实际是给系统增加了大量内存占用和上下文切换开销。一个更稳妥的思路是先查当前峰值连接数然后对比max_connections是否真的紧张。如果连接常年不满说明问题不在连接数而在连接被长时间占用。比如连接池配置过小、慢查询占用连接过久或者有没关闭的事务。盲目调大只会让系统支撑更多“病恹恹”的连接整体吞吐反而下降。thread_cache_size也是一个值得关注的连接相关变量。它控制 MySQL 缓存多少空闲线程供新连接复用。这个值如果太小短连接频繁创建销毁线程的成本就会显现但设太大也意义不大因为缓存的是线程不是连接本身。通常设为 16 到 64 之间足够。对这个参数我的态度是别过度关注但不意味着可以完全忽视尤其是业务大量使用短连接比如 PHP 进程每请求新建连接的场景线程缓存能带来肉眼可见的改善。3.2 会话级 buffer 的误区sort_buffer 和 join_buffer 并不是越大越好这两个是典型的“每个连接单独分配”的缓冲区。sort_buffer 用于排序join_buffer 用于无法走索引的 join 操作。问题在于它们是按会话分配的不是全局共享的。你把 sort_buffer 从 2M 调到 64M但如果有 200 个并发连接同时排序那最多可能占用 200 × 64M 12.8G 内存瞬间就能挤爆机器。我见过有人犯这个错机器总共 32G 内存把几个会话级 buffer 都调大结果一遇到高峰查询内存直接被吃光OOM 进程被杀。这个教训非常深刻。对这类参数正确的做法是尽量保持小让它们成为“溢出缓冲区”。更进一步应该去优化 SQL避免真正触发大排序和大 join而不是把它们调大。对于确需保留的排序操作可以单独观察Sort_merge_passes状态值。这个值如果很高说明排序溢出到磁盘了才需要考虑适度增大 sort_buffer。切记是“适度”比如从 2M 调到 8M而不是一下子翻几十倍。3.3 临时表的隐形开销tmp_table_size 和 max_heap_table_size一个容易忽略的点是临时表。排序、去重、GROUP BY、子查询都可能在内存中创建临时表。如果临时表太大MySQL 会把内存临时表转成磁盘临时表这个过程相比内存操作慢得多。控制临时表大小的两个参数是tmp_table_size和max_heap_table_size实际生效值取两者中的较小者。怎么判断这个值够不够看到状态变量Created_tmp_disk_tables和Created_tmp_tables的比值。如果磁盘临时表占比超过 25%说明临时表的配置偏小或者 SQL 写得有问题导致产生了超大中间结果集。调整之前先想想这个临时表真的需要这么大吗对某些业务一个简单的 GROUP BY 在数据量上来后可能产生几 G 的中间表光把 tmp_table_size 从 16M 调到 512M 并不能真正解决问题反而是 SQL 需要拆解。但如果确实有大量小临时表频繁落盘适度提高这个值是有帮助的。注意tmp_table_size是每个会话的限制不是全局的上限。假设 100 个并发每个临时表 64M那最多也可能用到 6.4G 内存所以要结合并发来看别以为调大就万事大吉。4. 刷盘与一致性你看不到的延迟开关还有一个经常被忽视的维度就是数据到底什么时候落盘。MySQL 在保证数据安全和追求高性能之间给了你几个“开关”这几个开关的取舍直接决定了你在极端情况下的数据丢失风险和写入延迟。4.1 innodb_flush_log_at_trx_commit写性能与数据安全的天平这个参数有三个值0、1、2。默认是 1表示每次事务提交Redo Log 都要刷到磁盘上。这是最安全的选择但代价也是最大的每次提交都等待磁盘落盘在高并发写入场景下SSD 还能扛机械硬盘会非常吃力。如果你把值设为 2事务提交时只要日志写入操作系统的缓存即可由操作系统决定什么时候真正刷到磁盘。这种模式下如果只是 MySQL 崩溃操作系统没断电数据不会丢太多但如果整个机器断电那么最近一秒左右的未刷盘日志可能会丢失。设为 0 则是完全交给 MySQL 后台处理最快但危险程度也更高任何 MySQL 进程异常退出都可能导致最近一批数据丢失。我对这个参数的实践建议是默认的 1 在绝大多数情况下都不要动除非你的业务能明确接受“最多丢失最近 1 秒的已提交数据”。我调过一些低频写入的报表库用值 2 明显提升了批量导入速度但那种场景业务对丢失 1 秒数据的容忍度很高。如果你想保证安全又想提速首先想到的不该是改这个参数而是把磁盘从机械盘换成 SSD或者检查是不是有别的 IO 瓶颈。4.2 双写与同步的联动sync_binlog除了 InnoDB 自己的日志二进制日志binlog的刷盘策略也影响性能。sync_binlog参数控制 binlog 何时同步到磁盘。默认配置下binlog 的写入可能是由操作系统缓存控制的如果设为 1则每次事务提交都要同步刷盘。当innodb_flush_log_at_trx_commit1且sync_binlog1时数据安全级别最高但写入性能会受两重刷盘拖累。我个人的倾向是如果数据库不是主从架构binlog 的同步策略可以不必那么激进但在主从复制场景为了保证 binlog 不丢sync_binlog1是更稳妥的选择。若同时想兼顾写入性能可以考虑把 binlog 放到独立的磁盘或目录让日志写入和数据文件写入不互相争用同一块磁盘的 IO。这个物理层面的拆分比纠结参数值往往更有效。5. 实操一次标准调优的执行记录参数了解得再多不如实际动手操作一遍。这里分享一次我在模拟项目 X 中的标准调优流程供参考。这套流程适合大多数中低复杂度的业务系统核心原则是“一次只动一个变量并且每次改动都有监控数据支撑”。5.1 先收集基线数据调优之前先花 15 分钟收集系统的基线数据机器层面CPU 使用率、内存占用、磁盘 IOPS、磁盘读写延迟。MySQL 层面SHOW GLOBAL STATUS中的关键状态值重点看 Buffer Pool 命中率、临时表落盘情况、排序合并次数、慢查询数量。业务层面当前 QPS/TPS、平均查询耗时、长事务数量。记录这些数据后再开始调整。没有基线数据你根本不知道改动是变好还是变坏。5.2 核心参数的调整对照表下表是我在一个 32G 内存、8 核 CPU、SSD 磁盘的 MySQL 实例上针对一个读多写少、单表数据量在 1 亿左右的业务场景所做的调整以及调整的依据参数默认值示例调整后值调整依据innodb_buffer_pool_size128M20G内存 32G留出系统与线程空间命中率测试低于 80%innodb_buffer_pool_instances18大缓冲池并发竞争拆分解锁innodb_log_file_size48M累计1G写日志频繁切换IO 毛刺tmp_table_size16M64MCreated_tmp_disk_tables 占比高max_heap_table_size16M64M与 tmp_table_size 联动生效thread_cache_size自动32短连接场景线程重复创建成本高sync_binlog0或 11主从场景保证 binlog 不丢注意这张表只是示例。不同版本的 MySQL参数默认值可能不同。8.0 和 5.7 在 Redo Log 的处理方式上就有很大差异。引用第三方参数时一定要结合自己实例的SHOW VARIABLES查看现状。5.3 修改配置后的验证方法改完配置重启 MySQL 或者用SET GLOBAL动态设置后不要立刻下结论。给系统至少二十分钟到半个小时的运行时间让缓存预热、统计信息稳定再对比之前的基线数据。此时重点看慢查询数量有没有下降。Buffer Pool 命中率有没有提升。Innodb_log_file_size调整后Log sequence number和Log flushed up to的差距是否缩小。磁盘 IOPS 有没有从频繁尖峰变成相对平缓。有条件的最好把监控图拉出来做前后对比。如果改动后核心指标没有明显变化那大概率说明你改的方向有问题继续调下去也意义不大应该退回原值重新分析定位瓶颈。6. 常见问题与排查技巧实录平时群里被问得最多的就是各种“调完之后怎么更慢了”的问题。这里把典型的几个坑集中整理一下。6.1 改完参数没生效问题出在哪很多人改的是my.cnf但不同配置项所属的段落不一样比如innodb_buffer_pool_size必须在[mysqld]段下。如果你把它写在[client]或[mysql]段MySQL 启动时根本不读。另外部分参数虽然动态可调但重启后如果配置文件里没写对也会恢复原值。所以改完配置后建议用SHOW VARIABLES LIKE xxx确认一下当前实际生效的值别想当然。还有一个经典问题SET GLOBAL并不影响已经存在的连接只影响之后新建的连接。如果你用SET GLOBAL调了某个参数再用同一个已存在的会话去查看看到的值可能没变甚至部分会话级参数只对当前会话生效。这种时候要区分“全局值”和“会话值”。6.2 缓存命中率 100% 但还是很慢Buffer Pool 命中率高不等于整体性能好。有一次排查命中率 99% 以上查询还是慢。后来发现是慢 SQL 在索引选择上出了问题大量回表导致 CPU 居高不下。Buffer Pool 只解决“从哪读”的问题不解决“读什么查得慢”的问题。遇到这种情况该看的是执行计划是不是有一个隐式类型转换导致索引失效是不是有一个SELECT *导致大量无用数据传输。参数优化在 SQL 优化面前永远是退而求其次的选择。6.3 参数变大性能反而变差这个我在前面也提过最典型的就是会话级 buffer 调大后内存吃紧。另外一个不容易察觉的坑是innodb_buffer_pool_instances配得不合理。实例数超过 CPU 核心数时锁竞争没有继续降低反而因为每个实例需要额外的内存管理结构造成轻微损耗。所以这个值不是越大越好通常不超过 8 或者不超过 CPU 核心数是比较稳妥的。这里整理一个速查表方便排查时对照现象优先排查方向相关参数或工具大量磁盘读命中率低Buffer Pool 偏小innodb_buffer_pool_size写入毛刺日志频繁切换Redo Log 太小innodb_log_file_sizeToo many connections连接数或慢查询堆积max_connections、slow log临时表落盘频繁中间结果集过大或 tmp 太小tmp_table_size、SQL 改写排序性能差Sort_merge_passes 高sort_buffer_size、索引优化数据安全顾虑高刷盘策略过弱innodb_flush_log_at_trx_commit、sync_binlog重启后性能骤降Buffer Pool 未预热innodb_buffer_pool_dump_at_shutdown6.4 一次调优只能解决一个主要矛盾最后说一个经验之谈你永远不要在同一个时段同时调五六个参数。每个参数的调整都会带来连锁反应同时改多个变量出了问题你根本分不清是哪个引起的。我更习惯的做法是一次只改一个最可疑的变量观察一段时间记录效果再决定下一步。如果连续改了两三个变量核心指标都没有任何改善那大概率是你对瓶颈的判断从一开始就错了这时候最有效的动作其实是回滚所有改动重新回到慢日志和监控面板上。在实际工作中我是吃过几次亏才慢慢建立起这套流程的。每次项目一紧张总想着调大这个调大那个来兜底结果往往是把问题从查询层引到了内存层。MySQL 的配置不是免死金牌它更像是一套精密的资源分配系统你只有理解了每个变量背后的资源申请逻辑才知道哪些地方该抠、哪些地方该松。如果你打算在自己的机器上做实验我建议从innodb_buffer_pool_size和innodb_log_file_size这两个入手先把内存和日志这两个地基打牢再往上层看连接和临时表。对照着慢日志和状态值先观察再动手你的数据库离稳定运行就不远了。
