手把手教你定位线上MySQL慢查询问题，包教包会

逃离我推掉我的手 2023-10-01 23:14 18阅读 0赞

### 1. 慢查询日志的作用 ###

慢查询日志默认不开启，建议手动开启，方便我们定位线上问题。

执行时间超过阈值的SQL会被写入到慢查询日志当中，这样可以帮助我们记录执行时间过长的SQL语句，定位线上慢SQL问题，方便我们进行SQL性能调优。

### 2. 慢查询日志的配置 ###

#### 2.1 查看是否开启了慢查询日志 ####

show variables like 'slow_query_log';
    复制代码

![format_png][]

默认是OFF，不开启，可以手动开启。

#### 2.2 开启慢查询日志 ####

一种方法是可以使用MySQL命令开启：

set global slow_query_log=1;
    复制代码

![f7736eeba8241259fe3c991fe6ae414d.jpeg][]

另一种方法是修改MySQL配置文件，重新MySQL服务后，开启。

修改配置文件my.cnf，加入下面一行命令

slow\_query\_log = ON

#### 2.3 设置慢查询日志的阈值 ####

慢查询日志的阈值默认是10，单位是秒。

对于线上服务来说，10秒太长了，我们可以手动修改。

![format_png 1][]

一种是通过MySQL命令修改，比如修改为1秒：

set long_query_time=1;
    复制代码

![format_png 2][]

另一种方法是修改MySQL配置文件，重新MySQL服务后，开启。

修改配置文件my.cnf，加入下面一行命令

long\_query\_time = 1

#### 2.4 修改慢查询日志位置 ####

使用MySQL命令查看慢查询日志位置：

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

![format_png 3][]

想要修改慢查询日志位置，可以修改MySQL配置文件，重新MySQL服务后，开启。

修改配置文件my.cnf，加入下面一行命令

slow\_query\_log\_file = /usr/local/mysql/data/localhost\_slow.log

#### 2.5 记录更多慢查询SQL ####

默认情况下管理语句是不会被记录到慢查询日志中，管理语句包括ALTER TABLE、 ANALYZE TABLE、 CHECK TABLE、 CREATE INDEX、 DROP INDEX、 OPTIMIZE TABLE和 REPAIR TABLE等。

![format_png 4][]

管理语句也是非常重要的，如果想要被记录，可以通过MySQL命令修改：

set global log_slow_admin_statements=ON;
    复制代码

![format_png 5][]

默认情况下，不使用索引的语句，也是不会被记录的。

够坑人吧！一不留神就掉坑里了，不记录不使用索引的语句，还要慢查询日志干嘛？

![format_png 6][]

想要记录不使用索引的语句，可以通过命令修改：

set global log_queries_not_using_indexes=ON;
    复制代码

![format_png 7][]

### 3. 慢查询日志的使用 ###

手动造一条慢SQL，测试一下效果，user表中有100万表数据：

select * from user;
    复制代码

然后看一下慢查询日志文件的内容：

cat /usr/local/mysql/data/localhost_slow.log
    复制代码

![format_png 8][]

SQL语句和执行时间都被记录了。

### 4. 分析慢查询日志 ###

有时候慢查询日志较多，手动查看起来并不是很方便，好在MySQL提供了分析慢查询日志的工具 **mysqldumpslow** 。

常用参数有
    
    -s: 表示按何种方式排序：
    　　c: 访问次数
    　　l: 锁定时间
    　　r: 返回记录
    　　t: 查询时间
    　　al: 平均锁定时间
    　　ar: 平均返回记录数
    　　at: 平均查询时间
    -t: 返回前面多少条的数据；
    复制代码

4.1 查询返回结果最多的10条SQL：

mysqldumpslow -s r -t 10 /usr/local/mysql/data/localhost_slow.log

![format_png 9][]

4.2 查询耗时最长的10条SQL：

mysqldumpslow -s t -t 10 /usr/local/mysql/data/localhost_slow.log

![format_png 10][]

![eaf2a971293c4bb5987ebefe0787b692.jpeg][]

[format_png]: https://img-blog.csdnimg.cn/img_convert/293edba3d24cd2663172097df1da4d6a.webp?x-oss-process=image/format,png
[f7736eeba8241259fe3c991fe6ae414d.jpeg]: https://img-blog.csdnimg.cn/img_convert/f7736eeba8241259fe3c991fe6ae414d.jpeg
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/8daf424323b14e1cf197a3657061767c.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/8eb95b9b5be0ae94e8d39d78451b0285.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/17544c703a56fb45ea962ad67fec49e8.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/e7aa779e66b6f9868c6d23ef367ee5b6.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/7e5c8a9892d1f2254d5946dacfa5db6e.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/a72d73b1e7d1dff19b87a67b6ff61820.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/22774bc98e2d235b577d742dd36d8abb.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/11226dd37cf7e3d7c100eb497173fd51.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/4c59ce8bc315099487e80cea92735bf0.webp?x-oss-process=image/format,png
[format_png 10]: https://img-blog.csdnimg.cn/img_convert/37ff5a4ecacff2a6561e21b2fad7db45.webp?x-oss-process=image/format,png
[eaf2a971293c4bb5987ebefe0787b692.jpeg]: https://img-blog.csdnimg.cn/eaf2a971293c4bb5987ebefe0787b692.jpeg