当前位置：首页 > news >正文

MySQL慢查询优化实战教程：200万数据从3秒优化到50ms（EXPLAIN + 索引设计 + 延迟关联）

news 2026/5/12 15:00:13

手把手带你用 EXPLAIN + 索引优化 + SQL 改写，把一条 3 秒的慢查询干到50ms 以内。

背景

最近在做一个电商项目的订单列表查询，页面加载巨慢。打开 Chrome DevTools 一看，一个接口响应 3.2 秒。排查下来，罪魁祸首是一条 SQL。这篇文章记录完整的排查和优化过程，希望对你有帮助。

第一步：定位慢查询

1.1 开启慢查询日志

先确认 MySQL 慢查询日志是否开启：

SHOWVARIABLESLIKE'slow_query%';SHOWVARIABLESLIKE'long_query_time';

如果没开，临时开启：

SETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=1;-- 超过1秒就记录SETGLOBALslow_query_log_file='/var/log/mysql/slow.log';

生产环境建议写到my.cnf里持久化：

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

1.2 找到问题 SQL

用mysqldumpslow快速分析慢查询日志：

mysqldumpslow-sat-t10/var/log/mysql/slow.log

定位到这条 SQL：

SELECTo.id,o.order_no,o.total_amount,o.status,o.created_at,u.nickname,u.phoneFROMorders oLEFTJOINusers uONo.user_id=u.idWHEREo.status=2ANDo.created_atBETWEEN'2026-01-01'AND'2026-03-01'ORDERBYo.created_atDESCLIMIT20OFFSET0;

看起来很普通，但在 200 万订单数据量下跑了 3.2 秒。

第二步：用 EXPLAIN 分析执行计划

EXPLAINSELECTo.id,o.order_no,o.total_amount,o.status,o.created_at,u.nickname,u.phoneFROMorders oLEFTJOINusers uONo.user_id=u.idWHEREo.status=2ANDo.created_atBETWEEN'2026-01-01'AND'2026-03-01'ORDERBYo.created_atDESCLIMIT20OFFSET0;

结果：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	o	ALL	NULL	NULL	2034567	Using where; Using filesort
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	1	NULL

三个致命问题一眼看出来：

type = ALL— 全表扫描，200 万行一行行找
key = NULL— 没命中任何索引
Using filesort— 排序走的磁盘临时文件，不是索引排序

第三步：优化方案

3.1 创建合适的联合索引

分析 WHERE 和 ORDER BY 的字段：

WHERE status = 2— 等值查询
WHERE created_at BETWEEN ...— 范围查询
ORDER BY created_at DESC— 排序

联合索引的设计原则：等值条件放前面，范围/排序字段放后面。

ALTERTABLEordersADDINDEXidx_status_created(status,created_at);

为什么这样设计？

status是等值查询（=），放第一位，先大幅缩小数据范围
created_at既是范围查询又是排序字段，放第二位，索引天然有序，可以避免 filesort

3.2 再次 EXPLAIN 验证

加完索引后再跑一次 EXPLAIN：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	o	range	idx_status_created	idx_status_created	8543	Using index condition
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	1	NULL

变化很明显：

type: ALL → range— 从全表扫描变成索引范围扫描
rows: 2034567 → 8543— 扫描行数降了 200 多倍
filesort 消失了— 排序直接走索引

此时查询耗时：3.2s → 120ms。好了很多，但还能更快。

3.3 使用覆盖索引进一步优化

当前 SQL 还需要回表查order_no、total_amount等字段。如果查询的字段都在索引里，就不用回表了（覆盖索引）。

但订单表字段太多，全放索引不现实。换个思路——先查 ID，再关联取数据：

SELECTo.id,o.order_no,o.total_amount,o.status,o.created_at,u.nickname,u.phoneFROMorders oINNERJOIN(SELECTidFROMordersWHEREstatus=2ANDcreated_atBETWEEN'2026-01-01'AND'2026-03-01'ORDERBYcreated_atDESCLIMIT20OFFSET0)tONo.id=t.idLEFTJOINusers uONo.user_id=u.id;

这就是经典的延迟关联（Deferred Join）技巧：

子查询只查id，完全走覆盖索引，不回表
外层用id精确取 20 条数据

最终耗时：120ms → 45ms。

效果对比

指标	优化前	第一次优化	最终效果
扫描行数	2,034,567	8,543	20
是否 filesort	是	否	否
是否回表	是（全表）	是（8543次）	是（20次）
响应时间	3,200ms	120ms	45ms

踩坑记录

坑 1：联合索引字段顺序搞反了

一开始建了INDEX(created_at, status)，结果 EXPLAIN 显示还是全表扫描。

原因：created_at是范围查询，放在前面会导致status无法利用索引。联合索引遵循最左前缀原则，范围查询会“截断”后面的字段。

正确的顺序是：等值字段在前，范围字段在后。

坑 2：OFFSET 过大导致深分页变慢

当用户翻到第 1000 页时LIMIT 20 OFFSET 20000，即使有索引也会很慢，因为 MySQL 会扫描前 20000 条再丢掉。

解决方案——游标分页，用上一页最后一条的created_at和id做条件：

SELECTidFROMordersWHEREstatus=2ANDcreated_at<='2026-02-15 13:20:00'ANDid<12345ORDERBYcreated_atDESC,idDESCLIMIT20;

这样无论翻到第几页，性能都是稳定的。

坑 3：时间字段用字符串比较

-- 错误写法：对索引列做了隐式类型转换，索引失效WHEREDATE(created_at)='2026-01-01'-- 正确写法：保持索引列原样WHEREcreated_at>='2026-01-01 00:00:00'ANDcreated_at<'2026-01-02 00:00:00'

优化速查表

遇到慢查询时，按这个顺序排查：

步骤	动作	关注点
1	`EXPLAIN`看执行计划	type 是否 ALL？key 是否 NULL？
2	检查索引	WHERE/ORDER BY 字段有没有合适的索引？
3	检查索引顺序	等值在前，范围在后？
4	检查是否回表	能否用覆盖索引或延迟关联？
5	检查分页方式	OFFSET 大不大？能否改游标分页？
6	检查隐式转换	索引列上有没有函数调用或类型转换？