为什么要研究跨库分页？网上很多业务都有分页拉取数据的需求，比如：当微信消息过多时，拉取第N页消息；京东下单数量过多时，在第N页拉取订单；浏览58个城市，查看第N页的帖子。这些业务场景对应的message表、order表、post表有一些共同的特点：有一个业务主键id，msg_id，order_id，tiezi_id；页面按非业务主键id排序，业务中经常用到sortorderbytime。当数据量不大的时候，如何实现跨库分页的需求？在排序字段time上创建索引；可以使用SQL提供的offset/limit来实现。例如：select*fromt_msgorderbytimeoffset200limit100;select*fromt_orderorderbytimeoffset200limit100;select*fromt_tieziorderbytimeoffset200limit100;画外音：这里假设一页有100条数据，拉取第三页的数据。为什么需要子数据库？在高并发、大流量的互联网架构中，一般通过服务层访问数据库。随着数据量的增加，需要对数据库进行水平切分。分库划分后，数据分布到不同的数据库实例中。（甚至是物理机）来达到减少数据量和增加实例数的目的。一旦涉及分库，“分库基础”patitionkey就逃不掉了。应该使用哪个字段来水平分片数据库？大多数业务场景都会用到业务主键id。确定了分库的patitionkey之后，接下来如何确定分库的算法呢？在大多数业务场景下，都会采用对业务主键id取模的算法来划分数据库。这样做的好处是：即可以保证各个数据库的数据分布均匀；也可以保证各个库的请求分布是统一的。确实是一种简单的实现负载均衡的好方法，这种方法在互联网架构中被广泛使用。更具体的例子：用户库user，经过水平拆分后，变成了两个库：根据partitionkey，partitionkey为uid；分区算法是uid取模：uid%2余数为0的数据会落到db0，uid%余数为2的数据会落到db1。数据库横向切分后，如果业务需要查询“第3页最近注册用户”，即跨库分页查询，如何实现？在单个数据库上，您可以：select*fromt_userorderbytimeoffset200limit100;变成两个数据库后，分库的依据是uid，排序的依据是时间。数据库层失去了时间排序的全局视图，数据分布在两个数据库上。这个时候怎么办？根据basis和sortingbasis的不同属性，需要进行分页的查询需求，实现：select*fromTorderbytimeoffsetXlimitY；这种跨库分页SQL是技术问题，后面会讲。解决方案一：全局视野方法如上图所示，服务层通过uid取模将数据分发到两个数据库后，每个数据库都失去了全局视野，页面数据不一定是全局排序的第三页数据，那么哪个数据是全局排序第三页的数据？需要分三种情况讨论。（1）极端情况下，两个库的数据完全一样，如果两个库的数据完全一样，你只需要每个库的一半offset，然后取半个page，就是最终想要的数据（比如上图中粉色部分的数据）（2）极端情况下，结果数据来自一个图书馆。它是所以有可能两个数据库的数据分布极不平衡。比如db0中所有数据的时间都大于db1中所??有数据的时间。可能出现某库第三页的数据是全局排序后第三页的数据（如上图粉色部分）。(3)一般情况下，每个库数据都包含一个部分。一般情况下，每个库都会包含全局排序第三页的一部分数据（如上图中粉色部分的数据）。由于不清楚是什么情况，所以需要：每个库返回3页数据；获取到的6页数据在服务层在内存中进行排序，得到数据的全局视图；然后就可以得到第三页数据了。所需的全局分页数据。总结一下这个方案的步骤：(1)重写SQL语句，即：orderbytimeoffsetXlimitY；对于N个库，服务层会得到N*(X+Y)条数据；(4)服务层将获取到的N*(X+Y)条数据在内存中进行排序；(5)内存排序后取偏移量测量X后的Y记录为全局视图需要的一页数据。全局视觉法有什么优点？通过在服务层修改SQL语句，扩大数据召回量，获得全局视野，业务不受损，准确返回所需数据。全局视觉方法的缺点是什么？缺点很明显：每个分库需要返回更多的数据，增加了网络传输量（网络消耗）；服务层除了按时间对数据库进行排序外，还需要进行二次排序，增加了服务层的计算量（CPU消耗）；最致命的是，这个算法的性能会随着页数的增加而急剧下降，这是因为SQL重写后，每个分库都会返回X+Y行数据：返回第3页，X=200偏移量；如果要返回第100页，offset中X=9900，即每个分库需要返回100页数据，数据量和排序都会大大增加，性能会呈二次方下降。“全局视觉法”虽然性能较差，但其业务未受损，数据准确。在技??术难度高的情况下，业务需求的妥协可以大大简化技术方案。解决方案二：禁止跳页查询方式当数据量大，翻页次数多时，很多产品不提供“直接跳转到指定页面”的功能，只提供“下一页”的功能页”。小型企业的妥协可以大大降低技术解决方案的复杂性。如上图，不能跳页，只能查看初始页：(1)重写查询orderbytimeoffset0limit100；一页数据（上图中粉色部分）；(3)服务层拿到2页数据，对内存进行排序，取出前100条数据作为最终的初始页数据。一般来说，每个分库都包含一部分数据（如上图中粉红色的部分）；这个方案还需要服务器内存排序，这不是和“全局视觉法”一样吗？最初的数据拉取确实是一样的，但是每次“下一页”拉取的解决方案是不同的。当点击“下一页”时，需要拉取第二页的数据。根据初始页的数据，可以找到初始页数据的时间值：上一页记录的time_max会作为第二页的数据拉取。查询条件：（1）改写查询orderbytimeoffset100limit100；intoorderbytimewheretime>$time_maxlimit100;（2）不再返回2页数据（“全局视觉法，将改写为偏移量0限制200”），每个分库仍返回一页数据（如上图粉色部分）;(3)服务层拿到2页数据，对内存进行排序，取出前100条数据作为最后的第二页数据。一般来说，全局第二页数据也是每个分库都包含一部分数据（如上图中粉色部分）；这样查询全局视图第100页的数据时，查询条件不会改写为：offset0limit9900+100；（返回100页数据）但是被改写为time>$time_max99limit100;（仍然返回一页数据）以保证传输和排序数据的数据量不会因为连续翻页而导致性能下降。方案三：允许数据精度损失的“全局视觉法”，可以在不损失业务的情况下返回准确的数据。当查询页数很大时，比如100页，就会出现性能问题。这时候不管业务能不能接受，返回的100Page都不是准确的数据，但允许有一些数据偏差？首先了解一下数据库分库-数据平衡的原理。数据库分库-数据平衡的原理是什么？对分库使用patitionkey，当数据量很大，数据分布足够随机时，每个分库的所有非patitionkey属性，在每个分库上，数据的分布统计概率是一致的.比如在随机uid的情况下，用uid取模划分两个数据库，db0和db1：性别属性，如果数据库db0上男性用户占70%，那么数据库db1上男性用户的比例也应该是70%;age属性，如果db0数据库中18-28岁的青少年用户比例占15%，那么db1数据库中青少年用户的比例也应该是15%；时间属性，如果db0数据库每天10:00之前登录的用户比例为20%，那么db1应该有相同的统计规律；…利用这个原理，查询全局100页数据，只需要改写：offset9900limit100；50)，得到的数据集的并集基本上可以看成是全局数据的offset9900limit100的数据。当然，此页面上的数据并不准确。根据实际业务经验，用户必须在第100页查询页面、帖子和邮件的数据。这个页面数据的准确性损失在业务上往往是可以接受的，但此时技术方案的复杂度就大大降低了。不需要返回更多的数据，也不需要对服务内存进行排序。画外音：如果业务可以接受，这个方案性能不错，强烈推荐。方案四：二次查询方式是否有一种技术方案既能满足业务的精准需求，又不影响业务，且性能高？这就是接下来要介绍的利器，“二次查询法”。例如假设一个页面只有5条数据，查询第200页的SQL语句为：select*fromTorderbytimeoffset1000limit5;第1步：将查询重写为select*fromTorderbytimeoffset1000limit5；改写为：select*fromTorderbytimeoffset500limit5；并下发给所有分库，注意，500的offset是全局offset1000的总offset除以水平拆分的数据库个数2.画外音：因为数据量比较大并且数据是高度随机的，宜假设它仍然符合“数据库分库-数据平衡定理”。如果有3个分库，可以改写为：select*fromTorderbytimeoffset333limit5；假设这三个分库返回的数据(time,uid)如下：可以看到，每个分库都是按时间数据排序的返回页面。第2步：找到3页返回的所有数据的最小值。5条数据的最小时间值为1487501123；第二个库中5条数据的最小时间值为1487501133；第三个库中5条数据的最小时间值为1487501143；因此，三页数据中，时间的最小值来自库1，time_min=1487501123，这个过程只需要比较各个分库的初始数据，时间复杂度很低。画外音：这个time_min很重要，后面的每一步都会用到time_min。第三步：查询第二次重写重写的SQL语句为select*fromTorderbytimeoffset333limit5；第二次重写是一个between语句：between的起点是time_min，between的终点是各个分库返回的原始数据的值分库，返回数据值为1487501523，所以改写查询如：select*fromTorderbytimewheretimebetweentime_minand1487501523；第二个分库，返回数据的值为1487501323，所以查询重写为select*fromTorderbytimewheretimebetweentime_minand1487501323；第三个分库，返回数据的值为1487501323，所以查询重写为5*fromTorderbytimewheretimebetweentime_minand1487501553；与初始查询相比，第二次查询条件放宽了，所以第二次查询会返回比初始查询结果集更多的数据，假设三个分库返回的数据(time,uid)如下：至：分库1的结果集，由于time_min来自于原来的分库1，所以分库1返回的结果集与初始查询相同（所以其实这次访问可以省略）；分库2的结果集，相比Initially，多返回了1条数据，head中的1条记录（时间最小的记录）是新的（上图中粉红色的记录）；分库3的结果集返回的数据比初始的多了2条数据，头2条记录中的记录（时间最小的2条记录）是新的（上图中粉红色的记录）；第四步：在每个结果集中虚拟出一条time_min记录，找到time_min在初始库中的全局偏移量，time_min在库中的偏移量为333；在第二个库中，(1487501133,uid_aa)的偏移量为333（根据初始查询条件），所以虚拟time_min在第二个库中的偏移量为331；画外音：来自333正向演绎。在第三个库中，(1487501143,uid_aaa)的偏移量为333（根据初始查询条件），所以虚拟time_min在第三个库中的偏移量为330；画外音：从333向前推演。综上，time_min的全局偏移量为333+331+330=994。第五步：既然已经获取了time_min的全局偏移量，就相当于拥有了全局视野。根据第二个结果集，可以得到全局偏移量1000limit5的记录，第二个查询返回的结果集在各个分库中是有序的，我们知道time_min的全局偏移量是994，它很容易知道globaloffset1000limit5的一页记录（记录在上图中）。这种方式的好处是可以准确的返回业务需要的数据，而且每次返回的数据量很小，不会随着翻页而增加返回的数据量。帅不帅！！！总结今天介绍了解决“跨N库分页”问题的四种方法：方法一：全局视野法（1）SQL重写，重写orderbytimeoffsetXlimitY；按时间偏移量0限制X+Y排序；(2)Service层对得到的N*(X+Y)条数据进行内存排序，然后取内存排序后偏移量X之后的Y条记录；随着翻页，此方法的性能越来越低。方法二：禁止跳页查询方法(1)正常获取初始页数据，获取初始记录的time_max；(2)每翻一页，重写orderbytimeoffsetXlimitY；intoorderbytimewheretime>$time_maxlimitY;确保只返回一页数据，性能不变。方法三：允许模糊数据方法（1）SQL查询重写，重写orderbytimeoffsetXlimitY；方法四：第二种查询方式（1）重写SQL，重写orderbytimeoffsetXlimitY；intoorderbytimeoffsetX/NlimitY;(2)返回多个页面，找到最小值time_min；(3)二次查询之间orderbytimebetween$time_minand$time_i_max；(4)设置虚拟time_min，找到time_min在各个分库中的偏移量，进而得到time_min的全局偏移量；(5)得到time_min的全局偏移量，自然得到全局偏移量XlimitY；文章比较长，希望大家有所收获。【本文为专栏作者《58神剑》原创稿件，转载请联系原作者】点此阅读更多该作者好文
