MySQL 慢查询定位与索引优化实战：从慢日志到 EXPLAIN

背景

线上接口偶发变慢，CPU 并不高，但数据库连接数堆积。这类问题多数来自少数几条慢 SQL。本文整理一套可复现的流程：开启慢查询日志、聚合定位、用 EXPLAIN 分析、建立合适索引并验证。云数据库 MySQL 的参数一般可在控制台“参数设置”中修改，具体入口以控制台为准。

1. 开启慢查询日志

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;                 -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 可选，注意日志量

SET GLOBAL 重启后失效，需要持久化时写入 my.cnf 的 [mysqld] 段，或在云数据库控制台修改参数。long_query_time 对已建立的连接不生效，需重连。

2. 聚合慢日志，找出最值得优化的语句

单条慢不一定最该优化，应关注“总耗时最大”的语句模板。MySQL 自带 mysqldumpslow：

# 按总耗时排序，取前 10
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 按平均耗时
mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log

也可用 Percona Toolkit 的 pt-query-digest，输出包含调用次数、扫描行数与返回行数之比。重点看 Rows_examined 远大于 Rows_sent 的语句，这通常意味着索引缺失或失效。

3. 用 EXPLAIN 分析

假设订单表有千万级数据，下面的查询很慢：

EXPLAIN SELECT id, amount FROM orders
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;

关注这几列：

• type：出现 ALL 表示全表扫描；期望至少 ref/range。
• key：实际使用的索引，为 NULL 说明没走索引。
• rows：预估扫描行数。
• Extra：出现 Using filesort、Using temporary 通常需要优化。

4. 建立联合索引

遵循“等值条件在前、排序字段在后”的原则：

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

这样 user_id、status 等值过滤后，数据已按 created_at 有序，可避免 filesort。再次 EXPLAIN，应看到 key 命中新索引、rows 大幅下降。大表加索引建议在低峰期执行，InnoDB 支持在线 DDL，可显式指定 ALGORITHM=INPLACE, LOCK=NONE，不支持时会报错而不是悄悄锁表。

5. 常见的索引失效场景

• 对索引列使用函数或运算：WHERE DATE(created_at) = '2026-10-01'，应改为范围条件。
• 隐式类型转换：字符串列用数字比较，如 phone = 13800000000。
• 联合索引不满足最左前缀。
• LIKE '%abc' 前缀通配。
• 深分页 LIMIT 1000000, 20，可改为基于上一页最大 id 的写法：WHERE id < :last_id ORDER BY id DESC LIMIT 20。

6. 验证与回归

• 对比优化前后同一语句的耗时与 Rows_examined。
• 使用 SHOW INDEX FROM orders; 检查冗余索引，索引越多写入越慢。
• 观察一段时间慢日志条数是否下降；上线前在预发库用相近数据量验证，数据分布不同会导致优化器选择不同的索引。

小结

慢 SQL 优化的闭环是：开日志、按总耗时聚合、EXPLAIN 定位、联合索引、复测。先解决占比最高的几条语句，往往就能让整体延迟明显下降。