MySQL慢查询日志举例分析

共计 4715 个字符，预计需要花费 12 分钟才能阅读完成。

这篇文章主要介绍“MySQL 慢查询日志举例分析”，在日常操作中，相信很多人在 MySQL 慢查询日志举例分析问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL 慢查询日志举例分析”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！

慢查询日志
打开慢查询日志

慢查询日志，顾名思义就是记录执行比较慢查询的日志。

查看是否开启慢查询日志：

show variables like  %slow%

打开慢查询日志。修改 MySQL 的配置文件 my.cn 一般是在 /etc 目录下面，加上下面三行配置后重启 MySQL。

slow_query_log = ON
slow_launch_time = 2 slow_query_log_file = /usr/local/mysql/data/slow_query.log

slow_launch_time 只能精确到秒，如果需要更精确可以使用一些第三方的工具比如后面介绍的 pt-query-digest。

注意：我这里用的 MySQL 版本是 5.6，不同版本的 MySQL 开启慢查询的配置是不同的，比如 5.6 之前的某些版本是 ong_query_time, long_query_time 和 log-slow-queries。可以先在终端执行 show variables like %slow% 查看下当前版本具体配置是什么。

也可以在终端通过设置全局变量来打开慢查询日志：

set @@global.slow_query_log = ON;

保存慢查询日志到表中

MySQL 支持将慢查询日志保存到 mysql.slow_log 这张表中。通过 @@global.log_output 可以设置默认为 TABLE，FILE 和 TABLE 只能同时使用一个。

set @@global.log_output= TABLE

我们可以使用下面的语句来模拟慢查询：

select sleep(10);

慢查询日志分析

1、可以使用 MySQL 自带的 mysqldumpslow 工具。使用很简单，可以跟 -help 来查看具体的用法。

# -s：排序方式。c , t , l , r  表示记录次数、时间、查询时间的多少、返回的记录数排序；# ac , at , al , ar  表示相应的倒叙；# -t：返回前面多少条的数据；# -g：包含什么，大小写不敏感的；mysqldumpslow -s r -t 10 /slowquery.log #slow 记录最多的 10 个语句
mysqldumpslow -s t -t 10 -g  left join  /slowquery.log # 按照时间排序前 10 中含有 left join 的

2、可以导到 mysql.slow_query 表中，然后通过 sql 语句进行分析。

3、使用第三方工具，下面会有介绍。

Percona Toolkit 介绍

percona-toolkit 是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的 mysql 和系统任务。这些任务包括：

检查 master 和 slave 数据的一致性

有效地对记录进行归档

查找重复的索引

对服务器信息进行汇总

分析来自日志和 tcpdump 的查询

当系统出问题的时候收集重要的系统信息

安装

安装 percona-toolkit 非常简单，到官网下载.tar.gz 包：

wget percona.com/get/percona-toolkit.tar.gz tar -zxvf percona-toolkit-2.2.5.tar.gz

然后依次执行下面的命令：

perl Makefile.PL make make test make install

默认的会被安装在 /usr/local/bin 目录下。执行 man percona-toolkit 可以查看安装了哪些工具。

运行工具可能会遇到下面的错误：

这是因为缺少相应包，.pm 包实际上 perl 的包，运行下面的命令安装即可：

yum install -y perl-Time-HiRes

如果安装过程中出现”Error Downloading Packages”错误，尝试 yum clean all 后再安装。使用其 Percona Toolkit 中其他工具也可能会遇到类似的问题，按照提示安装相应的 perl 包就可以了。

Percona Toolkit 整个工具箱提供了非常多实用的工具，具体的使用方法可以参看官方文档。

下面有选择的给大家介绍几个有用的工具。

pt-query-digest

pt-query-digest 可以从普通 MySQL 日志，慢查询日志以及二进制日志中分析查询，甚至可以从 SHOW PROCESSLIST 和 MySQL 协议的 tcpdump 中进行分析，如果没有指定文件，它从标准输入流（STDIN）中读取数据。

最简单的用法如下：

pt-query-digest slow.logs

输出信息大致如下:

整个输出分为三大部分：

1、整体概要（Overall）

这个部分是一个大致的概要信息(类似 loadrunner 给出的概要信息)，通过它可以对当前 MySQL 的查询性能做一个初步的评估，比如各个指标的最大值(max)，平均值(min)，95% 分布值，中位数(median)，标准偏差(stddev)。这些指标有查询的执行时间（Exec time），锁占用的时间（Lock time），MySQL 执行器需要检查的行数（Rows examine），最后返回给客户端的行数（Rows sent），查询的大小。

2、查询的汇总信息（Profile）

这个部分对所有”重要”的查询 (通常是比较慢的查询) 做了个一览表:

