第 20 章 参数调优与性能监控面试精讲

📖 本篇简介：参数调优与性能监控是 DBA/高级开发的日常，也是面试中考察"实战经验"的重头戏。本篇基于 MySQL 8.0，系统讲解关键参数（innodb_buffer_pool_size、innodb_log_file_size、max_connections、sync_binlog、innodb_flush_log_at_trx_commit、tmp_table_size、sort_buffer_size 等）的含义与取舍、监控指标（QPS/TPS、连接数、缓冲池命中率、慢查询、主从延迟）、Performance Schema、Sys Schema、慢查询分析、CPU/IO/内存排查思路。学完后你能拿到一台 MySQL 知道先调哪几个参数、监控哪些指标、看到性能曲线波动怎么定位是 CPU 还是 IO 瓶颈。

目录

• 题目 1：MySQL 参数调优的整体思路，先调哪几个
• 题目 2：innodb_buffer_pool_size 怎么设
• 题目 3：innodb_log_file_size 怎么设，太大太小有什么问题
• 题目 4：max_connections 怎么设，连接数打满怎么办
• 题目 5：sync_binlog 与 innodb_flush_log_at_trx_commit 双 1 配置
• 题目 6：tmp_table_size 与 max_heap_table_size
• 题目 7：sort_buffer_size 与 join_buffer_size 怎么设
• 题目 8：监控 MySQL 要监控哪些核心指标
• 题目 9：缓冲池命中率怎么算，低了怎么办
• 题目 10：QPS/TPS 怎么看，突然升高说明什么
• 题目 11：慢查询日志怎么分析
• 题目 12：CPU 打满怎么排查
• 题目 13：IO 打满怎么排查
• 题目 14：连接数打满怎么排查
• 题目 15：Performance Schema 是什么，怎么用
• 题目 16：Sys Schema 比 Performance Schema 好在哪
• 题目 17：innodb_flush_method 选 O_DIRECT 还是 fdatasync
• 题目 18：binlog_cache_size 与临时表磁盘
• 题目 19：关键参数调优后怎么验证效果
• 题目 20：常见坑与注意事项
• 速查表：关键参数速记
• 复习清单
• 练习题

题目 1：MySQL 参数调优的整体思路，先调哪几个

考点

• 不要盲目调参
• 先监控再调
• 核心几个参数

参考答案

1. 原则：不要看网上博客乱调，先监控找到瓶颈，再针对性调。

2. 第一批（80% 收益）：

• innodb_buffer_pool_size：缓冲池，最重要；
• innodb_log_file_size：redo log；
• max_connections：连接数；
• innodb_flush_log_at_trx_commit + sync_binlog：双 1。

3. 第二批：sort_buffer、join_buffer、tmp_table_size。

4. 第三批：线程缓存、表缓存、binlog 相关。

追问

• 追问：参数调优能解决一切慢 SQL 吗？应对：不能，SQL/索引/架构才是大头；参数调优是锦上添花。
• 追问：上线前要调参数吗？应对：用云厂商默认参数，业务稳定后再根据监控调。

题目 2：innodb_buffer_pool_size 怎么设

考点

• InnoDB 缓冲池
• 占物理内存 50%~70%
• 缓存数据页与索引页

参考答案

1. 作用：缓存 InnoDB 数据页与索引页，避免每次读磁盘，是 MySQL 性能最关键参数。

2. 设置：

• 专用 DB 服务器：物理内存的 50%~70%；
• 128GB 内存：设 80GB；
• 不要超过物理内存 80%，留内存给 OS/连接/临时表。

3. 查看：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 查看命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

追问

• 追问：buffer pool 设太大有问题吗？应对：太大 OS swap，性能骤降；留够内存给系统。
• 追问：8.0 支持 online 调 buffer pool 吗？应对：支持，SET GLOBAL innodb_buffer_pool_size=xxx，在线 resize。

题目 3：innodb_log_file_size 怎么设，太大太小有什么问题

考点

• redo log 大小
• 影响崩溃恢复与刷盘
• 8.0 动态调整

参考答案

1. 作用：redo log 大小，决定崩溃恢复时间与刷脏页频率。

2. 太小：刷脏页频繁，IO 压力大，性能差。

3. 太大：崩溃恢复慢（重启要重放更多 redo）。

4. 经验值：1~4GB，高写入业务 4~8GB。

5. 8.0：支持在线调整 innodb_log_file_size，不用重启。

追问

