MySQL高级性能优化---慢日志查询

本文围绕MySQL慢查询日志展开,介绍其是记录响应时间超阈值语句的日志,默认不开启。详细说明了查询是否开启、开启慢查询、配置long_query_time参数的方法,还提及慢日志分析工具及Show Profile工具的使用,包括查看支持情况、打开、查看结果、诊断SQL等。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1. 概述

MySQL的慢查询日志时MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句。
具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。
long_query_time的默认值为10,意思是运行10s以上的语句。就会被认作是慢查询。
默认情况下,mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表,
在这里插入图片描述

2. 查询是否开启慢查询日志

show variables like '%slow_query_log%';

在这里插入图片描述
参数:
slow_query_log 慢查询是否开启 OFF 关闭 ON开启
slow_query_log_file 日志文件位置

3. 开启慢查询

set global slow_query_log=1;

在这里插入图片描述
这种方式只对当前数据库有效,如果重启后,则会失效

如果想永久生效,在my.cnf文件加上以下配置

slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/docker-slow.log

4. 配置long_query_time参数

查询long_query_time当前阀值,默认是10秒

show variables like 'long_query_time';

在这里插入图片描述
更改long_query_time的阀值时间

set global long_query_time=4;

设置完查询long_query_time的值还是10s,这个参数断开连接之后才会生效
在这里插入图片描述
重新连接之后long_query_time值为4秒
在这里插入图片描述

测试以下慢查询功能

select sleep(5);

可以通过 show global status like ‘%slow_queries%’;这个语句查看日志文件中记录了几条慢查询sql

 show global status like '%slow_queries%';

在这里插入图片描述

慢查询日志文件在第二步已经获取到/usr/local/mysql/data/docker-slow.log
查看慢查询日志文件

cat /usr/local/mysql/data/docker-slow.log

在这里插入图片描述

5. 慢日志分析工具

在这里插入图片描述

参数说明
-s按照哪种方式排序
c访问计数
l锁定时间
r返回记录
al平均锁定时间
ar平均访问记录数
at平均查询时间
-t是top n的意思,返回多少条数据
-g可以跟上正则匹配模式,大小写不敏感

5.1 得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /usr/local/mysql/data/docker-slow.log

在这里插入图片描述

6. Show Profile

6.1 Show Profile概述

Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具,可用于sql调优的测量。
默认情况下处于关闭状态,并保存最近15次的运行结果。把一条sql在mysql当中每一个环节耗费的时间都记录下来。

6.2 Show Profile使用

6.2.1 查看当前版本是否支持

Show variables like 'profiling';

默认是关闭状态
在这里插入图片描述

6.2.3 打开profile

set profiling=on;

在这里插入图片描述

6.2.4 查看结果

先写一条sql并执行

SELECT * from employee WHERE id > 100000

查看执行结果

show profiles;

在这里插入图片描述

6.2.5 诊断sql

这里的数字3是在上一步show profiles的时候sql对应的Query_ID

show profile cpu,block io for query 3

在这里插入图片描述

6.2.6 诊断结果出现以下选项时,要进行优化

  1. converting HEAP to MyIsAM 查询结果太大,内存不够用了,往磁盘上存上了
  2. Creating tmp table 创建临时表, copy数据到临时表用完再进行删除
  3. Copying to tmp table on disk 把内存中临时表复制到磁盘,危险操作
  4. Locked 被锁定
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值