第 81 章速查卡：参数与调优

宋国卿2026-11-14 14:00:00

内容摘要

本篇把 MySQL 面试中最高频的系统参数按"内存 / 日志 / IO / 连接 / InnoDB / 复制"六大类整理成速查表，每条参数给出默认值、作用与调优建议。面试前扫一遍即可快速回忆起每个参数的"名字-干啥-怎么调"。本文所有示例基于 MySQL 8.0，参数名以 8.0 为准，5.7 与 8.0 的差异在第 84 章专门对比。

📖 本篇简介：本篇把 MySQL 面试中最高频的系统参数按"内存 / 日志 / IO / 连接 / InnoDB / 复制"六大类整理成速查表，每条参数给出默认值、作用与调优建议。面试前扫一遍即可快速回忆起每个参数的"名字-干啥-怎么调"。本文所有示例基于 MySQL 8.0，参数名以 8.0 为准，5.7 与 8.0 的差异在第 84 章专门对比。

1 本篇使用说明
2 内存相关参数速查
3 日志相关参数速查
4 IO与刷盘参数速查
5 连接与并发参数速查
6 InnoDB核心参数速查
7 复制与主从参数速查
8 常见调优组合建议
9 查看与修改变量命令
10 状态计数器速查
11 参数速记口诀
12 注意事项与常见坑
13 自测练习

1 本篇使用说明

本篇是一张"随身速查卡"，不是系统调优教程。面试中被问到某个参数时，你只需能回答三个问题：这个参数叫什么、默认值大概是多少、调大调小会带来什么影响。下面每张表都按这三个维度组织。

阅读建议：先看"作用"列建立印象，再看"调优建议"列记住方向，最后对照"常见坑"小节回忆面试官最喜欢追问的点。所有默认值以 MySQL 8.0 官方文档为准，线上环境默认值可能因发行版不同略有差异。

参数调优在面试里不是要你背出每个参数的精确字节数，而是要你建立"权衡意识"：任何一个参数调大，都是在用某样东西换另一样东西——用内存换速度、用磁盘换安全、用吞吐换延迟。能把这个权衡讲清楚，比背十个默认值更得分。

| 阅读维度 | 你要记住的 | 面试中的话术 |

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

| 参数名 | 准确拼写，下划线分隔 | "我一般会看 innodb_buffer_pool_size" |

| 默认值 | 量级即可，不必背精确字节 | "默认 128M，生产一般调到物理内存 50%~70%" |

| 调优方向 | 调大解决什么、调小牺牲什么 | "调大缓冲池减少磁盘 IO，但要预留 OS 与连接内存" |

| 风险 | 改错会怎样 | "改错缓冲池大小不会崩，但会命中率下降" |

| 层级 | 全局级还是会话级 | "sort_buffer 是会话级，每连接都会分配" |

判断一个参数是全局级还是会话级，是面试里区分"用过没"的细节。全局级参数影响整个实例，会话级参数只影响当前连接，多个会话叠加起来可能把内存撑爆。

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

记住：凡是带"buffer"且没写"global"的，多半是会话级，面试时要警惕乘法效应。

2 内存相关参数速查

内存参数是面试最高频的一类，核心思想是"尽量把热点数据放在内存里"。下表列出 8.0 中最常被追问的内存参数，按"全局大头"到"会话小块"排列。

| 参数名 | 默认值 | 作用 | 调优建议 |

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

下面这条命令可以一次性看缓冲池命中率，面试常被要求现场估算：

-- 查看 InnoDB 缓冲池命中率（本示例基于 MySQL 8.0）
-- 命中率 = 1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests
SHOW ENGINE INNODB STATUS;
-- 或用状态变量直接算：
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests 是逻辑读请求次数
-- Innodb_buffer_pool_reads 是落到物理磁盘读的次数，两者相减即命中率

估算话术：如果 read_requests 是 1 亿、reads 是 10 万，命中率就是 99.9%，说明缓冲池够用；如果 reads 占比超过 1%，就要考虑加缓冲池或优化热点。

