MySQL慢查询如何定位详解

前言

相信大家在平时工作中都有过 SQL 优化经历,那么在优化前就必须找到慢 SQL 方可进行分析。这篇文章就介绍下如何定位到慢查询。

慢查询日志是 MySQL 内置的一项功能,可以记录执行超过指定时间的 SQL 语句。

以下是慢查询的相关参数,大家感兴趣的可以看下:

参数 含义
log_output 日志输出位置,默认为 FILE,即保存为文件,若设置为 TABLE,则将日志记录到 mysql.show_log 表中,支持设置多种格式
slow_query_log_file 指定慢查询日志文件的路径和名字,可使用绝对路径指定,默认值是主机名-slow.log,位于配置的 datadir 目录
long_query_time 执行时间超过该值才记录到慢查询日志,单位为秒,默认为 10
min_examined_row_limit 对于查询扫描行数小于此参数的SQL,将不会记录到慢查询日志中,默认为 0
log_queries_not_using_indexes 是否将未使用索引的 SQL 记录到慢查询日志中,开启此配置后会无视 long_query_time 参数,默认为 OFF
log_throttle_queries_not_using_indexes 设定每分钟记录到日志的未使用索引的语句数目,超过这个数目后只记录语句数量和花费的总时间,默认为 0
log-slow-admin-statements 记录执行缓慢的管理 SQL,如 ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE 和 REPAIR TABLE,默认为 OFF
log_slow_slave_statements 记录从库上执行的慢查询语句,如果 binlog 的值为 row,则失效,默认为 OFF

开启慢查询

有两种方式可以开启慢查询

  1. 修改配置文件
  2. 设置全局变量

方式一需要修改配置文件 my.ini,在[mysqld]段落中加入如下参数:

