[Mysql]慢查询日志

慢查询

如何找到慢查询语句

要找出项目中的慢查询语句，可以通过几种方法，主要取决于你使用的数据库系统。以下是一些通用的步骤和针对MySQL和PostgreSQL数据库的具体方法：

通用步骤：

1. 启用慢查询日志：大多数数据库管理系统都支持慢查询日志，通过配置可以记录执行时间超过指定阈值的查询。
2. 分析日志：一旦慢查询日志启用，系统就会记录所有慢查询。你可以分析这些日志文件，找出执行最慢的查询。
3. 使用数据库分析工具：许多数据库系统提供了分析工具或命令，可以帮助识别慢查询。

MySQL：

在MySQL中，你可以通过以下步骤启用和查看慢查询日志：

1. 启用慢查询日志：可以在my.cnf（Linux）或my.ini（Windows）配置文件中设置以下参数启用慢查询日志，并定义慢查询的阈值（例如，设置为2秒）：

   [mysqld]
   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/mysql-slow.log
   long_query_time = 2
   

2. 重启MySQL服务：更改配置后，需要重启MySQL服务使设置生效。

3. 分析慢查询日志：使用mysqldumpslow工具或直接查看日志文件来分析慢查询。例如，通过以下命令查看慢查询日志：

   mysqldumpslow /var/log/mysql/mysql-slow.log
   

慢查询日志用来记录在 MySQL 中执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

默认情况下，慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志功能，开启慢查询日志可能会有一定的性能影响。命令和执行过程如下：

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/c629e6102239-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

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

参数说明：

slow_query_log：慢查询开启状态
slow_query_log_file：慢查询日志存放的位置（一般设置为 MySQL 的数据存放目录），默认的文件名为{hostname}-slow.log
long_query_time：查询超过多少秒才记录，默认为 10 秒，最小为 0，精度可达微秒

设置慢查询日志

可以通过log-slow-queries选项开启慢查询日志。通过long_query_time选项来设置时间值，时间以秒为单位。如果查询时间超过了这个时间值，这个查询语句将被记录到慢查询日志。

[mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysql/long_query.log
long_query_time=1
slow_query_log：设置为ON开启慢查询
slow_query_log_file：指定慢查询的存放位置
long_query_time：指定慢查询阈值，单位为秒
还可以使用log_output参数来指定日志的输出方式，默认会输出到文件，当然也可以选择输出到表，需要注意的是，如果选择输出到表，则表中记录的慢查询时间只能精确到秒，而日志文件中可以精确到微秒。

也可以通过命令行临时开启：

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

mysql> set long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

查看慢查询日志

和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。

查询log_query_time设置的值

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

为了方便测试，这里将long_query_time设置为 1s。

mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

执行一个“慢”查询

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

这个查看耗时大于等于 1s ，会出现在慢查询中。

查看慢查询日志

# more  long_query.log 
# Time: 2023-04-05T08:47:45.868233-05:00
# User@Host: root[root] @ devstack [192.168.122.10]  Id:     9
# Query_time: 1.000723  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use sakila;
SET timestamp=1680698865;
select sleep(1);

日志说明：

慢查询以#作为起始符号。上面的# Time: 2023-04-05T08:47:45.868233-05:00开始记录了一个慢查询
User@Host：表示用户和慢查询的连接地址。上述表示 root 用户，devstack 地址，Id为 9。
Query_time：表示 SQL 查询的耗时，单位为秒
Lock_time：表示获取锁的时间，单位为秒
Rows_sent：表示发送给客户端的行数
Rows_examined：表示服务器层检查的行数
use sakila;表示使用sakila数据库
SET timestamp：表示慢 SQL 记录时的时间戳
最后一行表示慢查询 SQL 语句。上面的是：select sleep(1);

mysqldumpslow工具

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL 客户端安装自带）来对慢查询日志进行分类汇总。

下例中对日志文件long_query.log进行了分类汇总，只显示汇总后摘要结果：

mysqldumpslow long_query.log

Reading mysql slow query log from long_query.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N) as a, N as b

Count: 1  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@devstack
  select sleep(N)

对于 SQL 文本完全一致，只是变量不同的语句，mysqldumpslow将会自动视为同一个语句进行统计，变量值用N来代替。这个统计结果将大大增加用户阅读慢查询日志的效率，并迅速定位系统的 SQL 瓶颈。

注意事项

在默认情况下，有两类常见语句不会记录到慢查询日志：

管理语句
不使用索引进行查询的语句。
这里的管理语句包括 ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、 DROP INDEX、OPTIMIZE TABLE和 REPAIR TABLE。如果要监控这两类 SQL 语句，可以分别通过参数log_slow_admin_statements和log_queries_not_using_indexes进行控制。

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
比如有100条慢sql，如何快速找出出现频次最高的前5条。
查看mysqldumpslow的帮助文档
在Linux命令行窗口执行mysqldumpslow --help

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

常用命令案例
日志文件地址：/var/lib/mysql/slow.log

 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
 
 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
 
 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
 另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more