• 追问：redo log 与 binlog 区别？应对：redo InnoDB 层 crash safe，binlog Server 层复制/PITR。
• 追问：大 log buffer 能提升性能吗？应对：innodb_log_buffer_size 一般 16M 够，大了浪费。

题目 4：max_connections 怎么设，连接数打满怎么办

考点

• 最大连接数
• 每个连接占内存
• 打满报错 Too many connections

参考答案

-- 查看当前连接数与最大
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

1. 设置：一般 500~2000，每个连接占几百 KB~几 MB 内存，设太大内存爆。

2. 打满原因：

• 慢查询占着连接不释放；
• 连接池配置过大；
• 连接泄漏（应用没关连接）。

3. 处理：

• 找占用连接的 SQL 杀；
• 调大连接池上限；
• 用中间件（ProxySQL）复用连接。

追问

• 追问：max_connections 设 10000 行吗？应对：不行，每个连接线程栈+排序缓冲，10000 连接内存几十 GB；连接池复用更重要。
• 追问：Threads_running 高说明什么？应对：活跃执行中的连接多，SQL 慢或并发高。

题目 5：sync_binlog 与 innodb_flush_log_at_trx_commit 双 1 配置

考点

• 数据安全 vs 性能
• 双 1 最安全
• 业务取舍

参考答案

| innodb_flush_log_at_trx_commit | sync_binlog | 安全性 | 性能 |

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

| 1 | 1 | 最高（不丢数据） | 略差 |

| 2 | 1 | 中（OS 崩溃丢 1 秒） | 中 |

| 1 | 0 | 中 | 中 |

| 2 | 0 | 低（可能丢 binlog） | 最好 |

1. 双 1：两个参数都设 1，事务提交时 redo 与 binlog 都刷盘，宕机不丢已提交事务。

2. 代价：每次提交 fsync，写 QPS 受限。

3. 核心业务：双 1；非核心日志业务可放松。

# my.cnf 双 1 配置
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

追问

• 追问：双 1 性能差多少？应对：比双 0 低 20%~50%，取决于磁盘。
• 追问：SSD 下双 1 代价大吗？应对：SSD fsync 快，双 1 可接受。

题目 6：tmp_table_size 与 max_heap_table_size

考点

• 内存临时表上限
• 超了落盘
• GROUP BY/DISTINCT 用到

参考答案

1. 两个参数要一起设：MySQL 取两者较小值作为内存临时表上限。

2. 太小：GROUP BY/DISTINCT 临时表落盘（磁盘），慢。

3. 太大：并发高时内存爆。

4. 经验值：64M~256M。

SHOW STATUS LIKE 'Created_tmp_tables';      -- 内存临时表数
SHOW STATUS LIKE 'Created_tmp_disk_tables'; -- 磁盘临时表数
-- disk 与 total 比值大，说明要调大 tmp_table_size

追问

• 追问：临时表落盘怎么发现？应对：Created_tmp_disk_tables 占比高。
• 追问：临时表在磁盘哪里？应对：tmpdir 目录，默认 /tmp。

题目 7：sort_buffer_size 与 join_buffer_size 怎么设

考点

• 每连接独享
• 不能设太大
• filesort 与 BNL 用

参考答案

1. sort_buffer_size：filesort 用，每连接独享；设 256K~2M 够，太大并发爆内存。

2. join_buffer_size：JOIN 无索引时 BNL 用，1M~8M。

3. 原则：这类 per-connection buffer 不要设太大，连接数 × buffer = 内存峰值。

SHOW STATUS LIKE 'Sort_merge_passes';  -- 排序归并次数，大说明 sort_buffer 小

追问

• 追问：sort_buffer 设 16M 好不好？应对：不好，1000 连接就是 16GB；256K~2M 足够。
• 追问：join_buffer 大能解决缺索引吗？应对：缓解但不如加索引。

题目 8：监控 MySQL 要监控哪些核心指标

考点

• 性能指标
• 可用性
• 容量
• 主从

参考答案

| 类别 | 指标 |

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

| 性能 | QPS/TPS、慢查询数、缓冲池命中率 |

| 连接 | Threads_connected、Threads_running |

| 锁 | InnoDB_row_lock_waits、锁等待时间 |

| 主从 | IO/SQL 线程、Seconds_Behind_Source |

| 容量 | 磁盘使用率、数据文件大小、binlog 大小 |

| 错误 | 连接拒绝、复制中断、崩溃 |

追问

