03.8 阶段 7:性能诊断、参数调优与运维
所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL
本页目录
一、阶段定位
前六个阶段把"建库、写查询、加索引、做事务、搭复制、做分片"都讲了。本阶段解决线上日常:数据库突然慢了怎么定位、哪些参数该调、日常运维要做什么。这是 DBA 与后端工程师的分水岭。
判断自己是否卡在本阶段:
- 线上慢了只会"重启数据库",不知道从慢查询日志、
pg_stat_statements、锁等待哪里入手——这是没有诊断闭环。 - 背了一堆参数名,但不知道
innodb_buffer_pool_size该设多少、work_mem设大了会出事——这是调优凭感觉。 - 连接数设到 2000 以为能扛更高并发,结果吞吐反而下降——这是连接数与并发的关系没搞懂。
- PG 用了一年表越来越大,不知道要 VACUUM;MySQL undo 暴涨不知道长事务——这是运维盲区。
衔接关系:本阶段把前六阶段的知识点串成诊断闭环——慢查询用阶段 2 的 EXPLAIN、锁等待用阶段 3 的锁机制、复制延迟用阶段 5 的指标、膨胀用阶段 3 的 MVCC。不掌握这些前置,看到指标也不知道怎么解读。
二、阶段目标与投入
| 能力目标(可验证的行为) | 验证方式 |
|---|---|
| 能开启慢查询日志并从高频慢 SQL 定位问题 | MySQL slow log / PG pg_stat_statements |
能读懂 SHOW ENGINE INNODB STATUS 关键段 |
解释 TRANSACTIONS、LATEST DEADLOCK、BUFFER POOL |
| 能找到锁等待链与长事务源头 | data_lock_waits / pg_blocking_pids |
| 能按内存规格估算 buffer pool / shared_buffers | 给出 32GB 机器的经验配置 |
| 能说清连接数不是越大越好 | 解释连接切换成本与吞吐关系 |
| 能诊断 PG 表膨胀并触发 VACUUM | pg_stat_user_tables.n_dead_tup 下降 |
| 能列出核心监控指标清单 | 连接数、QPS、慢查询、复制延迟、缓存命中率 |
建议学时:
- 全职:1.5 周,约 35 小时。慢查询与统计视图 8 小时、锁/长事务 6 小时、内存参数 8 小时、连接池 4 小时、VACUUM/膨胀 5 小时、监控与在线 DDL 4 小时。
- 在职:3 周,约 40 小时。必须在有压测的环境练,不要只看文档。
学时分配:慢查询定位 25%、锁等待与长事务 20%、内存参数 20%、连接池 10%、VACUUM/undo 膨胀 15%、监控指标 10%。
三、先修自检
| 先修项 | 合格标准 | 不合格时回到 |
|---|---|---|
| 索引与 EXPLAIN | 能解读执行计划 | 03.3 阶段 2 |
| 事务与锁 | 能定位锁等待与死锁 | 03.4 阶段 3 |
| 日志与复制 | 知道 redo/binlog/WAL | 03.5 阶段 4 |
| 复制延迟 | 会看 Seconds_Behind | 03.6 阶段 5 |
| 操作系统 | 知道 CPU/内存/IO 查看 | 01.1 操作系统 |
经验判断:调优的铁律是"先量后调"。没有基线指标(QPS、RT、缓存命中率)就改参数,是玄学调参。
四、知识地图
| 模块 | 知识点 | 掌握程度 | 对应题库章节 |
|---|---|---|---|
| 慢查询 | slow log、long_query_time、pg_stat_statements | 会用 | 05.2 MySQL |
| 活动视图 | INNODB_TRX、pg_stat_activity | 能讲清原理 | 05.2 MySQL |
| 锁等待 | data_lock_waits、pg_blocking_pids | 会用 | 05.3 PostgreSQL |
| 内存参数 | buffer_pool、shared_buffers、work_mem | 能讲清原理 | 05.2 MySQL |
| 连接池 | max_connections、HikariCP、PgBouncer | 会用 | 05.1 关系型数据库通用能力 |
| 膨胀 | VACUUM、autovacuum、undo 回收 | 能讲清原理 | 05.3 PostgreSQL |
| 在线 DDL | pt-online-schema-change、pg_repack | 了解 | 05.2 MySQL |
| 监控 | 指标清单、Prometheus exporter | 会用 | 08.4 CI/CD 与可观测性 |
进度管理建议:每个指标都要知道"异常时去哪查、怎么修"。术语见 03.12 术语表。
五、分模块精讲
5.1 慢查询定位:slow log 与 pg_stat_statements
为什么需要它:数据库慢了,第一步不是猜,而是找到"哪条 SQL 慢、慢多少次、占多少时间"。MySQL 用慢查询日志,PG 用 pg_stat_statements 扩展(聚合所有慢 SQL)。
核心机制:
- MySQL 慢查询日志:
slow_query_log=ON、long_query_time(默认 10 秒,生产设 1 秒)。log_queries_not_using_indexes=ON记录没走索引的查询。- 用
mysqldumpslow或 pt-query-digest 聚合分析。
- PG pg_stat_statements:
- 编译时/安装扩展:
CREATE EXTENSION pg_stat_statements;。 - 记录每条 SQL 的调用次数、总时间、行读、行写,按 queryid 聚合(不绑定参数值)。
- 按
total_time/mean_time排序找高频慢 SQL。
- 编译时/安装扩展:
最小可运行示例:
-- MySQL
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 分析(命令行)
-- mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
-- PG
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT queryid, query, calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 1) AS mean_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 慢查询日志为空 | long_query_time 设太大 | 设 1 秒;确认开启 |
| pg_stat_statements 无数据 | 扩展未装 | CREATE EXTENSION;配 shared_preload_libraries |
| 日志文件暴涨 | 没聚合 | pt-query-digest;定期轮转 |
面试考法:"怎么定位慢查询""慢查询日志关键参数"。要点:slow log + long_query_time;PG 用 pg_stat_statements 聚合。
5.2 活动会话与锁等待
为什么需要它:慢查询之外,"数据库突然卡住"往往是锁等待或长事务。本模块学会一眼找到阻塞源头。
核心机制:
- MySQL:
information_schema.INNODB_TRX:当前所有事务。performance_schema.data_lock_waits:谁等谁、等什么锁。SHOW PROCESSLIST:所有连接。
- PG:
pg_stat_activity:当前会话、状态、时长、SQL。pg_locks WHERE NOT granted:未授予的锁。pg_blocking_pids(pid):返回阻塞该会话的 pid 数组。
最小可运行示例:
-- MySQL:找最长事务
SELECT trx_id, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS dur,
trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;
-- 锁等待链
SELECT * FROM performance_schema.data_lock_waits;
-- PG:找长事务
SELECT pid, now() - xact_start AS dur, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY dur DESC;
-- 谁阻塞了谁
SELECT pid, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
-- 杀会话(PG)
SELECT pg_terminate_backend(12345);
-- MySQL: KILL 1234;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
大量 idle in transaction |
应用拿了连接没提交 | 杀会话;应用修事务边界 |
| 锁等待找不到源头 | 只看等待方 | 用 data_lock_waits / pg_blocking_pids 反查 |
| 杀错会话 | 没确认 SQL | 先查 query 再 terminate |
面试考法:"线上锁等待怎么排查""长事务为什么危险"。要点:活动视图找源头;长事务阻碍 MVCC 回收、持锁。
5.3 内存参数:buffer pool 与 shared_buffers
为什么需要它:数据库性能 80% 靠内存缓存。参数设错,要么 OOM,要么命中率低。
核心机制:
- MySQL InnoDB:
innodb_buffer_pool_size:数据页与索引页缓存,经验值为物理内存的 50%–70%( dedicated 数据库机)。- 命中率:
Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests,应 > 99%。
- PostgreSQL:
shared_buffers:共享缓冲,经验值物理内存的 25%(不要设太大,PG 还依赖 OS page cache)。work_mem:每个排序/哈希操作的内存,默认 4MB;设大了并发时 N 个操作 × work_mem 可能 OOM。effective_cache_size:告诉优化器 OS 缓存多大,不实际分配,经验 50%–75%。
- 32GB 机器参考:MySQL buffer_pool 设 16–20GB;PG shared_buffers 设 8GB、effective_cache_size 设 24GB。
最小可运行示例:
-- MySQL 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - reads / read_requests
-- PG
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 命中率 < 95% | buffer pool 太小 | 调大;或查是否有大查询扫全表 |
| PG OOM | work_mem 设太大,并发排序 | 调小 work_mem(如 8–16MB) |
| shared_buffers 设 75% | PG 不推荐 | 降到 25%,交给 OS cache |
面试考法:"buffer pool 设多少""work_mem 为什么不能乱设"。要点:专用机 50–70%;work_mem 是每操作内存,并发放大。
5.4 连接数与连接池
为什么需要它:连接数不是越大越好。每个连接在数据库占内存,且操作系统线程切换有成本。设 2000 连接,实际吞吐反而比 200 低。
核心机制:
- MySQL
max_connections默认 151;调过大会占内存(每连接约 1MB 栈)。 - PG
max_connections默认 100;每连接是一个 OS 进程(不是线程),进程切换成本高,通常设到 500–1000,再大就用连接池。 - 连接池:
- JVM 用 HikariCP,池大小经验
((核心数 * 2) + 有效磁盘数)(PostgreSQL wiki),通常 10–50,不是几百。 - PG 用 PgBouncer 做事务级池化,几百应用连接复用几十个后端连接。
- JVM 用 HikariCP,池大小经验
- 池太小:请求排队;池太大:数据库被打爆。要压测找拐点。
最小可运行示例:
# HikariCP 参考
spring:
datasource:
hikari:
maximum-pool-size: 20
minimum-idle: 5
connection-timeout: 3000
-- PG 连接占用
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 应用报 "too many connections" | max_connections 不够 | 调大;或上连接池 |
| 连接数 1000 但 RT 升高 | 操作系统切换成本 | 降连接数;上 PgBouncer |
| 池满请求排队 | pool-size 太小 | 压测调大;查慢 SQL |
面试考法:"连接池设多大""为什么不用几百连接"。要点:连接有内存与切换成本;压测找拐点;PG 用 PgBouncer。
5.5 表膨胀与 VACUUM(PostgreSQL)
为什么需要它:PG 的 MVCC(阶段 3)UPDATE 产生死元组,不回收会导致表与索引膨胀、查询变慢。这是 PG 日常运维的核心。
核心机制:
- 死元组(dead tuple):UPDATE/DELETE 后旧版本,被 VACUUM 回收。
- autovacuum 后台进程自动触发,但默认参数对大表偏保守。
n_dead_tup与n_live_tup比例高说明膨胀。VACUUM(不锁表,标记可回收);VACUUM FULL(锁表重建,彻底回收但停写);pg_repack在线重建。- 长事务阻止 autovacuum 回收(阶段 3)。
最小可运行示例:
-- 查膨胀
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup * 100.0 / NULLIF(n_live_tup,0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
-- 手动 VACUUM 某表
VACUUM (VERBOSE, ANALYZE) orders;
-- 紧急重建(停写窗口)
VACUUM FULL orders;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 表体积 100GB 但 n_live_tup 只占 1GB | 膨胀严重 | VACUUM FULL / pg_repack |
| autovacuum 跑不完 | 大表 + 默认参数 | 调 autovacuum_vacuum_scale_factor |
| 长事务期间 VACUUM 不回收 | 快照持有 | 杀长事务 |
面试考法:"PG 为什么要 VACUUM""VACUUM 与 VACUUM FULL 区别"。要点:回收死元组;VACUUM 标记不锁表,FULL 重建停写。
5.6 在线 DDL 与 MDL 锁
为什么需要它:大表加索引、改列在高峰期直接锁表,会被 MDL(元数据锁)堵住,甚至把整个库堵死。本模块学会安全地做在线变更。
核心机制:
- MySQL DDL 在 5.6+ 有 Online DDL,但部分操作仍会短暂锁 MDL。
- MDL:DML 与 DDL 互斥。长事务持有 MDL,后续 DDL 排队,把所有新 DML 也堵死——这是"改个表把库搞挂"的经典事故。
- 工具:pt-online-schema-change(gh-ost)通过影子表 + 触发器/binlog 同步在线变更。
- 大表加索引:MySQL 8 支持 instant/inplace;仍建议低峰、用工具、先备份。
- PG:
CREATE INDEX CONCURRENTLY不锁表(但慢)。
最小可运行示例:
-- MySQL:查看 MDL 等待
SELECT * FROM performance_schema.metadata_locks;
-- PG:在线建索引
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);
-- MySQL:pt-online-schema-change(命令行)
-- pt-online-schema-change --alter "ADD INDEX idx_user(user_id)" \
-- D=shop,t=orders --execute
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 改表后所有查询卡住 | MDL 被长事务堵 | 杀长事务;低峰;用 gh-ost |
| 建索引锁表 | 普通 CREATE INDEX | PG 用 CONCURRENTLY;MySQL 用工具 |
| 大表 DDL 几小时 | 重建表 | 用 pt-osc/gh-ost;业务低峰 |
面试考法:"大表怎么加索引不锁表""MDL 事故怎么发生"。要点:长事务持 MDL 堵 DDL、堵 DML;用在线工具、低峰。
5.7 监控指标清单与版本升级
为什么需要它:没有监控就没有告警,故障只能靠用户投诉。本模块列一份生产必备指标清单。
核心机制:
- 核心指标:
- 容量:连接数、QPS、TPS、缓冲池命中率、磁盘使用率。
- 慢查询:慢查询数、平均 RT、top SQL。
- 复制:复制延迟(Seconds_Behind)。
- 锁:锁等待数、最长事务时长、死锁数。
- 膨胀(PG):n_dead_tup、autovacuum 运行。
- 采集:mysqld_exporter / postgres_exporter + Prometheus + Grafana。
- 版本升级:
- 先在测试库跑全量回归;查 release notes 不兼容项;逻辑复制跨版本可做滚动升级。
- PG 大版本升级(如 15→16)需 pg_dump 或 pg_upgrade;小版本滚动重启。
最小可运行示例:
监控告警建议阈值:
连接数 > 80% max_connections
慢查询数 > 10/分钟
复制延迟 > 10s
最长事务 > 30s
缓冲池命中率 < 99%
磁盘 > 80%
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 故障才知道 | 没监控/告警 | 部署 exporter + Grafana |
| 升级后功能异常 | 没测回归 | 测试库全量回归 |
| 磁盘满 | 没监控磁盘 | 告警 + 日志轮转 |
面试考法:"你管数据库监控哪些指标""版本升级注意什么"。要点:连接/QPS/慢查询/复制延迟/锁/膨胀;先测试、查不兼容、滚动。
六、分层练习
6.1 复现型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 开慢查询 | MySQL 设 long_query_time=1,PG 装 pg_stat_statements | 能查到 top 慢 SQL | 1 小时 |
| 找长事务 | 造一个 idle in transaction,用视图找到并杀掉 | 抄下会话与 SQL | 1 小时 |
| 锁等待复现 | 两会话互相锁,用 data_lock_waits / pg_blocking_pids 定位 | 找到阻塞链 | 1.5 小时 |
| 内存参数 | 对 32GB 机器写 MySQL/PG 配置 | buffer_pool/shared_buffers 有依据 | 1 小时 |
| 膨胀观察 | PG 批量 UPDATE,看 n_dead_tup 后 VACUUM | 死元组清零 | 1.5 小时 |
6.2 改造型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 修慢 SQL | 从 top 慢 SQL 选一条,加索引/改写 | RT 下降对比 | 2 小时 |
| 连接池调优 | 压测不同 pool-size,找拐点 | 记录吞吐曲线 | 2 小时 |
| 在线加索引 | 用 CONCURRENTLY 或 gh-ost | 不锁表 | 2 小时 |
| 杀长事务 | 写脚本定时查 >60s 事务并告警 | 能告警 | 1.5 小时 |
6.3 设计型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 监控大盘 | 列出 10 个核心指标与阈值 | 可交付给 Grafana | 2 小时 |
| 应急手册 | 写"数据库慢/锁等待/磁盘满"三类应急步骤 | 可执行 | 2 小时 |
| 升级方案 | 对一次小版本升级写步骤与回滚 | 含测试与回归 | 1.5 小时 |
七、排障表
| 现象 | 可能原因 | 排查步骤 | 解决办法 |
|---|---|---|---|
| 数据库整体慢 | 慢 SQL、锁等待、资源打满 | 看 pg_stat_statements / 负载 | 定位 top SQL、杀锁、扩容 |
| 连接被打满 | 连接池过大/泄漏 | pg_stat_activity 计数 |
调池;修泄漏;PgBouncer |
| 改表后全库卡住 | MDL 被长事务堵 | metadata_locks |
杀长事务;用在线工具 |
| PG 表越来越慢 | 表膨胀 | n_dead_tup 比例 |
VACUUM / pg_repack |
| 命中率低 | buffer 小/大查询扫表 | Innodb_buffer_pool_reads |
调大 buffer;优化 SQL |
| 复制延迟升高 | 大事务/从库慢 | Seconds_Behind |
拆批、并行复制(阶段 5) |
| OOM | work_mem/max_connections 过大 | 看内存日志 | 调小;压测 |
| 磁盘满 | 日志/binlog 未轮转 | 查磁盘 | 日志轮转;归档;清理 |
八、出口条件
全部勾选后再进入 阶段 8 两库差异、迁移与架构综合(可选)。
- [ ] 能开启慢查询并从 slow log / pg_stat_statements 定位 top 慢 SQL
- [ ] 能从活动视图找到长事务与锁等待源头并杀掉
- [ ] 能为 32GB 机器写出 buffer_pool/shared_buffers 经验配置
- [ ] 能解释连接数为什么不是越大越好
- [ ] 能诊断 PG 膨胀并触发 VACUUM
- [ ] 能说清 MDL 事故与在线 DDL 工具
- [ ] 能列出 10 个核心监控指标与告警阈值
自测题:
- 线上数据库突然变慢,你的排查顺序?
- 要点:看负载/资源 → 慢查询 top(pg_stat_statements)→ 锁等待/长事务 → 复制延迟 → 资源(磁盘/CPU)。
- MySQL buffer pool 与 PG shared_buffers 经验值各是多少?
- 要点:MySQL 专用机 50–70%;PG 25%(其余交给 OS cache)。
- work_mem 为什么不能设太大?
- 要点:每个排序/哈希操作独立分配,并发 N 个操作就放大 N 倍,可能 OOM。
- PG 的 VACUUM 与 VACUUM FULL 区别?
- 要点:VACUUM 标记可回收、不锁表;FULL 重建表、彻底回收但停写。
- 大表加索引怎么避免锁表?
- 要点:PG 用 CREATE INDEX CONCURRENTLY;MySQL 用 pt-osc/gh-ost;低峰执行。
- 连接池是不是越大越好?为什么?
- 要点:不是;每个连接占内存、进程切换成本高,压测找拐点(通常 10–50)。
- 长事务为什么是性能与膨胀的共同源头?
- 要点:持锁阻塞其他写;其 Read View 阻碍 undo/死元组回收,导致膨胀。