MySQL数据库排查慢查询、死锁进程及解决方法

一、排查慢查询

1.1检查慢查询日志是否开启

1.1.1使用命令检查是否开启慢查询日志:

 SHOW VARIABLES LIKE 'slow_query_log';

如果是 Value 为 off 则并未开启

1.1.2开启并且查看慢查询日志:

MySQL提供了慢查询日志功能,可以记录所有执行时间超过long_query_time秒的查询语句,通过分析这些慢查询可以找到数据库性能瓶颈。

1. 启用慢查询日志,在my.cnf中添加配置:

slow_query_log=1
long_query_time=1 #修改记录慢查询的阈值,默认为10s
log-slow-queries=/路径/slowquery.log #slowquery.log日志文件的路径

2. 使用mysqldumpslow分析慢查询日志,找出真正的慢查询:

mysqldumpslow -s t /路径/slowquery.log

3. 根据慢查询结果分析数据库索引使用情况、SQL调优空间等。

1.2 使用EXPLAIN来分析SQL语句的执行计划

主要步骤如下:

1. 选择需要分析的SQL语句。

2. 在SQL语句前添加EXPLAIN关键字,例如:

EXPLAIN SELECT * FROM table WHERE condition;

3. 执行查询。EXPLAIN不会真正执行语句,只是展示其执行计划。

4. 查看结果集。EXPLAIN查询结果将是一张表格,包含以下重要列:
   

• id: SELECT的步骤序列,从上到下执行。id值从上到下为访问顺序,上层依赖下层。

• select_type: SELECT类型,如SIMPLE或PRIMARY。 简单为PRIMARY或SIMPLE查询较好,JOIN等复杂嵌套查询效率较低。

• table:读取的数据源。

• type:连接类型或访问类型,如ALL或RANGE。ALL类型效率最低,需要全表扫描;使用索引类型为CONST、eq_ref、ref、range效率较高。

• possible_keys:可能使用的索引。若为空,说明SQL没有利用索引,需添加索引。

• key:实际使用的索引。实际使用的索引名。若使用不到索引,表明优化空间大。

• key_len:使用的索引最大长度。 索引长度短效率高,避免过长索引。

• rows:预估需要读取的行数。数值越小性能越好,多表JOIN联合查询数值过高需优化提升性能。

• filtered:过滤条件生效率。比率越高过滤效率越好,能有效减少数据读取量。

5. 根据结果分析SQL访问数据的方式及性能瓶颈点,如是否利用索引、表连接方式等。从而优化SQL语句。多次查询对比验证是否效果明显。

二、检测死锁

1. 使用show processlist命令查看当前请求,锁等待(锁类型)大于0的为死锁进程
   
   找到对应的阻塞操作的进程id

2. 使用kill强制结束死锁进程

 KILL <对应的id>;

3. 分析死锁原因,修改sql或导致死锁的业务代码

三、优化建议

1. 添加合理的索引提高查询效率

2. 使用explain分析查询执行计划,查询是否走索引

3. 控制事务块大小,减小锁定范围
   可以将一大批次的操作拆分为多个较小的事务,从而减小每个事务持有锁的范围,降低其他事务被锁定的可能性。

4. 调整事务 isolation level,减少锁冲突
   可以将事务级别调整为较宽松的读已提交(READ COMMITED)或REPEATABLE READ等级。这些级别对锁的要求不如串行化等级严格,可以减少冲突。

5. 优化sql,减少回表次数等
   如减少排序、执行多个小查询代替一个大查询、利用索引更好地检索数据等,可以降低数据库磁盘的IO操作,同时锁定的时间也会缩短。

6. 增加硬件资源(CPU、内存等)对冲突几率更低

总体上,通过控制事务粒度、隔离级别和SQL优化,可以有效降低数据库锁冲突的可能性,提高并发处理能力。这些措施都值得尝试,看是否可以带来可观的优化空间