MySQL慢查询排查与优化：从日志到执行计划的完整实践

做后台开发和数据库维护这些年，我几乎每天都会和慢查询打交道。所谓的慢查询，就是执行耗时超过你容忍阈值的 SQL 语句，它们会被数据库单独记录在慢查询日志里，等着你去处理。定位慢查询、分析 SQL 执行缓慢的原因，是数据库性能优化里最基础也最实用的一环。这篇文章会从怎么把慢 SQL 捞出来、怎么看懂它的执行计划，再到锁等待和系统资源层面的排查，完整梳理一遍我的实操经验。适合刚接触数据库调优的后端开发、运维同学，也适合已经处理过一些慢 SQL 但还缺一套系统思路的同行。

1. 定位慢查询：先让数据库把慢 SQL“说出来”

1.1 开启慢查询日志的配置细节

慢查询日志是 MySQL 默认提供的诊断工具，开启之后，执行时间超过阈值的 SQL 会自动落到日志文件里。很多新手遇到线上 SQL 变慢，第一反应是打开数据库客户端手动执行几条 SQL 凭感觉猜，这种做法既不系统也不可复现。正确思路是先让数据库自己开口，把所有超时的 SQL 全部记录下来。

MySQL 中慢查询日志默认是关闭的，先用下面的命令确认当前状态：

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

如果slow_query_log是OFF，可以用下面的方式临时开启：

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

这里有个很容易忽略的细节：long_query_time的单位是秒，支持小数。线上环境我一般建议从1开始调，也就是 1 秒以上的 SQL 全记录。如果你的业务本身压力很大、日志量惊人，可以逐步调到2或者5，但不建议一开始就设成0.1这种值，否则慢日志文件会被每秒执行的小查询刷爆。

还需要确认一个参数：min_examined_row_limit。这个参数表示扫描行数达到多少才记录，默认是0。如果你的库里有大量小表全表扫描但执行很快的查询，你可能会在慢日志里看到一堆“没有价值”的记录。把它适当调大，比如1000，能过滤掉那些扫描行数太少的噪声记录，让慢日志更有参考价值。

另外补充一个运维细节。MySQL 的慢日志默认输出到文件，但也支持写入mysql.slow_log表（通过log_output = TABLE设置）。文件方式效率更高、解析更方便，我长期使用文件方式，不推荐把日志写到表里，因为表本身也需要写入，反而会影响数据库性能。

1.2 真正读懂慢日志里的每一行

开启慢日志之后，你需要能看懂它记录的内容。一条典型的慢日志长这样：

# Query_time: 4.213284 Lock_time: 0.000122 Rows_sent: 10 Rows_examined: 1234567 SET timestamp=1710000000; SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

四个关键字段：

• Query_time：SQL 从开始执行到返回的总耗时，单位秒。这是你判断是否超时的直接依据。
• Lock_time：等待获取锁的时间。如果这个值很大，说明 SQL 卡在锁等待上而不是真正在执行计算，排查方向要转向锁问题。
• Rows_sent：实际返回给客户端的行数。
• Rows_examined：执行过程中扫描过的行数。

我最看重的是Rows_examined和Rows_sent的比例。像日志里这条，扫描了 123 万行只返回 10 行，典型的大范围扫描撞上过滤条件，索引设计有问题的可能性极高。反过来，如果Rows_examined本身不大，但Query_time却很高，那就要考虑锁等待、网络延迟、CPU 资源竞争等外部因素。

慢日志还记录了 SQL 执行时的timestamp，你可以通过FROM_UNIXTIME()把它转换成可读时间，用来判断慢 SQL 是否集中在业务高峰时段。

2. 日志到手之后：快速找出最值得优化的 SQL

2.1 用自带的 mysqldumpslow 做初步聚合

慢日志一旦开起来，文件很快就变大，里面可能躺着几千条记录。这时候如果一条条去看，效率太低。MySQL 自带的mysqldumpslow工具就是用来做初步汇总的。

它的基本用法是：

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

参数含义：

• -s t：按查询耗时排序，-s c是按出现次数排序，-s l按锁定时间排序。
• -t 10：只看前 10 条。

mysqldumpslow会把结构相似、只有参数不同的 SQL 聚合到一起，比如user_id = 123和user_id = 456会被视为同一条。这样做的意义在于，你关注的不应该是某一个具体的慢查询，而是一类慢查询。如果同一类 SQL 每天晚上被调用几千次，每次慢 2 秒，它带来的整体影响远远大于一条偶尔跑了 30 秒的 SQL。

2.2 用 pt-query-digest 做深度剖析

