实际工作中经常会遇到数据库操作突然变慢的情况,但是检查了各种硬件设施以及网络监控后发现都没有什么问题;这时候就要从数据库入手了,而慢SQL是我们工作中经常会遇到的影响查询性能的情况。本文将介绍如何在MySQL中获取慢SQL。
一、数据库中通过日志记录慢SQL的几个配置项
1.slow_query_log 该配置用于配置是否捕获执行时间超过一定数值的SQL语句,默认值为OFF
查询数据的配置方式为
show variables like 'slow_query_log';
开启该配置的方式有两种
#第一种为执行设置命令
set global slow_query_log='ON';
#第二种为修改 my.cnf 中的配置
slow_query_log = 1 #开启慢查询
2.long_query_time 当SQL执行时间超过该配置设置值后将被记录,该值单位为秒设置时支持设置为小数,默认值为10秒
查询数据的配置方式为
show variables like 'long_query_time';
开启该配置的方式同样也是有两种
#第一种为执行设置命令
set global long_query_time=5;
#第二种为修改 my.cnf 中的配置
long_query_time = 1 #超过的时间为1s
3.slow_query_log_file 超时时间记录的文件路径
查询配置的方式为
show variables like 'slow_query_log_file';
开启配置的方式为
#第一种为执行设置命令
set global slow_query_log_file='/var/***.log';
#第二种为修改 my.cnf 中的配置
log-slow-queries=/var/***.log
4.log_queries_not_using_indexes 当设置为 ON 时,可以捕捉到所有未使用索引的SQL语句,默认值为OFF
查询数据的配置方式为
show variables like 'log_queries_not_using_indexes';
开启配置的方式为
#第一种为执行设置命令
set global log_queries_not_using_indexes ='ON';
#第二种为修改 my.cnf 中的配置
log-queries-not-using-indexes = 1 #开启慢查询
二、日志内容分析
当开启了慢SQL日志后,可以在配置好的日志输出位置看到慢SQL的记录,查看内容的方式有两种
1.直接查看日志内容,我们可以通过直接打开文件的方式查看日志内容;但是使用此种方式就需要我们自行对日志记录的内容进行分析。日志记录结果如下:
# Time: 201110 23:40:02
# User@Host: data_base[dataUserName_rw] @ [ip] Id: 55520890
# Query_time: 0.530398 Lock_time: 0.000042 Rows_sent: 1 Rows_examined: 2021690
SET timestamp=1605022802;
SELECT count(1) FROM TABLE_NAME WHERE 1 = 1;
2.使用MySQL自带的 mysqldumpslow 命令
#常用参数说明
-s 后面接下面的参数表示 mysqldumpslow 结果显示的顺序!
c query执行的次数
t sql执行的时间
l lock锁表的时间
r sql返回的行数
#ac,at,al,ar,表示倒序排列
-t,是top n,即为返回前面n条数据
-g,后边可以写一个正则匹配模式,大小写不敏感
#语句示例
mysqldumpslow -s t -t 2 /opt/mysql/data/slowquery.log #显示执行时间最长的前两个SQL
mysqldumpslow -s c -t 2 /opt/mysql/data/slowquery.log #显示次数最多的前两个SQL
三、使用 show processlist 命令实时查询执行语句
1.Show processlist命令可以将当前正在正在执行的线程进行打印,字段说明如下
2.state 列介绍,state只是语句执行过程中的某一个状态;比如执行查询时,可能需要经过copying to tmp table,Sorting result,Sending data等状态才可以完成。状态的值主要有以下几种: