MySQL 常见问题排查与故障处理实战：从踩坑到最佳实践

1. 核心理念：从“野路子”到“正规军”

目标读者：具备基础 CRUD 能力，但缺乏线上复杂故障排查经验的开发者。
核心痛点：遇到报警手忙脚乱、盲目重启、依赖运气修复。
解决思路：
- 标准化 (SOP)：建立固定的排查步骤，拒绝“拍脑袋”。
- 数据化：用监控数据和日志说话，而非凭感觉。
- 自动化：将重复性检查转化为脚本，释放人力。

2. 神兵利器：诊断工具链

工欲善其事，必先利其器。掌握以下工具是排查问题的前提。

2.1 基础三件套

命令

作用

关键关注点

SHOW PROCESSLIST

查看当前运行的所有线程

Time (耗时), State (状态), Info (SQL 语句)

EXPLAIN

分析 SQL 执行计划

type (访问类型), key (实际索引), rows (扫描行数)

SHOW ENGINE INNODB STATUS\G

查看 InnoDB 引擎内部状态

LATEST DETECTED DEADLOCK (最近死锁), TRANSACTIONS (事务等待)

2.2 高级监控与日志

PERFORMANCE_SCHEMA：数据库的“显微镜”。
- 用途：追踪每个 SQL 的资源消耗（如临时表创建次数、锁等待时间）。
- 注意：生产环境开启需评估性能开销。
慢查询日志 (Slow Query Log) + pt-query-digest：
- 用途：自动聚合分析，找出 Top 10 最慢 SQL。
- 命令示例：pt-query-digest /var/log/mysql/slow.log > report.txt
Binlog：
- 用途：数据恢复、操作回溯、主从同步源。

3. 实战场景：三大经典故障复盘

场景一：死锁问题 (Deadlock)

🚨 故障现象

电商大促期间，用户同时执行“下单”和“取消订单”操作，频繁报错： ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

🔍 排查步骤

查看死锁日志：
```
SHOW ENGINE INNODB STATUS\G
```
定位 LATEST DETECTED DEADLOCK 部分。
分析持有与等待：
- 事务 A：持有 order_id=1001 的行锁，等待 user_id=500 的锁。
- 事务 B：持有 user_id=500 的行锁，等待 order_id=1001 的锁。
- 结论：典型的循环等待，且两个事务操作资源的顺序不一致。

✅ 解决方案

统一加锁顺序：在代码中强制规定，多表操作时始终先锁 A 表再锁 B 表，或按 ID 从小到大排序操作。

显式加锁检查：

-- 业务逻辑优化：先锁定再检查
SELECT * FROM orders WHERE order_id = 1001 FOR UPDATE;
-- 检查状态...
UPDATE orders SET status = 'CANCELLED' ...;

缩小事务范围：将非核心的日志记录、发送通知等操作移出事务，减少锁持有时间。

💡 踩坑教训：曾因在事务中夹杂了远程 RPC 调用和日志插入，导致锁持有时间从 ms 级增加到 s 级，死锁概率飙升 10 倍。

场景二：主从延迟 (Replication Lag)

🚨 故障现象

主库更新库存后，用户在从库读取仍显示旧库存，Seconds_Behind_Master 高达 60s+。

🔍 排查步骤

检查同步状态：
```
SHOW SLAVE STATUS\G
```
- 关注：Seconds_Behind_Master, Slave_IO_Running, Slave_SQL_Running.
分析原因：
- 单线程瓶颈：传统复制只有一个 SQL 线程回放，主库并发高时从库跟不上。
- 大事务：主库执行了 DELETE FROM logs WHERE date < '2023-01-01' (几百万行)，从库回放耗时极长。
- 硬件差异：从库磁盘为机械硬盘，IO 性能不足。

✅ 解决方案

短期应急：应用层配置动态路由，将核心读流量暂时切回主库（保证一致性）。

长期优化：开启并行复制 (MySQL 5.7+/8.0)：

STOP SLAVE;
-- 设置为 4 个并行线程 (根据 CPU 核数调整，勿盲目过大)
SET GLOBAL slave_parallel_workers = 4; 
-- 基于 WRITESET 或 LOGICAL_CLOCK 策略
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
START SLAVE;

拆分大事务：将大批量删除/更新改为每 1000 行提交一次。

💡 踩坑教训：曾盲目将 slave_parallel_workers 设为 16，导致从库 CPU 瞬间 100% 崩溃。后经压测调整为 4，并配合 SSD 升级才稳定。

场景三：高 CPU 占用 (Performance Bottleneck)

🚨 故障现象

每早 9 点报表生成时，数据库 CPU 飙升至 90%，其他业务接口超时。

🔍 排查步骤

定位元凶：
```
SHOW PROCESSLIST ORDER BY Time DESC LIMIT 5;
```
发现某条统计 SQL 运行超过 10 秒。
分析执行计划：
```
EXPLAIN SELECT count(*) FROM orders WHERE create_time > '...' AND region = '...';
```
- 发现：type: ALL (全表扫描), rows: 10,000,000+。
- 原因：缺少复合索引，且使用了函数导致索引失效。

✅ 解决方案

添加复合索引：

ALTER TABLE orders ADD INDEX idx_time_region (create_time, region);

优化 SQL 写法：避免在索引列上使用函数（如 DATE(create_time)），改为范围查询。
历史数据归档/分区：
- 对超过 1 年的数据进行归档。
- 或使用 PARTITION BY RANGE 按年份分区，减少单次扫描数据量。

🚀 效果：查询耗时从 15s 降至 0.8s，CPU 回落至 20%。

4. 最佳实践：预防胜于治疗

4.1 建立 SOP (标准操作流程)

遇到问题不要慌，按此流程执行：

看监控：CPU、内存、连接数、延迟是否有突增？
查进程：SHOW PROCESSLIST 有无长事务或锁等待？
看日志：Error Log 和 Slow Log 有无异常？
析计划：对可疑 SQL 执行 EXPLAIN。
定方案：Kill 会话、加索引、限流或扩容。

4.2 关键参数优化建议

参数名

推荐值

说明

innodb_buffer_pool_size

物理内存 60%-80%

缓存数据和索引，最关键参数

tmp_table_size

64M - 256M

避免内存临时表溢出到磁盘

max_connections

根据业务预估

防止连接数爆满，配合连接池使用

expire_logs_days

7 - 15

必须设置！防止 Binlog 撑爆磁盘

sync_binlog

1 (高安全) / 0 (高性能)

金融类建议 1，普通业务可 0

4.3 防坑警示

ORM 陷阱：不要过度依赖 Hibernate/MyBatis 自动生成的复杂嵌套 SQL，关键业务必须手写并核查执行计划。
索引误区：索引不是越多越好。定期清理冗余索引（如已有 (a,b) 索引，单独的 a 索引通常多余），否则影响写入性能。
主从切换校验：切换前务必使用 pt-table-checksum 校验数据一致性，避免切换到数据缺失的从库。

5. 附录：自动化巡检脚本示例

一个简单的 Bash 脚本，用于检测长事务并报警：

#!/bin/bash
# check_long_transactions.sh

MYSQL_USER="monitor"
MYSQL_PASS="password"
THRESHOLD=60 # 超过 60 秒视为长事务

LONG_TX=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO 
FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' AND TIME > $THRESHOLD;
")

if [ ! -z "$LONG_TX" ]; then
    echo "[ALERT] Detected Long Transactions:"
    echo "$LONG_TX"
    # 此处可集成钉钉/企业微信/邮件报警
    # curl -X POST "https://webhook.url" -d "$LONG_TX"
else
    echo "[OK] No long transactions detected."
fi