聊 MySQL 性能优化，我见过最让人头疼的不是机器配置低，也不是 SQL 写得烂，而是一堆人拿着网上抄来的变量清单，照着一个个调，调完心里还没底。干维护和调优这些年，踩过的坑不少，最大的一个体会就是：MySQL 的优化变量确实很多，但真正重要的就那么几个。把这几个核心变量搞明白，比背二十个冷门参数有用得多。这篇就聊聊我眼中那些真正重要的变量，以及它们背后让我印象深刻的现场。
这篇文章适合谁？刚接手 MySQL 维护，被慢查询、CPU 打满、磁盘 IO 飙高折磨过的同学；准备系统性调优但不知道从哪下手的开发；以及那些已经被“调参模板”坑过一次的人。我不打算给你一堆复制粘贴的调优清单，只讲我实际验证过、真正改变过数据库命运的变量，以及每一步背后的“为什么”。
1. 先从三个层面对齐：MySQL 优化到底在调什么
1.1 真正的主线：存量、日志、连接
MySQL 调优最怕的是没有主线。很多人打开配置文件就开始改，看到什么调什么，改完也不知道哪个变量起了作用。我自己这些年摸出来的路子，是把优化对象分成三个层面：存量、日志、连接。
存量指数据在内存里待得住待不住，这是 InnoDB 的 buffer pool 管的；日志指事务提交时 redo log、binlog 怎么落盘，这决定了写入吞吐的上限；连接指客户端进来之后线程和会话级内存开销怎么控制，这决定了系统在高并发下会不会被自己拖垮。三个层面分别对应三个核心问题：数据放内存了吗？写数据卡磁盘了吗？连接打满了吗？
把这三条主线抓住，再看具体变量就非常清晰。比如内存层出问题，你调的是 buffer pool；日志层出问题，你看的是刷盘策略；连接层出问题，你才去动 max_connections。顺序反了，优化就是瞎忙。我见过有人内存命中率明明很低，却在拼命调 sort_buffer_size，调完毫无效果，这就是没有主线导致的典型浪费。
1.2 为什么通用调优模板大概率没用
网上流传的“万能优化配置”我也曾试过，结论是：能用，但绝对谈不上“最优”，而且经常带来副作用。原因很简单，每个业务的读写比例、数据热点、并发模型、硬件环境都不一样。同样一台 64G 内存的机器，跑电商订单库和跑日志分析库，最优配置天差地别。
另一个问题是，很多人把调优当成“改完重启就好”的事，忽略了先诊断再动手。我在实际工作中，调任何一个变量之前都会先回答三个问题：当前瓶颈是什么？这个变量是不是作用于瓶颈？改完之后怎么验证有效？如果答不上来，说明还没到改的时候。这个习惯让我少走了很多弯路，也让每次调整都有据可查、可回退。
2. 内存层：innodb_buffer_pool_size 与它的兄弟们
2.1 这个变量到底在管什么
InnoDB 的数据和索引以页为单位存在磁盘上，innodb_buffer_pool_size 就是给这些页准备的内存缓存区。查询一条记录，如果对应数据页已经在缓冲池里，直接内存读；如果不在，就得从磁盘读进来。这个池子的大小，直接决定了磁盘 IO 的频率。
我习惯把它比作饭店的后厨备菜台。备菜台越大，提前准备好的菜品越多，顾客点菜时直接端上去；备菜台小，就得频繁去仓库取货，出菜速度自然慢。MySQL 的 data page 就是那些菜品，仓库就是磁盘。所以很多“配置没问题但查询就是慢”的场景，第一反应就应该是：buffer pool 够不够大？
实际经验上，如果是独立 MySQL 实例，物理内存的 50% 到 70% 都可以留给 buffer pool。但注意，别贪。系统本身、操作系统 page cache、连接线程、排序缓冲都要吃内存。我见过有人把 64G 内存的机器给 buffer pool 设了 56G，结果操作系统开始 swap，整体性能反而崩了。
2.2 怎么判断当前池子够不够大
“感觉不够大”不算数，要看数据。
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_%';关注两个计数：Innodb_buffer_pool_read_requests 表示从缓冲池读的次数，Innodb_buffer_pool_reads 表示从磁盘读的次数。命中率的算法很简单：
命中率 = read_requests / (read_requests + reads)我的经验基准：命中率 99% 以上算健康；95% 到 99% 之间说明有优化空间；一旦掉到 90% 以下，基本可以断定缓冲池偏小，或者数据访问热点太分散。这个判断比看 CPU 占用率直接多了。
另一个更细的检查方法是执行 SHOW ENGINE INNODB STATUS，看 BUFFER POOL AND MEMORY 段落的命中率和空闲页数量。如果大量空闲页或者大量被迫淘汰的页，都有参考价值。
2.3 大内存场景下记得拆实例
Buffer pool 设到 64G 以上之后，你会遇到一个新问题：并发访问时，单个 buffer pool 内部的锁竞争越来越明显。解决方式是用 innodb_buffer_pool_instances 把缓存池拆成多个独立实例。
这个变量不是越大越好。合理范围是每个实例 1G 到 8G 之间。比如 64G 的 buffer pool，我通常设 8 个实例或 16 个实例。拆完之后，不同线程操作不同实例时互不干扰，并发读写性能有可见改善。
需要注意，这个参数改了必须重启，而且一旦设置，运行期不建议随意调整。旧的 MySQL 版本对实例数还有上限要求（比如不能超过 buffer pool 的 chunk 数量），升级版本时要重新确认配置。
2.4 重启之后的预热问题
内存池再大，实例重启后也会清空。冷启动阶段，所有查询都要从磁盘读，业务方会明显感觉“怎么重启完反而更慢了”。这个问题可以用两个变量解决：innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup。
开启之后，关库时把当前缓冲池里的热页位置记录到文件，启动时再按记录加载回来。我实际测试的效果是，几十 G 的缓冲池，预热时间可能几分钟，但能避免重启后高峰时段磁盘 IO 被打满的尴尬。
要注意的是，dump 和 load 过程本身也占 IO。如果维护窗口很短，可以把 load 延后到业务低峰。我一般建议开启 dump，load 的时机则根据维护计划灵活决定。
3. 日志层：redo 容量和刷盘策略如何决定写入上限
3.1 innodb_log_file_size 和 8.0 的 redo 容量变量
先问一句：你有没有遇到过这种情况？机器配置很高，磁盘是 SSD，但写入量一大，观察到 MySQL 的 checkpoint 非常频繁，磁盘 IO 永远在刷脏页？如果是，大概率是 redo log 太小了。
redo log 是 InnoDB 用来保证崩溃恢复的日志。事务提交时先写 redo，数据页的修改可以留在内存里慢慢刷。redo 空间不足时，系统必须把最早的脏页刷到磁盘才能复用日志空间，这就是 checkpoint 频率上升的原因。空间越大，事务提交和数据落盘之间的缓冲空间越大，写入吞吐就越平滑。
MySQL 8.0.30 之前，这个变量叫 innodb_log_file_size，默认值只有 48M，说实话非常保守。8.0.30 之后改成了 innodb_redo_log_capacity，默认 100M，官方建议用容量管理代替手动配置。
那到底设多少合适？我的经验公式是：估算业务写入峰值（每秒写入多少 M），乘上写峰值的持续时间（比如秒杀 10 分钟），再留 2 到 3 倍余量。对于普通中小业务，8G 到 16G 是很常见的取值。别怕大，redo log 大一点，崩溃恢复时间会变长，但恢复时间几十秒和运行期频繁 checkpoint 导致的持续性能损失相比，我肯定选前者。
3.2 innodb_flush_log_at_trx_commit：安全与性能的博弈
这个变量直接决定每次事务提交时，redo log 怎么落盘，有三个取值：
| 取值 | 行为 | 安全性 | 性能 |
|---|---|---|---|
| 1 | 每次提交都 fsync 日志到磁盘 | 最高，最多丢 0 个事务 | 最低，延迟受磁盘 fsync 速度影响 |
| 2 | 提交时写到操作系统缓存，后台上限每秒刷盘 | 高，数据库崩溃可能丢 1 秒数据 | 较高 |
| 0 | 提交时不写也不刷，完全交给后台每秒刷一次 | 低，崩溃可能丢 1 秒数据 | 最高 |
默认值是 1，这也是 ACID 里 D（持久性）的保证。但很多业务根本不需要这么强的持久性，比如日志表、统计表、临时结果表，丢了 1 秒的数据也无所谓。对这些场景，设置成 0 或 2，写入性能提升不是一点点。
我在生产环境常用的折中是设置 2。数据库进程崩溃，操作系统没崩，日志还在缓存里；只有机器宕机或操作系统崩溃才会丢数据。对于绝大多数业务，这种安全性足够，但性能比 1 好很多。金融支付类业务我强烈建议保持 1，别在这个变量上省性能。
3.3 innodb_flush_method：数据落盘的方式不能乱来
Linux 平台下，innodb_flush_method 建议设置为 O_DIRECT。原因是 InnoDB 自己已经管理 buffer pool，不需要操作系统再维持一份 page cache。如果不设置 O_DIRECT，数据先写到系统缓存，再拷贝到磁盘，内存会被双份数据占用，白白浪费。
设置成 O_DIRECT 之后，数据文件的读写绕过操作系统缓存，直接落到磁盘。配合足够大的 buffer pool，这套组合才是 InnoDB 在 Linux 上的最佳姿势。
但有个前提：你的文件系统要支持 O_DIRECT。ext4、XFS 这些年都很成熟，基本没问题。某些网络存储或特殊文件系统可能不支持，改了反而性能下降。所以每次改完，我都会验证一下：
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_method';然后实际压测确认生效。有时候线上文件系统恰好不支持，设置不报错但行为退化，这种情况我踩过一次，后面会再细说。
4. 连接与并发：max_connections 不是越大越好
4.1 连接数越大，系统越容易被拖垮
max_connections 是很多人第一反应要调大的变量。默认 151，业务一上来就报 “Too many connections”，于是赶紧调成 500、1000。但你真的需要那么多连接吗？
每个连接都需要线程栈、排序缓冲、临时表结构等资源。连接数越多，MySQL 内部的调度开销、上下文切换成本就越高。最典型的现象：连接数从 100 提到 300，TPS 不升反降。这个拐点，不是靠拍脑袋，而是靠压测和监控找到的。
我在排查性能问题时，优先看两个状态值：Threads_connected 和 Threads_running。Threads_connected 是当前建立的连接数，Threads_running 是真正在执行语句的线程数。如果 running 长期接近甚至等于 connected，说明连接基本都在干活，系统处于过载状态；如果 connected 很高但 running 很低，说明大量连接是空闲的，这时候问题不在 max_connections，而在连接池配置和 wait_timeout。
真正的解法是：连接数设到一个合理值（比如 200 到 400 之间，看机器核数和内存），同时让应用侧连接池控制并发，宁可排队等连接，也好过全连上 MySQL 然后大家一起慢。
4.2 thread_cache_size：连接创建开销的缓冲
连接断开后，MySQL 会销毁线程。如果业务侧频繁创建短连接，线程创建和销毁的开销会占用大量 CPU。thread_cache_size 的作用是缓存空闲线程，下次连接进来直接复用。
这个变量的调优依据是状态计数：
SHOW GLOBAL STATUS LIKE 'Threads_created'; SHOW GLOBAL STATUS LIKE 'Connections';如果 Threads_created 的数量接近 Connections 的总量，说明线程一直处于新建状态，缓存没有起作用；如果 Threads_created 远小于 Connections，说明线程复用率很高。
我一般把 thread_cache_size 设置在 50 到 100 之间，对于大多数中规模业务已经足够。这个值设太大没有意义，反而浪费内存。
提示：连接层的优化一定要和业务连接池配合，仅调 MySQL 侧变量，效果非常有限。
4.3 会话级变量三兄弟：排序、连接、随机读的缓冲
sort_buffer_size、join_buffer_size、read_buffer_size 这三个变量，是我见过被滥用最严重的。原因是它们听起来都能“加速”，但它们是 per-session 的，每个连接都会分配一份。
举个例子，sort_buffer_size 默认 256K，看起来不大。如果你全局改成 64M，那么 100 个连接同时执行排序时，理论占用就是 6.4G。稍微多几个连接，机器内存就炸了。我见过一台 32G 的机器被三个连接吃满内存，就是这个原因。
正确姿势是：保持默认或按需小幅调整。先用 EXPLAIN 看清楚 SQL 是不是真的产生了 Using filesort 或 Using temporary，再针对性调。甚至可以用会话级语法只对某一条 SQL 生效，而不是全局调整。
SET SESSION sort_buffer_size = 8M;这种精准调整，比全局乱调安全得多，也更容易验证效果。
4.4 临时表：tmp_table_size 与 max_heap_table_size
临时表是另一个容易被忽略的内存消耗点。查询中的 group by、order by、distinct 会创建内部临时表。临时表小于阈值时存在内存里，超过阈值就落盘，落盘意味着磁盘 IO，慢查询就从这里来。
相关变量是 tmp_table_size 和 max_heap_table_size。注意这两个变量是“取小值生效”的关系，只调 tmp_table_size 而 max_heap_table_size 不跟着调大，实际阈值还是被小值限制住。所以两个要一起设置。
我常用的起步值是 64M。如果业务中确实有大量复杂分组查询，临时表落盘次数很多，再往上加，同时关注内存总量。8.0 版本临时表引擎默认是 TempTable，还有 temptable_max_ram 变量控制最大内存占用，超了的话会转为磁盘临时表，这个变量在 8.0 里也要一并确认。
5. 容易被忽视的变量：表缓存、binlog 与隔离级别
5.1 table_open_cache：打开表太多也会出问题
MySQL 执行 SQL 前需要打开涉及的表。如果 table_open_cache 设置得太小，表打开后会很快被缓存淘汰，下次再用又要重新打开，表现为 Opened_tables 状态值持续增长。
诊断方法同样简单：
SHOW GLOBAL STATUS LIKE 'Opened_tables';这个值如果不停增长且幅度大，说明表缓存不够。注意，如果你有几千张分区表或大量临时表，这个变量对性能的影响会比想象中明显。
调大 table_open_cache 会占用内存，而且不是无限调。我一般的做法是：先看当前打开的表数量（performance_schema 里有精确计数），在此基础上加 20%-50%。改完观察 Opened_tables 是否停止增长。
5.2 sync_binlog：主从环境里的隐形性能瓶颈
开启 binlog 后，主库多了一条日志写入路径。sync_binlog=1 表示每次事务提交都刷 binlog 到磁盘，牺牲性能保证安全；很多业务可以接受设成 0 或让操作系统自己决定刷盘时机，换来的写入性能提升非常可观。
同时，binlog 组提交机制也值得关注。binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count，可以控制多少个事务攒在一起统一刷盘，减少 fsync 次数。这两个参数对高并发写入场景效果明显。
但注意：修改 sync_binlog 会降低主库崩溃时 binlog 的持久性，可能造成主从数据不一致。如果从库有严格一致性要求，需要综合考量。我处理的很多实际项目里，主库都会在同步安全性和性能之间找平衡，很少把两边都设成最极端。
5.3 transaction_isolation 与 binlog_format：隐藏的“组合变量”
很多调优文章不会把隔离级别当作变量，但它确实影响性能。默认的 REPEATABLE READ 下，InnoDB 需要维护更多的间隙锁，锁等待更容易发生。如果业务语义允许，用 READ COMMITTED 能明显减少锁竞争，提升并发。
另外 binlog_format 建议用 ROW。行格式的 binlog 在数据修复、主从同步一致性上比 statement 格式可靠得多，虽然日志量更大，但性能稳定性更好。这个组合（READ COMMITTED + ROW）是很多生产环境的标准搭配，也侧面说明最有效的优化不只在“变量”本身，还有配置之间的协同。
6. 一次完整调优记录：从采样到验证
6.1 先诊断再动手
去年我帮某电商报表库做过一次调优，典型的场景：64G 内存的机器，CPU 经常飙到 80% 以上，业务方反馈查询越来越慢，加了内存也没用。到现场先采样了十几分钟状态值，重点看 buffer pool 命中率、redo 相关状态、磁盘 IO 等待、Threads_running。
结果很有意思：buffer pool 只有 8G，命中率不到 90%；redo log 还是默认的 100M，checkpoint 频率非常高；max_connections 被调到了 500，但业务连接池同时开太多，Threads_running 长时间接近 200。三者叠加，CPU 和时间都耗在磁盘 IO 和线程调度上。
6.2 调整过程
第一步，把 innodb_buffer_pool_size 从 8G 调到 32G。因为机器 64G 内存，实例独占，这个比例安全。改完重启，观察命中率，稳步上升到 99% 以上。
第二步，把 innodb_redo_log_capacity 调到 8G。这样 checkpoint 压力大幅下降，磁盘 IO util 从持续 90% 降到 30% 左右。这里要说一句，8.0 版本下 redo log 的调整比以前简单，不用手动删日志文件，风险小很多。
第三步，把 max_connections 从 500 降到 200，同时让应用侧把连接池最大值从 300 调到 150。配合 thread_cache_size=64，Threads_created 的增长放缓，CPU 开销明显下降。
第四步，tmp_table_size 和 max_heap_table_size 一起从 16M 调到 64M。业务里有一堆定时统计的 group by 查询，临时表落盘次数明显减少。
6.3 验证效果
调优后的对比数据：
| 指标 | 调优前 | 调优后 |
|---|---|---|
| buffer pool 命中率 | 88% | 99.2% |
| 磁盘 IO util（峰值） | 92% | 32% |
| Threads_running 峰值 | 198 | 36 |
| 慢查询数量（每小时） | 1400+ | 40 |
| TPS（峰值） | 800 | 1900 |
这里的提升不是某一项配置的功劳，而是三个层面同时修正之后的结果。这也是我一直强调的：先诊断，抓住主要矛盾，再动手，一次只改一两个变量，验证完再继续。
7. 常见问题与排查实录
7.1 问题排查速查表
| 现象 | 可能原因 | 检查方法 | 对应变量 |
|---|---|---|---|
| 查询慢但 CPU 不高 | buffer pool 命中率低 | SHOW GLOBAL STATUS 命中率 < 95% | innodb_buffer_pool_size |
| 大量磁盘写，checkpoint 频繁 | redo log 太小 | 观察磁盘 IO 和 SHOW ENGINE INNODB STATUS | innodb_redo_log_capacity |
| Too many connections | 连接数超限 | 看 Threads_connected vs max_connections | max_connections、连接池配置 |
| 线程频繁创建销毁 | thread_cache 太小 | Threads_created 接近 Connections | thread_cache_size |
| 排序慢且临时表落盘 | 临时表内存太小 | 观察 Created_tmp_disk_tables | tmp_table_size、max_heap_table_size |
| 大量打开表操作 | table cache 不够 | Opened_tables 持续增长 | table_open_cache |
| 写入延迟高但机器空闲 | 刷盘策略保守 | 业务对丢失容忍度评估 | innodb_flush_log_at_trx_commit |
7.2 我踩过的几个坑
第一个坑就是无脑调大 buffer pool。某次给一台 48G 的机器设了 42G，结果操作系统被迫 swap，响应时间反而暴涨。后来才明白，系统本身还有 MySQL 连接、线程栈、page cache 等一堆开销，必须留足余量。现在我的原则是：超过物理内存 70% 就要谨慎。
第二个坑是 sort_buffer_size 全局调大。早年间为了让排序快一点，把 sort_buffer_size 调到了 32M，结果并发一高，直接内存溢出。从那以后，会话级变量我只做会话级调整，绝不全局乱来。
第三个坑是 8.0 升级之后，沿用旧的 innodb_log_file_size 配置不生效。MySQL 8.0.30 之后的 redo log 由 innodb_redo_log_capacity 管理，配置方式变了。升级前一定要看官方文档确认变量变化，别拿旧配置文件硬套。
第四个坑是改了 innodb_flush_method 之后性能没提升反而下降。最后查出来是文件系统对 O_DIRECT 支持有问题，改成 fsync 才恢复。所以每次改完这个变量，我都会立刻做一轮读写压测验证。
第五个坑是只调 tmp_table_size 没调 max_heap_table_size。两个变量取小值生效，调了等于没调。后来我写了个习惯：涉及成对变量，永远一起检查。
注意：调优是一个持续的过程，不是一次改完就结束。每次业务模型变化、数据量增长、硬件更换后，都应该重新评估这几个核心变量是否还合适。
我个人在实际操作中的体会是：MySQL 优化不是把变量背齐，而是抓住内存、日志、连接这三条主线，用数据验证每一个决定。宁可少动，也不要乱动；每改一个变量都要能说清它作用于哪条链路，然后用监控取证。最后再分享一个小技巧：调完一个变量后，压测对比一个周期再动下一个，一次只改一个，出了问题也能快速回退定位。这个习惯帮我省了太多排查时间，也希望你能用得上。
