MySQL数据库常见问题分析及解决方案【经验贴】

一、按影响范围划分

1.1影响所有交易

1.show engine innodb status看到大量线程出于sleeping before entering InnoDB,有大量queries in queue。
可能的问题：存在较多慢SQL，占用数据库服务资源，影响其他普通SQL。
解决办法:show processlist过滤出非sleep状态、Time时间较长的SQL可能是主因
2.观察到有TRUNCATE大表/分区
可能的问题：truncate大表/分区引起整库性能下降
解决办法：通过DROP TABLE规避
3. 观察到有CREATE/DROP大量分区的表
可能的问题：新增/删除有大量分区的表引起的整库性能下降
解决办法：停机期间做这些操作，不要在对外服务期间操作
4. 远程执行SQL很缓慢，但在数据库本地执行很快
可能的问题：网络延迟大，测试服务端与应用端的时延
解决办法：避免长距离操作，监控网络状态

1.2影响部分交易

5.观察到部分交易执行缓慢
可能的问题：没有匹配索引，执行计划差
解决方法：检查主要过滤条件是否建立索引，再按照无法匹配索引的情况进行检查

1.3影响更新类交易

6.大量交易阻塞，处于query end的状态

可能的问题	解决办法
大事务	将大事务拆分为小事务
IO调度算法或其他存储性能问题	检查IO调度算法、如无问题进一步检查存储
事务处于Waiting for semi-sync ACK from slave状态，可能是主备同步异常断开	必要条件下降级为异步模式
数据库在大批量清理binlog	减少一次性清理binlog个数

7.SQL等待x秒后报错：lock wait timeout exceed行锁超时

可能的问题	解决办法
多线程并发更新冲突	检查应用设计
有长事务未commit	检查涉及的事务

8.Deadlock found when trying to get lock;try restarting transation 即死锁
可能的问题：存在满足死锁的3要素的程序
解决方法：排查
9.写入完全卡死，df -h看到数据盘满
可能的问题：binlog或数据量暴涨导致磁盘满
解决方法：直接删除不再使用的binlog（并更新binlog的index文件），或磁盘扩容
10.大批量数据写入缓慢，但单条SQL执行没有明显效率
可能的问题：commit频率过高
解决办法：每几百笔commit一次
11.运行缓慢 SQL有子查询
可能的问题：带子查询的更新交易存在性能问题
解决办法：改为表连接方式

1.5影响某张表

12.读写交易堆积，大量交易处于Waiting for table metadata lock状态，期间该表有新增索引的操作

可能的问题	解决方法
建新索引前删除了旧索引	先建新索引，再删除旧索引
该表有慢SQL或长事务未结束	先解决慢SQL或长事务问题

13.更新交易堆积

可能的问题	解决方法
MyISAM引擎	使用InnoDB表
在repeateble-read事务隔离级别下更新数据，没有匹配索引，全局扫	使用read-committed事务隔离级别，或优化SQL避免全表扫

14.SQL等待半小时后报错：Lock wait timeout exceed
可能的问题：元数据锁
解决方法：如果是普通sql，检查期间是否有ddl操作；如果是ddl报错，检查是否有慢sql或长事务，或其他ddl

1.6影响ddl执行

15.大表执行缓慢
可能的问题：触发重建表操作，数据量较大
解决方法：安排足够停机时间执行ddl
数据库压力不大|IO调度算法或其他性能问题|排查