MySQL高CPU问题诊断与索引优化实战

1. 问题现象与初步排查某客户生产环境数据库系统节点1出现CPU使用率持续高于90%的告警持续时间超过2小时。作为DBA团队我们首先通过监控系统确认了以下关键指标CPU使用率平均92%峰值达到98%负载平均值Load Average15分钟负载达到8.3服务器为8核主要消耗来自用户态user进程占比约85%通过top命令实时观察发现一个mysqld进程持续占用CPU超过700%8核服务器满核为800%。这明显异常因为正常情况下该业务库的MySQL CPU使用率通常在30-50%之间波动。注意在Linux系统中top命令显示的CPU百分比是相对于单个核心的。例如800%表示进程使用了全部8个核心。2. 深入诊断与分析流程2.1 数据库层面检查首先使用MySQL自带的诊断工具进行排查-- 查看当前运行的所有线程 SHOW FULL PROCESSLIST; -- 查看性能模式中的事件统计 SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;发现一个高频执行的查询SELECT * FROM order_details WHERE user_id ? AND status pending ORDER BY create_time DESC LIMIT 100;该查询平均执行时间达到1.2秒且每分钟执行超过200次。通过EXPLAIN分析发现使用了全表扫描typeALL扫描行数约120万行返回行数通常0-5行2.2 系统层面分析使用perf工具采集CPU使用热点perf top -p $(pgrep mysqld)输出显示CPU时间主要消耗在55.3% myisam_key_read22.1% myisam_scan12.7% malloc_consolidate这表明存在严重的索引缺失问题和内存分配碎片化。3. 问题根源定位3.1 索引缺失分析检查order_details表结构CREATE TABLE order_details ( id bigint(20) NOT NULL AUTO_INCREMENT, user_id bigint(20) DEFAULT NULL, status varchar(20) DEFAULT NULL, create_time datetime DEFAULT NULL, -- 其他字段... PRIMARY KEY (id), KEY idx_create_time (create_time) ) ENGINEMyISAM;问题点查询条件中的user_id和status字段没有联合索引ORDER BY create_time与现有单列索引不匹配MyISAM引擎在高并发下有严重的表锁问题3.2 查询模式变化与开发团队沟通后了解到最近上线了新功能用户待处理订单列表该功能会频繁刷新用户界面导致上述查询激增测试环境数据量小约1万行未发现性能问题4. 解决方案与实施4.1 紧急处理措施添加临时索引缓解问题ALTER TABLE order_details ADD INDEX idx_user_status (user_id, status);优化查询语句SELECT * FROM order_details FORCE INDEX(idx_user_status) WHERE user_id ? AND status pending ORDER BY create_time DESC LIMIT 100;调整MySQL配置[mysqld] table_open_cache 4000 thread_cache_size 32 key_buffer_size 2G4.2 长期优化方案存储引擎迁移ALTER TABLE order_details ENGINEInnoDB;重建更合理的索引ALTER TABLE order_details ADD INDEX idx_user_status_time (user_id, status, create_time);应用层优化实现查询结果缓存TTL 30秒限制刷新频率前端防抖处理改为分页查询LIMIT 205. 验证与监控5.1 效果验证优化后指标对比指标优化前优化后CPU使用率92%45%查询响应时间1200ms35msQPS200205.2 监控增强新增监控项慢查询日志监控500ms索引命中率监控表扫描比例监控配置报警规则CPU持续70%超过10分钟同一SQL出现3次以上慢查询索引命中率95%6. 经验总结与最佳实践索引设计原则遵循最左前缀匹配原则区分度高的列在前避免过度索引每个表不超过5个MyISAM vs InnoDB选择读密集型且很少更新的表可考虑MyISAM有写并发需求的必须使用InnoDB事务需求必须使用InnoDB上线前检查清单检查生产环境数据量与测试环境差异分析新功能的所有SQL执行计划评估可能的热点查询应急处理流程先通过临时索引快速止血再分析根本原因制定长期方案最后完善监控防止复发在实际运维中我们发现80%的数据库CPU问题都源于索引缺失或不当。建议建立定期的索引审查机制特别是在业务功能更新时必须重新评估现有索引的适用性。