首页 > 数据库 >快速学会慢查询SQL排查

快速学会慢查询SQL排查

时间:2022-12-08 22:15:27浏览次数:64  
标签:slow log SQL 查询 排查 MySQL query

转载请注明出处❤️

作者:测试蔡坨坨

原文链接:caituotuo.top/c56bd0c5.html


你好,我是测试蔡坨坨。

在往期文章中,我们聊过数据库基础知识,可参考「数据库基础,看完这篇就够了!」。

学完数据库基础知识,要想更深入地了解数据库,就需要学习数据库进阶知识,今天我们就先来聊一聊慢SQL查询那些事儿。

在日常工作中,我们经常会遇到数据库慢查询问题,那么我们要如何进行排查呢?

假设一次执行20条SQL,我们如何判断哪条SQL是执行慢的烂SQL,这里就需要用到慢查询日志

在SQL中,广义的查询就是CRUD操作,而狭义的查询仅仅是SELECT查询操作,而我们所说的慢查询其实指的是广义的查询,包括增删改查,一般是查询,所以称为慢查询。

什么是慢查询日志

MySQL提供的一种日志记录,用于记录MySQL中响应时间超过阈值[yù zhí]的SQL语句(也就是long_query_time的值,默认时间是10秒)。

慢查询日志默认是关闭的,开启会消耗一定的性能,一般是开发调优时打开,而部署时会关闭。

检查是否开启了慢查询日志

执行语句:

show variables like '%slow_query_log';

从以下执行结果可以看出,慢查询日志默认是OFF关闭状态:

如何开启慢查询日志

开启慢查询日志有两种方式,分别是临时开启永久开启

临时开启

一般使用临时开启,即在内存中开启,MySQL退出就会自动关闭,从而避免过多的性能开销:

set global slow_query_log = 1;  // 1表示开启
set global slow_query_log_file="D:/MySQL Server 5.5/slow_query_log.log"; // 日志存储位置

临时开启,重启MySQL服务后慢查询日志会变成OFF状态:

Linux重启MySQL服务:

service mysql restart

Windows重启MySQL服务:

右击开始菜单——计算机管理——找到MySQL服务——重启:

重启后的可以看到慢查询日志变成OFF关闭状态:

永久开启

通过修改配置文件的方式可以永久开启慢查询日志。

Linux:在 /etc/my.cnf 中追加配置

vi /etc/my.cnf

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow-query-log.log

Windows:D:\MySQL Server 5.5\my.ini

[mysqld]
slow_query_log=1
slow_query_log_file="D:/MySQL Server 5.5/slow_query_log.log"

永久开启后,即使重启MySQL服务,慢查询日志也不会关闭:

慢查询阈值修改

查看默认值
show variables like '%long_query_time%';

可以看到默认值是10s:

临时设置阈值

设置完毕后,需要重新登录MySQL才能生效(注意是重新登录MySQL,不是重启MySQL服务)。

set global long_query_time = 5;

永久设置阈值

通过修改配置文件的方式可以永久设置阈值(修改完成后需要重启MySQL服务):

[mysqld]
long_query_time=3

慢SQL排查测试

模拟慢SQL数据

执行如下SQL语句休眠4秒,模拟慢SQL:

select sleep(4);

查询超过阈值的SQL的数量:

show global status like '%slow_queries%';

可以看到超过阈值的SQL数为1:

查询超过阈值的具体SQL语句

主要有两种方式可以定位到具体的慢SQL语句,分别为查看日志文件使用mysqldumpslow工具查看

方式一:通过查看日志文件,也就是前面设置的slow_query_log_file

方式二:通过mysqldumpslow工具快速定位慢SQL

假设执行了1000条SQL,其中有30条SQL都超过了阈值,如果直接查看日志文件,无法快速定位到具体的SQL,所以需要使用mysqldumpslow工具,通过一些过滤条件,快速查找出慢SQL。

Linux:

mysqldumpslow命令

--help命令查看帮助文档:

mysqldumpslow --help
-s ORDER排序   what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count(访问次数)
                 l: lock time(锁的时间)
                 r: rows sent(返回的记录数)
                 t: query time(查询时间)