每个查询都有一个 Query ID，这个 ID 通过 Hash 计算出来的。pt-query-digest 是根据这个所谓的 Fingerprint 来 group by 的。举例下面两个查询的 Fingerprint 是一样的都是 select * from table1 where column1 = ?，工具箱中也有一个与之相关的工具 pt-fingerprint。

select * from table1 where column1 = 2 select * from table1 where column1 = 3

Rank 整个分析中该“语句”的排名，一般也就是性能最常的。

Response time “语句”的响应时间以及整体占比情况。

Calls 该“语句”的执行次数。

R/Call 每次执行的平均响应时间。

V/M 响应时间的差异平均对比率。

在尾部有一行输出，显示了其他 2 个占比较低而不值得单独显示的查询的统计数据。

3、详细信息

这个部分会列出 Profile 表中每个查询的详细信息：

包括 Overall 中有的信息、查询响应时间的分布情况以及该查询”入榜”的理由。

pt-query-digest 还有很多复杂的操作，这里就不一一介绍了。比如：从 PROCESSLIST 中查询某个 MySQL 中最慢的查询：

pt-query-digest –processlist h=host1

从 tcpdump 中分析：

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306   mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

从一台机器上讲 slow log 保存到另外一台机器上待稍后详细分析：

pt-query-digest --review h=host2 --no-report slow.log

还可以跟一些过滤条件。详见官方文档：http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

另外结合一些第三方工具还能生成相应的报表，可以参考这里：http://biancheng.dnbcw.info/mysql/433514.html

建议：当 slow log 很大的时候最好还是将日志文件移到其他机器上进行分析。

pt-index-usage

这个工具主要是用来分析查询的索引使用情况。

pt-index-usage slow_query.log --h localhost --password 123456

详细的用法 –help 查看再对照官网就差不再赘述。

注意使用这个工具需要 MySQL 必须要有密码，另外运行时可能报找不到 /var/lib/mysql/mysql.sock 的错，简单的从 /tmp/mysql.sock 链接一个就行了。

重点要说明的是 pt-index-usage 只能分析慢查询日志，所以如果想全面分析所有查询的索引使用情况就得将 slow_launch_time 设置为 0，因此请谨慎使用该工具，线上使用的话最好在凌晨进行分析，尤其分析大量日志的时候是很耗 CPU 的。

整体来说这个工具是不推荐使用的，要想实现类似的分析可以考虑一些其他第三方的工具，比如：mysqlidxchx, userstat 和 check-unused-keys。网上比较推荐的是 userstat，一个 Google 贡献的 patch。

Oracle 是可以将执行计划保存到性能视图中的，这样分析起来可能更灵活，但是目前我还没找到 MySQL 中类似的做法。

pt-upgrade

这个工具用来检查在新版本中运行的 SQL 是否与老版本一样，返回相同的结果，最好的应用场景就是数据迁移的时候。

pt-upgrade h=host1 h=host2 slow.log

pt-query-advisor 

静态查询分析工具。能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。这个工具好像 2.2 的版本给去掉了，有可能是因为对性能影响比较大新版本直接去掉了。

总结: 上面这些工具最好不要直接在线上使用，应该作为上线辅助或故障后离线分析的工具，也可以做性能测试的时候配合着使用。

SHOW PROFILE

SHOW PROFILE 是 Google 高级架构师 Jeremy Cole 贡献给 MySQL 社区的，它可以用来 MySQL 执行语句时候所使用的资源。默认是关闭的，需要打开执行下面的语句：

set profiling = 1;# 这个命令只在本会话内起作用。

执行简单的 SHOW PROFILES 可以看到打开 profiling 之后所有查询的执行时间。

执行 SHOW PROFILE [TYPE] FOR QUERY Query_ID 可以看到 MySQL 执行某个查询各个步骤的各项性能指标的详细信息：

如果没有指定 FOR QUERY 则显示最近一条查询的详细信息。TYPE 是可选的，有以下几个选项：

ALL 显示所有性能信息

BLOCK IO 显示块 IO 操作的次数

CONTEXT SWITCHES 显示上下文切换次数，不管是主动还是被动

CPU 显示用户 CPU 时间、系统 CPU 时间

IPC 显示发送和接收的消息数量

MEMORY [暂未实现]

PAGE FAULTS 显示页错误数量

SOURCE 显示源码中的函数名称与位置

SWAPS 显示 SWAP 的次数

MySQL 在执行查询语句的时候会有很多步骤，这里就不一一赘述了，用到的时候网上搜下就行。需要特别说明的是 Sending data 这个步骤，给人感觉是 MySQL 把数据发送给客户端的耗时，其实不然，这个步骤包括了 MySQL 内部各个存储之间复制数据的过程，比如硬盘的寻道。

到此，关于“MySQL 慢查询日志举例分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！