“跨库分页”方案
前提:表按照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
参考文章: