CN116361297B

CN116361297B - 一种分库分表方法、装置、设备及存储介质

Info

Publication number: CN116361297B
Application number: CN202310541852.0A
Authority: CN
Inventors: 黄睿
Original assignee: Shenzhen Hualei Xuntou Technology Co ltd
Current assignee: Shenzhen Hualei Xuntou Technology Co ltd
Priority date: 2023-05-15
Filing date: 2023-05-15
Publication date: 2023-08-29
Anticipated expiration: 2043-05-15
Also published as: CN116361297A

Abstract

本发明涉及数据库技术领域，公开了一种分库分表方法、装置、设备及存储介质。所述分库分表方法包括：定义虚拟表，以及定义存储表的存储模式，其中，所述存储模式按固定条件、按表主键或者按时间段来定义；在所述存储模式下，定义各个SQL Server数据库中的存储表，并在所述存储表中定义所述存储表的操作类型；定义各个所述SQL Server数据库的数据库连接参数。本发明利用了BulkInsert特性的大批量数据快速插入的能力，利用系统提供的可配置化方式来定义分布式虚拟表，实现自动的分库分表，已缓解服务器磁盘IO的压力，同时缓解系统的压力。

Description

一种分库分表方法、装置、设备及存储介质

技术领域

本发明涉及数据库技术领域，尤其涉及一种分库分表方法、装置、设备及存储介质。

背景技术

目前现有技术中提供一些类似的自动分库分表方法，但实现方法有所不同，并且不能支持可配置方式的动态路由，在应对大批量数据写入时会碰到一些困难。而一些甲方企业公司更多是依赖于工程师对数据库的理解，写一些逻辑性比较固化的SQL脚本去实现。

在现有技术中，对于整体分库分表的设计，需要在系统规划之初就要考虑进去，如果在系统上线后再考虑，会存在数据切换大量耗费时间的问题。并且现有技术只能解决基本的查询、更新、删除和插入操作场景，如果是涉及复杂的逻辑性设计，操作起来很困难。

发明内容

本发明提供了一种分库分表方法、装置、设备及存储介质，用于解决上述提到的技术问题。

本发明第一方面提供了一种分库分表方法，所述分库分表方法包括：所述方法应用于SQL Server数据库，包括：

定义虚拟表，以及定义存储表的存储模式，其中，所述存储模式按固定条件、按表主键或者按时间段来定义；

在所述存储模式下，定义各个SQL Server数据库中的存储表，并在所述存储表中定义所述存储表的操作类型；

定义各个所述SQL Server数据库的数据库连接参数；

在所述虚拟表下定义每个所述SQL Server数据库对应的路由规则，其中，一个SQLServer数据库的数据库连接参数对应一个路由规则；

接收到写入的数据时，通过CLR_VSQL方法函数访问所述虚拟表，获取所述数据所满足的目标路由规则，并将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中。

可选的，在本发明第一方面的第一种实现方式中，所述路由规则包括：根据固定的条件值、根据主键值中的计数器来命中的范围、根据日期值命中的范围。

可选的，在本发明第一方面的第二种实现方式中，所述将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中包括：

根据配置的不同的返回类型参数值，将所述数据与所述返回类型参数值匹配的格式形式，输出至所述CLR_VSQL方法函数的反馈参数中，所述反馈参数再将所述数据以与所述返回类型参数值匹配的格式形式写入至对应的存储表中。

可选的，在本发明第一方面的第三种实现方式中，所述与所述返回类型参数值匹配的格式形式包括：XML格式、Json格式、第一行与第一列的格式。

可选的，在本发明第一方面的第四种实现方式中，所述数据库连接参数包括：每一个数据库变量的服务器名、数据库名、数据库用户名、数据库密码。

可选的，在本发明第一方面的第五种实现方式中，所述将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中包括：

基于Bulk Insert的方式向所述存储表写入数据，其中，包括：将变量表名参数赋值为一具体的表变量名，再根据配置的不同的运行类型参数值，将与所述运行类型参数值所对应的所述格式形式的数据字串赋值给SqlOrData参数，所述SqlOrData参数将所述数据字串写入对应的数据库表中。