• 追问：用什么监控系统？应对：Prometheus + mysqld_exporter + Grafana。
• 追问：告警阈值怎么定？应对：慢查询 >10s 告警、连接数 >80%、主从延迟 >10s。

题目 9：缓冲池命中率怎么算，低了怎么办

考点

• 读请求从内存命中比例
• 99% 以上健康
• 低了加内存/优化查询

参考答案

-- 命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

1. 健康值：> 99%。

2. 低了怎么办：

• 调大 innodb_buffer_pool_size；
• 优化慢查询，减少全表扫；
• 加索引，让查询走覆盖索引。

追问

• 追问：命中率 99% 还慢为什么？应对：可能是 CPU/锁/网络瓶颈，不是 IO。
• 追问：命中率实时算吗？应对：从 MySQL 启动至今累计，要按窗口算。

题目 10：QPS/TPS 怎么看，突然升高说明什么

考点

• Com_select/Com_commit
• 突然升高原因
• 突降原因

参考答案

SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_commit';
-- QPS = Com_select 增量 / 秒
-- TPS = (Com_commit + Com_rollback) 增量 / 秒

1. 突然升高：业务流量激增、爬虫、慢查询打满连接。

2. 突然降为 0：MySQL 挂了、网络断、应用连不上。

3. 突降但 CPU 低：应用层问题，不是 MySQL。

追问

• 追问：QPS 高一定好事吗？应对：高 QPS 但慢查询多，是坏高 QPS；要看 RT。
• 追问：怎么实时看 QPS？应对：SHOW GLOBAL STATUS 两次差值算，或用监控系统。

题目 11：慢查询日志怎么分析

考点

• mysqldumpslow / pt-query-digest
• SQL 指纹聚合
• 找 TOP N

参考答案

# mysqldumpslow 按时间排序取前 10
mysqldumpslow -s t -t 10 slow.log

# pt-query-digest（推荐）
pt-query-digest slow.log | head -100

1. pt-query-digest 输出：

• 按 SQL 指纹聚合；
• 每条 SQL 的总耗时、次数、平均耗时；
• 找占总耗时最多的 TOP SQL 优先优化。

追问

• 追问：慢查询日志太多怎么处理？应对：logrotate 切割，pt-query-digest 自动分析。
• 追问：没走索引的 SQL 也要记录吗？应对：log_queries_not_using_indexes=ON。

题目 12：CPU 打满怎么排查

考点

• 看是用户态还是系统态
• 找 SQL
• 找锁/排序

参考答案

1. 第一步：top：看 mysqld 进程 CPU。

2. 第二步：看 CPU 是 user 还是 sys：

• user 高：SQL 计算多、排序、聚合；
• sys 高：上下文切换、锁。

3. 第三步：找当前慢 SQL：

   SHOW FULL PROCESSLIST;  -- 看 Time 长的 SQL
   -- 或
   SELECT * FROM performance_schema.threads;

4. 第四步：优化 TOP SQL / 加索引 / 杀长查询。

追问

• 追问：CPU 高但慢查询少？应对：可能是大量小查询高频，看 QPS。
• 追问：怎么看 MySQL 内部哪个线程耗 CPU？应对：performance_schema 或 perf top。

题目 13：IO 打满怎么排查

考点

• iostat 看 IO 利用率
• 找刷脏页/binlog
• 找全表扫

参考答案

1. iostat -x 1：看 %util、await。

2. 常见原因：

• 全表扫描多；
• 缓冲池小，缓存命中率低；
• redo/binlog 刷盘多；
• 大事务 undo。

3. 排查：

• 看慢查询日志；
• 调大 buffer pool；
• 检查刷脏页参数 innodb_io_capacity。

追问

• 追问：IO 高加内存有用吗？应对：有用，buffer pool 大缓存多，少读磁盘。
• 追问：SSD 还会 IO 打满吗？应对：会，但 SSD 比 SATA 快 10 倍。

题目 14：连接数打满怎么排查

考点

• Too many connections
• 找长连接
• 连接池泄漏

参考答案

1. 报错：ERROR 1040: Too many connections。

2. 排查：

   SHOW FULL PROCESSLIST;
   -- 按 user/host 分组看连接数
   SELECT USER, HOST, DB, COUNT(*) FROM information_schema.processlist GROUP BY USER, HOST, DB;

3. 常见原因：

• 连接池配置过大；
• 连接泄漏（应用没释放）；
• 慢查询占着连接。

4. 处理：杀长 sleep 连接、调连接池、加 max_connections。

追问

