MySQL 8.0 生产参数调优清单：一份可落地的 my.cnf 与每个参数背后的取舍

个人主页：
> for_ever_love__ <（欢迎各位大佬莅临😊）

其他栏目:
> 大模型开发从0到1 <

其他栏目:
> iOS项目总结大全 <

其他栏目:
> 我想学python了 <

其他栏目:
> iOS UI <

文章目录

• MySQL 8.0 生产参数调优清单：一份可落地的 my.cnf 与每个参数背后的取舍
• • 一、调优的正确姿势：先量后改
  • 二、内存类参数（最重要，也最容易出事）
  • • 2.1 innodb_buffer_pool_size
    • 2.2 innodb_buffer_pool_instances
    • 2.3 每连接内存：最容易把机器搞挂的地方
    • 2.4 max_connections
  • 三、IO 与刷盘：性能抖动的主要来源
  • • 3.1 双一配置（数据安全 vs 性能）
    • 3.2 redo log 容量
    • 3.3 刷盘能力参数
    • 3.4 IO 线程
  • 四、并发与锁相关
  • 五、表缓存与文件句柄
  • 六、binlog 与复制
  • 七、字符集、SQL 模式与其它 8.0/5.7 差异
  • 八、一份 32C / 64G / SSD 的参考配置
  • 九、动态修改与持久化
  • 十、常见误区
  • 小结

MySQL 8.0 生产参数调优清单：一份可落地的 my.cnf 与每个参数背后的取舍

网上流传的"MySQL 优化配置"大多是这样的：一堆参数往 my.cnf 里塞，注释写着"提升性能 300%"。 真相是：参数调优没有银弹，只有取舍。 同一个参数，在 SSD 和机械盘上最优值相反；在金融业务和日志业务上最优值也相反。

这篇只讲"为什么这么调"，并给出一份 32C/64G 的参考配置和计算方法。 所有默认值以 MySQL 8.0 为准，顺带标注与 5.7 的差异。

一、调优的正确姿势：先量后改

改参数之前，先搞清楚三件事：瓶颈在哪（CPU / IO / 内存 / 锁 / SQL 本身）、 业务特征（读多写少？大事务多？热点行更新多？）、硬件是什么（SSD 还是机械盘）。

— 先看整体命中率和负载特征
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';
SHOW GLOBAL STATUS LIKE 'Opened_tables';

一次只改一个参数，改完压测。 一次改十个，出了问题你连是谁都不知道。

二、内存类参数（最重要，也最容易出事）

2.1 innodb_buffer_pool_size

这是整个 MySQL 里最重要的一个参数，没有之一。 默认只有 128MB（远远不够）， 专用数据库服务器建议给到物理内存的 50% ~ 70%，混部服务器则先减去系统和其它进程再算。

为什么不给到 80%+？ 因为还要给每连接的 sort/join/read buffer、临时表、 performance_schema、OS 页缓存留内存。给太满会 OOM 或触发 swap，而数据库一旦 swap，性能就是断崖式下跌。

一个 64GB 专用机的粗略算法：系统预留 2G + 线程级内存约 2G （500 连接 × 约 4MB，注意这些 buffer 是"需要时才分配"）+ performance_schema 1G = 剩 59G，实际配 40~48G 比较稳妥。

命中率怎么算：

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 hit_rate;

OLTP 场景 低于 99% 就该考虑加内存或优化 SQL（减少全表扫描）。

2.2 innodb_buffer_pool_instances

把 Buffer Pool 切成多个实例，各自管理 LRU / flush list，减少互斥锁竞争。 5.7 / 8.0 都是"BP ≥ 1GB 时默认 8 个实例，小于 1GB 时为 1"。

规则：每个实例至少 1GB 才有意义。BP 是 4G 就设 4，是 40G 就设 8~16；设成 64 而 BP 只有 4G，反而退化。

⚠️ 8.0 引入 innodb_buffer_pool_chunk_size（默认 128MB），innodb_buffer_pool_size 必须是 chunk_size × instances 的整数倍，MySQL 会自动向上取整。

2.3 每连接内存：最容易把机器搞挂的地方

这是调优领域最大的坑：这些 buffer 是每个连接独立分配的。 把 sort_buffer_size 设成 64MB，500 个连接同时排序就是 32GB，机器直接 OOM。

