分库分表的情况下如何从mysql查询分页数据(层层渐进,详细易懂)

业务场景

有一张一亿数据量的订单表按照ID哈希分片存储在N台mysql节点中,按照某一字段排序后将分页结果返回给前端

分库分表所带来的查询问题

1. 性能问题
2. 精度问题
3. 跨库跨表的join操作
4. order by问题
5. count (*)问题

SQL方面的解决方案

成本低,不用引入中间件,不用增加新表操作简单

SQL改写(精度准确,性能低)

该业务一般最常见的方式是对每个库中的每个表执行如下sql语句

select * from order order by time limit x, y;

首先我们不考虑深分页问题(想想分库分表的初衷是为了什么,为什么会出现深分页问题,如果想进一步优化,分库分表的深分页该如何解决,欢迎大家留言讨论),语句这样写会出现什么问题??

答案是精度缺失问题,例如我分表,表一中的time为1, 3, 4, 5,表二中的time为2,2,3,4,6,7,如果取limt 1, 3的话那么表一和表二中的数据取完之后再汇总结果为3,4,5,2,3,4,排序后为2,3,3,4,4,5, 可以看到无论何种取法都不会与我们期望的结果2,2,3一样,因此这种方法会导致数据精度不准,那么我们为了解决该问题,该如何修改呢,答案是扩大搜索范围,再将数据合并进行处理

select * from order order by time limit x + y;

可以看到将limit的查询条件进行修改后,查询到数据更过,我们从表一表二查询到的结果汇总结果为,1,3,4,5,2,2,3,4,经过排序则为1,2,2,3,3,4,4,5, 再排序取1-3位的数据则为,2,2,3,与我们希望得到的值一样

但是这样写仍然存在问题,每个分片都要返回更多的数据,增加网络传输,分片本身和服务端都需要进行排序,工作量增加,以及页数的增加导致的深分页(前面文章有提到过深分页的解决思路,感兴趣的可以看下),有没有更好的sql写法呢?

折中方案(精度小误差,性能中)

select * from order order by time limit x/N + y;//N为分片的数量

这种方案性能比上面有所提升,但仍然存在精度和深分页问题,上李子,如果我们要所有数据中的limit 2, 4,数据存到了两个表,分别对两个表进行limit (1, 4),time分别为:表一1,2,2,2,2,3,4,5,表二则为1,3,4,5,6,按照这种方式每个分片执行一次上述查询语句,那么表一的结果为2,2,2,2,表二的结果为3,4,5,6,汇总数据再排序则为2,2,2,2,3,4,5,6,而实际结果应该为1,2,2,2 可以看到精度仍然存在问题,但性能比较上述方案有所提升

二次查询(精度准确版)

首先每个分片执行上述提到的折中方案

select * from order order by time limit x/N + y;//N为分片的数量

再找到每个分片返回的时间最小值time[i]和所有分片中的时间最小值timeMIn(不是返回的最小值,所有数据中的最小值),之后再将每个分片中最小值与timeMin进行比较,小于则需要进行数据补全,也就是查找非timeMin片中小与本分片最小大于timeMin的数据,也就是再对每个非timeMin分片执行以下语句

select * from order where time between timeMin and time[i];

可能直接看文字有点难以理解,话不多说上李子,参考折中方案中的李子,我们对每个分片执行完后,分片一的结果为:2,2,2,2, 分片二的结果为: 3,4,5,6,汇总后排序结果为2,2,2,2,3,4,5,6,接着再进行二次查询,找到最小值为1,再在每个分片中找 1<time<time[i]的数据,分片一则执行 1<=time<=2,分片二则执行 1<= time <=3,分片一补冲的数据为1,2,2,2,2, 分片二补充的数据为1,3

找到需要补全的数据之后我们需要将原来分片查找的数据最小值去掉,加上我们补全中的值,不然会数据重复,将补全的数据和去掉原最小值之后的数据汇总排序后为1,1,2,2,2,2,3,4,5,6, 再取1-4则为1,2,2,2,为我们需要查找到的结果,但是性能仍然没有达到我们想要的结果,有没有更好的方案

二次查询(性能优化版)

你都看到这里了,显然答案是有的,仍然建立在上述方案上,我们只需要将第一次插叙稍作修改即可

老版本:
    select * from order order by time limit x/N + y;//N为分片的数量
新版本:
    select * from order a 
    left join 
    (select id from order order by time limit x/N, y) b 
    on a.id = b.id

提问:为什么新版的查询要比老版本的快?

答案:因为新版本的查询走的id(主索引),减少了大量回表操作,然后我们只需要根据id将原数据表中的对应的id筛选出来即可,可以这么理解有个大学生需要写毕设,然后毕设文档交给了一个ppt大师,那个大师ppt写的非常好啊,什么项目亮点,项目的实现方案都写出来了,之后那个大学生答辩的时候只需要根据ppt上面的介绍自己的项目即可

其他方向上的解决方案

1. 引入ES, 由ES完成分页查询
2. 新建映射表(需要排序的字段, ID) , 将非分片键的查询转换为分片键的查询,比如我的字段为:id(主键分片键), time(二级索引),我查询条件为2003<time<2024,我可以先在映射表中查询满足该time对应的id的结果集,后面再在每个库或表中查找id是否在这个结果集中,在就添加,再将查询到的数据同一汇总再在服务端统计整合所有结果,再返回分页数据

PS:其他问题的解决方案待做...插个眼,凑齐10个赞立马出如何优雅的分库分表,凑齐20个赞把其他问题的解决方案也一起加上,点个赞和收藏,给你他提供更多优质文章, 欢迎大家评论区讨论交流