第 65 章 参数调优踩坑

📖 本篇简介：参数调优是 DBA 的基本功，也是最容易"抄作业抄出事"的环节。网上流传的 my.cnf 抄来就用，往往水土不服。本篇基于 MySQL 8.0，梳理缓冲池、刷盘策略、连接数、临时表等核心参数调优时最容易踩的 11 个坑。前置知识是 InnoDB 架构。学完后你应能理解每个关键参数的代价、分清动态与静态参数、并用压测数据说话而不是凭感觉。

目录

• 坑 1：照搬网上的 innodb_buffer_pool_size
• 坑 2：改参数前不区分动态/静态
• 坑 3：sync_binlog 设置不当丢数据或太慢
• 坑 4：临时表大小照搬导致落盘
• 坑 5：max_connections 盲目调大
• 坑 6：沿用 5.7 的 query cache 思路
• 坑 7：不看版本差异照搬旧参数
• 坑 8：调参前不压测验证就上线
• 坑 9：innodb_flush_log_at_trx_commit 妥协过度
• 坑 10：只调 MySQL 不看 OS 层参数
• 坑 11：log_bin_trust_function_creators 乱开
• 参数调优方法论
• 速查表：核心参数与代价
• 自测练习

坑 1：照搬网上的 innodb_buffer_pool_size

场景 / 错误示范：

网上文章说"缓冲池设成物理内存的 70%"，DBA 在一台 64G 内存的机器上照抄，设成 45G。结果这台机器还跑了其他进程，OS 和其他程序内存不足，开始 swap，数据库性能暴跌。

# 错误：不看机器实际用途，照搬比例
[mysqld]
innodb_buffer_pool_size = 45G   # 64G 机器，留给 OS 和其他进程的太少

为什么是坑：innodb_buffer_pool_size 是 InnoDB 把数据和索引缓存在内存的区域，理论上越大越好。但它不能吃掉全部内存——OS 文件系统缓存、连接线程、其他进程都要分内存。照搬比例不看整机内存用途，很容易导致内存紧张甚至 swap。swap 一旦发生，数据库性能断崖式下跌。

正确做法：

专用 DB 机器上按物理内存的 50%~70% 设，并预留足够给 OS；多实例/多进程机器要重新核算。

# 正确：64G 专用 DB 机器，缓冲池设为总内存的约 60%，其余留给 OS 与连接
[mysqld]
innodb_buffer_pool_size = 38G
# 专用机其余内存留给 OS page cache、连接、临时排序等

-- 观察缓冲池命中率判断是否合理（本示例基于 MySQL 8.0）
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- 命中率应 >99%，长期偏低才考虑加大缓冲池

加分点：8.0 支持 innodb_buffer_pool_instances 分实例减少锁竞争，大缓冲池（>16G）建议分 4~8 个实例。

坑 2：改参数前不区分动态/静态

场景 / 错误示范：

DBA 用 SET GLOBAL 改了一个需要重启才生效的静态参数，以为立刻生效，观察半天没变化，白折腾。或者反过来，改了动态参数写进配置文件，重启后却发现没持久化。

-- 错误：不区分动态/静态，盲目 SET GLOBAL
SET GLOBAL innodb_buffer_pool_size = 51539607552;  -- 该参数静态，需重启，SET 不生效

为什么是坑：MySQL 参数分两类——动态参数可在线 SET GLOBAL 立即生效；静态参数必须改配置文件并重启才生效。搞混了要么改了不生效（白忙），要么重启后才发现配置没写进文件（丢失）。调参第一步永远是先确认这个参数是动态还是静态。

正确做法：

先用 @@ 查询并判断参数类型，动态参数在线改+写配置文件，静态参数安排重启窗口。

-- 1) 查看参数当前值与是否动态（本示例基于 MySQL 8.0）
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 2) 查参数是否只读（静态）：INFORMATION_SCHEMA
SELECT VARIABLE_NAME, VARIABLE_TYPE
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
WHERE VARIABLE_NAME = 'innodb_buffer_pool_size';
-- VARIABLE_TYPE=GLOBAL 且非 DYNAMIC 的通常需重启

