分库分表下分页查询解决方案
分库分表下分页查询解决方案
不管是随着业务量的增大、还是随着用户数量的增长，在单一表中无法承受大量大数据，导致查询速度极慢甚至拖垮数据库。所以分库分表的策略随之应用，但是如何在分库分表的情况下，进行分页查询，目前仍是业界难题。
本文记录了三种情况下，对于分库分表下的分页查询优化方案。
1 目前大多数的解决方案
不管是目前的一些数据库中间件例如Mycat，还是ElasticSearch下的分片查询，大多都使用了最简单的策略去实现不同存储下的全局有序查询，即在每次分页查询时，查询所有存储下相应的条数，汇总排序得到最终要展示的分页下的数量。
这种策略实现方案简单、精度高，但是随着查询页数的增长，不管是内存的占用还是查询速度都会极具上升。因此，如果要采取这种方案，要考虑到对于查询页数的限制，防止影响应用的运行。
2 APP上的下拉式分页
在APP上的分页查询，多数是采用了下拉式分页（向下拖动，会出现新的一页）。在这种情况下，用户无法自由的选取要查询的页数，因此可以采用以下方式来优化分页的性能。
举个简单的例子，查询需要涉及到五个ES的索引，每页分页数量是10。
- 当用户请求第一页数据时，从五个索引中分别查询前十条。
- 分别记录五个索引中的数据截止位置，例如
| 索引1 | 索引2 | 索引3 | 索引4 | 索引5 |
|---|---|---|---|---|
| 3 | 2 | 0 | 4 | 1 |
用户请求下一页数据时，先查询上一页的用户分页缓存，从缓存中获取到上一页的截止位置，3、2、0、4、1。从对应索引的截止位置当起始位置，分页查询十条，记录当前截止位置，更新缓存。
用户请求的页数为第一页时，清除缓存，重复1-4步骤。
该策略不会出现当页数过多时，应用负载过高的情况，但是前提条件为用户不能跨页查询。
注意点：再补充两点，使用该策略时，有两个需要注意的地方，
- 我具体写代码的时候，截止位置的索引是放到redis中的，并且设置了超时时间十分钟。这样会导致一个问题，假如用户下拉到第三页，然后过了十分钟继续下拉，会出现缓存失效，查询结果自然会出错。这个问题想不出好的解决方法，最后只能是加大了缓存失效时间。
- 用户下拉过快，会导致请求第N页和第N+1页的请求是同时发出的，但截止位置的缓存是一样的，自然返回的结果是一样的。解决方案：客户端要做限制，不能同时请求多页数据，后端设置缓存时，把page的信息设置到key中，收到请求page=N的请求时，去获取N-1的缓存，如果缓存没有，阻塞一秒，等待前一页的缓存生效，再继续操作。
3 普通的分页查询
在一些后台管理页面中，分页查询是要能支持跨页查询的，这样就无法使用上一种的策略了，但是还可以想办法做出一些优化。
3.1 查询精度要求很低
如果对查询精度没有要求的话，可以使用一种简单的策略，还是拿上面的例子还说明：
查询需要涉及到五个ES的索引，每页分页数量是10。
那么第一页查询分别查询五个索引的0-10条数据再排序返回前十条。
第二页查询分别查询五个索引的10-20条数据再排序返回前十条。
…
简单、性能高，但是问题也是显而易见的，查询的精度很低，可能会漏掉许多数据，但是每页数据无重复。
3.2 查询精度一般-二次查询
网上广为流传的二次查询策略，该策略的数据精度比上一种方案有了明显的提高。具体内容如下
条件：查询需要涉及到五个ES的索引，每页分页数量是10。
- 求得每个索引的查询条数actualPageSize，用每页查询的数量 整除 索引数量。
即actualPageSize = pageSize / indexSize（向上取整），比如在本例中，pageSize=10,indexSize=5，那么actualPageSize=2 - 查询每个索引下相应页数的actualPageSize条，
即 limit page * actualPageSize,actualPageSize - 汇总所有索引的查询，排序得到最小的记录的标识符minId（如果主键是自增就是id，主键不是自增就是创建时间cmt_created)，并记录每个索引各自的最大的记录的标识符maxId。注意，minId只有一个，maxId每个索引都有自己的一个。
- 在每个索引中，使用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 分页查询扩展知识
有很多人向我反馈，用你这个分页查询的策略，当数据发生动态的增加或者删除时，会有问题啊！
我只想说，就算你不是用分表的分页策略，用最普通的分页查询，当数据发生变化时，也是会出问题的好吧。。
这其实跟我本篇文章讲解的东西没有关联的，本篇文章讲的是分表条件下怎么提升分页查询性能，至于数据的动态变化导致分页出错那是分页查询下的问题，但是这么多人问了，我就把解决方案也写一下吧。
场景：当用户使用分页查询浏览第N页的数据时（N不等于第一页），如果这时候，数据新增了一条或者删除了一条，程序中分页查询的条件page和offset是没有随着相应变化的。
当数据新增一条（一般来说，新增的数据是放在最前面的）
那么N+1页的数据就会出现第N页的数据的最后一条，也就是数据会重复出现。
当用户查询过的数据减少了一条
N+1页的数据倒是不会重复出现，只是会漏掉，因为N+1页的第一条数据已经填充到上一页去了。
解决方案我目前有两种
- 如果查询的场景为推荐之类的，在这种场景下，数据理论上只会新增而不会被删掉（被删掉的都排在很后面），那么我们在查询的时候可以增加一个条件把新增的数据过滤掉就好了，比如说用户第一次查询的时间为T，那么增加条件create_time < T即可。
- 分页的场景为分页列表可以动态操作的，比方说我之前做过的直播间商品列表，主播可以使用该列表进行商品的上下架处理，也就是会动态改变数据。这种场景下，排序条件一般都不复杂，可能只是根据时间倒排就行。我们在查询的时候就不能使用传统的limit offset,page查询了，而要改变策略，使用客户端已经查询过的总条数当offset，比如客户端已经请求了三十条数据，他删除了一条，也就是说他目前有29条数据，我们查询的时候使用29这个数字当offset查询即可。