mysqldumpslow解决的是“哪类 SQL 最慢”的问题，但如果你想看更详细的统计分布，比如响应时间的百分位数、SQL 指纹、总耗时占比，那就需要pt-query-digest。这是 Percona Toolkit 里的明星工具，几乎是我定位慢查询必用的东西。

安装 Percona Toolkit 之后，一行命令就能生成分析报告：

pt-query-digest /var/log/mysql/mysql-slow.log > slow_analysis.txt

打开生成的报告，你会看到类似下面的信息：

# Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ========== ============== ====== ======= ===== ===== # 1 0x1234... 2356.2345 12.3% 452 5.2134 0.01 SELECT orders # 2 0x5678... 1834.1234 9.6% 128 14.3290 0.02 SELECT users

它按照“总响应时间占比”排序，排名靠前的就是真正消耗数据库资源的元凶。再往下翻，每个 SQL 指纹的详细报告里还有ts（耗时分布）、Rows_sent、Rows_examined等统计，能辅助你判断优化优先级。

我的实际建议是：维护一个固定的慢日志分析任务，比如每天凌晨把前一天的慢日志跑一遍 pt-query-digest，然后把 Top 10 发给相关开发。这样慢查询就不再是“出事才查”的被动状态，而是形成一个可持续跟踪的指标。实践中我发现，很多慢 SQL 的累积影响比单次报警要严重得多，用这类工具做聚合分析，才能真正看到全局。

3. 分析慢 SQL 的核心动作：看懂执行计划

3.1 EXPLAIN 结果逐列拆解

拿到一条待优化的慢 SQL 之后，第一个动作一定是看它的执行计划。MySQL 里执行计划用EXPLAIN查看：

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

输出结果的几个核心列，我逐个解释一下。type列是访问类型，它直接告诉你 MySQL 是怎么找数据的。从好到差大致是：system>const>eq_ref>ref>range>index>ALL。看到ALL就意味着全表扫描，这是最需要警惕的；index表示扫描了整个索引树，也不算好，但如果只是覆盖索引查询，有时候还能接受；range是范围扫描，级别中等；ref和eq_ref属于精准匹配，性能通常不错。

key列表示实际用到的索引，如果为NULL说明没走索引。key_len表示索引使用的字节数，对联合索引来说，它能看出实际用到了索引的哪几列。rows是 MySQL 预估需要扫描的行数，这个数字越大，执行代价越高。Extra列则提供额外信息，比如Using filesort表示文件排序、Using temporary表示使用了临时表、Using where表示在存储引擎层拿到的数据又做了过滤。

我之前遇到过一出事就急着加索引的团队，结果加了索引 SQL 还是慢，最后发现是EXPLAIN里type显示为ref，但rows依然几十万，原因是联合索引的字段顺序设计不合理。所以要记住：执行计划不是只看有没有索引，而是要看索引的字段顺序、扫描行数和访问方式。

3.2 用 EXPLAIN ANALYZE 验证真实执行

EXPLAIN展示的是优化器基于统计信息估算出来的方案，rows是猜测值，实际执行可能有偏差。MySQL 8.0.18 及以上版本提供了EXPLAIN ANALYZE，可以直接看到真实执行的行数和耗时：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

输出是一个树状结构，里面包含每个算子的真实耗时、真实行数、循环次数等。比如actual time=2.341..4.125 rows=10 loops=1中的rows=10是真实扫描到的行数，actual time是实际耗时，单位毫秒。

这里要特别提醒：EXPLAIN ANALYZE会真实执行这条 SQL。它对你的 SELECT 查询是安全的，但如果对大数据量查询执行，它真的会跑完整个查询，占用数据库资源，所以生产环境使用要慎重。如果一条 SQL 本身要跑 10 分钟，EXPLAIN ANALYZE也会真的让它跑 10 分钟。我在生产环境一般用两次：第一次用普通EXPLAIN看执行计划是否合理，确定没有明显问题后再对关键算子做一次EXPLAIN ANALYZE，确认估算和实际的差异。

4. 慢 SQL 背后的典型场景与优化方向

4.1 索引失效：隐式类型转换和函数操作

看执行计划时最常遇到的情况，是明明字段上有索引，type却是ALL。这种情况十有八九是索引失效，而索引失效最常见的原因，是隐式类型转换和函数操作。

举个例子：

SELECT * FROM users WHERE phone = 13800138000;

如果phone字段是VARCHAR类型，而你用数字去比较，MySQL 会把phone字段隐式转换成数字再比较，相当于对索引列做了一次函数操作，索引自然失效。解决方法是把参数改成字符串：

SELECT * FROM users WHERE phone = '13800138000';

