MySQL深度分页优化

Mysql深度分页优化

1. 背景

Mysql使用select * from table limit offset, rows分页在深度分页的情况下, 性能急剧下降。

• 例如：select * 的情况下直接⽤limit 600000，10 扫描的是约60万条数据，并且是需要回表 60W次，也就是说⼤部分性能都耗在随机访问上，到头来只⽤到10条数据(总共取600010条数据只留10条记录)

2. 优化

2.1 业务层面限制跨度比较大的跳页

百度和Google方案:

这样的话用户需要点很多次才能访问到页码比较大的数据，通过提高用户操作成本，降低了深分页请求数量。

2.2 记录每次取出的最大id， 然后where id > 最大id

select * from table_name where id > 最大id limit 10000, 10

这种方法适用于主键ID自增的情况，分布式ID则不行。需要考虑连续型字段datetime，sequence 等。
实现步骤：每一次查询把本批数据的最大ID传给前端，查询下一页的时候再带到后台。

2.3 通过in获取id

select * from table_name where id in (select id from table_name where userId = xxx  limit 10000, 10)

子查询先根据where条件分页查出一页id, 然后外层查询只需要回表查询返回这一页的数据行。大大降低了无效的数据回表次数。

2.4 上es

2.4.1 from,size浅分页

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

2.4.2 scroll 深分页

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

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

2.4.3 search_after 深分页

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

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

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

本文内容合并了以下两篇文章，留作笔记。
详细请参考：

1. Mysql使用limit深度分页优化
2. Elasticsearch 三种分页方式