您的位置：首页 > 数据库 > mysql  >  MySQL慢查询如何定位详解

MySQL慢查询如何定位详解

 更新时间：2020年11月24日 15:48  点击：2153

前言

相信大家在平时工作中都有过 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

[!--infotagslink--]

相关文章

  • MySQL性能监控软件Nagios的安装及配置教程

    这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14

  • 详解Mysql中的JSON系列操作函数

    新版 Mysql 中加入了对 JSON Document 的支持，可以创建 JSON 类型的字段，并有一套函数支持对JSON的查询、修改等操作，下面就实际体验一下...2016-08-23

  • 深入研究mysql中的varchar和limit(容易被忽略的知识)

    为什么标题要起这个名字呢？commen sence指的是那些大家都应该知道的事情，但往往大家又会会略这些东西，或者对这些东西一知半解，今天我总结下自己在mysql中遇到的一些commen sense类型的问题。　...2015-03-15

  • MySQL 字符串拆分操作(含分隔符的字符串截取)

    这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取)，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22

  • vue+高德地图实现地图搜索及点击定位操作

    这篇文章主要介绍了vue+高德地图实现地图搜索及点击定位操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-09-09

  • mysql的3种分表方案

    一、先说一下为什么要分表：当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。根据个人经验，mysql执行一个sql的过程如下：1...2014-05-31

  • Windows服务器MySQL中文乱码的解决方法

    我们自己鼓捣mysql时，总免不了会遇到这个问题：插入中文字符出现乱码，虽然这是运维先给配好的环境，但是在自己机子上玩的时候咧，总得知道个一二吧，不然以后如何优雅的吹牛B。...2015-03-15

  • vue+element ui实现锚点定位

    这篇文章主要为大家详细介绍了vue+element ui实现锚点定位，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2021-06-29

  • Centos5.5中安装Mysql5.5过程分享

    这几天在centos下装mysql，这里记录一下安装的过程，方便以后查阅Mysql5.5.37安装需要cmake，5.6版本开始都需要cmake来编译，5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15

  • 用VirtualBox构建MySQL测试环境

    宿主机使用网线的时候，客户机在Bridged Adapter模式下，使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候，客户机在Bridged Adapter模式下，使用可选项里唯一一个WIFI选项，Microsoft Virtual Wifi Miniport Adapter也无法上网，故弃之。...2013-09-19

  • node.js如何操作MySQL数据库

    这篇文章主要介绍了node.js如何操作MySQL数据库，帮助大家更好的进行web开发，感兴趣的朋友可以了解下...2020-10-29

  • 忘记MYSQL密码的6种常用解决方法总结

    首先要声明一点，大部分情况下，修改MySQL密码是需要有mysql里的root权限的...2013-09-11

  • MySQL数据库备份还原方法

    MySQL命令行导出数据库： 1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录 如我输入的命令行：cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26

  • Mysql命令大全(详细篇)

    一、连接Mysql格式： mysql -h主机地址 -u用户名 －p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口，然后进入目录mysql/bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密...2015-11-08

  • Navicat for MySQL 11注册码\激活码汇总

    Navicat for MySQL注册码用来激活 Navicat for MySQL 软件，只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23

  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14

  • RHEL6.5编译安装MySQL5.6.26教程

    一、准备编译环境，安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21

  • React+高德地图实时获取经纬度,定位地址

    思路其实没有那么复杂，把地图想成一个盒子容器，地图中心点想成盒子中心点；扎点在【地图中心点】不会动，当移动地图时，去获取【地图中心点】经纬度，设置某个位置的时候，将经纬度设置为【地图中心点】即可...2021-06-20

  • 基于PostgreSQL和mysql数据类型对比兼容

    这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25

  • Mysql中 show table status 获取表信息的方法

    这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12