类似的情况还有在索引列上使用函数，比如：

SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

只要在索引列上套了DATE()函数，索引基本用不上。正确的写法是把它改成范围条件：

SELECT * FROM orders WHERE created_at >= '2024-01-01 00:00:00' AND created_at < '2024-01-02 00:00:00';

这个改写保留了索引的使用能力，同时避免了函数操作。这类问题的排查思路很简单：看到EXPLAIN里key为NULL，先检查 SQL 的 WHERE 条件里有没有对索引列做运算。还有一点容易被忽略：LIKE '%keyword'这种前置通配符也会导致索引失效，LIKE 'keyword%'则能走范围扫描。

4.2 深分页问题：LIMIT 越翻越慢

分页查询越到后面越慢，几乎是每个业务系统都会遇到的问题。经典 SQL 长这样：

SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;

为什么慢？因为 MySQL 需要先找到第 1000020 行之前的全部数据，把前 100 万行全部扫描出来再丢弃，最后只返回 20 行。数据量小的时候问题不明显，一旦表里有了几百万上千万行，这种写法就会让Rows_examined爆炸式增长。

一种常用的优化方案是延迟关联。先只查出主键或id，再通过JOIN回到原表取完整数据：

SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20) tmp ON o.id = tmp.id;

内层子查询只查id，如果id是主键，ORDER BY created_at配合索引能够快速定位目标行；外层再回表取数据，整体扫描行数会大幅下降。

如果业务允许，更彻底的方案是游标分页，也就是记住上一次查询的最后一条位置，避免使用LIMIT的大 offset：

SELECT * FROM orders WHERE created_at < 上次返回的最晚时间 ORDER BY created_at DESC LIMIT 20;

这种玩法对用户体验来说需要配合下拉加载或者“上一页/下一页”交互，但对数据库最友好。实际项目中，我经常把延迟关联作为立即可用的快速优化手段，把游标分页作为需要产品配合的长期方案。

4.3 大表 JOIN 和临时表

多表 JOIN 出现性能问题的概率很高，尤其是大表 JOIN 小表、大表 JOIN 大表，以及带GROUP BY、ORDER BY、DISTINCT的查询。

先看一个常见问题：Extra列出现Using join buffer，说明 MySQL 需要在内存里缓冲一张表的数据再跟另一张表匹配，这通常意味着没有走索引关联。优化方向是：给关联字段建索引，并且尽量让驱动表（外层表）是小表，被驱动表（内层表）用索引查找。

我见过一个实际案例，两张表各 50 万行数据，JOIN 之后耗时 12 秒，EXPLAIN显示type是ALL，因为被驱动表orders.user_id上没有索引。后来给user_id建了普通索引，同一查询降到 0.2 秒。这个例子说明：JOIN 慢的时候，首要排查点不是 SQL 写法，而是关联字段的索引是否存在。

还需要警惕一个隐形问题：GROUP BY和ORDER BY如果出现在不同的索引上，MySQL 可能需要先排序再分组，Extra里出现Using temporary和Using filesort，这时候查询会变得很慢。解决办法通常是调整索引字段顺序，让排序和分组的字段都包含在同一个联合索引里；如果确实无法避免，那就考虑用冗余表或者把统计结果放到应用层缓存，而不是每次都现场计算。

Using temporary还有一个隐蔽的来源：SELECT DISTINCT和UNION。如果对几千行数据做 DISTINCT，临时表很快；但如果是几百万行，内存临时表装不下，就会落到磁盘上，性能断崖式下降。看到Using temporary时，去检查tmp_table_size和max_heap_table_size配置只是治标，把这种重型去重操作移到应用层往往更加有效。

5. 别忘了锁等待和系统资源层面的干扰

5.1 从 processlist 和 innodb_trx 定位锁问题

有时候 SQL 本身设计没问题、索引也齐全，但就是慢。这时候要怀疑是不是锁等待导致的阻塞。一条 SQL 写完了，可能一直在等另一个事务释放锁，Query_time高但Lock_time占了大头。慢日志里的Lock_time就是信号。

先通过SHOW FULL PROCESSLIST看当前连接状态：

SHOW FULL PROCESSLIST;

如果看到大量连接处于Waiting for table metadata lock或Waiting for next key lock之类的状态，基本可以确认锁等待。在 InnoDB 引擎下，更精确的手段是查询information_schema.innodb_trx表，找到当前所有活跃事务：

SELECT trx_id, trx_state, trx_started, trx_query, trx_mysql_thread_id FROM information_schema.innodb_trx\G

