事情经过

2024年3月，公司一个日活8万的教育类SaaS项目上线了大促活动。凌晨1点，监控告警：MySQL CPU使用率98%，活跃连接数飙到487，紧接着前端接口大面积502。

当时数据库是一台4核8G的云主机（腾讯云 S5.MEDIUM4），MySQL 8.0.35，配置基本是默认的。查了慢日志，一条很简单的SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20，明明有索引，执行却要1.8秒。

问题很明显：MySQL 8.0默认配置是为小型开发环境设计的，生产环境直接裸奔必出事。新版本把部分参数默认值调得「保守」，比如innodb_buffer_pool_size默认128M，max_connections默认151，innodb_io_capacity默认200——这些在8G内存的机器上等于自废武功。

这篇文梳理我踩过的坑，以及最终验证有效的调优方案。数据全部来自sysbench 1.0.20压测，测试环境：CentOS 7.9 / 4核8G / SSD云硬盘（IOPS上限约2400）/ MySQL 8.0.35。

先看结论：一套参数立竿见影

压测场景：sysbench oltp_read_write 混合读写，8线程，单表100万行，压测10分钟。

配置方案	QPS	TPS	平均延迟(ms)	95%延迟(ms)	CPU占用
默认配置	2,381	119	33.6	58.4	72%
基础调优	4,927	246	16.2	28.9	64%
完整调优	7,483	374	10.7	19.6	61%

完整调优方案下，QPS提升214%，95%延迟从58.4ms降到19.6ms。压测期间峰值并发600没崩，CPU稳定在61%。这个结果在4核8G的机器上已经接近上限。

方案选择：两种思路，我推荐组合打

搞定MySQL性能问题，市面上常见的就两条路：

方案A：纯参数调优（零成本，见效快）

调整my.cnf配置，不动代码、不动架构。优点是改完重启就生效，适合线上救急；缺点是有天花板，4核8G的机器怎么调也打不过16核32G。

方案B：架构改造（连接池/读写分离/缓存）

引入ProxySQL或MyCat做读写分离，加Redis缓存热点数据，业务代码配合改造。优点是扩展性强，扛得住千万级流量；缺点是周期长、改动大、成本高，大促前一周根本来不及。

我的实践结论：先调参数（方案A），把硬件潜力榨干；参数到位后仍然扛不住再上架构改造（方案B）。这次救火就是纯参数调优解决的，总耗时2小时，包括压测验证。

完整配置：直接复制就能用

1. my.cnf 完整配置（MySQL 8.0.35 + 4核8G）

# /etc/my.cnf
# MySQL 8.0.35 实测调优配置
# 适用：4核8G内存 / SSD硬盘 / CentOS 7.9
# 注意：内存 >16G 的机器需要等比放大 innodb_buffer_pool_size

[client]
port = 3306
socket = /tmp/mysql.sock

[mysql]
auto-rehash
prompt = "\\u@\\h [\\d]> "

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/mysql.pid

# 字符集 — 8.0默认utf8mb4，别改
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

# 连接数
max_connections = 600
max_connect_errors = 1000
# 5.7+ 必须显式设置，不然连接被拒
skip-name-resolve = 1

# InnoDB 核心参数
innodb_buffer_pool_size = 4G        # 物理内存的50%-60%
innodb_buffer_pool_instances = 4    # 每个实例不小于1G
innodb_log_file_size = 1G           # 默认48M，太小会导致频繁checkpoint
innodb_log_files_in_group = 3       # redo log 总大小 = 1G * 3 = 3G
innodb_flush_log_at_trx_commit = 2  # 0=每秒刷 1=每次提交刷(最安全) 2=每秒刷且不丢OS缓存
innodb_flush_method = O_DIRECT      # 绕过OS缓存，减少double buffer
innodb_io_capacity = 1500           # SSD设1000-2000
innodb_io_capacity_max = 3000
innodb_max_dirty_pages_pct = 75     # 脏页比例，默认90偏大
innodb_adaptive_hash_index = ON     # 8.0默认ON，等值查询多就留着
innodb_change_buffering = all
innodb_autoinc_lock_mode = 2        # 8.0默认2，性能最优
innodb_undo_tablespaces = 2         # 默认2，一般别动
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G # 防临时表撑爆磁盘