-- 3) 动态参数：在线改 + 同步写 my.cnf 持久化
SET GLOBAL max_connections = 2000;
-- 同时把 max_connections=2000 写进 my.cnf，避免重启丢失

加分点：用 SET PERSIST（8.0）可以在线改并自动写入 mysqld-auto.cnf，兼顾即时生效与持久化，比手动改 my.cnf 安全。

坑 3：sync_binlog 设置不当丢数据或太慢

场景 / 错误示范：

为了性能把 sync_binlog 设成 0，binlog 完全交给 OS 刷盘。主机突然掉电，最后几个事务的 binlog 还在内核页缓存里没刷盘，主从切换后数据丢失。

# 错误：sync_binlog=0，性能好但宕机丢数据
[mysqld]
sync_binlog = 0

为什么是坑：sync_binlog 控制 binlog 何时刷到磁盘。设 0 时 MySQL 不主动刷盘，依赖 OS，性能最好但宕机丢数据；设 1 时每次事务提交都刷盘，最安全但最慢。这个参数直接决定"宕机丢多少数据"，是持久化的核心开关，图性能设 0 等于拿数据安全换速度。

正确做法：

默认 sync_binlog=1 保证不丢，只有能容忍少量丢失的场景才妥协。

# 正确：金融/账务场景保持最安全配置（本示例基于 MySQL 8.0）
[mysqld]
sync_binlog = 1                          -- 每次提交刷 binlog
innodb_flush_log_at_trx_commit = 1       -- 配合 redo log 也每次刷
# 这两个 1 是 MySQL "双 1"，保证宕机不丢已提交事务

加分点："双 1"（sync_binlog=1 + innodb_flush_log_at_trx_commit=1）是数据安全的基线，牺牲点性能换不丢数据，绝大多数生产场景都该开。

坑 4：临时表大小照搬导致落盘

场景 / 错误示范：

DBA 把 tmp_table_size 和 heap_table_size 都设成 64M，以为够大。结果复杂 GROUP BY/DISTINCT 查询产生的临时表超过 64M，自动从内存转成磁盘临时表，磁盘 IO 暴涨，查询变慢。

-- 错误：临时表阈值设小了，超限就落盘
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';  -- 持续增长，说明频繁落盘

[mysqld]
tmp_table_size = 64M        -- 偏小，复杂查询临时表超限落盘
heap_table_size = 64M

为什么是坑：tmp_table_size 控制内存临时表上限，超过就转磁盘临时表（用 MyISAM/innodb-on-disk），性能差一个量级。两个参数要一起设成相同值才生效。不监控 Created_tmp_disk_tables，就不知道自己一直在落盘，还以为是 SQL 写得慢。

正确做法：

监控落盘临时表数量，合理调大两个参数并优化 SQL。

# 正确：两个参数设为相同值，给复杂聚合足够内存
[mysqld]
tmp_table_size = 256M
heap_table_size = 256M

-- 监控落盘临时表是否频繁（本示例基于 MySQL 8.0）
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_disk_tables / Created_tmp_tables 比例高，说明临时表频繁落盘
-- 根因往往是 SQL 本身需要优化（加索引、减少大排序），而不只是调大参数

加分点：临时表落盘的根治办法是优化 SQL（让聚合走索引、避免大排序），调参数只是缓解。

坑 5：max_connections 盲目调大

场景 / 错误示范：

业务高峰期频繁报 Too many connections，DBA 直接把 max_connections 从 500 调到 5000。结果每个连接都要占内存，几千个连接把内存吃光，上下文切换开销暴增，数据库反而更慢。

# 错误：连接数报错就盲目调大，不管内存和 CPU 扛不扛得住
[mysqld]
max_connections = 5000

为什么是坑：每个连接在 MySQL 里都要分配线程和内存（连接缓冲区、排序缓冲区等）。连接数不是越大越好——几千个并发连接会让 CPU 大量时间花在线程切换上，内存占用也线性增长。真正的问题往往是"连接泄漏"或"慢查询占着连接不放"，盲目调大只是把问题延后。

正确做法：

先查连接为什么多（泄漏？慢查询？），再合理设置并配合连接池。

