程序员经验分享-hwhale

MySQL慢查询日志总结

2023-11-09

慢查询日志概念

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过longquerytime值的SQL，则会被记录到慢查询日志中。longquerytime的默认值为10，意思是运行10S以上的语句。

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

官方文档，关于慢查询的日志介绍如下（部分资料，具体参考官方相关链接）：

The slow query log consists of SQL statements that took more than longquerytime seconds to execute and required at least minexaminedrowlimit rows to be examined. The minimum and default values of longquerytime are 0 and 10, respectively. The value can be specified to a resolution of microseconds.

 

For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.

 

By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed usinglogslowadminstatements and logqueriesnotusingindexes, as described later.

慢查询日志相关参数

MySQL 慢查询的相关参数解释：

  • slowquerylog ：是否开启慢查询日志，1表示开启，0表示关闭。
  • log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
  • slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
  • longquerytime ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
  • logqueriesnotusingindexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
  • logoutput：日志存储方式。logoutput='FILE'表示将日志存入文件，默认值是'FILE'。logoutput='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slowlog表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

慢查询日志配置

默认情况下slowquerylog的值为OFF，表示慢查询日志是禁用的，可以通过设置slowquerylog的值来开启，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

mysql> show variables  like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.09 sec)
 
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | ON                                            |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+
2 rows in set (0.00 sec)
 
mysql>

使用set global slowquerylog=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）。例如如下所示：

1
2
3
4
5
6
7
8
9
10

mysql> show variables like 'slow_query%';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+
2 rows in set (0.01 sec)
 
mysql>

修改my.cnf文件，增加或修改参数slowquerylog 和slowquerylog_file后，然后重启MySQL服务器，如下所示

slowquerylog =1

slowquerylogfile=/tmp/mysqlslow.log

MySQL慢查询日志总结

1
2
3
4
5
6
7
8
9
10

mysql> show variables like 'slow_query%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_query_log      | ON                  |
| slow_query_log_file | /tmp/mysql_slow.log |
+---------------------+---------------------+
2 rows in set (0.00 sec)
 
mysql>

关于慢查询的参数slowquerylogfile ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件hostname-slow.log（如果没有指定参数slowquerylog_file的话）

1
2
3
4
5
6
7

mysql> show variables like 'slow_query_log_file';
+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
+---------------------+-----------------------------------------------+
1 row in set (0.00 sec)

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数longquerytime控制，默认情况下longquerytime的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运行时间正好等于longquerytime的情况，并不会被记录下来。

也就是说，在mysql源码里是判断大于longquerytime，而非大于等于。从MySQL 5.1开始，longquerytime开始以微秒记录SQL语句运行时间，之前仅用秒为单位记录。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
 
mysql> set global long_query_time=4;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

如上所示，我修改了变量longquerytime，但是查询变量longquerytime的值还是10，难道没有修改到呢？

注意：使用命令 set global longquerytime=4修改后，需要重新连接或新开一个会话才能看到修改值。你用show variables like 'longquerytime'查看是当前会话的变量值，你也可以不用重新连接会话，而是用show global variables like 'longquerytime'; 如下所示：

MySQL慢查询日志总结

在MySQL里面执行下面SQL语句，然后我们去检查对应的慢查询日志，就会发现类似下面这样的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)
 
[root@DB-Server ~]# more /tmp/mysql_slow.log
/usr/sbin/mysqld, Version: 5.6.20-enterprise-commercial-advanced-log (MySQL Enterprise Server - Advanced Edition (Commercial)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.6.20-enterprise-commercial-advanced-log (MySQL Enterprise Server - Advanced Edition (Commercial)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
# Time: 160616 17:24:35
# User@Host: root[root] @ localhost []  Id:     5
# Query_time: 3.002615  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1466069075;
select sleep(3);

MySQL慢查询日志总结

logoutput 参数是指定日志的存储方式。logoutput='FILE'表示将日志存入文件，默认值是'FILE'。logoutput='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slowlog表中。

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
 
mysql> select sleep(5) ;
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (5.00 sec)
 
mysql>
 
mysql> select * from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| start_time          | user_host                 | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text        | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
| 2016-06-16 17:37:53 | root[root] @ localhost [] | 00:00:03   | 00:00:00  |         1 |             0 |    |              0 |         0 |         1 | select sleep(3) |         5 |
| 2016-06-16 21:45:23 | root[root] @ localhost [] | 00:00:05   | 00:00:00  |         1 |             0 |    |              0 |         0 |         1 | select sleep(5) |         2 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
2 rows in set (0.00 sec)
 
Mysql>

系统变量log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。

This option does not necessarily mean that no index is used. For example, a query that uses a full index scan uses an index but would be logged because the index would not limit the number of rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name| Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)
 
mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name| Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)
 
