MySQL (一) —— MySQL性能优化之慢查询日志

柔情只为你懂 2022-05-03 00:26 352阅读 0赞

##                                         慢查询日志**（slow-query-log）** ##

### **一、什么是慢查询日志？** ###

**慢查询日志**是MySQL提供的一种日志记录，用来记录在MySQL中**响应**(执行)**时间**超过**阀值**（**long\_query\_time**，默认值：**10**，单位：**秒**）的**SQL**语句。

**默认情况下，MySQL没有启用慢查询日志。**进行MySQL调优时，则可以开启慢查询日志，调优完成后，建议**关闭**慢查询日志，因为开启慢查询日志会或多或少带来一定的性能影响。

**登陆数据库：mysql –uroot –p**   
Enter password:

**查看 MySQL 慢查询日志**

1、查看慢查询日志是否开启：mysql> **show variables like 'slow\_query\_log%'；**

**　　slow\_query\_log** 慢查询开启状态 **OFF** 未开启；**ON** 为开启；

2、查看慢查询日志超时时间：mysql> **show variables like 'long\_query\_time'；**

**　　long\_query\_time** 查询超过多少秒才记录   默认**10**秒，修改为**1秒。**

## ##

### **二、****如何开启 MySQL** 慢查询日志**：** ###

**两种方法：**

1、**临时**开启慢查询：不需要重启数据库，但当 MySql **重启**后慢查询就**失效**。

mysql> **set global slow\_query\_log=1；**   **|| **  mysql> **set global slow\_query\_log=on；**

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

2、修改**配置**文件 **/etc/my.cnf ；**找到 **\[mysqld\]** 下面添加：

slow_query_log =1
    slow_query_log_file="/home/data/mysql/localhost-slow.log"
    long_query_time = 1

修改后**重启** MySQL：**service mysqld restart ；**

再次，查看慢查询日志开启状态：mysql> **show variables like '%query%'；**

**slow\_query\_log：ON** （已开启）

**slow\_query\_log\_file：/home/data/mysql/localhost-slow.log**（表示指定慢查询日志的存储路径）。

mysql> show variables like '%query%';
    +------------------------------+-------------------------------------+
    | Variable_name                | Value                               |
    +------------------------------+-------------------------------------+
    | binlog_rows_query_log_events | OFF                                 |
    | ft_query_expansion_limit     | 20                                  |
    | have_query_cache             | YES                                 |
    | long_query_time              | 1.000000                            |
    | query_alloc_block_size       | 8192                                |
    | query_cache_limit            | 1048576                             |
    | query_cache_min_res_unit     | 4096                                |
    | query_cache_size             | 1048576                             |
    | query_cache_type             | OFF                                 |
    | query_cache_wlock_invalidate | OFF                                 |
    | query_prealloc_size          | 8192                                |
    | slow_query_log               | ON                                  |
    | slow_query_log_file          | /home/data/mysql/localhost-slow.log |
    +------------------------------+-------------------------------------+
    13 rows in set (0.00 sec)

## ##

### 三、**慢查询日志分析工具  --  mysqldumpslow** ###

**　　mysqldumpslow**是**MySQL自带**的分析慢查询的工具。

测试：mysql> **select sleep(2.0)；**

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

查看**帮助**命令：\[root@localhost ~\]\# **mysqldumpslow -h **

[root@localhost ~]# mysqldumpslow -h
    Option h requires an argument
    ERROR: bad option
    
    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  表示正则匹配模式，不区分大小写。`

**mysqldumpslow使用举例：**

1、获取前 3 个SQL语句**  -t 3**

\[root@localhost ~\]\# **mysqldumpslow -t 3 /home/data/mysql/localhost-slow.log**

[root@localhost ~]# mysqldumpslow -t 3 /home/data/mysql/localhost-slow.log
    
    Reading mysql slow query log from /home/data/mysql/localhost-slow.log
    Count: 3  Time=2.29s (6s)  Lock=0.00s (0s)  Rows=1.0 (3), mmo_admin[mmo_admin]@[192.168.1.230]
      select sleep(N.N)
    
    Died at /usr/bin/mysqldumpslow line 161, <> chunk 3.

其中：

Count: 3  Time=2.29s (6s)  Lock=0.00s (0s)  Rows=1.0 (3), mmo\_admin\[mmo\_admin\]@\[192.168.1.230\]  
   　　select sleep(N.N)

表示：

Count  出现次数=3次

Time  执行最长时间=2.29秒(累计总耗费时间=6秒)

Lock  等待锁的时间=0秒

Rows  发送给客户端的行总数=1行(扫描的行总数=3行)

mmo\_admin  用户=mmo\_admin @ 当前登录IP=192.168.1.230

select sleep(N.N)  实际执行的SQL语句(抽象化类型SQL)

2、按照时间排序且含有'sleep'的 top 3 个SQL语句  **-s t  -t 3 -g "sleep"**

\[root@localhost ~\]\# **mysqldumpslow -s t -t 3 -g "sleep" /home/data/mysql/localhost-slow.log | more**

[root@localhost ~]# mysqldumpslow -s t -t 3 -g "sleep" /home/data/mysql/localhost-slow.log | more
    
    Reading mysql slow query log from /home/data/mysql/localhost-slow.log
    Died at /usr/bin/mysqldumpslow line 161, <> chunk 3.
    Count: 3  Time=2.29s (6s)  Lock=0.00s (0s)  Rows=1.0 (3), mmo_admin[mmo_admin]@[192.168.1.230]
      select sleep(N.N)

在命令后面加上：**| more** ，可以避免可能出现的刷屏的情况，便于查看。

3、获取访问次数最多的 5 个SQL语句  **-s c -t 5**

\[root@localhost ~\]\# **mysqldumpslow -s c -t 5 /home/data/mysql/localhost-slow.log | more**

## ##

### 四、小结 ###

1、**默认情况下，MySQL没有启用慢查询日志；**

2、**开启 MySQL** 慢查询日志有**两种方法：**mysql> **set global slow\_query\_log=1** 或者修改**配置**文件 **/etc/my.cnf ；**

3、可以使用**MySQL自带**的分析**工具：mysqldumpslow ，**进行**慢查询日志分析；**

4、调优完成后，建议**关闭**慢查询日志**。**