在业务系统中，列表查询是最常见的功能之一。当用户不断翻页，尤其是翻到非常靠后的页码时，很多开发者会发现接口响应越来越慢，数据库连接占用时间变长。这背后并不是数据库出了问题，而是传统分页写法在深分页场景下存在天然的缺陷。理解这些缺陷并掌握对应的优化方案，是后端开发中一项非常实用的能力。
一、深分页的性能瓶颈在哪里
最常用的分页写法是 LIMIT offset, size。数据库在执行时，会先根据查询条件以及排序规则，从存储引擎层读取符合条件的记录，然后在服务层跳过前 offset 行，只保留后面的 size 行返回。这意味着，如果用户要查第 100000 页、每页 20 条，那么 offset 就是 1999980，数据库实际上需要读取并暂存将近两百万行数据，最后只返回 20 行，其余全部丢弃。
这种大量无效扫描在表数据量小的时候不明显，一旦表达到千万级，并且查询还涉及非索引字段过滤或文件排序，性能下降会非常陡峭。更糟的是，深分页请求往往还会占用大量内存和 IO，影响同一数据库实例上的其他轻量查询。下面用一个最简单的示例说明问题。
-- 传统深分页写法 SELECT id, user_name, age, create_time FROM user ORDER BY create_time DESC LIMIT 1999980, 20;
上面的语句在 user 表数据量很大时，MySQL 需要先在 create_time 索引上定位，再回表拿字段，并顺序跳过前面近两百万行。即使 create_time 有索引，跳过的动作依然无法避免，这是深分页慢的根本原因。
二、延迟关联优化方案
延迟关联的核心思想是：先利用覆盖索引只查询主键或必要索引列，拿到需要返回的那一小批记录的主键，然后再通过主键去回表查询完整字段。因为第一步只走窄索引，不读取大字段，扫描和临时排序的成本大幅降低。
具体做法是把原查询拆成两层，子查询里只取 id 和排序字段，外层再通过 IN 或 JOIN 关联原表。这样数据库在偏移阶段只处理轻量的索引记录，回表动作只发生最终需要的 20 行上。对于宽表或者包含 text 字段的表，效果尤其明显。
-- 延迟关联写法
SELECT u.id, u.user_name, u.age, u.create_time
FROM user u
INNER JOIN (
SELECT id
FROM user
ORDER BY create_time DESC
LIMIT 1999980, 20
) tmp ON u.id = tmp.id
ORDER BY u.create_time DESC;
这种方案改动小，兼容原有排序逻辑，几乎不需要业务层配合。缺点是子查询仍然要计算 offset，当 offset 极大时，索引扫描行数虽少但遍历量依旧存在。另外 JOIN 写法在某些数据库版本中优化器可能选择不佳，需要结合执行计划观察。
三、基于游标的分页方案
游标分页也叫 Seek Method，它不再使用 offset，而是用上一页最后一条记录的排序字段值作为起点，直接做范围查询。比如按 create_time 倒序，下一页就查 create_time 小于上一页末值且 id 小于对应 id 的记录，取前 20 条。
这种方式完全避开了“跳过”动作，数据库可以直接从索引的合适位置开始向后读，时间复杂度接近常数。它非常适合无限下拉、时序数据展示等场景。不过它不支持任意跳页，只能上一页下一页，并且在排序字段有重复值时需要用唯一键兜底，避免漏数据或重复。
-- 游标分页写法，假设上一页末条 create_time 为 '2023-05-01 10:00:00'，id 为 100 SELECT id, user_name, age, create_time FROM user WHERE create_time < '2023-05-01 10:00:00' OR (create_time = '2023-05-01 10:00:00' AND id < 100) ORDER BY create_time DESC, id DESC LIMIT 20;
在代码实现时，前端只需缓存上一页最后一条的游标值，后端拼条件即可。它是对深分页最彻底的优化，但产品交互若要求“跳到第 500 页”则无法满足，需要业务妥协或混合方案。
四、子查询定位边界优化
子查询定位边界与延迟关联类似，但更轻量：先查到目标页起始主键，再取大于等于该主键的 size 行。它适用于按主键或唯一索引排序的场景，或者配合其他条件先圈定起点。
例如按 id 降序分页，可以先算出第 N 页起始 id 的偏移位置，通过子查询拿 id 后再取数。相比直接 LIMIT offset，它减少了回表范围。若排序字段非唯一，仍需组合条件保证边界准确。
-- 子查询定位起点再取数
SELECT id, user_name, age, create_time
FROM user
WHERE id <= (
SELECT id
FROM user
ORDER BY id DESC
LIMIT 1999980, 1
)
ORDER BY id DESC
LIMIT 20;
这种方法在自增主键且按主键排序时非常高效，但在复杂多条件排序下构造子查询较麻烦，且对写频繁导致主键不连续的表，偏移对应关系可能失真，需要谨慎使用。
五、冗余覆盖索引的辅助作用
无论采用哪种优化，索引设计都至关重要。如果排序和过滤字段能组成覆盖索引，数据库甚至不需要回表就能完成分页定位。例如对 (create_time, id) 建联合索引，游标分页和延迟关联都能最大化利用索引有序性。
在真实业务中，可以针对高频深分页接口建立专用覆盖索引，把查询需要的字段尽量纳入，减少随机 IO。当然索引不是越多越好，写性能和维护成本也要权衡。下表列出几种方案的特点。
| 方案 | 是否支持跳页 | 偏移成本 | 适用场景 |
|---|---|---|---|
| 传统 LIMIT | 支持 | 高 | 浅分页、小表 |
| 延迟关联 | 支持 | 中 | 宽表、深分页兼容旧交互 |
| 游标分页 | 不支持 | 低 | 下拉流、时序列表 |
| 子查询边界 | 有限支持 | 低到中 | 主键排序、简单列表 |
综合来看，新系统若交互允许，优先用游标分页；老系统想最小改动提升性能，延迟关联配合覆盖索引是最稳妥的切入点。理解数据访问路径，才能选对优化手段。