MySQL慢查询日志分析详解

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

MySQL慢查询定义

分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划,还可以让MySQL记录下查询超过指定时间的语句,我们将超过指定时间的SQL语句查询称为“慢查询”。

MySQL慢查询的体现

慢查询主要体现在慢上,通常意义上来讲,只要返回时间大于 >1 sec上的查询都可以称为慢查询。慢查询会导致CPU,内存消耗过高。数据库服务器压力陡然过大,那么大部分情况来讲,肯定是由某些慢查询导致的。

mysql慢查询开启

1.查看当前慢查询设置情况

#查看慢查询时间,默认10s,建议降到1s或以下
mysql> show variables like "long_query_time";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
#查看慢查询配置情况,其中,slow_query_log的值是on就是已开启功能了。
mysql> show variables like "%slow%";
+-----------------------------------+----------------------+
| Variable_name                     | Value                |
+-----------------------------------+----------------------+
| log_slow_admin_statements         | OFF                  |
| log_slow_filter                   |                      |
| log_slow_rate_limit               | 1                    |
| log_slow_rate_type                | session              |
| log_slow_slave_statements         | OFF                  |
| log_slow_sp_statements            | ON                   |
| log_slow_verbosity                |                      |
| max_slowlog_files                 | 0                    |
| max_slowlog_size                  | 0                    |
| slow_launch_time                  | 2                    |
| slow_query_log                    | ON                   |
| slow_query_log_always_write_time  | 10.000000            |
| slow_query_log_file               | /tmp/slow_querys.log |
| slow_query_log_use_global_control |                      |
+-----------------------------------+----------------------+
14 rows in set (0.01 sec)

2.如何开启慢查询功能
方法一:在服务器上找到mysql的配置文件my.cnf , 然后再mysqld模块里追加一下内容,这样的好处是会一直生效,不好就是需要重启mysql进程。

vim my.cnf
[mysqld]
slow_query_log = ON
#定义慢查询日志的路径
slow_query_log_file = /tmp/slow_querys.log
#定义查过多少秒的查询算是慢查询,我这里定义的是1秒,5.6之后允许设置少于1秒,例如0.1秒
long_query_time = 1
#用来设置是否记录没有使用索引的查询到慢查询记录,默认关闭,看需求开启,会产生很多日志,可动态修改
#log-queries-not-using-indexes
管理指令也会被记录到慢查询。比如OPTIMEZE TABLE, ALTER TABLE,默认关闭,看需求开启,会产生很多日志,可动态修改
#log-slow-admin-statements

然后重启mysql服务器即可,这是通过一下命令看一下慢查询日志的情况:

tail -f /tmp/slow_querys.log

方法二:通过修改mysql的全局变量来处理,这样做的好处是,不用重启mysql服务器,登陆到mysql上执行一下sql脚本即可,不过重启后就失效了。

#开启慢查询功能,1是开启,0是关闭
mysql> set global slow_query_log=1;
#定义查过多少秒的查询算是慢查询,我这里定义的是1秒,5.6之后允许设置少于1秒,例如0.1秒
mysql> set global long_query_time=1;
#定义慢查询日志的路径
mysql> set global slow_query_log_file='/tmp/slow_querys.log';
#关闭功能:set global slow_query_log=0;
然后通过一下命令查看是否成功
mysql> show variables like 'long%'; 
mysql> show variables like 'slow%';
#设置慢查询记录到表中
#set global log_output='TABLE';

特别要注意的是long_query_time的设置,5.6之后支持设置低于0.1秒,所以记录的详细程度,就看你自己的需求,数据库容量比较大的,超过0.1秒还是比较多,所以就变得有点不合理了。

MYSQL慢查询日志的记录定义

直接查看mysql的慢查询日志分析,比如我们可以tail -f slow_query.log查看里面的内容

tail -f  slow_query.log
# Time: 110107 16:22:11
# User@Host: root[root] @ localhost []
# Query_time: 9.869362 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 6261774
SET timestamp=1294388531;
select count(*) from ep_friends;

字段意义解析:

