SQL分页是业务开发中非常常见的需求，传统使用OFFSET加LIMIT的方式在偏移量较小时可以满足需求，但当OFFSET数值增长到一定规模后，查询耗时会呈指数级上升，严重影响系统性能。
OFFSET大值性能差的原因
数据库执行SELECT * FROM table LIMIT n OFFSET m语句时，并不是直接跳过前m条数据再取n条，而是会先扫描前m+n条数据，然后丢弃前m条，只返回后面的n条。当m的数值很大时，需要扫描的数据量会非常多，即使有索引支持，也会产生大量的随机IO，导致查询耗时大幅增加。
优化方案一：游标分页（Seek Method）
游标分页的核心思路是不使用OFFSET，而是基于上一页最后一条数据的主键或者唯一有序字段，作为下一页查询的条件，避免扫描多余的数据。这种方式要求查询的排序字段是唯一且有序的，通常建议使用自增主键或者带唯一索引的时间字段。
假设我们有一张用户表user，主键为id自增，原本的分页查询如下：
-- 传统OFFSET分页，查询第100001到100010条数据 SELECT * FROM user ORDER BY id ASC LIMIT 10 OFFSET 100000;
优化后的游标分页查询如下，假设上一页最后一条数据的id是100000：
-- 游标分页查询，基于上一页最后一条的id取值 SELECT * FROM user WHERE id > 100000 ORDER BY id ASC LIMIT 10;
这种查询方式只需要扫描从100001开始的10条数据，完全避免了大偏移量的扫描问题，性能非常稳定，即使分页到非常靠后的位置，查询耗时也和第一页几乎一致。
优化方案二：覆盖索引+子查询优化
如果业务场景必须使用OFFSET分页，无法使用游标分页，可以通过覆盖索引减少回表次数来提升性能。首先通过子查询利用覆盖索引快速定位到偏移量的位置，再关联原表获取完整数据。
还是以user表为例，假设我们需要按创建时间create_time排序分页，且create_time有索引：
-- 覆盖索引+子查询优化分页
SELECT u.* FROM user u
INNER JOIN (
SELECT id FROM user ORDER BY create_time ASC LIMIT 10 OFFSET 100000
) tmp ON u.id = tmp.id;
子查询中的SELECT id FROM user ORDER BY create_time ASC可以利用覆盖索引，只扫描索引树上的id和create_time字段，不需要回表查询完整数据，扫描完成后通过id关联原表获取需要的字段，相比直接SELECT *可以减少大量的IO消耗。
优化方案三：限制最大分页深度
从业务层面也可以做优化，很多场景下用户并不会翻到非常靠后的页码，因此可以限制分页的最大深度，比如只允许查询前1000页，超过这个范围的请求直接返回空或者提示。这种方式可以从源头上避免大OFFSET的查询出现，适合对分页深度没有强需求的业务场景。
不同优化方案对比
以下是几种常见分页方案的适用场景和优缺点对比：
| 分页方案 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 传统OFFSET分页 | 偏移量小、分页深度浅的场景 | 实现简单，支持跳页查询 | OFFSET大时性能差 |
| 游标分页 | 排序字段唯一有序、不需要跳页的场景 | 性能稳定，不受偏移量影响 | 不支持跳页，依赖有序唯一字段 |
| 覆盖索引+子查询 | 必须支持OFFSET、排序字段有索引的场景 | 比传统OFFSET性能更好，支持跳页 | 优化效果有限，偏移量极大时仍有性能问题 |
| 限制最大分页深度 | 用户不需要翻到深层页码的场景 | 从业务层规避问题，实现简单 | 无法满足深层分页的业务需求 |
注意事项
- 使用游标分页时，排序字段必须保证唯一性，否则可能出现数据重复或者遗漏的问题，如果有多个排序字段，需要把所有字段都加入查询条件中。
- 覆盖索引优化时，要确保子查询中查询的字段都包含在索引中，否则无法触发覆盖索引，优化效果会失效。
- 分页查询时尽量避免使用
SELECT *，只查询需要的字段，可以减少数据传输和数据库IO的消耗，提升查询效率。