Load balancing based on database partitioning method and device

Abstract

The application provides a database splitting method and device based on load balancing, which can be used in the financial field or other fields. The method comprises the following steps: obtaining table statistical information and table connection relationship information corresponding to a plurality of data tables; performing load balancing processing based on the table statistical information and the table connection relationship information of each data table, determining the database instances corresponding to each data table respectively, and distributing each data table to the database instance corresponding thereto, thereby completing the database splitting of each data table. The application can improve the automation degree and accuracy of data table splitting, thereby relieving the read-write pressure of the database.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a database sharding method and apparatus based on load balancing. Background Technology

[0002] When a database instance has a large amount of data or high access volume, it can put significant pressure on the database, leading to performance issues. In such cases, database sharding is typically implemented, distributing different tables across different database instances to achieve load balancing. Currently, the decision to distribute tables across different databases is made manually.

[0003] Manual judgment relies solely on daily experience to determine the usage of a table, separating frequently used tables into different database instances. This experience-based approach has two drawbacks: first, only developers with extensive knowledge of the application's business logic can make these judgments, imposing stringent limitations on the executors; second, it cannot guarantee sufficient accuracy, potentially resulting in allocating high-traffic tables to the same database instance, failing to resolve database performance issues even after sharding. Summary of the Invention

[0004] To address at least one problem in the prior art, this application proposes a database sharding method and apparatus based on load balancing, which can improve the automation and accuracy of database sharding, thereby alleviating the read and write pressure on the database.

[0005] To address the aforementioned technical problems, this application provides the following technical solution:

[0006] Firstly, this application provides a database sharding method based on load balancing, including:

[0007] Retrieve table statistics and table join information for multiple data tables;

[0008] Load balancing is performed based on the table statistics and join information of each data table. The corresponding database instance for each data table is determined and each data table is assigned to its corresponding database instance, thus completing the database sharding of each data table.

[0009] In one embodiment, the load balancing process based on the table statistics and table join information of each data table, determining the corresponding database instance for each data table, and allocating each data table to its corresponding database instance includes:

[0010] Based on the table join information of each data table, the data tables are divided into multiple data table groups. Each data table group includes: multiple data tables with table join relationships or a unique data table without table join relationships.

[0011] Determine whether the number of data table groups is less than or equal to the number of database instances. If so, assign each data table group to a different database instance.

[0012] In one embodiment, the database sharding method based on load balancing further includes:

[0013] If the number of data table groups is greater than the number of database instances, then based on the number of updated rows in the table statistics of each data table in multiple time periods, the average number of updated rows in each time period and the number of updated rows in each data table group in each time period are obtained.

[0014] The data table groups are sorted according to the average number of updated rows in each time period and the number of updated rows in each time period. The sorted data table groups are then distributed to each database instance in sequence, starting from the first one.

[0015] The data table group of the unassigned database instance is merged with the data table group of each assigned database instance to obtain the merged data table group;

[0016] Based on the average number of updated rows in each time period and the number of updated rows in the merged data table group in each time period, the database instance corresponding to the data table group without assigned database instance is determined and assigned to that database instance, thus completing the database sharding of each data table.

[0017] In one embodiment, sorting the data table groups according to the average number of updated rows in each time period and the number of updated rows for each data table group in each time period includes:

[0018] Based on the average number of updated rows in each time period and the number of updated rows of each data table group in each time period, determine the ratio of the number of updated rows of the data table group to the corresponding average number of updated rows in each time period, and count the number of target time periods where the ratio is greater than a preset first multiple.

[0019] Sort the data tables from most to least number of target time periods for each group.

[0020] In one embodiment, determining the database instance corresponding to the unallocated database instance for the data table group based on the average number of updated rows in each time period and the number of updated rows in the merged data table group in each time period, and allocating it to that database instance to complete the database sharding of each data table, includes:

[0021] Based on the average number of updated rows in each time period and the number of updated rows of the merged data table group in each time period, determine the ratio of the number of updated rows of the merged data table group to the corresponding average number of updated rows in each time period, and count the number of target time periods in which the ratio is greater than a preset first multiple.

[0022] The group with the fewest target time periods among the merged data table groups is identified as the target data table group. The data table group without assigned database instances is then assigned to the database instance of the data table group with assigned database instances in the target data table group, thus completing the database sharding of each data table.

[0023] In one embodiment, sorting the data table groups from most to least the number of target time periods in each group further includes:

[0024] If there are multiple sets of data tables to be compared with the same number of target time periods, then count the number of time periods for which the ratio of each set of data tables exceeds a preset second multiple, where the preset second multiple is less than a preset first multiple.

[0025] The comparison data tables are sorted from most to least based on the number of time periods in which the ratio exceeds a preset second multiple.

[0026] In one embodiment, obtaining table statistics and table join information corresponding to multiple data tables includes:

[0027] Obtain the full set of structured query language scripts and table statistics for each data table;

[0028] Based on the full set of structured query language scripts and the preset structured query language parser, the table join information corresponding to each data table is obtained.

[0029] Secondly, this application provides a database sharding device based on load balancing, comprising:

[0030] The acquisition module is used to obtain table statistics and table join information for multiple data tables.

[0031] The database sharding module is used to perform load balancing based on the table statistics and table join information of each data table, determine the corresponding database instance for each data table, and allocate each data table to its corresponding database instance to complete the database sharding of each data table.

