当数据表达到千万甚至亿级规模时，使用传统的LIMIT offset, size方式实现分页往往会让查询时间从毫秒级飙升到秒级。看似简单的翻页操作，在深页码处会触发大量的数据读取和丢弃，数据库不得不扫描并跳过offset指定的行数。以MySQL的InnoDB引擎为例，LIMIT 1000000, 10意味着需要读取前100万行数据后才能返回10条结果，尽管最终只取10行，但扫描成本极高。这种性能问题并非个别案例，而是所有关系型数据库在深度分页场景下的通病。下面本文将重点介绍两种经过生产环境验证的优化思路：延迟关联与游标分页。
深度分页的性能瓶颈在哪里？
传统分页语句通常写作SELECT * FROM table LIMIT offset, page_size。在offset较小时，例如LIMIT 0, 10，执行速度很快，因为数据库只需读取前10行即可返回。随着offset增大，比如LIMIT 1000000, 10，问题就暴露出来了。以MySQL为例，InnoDB引擎需要先根据查询条件找到所有匹配的行，然后逐行计数，跳过offset指定的行数，再返回后page_size行。这个过程中，即便有合适的索引，数据库也必须从索引中顺序扫描offset + page_size条记录，对于offset = 1000000的情况，实际扫描了1000010条索引记录，其中100万条被丢弃。这种无用功会消耗大量CPU和I/O。
更糟的是，如果SELECT语句使用了SELECT *，并且某些列不在索引中，数据库还需要根据主键回表获取完整行数据。回表操作会带来随机I/O，进一步拖慢查询。当offset达到百万级别时，查询时间可能从几毫秒膨胀到几秒甚至十几秒，严重影响用户体验。因此，要优化深度分页，核心思路就是减少扫描和回表的数据量，以及避免跳过大量记录。
此外，不同数据库对LIMIT offset, size的实现略有差异，但基本原理相似。PostgreSQL的LIMIT/OFFSET也会产生类似的开销。理解这个瓶颈之后，才能针对性地应用延迟关联和游标分页技术。
延迟关联：先取主键再回表
延迟关联也称延迟回表，其核心思想是避免在扫描阶段回表，先通过覆盖索引获取目标页面的主键集合，然后再根据主键关联原表取出完整数据。这样在跳过offset的过程中，只需要扫描覆盖索引，索引叶子节点通常比整行数据小得多，扫描成本大幅下降。具体SQL写法如下：
SELECT o.*
FROM orders o
INNER JOIN (
SELECT id
FROM orders
WHERE user_id = 123
ORDER BY id
LIMIT 1000000, 10
) tmp ON o.id = tmp.id
ORDER BY o.id;
上述查询中，子查询只选择主键id，并且确保WHERE条件中涉及的列和ORDER BY列构成覆盖索引。比如对user_id和id建立联合索引，这样子查询只需扫描该索引，不需要回表，扫描100万条索引记录的速度远快于扫描100万行数据。然后外层通过与主表的INNER JOIN获取完整行，此时只关联10条记录，回表次数极低。
延迟关联的优点在于它不需要改变业务逻辑，仅仅改写SQL。但它要求必须有合适的覆盖索引来支撑子查询，否则优化效果会打折扣。另外，如果WHERE条件非常复杂，可能无法建立有效的覆盖索引，此时延迟关联的收益有限。还有一个细节：子查询的LIMIT子句必须包含ORDER BY，否则主键顺序不确定，可能导致结果不一致。
游标分页：基于有序键的增量查询
游标分页又称键集分页，它完全绕开了OFFSET，而是通过上一页最后一条记录的有序字段值来定位下一页。通常使用自增主键或业务上单调递增的时间戳作为游标。以自增主键id为例，第一页使用LIMIT 10获取数据，并记录最后一条记录的id作为游标last_id。请求下一页时，查询条件变为WHERE id > last_id ORDER BY id LIMIT 10。这样数据库每次只扫描10条记录，无论翻到第几页，性能都恒定。
-- 第一页 SELECT id, user_id, order_time FROM orders ORDER BY id LIMIT 10; -- 假设第一页最后一条id为100 -- 第二页 SELECT id, user_id, order_time FROM orders WHERE id > 100 ORDER BY id LIMIT 10;
游标分页的关键在于游标列必须有序且唯一，才能保证分页的稳定性和不漏数据。如果使用非唯一列作为游标，比如时间戳，当同一时间戳有多条记录时，可能会漏掉部分数据。解决办法是使用复合游标，例如WHERE (order_time > ?) OR (order_time = ? AND id > ?)，并配合合适的索引。不过实现复杂度相应增加。
游标分页的显著优势是性能稳定，不受页码深度影响，特别适合无限滚动或者顺序遍历大表的场景。但它的缺点是只能顺序翻页，无法直接跳转到任意页码，因此不适合需要页码跳转的传统分页UI。若要实现跳页，可以结合记录总数和估算位置，或者退化为延迟关联方案。
其他优化手段与选型建议
除了延迟关联和游标分页，还有一些辅助优化手段值得了解。例如覆盖索引可以直接提升LIMIT查询的性能，如果SELECT的列全部包含在索引中，即使offset很大，扫描也较快。但覆盖索引难以满足所有业务查询，尤其是需要大字段的场景。另一个思路是限制最大跳转页数，比如只允许用户查看前N页，超过N页则引导用户使用更精确的筛选条件缩小范围，从而避免极端深度分页。
在数据库选型上，一些新型数据库或存储引擎对深度分页有特殊优化。例如MySQL 8.0的窗口函数或CTE可以辅助复杂查询，但底层仍然是类似的扫描成本。对于超大规模数据集，还可以考虑使用Elasticsearch等搜索引擎替代关系型数据库做分页，不过它们也面临深度分页的挑战，需要借助search_after等机制（类似游标分页）。
实际项目中，选择哪种方案取决于业务形态。如果前端需要页码跳转功能，延迟关联是首选；如果业务允许无限滚动或按顺序翻页，游标分页能提供最佳性能。有时也可以混合使用，比如第一页到第100页使用延迟关联，更深的分页则通过限制跳转或切换游标模式。无论哪种方案，都应当配合EXPLAIN分析执行计划，确保索引被正确使用。