游标式分页通过利用上一页最后记录的唯一标识（如主键或时间戳）作为查询起点，结合索引实现高效分页。传统OFFSET分页需扫描并跳过大量数据，导致性能随页码加深急剧下降；而游标式分页使用WHERE条件直接定位起始位置，避免全表扫描和行跳过，性能稳定且与页码深度无关。配合单列、复合及覆盖索引，可进一步提升查询效率，尤其适用于大数据量下的“下一页”场景。
处理SQL中的大数据量分页，核心在于理解传统
OFFSET方法的局限性，并积极转向基于索引的“游标式分页”（Keyset Pagination），这能有效避免随着页码深入而导致的性能急剧下降。简单来说，就是不让数据库从头开始数数跳过海量行，而是告诉它“从某个已知点之后开始找”。
解决方案
当我们面对千万级甚至亿级的数据表时，传统的
LIMIT X OFFSET Y分页方式几乎是性能杀手。数据库在处理
OFFSET Y时，即便你只想要10条数据，它也可能不得不扫描并跳过前面的Y条记录。这个“跳过”操作并不是免费的，它会消耗大量的I/O和CPU资源，尤其当Y值变得非常大时，查询时间会呈线性甚至指数级增长。
解决之道在于放弃这种“跳过N条”的思维，转而采用“从哪里开始”的策略。这通常被称为“游标式分页”或“Keyset Pagination”。其基本思想是：你不是指定页码，而是指定上一页最后一条记录的某个唯一标识（比如主键ID或一个唯一的时间戳），然后查询“所有ID大于这个标识的N条记录”。
举个例子，假设我们有一个
products表，主键是
id。 传统慢查询：
SELECT * FROM products ORDER BY id ASC LIMIT 10 OFFSET 1000000;
这条查询要找到第100万页后的10条数据，数据库会先排序，然后跳过100万行。想想都觉得慢。
游标式分页： 假设上一页最后一条记录的
id是
999999。
SELECT * FROM products WHERE id > 999999 ORDER BY id ASC LIMIT 10;
这条查询会直接利用
id上的索引，快速定位到
id > 999999的起始位置，然后只读取接下来的10条数据。它的性能几乎与读取第一页数据一样快，因为它不再需要扫描和跳过前面的海量数据。
这种方式的优点显而易见：性能稳定，与页码深度无关。但它也有一个限制：它通常只支持“下一页/上一页”的导航，而不支持直接跳转到任意页码。不过，在大多数面向用户的场景中，无限滚动或“加载更多”的体验，比直接跳转到第500页更常见也更友好。
为什么传统的 OFFSET
分页在大数据量下会变得异常缓慢？
我记得有一次，我们系统上线后，用户反馈列表加载越来越慢，一开始还没在意，以为是网络问题。后来通过监控才发现，数据库的CPU和I/O负载随着用户浏览深度的增加而飙升。深入一查，罪魁祸首就是那个看似无害的
OFFSET。
OFFSET之所以慢，核心原因在于数据库的工作方式。当你执行
SELECT * FROM table ORDER BY column LIMIT N OFFSET M时，数据库并不能直接跳到第M行。它必须：
-
执行排序：如果
ORDER BY
的列没有合适的索引，或者索引不完整，数据库可能需要对整个数据集进行一次全表扫描并排序。这本身就是个昂贵的操作。 - 扫描并丢弃：即使有索引辅助排序，数据库也需要从头开始，逐条扫描前M条记录，然后把它们“丢弃”掉，直到达到M的位置。只有这样，它才能开始收集你真正想要的N条记录。
想象一下你在一个巨大的图书馆里找一本书，书架是按编号排列的。
OFFSET 1000000 LIMIT 10就像是让你从头开始数，数到第100万本之后，再从那里拿10本。你必须一本本地翻阅，即使你并不想要前面那100万本。这个过程，随着M的增大，开销是线性增长的，甚至可能因为内存不足导致磁盘I/O而变得更糟。对于一个高并发的系统来说，这种操作很快就能把数据库拖垮。
基于游标（Keyset Pagination）如何彻底改变分页性能？
游标式分页，或者叫“键集分页”，在我看来，是处理大数据量分页时最优雅的解决方案。它彻底改变了数据库的思考模式，从“跳过多少行”变成了“从某个已知点继续”。
它的基本原理是利用上一页的最后一条记录的唯一标识（通常是主键ID或一个唯一且可排序的列，比如
created_at时间戳），作为下一页查询的起点。
例如，我们有一个用户列表，按
id升序排列： 第一页：
SELECT id, name, created_at FROM users ORDER BY id ASC LIMIT 20;
假设第一页最后一条记录的
id是
20。 第二页：
SELECT id, name, created_at FROM users WHERE id > 20 ORDER BY id ASC LIMIT 20;
假设第二页最后一条记录的
id是
40。 第三页：
SELECT id, name, created_at FROM users WHERE id > 40 ORDER BY id ASC LIMIT 20;
这里的
WHERE id > X子句至关重要。如果
id列上有索引（通常主键都会自动创建索引），数据库可以直接通过索引树定位到
id大于
X的第一条记录，然后顺序读取接下来的20条。这个过程的效率极高，因为它避免了全表扫描和大量的行跳过操作。无论你翻到第几页，查询的开销都大致相同，因为它总是从一个已知的索引点开始查找。
如果排序的列不是主键，比如按
created_at时间戳排序，并且
created_at可能不唯一，那么我们需要一个“tie-breaker”来确保唯一性。例如：
-- 假设上一页最后一条记录是 (created_at = '2023-01-01 10:00:00', id = 123) SELECT id, name, created_at FROM users WHERE (created_at > '2023-01-01 10:00:00') OR (created_at = '2023-01-01 10:00:00' AND id > 123) ORDER BY created_at ASC, id ASC LIMIT 20;
这确保了即使
created_at相同，也能通过
id继续唯一地排序和分页。这种方式极大地提升了大数据量分页的响应速度和稳定性。
哪些索引策略能真正优化大数据量分页查询？
索引是数据库性能优化的基石，对于大数据量分页来说，更是如此。没有合适的索引，再巧妙的分页逻辑也可能事倍功半。
-
单列索引（Single-Column Index）： 这是最基础也是最重要的。如果你的分页查询是基于某个单一列进行排序（
ORDER BY
）和过滤（WHERE
），那么在这个列上建立索引是首要任务。 例如，如果你的查询是ORDER BY id ASC
，那么id
列上的主键索引已经足够。 如果你的查询是ORDER BY created_at DESC
，那么在created_at
列上建立索引：CREATE INDEX idx_users_created_at ON users (created_at DESC);
这能让数据库快速定位到排序的起点，尤其是在游标式分页中，
WHERE created_at > '...'
可以直接利用这个索引。 -
复合索引（Composite Index）： 当你的分页查询涉及多个排序条件，或者
WHERE
子句与ORDER BY
子句都包含多个列时，复合索引就显得尤为重要。它能让数据库避免额外的排序操作。 例如，你可能需要按category_id
过滤，然后按created_at
排序：SELECT * FROM products WHERE category_id = 5 ORDER BY created_at DESC LIMIT 10 OFFSET 1000; -- 传统 -- 或者，游标式： SELECT * FROM products WHERE category_id = 5 AND created_at < '2023-01-01 12:00:00' ORDER BY created_at DESC LIMIT 10;
这时，一个在
(category_id, created_at)
上的复合索引将是理想选择：CREATE INDEX idx_products_category_created_at ON products (category_id, created_at DESC);
索引的列顺序很重要，通常将用于过滤的列放在前面，然后是用于排序的列。
-
覆盖索引（Covering Index）： 这是更高级的优化手段。一个覆盖索引是指，查询所需的所有列（包括
SELECT
列表中的列、WHERE
子句中的列和ORDER BY
子句中的列）都包含在索引中。这意味着数据库可以直接从索引中获取所有需要的数据，而无需回表（即无需再访问实际的数据行）。回表操作是昂贵的，因为它通常意味着随机I/O。 例如，如果你的查询是SELECT id, name, created_at FROM users WHERE id > X ORDER BY id ASC LIMIT 20;
而你的索引是(id, name, created_at)
：CREATE INDEX idx_users_id_name_created_at ON users (id, name, created_at);
那么，这个查询就可以完全通过索引来满足，性能会达到极致。当然，覆盖索引会增加索引的大小和写入开销，所以需要权衡。
在实际操作中，我发现很多人在遇到性能问题时，往往倾向于先调整代码逻辑，但很多时候，一个设计得当的索引就能解决大部分问题。合理利用这些索引策略，能让你的分页查询在面对海量数据时依然保持敏捷。
