MySQL慢查询日志全解析：从配置到实战优化数据库性能-尧图网络科技

1. 从一次真实的线上事故说起

那天晚上十一点，我正打算关电脑，突然收到监控告警：核心业务接口的响应时间从平时的50ms飙到了5秒以上，应用服务器的CPU使用率也冲到了90%。这可不是小事，用户正在下单呢。我第一时间登录服务器，习惯性地先看了一眼数据库监控面板，好家伙，MySQL实例的CPU和IO都高得离谱，活跃连接数也堆起来了。问题大概率出在数据库上。

这时候，第一反应就是去查慢查询日志。我连上数据库，执行了SHOW PROCESSLIST;，果然看到有几个查询已经执行了十几秒还没结束，状态是“Sending data”。但PROCESSLIST只能看到当前正在执行的，历史中哪些查询拖慢了系统，还得靠慢查询日志这个“黑匣子”。我迅速调整了慢查询的阈值，让它临时记录所有超过1秒的查询，几分钟后，日志文件里就清晰地躺着了那条罪魁祸首：一个缺少关键索引的联表查询，在全表扫描几百万行数据。

这次经历再次印证了，慢查询日志是DBA和开发人员定位数据库性能问题的“第一现场”和“终极证据”。它不像监控图表那样只给一个模糊的“慢”的结论，而是直接把那条拖垮数据库的SQL语句、它的执行时间、扫描行数、返回行数等细节拍在你面前。无论你是刚接手一个老项目，想优化其数据库性能，还是在开发过程中想提前发现潜在的性能瓶颈，学会开启、配置并分析慢查询日志，都是一项必备的核心技能。这篇文章，我就结合多年踩坑经验，带你彻底搞懂MySQL慢查询日志从开启到分析的全流程，让你下次遇到性能问题时，能快速定位，精准打击。

2. 慢查询日志到底是什么？为什么非用不可？

在深入操作之前，我们得先搞清楚这个工具的本质。你可以把MySQL服务器想象成一个繁忙的餐厅厨房，SQL查询就是客人点的菜。慢查询日志，就是厨房经理（MySQL）的一个记录本，专门记录那些做起来特别慢的“菜”（查询）。它不关心菜做得好不好吃（查询结果正确与否），只关心做这道菜花了太长时间。

那么，“慢”的标准是什么？这就是一个可配置的阈值，默认是10秒。也就是说，任何执行时间超过10秒的查询，都会被自动记录到这个日志里。这个阈值可以根据你的业务容忍度进行调整，对于OLTP（在线事务处理）系统，可能1秒就算慢了；对于后台报表系统，10秒或许可以接受。

为什么说它不可或缺？我总结有三大原因：

问题定位的“铁证”：当应用变慢时，原因可能有很多——代码逻辑、网络、中间件、数据库。慢查询日志能直接告诉你，是不是数据库层面的问题，并且精确到是哪一条或哪几条SQL语句导致的。这避免了在错误的方向上浪费时间。
性能优化的“指南针”：通过分析日志，你能发现那些频繁出现且执行缓慢的查询。这些就是你需要优先优化的目标。优化后，你还可以通过对比优化前后的日志，直观地看到优化效果（执行时间缩短、扫描行数减少）。
架构与代码的“照妖镜”：长期分析慢查询日志，你可能会发现一些规律：比如某个功能模块的查询普遍较慢，或者某些类型的查询在业务高峰时容易出问题。这能反过来推动应用架构的改进（如引入缓存、读写分离）或代码的重构（如避免N+1查询）。

很多人觉得开启了慢查询日志会影响性能。确实，写入日志会有额外的I/O开销，但在生产环境中，这个开销与它带来的问题诊断价值相比，通常是微不足道的。况且，我们可以通过合理的配置（如不记录管理语句、设置合适的阈值）来最小化其影响。我的建议是：在测试环境和生产环境，都应该开启慢查询日志，这是对自己系统负责的表现。

3. 手把手配置与开启慢查询日志