本发明还提供一种分库分表装置，所述分库分表装置包括：

第一定义单元，用于定义虚拟表，以及定义存储表的存储模式，其中，所述存储模式按固定条件、按表主键或者按时间段来定义；

第二定义单元，用于在所述存储模式下，定义各个SQL Server数据库中的存储表，并在所述存储表中定义所述存储表的操作类型；

第三定义单元，用于定义各个所述SQL Server数据库的数据库连接参数；

第四定义单元，用于在所述虚拟表下定义每个所述SQL Server数据库对应的路由规则，其中，一个SQL Server数据库的数据库连接参数对应一个路由规则；

数据处理单元，用于接收到写入的数据时，通过CLR_VSQL方法函数访问所述虚拟表，获取所述数据所满足的目标路由规则，并将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中。

本发明还提供一种分库分表设备，所述分库分表设备包括：存储器和至少一个处理器，所述存储器中存储有指令；

所述至少一个处理器调用所述存储器中的所述指令，以使得所述分库分表设备执行上述所述的分库分表方法。

本发明还提供一种计算机可读存储介质，所述计算机可读存储介质上存储有指令，所述指令被处理器执行时实现上述所述的分库分表方法。

与现有技术相比，本发明的有益效果是：本发明提供的一种分库分表方法、装置、设备及存储介质，通过可配置的分布式虚拟表方式，提前在系统中定义好分库分表的规则，结合CLR_VSQL方法函数，实现的一套完善的分库分表的方法，本发明实现了自动的分库分表，缓解了服务器磁盘IO的压力，同时缓解了系统的压力。

附图说明

图1为本发明实施例中分库分表方法的一个实施例示意图；

图2为本发明实施例中分库分表装置的一个实施例示意图。

具体实施方式

本发明实施例提供了一种分库分表方法、装置、设备及存储介质，用于解决大数据高并发的技术问题。本发明的说明书和权利要求书及上述附图中的术语“第一”、“第二”、“第三”、“第四”等（如果存在）是用于区别类似的对象，而不必用于描述特定的顺序或先后次序。应该理解这样使用的数据在适当情况下可以互换，以便这里描述的实施例能够以除了在这里图示或描述的内容以外的顺序实施。此外，术语“包括”或“具有”及其任何变形，意图在于覆盖不排他的包含，例如，包含了一系列步骤或单元的过程、方法、系统、产品或设备不必限于清楚地列出的那些步骤或单元，而是可包括没有清楚地列出的或对于这些过程、方法、产品或设备固有的其它步骤或单元。

可以理解的是，本发明的执行主体可以为分库分表装置，还可以是终端或者服务器，具体此处不做限定。本发明实施例以服务器为执行主体为例进行说明。

在介绍本发明的具体实施例之前，先对本发明的大致思路进行阐述：

本发明利用了SQL Server数据库本身的CLR服务器，使用CLR服务创建一个可支持多种模式的方法函数，用户只需要在系统后台完成参数配置和规则定义后，运行此函数（CLR_VSQL），并且传入函数所需的各种参数后，即可以实现系统自动分服分库分表的操作。

本发明支持SQL Server数据库常用的一些基本操作，包括Select（查询）、Insert（写入）、Update（更新）、Delete（删除）等操作，还支持利用Bulk Insert技术的能力批量写入数据。（注：Bulk Insert数据库中可以通过把大数据文件（txt、csv）等快速写入数据库表或者视图中的一种技术）。

本发明支持两种运行模式，一种通过数据库连接参数直接访问数据库的方式，另外一种是通过WCF通讯协议来访问数据库的方式，两种方式都支持跨服务器使用。

图1为本发明实施例中分库分表方法的一个实施例示意图，参阅图1，本发明的一实施例提供一种分库分表方法，所述方法应用于SQL Server数据库，包括：

S1：定义虚拟表，以及定义存储表的存储模式，其中，所述存储模式按固定条件、按表主键或者按时间段来定义；

