为什么SQL深度分页查询越往后翻页速度越慢应该怎么解决？

MySQL的LIMIT offset, size越往后越慢是因为offset大导致数据库必须从头扫描并丢弃大量数据，如LIMIT 1000000,20需读取至少1000020行再丢弃前100万行，引发I/O、回表、排序等开销；优化方案包括游标分页（WHERE id > last_id）和延迟关联（先查ID再JOIN）。

MySQL的LIMIT offset, size为什么越往后越慢

不是“LIMIT慢”，是offset大导致数据库必须从头扫描并丢弃大量数据。比如LIMIT 1000000, 20，MySQL仍要读取至少1000020行（含索引页和数据页），再丢掉前100万行——这些被丢弃的行照样走I/O、触发回表、参与排序，CPU和磁盘压力一点没少。

常见错误现象：EXPLAIN显示rows值随offset线性增长；Handler_read_next陡增；慢查询日志里query_time从毫秒级跳到几秒甚至超时。

• 即使ORDER BY字段有索引，优化器也常因统计信息不准或谓词复杂放弃索引覆盖扫描
• 若SELECT *且索引不覆盖，每条被跳过的记录都可能触发一次回表，随机I/O雪崩
• sort_buffer_size不足时，排序阶段会写磁盘临时文件，性能断崖下跌

用WHERE id > last_id替代OFFSET实现游标分页

这是目前最稳定、可扩展性最强的方案，适用于时间线、Feed流、订单列表等“向下滚动”场景。

核心前提是排序字段必须单调且唯一（推荐主键或created_at, id组合）。

• 第一页：SELECT * FROM orders WHERE status = 'paid' ORDER BY id LIMIT 20
• 拿到最后一条的id = 105872后，第二页写成：SELECT * FROM orders WHERE status = 'paid' AND id > 105872 ORDER BY id LIMIT 20
• 如果按时间排序且created_at可能重复，必须追加主键兜底：ORDER BY created_at DESC, id DESC，WHERE条件也要对应写成复合判断

优势：每次都是索引范围扫描，执行时间恒定，与总数据量无关；缺点：无法跳转任意页码，只支持顺序翻页。

用延迟关联（Deferred Join）缓解大offset下的回表压力

当业务仍需支持“跳页”（如后台管理系统的页码输入框），又不能改成分页模式时，这是最务实的SQL层优化手段。

原理是把“查全字段”拆成两步：先用覆盖索引快速定位ID，再用这些ID批量回查。

• 原慢查询：SELECT * FROM articles WHERE category_id = 5 ORDER BY created_at DESC LIMIT 10000, 20
• 优化后：SELECT a.* FROM articles a INNER JOIN (SELECT id FROM articles WHERE category_id = 5 ORDER BY created_at DESC LIMIT 10000, 20) t ON a.id = t.id
• 关键前提：category_id, created_at必须有联合索引，且子查询只查id（避免回表）

效果：子查询走覆盖索引，扫描行数大幅减少；外层JOIN避免全表扫描。实测可将LIMIT 1000000, 20从50秒降到0.2秒左右。

别忽略排序字段索引和业务约束的配合

所有优化方案都依赖一个前提：排序字段必须走索引。但光建索引不够，容易踩的坑比想象中多。

• ORDER BY created_at却没给created_at建索引？直接退化为filesort + 全表扫描
• WHERE条件含函数（如WHERE DATE(created_at) = '2024-01-01'）？索引失效，offset再小也慢
• 联合索引顺序错（如建了(id, created_at)却ORDER BY created_at）？无法利用索引有序性
• 业务允许但未强制限制页码深度？用户真翻到第50万页时，再好的SQL也扛不住

真正卡住性能的，往往不是技术方案本身，而是排序字段索引缺失、类型隐式转换、或前端传入极大offset却无校验——这些细节比选哪种分页方式更早决定成败。