“跨库分页”方案

前提:表按照uid水平分为2张,其他情况类似推理

需求:通过字段time升序分页,实现 select * from T order by time offset X limit Y

1.全局视野法

假设现在要拿到第3页的数据,如下图所述,服务层通过uid取模将数据分布到两个库上去之后,每个数据库都失去了全局视野,数据按照time局部排序之后,不管哪个分库的第3页数据,都不一定是全局排序的第3页数据。

 那到底哪些数据才是全局排序的第3页数据呢,暂且分三种情况讨论。

(1)极端情况,两个库的数据完全一样

如果两个库的数据完全相同,只需要每个库offset一半,再取半页,就是最终想要的数据(如上图中粉色部分数据)。

(2)极端情况,结果数据来自一个库

也可能两个库的数据分布及其不均衡,例如db0的所有数据的time都大于db1的所有数据的time,则可能出现:一个库的第3页数据,就是全局排序后的第3页数据(如上图中粉色部分数据)。

 (3)一般情况,每个库数据各包含一部分

正常情况下,全局排序的第3页数据,每个库都会包含一部分(如上图中粉色部分数据)。

由于不清楚到底是哪种情况,所以必须每个库都返回3页数据,所得到的6页数据在服务层进行内存排序,得到数据全局视野,再取第3页数据,便能够得到想要的全局分页数据。

再总结一下这个方案的步骤:

(1)将order by time offset X limit Y,改写成order by time offset 0 limit X+Y

(2)服务层将改写后的SQL语句发往各个分库:即例子中的各取3页数据

(3)假设共分为N个库,服务层将得到N*(X+Y)条数据:即例子中的6页数据

(4)服务层对得到的N*(X+Y)条数据进行内存排序,内存排序后再取偏移量X后的Y条记录,就是全局视野所需的一页数据

方案缺点

(1)每个分库需要返回更多的数据,增大了网络传输量(耗网络);

(2)除了数据库按照time进行排序,服务层还需要进行二次排序,增大了服务层的计算量(耗CPU);

(3)最致命的,这个算法随着页码的增大,性能会急剧下降,这是因为SQL改写后每个分库要返回X+Y行数据:返回第3页,offset中的X=200;假如要返回第100页,offset中的X=9900,即每个分库要返回100页数据,数据量和排序量都将大增,性能平方级下降。

 

2.禁止跳页查询

在数据量很大,翻页数很多的时候,很多产品并不提供“直接跳到指定页面”的功能,而只提供“下一页”的功能,这一个小小的业务折衷,就能极大的降低技术方案的复杂度。

(1)跟“全局视野法”一样,拿出2个库的第1页数据,并进行内存排序,返回真正第1页的数据(如下图),并且,我们记录下2张表筛选完之后最后一条记录的time,记为 time-max

 

(2)拉取“下一页”数据时,改变sql,如:

a.将查询order by time offset 100 limit 100,改写成order by time where time>$time_max limit 100

b.这下不是返回2页数据了(“全局视野法,会改写成offset 0 limit 200”),每个分库还是返回一页数据(如上图中粉色部分);

c.将这些数据进行内存排序

 

3.允许数据精度损失

这里有个假设前提:使用patition key进行分库,在数据量较大,数据分布足够随机的情况下,各分库所有非patition key属性,在各个分库上的数据分布,统计概率情况是一致的。

在这个假设的前提下,我们可以认为,分页的数据,其实是均匀散布在各个库表上的,所以查询第3页的情况如下所示:

利用这一原理,要查询全局100页数据,offset 9900 limit 100改写为offset 4950 limit 50,每个分库偏移4950(一半),获取50条数据(半页),得到的数据集的并集,基本能够认为,是全局数据的offset 9900 limit 100的数据,当然,这一页数据的精度,并不是精准的。

根据实际业务经验,用户都要查询第100页网页、帖子、邮件的数据了,这一页数据的精准性损失,业务上往往是可以接受的,但此时技术方案的复杂度便大大降低了,既不需要返回更多的数据,也不需要进行服务内存排序了。

 

4.二次查询法(终极武器)

上面的集中技术方案里,均有缺点,有没有一种技术方案,即能够满足业务的精确需要,无需业务折衷,又高性能的方法呢?