[0032] In one embodiment, the database sharding module includes:

[0033] The partitioning unit is used to divide each data table into multiple data table groups based on the table join relationship information of each data table. Each data table group includes: multiple data tables with table join relationships or a unique data table without table join relationships.

[0034] The judgment unit is used to determine whether the number of data table groups is less than or equal to the number of database instances. If so, the data table groups are assigned to different database instances.

[0035] In one embodiment, the database sharding device based on load balancing further includes:

[0036] The module is used to obtain the average number of updated rows in each time period and the number of updated rows of each data table group in each time period based on the number of updated rows in the table statistics information of each data table in multiple time periods if the number of data table groups is greater than the number of database instances.

[0037] The sorting module is used to sort the data table groups according to the average number of updated rows in each time period and the number of updated rows of each data table group in each time period, and then distribute the sorted data table groups to each database instance in sequence, starting from the first one.

[0038] The merge module is used to merge the data table groups of unassigned database instances with the data table groups of each assigned database instance to obtain the merged data table group.

[0039] The allocation module is used to determine the database instance corresponding to the unallocated database instance of the data table group based on the average number of updated rows in each time period and the number of updated rows of the merged data table group in each time period, and to allocate the data table to the database instance, thereby completing the database sharding of each data table.

[0040] In one embodiment, the sorting module includes:

[0041] The determining unit is used to determine the ratio of the number of updated rows of a data table group to the corresponding average number of updated rows in each time period based on the average number of updated rows in each time period and the number of updated rows of each data table group in each time period, and to count the number of target time periods in which the ratio is greater than a preset first multiple.

[0042] The sorting unit is used to sort the data tables from most to least number based on the number of target time periods in each data table group.

[0043] In one embodiment, the allocation module includes:

[0044] The unit for determining the number of time periods is used to determine the ratio of the number of updated rows of the merged data table group to the corresponding average number of updated rows in each time period based on the average number of updated rows in each time period and the number of updated rows of the merged data table group in each time period, and to count the number of target time periods in which the ratio is greater than a preset first multiple.

[0045] The data table group unit is determined by identifying the group with the fewest target time periods among the merged data table groups as the target data table group. The data table group without assigned database instances is then assigned to the database instance of the data table group with assigned database instances in the target data table group, thus completing the database sharding of each data table.

[0046] In one embodiment, the sorting unit is further specifically used for:

[0047] If there are multiple sets of data tables to be compared with the same number of target time periods, then count the number of time periods for which the ratio of each set of data tables exceeds a preset second multiple, where the preset second multiple is less than a preset first multiple.

[0048] The comparison data tables are sorted from most to least based on the number of time periods in which the ratio exceeds a preset second multiple.

[0049] In one embodiment, the acquisition module includes:

[0050] The acquisition unit is used to acquire the full set of structured query language scripts and table statistics of each data table;

[0051] The parsing unit is used to obtain the table join information corresponding to each data table based on the full set of structured query language scripts and the preset structured query language parser.

[0052] Thirdly, this application provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the aforementioned database sharding method based on load balancing.

[0053] Fourthly, this application provides a computer-readable storage medium storing computer instructions thereon, which, when executed, implement the aforementioned database sharding method based on load balancing.

[0054] As can be seen from the above technical solution, this application provides a database sharding method and apparatus based on load balancing. The method includes: obtaining table statistics and table join information for multiple data tables; performing load balancing based on the table statistics and join information of each data table, determining the corresponding database instance for each data table, and allocating each data table to its corresponding database instance, thus completing the database sharding of each data table. This improves the automation and accuracy of database sharding, thereby alleviating the read / write pressure on the database. Specifically, it solves the problems of manual database sharding being labor-intensive and inaccurate. Users are not limited to developers who are particularly familiar with the business; they only need to know a small amount of information to perform database sharding analysis. Compared to manually judging which tables have a higher load, this method is more accurate, and the effect of allocating tables to various database instances is better. It avoids the situation where tables with join relationships are allocated to different database instances, greatly reducing the risk of SQL errors due to table not being found after database sharding. Attached Figure Description

[0055] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0056] Figure 1 This is a schematic diagram of the first process of the database sharding method based on load balancing in the embodiments of this application;

[0057] Figure 2 This is a schematic diagram of the second process of the database sharding method based on load balancing in the embodiments of this application;

[0058] Figure 3 This is a schematic diagram of the third process of the database sharding method based on load balancing in the embodiments of this application;

[0059] Figure 4 This is a schematic diagram of the fourth process of the database sharding method based on load balancing in the embodiments of this application;

[0060] Figure 5 This is a schematic diagram of the structure of the load-balanced database distribution device in the embodiments of this application;

[0061] Figure 6 This is a schematic diagram of the structure of the database sub-module in the embodiments of this application;

[0062] Figure 7 This is a schematic diagram of the structure of a load-balanced database distribution device in a specific application example of this application;

[0063] Figure 8 This is a flowchart illustrating the database sharding method based on load balancing in a specific application example of this application;

[0064] Figure 9 This is a flowchart illustrating the application load balancing algorithm process in a specific application example of this application;

[0065] Figure 10 This is a schematic block diagram of the system configuration of an electronic device according to an embodiment of this application. Detailed Implementation