正确做法是会话级临时调大：跑 ETL 大查询前 SET SESSION sort_buffer_size = 32*1024*1024;，跑完 SET SESSION sort_buffer_size = DEFAULT;。

判断依据：Created_tmp_disk_tables、Created_tmp_tables、Sort_merge_passes。 Sort_merge_passes 很高说明排序缓冲区不够，但先想想能不能用索引消除排序，而不是无脑加 buffer。

2.4 max_connections

默认 151（太保守），建议 300~1000 并配合连接池。

⚠️ max_connections 不是越大越好。连接数超过 CPU 核数的几倍后， 上下文切换开销会让吞吐下降。连接被打满通常是"连接被慢 SQL 占住了"，解决慢 SQL 才是根本。

max_connections = 500
thread_cache_size = 50 # 8.0 默认按公式算，约 8+max_connections/100
max_connect_errors = 100000 # 防止网络抖动后被 host cache 拉黑
wait_timeout = 600 # 空闲连接超时，短一点能释放资源
interactive_timeout = 600

三、IO 与刷盘：性能抖动的主要来源

3.1 双一配置（数据安全 vs 性能）

两个参数决定"宕机丢不丢数据"，8.0 默认都是 1：

取舍建议：

⚠️ 注意：只把 innodb_flush_log_at_trx_commit 调松而 sync_binlog 保持 1， 并不能省多少 IO，因为 binlog 的 fsync 还在。要调就一起调。

3.2 redo log 容量

参数名在 8.0.30 变了：之前是 innodb_log_file_size（默认 48MB × 2）， 8.0.30 起改为 innodb_redo_log_capacity（默认 100MB，redo 变成动态的 32 个文件）。

redo 太小的后果很严重：写满就触发强制 checkpoint 把脏页全刷下去， 表现为 TPS 周期性掉到接近 0，监控图上是典型的"锯齿"。 判断方法是看 SHOW ENGINE INNODB STATUS 里 LOG 段的 Log sequence number – Last checkpoint at（未 checkpoint 的日志量）。

经验公式：让 redo 容量能容纳 1 小时的写入量。测法很简单， 取两次 Innodb_os_log_written 的差值（DO SLEEP(60) 隔 60 秒），算出每小时写入量即可。 写入密集的库通常配 2G~8G。也不是越大越好——崩溃恢复时间会变长。

# 8.0.30 之前
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
# 8.0.30 及之后
innodb_redo_log_capacity = 4G

3.3 刷盘能力参数

⚠️ innodb_io_capacity=200 是 MySQL 默认给机械盘的值。 SSD 上不改它，刷脏速度跟不上写入，就会频繁触发 “fuzzy checkpoint 追不上”， 表现为写入抖动。这是从 5.7 迁移到 SSD 后最常见的漏配项。

3.4 IO 线程

innodb_read_io_threads = 8 # 默认 4
innodb_write_io_threads = 8 # 默认 4
innodb_purge_threads = 4 # 默认 4，大写入库可以设 8
innodb_page_cleaners = 4 # 默认 4，应与 purge_threads 一致

innodb_purge_threads 负责回收 undo。 大事务 / 大量 UPDATE 的表，purge 跟不上会导致 history list 变长、 undo 表空间膨胀，进而拖慢所有快照读。看这个指标：

SHOW ENGINE INNODB STATUS\\G
— 看 TRANSACTIONS 段的 History list length

History list length 持续超过几万，说明 purge 落后了， 优先排查有没有长事务（information_schema.innodb_trx）。

四、并发与锁相关

innodb_thread_concurrency 说明：默认 0 表示不限制，通常是最好的选择。 只有看到 SHOW ENGINE INNODB STATUS 里大量 “threads waiting for InnoDB semaphore” 时， 才考虑设成具体值（比如 CPU 核数的 2 倍），并且必须压测验证——设小了吞吐反而下降。

innodb_autoinc_lock_mode 版本差异是个容易踩的坑：8.0 默认 2（interleaved）， 配合默认 ROW binlog 安全且并发最好；5.7 默认 1（consecutive）； binlog 是 STATEMENT 时必须用 1，否则主从自增 ID 会不一致。

强烈建议打开死锁日志（innodb_print_all_deadlocks = ON）： 死锁信息默认只在 SHOW ENGINE INNODB STATUS 里保留最近一条，不打日志事后就没法排查。

五、表缓存与文件句柄

判断依据：

