MySQL慢查询日志从配置到实战：定位慢SQL与索引优化指南

1. 先说清楚：慢查询日志到底是个什么东西

很多人聊到MySQL性能调优，第一反应就是加索引、改SQL、调缓存参数，但真正动手的时候却又无从下手。原因很简单：你根本不知道线上哪些SQL是慢的。MySQL慢查询日志（Slow Query Log）干的就是这件事——把执行时间超过阈值的SQL原原本本记下来，让你有据可查，而不是靠猜。

它解决的最核心问题就一句话：在不知道瓶颈在哪的情况下，快速圈定一批“嫌疑SQL”。数据库慢，原因有可能在SQL本身、有可能在索引失效、有可能在锁等待、有可能在大事务，如果没有日志兜底，你就只能碰运气。而有了慢查询日志，你每天看一眼记录，问题基本能定位到具体语句，剩下的才是分析为什么慢。

这个功能不用装插件、不用改代码，MySQL天生自带，从5.0一直延续到8.0，开箱即用。适合所有用MySQL的人：后端开发排查接口卡顿、运维定位数据库负载、DBA做定期巡检。对新手来说尤其友好——你不需要马上懂执行计划，只需要先会开日志、看日志，就已经跑在正确的调优路上了。

我自己的习惯是，从接手一个项目的第二天开始，第一件事就是打开慢查询日志，设一个相对保守的阈值，跑上一周。等一周之后再去翻，哪些SQL是“惯犯”一目了然。这几乎是我做性能优化最快见效的一步，没有之一。

2. 核心配置项拆解：开关、阈值、文件位，一个都不能少

2.1 四个关键参数的作用逻辑

慢查询日志涉及的参数不多，但每个都值得认真理解。不是背参数名，而是搞清楚它们是怎么配合工作的。

• slow_query_log：总开关。默认是OFF，必须手动打开。你可以用SET GLOBAL动态开启，但重启后会失效，如果要永久生效得写进my.cnf配置文件。
• slow_query_log_file：日志文件路径。文件默认在数据目录下，名字通常是主机名-slow.log。要注意MySQL的运行账号必须有该目录的写权限，否则开关开了但文件写不进去，日志依旧为空。
• long_query_time：阈值秒数。执行时间超过这个值的SQL会被记录。注意两点：一是它支持小数，比如0.1就代表100毫秒；二是等于阈值不会记录，是严格大于才记录。MySQL 5.7开始这个值的默认是10秒，但10秒在生产环境里太宽松了，我一般建议先从1秒起步，压到0.5秒甚至0.1秒。
• log_queries_not_using_indexes：是否记录没走索引的SQL。把它也打开后，即使SQL执行只要1毫秒，只要没走索引也会被记录下来。这个开关的价值在于抓“潜在问题SQL”，因为全表扫描的危害是随着数据量增长而放大的，今天读1万行不慢，明年读1000万行就会出事。

实操中我的建议是：阈值初期设1秒，让系统先稳定跑几天，然后再压到0.5秒。如果业务本身对接口耗时有严格要求，就直接上0.1秒。宁可日志多一点，也不要漏掉任何可疑语句。

注意：long_query_time的单位是秒，不是毫秒。很多新手上来写long_query_time=100，以为设了100毫秒，实际是把阈值设成了100秒——后果就是除了极端情况什么都记不到。类似这种一眼看不出来的坑，往往最耽误事。

2.2 配置后为什么需要重启或动态设置

新手最容易困惑的一点是：改了my.cnf里的参数，要不要重启MySQL？答案是分两种看：

• 动态参数：比如slow_query_log、long_query_time，在MySQL命令行里执行SET GLOBAL语句就能即时生效，不需要重启。
• 静态参数：比如slow_query_log_file，在8.0里动态修改可以生效，但5.7某些版本对文件路径的修改要求重启才稳妥。

为了省心，我的操作流程是：先直接执行SET GLOBAL开起来，立刻能用，然后顺手把参数写进my.cnf，保证重启后配置还在，两条路都走，谁都不耽误。你要注意一个细节：SET GLOBAL只是修改了内存中的全局变量，不会帮你改配置文件，如果只动态开启不写配置文件，MySQL重启后就会回到默认的OFF状态。

2.3 测试配置是否生效的快速方法

