故障排查与最佳实践

排查套路

数据库告警响了，别急着改参数。按固定顺序走，比乱试快得多：

1. 先止血：业务已经不可用，先恢复服务（限流、kill 大查询、切主、扩容），再慢慢找根因。
2. 看现象：错误码是什么？1040 是连接满，1205 是锁等待超时，1213 是死锁，1114 是表满。错误码直接指向故障类别。
3. 看现场：SHOW PROCESSLIST、SHOW ENGINE INNODB STATUS、performance_schema 里的锁和事务——这些是「此刻」的快照，故障过去就没了，先抓下来。
4. 看日志：错误日志（error log）记崩溃和重启，慢日志记慢查询，binlog 记变更。三份日志对照时间点。
5. 复盘：写清楚触发条件、影响范围、根因、改进项。同样的坑不踩第二次。

先保存现场

SHOW PROCESSLIST、SHOW ENGINE INNODB STATUS 的输出在故障结束后就查不到了。养成习惯：一发现异常，先把这几条命令的输出重定向到文件，再动手处置。

慢 SQL 分析

慢查询是日常最高频的「慢性病」：不至于让库挂掉，但会拖垮接口、霸占连接（连接数打满那一节里，一半以上的根因最后都落在慢 SQL 上）。分析慢 SQL 有固定套路，四步走完。

第一步：把慢 SQL 抓到手

三个来源，按场景选：

• 慢日志：事后分析用。第 7 篇已经配好 slow_query_log 和 long_query_time，按故障时间点过滤即可。
• 正在发生的：SHOW PROCESSLIST 里 Time 很大且 State 不是 Sleep 的 Query，抓现行最直接。
• 聚合 TOP：performance_schema.events_statements_summary_by_digest 按 SQL 指纹聚合，按 SUM_TIMER_WAIT 排序，找出「总耗时最毒」的那几条——单条不慢的 SQL 被高频执行，杀伤力一样大。

-- 按总耗时排序，找 TOP 10 慢 SQL 指纹
SELECT DIGEST_TEXT,
       COUNT_STAR AS exec_count,
       ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
       ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_sec,
       SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

第二步：EXPLAIN 看执行计划

拿到 SQL 先别猜，跑一遍 EXPLAIN。排错视角只看四个红灯：

红灯	含义	方向
type = ALL	全表扫描	基本就是没走索引
key = NULL	一个索引都没用上	没建索引，或索引被写法作废
rows 远超预期	扫描行数比结果集大几个数量级	索引没建对或范围条件失效
Extra 有 filesort / temporary	排序/分组没用上索引，落了临时表	调整索引或改写 SQL

四个字段的完整解读在第 4 篇，这里只记排错口诀：先看 type，再看 key，rows 对数量级，Extra 抓 filesort。

第三步：对照病因清单

key = NULL 或 rows 异常时，按这张表对号入座：

症状	常见病因	处理
有索引但没用上	对索引列用了函数（如 date(created_at) = '...'）	改写为范围条件
有索引但没用上	隐式类型转换（字符串列传了数字）	参数类型对齐列类型
有索引但没用上	LIKE '%xx' 前导通配符	改后缀匹配，或上全文索引
复合索引没生效	不满足最左前缀（跳过了第一列）	调整索引列序或查询条件
rows 依然很大	回表次数太多（SELECT *）	只查必要列，做覆盖索引
Using filesort	ORDER BY 的列不在索引里	把排序列并入复合索引末尾
JOIN 特别慢	被驱动表的关联字段没索引	给关联字段加索引
LIMIT 100000, 10	深分页，前面 10 万行白扫	改游标分页（WHERE id > 上页最大值）

一个完整案例

运营后台反馈订单列表加载 12 秒。抓到这条 SQL：

SELECT * FROM orders
WHERE status = 'paid' AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN 一看三个红灯全亮：type = ALL、rows = 800000、Extra = Using filesort——status 和 created_at 都没有合适的索引，80 万行全表扫一遍再文件排序，取前 20 条。

病因明确，处理就一条：

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

复测 EXPLAIN：type = ref、rows = 200、filesort 消失（索引本身有序），接口从 12 秒降到 20 毫秒。

九成是索引问题

慢 SQL 里约九成是「没索引或索引没用上」，剩下一成是写法问题（函数、隐式转换、深分页）。真正需要改业务逻辑或拆表的，少之又少。

第四步：验证效果，留下证据

优化完别凭感觉说「变快了」：

1. EXPLAIN 对比改前改后的 rows（量级应该明显下降）；
2. 用 SET profiling = 1 + SHOW PROFILES 或 performance_schema 看真实耗时；
3. 大表加索引别在高峰直接 ALTER——用 gh-ost 或 pt-osc 在线变更，避免锁表事故（第 3 篇 DDL 一节讲过取舍）。

