慢SQL常态化治理：从慢日志到修复的完整链路

  1.  Home
  2. Text  
    3. Share to：

慢 SQL 这件事，很多站长第一次真实感觉到它，不是因为看了监控，而是因为某个下午网站突然打不开，上服务器一看 MySQL 的 CPU 跑满，SHOW PROCESSLIST 里一条 SELECT 卡在那儿几十秒还没结束。更让人抓狂的是，第二天它又好了，你连那条语句长什么样都没看到。

本文不讲「加索引就快」这种正确的废话，而是讲一套可以在小站上真正跑起来的慢 SQL 常态化治理流程：怎么把慢查询稳定捞出来、怎么读懂执行计划、索引为什么明明建了却不用、以及一个最容易被忽略的事实——很多「慢 SQL」的问题出在应用层，不在数据库。

一、先把慢查询日志开对，别急着查原因

绝大多数「查不到慢 SQL」的情况，不是没有慢 SQL，而是日志参数没开对。MySQL 8 默认 slow_query_log 是关闭的，long_query_time 默认 10 秒，对一张博客的文章表来说，一条 3 秒的查询已经够把 PHP-FPM 的进程池占满了，但你永远看不到它。

-- 查看当前状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

-- 建议在 my.cnf 里固化，而不是 SET GLOBAL
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
min_examined_row_limit = 100

几个参数值得单独说明：

  • long_query_time 设为 0.5 秒是个人站点的合理起点。设太大等于没开，设太小（比如 0.05）日志会爆炸，反而看不进去。先在 0.5 跑一周，找出 Top 10 之后再谈要不要收紧。
  • log_queries_not_using_indexes 表面上很好用，实际是小站日志膨胀的第一元凶：一张几百行的小表全表扫描只要 0.2 毫秒，但它会被记下来。**务必同时打开 min_examined_row_limit**，让「扫了不到 100 行」的查询直接不记录。
  • SET GLOBAL 改的参数重启就没了。用完就固化到配置文件，否则下次服务器重启，你以为开着其实已经关了。
  • MySQL 8.0.14 之后还有 log_slow_extra，打开后日志里会多出 Rows_examined、Rows_sent、Bytes_sent 等字段。对判断「扫描行数和返回行数差了几个数量级」非常有价值，建议打开。

顺带一提磁盘：慢日志本身也会写满盘。给它配一个独立的 logrotate，daily + rotate 7 + compress 就够，别和主库数据放同一个分区。

二、用 pt-query-digest 做真正有用的聚合

直接打开 slow.log 看，你会被几百条重复的 INSERT INTO typecho_comments 淹没。慢日志的正确用法是聚合，最成熟的工具还是 Percona 的 pt-query-digest。

# 汇总全量，按总耗时排序
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt

# 只看最近两小时（按 since 过滤），输出成表格
pt-query-digest --since '2h ago' \
  --output json /var/log/mysql/slow.log > /tmp/slow.json

看报表时，**不要只看「最慢的那一条」**，要看三列：

  • Response time 的总占比：一条 8 秒的报表导出查询，一天跑一次，和一条 0.6 秒的首页查询，一分钟跑 200 次，谁的危害大一目了然。优化要按「总耗时占比」排队，不是按单条耗时。
  • Rows examined / Rows sent 的比值：这是判断索引好不好的硬指标。比值超过 100 基本可以确定索引设计有问题；比值在 2 以内说明索引已经很精准，慢的原因大概率是数据量大、返回字段多，或者锁等待。
  • Query time 的标准差：同一条语句有时 0.2 秒有时 6 秒，说明它不稳定。这种波动通常指向三件事：统计信息过期导致执行计划切换、锁等待、或者 buffer pool 被别的大查询挤掉了。

三、读执行计划：只看三个地方

EXPLAIN 的输出字段很多，实际上判断一条查询「病在哪」只需要看三处。

EXPLAIN ANALYZE
SELECT id, title, created FROM posts
WHERE category_id = 12 AND status = 'publish'
ORDER BY created DESC LIMIT 20;

第一处：type 列。这是一条排查主线，从小到大依次是 system > const > eq_ref > ref > range > index > ALL。看到 index 不要高兴太早，它意味着「扫了整个索引」而不是「用了索引查找」，数据量大时和全表扫差不多；看到 ALL 就更不用说了。