[0066] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments in this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of this application.

[0067] To facilitate understanding of this solution, the technical details related to this solution are explained below.

[0068] Database sharding refers to splitting data originally stored in a single database into multiple databases. Table sharding refers to splitting data originally stored in a single table into multiple tables. This invention primarily addresses database sharding.

[0069] A database instance is actually a database server engine. Each database server engine instance has its own system and user database that is not shared with other instances, and can be distributed across different servers.

[0070] Load, here refers to the pressure exerted on the database, including read pressure and write pressure.

[0071] To address the problems existing in the prior art, this application provides a database sharding method and apparatus based on load balancing. It can use SQL to obtain statistical information from a MySQL database, analyze the load based on the statistical information, and distribute all tables to various database instances according to the load, thereby achieving load balancing among the database instances.

[0072] It should be noted that the database sharding method and apparatus based on load balancing disclosed in this application can be used in the field of financial technology, or in any field other than financial technology. The application field of the database sharding method and apparatus based on load balancing disclosed in this application is not limited.

[0073] The following examples illustrate this in detail.

[0074] To improve the automation and accuracy of database sharding and thus alleviate database read / write pressure, this embodiment provides a load-balanced database sharding method whose execution entity is a load-balanced sharding device. This load-balanced sharding device includes, but is not limited to, servers, such as... Figure 1 As shown, this method specifically includes the following:

[0075] Step 100: Obtain table statistics and table join information for multiple data tables.

[0076] Specifically, it is possible to obtain table statistics and table join information for multiple data tables corresponding to a financial transaction system. The table statistics may include the number of updated rows in the data table over multiple time periods, and may also include the number of queried rows in the data table over multiple time periods. The table join information can indicate data tables that have table join relationships. For example, if an SQL query statement needs to join multiple data tables to complete the data query, then the multiple data tables can be identified as data tables that have table join relationships.

[0077] Step 200: Based on the table statistics and table join information of each data table, perform load balancing to determine the corresponding database instance for each data table and allocate each data table to its corresponding database instance, thus completing the database sharding of each data table.

[0078] To further improve the reliability of database sharding, such as Figure 2 As shown, in one embodiment, step 200 includes:

[0079] Step 201: Based on the table join information of each data table, divide each data table into multiple data table groups. Each data table group includes: multiple data tables with table join relationships or a unique data table without table join relationships.

[0080] For example, the data tables corresponding to a financial trading system include: data tables A, B, C, D, E, and F. Assuming that there are table joins between data tables A, B, and C, and no table joins between data tables D, E, and F, then the data tables can be divided into four groups: data table group 1 consisting of data tables A, B, and C; data table group 2 consisting of data table D; data table group 3 consisting of data table E; and data table group 4 consisting of data table F.

[0081] Step 202: Determine whether the number of data table groups is less than or equal to the number of database instances. If so, assign each data table group to a different database instance.

[0082] Specifically, the number of database instances can be pre-stored locally on the load-balanced database sharding device.

[0083] In the example above, assuming there are 5 database instances, 4 database instances can be randomly selected, and the four sets of data tables can be assigned to these 4 database instances.

[0084] To avoid assigning tables with join relationships to different database instances and to further improve the reliability of database sharding, such as... Figure 3 As shown, in one embodiment, the database sharding method based on load balancing further includes:

[0085] Step 203: If the number of data table groups is greater than the number of database instances, then based on the number of updated rows in the table statistics of each data table in multiple time periods, obtain the average number of updated rows in each time period and the number of updated rows of each data table group in each time period.

[0086] In the example above, assuming there are 3 database instances, the number of rows updated in data tables A, B, C, D, E, and F during time period K1 are x1, x2, x3, x4, x5, and x6 respectively, and the number of rows updated in time period K2 are y1, y2, y3, y4, y5, and y6 respectively. Then, the average number of rows updated in time period K1 is (x1+x2+x3+x4+x5+x6) / 6, the average number of rows updated in time period K2 is (y1+y2+y3+y4+y5+y6) / 6, the number of rows updated in data table group 1 during time period K1 is (x1+x2+x3), the number of rows updated in time period K2 is (y1+y2+y3), and so on.

[0087] Step 204: Sort the data table groups according to the average number of updated rows in each time period and the number of updated rows in each time period, and then distribute the sorted data table groups to each database instance in sequence, starting from the first one.

[0088] To further improve the reliability of data table group sorting, preferably, step 204, which involves sorting the data table groups based on the average number of updated rows in each time period and the number of updated rows for each data table group in each time period, may include: Step 2041: Determine the ratio of the number of updated rows for a data table group to the corresponding average number of updated rows in each time period based on the average number of updated rows in each time period and the number of updated rows for each data table group in each time period, and count the number of target time periods where the ratio is greater than a preset first multiple; Step 2042: Sort the data table groups from most to least according to the number of target time periods for each data table group.

[0089] Specifically, the preset first multiple can be set according to the actual situation, and this application does not impose any restrictions on it.