3 日志相关参数速查

日志参数决定"性能 vs 安全"的权衡，是面试必问。核心矛盾是：redo 写得越频繁越安全但越慢，写得越粗越快但崩溃丢数据越多。binlog 与 redo 是两套独立日志，刷盘策略要分别看。

| 参数名 | 默认值 | 作用 | 调优建议 |

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

"双 1"是面试高频词，指的就是 innodb_flush_log_at_trx_commit=1 与 sync_binlog=1。两个都等于 1 时，每次事务提交既刷 redo 又刷 binlog，宕机最多丢一个事务的毫秒级数据。

| flush_log_at_trx_commit | 行为 | 宕机丢数据 |

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

| 0 | 每秒刷 redo，提交不刷 | 丢 1 秒事务 |

| 1 | 每次提交刷 redo | 基本不丢 |

| 2 | 提交只写 OS cache，每秒刷盘 | OS 崩丢 1 秒，MySQL 崩不丢 |

4 IO与刷盘参数速查

InnoDB 刷盘策略直接影响写入吞吐与崩溃恢复时间。面试常问"为什么你的数据库写不上去了"，很多时候就是刷盘参数没配对，或者脏页积压导致 checkpoint 卡顿。

| 参数名 | 默认值 | 作用 | 调优建议 |

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

脏页积压的连锁反应：脏页比例超过 max_dirty_pages_pct 后，用户线程会被拉住帮忙刷脏，表现就是"平时很快，突然卡一下"。把 max_dirty_pages_pct 调到 50~75 并开启低水位，可以把卡顿摊平。

5 连接与并发参数速查

连接参数决定"最多能接多少客户端"以及"连不上怎么办"。面试常考 too many connections 怎么处理，以及短连接风暴怎么缓解。

| 参数名 | 默认值 | 作用 | 调优建议 |

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

下面这段 Python 用 pymysql 连库后查看当前连接数，仅作示范（本机无 MySQL 服务，需替换为真实地址）：

# -*- coding: utf-8 -*-
# 查看 MySQL 连接数与最大连接数（本示例基于 MySQL 8.0，需真实可连的库）
import pymysql  # 导入数据库驱动

conn = pymysql.connect(
    host="127.0.0.1", user="root", password="your_password",
    database="test", charset="utf8mb4"
)
cur = conn.cursor()
cur.execute("SHOW STATUS LIKE 'Threads_connected'")   # 当前已建立连接
print("当前连接数:", cur.fetchone())
cur.execute("SHOW VARIABLES LIKE 'max_connections'")   # 最大连接数
print("最大连接数:", cur.fetchone())
cur.execute("SHOW STATUS LIKE 'Threads_created'")    # 历史创建过的线程总数
print("累计创建线程:", cur.fetchone())
cur.close()
conn.close()

判断连接是不是瓶颈，除了看 Threads_connected 是否逼近 max_connections，还要看 Threads_created 增长速度：如果它一直涨，说明短连接太多，线程缓存没复用，要调大 thread_cache_size。

6 InnoDB核心参数速查

除了内存与日志，InnoDB 还有一批"行为类"参数，决定了锁、隔离、压缩等核心行为。这些参数面试常与"锁"和"事务"联动追问。

| 参数名 | 默认值 | 作用 | 调优建议 |

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

自增锁模式 2（interleaved）是 8.0 默认，意思是多条 INSERT 在拿到自增值后立即释放，不锁到事务结束，并发插入吞吐最高。面试问"为什么我的自增 ID 不连续了"，答案就是模式 2 下并发插入会出现空洞。

7 复制与主从参数速查

主从复制参数决定"从库追不追得上主库"以及"切换时丢不丢数据"。8.0 默认开启基于 GTID 的复制，这是和 5.7 的一大区别。

| 参数名 | 默认值 | 作用 | 调优建议 |

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

主从延迟怎么定位：先看 Seconds_Behind_Master，再看 Relay_Master_Log_File 与主库当前 binlog 的差距，最后看 IO 线程与 SQL 线程谁堵了。并行回放没开、大事务、从库单线程是三大常见原因。

