印刻数据库

外观

MySQL 经典故障案例分析 ​

案例一：慢查询导致数据库服务器负载过高 ​

故障现象 ​

  • 数据库服务器 CPU 使用率持续高达 99%
  • 应用响应时间延长，部分请求超时
  • 连接数急剧增加，接近最大连接数限制

故障原因分析 ​

  1. 查看当前连接状态
sql
SHOW FULL PROCESSLIST;

发现大量相同的 SELECT 查询处于 "Sending data" 状态。

  1. 分析慢查询日志

查看慢查询日志，发现一条复杂的 JOIN 查询执行时间超过 30 秒，且被频繁执行。

  1. 查看执行计划
sql
EXPLAIN SELECT * FROM orders o 
JOIN order_items oi ON o.id = oi.order_id 
JOIN products p ON oi.product_id = p.id 
WHERE o.created_at BETWEEN '2023-01-01' AND '2023-01-31' 
AND p.category_id = 1;

发现查询未使用索引，进行了全表扫描。

解决方案 ​

  1. 优化查询语句
  • 只选择需要的列，避免使用 SELECT *
  • 添加适当的索引：ALTER TABLE orders ADD INDEX idx_created_at (created_at);
  • 优化 JOIN 条件
  1. 调整数据库参数
  • 增加 innodb_buffer_pool_size，提高缓存命中率
  • 调整 max_connections，避免连接数耗尽
  1. 应用层优化
  • 减少查询频率，添加缓存机制
  • 优化应用逻辑，避免不必要的查询

经验教训 ​

  • 定期分析慢查询日志，及时发现和优化慢查询
  • 为经常查询的列添加适当的索引
  • 避免在生产环境中使用复杂的 JOIN 查询
  • 应用层添加缓存机制，减少数据库压力

案例二：主从复制延迟过大 ​

故障现象 ​

  • 主从复制延迟超过 1 小时
  • 从库无法及时同步主库数据
  • 依赖从库的业务系统出现数据不一致问题

故障原因分析 ​

  1. 查看复制状态
sql
SHOW SLAVE STATUS\G;

发现从库的 Seconds_Behind_Master 为 3600 秒。

  1. 检查从库状态
  • 从库 CPU 使用率接近 100%
  • 从库 I/O 等待时间过长
  • 从库存在大量慢查询
  1. 分析二进制日志

发现主库执行了大量的批量更新操作，生成了大量的二进制日志。

解决方案 ​

  1. 优化从库配置
  • 增加从库的硬件资源（CPU、内存、磁盘）
  • 调整 innodb_buffer_pool_size，提高缓存命中率
  • 开启 slave_parallel_workers，启用并行复制
  1. 优化主库操作
  • 避免在业务高峰期执行大量批量操作
  • 拆分大的批量更新为多个小批量更新
  • 优化 SQL 语句，减少二进制日志量
  1. 监控和告警
  • 设置主从延迟告警阈值
  • 定期检查复制状态
  • 配置自动切换机制

经验教训 ​

  • 主从复制需要考虑硬件资源匹配
  • 启用并行复制可以提高复制效率
  • 避免在业务高峰期执行大量批量操作
  • 建立完善的监控和告警机制

案例三：数据库连接数耗尽 ​

故障现象 ​

  • 应用无法连接到数据库，报错 "Too many connections"
  • 数据库服务器连接数达到最大限制
  • 业务系统完全不可用

故障原因分析 ​

  1. 查看当前连接数
sql
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

发现连接数已达到 max_connections 限制（默认 151）。

  1. 分析连接来源

查看 processlist，发现大量来自应用服务器的连接处于 "Sleep" 状态，长时间未释放。

  1. 检查应用代码

发现应用程序存在连接泄漏问题，未正确关闭数据库连接。

解决方案 ​

  1. 临时增加连接数
sql
SET GLOBAL max_connections = 1000;
  1. 优化连接池配置
  • 调整连接池的最大连接数
  • 设置合理的连接超时时间
  • 配置连接池的心跳机制
  1. 修复应用代码
  • 确保所有数据库连接都能正确关闭
  • 使用 try-with-resources 或 finally 块关闭连接
  • 增加连接使用日志，便于排查连接泄漏
  1. 优化数据库配置
  • 设置 wait_timeout 和 interactive_timeout，自动关闭空闲连接
  • 启用连接池监控，及时发现连接泄漏

经验教训 ​

  • 合理设置连接池参数，避免连接数过多
  • 确保应用程序正确关闭数据库连接
  • 定期监控连接使用情况
  • 启用连接超时机制，自动关闭空闲连接

案例四：磁盘空间不足导致数据库崩溃 ​

故障现象 ​

  • 数据库突然崩溃，无法启动
  • 错误日志显示 "Error writing file '/var/lib/mysql/binlog.000001' (Errcode: 28 - No space left on device)"
  • 磁盘使用率 100%

故障原因分析 ​

  1. 检查磁盘空间
bash
df -h

发现 /var/lib/mysql 所在分区磁盘空间已耗尽。

  1. 分析磁盘使用情况