第二处：Extra 列。这里出现的词几乎就是诊断结论：

  • Using filesort：排序没能用上索引顺序，需要额外排序。数据量小时无所谓，几十万行时会直接吃满 CPU 和磁盘临时表。
  • Using temporary：用了临时表，常见于 GROUP BY 和 DISTINCT。和 filesort 一起出现时尤其危险。
  • Using index condition：索引下推，一般是好事，说明部分条件下推到了存储引擎层。
  • Using where 且 rows 很大：索引只用到了最左一列，剩下的条件在服务层逐行过滤。

第三处：rows 和 filtered。rows 是优化器估算的扫描行数，filtered 是过滤后剩余比例。两个数相乘就是实际参与后续操作的行数。如果 rows 估算和 EXPLAIN ANALYZE 给出的实际行数差了一个数量级，说明统计信息不准，先 ANALYZE TABLE，别急着加索引。

这里有个常见误区：很多人看到 Using filesort 就去加索引，结果加了还是 filesort。原因往往是索引列顺序不对，后面第四节会细说。

四、索引建了却不用：五个真实原因

这是本站读者问得最多的一类问题。索引存在、SHOW INDEX 里能看到，但执行计划里就是不选它。按出现频率从高到低排：

第一，没满足最左前缀。建了 (category_id, status, created)，查询条件写成 WHERE status = 'publish' AND created > '2026-01-01'，跳过了最左列 category_id，整条索引都用不上。规则很硬：联合索引必须从最左列开始连续使用。

第二，在索引列上做运算或函数。WHERE DATE(created) = '2026-10-01' 会让 created 上的索引整体失效。改成范围写法 created >= '2026-10-01' AND created < '2026-10-02' 立刻就能用上。同理还有 WHERE id + 0 = 100、WHERE LEFT(title, 3) = 'abc'。

第三，隐式类型转换。这一条最隐蔽：user_id 是 VARCHAR，但 PHP 传进来的是数字，SQL 里写成 WHERE user_id = 12345，MySQL 会把列转成数字比较，索引直接作废。字符集不一致（表是 utf8mb4、连接是 latin1）也会导致同样的结果。用 SHOW WARNINGS 跟在 EXPLAIN 后面，能看到 Cannot use ref access on index ... due to type or collation conversion 这类提示。

第四，优化器选错了路线。当条件命中的行数占全表 20% 以上时，走索引再回表反而比全表扫更慢，优化器会主动放弃索引——这在数据分布倾斜时是合理的。此时要做的不是 FORCE INDEX，而是看能不能建覆盖索引，或者把查询拆成「先查主键再回表」两步。

第五，隐式排序没吃到索引。WHERE category_id = 12 ORDER BY created DESC 想用上索引，索引必须是 (category_id, created) 这个顺序，不能是 (created, category_id)。顺序错了就要 filesort。这个坑在分页查询里出现率极高。

五、从慢日志到修复：一个真实案例

拿一个典型场景走一遍完整链路。某站的文章列表页，慢日志里出现：

# Query_time: 4.12  Rows_examined: 412300  Rows_sent: 20
SELECT * FROM posts
WHERE status = 'publish'
ORDER BY created DESC
LIMIT 20 OFFSET 0;

这张表 41 万行，status 上有单列索引。EXPLAIN 显示 type=ALL、Extra=Using where; Using filesort。分析：status 只有两个值，90% 都是 publish，选择性极差，优化器放弃索引是对的；真正的病根是 ORDER BY created DESC 没有索引可用。

修复方式是建 (status, created) 联合索引。因为 status 是等值条件、created 是排序条件，索引顺序「等值列在前、排序列在后」正好让 MySQL 顺着索引顺序取 20 行直接返回，不需要排序也不需要扫全表。

ALTER TABLE posts ADD INDEX idx_status_created (status, created);
-- 建完先 ANALYZE，让优化器看到新索引
ANALYZE TABLE posts;
EXPLAIN SELECT * FROM posts WHERE status='publish'
ORDER BY created DESC LIMIT 20;
-- 期望：type=ref, Extra 里没有 Using filesort

