mysql数据库故障排查

对于问题的排查思路都大同小异，一般情况下可以分为下面几个步骤：

1. 复现问题：对于一个问题，首先需要明确他的复现条件，这些条件和问题现象在后面的问题排查中非常重要；
2. 分析和验证：在这个阶段，通过问题条件和显现，分析可能出现的原因，查看问题日志，再进过猜想验证，基本可以定位问题的原因；
3. 定位和解决：问题定位了，后续解决就顺理成章了，但一般问题的解决方案可能不止一个，这个就需要从修复难度、问题常见等各个方面权衡那种解决方案合适了。
4. 总结和复盘：最后一个阶段，需要总结从这个问题中学会的知识和技能，让自己的能力有更进一步，同时也要做好记录，总结经验，避免后续再出现这样的问题。

一、慢查询

思路

1. 查询慢查询日志；
2. 通过expain查询慢查询的sql原因；查询是否进入死锁状态；
3. 优化索引；解除死锁；

案例

对于不唯一字段加唯一索引，会引入全表扫描，如果数据比较多，就会形成慢查询。解决方案是对于非唯一字段加普通索引，对唯一字段加唯一索引。

操作

1.修改慢查询的两个关键参数：

slow_query_log：取值为on、off，默认为off关闭。打开慢查询：set global slow_query_log='on'; 
long_query_time：指定记录慢查询日志的阈值，单位是秒，要指定更细粒度可以用小数表示。

2.通过:show variables like ‘slow_query_log_file’ 查询慢查询日志文件目录，通过在慢日志中看到的日志信息如下：

3.慢日志产生的原因可能是进入死锁状态了，可以通过以下命令查看：show status like ‘innodb_row_lock_%’;

查询结果为：

• Innodb_row_lock_current_waits：当前正在阻塞等待锁的事务数量。
• Innodb_row_lock_time：MySQL启动到现在，所有事务总共阻塞等待的总时长。
• Innodb_row_lock_time_avg：平均每次事务阻塞等待锁时，其平均阻塞时长。
• Innodb_row_lock_time_max：MySQL启动至今，最长的一次阻塞时间。
• Innodb_row_lock_waits：MySQL启动到现在，所有事务总共阻塞等待的总次数。

除了进入死锁外，也有可能是由于索引问题导致SQL本身执行很慢，可以通过explain来排查。

二、死锁

mysql默认会开启死锁检测的功能，带死锁超时之后会自动解锁死锁。但我们再业务开发过程中不能仅依赖mysql提供的死锁检测，需要自己的业务和SQL尽量不要引入死锁才行。碰到死锁的排查思如和操作如下：

思路：

1. 通过show status查看加锁线程；
2. 找出加锁sql和加锁对象；
3. 优化sql；

案例：

在未加索引的事务中，多个事务同时按照同一字段条件进行更新操作，可能会造成死锁，原因是没有索引，在进行更新操作时会加表锁，就会阻塞其他事务的操作，形成死锁。修复的方案是对条件字段加索引，从而形成行锁，提升并发度。

操作

先通过命令判断是否存在死锁：show status like ‘innodb_row_lock_%’;

再查询死锁状态：SHOW ENGINE INNODB STATUS;查看InnoDB存储引擎的运行状态日志。

在死锁状态中可以清除看到哪个SQL导致的死锁，以及如何解开死锁；

对于线上碰到的死锁故障，可以手动回滚死锁事务临时解决，但是为了彻底解决，需要在写SQL的时候考虑避免死锁情况。

三、CPU使用率飙升

思路

1. 通难过top命令查看是否是mysqld的进程导致；
2. 通过show processlist查看session的连接时间和状态等信息；
3. 找到性能最大连接的sql，进行sql和索引优化；

操作

1. 通过top命令能找到占用率最高的进程；
2. 通过top -Hp [PID]命令查看该进程中CPU占用率最高的线程
3. 将OS中的线程id转化为Mysql层面的线程id；
4. 通过线程id就能找到正在执行的哪句正在执行的SQL占用CPU最高。

总结

Mysql中CPU飙升的排查思路和JVM中CPU飙升的排查思路差不多。首先还是通过top命令看到哪个进程的占用率最高，再通过命令看该进程下哪个线程的占用率最高，最高在Mysql中能查到OS的线程Id和Mysql的线程Id的对应关系，找到这个关系后，就能找到哪句SQL执行占用的CPU比较高。

四、磁盘IO使用率飙升

思路：

这个一般是突然大批量的对Mysql数据库进行读写，同时Buffer Pool容量不够，导致命中率不高，经常需要去读写磁盘。可以通过iotop、pstack等命令查看IO情况 。

总结：

1. 可以优化SQL对数据库的读写操作，可以进行批量查询和修改；
2. 可以提升Buffer Pool的容量，提升命中率。

---

参考资料

(十八)MySQL排查篇：该如何定位并解决线上突发的Bug与疑难杂症？：https://juejin.cn/post/7165767441883398175