分库分表下分页查询解决方案

不管是随着业务量的增大、还是随着用户数量的增长，在单一表中无法承受大量大数据，导致查询速度极慢甚至拖垮数据库。所以分库分表的策略随之应用，但是如何在分库分表的情况下，进行分页查询，目前仍是业界难题。
本文记录了三种情况下，对于分库分表下的分页查询优化方案。

1 目前大多数的解决方案

不管是目前的一些数据库中间件例如Mycat，还是ElasticSearch下的分片查询，大多都使用了最简单的策略去实现不同存储下的全局有序查询，即在每次分页查询时，查询所有存储下相应的条数，汇总排序得到最终要展示的分页下的数量。
这种策略实现方案简单、精度高，但是随着查询页数的增长，不管是内存的占用还是查询速度都会极具上升。因此，如果要采取这种方案，要考虑到对于查询页数的限制，防止影响应用的运行。

2 APP上的下拉式分页

在APP上的分页查询，多数是采用了下拉式分页（向下拖动，会出现新的一页）。在这种情况下，用户无法自由的选取要查询的页数，因此可以采用以下方式来优化分页的性能。
举个简单的例子，查询需要涉及到五张分表，每页分页数量是10。
1.当用户请求第一页数据时，从五个分表中分别查询前十条。
2.分别记录五个索引中的数据截止位置，例如

分表1	分表2	分表3	分表4	分表5
3	2	0	4	1

将该数组 和 请求页数信息 存储到用户相关的缓存下。
3.用户请求下一页数据时，先查询用户分页缓存信息，从缓存中获取到请求页数 和 上一页的截止位置，3、2、0、4、1。从对应索引的截止位置当起始位置，分页查询十条。
4.用户请求的页数为第一页时，清除缓存，重复1-4步骤。

该策略不会出现当页数过多时，应用负载过高的情况，但是前提条件为用户不能跨页查询。 并且要注意控制一下单用户并发请求，防止设置错误的缓存信息导致用户请求到同一页数据。

3 普通的分页查询

在一些后台管理页面中，分页查询是要能支持跨页查询的，这样就无法使用上一种的策略了，但是还可以想办法做出一些优化。

3.1 查询精度要求很低

如果对查询精度没有要求的话，可以使用一种简单的策略，还是拿上面的例子还说明：
查询需要涉及到五张分表，每页分页数量是10。
那么第一页查询分别查询五个分表的0-10条数据再排序返回前十条。
第二页查询分别查询五个分表的10-20条数据再排序返回前十条。
…
简单、性能高，但是问题也是显而易见的，查询的精度很低，可能会漏掉许多数据，但是每页数据无重复。

3.2 查询精度一般-二次查询

网上广为流传的二次查询策略，该策略的数据精度比上一种方案有了明显的提高。具体内容如下
条件：查询需要涉及到五张分表，每页分页数量是10。

1. 求得每个索引的查询条数actualPageSize，用每页查询的数量 整除 分表总数量（5）。
   即actualPageSize = pageSize / indexSize（向上取整），比如在本例中，pageSize=10,indexSize=5，那么actualPageSize=2
2. 查询每张分表下相应页数的actualPageSize条，
   即 limit page * actualPageSize,actualPageSize
3. 汇总所有索引的查询，排序得到最小的记录的标识符minId（用你本身的排序字段排，比如说你目前的需求是根据创建时间倒序，就用创建时间)，并记录每个索引各自的最大的记录的标识符maxId。注意，minId只有一个，maxId每个索引都有自己的一个。
4. 在每个索引中，使用between minId,maxId 查询相应的结果后汇总排序，得到前十条记录返回。
   

该种策略查询数据的精度比较高（在数据为随机分配的情况下，精度可为百分百），且不会出现数据丢失，但在数据分布不均的情况会出现数据重复，即上一页的数据在下一页中还能看到。
像以下这种情况，其实排序就已经全乱掉了。

PS：为了继续提高查询精度，我又做出了改进点，比如，当请求前十页的数据时，使用策略1，获取前N页的所有数据，内存排序。请求十页以后，使用策略3.2。考虑点为前十页的数据是用户经常要访问的，不应该出现数据的丢失和重复，十页以后的数据基本上不会有人看，数据精度不准页没关系。

PS：阈值的设定，不能定的太草率，之前我设定的阈值是10，从第十页开始，使用不同的策略。也应该考虑一下具体每条数据的大小、内存排序时的总空间占用，用户的并发访问度等情况。目前阈值的设定为全局排序时的内存占用不能超过5M。

3.3 查询精度不允许损失-双写

我也不知道什么样的业务场景下有该需求啊，但是之前我的部门主管使用过一种策略，可以保证精度不丢失。

当当当当，就是双写

什么含义呢？就是当新增或者删除你要查询的数据列表时，同时向一张新表中修改所对应的值。

新表的主要表结构如下，主要作用是，记录了不同排序规则下，记录的排序情况。

这样在查询时，根据不同的排序条件，查找不同的排序类型，就得到了一个全局排序记录的id列表，从中截取你要查询的页数量后，再根据id查询相应的记录列表。

字段名称	字段类型	字段描述
Id	Int	主键
order_type	tinyint	排序类型
record_id_list	Text	有序记录id列表

限制条件呢有很多，第一，记录要符合读多写少的情况，不然双写的性能消耗太大。

第二，排序类型不能过多，我之前的需求是只有一种排序类型的。

第三，记录id列表中存储的记录不能过多，否则就得不偿失。

3.4 *使用更NB的数据库来查询（加星建议使用）

随着技术的不断发展，又诞生了一种新的解决方案。
比如说我mysql分库分表了，还要支持多种条件分页查询，那我就把数据同步到ES，用ES来查，一个ES索引如果硬件够强的话可以支持过亿数据查询的。
如果ES也撑不住了，那就放到ClickHouse里面查（QPS不高），因为过亿的数据还要做分页查询本来就是极特殊的业务场景了，所以使用频率一定不会高到那里去。