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

谁借莪１个温暖的怀抱￠ 2023-09-23 16:13 122阅读 0赞

## 前言 ##

在项目里面，多多少少都隐藏着一些执行比较慢的SQL, 不同的开发测试人员在平时使用的过程中多多少少都能够遇到，但是无法立马有时间去排查解决。那么如果有一个文件能够将这些使用过程中比较慢的SQL记录下来，定期去分析排查，那该多美好啊。这种情况MySQL也替我们想到了，它提供了SQL慢查询的日志，本文就分享下如何使用吧。

## 什么是慢查询日志？ ##

MySQL的慢询日志，提供了记录在MySQL中响应时间超过指定阈值语句的功能，比如设定阈值为3秒，那么任何SQL执行超过3秒都会被记录下来。

我们借助慢查询日志功能可以发现哪些那些执行时间特别长的询，并且有针对性地进行优化，从而提高系统的整体效率。

## 怎么开启慢查询日志？ ##

默认情况下，MySQL数据库**没有开启**慢查询日志，因为多多少少会带来一定性能的影响。我们可以在开发测试环境、或者生产环境做调优的时候开启，**那怎么查看是否开启了呢？**

1.  **查看慢SQL是否开启**

执行下面命令查看是否开启慢SQL

show variables like '%slow_query_log';
    复制代码

![format_png][]

*  `OFF`: 未开启
 *  `ON`: 开启

1.  **如何开启慢查询**

执行下面的命令开启慢查询日志

set global slow_query_log='ON';
    复制代码

![format_png 1][]

1.  **修改慢查询阈值**

前面介绍了SQL执行到达了制定的时间阈值后记录到慢查询日志中，那么如何设置呢？

set global long_query_time = N;
    
    set long_query_time = N
    复制代码

*  设置global的方式对当前`session`的`long_query_time`失效。对新连接的客户端有效。所以可以一并执行下述语句

*  N表示设置的阈值，单位为秒

![format_png 2][]

*  这里的`show global variables like '%long_query_time%';`可以查看阈值大小

1.  **如何设置永久生效**

前面是通过命令行的方式设置，如果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
    复制代码

## 慢查询日志在哪里呢？ ##

前面讲解了如何开启MySQL的慢查询日志，那么它把日志记录在哪里了呢?

1.  **查看慢查询日志位置**

通过`show variables like '%slow_query_log_file%';`命令可以查看慢SQL文件位置，如下图所示：

![format_png 3][]

1.  **修改慢查询日志位置**

也很简单，执行下面的命令即可：

set global slow_query_log_file = '/usr/local/mysql/data/alvin-slow-slow.log';
    复制代码

## 怎么查看慢SQL内容？ ##

现在我们已经知道慢查询日志在哪里了，那么如何查看里面的内容呢？我们这里用一个例子演示下吧。

1.  **执行一个查询的SQL**

![format_png 4][]

*  执行花了1秒多，超过了前面设置的阈值1s

1.  **查看慢查询数目**

执行下面命令查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
    复制代码

![format_png 5][]

*  value=1, 表明刚刚的日志被记录了。

1.  **查看日志内容**

通过`cat`命令查看文件内容，可以看到对应的慢SQL。

![format_png 6][]

### 慢查询日志分析工具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
    复制代码

![format_png 7][]

**常见的用法：**

#得到返回记录集最多的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日志？ ##

现在慢查询日志很多了啊，为了不互相混淆，我要删除一些慢SQL日志，怎么删除呢？

1.  手动删除慢查询日志文件即可, 也就是`rm`命令。
2.  使用命令`mysqladmin flush-logs`重置慢sql日志内容，完整命令如下 ：

mysqladmin -uroot -p flush-logs slow
    复制代码

## 总结 ##

本文讲解了慢SQL日志该如何打开以及使用，是非常重要有效的排查手段，最后记得在排查完毕以后，要关闭慢SQL日志，不然可能影响性能哦。

[format_png]: https://img-blog.csdnimg.cn/img_convert/b38024d6ee96de43eb1c65f8a83e8b93.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/0088333427b8d9fd8bacc06a1e9962c0.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/403cab36c163c4d5f606945fa6ff711b.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/43ce9a10b6e110a2656fd56084ba8435.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/d202c26798fb3080b5dc22e46ed17006.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/0da317d3eef5eb7bfb812d457b2b4ee6.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/4182292faf9ab210643f879e717af524.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/36b9f7f1b9b2d8c2534545c7a6780a6c.webp?x-oss-process=image/format,png