跨库分页

互联网很多业务都有分页拉取数据的需求,业务中经常按照时间time来排序order by,在数据量不大时,可以通过在排序字段time上建立索引,利用SQL提供的offset/limit功能就能满足分页查询需求。此处假设一页数据为100条,均拉取第3页数据。select * from t_msg order by time offset 200 limit 100

分库需求
高并发大流量的互联网架构,一般通过服务层来访问数据库,随着数据量的增大,数据库需要进行水平切分,分库后将数据分布到不同的数据库实例(甚至物理机器)上,以达到降低数据量,增加实例数的扩容目的。
一旦涉及分库,逃不开“分库依据”patition key的概念,使用哪一个字段来水平切分数据库呢:大部分的业务场景,会使用业务主键id。
确定了分库依据patition key后,接下来要确定的是分库算法:大部分的业务场景,会使用业务主键id取模的算法来分库,这样即能够保证每个库的数据分布是均匀的,又能够保证每个库的请求分布是均匀的,实在是简单实现负载均衡的好方法,此法在互联网架构中应用颇多。
问题的提出
仍然是上述用户库的例子,如果业务要查询“最近注册的第3页用户”,该如何实现呢?单库上,可以
select * from t_user order by time offset 200 limit 100
变成两个库后,分库依据是uid,排序依据是time,数据库层失去了time排序的全局视野,数据分布在两个库上,此时该怎么办呢?

全局视野法

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



一般情况下,全局排序的第3页数据,两个库都会包含一部分,所以必须每个库都返回3页数据,所得到的6页数据在服务层进行内存排序,得到数据全局视野,再取第3页数据,便能够得到想要的全局分页数据。

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

  • order by time offset X limit Y,改写成order by time offset 0 limit X+Y
  • 服务层将改写后的SQL语句发往各个分库:即例子中的各取3页数据。
  • 假设共分为N个库,服务层将得到N*(X+Y)条数据:即例子中的6页数据。
  • 服务层对得到的N*(X+Y)条数据进行内存排序,内存排序后再取偏移量X后的Y条记录,就是全局视野所需的一页数据。

方案优点:通过服务层修改SQL语句,扩大数据召回量,能够得到全局视野,业务无损,精准返回所需数据。

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

业务折衷法
“全局视野法”虽然性能较差,但其业务无损,数据精准,不失为一种方案,有没有性能更优的方案呢?
业务折衷一:禁止跳页查询
在数据量很大,翻页数很多的时候,很多产品并不提供“直接跳到指定页面”的功能,而只提供“下一页”的功能。


如上图,不够跳页,那么第一次只能够查第一页:
查第一页的方法和“全局视野法”一样,但每一次“下一页”拉取的方案就不一样了。但点击“下一页”时,需要拉取第二页数据,在第一页数据的基础之上,能够找到第一页数据time的最大值:

这个上一页记录的time_max,会作为第二页数据拉取的查询条件,将查询order by time offset 100 limit 100,改写成order by time where time>$time_max limit 100。这下不是返回2页数据了(“全局视野法,会改写成offset 0 limit 200”),每个分库还是返回一页数据(如上图中粉色部分)。服务层得到2页数据,内存排序,取出前100条数据,作为最终的第2页数据。
如此往复,查询全局视野第100页数据时,仍只返回一页数据以保证数据的传输量和排序的数据量不会随着不断翻页而导致性能下降。
业务折衷二:允许数据精度损失
“全局视野法”能够返回业务无损的精确数据,在查询页数较大,例如第100页时,会有性能问题,此时业务上是否能够接受,返回的100页不是精准的数据,而允许有一些数据偏差呢?
例如:要查询全局的第100页(每页50条)的数据,对于单库:offset 9900 limit 100改写为offset 4950 limit 50对于跨库查询:每个分库偏移4950(一半),获取50条数据(半页),得到的数据集的并集,基本能够认为是全局数据。当然这一页数据的精度并不是精准的。
二次查询法
有没有一种技术方案,即能够满足业务的精确需要,无需业务折衷,又高性能的方法呢?这就是接下来要介绍的终极武器:“二次查询法”。
为了方便举例,假设一页有5条数据,查询第200页的SQL语句为select * from T order by time offset 1000 limit 5
步骤一:查询改写
select * from T order by time offset 1000 limit 5
改写为select * from T order by time offset 500 limit 5
并投递给所有的分库,注意,这个offset的500,来自于全局offset的总偏移量1000,除以水平切分数据库个数2。

如果是3个分库,则可以改写为select * from T order by time offset 333 limit 5
假设这三个分库返回的数据(time, uid)如下:


可以看到,每个分库都是返回的按照time排序的一页数据。

步骤二:找到所返回3页全部数据的最小值
第一个库,5条数据的time最小值是1487501123
第二个库,5条数据的time最小值是1487501133
第三个库,5条数据的time最小值是1487501143

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

步骤三:查询二次改写
第一次改写的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条记录)是新的(上图中粉色记录);

步骤四:在每个结果集中虚拟一个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。

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


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

优点是:可以精确的返回业务所需数据,每次返回的数据量都非常小,不会随着翻页增加数据的返回量。
不足是:需要进行两次数据库查询。


参考:
http://mp.weixin.qq.com/s/h99sXP4mvVFsJw6Oh3aU5A
https://mp.weixin.qq.com/s/B9FHHM-NCZvtn7xnawYl1w

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容

  • 一、需求缘起 分页需求 互联网很多业务都有分页拉取数据的需求,例如: (1)微信消息过多时,拉取第N页消息 (2)...
    duzhongli阅读 431评论 0 3
  • 微信公共号 [架构师之路] 上看到一篇文章 业界难题-“跨库分页”的四种方案,学习了下,一些笔记。 分页需求 互联...
    专职跑龙套阅读 2,163评论 1 4
  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
    卡卡罗2017阅读 134,050评论 18 139
  • Caffe,全称Convolution Architecture For Feature Extraction,是...
    mtobeiyf阅读 13,722评论 4 7
  • 第一次看到朋友在朋友圈转发了她在简书发的文章,我并没有敢试一试,因为自己已经很久没有写些什么了,而且一直写的也不怎...
    zhaokeke阅读 393评论 7 8