[0090] In the example above, the average number of rows updated in time period K1 is (x1+x2+x3+x4+x5+x6) / 6, and the number of rows updated in data table group 1 is (x1+x2+x3). In time period K1, the number of rows updated in this data table group exceeds the corresponding average number of rows updated by a multiple of 6(x1+x2+x3) / (x1+x2+x3+x4+x5+x6). Assuming that 6(x1+x2+x3) / (x1+x2+x3+x4+x5+x6) is greater than the preset first multiple, the number of target time periods for data table group 1 is increased by 1. And so on, the total number of target time periods for each of data table groups 1 to 4 can be determined. Assuming that the number of target time periods for data table groups is from most to least in the order of data table group 1, data table group 3, data table group 2, and data table group 4, then data table group 1, data table group 3, and data table group 2 can be configured into different database instances.

[0091] Step 205: Merge the data table groups of the unassigned database instances with the data table groups of each assigned database instance to obtain the merged data table groups.

[0092] In the above example, data table group 4 is merged with data table group 1, data table group 3, and data table group 2 respectively to obtain data table group 5 consisting of data tables A, B, C, and F, data table group 6 consisting of data tables D and F, and data table group 7 consisting of data tables E and F.

[0093] Step 206: Based on the average number of updated rows in each time period and the number of updated rows in the merged data table group in each time period, determine the database instance corresponding to the data table group without assigned database instance and assign it to that database instance to complete the database sharding of each data table.

[0094] To avoid distributing high-traffic tables to the same database instance and further alleviate database performance pressure, such as... Figure 4 As shown, in one embodiment, step 206 includes:

[0095] Step 2061: Based on the average number of updated rows in each time period and the number of updated rows of the merged data table group in each time period, determine the ratio of the number of updated rows of the merged data table group to the corresponding average number of updated rows in each time period, and count the number of target time periods where the ratio is greater than a preset first multiple.

[0096] In the example above, the average number of rows updated in time period K1 is (x1+x2+x3+x4+x5+x6) / 6, and the number of rows updated in data table group 5 of time period K1 is (x1+x2+x3+x6). Assuming that 6(x1+x2+x3+x6) / (x1+x2+x3+x4+x5+x6) is greater than the preset first multiple, then the number of time periods in data table group 1 that exceed the preset first multiple of the corresponding average number of rows updated is increased by 1. Similarly, the number of time periods in data table group 5 that exceed the preset first multiple of the corresponding average number of rows updated can be determined.

[0097] Step 2062: Determine the target data table group as the one with the fewest target time periods among the merged data table groups. Assign the data table group without assigned database instances to the database instances of the data table groups with assigned database instances in the target data table group, thus completing the database sharding of each data table.

[0098] In the example above, assuming that the number of time periods in which the number of updated rows in data table group 5 exceeds the preset first multiple of the corresponding average number of updated rows is the least, then data table group 4 can be assigned to the database instance where data table group 1 is located.

[0099] To further improve the reliability of sorting the data table groups, in one embodiment, step 2042 further includes:

[0100] If there are multiple sets of data tables to be compared with the same number of target time periods, then count the number of time periods for each set of data tables whose ratio exceeds a preset second multiple, where the preset second multiple is less than a preset first multiple; sort the sets of data tables to be compared from most to least according to the number of time periods whose ratio exceeds the preset second multiple.

[0101] Specifically, if there are multiple sets of data tables to be compared with the same number of target time periods, the number of time periods in each set where the number of updated rows exceeds a preset second multiple of the corresponding average number of updated rows can be obtained. The data tables to be compared are then sorted from largest to smallest based on the number of time periods where the number of updated rows exceeds a preset second multiple of the corresponding average number of updated rows. The data tables to be compared can represent sets of data tables with the same number of target time periods.

[0102] Specifically, the data table group to be compared can represent multiple data table groups with the same number of target time periods; the preset second multiple can be set according to actual conditions, and this application does not impose any restrictions on it. The data table groups to be compared can be sorted by adjusting the preset second multiple; if, after multiple adjustments to the preset second multiple, there are still data table groups to be compared with the same number of time periods whose ratio exceeds the preset second multiple, the database sharding of the data table groups to be compared can be completed based on the average number of query rows in each time period and the number of query rows in each time period of the data table group to be compared.

[0103] To further improve the efficiency and accuracy of obtaining table join relationship information, in one embodiment, step 100 includes:

[0104] Step 001: Obtain the full set of Structured Query Language scripts and table statistics for each data table.

[0105] Specifically, the full set of structured query language scripts can be the full set of SQL statements from the configuration object of the financial transaction system.

[0106] Step 002: Based on the full set of structured query language scripts and the preset structured query language parser, obtain the table join information corresponding to each data table.

[0107] Specifically, the preset structured query language parser can be an SQL parser.

[0108] From a software perspective, to improve the automation and accuracy of database sharding and thus alleviate database read / write pressure, this application provides an embodiment of a load-balanced database sharding device for implementing all or part of the aforementioned load-balanced database sharding method. See [link to embodiment]. Figure 5 The database sharding device based on load balancing specifically includes the following components:

[0109] Module 01 is used to obtain table statistics and table join information for multiple data tables.

[0110] The database sharding module 02 is used to perform load balancing based on the table statistics and table join information of each data table, determine the corresponding database instance for each data table, and allocate each data table to its corresponding database instance to complete the database sharding of each data table.

[0111] like Figure 6 As shown, in one embodiment, the database sharding module includes:

