后端开发-分页游标设计（解决大数据量分页查询时的性能问题）

适用场景/背景描述

• 如果需要做分页查询
• 且被查询的数据量很大，直接 limit offset 有可能会导致全表扫描或扫描前 n 项
  • 如果你用查询的字段不包含索引字段，那么会发生全表扫描
  • 如果你查询的字段包括索引字段，你仍然要扫描索引字段的前 n 项
• 且客户端侧仅支持每次到下一页的查找，不支持跳转查找第几页
• 且 order by 排序是以索引来排序

如果你遇到的是上方描述的场景，那么分页游标就是你很好的选择！

本质原理

一句话：原本的常规的分页查找，思路转变成利用索引条件和 limit 查找多少个数量来替代，来避免 offset 的性能瓶颈

简单举例子

如果你使用 limit offset：
比如你要一页一页的分页查找用户表数据，按照每页 10 条来查找，你查找第一页，能很快找到，因为 id 为主键索引，依据 B+ 树的二分查找特性

select * from t_user order by id limit 10;

…你一页一页的查找，然后你查找后头第 4 页，取 10 条数据，但是在 B+ 树二分查找的前提下，你还需要扫描前面 3 页的共 30 条数据才能定位到起始位置

select * from t_user order by id limit 10 offset 30

如果你使用分页游标：
如果使用一页一页的查找数据，按照每页 10 条查找，你查找第一页，因为 id 是主键索引你能快速查找到，然后后端多返回查找到的最后一条数据的这个索引字段 id（要求这个索引需要唯一）

select * from t_user order by id limit 10;

…你一页一页的查找，然后你查找后头第 4 页，取 10 条数据，你就可以利用上一页查找时候返回的索引 id（假如是 30）来快速查找了，这时候你可以直接定位到 id > 30 这个起始位置

select * from t_user where id > 30 order by id limit 10;

这里区别就出来，当你一页页查找，查找到后头的页数时

• 使用 limit offset，你每页查找时候，都需要把前面的页面的数据给扫一遍
• 使用分页游标时候，你每页查找时候，每页查找都能快速定位，无需把前面页面的数据给扫一遍

分页游标，若用非唯一索引的字段排序

比如使用非唯一索引的字段 age 来排序呢？那么用分页游标时候我不能简单的 age > 30 来 limit 了，因为 age 有很多等于 30 的，但是你可以依附于另一个唯一的索引字段来弄。因此你需要传 2 个游标给客户端

-- 假设上一页的游标是 age = 30，id = 100，id 是主键索引，age 不是索引，age 不唯一
select * from t_user where (age = 30 and id > 100) or age > 30 order by age, id limit 10

分页游标，若 order by 的不是索引还能使用分页游标吗

一般来说 order by 的不是索引字段，或者这个字段不能添加索引，那么不建议分页游标来做，如果非要用分页游标，也需要你贴紧一个有唯一索引的字段来做，但即使是这样，首页查询也会全表扫描，后续页查询时因为会先执行 where 中的语句能缩减一点点范围

因为当你使用分页游标时，第一页查询就直接进行了全表扫描了，因为你会 order by 一个非索引字段

select * from t_user order by name, id limit 10;

后续页，假设上一页最后一条记录 id 是 100

select * from t_user where id > 100 order name, id limit 10;