参数设完别急着等，自己先造一条慢SQL测一下。比如SELECT SLEEP(2);这种语句，执行时间妥妥超过阈值。如果这条语句出现在日志里，说明配置链路全通。

但这里有个细节得多说一句：默认情况下，慢查询日志记录的SQL来自执行完的语句，如果一条SQL执行到一半被客户端断开连接，MySQL可能来不及记录。所以测试的时候，老老实实等SLEEP语句执行完，别用Ctrl+C中断。

3. 实操过程：从零开启慢查询日志并看懂一条日志

3.1 我的完整开启步骤实录

下面是我在实际环境里跑过的一套流程，照着做就行。

第一步：登录MySQL命令行，先看当前状态。

SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time';

这一步是为了确认当前值，尤其是确认数据目录是否存在、文件是否在指定路径。

第二步：动态开启慢查询日志并设置阈值。

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.5; SET GLOBAL log_queries_not_using_indexes = 'ON';

阈值直接设0.5秒，也就是500毫秒。这个标准在绝大多数互联网业务里都够敏感了。

第三步：确认参数已生效。

SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time';

看到slow_query_log变成ON，long_query_time变成0.500000，就说明开启了。

第四步：写配置文件保证重启后依然有效。

[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 0.5 log_queries_not_using_indexes = 1

提示：路径也可以先指定再创建目录。我当时图省事直接写默认路径，省了权限这步，后面换自定义目录的时候踩了权限坑，这个放到后面的常见问题部分细说。

第五步：造一条慢SQL测试。

SELECT SLEEP(2);

执行完打开日志文件，能看到这条语句就说明链路全通。

3.2 一条慢日志记录的逐字段解读

日志文件里每一条慢SQL长什么样子？我用实际记录举例：

# Time: 2024-11-20T10:24:36.123456Z # User@Host: app_user[app_user] @ [192.168.1.100] Id: 88231 # Query_time: 2.000125 Lock_time: 0.000111 Rows_sent: 1 Rows_examined: 1 SET timestamp=1732098276; SELECT SLEEP(2);

别看这堆字段密密麻麻，真正要关注的就这么几个：

• Query_time：SQL执行总耗时，单位秒。这是核心指标，超过阈值才会出现在这里。
• Lock_time：锁等待耗时。注意这包含在Query_time里，如果Lock_time占比很高，说明瓶颈在锁争用，而不是SQL本身的计算。
• Rows_examined：扫描了多少行。这个值越大，说明SQL扫描的数据越多，即使最终返回结果很少。
• Rows_sent：最终返回了多少行。
• Rows_examined / Rows_sent：这两个的比值特别值得看。如果扫描了10万行只返回10行，那这个SQL就存在严重的扫描浪费，通常跟索引缺失或SQL写法有关。

Time字段里的时间是日志写入时间，不是SQL开始时间。SET timestamp=后面的数字才是SQL真正执行的时间戳，这个是Unix时间戳，需要转换一下。判断慢SQL是否集中在业务高峰期，要看这个timestamp，而不是看Time。

3.3 案例实战：一条真实慢查询的分析路径

有一次我排查一个报表接口，调用一次要5秒多。打开慢日志看到这样一条记录：

# Query_time: 4.835271 Lock_time: 0.000023 Rows_sent: 50 Rows_examined: 831240 SET timestamp=1700000000; SELECT o.order_no, u.user_name FROM order_info o LEFT JOIN user_info u ON o.user_id = u.id WHERE o.status = 1 AND o.create_time > '2024-01-01' ORDER BY o.create_time DESC LIMIT 50;

关键信息非常明确：Rows_examined是83万，Rows_sent只有50条，扫描了83万行才拿50条结果，这SQL必慢。分析路径是这样的：

• 首先怀疑order_info表上的status和create_time没有组合索引。单查status，如果status=1的数据占了全表的70%，优化器算一下觉得不如全表扫，索引就废了。
• 其次LEFT JOIN user_info时，如果user_id在user_info表里没有索引，每行都要回表查一次。这就等于嵌套循环了83万次。

我当时做的事情是：

第一步，用EXPLAIN确认执行计划：

EXPLAIN SELECT o.order_no, u.user_name FROM order_info o LEFT JOIN user_info u ON o.user_id = u.id WHERE o.status = 1 AND o.create_time > '2024-01-01' ORDER BY o.create_time DESC LIMIT 50;

看到type那一列是ALL，key是NULL，基本就实锤了全表扫描。

第二步，创建组合索引：

ALTER TABLE order_info ADD INDEX idx_status_create (status, create_time);

第三步，再查慢日志，这条SQL彻底消失，接口耗时从5秒降到了200毫秒以内。

这个案例想说明的是：慢查询日志本身只是定位工具，真正解决问题还是要配合EXPLAIN和索引优化。但反过来想，如果没有慢日志，你根本不知道要优化这条SQL。

4. 从日志到行动：慢查询引发的性能分析三板斧

4.1 第一板斧：用EXPLAIN看清执行计划

拿到一条慢SQL后，最忌讳的是在不知道执行计划的情况下瞎改。EXPLAIN是MySQL提供的执行计划查看工具，用法就是在原SQL前面加EXPLAIN关键字。

需要重点看的列就这么几个：

我来解释一下Extra列的常见坑。Using filesort不是真的用了磁盘文件排序，而是指MySQL需要额外做一次排序操作，跟是否走索引排序是两回事。如果ORDER BY字段不在索引里，就容易出现这个。而Using temporary更严重，说明需要临时表，通常跟GROUP BY或DISTINCT使用不当有关。

4.2 第二板斧：判断慢的根源是SQL还是锁

Query_time里包含了Lock_time，所以一条慢SQL可能是真的在执行计算，也可能大部分时间都在等锁。这俩处理方向完全不一样：

• Lock_time占比高，比如Query_time是3秒，Lock_time就有2.5秒，那优先排查并发事务和锁等待，而不是改SQL。
• Lock_time很低，Query_time却很高，那大概率是SQL本身在扫描、排序、临时表上花的时间，走索引优化。

判断锁问题可以查information_schema里的INNODB_TRX和LOCK_WAIT表，或者更简单，开启innodb_lock_wait_timeout相关的监控。实际排查中我发现很多“慢SQL”，单拿出来执行只要几十毫秒，但一上线就慢，基本都是锁的问题。

4.3 第三板斧：row和排序的连带优化

当一个慢SQL的Rows_examined远远大于Rows_sent，最常见的几个原因：

• 索引缺失或失效：WHERE条件里的字段没有合适的索引，只能全表扫描。
• 隐式类型转换：字段是varchar，查询条件写的是数字，MySQL会放弃索引。
• 函数运算：WHERE DATE(create_time) = '2024-11-20'，这种写法会导致索引失效。
• 排序字段不在索引内：导致Using filesort，排序大数据集耗时严重。

我的建议是一步一步来，先加索引，再改SQL写法，最后再考虑业务拆分。因为加索引是成本最低、风险最小的改动，而且大概率能解决80%的问题。

4.4 索引优化时容易被忽略的两个细节

第一个细节是联合索引的字段顺序。比如WHERE status = 1 AND create_time > '2024-01-01'，索引建议建(status, create_time)，把等值条件放前面，范围条件放后面。这个顺序不要搞反，否则范围条件后面的字段无法利用索引。

第二个细节是同步关注ORDER BY字段。索引除了覆盖WHERE条件，还可以覆盖排序。上面那个报表案例里，order_no、user_name都在查询列表里，但排序字段是create_time。实际项目里我经常看到只给WHERE字段建了索引，ORDER BY字段没覆盖，SQL依旧用了filesort，性能提升不到位。

注意：索引不是越多越好。每多一个索引，INSERT、UPDATE、DELETE都要多维护一棵B+树。从慢查询里发现的高频WHERE组合，才值得建索引。低频查询、分析临时SQL，就别折腾索引了。

5. 常见问题与排查技巧实录：比文档多走三步

5.1 排查一：日志开关已开，但文件里什么都没记录

这个现象我见过太多次，开关ON了，文件也生成了，但里面空空的，一条记录都没有。原因排查顺序，按照出现频率排：

• long_query_time设得太大了。默认10秒，普通业务很难触发。
• 测试SQL执行得太快。你以为它慢，实际毫秒级就完成了。
• 刚执行SET GLOBAL之后，当前会话的变量没有刷新。这种情况要重开一个连接，或者直接执行SET SESSION long_query_time = 0.5来覆盖当前会话。
• 日志文件路径权限不对。MySQL进程没权限写指定文件，SQL正常执行，但日志写不进去。
• MySQL 8.0里的log_error_verbosity如果设得比较低，某些情况下会影响错误日志但慢查询日志不受此限，这个可以排除。

5.2 排查二：日志文件越来越大，磁盘告警

慢查询日志是纯文本追加写入，不轮转、不自动清理。线上跑一个月，文件轻松上GB。磁盘满了MySQL直接拒绝写入，这远比慢SQL本身可怕。

我的处理方案：

# 重命名当前日志文件 mv mysql-slow.log mysql-slow.log.20241120 # 进入MySQL执行 SET GLOBAL slow_query_log = 'OFF'; SET GLOBAL slow_query_log = 'ON';

手动轮转的原理是：先关闭再开启，会让MySQL重新创建一个新的日志文件。但要注意，重命名和开关之间如果有SQL执行，那部分日志会丢。真正的生产环境建议用logrotate工具做自动化轮转，配合crontab定期压缩归档，保留近30天即可。

5.3 排查三：日志里全是小SQL，真正的问题SQL反而被淹没

这是一个“阈值设太低导致的反向问题”。有些业务本身扫描量就大但很快，或者测试环境QPS高且很多简单查询，一旦把long_query_time压到0.1秒，日志文件每分钟能增加几万条，你要的核心问题反而不显眼了。

我用的办法是分级处理：

• 第一层，全局阈值设在1秒，保证只记录真正的慢SQL。
• 第二层，针对特定重要业务，单独打开一组更敏感的监控，比如performance_schema里的events_statements_summary_by_digest表，可以按SQL指纹维度聚合统计。
• 第三层，周期性（比如每天凌晨）用工具把日志里的SQL按频率和平均耗时排序，重点盯那些既慢又被频繁执行的语句。

这里提一句，慢SQL最值钱的往往不是平均耗时，而是总耗时。一条执行2秒但每天只跑1次，和一条执行0.5秒每秒跑100次的SQL，后者才是真正的性能毒药。处理优先级永远按总耗时排序。

5.4 排查四：如何分析海量日志而不是一条条看

日志大了之后，靠人眼没法看。我推荐几个工具：

• mysqldumpslow：MySQL自带的分析工具，可以按执行次数、耗时、返回行数排序。缺点是比较基础，只能拿到汇总信息。
• pt-query-digest：Percona Toolkit里的王牌工具，可以生成一份按Query指纹分组的详尽报告，包含每类SQL的执行次数、平均耗时、总耗时、响应时间占比等。我主力用它。

用法示例：

pt-query-digest mysql-slow.log > slow_report.txt

打开报告之后，重点看两个部分：

• Profile（全貌）：列出耗时长、次数多、响应时间占比高的SQL，按总耗时排序。
• 每类SQL的具体样本和执行计划建议。

工具本身不解决任何问题，但能帮你把“哪些SQL最值得优化”这个优先级排清楚。做DBA和性能优化的一个核心意识就是：先诊断，再治疗。诊断方向错了，后面的功夫都白费。

5.5 独家技巧：用慢日志做定期“数据库体检”

分享一个我长期在用的思路。每月固定跑一次慢查询日志分析，把top 10的SQL整理成一张表格，对比上月，观察趋势：

这种对比看起来朴素，但特别有效。一方面验证上月优化动作是否真的有效，另一方面提前发现数据量增长带来的隐患。比如某条SQL上个月扫描10万行，这个月扫描50万行，即使还没超过阈值，也是一个危险信号，说明数据在涨而索引设计没跟上。慢查询日志不只是用来救火的，更是用来预防火灾的。

6. 附：慢查询日志使用速查表

这部分我整理成表格，方便你直接参考。

再提醒一个细节：上述动态设置只影响新建立的连接，已有的连接继续沿用老参数。有些工具连接池里的连接长期不释放，你改了开关，工具体验上仍是旧状态。最稳妥的办法是改完参数之后，重启一下应用或重建连接。

我第一次接触慢查询日志时，也以为这只是个简单的开关配置。真正跑过一两年之后回头看，慢查询日志是整个MySQL性能优化体系里性价比最高的一环，没有之一。它的价值不在于记录几条SQL，而在于它给你建立了一条“有数据可依据的决策链路”——慢在哪、为什么慢、改完有没有效果，全部能被回答。如果你手头正好有一套MySQL在跑，别犹豫，今天就把慢查询日志打开，配一个合理的阈值，下个星期你再来看，你对自己数据库的理解会完全不一样。