S2：在所述存储模式下，定义各个SQL Server数据库中的存储表，并在所述存储表中定义所述存储表的操作类型；

S3：定义各个所述SQL Server数据库的数据库连接参数；

S4：在所述虚拟表下定义每个所述SQL Server数据库对应的路由规则，其中，一个SQL Server数据库的数据库连接参数对应一个路由规则；

S5：接收到写入的数据时，通过CLR_VSQL方法函数访问所述虚拟表，获取所述数据所满足的目标路由规则，并将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中。

其中，在本实施例中，通过VTAB（虚函数表）数据表变量定义插件实现对存储表的存储模式定义、存储表的操作类型的定义、路由规则的定义、数据库连接参数的定义。

定义存储表的存储模式包括：

0-固定名称：

分布式存储列表中的每一个固定名称对应一个固定的条件值。当SQL操作是传入的条件值与之匹配，则路由生效。

1-主键序列：

接收Insert时的主键计数器，当该计数器达到分布式存储列表中的某一项的计数器范围之内，则该路由生效。

2-Y年，3-Y年M月，4-Y年W周，5-D年月日：

接收Insert时的插入时间，当该时间匹配到分布式存储列表中的某一项的时间范围之内，则该路由生效。

存储表的操作类型的定义包括：自动创建表与索引的定义和表变量的权限支持的定义。

具体的，自动创建表不索引的定义：必须为每一个表变量提供一套创建表不索引的脚本，其中与表名相关的字符，必须用#T#标识符加以替换。同时必须定义模板表名字段。

表变量的权限支持的定义：每一个表允许针对5种SQL操作进行权限的定义，可以允许或拒绝某一个操作语句，以提升系统的安全性。

对路由规则的定义包括：

1.在本页签之内，定义数据库连接参数以及路由条件：

针对0模式：可定义选择条件值-不物理表名，当选择条件值为空时，该表为默认表。

针对1模式：可定义主键计数据的起始值不物理表名，当这起始值为空时，该表为默认表。

针对2，3，4模式：无需定义物理表名，系统将根据定义的起始日期，根据规则自劢生成表。当起始日期为空时，该表为默认表。

默认表：作用为：当任何条件都不满足时。数据将插入到默认表中。

2.关于2，3，4，5分布式存储模式的动态表名生成规则：

2模式模板表名+Y22 （TestTB Y22第2022年的表）

3模式模板表名+Y22M06 （TestTB Y22M06第2022年6月的表）

4模式模板表名+Y22W27 （TestTB Y22W27第2022年27周的表）

5模式模板表名+D220703 （TestTB D220703第2022年7月3日的表）

已经创建的物理表名，将统一保存到“已经生成的物理表名清单”字段中。

数据库连接参数的定义：

在上述页签中，定义数据库连接的ADO.NET的连接字符串，目前支持SQL-Server的连接字串。

具体的，CLR_VSQL方法函数根据VTAB配置的规则运行4大SQL语句或是批量插入数据，并返回4类数据结果（SQL行集、XML、JSON、文本）。

在S5中，以下是通过CLR_VSQL方法函数选择目标路由的应用示例：

（1）0模式的运行示例：根据固定的条件值选择路由。

1.创建一个[TB:TestV]表变量，将其存储模式配置为0；

2.创建两个路由条件，当条件值为“OrBit时”将数据插入到TestVOrBit表，当条件值为“HuaWei”将数据插入到TestVHuanWei表；

3.演示Insert的命令，传入不同的@ConditionValue条件，数据插入到不同的物理表。

4.演示Select 的命令，传入不同的条件，数据将从不同的表中返回数据集，当@ConditionValue为空时，将从每一个路由上的物理表中返回数据，当Select命令中包含<S>标记符时，以观察到数据来自于哪个路由。

5.当在0模式运行Update，Delete命令时，如果@ConditionValue的值与路由条件匹配时，则在该路由中运行。

（2）1模式的运行示例：用主键值中的计数器来命中的范围选择路由。

