【MySQL】基础实战篇(2)—慢查询日志分析

r囧r小猫 2023-10-15 18:27 23阅读 0赞

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long\_query\_time值的SQL,则会被记录到慢查询日志中。

long\_query\_time的默认值为10，意思是运行10秒以上(不合10秒)的语句，认为是超出了我们的最大忍耐时间值  
它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。  
当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。  
比如一条sq执行超过5秒钟，我们就算慢SQL,希望能收集超过5秒的sql,结合explain 进行全面分析。

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

首先  
先查看一下慢查询是否已经开启

mysql> show variables like '%slow_query_log';

结果如下

+----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | slow_query_log | OFF   |
    +----------------+-------+
    1 row in set (0.03 sec)

OFF 代表关闭状态  
把其设置成打开状态，如下

mysql> set global slow_query_log =on;
    Query OK, 0 rows affected (0.13 sec)

结果如下

接下来设置long\_query\_time值

由此可说，sql中long\_query\_time默认值为10  
语句如下：

mysql > set global long_query_time=1;
    mysql >show global variables like '%long_query_time%';
    
    mysql >set long_query_time=1;
    mysql >show variables like '%long_query_time%';

设置global 的方式对当前session的long\_query\_time失效。对新链接的客户端有效

具体示范如下：

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

此改法在服务器重启之后，会自动失效。  
所以可以在配置文件中修改设置参数以用作永久设置。

> \[mysqld\]  
> slow\_query\_log=ON \#开启慢查询日志  
> slow\_query\_log\_file=/var/lid/mysql/bsk-slow.log \#慢查询日志的目录和文件名信息  
> long\_query\_time=3 \#设置慢查询值为3秒  
> long\_output=FILE

查看是否存在慢查询

慢查询日志分析工具  
mysqldumpslow 定位到慢查询语句  
查看mysqldumpslow的帮助信息 ：

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

首先进入文件所在目录下

[root@localhost ~]# cd /var/lib/mysql
    #会出现如下文件
    -rw-r-----. 1 mysql mysql      178 Sep  7 06:07 localhost-slow.log
    
    #然后在进行如下语句
    [root@localhost mysql]# mysqldumpslow -a -s t -t 5 /var/lib/mysql/localhost-slow.log 
    
    Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
    Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
      
    
    Died at /bin/mysqldumpslow line 162, <> chunk 1.

关闭慢查询日志

方式一：永久性方式

[mysqld]
    #slow_query_log =OFF

方式二：临时性方式

mysql> set global slow_query_log=off;
    Query OK, 0 rows affected (0.00 sec)
    # 查看慢查询日志功能
    mysql> show variables like '%slow_query_log%';
    +---------------------+-----------------------------------+
    | Variable_name       | Value                             |
    +---------------------+-----------------------------------+
    | slow_query_log      | OFF                               |
    | slow_query_log_file | /var/lib/mysql/localhost-slow.log |
    +---------------------+-----------------------------------+
    2 rows in set (0.04 sec)

删除慢查询日志文件

[root@localhost mysql]# rm localhost-slow.log