本文探讨不同服务器资源下 MySQL 配置的优化思路。
MySQL：5.7
一、my.cnf 参数解释
(1) innodb_flush_log_at_trx_commit
1：默认取值。每次事务提交 立即把 redo log fsync 到磁盘。符合 ACID，零失误丢失。金融、银行必须设为 1。
0：事务提交不写 redo log「write()」也不 fsync，由后台线程每秒一次把 redo log buffer 刷盘。性能最高，最多丢失 1s 事务，只用于可容忍丢数据的测试/日志库。
2：事务提交时 只把 redo log buffer 写到 OS page cache 「write()」，每秒 fsync 一次。比 0 稍安全，仅 OS 崩溃会丢 1s 数据，MySQL 崩溃不丢。大多数 Web 应用可以选择此方案，选择此方案业务层面要确认：
少量支付：TPS < 1 k，丢 1s 最多丢几十条，财务对账可以容忍。
B2B：没有 C 端秒到诉求，客户可以接受 T+0/ T+1 到账即可。（T+0 第一个工作日，T+1 第二个工作日）
机器掉电风险：UPS 等手段。如果真丢，也能通过上游渠道补单。
二、优化指标
优化目的：充分利用内存，降低服务器的 CPU 负载、IO 延迟、IO 负载 以及 MySQL 指标。
1、健康的 IO 状态
以阿里云的 ESSD Entry 云盘为例，标称 IOPS 为 2121。
健康区间：
持续负载 <= 1500 IOPS ( ≈ 70% )
峰值 <= 1900 IOPS ( ≈ 90% )
延迟红线：
p99 < 10 ms 优秀
p99 < 20 ms 可接受
p99 > 50 ms 立即排查
2、MySQL 指标
（1）磁盘排序、全连接次数
-- 观察过去 1 小时内是否频繁出现磁盘排序
-- Sort_merge_passes < 10 / 小时
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
-- Select_full_join 持续 > 100 / 小时
SHOW GLOBAL STATUS LIKE 'Select_full_join';不满足指标，增加相应缓存大小：sort_buffer_size、join_buffer_size
（2）MySQL 服务端缓存线程数量：thread_cache_size
缓存线程，避免频繁创建/销毁线程带来的 CPU 开销。
-- 查看缓存命中率
SHOW GLOBAL STATUS LIKE 'Threads_created';
SHOW GLOBAL STATUS LIKE 'Connections';
-- 计算命中率
-- 若 Threads_created / Connections < 1% 说明缓存足够设置原则：
并发连接数 < 50：25-50
50-200：50-100
>= 200：100+
比如常态并发 50、突发并发 300，按常态并发 + 小余量计算，推荐设置 60 即可。
再多就是空占内存了，命中率已接近 100%；突发 300 让系统瞬时新建线程即可，常驻 300 线程内存成本代价高、空转，没有意义。
（3）读写 io 线程数：innodb_read_io_threads
经验公式：min(8, cpu_cores)
计算公式：单线程约 300 IOPS，假设 SSD 是 2121 IOPS，6 线程 ≈ 1800，已接近盘极限
三、12 核 24 G
场景：B2C 场景、支付业务频率较低、客户端数据库连接池活跃连接一般不超过 50（平均可能 10 ~ 20）
阿里云 ESSD Entry 云盘（2121 IOPS）
宝塔 IO 监控：1500 读写/s，IO 延迟 400ms，AI 判断属于“性能灾难”级别。
1、内存、IO 分配方案
（1）JVM
Top 查看到 JVM 稳定占用 30% 内存（约 7.2G），留 3G 余量。
IOPS 分配 30%
（2）MySQL 内存分配
MySQL 每连接 ≈ 2M x 2 + 1M x 2 + 1M + 2M = 9MB
公式解释：
1、2M x 2 + 1M x 2
对应 sort_buffer_size 等 4 个参数的值
在一次复杂查询中，经常会有 4 条不同的缓冲区被同时用到：
sort_buffer（order by / group by）
read_buffer（全表/全索引顺序读）
read_rnd_buffer（排序后的随机回表）
join_buffer（无索引 JOIN）
2、+1
1 MB ≈ 线程栈（thread_stack 256 KB）+ 网络缓冲区（net_buffer_length 16 KB + 扩容余量）+ 少量内部结构，经验上取 1MB 做顶包估算。
3、2M：余量
100 并发：100 x 9 MB = 0.9 G
10 GB Buffer Pool（根据热数据评估）+ 0.9 GB ≈ 11 GB
（3）MySQL IOPS 分配
IOPS 分配 70%，2120 x 0.7 ≈ 1480
innodb_io_capacity = 1400 # 日常刷脏上限
innodb_io_capacity_max = 2000 # 紧急时接近云盘极限（留 5 % 余量）（4）最终分配
OS + Page Cache：2 G（给 Linux 做文件缓存，防止 swap）
JVM：10G
MySQL InnoDB Buffer Pool：10 G（仍然是 MySQL 最大头）
MySQL 其余（连接、日志、字典等）：1.5 G（连接 0.9G，其他按经验值 0.5G）
预留 buffer：0.5 G（防止突发，不止 0.5 G，其他预估留了很多余量）
2、推荐配置
1、内存（核心）
innodb_buffer_pool_size：10G（ InnoDB 缓冲池 ）
innodb_buffer_pool_instances：8（ InnoDB 缓冲池分片：减少锁竞争、提高并发性能。10 GB/每实例 1.25 GB ）
key_buffer_size：64M（ 默认，用不到 MyISAM 可忽略 ）
2、连接
max_connections：200（最大连接数，留余量给运维）
thread_cache_size：50（线程缓存，避免频繁创建/销毁线程）
3、会话缓冲区
sort_buffer_size：2M（单线程独占，保守，防止“连接数 x 缓冲区”爆内存）
read_buffer_size：1M（同上）
read_rnd_buffer_size：1M（同上）
join_buffer_size：2M（用于无索引 join）
tmp_table_size：64M（与 max_heap_table_size 成对）
max_heap_table_size：64M（避免内存临时表落盘）
query_cache_size：0（关闭，原因看后面）
4、InnoDB IO
innodb_log_file_size：1G（redolog 大小。可以提高检查点间隔，减少写抖动）
innodb_log_buffer_size：32M（redolog 内存大小，不用太大没意义）
innodb_flush_log_at_trx_commit：2（性能和安全的折中「非金融级」）
innodb_flush_method：O_DIRECT（避免双缓冲）
innodb_io_capacity：1400（与 SSD 实际 IOPS 相匹配「留余量给 Tomcat 日志」）
innodb_io_capacity_max：2000（紧急时接近云盘极限「留 5% 余量」）
innodb_read_io_threads：6（匹配 12 核）
innodb_write_io_threads：6（同上）
5、日志和监控
slow_query_log：1（必开）
long_query_time：0.5（快速定位慢 SQL，看情况配置）
log_queries_not_using_indexes：1（「没用索引的 select」都会写入慢查询日志，生产环境慎用，高并发下日志会保障）
6、其他配置
transaction-isolation = READ-COMMITTED（RR -> RC）
7、其他建议
query_cache_size 在 MySQL 8 已废弃；5.7 建议关闭，原因：在高并发、写多读少的 OLTP 场景弊大于利 —— 锁争用严重，命中率低，妥妥是性能杀手。
sort/read_buffer 不能调太大，容易挤爆连接内存
innodb_log_buffer_size「16-64MB」是性价比最高的区间；再大只在大事务或批量写入时才有意义，否则白白占用内存。
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';持续 > 0，再考虑上调。
3、操作建议
1、分批生效：先调整 innodb_buffer_pool_size / max_connections / innodb_log_file_size 并重启；其余在线 SET GLOBAL 测试。
2、压测验证：sysbench / tpcc-mysql 跑 30 min，观察
SHOW ENGINE INNODB STATUS 中 Buffer pool hit rate > 99 %
SHOW STATUS LIKE 'Threads_created' 增长趋缓
SHOW STATUS LIKE 'Created_tmp_disk_tables' 不暴涨
3、监控闭环：接入 Prometheus mysqld_exporter 或 Performance Schema，持续观察 QPS、95% RT、Buffer Pool hit ratio 等关键指标。
4、修改 my.cnf 前先备份：cp /etc/my.cnf /etc/my.cnf.$(date +%F_%T).bak
4、一句话总结
把 InnoDB 缓冲池拉到 10 GB、连接/线程参数与 CPU 核数匹配、会话级缓冲区保守配置，就能在 24GB 内存的机器上把 CPU 和 SSD 都吃满，而不会出现 OOM 或上下文抖动。
5、优化效果
优化前：CPU