创建一个[TB:TestV]表变量，将其存储模式配置为1。

2.创建两个路由条件，路由一：主键的起始范围1-100，路由2：主键的起始范围为101-200。

3.演示Insert的命令，传入不同@InsertPKId，系统将分析其中的主键计数器的值，并将它匹配既定的路由条件。

4.演示Select的命令，将从每一个路由上的物理表中返回数据，当Select命令中包含<S>标记符时，以观察到数据来自于哪个路由。

5.当在1模式运行Update，Delete命令时，将在所有的路由列表中去运行。

（3）2，3，4模式的运行示例：用日期值命中的范围选择路由。

1.创建一个[TB:TestVD]表变量，将其存储模式配置为4(年+周)；

2.创建两个路由条件，日期起始22-01-01到22-6-30为路由1日期起始22-07-01到22-12-30为路由2；

3.演示Insert的命令，传入不同创建时间@CreateDate，数据将插入到不同的物理表（2模式：表名_Y年，3模式：表名_Y年M月，4模式：表名_Y年W周)，当@CreateTime为空时，将取系统当前的时间为插入时间。

5.当在2，3，4模式运行Update，Delete命令时，将在所有的路由列表中去运行。

其中，上述内容提到的参数解释：

@ConditionValue：0模式时的选择条件值，当0-模式时，该值为路由选择条件值；

@InsertPKId：1模式时的插入的主键或计数器；

1模式时，开始主建值(12位标准主键，或是Bigint型数字)；

@CreateTime：2，3，4模式时，插入数据的时间；

当它为空时，将取前实时的插入时间。

在一实施例中，所述路由规则包括：根据固定的条件值、根据主键值中的计数器来命中的范围、根据日期值命中的范围。

在一实施例中，所述将所述数据写入至所述目标路由规则所对应的所述SQLServer数据库中的存储表中包括：

其中，所述返回类型参数值为@ReturnType，具体@ReturnType返回数据的类型：

@ReturnType=1，返回标准的结果集；

@ReturnType=2，向I_ReturnMessageOrData返回XML结果集；

@ReturnType=3，向I_ReturnMessageOrData返回JSON结果集；

@ReturnType=4，向I_ReturnMessageOrData返回表的第一行第一个单元格数据。

所述CLR_VSQL方法函数的反馈参数为@I_ReturnMessageOrData，

具体的，@I_ReturnMessageOrData返回的消息或数据(ReturnType=2，3，4时分别返回XML，JSON，单个文本的结果集)；

具体的实现步骤如下：

1.系统默认采用SQL的标准行集来输入数据，此种模式通过参数@ReturnType=1（或留空）来实现，此为系统默认方式；

2.当@ReturnType=2将结果集以XML的格式输出到@I_ReturnMessageOrData变量中；

3.当@ReturnType=3将结果集以JSON的格式输出到@I_ReturnMessageOrData变量中；

4.当@ReturnType=4将结果集的第一行与第一列的值，以文本的方式输出到@I_ReturnMessageOrData变量中。

其中，运行类型参数为@RunType，具体的，

@RunType=1，在集群表上运行SQL并返回数据集，

@RunType=2，在本地运行SQL之后，将数据BulkInsert到集群表，

@RunType=3，将XML数据BulkInsert到集群表，

@RunType=4，将JSON数据BulkInsert到集群表，

说明：

当RunType=1：只运行最基本的SQL语句(Select, Insert, Update, Delete)将从@SqlOrData中的[TB:xxxxx]去解析VariableTableName，

当RunType=2，3，4：必须指定VariableTableName。

SqlOrData参数为@SqlOrData：运行的SQL指令或是BlukInsert批量插入的数据（当RunType=1传SQL命令，当RunType=2传XML，当RunType=3传JSON）。

本发明提供了3个基于BulkInsert方式的高速数据插入的方法。

具体的步骤如下：

