ENZOMEGA.
Clipping 微信公众号

Mysql面试题：慢 SQL 一直慢和偶尔慢，处理思路有什么不同？ 偶尔慢的 SQL 你觉得可能是什么原因？

by Fox爱分享 原文 ↗
Created: 2026-06-18
#公众号文章

公众号名称：Fox爱分享

作者名称：Fox爱分享

发布时间：2026-06-18 07:00

核心观点：一直慢是「执行计划」的问题，偶尔慢是「执行环境」的问题。把偶尔慢当一直慢去优化，加索引、改 SQL，大概率白忙一场。

一、先讲一个真实的”坑”

某天线上告警：一个原本 5ms 的查询，每隔十几分钟就跳到 2～3 秒，过几秒又恢复正常。团队一看慢查询日志，查出来是：

SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20;

第一反应：加索引。

检查后发现 (user_id, status, created_at) 联合索引已经存在。索引对了吗？对。执行计划看了吗？看了，type 是 ref，Extra 用了 Using index condition，看起来没问题。

那为什么还会偶尔慢？

问题出在另一个方向——这个表上有一个高频跑批任务，每隔 15 分钟扫全表更新一批订单状态。跑批期间，大量脏页被刷入磁盘，Buffer Pool 里这个查询原本热的数据页被淘汰。查询再进来时，不得不从磁盘读取大量数据页，IO 延迟从几百微秒飙到几十毫秒，整体查询时间一下子涨了几百倍。

加索引当然没用。跑批结束后，脏页刷完，数据页重新被加载到 Buffer Pool，查询又恢复 5ms。

这个案例揭示了一个关键区分：

一直慢和偶尔慢，根因不在一个层面上，处理思路也完全不同。

二、先分清楚：你是哪种慢？

在对症下药之前，先要回答一个问题：你的慢查询日志里，那条 SQL 是次次慢，还是时好时坏？

2.1 一直慢——可复现的稳定低效

特征：

维度表现
执行时间每次执行都在数百毫秒到数秒，波动小
触发条件任意参数或大部分参数下都慢
复现性explain 重新执行，结果一致
资源特征CPU 或 IO 消耗稳定偏高

诊断方向很明确：执行计划有问题。

2.2 偶尔慢——间歇性波动

特征：

维度表现
执行时间90% 时候很快（1～10ms），偶尔跳到秒级
触发条件特定时间窗口、特定参数值、特定并发量
复现性同样的 SQL 和参数，换个时间跑就正常
资源特征出现时可观察到 IO 抖动、锁等待或 CPU 尖刺

诊断方向完全不同：执行环境发生了变化。

2.3 一个判断技巧

把那条 SQL 拿出来，连续执行 20 次：

# 一直慢
3.2s, 3.1s, 3.3s, 3.0s, 3.2s ... 稳定在 3 秒左右

# 偶尔慢
0.005s, 0.004s, 2.1s, 0.005s, 0.003s, 0.004s, 3.5s, 0.005s ...

标准差差一个数量级，就是两类问题。

三、一直慢的 SQL：三板斧能解决 90%

这类问题的思路非常成熟，业内已有高度共识。我简单归纳为”三板斧”，每一斧都有明确的诊断手段和落地动作。

第一斧：看执行计划

EXPLAIN SELECT ... \G

核心关注四个字段：

字段警告信号含义
typeALL（全表扫描）、index（全索引扫描）没有有效过滤
rows远大于预期结果集过滤性差
ExtraUsing filesort、Using temporary、Using join buffer排序/分组无索引、临时表
keyNULL 或意外的索引索引没用到或用错了

**🔍 反直觉的一点：type 是 ref 也可能慢。**当 rows 估算值很大（几万行）但实际结果只有几十行时，说明索引的过滤性并不好。比如 status 字段只有 3 个值，建了索引也没用，MySQL 可能会认为还不如扫全表。

第二斧：检查索引

以下情况再强的索引也救不了：

  • 回表太多：覆盖索引没建好，查询的列不在索引里，每行都要回聚簇索引查一次。查询 10 万行就回表 10 万次。

  • 索引过滤性差：区分度低于 20% 的字段放在索引最左列，意义不大。一定要放的话，放到联合索引的后面。

  • 隐式类型转换：WHERE user_id = '123' 但 user_id 是整型，MySQL 放弃索引。

  • 函数操作：WHERE DATE(created_at) = '2024-01-01'，改成 created_at >= '2024-01-01' AND created_at < '2024-01-02'。

