网站设计流程步骤,一个专门做ppt的网站,成都h5模板建站,如何自己做微信小程序优质博文#xff1a;IT-BLOG-CN
背景#xff1a;我们系统上云后#xff0c;数据根据用户UDL部分数据在国内#xff0c;部分数据存储在海外#xff0c;因此需要考虑分库查询的分页排序问题
一、分库后带来的问题
需求根据订单创单时间进行排序分页查询#xff0c;在单表…优质博文IT-BLOG-CN
背景我们系统上云后数据根据用户UDL部分数据在国内部分数据存储在海外因此需要考虑分库查询的分页排序问题
一、分库后带来的问题
需求根据订单创单时间进行排序分页查询在单表中的查询SQL如下(省略部分查询内容)每页获取10条记录
select orderId, orderStatus from t_order order by create_time asc limit 20, 10我们做了分库之后如果需要完成上述的需求需要在两个表中直接执行如下两条SQLoffset都需要从0开始否则数据就不正确了。我这里为了区分表的名字后面带上对应的环境实际生产sql是一样的只是查询的库不同而已。
select * from t_order_sha order by create_time asc limit 0,30;select * from t_order_fra order by create_time asc limit 0,30;如上所示我们需要将前3页的数据全部查出来然后在内存中重新排序最后从中取出第3页的数据也称为“全局查询法”。
该方案存在的问题 随着页码的增加每个节点返回的数据会增多性能也随着下降。同时服务层需要进行二次排序增加了服务层的计算量如果数据过大对内存和cpu的要求也非常高。
不过这种方案也有很多优化方案Sharding-JDBC中就对此方案做出优化采用的是流式处理和归并排序避免内存的过量占用。
二、禁止跳页查询法
我们部分系统航班列表页是通过点击“更多”按钮展示下一页的数据只提供了查询下一页的功能此时页面上展示的是前n页的数据集。
上述的功能在分库分页查询的情况下可以极大的降低业务的复杂度因为当查询第二页数据的时候可以将上一页的最大值最为查询条件此时的SQL可以改写为
select * from t_order_sha where create_time1726671336 order by time asc limit 10;select * from t_order_fra where create_time1726671336 order by time asc limit 10;查询到数据后需要在内存中进行重新排序但相对于“全局查询”数据量已经减少了很多页码越大性能提升越明显。此方案的缺点也非常明显不能跳页查询只能一页一页查询。
三、二分查找法
二分查找法既能满足性能要求也能满足业务要求不过相对前面两种方案理解起来比较困难。
我们还是以上述的查询语句为例这里为了演示方便修改为查询第二页每页返回5条数据
select orderId, orderStatus from t_order order by create_time asc limit 5, 5;【1】SQL改写 原先的SQL的offset5称之为全局offset这里由于是拆分成了两张表因此改写后的offset全局offset/25/22 核心思想第一页的5数据肯定包含在t_order_sha或t_order_fra表中的二分后的0-2之中
最终的落到每张表的SQL如下
select * from t_order_sha order by create_time asc limit 2,5;select * from t_order_fra order by create_time asc limit 2,5;红色部分表示查询结果
t_order_shat_order_fra0000000000100000000002000000000030000000000800000000004000000000090000000000500000000010000000000060000000001100000000007000000000120000000001300000000014……
【2】返回查询数据中的最小值 t_order_sha 00000000003 (这个过程只需要比较各个分库的第一条数据时间复杂度很低)
【3】查询二次改写 第二次查询使用beteween语句起点是第二部返回的最小值终点是每个表第一次查询后的最大值。
t_order_sha 这张表第一次查询的最大值00000000013则SQL改写后
select * from t_order_1 where time between 00000000004 and 00000000013 order by time asc;t_order_fra 这张表第一次查询的最大值00000000014则SQL改写后
select * from t_order_1 where time between 00000000004 and 00000000014 order by time asc;红色部分为第次的查询结果
t_order_shat_order_fra0000000000100000000002000000000030000000000800000000004000000000090000000000500000000010000000000060000000001100000000007000000000120000000001300000000014……
在每个结果集中虚拟一个time_min记录找到time_min在全局的offset。
下图蓝色部分为虚拟的time_min红色部分为第2步的查询结果集。
t_order_shat_order_fra000000000010000000000200000000003000000000040000000000400000000008000000000050000000000900000000006000000000100000000000700000000011000000000130000000001200000000014……
t_order_sha中的第一条数据就是time_min则offset3。 t_order_fra中的第一条数据为00000000008这里的offset为2则向上推移一个找到了虚拟的time_min则offset1。
那么此时的time_min的全局offset134。
【5】查找最终结果 找到了time_min的最终全局offset4之后再根据第2步获取的两个结果集在内存中重新排序。
[00000000004,00000000005,00000000006,00000000007,00000000008,00000000009,00000000010,00000000011,00000000012,00000000013,000000000104]现在time_min也就是00000000004的offset4那么原先的SQLselect * from t_order order by time asc limit 5,5;此时可以发现SQL中的总偏移量和最小值的偏移量的差值5-41因此需要对排序后的结果集向后推移一位取值。同时因为最小值也包含在集合中的无论前面的差值是多少这里都需要将最小值踢出去所以也需要再向后移一位。根据SQL取5条数据就能够得到如下结果
00000000006,00000000007,00000000008,00000000009,00000000010这种方案的优点可以精确的返回业务所需数据每次返回的数据量都非常小不会随着翻页增加数据的返回量。 缺点也是很明显需要进行两次查询。