bash
du -sh /var/lib/mysql/*

发现二进制日志文件占用了大量空间，最大的二进制日志文件达到 100GB。

  1. 查看二进制日志配置
sql
SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'max_binlog_size';

发现 expire_logs_days 未设置（默认 0，即不自动清理），max_binlog_size 设置为 1GB。

解决方案 ​

  1. 清理磁盘空间
  • 删除过期的二进制日志文件
  • 调整二进制日志保留策略
  1. 重启数据库
bash
systemctl start mysql
  1. 优化二进制日志配置
sql
SET GLOBAL expire_logs_days = 7;
SET GLOBAL max_binlog_size = 500M;
  1. 定期监控磁盘空间
  • 设置磁盘空间告警阈值
  • 定期清理不必要的日志文件
  • 考虑使用外部存储存储二进制日志

经验教训 ​

  • 定期检查磁盘空间使用情况
  • 设置合理的二进制日志保留策略
  • 启用自动清理过期日志
  • 考虑使用外部存储存储日志文件

案例五：死锁导致事务回滚 ​

故障现象 ​

  • 应用程序频繁报 "Deadlock found when trying to get lock; try restarting transaction"
  • 事务执行失败，需要重新执行
  • 系统吞吐量下降

故障原因分析 ​

  1. 查看死锁日志

在 MySQL 5.6+ 中，可以通过 SHOW ENGINE INNODB STATUS 查看最近的死锁信息。

  1. 分析死锁原因

发现两个事务互相等待对方释放锁：

  • 事务 A：更新表 A 的行 1，然后更新表 B 的行 1
  • 事务 B：更新表 B 的行 1，然后更新表 A 的行 1
  1. 查看事务隔离级别
sql
SHOW VARIABLES LIKE 'transaction_isolation';

发现使用的是默认的 REPEATABLE-READ 隔离级别。

解决方案 ​

  1. 优化事务顺序
  • 确保所有事务按照相同的顺序访问表和行
  • 简化事务，减少事务持有锁的时间
  1. 调整事务隔离级别
  • 考虑使用 READ-COMMITTED 隔离级别，减少锁的持有时间
  • 或使用 SERIALIZABLE 隔离级别，避免幻读
  1. 添加适当的索引
  • 为经常更新的列添加索引，减少锁的范围
  • 避免全表扫描，减少锁的冲突
  1. 应用层处理
  • 实现重试机制，自动重试死锁的事务
  • 优化应用逻辑，减少并发更新

经验教训 ​

  • 确保所有事务按照相同的顺序访问资源
  • 简化事务，减少事务持有锁的时间
  • 为经常更新的列添加适当的索引
  • 实现重试机制，处理死锁情况

版本差异对故障处理的影响 ​

MySQL 5.6 及之前版本 ​

  • 死锁信息只能通过 SHOW ENGINE INNODB STATUS 查看，且只显示最近一次死锁
  • 不支持并行复制，主从延迟问题较难解决
  • Performance Schema 功能有限，监控能力较弱
  • 慢查询日志格式较为简单，缺少详细的执行计划信息

MySQL 5.7 版本 ​

  • 引入了 performance_schema.events_transactions_* 表，增强了事务监控
  • 支持并行复制（基于库的并行复制）
  • 改进了慢查询日志格式，包含更多执行计划信息
  • 引入了 innodb_deadlock_detect 变量，可禁用死锁检测

MySQL 8.0 版本 ​

  • 支持基于写集合的并行复制，大幅提高复制效率
  • 引入了 innodb_undo_log_truncate 功能，自动清理 undo 日志
  • 增强了 Performance Schema，提供更详细的性能监控
  • 支持 EXPLAIN ANALYZE，提供更详细的执行计划分析
  • 引入了 SET PERSIST 命令，无需重启即可永久修改变量

生产实践建议 ​

1. 建立完善的监控体系 ​

  • 监控 CPU、内存、磁盘、网络等系统资源
  • 监控连接数、查询响应时间、慢查询数量等数据库指标
  • 监控主从复制延迟、二进制日志大小等复制指标

2. 定期进行性能优化 ​

  • 定期分析慢查询日志，优化查询语句
  • 定期检查索引使用情况，删除冗余索引
  • 定期优化表结构，整理碎片

3. 建立故障处理流程 ​

  • 制定详细的故障处理流程
  • 定期进行故障演练，提高团队应对能力
  • 建立故障知识库，总结经验教训

4. 合理配置数据库参数 ​

  • 根据硬件资源和业务需求，合理配置数据库参数
  • 定期调整参数，优化数据库性能
  • 记录参数调整历史，便于回溯

常见问题（FAQ） ​

Q1: 如何快速定位 MySQL 故障？ ​

A1: 可以通过以下步骤快速定位故障：

  • 查看系统资源使用情况
  • 查看数据库连接状态
  • 分析慢查询日志
  • 查看错误日志
  • 查看 InnoDB 状态

Q2: 如何避免慢查询导致的性能问题？ ​

A2: 可以通过以下方法避免慢查询：

  • 为经常查询的列添加适当的索引
  • 优化查询语句，避免复杂的 JOIN 查询
  • 应用层添加缓存机制，减少数据库压力
  • 定期分析慢查询日志，及时优化

Q3: 如何解决主从复制延迟问题？ ​

A3: 可以通过以下方法解决主从复制延迟：

  • 启用并行复制
  • 优化主库操作，避免批量更新
  • 增加从库的硬件资源
  • 调整从库配置参数

Q4: 如何处理数据库连接数耗尽问题？ ​

A4: 可以通过以下方法处理连接数耗尽：

  • 临时增加 max_connections 参数
  • 优化连接池配置，设置合理的连接超时时间
  • 修复应用代码，确保正确关闭连接
  • 启用连接超时机制，自动关闭空闲连接

Q5: 如何避免死锁问题？ ​

A5: 可以通过以下方法避免死锁：

  • 确保所有事务按照相同的顺序访问资源
  • 简化事务，减少事务持有锁的时间
  • 为经常更新的列添加适当的索引
  • 调整事务隔离级别
  • 实现重试机制，处理死锁情况