[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'
long_query_time=0.001

然后需要重启 MySQL 才可以生效,命令为 service mysqld restart

方式二无需重启即可生效,但是重启会导致设置失效,设置的命令如下所示:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL long_query_time = 0.001;

这样就可以将慢查询日志同时记录在文件以及 mysql.slow_log 表中。

通过第二种方式开启慢查询日志,然后使用全表查询语句 SELECT * FROM user

然后再查询慢查询日志:SELECT * FROM mysql.slow_log,可以发现其中有这样一条记录:


slow_log

其中,start_time 为执行时间,user_host 为用户的主机名,query_time 为查询所花费的时间,lock_time 为该查询使用锁的时间,rows_sent 为这条查询返回了多少数据给客户端,rows_examined 表示这条语句扫描了多少行,db 为数据库,sql_text 为这条 SQL,thread_id 为执行这条查询的线程 id。

这样我们就可以通过 slow_log 表的数据进行分析,然后对 SQL 进行调优了。

以上是通过 Table 来进行分析的,下面来通过文件的慢查询是怎么样的。

如果不知道文件保存在哪里,可以使用 SHOW VARIABLES LIKE '%slow_query_log_file%' 来查看文件保存位置,打开慢查询日志文件,可以看出每五行表示一个慢 SQL,这样查看比较费事,可以使用一些工具来查看。


慢查询日志文件

mysqldumpslow

MySQL 内置了 mysqldumpslow 这个工具来帮助我们分析慢查询日志文件,Windows 环境下使用该工具需要安装 Perl 环境。

可以通过 -help 来查看它的命令参数:


mysqldumpslow help

比如我们可以通过 mysqldumpslow -s t 10 LAPTOP-8817LKVE-slow.log 命令得到按照查询时间排序的 10 条 SQL 。


mysqldumpslow 结果

pt-query-digest

除此之外还有 pt-query-digest,这个是 Percona Toolkit 中的工具之一,下载地址:https://www.percona.com/downloads/percona-toolkit/LATEST/,如果是 Windows 系统,可以在安装 Perl 的环境下,把脚本下载下来:https://raw.githubusercontent.com/percona/percona-toolkit/3.x/bin/pt-query-digest

下面先对 pt-query-digest 进行简单的介绍:

pt-query-digest 是用于分析 MySQL 慢查询的一个第三方工具,可以分析 binlog、General log 和 slowlog,也可以通过 showprocesslist 或者通过 tcpdump 抓取的 MySQL 协议数据来进行分析,可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。

有兴趣的可以先下载下来自己玩玩,将在后续的文章中对 pt-query-digest 工具进行详细介绍。

show processlist

还有种情况是慢查询还在执行中,慢查询日志里是找不到慢 SQL 呢,这个时候可以用 show processlist 命令来寻找慢查询,该命令可以显示正在运行的线程,执行结果如下图所示,可以根据 Time 的大小来判断是否为慢查询。


show processlist

总结

这篇文章主要讲解了如何定位慢查询以及简单介绍了 mysqldumpslow 和 pt-query-digest 工具,后续还会讲解 explain 和 show profile 以及 trace 等常用的方法。

你在定位慢查询或者优化 SQL 时,都会用到哪些方法呢?

到此这篇关于MySQL慢查询如何定位的文章就介绍到这了,更多相关MySQL慢查询定位内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

参考文档

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

(0)

相关推荐

  • MySQL慢查询查找和调优测试

    编辑 my.cnf或者my.ini文件,去除下面这几行代码的注释: 复制代码 代码如下: log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes 这将使得慢查询和没有使用索引的查询被记录下来. 这样做之后,对mysql-slow.log文件执行tail -f命令,将能看到其中记录的慢查询和未使用索引的查询. 随便提取一个慢查询,执行explain: 复制代

  • MySQL数据库中把int转化varchar引发的慢查询

    最近一周接连处理了2个由于int向varchar转换无法使用索引,从而引发的慢查询. CREATE TABLE `appstat_day_prototype_201305` ( `day_key` date NOT NULL DEFAULT '1900-01-01', `appkey` varchar(20) NOT NULL DEFAULT '', `user_total` bigint(20) NOT NULL DEFAULT '0', `user_activity` bigint(20)

  • 简单谈谈MySQL优化利器-慢查询

    慢查询 首先,无论进行何种优化,开启慢查询都算是前置条件.慢查询机制,将记录过慢的查询语句(事件),从而为DB维护人员提供优化目标. 检查慢查询是否开启 通过show variables like 'slow_query_log'这条语句,可以找到慢查询的状态(On/Off). 开启慢查询 本文使用的MySQL版本:MariaDB - 10.1.19,请注意,不同版本的MySQL存在差异. 在[mysqld]下加入: [mysqld] port= 3306 slow-query-log=1 #

  • MySQL开启慢查询日志log-slow-queries的方法

    一个普通WEB站点的页面常常需要查询N条SQL语句后才能得出页面结果,当网站访问速度慢而前端做了大量优化工作以后,数据库瓶颈的查找也是WEB优化的一个重要部分.   MySQL中提供了一个慢查询的日志记录功能,可以把查询SQL语句时间大于多少秒的语句写入慢查询日志,日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在. 开启慢查询功能 log-slow-queries 慢查询日志文件路径 long_query_time 超过多少秒的查询就写入日志 打开my.cnf配置文件,加入以下代码:

  • MySQL慢查询优化之慢查询日志分析的实例教程

    数据库响应慢问题最多的就是查询了.现在大部分数据库都提供了性能分析的帮助手段.例如Oracle中会帮你直接找出慢的语句,并且提供优化方案.在MySQL中就要自己开启慢日志记录加以分析(记录可以保存在表或者文件中,默认是保存在文件中,我们系统使用的就是默认方式). 先看看MySQL慢查询日志里面的记录长什么样的: Time Id Command Argument # Time: 141010 9:33:57 # User@Host: root[root] @ localhost [] Id: 1

  • 深入mysql慢查询设置的详解

    在web开发中,我们经常会写出一些SQL语句,一条糟糕的SQL语句可能让你的整个程序都非常慢,超过10秒一般用户就会选择关闭网页,如何优化SQL语句将那些运行时间 比较长的SQL语句找出呢?MySQL给我们提供了一个很好的功能,那就是慢查询!所谓的慢查询就是通过设置来记录超过一定时间的SQL语句!那么如何应用慢查询呢? 1.开启MySQL的慢查询日志功能默认情况下,MySQL是不会记录超过一定执行时间的SQL语句的.要开启这个功能,我们需要修改MySQL的配置文件,windows下修改my.in

  • MYSQL5.7.9开启慢查询日志的技巧

    用MYSQL 5.7.9 作为ZABBIX 2.4.7 的监控数据库. 前段时间开启了慢查询日志, 后来发现慢查询日志膨胀到了700M 查看最后100条 大部分都是 0.1 秒的 后来想改, 以前是动态设置的 set global slow_query_log=1; 方式的 . 然后想直接用配置文件/etc/my.cnf 配慢查询 # Remove leading # and set to the amount of RAM for the most important data # cache

  • 详解MySql的慢查询分析及开启慢查询日志

    我最近也在研究MySQL性能优化的路上,那么今天也算个学习笔记吧! 在小伙伴们开发的项目中,对于MySQL排查问题找出性能瓶颈来说,最容易发现并解决的问题就是MYSQL的慢查询以及没有得用索引的查询. 接下来教大家如何开启MySQL5.0版本以上的慢查询日志记录: OK,一起开始找出mysql中执行起来不"爽"的SQL语句吧. 首先,我们通过mysql命令进入mysql命令行中: [root@yunuo_vm ~]# mysql -u root -p Enter password: W

  • MySQL 开启慢查询日志的方法

    1.1 简介 开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能. 1.2 登录数据库查看 [root@localhost lib]# mysql –uroot 因为没有设置设置密码,有密码的在 mysql –uroot –p 接密码 1.2.1 进入MySql 查询是否开了慢查询 mysql> show variables like 'slow_query%'; +---------------------+----------

  • mysql 开启慢查询 如何打开mysql的慢查询日志记录

    mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢? 其实打开mysql的慢查询日志很简单,只需要在mysql的配置文件里(windows系统是my.ini,linux系统是my.cnf)的[mysqld]下面加上如下代码: 复制代码 代码如下: log-slow-queries=/var/lib/mysql/slowquery.log long_query_time=2 注: log-slow-queries

随机推荐