SHOW GLOBAL STATUS LIKE 'Opened_tables';
SHOW GLOBAL STATUS LIKE 'Opened_table_definitions';
SHOW GLOBAL STATUS LIKE 'Open_tables';

Opened_tables 增长很快（比如每秒几十次）说明 table_open_cache 太小。 分区表尤其要注意：一张 100 个分区的表，一次查询可能打开 100 个句柄。

六、binlog 与复制

server_id = 1
log_bin = /data/mysql/mysql-bin # 8.0 默认已开启
binlog_format = ROW # 8.0 默认 ROW
binlog_row_image = FULL # 8.0 默认 FULL，闪回/gh-ost 依赖
sync_binlog = 1
max_binlog_size = 1G # 默认 1G
binlog_expire_logs_seconds = 604800 # 8.0 默认 2592000(30天)；5.7 用 expire_logs_days
binlog_cache_size = 64K
log_slave_updates = ON # 8.0.26+ 叫 log_replica_updates，级联复制必须开

组提交优化（写压力大时可以试）：

binlog_group_commit_sync_delay = 100 # 微秒，等一等凑一批再 fsync
binlog_group_commit_sync_no_delay_count = 20 # 凑够 20 个就不等了

这能明显提升高并发写入吞吐，代价是每个事务的提交延迟增加约 100 微秒。 属于典型的"吞吐换延迟"。

并行复制：

replica_parallel_workers = 8 # 8.0.26+ 命名，5.7 叫 slave_parallel_workers
replica_parallel_type = LOGICAL_CLOCK # 基于组提交的并行回放
replica_preserve_commit_order = ON

⚠️ replica_parallel_type=DATABASE（旧默认）只在多库场景下有效， 单库多表的业务必须改成 LOGICAL_CLOCK 才有并行效果。

七、字符集、SQL 模式与其它 8.0/5.7 差异

升级 5.7 → 8.0 时，这几条要提前检查，否则会出各种"莫名其妙"的问题。

八、一份 32C / 64G / SSD 的参考配置

[mysqld]
# ———- 基础 ———-
server_id = 1 ; port = 3306 ; datadir = /data/mysql
character_set_server = utf8mb4 ; collation_server = utf8mb4_0900_ai_ci ; default_time_zone = +08:00
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# ———- 连接与表缓存 ———-
max_connections = 500 ; thread_cache_size = 50 ; max_connect_errors = 100000
wait_timeout = 600 ; interactive_timeout = 600 ; max_allowed_packet = 64M
table_open_cache = 4096 ; table_definition_cache = 2048 ; open_files_limit = 65535

# ———- Buffer Pool ———-
innodb_buffer_pool_size = 40G ; innodb_buffer_pool_instances = 8 ; innodb_buffer_pool_dump_pct = 40
innodb_buffer_pool_dump_at_shutdown = ON ; innodb_buffer_pool_load_at_startup = ON # 预热，降冷启动抖动

# ———- Redo / 刷盘 ———-
innodb_redo_log_capacity = 4G # 8.0.30+；旧版用 innodb_log_file_size=2G × 2
innodb_flush_log_at_trx_commit = 1 ; sync_binlog = 1 # 双一
innodb_flush_method = O_DIRECT ; innodb_flush_neighbors = 0 # SSD
innodb_io_capacity = 5000 ; innodb_io_capacity_max = 10000
innodb_max_dirty_pages_pct = 90 ; innodb_adaptive_flushing = ON

# ———- IO 线程 ———-
innodb_read_io_threads = 8 ; innodb_write_io_threads = 8 ; innodb_purge_threads = 4 ; innodb_page_cleaners = 4

# ———- 锁与事务 ———-
transaction_isolation = READ-COMMITTED # 高并发写场景；默认 RR 也可以
innodb_lock_wait_timeout = 20 ; innodb_print_all_deadlocks = ON ; innodb_autoinc_lock_mode = 2

# ———- 每连接内存（务必保守）———-
sort_buffer_size = 1M ; join_buffer_size = 1M ; read_buffer_size = 128K ; read_rnd_buffer_size = 256K
tmp_table_size = 64M ; max_heap_table_size = 64M ; binlog_cache_size = 64K

# ———- binlog 与慢日志 ———-
log_bin = /data/mysql/mysql-bin ; binlog_format = ROW ; binlog_row_image = FULL
max_binlog_size = 1G ; binlog_expire_logs_seconds = 604800
slow_query_log = ON ; long_query_time = 1 ; log_slow_extra = ON
min_examined_row_limit = 100 ; log_timestamps = SYSTEM # 慢日志用本地时间，别用 UTC

