背景
如何设计出符合互联网需求的分页查询，刚刚来到互联网公司菜鸟，一个查询竟然让我束手无策了。之前天天做传统业务的增删改查，只要实现功能就行，没有考虑过系统性能问题，我的老大让我不要用limit offset ,size 方式，我想了一下之前我都是用这种方式的，让我用ID进行查询，然后给我上了一课，此处省略若干字。。。。。。
方案设计
接口设计
请求字段：
- id ：默认值未0，如果查询下一页传最大的ID过来，如果查询上一页查询最小的
- type: 查询上一页给0，下一页给1，默认给1
- size:每页查询多少条
相应字段：
- hasPre:是否有上一页
- hasNext:是否有下一页
- List<T> dataList:数据集合
流程设计
上述方案 VS limit offset,size
实现起来比较复杂，场景不是很通用，受排序的影响，如果情况比较复杂就不太好实现了，查询效率高，查询第1000页和查询第1页效率差不多。
offset这种方式，代码比较简单，可以使用mysbatis-puls 分页插件，通用性强。查询的页数越大，查询效率越慢。
问题原因
主要就是mysql可以看做分为两层，图片如下，service层和存储引擎层
select id,nam,age from user order by id limit 10000,20
这条sql理论上需要20条数据，但是存储引擎会给我们返回10000+20 条数据，service层再进行过滤，这极大的增加了IO操作。IO一直都是系统的瓶颈，减少IO操作是一个高性能应用必做的一个事。
MySQL架构图
limit 优化方案
以下内容来自《高性能mysql》第3版
在系统中需要进行分页操作的时候，我们通常会使用limit加上偏移量的办法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则，mysql需要做大量的文件排序操作。
一个非常常见又令人头痛的问题就是，在偏移量非常大的时候，例如可能是limit 10000,20 这样的查询，这是mysql需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。需要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。
优化此类分页查询的一个最简单的办法就是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后更加需要做一次关联操作再返回所需要的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询：
select film_id,descrpition from film order by title limit 50,5;
如果这个表非常大，那么这个查询最好改成下面的样子:
select film_id,description
from film
inner join (
select film_id from film
order by title limit 50,5
) as lim using(film_id);
这里的“延迟关联”将大大提升查询效率，它让mysql扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的limit子句。
有时候也可以将limit查询转换为已知位置的查询，让mysql通过范围扫描获得到对应的结果。列如，如果在要给位置列上有索引，并且预先计算出了边界值，上面的查询就可以改成为：
select film_id,description from film
where position between 50 and 54 order by position;
对数据进行排名的问题也与此类似，但往往还会同时和group by 混合使用。在这种情况下通常都需要预先计算并存储排名信息。
limit 和offset 的问题，其实是offset问题，它会导致mysql扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用offset。例如，若需要按照租借记录做翻页，那么可以根据最新的一条记录向后最朔，这种做法可行是因为租借记录的主键是单调递增的。首先使用下面的查询获得第一组结果：
select * from rental
order by retenl_id desc limit 20;
假设上面的查询返回的主键为16049到16030的租借记录，那么下一页查询就可以从16030这个点开始：
select * from rental
where rental_id<16030
order by rental_id desc limit 20;
该技术的好处就是无论翻页到多么后面，其性能都会很好。
总结
limit 优化有2种方案，offset 过大容易引起不必要的数据返回，代价很大，有子句查询和记录上一次位置的方案，根据应用场景选择。
