A data table sharding method, device, equipment and medium

Abstract

The present application relates to the field of database technology, and provides a data table sharding method, device, equipment and medium, which are used to improve the efficiency of data table sharding when migrating a data table from a centralized database to a distributed database. The method includes: determining the data volume and the proportion of commonly used fields of a target data table to be migrated in a centralized database, and the target data table includes multiple fields; if the data volume is greater than a first preset data volume, and the proportion of commonly used fields is less than the first preset proportion, vertical sharding is used for the target data table, and vertical sharding means storing multiple data sub-tables split from the target data table in one node of the distributed database; if the data volume is greater than the first preset data volume, and the proportion of commonly used fields is greater than the second preset proportion, horizontal sharding is used for the target data table, and horizontal sharding means storing multiple data sub-tables split from the target data table in different nodes of the distributed database.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a data table sharding method, device, equipment and medium. Background Art

[0002] At present, most commercial banking systems are still using traditional centralized databases. However, centralized databases have low scalability and are increasingly unable to meet the needs of large commercial banking systems. Therefore, large commercial banking systems urgently need to use distributed databases to replace centralized databases, which means that data tables in centralized databases need to be migrated to distributed databases.

[0003] Since distributed databases still have certain performance defects in transaction processing and cross-node multi-table joins (JOIN), it is necessary to fully consider the advantages and disadvantages of distributed databases and design a model that is more suitable for distributed databases. The sharding link is one of the most important links in the design of distributed database models.

[0004] At present, database administrators mainly choose a specific sharding method for each data table based on their experience during the sharding process, and then store the sharded multiple data sub-tables in a distributed database. This manual data table sharding is inefficient. Summary of the invention

[0005] Embodiments of the present application provide a data table sharding method, apparatus, device, and medium for improving the efficiency of data table sharding when migrating a data table from a centralized database to a distributed database.

[0006] In a first aspect, the present application provides a data table sharding method, the method comprising:

[0007] Determine the data volume and the proportion of commonly used fields of the target data table to be migrated in the centralized database, wherein the target data table includes multiple fields, and the proportion of commonly used fields represents the ratio of the number of commonly used fields to the number of the multiple fields;

[0008] If the data volume is greater than a first preset data volume, and the proportion of the commonly used fields is less than the first preset proportion, vertical sharding is adopted for the target data table, wherein the vertical sharding means storing multiple data sub-tables split from the target data table in one node of the distributed database;

[0009] If the data volume is greater than the first preset data volume, and the proportion of the commonly used fields is greater than the second preset proportion, horizontal sharding is adopted for the target data table, wherein the second preset proportion is greater than or equal to the first preset proportion, and the horizontal sharding means storing the multiple data sub-tables split from the target data table in different nodes of the distributed database.

[0010] In a possible embodiment, the method further includes:

[0011] If the data volume is less than a second preset data volume, determining the number of table accesses of the target data table, wherein the second preset data volume is less than the first preset data volume;

[0012] If the table access times are greater than a preset times, copying the target data table to obtain multiple copy tables;

[0013] The multiple replicated tables are stored in each node of the distributed database.

[0014] In a possible embodiment, the method further includes:

[0015] If the data volume is greater than the second preset data volume and less than the first preset data volume, determining the number of table accesses of the target data table;

[0016] If the table access times are greater than a preset times, the vertical sharding and the horizontal sharding are used for the target data table.

[0017] In a possible embodiment, horizontal sharding is adopted for the target data table, including:

[0018] Based on the association information of each field, determining a sharding field from the multiple fields, wherein the association information of each field includes the data volume of the field association table, the number of field accesses, the field distinction, and whether the field is a partition key;

[0019] According to different values ​​of the sharding field, the target data table is split into multiple data sub-tables, and the multiple data sub-tables are stored in different nodes of the distributed database.

[0020] In a possible embodiment, determining a fragmentation field from the multiple fields based on association information of each field includes:

[0021] Calculate the product of the data volume, field access times, field discrimination and preset weight of the field association table of each field to obtain the calculated value of each field; wherein the preset weight of each field is determined according to whether each field is a partition key;

[0022] A field having the largest calculated value among the multiple fields is determined as the partition field.

[0023] In a possible embodiment, the first preset data amount and the second preset data amount are determined in the following manner:

[0024] Sorting the data volumes of all data tables in the centralized database;

[0025] In order from large to small, determining the data amount corresponding to the first ratio as the first preset data amount;

[0026] In order from small to large, the data amount corresponding to the first ratio is determined as the second preset data amount.

[0027] In a second aspect, the present application provides a device based on data table sharding, the device comprising:

[0028] A determination module, used to determine the data volume and the proportion of commonly used fields of a target data table to be migrated in a centralized database, wherein the target data table includes multiple fields, and the proportion of commonly used fields represents the ratio of the number of commonly used fields to the number of the multiple fields;

[0029] A sharding module, configured to adopt vertical sharding for the target data table if the data volume is greater than a first preset data volume and the proportion of the commonly used fields is less than the first preset proportion, wherein the vertical sharding means storing multiple data sub-tables split from the target data table in one node of a distributed database;

[0030] The sharding module is also used to adopt horizontal sharding for the target data table if the data volume is greater than the first preset data volume and the proportion of the commonly used fields is greater than the second preset proportion, wherein the second preset proportion is greater than or equal to the first preset proportion, and the horizontal sharding means storing the multiple data sub-tables split from the target data table in different nodes of the distributed database.

[0031] In a possible embodiment, the determination module is further used to: determine the number of table accesses of the target data table if the data amount is less than a second preset data amount, wherein the second preset data amount is less than the first preset data amount;

