MySQL 跨库分页/ 分表分页/ 跨库分页，为什么这么难？

水深无声 2022-09-13 13:27 410阅读 0赞

**点击关注公众号，Java干货****及时送达**![0d2af3e48e1bb780c562f3b2dc5f3eea.png][]

作者：菩提树下的杨过  
出处：www.cnblogs.com/yjmyzz/p/12149737.html

当业务数据达到一定量级(比如：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)][1_3_5_7_ limit 1_2 _3 _5]

[(2，4，6，8)=> limit 1,2 =>(4, 6)][1_3_5_7_ limit 1_2 _3 _5]

内存合并排序后，取前2个，最终结果为(3 , 4)

结论：不管分库分表采用什么分法，简单归并的思路，都无法正确解决分页问题。另外，MySQL 系列面试题和答案全部整理好了，微信搜索Java技术栈，在后台发送：面试，可以在线阅读。

## 二、全局法(limit x+y) ##

反思一下刚才的归并思路，本质上我们在每个子数列（即：分表）上limit x,y 时，取出来的数据就有可能已经产生缺失了。

推荐一个 Spring Boot 基础教程及实战示例：https://github.com/javastacks/spring-boot-best-practice

网上有一篇广为流转的文章"业界难题-跨库分页”，作者在文中提出了一个方案：把范围扩大，分表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。

注：一般情况下，需要用分库分表的场景，数据量必然很大，所以这个方法，实际中基本上没法用。

## 三、二次查询法 ##

这也是"业界难题-跨库分页”一文中提到的一个方法，大致思路如下：在某1页的数据均摊到各分表的前提下(注：这个前提很重要，也就是说不会有一个分表的数据特别多或特别少)，换句话说：这个方案不适用分段法，按如下步骤操作：

[1）原sql中的limit offset,pagesize 改写成 limit offset/n ,pagesize (注：n为分表个数，如果offset/n除不尽，向下取整，避免最后的结果丢数据）-- 这个的意思，其实就是假设原表这一页的数据，会均分到各个分表(所以，我一再强调，前提是数据是均摊的，如果某个分表的记录很少，极端情况下，甚至是空的，这个就不对了，最终结果会少数据)][1_3_5_7_ limit 1_2 _3 _5]

[2）分表上，执行改写后的sql，得到一堆结果集，然后找出这堆结果中的最小id (假设id是关键的排序字段)，记为min\_id -- 这一步的目的，是为了找出最小的起始点，保证第1页数据起点正确。][1_3_5_7_ limit 1_2 _3 _5]

[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)][1_3_5_7_ limit 1_2 _3 _5]

[4）将上一步查询出来的结果，在内存中合并排序去重（注：如果上一步采用了优化方案，就应该是把1）与3）这二次查询的结果全取出来合并排序去重），然后从开始连续取pagesize条数据即可（注：offset/n除不尽的话，向下取整了，也就是起始点可能向前多移了，所以有可能开始的第1条记录，其实是上1页的最后1条记录，要追求精确的话，可以在应用层记录上一页最后1条记录的id，然后跟本次查询结果前1条记录对比，如果发现是一样的，开始取数据的位置，就要向后移1位，如果考虑id有重复的话，就要根据情况多移几位）][1_3_5_7_ limit 1_2 _3 _5]

**点击关注公众号，Java干货****及时送达**![49432b4cd6fad60edf36c95364595c0e.png][]

验证一下看看效果：

[**场景1（前提：取余法）**][1_3_5_7_ limit 1_2 _3 _5]

[原序列：(1，2，3，4，5，6，7，8)，需要取出limit 2,2 ，即：(3，4)][1_3_5_7_ limit 1_2 _3 _5]

[第1次查询][1_3_5_7_ limit 1_2 _3 _5]

[(1，3，5，7) -> limit 2,2 -> 改写成 limit 1,2 -> (3，5)][1_3_5_7_ limit 1_2 _3 _5]

[(2，4，6，8) -> limit 2,2 -> 改写成 limit 1,2 -> (4，6)][1_3_5_7_ limit 1_2 _3 _5]

[最小值为3][1_3_5_7_ limit 1_2 _3 _5]

[第2次查询][1_3_5_7_ limit 1_2 _3 _5]

[(1，3，5，7) -> between 3 and 5 -> (3，5)][1_3_5_7_ limit 1_2 _3 _5]

[(2，4，6，8) -> between 3 and 6 -> (4，6)][1_3_5_7_ limit 1_2 _3 _5]

[将第2次查询的结果合并：][1_3_5_7_ limit 1_2 _3 _5]

[(3，4，5，6) ->取头开始，取pageSize即2个元素 -> (3，4) 正确][1_3_5_7_ limit 1_2 _3 _5]

[\-----------------------------------------------------][1_3_5_7_ limit 1_2 _3 _5]

