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

最新推荐文章于 2024-04-08 00:38:42 发布

不才陈某

最新推荐文章于 2024-04-08 00:38:42 发布

阅读量3.3k

点赞数 2

文章标签： java 数据库 mysql 分布式算法

文章首发于微信公众号【码猿技术专栏】，欢迎关注！！！

本文链接：https://blog.csdn.net/qq_34162294/article/details/127063911

版权

大家好，我是不才陈某~

随着数据的日益增多，在架构上不得不分库分表，提高系统的读写速度，但是这种架构带来的问题也是很多，这篇文章就来讲一讲跨库/表分页查询的解决方案。

关于分库分表后的其他的问题，请看陈某前一篇文章：聊聊分库分表

架构背景

笔者曾经做过大型的电商系统中的订单服务，在企业初期时业务量很少，单库单表基本扛得住，但是随着时间推移，数据量越来越多，订单服务在读写的性能上逐渐变差，架构组也尝试过各种优化方案，比如前面介绍过的：冷热分离、查询分离各种方案。虽说提升一些性能，但是在每日百万数据增长的情况下，也是杯水车薪。

关于冷热分离和查询分离不了解的，可以看笔者前面的文章：

最终经过架构组的讨论，选择了分库分表；至于如何拆分，分片键如何选择等等细节不是本文重点，不再赘述。

在分库分表之前先来拆解一下业务需求：

C端用户需要查询自己所有的订单
后台管理员、客服需要查询订单信息（根据订单号、用户信息.....查询）
B端商家需要查询自己店铺的订单信息

针对以上三个需求，判断下优先级，当然首先需要满足C端用户的业务场景，因此最终选用了uid作为了shardingKey

当然选择uid作为shardingKey仅仅满足了C端用户的业务场景，对于后台和C端用户的业务场景如何做呢？很简单，只需要将数据异构一份存放在ES或者HBase中就可以实现，比较简单，不再赘述。

假设将订单表根据hash(uid%2+1)拆分成了两张表，如下图：

假设现在需要根据订单的时间进行排序分页查询（这里不讨论shardingKey路由，直接全表扫描），在单表中的SQL如下：

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

这条SQL非常容易理解，就是翻页查询第2页数据，每页查询5条数据，其中offest=5

假设现在t_order_1和t_order_2中的数据如下：

以上20条数据从小到大的排序如下：

t_order_1中对应的排序如下：

t_order_2中对应的排序如下：

那么单表结构下最终结果只需要查询一次，结果如下：

分表的架构下如何分页查询呢？下面介绍几种方案

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;

将获取的数据然后在内存中再次进行排序，那么最终的结果如下：

可以看到上述的结果肯定是不对的。

所以正确的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或者小程序中的下拉翻页，这是一种业务折中的方案，但是却能极大的降低业务复杂度

比如第一页的排序数据如下：

那么查询第二页的时候可以将上一页的最大值1664088392作为查询条件，此时的两个表中的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;

执行后的结果如下：

下图中红色部分则为最终结果：

2. 返回数据的最小值

t_order_1：5条数据中最小值为：1664088479

t_order_2：5条数据中最小值为：1664088392

那么两张表中的最小值为1664088392，记为time_min，来自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;

此时查询的结果如下（红色部分）：

上述例子只是数据巧合导致第2步的结果和第3步的结果相同，实际情况下一般第3步的结果会比第2步的结果返回的数据会多。

4. 在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset

在每个结果集中虚拟一个time_min记录，找到time_min在全局的offset，下图蓝色部分为虚拟的time_min，红色部分为第2步的查询结果集

因为第1步改后的SQL的offset为2，所以查询结果集中每个分表的第一条数据offset为3（2+1）；

t_order_1中的第一条数据为1664088479，这里的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步获取的两个结果集在内存中重新排序后，结果如下：

现在time_min也就是1664088392的offset=5，那么原先的SQL：select * from t_order order by time asc limit 5,5;的结果显而易见了，向后推移一位，则结果为：

刚好符合之前的结果，说明二次查询的方案没问题

这种方案的优点：可以精确的返回业务所需数据，每次返回的数据量都非常小，不会随着翻页增加数据的返回量

缺点也是很明显：需要进行两次查询

总结

本篇文章中介绍了分库分表后的分页查询的三种方案：

全局查询法：这种方案最简单，但是随着页码的增加，性能越来越低
禁止跳页查询法：这种方案是在业务上更改，不能跳页查询，由于只返回一页数据，性能较高
二次查询法：数据精确，在数据分布均衡的情况下适用，查询的数据较少，不会随着翻页增加数据的返回量，性能较高