在关系型数据库中，LIMIT子句常用于实现分页展示。但当数据量增长、翻页深度加大时，许多系统的列表接口响应时间会明显变长。要彻底解决这类问题，必须先理解LIMIT偏移量分页的执行机制，再针对性地调整查询方案。
LIMIT偏移分页的基本执行原理
标准SQL中，LIMIT m, n的含义是从结果集的第m条之后取n条记录，m即为偏移量。数据库在执行该语句时，并不会智能地直接跳到第m条，而是先按照ORDER BY规则生成有序结果集，然后从头开始逐行读取，读过的m行被丢弃，再返回接下来的n行。
以MySQL的InnoDB为例，如果ORDER BY字段有索引，引擎会沿索引顺序扫描；如果没有合适索引，则可能触发文件排序。无论哪种方式，偏移量m越大，前面被跳过和丢弃的行就越多，这些行依然要经过读取、排序或索引定位等处理，只是不返回给客户端。这就是深分页变慢的本质原因。
-- 常见深分页写法，offset达到100万时性能极差 SELECT id, user_name, amount FROM orders ORDER BY id ASC LIMIT 1000000, 20;
深分页导致的典型性能问题
当偏移量达到几十万或上百万时，数据库需要扫描大量数据页。即使最终只返回20行，磁盘IO和缓冲池占用却与偏移量成正比。在并发较高的场景下，这类查询容易引发连接堆积、CPU飙升，甚至拖垮整个实例。
另一个容易被忽视的问题是，如果ORDER BY的列不唯一，分页还可能产生数据重复或漏读。例如按非唯一索引排序时，同值行在不同页之间可能发生位置漂移，导致用户翻页看到重复订单或跳过部分记录。
| 偏移量 | 扫描行数 | 典型响应时间 |
|---|---|---|
| 0 | 20 | 1ms |
| 10000 | 10020 | 15ms |
| 1000000 | 1000020 | 800ms |
优化方案之游标分页
游标分页又称seek method，核心思想是放弃偏移量，改用上一页最后一条记录的排序值作为起点。比如按id升序，每次记录上次最大id，下一页查询id大于该值的n条。这样数据库可以利用索引范围扫描，直接定位起始位置，避免丢弃前面所有行。
该方式要求排序字段具备唯一性或配合二级唯一键，且不支持任意跳页，只适合“上一页、下一页”的连续浏览。但在无限下拉、消息流等场景中，游标分页几乎是最优解，性能与偏移量大小无关。
-- 第一页 SELECT id, user_name, amount FROM orders ORDER BY id ASC LIMIT 20; -- 后续页，last_id为上一页最大id SELECT id, user_name, amount FROM orders WHERE id > 1000020 ORDER BY id ASC LIMIT 20;
优化方案之延迟关联
延迟关联用于既要深分页又必须按非主键字段排序的情况。思路是先通过索引查出目标页对应的主键id，再用这些id回表取完整列。由于子查询只遍历索引树，不读取大字段，能显著减少回表成本。
以下示例先按create_time索引取id，再关联原表。虽然仍要跳过偏移量，但索引页比数据页小很多，且避免了提前读取宽列，整体开销大幅下降。如果业务允许，配合覆盖索引效果更佳。
SELECT o.id, o.user_name, o.amount
FROM orders o
INNER JOIN (
SELECT id
FROM orders
ORDER BY create_time ASC
LIMIT 1000000, 20
) t ON o.id = t.id;
架构层面的分页思考
在超大数据集下，单纯靠SQL改写也有上限。可以考虑按时间或租户做分表，让单表数据量可控；或将冷数据归档至数仓，线上只查近期。对于后台管理类需要跳页的查询，可限制最大翻页深度，超出后提示缩小筛选条件。
另外，合理利用缓存也能缓解压力。例如将首页和前几页结果缓存，或把热门筛选条件的结果集预计算。理解LIMIT分页的底层代价，结合业务特征选择方案，才能让列表查询在大数据量下依然稳定。