知道了“为什么”，接下来就是“怎么做”。MySQL提供了全局变量来控制慢查询日志的行为，我们可以通过命令行动态设置，也可以写入配置文件使其永久生效。下面我分步骤详细说明。

3.1 动态开启与基本配置

首先，我们登录MySQL服务器，查看当前慢查询日志的状态：

-- 查看慢查询日志是否开启 SHOW VARIABLES LIKE 'slow_query_log'; -- 查看慢查询日志文件路径 SHOW VARIABLES LIKE 'slow_query_log_file'; -- 查看“慢”的阈值（单位：秒） SHOW VARIABLES LIKE 'long_query_time'; -- 查看是否记录未使用索引的查询（即使它执行得很快） SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

如果slow_query_log的值是OFF，说明还没开启。我们来开启它并设置一个合理的阈值：

-- 1. 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; -- 2. 设置慢查询阈值，比如设为2秒。注意：对于超时时间设置，可能需要新会话才能生效。 SET GLOBAL long_query_time = 2; -- 3. （可选但推荐）设置慢查询日志文件路径，确保MySQL进程有写入权限 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; -- 4. （可选）开启记录未使用索引的查询。这对于发现潜在的全表扫描很有用，但日志量可能会变大。 SET GLOBAL log_queries_not_using_indexes = 'ON';

注意：SET GLOBAL命令设置的变量只在当前MySQL服务实例运行期间有效，重启后会失效。并且，long_query_time这个变量有个特殊之处：它对当前已建立的会话不生效，只对新建立的会话生效。所以设置后，你需要重新连接MySQL，或者在你的应用连接池中断开重连，才能让新的阈值生效。

执行完上述命令后，慢查询日志就已经开始记录了。你可以故意执行一个睡眠3秒的语句来测试：

SELECT SLEEP(3);

然后去查看你设置的slow_query_log_file（如/var/log/mysql/slow.log），应该就能看到这条查询的记录。

3.2 写入配置文件实现永久生效

动态设置适合临时调试，对于生产环境，我们必须将配置写入MySQL的配置文件（通常是my.cnf或my.ini），以确保服务重启后配置依然有效。

找到你的MySQL配置文件，在[mysqld]段落下添加或修改如下配置：

[mysqld] # 开启慢查询日志 slow_query_log = 1 # 指定慢查询日志文件路径 slow_query_log_file = /var/log/mysql/slow.log # 设置慢查询时间阈值为2秒 long_query_time = 2 # 记录未使用索引的查询（可选，根据情况开启） log_queries_not_using_indexes = 1 # 慢查询日志的存储格式，FILE表示存文件（默认），TABLE可以存到mysql.slow_log表，但性能开销大，一般不推荐。 log_output = FILE

修改保存后，重启MySQL服务使配置生效：

# 对于使用systemd的系统（如CentOS 7+, Ubuntu 16.04+） sudo systemctl restart mysqld # 或 sudo systemctl restart mysql # 对于老版本系统 sudo service mysql restart

重启后，再次使用SHOW VARIABLES命令检查配置是否已按预期加载。

3.3 几个关键的高级配置项

除了上面基本的，还有几个参数能帮你更精细地控制日志记录：

min_examined_row_limit：设置查询扫描过的最少行数阈值。即使查询时间超过了long_query_time，但如果它扫描的行数少于这个值，也不会被记录。这可以用来过滤掉那些虽然执行慢但代价很小的查询（比如在极小表上因锁等待导致的慢查询）。
log_slow_admin_statements：是否将管理语句（如OPTIMIZE TABLE,ANALYZE TABLE,ALTER TABLE等）记入慢查询日志。这些语句本身可能就很慢，记录它们有助于你了解维护操作对系统的影响。
log_throttle_queries_not_using_indexes：当log_queries_not_using_indexes开启时，可能会瞬间产生大量日志（例如一个循环调用全表扫描）。这个参数可以限制每分钟内，相同SQL指纹（即结构相同，参数不同）的未使用索引查询被记录的次数，避免日志爆炸。