# ———- 其它 ———-
innodb_file_per_table = ON ; innodb_stats_persistent = ON ; innodb_stats_persistent_sample_pages = 20
innodb_online_alter_log_max_size = 1G ; innodb_print_ddl_logs = ON ; performance_schema = ON

⚠️ 这份配置只是起点。innodb_buffer_pool_size、innodb_io_capacity、redo 容量、 隔离级别这四项必须按你的实际硬件和业务调整后再压测。

九、动态修改与持久化

8.0 支持 SET PERSIST，可以把改动写进配置文件，重启后依然生效：

— 立即生效 + 持久化到 mysqld-auto.cnf
SET PERSIST innodb_io_capacity = 5000;
SET PERSIST_ONLY max_connections = 800; — 只持久化，重启后才生效

— 查看持久化了哪些
SELECT * FROM performance_schema.persisted_variables;

— 撤销
RESET PERSIST innodb_io_capacity;

持久化内容写在 datadir/mysqld-auto.cnf（JSON 格式）。 比手动改 my.cnf 安全些，但生产环境还是建议走配置管理，便于版本化和审计。

— 查看某个参数当前值
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT @@global.innodb_buffer_pool_size;

— 8.0 起 innodb_buffer_pool_size 可以在线调整
SET GLOBAL innodb_buffer_pool_size = 42949672960;

十、常见误区

误区 1：把 sort_buffer_size / join_buffer_size 调到几十 MB。 ——它们是每连接分配的。500 连接 × 64MB = 32GB，OOM 预定。 要调就调会话级。

误区 2：max_connections 越大越好。 ——连接数远超 CPU 核数后，上下文切换会吃掉所有收益。 连接被打满通常是慢 SQL 的果，不是因。

误区 3：innodb_buffer_pool_size 给到 80% 物理内存。 ——留够给每连接内存和 OS，否则一 swap 就完蛋。

误区 4：SSD 上沿用 innodb_io_capacity=200。 ——这是给机械盘的默认值。SSD 不改，刷脏跟不上写入，TPS 周期性掉底。

误区 5：关掉"双一"就能大幅提升性能。 ——只关 innodb_flush_log_at_trx_commit 而 sync_binlog 还是 1，收益很有限。 而且关掉意味着宕机会丢数据，这个代价要业务方签字。

误区 6：redo log 越大越好。 ——大 redo 减少 checkpoint 抖动，但崩溃恢复时间变长。 配 1 小时写入量是合理的平衡点。

误区 7：抄一份"万能配置"就上线。 ——参数之间互相影响，硬件和业务不同最优值就不同。 必须压测（sysbench、tpcc-mysql 或真实流量回放）。

误区 8：8.0 升级后老应用连不上，以为是网络问题。 ——大概率是 caching_sha2_password 认证插件。 要么升级驱动，要么建用户时指定 mysql_native_password。

小结

• 调优的顺序是：SQL 与索引 → 架构 → 参数。参数调优的收益远小于改一条烂 SQL
• 最重要的参数是 innodb_buffer_pool_size：专用机给物理内存的 50%~70%，留够给连接和 OS
• 每连接 buffer（sort/join/read）绝不能全局调大，只在会话级给特定查询临时调
• SSD 上必改三项：innodb_io_capacity（2000+）、innodb_flush_neighbors=0、innodb_flush_method=O_DIRECT
• redo 容量按 1 小时写入量估算，太小会导致 TPS 周期性掉底
• 双一（innodb_flush_log_at_trx_commit=1 + sync_binlog=1）是数据安全底线，放宽要业务签字
• 打开 innodb_print_all_deadlocks，否则死锁事后无从排查
• 8.0 与 5.7 的关键默认差异：字符集 utf8mb4、认证插件 caching_sha2_password、 explicit_defaults_for_timestamp=ON、脏页上限 90、autoinc_lock_mode=2、 8.0.30 起 redo 参数改为 innodb_redo_log_capacity
• 并行复制要用 replica_parallel_type=LOGICAL_CLOCK，旧的 DATABASE 模式在单库场景无效
• 改参数用 SET PERSIST，但一次只改一个，改完压测

下一篇聊 MySQL 高频面试题——把这一整个系列的核心考点串成一份可以拿去面试的清单。