-r:逆序 reverse the sort order (largest last instead of first)
-l:锁定时间 don't subtract lock time from total time
-g:后面跟一个正则匹配模式,大小写不敏感
-t:top n,即为返回前面多少条的数据

举栗:

  • 获取返回记录最多的3个SQL

    mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow-query-log.log
    
  • 获取访问次数最多的3个SQL

    mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow-query-log.log
    
  • 按照时间排序,前10条包含left join查询语句的SQL

    mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow-query-log.log
    

Windows:

MySQL安装后以后在bin下有mysqldumpslow.pl文件,如果没有可以自行下载。

D:\MySQL Server 5.5\bin\mysqldumpslow.pl:

mysqldumpslow是一个perl脚本,要想在Windows执行,首先需要安装Perl。

安装过程比较简单,从官网 http://strawberryperl.com/ 下载windows安装包,安装完成后通过perl -v命令测试是否安装成功,如果能显示版本号,表示安装成功。

百度网盘:

链接:https://pan.baidu.com/s/1MiJ3FNUGEoSE1U6dJzOsAg
提取码:slt7

查看帮助文档:

perl mysqldumpslow.pl --help

举栗:

  • 获取返回记录最多的3个SQL

    perl mysqldumpslow.pl -s r -t 3 "D:/MySQL Server 5.5/slow_query_log.log"
    
  • 获取访问次数最多的3个SQL

    perl mysqldumpslow.pl -s c -t 3 "D:/MySQL Server 5.5/slow_query_log.log"
    
  • 按照时间排序,前10条包含left join查询语句的SQL

    perl mysqldumpslow.pl -s t -t 10 -g "left join" "D:/MySQL Server 5.5/slow_query_log.log"
    

标签:slow,log,SQL,查询,排查,MySQL,query
From: https://www.cnblogs.com/caituotuo/p/16967524.html

相关文章

  • Public Key Retrieval is not allowed 解决 Mysql
    一、导致“PublicKeyRetrievalisnotallowed”原因当禁用SSL/TLS协议传输后,客户端会使用服务器的公钥进行传输,默认情况下客户端不会主动去找服务器拿公钥,进而会出现......
  • Laravel 查询包括软删除的记录
    本文为joshua317原创文章,转载请注明:转载自joshua317博客 https://www.joshua317.com/article/287###1.查询结果包括已被软删除的记录:Model::withTrashed()->get();#......
  • Mysql的MVCC机制
    MVCCMultiVersionConcurrencyControl的简称,代表多版本并发控制,实现非锁定一致性读概念:??非锁定读??快照读(查询的行执行删除或修改操作,则读取快照数据,(历史数据),)多版本控制??......
  • sqlserver 低版本附加到高版本的库,只剩一个数据文件
    1.新建一个同名的数据库2.选中数据库->右键->属性->选项->兼容性级别->(选中你附加文件的版本)3.停掉sqlserver服务,将需要附件的mdf文件替换掉创建数据生成的mdf,删除掉......
  • idea里面连接数据库进行sql操作
    1.第一种写法packagecom.woniuxy;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;importjava.sql.Statement;publicclassT......
  • 一些最重要的 SQL 命令
    一些最重要的SQL命令SELECT-从数据库中提取数据UPDATE-更新数据库中的数据(updatetestsettest2='A'wheretest1=1;)DELETE-从数据库中删除数据(deletefr......
  • ezsql5
    1';showtables;1919810931114514wordsreturnpreg_match("/select|update|delete|drop|insert|where|\./i",$inject);1';showcolumnsfrom`words`;关于在这......
  • SQL SERVER定期转移海量数据方案
    SQLSERVER定期转移海量数据方案【关键词】1、   分区切换2、   BCP3、   BULKINSERT4、   Insert【背景】有个表比较巨大,每天新增约500万条记录......
  • BKPF会计凭证查询-ALV表
    *&---------------------------------------------------------------------**&ReportZ14*&*&-----------------------------------------------------------------......
  • SQLSERVER日期查询(年、月、日、季、周、时、分、秒)
     常用日期查询操作SELECTGETDATE()[当前日期],DATENAME(YEAR,GETDATE())[年],DATENAME(MONTH,GETDATE())[月],DATENAME(DAY,GETD......