MySQL-慢查询日志分析

慢日志查询

概述

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

查询日志相关参数

查看是否开启慢查询日志

SHOW VARIABLES LIKE '%slow_query_log%'

开启

SET GLOBAL slow_query_log = 1;

只对当前数据库生效, 如果重启后, 则会失效,如果永久生效,必须要修改配置文件

slow_query_log = 1
slow_query_log_file = 地址

那么开启了慢查询日志后,什么样的SQL才会记录到慢查询日志里面呢?

SHOW VARIABLES LIKE 'long_query_time'
SET GLOBAL long_query_time = 4;

要断开连接后, 才能生效

SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SELECT sleep(4)
SHOW GLOBAL STATUS LIKE '%slow_queries%';

慢日志分析工具

参数

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

示例

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

mysqldumpslow - s r - t 10 / DATABASE / mysql / mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow - s c - t 10 / DATABASE / mysql / mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow - s t - t 10 - g “left join” / DATABASE / mysql / mysql06_slow.log

另外建议在使用这些命令时结合 | 和 more 使用,否则有可能出现刷屏的情况

mysqldumpslow - s r - t 20 / mysqldata / mysql / mysql06 - slow.log | more

Show Profile分析

概述

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

使用

  1. 查看当前版本是否支持
SHOW VARIABLES LIKE 'profiling';
  1. 打开 profile
SET profiling = ON
  1. 查看结果
SHOW PROFILES
  1. 诊断SQL
SHOW PROFILE cpu,block io FOR QUERY 88;

当出现以下选项时, 要进行优化

  • converting HEAP to MyIsAM,查询结果太大,内存都不够用了,往确磁盘上存在了
  • Creating tmp table,创建临时表,copy数据到临时表,用完再进行删除
  • Copying to tmp table on disk,把内存中临时表复制到磁盘, 危险操作
  • Locked,被锁定

全局查询日志

只允许在测试环境用, 不能在生产环境使用,在测试是可以把所有执行的SQL抓取出来查看

设置启用

SET GLOBAL general_log = 1;
SET GLOBAL log_output = 'TABLE'

此后所编写的SQL语句将会记录到mysql库里的 general_log 表中

SELECT * FROM mysql.general_log;
mysql - u root - p1234 -- DEFAULT - CHARACTER -SET = utf8
posted @ 2020-07-17 18:44  Leader_TBlog  阅读(135)  评论(0编辑  收藏  举报