我用延迟关联干掉了深分页，响应时间砍了90%

原创于 2026-06-23 09:44:07 发布 · 163 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#SQL #开发语言 #大数据 #深度优先 #宽度优先

话题

#编程达人挑战赛·第10期

我用延迟关联干掉了深分页，响应时间砍了90%

凌晨两点，线上告警响了。用户反馈订单列表加载不出来，排查了半小时发现就一条SQL在拖后腿——全表扫描，扫了500多万行。这种场景每个后端开发都经历过，但真正能系统性解决问题的人并不多。今天我把这次优化的完整过程写出来，从发现问题到最终落地，每一步都有真实数据支撑，希望对你有实际参考价值。

一、问题浮现：一个接口拖垮了整个后台管理系统

我们的电商平台后台有个订单管理页面，支持按订单状态、下单时间范围进行筛选，然后按创建时间倒序展示最新的20条记录。上线半年后，订单数据量从几万涨到了500多万条，这个接口的响应时间从最初的180ms一路飙升到3秒以上，高峰期甚至直接超时。

先看看原始SQL长什么样：

sql

SELECT

o.id, o.order_no, o.user_id, o.total_amount,

o.status, o.created_at, u.nickname

FROM orders o

LEFT JOIN users u ON o.user_id = u.id

WHERE o.status IN (1, 2, 3)

AND o.created_at BETWEEN '2025-01-01' AND '2026-06-22'

ORDER BY o.created_at DESC

LIMIT 20 OFFSET 0;

这条SQL乍一看没什么毛病，但执行计划一拉出来，问题就暴露了。

二、EXPLAIN对比：优化前后的差距有多大

优化之前，EXPLAIN的结果如下表所示：

字段优化前说明

type ALL 全表扫描，最差的访问方式

possible_keys NULL 没有可用索引

key NULL 实际未使用任何索引

rows 4876320 扫描了将近500万行

Extra Using filesort 需要额外的文件排序

几个关键问题非常明确：

1、orders表虽然建了user_id和created_at的单列索引，但查询条件是status IN (1,2,3)配合created_at BETWEEN ...，这两个条件的组合没有对应的联合索引，优化器直接放弃索引选择了全表扫描。

2、ORDER BY created_at DESC触发了Using filesort，意味着MySQL需要把所有符合条件的行加载到内存中再排序，500万行的排序开销可想而知。

3、LEFT JOIN users虽然走的是主键关联，但因为驱动表扫描了近500万行，JOIN的累计开销也被大幅放大。

到这里，优化方向基本清晰了——核心就是两件事：让索引生效，让排序不走文件排序。

三、第一轮优化：建联合索引，让查询走索引扫描

最直接的思路就是给orders表加一个联合索引，把status和created_at都包含进去。这里有个关键点：根据MySQL的最左前缀原则，等值查询的列应该放在前面，范围查询的列放在后面。所以索引顺序是status在前，created_at在后。

sql

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

加完索引后再次执行EXPLAIN，结果如下：

字段优化后变化

type range 从全表扫描变为范围扫描

possible_keys idx_status_created 出现了可用索引

key idx_status_created 实际用上了索引

rows 186542 扫描行数从500万降到18万

Extra Using index condition 有改善但仍有filesort

响应时间从3秒多降到了800ms左右，有明显进步。但ORDER BY的问题依然存在，Using filesort还在，说明索引的顺序和排序方向不匹配。

四、第二轮优化：覆盖索引加降序，彻底干掉文件排序

问题的根子在于：我们建的索引(status, created_at)默认是升序排列的，而查询要求ORDER BY created_at DESC。虽然MySQL 8.0支持索引降序扫描，但为了兼容性和确定性，最稳妥的做法是把索引改成降序，同时把查询需要的字段也塞进索引里，做成覆盖索引，避免回表。

具体操作如下：

sql

-- 先删掉之前的索引

