MySQL 排查总览与健康巡检

MySQL 故障的特点是可传染：单条缺索引的 SQL 占住一个连接，应用连接池随之被占满，客户端开始重试并放大流量，最后表现为“全站 502”。所以排查的第一原则是先看全局水位再看单条语句，先把连接与活跃线程压下去，再谈 SQL 优化。本章给出统一排查顺序、健康巡检清单与关键状态指标的阈值参考。

一条 SQL 如何演变成全站不可用

某条 SQL 因缺索引做全表扫描（执行 30 秒）
  → 该连接被占用 30 秒，其间还持有行锁或表锁
  → 应用连接池的活跃连接逐个被占满
  → 业务线程阻塞在 getConnection()，开始排队
  → 请求超时 → 客户端重试 → 流量翻倍
  → 更多连接涌入 MySQL，Threads_running 持续抬高
  → 所有 SQL 挤在 CPU 与锁上排队，连 SELECT 1 都变慢
  → 全站接口超时

数据库的“死”往往是从“慢”开始的，中间有一段可观测的缓冲期，通常几分钟。这段时间里 Threads_running 与活跃连接数会先抬头，抓住这个信号就能在崩盘前止血。

排查顺序（按此顺序，不要跳步）

  1. 连接与并发：Threads_connected、Threads_running、SHOW PROCESSLIST 里的长耗时语句。
  2. 当前活跃 SQL：按 TIME 倒序，看排在前面的是谁、什么状态。
  3. 慢查询：慢日志与 events_statements_summary_by_digest，找累计耗时最大的指纹。
  4. 锁：SHOW ENGINE INNODB STATUS 的锁段、8.0 的 performance_schema.data_lock_waits。
  5. 复制：主从延迟、SQL 线程是否已停止。
  6. 存储：数据目录水位、binlog 与 undo 占用、慢日志体积。
  7. 主机：CPU、内存、IO await、网络、句柄数。

顺序反了代价很高：先去看主机 CPU，只能看到“mysqld 占了 90% CPU”，而真正的原因一小时前就写进慢日志了。

第一步：连接与线程水位

-- 5.7 / 8.0 通用
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Aborted_%';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

-- 谁在跑、跑了多久、什么状态（5.7 与 8.0 都可用）
SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS sql_text
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC
LIMIT 20;

8.0 更推荐走 performance_schema，能看到更细的等待阶段：

SELECT thread_id, processlist_time, processlist_state,
       LEFT(processlist_info, 120) AS sql_text
FROM performance_schema.threads
WHERE processlist_command <> 'Sleep'
ORDER BY processlist_time DESC
LIMIT 20;

关键状态指标与阈值参考

指标含义正常参考异常判断
Threads_connected当前连接数低于 max_connections 的 70%逼近上限就会报 Too many connections
Threads_running正在执行的线程（不含 Sleep）一般低于 CPU 核数的 2~3 倍持续高于 2 倍核数说明 SQL 在排队
Innodb_row_lock_waits行锁等待累计次数增长平缓短时间内快速增长说明锁冲突严重
Innodb_row_lock_time_avg平均锁等待毫秒数个位数到几十毫秒上百毫秒说明锁被长时间持有
Slow_queries慢查询累计条数每秒增量接近 0每秒都在涨说明索引治理不到位
buffer pool 命中率热数据在内存中的比例高于 99%低于 95% 说明内存装不下工作集
Seconds_Behind_Master从库延迟秒数0~1 秒持续大于 10 秒要做读写隔离
Aborted_connects握手阶段失败累计偶发快速增长说明网络抖动或账号密码错误

命中率可以直接算，不必心算两个计数器的比值：

SELECT ROUND(100 - (
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') * 100
  / NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 0)
), 2) AS buffer_pool_hit_pct;

performance_schema.global_status 在 5.7 与 8.0 都可用；8.0 里 information_schema.GLOBAL_STATUS 已废弃，新脚本不要再依赖它。

健康巡检清单

巡检项采集方式阈值与处置
QPS / TPSSHOW GLOBAL STATUS LIKE 'Questions'、Com_commit+Com_rollback 做差分与容量基线比对，突增先查来源 IP 与业务
活跃连接数Threads_running、processlist 非 Sleep 计数超过 2 倍核数即介入
行锁等待Innodb_row_lock_waits、Innodb_row_lock_time_avg均值上百毫秒要查长事务
慢查询条数Slow_queries、慢日志行数按小时统计每小时新增持续上升即安排治理
主从延迟Seconds_Behind_Master、GTID 差集超过 10 秒告警，超过 60 秒切换读流量
数据目录水位df -h、du -sh /var/lib/mysql/*超过 80% 必须清理，超过 90% 立即处理
buffer pool 命中率上面的 SQL低于 95% 考虑扩内存或治理全表扫描
连接错误Aborted_clients、Aborted_connects持续增长说明 wait_timeout 偏小或网络有问题

一次性抓取的巡检脚本：

#!/bin/bash
# MySQL 日常巡检，输出到文件后与昨日结果对比
mysql -uroot -p"$PASS" -e "
SHOW GLOBAL STATUS WHERE Variable_name IN
 ('Threads_connected','Threads_running','Slow_queries','Aborted_clients',
  'Innodb_row_lock_waits','Innodb_row_lock_time_avg',
  'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');
SHOW SLAVE STATUS\G" > /tmp/mysql_health_$(date +%F).txt 2>&1

df -h /var/lib/mysql >> /tmp/mysql_health_$(date +%F).txt
du -sh /var/lib/mysql/* >> /tmp/mysql_health_$(date +%F).txt 2>/dev/null

处置动作

  • 发现长耗时 SELECT：确认是否可 kill，kill 前先看它是否持有大量行锁。
  • 发现活跃连接打满：先临时抬高 max_connections 争取时间，同时限流上游，别把它当长久方案。
  • 发现锁等待飙升：找到阻塞源事务（information_schema.innodb_trx），kill 阻塞者而不是全部等待者。
  • 发现磁盘水位告警：优先清理 binlog 与慢日志，不要先动数据文件。
  • 处置完成前不要重启：重启会丢现场，而且流量一回来问题大概率复发。

预防与巡检项

  • 慢查询阈值固化：long_query_time 设 0.5~1 秒，并打开慢日志，这是后续一切治理的数据源。
  • 监控必须有：活跃连接、Threads_running、锁等待、慢查询速率、主从延迟、磁盘水位六条曲线。
  • 索引变更走流程：先 EXPLAIN 再上，线上用 Online DDL 或 gh-ost，避开业务高峰。
  • 连接数预算按上游实例数与池大小之和计算，留 30% 余量，不要指望“不够就调大”。
  • 每季度做一次巡检基线归档，用同比数据判断容量趋势，而不是等告警才看。

小结：MySQL 故障基本都沿“慢 SQL → 连接堆积 → 线程耗尽 → 全站不可用”这条链传导，排查必须按连接数、活跃 SQL、慢查询、锁、复制、磁盘、主机的顺序自上而下推进，用 Threads_running、锁等待、慢查询速率、主从延迟、磁盘水位这几个信号在缓冲期内止血，最后把阈值固化进监控与巡检。

笔记加载中…

标签：#故障排查 #运维

返回知识库