线上MySQL故障排查慢SQL、死锁、连接爆满解决黒漂技术佬 · 2026年7月前言凌晨两点你的手机震了。监控系统疯狂告警无人售货柜系统数据库 CPU 98%接口大面积超时用户扫码开门开不了投诉电话打爆了客服。你爬起来打开电脑面对一台你已经看不懂的 MySQL 实例——它正在以某种你无法理解的方式罢工。这时候你的排查能力决定了你什么时候能回去睡觉。线上故障排查是每个后端的必修课。这篇把最常见的四类 MySQL 故障——慢SQL、死锁、连接爆满、CPU飙升——的排查方法和处理流程讲清楚再附一个真实案例。一、故障排查方法论1.1 四步法遇到数据库故障别慌按这个顺序来监控告警 → 快速定位 → 深入分析 → 修复恢复监控告警通过 Prometheus/Grafana/Druid 监控面板确认故障范围。是 CPU 高内存高连接数满磁盘满快速定位用SHOW PROCESSLIST、慢查询日志快速找到元凶SQL深入分析用EXPLAIN分析执行计划用SHOW ENGINE INNODB STATUS看锁信息修复恢复Kill 掉问题 SQL、加索引、限流先恢复服务再根治1.2 黄金法则先止血再治病线上故障第一优先级是恢复服务不是找根因。如果一条 SQL 把数据库搞卡了先 Kill 掉它让服务恢复再慢慢分析为什么这条 SQL 慢。别在故障现场做深度分析——用户等不了你。二、慢SQL排查2.1 开启慢查询日志慢查询日志是 MySQL 自带的慢SQL记录功能记录执行时间超过阈值的 SQL。-- 查看慢查询日志状态SHOWVARIABLESLIKEslow_query%;SHOWVARIABLESLIKElong_query_time;-- 临时开启重启失效SETGLOBALslow_query_logON;SETGLOBALslow_query_log_file/var/log/mysql/slow.log;SETGLOBALlong_query_time1;-- 超过1秒的算慢查询SETGLOBALlog_queries_not_using_indexesON;-- 记录没走索引的查询-- 永久生效在 my.cnf 中配置-- [mysqld]-- slow_query_log 1-- slow_query_log_file /var/log/mysql/slow.log-- long_query_time 1-- log_queries_not_using_indexes 1long_query_time建议生产环境设为 1 秒。设太低比如 0.1 秒日志量太大设太高比如 5 秒会漏掉一些累积型慢查询。2.2 慢查询日志长什么样# Time: 2026-07-30T02:15:33.123456Z # UserHost: root[root] [192.168.1.50] Id: 12345 # Query_time: 12.345678 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 5000000 SET timestamp1722311733; SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC LIMIT 10;关键信息Query_time: 12.34这条 SQL 执行了 12 秒Lock_time: 0.000123等锁时间通常很小Rows_sent: 10返回了 10 行Rows_examined: 5000000扫描了 500 万行问题就在这——扫描500万行只返回10行说明没用索引或索引失效2.3 用 pt-query-digest 分析慢查询日志可能几千条手动看不过来。用 Percona Toolkit 的pt-query-digest工具自动分析pt-query-digest /var/log/mysql/slow.logslow_report.txt输出按总耗时排序的 SQL 统计# Profile # Rank Query ID Response time Calls R/Call V/M # # 1 0xABC123... 1200.5 65.2% 1500 0.8003 0.05 # 2 0xDEF456... 400.2 21.7% 200 2.0010 0.15 # 3 0xGHI789... 150.3 8.2% 50 3.0060 0.20Response time这类SQL总耗时占比。排名第一的就是最需要优化的Calls执行次数R/Call平均每次执行耗时聚焦排名靠前的 SQL 优化投入产出比最高。2.4 EXPLAIN 分析执行计划找到慢SQL后用EXPLAIN看它的执行计划EXPLAINSELECT*FROMt_orderWHEREstatus1ORDERBYcreate_timeDESCLIMIT10;---------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 5000000 | Using where; Using filesort | ----------------------------------------------------------------------------------------------------------关键字段解读字段含义重点关注type访问类型ALL全表扫描是最差的ref、range、const是好的key实际使用的索引NULL表示没走索引rows预估扫描行数越小越好理想情况接近 Rows_sentExtra额外信息Using filesort文件排序、Using temporary临时表都是需要优化的信号上面这个例子的执行计划显示typeALL全表扫描、keyNULL没走索引、rows500万扫描500万行、Using filesort排序还用了文件排序。加个联合索引就能解决ALTERTABLEt_orderADDINDEXidx_status_create_time(status,create_time);加完索引后再EXPLAIN----------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ----------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | t_order | range | idx_status_create_time | idx_status_create_time | 1 | NULL | 500 | NULL | -----------------------------------------------------------------------------------------------------------------扫描行数从 500 万降到 500查询时间从 12 秒降到毫秒级。三、死锁排查3.1 死锁是什么两个事务互相持有对方需要的锁谁也不让谁MySQL 只能杀掉其中一个。事务A: 锁了商品1 → 想锁商品2 事务B: 锁了商品2 → 想锁商品1 → 死锁MySQL 检测到死锁后会自动回滚代价较小的事务修改行数少的另一个事务继续执行。所以死锁不会让数据库卡死但会导致业务报错。3.2 查看死锁信息SHOWENGINEINNODBSTATUS\G在输出的LATEST DETECTED DEADLOCK部分 LATEST DETECTED DEADLOCK 2026-07-30 02:30:15 0x7f8b2c3d4b00 *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 100, OS thread handle 0x7f8b2c3d4b00, query id 2000 192.168.1.50 root UPDATE t_product SET stock stock - 1 WHERE id 100 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table vending_machine.t_product trx id 12345 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 12346, ACTIVE 4 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 101, OS thread handle 0x7f8b2c4e5c00, query id 2001 192.168.1.51 root UPDATE t_product SET stock stock - 1 WHERE id 200 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table vending_machine.t_product trx id 12346 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 5 n bits 72 index PRIMARY of table vending_machine.t_product trx id 12346 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2)解读事务1id12345执行UPDATE ... WHERE id 100在等待 id100 的行锁事务2id12346持有某个行锁执行UPDATE ... WHERE id 200在等待 id200 的行锁MySQL 选择回滚事务23.3 死锁的常见原因和解决原因1加锁顺序不一致// 事务A先锁商品1再锁商品2deductStock(1);deductStock(2);// 事务B先锁商品2再锁商品1deductStock(2);deductStock(1);解决统一按productId升序加锁。// 统一按productId排序后再扣减ListLongsortedIdsdto.getProductIds().stream().sorted().collect(Collectors.toList());for(Longid:sortedIds){deductStock(id);}原因2唯一索引冲突两个事务同时 INSERT 相同的唯一键值一个成功一个等待如果还有其他锁依赖就可能死锁。解决用INSERT ... ON DUPLICATE KEY UPDATE替代先查后插。原因3gap lock间隙锁在REPEATABLE_READ隔离级别下范围查询会锁住间隙。两个事务在不同范围更新可能互相阻塞。解决缩小事务范围尽快提交或降低隔离级别到READ_COMMITTED。3.4 死锁后的业务处理死锁是正常现象不可能完全避免。业务代码要捕获死锁异常并重试ServiceSlf4jpublicclassOrderService{Transactional(rollbackForException.class)publicvoidplaceOrder(OrderDTOdto){try{doPlaceOrder(dto);}catch(CannotAcquireLockException|DeadlockLoserDataAccessExceptione){log.warn(死锁触发重试: {},dto.getOrderId());thrownewRetryableException(系统繁忙请重试,e);}}// 配合Spring RetryRetryable(valueRetryableException.class,maxAttempts3,backoffBackoff(delay100,multiplier2))publicvoidplaceOrderWithRetry(OrderDTOdto){placeOrder(dto);}}四、连接数爆满4.1 症状应用报错HikariPool-1 - Connection is not available, request timed out after 30000ms。MySQL 的SHOW PROCESSLIST显示大量连接处于Sleep状态连接数接近max_connections上限。4.2 排查步骤第一步看当前连接数-- 查看当前连接数SHOWSTATUSLIKEThreads_connected;-- 查看最大连接数配置SHOWVARIABLESLIKEmax_connections;-- 查看每个连接的详情SHOWPROCESSLIST;SHOW PROCESSLIST输出----------------------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ----------------------------------------------------------------------------------- | 100 | root | 192.168.1.50:54321 | vending| Sleep | 300 | | NULL | | 101 | root | 192.168.1.50:54322 | vending| Query | 15 | updating | UPDATE t_order...| | 102 | root | 192.168.1.51:54323 | vending| Sleep | 600 | | NULL | | 103 | root | 192.168.1.50:54324 | vending| Query | 45 | Sending | SELECT * FROM... | -----------------------------------------------------------------------------------关注Time执行时间。Sleep 状态 Time 很大说明连接泄漏用完没还Stateupdating、Sending data、Waiting for lock都可能是卡住的连接CommandSleep表示空闲连接。如果 Sleep 连接太多可能是连接池配置太大或连接泄漏第二步Kill 问题连接-- Kill 执行时间过长的查询KILL103;-- 批量Kill Sleep超过300秒的连接谨慎操作SELECTCONCAT(KILL ,id,;)FROMinformation_schema.processlistWHERECommandSleepANDTime300INTOOUTFILE/tmp/kill.sql;4.3 连接泄漏排查连接泄漏的典型原因手动获取 Connection 忘记 close或者Transactional方法里开了子线程用了同一个 DataSource。排查方法在 HikariCP 中开启泄漏检测spring:datasource:hikari:leak-detection-threshold:60000# 60秒未归还视为泄漏开启后连接泄漏时日志会打印堆栈直接定位到哪行代码没关连接。4.4 合理配置连接池spring:datasource:hikari:maximum-pool-size:20# 最大连接数minimum-idle:5# 最小空闲连接connection-timeout:30000# 获取连接超时30秒idle-timeout:600000# 空闲连接超时10分钟max-lifetime:1800000# 连接最大生命周期30分钟连接池大小公式PostgreSQL 官方推荐MySQL 也适用连接数 (核心数 * 2) 磁盘数4核服务器(4 * 2) 1 9连接池设 10-20 就够了。别以为连接池越大越好——连接太多反而增加 MySQL 的上下文切换开销降低吞吐量。五、CPU飙升排查5.1 定位高负载SQLCPU 飙升通常是几条 SQL 在做大量计算全表扫描、大排序、大聚合。-- 查看当前正在执行的SQL按执行时间排序SELECTid,user,host,db,command,time,state,infoFROMinformation_schema.processlistWHEREcommand!SleepORDERBYtimeDESC;-- 查看正在执行的事务SELECT*FROMinformation_schema.innodb_trxORDERBYtrx_started;重点关注time大且state为Sending data、Sorting result、Creating sort index的查询——这些状态表示 MySQL 正在做大量计算。5.2 常见CPU杀手SQL特征原因解决方案LIKE %xxx%全表扫描用全文索引或 ESORDER BY无索引filesort给排序字段加索引GROUP BY无索引临时表filesort给分组字段加索引大表COUNT(*)全表扫描用估算值或缓存计数多张大表JOIN笛卡尔积拆分查询业务层组装子查询可能全表扫描改写为JOIN5.3 临时处理-- 找到耗时最长的SQLKill掉KILL连接ID;-- 如果是批量更新导致CPU高可以限速-- 每次只更新1000条间隔0.5秒六、磁盘空间满6.1 检查磁盘占用df-h# 查看MySQL数据目录大小du-sh/var/lib/mysql/6.2 binlog 清理MySQL 的 binlog二进制日志可能占大量空间-- 查看binlog文件列表和大小SHOWBINARYLOGS;-- 查看binlog自动清理配置SHOWVARIABLESLIKEexpire_logs_days;SHOWVARIABLESLIKEbinlog_expire_logs_seconds;-- 手动清理只保留最近7天的PURGEBINARYLOGS BEFORE DATE_SUB(NOW(),INTERVAL7DAY);-- 永久配置自动清理my.cnf-- binlog_expire_logs_seconds 604800 -- 7天6.3 大表清理-- 查看各表大小SELECTtable_name,ROUND(data_length/1024/1024,2)ASdata_mb,ROUND(index_length/1024/1024,2)ASindex_mb,ROUND((data_lengthindex_length)/1024/1024,2)AStotal_mb,table_rowsFROMinformation_schema.tablesWHEREtable_schemavending_machineORDERBYtotal_mbDESC;清理大表数据时注意DELETE FROM t_log WHERE create_time 2026-01-01大量删除会产生大量 binlog且不会释放磁盘空间只是标记为可复用用pt-archiver分批删除避免锁表和 binlog 暴涨如果要删除大部分数据考虑新建表→导出保留数据→重命名替换七、数据丢失恢复binlog 恢复7.1 场景运维误执行了DELETE FROM t_order WHERE status 1把所有已支付订单删了。没有备份怎么办binlog 可以救你。7.2 前提条件MySQL 开启了 binloglog_bin ONbinlog 格式为ROWbinlog_format ROW7.3 恢复步骤第一步找到误操作的 binlog 位置-- 查看binlog文件SHOWBINARYLOGS;-- 查看指定binlog的内容找到误操作的时间点SHOWBINLOG EVENTSINmysql-bin.000123LIMIT100;第二步用 mysqlbinlog 工具解析# 解析binlog找到误删除操作的起始和结束位置mysqlbinlog --base64-outputdecode-rows-v\--start-datetime2026-07-30 02:00:00\--stop-datetime2026-07-30 02:10:00\mysql-bin.000123binlog_output.txt第三步用闪回工具反向生成恢复SQL开源工具binlog2sql可以把 binlog 中的 DELETE 操作反向生成 INSERT# 生成反向SQLpython binlog2sql.py-h127.0.0.1-P3306-uroot-p123456\-dvending_machine-tt_order\--start-file mysql-bin.000123\--start-datetime2026-07-30 02:00:00\--stop-datetime2026-07-30 02:10:00\-Brollback.sql# 执行恢复mysql-uroot-p123456rollback.sql-B参数表示生成反向 SQLDELETE → INSERTINSERT → DELETEUPDATE → 反向UPDATE。八、线上故障排查 checklist把下面的 checklist 贴在工位上出故障时照着走[ ] 1. 确认故障范围是单台还是集群是数据库还是应用 [ ] 2. 查看监控CPU、内存、磁盘、连接数、QPS [ ] 3. SHOW PROCESSLIST有没有长时间运行的SQL [ ] 4. 慢查询日志最近有没有新增慢SQL [ ] 5. SHOW ENGINE INNODB STATUS有没有死锁 [ ] 6. 查看错误日志/var/log/mysql/error.log [ ] 7. 确认最近的变更有没有上线、加索引、改配置 [ ] 8. 止血Kill问题SQL / 降级 / 限流 / 回滚 [ ] 9. 根因分析EXPLAIN慢SQL / 分析死锁日志 [ ] 10. 修复验证加索引 / 修复SQL / 调整配置 [ ] 11. 复盘文档记录时间线、原因、改进措施九、实战案例无人售货柜高峰期CPU飙升9.1 故障背景某无人售货柜品牌200 台设备分布在全国。早高峰 7:00-9:00 用户集中扫码购物某天早上 7:30 开始 MySQL CPU 从 30% 飙到 95%接口响应时间从 100ms 飙到 5 秒大量用户扫码后打不开柜门。9.2 排查过程第一步SHOW PROCESSLISTSHOWPROCESSLIST;发现 15 条SELECT查询每条 Time 都在 3-5 秒State 都是Sending data。SQL 大致是SELECT*FROMt_orderWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;第二步EXPLAINEXPLAINSELECT*FROMt_orderWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;结果typerefkeyidx_user_idrows50000ExtraUsing filesort。走了idx_user_id索引但扫描了 5 万行这个用户下了 5 万单不对而且Using filesort表示排序没走索引。第三步分析根因原来idx_user_id只索引了user_id查到 5 万条记录后在内存中按create_time排序再取前 20 条。单个用户不会有 5 万单但系统有一个虚拟用户游客模式的 user_id10086所有未注册用户的订单都挂在它名下积攒了 5 万条订单。早高峰大量游客扫码购物都查这个虚拟用户的订单列表每条查询扫 5 万行 filesort并发一上来 CPU 就爆了。9.3 修复止血临时 Kill 掉这些查询把游客模式的订单查询接口降级返回空列表。根治加联合索引ALTERTABLEt_orderADDINDEXidx_user_create(user_id,create_time);加完后EXPLAINtyperefkeyidx_user_createrows20ExtraNULL。扫描行数从 5 万降到 20filesort 消失。进一步优化游客模式不再查询订单历史反正游客不关心历史订单只有注册用户才查。从业务层面消除了这个高频查询。9.4 经验教训联合索引的顺序很重要(user_id, create_time)比(user_id) filesort 快几个数量级虚拟用户是隐藏炸弹把大量数据挂在同一个虚拟用户下等于制造了一个热点慢查询日志要提前开这次靠 PROCESSLIST 现场抓SQL如果有慢查询日志pt-query-digest能更快定位监控要覆盖扫描行数不只看响应时间还要看Rows_examined扫描行数异常增长是慢查询的先兆总结线上 MySQL 故障排查的核心要点四步法监控→定位→分析→修复先止血再治病慢SQL开启慢查询日志 pt-query-digest分析 EXPLAIN优化死锁SHOW ENGINE INNODB STATUS看死锁日志统一加锁顺序预防连接爆满SHOW PROCESSLIST找问题连接Kill 排查泄漏CPU飙升定位高负载SQL加索引消除全表扫描和filesort磁盘满清理 binlog、清理大表数据数据恢复binlog binlog2sql 闪回工具故障 checklist贴在工位上照着走不慌排查能力是练出来的。平时多看SHOW PROCESSLIST、多用EXPLAIN分析自己的SQL出故障时才能手到擒来。 10-线上MySQL故障排查：慢SQL、死锁、连接爆满解决
线上MySQL故障排查慢SQL、死锁、连接爆满解决黒漂技术佬 · 2026年7月前言凌晨两点你的手机震了。监控系统疯狂告警无人售货柜系统数据库 CPU 98%接口大面积超时用户扫码开门开不了投诉电话打爆了客服。你爬起来打开电脑面对一台你已经看不懂的 MySQL 实例——它正在以某种你无法理解的方式罢工。这时候你的排查能力决定了你什么时候能回去睡觉。线上故障排查是每个后端的必修课。这篇把最常见的四类 MySQL 故障——慢SQL、死锁、连接爆满、CPU飙升——的排查方法和处理流程讲清楚再附一个真实案例。一、故障排查方法论1.1 四步法遇到数据库故障别慌按这个顺序来监控告警 → 快速定位 → 深入分析 → 修复恢复监控告警通过 Prometheus/Grafana/Druid 监控面板确认故障范围。是 CPU 高内存高连接数满磁盘满快速定位用SHOW PROCESSLIST、慢查询日志快速找到元凶SQL深入分析用EXPLAIN分析执行计划用SHOW ENGINE INNODB STATUS看锁信息修复恢复Kill 掉问题 SQL、加索引、限流先恢复服务再根治1.2 黄金法则先止血再治病线上故障第一优先级是恢复服务不是找根因。如果一条 SQL 把数据库搞卡了先 Kill 掉它让服务恢复再慢慢分析为什么这条 SQL 慢。别在故障现场做深度分析——用户等不了你。二、慢SQL排查2.1 开启慢查询日志慢查询日志是 MySQL 自带的慢SQL记录功能记录执行时间超过阈值的 SQL。-- 查看慢查询日志状态SHOWVARIABLESLIKEslow_query%;SHOWVARIABLESLIKElong_query_time;-- 临时开启重启失效SETGLOBALslow_query_logON;SETGLOBALslow_query_log_file/var/log/mysql/slow.log;SETGLOBALlong_query_time1;-- 超过1秒的算慢查询SETGLOBALlog_queries_not_using_indexesON;-- 记录没走索引的查询-- 永久生效在 my.cnf 中配置-- [mysqld]-- slow_query_log 1-- slow_query_log_file /var/log/mysql/slow.log-- long_query_time 1-- log_queries_not_using_indexes 1long_query_time建议生产环境设为 1 秒。设太低比如 0.1 秒日志量太大设太高比如 5 秒会漏掉一些累积型慢查询。2.2 慢查询日志长什么样# Time: 2026-07-30T02:15:33.123456Z # UserHost: root[root] [192.168.1.50] Id: 12345 # Query_time: 12.345678 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 5000000 SET timestamp1722311733; SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC LIMIT 10;关键信息Query_time: 12.34这条 SQL 执行了 12 秒Lock_time: 0.000123等锁时间通常很小Rows_sent: 10返回了 10 行Rows_examined: 5000000扫描了 500 万行问题就在这——扫描500万行只返回10行说明没用索引或索引失效2.3 用 pt-query-digest 分析慢查询日志可能几千条手动看不过来。用 Percona Toolkit 的pt-query-digest工具自动分析pt-query-digest /var/log/mysql/slow.logslow_report.txt输出按总耗时排序的 SQL 统计# Profile # Rank Query ID Response time Calls R/Call V/M # # 1 0xABC123... 1200.5 65.2% 1500 0.8003 0.05 # 2 0xDEF456... 400.2 21.7% 200 2.0010 0.15 # 3 0xGHI789... 150.3 8.2% 50 3.0060 0.20Response time这类SQL总耗时占比。排名第一的就是最需要优化的Calls执行次数R/Call平均每次执行耗时聚焦排名靠前的 SQL 优化投入产出比最高。2.4 EXPLAIN 分析执行计划找到慢SQL后用EXPLAIN看它的执行计划EXPLAINSELECT*FROMt_orderWHEREstatus1ORDERBYcreate_timeDESCLIMIT10;---------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | t_order | ALL | NULL | NULL | NULL | NULL | 5000000 | Using where; Using filesort | ----------------------------------------------------------------------------------------------------------关键字段解读字段含义重点关注type访问类型ALL全表扫描是最差的ref、range、const是好的key实际使用的索引NULL表示没走索引rows预估扫描行数越小越好理想情况接近 Rows_sentExtra额外信息Using filesort文件排序、Using temporary临时表都是需要优化的信号上面这个例子的执行计划显示typeALL全表扫描、keyNULL没走索引、rows500万扫描500万行、Using filesort排序还用了文件排序。加个联合索引就能解决ALTERTABLEt_orderADDINDEXidx_status_create_time(status,create_time);加完索引后再EXPLAIN----------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ----------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | t_order | range | idx_status_create_time | idx_status_create_time | 1 | NULL | 500 | NULL | -----------------------------------------------------------------------------------------------------------------扫描行数从 500 万降到 500查询时间从 12 秒降到毫秒级。三、死锁排查3.1 死锁是什么两个事务互相持有对方需要的锁谁也不让谁MySQL 只能杀掉其中一个。事务A: 锁了商品1 → 想锁商品2 事务B: 锁了商品2 → 想锁商品1 → 死锁MySQL 检测到死锁后会自动回滚代价较小的事务修改行数少的另一个事务继续执行。所以死锁不会让数据库卡死但会导致业务报错。3.2 查看死锁信息SHOWENGINEINNODBSTATUS\G在输出的LATEST DETECTED DEADLOCK部分 LATEST DETECTED DEADLOCK 2026-07-30 02:30:15 0x7f8b2c3d4b00 *** (1) TRANSACTION: TRANSACTION 12345, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 100, OS thread handle 0x7f8b2c3d4b00, query id 2000 192.168.1.50 root UPDATE t_product SET stock stock - 1 WHERE id 100 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table vending_machine.t_product trx id 12345 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 12346, ACTIVE 4 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 101, OS thread handle 0x7f8b2c4e5c00, query id 2001 192.168.1.51 root UPDATE t_product SET stock stock - 1 WHERE id 200 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table vending_machine.t_product trx id 12346 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 50 page no 5 n bits 72 index PRIMARY of table vending_machine.t_product trx id 12346 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2)解读事务1id12345执行UPDATE ... WHERE id 100在等待 id100 的行锁事务2id12346持有某个行锁执行UPDATE ... WHERE id 200在等待 id200 的行锁MySQL 选择回滚事务23.3 死锁的常见原因和解决原因1加锁顺序不一致// 事务A先锁商品1再锁商品2deductStock(1);deductStock(2);// 事务B先锁商品2再锁商品1deductStock(2);deductStock(1);解决统一按productId升序加锁。// 统一按productId排序后再扣减ListLongsortedIdsdto.getProductIds().stream().sorted().collect(Collectors.toList());for(Longid:sortedIds){deductStock(id);}原因2唯一索引冲突两个事务同时 INSERT 相同的唯一键值一个成功一个等待如果还有其他锁依赖就可能死锁。解决用INSERT ... ON DUPLICATE KEY UPDATE替代先查后插。原因3gap lock间隙锁在REPEATABLE_READ隔离级别下范围查询会锁住间隙。两个事务在不同范围更新可能互相阻塞。解决缩小事务范围尽快提交或降低隔离级别到READ_COMMITTED。3.4 死锁后的业务处理死锁是正常现象不可能完全避免。业务代码要捕获死锁异常并重试ServiceSlf4jpublicclassOrderService{Transactional(rollbackForException.class)publicvoidplaceOrder(OrderDTOdto){try{doPlaceOrder(dto);}catch(CannotAcquireLockException|DeadlockLoserDataAccessExceptione){log.warn(死锁触发重试: {},dto.getOrderId());thrownewRetryableException(系统繁忙请重试,e);}}// 配合Spring RetryRetryable(valueRetryableException.class,maxAttempts3,backoffBackoff(delay100,multiplier2))publicvoidplaceOrderWithRetry(OrderDTOdto){placeOrder(dto);}}四、连接数爆满4.1 症状应用报错HikariPool-1 - Connection is not available, request timed out after 30000ms。MySQL 的SHOW PROCESSLIST显示大量连接处于Sleep状态连接数接近max_connections上限。4.2 排查步骤第一步看当前连接数-- 查看当前连接数SHOWSTATUSLIKEThreads_connected;-- 查看最大连接数配置SHOWVARIABLESLIKEmax_connections;-- 查看每个连接的详情SHOWPROCESSLIST;SHOW PROCESSLIST输出----------------------------------------------------------------------------------- | Id | User | Host | db | Command | Time | State | Info | ----------------------------------------------------------------------------------- | 100 | root | 192.168.1.50:54321 | vending| Sleep | 300 | | NULL | | 101 | root | 192.168.1.50:54322 | vending| Query | 15 | updating | UPDATE t_order...| | 102 | root | 192.168.1.51:54323 | vending| Sleep | 600 | | NULL | | 103 | root | 192.168.1.50:54324 | vending| Query | 45 | Sending | SELECT * FROM... | -----------------------------------------------------------------------------------关注Time执行时间。Sleep 状态 Time 很大说明连接泄漏用完没还Stateupdating、Sending data、Waiting for lock都可能是卡住的连接CommandSleep表示空闲连接。如果 Sleep 连接太多可能是连接池配置太大或连接泄漏第二步Kill 问题连接-- Kill 执行时间过长的查询KILL103;-- 批量Kill Sleep超过300秒的连接谨慎操作SELECTCONCAT(KILL ,id,;)FROMinformation_schema.processlistWHERECommandSleepANDTime300INTOOUTFILE/tmp/kill.sql;4.3 连接泄漏排查连接泄漏的典型原因手动获取 Connection 忘记 close或者Transactional方法里开了子线程用了同一个 DataSource。排查方法在 HikariCP 中开启泄漏检测spring:datasource:hikari:leak-detection-threshold:60000# 60秒未归还视为泄漏开启后连接泄漏时日志会打印堆栈直接定位到哪行代码没关连接。4.4 合理配置连接池spring:datasource:hikari:maximum-pool-size:20# 最大连接数minimum-idle:5# 最小空闲连接connection-timeout:30000# 获取连接超时30秒idle-timeout:600000# 空闲连接超时10分钟max-lifetime:1800000# 连接最大生命周期30分钟连接池大小公式PostgreSQL 官方推荐MySQL 也适用连接数 (核心数 * 2) 磁盘数4核服务器(4 * 2) 1 9连接池设 10-20 就够了。别以为连接池越大越好——连接太多反而增加 MySQL 的上下文切换开销降低吞吐量。五、CPU飙升排查5.1 定位高负载SQLCPU 飙升通常是几条 SQL 在做大量计算全表扫描、大排序、大聚合。-- 查看当前正在执行的SQL按执行时间排序SELECTid,user,host,db,command,time,state,infoFROMinformation_schema.processlistWHEREcommand!SleepORDERBYtimeDESC;-- 查看正在执行的事务SELECT*FROMinformation_schema.innodb_trxORDERBYtrx_started;重点关注time大且state为Sending data、Sorting result、Creating sort index的查询——这些状态表示 MySQL 正在做大量计算。5.2 常见CPU杀手SQL特征原因解决方案LIKE %xxx%全表扫描用全文索引或 ESORDER BY无索引filesort给排序字段加索引GROUP BY无索引临时表filesort给分组字段加索引大表COUNT(*)全表扫描用估算值或缓存计数多张大表JOIN笛卡尔积拆分查询业务层组装子查询可能全表扫描改写为JOIN5.3 临时处理-- 找到耗时最长的SQLKill掉KILL连接ID;-- 如果是批量更新导致CPU高可以限速-- 每次只更新1000条间隔0.5秒六、磁盘空间满6.1 检查磁盘占用df-h# 查看MySQL数据目录大小du-sh/var/lib/mysql/6.2 binlog 清理MySQL 的 binlog二进制日志可能占大量空间-- 查看binlog文件列表和大小SHOWBINARYLOGS;-- 查看binlog自动清理配置SHOWVARIABLESLIKEexpire_logs_days;SHOWVARIABLESLIKEbinlog_expire_logs_seconds;-- 手动清理只保留最近7天的PURGEBINARYLOGS BEFORE DATE_SUB(NOW(),INTERVAL7DAY);-- 永久配置自动清理my.cnf-- binlog_expire_logs_seconds 604800 -- 7天6.3 大表清理-- 查看各表大小SELECTtable_name,ROUND(data_length/1024/1024,2)ASdata_mb,ROUND(index_length/1024/1024,2)ASindex_mb,ROUND((data_lengthindex_length)/1024/1024,2)AStotal_mb,table_rowsFROMinformation_schema.tablesWHEREtable_schemavending_machineORDERBYtotal_mbDESC;清理大表数据时注意DELETE FROM t_log WHERE create_time 2026-01-01大量删除会产生大量 binlog且不会释放磁盘空间只是标记为可复用用pt-archiver分批删除避免锁表和 binlog 暴涨如果要删除大部分数据考虑新建表→导出保留数据→重命名替换七、数据丢失恢复binlog 恢复7.1 场景运维误执行了DELETE FROM t_order WHERE status 1把所有已支付订单删了。没有备份怎么办binlog 可以救你。7.2 前提条件MySQL 开启了 binloglog_bin ONbinlog 格式为ROWbinlog_format ROW7.3 恢复步骤第一步找到误操作的 binlog 位置-- 查看binlog文件SHOWBINARYLOGS;-- 查看指定binlog的内容找到误操作的时间点SHOWBINLOG EVENTSINmysql-bin.000123LIMIT100;第二步用 mysqlbinlog 工具解析# 解析binlog找到误删除操作的起始和结束位置mysqlbinlog --base64-outputdecode-rows-v\--start-datetime2026-07-30 02:00:00\--stop-datetime2026-07-30 02:10:00\mysql-bin.000123binlog_output.txt第三步用闪回工具反向生成恢复SQL开源工具binlog2sql可以把 binlog 中的 DELETE 操作反向生成 INSERT# 生成反向SQLpython binlog2sql.py-h127.0.0.1-P3306-uroot-p123456\-dvending_machine-tt_order\--start-file mysql-bin.000123\--start-datetime2026-07-30 02:00:00\--stop-datetime2026-07-30 02:10:00\-Brollback.sql# 执行恢复mysql-uroot-p123456rollback.sql-B参数表示生成反向 SQLDELETE → INSERTINSERT → DELETEUPDATE → 反向UPDATE。八、线上故障排查 checklist把下面的 checklist 贴在工位上出故障时照着走[ ] 1. 确认故障范围是单台还是集群是数据库还是应用 [ ] 2. 查看监控CPU、内存、磁盘、连接数、QPS [ ] 3. SHOW PROCESSLIST有没有长时间运行的SQL [ ] 4. 慢查询日志最近有没有新增慢SQL [ ] 5. SHOW ENGINE INNODB STATUS有没有死锁 [ ] 6. 查看错误日志/var/log/mysql/error.log [ ] 7. 确认最近的变更有没有上线、加索引、改配置 [ ] 8. 止血Kill问题SQL / 降级 / 限流 / 回滚 [ ] 9. 根因分析EXPLAIN慢SQL / 分析死锁日志 [ ] 10. 修复验证加索引 / 修复SQL / 调整配置 [ ] 11. 复盘文档记录时间线、原因、改进措施九、实战案例无人售货柜高峰期CPU飙升9.1 故障背景某无人售货柜品牌200 台设备分布在全国。早高峰 7:00-9:00 用户集中扫码购物某天早上 7:30 开始 MySQL CPU 从 30% 飙到 95%接口响应时间从 100ms 飙到 5 秒大量用户扫码后打不开柜门。9.2 排查过程第一步SHOW PROCESSLISTSHOWPROCESSLIST;发现 15 条SELECT查询每条 Time 都在 3-5 秒State 都是Sending data。SQL 大致是SELECT*FROMt_orderWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;第二步EXPLAINEXPLAINSELECT*FROMt_orderWHEREuser_id10086ORDERBYcreate_timeDESCLIMIT20;结果typerefkeyidx_user_idrows50000ExtraUsing filesort。走了idx_user_id索引但扫描了 5 万行这个用户下了 5 万单不对而且Using filesort表示排序没走索引。第三步分析根因原来idx_user_id只索引了user_id查到 5 万条记录后在内存中按create_time排序再取前 20 条。单个用户不会有 5 万单但系统有一个虚拟用户游客模式的 user_id10086所有未注册用户的订单都挂在它名下积攒了 5 万条订单。早高峰大量游客扫码购物都查这个虚拟用户的订单列表每条查询扫 5 万行 filesort并发一上来 CPU 就爆了。9.3 修复止血临时 Kill 掉这些查询把游客模式的订单查询接口降级返回空列表。根治加联合索引ALTERTABLEt_orderADDINDEXidx_user_create(user_id,create_time);加完后EXPLAINtyperefkeyidx_user_createrows20ExtraNULL。扫描行数从 5 万降到 20filesort 消失。进一步优化游客模式不再查询订单历史反正游客不关心历史订单只有注册用户才查。从业务层面消除了这个高频查询。9.4 经验教训联合索引的顺序很重要(user_id, create_time)比(user_id) filesort 快几个数量级虚拟用户是隐藏炸弹把大量数据挂在同一个虚拟用户下等于制造了一个热点慢查询日志要提前开这次靠 PROCESSLIST 现场抓SQL如果有慢查询日志pt-query-digest能更快定位监控要覆盖扫描行数不只看响应时间还要看Rows_examined扫描行数异常增长是慢查询的先兆总结线上 MySQL 故障排查的核心要点四步法监控→定位→分析→修复先止血再治病慢SQL开启慢查询日志 pt-query-digest分析 EXPLAIN优化死锁SHOW ENGINE INNODB STATUS看死锁日志统一加锁顺序预防连接爆满SHOW PROCESSLIST找问题连接Kill 排查泄漏CPU飙升定位高负载SQL加索引消除全表扫描和filesort磁盘满清理 binlog、清理大表数据数据恢复binlog binlog2sql 闪回工具故障 checklist贴在工位上照着走不慌排查能力是练出来的。平时多看SHOW PROCESSLIST、多用EXPLAIN分析自己的SQL出故障时才能手到擒来。