-- 先看连接都被谁占了（本示例基于 MySQL 8.0）
SELECT user, host, db, COUNT(*) AS conn_cnt
FROM information_schema.PROCESSLIST
GROUP BY user, host, db
ORDER BY conn_cnt DESC;
-- 看是不是某个应用连接泄漏，或长事务占着连接

-- 合理设置：按内存和业务并发估算，而不是越大越好
SET GLOBAL max_connections = 1000;

# 正确：配合应用连接池限制，数据库侧连接数可控
[mysqld]
max_connections = 1000
# 应用侧用连接池把并发连接收敛到合理范围

加分点：MySQL 8.0 默认线程池（thread_pool）可在高并发下减少线程切换开销，比单纯堆连接数有效。

坑 6：沿用 5.7 的 query cache 思路

场景 / 错误示范：

老 DBA 从 5.7 过来，还想着开启 query cache 缓存查询结果提升性能。在 8.0 上找 query_cache_type，发现参数已经不存在了。

-- 错误：8.0 已彻底移除 query cache，再调相关参数无效
-- 本示例基于 MySQL 8.0
SHOW VARIABLES LIKE 'query_cache_type';   -- 已不存在，报错/空

为什么是坑：查询缓存（query cache）在 5.7 就已被标记废弃，8.0 彻底移除。它的问题是——只要表有任何写，该表所有缓存全部失效，写频繁的业务命中率极低，还引入锁竞争。老调优文档里关于 query cache 的经验，在 8.0 上完全不适用。

正确做法：

8.0 不要再碰 query cache，靠索引和 buffer pool 提升性能。

-- 正确：8.0 用 buffer pool 和索引加速，而非查询缓存
-- 本示例基于 MySQL 8.0
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 查询结果缓存应放到应用层（Redis），而不是数据库层

加分点：8.0 引入了 schema 表缓存、innodb_buffer_pool 预热等新机制替代老思路，别再背 query cache 的旧知识。

坑 7：不看版本差异照搬旧参数

场景 / 错误示范：

DBA 把 5.7 时代的 my.cnf 原样搬到 8.0。有些参数 8.0 已废弃、改名或行为变了，启动时报 warning，部分参数 silently 失效。

# 错误：5.7 配置直接搬到 8.0
[mysqld]
query_cache_type = 1          -- 8.0 已移除，失效
innodb_file_format = Barracuda -- 8.0 已移除

为什么是坑：MySQL 大版本升级会废弃、改名、改变参数行为。5.7 的 my.cnf 直接搬到 8.0，废弃参数会被忽略（不报错但失效），行为变化的参数可能导致性能或安全问题。照搬旧配置等于在 8.0 上跑着一堆"以为生效其实没生效"的参数。

正确做法：

升级后对照官方文档核对每个参数，清理废弃项。

-- 升级后检查有哪些参数不被识别（本示例基于 MySQL 8.0）
SHOW VARIABLES;   -- 对照旧配置，看哪些已不存在
-- 启动日志里也会有 unknown variable 的 warning，需逐一清理

# 正确：8.0 上只保留 8.0 仍有效的参数，废弃的删掉
[mysqld]
# 删除 query_cache_*、innodb_file_format 等废弃项
transaction_isolation = READ-COMMITTED   -- 8.0 用 transaction_isolation（5.7 是 tx_isolation）

加分点：升级大版本后，第一件事是用 mysql_upgrade 或官方 checklist 核对参数，而不是直接复用旧配置。

坑 8：调参前不压测验证就上线

场景 / 错误示范：

DBA 看到一篇文章说某个参数改了能提速 30%，直接在生产库 SET GLOBAL 改了。结果业务模式和文章里不一样，改完反而变慢，又紧急改回来，期间业务抖动。

-- 错误：凭文章/感觉直接在生产改核心参数，不压测
SET GLOBAL innodb_flush_method = O_DIRECT;  -- 没验证就上生产

为什么是坑：参数效果高度依赖业务负载——同样的参数，读写比例、数据量、并发不同，效果可能相反。直接在生产改核心参数，等于拿线上业务做实验。调参必须在预发环境用真实流量回放压测，验证有效再灰度上生产。

正确做法：

压测验证 → 灰度 → 全量，每步观察。