上线前先确认一件事：ALTER TABLE 在大表上会锁表。MySQL 8 的 ALGORITHM=INPLACE, LOCK=NONE 对加索引一般是支持的，但并不是所有情况都能在线完成。几十万行以内通常几秒到几十秒，放到凌晨业务低谷执行即可；上千万行的表就必须用 pt-online-schema-change 或 gh-ost，不要硬来。

六、有些「慢 SQL」根本不是 SQL 的问题

这一点必须单独讲，因为它是排查成本最高、最容易误判的一类。慢日志记录的是「服务端执行时间」，但用户感知的慢可能来自数据库之外：

  • 锁等待：慢日志里有 Lock_time 字段，如果它占了 Query_time 的大部分，说明是在等行锁或元数据锁，而不是执行慢。此时要查的是「谁持有了锁」，SHOW ENGINE INNODB STATUS 里的 TRANSACTIONS 段最直接。长事务拖住一批小查询，是电商和论坛站最常见的性能事故。
  • 连接排队：PHP 一次性创建几百个连接，MySQL 的 max_connections 打满后新请求开始排队。此时每条 SQL 本身都不慢，但用户要等连接。看 Threads_connected 和 Threads_running 两个值的差距就能判断。
  • 网络与连接池：应用和数据库不在同一台机器时，每次新建连接的握手（含 DNS 解析）可能就要几十毫秒。用 skip_name_resolve 关掉反向解析、或让应用用持久连接，效果立竿见影。
  • 锁表级别的 DDL：某个定时任务在跑 ALTER TABLE，期间所有相关查询全部排队，慢日志里会突然冒出一堆平时很快的语句。这种「齐刷刷变慢」的形态，先看有没有 DDL 在跑。

判断方法很简单：在慢日志报表里看 Query_time 的分布。如果同一批语句的耗时集中在某个时间窗内整齐地上涨，那是外部因素；如果是少数几条一直很慢，那才是语句本身的问题。

七、把小站也负担得起的日常节奏定下来

最后说流程。小站运维最怕的是「出了事再查」，因为查的时候日志早滚没了。把下面这几件事固定成节奏，成本很低但收益很大：

  • 每天：一条 cron 跑 pt-query-digest --since '24h ago'，只输出总耗时前 5 名的摘要，发到邮箱或写入文件。花十秒看一眼就能发现新冒出来的慢查询。
  • 每周：检查一次 performance_schema 里的「无用索引」——索引建了一堆但从未被使用，只会拖慢写入。这个站小，可以直接用 sys.schema_unused_indexes 视图，MySQL 5.7 以上自带。
  • 每次改 SQL 之前：养成先用 EXPLAIN 看一遍的习惯，特别是加了 ORDER BY、GROUP BY、LIKE 的查询。改动之前就知道代价，比上线之后回滚便宜得多。
  • 每月：对核心表跑一次 ANALYZE TABLE，确认统计信息不是陈旧的。数据量大且分布跳跃的表，甚至可以考虑把 innodb_stats_persistent 的采样页数调高。

还要提醒一件事：优化完一条慢 SQL，**不要立刻把慢日志关了**。改成参数之后，很可能有另一条语句从「第二名」变成「第一名」。至少再观察一周，确认总耗时曲线真的降下来了，再把 long_query_time 放回去。

八、小结：慢 SQL 治理的三条主线

回头看，这套流程其实只有三条主线。第一是**可见性**：日志开了吗、参数对不对、有没有被 min_examined_row_limit 挡住噪声、报表是不是按总耗时排序看。第二是**归因**：到底是索引没吃到、统计信息不准、锁等待、还是连接排队，四类问题对应四种完全不同的修法，判断错方向就会白改。第三是**节奏**：把它变成一个每天十秒、每周十分钟的例行动作，而不是等网站崩了才想起数据库。

对小站来说，真正致命的往往不是「有一条 SQL 慢了 2 秒」，而是这个问题反复出现而没人知道原因。一条慢 SQL 修好只是战术动作，把「日志—聚合—归因—验证」这条链路固定下来，才是战术变成能力的地方。

Last modification：October 31st, 2026 at 08:30 pm
© 允许规范转载

相关文章

Leave a Comment