慢查询排查

摘要：慢查询不一定是 SQL 写得差，也可能是锁等待、统计信息过期或实例资源不足。本文先确认慢日志是否真的开启，再用活跃会话抓现场，最后用执行计划验证索引是否生效。

适用环境

mysql --version 2>/dev/null
psql --version 2>/dev/null
which pt-query-digest 2>/dev/null

排障步骤

第 1 步：确认慢日志已开启且阈值合理

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

慢日志默认可能关闭

未开启时"没有慢日志"不代表没有慢查询。开启后注意磁盘占用，通常需要配合轮转。

第 2 步：抓当前活跃会话（现场优先）

SELECT id, user, host, db, command, time, state, LEFT(info, 120)
FROM information_schema.processlist
WHERE command <> 'Sleep' AND time > 2
ORDER BY time DESC LIMIT 20;

time 大且 state 为 Sending data、Sorting result 说明在执行；为 Waiting for ... lock 则是锁等待。

第 3 步：区分锁等待与执行慢

SELECT * FROM sys.innodb_lock_waits LIMIT 20;
SHOW ENGINE INNODB STATUS\G

锁等待不会被记入慢日志的"执行时间"统计口径中，容易漏掉。

第 4 步：用执行计划验证索引

EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;

重点看 type（ALL 表示全表扫描）、key（实际用到的索引）、rows（预估扫描行数）、Extra（Using filesort、Using temporary）。

PostgreSQL 对应命令：

psql -c 'EXPLAIN (ANALYZE, BUFFERS) SELECT ...;'

EXPLAIN 是估算，不等于实际执行

只有 EXPLAIN ANALYZE（或 MySQL 8.0.18+ 的 EXPLAIN ANALYZE）会真实执行并返回实际行数。但 ANALYZE 会真正跑一遍 SQL，不要对写操作使用。

第 5 步：聚合分析慢日志

pt-query-digest --limit 20 /var/lib/mysql/slow.log > /tmp/slow-report.txt
head -60 /tmp/slow-report.txt

看排名前列的查询指纹（同一类 SQL 的聚合），而不是单条语句。

第 6 步：确认实例资源是否也是瓶颈

iostat -x -d 1 3
free -h
grep -E 'innodb_buffer_pool_size|max_connections' /etc/my.cnf 2>/dev/null

验证

SHOW STATUS LIKE 'Slow_queries';
SELECT COUNT(*) FROM information_schema.processlist WHERE command <> 'Sleep' AND time > 2;

curl -sS -o /dev/null -w 'total=%{time_total}\n' http://127.0.0.1:8080/api/query

常见坑

隐式类型转换导致索引失效

字段是字符串却传了数字（或反之），索引会整体失效，执行计划中 key 为 NULL。

在索引列上使用函数

WHERE DATE(created_at) = ... 会让索引无法用于范围查找，应改写为范围条件。

在线上直接执行未验证的优化语句

OPTIMIZE TABLE、加索引、删数据在大表上会造成长时间锁表或主从延迟。应先在从库或低峰期评估影响。

只看平均耗时

几条超慢 SQL 会被大量快查询平均掉，应看 P95/P99 与最慢样本。

参考资料

• MySQL 8.0 - The Slow Query Log
• MySQL 8.0 - EXPLAIN Output Format
• pt-query-digest documentation
• PostgreSQL - Using EXPLAIN