# redo log 容量（8.0.30+ 新参数，替代 innodb_log_file_size）
innodb_redo_log_capacity = 3G

# 连接与线程
thread_cache_size = 64
back_log = 300
# 8.0.16+ 可用
performance_schema = OFF

# 临时表
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序与连接缓冲 — 防排序落盘
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 16M

# 查询缓存 — 8.0已废弃，别开
# query_cache_type = 0

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 1
log_queries_not_using_indexes = 0

# binlog — 按需开启，本例开启
server-id = 1
log-bin = /data/mysql/log/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800  # 7天
max_binlog_size = 512M
# 关键优化：减少binlog刷盘频率，大促场景可接受丢失1秒数据
sync_binlog = 0

# 错误日志
log_error = /data/mysql/log/error.log

# 时区
default-time-zone = '+08:00'

[mysqldump]
single-transaction
quick
max_allowed_packet = 64M

2. 在线动态调整（不重启，救急用）

-- 先看当前值
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_log_file_size';

-- 在线调整（重启失效，需同步改配置文件）
SET GLOBAL max_connections = 600;
SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024;

-- 8.0.30+ 支持在线调整 redo log 容量
SET GLOBAL innodb_redo_log_capacity = 3 * 1024 * 1024 * 1024;

-- 确认生效
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';

3. 压测脚本（sysbench 1.0.20）

#!/bin/bash
# /root/bench.sh — MySQL 调优前后压测脚本

# 1. 准备数据：单表100万行
sysbench /usr/local/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password='your_pass' \
  --mysql-db=sbtest \
  --tables=1 \
  --table-size=1000000 \
  prepare

# 2. 压测 10分钟，8线程
sysbench /usr/local/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password='your_pass' \
  --mysql-db=sbtest \
  --tables=1 \
  --table-size=1000000 \
  --threads=8 \
  --time=600 \
  --report-interval=10 \
  --rand-type=uniform \
  run

# 3. 清理
sysbench /usr/local/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password='your_pass' \
  --mysql-db=sbtest \
  --tables=1 \
  cleanup

4. 调优后的监控SQL

-- 实时确认buffer pool命中率（>99% 为理想）
SELECT 
  (1 - (SELECT variable_value FROM performance_schema.global_status 
        WHERE variable_name = 'Innodb_buffer_pool_reads') / 
   (SELECT variable_value FROM performance_schema.global_status 
    WHERE variable_name = 'Innodb_buffer_pool_read_requests')) AS buffer_pool_hit_ratio;

-- 查看当前活跃连接数（对比 max_connections）
SELECT COUNT(*) AS active_conn FROM information_schema.processlist 
WHERE command != 'Sleep';

-- 查看redo log刷盘频率（判断 log 容量是否够）
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

-- 查看脏页比例
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total';

5. PHP 8.2 + PDO 连接池配置（配合调优使用）

 'mysql:host=127.0.0.1;port=3306;dbname=app_db;charset=utf8mb4',
    'username' => 'app_user',
    'password' => 'secret',
    'options' => [
        PDO::ATTR_PERSISTENT => true,           // 长连接
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_TIMEOUT => 3,                 // 连接超时3秒
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION wait_timeout=3600",
        // 关键：设置连接池复用
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
    ],
];

// 每次请求获取连接
function getDb(): PDO {
    static $pdo = null;
    if ($pdo === null) {
        global $config;
        $pdo = new PDO(
            $config['dsn'],
            $config['username'],
            $config['password'],
            $config['options']
        );
        // 设置会话参数，避免每次查询走磁盘排序
        $pdo->exec("SET SESSION sort_buffer_size = 4M");
        $pdo->exec("SET SESSION join_buffer_size = 4M");
    }
    return $pdo;
}

参数逐项分析：为什么这么调

1. innodb_buffer_pool_size：最核心的参数

这是InnoDB的「内存仓库」，缓存数据和索引。8.0默认128M，对着一张1GB的表做全表扫描会直接打满，后续查询全部走磁盘，一条简单SQL慢到怀疑人生。