第三斧：检查数据量和写入模式

  • 数据量已经超过单表天花板：几千万行甚至上亿行，索引再完美，B+树也深了，回表路径也长了。考虑分库分表或归档。

  • 写入频繁导致索引碎片：大量随机 INSERT/UPDATE/DELETE 会产生索引页分裂和碎片。OPTIMIZE TABLE 定期重建。

一直慢到此基本能解决。 如果三板斧砍完还是慢——回到第一步，问自己：它真的是”一直慢”吗？

有没有可能只是你以为它一直慢，实际上它有规律的”快”和”慢”？

四、偶尔慢的 SQL：这才是真正的硬骨头

偶然慢往往比一直慢更难定位，原因有两条：

  1. 复现困难——你打开监控的时候它正常，你一转头它又慢了。等你想抓现场，它又不出现了。

  2. 根因多元——不像一直慢那样基本锁定在”执行计划或索引”，偶尔慢可以来自操作系统、存储、网络、并发控制、缓存、优化器行为等任何一个环节。

下面逐一拆解最常见的原因。

4.1 Buffer Pool 被”冲凉”

这是最常见也是被低估最多的原因。

原理

MySQL 的 InnoDB 用 Buffer Pool 缓存数据页和索引页。一个查询快，通常是因为它需要的数据页已经在内存中。

当以下事件发生时，热数据页会被淘汰：

  • 全表扫描：一个大查询把 Buffer Pool 刷了一遍，冷数据把热数据挤出去。

  • 跑批任务：夜间的 ETL、状态更新、数据清理，扫描了大量页面。

  • 内存压力：服务器内存被其他进程争夺，操作系统触发回收。

查询再进来时，数据页不在 Buffer Pool 中，必须从磁盘读取。从磁盘读一个 16KB 的页大约 0.1～10ms（取决于 HDD/SSD/NVMe），而内存读是几十纳秒。差距是 3～4 个数量级。

如果你的查询需要读几百个数据页，那几百次 IO 延迟叠加起来，就是秒级。

如何判断

看 Innodb_buffer_pool_reads（从磁盘读的页数）和 Innodb_buffer_pool_read_requests（总读请求数）。比值异常升高时，就是 Buffer Pool 在”冷”的状态。

具体的 SQL：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

计算 Buffer Pool Hit Rate：

hit_rate = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%

正常应该在 99%～99.99%。如果低于 95%，说明缓存命中率出了问题。

解决方案

  • 大查询加限流：非必要不在业务高峰期做全表扫描。跑批任务加 LIMIT 分批执行。

  • 用 innodb_buffer_pool_size 给够内存：一般建议设为可用内存的 60%～80%。

  • 业务错峰：跑批和核心查询的时间窗口分隔开。

  • **使用 innodb_old_blocks_time**：让新读入的页先留在 old sublist 中一段时间（比如 1 秒），防止一次大查询就把热数据冲走。

SET GLOBAL innodb_old_blocks_time = 1000; —— 新加载的页在前 1 秒内不会被移入热区，避免被一次扫描冲掉。

4.2 执行计划突变（参数嗅探）

这是 MySQL 里一个容易被忽略的”坑”。

原理

MySQL 的优化器在生成执行计划时，会”偷看”你传入的参数值——这就是参数嗅探（Parameter Sniffing）。

同一个 SQL，传 status = 1（占比 90%）和 status = 2（占比 0.1%），优化器可能给出完全不同的执行计划：

  • 对 status = 1：扫全表更划算，因为 90% 的数据都要返回。

  • 对 status = 2：走索引更划算，回表次数少。

问题是，优化器会缓存执行计划。如果第一次执行传的是 status = 1，生成的执行计划是全表扫描。那么后续传 status = 2 也复用这个计划——明明走索引更快，却还在走全表扫描。

反过来也一样：第一次传 status = 2 走了索引，后续传 status = 1 导致大量回表，反而变慢。

如何判断

对比相同的 SQL 在不同参数下的执行计划：

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 1;
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 2;

如果 rows 估算值差异很大但索引选择了同一个，就可能是参数嗅探导致的次优计划。

MySQL 8.0 可以通过 explain analyze 拿到实际执行信息：

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 2;

看 actual time 是否与预期差距巨大。