1.采用SQL的结果集，向数据表变量中插入。比如：我们要将如表TestV的数据，一次性插入到目标表中。需要将@RunType配置为2，同时需要将@VariableTableName赋值为一具体的表变量名，最后在@SqlOrData赋值为一个取数的Select语句。

2.将一个XML数据集，向数据表变量中插入。比如：我们要将从表TestV所取得的XML数据，一次性插入到目标表中。需要将@RunType配置为3，同时需要将@VariableTableName赋值为一具体的表变量名，最后在@SqlOrData赋值为一个XML数据字串。

3.将一个JSON数据集，向数据表变量中插入。比如：我们要将从表TestV的取得的JSON数据，一次性插入到目标表中。需要将@RunType配置为4，同时需要将@VariableTableName赋值为一具体的表变量名，最后在@SqlOrData赋值为一个JSON数据字串。

其中，@VariableTableName：BulkInser批量插入模式时的表变量名当RunType不等于1时，在BulkInsert模式之下必须指定VariableTableName。

本发明实施例中，有益效果：本发明通过可配置化的方式来灵活定义“数据表变量”以及“数据库连接参数”根据路由访问规则达到在分库分表集群之上运行4大基本SQL语句（Select、Insert、Update、Delete）+批量数据插入BulkInsert的能力，本发明仅仅需要使用者掌握SQL语法，并在存储过程的逻辑中就能低成本的实现解决大数据高并发的技术问题。

在另一实施例中，本发明还提供一种分库分表装置1，所述分库分表装置1包括：

第一定义单元10，用于定义虚拟表，以及定义存储表的存储模式，其中，所述存储模式按固定条件、按表主键或者按时间段来定义；

第二定义单元11，用于在所述存储模式下，定义各个SQL Server数据库中的存储表，并在所述存储表中定义所述存储表的操作类型；

第三定义单元12，用于定义各个所述SQL Server数据库的数据库连接参数；

第四定义单元13，用于在所述虚拟表下定义每个所述SQL Server数据库对应的路由规则，其中，一个SQL Server数据库的数据库连接参数对应一个路由规则；

数据处理单元14，用于接收到写入的数据时，通过CLR_VSQL方法函数访问所述虚拟表，获取所述数据所满足的目标路由规则，并将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中。

本发明还提供一种分库分表设备，所述分库分表设备包括存储器和处理器，存储器中存储有计算机可读指令，计算机可读指令被处理器执行时，使得处理器执行上述各实施例中的所述分库分表方法的步骤。

本发明还提供一种计算机可读存储介质，该计算机可读存储介质可以为非易失性计算机可读存储介质，该计算机可读存储介质也可以为易失性计算机可读存储介质，所述计算机可读存储介质中存储有指令，当所述指令在计算机上运行时，使得计算机执行所述分库分表方法的步骤。

本发明提供的一种分库分表方法、装置、设备及存储介质，通过可配置话的分布式虚拟表方式，提前在系统中定义好分库分表的规则，结合系统提供的CLR_VSQL方法，实现的一套完善的分库分表的方法，本发明实现了自动的分库分表，缓解了服务器磁盘IO的压力，同时缓解了系统的压力。

所属领域的技术人员可以清楚地了解到，为描述的方便和简洁，上述描述的系统，装置和单元的具体工作过程，可以参考前述方法实施例中的对应过程，在此不再赘述。

所述集成的单元如果以软件功能单元的形式实现并作为独立的产品销售或使用时，可以存储在一个计算机可读取存储介质中。基于这样的理解，本发明的技术方案本质上或者说对现有技术做出贡献的部分或者该技术方案的全部或部分可以以软件产品的形式体现出来，该计算机软件产品存储在一个存储介质中，包括若干指令用以使得一台计算机设备（可以是个人计算机，服务器，或者网络设备等）执行本发明各个实施例所述方法的全部或部分步骤。而前述的存储介质包括：U盘、移动硬盘、只读存储器（read-only memory，ROM）、随机存取存储器（random access memory，RAM）、磁碟或者光盘等各种可以存储程序代码的介质。

