您当前的位置:首页 > 电脑百科 > 数据库 > 百科

分库分表必会:跨库分页查询看此一篇就够了

时间:2023-12-22 13:25:33  来源:微信公众号  作者:架构成长指南

概述

随着数据库中数据量日益增多,不得进行分库分表,在分库后将数据分布到不同的数据库实例(甚至物理机器)上,以达到降低数据量,提供系统的处理能力,但是这种架构也带来其他问题,比如本文要讲解的跨库分页查询。

全局查询法

分库分表必会:跨库分页查询看此一篇就够了

test表有数据[1,2,3,4,5,6,7,8],在单库的时候,查询第2页数据并且显示2条,语句是这样的:

select * from test  order by id limit 2 offset 2

数据返回[3,4],但是数据切分以后,如果要查询,这样语句就可能就会有问题,例如:在节点1执行此语句,返回【6,8】,   节点2返回【5,7】,然后进行排序取前二条返回了【5,6】,可以看到此结果与实际结果不一致,所以应该对sql语句改写为:

select * from test  order by id limit 0 offset 4;

然后在根据各节点返回的数据,在进行排序,筛选出第2页的2条。

缺点:

  • 每个节点返回更多的数据,增大了网络传输量
  • 服务层还需要进行二次排序,增大了服务层的计算量
  • 随着页码的增大,性能会急剧下降

优点:

  • 查询简单,数据准确,不用做业务兼容,数据库中间件都支持

禁止跳页查询法

在数据量很大,翻页数很多的时候,很多产品并不提供“直接跳到指定页面”的功能,而只提供“下一页”的功能,这一个小小的业务折衷,就能极大的降低技术方案的复杂度。

假设db1中值为【2、4、6、8】,db2中值为【1、3、5、7】,根据id进行排序,返回对应的条数,在内存中对各个节点返回的数据进行排序,得到需要的数据,执行以下语句,查询第一页数据,返回结果集为【1,2】:

select * from test where id>0 order by id  limit 2;

相比以前的方案,貌似跟以前处理流程一样,但是在查询第二页时,要根据上一页的id的最大值id_max(第一页的最大id_max为2),作为第二页的最小值,那么会将如下语句:

select * from test  order by id limit 2,2;

改写成:

select * from test order by id> 2 limit 2

这样每个节点不用返回4页数据了,只需要返回跟第一页一样页数的数据,可以看到通过对业务的折中,性能得到大大的提升。

  • 缺点:此种方案需要业务层进行处理,而且不能跳页查询,比如当前页是第一页,直接调到第五页,因无法获取到第四页的最大ID,所以无法查询第五页的数据
  • 优点:不会随着页数的增大而影响查询性能

允许数据精度损失查询法

使用patition key进行分库,在数据量较大,数据分布足够随机的情况下,各分库所有非patition key属性,在各个分库上的数据分布,统计概率情况是一致的。

例如,在uid随机的情况下,使用uid取模分两库,db0和db1:

  • 性别属性,如果db0库上的男性用户占比70%,则db1上男性用户占比也应为70%
  • 年龄属性,如果db0库上18-28岁少女用户比例占比15%,则db1上少女用户比例也应为15%
  • 时间属性,如果db0库上每天10:00之前登录的用户占比为20%,则db1上应该是相同的统计规律

分库分表必会:跨库分页查询看此一篇就够了

精度损失查询法示意图

利用这一原理,如上图要查询全局第二页数据,limit 2 offset 2 改写为 limit 1 offset 1,每个分库偏移 1(一半),获取1条数据(半页) ,得到的数据集的并集,那么结果为【3,4】基本能够认为,是全局数据的limit 2 offset 2的数据,当然这里只是为了所以返回的准确数据,但是实际并不是精准的。

根据实际业务经验,用户都要查询第100页网页、帖子、邮件的数据了,这一页数据的精准性损失,业务上往往是可以接受的,但此时技术方案的复杂度便大大降低了,既不需要返回更多的数据,也不需要进行服务内存排序了。

终极大招-二次查询法