ALTER TABLE orders DROP INDEX idx_status_created;

-- 建降序覆盖索引

ALTER TABLE orders ADD INDEX idx_status_created_cover (

status,

created_at DESC,

id,

order_no,

total_amount

);

同时SQL也做了调整，先把JOIN去掉，只查必要字段：

sql

SELECT

o.id, o.order_no, o.user_id, o.total_amount,

o.status, o.created_at

FROM orders o

WHERE o.status IN (1, 2, 3)

AND o.created_at BETWEEN '2025-01-01' AND '2026-06-22'

ORDER BY o.created_at DESC

LIMIT 20 OFFSET 0;

再次执行EXPLAIN：

字段优化后说明

type range 保持范围扫描

key idx_status_created_cover 使用覆盖索引

rows 98234 扫描行数进一步下降

Extra Using index 覆盖索引，无需回表

Extra 无filesort 排序问题彻底解决

响应时间直接降到了45ms。从3秒到45毫秒，提升了将近70倍。这个结果让整个团队都松了口气。

五、第三轮优化：延迟关联，把JOIN的开销压到最低

上面我们把JOIN去掉了，但实际业务中用户昵称确实需要展示。如果直接LEFT JOIN，虽然users.id是主键，关联很快，但orders表扫描的行数仍然有近10万，每一行都去users表查一次，累计开销也不小。

这里用一个经典技巧——延迟关联。核心思路是：先在子查询里只查orders表，利用覆盖索引拿到20条记录的主键ID，再用这20个ID去关联users表。

sql

SELECT

o.id, o.order_no, o.user_id, o.total_amount,

o.status, o.created_at, u.nickname

FROM (

SELECT id, order_no, user_id, total_amount, status, created_at

FROM orders

WHERE status IN (1, 2, 3)

AND created_at BETWEEN '2025-01-01' AND '2026-06-22'

ORDER BY created_at DESC

LIMIT 20 OFFSET 0

) o

LEFT JOIN users u ON o.user_id = u.id;

这样做的好处非常明显：子查询走覆盖索引，只需要扫描20行；JOIN操作也只执行20次，而不是10万次。最终响应时间稳定在50ms以内，完全满足业务SLA要求。

六、几个实战中容易踩的坑

1、深分页问题：当OFFSET很大时，比如OFFSET 100000，MySQL依然需要扫描前10万行再丢弃。这种场景建议用游标分页替代，也就是WHERE id > 上一页最大ID，避免大OFFSET带来的性能损耗。

2、IN条件元素过多：IN (1,2,3)完全没问题，但如果IN里面塞了几十上百个值，优化器可能会放弃索引走全表扫描。这种情况建议改成JOIN一张临时表，或者拆成多个UNION ALL。

3、索引不是越多越好：每多一个索引，INSERT、UPDATE、DELETE都会变慢，因为索引也需要维护。生产环境加索引之前一定要评估写入频率，别为了一个查询把写入性能搞崩了。

4、EXPLAIN的rows只是估算值：EXPLAIN给出的扫描行数是优化器的估算，和实际执行可能有偏差。真正靠谱的判断依据还是慢查询日志和实际响应时间，EXPLAIN只是帮你缩小排查范围的工具。

5、不要迷信工具推荐的索引：有些ORM框架或者云数据库控制台会自动推荐索引，但它们不了解你的业务查询模式。最终还是得自己根据实际SQL和EXPLAIN结果来判断。

七、写在最后

回头看这次优化，核心就三步：用EXPLAIN定位瓶颈，根据查询条件建合适的联合索引，用覆盖索引和延迟关联把回表和JOIN的开销压到最低。SQL优化这事没有银弹，但有方法。大多数慢查询的根源都是索引没用对，把这一关过了，80%的性能问题都能解决。剩下那20%，才轮到SQL改写、分库分表这些大招上场。

希望这篇实战记录对你有帮助，少走点弯路。