作者：菩提树下的杨果来源：https://www.cnblogs.com/yjmyz...当业务数据达到一定程度（例如：mysql单表记录>1000万），通常考虑“分库分表”将数据分散到不同的库或表中，可以大大提高读写性能。但问题来了。对于select*fromtablelimitoffset和pagesize这样的分页方式，原本一条语句可以轻松搞定的事情会变得很复杂。本文将与大家一起探讨分库分表后“分页”所面临的新问题。1、分表对分页的影响比如有一张表有8条记录（为简单起见，假设表上只有1个自增ID），可以抽象成1个（有序的）序列在数学（注：为方便讨论，如无特别说明，文中数列顺序均指升序排列）（1,2,3,4,5,6,7,8）如果要取出上面用红色标出的2、3两条记录，limit1,2就行了。现在如果分成2张表（即把原序列拆分成2个非空子序列），一般来说，常用的分为两种分割方法：1.1切分法（例如：带时间属性的数据,比如orders类型可以根据下单时间拆分,一个月一张表)(1,2,3,4)(5,6,7,8)沿用之前的思路limitx,y,on每个子表限制1,2，会得到如下2个子数组：(2,3)(6,7)然后在内存中归并排序，然后取前2项(2,3,6,7)=>(2,3)，这似乎符合预期（这种想法也称为合并），但这只是一种错觉。当要取的分页数据落在不同的子序列上时，可以发现问题：(1,2,3,4,5,6,7,8)比如我们要从4个位置开始，取2个连续元素，即：limit3,2(1,2,3,4)=>limit3,2=>(4)(5,6,7,8)=>limit3,2=>(8)最后合并后的结果为(4,8)，与正确的结果(4,5)相比，显然是错误的。1.2模块化余数摊销法（例如：取字段值到2求余数，根据余数决定分配到哪个表，这种方法也称为余数法）（1,3,5,7)(2,4,6,8)归并排序的思想在切分法中不起作用，对于取模和摊销也不起作用。还是以limit1,2为例，原序列的结果为(2,3)。如果使用合并的思路：(1,3,5,7)=>limit1,2=>(3,5)(2,4,6,8)=>limit1,2=>(4,6)内存合并排序后，取前面2，最终结果为(3,4)结论：分库分表不管用什么划分方式，思路很简单合并不能正确解决分页问题。2.globalmethod(limitx+y)反思刚才合并的思路。本质上，当我们在每个子序列（即：子表）上限制x,y时，取出来的数据可能已经缺失了。网上有一篇流传很广的文章《行业问题-跨库分页》，文中作者提出了一个解决方案：扩大范围，将分表sql上的limitx,y改为limit0,x+y，这样改写后，相当于把子表中“每页最后一条数据”之前的数据全部取出来（当然：里面可能有不必要的冗余数据），然后合并它们一起在内存中，然后取x偏移量的最后y条数据。用前面的例子验证一下：原序列：(1,2,3,4,5,6,7,8)，需要取出极限1,2，即：(2,3)2.1是通过段法分成2段：(1,2,3,4)=>limit1,2=>重写为limit0,1+2=>(1,2,3)(5,6,7,8)=>limit1,2=>重写为limit0,1+2=>(5,6,7)合并排序子数组=>{1,2,3,5,6,7}=>根据原来的offsetlimit1,2=>{2,3}正确如果原序列中要取的数据落在两个子序列上(1,2,3,4,5,6,7,8),limit3,2需要去掉，即：(4,5)(1,2,3,4)=>limit3,2=>改写为limit0,3+2=>(1,2,3,4)(5,6,7,8)=>limit3,2=>重写为limit0,3+2=>(5,6,7,8)合并排序子数组=>(1,2,3,4,5,6,7,8)=>byoriginaloffsetlimit3,2=>(4,5)也按预期工作。2.2取模并将其分成2段(1,3,5,7)=>limit1,2->改写为limit0,1+2=>(1,3,5)(2,4,6,8)=>limit1,2->重写为limit0,1+2=>(2,4,6)合并子序列=>(1,2,3,4,5,6)=>根据原始部分Shiftlimit1,2=>(2,3)是正确的。这种方式的缺点也很明显：取出的记录太多，比如limit10000000,10->重写后变成limit0,10000010。遇到海量数据的时候，mysql查询可能直接超时，这么多数据从db传输到应用层，网络开销也很大，更何况如果是java应用，大量的数据放在一个List或者Map中，很容易出现OOM。（注：一般情况下，如果需要分库分表，数据量一定很大，所以这种方法在实践中基本没用）另外，MySQL系列面试题和答案都已经整理好了，微信搜索Java栈，后台发：面试，可以在线阅读。3.二级查询方式这也是《行业难点-跨库分页》一文中提到的一种方式。也就是说不会出现数据过多或过少的分表），换句话说：本方案没有应用切分的方法，操作如下：1）limitoffset和pagesizein将原来的sql改写为limitoffset/n,pagesize（注意：n是分表的个数，如果offset/n不可分割，则向下取整，以免最终结果丢失数据）——意思是，其实，假设原表这一页的数据，会被平均分配到各个子表（所以，我反复强调，前提是数据均分。如果一个子表的记录很少-表，甚至极端情况下为空，这是错误的，最终结果会少数据）2）在分表上，执行改写后的sql得到一堆结果集，然后找到其中最小的id这一堆结果（假设id是关键排序字段），记录为min_id——这一步的目的是为了找到最小的起点，保证第1页数据的起点是正确的。3）各分表上的sql，where条件部分改写为idbetweenmin_idandorigin_max_id（注：origin_max_id为上一步各分表查询结果集中的最大值，显然是分表whosemin_id=自己的最小id，不用重复查询）--这一步的目的是，因为第1）步查到的结果通常比原表中page上的数据少，所以设置starting这里指向正确的位置，即：min_id，再检查一遍，相当于扩大范围，保证数据不会丢失。但是，这里有一个可以优化的地方。仔细想想，这个查询的结果和步骤1)找到的结果肯定有一些重复，所以重写部分只需要min_id和origin_min_id之间的id就可以了（origin_min_id是原表拆分结果上的最小id）4）合并并在内存中对上一步的结果进行排序，去重（注意：如果上一步采用了优化方案，则应该是1）和3）这第二次查询的结果全部取出，合并，排序，去重），然后可以从头开始连续取pagesize条数据（注意：如果offset/n不可分割，则向下取整，也就是起点可能往前移了，所以有可能是第一条记录开头的其实是上一页的最后一条记录，如果追求准确性，可以在应用层记录上一页最后一条记录的id，然后跟本次查询结果的第一条记录进行比较，如果发现相同，则开始取数据的位置要向后移1，如果id认为是重复的，则要根据情况多移几位）验证看看效果：场景1（前提：取余法）原序列：（1,2,3,4,5,6,7,8），需要取出极限2,2，即：（3,4）先查询（1,3,5,7)->极限2,2->重写为极限1,2->(3,5)(2,4,6,8)->极限2,2->重写为极限1,2->(4,6)最小值为3第二个查询(1,3,5,7)->介于3和5之间->(3,5)(2,4,6,8)->between3and6->(4,6)合并第二次查询的结果：(3,4,5,6)->从头部开始，取pageSize，也就是2个元素->(3,4)is正确的-----------------------------场景2（前提：取余法）原序列：(1,2,3,4,5,6,7,8)，需取出极限1,2，即：(2,3)为第一次查询(1,3,5,7)->limit1,2->改写为limit0,2->(1,3)--注意：因为1/2不可分，这里四舍五入(2,4,6,8)->限制1,2->重写为限制0,2->(2,4)最小值为1第二个查询(1,3,5,7)->between1and3->(1,3)(2,4,6,8)->between1and4->(2,4)结合上面的结果：(1,2,3,4)->(2,3)（注：起点，第一个query改写的时候，拿下来大功告成，所以这里我们需要移位1位，从第二个数中取出pagesize的数据块）--------------------------------------------------------场景三（前提：分段法）为什么分段法，这个方案不适用，可以看下面的分析:原序列：(1,2,3,4,5,6,7,8)，需要取出极限2,2，即：(3,4)第一次查询(1,2,3,4)->limit2,2->limit1,2->{2,3}--注意：这里丢失了正确的数据(5,6,7,8)->limit2,2->limit1,2->{5,6}--注：该段本页根本没有数据最小值2第二次查询(1,2,3,4)->between2and3->{2,3}(5,6,7,8)->2和6之间->{5,6}(2,3,5,6)->(2,3)这是预期的结果抱歉---------------------------------------------------场景四（前提：取余法）在取余法的前提下，如果某个子表中的一部分数据被清空，即某个子表中的数据过少，会发生什么？比如下面这个，按照奇数和偶数分成2个子序列，但是有意删除了几个偶数（相当于真正的业务，把这个子表上的部分数据干掉了）。原序列：(1,3,5,6,7,8,9,11)，需要取出极限2,2，即：(5,6)第一次查询(1,3,5,7,9,11)->limit2,2->重写为limit1,2->(3,5)(6,8)->limit2,2->重写为limit1,2->(8)第二个查询(1,3,5,7,9,11)->3和5之间->(3,5)(6,8)->3和8之间->(6,8)合并后(3,5,6,8)->(3,5)这不符合预期的结果。4、禁止页面跳转相当于只允许向上或向下翻页。原理很简单，比如：对于下一页，先记录上一页的最大id，然后对于下一页，只需要whereid>上一页id的最大值限制pagesize，只转1每次翻页。显然，这是一种牺牲用户体验的做法。结论：分表分页没有通用的解决方案。要么性能有问题（比如：全局方法限制x+y），要么必须满足一定的前提条件（比如：二次查询），要么在产品设计上牺牲了用户体验，仍然是业界的难题。参考文章：https://juejin.im/post/5d1f52...https://shardingsphere.apache...https://stackoverflow.com/que...http://kmiku7.github.io/2019/...https://segmentfault.com/a/11...https://mp.weixin.qq.com/s/h9...本文版权归作者及博客园所有，欢迎转载，但未经作者同意，必须保留此段声明，并在文章页面显着位置给出原文链接，否则保留追究法律责任的权利。近期热点文章推荐：1.1,000+Java面试题及答案（2021最新版）2.别在满屏的if/else中，试试策略模式，真的很好吃！！3.操！Java中xx≠null的新语法是什么？4、SpringBoot2.5发布，深色模式太炸了！5.《Java开发手册（嵩山版）》最新发布，赶快下载吧！感觉不错，别忘了点赞+转发！