设置原则：物理内存的50%-60%。8G内存的机器设4G，32G的机器设16-20G。别设到80%以上，要给OS、连接线程、排序缓冲留余地。这台4核8G云主机上我对比过：2G buffer pool 时 QPS 是4210，4G 时是7483，提升77%。

特殊参数：8.0.30+ 引入了innodb_buffer_pool_instances，每实例至少1G。4G池设4个实例，能减少并发访问的锁竞争。如果你在 8.0.30 以下版本，也需要手动设置，但值得注意的是默认值是8，所以需要显式设4以避免无谓的锁开销。

2. innodb_log_file_size 和 innodb_redo_log_capacity：被低估的坑

默认48M的redo log，意味着MySQL每写48M数据就要做一次checkpoint把脏页刷盘。在SSD上刷盘虽快，但频繁checkpoint会抢业务IO，导致周期性性能抖动。

之前线上MySQL每5分钟一次CPU尖刺，查了Innodb_log_waits状态值，数字狂涨。把log file从48M提到1G后，尖刺消失，TPS提升31%。

8.0.30版本开始，innodb_log_file_size被innodb_redo_log_capacity取代，默认100M。建议直接设3G（对应3个1G log file），兼顾恢复时间和写入吞吐。

3. innodb_flush_log_at_trx_commit：性能和数据安全的取舍

三个值对应三种策略：

• 1（默认）：每次事务提交都刷盘，最安全，但每次commit一个fsync，性能差。
• 0：每秒刷一次盘，性能最好，但MySQL崩溃丢1秒数据，OS崩溃也丢。
• 2：每次提交写OS缓存，每秒刷盘一次。MySQL崩溃不丢，OS崩溃丢1秒。

大促期间我选了2。测试数据：值1时TPS 312，值2时TPS 486，提升55%。对电商大促这种场景，GAP为1秒的数据丢失是可以接受的——大不了补单，但服务器直接502是绝对不能接受的。日常业务如果是支付/订单强一致场景，建议保持1。

4. innodb_io_capacity：不告诉MySQL你的SSD有多快

默认200，意味MySQL以为你的磁盘每秒只能做200次IO。后台刷脏页、合并插入缓冲都按这个速度来。SSD实际能跑2000+，MySQL却用200的节奏磨蹭，脏页堆积到90%才紧急刷。

设1500后，脏页刷盘平稳，Innodb_buffer_pool_pages_dirty从峰值5000+降到800左右。

5. max_connections：调高不等于滥用

默认151。压测工具模拟200连接直接报Too many connections。调到600后，配合skip-name-resolve（跳过DNS反查，减少握手延迟），压测600并发稳定运行。

但你要清楚：每个连接大约占用1-2MB内存。600连接 ≈ 1.2G内存。8G内存机器去掉MySQL 4G buffer pool，系统缓冲和web服务，600是上限。再高会触发内存交换，性能直接跳水。大促完事记得调回200，省内存。

6. performance_schema = OFF：省10% CPU

8.0的performance_schema更强大，但开销不可忽视。实测8线程压测下，开启时CPU 71%，关闭后59%，QPS提升约12%。如果你的业务不需要细粒度监控指标（如等待事件分析），建议关掉，用Prometheus + mysqld_exporter 监控就够了。

但注意：如果你们的监控体系依赖performance_schema的数据（比如PMM），那别关，写个脚本定期采集再清理更合理。

调优前后对比：完整压测数据

测试环境一致：sysbench 1.0.20，oltp_read_write.lua，单表100万行，8线程压测600秒。状态：MySQL 8.0.35，4核8G，CentOS 7.9。

指标	默认配置	基础调优	完整调优
QPS	2,381	4,927	7,483
TPS	119	246	374
平均延迟(ms)	33.6	16.2	10.7
95%延迟(ms)	58.4	28.9	19.6
CPU占用	72%	64%	61%
内存占用	3.1G	6.2G	7.1G

基础调优改动项：innodb_buffer_pool_size=2G、max_connections=300、innodb_log_file_size=512M、innodb_flush_log_at_trx_commit=2。

完整调优在基础之上：innodb_buffer_pool_size=4G、innodb_io_capacity=1500、performance_schema=OFF、thread_cache_size=64、sort_buffer_size=4M 等。