解决方案

  • 改写 SQL 避免参数依赖：用绑定变量，但不等于完全解决。

  • 索引优化：让不同参数值都能用上索引（比如覆盖索引消除回表差异）。

  • **使用 FORCE INDEX 或 USE INDEX**：人工固定执行计划（但需要持续维护）。

  • MySQL 8.0 的 optimizer_switch 控制：考虑关闭 prefer_ordering_index 等开关。

  • 升级到 MySQL 8.0 并开启 ‘contention aware’ 计划缓存策略。

4.3 锁等待与 MDL 阻塞

偶尔慢也可能是被阻塞了，而不是执行慢。

行锁等待

SHOW ENGINE INNODB STATUS \G

查找 LATEST DETECTED DEADLOCK 或 TRANSACTIONS 部分中的锁等待信息。

典型场景：

  • 事务 A 更新了某行数据但未提交，事务 B 要更新同一行，只能等。

  • Gap Lock / Next-Key Lock 导致间隙锁冲突。

MySQL 8.0 可以使用 performance_schema.data_lock_waits 直接查：

SELECT * FROM performance_schema.data_lock_waits;

MDL（元数据锁）等待

这是更新频繁的表上非常常见的”隐形杀手”。

事务 A 有一个长事务在查询表 T，事务 B 要 ALTER TABLE T ADD COLUMN ...。DDL 需要 MDL 写锁，但被事务 A 的 MDL 读锁阻塞。

最致命的是：事务 B 排队等 MDL 写锁时，后面所有对表 T 的查询都会被阻塞。 整个表”冻住”了，看起来就是”所有查询都突然变慢”。

如何发现：

SELECT * FROM performance_schema.metadata_locks;

或者观察 SHOW PROCESSLIST 中大量 Waiting for table metadata lock 的线程。

解决方案

  • 给 DDL 操作加超时：lock_wait_timeout 避免无限阻塞。

  • DDL 用 pt-online-schema-change 或 MySQL 8.0 的 Instant DDL。

  • 监控长事务：SELECT * FROM information_schema.INNODB_TRX 找出超过几十秒未提交的事务。

4.4 排序和临时表落盘

查询中有 ORDER BY、GROUP BY、DISTINCT、UNION 时，MySQL 需要创建临时表。

只要临时表小于 tmp_table_size 或 max_heap_table_size，临时表在内存中，非常快。

但当临时表大小超过阈值时，MySQL 会自动将其转为磁盘上的 MyISAM 临时表，性能瞬间暴跌。

如何判断

看 SHOW STATUS 中的 Created_tmp_disk_tables：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

如果 Created_tmp_disk_tables 与 Created_tmp_tables 的比值较高（>10%），说明很多临时表落盘了。

在慢查询日志中也可以看到 Using temporary + Using filesort 的标记。

为什么是偶尔慢

因为临时表的大小取决于查询的数据量和参数值。大部分时候结果集小，临时表在内存中。偶尔传入的参数导致中间结果集变大，超了阈值，就落盘了。

解决方案

  • 给排序字段建索引，消除 filesort。

  • 增大 tmp_table_size 和 max_heap_table_size（但不能太大，避免占用过多内存）。

  • 优化 SQL，减少中间结果集。

4.5 操作系统层面的抖动

这也是一类被忽视的原因：

  • Swap 被触发：内存紧张时，部分 MySQL 内存被换出。一个页被访问时触发缺页中断，IO 延迟陡增。用 vmstat 或 /proc/meminfo 检查 si 和 so 字段，如果 > 0，就是在 swap 了。

  • NUMA 跨节点访问：在多路服务器上，MySQL 进程的内存分配在 Node 0，但 CPU 调度到了 Node 1，跨节点访问内存的延迟增加。

  • 磁盘 IO 竞争：同一块磁盘上跑了其他高 IO 任务（日志、备份、监控采集）。

  • Linux 的 Page Cache 回收：操作系统在内存紧张时回收 Page Cache，导致 MySQL 的 O_DIRECT 文件读取变慢。

解决方案

  • 关掉 NUMA 的 interleave 或绑定 MySQL 进程到特定 CPU 和内存节点：numactl --interleave=all mysqld

  • 监控 vmstat 的 si/so，明确保 swap 为 0。

  • 用 iotop 或 iostat -x 1 观察磁盘 IO 使用量，确认是否有资源争用。

  • 如果使用云数据库，检查是否有底层实例争抢（“吵闹的邻居”问题），考虑升级实例规格或使用独享型实例。