[0032] The sharding module is also used for: if the number of table accesses is greater than a preset number, copying the target data table to obtain multiple copy tables; and storing the multiple copy tables in each node of the distributed database.

[0033] In a possible embodiment, the determining module is further used to: if the data volume is greater than the second preset data volume and less than the first preset data volume, determine the number of table accesses of the target data table;

[0034] The sharding module is further used for: if the number of table accesses is greater than a preset number, adopting the vertical sharding and the horizontal sharding for the target data table.

[0035] In a possible embodiment, the determination module is further used to: determine the sharding field from the multiple fields based on the association information of each field, wherein the association information of each field includes the data volume of the field association table, the number of field accesses, the field distinction, and whether the field is a partition key;

[0036] The sharding module is specifically used to split the target data table into multiple data sub-tables according to different values ​​of the sharding field, and store the multiple data sub-tables in different nodes of the distributed database.

[0037] In a possible embodiment, the determining module is specifically configured to:

[0038] Calculate the product of the data volume, field access times, field discrimination and preset weight of the field association table of each field to obtain the calculated value of each field; wherein the preset weight of each field is determined according to whether each field is a partition key;

[0039] A field having the largest calculated value among the multiple fields is determined as the partition field.

[0040] In a possible embodiment, the determining module is further used for:

[0041] Sorting the data volumes of all data tables in the centralized database;

[0042] In order from large to small, determining the data amount corresponding to the first ratio as the first preset data amount;

[0043] In order from small to large, the data amount corresponding to the first ratio is determined as the second preset data amount.

[0044] In a third aspect, the present application provides an electronic device, including:

[0045] A memory for storing program instructions;

[0046] The processor is used to call the program instructions stored in the memory, and execute any one of the methods in the first aspect according to the obtained program instructions.

[0047] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, wherein the computer program includes program instructions, and when the program instructions are executed by a computer, the computer executes any one of the methods described in the first aspect.

[0048] In a fifth aspect, the present application provides a computer program product, comprising: a computer program code, when the computer program code is run on a computer, the computer executes any one of the methods described in the first aspect.

[0049] In an embodiment of the present application, when migrating a data table from a centralized database to a distributed database, the data volume and the proportion of commonly used fields of the target data table to be migrated in the centralized database are determined. If the data volume is greater than a first preset data volume and the proportion of commonly used fields is less than the first preset proportion, vertical sharding is adopted for the target data table. If the data volume is greater than the first preset data volume and the proportion of commonly used fields is greater than the second preset proportion, horizontal sharding is adopted for the target data table. A specific sharding principle is provided, and whether vertical sharding or horizontal sharding is adopted for the data table is determined according to the data volume and commonly used fields. Compared with the manual sharding method of data table, the efficiency of data table sharding can be improved, and the migration efficiency of the data table can be further improved. The manual method relying on experience is not accurate, and the accuracy of data table sharding can also be improved.

[0050] The technical effects that can be achieved in each of the above-mentioned second to fifth aspects and each of the above-mentioned aspects refer to the technical effects that can be achieved in the above-mentioned first aspect or various possible schemes in the first aspect, and will not be repeated here. BRIEF DESCRIPTION OF THE DRAWINGS

[0051] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the related technologies, the drawings required for use in the embodiments or the related technical descriptions are briefly introduced below. Obviously, the drawings described below are only the embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0052] Figure 1 A schematic diagram of an application scenario of a data table sharding method provided in an embodiment of the present application;

[0053] Figure 2 A schematic diagram of a data table sharding method provided in an embodiment of the present application Figure 1 ;

[0054] Figure 3 A schematic diagram of a data table sharding method provided in an embodiment of the present application Figure 2 ;

[0055] Figure 4 A structural diagram of a data table sharding device provided in an embodiment of the present application;

[0056] Figure 5 A structural diagram of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0057] To make the purpose, technical scheme and advantages of the present invention clearer, the technical scheme in the embodiment of the present application will be clearly and completely described below in conjunction with the drawings in the embodiment of the present invention. Obviously, the described embodiment is only a part of the embodiment of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of the present application. In the absence of conflict, the embodiments in the present application and the features in the embodiments can be combined with each other arbitrarily. In addition, although the logical order is shown in the flow chart, in some cases, the steps shown or described can be performed in an order different from that here.

[0058] The terms "first" and "second" in the specification and claims of this application and the above-mentioned drawings are used to distinguish different objects, rather than to describe a specific order. In addition, the term "comprising" and any variation thereof are intended to cover non-exclusive protection. For example, a process, method, system, product or device comprising a series of steps or units is not limited to the listed steps or units, but optionally includes steps or units that are not listed, or optionally includes other steps or units inherent to these processes, methods, products or devices.

[0059] In the embodiment of the present application, "multiple" may mean at least two, for example, two, three or more, and the embodiment of the present application does not limit this.

[0060] To achieve the migration of data tables from centralized databases to distributed databases, in order to meet the structure of distributed databases, data tables are usually required to be sharded, and multiple data sub-tables after sharding are stored in distributed databases. In the sharding stage, developers mainly study the code and discuss with professional data administrators (DBAs) to decide how to shard. The process of manually sharding data tables requires a lot of time and effort, and the sharding efficiency is low.

[0061] In order to improve the efficiency of data table sharding when migrating a data table from a centralized database to a distributed database, an embodiment of the present application provides a data table sharding method. The method comprehensively determines whether to use vertical sharding or horizontal sharding based on the amount of data in the data table and the proportion of commonly used fields, thereby improving the efficiency and accuracy of data table sharding.

[0062] The following briefly introduces the application scenarios to which the technical solutions of the embodiments of the present application can be applied. It should be noted that the application scenarios introduced below are only used to illustrate the embodiments of the present application and are not limited. In the specific implementation process, the technical solutions provided by the embodiments of the present application can be flexibly applied according to actual needs.

[0063] Please refer to Figure 1, is a schematic diagram of an application scenario of a data table sharding method provided in an embodiment of the present application, the application scenario including a data table 110 and a data table sharding device 120.

[0064] The data table sharding device 120 can be implemented by a terminal or a server, such as a mobile terminal, a fixed terminal or a portable terminal, such as a mobile phone, a site, a unit, a device, a multimedia computer, a multimedia tablet, an Internet node, a communicator, a desktop computer, a notebook computer, a tablet computer, a personal communication system (PCS) device, a positioning device or any combination thereof, including accessories and peripherals of these devices or any combination thereof. The server can be an independent physical server, or a server cluster or a distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms, but is not limited to this.

[0065] After the data table sharding device 120 obtains the data table 110 to be migrated from the centralized database, it shards the data table 110 and then stores it in the distributed database. The specific sharding process of the data table sharding device 120 will be introduced below.

[0066] As described above, the application scenarios of the embodiments of the present application are described below. Figure 1 The data table sharding device in the example is used to implement the data table sharding method. Please refer to Figure 2 , is a flow chart of a data table sharding method provided in the embodiment of the present application. All implementation methods of the embodiment of the present application comply with the relevant provisions of national laws and regulations on data acquisition, storage, use, and processing.

[0067] S201. Determine the data volume and proportion of commonly used fields in the target data table that needs to be migrated in the centralized database.

[0068] In the process of migrating data tables from a centralized database to a distributed database, it may not be necessary to migrate all data tables in the centralized database for some reasons, for example, the user only wants to migrate data tables from three days ago, or the user only wants to migrate data tables of a certain department, etc. Therefore, in a possible embodiment, the data table sharding device can use certain screening means to perform pre-filtering to determine the data tables that need to be migrated, so that the data finally migrated meets the actual migration needs of the user.

[0069] Specifically, the data table sharding device determines whether there is a screening condition, wherein the screening condition can be automatically set by the data table sharding device, or can be manually set by the user, and the screening condition is a data table that meets the preset condition, such as a data table 3 days ago, a data table within 3 days from the current date, or a data table of the finance department. If the screening condition exists, the data table sharding device filters out the data tables that need to be migrated from all the data tables in the centralized database according to the screening condition. If the screening condition does not exist, the data table sharding device determines all the data tables in the centralized database as the data tables that need to be migrated.

[0070] The target data table is any data table in the data table to be migrated, and the target data table includes multiple fields. After the data table sharding device determines the target data table, it can determine the data volume and the proportion of common fields in the target data table. For example, the data volume of the target data table can be queried through the following SQL statement:

[0071] SELECT NUM_ROWS FROM DBA_TABLES WHERE OWNER='XX'AND TABLE_NAME='XXXX';

[0072] Commonly used fields may be pre-set by a centralized database. Commonly used fields of different centralized databases may be the same or different. The commonly used fields of the target data table can be obtained by using a SQL parser to parse the data dictionary of the SQL execution history. Commonly used fields are, for example, customer number CMAVY_ID, customer name CMAVY_NM, etc. The proportion of commonly used fields indicates the ratio of the number of commonly used fields to the number of multiple fields. For example, if the target data table has 50 fields, of which there are 2 commonly used fields, the proportion of commonly used fields is 1 / 25.

[0073] S202: If the data volume is greater than a first preset data volume, and the proportion of commonly used fields is less than the first preset proportion, vertical sharding is adopted for the target data table.

[0074] Specifically, after the data table sharding device obtains the data volume and the proportion of commonly used fields in the target data table, it compares the data volume with the first preset data volume, and compares the proportion of commonly used fields with the first preset proportion. The first preset proportion is a preset fixed proportion, for example, the first preset proportion is 70%. The first preset data volume can be a preset fixed data volume, for example, the first preset data volume is 20 million. Taking into account the different data volumes of different centralized databases, the first preset data volume can also be dynamically determined. Specifically, the data table sharding device can sort the data volumes of all data tables in the centralized database in order from large to small, and determine the data volume corresponding to the first ratio as the first preset data volume.

[0075] For example, the first ratio is 20%. There are 10 data tables in the centralized database. The data volumes of the 10 data tables are sorted in descending order, namely, data table 1, data table 2, ..., data table 10. The data volume corresponding to 20% is the data volume of data table 2. The data volume of data table 2 is 20 million. The first preset data volume is 20 million.

[0076] In the first case, when the data volume of the target data table is greater than the first preset data volume, it means that the data volume of the target data table is large, and the proportion of commonly used fields in the target data table is less than the first preset proportion, which means that the proportion of commonly used fields in the target data table is small, then vertical sharding is adopted for the target data table.

[0077] Vertical sharding means storing multiple data sub-tables split from the target data table in a node of the distributed database. Specifically, the data table sharding device can divide the fields belonging to a business type into a data sub-table according to the business type to which each field in the multiple fields of the target data table belongs, thereby splitting the target data table into multiple data sub-tables, and storing the multiple data sub-tables in a node of the distributed database.

[0078] For example, a target data table has 50 fields, 10 of which belong to business type A and are stored in data sub-table A, 10 fields belong to business type B and are stored in data sub-table B, and the remaining 30 fields belong to business type C and are stored in data sub-table C. Data sub-table A, data sub-table B and data sub-table C are all stored on a node of the distributed database.

[0079] S203: If the data volume is greater than the first preset data volume, and the proportion of commonly used fields is greater than the second preset proportion, horizontal sharding is adopted for the target data table.

