百亿级数据分库分表后怎么分页查询

数据量很大时，可以禁止跳页查询，只提供下一页的查询方法，比如APP或者小程序中的下拉刷新，这是一种业务折中的方案，但是却能极大的降低业务复杂度。 随着数据的日益增多，在架构上不得不分库分表，提高系统的读写速度，但是这种架构带来的问题也是很多，这篇文章就来讲一讲跨库/表分页查询的解决方案。

架构背景笔者曾经做过大型的电商系统中的订单服务，在企业初期时业务量很少，单库单表基本扛得住，但是随着时间推移，数据量越来越多，订单服务在读写的性能上逐渐变差，架构组也尝试过各种优化方案，比如前面介绍过的：、各种方案。虽说提升一些性能，但是在每日百万数据增长的情况下，也是杯水车薪。最终经过架构组的讨论，选择了分库分表；至于如何拆分，分片键如何选择等等细节不是本文重点，不再赘述。在分库分表之前先来拆解一下业务需求：。C端用户需要查询自己所有的订单。后台管理员、客服需要查询订单信息（根据订单号、用户信息.....查询）。B端商家需要查询自己店铺的订单信息。针对以上三个需求，判断下优先级，当然首先需要满足C端用户的业务场景，因此最终选用了uid作为了shardingKey。当然选择uid作为shardingKey仅仅满足了C端用户的业务场景，对于后台和C端用户的业务场景如何做呢？很简单，只需要将数据异构一份存放在ES或者HBase中就可以实现，比较简单，不再赘述。假设将订单表根据hash(uid%2+1)拆分成了两张表，如下图：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/dlzlcuxkfvg.jpg" 假设现在需要根据订单的时间进行排序分页查询（这里不讨论shardingKey路由，直接全表扫描），在单表中的SQL如下：

select * from t_order order by time asc limit 5,5;

这条SQL非常容易理解，就是翻页查询第2页数据，每页查询5条数据，其中offest=5假设现在t_order_1和t_order_2中的数据如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/2nivj22bpin.jpg" 以上20条数据从小到大的排序如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/rfxtwokiv5k.jpg" t_order_1中对应的排序如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/41tktfz4mdn.jpg" t_order_2中对应的排序如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/ndkxv2tpzfo.jpg" 那么单表结构下最终结果只需要查询一次，结果如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/wqmn4tz0ill.jpg" 分表的架构下如何分页查询呢？下面介绍几种方案：

1. 全局查询法在数据拆分之后，如果还是上述的语句，在两个表中直接执行，变成如下两条SQL：

select * from t_order_1 order by time asc limit 5,5;

select * from t_order_2 order by time asc limit 5,5;

将获取的数据然后在内存中再次进行排序，那么最终的结果如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/aesoosufsvc.jpg" 可以看到上述的结果肯定是不对的。所以正确的SQL改写成如下：

select * from t_order_1 order by time asc limit 0,10;

select * from t_order_2 order by time asc limit 0,10;

也就是说，要在每个表中将前两页的数据全部查询出来，然后在内存中再次重新排序，最后从中取出第二页的数据，这就是全局查询法该方案的缺点非常明显：随着页码的增加，每个节点返回的数据会增多，性能非常低。服务层需要进行二次排序，增加了服务层的计算量，如果数据过大，对内存和CPU的要求也非常高。不过这种方案也有很多的优化方法，比如Sharding-JDBC中就对此种方案做出了优化，采用的是，有兴趣的可以自行去了解一下。

2. 禁止跳页查询法数据量很大时，可以禁止跳页查询，只提供下一页的查询方法，比如APP或者小程序中的下拉刷新，这是一种业务折中的方案，但是却能极大的降低业务复杂度。比如第一页的排序数据如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/xtfg4vpczen.jpg" 那么查询第二页的时候可以将上一页的最大值作为查询条件，此时的两个表中的SQL改写如下：

select * from t_order_1 where time>1664088392 order by time asc limit 5;