第一行,SQL查询执行的时间 第二行,执行SQL查询的连接信息,用户和连接IP 第三行,记录了一些我们比较有用的信息,如下解析:
- Query_time,这条SQL执行的时间,越长则越慢
- Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
- Rows_sent,查询返回的行数
- Rows_examined,查询检查的行数,越长就当然越费时间
第四行,设置时间戳,没有实际意义,只是和第一行对应执行时间。
第五行及后面所有行(第二个# Time:之前),执行的sql语句记录信息,因为sql可能会很长。

MYSQL慢查询日志分析方法

虽然慢查询日志已经够清晰,但是往往我们的日志记录到的不是只有一条sql,可能有很多很多条,如果不加以统计,估计要看到猴年马月,这个时候就需要做统计分析了。

方法一:使用mysql程序自带的mysqldumpslow命令分析
例如:
mysqldumpslow -s c -t 10 /tmp/slow-log
这会输出记录次数最多的10条SQL语句,得出的结果和上面一般慢查询记录的格式没什么太大差别,这里就不展开来详细解析了。

参数解析:
-s:是表示按照何种方式排序,子参数如下:
    c、t、l、r:分别是按照记录次数、时间、查询时间、返回的记录数来排序,
    ac、at、al、ar:表示相应的倒叙;
-t:返回前面多少条的数据,这里意思就是返回10条数据了(也可以说是前十)
-g:后边可以写一个正则匹配模式,大小写不敏感的,比如:
    /path/mysqldumpslow -s r -t 10 /tmp/slow-log,得到返回记录集最多的10个查询。
    /path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log,得到按照时间排序的前10条里面含有左连接的查询语句。

方法二:使用pt(Percona Toolkit)工具的pt-query-digest进行统计分析。
这个是由Percona公司出品的一个用perl编写的脚本,只有安装上pt工具集才会存在,有兴趣的朋友就要先安装pt工具了。直接分析慢查询文件,执行如下:

pt-query-digest slow_querys.log >t.txt

因为记录里还是可能有很多sql在,看起来还是费劲,所以建议输出到文件来看了,具体不多过多介绍。

参考资料: https://blog.csdn.net/enweitech/article/details/80239189
https://blog.51cto.com/arthur376/1948944

  • 13
    点赞
  • 29
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL 的配置文件是一个重要的组成部分,它决定了 MySQL 服务器的行为和功能。下面是 MySQL 配置文件的一些重要参数的详解: 1. `datadir`:指定 MySQL 数据文件的存储路径。默认情况下,数据文件存储在 `/var/lib/mysql` 目录下。 2. `port`:指定 MySQL 服务器监听的端口号。默认端口号是 3306。 3. `bind-address`:指定 MySQL 服务器绑定的 IP 地址。默认情况下,它绑定在所有可用的 IP 地址上。 4. `max_connections`:指定允许的最大并发连接数。这个值取决于你的硬件和应用程序的需求。 5. `character_set_server`:指定服务器默认字符集。见的字符集有 utf8、utf8mb4 等。 6. `collation_server`:指定服务器默认的排序规则。它与字符集相关,用于确定字符串比较和排序的规则。 7. `log_error`:指定错误日志文件的路径。MySQL 会将错误信息记录在这个文件中,以便进行故障排查。 8. `slow_query_log`:启用慢查询日志功能。当查询执行时间超过设定阈值时,将会被记录在慢查询日志中。 9. `innodb_buffer_pool_size`:指定 InnoDB 存储引擎使用的缓冲池大小。这个参数决定了 InnoDB 存储引擎能够缓存的数据量。 10. `innodb_log_file_size`:指定 InnoDB 存储引擎的日志文件大小。这个参数影响到事务处理的性能和恢复过程的速度。 这些只是 MySQL 配置文件中的一部分参数,还有很多其他参数可以根据需求进行配置。配置文件的路径通常是 `/etc/my.cnf` 或 `/etc/mysql/my.cnf`,具体路径可能因操作系统而异。在修改配置文件后,需要重启 MySQL 服务器使修改生效。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值