以上介绍的方案或多或少都有一定缺点,那么有没有一种方式能够满足业务需要,也能满足性能要求的方法呢,有,那就是二次查询法。

因此方案相比前三个方案理解起来相对复杂点,为了方便说明,所以先单一DB说起,以下单一DB中保存用户年龄数据,1到30岁,总共30条,如果要查询:

select * from T order by age limit 5 offset 10 

那么会返回以下粉色标识数据,即【11-15】,请记住此结果,下面会讲解怎么分库查询以下结果。

分库分表必会:跨库分页查询看此一篇就够了

单一DB数据集

把以上所有数据进行拆分打散存放到3个分库中,如下:

分库分表必会:跨库分页查询看此一篇就够了

分库数据集

通过上文介绍,在单一DB中查询limit 5 offset 10,返回了【11-15】结果,那如果在以上三个分库全局查询limit 5 offset 10怎么做?

第一步:语句改写

将 select * from T order by age limit 5 offset 10 改写为 select * from T order by age limit 5 offset 3 , 并投递给所有的分库,注意,这个 offset 的 3,来自于全局offset的总偏移量 10,除以水平切分数据库个数 3。

执行select * from T order by age limit 5 offset 3,结果如下(粉色标识数据),为了便于理解用青黄色标识库表前三条数据:

分库分表必会:跨库分页查询看此一篇就够了

执行limit 5 offset 3数据集(青黄色表库表前三条数据)

第二步:找到返回数据的最小值

  • 第一个库,5 条数据的 age 最小值是10;
  • 第二个库,5 条数据的 age 最小值是 6;
  • 第三个库,5 条数据的 age 最小值是 12;

分库分表必会:跨库分页查询看此一篇就够了

标识结果集最小值

故,三页数据中,age最小值来自第二个库,age_min=6,这个过程只需要比较各个分库第一条数据,时间复杂度很低

第三步:查询二次改写

第一次改写的SQL语句是select * from T order by age  limit 5 offset 3 第二次要改写成一个between语句,between的起点是age_min,between的终点是原来每个分库各自返回数据的最大值:

  • 第一个分库,第一次返回数据的最大值是22 所以查询改写为select * from T order by age where age between age_min and 22
  • 第二个分库,第一次返回数据的最大值是20 所以查询改写为select * from T order by age where age between age_min and 20
  • 第三个分库,第一次返回数据的最大值是25 所以查询改写为select * from T order by age where age between age_min and 25

相对第一次查询,第二次查询条件放宽了,故第二次查询会返回比第一次查询结果集更多的数据,假设这三个分库返回的数据如下:

分库分表必会:跨库分页查询看此一篇就够了

二次查询结果,(深蓝色表示)

分库一的结果集,比第一次多返回了1条数据,上图中深蓝色记录7

由于age_min来自原来的分库二,所以分库二的返回结果集和第一次查询相同,其实这次查询可以省掉

分库三的结果集,比第一次多返回了3条数据,上图中深蓝色记录8,9,10

第四步:找到age_min在全局的offset

在每个结果集中虚拟一个age_min记录,找到age_min在全局的offset

分库分表必会:跨库分页查询看此一篇就够了

标识全局offset

因为查询语句为 limit 5 offset 3 ,所以查询结果集中每个分库的第一条数据offset为4;

  • 分库一中,根据第一次查询条件得出的10的offset是4,查询又返回了一条数据向前推进一位索引,故虚拟age_min在第一个库的offset是2
  • 分库二没有数据变化所以age_min的offset=4
  • 分库三中,根据第一次查询条件得出的12的offset是4,查询又返回了三条数据向前推进三位索引,故虚拟age_min在第三个库的offset是0

因此age_min的全局offset为:2+4+0=6

第五步:查找最终数据

既然得到了age_min在全局的offset,就有了全局视野,根据第二次的结果集,就能够得到全局limit 5 offset 10的记录(下图黄色标识数据【11-15】)

分库分表必会:跨库分页查询看此一篇就够了

标识最终结果数据(黄色表示)

  • 优点:精确返回数据,不会随着页数变大而丢失数据
  • 缺点:需要进行两次数据库查询


