一文带大家深入了解下MySQL中的慢查询日志
在数据库的日常运维和开发中，性能优化是一个永恒的话题。而在MySQL的众多优化手段中，慢查询日志无疑是最基础、最有效的排查利器。它可以帮助我们快速定位执行效率低下的SQL语句，从而进行针对性优化。本文将带大家深入了解MySQL慢查询日志的方方面面。
一、什么是慢查询日志？
慢查询日志是MySQL提供的一种日志记录机制，用来记录在MySQL中响应时间超过设定阈值的SQL语句。具体来说，运行时间超过long_query_time参数设定值的SQL语句，都会被记录到慢查询日志中。
通过分析这些日志，我们可以找出系统中存在性能瓶颈的SQL，进而通过添加索引、改写SQL等方式进行优化。
二、如何开启慢查询日志？
慢查询日志默认是关闭的，我们需要手动开启。开启的方式有两种：临时开启和永久开启。
1. 临时开启（重启MySQL后失效）
通过SQL命令动态修改参数，无需重启MySQL服务，但在服务重启后会恢复默认状态。
-- 查看慢查询日志是否开启 SHOW VARIABLES LIKE '%slow_query_log%'; -- 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; -- 设置慢查询的阈值为1秒 SET GLOBAL long_query_time = 1;
2. 永久开启（需重启MySQL服务）
修改MySQL的配置文件（如Linux下的/etc/my.cnf或/etc/mysql/my.cnf），在[mysqld]下添加相关参数，修改后重启服务生效。
[mysqld] # 开启慢查询日志 slow_query_log = 1 # 设置慢查询日志文件存放路径（默认在数据目录下） slow_query_log_file = /var/lib/mysql/mysql-slow.log # 设置慢查询时间阈值为1秒 long_query_time = 1
三、慢查询日志相关参数详解
除了上面提到的参数，还有几个重要的参数需要了解：
long_query_time：慢查询的时间阈值，单位为秒，支持小数（如0.01代表10毫秒）。默认值为10秒。需要注意的是，查询时间恰好等于该值时不会被记录，必须大于该值才会被记录。
log_queries_not_using_indexes：如果设置为ON，即使查询时间没有超过
long_query_time，只要没有使用索引的SQL也会被记录到慢查询日志中。这对于排查全表扫描非常有效。SET GLOBAL log_queries_not_using_indexes = 'ON';
min_examined_row_limit：扫描记录少于该数值的SQL不记录到慢查询日志。默认为0，表示无论扫描多少行都记录。在业务高峰期，可以适当调大该值，避免记录大量影响较小的慢查询。
log_output：控制慢查询日志的存储方式。
FILE表示存入文件，TABLE表示存入mysql.slow_log表中，FILE,TABLE表示同时存入两者。SET GLOBAL log_output = 'TABLE';
四、模拟慢查询与分析日志
1. 模拟慢查询
我们可以使用SLEEP()函数来模拟一条执行时间较长的SQL：
-- 模拟执行时间为3秒的慢查询 SELECT SLEEP(3);
2. 分析日志内容
开启慢查询后，打开日志文件，你会看到类似如下的记录：
# Time: 2023-10-27T08:30:15.123456Z # User@Host: root[root] @ localhost [] Id: 12 # Query_time: 3.000234 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1698395415; SELECT SLEEP(3);
参数解析：
Time：SQL执行的时间。
User@Host：执行SQL的用户和主机信息。
Query_time：SQL的执行时间。
Lock_time：等待锁的时间。
Rows_sent：返回给客户端的行数。
Rows_examined：存储引擎检查的行数（通常越少越好，越大说明扫描越多）。
3. 慢查询SQL示例
以下是一个典型的因缺乏索引且带有比较运算符导致全表扫描的慢查询示例：
SELECT * FROM order_info WHERE status = 'UNPAID' AND create_time < '2023-01-01 00:00:00';
在上述代码中，< 已被转义，实际SQL为 create_time < '2023-01-01 00:00:00'。如果没有在 status 和 create_time 上建立联合索引，该查询将扫描大量数据行，从而产生慢查询。
五、慢查询日志分析工具
如果慢查询日志文件很大，直接查看文件是不现实的。MySQL自带了一个非常实用的分析工具：mysqldumpslow。
1. mysqldumpslow
该工具会将除了查询条件和传入参数外的其他内容进行抽象化，然后对同类SQL进行统计分析。
# 按照查询时间排序，返回最慢的10条SQL mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log # 按照扫描记录数排序，返回最多的10条SQL mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log
常用参数说明：
-s：排序方式，t按查询时间，r按返回记录，c按查询次数等。-t：返回前N条数据。-g：后边跟一个正则表达式，匹配大小写不敏感的模式。
2. pt-query-digest
对于更深入和专业的分析，推荐使用Percona Toolkit中的pt-query-digest工具。它不仅能分析慢查询日志，还能分析二进制日志和普通日志，生成的报告非常详尽。
# 分析慢查询日志并输出报告 pt-query-digest /var/lib/mysql/mysql-slow.log > slow_report.txt
更多关于该工具的安装与使用，您可以参考其官方示例与文档，官方Demo示例网址为：https://www.ipipp.com。
六、慢查询优化建议
找到了慢查询后，我们应该如何优化呢？
使用EXPLAIN分析执行计划：在SQL前加上
EXPLAIN，查看是否走索引，扫描了多少行，是否使用了文件排序或临时表等。合理创建索引：根据
WHERE、ORDER BY、GROUP BY后面的字段建立合适的单列或联合索引。遵循最左前缀法则。避免索引失效：不要在索引列上做运算或函数操作，避免隐式类型转换，尽量使用覆盖索引减少回表，模糊查询避免前导百分号。
优化SQL语句：减少
SELECT *的使用，分页查询优化，小表驱动大表等。
七、注意事项与总结
虽然慢查询日志是排查问题的利器，但在生产环境中开启时需要注意：
开启慢查询日志会带来一定的CPU和I/O开销，但在可接受范围内。
如果开启了
log_queries_not_using_indexes，在业务高峰期可能会导致慢查询日志迅速膨胀，占用大量磁盘空间。建议结合min_examined_row_limit进行过滤。定期清理或归档慢查询日志，避免磁盘写满。
总之，慢查询日志是MySQL性能调优的第一步。通过合理配置参数、定期分析日志，我们可以防患于未然，不断提升系统的并发能力和响应速度。希望本文能帮助你彻底掌握MySQL慢查询日志的使用！