[0080] In the second case, when the data volume of the target data table is greater than the first preset data volume, it means that the data volume of the target data table is large. Please refer to the previous discussion for the determination of the first preset quantity, which will not be repeated here. The proportion of commonly used fields in the target data table is greater than the second preset proportion, which means that the proportion of commonly used fields in the target data table is small, and horizontal sharding is used for the target data table. The second preset proportion is greater than or equal to the first preset proportion. The second preset proportion is the same as the first preset proportion, which is also a preset fixed proportion. For example, the first preset proportion is 70% and the second preset proportion is 30%.

[0081] The following describes how to use horizontal sharding for the target data table.

[0082] Horizontal sharding means splitting the target data table into multiple data sub-tables and storing them in different nodes of the distributed database. The data table sharding device can determine the sharding field from multiple fields based on the association information of each field in the target data table, split the target data table into multiple data sub-tables according to different values ​​of the sharding field, and store the multiple data sub-tables in different nodes of the distributed database.

[0083] The associated information of each field includes the data volume of the field association table, the number of field accesses, the field distinction, and whether the field is a partition key. Examples are given below to introduce each of these.

[0084] (1) The amount of data in the field association table.

[0085] The data table with the same fields as the target data table is called the associated table of the target data table. The field associated table of each field refers to the data table with the field in the associated table of the target data table. For example, the target data table is CC_CMAVY, and the target data table includes the field PD_ID. Since the data table CC_CMPN_PD also includes PD_ID, the data table CC_CMPN_PD is the field associated table of the field CC_CMPN_PD.

[0086] (2) Number of field accesses.

[0087] The field access count of each field is also called the field association count, which refers to the average number of times the field is accessed per day.

[0088] (3) Field differentiation.

[0089] The field discrimination of each field refers to the ratio between the number of values ​​of the field in the target data table after duplicate removal and the number of all values ​​of the field in the target data table. The field discrimination is greater than 0 and less than or equal to 1. For example, after the values ​​of the gender field are removed, there are only two values, male and female. The target data table has a total of 100 rows, and there are 100 values ​​of the gender field. The field discrimination of the gender field is 1 / 50.

[0090] (4) Whether the field is a partition key.

[0091] Partitioning means storing the data of a data table in different physical media according to the partition field. In fact, it is still a table. If the target data table is a partition table, then the target data table must have a partition field. If a field is a partition field, then the field is the partition key.

[0092] For example, the SQL statement to query the partition table is:

[0093] SELECT TABLE_NAME FROM DBA_PART_TABLES WHERE OWNER='XX'AND TABLE_NAME='XX';

[0094] The SQL statement for querying the partition field is:

[0095] SELECT COLUMN_NAME FROM DBA_PART_KEY_COLUMNS WHERE OWNER='XX'ANDTABLE_NAME='XXXX';

[0096] For example, through the SQL statement query, it is obtained that the data table CST_REL_MGT_AR is a partition table, and the partition field is CST_ID. The data table CC_CMPN_PD is not a partition table and has no partition field. CST_GRP is a partition table, and the partition field is CST_ID.

[0097] In a possible embodiment, after the data table sharding device obtains the associated information of each field, it can calculate the product of the data volume, field access times, field discrimination and preset weight of the field association table of each field, obtain the calculated value of each field, and determine the field with the largest calculated value from multiple fields as the partition field.

[0098] The preset weight of each field is determined according to whether each field is a partition key. If a field is a partition key, the preset weight of the field is a first preset weight, which can be obtained through modeling training, and the first preset weight is, for example, N. If a field is not a partition key, the preset weight of the field is a second preset weight, and the second preset weight is, for example, 1. The first preset weight is greater than the second preset weight.

[0099] For example, horizontal sharding is adopted for the target data table CST_GRP, and Table 1 is various associated information of CST_GRP.

[0100] Table 1

[0101]

[0102] Assuming that the first weight is N and the second preset weight is 1, the calculated value of the field CST_ID is 10250×10020025×0.85×N, the calculated value of the field CCBINS_ID is 1000222×222110×0.26×1, and the calculated value of the field EMP_ID is 99860×90601×0.05×1.

[0103] In a possible embodiment, if there is more than one data table with the same field as the target data table, a certain field may have more than one field association table. For example, the target data table is CC_CMAVY, and the target data table includes the field PD_ID. Since the data table CC_CMPN_PD also includes PD_ID, the data table CC_CMPN_PD is the field association table of the field CC_CMPN_PD.

[0104] For example, if any field has two field association tables, the product of the data volume, field access times, field discrimination and preset weight of the first field association table of the field can be calculated to obtain the first calculated value of the field, the data volume, field access times, field discrimination and preset weight of the second field association table of the field can be calculated to obtain the second calculated value of the field, and the first calculated value and the second calculated value can be added to obtain the calculated value of the field. Similarly, if the field has multiple field association tables, multiple calculated values ​​of the field can be obtained respectively, and the accumulated value of the multiple calculated values ​​can be used as the calculated value of the field.

[0105] Two situations where the data volume of the target data table is greater than the first preset data volume are introduced above. Other possible situations are introduced below.

[0106] In the third case, if the data volume is less than the second preset data volume, the number of table accesses of the target data table is determined; if the number of table accesses is greater than the preset number, the target data table is copied to obtain multiple copy tables, and the multiple copy tables are stored in each node of the distributed database.

[0107] The second preset data volume is smaller than the first preset data volume. If the first preset data volume is a preset fixed data volume, the second preset data volume is also a preset fixed data volume. For example, the first preset data volume is fixed to 20 million and the second preset data volume is fixed to 100,000. If the first preset data volume is dynamically determined, the second preset data volume is also dynamically determined in the same manner as the first preset data volume.