mysql>

系统变量logslowadmin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志

1
2
3
4
5
6
7
8
9

mysql> show variables like 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name| Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
+---------------------------+-------+
1 row in set (0.00 sec)
 
mysql>

系统变量logslowslave_statements 表示

By default, a replication slave does not write replicated queries to the slow query log. To change this, use thelogslowslave_statements system variable.

When the slow query log is enabled, this variable enables logging for queries that have taken more than longquerytime seconds to execute on the slave. This variable was added in MySQL 5.7.1. Setting this variable has no immediate effect. The state of the variable applies on all subsequent START SLAVE statements.

参数--log-short-format

The server writes less information to the slow query log if you use the --log-short-format option.

Command-Line Format --log-short-format
Permitted Values Type boolean
Default FALSE

另外，如果你想查询有多少条慢查询记录，可以使用系统变量。

1
2
3
4
5
6
7
8
9

mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries| 2104|
+---------------+-------+
1 row in set (0.00 sec)
 
mysql>

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow

查看mysqldumpslow的帮助信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

[root@DB-Server ~]# 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

-s, 是表示按照何种方式排序，

  • c: 访问计数
  • l: 锁定时间
  • r: 返回记录
  • t: 查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平均查询时间

-t, 是top n的意思，即为返回前面多少条的数据；

-g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如

得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

原文地址：https://www.javazhiyin.com/23360.html 

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

MySQL慢查询日志总结 的相关文章

