故障排查手册

MySQL 常见问题排障指南

系统的故障排障可以节省数小时的调试时间。本指南涵盖诊断程序、常见错误和解决策略。

1. 性能问题诊断流程

问题诊断流程：

1. 确认问题存在
   SHOW PROCESSLIST; -- 是否有慢查询
   SELECT * FROM slow_log ORDER BY start_time DESC;

2. 检查资源利用率
   SHOW ENGINE INNODB STATUS\G  -- CPU、I/O、锁
   SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

3. 找出慢查询
   SELECT * FROM performance_schema.events_statements_summary_by_digest
   ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

4. 分析执行计划
   EXPLAIN FORMAT=JSON SELECT ...;

5. 检查锁和阻塞
   SELECT * FROM performance_schema.data_locks;

常见性能问题：

索引缺失（全表扫描）：
-- EXPLAIN显示type=ALL表示全表扫描
-- 解决：CREATE INDEX idx_column ON table(column);

N+1查询问题：
❌ for each user { getOrdersByUser(user.id); }  -- 1000个查询
✅ SELECT * FROM orders WHERE user_id IN (user_ids);  -- 1个查询

字符串与整数关联：
❌ users.id INT, logs.user_id VARCHAR(50)
✅ 两列类型必须一致

ORDER BY使用文件排序：
❌ SELECT * FROM users ORDER BY created_at LIMIT 10;
✅ CREATE INDEX idx_created ON users(created_at);

死锁排查：

查看死锁信息：
SHOW ENGINE INNODB STATUS\G | grep -A 20 "DEADLOCK"

防止死锁：
1. 锁定顺序一致（所有事务按同一顺序加锁）
2. 使用READ COMMITTED隔离级别
3. 设置锁超时和重试

内存和资源：

OOM错误原因：
├─ Buffer pool配置过大
├─ 临时表占用内存过多
└─ 查询结果集太大

解决：
-- 减小buffer pool
SET GLOBAL innodb_buffer_pool_size = 12GB;

-- 增大临时表大小
SET GLOBAL tmp_table_size = 1GB;

-- 使用分页查询
SELECT * FROM big_table LIMIT 0, 1000;

常见错误码：

ERROR 1040：连接数超限
-- SET GLOBAL max_connections = 500;

ERROR 1205：锁超时
-- 检查阻塞查询，分析长事务

ERROR 2006：连接断开
-- 实施连接池重试机制

故障排查最佳实践：

1. 编写故障指南（Runbook）
2. 在staging测试修复
3. 获得团队批准
4. 维护窗口执行
5. 密切监控结果
6. 事后总结原因

2. 常见慢查询模式

模式1：缺少索引

查询：SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN显示：type=ALL（全表扫描）
修复：CREATE INDEX idx_email ON users(email);

模式2：ORDER BY未使用索引

查询：SELECT id, name FROM users ORDER BY created_at DESC LIMIT 10;
问题：文件排序（大表慢）
修复：CREATE INDEX idx_created ON users(created_at, id, name);

模式3：N+1查询

❌ 坏代码：
users = getAllUsers();  -- 1个查询，返回1000行
for (user in users) {
    user.orders = getOrdersByUser(user.id);  -- 1000个查询！
}

✅ 方案A：JOIN
SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id;

✅ 方案B：批量查询
SELECT * FROM orders WHERE user_id IN (ID列表);

模式4：JOIN列类型不匹配

❌ 坏：
CREATE TABLE users (id INT);
CREATE TABLE logs (user_id VARCHAR(50));  -- 类型错误！

SELECT * FROM users u JOIN logs l ON u.id = l.user_id;
-- 类型强制转换导致全表扫描

✅ 修复：ALTER TABLE logs MODIFY user_id INT;

模式5：子查询未优化

❌ 坏：
SELECT * FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE status = 'active'
);

✅ 更好：
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

模式6：LIKE前缀通配符

❌ 坏：
SELECT * FROM users WHERE email LIKE '%@example.com';
-- 不能使用索引（全表扫描）

✅ 更好：
SELECT * FROM users WHERE email LIKE '[email protected]';
-- 可以使用索引

3. 锁和死锁排查

检测死锁：

查看死锁日志：
SHOW ENGINE INNODB STATUS\G | grep -A 20 "DEADLOCK"

输出示例：
--- Last deadlock time 2024-04-24 15:30:45 ---

事务A: UPDATE orders SET status='shipped' WHERE id=1
  持有锁：orders行1
  等待：orders行2

事务B: UPDATE orders SET status='paid' WHERE id=2
  持有锁：orders行2
  等待：orders行1