• 追问：sleep 连接多正常吗？应对：连接池复用正常；但太多 sleep 说明连接池开太大。
• 追问：wait_timeout 设多少？应对：8 小时默认太长，生产 300~600 秒。

题目 15：Performance Schema 是什么，怎么用

考点

• MySQL 自带性能监控
• 采集等待事件/语句
• 8.0 默认开启

参考答案

1. Performance Schema：MySQL 内置的运行时性能监控库，采集 SQL 执行、等待事件、锁、IO 等。

2. 常用查询：

-- 查最近执行慢的 SQL
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e9 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

-- 查表锁等待
SELECT * FROM performance_schema.data_lock_waits;

3. 8.0 默认开启，5.7 要手动开。

追问

• 追问：Performance Schema 影响性能吗？应对：5.7 影响大，8.0 优化后 <5%。
• 追问：Performance Schema 与 slow log 区别？应对：slow log 只记慢 SQL，PS 记所有 SQL 聚合。

题目 16：Sys Schema 比 Performance Schema 好在哪

考点

• PS 的视图封装
• 易用
• 8.0 默认带

参考答案

1. Sys Schema：把 Performance Schema 的复杂表封装成易读视图，DBA 友好。

2. 常用视图：

• sys.statement_analysis：TOP SQL；
• sys.innodb_lock_waits：锁等待；
• sys.host_summary：主机连接汇总；
• sys.schema_unused_indexes：未使用索引。

-- 查 TOP 10 慢 SQL
SELECT * FROM sys.statement_analysis LIMIT 10;
-- 查从未使用的索引
SELECT * FROM sys.schema_unused_indexes;

追问

• 追问：Sys Schema 8.0 默认有吗？应对：是，8.0 默认安装。
• 追问：未使用索引怎么处理？应对：先观察一周，确认没用再 DROP。

题目 17：innodb_flush_method 选 O_DIRECT 还是 fdatasync

考点

• O_DIRECT 绕过 OS 缓存
• fdatasync 走 OS 缓存
• Linux 推荐 O_DIRECT

参考答案

1. O_DIRECT：InnoDB 直接读写磁盘，绕过 OS page cache，避免双缓冲。

2. fdatasync：走 OS 缓存，OS 再刷盘。

3. Linux 生产推荐 O_DIRECT：避免 InnoDB buffer pool 与 OS page cache 重复缓存。

4. Windows：用 async_unbuffered。

innodb_flush_method = O_DIRECT

追问

• 追问：O_DIRECT 缺点？应对：没有 OS 缓存兜底，buffer pool 小了性能差。
• 追问：为什么不双缓冲？应对：内存浪费，OS page cache 与 buffer pool 缓存同样数据。

题目 18：binlog_cache_size 与临时表磁盘

考点

• 事务 binlog 缓存
• 超了写临时文件
• 大事务要调大

参考答案

1. 作用：事务未提交时 binlog 先写内存 binlog_cache，提交时刷盘。

2. 太小：大事务超 cache 写磁盘临时文件，慢。

3. 查看：

SHOW STATUS LIKE 'Binlog_cache_disk_use';  -- 用磁盘次数
SHOW STATUS LIKE 'Binlog_cache_use';        -- 用内存次数

4. 经验值：2M~8M，大事务业务调大。

追问

• 追问：Binlog_cache_disk_use 大于 0 要紧吗？应对：偶尔没事，持续涨说明要调大。
• 追问：binlog_cache 是每连接吗？应对：是，每个事务独享。

题目 19：关键参数调优后怎么验证效果

考点

• 不能凭感觉
• 对比压测
• 观察监控

参考答案

1. 压测：sysbench/tpcc 压调优前后对比 QPS/RT。

2. 监控：观察压测期间 CPU/IO/缓冲池命中率/慢查询。

3. 灰度：先一台机器调，观察一周再全量。

4. 回滚预案：调坏了能改回。

# sysbench 压测示例
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 \
  --tables=10 --table-size=1000000 --threads=32 --time=60 run

追问

• 追问：调参要一次调多个吗？应对：不要，一次调一个，观察效果，否则不知道哪个起作用。
• 追问：生产怎么安全调？应对：动态参数先 SESSION 再 GLOBAL，持久化到 my.cnf。

题目 20：常见坑与注意事项

考点

• 参数调优高频坑
• 监控盲区
• 经验主义

参考答案

1. 坑 1：照搬博客参数，不看自己硬件与业务。

2. 坑 2：per-connection buffer（sort/join）设太大，连接数高时内存爆。