4.6 网络抖动

如果数据库和服务不在同一台机器上（绝大多数场景都是如此），网络本身就是一个变量。

  • TCP 重传：丢包导致重传，查询等待时间增加。

  • 连接池中的连接变慢：某些中间件或驱动在连接重建时会有 DNS 解析延迟。

  • 网络带宽打满：大量数据传输时，队列延迟增加。

如何判断

对比数据库端的执行时间和应用端感知到的执行时间。如果数据库端执行日志显示查询只要 5ms，但应用端发现耗时 500ms——问题出在网络。

使用 tcpdump 抓包或用 ping / mtr 检查网络延迟和丢包率。

4.7 查询缓存的”回光返照”

MySQL 8.0 已经彻底移除了 Query Cache。如果你还在用 5.7 及以下版本且开启了 query_cache_type=ON，这是一个专门的坑。

Query Cache 的原理：一个查询的结果集被缓存，同样的查询再来时直接返回。

但问题是：只要表有数据发生变化（INSERT、UPDATE、DELETE），这个表上所有相关的 Query Cache 都会被清空。

如果你的表写入频繁，Query Cache 刚缓存好就被清空。缓存命中率极低，而且每次清空缓存时还会对 Query Cache 加锁——不仅没加速，反而成了并发瓶颈。

我曾经见过一个案例：关闭 Query Cache 后，高并发下的查询性能反而提升了 30%。

解决方案：SET GLOBAL query_cache_type = 0; 然后重启 MySQL（5.7 需要重启才能彻底禁用）。

五、诊断偶尔慢的”套路”

如果你遇到一个偶尔慢的 SQL，按以下顺序排查，效率最高：

Step 1：排除”缓存被冲凉”

查 Buffer Pool 命中率。如果异常高且有全表扫描或跑批任务同时发生，这就是根因。

命中率 < 99% → Buffer Pool 问题 → 错峰 / 调整 innodb_old_blocks_time

Step 2：排除锁等待

查 performance_schema 或 SHOW PROCESSLIST，看有没有大量 Waiting for ... 状态的连接。

有锁等待 → 找长事务 / MDL 阻塞源 → 杀事务或优化 DDL

Step 3：检查临时表落盘

看 Created_tmp_disk_tables 的占比。

占比 > 10% → 增大 tmp_table_size 或优化 group by / order by

Step 4：分析执行计划是否突变

对比慢的参数和不慢的参数下的 explain 输出。

同一 SQL 不同参数 → 执行计划不同 → 参数嗅探 / 统计信息过旧

Step 5：排除系统层面抖动

检查 swap、NUMA、磁盘 IO 竞争、网络延迟。

系统层面异常 → 操作系统调优 / 资源隔离

Step 6：最后才考虑 SQL 本身

如果上述都查过了还找不到原因，才回到 SQL 本身——但这已经是极小概率。

六、总结：一张表说清楚

维度一直慢偶尔慢
根因执行计划存在根本缺陷执行环境发生间歇性变化
诊断起点explain + 索引分析Buffer Pool + 锁 + 系统资源
复现难度容易，随时可复现困难，需要命中”窗口期”
常用工具EXPLAIN, optimizer traceperformance_schema, SHOW ENGINE INNODB STATUS, 操作系统监控
典型解法加索引、改 SQL、分表错峰、调参数、资源隔离、改 DDL 策略
常见误区加索引一定能解决改 SQL 就能解决（实际大概率不是 SQL 的问题）

七、写在最后

MySQL 性能问题诊断比修复难 10 倍。

一直慢，你只要耐心看执行计划、看索引、看数据量，十有八九能找到答案。

偶尔慢，你必须把视野扩大到整个系统——MySQL 内部的缓存和锁机制、操作系统的内存和 IO 调度、以及业务层的并发模式。任何一个环节出问题，都会表现为”那条 SQL 偶尔慢一次”。

不要一上来就问”这个 SQL 能不能优化”——先问问自己：它真的每次都很慢，还是只是偶尔让你看见了？

如果你遇到过类似的”偶尔慢”经典案例，欢迎一起讨论。有些坑，走过一次就忘不掉。

内容效果不满意？点此反馈

输入关键词开始搜索