以上所述，以上实施例仅用以说明本发明的技术方案，而非对其限制；尽管参照前述实施例对本发明进行了详细的说明，本领域的普通技术人员应当理解：其依然可以对前述各实施例所记载的技术方案进行修改，或者对其中部分技术特征进行等同替换；而这些修改或者替换，并不使相应技术方案的本质脱离本发明各实施例技术方案的精神和范围。

Claims

1.一种分库分表方法，所述方法应用于SQL Server数据库，其特征在于，包括：

定义各个所述SQL Server数据库的数据库连接参数；

接收到写入的数据时，通过CLR_VSQL方法函数访问所述虚拟表，获取所述数据所满足的目标路由规则，并将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中；

所述将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中包括：

根据配置的不同的返回类型参数值，将所述数据与所述返回类型参数值匹配的格式形式，输出至所述CLR_VSQL方法函数的反馈参数中，所述反馈参数再将所述数据以与所述返回类型参数值匹配的格式形式写入至对应的存储表中；其中，所述CLR_VSQL方法函数为一个可支持多种模式的方法函数，用户只需要在系统后台完成参数配置和规则定义后，运行CLR_VSQL方法函数，并且传入CLR_VSQL方法函数所需的各种参数后，并实现自动分服分库分表的操作；

所述返回类型参数值为@ReturnType，具体@ReturnType返回数据的类型：

@ReturnType=1，返回标准的结果集；

@ReturnType=2，向I_ReturnMessageOrData返回XML结果集；

@ReturnType=3，向I_ReturnMessageOrData返回JSON结果集；

@ReturnType=4，向I_ReturnMessageOrData返回表的第一行第一个单元格数据；

所述CLR_VSQL方法函数的反馈参数为@I_ReturnMessageOrData，

具体的，@I_ReturnMessageOrData返回的消息或数据；ReturnType=2，3，4时分别返回XML，JSON，单个文本的结果集；

具体的实现步骤如下：

系统默认采用SQL的标准行集来输入数据，此种模式通过参数@ReturnType=1或留空来实现，此为系统默认方式；

当@ReturnType=2将结果集以XML的格式输出到@I_ReturnMessageOrData变量中；

当@ReturnType=3将结果集以JSON的格式输出到@I_ReturnMessageOrData变量中；

当@ReturnType=4将结果集的第一行与第一列的值，以文本的方式输出到@I_ReturnMessageOrData变量中。

2.根据权利要求1所述的方法，其特征在于，所述路由规则包括：根据固定的条件值、根据主键值中的计数器来命中的范围、根据日期值命中的范围。

3.根据权利要求1所述的方法，其特征在于，所述与所述返回类型参数值匹配的格式形式包括：XML格式、Json格式、第一行与第一列的格式。

4.根据权利要求1所述的方法，其特征在于，所述数据库连接参数包括：每一个数据库变量的服务器名、数据库名、数据库用户名、数据库密码。

5.一种分库分表装置，其特征在于，所述分库分表装置包括：

数据处理单元，用于接收到写入的数据时，通过CLR_VSQL方法函数访问所述虚拟表，获取所述数据所满足的目标路由规则，并将所述数据写入至所述目标路由规则所对应的所述SQL Server数据库中的存储表中；

数据处理单元，具体用于：

@ReturnType=1，返回标准的结果集；

@ReturnType=2，向I_ReturnMessageOrData返回XML结果集；

@ReturnType=3，向I_ReturnMessageOrData返回JSON结果集；

所述CLR_VSQL方法函数的反馈参数为@I_ReturnMessageOrData，

具体的实现步骤如下：

6.一种分库分表设备，其特征在于，所述分库分表设备包括：存储器和至少一个处理器，所述存储器中存储有指令；

所述至少一个处理器调用所述存储器中的所述指令，以使得所述分库分表设备执行如权利要求1-4中任一项所述的分库分表方法。

7.一种计算机可读存储介质，所述计算机可读存储介质上存储有指令，其特征在于，所述指令被处理器执行时实现如权利要求1-4中任一项所述的分库分表方法。