解决Mysql深分页问题

深分页时发生在生产环境中数据量比较大的情况下，但是这个数据量又不至于动用大数据的工具一类，数据都存储在mysql里，就可能会有深分页的问题， 比如你查limit 0 ，10没啥问题，假如数据量很大页数很多， 当 limit 100000，10时，执行速度就会慢很多那么如何解决呢？

解析

举一个简单的例子：

select id,name from red_packet_detail where create_time> '2022-06-14 00:00' limit 0,10;

select id,name from red_packet_detail where create_time> '2022-06-14 00:00' limit 100000,10;

1. 假设create_time是一个二级索引，我们先要找到所有满足记录的条件，拿到了非聚集索引上面记录的主键id。
2. 到聚集索引进行回表，查询数据。
3. 扫描我们要查询的数据，从0开始扫描，扫描到100010，抛弃前100000条。

慢的原因：

• 因为limit要扫描十万十条数据，并且进行丢弃
• 扫描更多数据也意味着回表的数据更多

解决方案

  1.基于主键自增

SELECT * FROM red_packet_detail d WHERE d.id > =（select id from red_packet_detail where  d.create_time>'2022-06-14 00:00' limit 100000,1 ) limit 10 

  2.limit 深分页问题的本质原因就是：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。

其实我们可以采用标签记录法，就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

SELECT * FROM red_packet_detail d WHERE d.id > #{maxId} AND d.create_time>'2022-06-14 00:00' ORDER BY d.create_time LIMIT 10;同样也是分页，但是有个maxId的限制条件，这个是什么意思呢，maxId就是上一页中的最大主键Id。所以采用此方式的前提：1）主键必须自增不能是UUID并且前端除了传基本分页参数pageNo,pageSize外，还必须把每次上一页的最大Id带过来，2）该方式不支持随机跳页，也就是说只能上下翻页。如下图所示是某知名电商中的实际页面。（注意order by和limit一起使用时的大坑，如果createtime列存在相同数据，数据相同的会随机顺序返回，如果需要使用，请在order by中加入附加列）

2.二是通过Elastic Search搜索引擎优化（基于倒排索引），实际上类似于淘宝这样的电商基本上都是把所有商品放进ES搜索引擎里的（那么海量的数据，放进MySQL是不可能的，放进Redis也不现实）。但即使用了ES搜索引擎，也还是有可能发生深度分页的问题的，这时怎么办呢？答案是通过游标scroll。

from,size浅分页

因为es是基于分片的，假设有5个分片，from=100，size=10。则会根据排序规则从5个分片中各取回100条数据数据，然后汇总成500条数据后选择最后面的10条数据。

scroll 深分页

from+size查询在10000-50000条数据（1000到5000页）以内的时候还是可以的，但是如果数据过多的话，就会出现深分页问题。

为了解决上面的问题，elasticsearch提出了一个scroll滚动的方式。
scroll 类似于sql中的cursor，使用scroll，每次只能获取一页的内容，然后会返回一个scroll_id。根据返回的这个scroll_id可以不断地获取下一页的内容，所以scroll并不适用于有跳页的情景。

 search_after 深分页

scroll 的方式，官方的建议不用于实时的请求（一般用于数据导出），因为每一个 scroll_id 不仅会占用大量的资源，而且会生成历史快照，对于数据的变更不会反映到快照上。

search_after 分页的方式是根据上一页的最后一条数据来确定下一页的位置，同时在分页请求的过程中，如果有索引数据的增删改查，这些变更也会实时的反映到游标上。但是需要注意，因为每一页的数据依赖于上一页最后一条数据，所以无法跳页请求。

为了找到每一页最后一条数据，每个文档必须有一个全局唯一值，官方推荐使用 _uid 作为全局唯一值，其实使用业务层的 id 也可以。