MySQL 慢查询风暴处理

慢查询风暴定义

特征识别

慢查询风暴是指数据库突然出现大量慢查询，导致数据库性能急剧下降，甚至服务不可用的情况。主要特征包括：

• CPU 使用率突然飙升
• 连接数迅速增加
• 查询响应时间显著变长
• 数据库服务器负载过高
• 可能出现连接超时或拒绝连接

影响范围

• 应用层：用户体验下降，请求超时，业务功能不可用
• 数据库层：锁竞争加剧，事务积压，复制延迟增加
• 系统层：服务器资源耗尽，可能导致系统崩溃

紧急处理措施

快速定位

识别慢查询来源

-- 查看当前执行的慢查询
SHOW FULL PROCESSLIST;

-- 按执行时间排序
SHOW FULL PROCESSLIST ORDER BY Time DESC;

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log%';

分析慢查询特征

• 识别共同的查询模式
• 检查是否有全表扫描
• 分析是否有锁等待
• 确认是否为特定表或索引问题

紧急缓解

终止长时间运行的查询

-- 终止指定进程
KILL [process_id];

-- 批量终止长时间运行的查询
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE Time > 60;

临时调整参数

-- 降低连接超时时间
SET GLOBAL wait_timeout = 300;

-- 降低交互式超时时间
SET GLOBAL interactive_timeout = 300;

-- 限制最大连接数
SET GLOBAL max_connections = 500;

-- 启用查询缓存（临时）
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 64M;

应用层降级

• 启用服务降级
• 限制并发请求
• 暂时关闭非核心功能
• 启用缓存机制

根本原因分析

常见原因

原因类型	具体表现	影响程度
索引问题	缺少索引、索引失效	高
SQL 语句问题	复杂查询、全表扫描	高
数据量问题	表数据量突增、统计信息过时	中
锁竞争	长时间事务、死锁	高
配置问题	内存不足、参数不合理	中
硬件问题	磁盘 I/O 瓶颈、CPU 不足	高
外部因素	网络延迟、应用程序 bug	中

详细分析方法

慢查询日志分析

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;

-- 使用 pt-query-digest 分析
pt-query-digest /var/log/mysql/slow-query.log

执行计划分析

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE created_at > '2023-01-01';

-- 分析带索引使用情况
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at > '2023-01-01';

系统资源分析

-- 查看 CPU 使用情况
top

-- 查看磁盘 I/O
iiostat -x

-- 查看内存使用情况
free -h

-- 查看网络状态
netstat -an

长期解决方案

索引优化

索引设计

• 添加缺失索引：根据慢查询模式添加合适的索引
• 优化现有索引：删除冗余索引，合并重复索引
• 使用覆盖索引：减少回表操作
• 考虑索引顺序：将选择性高的列放在前面

索引维护

-- 重建索引
ALTER TABLE users ENGINE=InnoDB;

-- 优化表
OPTIMIZE TABLE users;

-- 更新统计信息
ANALYZE TABLE users;

SQL 语句优化

常见优化技巧

• **避免 SELECT ***：只选择需要的列
• 使用 LIMIT：限制返回行数
• 优化 JOIN 语句：使用合适的 JOIN 类型和顺序
• 避免在 WHERE 子句中使用函数：可能导致索引失效
• 使用预处理语句：提高执行效率

示例优化

-- 优化前
SELECT * FROM orders WHERE DATE(create_time) = '2023-01-01';

-- 优化后
SELECT order_id, user_id, amount FROM orders WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

配置优化

内存配置

# my.cnf 配置
[mysqld]
# 缓冲池大小
innodb_buffer_pool_size = 8G

# 缓冲池实例数
innodb_buffer_pool_instances = 8

# 查询缓存
query_cache_type = OFF
query_cache_size = 0

# 排序缓冲
sort_buffer_size = 2M

# 连接缓冲
join_buffer_size = 2M

并发配置

# my.cnf 配置
[mysqld]
# 最大连接数
max_connections = 1000

# 连接队列大小
back_log = 100

