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

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

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

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

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

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

-- 先看整体命中率和负载特征SHOWGLOBALSTATUSLIKE'Innodb_buffer_pool_read%';SHOWGLOBALSTATUSLIKE'Threads_%';SHOWGLOBALSTATUSLIKE'Created_tmp%';SHOWGLOBALSTATUSLIKE'Binlog_cache%';SHOWGLOBALSTATUSLIKE'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-(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAME='Innodb_buffer_pool_reads')/(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAME='Innodb_buffer_pool_read_requests'))AShit_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 表空间膨胀，进而拖慢所有快照读。看这个指标：

SHOWENGINEINNODBSTATUS\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里保留最近一条，不打日志事后就没法排查。

五、表缓存与文件句柄

判断依据：

SHOWGLOBALSTATUSLIKE'Opened_tables';SHOWGLOBALSTATUSLIKE'Opened_table_definitions';SHOWGLOBALSTATUSLIKE'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.cnfSETPERSIST innodb_io_capacity=5000;SETPERSIST_ONLY max_connections=800;-- 只持久化，重启后才生效-- 查看持久化了哪些SELECT*FROMperformance_schema.persisted_variables;-- 撤销RESET PERSIST innodb_io_capacity;

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

-- 查看某个参数当前值SHOWGLOBALVARIABLESLIKE'innodb_buffer_pool_size';SELECT@@global.innodb_buffer_pool_size;-- 8.0 起 innodb_buffer_pool_size 可以在线调整SETGLOBALinnodb_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 高频面试题——把这一整个系列的核心考点串成一份可以拿去面试的清单。