8 常见调优组合建议

面试经常被问"给你一台 32C64G 的库，你怎么配"。下面给出一个通用起步配置表，按物理内存规模给方向，不是标准答案，重点是让你说出权衡。

| 场景 | 缓冲池 | redo log | 连接数 | 刷盘策略 |

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

| 16G 内存小库 | 8G | 512M | 300 | 双 1，io_capacity 2000 |

| 64G 内存常规业务 | 40G | 2G | 1000 | 双 1，io_capacity 4000 |

| 128G 内存大库 | 80G | 4G | 2000 | 双 1，io_capacity 8000 |

| 写多读少日志库 | 32G | 4G | 500 | 可调 2+非双 1，换吞吐 |

| 读多写少查询库 | 90G | 512M | 2000 | 双 1，重点优化 IO 读 |

| 测试开发环境 | 2G | 128M | 100 | 关闭双 1 提速 |

调优的黄金顺序：先定位瓶颈（CPU / IO / 锁 / 连接），再动对应参数；不要一上来就堆内存。任何参数调整都要先在测试环境压测，再灰度上线。

定位瓶颈的常用视图：

-- 按维度定位瓶颈（本示例基于 MySQL 8.0）
SHOW GLOBAL STATUS LIKE 'Threads_running';       -- 活跃线程数，持续高说明 CPU 瓶颈
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';     -- 行锁等待情况
SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';     -- 物理读，判断 IO 瓶颈
SHOW ENGINE INNODB STATUS;                        -- 看历史死锁与 buffer pool 命中率

改完参数后不要直接上生产，要在测试环境压测对照。下面这张清单是每次调参后必须核对的指标。

| 验证维度 | 压测时观察 | 期望结果 |

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

| 吞吐 | QPS / TPS 是否提升 | 改写入参数应看到 TPS 上涨 |

| 延迟 | P99 响应时间 | 不能因为提吞吐把延迟拉高 |

| 稳定性 | 跑 1 小时是否有毛刺 | 无周期性卡顿 |

| 内存 | RSS 是否稳定 | 没有持续增长导致 OOM |

| 复制 | 从库延迟是否变大 | 改主库参数后从库不能掉队 |

| 崩溃恢复 | kill -9 后能否正常启动 | 双 1 配置下不丢已提交事务 |

| 磁盘 | 磁盘空间是否涨速异常 | binlog/redo 没爆 |

压测工具常用 sysbench：sysbench oltp_read_write --mysql-db=test run。压测时要把预热时间设够，前几分钟缓冲池还没热，数据不算数。

9.1 调参优先级路线图

面试常问"你调参先动哪个"。答案不是背参数表，而是按"收益从大到小"排序：

| 优先级 | 先做什么 | 为什么 |

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

| 第一 | 先优化 SQL 与索引 | 一个烂 SQL 能拖垮整库，参数救不回来 |

| 第二 | 调缓冲池大小 | 决定命中率，收益最大 |

| 第三 | 调双 1 与 redo 大小 | 决定写入性能与安全的平衡 |

| 第四 | 调连接与超时 | 解决连接打满类问题 |

| 第五 | 调刷脏与 IO 容量 | 解决高写入下的卡顿 |

| 第六 | 调并行回放 | 解决主从延迟 |

记住一句话：参数是放大器，不是救命稻草。索引烂、SQL 烂，再怎么调参数都是治标不治本。

9 查看与修改变量命令

面试可能让你现场写几条命令查参数。下面给出最常用的三组命令，记住 SHOW VARIABLES 查配置、SHOW STATUS 查计数器这个区别即可。

-- 查看参数值（本示例基于 MySQL 8.0）
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';          -- 查单个参数
SHOW VARIABLES LIKE 'innodb_log%';                      -- 模糊查一批
SHOW GLOBAL VARIABLES LIKE 'max_connections';           -- 查全局生效值
-- 查看运行时状态（计数器）
SHOW STATUS LIKE 'Threads%';                            -- 线程相关状态
SHOW ENGINE INNODB STATUS;                               -- InnoDB 内部状态