3. 坑 3：buffer pool 设超过物理内存，触发 swap。

4. 坑 4：max_connections 设太大，连接内存爆。

5. 坑 5：不监控，问题爆了才发现。

6. 坑 6：慢查询日志没开，性能问题无从查起。

7. 坑 7：双 1 关了换性能，宕机丢数据。

8. 坑 8：tmp_table_size 设太小，GROUP BY 全落磁盘。

9. 坑 9：调参不验证，凭感觉。

10. 坑 10：以为参数调优能解决 SQL 慢，忽略索引与写法。

追问

• 追问：新手最容易调错哪个参数？应对：sort_buffer_size 设太大，并发时 OOM。
• 追问：参数调优的正确姿势？应对：监控找瓶颈 → 小步调整 → 压测验证 → 灰度上线。

速查表：关键参数速记

| 参数 | 推荐值 | 作用 |

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

| innodb_buffer_pool_size | 物理内存 50%~70% | 数据/索引缓存 |

| innodb_log_file_size | 1~4GB | redo log |

| max_connections | 500~2000 | 最大连接 |

| innodb_flush_log_at_trx_commit | 1 | redo 刷盘 |

| sync_binlog | 1 | binlog 刷盘 |

| tmp_table_size | 64M~256M | 内存临时表 |

| sort_buffer_size | 256K~2M | filesort |

| join_buffer_size | 1M~8M | BNL JOIN |

| innodb_flush_method | O_DIRECT | 绕过 OS 缓存 |

| binlog_cache_size | 2M~8M | 事务 binlog 缓存 |

| wait_timeout | 300~600s | 连接超时 |

复习清单

| 题目 | 核心结论 | 易错点 |

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

| 1 | 先监控再调，buffer pool 优先 | 照搬博客参数 |

| 2 | buffer pool 占内存 50%~70% | 设太大触发 swap |

| 3 | redo log 1~4GB | 太小刷盘频繁 |

| 4 | max_connections 500~2000 | 设 10000 OOM |

| 5 | 双 1 最安全 | 关双 1 换性能 |

| 6 | tmp_table_size 取与 heap 较小值 | 落盘临时表不监控 |

| 7 | per-connection buffer 别太大 | sort_buffer 设 16M |

| 8 | 性能/连接/锁/主从/容量 | 监控覆盖不全 |

| 9 | 缓冲池命中率 >99% | 命中率低加索引 |

| 10 | QPS/TPS 看增量 | 突降是应用问题 |

| 11 | pt-query-digest 找 TOP SQL | 慢日志不分析 |

| 12 | user 高看 SQL，sys 高看锁 | 不会用 processlist |

| 13 | 全表扫+buffer 小 IO 高 | 不看 iostat |

| 14 | 连接池泄漏打满 | sleep 连接多不处理 |

| 15 | PS 采集运行时性能 | 5.7 要手动开 |

| 16 | Sys Schema 视图封装 | 不查未使用索引 |

| 17 | O_DIRECT 避免双缓冲 | Windows 不能用 |

| 18 | binlog_cache 太小写磁盘 | 大事务要调大 |

| 19 | 压测对比+灰度上线 | 一次调多个参数 |

| 20 | per-connection buffer 别贪大 | 不监控不验证 |

练习题

1. 题目：一台 64GB 内存的 MySQL 服务器，innodb_buffer_pool_size 设多少合适？

• 参考答案：50%~70%，即 32GB~45GB；留内存给 OS、连接、临时表。

2. 题目：Too many connections 报错怎么排查？

• 参考答案：SHOW PROCESSLIST 看连接来源；找慢查询占连接、连接池泄漏；杀长 sleep、调大 max_connections、用 ProxySQL 复用连接。

3. 题目：双 1 配置指哪两个参数？为什么核心业务要开？

• 参考答案：innodb_flush_log_at_trx_commit=1 与 sync_binlog=1；宕机不丢已提交事务，保证数据安全。

4. 题目：缓冲池命中率 95%，但业务仍慢，可能原因？

• 参考答案：命中率不是唯一指标；可能 CPU 瓶颈（SQL 计算多）、锁等待、网络问题、或慢查询本身逻辑复杂。

5. 题目：一次调优同时改了 buffer pool 和 sort_buffer，怎么知道是哪个起作用？

• 参考答案：不应该一次调多个；正确做法是一次改一个，压测/观察验证效果，再调下一个。

下一篇预告：第 21 章 线上慢查询现场排查