连接数打满

现象

应用报 ERROR 1040 (HY000): Too many connections，或者连接池报「获取连接超时」。此时 Threads_connected 等于 max_connections，新连接一律被拒——包括你自己的排查连接。

保留一条救命通道

[mysqld]
max_connections = 1000
# 额外监听一个管理端口，主端口满了还能从这里进（8.0.14+）
admin_address = 127.0.0.1
admin_port = 3308

admin_address 是 8.0 的特性，走独立端口，连接数满时依然可用。没配的话，只能靠 root 的 CONNECTION_ADMIN 权限挤进去（MySQL 会给超级用户留一个保留连接）。

定位：谁占着连接

-- 按来源 IP + 状态聚合，先看是不是某个应用炸了
SELECT SUBSTRING_INDEX(HOST, ':', 1) AS client_ip,
       COMMAND, STATE, COUNT(*) AS cnt
FROM information_schema.PROCESSLIST
GROUP BY client_ip, COMMAND, STATE
ORDER BY cnt DESC;

-- 按账号聚合
SELECT USER, COUNT(*) AS cnt
FROM information_schema.PROCESSLIST
GROUP BY USER
ORDER BY cnt DESC;

-- 跑得最久的非 Sleep 连接
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 100) AS sql_head
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC
LIMIT 20;

读结果的三个要点：

• 大量 Sleep：连接池空闲连接太多，或者应用没归还连接（泄漏）。看 Time 是不是都很长。
• 大量 Query 且 Time 很大：有慢查询在霸占连接，先治慢查询。
• Waiting for table metadata lock：有人在等 MDL，通常是一个没提交的长事务挡着 DDL。

处置

-- 应急：调大上限（立即生效，重启失效）
SET GLOBAL max_connections = 2000;

-- 应急：kill 掉卡住的连接（8.0 用 KILL CONNECTION）
KILL CONNECTION 12345;

-- 批量生成 kill 语句（确认后再执行！）
SELECT CONCAT('KILL CONNECTION ', ID, ';')
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 600;

KILL 只是止血。连接数打满的根因通常是这三个：

根因	表现	根治
连接池配置过大	每个 Pod 配 100 连接，20 个 Pod 就是 2000	按 max_connections 倒推单实例连接数，上连接池代理（ProxySQL）
连接泄漏	连接数只涨不跌，Sleep 占绝大多数	查代码里没关的 Connection，设 wait_timeout 兜底
慢查询霸占	Threads_running 高，连接都卡在 Query	优化 SQL、加索引、加超时（max_execution_time）

-- 给单条 SELECT 设超时，防一条烂 SQL 拖死整个实例
SET SESSION max_execution_time = 5000;   -- 毫秒

wait_timeout 是最后的兜底

wait_timeout（默认 8 小时）控制空闲连接多久被断开。配合连接池的 idleTimeout，让服务端比客户端稍晚回收，能避免「客户端以为连接还在、其实已被服务端关掉」的报错。一般设 600~1800 秒。

锁等待与死锁

两种错误先分清

• ERROR 1205: Lock wait timeout exceeded：等锁超时。事务 A 拿着锁不放，事务 B 等到 innodb_lock_wait_timeout（默认 50 秒）放弃。
• ERROR 1213: Deadlock found when trying to get lock：死锁。A 等 B、B 等 A，InnoDB 检测到环路，主动回滚其中一个（代价小的那个）。

死锁不是 bug，是并发系统的正常现象。InnoDB 自动处理，应用要做的只是捕获 1213 并重试。

看现场

-- 最近一次死锁的完整信息（最有用的一条命令）
SHOW ENGINE INNODB STATUS\G

输出里的 LATEST DETECTED DEADLOCK 段是重点：

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 421580, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 120, query id 8842 localhost app updating
UPDATE orders SET status = 2 WHERE id = 100;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 82 page no 4 n bits 72 index PRIMARY of table shop.orders
trx id 421580 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 421579, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 121, query id 8843 localhost app updating
UPDATE orders SET status = 3 WHERE id = 200;

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table shop.orders
trx id 421579 lock_mode X locks rec but not gap

读这段的关键：

• 看两条 SQL 的加锁顺序。上面例子是经典的「交叉更新」：事务 1 先锁 id=100 再要 id=200，事务 2 反过来。
• 看锁类型。lock_mode X locks rec but not gap 是记录锁，gap 前缀是间隙锁（RR 下范围条件才有）。
• 看索引。如果 index 后面不是 PRIMARY 而是一个普通索引，说明更新走的是二级索引，锁范围可能比预期大。

根因与改法

