MySQL数据库排查慢查询、死锁进程及解决方法 一、排查慢查询 1.1检查慢查询日志是否开启 1.1.1使用命令检查是否开启慢查询日志: SHOW VARIABLES LIKE 'slow_query_log';   如果是 Value 为 off 则并未开启 1.1.2开启并且查看慢查询日志: MySQL提供了慢查询日志功能,可以记录所有执行时间超过long_query_time秒的查询语句,通过分析这些慢查询可以找到数据库性能瓶颈。 启用慢查询日志,在my.cnf中添加配置: slow_query_log=1 long_query_time=1 #修改记录慢查询的阈值,默认为10s log-slow-queries=/路径/slowquery.log #slowquery.log日志文件的路径 使用mysqldumpslow分析慢查询日志,找出真正的慢查询: mysqldumpslow -s t /路径/slowquery.log 根据慢查询结果分析数据库索引使用情况、SQL调优空间等。 1.2 使用EXPLAIN来分析SQL语句的执行计划 主要步骤如下: 选择需要分析的SQL语句。 在SQL语句前添加EXPLAIN关键字,例如: EXPLAIN SELECT * FROM table WHERE condition; 执行查询。EXPLAIN不会真正执行语句,只是展示其执行计划。 查看结果集。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:过滤条件生效率。比率越高过滤效率越好,能有效减少数据读取量。 根据结果分析SQL访问数据的方式及性能瓶颈点,如是否利用索引、表连接方式等。从而优化SQL语句。多次查询对比验证是否效果明显。 二、检测死锁 使用show processlist命令查看当前请求,锁等待(锁类型)大于0的为死锁进程   找到对应的阻塞操作的进程id 使用kill强制结束死锁进程 KILL ; 分析死锁原因,修改sql或导致死锁的业务代码 三、优化建议 添加合理的索引提高查询效率 使用explain分析查询执行计划,查询是否走索引 控制事务块大小,减小锁定范围 可以将一大批次的操作拆分为多个较小的事务,从而减小每个事务持有锁的范围,降低其他事务被锁定的可能性。 调整事务 isolation level,减少锁冲突 可以将事务级别调整为较宽松的读已提交(READ COMMITED)或REPEATABLE READ等级。这些级别对锁的要求不如串行化等级严格,可以减少冲突。 优化sql,减少回表次数等 如减少排序、执行多个小查询代替一个大查询、利用索引更好地检索数据等,可以降低数据库磁盘的IO操作,同时锁定的时间也会缩短。 增加硬件资源(CPU、内存等)对冲突几率更低 总体上,通过控制事务粒度、隔离级别和SQL优化,可以有效降低数据库锁冲突的可能性,提高并发处理能力。这些措施都值得尝试,看是否可以带来可观的优化空间 ...