作者：菩提树下的杨过
出处：https://www.cnblogs.com/yjmyz…
当业务数据达到一定量级 (比方：mysql 单表记录量 >1 千万) 后，通常会思考“分库分表”将数据扩散到不同的库或表中，这样能够大大提高读 / 写性能。然而问题来了，对于 select * from table limit offset , pagesize 这种分页形式，原来一条语句就能够简略搞定的事件会变得很简单，本文将与大家一起探讨分库分表后 ” 分页 ” 面临的新问题。
一、分表对分页的影响
比方有一张表，外面有 8 条记录 (为简略起见，假如该表上只有 1 个自增 ID），数学上能够形象成 1 个(有序) 数列(注：为不便探讨，不加非凡阐明的状况下，文本中数列的程序，均指升序)
(1，2，3，4，5，6，7，8)
如果要取出下面红色标识的 2,3 这二条记录，limit 1,2 就行了。
当初如果分成 2 张表（即：原来的数列，拆分成 2 个非空子数列），一般来讲，有二种罕用分法：
1.1 分段法（比方：有工夫属性的数据，相似订单这种，能够按下单工夫拆分，每个月 1 张表）
(1，2，3，4)
(5，6，7，8)
沿用之前的 limit x,y 的思路，每个分表上 limit 1,2，会失去如下 2 个子数列：
(2，3)
(6，7)
而后在内存中合并排序，再取前 2 条 (2，3，6，7) => (2，3)，貌似看上去也合乎预期（这个思路也称为归并），但这只是假象。当要取的分页数据落在不同的子数列上时，就能发现问题：
(1，2，3，4，5，6，7，8) 比方，咱们要从 4 个地位开始，间断取 2 个元素，即: limit 3,2
(1，2，3，4) => limit 3,2 =>(4)
(5，6，7，8) => limit 3,2 =>(8)
最初合并进去的后果是 (4，8) 与正确后果 (4，5) 相比，显然不对。
1.2 模余均摊法（比方：字段值对 2 取模求余数，依据余数决定分到哪个表，该办法也简称为取余法）
(1，3，5，7)
(2，4，6，8)
归并排序的思路在分段法上行不通，对于取模均摊同样也不行，仍以 limit 1,2 为例，原始序列取出来的后果是(2，3)，如果用归并的思路：
(1，3，5，7)=> limit 1,2 =>(3 ,5)
(2，4，6，8)=> limit 1,2 =>(4, 6)
内存合并排序后，取前 2 个，最终后果为(3 , 4)
论断：不论分库分表采纳什么分法，简略归并的思路，都无奈正确解决分页问题。
二、全局法(limit x+y)
反思一下方才的归并思路，实质上咱们在每个子数列（即：分表）上 limit x,y 时，取出来的数据就有可能曾经产生缺失了。网上有一篇广为流转的文章 ” 业界难题 - 跨库分页”，作者在文中提出了一个计划：把范畴扩充，分表 sql 上的 limit x,y 变成 limit 0, x+y，这样改写后，相当于分表中把 ” 每页最初一条数据 ” 之前的所有数据全都取出来了（当然：这外面可能会有不须要的多余数据)，而后内存中合并在一起，再取 x 偏移量后的 y 条数据。
用后面的例子验证一下：
原序列：(1，2，3，4，5，6，7，8)，须要取出 limit 1,2，即：(2，3)
2.1 按分段法拆成 2 段：
(1 , 2 , 3 , 4) => limit 1,2 => 改写成 limit 0, 1+2 => (1，2，3)
(5 , 6 , 7 , 8) => limit 1,2 => 改写成 limit 0, 1+2 => (5，6，7)
将子数列合并排序 => {1，2，3，5，6，7} => 按原始偏移量 limit 1,2 =>{2，3} 正确
如果原数列中要取的数据，正好落在 2 个子数列上(1，2，3，4，5，6，7，8)，须要取出 limit 3,2，即：(4，5)
(1 , 2 , 3 , 4) => limit 3,2 => 改写成 limit 0, 3+2 => (1，2，3，4)
(5 , 6 , 7 , 8) => limit 3,2 => 改写成 limit 0, 3+2 => (5，6，7，8)
将子数列合并排序 => (1，2，3，4，5，6，7，8) => 按原始偏移量 limit 3,2 => (4，5) 也合乎预期。
2.2 取模均摊拆成 2 段
(1，3，5，7) => limit 1,2 -> 改写成 limit 0, 1+2 => (1，3，5)
(2，4，6，8) => limit 1,2 -> 改写成 limit 0, 1+2=> (2，4，6)
将子序列合并 => (1，2，3，4，5，6) => 按原始偏移量 limit 1,2 =>(2，3) 正确
该办法毛病也很显著：取出的记录太多了，比方 limit 10000000,10 -> 改写后变成 limit 0, 10000010 遇到海量数据，mysql 中查问有可能间接超时，这么多数据从 db 传到应用层，网络开销也很大，更不用说如果是 java 利用，大量数据放到 List 或 Map 中，容易呈现 OOM。(注：个别状况下，须要用分库分表的场景，数据量必然很大，所以这个办法，理论中基本上没法用)
另外，MySQL 系列面试题和答案全副整顿好了，微信搜寻Java 技术栈，在后盾发送：面试，能够在线浏览。
三、二次查问法
这也是 ” 业界难题 - 跨库分页”一文中提到的一个办法，大抵思路如下：在某 1 页的数据均摊到各分表的前提下(注：这个前提很重要，也就是说不会有一个分表的数据特地多或特地少)，换句话说：这个计划不实用分段法，按如下步骤操作：
1）原 sql 中的 limit offset,pagesize 改写成 limit offset/n ,pagesize (注：n 为分表个数，如果 offset/ n 除不尽，向下取整，防止最初的后果丢数据）– 这个的意思，其实就是假如原表这一页的数据，会均分到各个分表(所以，我一再强调，前提是数据是均摊的，如果某个分表的记录很少，极其状况下，甚至是空的，这个就不对了，最终后果会少数据)
2）分表上，执行改写后的 sql，失去一堆后果集，而后找出这堆后果中的最小 id (假如 id 是要害的排序字段)，记为 min_id — 这一步的目标，是为了找出最小的起始点，保障第 1 页数据终点正确。
3）各分表上的 sql，where 条件局部改写成 id between min_id and origin_max_id (注：origin_max_id 为上一步，每个分表查问后果集中的最大值，显然 min_id= 本身最小 id 的那张分表，不必再反复查问) — 这一步的目标在于，因为步骤 1）查出来的后果，通常会比原表上该页的数据少，所以这里从新将起始点设置到正确的地位，即：min_id，再查 1 次，相当于范畴扩充了，以保证数据不会丢。不过，这里有一个可优化的中央，认真想想，这 1 次查问进去的后果，跟步骤 1）中的查出来的后果，必然有一部分是反复的，因而改写局部，只须要 id between min_id and origin_min_id 就能够了(origin_min_id 即为原来分表后果上的最小 id)
4）将上一步查问进去的后果，在内存中合并排序去重（注：如果上一步采纳了优化计划，就应该是把 1）与 3）这二次查问的后果全取出来合并排序去重），而后从开始间断取 pagesize 条数据即可（注：offset/ n 除不尽的话，向下取整了，也就是起始点可能向前多移了，所以有可能开始的第 1 条记录，其实是上 1 页的最初 1 条记录，要谋求准确的话，能够在应用层记录上一页最初 1 条记录的 id，而后跟本次查问后果前 1 条记录比照，如果发现是一样的，开始取数据的地位，就要向后移 1 位，如果思考 id 有反复的话，就要依据状况多移几位）
验证一下看看成果：
场景 1（前提：取余法）
原序列：(1，2，3，4，5，6，7，8)，须要取出 limit 2,2，即：(3，4)
第 1 次查问
(1，3，5，7) -> limit 2,2 -> 改写成 limit 1,2 -> (3，5)
(2，4，6，8) -> limit 2,2 -> 改写成 limit 1,2 -> (4，6)
最小值为 3
第 2 次查问
(1，3，5，7) -> between 3 and 5 -> (3，5)
(2，4，6，8) -> between 3 and 6 -> (4，6)
将第 2 次查问的后果合并：
(3，4，5，6) -> 取头开始，取 pageSize 即 2 个元素 -> (3，4) 正确
—————————————————–
场景 2（前提：取余法）
原序列：(1，2，3，4，5，6，7，8)，须要取出 limit 1,2，即：(2，3)
第 1 次查问
(1，3，5，7) -> limit 1,2 -> 改写成 limit 0,2 -> (1，3) – 注：因为 1 / 2 除不尽，这里向下取整了
(2，4，6，8) -> limit 1,2 -> 改写成 limit 0,2 -> (2，4)
最小值为 1
第 2 次查问
(1，3，5，7) -> between 1 and 3 -> (1，3)
(2，4，6，8) -> between 1 and 4 -> (2，4)
将下面的后果合并：
(1，2，3，4) -> (2，3) (注：起始点，第 1 次查问改写时，向下取整了，所以这里要向移 1 位，从第 2 个数字开始取 pagesize 条数据)
——————————————————–
场景 3（前提：分段法）
为什么说分段法，这个计划不实用，能够看上面的剖析：
原序列：(1，2，3，4，5，6，7，8)，须要取出 limit 2,2，即：(3，4)
第 1 次查问
(1，2，3，4) -> limit 2,2 -> limit 1,2 -> {2，3} – 注：这里就曾经把正确的数据给丢掉了
(5，6，7，8) -> limit 2,2 -> limit 1,2 -> {5，6} – 注：这一段里基本就没有这一页的数据
最小值 2
第 2 次查问
(1，2，3，4) -> between 2 and 3 -> {2，3}
(5，6，7，8) -> between 2 and 6 -> {5，6}
(2，3，5，6) -> (2，3) 这个跟预期后果就对不上了。
——————————————————-
场景 4（前提：取余法）
取余法的前提下，如果某个分表的数据，被清掉一部分，也就是某个分表数据偏少，会产生什么？
比方上面这个，按奇数、偶数分成 2 个子序列，然而偶数成心删除了几个（相当于事实业务中，这个分表上的数据被干掉了一部分）
原序列：(1，3，5，6，7，8，9，11)，须要取出 limit 2,2，即：(5，6)
第 1 次查问
(1，3，5，7，9，11) -> limit 2,2 -> 改写成 limit 1,2 -> (3，5)
(6，8) -> limit 2,2 -> 改写成 limit 1,2 -> (8)
第 2 次查问
(1，3，5，7，9，11) -> between 3 and 5 -> (3，5)
(6，8) -> between 3 and 8 -> (6，8)
合并后
(3，5，6，8) -> (3，5) 这跟预期后果也对不上。
四、禁止跳页
相当于只容许向上或向下翻页，原理很简略，比方：下一页，先记录上一页的最大 id，而后下一页时，只须要 where id > 上一页最大 id limit pagesize，每次只翻 1 页。显然，这是一个就义用户体验的做法。
论断：分表分页不存在一个通用的解决方案，要么性能有问题（比方: 全局法 limit 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.Spring Boot 2.5 重磅公布，光明模式太炸了！
5.《Java 开发手册（嵩山版）》最新公布，速速下载！
感觉不错，别忘了顺手点赞 + 转发哦！