修改参数分"动态生效"和"需重启"两类，面试要能区分：

-- 动态参数：在线改，立即生效，但重启后丢失
SET GLOBAL innodb_flush_log_at_trx_commit = 2;          -- 运行时临时调
-- 持久化到系统变量表：重启后仍生效（8.0 新特性）
SET PERSIST max_connections = 1000;                     -- 写入 mysqld-auto.cnf
-- 注意：innodb_buffer_pool_size 8.0 支持在线调，但仍建议重启窗口操作

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

| SET GLOBAL | 是 | 否 | 临时应急、灰度验证 |

| SET PERSIST | 是 | 是（8.0） | 正式调整推荐 |

| 改 my.cnf | 否 | 是 | 需重启的参数 |

| SET PERSIST_ONLY | 否 | 是 | 不能动态改的参数 |

SET PERSIST 是 8.0 的重要改进：以前改持久化参数必须手动编辑 my.cnf 再重启，容易改错；现在一条命令同时在线生效并落盘，重启不丢。

10 状态计数器速查

光会调参数不够，面试官还会问"你怎么知道瓶颈在哪"。这就要靠 SHOW STATUS 的计数器。下面这张表列出最常被用来判断瓶颈的状态变量。

| 状态变量 | 含义 | 怎么用它判断 |

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

| Threads_connected | 当前连接数 | 逼近 max_connections 就要扩容或限流 |

| Threads_running | 当前活跃（非 sleep）线程数 | 持续 > 内核数说明 CPU 打满 |

| Threads_created | 历史累计创建线程数 | 一直涨说明短连接多，要调 thread_cache |

| Innodb_buffer_pool_reads | 缓冲池未命中落到磁盘的次数 | 算命中率的分母 |

| Innodb_buffer_pool_read_requests | 逻辑读总次数 | 算命中率的分子 |

| Innodb_row_lock_waits | 行锁等待次数 | 持续涨说明锁竞争严重 |

| Innodb_row_lock_time_avg | 行锁平均等待时间 | 超过 100ms 就要排查热点行 |

| Innodb_deadlocks | 死锁次数 | 不为 0 就要梳理事务顺序 |

| Innodb_data_reads | 数据文件物理读次数 | 高说明 IO 读瓶颈，缓冲池不够 |

| Innodb_data_writes | 数据文件物理写次数 | 判断写入压力 |

| Slow_queries | 慢查询累计数 | 配合慢日志优化 |

| Created_tmp_disk_tables | 落到磁盘的临时表数 | 多说明 tmp_table_size 太小 |

| Created_tmp_tables | 内存临时表数 | 对比前者比例 |

| Handler_read_rnd_next | 全表扫描读行次数 | 很高说明缺索引 |

| Connections | 历史连接总数 | 配合 aborted 看连接异常 |

| Aborted_connects | 连接失败次数 | 高说明网络或密码问题 |

-- 一次把关键计数器拉出来对比（本示例基于 MySQL 8.0）
SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected','Threads_running','Threads_created',
  'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests',
  'Innodb_row_lock_waits','Innodb_deadlocks','Slow_queries',
  'Created_tmp_disk_tables','Handler_read_rnd_next'
);
-- 观察计数器要隔一段时间采两次，算差值才有意义，单点值没意义

计数器的正确用法是"两次采样做差"：比如 10 秒内 Innodb_rows_inserted 涨了多少，才是真实写入 QPS。直接看累计值会被冷启动历史污染。

11 参数速记口诀

把高频参数编成口诀，面试前过一遍，比零散背好记。

| 主题 | 口诀 | 对应参数 |

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

| 内存大头 | 缓冲池占六七成 | innodb_buffer_pool_size |

| 安全双 1 | redo 刷一次，binlog 刷一次 | flush_log_at_trx_commit + sync_binlog |

| 写性能 | redo 做大，脏页放低 | log_file_size + max_dirty_pages_pct |

| 连接 | 别让短连接打爆 | wait_timeout + thread_cache |