还原线上故障场景：压测工具模拟300并发时，默认配置下跑到第3分钟就开始报连接超时，第7分钟MySQL直接拒绝连接。调优后同样300并发跑了10分钟，系统稳定，CPU峰值58%，连接数峰值287，无一次超时。

# 查看压测结果摘要的完整命令
sysbench /usr/local/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password='your_pass' \
  --mysql-db=sbtest \
  --tables=1 \
  --table-size=1000000 \
  --threads=8 \
  --time=600 \
  --report-interval=10 \
  --rand-type=uniform \
  run 2>&1 | tee /tmp/bench_result.log

# 压测完成后查看摘要
grep -E "(queries|transactions|latency|percentile)" /tmp/bench_result.log

避坑：我实际踩过的5个坑

这套配置救火成功，但过程中踩了几个坑，写下来省得你们再踩一遍。

坑1：innodb_buffer_pool_size 直接设10G，机器 OOM

第一次调优，我把buffer pool从128M直接拉到10G——因为网上说「越大越好」，按下不表物理内存根本只有8G。MySQL启动成功但跑了两小时，OS直接OOM，把mysqld给杀了。buffer pool不是越大越好，超过物理内存60%就会和OS其他进程抢内存，触发swap后性能反而更差。

坑2：max_connections 调到2000，连接数上去了 CPU 爆了

大促前我把max_connections调到2000，想着「多接点没事」。结果压测400并发时CPU先到100%，大量连接在等待锁和IO，真正干活的没几个。调大连接数是扩容，但底层硬件没变，线程上下文切换的开销反而让性能更差。2000个线程在4核CPU上切换，光调度就吃掉30% CPU。

正确的思路：连接数调大后，同时缩短SQL执行时间。连接池配合慢查询优化，把每个连接占用CPU的时间降下来。

坑3：改了 /etc/my.cnf 不生效，原来是权限问题

改完配置文件重启MySQL，SHOW VARIABLES LIKE 'innodb_buffer_pool_size'还是128M。排查半小时，发现/etc/my.cnf的权限是644，MySQL启动时读不到（因为以mysql用户运行）。用chmod 644 /etc/my.cnf解决。

另外注意：MySQL 8.0 会按顺序读取所有配置片段，包括 /etc/my.cnf.d/ 目录下的文件。如果多个配置文件里都写了同一个参数，后读取的会覆盖先读取的。

坑4：skip-name-resolve 开了之后 PHP 连不上

开启skip-name-resolve后，PHP的PDO连接直接报 Access denied for user 'app'@'localhost'。原因：MySQL的user表里授权主机写的是localhost，开启skip-name-resolve后MySQL跳过反查，localhost不能被解析为127.0.0.1。解决：把用户授权改成 'app'@'127.0.0.1'。

-- 修改用户授权主机
RENAME USER 'app'@'localhost' TO 'app'@'127.0.0.1';
SELECT user, host FROM mysql.user;
FLUSH PRIVILEGES;

坑5：undo 表空间不足导致 SQL 报错

调优后跑大批量UPDATE，报错 Table '/tmp/#sql...' is full。排查发现ibtmp1（临时表空间）默认12M，大批量排序操作直接把临时表空间打爆了。配置里加上innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G 解决。注意这个参数只能在配置文件里改，不支持在线修改。

坑6：performance_schema 关闭后，原来依赖它的监控脚本直接报错

关掉performance_schema后，我现有的监控脚本（用的performance_schema.events_statements_summary_by_digest 查慢SQL排行）直接返回空。 这也是坑5之后紧接着踩到的。关掉前先确认监控系统是否有依赖，或者改用slow_log表来分析慢SQL。

最后：调优不是万能药

这套参数在4核8G的机器上，能把MySQL的QPS推到7000+，覆盖绝大多数中小型业务的峰值流量。但95%延迟19.6ms已经是这台机器的物理极限。如果你的业务未来日活过百万，光调参数撑不住，得考虑读写分离、分库分表，或者换更强规格的机器。

你们现在的MySQL是什么版本？踩过什么参数坑？欢迎评论区交流。