根因	表现	改法
加锁顺序不一致	两个事务交叉更新同一批行	业务层统一按 id 排序后批量更新
无索引更新	UPDATE ... WHERE 非索引列 锁全表	给 WHERE 列建索引，把锁范围缩到行
事务太长	事务里夹着 RPC、循环、批量操作	拆短事务，RPC 移到事务外
间隙锁（RR）	两事务锁相邻区间互相阻塞	评估降到 RC，减少 gap lock
唯一键冲突并发插入	INSERT ... ON DUPLICATE KEY UPDATE 高并发死锁	改 INSERT IGNORE 或业务层判重

-- 看一眼锁等待超时设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 死锁检测默认开启，关掉能省一点 CPU，但生产别关
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- 把死锁信息打到错误日志里，方便事后翻
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

别用 kill 解决死锁

死锁是两个事务互相等，InnoDB 已经自动回滚了一个。如果你看到 1213 就去 kill 一堆连接，很可能把正常业务也打断了。要做的是：让应用重试、把死锁日志留档、按顺序改 SQL。

主从延迟

先确认是不是真延迟

-- 从库
SHOW REPLICA STATUS\G

Seconds_Behind_Source 突然变大，先排除两种假象：

• 主库长时间没写入，从库追平了，但定时任务一跑又跳起来。
• 大事务执行期间，它只在事务结束后才更新，看起来会停住不动，然后突然跳变。

更可靠的手段是 pt-heartbeat：主库每秒写一条心跳，从库读出来算差值：

# 主库上后台跑心跳
pt-heartbeat --host=10.0.0.11 --user=root --password=xxx --database=heartbeat --create-table --update --daemonize

# 从库上监控延迟
pt-heartbeat --host=10.0.0.12 --user=root --password=xxx --database=heartbeat --monitor

四类原因，对应四种修法

原因	特征	修法
大事务	一次 DELETE/UPDATE 影响几十万行，binlog 事件巨大	拆成小批（每批 1000~5000 行），批间 sleep
大表 DDL	主库执行 ALTER，从库单线程重放几十分钟	用 gh-ost/pt-osc；从库开并行复制
无索引更新	ROW 格式下从库回放要全表扫定位行	主库补索引，binlog_row_image 按需调整
从库资源不足	从库 CPU/IO 打满，回放线程抢不到资源	升配、把备份和报表查询挪走、加 innodb_buffer_pool_size

8.0 的并行复制值得开（从库按事务组并行回放，通常能到主库写入速度的 2~5 倍）：

[mysqld]
replica_parallel_workers = 8
replica_preserve_commit_order = ON

8.0.27 起默认就是 LOGICAL_CLOCK（按主库组提交并行），要更细的粒度可以设 replica_parallel_type = WRITESET，但要额外内存。

无索引更新为什么特别伤

ROW 格式下，从库回放 UPDATE 时要在从库上找到对应行。如果 WHERE 列没索引，从库就得全表扫描——主库执行一次 100ms 的更新，从库可能扫 10 分钟。这是最隐蔽的一类延迟，因为主库看起来一点也不慢。

延迟监控的阈值

读写分离的业务，延迟超过 5 秒就该考虑把读流量切回主库；超过 60 秒基本等于从库不可用。别等「延迟 1 小时」的告警，那时候缓存早被打穿了。

表空间膨胀与磁盘满

磁盘满是最常见的「一键下线」故障。先看清空间被谁吃了：