| 从库追 | 并行回放开起来 | slave_parallel_workers + LOGICAL_CLOCK |

| 临时表 | 超了就落盘 | tmp_table_size + max_heap_table_size |

| 排序 | 别盲目调大 | sort_buffer_size 每连接 |

| 刷脏 | SSD 不刷邻居 | flush_neighbors=0 |

面试中如果记不住精确值，用"量级 + 权衡"回答也能得分：例如"缓冲池大概是物理内存的一半多一点，再大就要给系统留余地"。

12 注意事项与常见坑

参数调优最容易踩的坑不是"参数记错"，而是"方向搞反"或"忽略乘法效应"。下面这张表把最高频的坑列出来。

| 常见坑 | 错误做法 | 正确认知 |

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

| 缓冲池越大越好 | 调到物理内存 95% | 要预留 OS、连接、临时表内存，否则 swap |

| sort_buffer_size 调很大 | 一次调到 16M | 它是每连接分配，乘以连接数会爆内存 |

| sync_binlog=0 提升性能 | 线上直接关双 1 | 宕机丢数据，只允许日志类可丢场景 |

| tmp_table_size 越大越好 | 调到 1G | 内存临时表占用每连接，放大内存压力 |

| 改完参数就上线 | 直接改生产 my.cnf | 先压测，再 SET PERSIST，最后复盘 |

| 忽略 binlog 过期 | 保持 30 天默认 | 磁盘写满会导致库 crash |

| innodb_log_file_size 随便改 | 在线改大不重启 | 8.0 前需干净关闭，否则启动失败 |

| slave_skip_errors 兜底 | 跳过错继续同步 | 主从数据分叉，后面越追越偏 |

| max_connections 无限调大 | 加到一万 | 连接多了上下文切换反而拖慢，靠连接池 |

| read_only 当强约束 | 只开 read_only | super 用户可写，要用 super_read_only |

13 自测练习

自测 1：innodb_flush_log_at_trx_commit=1、sync_binlog=1、innodb_buffer_pool_size 分别解决什么问题？

innodb_flush_log_at_trx_commit=1：每次事务提交把 redo log 刷盘，保证 crash 后已提交事务不丢。
sync_binlog=1：每次提交把 binlog 刷盘，保证主从切换时 binlog 不丢，配合双 1 实现持久化。
innodb_buffer_pool_size：缓存热点数据与索引，减少磁盘读，是 MySQL 性能第一参数。

</details>

自测 2：线上突然报 Too many connections，你按什么顺序处理？

1. 先 SHOW PROCESSLIST 看连接来自哪里、是不是慢查询占着不放；

2. 杀个别异常连接释放资源；

3. 评估 max_connections 是否真不够，临时 SET GLOBAL 调大；

4. 根因排查：应用连接池泄漏、慢查询、短连接风暴；

5. 长期靠连接池与限流，而不是无限调大 max_connections。

</details>

自测 3：为什么 sort_buffer_size 不能盲目调大？

因为它是"每个连接、每次排序"都会分配的会话级内存。max_connections=1000、sort_buffer_size=16M，理论峰值就是 16G，直接把内存打爆。正确做法是配合慢查询优化，让排序走索引，减少 sort_buffer 的使用频率。

</details>

自测 4：SET GLOBAL 和 SET PERSIST 有什么区别？为什么 8.0 推荐后者？

SET GLOBAL 立即生效但重启丢失，SET PERSIST 立即生效且写入 mysqld-auto.cnf，重启后自动恢复。8.0 推荐后者是因为它把"在线调参"和"持久化"合成一步，避免了改 my.cnf 漏改或改错的风险，同时不需要重启。

</details>

自测 5：缓冲池命中率怎么算？低于多少要警惕？

命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests。生产健康值应在 99% 以上。如果命中率掉到 99% 以下，说明缓冲池装不下热点数据，要考虑加大缓冲池，或者把热点表/索引做拆分，或者加只读从库分担读压力。注意：命中率是两次采样做差后再算，不是拿累计值直接算。

</details>

