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

架构背景

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

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

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

1. C端用户需要查询自己所有的订单
2. 后台管理员、客服需要查询订单信息根据订单号、用户信息…查询
3. 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_15条数据中最小值为1664088479

t_order_25条数据中最小值为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为32+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那么原先的SQLselect * from t_order order by time asc limit 5,5;的结果显而易见了向后推移一位则结果为

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

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

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

总结

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

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