[0112] The partitioning unit 21 is used to divide each data table into multiple data table groups according to the table join relationship information of each data table. Each data table group includes: multiple data tables with table join relationships or a unique data table without table join relationships.

[0113] The judgment unit 22 is used to determine whether the number of data table groups is less than or equal to the number of database instances. If so, the data table groups are assigned to different database instances.

[0114] In one embodiment, the database sharding device based on load balancing further includes:

[0115] The module is used to obtain the average number of updated rows in each time period and the number of updated rows of each data table group in each time period based on the number of updated rows in the table statistics information of each data table in multiple time periods if the number of data table groups is greater than the number of database instances.

[0116] The sorting module is used to sort the data table groups according to the average number of updated rows in each time period and the number of updated rows of each data table group in each time period, and then distribute the sorted data table groups to each database instance in sequence, starting from the first one.

[0117] The merge module is used to merge the data table groups of unassigned database instances with the data table groups of each assigned database instance to obtain the merged data table group.

[0118] The allocation module is used to determine the database instance corresponding to the unallocated database instance of the data table group based on the average number of updated rows in each time period and the number of updated rows of the merged data table group in each time period, and to allocate the data table to the database instance, thereby completing the database sharding of each data table.

[0119] In one embodiment, the sorting module includes:

[0120] The determining unit is used to determine the ratio of the number of updated rows of a data table group to the corresponding average number of updated rows in each time period based on the average number of updated rows in each time period and the number of updated rows of each data table group in each time period, and to count the number of target time periods in which the ratio is greater than a preset first multiple.

[0121] The sorting unit is used to sort the data tables from most to least number based on the number of target time periods in each data table group.

[0122] In one embodiment, the allocation module includes:

[0123] The unit for determining the number of time periods is used to determine the ratio of the number of updated rows of the merged data table group to the corresponding average number of updated rows in each time period based on the average number of updated rows in each time period and the number of updated rows of the merged data table group in each time period, and to count the number of target time periods in which the ratio is greater than a preset first multiple.

[0124] The data table group unit is determined by identifying the group with the fewest target time periods among the merged data table groups as the target data table group. The data table group without assigned database instances is then assigned to the database instance of the data table group with assigned database instances in the target data table group, thus completing the database sharding of each data table.

[0125] In one embodiment, the sorting unit is further specifically used for:

[0126] If there are multiple sets of data tables to be compared with the same number of target time periods, then count the number of time periods for which the ratio of each set of data tables exceeds a preset second multiple, where the preset second multiple is less than a preset first multiple.

[0127] The comparison data tables are sorted from most to least based on the number of time periods in which the ratio exceeds a preset second multiple.

[0128] In one embodiment, the acquisition module includes:

[0129] The acquisition unit is used to acquire the full set of structured query language scripts and table statistics of each data table;

[0130] The parsing unit is used to obtain the table join information corresponding to each data table based on the full set of structured query language scripts and the preset structured query language parser.

[0131] The embodiments of the load-balanced database sharding device provided in this specification can be used to execute the processing flow of the embodiments of the load-balanced database sharding method described above. Its functions will not be repeated here, but can be referred to the detailed description of the embodiments of the load-balanced database sharding method described above.

[0132] To further illustrate this plan, such as Figure 7 As shown, this application provides an application example of a database sharding device based on load balancing, which is described in detail below:

[0133] The sharding and load balancing system 10 serves as the initiator of sharding and load balancing statistics and the controller of the entire process.

[0134] Application server 20 is where all core programs run. The combined functionality of the database sharding and table-sharing load balancing system and the application server is equivalent to the functionality achieved by the aforementioned database sharding device based on load balancing.

[0135] Application database 30 stores information about all tables in the application, including necessary table statistics.

[0136] System database 40 stores the collected data for subsequent data statistics. The application database and system database can be deployed on the application server or on different independent servers.

[0137] Injection program 50 is a program distributed to the application server by the database sharding and table balancing load balancing system, and it contains all the core content.

[0138] The injected program includes a data collection module 60, which periodically collects statistical information from all tables in the application database, primarily the number of rows read and written to each table. This collected information is then written to the system database. The data statistics module is activated upon completion of the data collection process.

[0139] The data statistics module 70 in the injected program analyzes and statistically processes the collected information, ultimately analyzing the distribution of each table across different database instances to achieve load balancing as much as possible.

[0140] The SQL collection module 80 in the injection program obtains a configuration object from the application's JVM, through which the full SQL can be obtained and stored in the system database.

[0141] The application JVM90 runs the application's logic and stores all SQL information.

[0142] To further illustrate this solution, let's consider the database sharding device based on load balancing in the above application examples, such as... Figure 8 As shown, this application provides an application example of a database sharding method based on load balancing, which is described in detail below:

[0143] Step S11: The sharding and load balancing system injects the injection program into the application server; that is, after inputting information such as application server connection information, application database connection information, collection duration, and number of database instances for load balancing into the sharding and load balancing system, the injection program is injected into the application server.

[0144] Step S12: The SQL acquisition module in the injection program injects the acquisition program into the application's JVM, which contains all the application's SQL information.

[0145] Step S13: Obtain the full SQL of the MyBatis configuration object.

