MySQL慢查询开启，定位慢SQL语句方法

前言

做网站开发的同学肯定遇到过这种情况：网站打开越来越慢，数据库CPU占用很高，但就是不知道哪条SQL语句拖慢了整个系统。这时候就需要用到MySQL的慢查询日志功能，把执行慢的SQL语句记录下来，然后逐个优化。

这篇文章就详细讲讲如何开启MySQL慢查询日志，以及怎么用它来定位和优化慢SQL语句。

操作步骤

1. 先查看当前慢查询日志的状态

登录MySQL之后，先看看慢查询日志有没有开启：

SHOW VARIABLES LIKE '%slow_query_log%';

执行完之后会看到类似下面的输出：

+---------------------+-----------------------------------------------+
| Variable_name       | Value                                         |
+---------------------+-----------------------------------------------+
| slow_query_log      | OFF                                           |
| slow_query_log_file | /var/lib/mysql/izuf6w7x1x2x3x4x5x6x-slow.log  |
+---------------------+-----------------------------------------------+

从这里可以看到，slow_query_log 是 OFF 状态，说明慢查询日志没有开启。

2. 查看当前慢查询时间阈值

慢查询的默认时间阈值是10秒，也就是说执行时间超过10秒的SQL才会被记录。这个阈值对生产环境来说太大了，一般我们会改成1秒或者2秒。

查看当前阈值：

SHOW VARIABLES LIKE 'long_query_time';

3. 临时开启慢查询日志（重启后失效）

如果只是想临时排查问题，可以直接在MySQL命令行里开启，不需要重启服务：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询阈值为1秒
SET GLOBAL long_query_time = 1;

这样设置之后，执行时间超过1秒的SQL语句就会被记录到慢查询日志文件里了。

4. 永久开启慢查询日志（修改配置文件）

临时开启的方式在MySQL重启之后就会失效，如果想长期开启，需要修改MySQL的配置文件。

找到MySQL的配置文件，一般是 /etc/my.cnf 或者 /etc/mysql/my.cnf，在 [mysqld] 下面添加以下配置：

[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志文件位置
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值，单位秒
long_query_time = 1
# 没有用到索引的SQL也记录下来
log_queries_not_using_indexes = 1

修改完配置文件之后，重启MySQL服务：

systemctl restart mysqld

5. 查看慢查询日志内容

慢查询日志开启之后，执行慢的SQL语句就会被记录到日志文件里。我们可以直接查看日志文件：

cat /var/log/mysql/slow.log

不过慢查询日志的格式比较复杂，直接看日志文件不太直观。我们可以用 mysqldumpslow 工具来分析：

# 查看最慢的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 查看访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

6. 使用EXPLAIN分析慢SQL

找到慢SQL之后，下一步就是分析这条SQL为什么慢。我们可以用 EXPLAIN 关键字来查看SQL的执行计划：

EXPLAIN SELECT * FROM users WHERE name = '张三';

执行完之后会输出很多字段，我们重点关注这几个：

• type：访问类型，最好是 const、eq_ref，最差是 ALL（全表扫描）
• key：实际用到的索引，如果是 NULL 说明没用到索引
• rows：扫描的行数，这个值越大越慢
• Extra：额外信息，如果看到 Using filesort 或者 Using temporary 就说明需要优化了

常见坑

坑1：开启了慢查询但日志文件里什么都没有

很多同学开启了慢查询日志，但是去看日志文件却发现什么都没有。这通常是因为以下几个原因：

1. 慢查询时间阈值设置得太大了，你的SQL执行时间还没超过阈值
2. 日志文件的路径不对，你看的不是正确的日志文件
3. MySQL没有权限写入日志文件，需要检查文件权限

坑2：long_query_time设置了但不生效

有时候你设置了 long_query_time = 1，但是发现执行时间0.5秒的SQL也被记录下来了。这是因为当前已经建立的MySQL连接还是用的旧的阈值，需要重新连接MySQL才能生效。

坑3：慢查询日志文件太大占满磁盘

如果你的网站访问量很大，慢查询日志可能会增长得非常快，几天就把磁盘占满了。所以一定要配置日志切割，或者定期清理慢查询日志文件。

总结

MySQL慢查询日志是定位数据库性能问题的必备工具，核心就三步：

1. 开启慢查询日志，设置合理的时间阈值（一般1秒）
2. 用mysqldumpslow工具分析慢查询日志，找到最慢的SQL
3. 用EXPLAIN分析慢SQL的执行计划，针对性优化（加索引、改写SQL等）

掌握了慢查询日志的使用方法，网站数据库性能问题就能快速定位和解决了。

遇到问题加QQ23979811 协助处理