为了方便举例,假设一页只有5条数据,user的信息分到了3张表上,查询第200页的SQL语句为select * from T order by time offset 1000 limit 5;

(1)查询改写

将select * from T order by time offset 1000 limit 5

改写为select * from T order by time offset 333 limit 5

并投递给所有的分库,注意,这个offset的333,来自于全局offset的总偏移量1000,除以水平切分数据库个数3。

假设这三个分库返回的数据(time, uid)如下:

(2)找到所返回3页数据的最小值

第一个库,5条数据的time最小值是1487501123

第二个库,5条数据的time最小值是1487501133

第三个库,5条数据的time最小值是1487501143

故,三页数据中,time最小值来自第一个库,time_min=1487501123,这个过程只需要比较各分库第一条数据,时间复杂度很低

(3)查询二次改写

第一次改写的SQL语句是select * from T order by time offset 333 limit 5

第二次要改写成一个between语句,between的起点是time_min,between的终点是原来每个分库各自返回数据的最大值:

第一个分库,第一次返回数据的最大值是1487501523

所以查询改写为select * from T order by time where time between time_min and 1487501523

第二个分库,第一次返回数据的最大值是1487501323

所以查询改写为select * from T order by time where time between time_min and 1487501323

第三个分库,第一次返回数据的最大值是1487501553

所以查询改写为select * from T order by time where time between time_min and 1487501553

相对第一次查询,第二次查询条件放宽了,故第二次查询会返回比第一次查询结果集更多的数据,假设这三个分库返回的数据(time, uid)如下:

可以看到:

由于time_min来自原来的分库一,所以分库一的返回结果集和第一次查询相同(所以其实这次访问是可以省略的);

分库二的结果集,比第一次多返回了1条数据,头部的1条记录(time最小的记录)是新的(上图中粉色记录);

分库三的结果集,比第一次多返回了2条数据,头部的2条记录(time最小的2条记录)是新的(上图中粉色记录);

(4)在每个结果集中虚拟一个time_min记录,找到time_min在全局的offset

在第一个库中,time_min在第一个库的offset是333

在第二个库中,(1487501133, uid_aa)的offset是333(根据第一次查询条件得出的),故虚拟time_min在第二个库的offset是331

在第三个库中,(1487501143, uid_aaa)的offset是333(根据第一次查询条件得出的),故虚拟time_min在第三个库的offset是330

综上,time_min在全局的offset是333+331+330=994

(5)既然得到了time_min在全局的offset,就相当于有了全局视野,根据第二次的结果集,就能够得到全局offset 1000 limit 5的记录

第二次查询在各个分库返回的结果集是有序的,又知道了time_min在全局的offset是994,一路排下来,容易知道全局offset 1000 limit 5的一页记录(上图中黄色记录)。

 

 

总结:

方法一:全局视野法

(1)将order by time offset X limit Y,改写成order by time offset 0 limit X+Y

(2)服务层对得到的N*(X+Y)条数据进行内存排序,内存排序后再取偏移量X后的Y条记录

这种方法随着翻页的进行,性能越来越低。

 

方法二:业务折衷法-禁止跳页查询

(1)用正常的方法取得第一页数据,并得到第一页记录的time_max

(2)每次翻页,将order by time offset X limit Y,改写成order by time where time>$time_max limit Y

以保证每次只返回一页数据,性能为常量。

 

方法三:业务折衷法-允许模糊数据

(1)将order by time offset X limit Y,改写成order by time offset X/N limit Y/N

 

方法四:二次查询法

(1)将order by time offset X limit Y,改写成order by time offset X/N limit Y

(2)找到最小值time_min

(3)between二次查询,order by time between $time_min and $time_i_max

(4)设置虚拟time_min,找到time_min在各个分库的offset,从而得到time_min在全局的offset

(5)得到了time_min在全局的offset,自然得到了全局的offset X limit Y

 

 

参考文章:

https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651959942&idx=1&sn=e9d3fe111b8a1d44335f798bbb6b9eea

 

 

 

 

 

 

 

 

 

 

 

posted @ 2018-08-11 15:33  无名草110  阅读(212)  评论(0编辑  收藏  举报