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

开启慢查询

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

修改配置文件
设置全局变量

方式一需要修改配置文件 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慢查询定位内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

参考文档

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

MySQL慢查询如何定位详解

相关推荐