MySQL慢查询排查常用命令汇总

桃扇骨 2024-02-19 11:55 51阅读 0赞

#### 文章目录 ####

*  一、开启慢查询日志
 *   *  1.永久开启
     *  2.临时开启
 *  二、常用命令
 *  三、分析慢查询日志

## 一、开启慢查询日志 ##

### 1.永久开启 ###

要开启MySQL慢查询日志，需要修改MySQL服务器的配置文件`my.cnf`或者`my.ini`。  
在配置文件中加上如下配置：

# 开启慢查询日志  1:开启|0:关闭
    slow_query_log = 1 
    
    # 指定慢查询的阈值，默认为10秒
    long_query_time = 10 
    
    # 指定慢查询日志的保存位置 (如果路径不存在一定要手动创建，否者可能会因为权限不够，无法记录慢查询日志)
    slow_query_log_file = /var/log/mysql/mysql-slow.log

**完成配置后，需要重启MySQL服务器才能使配置生效。**

### 2.临时开启 ###

SET GLOBAL slow_query_log = 1;
    # 目录必须存在，且有读写权限，不然会报错
    SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
    SET GLOBAL long_query_time = 10;
    # 日志输出格式 TABLE|FILE
    SET GLOBAL log_output = 'FILE';

临时开启的方法下，不需要重启MySQL就可以`生效`，但是MySQL重启后会`失效`。  
**注意：** 设置完可能需要`重新打开会话窗口查询`才能发现配置生效了！

## 二、常用命令 ##

# 查看慢查询日志的完整路径和文件名
    show variables like 'slow_query_log_file'; 
    
    # 查看当前MySQL服务器的慢查询日志是否开启
    show variables like 'slow_query_log';
    
    # 查询慢查询的阈值
    show variables like '%long_query_time%'; 
    
    # 查询慢查询日志输出方式
    show variables like '%log_output%';

## 三、分析慢查询日志 ##

mysqldumpslow 工具是慢查询自带的分析慢查询工具，一般只要安装了mysql，就会有该工具。

**完整语法如下所示：**

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]  -- 后跟参数以及log文件的绝对地址;
    
      -s            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

`mysqldumpslow`命令常见的分析和优化查询日志文件语句如下：

# 取出使用最多的10条慢查询
    mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
    # 取出查询时间最慢的3条慢查询
    mysqldumpslow -s t -t 3 /var/log/mysql/mysql-slow.log
    # 取出查询时间最慢的前10条里面含有左连接的慢查询
    mysqldumpslow -s t -t 10 -g 'left join' /var/log/mysql/mysql-slow.log
    # 取出按照扫描行数最多的前10条慢查询
    mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

其中，`-s t` 表示按查询`总时间`排序，`-s c`表示按`查询次数`排序，`-t 10`表示显示`前10条`查询信息。