[0108] Specifically, the sharding device can sort the data volume of all data tables in the centralized database in ascending order, and determine the data volume corresponding to the first ratio as the second preset data volume. For example, the first ratio is 20%, and there are 10 data tables in the centralized database. The data volumes of the 10 data tables are sorted in ascending order, namely, data table 10, data table 9, ..., data table 1. Then, the data volume corresponding to 20% is the data volume of data table 9. The data volume of data table 9 is 100,000, and the second preset data volume is 100,000.

[0109] Table access count refers to the average daily transaction count of the target data table, which can also be called table association count. For example, the table association count of the target data table can be queried using the following SQL statement:

[0110] SELECT SUM(A.EXECUTIONS_DELTA)FROM DBA_HIST_SQLSTAT A,DBA_HIST_SQLTEXT B WHERE A.SQL_ID=B.SQL_ID AND B.SQL_TEXT LIKE'%XX%';

[0111] The preset number of times may be a preset fixed number of times, for example, the preset number of times is 1 million times. Considering the different access frequencies of different centralized databases, the preset number of times may also be determined dynamically. Specifically, the data table sharding device may sort the table access times of all data tables in the centralized database in descending order, and determine the table access times corresponding to the second ratio as the preset number of times. The second ratio may be the same as or different from the first ratio.

[0112] For example, the second ratio is 20%. There are 10 data tables in the centralized database. The table access times of the 10 data tables are sorted in descending order, namely, data table 1, data table 2, ..., data table 10. The table access times corresponding to 20% are the table access times of data table 9. The table access times of data table 9 are 1 million, and the preset times are 1 million.

[0113] When the data volume of the target data table is less than the second preset data volume, it means that the data volume of the target data table is small. When the table access times of the target data table is greater than the preset times, it means that the table access times of the target data table is large. For example, the target data table is an organization table, an employee table, etc. At this time, the target data table is copied, and the copied multiple copy tables are stored in each node of the distributed database. It should be noted that the number of the multiple copy tables is the same as the number of nodes of the distributed database, so that each node of the distributed database stores a copy table of the target data table.

[0114] In the fourth case, if the data volume is greater than the second preset data volume and less than the first preset data volume, the number of table accesses of the target data table is determined. If the number of table accesses is greater than the preset number, vertical sharding and horizontal sharding are used for the target data table.

[0115] Specifically, when the data volume of the target data table is greater than the second preset data volume and less than the first preset data volume, and the number of table accesses to the target data table is greater than the preset number, the data table sharding device may adopt hybrid sharding for the target data table, which refers to a mixture of horizontal sharding and vertical sharding. Vertical sharding may be adopted for the target data table first and then horizontal sharding, or horizontal sharding may be adopted for the target data table first and then vertical sharding. Please refer to the content discussed above for the meaning of the number of table accesses, the method of determining the second preset data volume and the preset number of times, and the process of horizontal sharding and vertical sharding, which will not be repeated here.

[0116] It should be noted that, except for the above four sharding situations, the remaining situations indicate that the target data table is a common table, which is not sharded and is directly stored in a node of the distributed database.

[0117] For a clearer explanation of which sharding method to use in which situation, please refer to Figure 3 , which is a flow chart of a data table sharding method provided in an embodiment of the present application Figure 2 , the following combination Figure 3 This section introduces various sharding situations.

[0118] S301. Obtain the data volume and the proportion of commonly used fields in the target data table.

[0119] For details on how to obtain the data volume and the proportion of commonly used fields, please refer to the previous discussion, which will not be repeated here.

[0120] S302: Determine whether the data volume is greater than a first preset data volume.

[0121] If the data volume is greater than the first preset data volume, S303 is executed, i.e., it is determined whether the proportion of commonly used fields is less than the first preset proportion. The method for determining the first preset data volume can refer to the content discussed above, which will not be repeated here. If the data volume is less than the first preset data volume, S308 is executed, i.e., it is determined whether the data volume is less than the second preset data volume.

[0122] S303: Determine whether the proportion of commonly used fields is less than a first preset proportion.

[0123] If the proportion of commonly used fields is less than the first preset proportion, S304 is executed, that is, vertical slicing is adopted for the target data table. The method for determining the first preset proportion is referred to the content discussed above, which will not be repeated here. If the proportion of commonly used fields is greater than the first preset proportion, S305 is executed, that is, whether the proportion of commonly used fields is greater than the second preset proportion is determined.

[0124] S304: vertically shard the target data table.

[0125] For the specific process of vertical sharding, please refer to the previous discussion, which will not be repeated here.

[0126] S305: Determine whether the proportion of commonly used fields is greater than a second preset proportion.

[0127] If the proportion of commonly used fields is greater than the second preset proportion, S306 is executed, that is, horizontal sharding is adopted for the target data table. The method for determining the second preset proportion can be referred to the content discussed above, which will not be repeated here. If the proportion of commonly used fields is less than the second preset proportion, S307 is executed, that is, the target data table is not sharded.

[0128] S306: horizontal sharding is adopted for the target data table.

[0129] For the specific process of horizontal sharding, please refer to the previous discussion, which will not be repeated here.

[0130] S307: The target data table is not fragmented.

[0131] S308: Determine whether the data amount is less than a second preset data amount.

[0132] If the data volume is less than the second preset data volume, S309 is executed, that is, the number of table accesses of the target data table is determined. The method for determining the second preset data volume can be referred to the content discussed above, which will not be repeated here. If the data volume is greater than the second preset data volume, S307 is executed, that is, the target data table is not fragmented.

[0133] S309: Determine the number of table accesses of the target data table.

[0134] Please refer to the previous discussion for the meaning and determination method of the table access count, which will not be repeated here.