配置示例（写入my.cnf）：

[mysqld] ... # 只记录扫描行数超过1000的慢查询 min_examined_row_limit = 1000 # 记录慢的管理语句 log_slow_admin_statements = 1 # 限制每分钟相同未使用索引的SQL最多记录10条 log_throttle_queries_not_using_indexes = 10

4. 解读慢查询日志：一行记录里藏着什么秘密？

配置好并运行一段时间后，你的慢查询日志文件里就会积累不少记录。每一条记录都包含丰富的信息，读懂它们是分析的关键。一条典型的记录长这样（为了可读性已格式化）：

# Time: 2023-10-27T14:23:01.123456Z # User@Host: app_user[app_user] @ [192.168.1.100] Id: 123456 # Query_time: 5.620583 Lock_time: 0.000120 Rows_sent: 1 Rows_examined: 1002543 SET timestamp=1698416581; SELECT * FROM `order` WHERE `user_id` = 123 AND `status` = 'pending' ORDER BY `create_time` DESC;

我们来逐行拆解：

# Time: 查询执行完成的时间点。非常重要，可以结合业务高峰时间分析。
# User@Host: 执行该查询的数据库用户和来源主机。这能帮你定位是哪个应用服务发起的查询。
# Query_time:查询执行总时间，单位秒。这是判断“慢”的核心指标。本例是5.62秒。
# Lock_time: 查询等待锁（表锁、行锁）的时间。如果这个时间占Query_time很大比例，说明可能存在锁竞争。
# Rows_sent: 返回给客户端的数据行数。本例是1行。
# Rows_examined:为了返回结果，服务器层检查的行数。这是另一个关键指标！本例检查了超过100万行，但只返回1行。这是一个非常危险的信号，说明查询可能没有有效利用索引，导致了大量的无效扫描。
SET timestamp: 查询开始执行时的时间戳（Unix时间戳）。可以通过这个时间关联其他监控数据。
最后一行: 就是那条“罪魁祸首”的SQL语句本身。

重点理解Rows_examined与Rows_sent的关系：理想情况下，Rows_examined应该略大于或等于Rows_sent。如果Rows_examined远大于Rows_sent（像例子中的100万 vs 1），几乎可以肯定存在全表扫描或者索引使用不当的问题，这是优化的首要目标。

5. 化繁为简：使用工具高效分析慢查询日志

直接阅读原始的慢查询日志文件效率很低，尤其是当日志文件很大时。我们需要借助工具来聚合、排序、分析。MySQL官方自带了一个非常强大的工具：mysqldumpslow。此外，更强大的第三方工具如pt-query-digest（Percona Toolkit的一部分）是专业DBA的标配。

5.1 使用官方工具 mysqldumpslow 进行初步分析

mysqldumpslow通常随MySQL客户端一起安装。它的基本语法是解析慢查询日志文件，并按照各种方式进行汇总。

# 查看使用帮助 mysqldumpslow --help # 1. 得到返回记录集最多的10条SQL（按Rows_sent排序） mysqldumpslow -s r -t 10 /var/log/mysql/slow.log # 2. 得到访问次数最多的10条SQL（按Count排序） mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 3. 得到按照时间排序，执行时间最长的前10条SQL（这是最常用的！） mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 4. 得到总耗时（Query_time * Count）最长的前10条SQL mysqldumpslow -s at -t 10 /var/log/mysql/slow.log # 5. 组合排序，并且将具体的数字（如user_id）用'N'代替，字符串用'S'代替，便于归类 mysqldumpslow -s t -t 10 -g "SELECT" /var/log/mysql/slow.log

例如，执行mysqldumpslow -s t -t 5 slow.log可能输出：

Count: 12 Time=5.62s (67s) Lock=0.00s (0s) Rows=1.0 (12), app_user[app_user]@[192.168.1.100] SELECT * FROM `order` WHERE `user_id` = N AND `status` = 'S' ORDER BY `create_time` DESC

