MySQL 慢查询怎么定位：开启日志与分析工具

数据库 CPU 打满，但不知道是哪条 SQL 干的。慢查询日志就是那个「谁在拖后腿」的名单，先打开它。

一、开启慢查询日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;   -- 记录未走索引的
SHOW VARIABLES LIKE 'slow_query_log_file';

long_query_time 支持小数（如 0.5）。生产环境建议 1 秒起步，太敏感会产生大量日志。

永久生效写进 my.cnf：

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

注意：SET GLOBAL 重启失效，且对已存在的连接不生效（新连接才用新值）。

二、分析日志

日志是纯文本，直接看能看但效率低。两个工具：

# 自带工具，按次数/耗时排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Percona 工具，输出更专业（推荐）
pt-query-digest /var/log/mysql/slow.log

pt-query-digest 会把相同「指纹」的 SQL 聚合，给出总耗时占比、平均耗时、扫描行数、示例语句。优先优化总耗时占比最高的那条，而不是单次最慢的那条——一条 10 秒跑一次的 SQL，危害远大于一条 2 秒跑一万次的。

三、五类常见慢因

1. 缺索引 / 索引失效

最常见。用 EXPLAIN 确认是否全表扫，参考索引失效的六个场景。

2. 深分页

-- 越翻越慢：要扫描 OFFSET + LIMIT 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 改成游标式：记住上一页最后一个 id
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

LIMIT 大偏移量必须扫过前面所有行再丢弃，这是设计上的必然。业务上应限制最大页数，或改用「上一页/下一页」而非跳页。

3. 大事务 / 长事务

一个事务里更新十万行，会长时间持锁、产生大量 undo，还会拖慢主从同步。批量操作要分批提交（每批 500–1000 行），并在批间稍作停顿。

4. 联表没走索引

JOIN 的关联字段两边类型不一致（一个 int 一个 varchar）会退化为笛卡尔积级别的扫描。检查 EXPLAIN 里的 Using join buffer。

5. 统计信息过期

优化器选错索引。ANALYZE TABLE t; 更新统计信息后重试。

四、一个优化实例

-- 慢：全表扫 + 排序 + 回表
SELECT * FROM orders
WHERE status = 1 AND created_at > '2026-10-01'
ORDER BY created_at DESC LIMIT 20;

-- 思路：联合索引 (status, created_at)，并只取需要的列
ALTER TABLE orders ADD INDEX idx_status_time (status, created_at);

SELECT id, user_id, amount, created_at FROM orders
WHERE status = 1 AND created_at > '2026-10-01'
ORDER BY created_at DESC LIMIT 20;

改完后 EXPLAIN 应从 ALL + filesort 变成 range + 无 filesort。

五、不能只靠慢日志

慢日志只记录「已经超过阈值」的查询，会漏掉两类问题：

单次 10ms 但每秒执行 5000 次的查询，总耗时很吓人却没进慢日志。
锁等待、连接数打满这类问题不体现在 SQL 耗时上。

所以还要看：

SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW ENGINE INNODB STATUS;      -- 看锁等待
SHOW PROCESSLIST;               -- 当前在跑什么

以及数据库整体的 QPS、连接数、缓冲池命中率（Innodb_buffer_pool_read_requests 与 ..._reads 的比值）。

六、上线前的一道关

新 SQL 必须过 EXPLAIN，type 不能是 ALL（大表）。
避免 SELECT *，尤其是带 TEXT/BLOB 的表。
分批处理，别在一个事务里改太多行。
加索引要评估写入影响，用 pt-online-schema-change 或 gh-ost 避免锁表。

小结

先开 slow_query_log，用 pt-query-digest 按总耗时排序，优先修占比最高的；深分页用游标式改写；大事务分批提交；别只看慢日志，还要看连接数与锁等待。