相关推荐recommended
当Mysql缓慢时,这几招可解燃眉之急
作者:mmseoamin日期:2023-12-14

第一步定位问题源:

常见的以查询Mysql性能问题的方法

1.大部分的性能问题都是查询过慢的问题,可以查询慢sql日志。

通过慢查询日志定位那些执行效率较低的SQL语句,用–log-slow-queries[= file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。

long_query_time 可以在 my.cnf配置文件里设置

也可以直接设置 set global long_query_time=4

2.使用show processlist命令查看当前MySQL在进行的线程

包括线程的状态、是否锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。

show processlist; 只列出前100条

show full processlist;列出全部线程情况

当Mysql缓慢时,这几招可解燃眉之急,在这里插入图片描述,第1张

3.排查机器内存,磁盘,IO,网络是否资源不够

(1) linux 系统命令

(2)free 命令 显示系统内存使用情况

(3)vmstat 命令 看到整个机器的CPU,内存,IO的使用情况

(4)top 命令 实时显示各个进程的资源占用情况

二、性能优化

1.使用索引的优点

(1)没有合适的索引匹配查询导致查询慢,

(2)使用索引的优点、大大减少了服务器需要扫描的数量,

(3)索引可以帮助服务器避免排序和临时表

(4)索引可以帮助将随机I/O变为顺序I/O

2.索引的类型:

(1)普通索引,普通单列索引

(2)唯一索引,具有唯一约束,可以有多个null

(3)组合索引,多个建组成的索引,使用需要查询符合最左匹配原则。

(4)全文索引,和B-tree索引不会冲突,全文索引适用于MATCH AGAINST 操作,而不是普通的WHERE条件操作。

(5)哈希索引

3.查询语句是否语句的执行计划是否使用了合适的索引。

(1)使用explain关键字查看语句的执行计划

使用方法:explain 【查询语句】

当Mysql缓慢时,这几招可解燃眉之急,在这里插入图片描述,第2张

key 列表示用到了那个索引。

rows列表示预计要扫描多少行。

一般来说扫描的行数越少速度越快。

(2)强制使用索引

use index 关键字 建议mysql使用索引,可以指定多个索引让mysql选择。

SELECT * FROM sys_user use index(id, idx_phone) WHERE phone = '123456';

ignore index 关键字 禁止mysql使用指定的索引,防止查询语句使用错误的索引,可指定多个索引。

SELECT * FROM sys_user ignore index(id, idx_email) WHERE phone = '123456';

force index 关键字 强制mysql使用指定索引进行查询。

SELECT * FROM sys_user force index(id, idx_email) WHERE phone = '123456';

4.索引如何挑选

(1)一般常用/高流量的查询条件上建索引。

(2)按照数据的区分度建索引。同样的在where条件中,区分度越高的列上建索引查询得更快。建立组合索引时,多个列将区分度高的列排前面。

(3)按照索引的功能建索引,如果字段有唯一性约束则建唯一索引。如果有多列是常用的筛选条件,那就用组合索引

5.索引失效的情况

(1)使用 or 关键字

(2)复合索引未用左列字段;

(3)like以%开头;

(4)需要类型转换;

(5)where中索引列有运算;

(6)where中索引列使用了函数;

(7)如果mysql觉得全表扫描更快时(数据少);

6.什么时候不建议用索引:

(1)唯一性差;

(2)频繁更新的字段不用(更新索引消耗);

(3)where中不用的字段;

(4)索引使用<>时,效果一般;

7.查询优化

优化数据访问

简单衡量查询开销的三个指标:

*响应时间

*扫描行数

*返回行数

(1)确认应用程序是否检索的太多不必要的数据,减少应用程序访问行和访问列。

(2)检查多表join in 时,是否返回了全部的列,只返回需要的列即可。

(3)使用合适索引减少扫描的行数。

(4)使用覆盖索引,避免回表查询。

(5)order by 排序操作和 group by 分组操作尽量使用索引,避免生成临时表和文件排序。

8.DML性能问题*

(1)避免死锁产生。

(2)分析语句在事务中将含有锁竞争的语句放到后面去执行。

(3)能用普通索引的就不用唯一索引,唯一索引用不上 change buffer 的优化机制。

9.数据库参数的优化:

(1)尽量把表字段设置为 not null,避免影响mysql的优化

(2)innodb_buffer_pool_size:调整InnoDB存储引擎的缓存池大小。

三、紧急情况下kill掉异常线程,保证mysql整体可用。

在使用mysql时,可能会发生死锁、应用端没有释放连接、大量的慢查询,占用大量数据库连接,导致mysql连接数不够,无法对外提供服务,处于不可用的状态,这时要kill掉那些异常连接保证数据库整体可用。

KILL命令的语法格式如下:

KILL [CONNECTION | QUERY] thread_id

批量kill 慢查询的方式

select concat(‘kill ‘, id,’;’) from information_schema.processlist where time > 3;