西瓜空间站

MySQL慢查询

3/27/2023 MySQL数据库

MySQL慢查询

# 一、MySQL慢查询介绍

在MySQL中执行查询、插入、更新、删除等SQL语句时，如果语句的执行时间超过了设定的时间阈值，改 SQL就会被定义为慢查询SQ。在 MySQL 中这个阈值是由参数 long_query_time 来设置的。当执行的查询语句所需的时间超过了long_query_time的设定值，MySQL会将这个SQL语句记录在慢查询日志中，以便后续分析。在慢查询日志中，记录了每条慢查询语句的执行耗时、执行的时间点、执行语句的详细信息等内容，可以用于分析SQL查询性能问题，从而进行查询优化。慢查询的原因可能有很多，比如未设置索引，查询条件不当导致索引失效、表结构设计不合理等等。

# 二、MySQL慢查询

# 2.1 查询MySQL慢查询开启状态

# 查看慢查询开启状态和慢查询日志保存路径

在 MySQL 数据库中执行以下命令

show global variables like '%slow_query_log%';
1

结果如下：

mysql> show global variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_query_log      | ON                                       |
| slow_query_log_file | /data/apps_data/mysql-data/mysqlslow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

slow_query_log:字段值为ON，则表示已经成功开启了慢查询日志功能，OFF表示未开启慢查询记录。

slow_query_log_file:字段值表示慢查询日志的保存路径。

# 查看当前慢查询时间阈值

在 MySQL 数据库中执行以下命令

show variables like '%long_query_time%';
1

结果如下：

mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

显示的value值，即为当前设置的慢查询的时间阈值，单位为秒，如上结果所示，如开启慢查询后，则执行时间超过2秒的SQL将记录在慢查询日志中。

# 2.2 修改MySQL慢查询开启状态

# 2.2.1 MySQL 配置文件修改慢查询

编辑MySQL配置文件my.cnf（或my.ini），保存后重启 MySQL 服务。

# 开启慢查询日志功能
slow_query_log=1
# 慢查询日志文件路径
slow_query_log_file=/var/log/mysql/slow-query.log
# 定义慢查询的时间阈值，单位为秒
long_query_time=10
1
2
3
4
5
6

# 2.2.2 MySQL 语句修改慢查询

注意：语句方式修改的在 MySQL重启后会失效。

# 开启慢查询日志
set global slow_query_log=on;
# 设置慢查询日志文件保存路径
set global slow_query_log_file='/var/log/mysql/slow-query.log';
# 设置慢查询时间阈值 单位秒，不加global只对当前会话有效
set global long_query_time=2;
# 开启未使用索引SQL记录
set global log_queries_not_using_indexes=on;
1
2
3
4
5
6
7
8

# 2.3 MySQL慢查询日志

# 2.3.1 MySQL 慢查询日志解析

以下为一条MySQL慢查询日志的示例

# Time: 2022-01-01T12:34:56.789123Z
# User@Host: dbuser[192.168.0.1] @ mydb
# Query_time: 2.345678  Lock_time: 0.123456 Rows_sent: 10  Rows_examined: 200
SET timestamp=1641032096;
SELECT * FROM mytable WHERE col1 = 'value1' AND col2 = 'value2' ORDER BY col3 DESC LIMIT 10;
1
2
3
4
5

慢查询日志解析：

第一行 Time（时间戳）：SQL执行的时间，上述为 2022年1月1日12时34分56.789123秒，以UTC时间表示。

第二行 User@Host（用户和主机）：使用的 MySQL用户名为dbuser，登录的IP地址为192.168.0.1，连接到的数据库为mydb。

第三行 Query_time（查询时间）：查询时间为2.345678秒，
      Lock_time（锁定时间）：锁定时间为0.123456秒，
      Rows_sent（返回行数）：查询返回了10行数据，
      Rows_examined（扫描行数）：查询扫描了200行数据。

第四行 SET timestamp（时间戳参数）：该查询使用的时间戳参数为1641032096。

第五行 执行的查询SQL语句。
1
2
3
4
5
6
7
8
9
10
11
12

# 2.3.2 使用mysqldumpslow分析慢查询日志

mysqldumpslow是MySQL提供的一个命令行工具，用于分析并汇总MySQL慢查询日志文件。它可以帮助我们找出MySQL慢查询的瓶颈，并快速定位优化的方向。

获取mysqldumpslow可用参数，执行命令mysqldumpslow --help

[root@evan ~]# 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

特别注意的是-s参数，表示按指定方式排序。默认按照平均查询时间（at）排序，还可选择，

  • c: 访问次数

  • l: 锁定时间

  • r: 返回记录的行数

  • t: 查询时间

  • al:平均锁定时间

  • ar:平均返回记录的行数

  • at:平均查询时间

常用分析语句

# 取出查询耗时最久的3条慢查询
# 取出查询耗时最久的3条慢查询
mysqldumpslow -s t -t 3 /data/apps_data/mysql-data/mysqlslow.log
1
2
# 取出查询耗时最久的且含有transaction的3条慢查询
# 取出查询耗时最久的且含有transaction的3条慢查询
mysqldumpslow -s t -t 3 -g "transaction" /data/apps_data/mysql-data/mysqlslow.log
1
2
# 取出返回记录行数最多的3条慢查询记录
# 取出返回记录行数最多的3条慢查询记录
mysqldumpslow -s r -t 3 /data/apps_data/mysql-data/mysqlslow.log
1
2
# 取出访问次数最多的3条慢查询记录
# 取出访问次数最多的3条慢查询记录
mysqldumpslow -s c -t 3 /data/apps_data/mysql-data/mysqlslow.log
1
2