SQL｜使用游标分页优化深分页

业务场景：需要将有 500 万条记录的表 my_table 中的 content 字段取出。这显然需要分页取出，我们规定每一页取出 1000 条记录。

LIMIT ... OFFSET ... 语法

SELECT content FROM my_table LIMIT 1000000, 1000;

因为 B+ 树的非叶子节点中不存储节点的记录数，所以 MySQL 并不能准确地知道 1000001 条记录存在于那个叶子节点中。因此，MySQL 为了找到第 1000001 条记录，需要将前 1000000 条记录所在的所有 B+ 树的叶子结点都遍历一遍。然后再从第 1000001 条记录开始向后继续遍历 B+ 树的叶子节点，直至取出 1000 条并返回，随着记录数的增大，这个过程显然是极其缓慢的。

其时间复杂度为 $O(n+m)$，其中 $n$ 为获取的第一条记录的偏移量，$m$ 为需要获取的记录数。

游标分页方法

面对这种情况，我们可以考虑使用游标分页。

在第一次请求时，我们获取前 1000 条记录：

SELECT id, content FROM my_table LIMIT 1000;

在请求之后，我们记录下自增主键 id 的最大值（且后续每次请求都记录 id 的最大值）；再下一次请求时，我们使用自增主键 id 的最大值来过滤数据（例如当上一次请求的 id 最大值为 1000000 时）：

SELECT id, content FROM my_table WHERE id > 1000000 LIMIT 1000;

此时，MySQL 需要找到的不再是第 1000001 条记录，而是 id 大于 1000000 的第 1 条记录。因为 B+ 树的非叶子节点都包含数据索引，所以我们只需要从根节点开始向下走到叶子节点，即可找到 id 大于 1000000 的第 1 条记录，然后从该条记录开始向后继续遍历 B+ 树的叶子节点，直至取出 1000 条并返回即可。

其时间复杂度为 $O(\log n+m)$，其中 $n$ 为获取的第一条记录的偏移量，$m$ 为需要获取的记录数。

需要注意的是，当我们使用游标分页的方法时，无法直接获取到指定页数，而是必须从前往后逐页遍历。这与 ES 的 scroll 是类似的。