# 数据目录按大小排序（示例路径，以实际 datadir 为准）
du -sh /data/mysql/* | sort -rh | head -20

# binlog 单独看（最常见的元凶）
du -sh /var/lib/mysql/binlog/ 2>/dev/null
ls -lh /data/mysql/binlog/ | tail -5

# 系统层面确认还剩多少
df -h /data

MySQL 里占空间的东西和处置方式：

占用项	位置	能不能直接删	正确清法
binlog	log_bin 目录	不能 rm（会让复制和恢复坏掉）	PURGE BINARY LOGS BEFORE '...'
relay log	relay_log 目录	不能（SQL 线程要用）	STOP REPLICA SQL_THREAD 后由 MySQL 自动清
慢查询日志	slow_query_log_file	可以	mv 后 FLUSH SLOW LOGS
错误日志	log_error	可以	同上，注意 logrotate
undo 表空间	undo_001	不能	等 purge 线程回收，必要时重建
临时表空间	ibtmp1	不能	重启重建（会缩回初始大小）
表碎片	表自己的 .ibd	不能	ALTER TABLE ... ENGINE=InnoDB 重建

-- 手动清理 binlog（保留最近 7 天）
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 或者配置自动清理（8.0 推荐秒级参数）
SET GLOBAL binlog_expire_logs_seconds = 604800;

-- 查碎片：data_free 很大的表可以做一次重建
SELECT TABLE_SCHEMA, TABLE_NAME,
       ROUND(DATA_LENGTH / 1024 / 1024, 1) AS data_mb,
       ROUND(DATA_FREE / 1024 / 1024, 1)   AS free_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop'
ORDER BY DATA_FREE DESC
LIMIT 10;

大表 DELETE 不释放空间：InnoDB 删除行只是标记，空间留给后续插入复用，文件不会变小。要真正缩容得 OPTIMIZE TABLE（本质是重建表，锁表且耗时，必须用 gh-ost 或放到低峰）。

别指望表空间自己缩回去

ibdata1 只会涨不会自动缩；undo 表空间虽然有 truncate 机制，但默认策略下回收得也很有限。磁盘告警时别指望「等它自己回收」，要么清 binlog，要么重建表，要么扩容。生产上提前把数据目录放在独立分区并设 80% 告警，比事后救火便宜。

磁盘满的急救顺序

1. 清 binlog（已备份/已同步的前提下）：PURGE BINARY LOGS BEFORE ...，立刻能腾出几十 G。
2. 清慢日志和错误日志：mv 走再 FLUSH，让 MySQL 重建文件句柄。
3. 确认没有大事务在写临时文件：SHOW PROCESSLIST 里找 Creating sort index、Copying to tmp table。
4. 扩容：云盘在线扩容 + resize2fs/xfs_growfs。
5. 最后才是删数据：确认备份可用之后再动，别一急删错表。

最佳实践清单

表设计

• 必须有主键，且是自增 BIGINT UNSIGNED。随机 UUID 做主键会导致页分裂、写入放大。
• 字符集统一 utf8mb4，排序规则 utf8mb4_0900_ai_ci，别混用。
• 金额用 DECIMAL，别用 FLOAT/DOUBLE（精度会丢）。
• 时间字段用 DATETIME 并设默认值（CURRENT_TIMESTAMP），顺手加 ON UPDATE。TIMESTAMP 有 2038 年问题和时区转换的坑。
• 单表索引不超过 5 个，每个索引都要能说清服务于哪条查询。
• 大字段（TEXT/BLOB）拆表，别让主表一行几 KB。

SQL 与事务

• 禁止 SELECT *，只取需要的列，给覆盖索引留机会。
• UPDATE/DELETE 必须先 SELECT 验证影响行数，线上执行带 LIMIT。
• 事务要短：不要在事务里做 RPC、发消息、循环处理。
• 批量操作分批：每批 1000~5000 行，批间 sleep 几十毫秒。
• 深分页用游标（WHERE id > last_id）或延迟关联。

变更与容量

• 大表 DDL 走 gh-ost / pt-osc，并在低峰执行，提前确认磁盘余量（要能放下新表）。
• binlog 保留期按备份周期设，一般 7 天；磁盘水位 80% 告警。
• 备份每天全备 + binlog 持续归档，每季度演练恢复并记录 RTO。

监控与安全

• 四个核心指标必须告警：存活、连接使用率、复制延迟、慢查询速率。
• 账号最小权限，应用账号不给 DROP、不给其他库；禁止 root 远程登录。
• bind-address 收紧 + 防火墙，数据库永远不要直接暴露公网。
• 错误日志、死锁日志、慢日志集中收集，故障时按时间线对齐三份日志。

小结

• 排查按套路：先止血、抓现场、看日志、再复盘；SHOW PROCESSLIST 和 SHOW ENGINE INNODB STATUS 的输出要第一时间存下来。
• 慢 SQL 四步分析：抓到手（慢日志/现场/指纹聚合）→ EXPLAIN 看四个红灯 → 对照病因清单 → 验证留证据；九成是索引问题。
• 连接数打满：1040 报警后按 IP/账号/状态聚合定位，根因多半是连接池过大、连接泄漏或慢查询；配 admin_address 留救命通道。
• 死锁：1213 是正常现象，看 LATEST DETECTED DEADLOCK 的两条 SQL 和加锁顺序；靠统一顺序、补索引、缩短事务解决。
• 主从延迟四大来源：大事务、大表 DDL、无索引更新、从库资源不足；开并行复制，别再让从库跑备份和报表。
• 磁盘满：binlog 是第一嫌疑，用 PURGE BINARY LOGS 清，别 rm；大表 DELETE 不缩容，缩容要重建表。
• 最佳实践落地成清单：表设计守主键和字符集，SQL 禁 SELECT *，事务要短，变更走工具，监控四指标，权限最小化。

到这里，MySQL 系列的正篇就结束了。附录里整理了命令速查和参考资料，日常排查时可以直接翻。