[0135] S310: Determine whether the number of table accesses is greater than a preset number.

[0136] If the number of table accesses of the target data table is greater than the preset number, S311 is executed, that is, the target data table is copied. The method for determining the preset number of times can be referred to the content discussed above, which will not be repeated here. If the number of table accesses of the target data table is less than the preset number, S307 is executed, that is, the target data table is not fragmented.

[0137] S311. Copy the target data table.

[0138] For the specific process of copying the target data table, please refer to the content discussed above, which will not be repeated here.

[0139] It should be noted that S301-S302-S308-S309-S3010-S311, S301-S302-S308-S307, S301-S302-S303-S305-S307, S301-S302-S303-S304, and S301-S302-S303-S305-S306 are several different situations, and only one of them needs to be executed each time.

[0140] For example, the data volume, table access times, and common field ratios of three tables are shown in Table 2:

[0141] Table 2

[0142] Table name Data volume Table access count Commonly used fields CST_REL_MGT_AR 1021001 50201 21％ CC_CMPN_PD 80910 2010091 90％ CST_GRP 39010900 890001 80％

[0143] The data table sharding method provided in this application is used for these three tables respectively, and the sharding schemes for these three tables are shown in Table 3:

[0144] Table 3

[0145] Table name Sharding Recommendations CST_REL_MGT_AR Vertical Sharding CC_CMPN_PD Copy Table CST_GRP Horizontal Sharding

[0146] To sum up, the data table sharding method provided in the present application can give sharding suggestions based on a comprehensive judgment of indicators such as the amount of data in the data table of the centralized database, the proportion of commonly used fields, the number of table accesses, etc. Compared with the manual sharding method, the efficiency of data table sharding can be improved, and the efficiency of migrating data tables in the centralized database to the distributed database can be further improved. Developers can further analyze and judge based on the given sharding suggestions, which can improve the accuracy of data table sharding.

[0147] Based on the same inventive concept, the embodiment of the present application provides a data table sharding device, which is applied to the scenario of migrating a data table from a centralized database to a distributed database. The device is arranged in the data table sharding device discussed above, please refer to Figure 4 , the device comprises:

[0148] The determination module 401 is used to determine the data volume and the proportion of commonly used fields of the target data table to be migrated in the centralized database, wherein the target data table includes multiple fields, and the proportion of commonly used fields represents the ratio of the number of commonly used fields to the number of multiple fields;

[0149] A sharding module 402 is configured to, if the data volume is greater than a first preset data volume and the proportion of commonly used fields is less than the first preset proportion, vertically shard the target data table, wherein vertical sharding means storing multiple data sub-tables split from the target data table in one node of the distributed database;

[0150] The sharding module 402 is also used to adopt horizontal sharding for the target data table if the data volume is greater than a first preset data volume and the proportion of commonly used fields is greater than a second preset proportion, wherein the second preset proportion is greater than or equal to the first preset proportion. Horizontal sharding means that the multiple data sub-tables split from the target data table are stored in different nodes of the distributed database.

[0151] In a possible embodiment, the determination module 401 is further used to: determine the number of table accesses of the target data table if the data amount is less than a second preset data amount, wherein the second preset data amount is less than the first preset data amount;

[0152] The sharding module 402 is also used for: if the number of table accesses is greater than a preset number, then copying the target data table to obtain multiple copy tables; and storing the multiple copy tables in each node of the distributed database.

[0153] In a possible embodiment, the determination module 401 is further configured to: if the data volume is greater than the second preset data volume and less than the first preset data volume, determine the number of table accesses of the target data table;

[0154] The sharding module 402 is further configured to: if the number of table accesses is greater than a preset number, perform vertical sharding and horizontal sharding on the target data table.

[0155] In a possible embodiment, the determination module 401 is further used to: determine a sharding field from multiple fields based on association information of each field, wherein the association information of each field includes the data volume of the field association table, the number of field accesses, the field distinction, and whether the field is a partition key;

[0156] The sharding module 402 is specifically used to split the target data table into multiple data sub-tables according to different values ​​of the sharding field, and store the multiple data sub-tables in different nodes of the distributed database.

[0157] In a possible embodiment, the determination module 401 is specifically configured to:

[0158] Calculate the product of the data volume, field access times, field discrimination and preset weight of the field association table of each field to obtain the calculated value of each field; wherein the preset weight of each field is determined according to whether each field is a partition key;

[0159] The field with the largest calculated value among multiple fields is determined as the partition field.

[0160] In a possible embodiment, the determining module 401 is further configured to:

[0161] Sort the data volume of all data tables in the centralized database;

[0162] In order from large to small, determining the data amount corresponding to the first ratio as the first preset data amount;

[0163] In order from small to large, the data amount corresponding to the first ratio is determined as the second preset data amount.

[0164] As an example, Figure 4 The apparatus described can be used to perform Figure 2-Figure 3 Therefore, for the functions that can be realized by each functional module of the device, reference can be made to Figure 2-Figure 3 The description of the illustrated embodiment will not be repeated here.

[0165] Based on the same inventive concept, an electronic device is also provided in the embodiment of the present application. The device can realize the function of the data table sharding device discussed above. Please refer to Figure 5 , the electronic device comprises:

[0166] At least one processor 501, and a memory 502 connected to the at least one processor 501. The specific connection medium between the processor 501 and the memory 502 is not limited in the embodiment of the present application. Figure 5 In the example, the processor 501 and the memory 502 are connected via a bus 500. The bus 500 is Figure 5 The connections between other components are shown in bold lines, and are not intended to be limiting. The bus 500 can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 5 Only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus. Alternatively, the processor 501 can also be called a controller, and there is no limitation on the name.

