资讯

展开

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

作者:快盘下载 人气:

数据量很大时,可以禁止跳页查询,只提供下一页的查询方法,比如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" 刚好符合之前的结果,说明二次查询的方案没问题这种方案的优点:可以精确的返回业务所需数据,每次返回的数据量都非常小,不会随着翻页增加数据的返回量。缺点也是很明显:需要进行两次查询

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

加载全部内容

相关教程
猜你喜欢
用户评论
快盘暂不提供评论功能!