[0146] Step S14: Based on the collected SQL, identify which tables have table join relationships; that is, based on the SQL obtained in step S13, it is possible to identify which tables have table join relationships. Tables with table join relationships cannot be assigned to different database instances, otherwise it will affect the execution of SQL after database sharding and table partitioning.

[0147] Step S15: Store the statistically obtained table join relationships into the system database; that is, store all the table join relationships statistically obtained in step S14 into the sharded database and sharded table load balancing system database for easy access by the data statistics module.

[0148] Step S16: The data acquisition module in the injected program periodically collects table statistics information from the application database. The table statistics include the number of updated rows (added, deleted, and modified) and the number of queried rows.

[0149] Step S17: Store the table statistics information collected at the current time point into the system database; that is, store the information collected at each time point in step S16 into the system database so that the data statistics module can easily obtain it.

[0150] Step S18: Determine if the data collection deadline has been exceeded; that is, determine whether it is necessary to continue collecting table statistics. If the data collection deadline has been exceeded, exit the data collection loop.

[0151] Step S19: The data statistics module of the injection program obtains the collected table connection relationships and table statistics data from the system database; that is, after the table statistics data collection is completed, the data statistics module in the injection program is woken up, and the module first obtains the previously collected table connection relationships and table statistics data.

[0152] Step S110: Using the information obtained in step S19, the distribution of the table across the specified number of database instances is obtained through a load balancing algorithm.

[0153] Step S111: Return the results to the sharding and load balancing system; that is, return the results analyzed in step S110 to the sharding and load balancing system to display the analysis results.

[0154] Specifically, such as Figure 9 As shown, the process of applying the load balancing algorithm specifically includes:

[0155] Step S21: Group tables according to their join relationships (groupi). Since tables with join relationships cannot be assigned to different database instances, they must first be grouped into the same group, while other independent tables form their own group. This results in multiple groups (group1, group2, ..., groupi). Determine if the number of groups exceeds the number of database instances n to be allocated. If yes, proceed to step S22; otherwise, proceed to step S211.

[0156] Step S22: Count the number of updated rows (groupi_uj) and the number of queried rows (groupi_sj) for each group and each time period; that is, subtract the table statistics from the previous time point from the table statistics of the later time point to obtain the number of updated rows and the number of queried rows for that time period. This method can be used to calculate the number of updated rows (groupi_uj) and the number of queried rows (groupi_sj) for each group and each time period.

[0157] Step S23: Calculate the average number of updated rows (avg_ui) and the average number of queried rows (avg_si) for each time period; that is, the average number of updated rows (avg_ui) and the average number of queried rows (avg_si) for each time period can be calculated based on the information collected in step S22 and the number of time periods.

[0158] Step S24: Count the groupi_out2avg, groupi_out1.5avg, and groupi_out1avg for each group; that is, measure the load based on the number of time periods (including updates and queries) that exceed the average by 2 times groupi_out2avg, 1.5 times groupi_out1.5avg, and 1 times groupi_out1avg.

[0159] Step S25: Sort each group sequentially using groupi_out2avg, groupi_out1.5avg, and groupi_out1avg; that is, if there are a lot of time periods that exceed twice the average, it proves that there are some time periods with high load, so these are compared first. If they are equal, then compare the cases of 1.5 times and 1 times. Finally, sort each group.

[0160] Step S26: Assign the first n groups to n database instances (result1, result2, ..., resultn) in sequence. Determine if k ≤ group_num (k starts from n+1). If yes, proceed to step S27; otherwise, proceed to step S211.

[0161] Step S27: The k-th group is summed with the number of updated rows and the number of queried rows for each time period corresponding to result1, result2, ..., resultn, respectively. The sums are: tmpn_uj = groupk_uj + resultn_uj, tmpn_sj = groupk_sj + resultn_sj. That is, the remaining groups not yet distributed to database instances are analyzed sequentially to determine which database instance to assign them to. This group is then summed with the number of updated rows and the number of queried rows for each time period corresponding to result1, result2, ..., resultn, respectively, to obtain the new number of updated rows and the number of queried rows tmp_sj for each time period tmpn_uj.

[0162] Step S28: Count the tmpn_out2avg, tmpn_out1.5avg, and tmpn_out1avg values ​​for tmp1, tmp2, ..., tmpn; that is, based on tmpn_uj and tmp_sj, count the number of time periods that exceed the average value by 2 times tmpn_out2avg, 1.5 times tmpn_out1.5avg, and 1 time period that exceed the average value by 1 time period.

[0163] Step S29: Compare tmpn_out2avg, tmpn_out1.5avg, and tmpn_out1avg in sequence to find the database instance resultn where the minimum value is located; that is, compare the number of time periods that are twice the average value of tmpn_out2avg, 1.5 times tmpn_out1.5avg, and 1 times tmpn_out1avg in sequence to obtain the minimum value, and thus obtain the database instance resultn where the minimum value is located.

[0164] Step S210: Distribute groupk to resultn, resultn = result + groupk; that is, add this group to resultn found in step S29. k = k + 1, and check again whether k ≤ group_num (k starts from n+1). If yes, execute step S27; otherwise, execute step S211.

[0165] Step S211: After all groups have been assigned, the output table shows the distribution as result1, result2, ..., resultn.

[0166] From a hardware perspective, in order to improve the automation and accuracy of database sharding and thus alleviate the read / write pressure on the database, this application provides an embodiment of an electronic device for implementing all or part of the aforementioned load-balanced database sharding method. The electronic device specifically includes the following components:

[0167] The system comprises a processor, memory, a communications interface, and a bus; wherein the processor, memory, and communications interface communicate with each other via the bus; the communications interface is used to realize information transmission between the load-balanced database sharding device and related devices such as user terminals; the electronic device can be a desktop computer, tablet computer, or mobile terminal, etc., and this embodiment is not limited to these. In this embodiment, the electronic device can be implemented with reference to the embodiments for implementing the load-balanced database sharding method and the embodiments for implementing the load-balanced database sharding device, the content of which is incorporated herein, and repeated details will not be described again.

[0168] Figure 10 This is a schematic block diagram illustrating the system configuration of the electronic device 9600 according to an embodiment of this application. Figure 10 As shown, the electronic device 9600 may include a central processing unit 9100 and a memory 9140; the memory 9140 is coupled to the central processing unit 9100. It is worth noting that... Figure 10 This is an example; other types of structures can also be used to supplement or replace this structure to achieve telecommunications functions or other functions.

[0169] In one or more embodiments of this application, the database sharding function based on load balancing can be integrated into the central processing unit 9100. The central processing unit 9100 can be configured to perform the following control:

[0170] Step 100: Obtain table statistics and table join information for multiple data tables.

[0171] Step 200: Based on the table statistics and table join information of each data table, perform load balancing to determine the corresponding database instance for each data table and allocate each data table to its corresponding database instance, thus completing the database sharding of each data table.

[0172] As can be seen from the above description, the electronic device provided in the embodiments of this application can improve the automation and accuracy of data table sharding, thereby alleviating the read and write pressure of the database.

[0173] In another embodiment, the load-balanced database sharding device can be configured separately from the central processing unit 9100. For example, the load-balanced database sharding device can be configured as a chip connected to the central processing unit 9100, and the load-balanced database sharding function can be implemented through the control of the central processing unit.

[0174] like Figure 10As shown, the electronic device 9600 may further include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is worth noting that the electronic device 9600 does not necessarily need to include these components. Figure 10 All components shown; in addition, the electronic device 9600 may also include Figure 10 For components not shown, please refer to existing technology.

[0175] like Figure 10 As shown, the central processing unit 9100, sometimes also referred to as a controller or operating control, may include a microprocessor or other processor device and / or logic device, which receives inputs and controls the operation of various components of the electronic device 9600.

[0176] The memory 9140 may be, for example, one or more of a cache, flash memory, hard drive, removable media, volatile memory, non-volatile memory, or other suitable devices. It may store the aforementioned failure-related information, and also store a program for executing that information. The central processing unit 9100 may execute the program stored in the memory 9140 to perform information storage or processing, etc.

[0177] Input unit 9120 provides input to central processing unit 9100. Input unit 9120 may be, for example, a keypad or touch input device. Power supply 9170 provides power to electronic device 9600. Display 9160 displays images and text. Display may be, for example, an LCD display, but is not limited thereto.

[0178] The memory 9140 can be a solid-state memory, such as a read-only memory (ROM), random access memory (RAM), a SIM card, etc. It can also be a memory that retains information even when power is off, can be selectively erased, and contains more data; examples of this type of memory are sometimes referred to as EPROMs. The memory 9140 can also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application / function storage unit 9142 for storing application programs and function programs or processes for executing the operation of the electronic device 9600 via the central processing unit 9100.

[0179] The memory 9140 may also include a data storage unit 9143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 9144 of the memory 9140 may include various drivers for the electronic device's communication functions and / or for performing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0180] The communication module 9110 is a transmitter / receiver 9110 that transmits and receives signals via the antenna 9111. The communication module (transmitter / receiver) 9110 is coupled to the central processing unit 9100 to provide input signals and receive output signals, which can be the same as in a conventional mobile communication terminal.

[0181] Based on different communication technologies, multiple communication modules 9110 can be configured in the same electronic device, such as cellular network modules, Bluetooth modules, and / or wireless LAN modules. The communication module (transmitter / receiver) 9110 is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide audio output via the speaker 9131 and receive audio input from the microphone 9132, thereby realizing typical telecommunications functions. The audio processor 9130 may include any suitable buffer, decoder, amplifier, etc. Additionally, the audio processor 9130 is coupled to a central processing unit 9100, enabling on-device recording via the microphone 9132 and on-device playback of stored sound via the speaker 9131.

[0182] As described above, the electronic device provided in the embodiments of this application can improve the automation and accuracy of database sharding, thereby alleviating the read and write pressure on the database.

[0183] Embodiments of this application also provide a computer-readable storage medium capable of implementing all steps of the load-balanced database sharding method in the above embodiments. The computer-readable storage medium stores a computer program that, when executed by a processor, implements all steps of the load-balanced database sharding method in the above embodiments. For example, when the processor executes the computer program, it implements the following steps:

[0184] Step 100: Obtain table statistics and table join information for multiple data tables.

[0185] Step 200: Based on the table statistics and table join information of each data table, perform load balancing to determine the corresponding database instance for each data table and allocate each data table to its corresponding database instance, thus completing the database sharding of each data table.

[0186] As can be seen from the above description, the computer-readable storage medium provided in the embodiments of this application can improve the automation and accuracy of database sharding, thereby alleviating the read and write pressure on the database.