[0167] In the embodiment of the present application, the memory 502 stores instructions that can be executed by at least one processor 501. The at least one processor 501 can execute any of the data table sharding methods discussed above by executing the instructions stored in the memory 502. The processor 501 can implement Figure 4 The functions of each module in the device shown.

[0168] Among them, the processor 501 is the control center of the device, and can use various interfaces and lines to connect the various parts of the entire control device. By running or executing instructions stored in the memory 502 and calling data stored in the memory 502, the various functions of the device and processing data, the device can be monitored as a whole.

[0169] In one possible design, the processor 501 may include one or more processing units, and the processor 501 may integrate an application processor and a modem processor, wherein the application processor mainly processes an operating system, a user interface, and application programs, and the modem processor mainly processes wireless communications. It is understandable that the modem processor may not be integrated into the processor 501. In some embodiments, the processor 501 and the memory 502 may be implemented on the same chip, and in some embodiments, they may also be implemented separately on separate chips.

[0170] Processor 501 can be a general-purpose processor, such as a central processing unit (CPU), a digital signal processor, an application-specific integrated circuit, a field programmable gate array or other programmable logic device, a discrete gate or transistor logic device, a discrete hardware component, and can implement or execute the various methods, steps and logic block diagrams disclosed in the embodiments of the present application. A general-purpose processor can be a microprocessor or any conventional processor, etc. The steps of the data table sharding method disclosed in the embodiments of the present application can be directly embodied as a hardware processor to be executed, or a combination of hardware and software modules in the processor can be executed.

[0171] The memory 502 is a non-volatile computer-readable storage medium that can be used to store non-volatile software programs, non-volatile computer executable programs and modules. The memory 502 may include at least one type of storage medium, such as a flash memory, a hard disk, a multimedia card, a card-type memory, a random access memory (Random Access Memory, RAM), a static random access memory (Static Random Access Memory, SRAM), a programmable read-only memory (Programmable Read Only Memory, PROM), a read-only memory (Read Only Memory, ROM), an electrically erasable programmable read-only memory (Electrically Erasable Programmable Read-Only Memory, EEPROM), a magnetic memory, a disk, an optical disk, etc. The memory 502 is any other medium that can be used to carry or store a desired program code in the form of an instruction or data structure and can be accessed by a computer, but is not limited thereto. The memory 502 in the embodiment of the present application can also be a circuit or any other device that can realize a storage function, for storing program instructions and / or data.

[0172] By designing and programming the processor 501, the code corresponding to the data table sharding method described in the above embodiment can be fixed into the chip, so that the chip can execute the code when running. Figure 2-Figure 3The steps of the data table sharding method of the embodiment shown are as follows: How to design and program the processor 501 is a technique known to those skilled in the art and will not be described in detail here.

[0173] Based on the same inventive concept, an embodiment of the present application provides a computer-readable storage medium, which stores a computer program, and the computer program includes program instructions. When the program instructions are executed by a computer, the computer executes any of the data table sharding methods discussed above. Since the principle of solving the problem by the above-mentioned computer-readable storage medium is similar to that of the data table sharding method, the implementation of the above-mentioned computer-readable storage medium can refer to the implementation of the method, and the repeated parts will not be repeated.

[0174] Based on the same inventive concept, the embodiment of the present application also provides a computer program product, which includes: computer program code, when the computer program code is run on a computer, the computer executes any of the data table sharding methods discussed above. Since the principle of solving the problem by the above computer program product is similar to that of the data table sharding method, the implementation of the above computer program product can refer to the implementation of the method, and the repeated parts will not be repeated.

[0175] The program product may employ any combination of one or more readable media. The readable medium may be a readable signal medium or a readable storage medium. The readable storage medium may be, but is not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination thereof. More specific examples of readable storage media (a non-exhaustive list) include: an electrical connection with one or more wires, a portable disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof.

[0176] The program product of the data table sharding method provided in the embodiment of the present invention can adopt a portable compact disk read-only memory (CD-ROM) and include program code, and can be run on a computing device. However, the program product of the present invention is not limited to this. In this document, the readable storage medium can be any tangible medium containing or storing a program, which can be used by or in combination with an instruction execution system, an apparatus or a device.

[0177] A readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, wherein readable program code is carried. Such propagated data signals may take a variety of forms, including, but not limited to, electromagnetic signals, optical signals, or any suitable combination of the foregoing. A readable signal medium may also be any readable medium other than a readable storage medium, which may transmit, propagate, or transfer a program for use by or in conjunction with an instruction execution system, apparatus, or device.

[0178] The program code contained on the readable medium can be transmitted with any appropriate medium, including - but not limited to - wireless, wired, optical cable, RF, etc., or any suitable combination of the above. The program code for performing the operation of the present invention can be written in any combination of one or more programming languages, including object-oriented programming languages ​​- such as Java, C++, etc., and also including conventional procedural programming languages ​​- such as "C" language or similar programming languages. The program code can be executed completely on the user computing device, partially on the user device, as an independent software package, partially on the user computing device, partially on the remote computing device, or completely on the remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network - including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (for example, using an Internet service provider to connect through the Internet).

[0179] It should be noted that, although several units or subunits of the device are mentioned in the above detailed description, this division is merely exemplary and not mandatory. In fact, according to an embodiment of the present invention, the features and functions of two or more units described above can be embodied in one unit. Conversely, the features and functions of one unit described above can be further divided into multiple units to be embodied.

[0180] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0181] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0182] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0183] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0184] Obviously, those skilled in the art can make various changes and modifications to the present application without departing from the spirit and scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is also intended to include these modifications and variations.

Claims