# 正确：预发环境用 sysbench 或影子流量压测对比
# 改前跑一遍基准，改后跑一遍对比 QPS/RT
sysbench oltp_read_write --mysql-db=test run
# 对比改参数前后的吞吐和延迟，确认有效且无副作用

-- 灰度：先在一台从库改，观察无异常再逐台推
-- 本示例基于 MySQL 8.0
SET GLOBAL innodb_flush_method = O_DIRECT;  -- 先在从库验证

加分点：每次调参都记录"改了什么、为什么、效果如何"，形成自己的调参知识库，避免重复试错。

坑 9：innodb_flush_log_at_trx_commit 妥协过度

场景 / 错误示范：

为了提升写入性能，DBA 把 innodb_flush_log_at_trx_commit 从 1 改成 2。性能确实提升了，但主机宕机（不是 MySQL 重启）时，最后 1 秒的 redo log 还没刷盘，事务丢失。

# 错误：为性能把刷盘级别降到 2，宕机丢数据
[mysqld]
innodb_flush_log_at_trx_commit = 2

为什么是坑：这个参数控制 redo log 刷盘时机：1 是每次提交都刷盘（最安全）；2 是每次提交写 OS 缓存、每秒刷盘（MySQL 重启不丢、OS 宕机丢 1 秒）；0 是更差。它和 sync_binlog 一起决定宕机丢数据量。为了那点写性能把它降到 2，等于把"宕机必丢 1 秒数据"接受下来，账务场景不可接受。

正确做法：

数据安全敏感场景保持双 1，性能场景才谨慎妥协。