这告诉我们，同一种模式的查询执行了12次，每次平均耗时5.62秒，总耗时67秒，每次返回1行。这立刻让我们意识到，这不是一个偶然的慢查询，而是一个高频的、有严重性能问题的查询模式，必须优先处理。

5.2 使用专业神器 pt-query-digest 进行深度剖析

pt-query-digest的功能比mysqldumpslow强大得多，它能生成一份非常详细的HTML或文本报告，包括：

总体概览：总查询次数、唯一查询指纹数、总耗时、时间分布等。
响应时间排名：清晰地列出最耗时的查询。
执行属性分析：包括扫描行数、返回行数、临时表、排序等维度的统计。
表与索引使用情况。
查询语句的“指纹”：将具体参数抽象化，真正归类同一类查询。

安装Percona Toolkit后，使用非常简单：

# 生成一份详细的文本报告 pt-query-digest /var/log/mysql/slow.log > slow_report.txt # 生成HTML报告，更直观 pt-query-digest /var/log/mysql/slow.log --review h=localhost,D=slow_query_log,t=global_query_review --history h=localhost,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" > slow_report.html

生成的HTML报告会有一个“Response time distribution”图表，直观展示查询时间的分布；还有“Rank by response time”表格，详细列出每个查询指纹的调用次数、总时间、平均时间、95%时间等，是性能分析的利器。

我的经验是：在例行巡检时，用pt-query-digest生成每日或每周的慢查询分析报告。在紧急故障排查时，先用mysqldumpslow -s t快速找到最慢的几条SQL，立刻着手分析。

6. 从分析到行动：常见的慢查询优化实战

分析出慢查询后，接下来就是优化。这里我结合几个最常见的慢查询模式，给出具体的优化思路和操作。

6.1 案例一：缺失索引导致的全表扫描

这是最经典的问题，对应我们前面例子中的SELECT * FROM order WHERE user_id = 123 AND status = 'pending'，扫描了100万行。

分析：WHERE条件涉及user_id和status两个字段。我们需要判断现有索引情况。

SHOW INDEX FROM `order`;

如果发现没有索引，或者只有一个单列索引（比如只有user_id），那么查询可能无法高效定位数据。

优化：创建联合索引。这里有一个重要的原则：索引列的顺序。通常，将选择性更高（唯一值更多）的列放在前面。我们可以先查看字段的选择性：

SELECT COUNT(DISTINCT user_id) / COUNT(*) as user_id_selectivity, COUNT(DISTINCT status) / COUNT(*) as status_selectivity FROM `order`;

假设user_id选择性远高于status，那么创建(user_id, status)的联合索引会更有效。

ALTER TABLE `order` ADD INDEX idx_user_status (user_id, status);

创建索引后，查询会先通过user_id快速定位到一批数据，再在这批数据中过滤status，Rows_examined会从全表行数骤降到符合条件的行数，性能提升立竿见影。

6.2 案例二：低效的分页查询

慢查询日志里经常看到这样的语句：

SELECT * FROM `article` ORDER BY `create_time` DESC LIMIT 1000000, 20;

Query_time很长，Rows_examined巨大。

分析：LIMIT 1000000, 20意味着MySQL需要先排序，然后跳过前100万行，最后取20行。即使使用了ORDER BY create_time的索引，这个“跳过”的操作（OFFSET）代价也非常高，因为MySQL需要实际扫描并丢弃这100万行。

优化：使用“游标分页”或“基于上次ID的分页”。

-- 传统分页（慢） SELECT * FROM `article` ORDER BY `id` DESC LIMIT 1000000, 20; -- 优化后：记录上一页最后一条记录的ID -- 假设上一页最后一条记录的id是 1234567 SELECT * FROM `article` WHERE `id` < 1234567 ORDER BY `id` DESC LIMIT 20;

这样，查询可以利用id上的主键索引，直接定位到id < 1234567的位置开始扫描，效率极高。前端需要配合传递“最后一个ID”作为参数。

6.3 案例三：复杂的联表查询与临时表