1. A data table sharding method, It is characterized in that The method comprises: Determine the data volume and the proportion of commonly used fields of the target data table to be migrated in the centralized database, wherein the target data table includes multiple fields, and the proportion of commonly used fields represents the ratio of the number of commonly used fields to the number of the multiple fields; If the data volume is greater than a first preset data volume, and the proportion of the commonly used fields is less than the first preset proportion, vertical sharding is adopted for the target data table, wherein the vertical sharding means storing multiple data sub-tables split from the target data table in one node of the distributed database; If the data volume is greater than the first preset data volume, and the proportion of the commonly used fields is greater than the second preset proportion, horizontal sharding is adopted for the target data table, wherein the second preset proportion is greater than or equal to the first preset proportion, and the horizontal sharding means storing the multiple data sub-tables split from the target data table in different nodes of the distributed database.

2. The method according to claim 1, It is characterized in that The method further comprises: If the data volume is less than a second preset data volume, determining the number of table accesses of the target data table, wherein the second preset data volume is less than the first preset data volume; If the table access times are greater than a preset times, copying the target data table to obtain multiple copy tables; The multiple replicated tables are stored in each node of the distributed database.

3. The method according to claim 1, It is characterized in that The method further comprises: If the data amount is greater than a second preset data amount and less than the first preset data amount, determining the number of table accesses of the target data table; If the table access times are greater than a preset times, the vertical sharding and the horizontal sharding are used for the target data table.

4. The method according to any one of claims 1 to 3, It is characterized in that The target data table is horizontally sharded, including: Based on the association information of each field, determining a sharding field from the multiple fields, wherein the association information of each field includes the data volume of the field association table, the number of field accesses, the field distinction, and whether the field is a partition key; According to different values ​​of the sharding field, the target data table is split into multiple data sub-tables, and the multiple data sub-tables are stored in different nodes of the distributed database.

5. The method according to claim 4, It is characterized in that Determining a fragmentation field from the multiple fields based on the association information of each field includes: Calculate the product of the data volume, field access times, field discrimination and preset weight of the field association table of each field to obtain the calculated value of each field; wherein the preset weight of each field is determined according to whether each field is a partition key; A field having the largest calculated value among the multiple fields is determined as a partition field.

6. The method according to any one of claims 1 to 3, It is characterized in that The first preset data amount and the second preset data amount are determined in the following manner: Sorting the data volumes of all data tables in the centralized database; In order from large to small, determining the data amount corresponding to the first ratio as the first preset data amount; In order from small to large, the data amount corresponding to the first ratio is determined as the second preset data amount.

7. A data table sharding device, It is characterized in that The device comprises: A determination module, used to determine the data volume and the proportion of commonly used fields of a target data table to be migrated in a centralized database, wherein the target data table includes multiple fields, and the proportion of commonly used fields represents the ratio of the number of commonly used fields to the number of the multiple fields; A sharding module, configured to adopt vertical sharding for the target data table if the data volume is greater than a first preset data volume and the proportion of the commonly used fields is less than the first preset proportion, wherein the vertical sharding means storing multiple data sub-tables split from the target data table in one node of a distributed database; The sharding module is also used to adopt horizontal sharding for the target data table if the data volume is greater than the first preset data volume and the proportion of the commonly used fields is greater than the second preset proportion, wherein the second preset proportion is greater than or equal to the first preset proportion, and the horizontal sharding means storing the multiple data sub-tables split from the target data table in different nodes of the distributed database.

8. The device according to claim 7, It is characterized in that The determining module is also used for: If the data volume is less than a second preset data volume, determining the number of table accesses of the target data table, wherein the second preset data volume is less than the first preset data volume; The sharding module is also used for: If the table access times are greater than a preset times, copying the target data table to obtain multiple copy tables; The multiple replicated tables are stored in each node of the distributed database.

9. The device according to claim 7, It is characterized in that The determination module is further configured to: if the data volume is greater than a second preset data volume and less than the first preset data volume, determine the number of table accesses of the target data table; The sharding module is further used for: if the number of table accesses is greater than a preset number, adopting the vertical sharding and the horizontal sharding for the target data table.

10. The device according to any one of claims 7 to 9, It is characterized in that The determination module is further used to: determine the sharding field from the multiple fields based on the association information of each field, wherein the association information of each field includes the data volume of the field association table, the number of field accesses, the field distinction, and whether the field is a partition key; The sharding module is specifically used to split the target data table into multiple data sub-tables according to different values ​​of the sharding field, and store the multiple data sub-tables in different nodes of the distributed database.

11. The device according to claim 10, It is characterized in that The determination module is specifically used for: Calculate the product of the data volume, field access times, field discrimination and preset weight of the field association table of each field to obtain the calculated value of each field; wherein the preset weight of each field is determined according to whether each field is a partition key; A field having the largest calculated value among the multiple fields is determined as a partition field.

12. The device according to any one of claims 7 to 9, It is characterized in that The determining module is also used for: Sorting the data volumes of all data tables in the centralized database; In order from large to small, determining the data amount corresponding to the first ratio as the first preset data amount; In order from small to large, the data amount corresponding to the first ratio is determined as the second preset data amount.

13. An electronic device, It is characterized in that include: A memory for storing program instructions; A processor is used to call the program instructions stored in the memory, and execute the method according to any one of claims 1 to 6 according to the obtained program instructions.

14. A computer-readable storage medium, It is characterized in that The computer-readable storage medium stores a computer program, wherein the computer program includes program instructions, and when the program instructions are executed by a computer, the computer executes the method according to any one of claims 1 to 6.

15. A computer program product, It is characterized in that The computer program product comprises: a computer program code, and when the computer program code is run on a computer, the computer is enabled to execute the method according to any one of claims 1 to 6.

Images