select * from t_order_2 time>1664088392 order by time asc limit 5;

然后同样是需要在内存中再次进行重新排序，最后取出前5条数据但是这样的好处就是不用返回前两页的全部数据了，只需要返回一页数据，在页数很大的情况下也是一样，在性能上的提升非常大此种方案的缺点也是非常明显：不能跳页查询，只能一页一页的查询，比如说从第一页直接跳到第五页，因为无法获取到第四页的最大值，所以这种跳页查询肯定是不行的。

3. 二次查询法以上两种方案或多或少的都有一些缺点，下面介绍一下二次查询法，这种方案既能满足性能要求，也能满足业务的要求，不过相对前面两种方案理解起来比较困难。还是上面的SQL：

select * from t_order order by time asc limit 5,5;

（1）SQL改写第一步需要对上述的SQL进行改写：

select * from t_order order by time asc limit 2,5;

注意：原先的SQL的offset=5，称之为全局offset，这里由于是拆分成了两张表，因此改写后的offset=全局offset/2=5/2=2。最终的落到每张表的SQL如下：

select * from t_order_1 order by time asc limit 2,5;

select * from t_order_2 order by time asc limit 2,5;

执行后的结果如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/gzij2iwbz3i.jpg" 下图中红色部分则为最终结果：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/xqmpa1l2mwl.jpg" （2）返回数据的最小值t_order_1：5条数据中最小值为：t_order_1：5条数据中最小值为：那么两张表中的最小值为，记为，来自t_order_2这张表，这个过程只需要比较各个分库第一条数据，时间复杂度很低。（3）查询二次改写第二次的SQL改写也是非常简单，使用between语句，起点就是第2步返回的最小值time_min，终点就是每个表中在第一次查询时的最大值。t_order_1这张表，第一次查询时的最大值为1664088581，则SQL改写后：

select * from t_order_1 where time between $time_min and 1664088581 order by time asc;

t_order_2这张表，第一次查询时的最大值为1664088481，则SQL改写后：

select * from t_order_2 where time between $time_min and 1664088481 order by time asc;

此时查询的结果如下（红色部分）：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/pzktifiwmv0.jpg" 上述例子只是数据巧合导致第2步的结果和第3步的结果相同，实际情况下一般第3步的结果会比第2步的结果返回的数据会多。（4）在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset。在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset，下图蓝色部分为虚拟的time_min，红色部分为第2步的查询结果集。<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/lulsy1QQhoc.jpg" 因为第1步改后的SQL的offset为2，所以查询结果集中每个分表的第一条数据offset为3（2+1）；t_order_1中的第一条数据为，这里的offset为3，则向上推移一个找到了虚拟的time_min，则offset=2。t_order_2中的第一条数据就是time_min，则offset=3。那么此时的time_min的全局offset=2+3=5。（5） 查找最终数据找到了time_min的最终全局offset=5之后，那么就可以知道排序的数据了。将第2步获取的两个结果集在内存中重新排序后，结果如下：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/xvuqu5vp4mx.jpg" 现在time_min也就是的offset=5，那么原先的SQL：select * from t_order order by time asc limit 5,5;的结果显而易见了，向后推移一位，则结果为：<img src="http://218.206.242.148:12345/images/20_51cto.comt/20220926/xg0qgii2suy.jpg" 刚好符合之前的结果，说明二次查询的方案没问题这种方案的优点：可以精确的返回业务所需数据，每次返回的数据量都非常小，不会随着翻页增加数据的返回量。缺点也是很明显：需要进行两次查询

总结本篇文章中介绍了分库分表后的分页查询的三种方案：全局查询法：这种方案最简单，但是随着页码的增加，性能越来越低。禁止跳页查询法：这种方案是在业务上更改，不能跳页查询，由于只返回一页数据，性能较高。二次查询法：数据精确，查询的数据较少，不会随着翻页增加数据的返回量，性能较高。