随机推荐

  • 什么是.NET？什么是.NET Core？.NET和.NET Core区别又是什么呢？

    本文首发于码友网 什么是 NET 什么是 NET Core NET和 NET Core区别又是什么呢 概述 对于 NET平台的初学者来说 有时候比较困惑 什么是 NET 什么是 NET Core NET和 NET Core区别又是什么呢 确

  • 【一句话攻略】彻底理解JS中的回调(Callback)函数

    作为JS的核心 回调函数和异步执行是紧密相关的 也是必须跨过去的一道个门槛 那么究竟什么是回调函数 Callback 其实回调函数并不复杂 明白两个重点即可 1 函数可以作为一个参数在另一个函数中被调用 2 JS是异步编程语言 这就是说JS

  • python-opencv滑动条，cv2滑动条，模糊示例

    cv2的滑动条使用起来其实挺简单的 只需记住两个函数就行 cv2 createTrackbar创建滑动条 cv2 getTrackbarPos获取滑动条当前值 其他要注意的是滑动条的名字以及滑动条创建在哪个窗口上 这个要一一对应 详见下例

  • react源码分析：babel如何解析jsx

    同作为MVVM框架 React相比于Vue来讲 上手更需要JavaScript功底深厚一些 本系列将阅读React相关源码 从jsx gt VDom gt RDOM等一些列的过程 将会在本系列中一一讲解 工欲善其事 必先利其器 经过多年的发

  • 内存泄漏的8种情况（附代码示例）

    一 内存泄漏 memroy leak 严格来说 只有对象不会再被程序用到了 但是GC又不能回收它们的情况 才叫内存泄漏 宽泛的讲 实际情况中很多时候一些不太好的实践会导致对象的生命周期变得很长甚至导致OOM 也叫 内存泄漏 申请了内存用完了

  • C++ - 类中的函数重载

    1 回顾函数重载 gt 函数重载的本质为相互独立的不同函数 gt C 中通过函数名和函数参数确定函数调用 gt 无法直接通过函数名得到重载函数的入口地址 gt 函数重载必然发生在同一个作用域中 2 类中的重载 类中的成员函数可以进行重载 g

  • JNI线程

    作者 左少华 博客 http blog csdn net shaohuazuo article details 43149193 转载请注明出处 http blog csdn net shaohuazuo 1 Android线程介绍 1 线

  • 一个“/”键，封锁了整个互联网

    专栏 九章算法 网址 http www jiuzhang com 正所谓无BUG不生活 从你含辛茹苦地码着第一行代码开始 bug就如影随形 其实 bug 被自己或者是测试人员发现都是好事 但如果是被用户发现 又或者导致了客户和公司的巨额损失

  • 412. Fizz Buzz \ 404. Sum of Left Leaves

    412 法一 class Solution public vector

  • python中好用的库（一）

    python中好用的库 一 时间库 arrow 使用背景 日期时间处理在实际应用场景中无处不在 所以这也成了编程语言中必不可少的模块 Python 也不例外 但是 你知道在Python中有多少个相关的模块吗 datetime time ca

  • 中州韵输入法 linux 小鹤双拼,「Rime 鼠须管」小鹤双拼配置指南

    引言 如何将汉字输入到计算机中是一个编码有关的问题 目前市面上主流的方案包括音码 形码 音形码 和大多数人一样 之前我一直使用全拼 而且得益于 NLP 技术发展 使用搜狗输入法搭配云词库 输入效率可以媲美五笔输入法 但是今天要和大家分享 是

  • java实现5种不同的验证码图片，包括中文、算式等，并返回前端

    导入以下依赖

  • Windows修改右键新建菜单【Win10、Win11版】

    目录 一 引言 二 方法一 三 成果展示 四 方法二 删除Shell New 五 方法三 借助shellNewSettings小工具 一 引言 有些混乱的windows桌面新建菜单 是不是让人很不舒服 比如下图 图中的Access需要新建么

  • 浅谈VPS

    1 VPS的原理 VPS是采用VPS VirtualPrivateServer 技术 将一部物理服务器分割成多个虚拟专享服务器 分割后形成的VPS虚拟主机都可分配独立公网IP地址 独立操作系统Windows Linux 独立享用空间 独立内

  • 5.4.1 虚拟专用网VPN

    5 4 1 虚拟专用网VPN 我们已经学习了因特网的路由协议 5 3 1 因特网的路由协议 一 5 3 2 因特网的路由协议 二 基于距离向量算法的RIP协议 5 3 3 因特网的路由协议 三 OSPF协议 5 3 4 因特网的路由协议 四

  • TCP滑动窗口 和 拥塞窗口

    结论 滑动窗口 表征发送端和接收端的接收能力 拥塞窗口 表征中间设备的传输能力 TCP滑动窗口 需要说明一下 如果你不了解TCP的滑动窗口这个事 你等于不了解TCP协议 我们都知道 TCP必需要解决的可靠传输以及包乱序 reordering

  • 计算智能：不确定知识与推理

    不确定性 简单来说 不确定性指的是条件A不能100 推导出结论B 不确定性是现实问题中的常态 比如说医学中症状向疾病的推导等等 信度是智能体主动提供的对于相关命题的信心 它来自于已经接受到的感知信息 智能体获得新的感知信息后 其概率评估应得

  • 语雀导出知识库内所有文档为markdown格式备份

    语雀 写在前面 本复刻版本无法提交issue 有问题欢迎到语雀评论区和我交流 我一般不看csdn的评论 需要找我请到语雀文章评论区 欢迎给我的语雀文档点赞 或者给我复刻的仓库star 另外我想说明一下 复刻这个仓库的目的是为了防止重要的笔记

  • navigator对象

    navigator 对象是 JavaScript 中的一个内置对象 表示当前浏览器的信息和状态 它提供了访问浏览器相关信息的属性和方法 下面是一些 navigator 对象的常见属性和方法 navigator userAgent 返回包含浏

  • MySQL慢查询日志总结

    慢查询日志概念 MySQL的慢查询日志是MySQL提供的一种日志记录 它用来记录在MySQL中响应时间超过阀值的语句 具体指运行时间超过longquerytime值的SQL 则会被记录到慢查询日志中 longquerytime的默认值为10

热门标签