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

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

1 目前大多数的解决方案

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

2 APP上的下拉式分页

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

分表1分表2分表3分表4分表5
32041

将该数组 和 请求页数信息 存储到用户相关的缓存下。
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查询相应的记录列表。

字段名称字段类型字段描述
IdInt主键
order_typetinyint排序类型
record_id_listText有序记录id列表

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

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

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

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

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

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