MySQL 慢查询优化

摘要：慢查询优化的标准流程是「开慢日志 → 聚合找 Top SQL → EXPLAIN 看执行计划 → 加索引或改写 SQL → 复测」。本文给出每个环节的可执行命令与判读要点。

适用环境

# 查看慢日志是否已开启及文件路径
mysql -uroot -p -e "SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time';"

操作步骤

1. 开启慢查询日志（可在线生效）

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;              -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;

# 持久化到 /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/log/slow.log
long_query_time=1

2. 聚合分析慢日志

# 官方工具：按平均耗时排序
mysqldumpslow -s at -t 10 /data/mysql/log/slow.log

# Percona Toolkit：更详细的报告（推荐）
pt-query-digest /data/mysql/log/slow.log > /tmp/slow_report.txt
head -n 60 /tmp/slow_report.txt

3. 用 EXPLAIN 看执行计划

EXPLAIN SELECT * FROM orders WHERE user_id=1001 AND status='PAID' ORDER BY created_at DESC LIMIT 20\G

重点看三列：type（ALL 表示全表扫描，需优化）、key（是否用到索引）、rows（扫描行数）。

4. 建立合适索引

-- 按「等值条件在前、排序列在后」建联合索引
CREATE INDEX idx_orders_user_status_time ON orders(user_id, status, created_at);

-- 确认是否命中
EXPLAIN SELECT * FROM orders WHERE user_id=1001 AND status='PAID' ORDER BY created_at DESC LIMIT 20\G

危险

在线大表加索引会锁表并可能引发主从延迟。务必先确认表大小，并在低峰期执行（MySQL 8.0 的 InnoDB 支持 ALGORITHM=INPLACE, LOCK=NONE，但仍要评估磁盘与延迟）。

5. 常见改写手法

-- 反例：索引列上做函数运算，索引失效
SELECT * FROM orders WHERE DATE(created_at)='2026-10-09';
-- 正例：改成范围查询
SELECT * FROM orders WHERE created_at >= '2026-10-09' AND created_at < '2026-10-10';

-- 反例：SELECT * 拉取多余列，无法覆盖索引
-- 正例：只取需要的列
SELECT id, status, created_at FROM orders WHERE user_id=1001;

验证

# 优化后重新执行，观察扫描行数与耗时
mysql -uroot -p -e "EXPLAIN SELECT ...\G" | grep -E 'type|key|rows'

# 一段时间后对比慢日志中该 SQL 是否消失
mysqldumpslow -s at -t 10 /data/mysql/log/slow.log

常见坑

WARNING

索引不是越多越好：每个索引都会拖慢写入并占用空间，定期清理重复索引与未被使用的索引。

WARNING

long_query_time 在线 SET GLOBAL 只对新连接生效，已有连接需重连；永久生效要写进配置文件。

DANGER

不要用 SELECT * 加 LIMIT 误以为安全——没有合适索引时，LIMIT 20 仍可能扫描全表并排序。

参考资料

• MySQL 慢查询日志文档
• EXPLAIN 输出格式说明
• pt-query-digest 官方文档
• MySQL 优化索引