Appearance

MySQL 监控与故障排查 ​

生产排障要先分类，再取证，最后变更。不要看到 CPU 高就重启，也不要看到慢 SQL 就立刻加索引。

四层观测模型 ​

层次重点指标常见问题
业务层错误率、P95/P99、超时、吞吐某接口或租户放大数据库压力
数据库层QPS/TPS、连接、锁、Buffer Pool、临时表慢 SQL、连接耗尽、锁等待
复制恢复层复制延迟、relay log、binlog、备份状态写后读不一致、容灾失效
主机层CPU、内存、磁盘延迟/IOPS、空间、网络资源饱和、抖动、磁盘满

先看实时现场 ​

sql
show full processlist;
show global status like 'Threads%';
show global status like 'Connections';
show global status like 'Aborted_connects';
show engine innodb status;

Threads_connected 是连接数，Threads_running 更接近正在运行而非休眠的线程。连接很多但 running 很少可能是连接池规模过大；running 持续高通常意味着 CPU、IO 或锁争用。

慢 SQL 证据链 ​

  1. 从 APM 或慢日志确认 SQL 指纹、频率、P95/P99、Rows_examined 与 Rows_sent。
  2. 使用真实且有代表性的参数查看执行计划。
  3. 在安全环境执行 EXPLAIN ANALYZE，比较预估行数与实际行数。
  4. 检查索引、统计信息、数据倾斜、排序、临时表、回表和锁等待。
  5. 优化后比较端到端指标，同时评估写入与空间成本。
sql
explain analyze
select id, amount
from orders
where user_id = ?
order by created_at desc
limit 20;

EXPLAIN ANALYZE 会真实执行查询，生产使用前必须确认语句只读、结果规模和资源风险。

Performance Schema 常用查询 ​

按总耗时找 SQL 指纹：

sql
select digest_text, count_star,
       round(sum_timer_wait / 1000000000000, 2) as total_seconds,
       sum_rows_examined, sum_rows_sent
from performance_schema.events_statements_summary_by_digest
where schema_name = 'app_db'
order by sum_timer_wait desc
limit 20;

查看数据锁与等待：

sql
select * from performance_schema.data_lock_waits;
select * from performance_schema.data_locks;

不同小版本的字段和 sys 视图会有变化，现场以目标版本为准。不要在事故中执行没有限制的大范围诊断查询。

常见故障剧本 ​

CPU 突然升高 ​

检查业务流量是否增加、活跃线程、Top SQL 指纹、执行计划变化和统计信息。常见原因是全表扫描、排序聚合、计划退化、批任务或流量突增。先限流或暂停异常任务，再处理 SQL；盲目扩连接会继续放大 CPU 竞争。

IO 延迟升高 ​

检查 Buffer Pool 命中、脏页刷盘、Redo 压力、大扫描、备份和 DDL 是否同时运行。区分读 IO 与写 IO，并查看磁盘队列和 fsync 延迟。缓存命中率高也可能因写入刷盘而 IO 高。

连接耗尽 ​

检查连接池泄漏、事务未关闭、慢查询堆积、锁等待和下游重试风暴。短期可限流、终止确认安全的异常会话；长期应设置连接池上限、获取超时、SQL 超时和熔断，不能只调大 max_connections。

锁等待或死锁 ​

先找到等待者、阻塞者、SQL、事务开始时间和锁对象。死锁会由 InnoDB 选择牺牲者回滚，应用应对可重试事务做有界退避重试。根因通常是访问顺序不一致、事务过长、范围过大或缺索引。

磁盘空间不足 ​

区分数据文件、binlog、relay log、临时文件、慢日志和备份占用。不要直接删除正在使用的数据库文件或 binlog；用受支持的保留策略和 purge 命令，先确认副本与恢复需求。空间满可能使写入停止甚至影响恢复。

延迟忽高忽低 ​

看 P99 而不只看平均值，并关联 GC、批任务、Checkpoint/刷脏、磁盘抖动、锁等待、连接建立和计划变化。抖动问题必须用时间线相关性分析。

长事务检查 ​

sql
select trx_id, trx_started, trx_state,
       trx_rows_locked, trx_rows_modified, trx_query
from information_schema.innodb_trx
order by trx_started;

长事务会长期持锁、阻碍 purge、扩大 undo 和失败回滚成本。常见根因是连接池自动提交配置错误、应用异常路径未结束事务、事务中调用远程接口或大批量处理。

变更前后的验证 ​

任何优化都至少比较：

  • 业务 P95/P99 和错误率。
  • SQL 调用频率、耗时、扫描行数、返回行数。
  • CPU、IO、Buffer Pool 和临时表。
  • 锁等待、事务时长和复制延迟。
  • 新索引空间及写入耗时。

面试标准回答：线上 MySQL 变慢怎么排查 ​

text
先确认影响范围和时间线，判断是单 SQL、全实例还是外部资源问题；再关联业务流量、错误率、P99、主机 CPU/IO/磁盘和数据库连接、活跃线程、锁、复制延迟。单 SQL 用慢日志与 Performance Schema 找指纹，用真实参数查看 EXPLAIN/EXPLAIN ANALYZE，核对扫描量、回表、排序、临时表和统计信息。锁问题还原阻塞链，资源问题检查批任务、DDL、备份和刷盘。先止损再根治，所有调整用前后指标验证并保留回滚方案。