游标分页的解决方案思考
背景
Mysql查询中，传统的分页方式是客户端传入pagesize、pageno，计算出offset偏移量，然后使用Limit分页查询。
但是在大数据量下，会产生性能问题。
具体原理就涉及到存储引擎对sql的执行，首先需要定位到offset的位置，也就是分页起始位置，这个过程可以想象成遍历到offset的位置，当offset变得很大，那么这个过程的时间就会变成，从而产生深度分页的性能问题。
基于这个背景下，我们来探讨，如何解决深度分页导致的慢查询这个问题。
例如：
SELECT * FROM table LIMIT 1000000,100;
第一百万条数据，后100条，为结果集，那么如果有一个自增id=1000000那么sql可以改成：
SELECT * FROM table id >1000000 LIMIT 100;
这样返回的结果集是一样的，并且id默认是有主键索引的，通过索引查找定位到分页的起始位置，效率大大高于limit offset。
首先要知道游标查询的局限性，它是不支持传统的直接定位到某一页的，只能一页一页的向下查询，类似淘宝搜索到了商品，只能向下滚动查看更多商品的这样的场景。
为什么采用滚动的？因为超大数据量下，直接找到指定页已经没有什么意义了，用户检索商品，一般都以条件进行检索，及时支持跳到指定页，就能确定这些东西能符合用户的需要吗？
场景一
我们知道，limit offset是将筛选出来的结果集，进行偏移量定位，然后找到偏移位置的后pagesize条数据，那么，在多条件或者复杂条件的查询下，使用游标查找，可能带来很多问题。
比如我们要按照某一字段进行排序，这里取创建时间（crete_time）为排序字段，那么sql应该为：
SELECT * FROM table ORDER BY crete_time LIMIT 1000000,100;
这样的话，转换成游标查询该怎么查呢？因为按照更新时间排序会打乱id的顺序，所以，我们不能再用id作为游标了，我们直接采用update_time来当做游标，sql可以写成如下：
假设我们滚动到了这一页，并且拿到了时间游标crete_time，假设为2022-09-19 00:00:00，游标一般只会是，上一页数据的最后一条数据的某一个字段。
SELECT * FROM table update_time > 2022-09-19 00:00:00 ORDER BY crete_time LIMIT 100;
这两条sql的效果是一样的。
那问题又来了，更新时间它可能出现多个2022-09-21 00:00:00，这个场景可能比较抽象，下面用一张图来解释：
首先，上一页的最后一条记录的更新时间将会作为游标。
但是下一页，也就是id=7这条记录，如果按照我们上述的sql，也就是：
SELECT * FROM table update_time > 2022-09-21 00:00:00 ORDER BY DESC crete_time LIMIT 100;
这样的话，id=7这条记录，就被排除出去了，本来id=1这条记录不应该被排除的，应当作为下一页的数据结果集。
如何解决这个问题呢？
问题的关键在于，我们要顺着上一次的游标往下顺序地寻找结果集。
那我们这样子是否可行呢：
SELECT * FROM table update_time >= 2022-09-21 00:00:00 ORDER BY DESC crete_time LIMIT 100;
当然是不可以的，这样查询 会把上一页的id=1，id=6的两条数据一起查出来，这样就不正确了，也就是如下结果集
那我们只能继续修改我们的sql了，现在要解决的问题是，如何让上一页，create_time与游标相同的不要在当前页出现？
此时，我们就需要让id，加入进来，共同地组成我们的游标，注意，游标不一定只有一个，也可以是几个字段共同组成的条件
一般来说，表设计会有一个自增id，自增id可以从0开始自增，可以通过雪花算法自增，这是游标查询的先决条件。
这里有两种解决办法：
1、利用非自增的user_id，注意，这里的user_id不是自增的，但是是唯一的。
但是它依旧有个特点，那就是可排序。
所以我们可以将相同的create_time的进行排序，并作如下改动。我们现在的锚点是user_id = 6，create_time = 2022-09-21 00:00:00
SELECT
*
FROM
`user`
WHERE
create_time > '2022-09-21 00:00:00'
//这个条件就是为了筛选掉create_time相同的【上一页】的数据
OR ( user_id > 6 AND create_time = '2022-09-21 00:00:00' )
ORDER BY
create_time ASC,
user_id ASC
LIMIT 100
2、假如我们有一个自增的主键id，那就更好做了！
首先分析，自增主键，那必然是和create_time的顺序相同，这属于特殊情况，因为我们的游标不一定是create_time，也有可能是update_time。
那当前情况，游标可以更换为id，那对应的id游标就是id = 2，那sql如下改写：
SELECT
*
FROM
`user`
WHERE
id > 2
ORDER BY id
LIMIT 100
这样就能实现与上面的sql同样的效果。
当然，这种情况，只能因业务条件而定，若业务需要根据update_time排序，那么就不能这样写，因为id与update_time没有顺序关联了，依旧要使用第一种情况的sql查询。
场景二
在某些情况下，筛选条件复杂，无法使用sql达到目的的场景下进行查询。
例如，我查出了100条数据，我需要在代码层面，剔除一些不符合条件的数据，假如此时这100条数据就剩下50条了。
但是万恶的客户端需要我们返回100条有效数据，那怎么办？难办？那就都别办了！
言归正传。
我得出的解决办法就是使用wihle查询（mysql）。
具体伪代码是这样：
List<Object> result = List.of();
while(){
//查询100条
List<Object> currResult = doSelectList();
//剔除无效数据
doFilter();
//判断是否达到100条
if(result.size() > 100){
break;
}
//如果下一页没数据了，则说明到最后一页了。
if(currResult <= 0){
break;
}
}
其中的doSelectList() 采用的是游标查询
while操作是比较危险的操作，如果break条件是基于数据驱动，存在脏数据的情况下，很可能无法结束循环，导致stackOverFlow，所以这个操作需要慎用，需要有一个兜底的结束条件，例如循环了n次依旧凑不齐100条，那就直接返回。
补充
2022.11.08更新
想起前后端这种查询场景交互问题，所以在这里补充一些内容。
既然引入了游标查询的场景，那么我们就要更好地利用上这种特性。
向上查询
游标是利用当前id+pageSize作为一整页的数据，如果Id是自增的，那么我们也可以向上查询
假设当前已经中途查到10000id这个数据了，我们想向前查询。为什么要向前查询？前面的数据不是已经查询出来了吗？
但是你是否有想过，客户端要把这一万条信息都存下来吗？
或许客户端能看到的信息是一个滑动窗口，只保存以当前信息为基准，向上的100条数据和，这里只是打个比方，可能是向上的1000条，视情况而定。
那么，我现在想要返回找向上的101条，那是不是就要再次触发查询呢？
对！游标查询就派上用场了。
例如：
SELECT FROM TABLE WHERE ID <= 10000 limit 100;
在游标
Elastic Search 滚动查询
es，作为分布式大数据的数据库，自然兼容了游标查询，只不过es将其成为滚动查询。
具体原理是在查询的时候生成一个数据快照，并有一定的快照存活时间（由用户自定义），但有最大限制。每一个快照都有存活时间，当存活时间到期，快照自动清除。
在实际开发中，一次滚动查询会返回一个scrollerId，这个id就相当于我们在Mysql里面自定义出来的游标。
只不过es更加直接，给符合条件的结果集的每一行数据都生成了Id，然后以当前结果集的最后一条数据的id作为本次查询返回的scrollerId，当下一次查询的时候，通过这个游标继续向下索引数据并返回，一直"滚动"下去。
但是这种也存在一定的的问题，假如说存活时间过长，许多快照在es中滞留，此时并发较高，那么内存占用自然也会提高。
所以我们需要控制这个快照时间，需要与线程生命周期一致。
所以，我们需要在查询结束后，手动清除所有的快照。
注意，并不是一次查询一个快照，而是一个scrollerId一个快照，所以在elasticSearchReatTemplates中提供了批量删除滚动快照的api。