# 正确：账务/交易场景保持双 1（本示例基于 MySQL 8.0）
[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# 只有日志、监控类可丢几秒数据的业务，才考虑降为 2 换性能

加分点：判断标准是"宕机时能不能接受丢 1 秒数据"——能接受才妥协，不能接受坚决双 1。

坑 10：只调 MySQL 不看 OS 层参数

场景 / 错误示范：

DBA 把 MySQL 参数调到最优，但 OS 层 vm.swappiness=60（倾向用 swap）、文件系统是 ext4 没开 deadline 调度，MySQL 性能还是上不去。

# 错误：只调 MySQL，OS 层放任不管
# swappiness 默认 60，内存紧张时容易触发 swap，数据库性能暴跌

为什么是坑：MySQL 跑在 OS 之上，OS 的内存管理、文件系统、IO 调度直接影响数据库性能。MySQL 调得再好，OS 动不动 swap、IO 调度不对，照样慢。数据库优化是"OS + MySQL + SQL"三层的事，只调一层是头痛医头。

正确做法：

OS 层配合调优，尤其关闭 swap 倾向。

# 正确：DB 专用机 OS 层调优（本示例基于 Linux）
# 1) 关闭 swap 倾向，避免数据库被换出内存
sysctl -w vm.swappiness=1
# 2) 文件系统选 xfs，挂载加 noatime
# 3) IO 调度器用 deadline/none（SSD）
echo deadline > /sys/block/sda/queue/scheduler

# MySQL 侧配合 O_DIRECT 绕过 OS 缓存，避免 double buffer
[mysqld]
innodb_flush_method = O_DIRECT

加分点：DBA 至少要懂 swappiness、文件系统挂载选项、IO 调度这三项 OS 基础调优。

坑 11：log_bin_trust_function_creators 乱开

场景 / 错误示范：

创建存储函数时报错"You might want to use the DETERMINISTIC..."，DBA 图省事直接 SET GLOBAL log_bin_trust_function_creators=1 绕过，埋下主从不一致隐患。

-- 错误：为建函数图省事，直接信任所有函数创建者
SET GLOBAL log_bin_trust_function_creators = 1;
-- 之后创建的非确定性函数，binlog  statement 格式下主从执行结果可能不同

为什么是坑：开启 binlog 后，创建存储函数/触发器有安全限制——非确定性函数（含 NOW()、RAND()）在 statement 格式 binlog 下，主从执行结果可能不同，导致主从不一致。直接开 log_bin_trust_function_creators=1 绕过检查，等于放弃了这层保护。正确做法是规范函数定义（声明 DETERMINISTIC 或用 ROW 格式 binlog），而不是关闭校验。

正确做法：

规范函数定义，binlog 用 ROW 格式，谨慎开这个参数。

-- 正确：函数明确声明确定性，或 binlog 用 ROW 格式（本示例基于 MySQL 8.0）
SET GLOBAL binlog_format = ROW;   -- ROW 格式记录行变化，函数主从一致

-- 创建函数时声明 DETERMINISTIC（确定性）
CREATE FUNCTION calc_price(qty INT) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN qty * 10.0;
END;

加分点：8.0 默认 binlog 就是 ROW 格式，这也是 ROW 相比 statement 的一大优势。

参数调优方法论

参数调优不是"抄配置"，而是一套有方法论的系统工程。以下是经过验证的调优思路。

| 步骤 | 做什么 | 常见错误 |

|---|---|---|

| 1. 量测基线 | 先压测记录当前 QPS/RT/资源占用 | 没基线就调，不知变好变坏 |

| 2. 定位瓶颈 | 看是 CPU/IO/锁/缓冲池哪类瓶颈 | 上来就乱调一通 |

| 3. 小步调整 | 一次只改一个参数，观察效果 | 一次改一堆，无法归因 |

| 4. 压测验证 | 预发压测对比改前改后 | 直接生产改 |

| 5. 灰度上线 | 先从库/单实例，再全量 | 全量一把梭 |

| 6. 持续观察 | 上线后盯监控确认无副作用 | 改完就不管了 |

核心原则：没有"通用最优配置"，每个业务的最优参数都不同。调参的功夫一半在调，一半在测量和验证。永远用数据说话，而不是网上文章的结论。

速查表：核心参数与代价

| 参数 | 安全值 | 调优方向 | 代价/风险 |

|---|---|---|---|

| innodb_buffer_pool_size | 内存 50%~70% | 命中率低才加大 | 太大触发 swap |

| sync_binlog | 1 | 一般不动 | 0 宕机丢数据 |

| innodb_flush_log_at_trx_commit | 1 | 一般不动 | 2 宕机丢 1 秒 |

| max_connections | 按内存估 | 先查连接为何多 | 盲目调大耗内存 |

| tmp_table_size/heap_table_size | 相同值 | 落盘频繁才调大 | 调大占内存 |

| innodb_flush_method | O_DIRECT | 避免 double buffer | — |

| log_bin_trust_function_creators | 0 | 规范函数定义 | 乱开致主从不一致 |

自测练习

1. innodb_buffer_pool_size 是不是越大越好？

<details>

<summary>参考答案</summary>

不是。它要和整机内存用途匹配，专用机 50%~70%，留够 OS 和连接内存。盲目设太大会触发 swap，性能反而暴跌。用缓冲池命中率判断是否真需要加大。

</details>

2. 动态参数和静态参数调法有什么区别？

<details>

<summary>参考答案</summary>

动态参数可 SET GLOBAL（或 8.0 的 SET PERSIST）在线生效并持久化；静态参数必须改配置文件重启才生效。调参前先确认参数类型，否则改了不生效或重启丢失。

</details>

3. 什么是 MySQL 的"双 1"？

<details>

<summary>参考答案</summary>

sync_binlog=1（每次提交刷 binlog）+ innodb_flush_log_at_trx_commit=1（每次提交刷 redo log）。两个都为 1 保证宕机不丢已提交事务，是数据安全基线，牺牲少量写性能。

</details>

4. 为什么不能盲目调大 max_connections？

<details>

<summary>参考答案</summary>

每个连接占线程和内存，连接数太大会导致上下文切换开销暴增、内存耗尽。正确做法是先用 PROCESSLIST 查连接为什么多（泄漏/慢查询占连接），配合应用连接池收敛，而不是一味调大数据库上限。

</details>

5. 8.0 上为什么不要再调 query cache？

<details>

<summary>参考答案</summary>

query cache 在 8.0 已彻底移除。它在写频繁业务命中率低还引入锁竞争，应改用 buffer pool、索引和应用层 Redis 缓存来提升读性能。

</details>

下一篇预告：第 66 章 磁盘与容量踩坑