1 什么是慢查询
慢查询日志是MySQL提供的一种日志记录功能,它能够记录执行时间超过预设阈值(long_query_time)的SQL查询语句,并将这些信息写入到日志文件中。
2 查看慢查询日志的设置和状态
2.1 慢查询日志的开启状态和日志文件位置
show variables like '%slow_query_log%';
- slow_query_log:慢查询日志开启状态,ON为开启,OFF为关闭,默认为关闭OFF
- slow_query_log_file:慢查询日志存储路径
2.2 查看慢查询阈值
show variables like '%long_query_time%';
- long_query_time:慢查询阈值,默认10s
2.3 查询系统中慢查询记录的数量
show global status like '%Slow_queries%';
3 如何开启慢查询日志
3.1 临时开启慢查询日志
(1)设置慢查询日志状态
临时开启只对当前数据库会话有效,并且重启数据库后设置会失效:
#开启慢查询日志:slow_query_log=ON 或者 slow_query_log=1
set global slow_query_log = ON;
set global slow_query_log = 1;
#关闭慢查询日志:slow_query_log=OFF 或者 slow_query_log=0
set global slow_query_log = OFF;
set global slow_query_log = 0;
(2)设置慢查询阈值
set long_query_time = 3;
3.2 永久开启慢查询日志
步骤一:修改my.cnf配置文件,添加:
[mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/lib/mysql/localhost-slow.log
- slow_query_log:慢查询日志开启状态,ON或1为开启,OFF或0为关闭
- long_query_time:慢查询阈值
- slow_query_log_file:慢查询日志存储路径
步骤二:重启mysql服务
service mysqld restart
4 实战慢查询
(1)检查是否开启慢查询日志
show variables like '%slow_query_log%';
show variables like '%long_query_time%';
如上图所示,目前已开启慢查询日志,且慢查询阈值设置为2s。
(2)运行慢查询sql,查看慢查询日志
运行sql:
select sleep(3);
查看日志:
tail -f /var/lib/mysql/localhost-slow.log
慢查询阈值设置为2s,sql实际运行3s,故生成慢查询日志:
慢查询日志字段说明:
- Time:查询开始的时间。
- Id:服务器进程ID。
- User:执行查询的MySQL用户。
- Host:发送查询的客户端主机。
- Query_time:查询执行的时间。
- Lock_time:查询在锁定资源过程中花费的时间。
- Rows_sent:查询结果中发送的行数。
- Rows_examined:查询执行过程中检查的行数。
- Query:执行的SQL语句
(3)查询当前系统有多少条慢查询记录
show global status like '%Slow_queries%';
5 日志分析工具mysqldumpslow
mysqldumpslow是MySQL提供的一个命令行工具,专门用于分析MySQL的慢查询日志(slow query log),帮助我们识别和优化执行效率低下的SQL语句。
通过 --help命令,就能知道如何使用Mysqlsumpslow
参数说明:
(1)基本排序参数
- -s / --sort=:指定排序方式。后面可以跟不同的参数来指定按照何种方式进行排序。
- t:以查询总时间排序(默认)。
- c:以查询出现次数(即相同的查询语句执行了多少次)排序。
- l:以查询锁的总时间排序。
- r:以查询返回的记录数排序。
- at:以查询平均时间排序。
- al:以查询锁的平均时间排序。
- ar:以查询平均返回记录数排序。
(2)输出限制参数
- -t / --top=N:限制输出的慢查询数量,即仅显示最慢的N条查询。N是一个正整数,用户可以根据需要指定。
(3)正则表达式匹配参数
- -g / --grep=PATTERN:仅显示包含指定模式的查询。PATTERN是一个正则表达式,用于匹配SQL语句中的特定文本。该参数是大小写不敏感的,有助于筛选出包含特定关键词或模式的慢查询语句。
示例:
获取返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
获取访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
获取按时间排序的前10条包含左连接的查询语句:
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/localhost-slow.log
建议在使用这些命令时结合| 和more 使用 ,否则有可能出现刷屏的情况
mysqldumpslow -s r -t 20 /var/lib/mysql/localhost-slow.log | more