原创

MySQl慢查询详解

作者:作者 围观群众:41 更新于 标签:mysql优化mysql慢查询mysql慢查询详解

简介

在数据库管理系统中,性能优化是确保系统高效运行的关键因素之一。MySQL 作为一款广泛使用的关系型数据库,其性能优化尤为重要。其中,慢查询的分析与优化是提升数据库性能的重要手段。 慢查询是指执行时间超过预设阈值的 SQL 查询语句。默认情况下,MySQL 会将执行时间超过 10 秒的查询定义为慢查询。慢查询通常会导致数据库性能下降,影响系统的整体响应时间。

使用

一、先确定自己是否开启了慢查询,没有开启的话开启慢查询

  • 第一种方式可以通过执行命令开启慢查询
show variables like "%slow%";

慢查询结果图片

slow_query_log为OFF,表示未开启慢查询,我们通过set global slow_query_log=on; 命令来开启慢查询。

slow_query_log_file是存放慢查询日志的地址

查询当前配置的慢查询查询时间阈值并设置时间,默认为10s

 show variables like "long_query_time";

慢查询时间阈值图片

set long_query_time = 0.05;

0.05为自己设定的慢查询 阈值。超过该值sql,将被记录到存放慢查询的日志文件中。

  • 第二种方式通过修改mysql的配置开启慢查询

在 MySQL 配置文件(通常是 my.cnfmy.ini)中,添加或修改以下配置项:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
  • slow_query_log:启用慢查询日志,设置为 1。
  • slow_query_log_file:指定慢查询日志文件的路径。
  • long_query_time:设置慢查询的时间阈值,单位为秒。例如,设置为 1 表示查询执行时间超过 1 秒时会被记录。

重启mysql服务使配置生效。

二、慢查询日志查看

慢查询日志

Time :日志记录的时间

User@Host:执行的用户及主机

Query_time:查询耗费时间 Lock_time 锁表时间 Rows_sent 发送给请求方的记录条数 Rows_examined:语句扫描的记录条数

SET timestamp:语句执行的时间点

三、使用mysql自带的mysqldumpslow来进行分析。

mysqldumpslow 是 MySQL 自带的一个工具,用于解析和汇总慢查询日志文件。它可以帮助你快速识别出哪些 SQL 查询执行时间较长,从而进行针对性的优化。以下是 mysqldumpslow 的详细使用指南:

基本用法

mysqldumpslow 的基本用法如下:

mysqldumpslow [options] slow_query_log_file
  • options:各种选项,用于控制输出的格式和内容。
  • slow_query_log_file:慢查询日志文件的路径。

常用选项

以下是一些常用的 mysqldumpslow 选项:

  • -s:指定排序方式。常用的排序方式包括:
    • t:按总执行时间排序(默认)。
    • at:按平均执行时间排序。
    • c:按执行次数排序。
    • l:按锁定时间排序。
    • al:按平均锁定时间排序。
    • r:按返回行数排序。
    • ar:按平均返回行数排序。
  • -t:限制输出查询的数量。例如,-t 10 表示只输出前 10 个查询。
  • -g:使用正则表达式过滤查询。例如,-g 'SELECT' 表示只输出包含 SELECT 的查询。

示例

示例 1:按总执行时间排序并输出前 10 个慢查询

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

示例 2:按平均执行时间排序并输出前 5 个慢查询

mysqldumpslow -s at -t 5 /var/log/mysql/mysql-slow.log

示例 3:只输出包含 SELECT 的查询

mysqldumpslow -g 'SELECT' /var/log/mysql/mysql-slow.log

输出格式

mysqldumpslow 的输出格式类似于以下内容:

Count: 10  Time=2.00s (20s)  Lock=0.00s (0s)  Rows=10.0 (100), root[root]@localhost
  SELECT * FROM table_name WHERE column_name = N

Count: 5  Time=3.00s (15s)  Lock=0.00s (0s)  Rows=5.0 (25), root[root]@localhost
  SELECT * FROM table_name WHERE another_column = N
  • Count:该类查询的总执行次数。
  • Time:该类查询的总执行时间。
  • (20s):所有该类查询的总执行时间。
  • Lock:该类查询的总锁定时间。
  • (0s):所有该类查询的总锁定时间。
  • Rows:该类查询的平均返回行数。
  • (100):所有该类查询的总返回行数。
  • root[root]@localhost:执行该查询的用户和主机。
  • SELECT * FROM table_name WHERE column_name = N:具体的查询语句。N 表示占位符,代表具体的数值或字符串。

注意事项

  1. 占位符处理mysqldumpslow 会将查询语句中的具体数值或字符串替换为 NS,以便更好地进行聚合。如果你想查看具体的数值或字符串,可以在分析后手动查看慢查询日志文件。
  2. 日志文件路径:确保指定的慢查询日志文件路径正确,且你有权限访问该文件。
  3. 性能影响mysqldumpslow 在处理大型慢查询日志文件时可能