# 线程池大小
thread_pool_size = 8

# 事务隔离级别
transaction_isolation = READ-COMMITTED

架构优化

读写分离

• 主库处理写操作
• 从库处理读操作
• 使用 ProxySQL 或 MaxScale 实现自动读写分离

分库分表

• 水平分表：按时间或哈希分表
• 垂直分表：将大表拆分为小表
• 分库：按业务线或数据范围分库

缓存策略

• 使用 Redis 缓存热点数据
• 实现应用层缓存
• 考虑使用 Memcached

监控与预警

监控指标

慢查询相关指标

• 慢查询数量
• 平均查询执行时间
• 全表扫描次数
• 临时表创建次数
• 排序扫描行数

系统资源指标

• CPU 使用率
• 内存使用率
• 磁盘 I/O 利用率
• 网络流量
• 连接数

预警设置

合理的告警阈值

指标	阈值	告警级别
慢查询数	> 100/分钟	警告
平均查询时间	> 1秒	警告
CPU 使用率	> 80%	警告
连接数	> 80% 最大连接数	警告
磁盘 I/O	> 90% 利用率	严重

告警方式

• 邮件通知
• 短信通知
• 企业微信/钉钉通知
• 监控系统集成

预防措施

开发规范

1. SQL 审查：建立 SQL 语句审查机制
2. 索引设计：在开发阶段考虑索引设计
3. 性能测试：上线前进行性能测试
4. 代码规范：制定 SQL 编写规范

运维规范

1. 定期分析：定期分析慢查询日志
2. 索引维护：定期检查和优化索引
3. 统计信息更新：定期更新表统计信息
4. 配置调整：根据业务变化调整配置

应急演练

1. 模拟演练：定期模拟慢查询风暴场景
2. 响应测试：测试应急响应流程
3. 预案更新：根据演练结果更新预案
4. 团队培训：培训团队成员的应急处理能力

案例分析

案例一：索引失效导致的慢查询风暴

现象

• 应用突然响应缓慢
• 数据库 CPU 使用率达到 100%
• 大量相同的查询在执行

原因

• 开发人员修改了 SQL 语句，导致索引失效
• 查询变为全表扫描，数据量较大时性能急剧下降

解决方案

• 紧急添加合适的索引
• 优化 SQL 语句，确保索引被使用
• 建立 SQL 审查机制

案例二：业务高峰期的慢查询风暴

现象

• 每天固定时间点出现性能下降
• 大量并发查询导致系统负载过高

原因

• 业务高峰期，用户请求量突增
• 部分查询没有优化，无法应对高并发

解决方案

• 实施读写分离
• 优化关键查询语句
• 增加缓存机制
• 考虑数据库扩容

常见问题（FAQ）

Q1: 如何快速识别慢查询风暴的来源？

A1: 可以通过以下步骤快速识别：

• 执行 SHOW FULL PROCESSLIST 查看当前运行的查询
• 分析慢查询日志，使用 pt-query-digest 工具
• 查看系统资源使用情况，定位瓶颈
• 检查是否有锁等待或死锁

Q2: 慢查询风暴发生时，是否应该立即终止所有慢查询？

A2: 不建议立即终止所有慢查询，应该：

• 先分析慢查询的类型和影响范围
• 优先终止执行时间长、影响大的查询
• 保留部分查询用于分析根因
• 同时采取其他缓解措施

Q3: 如何防止慢查询风暴再次发生？

A3: 可以采取以下预防措施：

• 建立完善的监控和预警机制
• 定期分析慢查询日志并优化
• 实施 SQL 审查和性能测试
• 优化数据库架构和配置
• 定期进行应急演练

Q4: 慢查询风暴和死锁有什么区别？

A4: 两者的主要区别：

• 慢查询风暴：大量查询执行缓慢，导致系统资源耗尽
• 死锁：两个或多个事务相互等待对方释放锁
• 影响范围：慢查询风暴影响整个系统，死锁通常影响特定事务
• 处理方式：慢查询风暴需要终止慢查询，死锁会被 MySQL 自动检测并回滚