结果：检测到死锁，事务B回滚

预防死锁：

1. 锁定顺序（最有效）

❌ 坏（可能死锁）：
事务A：锁表1，然后锁表2
事务B：锁表2，然后锁表1

✅ 好（无死锁）：
所有事务按相同顺序加锁：表1，然后表2

2. 超时和重试

SET innodb_lock_wait_timeout = 5;  -- 超时前等待秒数

应用代码：
try {
  START TRANSACTION;
  UPDATE orders SET status='shipped' WHERE id = order_id;
  COMMIT;
} catch (DeadlockException e) {
  // 重试最多3次
  sleep(random(1,3) seconds);  // 退避
  retry();
}

锁等待诊断：

查看当前锁：
SELECT * FROM performance_schema.data_locks;

等待锁的会话：
SELECT * FROM performance_schema.data_lock_waits;

杀死阻塞会话（必要时）：
KILL 12345;  -- 来自SHOW PROCESSLIST的会话ID

4. 内存和资源问题

OOM（内存不足）错误：

原因1：Buffer pool过大
-- 系统16GB，innodb_buffer_pool_size = 20GB → OOM

修复：
SET GLOBAL innodb_buffer_pool_size = 12GB;

原因2：内存中的大临时表
解决：
-- 增加tmp_table_size
SET GLOBAL tmp_table_size = 1GB;
SET GLOBAL max_heap_table_size = 1GB;

-- 或重写查询以减少临时表大小

原因3：查询结果集太大
SELECT * FROM big_table;  -- 所有1亿行加载到内存

修复：使用LIMIT和分页
SELECT * FROM big_table LIMIT 0, 1000;

监控内存：

按线程检查内存使用：
SELECT
  THREAD_ID,
  SUM(CURRENT_NUMBER_OF_BYTES_USED) as bytes_used
FROM performance_schema.memory_summary_by_thread_by_event_name
GROUP BY THREAD_ID
ORDER BY bytes_used DESC;

如果current_bytes持续增长：
├─ 怀疑内存泄漏
├─ 检查表缓存大小
├─ 重启MySQL以回收内存
└─ 调查应用连接池

5. 常见错误码和解决方案

ERROR 1040：连接数过多
原因：达到max_connections限制
解决：SHOW VARIABLES LIKE 'max_connections';
     SET GLOBAL max_connections = 500;
     或杀死空闲连接：KILL ;

ERROR 1090：SafeUpdates模式
原因：MySQL以安全更新模式运行
解决：SET SQL_SAFE_UPDATES = 0;

ERROR 1205：锁等待超时
原因：事务等待锁的时间过长
解决：SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
     SET GLOBAL innodb_lock_wait_timeout = 60;
     分析阻塞查询

ERROR 1366：整数值不正确
原因：向INT列插入字符串
解决：插入前检查数据类型

ERROR 1451：外键约束（不能删除/更新）
原因：外键约束阻止删除
解决：SET FOREIGN_KEY_CHECKS=0;（临时维护用）
     或修复约束关系

ERROR 2006：MySQL已断开
原因：连接意外关闭
解决：实施连接重试逻辑
     检查MySQL是否崩溃：查看错误日志
     增加wait_timeout：SET GLOBAL wait_timeout = 3600;

ERROR 2013：查询期间连接丢失
原因：网络超时或服务器重启
解决：使用重试实现连接池
     增加连接字符串中的read_timeout

磁盘满错误
原因：磁盘100%利用率
解决：立即释放磁盘空间
     PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

6. 排障最佳实践

文档化：

为每个生产错误维护故障手册：

错误：复制延迟超过30秒
├─ 症状：延迟增加，用户看到过时数据
├─ 根本原因：主库上的长时间运行事务
├─ 检测：SHOW SLAVE STATUS; 检查Seconds_Behind_Master
├─ 解决步骤：
│  1. 检查主库上的SHOW PROCESSLIST;
│  2. 识别长查询
│  3. 等待完成或在安全时KILL QUERY
│  4. 验证延迟恢复到60秒立即通知DBA

应用修复前测试：
1. 在staging重现
2. 记录预期影响（延迟、停机时间）
3. 准备好回滚计划
4. 获得团队负责人批准
5. 在维护窗口执行
6. 密切监控指标
7. 记录修复内容

排障工具：

Percona Toolkit:
pt-query-advisor /var/log/mysql/slow.log

MySQL Workbench:
-- 可视化EXPLAIN输出
-- 连接管理
-- Schema比较

Performance Schema查询:
SELECT * FROM performance_schema.global_status;
SELECT * FROM performance_schema.data_locks;