日志中可能出现执行时间长，且Rows_examined各表加起来很大的联表查询，备注里可能还有Using temporary; Using filesort。

分析：例如：

SELECT a.*, b.name, c.category FROM orders a LEFT JOIN users b ON a.user_id = b.id LEFT JOIN products c ON a.product_id = c.id WHERE a.create_time > '2023-10-01' ORDER BY a.amount DESC LIMIT 100;

这个查询涉及三张表关联，并按一个非索引字段amount排序。

优化：

确保连接字段有索引：检查orders.user_id,orders.product_id,users.id,products.id上是否有索引。连接条件（ON子句）的字段必须有索引，这是联表查询性能的基石。
为WHERE条件和ORDER BY创建索引：本例中，WHERE a.create_time > 'xxx'和ORDER BY a.amount DESC是性能关键。可以考虑创建(create_time, amount)的联合索引，但需要注意，范围查询（>）后面的列可能无法用于排序。有时更好的选择是创建(create_time)索引用于过滤，或者如果amount过滤性更好，则创建(amount, create_time)。这需要根据实际数据分布来测试决定。
考虑反范式或汇总表：如果这类报表查询非常频繁且实时性要求不高，可以定期将结果计算好存入一张汇总表，查询直接查汇总表，避免复杂的实时联表计算。
使用EXPLAIN深入分析：对优化后的SQL使用EXPLAIN命令，查看执行计划，确认是否真正用上了你创建的索引，以及连接类型（type）是否从ALL（全表扫描）提升到了ref或range。

7. 将慢查询分析融入日常运维：监控与自动化

手动分析日志毕竟不是长久之计，我们应该将其自动化，并纳入监控体系。

1. 日志轮转与清理慢查询日志会不断增长，需要定期轮转和清理，避免撑满磁盘。可以使用Linux的logrotate工具。创建/etc/logrotate.d/mysql-slow文件：

/var/log/mysql/slow.log { daily rotate 30 compress delaycompress missingok notifempty create 640 mysql mysql postrotate # 如果MySQL支持，可以发送FLUSH LOGS信号，但更简单的方式是在配置中不指定文件，而用`slow_query_log_file`变量。 # 这里我们采用更通用的方法：在my.cnf中配置固定文件名，由logrotate负责切割和创建新文件。 # 切割后，需要让MySQL重新打开日志文件。通常重启MySQL或执行`mysqladmin flush-logs`。 /usr/bin/mysqladmin -uroot -p${密码} flush-logs 2>/dev/null || true endscript }

2. 定期分析报告可以写一个简单的Shell脚本，定期（如每天凌晨）使用pt-query-digest分析前一天的慢查询日志，并将报告发送到邮箱或存入数据库。

#!/bin/bash LOG_FILE="/var/log/mysql/slow.log" YESTERDAY=$(date -d "yesterday" +"%Y-%m-%d") REPORT_FILE="/path/to/reports/slow_report_${YESTERDAY}.html" # 使用pt-query-digest分析 pt-query-digest $LOG_FILE --since "$YESTERDAY 00:00:00" --until "$YESTERDAY 23:59:59" > $REPORT_FILE # 发送邮件（需要配置mailx等） # mail -s "MySQL Slow Query Report - $YESTERDAY" team@example.com < $REPORT_FILE

3. 与监控系统集成将慢查询的数量、最慢查询的执行时间等作为关键指标，接入到Prometheus、Zabbix等监控系统中，并设置告警。例如，当每分钟慢查询数量超过某个阈值，或出现执行时间超过30秒的“超级慢查询”时，立即触发告警，让运维人员能第一时间介入。

开启和用好慢查询日志，就像是给数据库装上了“心电图”和“黑匣子”。它不能直接治病，但能精准地告诉你病根在哪里。从被动救火到主动预防，慢查询日志是你构建稳定、高效数据库系统不可或缺的一环。花点时间把它配置好，并养成定期分析的习惯，你在处理数据库性能问题时，会更有底气，也更加从容。