MySQL慢查询日志使用与分析

最新推荐文章于 2024-10-16 12:41:35 发布

shangjg3

最新推荐文章于 2024-10-16 12:41:35 发布

阅读量399

点赞数 3

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/shangjg03/article/details/141628022

版权

数据库专栏收录该内容

20 篇文章 0 订阅

订阅专栏

分析慢SQL的步骤

1.慢查询的开启并捕获：开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，至少跑1天，看看生产的慢SQL情况，并将它抓取出来

2.explain + 慢SQL分析

3.show Profile。（比explain还要详细，可以查询SQL在MySQL数据库中的执行细节和生命周期情况）

4.运维经理 OR DBA，进行MySQL数据库服务器的参数调优。（后端程序员没有这个权限）

1.慢查询日志（定位慢sql）

1.1慢查询日志是什么？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10秒以上的语句
由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前explain进行全面分析

特别说明

**默认情况下，MySQL数据库没有开启慢查询日志，**需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

1.2查看慢查询日志是否开以及如何开启

查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。
开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

-- 指定数据库
mysql> use mysql;
Database changed
--  查看慢查询日志是否开启
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
--  开启慢查询日志
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

如果要使慢查询日志永久开启（不推荐，浪费性能），需要修改my.cnf文件，在[mysqld]下增加修改参数。

1 # my.cnf
2 [mysqld]
3 # 开启慢查询
4 slow_query_log=ON  
5 # 指定存储慢查询日志的文件。如果这个文件不存在，会自动创建
6 slow_query_log_file=/var/lib/mysql/slow.log

1.3设置慢SQL的时间阈值

查看阈值

时间阈值是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。

MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

注意：是超过阈值才会被记录，等于不会被记录

设置阈值

--  设置阈值
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
--  可以发现设置没有成功
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

也可以不重启连接，使用如下命令直接查看

show global variables like 'long_query_time';

也直接在my.cnf配置文件中修改

[mysqld]

long_query_time=1

1.4查询慢查询日志文件中的总记录条数

2.日志分析

2.1模拟慢查询

mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.00 sec)

use advanced_mysql_learning：使用的数据库
Query_time：实际查询时间，单位是秒
Lock_time：锁时间
select sleep(4)：超时的语句

2.2常见问题

在对慢查询进行查看的时候发现时间不对，正好与系统时间相差8个小时。可以查看mysql如下环境变量：

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

UTC大家都知道是世界统一时间，而我现在的系统时间是东八区，比UTC早了8个小时，这就对上了。

修改参数就可以解决问题。

mysql> set global log_timestamps = system;
Query OK, 0 rows affected (0.00 sec)

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