Tags:分库分表   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
到底有没有必要分库分表,如何考量的
关于是否需要进行分库分表,可以根据以下考量因素来决定: 数据量和负载:如果数据量巨大且负载压力较大,单一库单一表可能无法满足性能需求,考虑分库分表。 数据增长:预估数据增长...【详细内容】
2024-03-20  Search: 分库分表  点击:(13)  评论:(0)  加入收藏
MyCat分库分表实时同步到GreatSQL
这个事情怎么产生的MyCat作为经典的分库分表中间件,在长时间内被广泛认为是管理超大MySQL数据库集合的有效解决方案。近来接到客户需求,需要将MyCat集群迁移到GreatSQL中,并且...【详细内容】
2024-01-03  Search: 分库分表  点击:(99)  评论:(0)  加入收藏
分库分表必会:跨库分页查询看此一篇就够了
概述随着数据库中数据量日益增多,不得进行分库分表,在分库后将数据分布到不同的数据库实例(甚至物理机器)上,以达到降低数据量,提供系统的处理能力,但是这种架构也带来其他问题,比如...【详细内容】
2023-12-22  Search: 分库分表  点击:(141)  评论:(0)  加入收藏
聊聊分库分表的四种方案
在Java中,有一些常用的技术可用于实现分库分表:1. ShardingSphere:ShardingSphere是一套开源的分布式数据库中间件,提供了完整的分库分表解决方案。它支持基于规则的分片、动态...【详细内容】
2023-08-26  Search: 分库分表  点击:(299)  评论:(0)  加入收藏
MySQL分库分表全攻略:从小白到大神的进阶指南!
大家好,我是小米,一个热爱技术的程序员。今天,我来和大家聊一下关于MySQL中的分库分表技术,相信对于开发者和DBA来说是一个非常重要的话题。 什么是分库分表首先,我们先来了...【详细内容】
2023-06-09  Search: 分库分表  点击:(290)  评论:(0)  加入收藏
大数据时代必备技能——分库分表的原理与应用
什么是分库分表分库分表是指将一个大型的数据库按照一定规则分成多个较小的数据库,并将每个小数据库再分成多个较小的表,以达到提高数据库处理能力和加强数据安全性的目的。...【详细内容】
2023-05-27  Search: 分库分表  点击:(231)  评论:(0)  加入收藏
分库分表的 21 条法则,hold 住!
大家好,我是小富~(一)好好的系统,为什么要分库分表?本文是《分库分表ShardingSphere5.x原理与实战》系列的第二篇文章,距离上一篇文章已经过去好久了,惭愧惭愧~还是不着急实战,咱们先...【详细内容】
2023-05-15  Search: 分库分表  点击:(380)  评论:(0)  加入收藏
别再分库分表了,试试TiDB!
TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和 MySQL 协议,具有数据强一致的高可用特性,是一个不仅适合 OLTP 场景还适合 OLAP...【详细内容】
2023-03-28  Search: 分库分表  点击:(237)  评论:(0)  加入收藏
这些开源的分库分表中间件,你们都知道吗?
当我们的数据达到一定的量级之后,单表甚至单库都无法支撑之时,那么,便会涉及到分库分表。分库分表的方式有多种,开源的解决方案也很多,都是围绕客户端和代理两种模式来处理的。...【详细内容】
2023-03-10  Search: 分库分表  点击:(180)  评论:(0)  加入收藏
一文读懂MySQL分库分表的实现原理和策略
在大型的数据应用场景下,MySQL作为一个关系型数据库管理系统(RDBMS)是非常受欢迎的。然而,MySQL在处理大量数据时会遇到瓶颈,为了解决这个问题,分库分表是一种有效的解决方案。分...【详细内容】
2023-02-24  Search: 分库分表  点击:(116)  评论:(0)  加入收藏
▌简易百科推荐
向量数据库落地实践
本文基于京东内部向量数据库vearch进行实践。Vearch 是对大规模深度学习向量进行高性能相似搜索的弹性分布式系统。详见: https://github.com/vearch/zh_docs/blob/v3.3.X/do...【详细内容】
2024-04-03  京东云开发者    Tags:向量数据库   点击:(4)  评论:(0)  加入收藏
原来 SQL 函数是可以内联的!
介绍在某些情况下,SQL 函数(即指定LANGUAGE SQL)会将其函数体内联到调用它的查询中,而不是直接调用。这可以带来显著的性能提升,因为函数体可以暴露给调用查询的规划器,从而规划器...【详细内容】
2024-04-03  红石PG  微信公众号  Tags:SQL 函数   点击:(3)  评论:(0)  加入收藏
如何正确选择NoSQL数据库
译者 | 陈峻审校 | 重楼Allied Market Research最近发布的一份报告指出,业界对于NoSQL数据库的需求正在持续上升。2022年,全球NoSQL市场的销售额已达73亿美元,预计到2032年将达...【详细内容】
2024-03-28    51CTO  Tags:NoSQL   点击:(13)  评论:(0)  加入收藏
为什么数据库连接池不采用 IO 多路复用?
这是一个非常好的问题。IO多路复用被视为是非常好的性能助力器。但是一般我们在使用DB时,还是经常性采用c3p0,tomcat connection pool等技术来与DB连接,哪怕整个程序已经变成以...【详细内容】
2024-03-27  dbaplus社群    Tags:数据库连接池   点击:(12)  评论:(0)  加入收藏
八个常见的数据可视化错误以及如何避免它们
在当今以数据驱动为主导的世界里,清晰且具有洞察力的数据可视化至关重要。然而,在创建数据可视化时很容易犯错误,这可能导致对数据的错误解读。本文将探讨一些常见的糟糕数据可...【详细内容】
2024-03-26  DeepHub IMBA  微信公众号  Tags:数据可视化   点击:(6)  评论:(0)  加入收藏
到底有没有必要分库分表,如何考量的
关于是否需要进行分库分表,可以根据以下考量因素来决定: 数据量和负载:如果数据量巨大且负载压力较大,单一库单一表可能无法满足性能需求,考虑分库分表。 数据增长:预估数据增长...【详细内容】
2024-03-20  码上遇见你  微信公众号  Tags:分库分表   点击:(13)  评论:(0)  加入收藏
在 SQL 中写了 in 和 not in,技术总监说要炒了我……
WHY?IN 和 NOT IN 是比较常用的关键字,为什么要尽量避免呢?1、效率低项目中遇到这么个情况:t1表 和 t2表 都是150w条数据,600M的样子,都不算大。但是这样一句查询 ↓select *...【详细内容】
2024-03-18  dbaplus社群    Tags:SQL   点击:(5)  评论:(0)  加入收藏
应对慢SQL的致胜法宝:7大实例剖析+优化原则
大促备战,最大的隐患项之一就是慢SQL,对于服务平稳运行带来的破坏性最大,也是日常工作中经常带来整个应用抖动的最大隐患,在日常开发中如何避免出现慢SQL,出现了慢SQL应该按照什...【详细内容】
2024-03-14  京东云开发者    Tags:慢SQL   点击:(4)  评论:(0)  加入收藏
过去一年,我看到了数据库领域的十大发展趋势
作者 | 朱洁策划 | 李冬梅过去一年,行业信心跌至冰点2022 年中,红衫的一篇《适应与忍耐》的报告,对公司经营提出了预警,让各个公司保持现金流,重整团队,想办法增加盈利。这篇报告...【详细内容】
2024-03-12    InfoQ  Tags:数据库   点击:(25)  评论:(0)  加入收藏
SQL优化的七个方法,你会哪个?
一、插入数据优化 普通插入:在平时我们执行insert语句的时候,可能都是一条一条数据插入进去的,就像下面这样。INSERT INTO `department` VALUES(1, '研发部(RD)', &#39...【详细内容】
2024-03-07  程序员恰恰  微信公众号  Tags:SQL优化   点击:(19)  评论:(0)  加入收藏
站内最新
站内热门
站内头条