配合sys.innodb_lock_waits视图，能直接看到谁在等锁、谁持有了锁：

SELECT * FROM sys.innodb_lock_waits\G

这个视图会返回waiting_pid（等待线程）和blocking_pid（阻塞线程）。通过blocking_pid去SHOW FULL PROCESSLIST里找到持锁会话，再结合trx_started看它跑了多久。如果它已经执行了很久还没提交，那就需要和业务方确认，看能不能尽快提交或回滚事务。

这里有一个很常见的误区：很多人以为死锁才会导致慢查询。实际上，死锁只是锁问题的极端情况，更多的是长事务持锁不释放，导致后面所有相关 SQL 排队等待。这类慢查询用执行计划看不出来，必须从锁等待的角度去排查。

5.2 系统资源、统计信息与执行计划的偏差

在排除了 SQL 写法、索引、锁问题之后，还要把眼光放到数据库所在的机器上。慢查询有时是资源竞争引发的。top看到 CPU 使用率飙高，可能是大量排序和 join 操作；iostat -x看到磁盘util接近 100%，可能是内存不足导致频繁磁盘读写，比如临时表落盘、InnoDB buffer pool 太小导致频繁读盘。

我遇到过最典型的案例是：某条统计 SQL 平时执行 200 毫秒，某段时间突然变成 8 秒，EXPLAIN 看执行计划完全正常，type是ref，rows也合理。后来发现是同一时间上了个大报表任务，把磁盘 IO 打满了。排查该类问题不需要高深手段，先top看整体负载，再用iostat看磁盘压力，往往几秒钟就能定位。

另一个容易被忽略的因素是统计信息过期。MySQL 优化器选择索引依赖表的统计信息，如果表数据量在短时间内剧烈变化，统计信息没有及时更新，优化器可能选错索引。这时候即使 SQL 没有变化，执行计划也可能变得很差。解决方法是定期执行：

ANALYZE TABLE orders;

如果不想手工执行，可以在业务低峰期做一个定时任务。实战中，大表在执行计划突变、前一天还好好的情况下，排查思路里应该加上“统计信息是否过期”这一项。先用SHOW INDEX FROM orders看Cardinality，数值明显偏低或者和实际行数差距很大时，就执行一次ANALYZE TABLE，再重新EXPLAIN看执行计划有没有变化。

6. 常见问题速查表与排查经验

6.1 慢查询排查速查表

我把日常工作中最常遇到的慢查询现象整理成一张表，方便对照排查。

遇到慢 SQL 时，我会从“日志记录 → 执行计划 → 锁等待 → 系统资源”四个层面依次排查。大多数情况下，问题会在第一层和第二层之间暴露；如果前两层都正常，再去看锁和系统资源，基本不会走弯路。

6.2 实操中容易踩的几个坑

先说说慢日志参数的坑。很多人把long_query_time设得特别小，比如0.1，导致慢日志文件疯长，一天几个 GB，结果真正重要的慢 SQL 反而被淹没了。我建议线上环境从1秒起步，根据日志量和业务压力逐步调整。还有log_queries_not_using_indexes这个参数，它的本意是记录没走索引的查询，但实际效果是大量小表全表扫描的查询都会刷进来，日志爆炸速度比long_query_time=0.1还快，除非你有明确的监控告警系统，否则不要轻易打开。

再说说执行计划的坑。EXPLAIN里的rows是优化器的估算值，不代表真实扫描行数。如果两条同样的 SQL 因为统计信息不同，rows可能差一个数量级。所以我在判断一个索引是否发挥作用时，会同时关注type和rows，而不会只盯着其中一个。更严谨的做法是结合EXPLAIN ANALYZE验证真实数据。

还有关于索引的惯性思维：发现 SQL 慢就加索引，这个做法本身没错，但要注意索引不是越多越好。每个索引都会增加写入开销，还会占用额外的存储空间。我见过一张表被加了十几个索引，写入性能严重下降，最后不得不清理。优化慢查询时，优先通过改写 SQL、调整索引字段顺序来解决，而不是无脑加新索引。一条 SQL 慢，很多时候是因为现有索引用不上，并不是缺少索引。

最后分享一个运维习惯：生产环境变更之前，先把 SQL 的EXPLAIN截图留档，变更之后再做一次对比。这个习惯帮我避免了很多“改了配置反而更慢”的问题。慢查询优化不是一次性工作，而是一个持续跟踪的过程。每个季度把慢日志重新分析一遍，把 Top 10 拿出来过一遍，你会发现数据库的性能问题大多是周期性出现的，解决一批还会来一批。把这套流程跑起来，慢查询就不会再是让你半夜上线的元凶。