[**场景2（前提：取余法）**][1_3_5_7_ limit 1_2 _3 _5]

[原序列：(1，2，3，4，5，6，7，8)，需要取出limit 1,2 ，即：(2，3)][1_3_5_7_ limit 1_2 _3 _5]

[第1次查询][1_3_5_7_ limit 1_2 _3 _5]

[(1，3，5，7) -> limit 1,2 -> 改写成 limit 0,2 -> (1，3) --注：因为1/2除不尽，这里向下取整了][1_3_5_7_ limit 1_2 _3 _5]

[(2，4，6，8) -> limit 1,2 -> 改写成 limit 0,2 -> (2，4)][1_3_5_7_ limit 1_2 _3 _5]

[最小值为1][1_3_5_7_ limit 1_2 _3 _5]

[第2次查询][1_3_5_7_ limit 1_2 _3 _5]

[(1，3，5，7) -> between 1 and 3 -> (1，3)][1_3_5_7_ limit 1_2 _3 _5]

[(2，4，6，8) -> between 1 and 4 -> (2，4)][1_3_5_7_ limit 1_2 _3 _5]

[将上面的结果合并：][1_3_5_7_ limit 1_2 _3 _5]

[(1，2，3，4) -> (2，3) (注：起始点，第1次查询改写时，向下取整了，所以这里要向移1位，从第2个数字开始取pagesize条数据)][1_3_5_7_ limit 1_2 _3 _5]

[\--------------------------------------------------------][1_3_5_7_ limit 1_2 _3 _5]

[**场景3（前提：分段法）**][1_3_5_7_ limit 1_2 _3 _5]

[为什么说分段法，这个方案不适用，可以看下面的分析：][1_3_5_7_ limit 1_2 _3 _5]

[原序列：(1，2，3，4，5，6，7，8)，需要取出limit 2,2 ，即：(3，4)][1_3_5_7_ limit 1_2 _3 _5]

[第1次查询][1_3_5_7_ limit 1_2 _3 _5]

[(1，2，3，4) -> limit 2,2 -> limit 1,2 -> \{2，3\} --注：这里就已经把正确的数据给丢掉了][1_3_5_7_ limit 1_2 _3 _5]

[(5，6，7，8) -> limit 2,2 -> limit 1,2 -> \{5，6\} --注：这一段里根本就没有这一页的数据][1_3_5_7_ limit 1_2 _3 _5]

[最小值2][1_3_5_7_ limit 1_2 _3 _5]

[第2次查询][1_3_5_7_ limit 1_2 _3 _5]

[(1，2，3，4) -> between 2 and 3 -> \{2，3\}][1_3_5_7_ limit 1_2 _3 _5]

[(5，6，7，8) -> between 2 and 6 -> \{5，6\}][1_3_5_7_ limit 1_2 _3 _5]

[(2，3，5，6) -> (2，3) 这个跟预期结果就对不上了。][1_3_5_7_ limit 1_2 _3 _5]

[\-------------------------------------------------------][1_3_5_7_ limit 1_2 _3 _5]

[**场景4（前提：取余法）**][1_3_5_7_ limit 1_2 _3 _5]

[取余法的前提下，如果某个分表的数据，被清掉一部分，也就是某个分表数据偏少，会发生什么？][1_3_5_7_ limit 1_2 _3 _5]

[比如下面这个，按奇数、偶数分成2个子序列，但是偶数故意删除了几个（相当于现实业务中，这个分表上的数据被干掉了一部分）][1_3_5_7_ limit 1_2 _3 _5]

[原序列：(1，3，5，6，7，8，9，11)，需要取出limit 2,2 ，即：(5，6)][1_3_5_7_ limit 1_2 _3 _5]

[第1次查询][1_3_5_7_ limit 1_2 _3 _5]

[(1，3，5，7，9，11) -> limit 2,2 -> 改写成limit 1,2 -> (3，5)][1_3_5_7_ limit 1_2 _3 _5]

[(6，8) -> limit 2,2 -> 改写成limit 1,2 -> (8)][1_3_5_7_ limit 1_2 _3 _5]

[第2次查询][1_3_5_7_ limit 1_2 _3 _5]

[(1，3，5，7，9，11) -> between 3 and 5 -> (3，5)][1_3_5_7_ limit 1_2 _3 _5]

[(6，8) -> between 3 and 8 -> (6，8)][1_3_5_7_ limit 1_2 _3 _5]

[合并后][1_3_5_7_ limit 1_2 _3 _5]

[(3，5，6，8) -> (3，5) 这跟预期结果也对不上。][1_3_5_7_ limit 1_2 _3 _5]

## 四、禁止跳页 ##