[0187] The various embodiments of the methods described in this application are presented in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on explaining the differences from other embodiments. Relevant details can be found in the descriptions of the method embodiments.

[0188] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0189] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0190] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0191] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0192] This application uses specific embodiments to illustrate the principles and implementation methods of this application. The description of the above embodiments is only for the purpose of helping to understand the method and core ideas of this application. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this application. Therefore, the content of this specification should not be construed as a limitation of this application.

Claims

1. A database sharding method based on load balancing, characterized in that, include: Obtain the full set of structured query language scripts and table statistics for each data table; Based on the full set of structured query language scripts and the preset structured query language parser, the table join information corresponding to each data table is obtained; Load balancing is performed based on the table statistics and table join information of each data table. The corresponding database instance for each data table is determined and each data table is assigned to its corresponding database instance to complete the database sharding of each data table. The database sharding method based on load balancing also includes: If the number of data table groups is greater than the number of database instances, then based on the number of updated rows in the table statistics of each data table in multiple time periods, the average number of updated rows in each time period and the number of updated rows in each data table group in each time period are obtained. The data table groups are sorted according to the average number of updated rows in each time period and the number of updated rows in each time period. The sorted data table groups are then distributed to each database instance in sequence, starting from the first one. The data table group of the unassigned database instance is merged with the data table group of each assigned database instance to obtain the merged data table group; Based on the average number of updated rows in each time period and the number of updated rows in the merged data table group in each time period, the database instance corresponding to the data table group without assigned database instance is determined and assigned to that database instance, thus completing the database sharding of each data table.

2. The database sharding method based on load balancing according to claim 1, characterized in that, The load balancing process based on table statistics and join information of each data table, determining the corresponding database instance for each data table and allocating each data table to its corresponding database instance, includes: Based on the table join information of each data table, the data tables are divided into multiple data table groups. Each data table group includes: multiple data tables with table join relationships or a unique data table without table join relationships. Determine whether the number of data table groups is less than or equal to the number of database instances. If so, assign each data table group to a different database instance.

3. The database sharding method based on load balancing according to claim 1, characterized in that, The process of sorting the data table groups based on the average number of updated rows in each time period and the number of updated rows for each data table group in each time period includes: Based on the average number of updated rows in each time period and the number of updated rows of each data table group in each time period, determine the ratio of the number of updated rows of the data table group to the corresponding average number of updated rows in each time period, and count the number of target time periods where the ratio is greater than a preset first multiple. Sort the data tables from most to least number of target time periods for each group.

4. The database sharding method based on load balancing according to claim 1, characterized in that, The step of determining the database instance corresponding to the unallocated database instance for the data table group based on the average number of updated rows in each time period and the number of updated rows in the merged data table group in each time period, and allocating the data to that database instance to complete the database sharding of each data table, includes: Based on the average number of updated rows in each time period and the number of updated rows of the merged data table group in each time period, determine the ratio of the number of updated rows of the merged data table group to the corresponding average number of updated rows in each time period, and count the number of target time periods in which the ratio is greater than a preset first multiple. The group with the fewest target time periods among the merged data table groups is identified as the target data table group. The data table group without assigned database instances is then assigned to the database instance of the data table group with assigned database instances in the target data table group, thus completing the database sharding of each data table.

5. The database sharding method based on load balancing according to claim 3, characterized in that, The step of sorting the data table groups from most to least the number of target time periods in each group also includes: If there are multiple sets of data tables to be compared with the same number of target time periods, then count the number of time periods for which the ratio of each set of data tables exceeds a preset second multiple, where the preset second multiple is less than a preset first multiple. The comparison data tables are sorted from most to least based on the number of time periods in which the ratio exceeds a preset second multiple.

6. A warehouse distribution device based on load balancing, characterized in that, include: The acquisition module is used to acquire the full set of structured query language scripts and table statistics for each data table; Based on the full set of structured query language scripts and the preset structured query language parser, the table join information corresponding to each data table is obtained; The database sharding module is used to perform load balancing based on the table statistics and table join information of each data table, determine the corresponding database instance for each data table, and allocate each data table to its corresponding database instance to complete the database sharding of each data table. The aforementioned load-balanced database sharding device further includes: The module is used to obtain the average number of updated rows in each time period and the number of updated rows for each data table group in each time period based on the number of updated rows in the table statistics of each data table in multiple time periods if the number of data table groups is greater than the number of database instances. The sorting module is used to sort the data table groups according to the average number of updated rows in each time period and the number of updated rows of each data table group in each time period, and then distribute the sorted data table groups to each database instance in sequence, starting from the first one. The merge module is used to merge the data table groups of unassigned database instances with the data table groups of each assigned database instance to obtain the merged data table group. The allocation module is used to determine the database instance corresponding to the unallocated database instance of the data table group based on the average number of updated rows in each time period and the number of updated rows of the merged data table group in each time period, and to allocate the data table to that database instance, thereby completing the database sharding of each data table.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the database sharding method based on load balancing as described in any one of claims 1 to 5.

8. A computer-readable storage medium storing computer instructions thereon, characterized in that, When the instruction is executed, it implements the database sharding method based on load balancing as described in any one of claims 1 to 5.

Images