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优化,可以有效降低数据库锁冲突的可能性,提高并发处理能力。这些措施都值得尝试,看是否可以带来可观的优化空间 ...

使用函数或运算符处理列 当在查询条件中对列进行函数调用或进行运算时，索引通常会失效。 例如，WHERE DATE_FORMAT(column, '%Y-%m-%d') = '2023-11-10'，这种情况下，无法使用列上的索引。 使用通配符前缀 当在查询条件中使用通配符前缀（如LIKE 'prefix%'）时，通常无法使用索引。 使用OR条件 在WHERE子句中使用多个OR条件时，如果每个条件都没有适用的索引，那么索引可能会失效。 不等于条件 当使用不等于条件（!=或<>）时，索引通常会失效。 数据分布不均匀 如果索引列上的数据分布极不均匀，即某些值出现非常频繁，而其他值出现很少，索引可能会失效。这会导致查询优化器认为扫描整个表更快。 大数据表 当表非常大时，即使索引存在，查询优化器可能会选择执行全表扫描，因为扫描整个表可能比使用索引更快。 列数据类型不匹配 当查询条件中的列数据类型与索引列数据类型不匹配时，索引可能会失效。例如，对字符串列应用数值函数。 连接操作（JOIN） 在连接操作中，如果没有适用的索引来支持连接条件，索引可能会失效。 类型转换 当查询条件中的列需要进行类型转换以与索引列匹配时，索引通常会失效。 子查询 子查询可能会导致索引失效，尤其是当子查询结果集非常大时。 ...

特点 InnoDB MyISAM Memory 存储限制 64TB 有 有 事务安全 支持 – – 锁机制 行锁 表锁 表锁 B+tree索引 支持 支持 支持 Hash索引 – – 支持 全文索引 支持(5.6版本之后) 支持 – 空间使用 高 低 n/a 内存使用 高 低 中等 批量插入速度 低 高 高 支持外键 支持 – – ...