MySQL CPU使用率飙升的排查与优化实战

1. MySQL CPU使用率飙升的典型表现与影响范围当MySQL服务器的CPU使用率持续高于80%甚至达到100%时数据库响应会明显变慢业务系统可能出现超时错误。这种情况通常伴随着以下可观测现象监控图表显示CPU核心全部处于高负载状态慢查询日志中出现大量执行时间超过阈值的SQL语句连接数接近max_connections上限系统负载平均值(load average)持续高于CPU核心数这种情况如果持续超过5分钟就可能引发级联故障。去年我们一个电商系统在促销期间就遇到过类似问题由于一个未优化的商品搜索查询导致16核服务器CPU满载最终引发支付系统超时直接损失了当天的交易额。2. 系统性排查流程与关键指标分析2.1 实时状态快照采集首先通过SHOW PROCESSLIST获取当前执行的SQL线程SHOW FULL PROCESSLIST;重点关注State列显示为Sending data或Copying to tmp table的线程Time列显示执行时间过长的查询Info列包含完整SQL文本需要FULL关键字同时检查全局状态SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Handler_%; SHOW GLOBAL STATUS LIKE Innodb_row_lock%;2.2 性能模式(Performance Schema)深度分析启用性能模式可以获取更细粒度的诊断数据-- 查看哪些语句消耗CPU最多 SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10; -- 检查锁等待情况 SELECT * FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE %lock%;2.3 慢查询日志分析配置确保慢查询日志已正确配置# my.cnf配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1使用pt-query-digest工具分析pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt3. 六大常见原因与针对性解决方案3.1 低效SQL查询优化典型特征全表扫描typeALL未使用索引keyNULL大量临时表Created_tmp_tables激增优化方案-- 添加适当索引 ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status); -- 重写复杂查询 -- 原查询 SELECT * FROM orders WHERE DATE(create_time) 2023-01-01; -- 优化后 SELECT * FROM orders WHERE create_time 2023-01-01 00:00:00 AND create_time 2023-01-02 00:00:00;3.2 锁争用问题处理诊断锁争用SHOW ENGINE INNODB STATUS\G -- 查看TRANSACTION部分解决方案降低事务隔离级别如从REPEATABLE READ改为READ COMMITTED拆分大事务为小事务优化索引减少锁定范围3.3 连接风暴应对措施紧急处理-- 临时增加连接数 SET GLOBAL max_connections 500; -- 杀死空闲连接 SELECT CONCAT(KILL , id, ;) FROM information_schema.processlist WHERE Command Sleep AND Time 300 INTO OUTFILE /tmp/kill.sql; SOURCE /tmp/kill.sql;长期方案配置连接池建议HikariCP实现读写分离3.4 缓冲池配置优化检查缓冲池状态SHOW ENGINE INNODB STATUS\G -- 查看BUFFER POOL AND MEMORY部分优化建议# 缓冲池大小建议为可用内存的70-80% innodb_buffer_pool_size 12G # 开启多实例缓冲池 innodb_buffer_pool_instances 83.5 排序操作优化识别排序问题SHOW STATUS LIKE Sort%;优化方案-- 增加排序缓冲区 SET sort_buffer_size 4M; -- 使用索引排序替代filesort ALTER TABLE products ADD INDEX idx_price_name (price, product_name);3.6 复制延迟处理检查复制状态SHOW SLAVE STATUS\G解决方案使用多线程复制slave_parallel_workers 8 slave_parallel_type LOGICAL_CLOCK优化主库大事务4. 高级诊断工具与实战技巧4.1 sys schema深度使用-- 查看最耗资源的SQL SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10; -- 索引使用情况统计 SELECT * FROM sys.schema_unused_indexes;4.2 pt工具集实战使用pt-pmp进行堆栈分析pt-pmp --iterations5 --interval5 stack.txt使用pt-index-usage分析索引有效性pt-index-usage /var/log/mysql/mysql-slow.log --host localhost --user root4.3 应急处理checklist当CPU突然飙升至100%时立即执行SHOW PROCESSLIST定位问题SQL使用EXPLAIN分析执行计划临时kill问题会话考虑添加SQL_NO_CACHE提示测试检查是否有全表扫描操作5. 长效预防机制建设5.1 监控体系配置推荐监控指标CPU使用率按核心监控活跃线程数每秒查询量(QPS)缓冲池命中率锁等待时间Prometheus配置示例- job_name: mysql static_configs: - targets: [mysql-server:9104] metrics_path: /metrics5.2 定期健康检查每月执行检查脚本#!/bin/bash # 检查索引有效性 pt-index-usage /var/log/mysql/mysql-slow.log --host localhost index_report.txt # 检查表碎片率 mysql -e SELECT TABLE_NAME, DATA_FREE/1024/1024 AS frag_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (information_schema,mysql) AND DATA_FREE 100*1024*1024; # 检查过期统计信息 mysql -e SELECT TABLE_NAME, UPDATE_TIME FROM information_schema.TABLES WHERE UPDATE_TIME DATE_SUB(NOW(), INTERVAL 7 DAY);5.3 架构优化方向对于持续高负载系统考虑分库分表推荐使用ShardingSphere实现读写分离可使用ProxySQL热点数据缓存Redis集群考虑TiDB等分布式方案6. 典型场景案例分析6.1 电商大促期间CPU飙升现象秒杀活动开始后CPU立即100% 根因商品库存查询未加缓存 解决方案-- 添加覆盖索引 ALTER TABLE inventory ADD INDEX idx_product_warehouse (product_id, warehouse_id, stock); -- 应用层添加Redis缓存6.2 报表系统凌晨跑批卡死现象每日3点ETL任务超时 根因全表扫描无索引join 解决方案-- 创建星型模型物化视图 CREATE TABLE sales_summary_mv ( product_id INT, sale_date DATE, total_amount DECIMAL(12,2), PRIMARY KEY (product_id, sale_date) ) ENGINEInnoDB; -- 使用事件定时刷新 CREATE EVENT refresh_mv ON SCHEDULE EVERY 1 DAY STARTS 03:00:00 DO INSERT INTO sales_summary_mv SELECT product_id, sale_date, SUM(amount) FROM sales GROUP BY product_id, sale_date ON DUPLICATE KEY UPDATE total_amountVALUES(total_amount);6.3 突然的性能退化现象系统运行良好数月后突然变慢 根因统计信息过期导致执行计划变化 解决方案-- 手动更新统计信息 ANALYZE TABLE orders, order_items; -- 设置自动更新 SET GLOBAL innodb_stats_auto_recalc ON;