MySQL慢查询日志是MySQL提供的一种日志记录，用来记录执行时长超过指定时长的查询语句，具体指运行时间超过 long_query_time 值的 SQL 语句，则会被记录到慢查询日志中。
long_query_time 默认值是 10 ，单位是 s，即默认是 10秒 。默认情况下，MySQL数据库并不会开启慢查询日志，需要手动设置这个参数。
通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。一般建议开启，它对服务器性能的影响微乎其微，但是可以记录MySQL服务器上执行了很长时间的查询语句。慢查询日志可以帮助我们定位mysql性能问题所在。
MySQL慢查询日志
慢查询日志相关参数
slow_query_log : 是否启用慢查询日志，[1 | 0] 或者 [ON | OFF]
slow_query_log_file : MySQL数据库（5.6及以上版本）慢查询日志存储路径。
可以不设置该参数，系统则会默认给一个缺省的文件 HOST_NAME-slow.log
long_query_time : 慢查询的阈值，当查询时间超过设定的阈值时，记录该SQL语句到慢查询日志。
log_queries_not_using_indexes ：设置为 ON ，可以捕获到所有未使用索引的SQL语句(不建议启用)
log_output : 日志存储方式。
log_output='FILE'，表示将日志存入文件，默认值是'FILE'。
log_output='TABLE'，表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。
MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。
日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。5.6之前的版本，有些参数名字不一样：
log-slow-queries : MySQL数据库（5.6以下版本）慢查询日志存储路径。开启日志
立即生效，重启失效
mysql> set global slow_query_log=ON;
mysql> set global slow_query_log_file='/xxx/mysql-slow.log';永久生效
修改 my.cnf ：
[mysqld]
slow_query_log = 1
slow_query_log_file = /xxx/mysql-slow.log
long_query_time = 1
# 也可以写成这种形式
slow-query-log = 1
slow-query-log-file = /xxx/mysql-slow.log
long-query-time = 1重启mysql服务。
关闭日志
临时关闭，重启失效：
mysql> set global slow_query_log=OFF;永久关闭，修改 my.cnf，重启mysql服务：
[mysqld]
slow_query_log = 0MySQL慢查询日志分析
慢查询日志格式说明
打开慢查询日志 mysql-slow.log ，内容都是以下格式：
# Time: 2017-11-22T12:22:32.554299Z
# User@Host: www[www] @ [192.168.10.2] Id: 580785559
# Query_time: 24.354270 Lock_time: 0.000238 Rows_sent: 1 Rows_examined: 511156
SET timestamp=1511353352;
SELECT * FROM mo_user WHERE email = 'chxxx@hotmail.com' LIMIT 1;其中参数说明如下：
- log 记录的时间：# Time: 2017-11-22T12:22:32.554299Z
- SQL 的执行主机：# User@Host: www[www] @ [192.168.10.2] Id: 580785559
- SQL 的执行信息（执行时间(单位：s)，锁时间，返回结果行数，查询总行数）：# Query_time: 24.354270 Lock_time: 0.000238 Rows_sent: 1 Rows_examined: 511156;
- SQL 执行发生的时间：SET timestamp=1511353352;
- SQL 的执行内容：SELECT * FROM mo_user WHERE email = 'chxxx@hotmail.com' LIMIT 1;
mysqldumpslow
mysqldumpslow 是MySQL自带的慢查询日志分析工具(perl脚本)。执行命令 mysqldumpslow --help，显示命令参数如下：
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-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)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time参数说明：
- -v、--verbose : 在详细模式下运行，打印有关该程序的更多信息。
- -d、--debug : 在调试模式下运行。
- --help : 显示帮助信息并退出程序
-s [sort_type] : sort_type 是信息排序的依据
al：average lock time，按平均等待锁的时间排序
ar：average rows sent，按平均发给客户端的行总数排序
at：average query time，按平均查询时间排序
c：count，按出现总次数排序
l：lock time，按等待锁的时间排序
r：rows sent，按扫描的行总数排序
t：query time，按累计总耗费时间排序- -r : 倒序信息排序
- -t NUM: 只显示前 n 个查询，降序
- -a : 不把数字抽象为'N'，不把字符串抽象为'S'
- -n NUM : 「abstract numbers with at least n digits within names」
- -g PATTERN : 根据字符串筛选慢查询日志，可写正则匹配，大小写不敏感。
- -h HOSTNAME : 根据服务器名称选择慢查询日志
- -i NAME : 根据服务器 MySQL 实例名称选择慢查询日志
-l : 不要将总时间减去锁定时间
mysqldumpslow 分析的结果如下:
- Count : 出现次数(Count)
- Time : 执行最长时间(Time) 和 累计总耗费时间(Time)
- Lock : 等待锁的时间(Lock)
- Rows : 发送给客户端的行总数(Rows) 和 扫描的行总数(Rows)
- root[root]@localhost : 用户
- SHOw FULL ... : SQL语句本身(抽象了格式, 比如 limit 1, 20 用 limit N,N 表示。'N'表示数字，'S'表示字符串)。
例子:
返回记录数最多的10个SQL
mysqldumpslow -s r -t 10 mysql-slow.log
mysqlsla
mysqlsla是 hackmysql.com 推出的一款日志分析工具(该网站还维护了 mysqlreport, mysqlidxchk 等比较实用的mysql工具)
整体来说, 功能非常强大。数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等。
但是，hackmysql.com官方已经在2015年1月份放弃了对mysqlsla的维护。
安装
解决依赖关系
# yum install perl-DBI perl-DBD-MySQL 可能会遇到的问题：Can't locate ExtUtils/MakeMaker.pm，解决如下：
# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker可能会遇到的问题：Can't locate Time/HiRes.pm in @INC，解决如下：
# yum install perl-Time-HiRes下载mysqlsla
当前 mysqlsla 的最新版本为 2.03，到官网下载（官方链接已经失效），可以去这个 有效下载地址 下载。
编译安装
# tar xvfz mysqlsla-2.03.tar.gz
# cd mysqlsla-2.03
# perl Makefile.PL
# make
# make install使用
# mysqlsla -lt slow mysql-slow.log
或者
# mysqlsla -lt slow mysql-slow.log -sf "+SELECT" -db dbName -top 10 -sort t_sum参数意义 ：
- -lt ：表示日志类型，有: slow, general, binary, msl, udl
- -sf ：[+-][TYPE]，包括|不包括，过滤sql语句的类型 [TYPE]有 SELECT, CREATE, DROP, UPDATE, INSERT，例如 "+SELECT,INSERT"，不出现的默认是 - ，即不包括。
- -db ：要处理哪个库的日志。
- -top ：表示取按规则排序的前多少条。
- -sort ：按某种规则排序，t_sum 按总时间排序， c_sum 按总次数排序。c_sum_p : sql语句执行次数占总执行次数的百分比。
对慢查询日志文件的分析，最简化的调用方式如下：
# mysqlsla -lt slow [SlowLogFilePath] > [ResultFilePath]
格式说明如下:
- 总查询次数 (queries total), 去重后的sql数量 (unique)
- 输出报表的内容排序方式(sorted by)
最重大的慢sql统计信息, 包括平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数。
- Count： sql的执行次数及占总的slow log数量的百分比.
- Time：执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占总慢sql时间的百分比.
- 95% of Time：去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.
- Lock Time：等待锁的时间.
- 95% of Lock ：95%的慢sql等待锁时间.
- Rows sent：结果行统计数量, 包括平均, 最小, 最大数量.
- Rows examined： 扫描的行数量.
- Database：属于哪个数据库
Users：哪个用户,IP, 占到所有用户执行的sql百分比
- Query abstract：抽象后的sql语句
- Query sample：sql语句个例
pt-query-digest
percona-toolkit 工具介绍
percona-toolkit 是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务。这些任务包括：
- 检查master和slave数据的一致性
- 有效地对记录进行归档
- 查找重复的索引
- 对服务器信息进行汇总
- 分析来自日志和tcpdump的查询
- 当系统出问题的时候收集重要的系统信息
Percona Toolkit整个工具箱提供了非常多实用的工具，具体的使用方法可以参看 官方文档
percona-toolkit安装
安装 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 install默认的会被安装在 /usr/local/bin 目录下。执行 man percona-toolkit 可以查看安装了哪些工具。
运行工具可能会遇到下面的错误：
这是因为缺少相应包，.pm包实际上是perl的包，运行下面的命令安装即可：
yum install -y perl-Time-HiRes如果安装过程中出现 Error Downloading Packages 错误，尝试 yum clean all 后再安装。使用其Percona Toolkit中其他工具也可能会遇到类似的问题，按照提示安装相应的perl包就可以了。
问题：Can't locate Digest/MD5.pm in @INC
解决：# yum install perl-Digest-MD5
问题：Can't locate ExtUtils/MakeMaker.pm in @INC
解决：# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
pt-query-digest使用
pt-query-digest 可以从普通MySQL日志，慢查询日志以及二进制日志中分析查询，甚至可以从 SHOW PROCESSLIST; 和MySQL协议的tcpdump中进行分析，如果没有指定文件，它从标准输入流（STDIN）中读取数据。
最简单的用法如下：
# pt-query-digest mysql-slow.log输出信息大致如下:
整个输出分为三大部分：
整体概要（Overall）
这个部分是一个大致的概要信息(类似loadrunner给出的概要信息)，通过它可以对当前MySQL的查询性能做一个初步的评估，比如各个指标的最大值(max)，平均值(min)，95%分布值，中位数(median)，标准偏差(stddev)。
这些指标有查询的执行时间（Exec time），锁占用的时间（Lock time），MySQL执行器需要检查的行数（Rows examine），最后返回给客户端的行数（Rows sent），查询的大小。
查询的汇总信息（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个占比较低而不值得单独显示的查询的统计数据。
详细信息
这个部分会列出Profile表中每个查询的详细信息：（默认是按照总的Exec time排序，降序）
包括Overall中有的信息、查询响应时间的分布情况以及该查询 "入榜" 的理由，最底下会显示该查询SQL语句（真实显示，非抽象格式）。
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还可以跟一些过滤条件。详见 官方文档
另外结合一些第三方工具还能生成相应的报表，可以 参考这里
建议 ：当 slow log 很大的时候最好还是将日志文件移到其他机器上进行分析，避免分析时过度消耗该服务器资源。
扫一扫
2162
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
