如何配置与查看慢查询

mysql 慢查询相关命令：

# 查看慢查询是否开启
SHOW VARIABLES LIKE '%slow_query_log%'

# 开启慢查询
SET GLOBAL slow_query_log='ON'

# 查看慢查询日志位置
SHOW VARIABLES LIKE '%slow_query_log_file%'

# 查看慢查询阈值，单位：秒
SHOW GLOBAL VARIABLES LIKE '%long_query_time%'

# 修改慢查询阈值，单位：秒
SET long_query_time=1

# 查看慢查询语句的数量
SHOW GLOBAL STATUS LIKE '%Slow_queries'

# 测试执行一个慢查询语句
SELECT SLEEP(3);

前面是通过命令行的方式设置，如果MySQL重启，那么配置就会重置。我们可以通过修改MySQL的配置my.cfg或者my.ini永久生效。

[mysqld]
slow_query_log=ON  # 开启慢查询日志开关
slow_query_log_file=/var/lib/mysql/alvin-slow.log  # 慢查询日志的目录和文件名信息
long_query_time=3  # 设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

可以配置的参数有：

  • slow_query_log：是否启用慢查询日志。
  • slow_query_log_file：指定慢查询日志位置及名称，默认值为host_name-slow.log，可指定绝对路径。
  • long_query_time：慢查询执行时间阈值，超过此时间会记录，默认为10，单位为s。
  • log_output：慢查询日志输出目标，默认为file，即输出到文件。
  • log_timestamps：主要是控制 error log、slow log、genera log 日志文件中的显示时区，默认使用UTC时区，建议改为 SYSTEM 系统时区。
  • log_queries_not_using_indexes：是否记录所有未使用索引的查询语句，默认为off。
  • min_examined_row_limit：对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中，默认为0。
  • log_slow_admin_statements：慢速管理语句是否写入慢日志中，管理语句包含 alter table、create index 等，默认为 off 即不写入。

分析慢查询日志

通过前面命令行的操作，我们可以在慢查询日志文件中看到以下内容：

MySQL, Version: 5.7.28 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2023-06-07T08:01:23.502495Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
# Query_time: 2.999793  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1686124883;
select sleep(3);

写入日志的每个语句都以 # 字符开头。对于每一组慢SQL，第一行记录的是该条 SQL 执行的时刻，第二行记录的是执行该语句的用户和 IP 以及链接 id ，第三行的几个字段解释如下：

  • Query_time: duration 语句执行时间，以秒为单位。
  • Lock_time: duration 获取锁的时间(以秒为单位)。
  • Rows_sent: N 发送给 Client 端的行数。
  • Rows_examined: N 服务器层检查的行数(不计算存储引擎内部的任何处理)。

最下面的两行分别是此语句执行时候的时间戳和具体的慢 SQL 。

慢查询日志分析命令 mysqldumpslow

如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析命令 mysqldumpslow 。

mysqldumpslow 命令的具体参数如下：

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：

  • c: 访问次数
  • l: 锁定时间
  • r: 返回记录
  • t: 查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平均查询时间 （默认方式）
  • ac:平均查询次数

-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的；

mysqldumpslow 位置mysql的bin目录下，以通过执行 mysqldumpslow --help命令查看使用。

举例： 我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

mysqldumpslow -s t -t 5 /usr/local/mysql/data/alvin-slow-slow.log

结果：

mysql 慢查询浅析_mysql

常见的用法：

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /usr/local/mysql/data/alvin-slow-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /usr/local/mysql/data/alvin-slow-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /usr/local/mysql/data/alvin-slow-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /usr/local/mysql/data/alvin-slow-slow.log | more

参考文章

你的哪些SQL慢？看看MySQL慢查询日志吧

MySQL慢日志全解析 - MySQL技术 -