相当于只允许向上或向下翻页，原理很简单，比如：下一页，先记录上一页的最大id，然后下一页时，只需要 where id > 上一页最大id limit pagesize， 每次只翻1页。显然，这是一个牺牲用户体验的做法。

**结论：**

分表分页不存在一个通用的解决方案，要么性能有问题（比如:全局法 limit x+y），要么必须具备一定的前提条件(比如：二次查询)，或者产品设计上牺牲用户体验，仍然是一个业内难题。另外，关注公众号Java技术栈，在后台回复：面试，可以获取我整理的 MySQL 系列面试题和答案，非常齐全。

参考文章：

https://juejin.im/post/5d1f52e46fb9a07eb3099bbf

https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/pagination/

https://stackoverflow.com/questions/3927537/how-do-you-implement-sorting-and-paging-on-distributed-data

http://kmiku7.github.io/2019/08/01/Do-Pagination-With-Table-Database-Sharding/

https://segmentfault.com/a/1190000013225860

https://mp.weixin.qq.com/s/h99sXP4mvVFsJw6Oh3aU5A

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

![d1f5bc39ea3abbdc3dc6441f32f2662b.gif][]

![cec4afbe6433e3d2a74053d2e0ee9bf1.png][]

[![117f204905e183f12d6c36422a831a3f.png][]][117f204905e183f12d6c36422a831a3f.png 1]

[![d5060d892ea0dadb4c0efa7db861c046.png][]][d5060d892ea0dadb4c0efa7db861c046.png 1]

[![81dfc796382f9e0dccd15fda12a7e9c8.png][]][81dfc796382f9e0dccd15fda12a7e9c8.png 1]

[![698434250adac619df28fed71230a745.png][]][698434250adac619df28fed71230a745.png 1]

[![bae5568b74e6bf8ed2f3851b81a5d33f.png][]][bae5568b74e6bf8ed2f3851b81a5d33f.png 1]

**关注Java技术栈看更多干货**

![462e1d312d3100ce3f9ab08389019191.png][]

![8bd682853bee305eb321ce79431f30a4.gif][]

获取 Spring Boot 实战笔记！

[0d2af3e48e1bb780c562f3b2dc5f3eea.png]: /images/20220828/fbe273034db3481e8a6622c55cc18982.png
[1_3_5_7_ limit 1_2 _3 _5]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247540947&idx=2&sn=7faf14de2e31d03167128d6d3cc8c795&chksm=eb50a5e5dc272cf320c15c50f14bc97d3b98aa1e60fec5460824be0f057cb15ed82ba5ed2787&scene=21#wechat_redirect
[49432b4cd6fad60edf36c95364595c0e.png]: /images/20220828/e72d27f13b9d41168bfc376751c8bc58.png
[d1f5bc39ea3abbdc3dc6441f32f2662b.gif]: /images/20220828/d33e1e52cd344dfdb600b47205a28148.png
[cec4afbe6433e3d2a74053d2e0ee9bf1.png]: /images/20220828/8ea1e75102db4354bea2e36e6b1e8807.png
[117f204905e183f12d6c36422a831a3f.png]: /images/20220828/c4499d0fcec4497da680923906b23883.png
[117f204905e183f12d6c36422a831a3f.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247539795&idx=1&sn=1efca511169265e8146e90db220159d5&scene=21#wechat_redirect
[d5060d892ea0dadb4c0efa7db861c046.png]: /images/20220828/1e51ea144ac544d6a2dfdfa87523569c.png
[d5060d892ea0dadb4c0efa7db861c046.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247542231&idx=1&sn=75ab147414255cc18c26b03746833be8&scene=21#wechat_redirect
[81dfc796382f9e0dccd15fda12a7e9c8.png]: /images/20220828/1087a717ffe14799a8f2a43ff516f565.png
[81dfc796382f9e0dccd15fda12a7e9c8.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247540613&idx=2&sn=acbe3189c3e5d11419b8f383f36b145f&scene=21#wechat_redirect
[698434250adac619df28fed71230a745.png]: /images/20220828/e3e74cdef1ef482cb36e1921e0eff6ee.png
[698434250adac619df28fed71230a745.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247537730&idx=1&sn=3869e164e482b838671fa8896cb33cdb&scene=21#wechat_redirect
[bae5568b74e6bf8ed2f3851b81a5d33f.png]: /images/20220828/b136044e7ca94bd3895fdcf7173f13ab.png
[bae5568b74e6bf8ed2f3851b81a5d33f.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247540947&idx=1&sn=a4941f93acb4ee551534a4a1f57d9376&scene=21#wechat_redirect
[462e1d312d3100ce3f9ab08389019191.png]: /images/20220828/53acdbcbcec54864a2fdedaf9de00709.png
[8bd682853bee305eb321ce79431f30a4.gif]: /images/20220828/152379f8a4ec4e889fcacc24549953d4.png