自测 6：线上写入突然变慢，你怀疑是刷脏页问题，会看哪些参数与计数器？

看 innodb_max_dirty_pages_pct 是否被打满，超过后用户线程被迫刷脏；
看 innodb_io_capacity 是否太低，SSD 没吃满带宽；
看 Innodb_buffer_pool_wait_free，它不为 0 说明空闲页不够，用户在等刷脏；
看 Innodb_os_log_pending_writes，redo 写是否堆积；
处理方向：提高 io_capacity、降低脏页水位、开自适应刷脏、检查磁盘是否到瓶颈。

</details>

自测 7：从库追不上主库，除了并行回放参数，还可能是哪些原因？

大事务：主库一个事务改了 100 万行，从库只能单线程回放这一个事务，并行也没用；
从库没有和主库同规格的硬件，IO 或 CPU 更弱；
从库还承担了大量查询，回放线程被抢占资源；
二级索引过多，从库维护索引代价大；
网络带宽打满，binlog 传不过来；
没开 log_slave_updates 的级联场景下，中间层阻塞。

</details>

自测 8：为什么 8.0 推荐 SET PERSIST 而不是直接改 my.cnf？

SET PERSIST 在线生效，不用重启就能验证效果；
它会自动写到 mysqld-auto.cnf，重启后自动加载，不会漏；
改 my.cnf 容易手滑写错参数名或漏逗号，重启才发现，故障窗口大；
但需要重启才能生效的参数（比如 buffer_pool 8.0 前），还是得改 my.cnf。

</details>

自测 9：buffer pool 命中率高，但查询还是慢，可能是什么原因？

命中率高只说明"数据在内存里"，不代表"查得快"。可能原因：

SQL 没走索引，在内存里做全表扫描，逻辑读次数巨大；
返回行数太多，网络传输与回表开销大；
锁等待，数据在内存但拿不到锁；
排序分组占用大量临时空间；
Buffer Pool 里都是冷数据，热点页被挤出。

</details>

练习 1：用你自己的话写出"双 1"是哪两个参数，各自的 0/1/2 含义，并说明金融业务为什么必须保持双 1。

练习 2：假设你有一台 32C128G 的 MySQL 服务器，正在跑一个高并发交易库，请给出缓冲池、redo log、连接数、双 1 的建议值，并说明你的理由。

练习 3：线上从库延迟突然从 0 涨到 300 秒，列出你会按什么顺序排查哪些参数与状态。

本篇速查表覆盖了面试 90% 以上的参数题。复习时建议遮住"调优建议"列，看着参数名自己说方向，说不出来的就是你下一轮要补的点。

12.1 参数速查补充

| 参数 | 作用 |

|---|---|

| innodb_numa_interleave | NUMA 绑核 |

| innodb_buffer_pool_chunk_size | 缓冲池 chunk |

| innodb_redo_log_capacity | 8.0 重做日志容量 |

| innodb_temp_data_file_path | 临时表空间 |

下一篇预告：第 82 章速查卡：SQL高频写法

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 81 章速查卡：参数与调优

目录

1 本篇使用说明

2 内存相关参数速查

3 日志相关参数速查

4 IO与刷盘参数速查

5 连接与并发参数速查

6 InnoDB核心参数速查

7 复制与主从参数速查

8 常见调优组合建议

9.1 调参优先级路线图

9 查看与修改变量命令

10 状态计数器速查

11 参数速记口诀

12 注意事项与常见坑

13 自测练习

12.1 参数速查补充

文章推广海报

文章回复

第 81 章 速查卡：参数与调优

目录

1 本篇使用说明

2 内存相关参数速查

3 日志相关参数速查

4 IO与刷盘参数速查

5 连接与并发参数速查

6 InnoDB核心参数速查

7 复制与主从参数速查

8 常见调优组合建议

9.1 调参优先级路线图

9 查看与修改变量命令

10 状态计数器速查

11 参数速记口诀

12 注意事项与常见坑

13 自测练习

12.1 参数速查补充

第 81 章速查卡：参数与调优