Distributed database fragmentation method and related product

Abstract

The invention provides a fragmentation method of a distributed database and a related product. The fragmentation method comprises the steps of obtaining first information of a table object of a to-be-processed database, wherein the first information comprises a basic data volume, an association table and log information of the table object; according to the first information, the fragment type of the table object is determined, and the fragment type comprises a single table, a broadcast table and a fragment table; and fragmenting the table object according to the fragmentation type. According to the fragmentation method of the distributed database, the related information of the table object of the to-be-processed database is obtained, different fragmentation strategies are determined according to the service characteristics of the table object, reasonable fragmentation is carried out, and the hotspot data are uniformly distributed on all the fragments, so that the situation that some nodes are overloaded and other node resources are not fully utilized is avoided, and the fragmentation efficiency is improved. The query efficiency can be optimized, the response time is shortened, and the processing capacity of the whole system is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a distributed database sharding method, a computer-readable storage medium, and a computer program product. Background Art

[0002] At present, with the surge in business data volume, a large number of centralized databases need to be converted into distributed databases to migrate the data in the centralized databases to the distributed databases. In this context, it is particularly important to determine a reasonable and efficient sharding strategy in a distributed database scenario. In related technologies, it is usually necessary to rely on experience to determine the sharding strategy. Specifically, for table objects with primary key tables, hash sharding is performed based on the primary key, and hash sharding is performed based on the unique index if there is no primary key. If the unique index does not exist, hash sharding is performed based on the column order, and the first data type column that can be used as the sharding key is used.

[0003] The above sharding strategy may lead to unreasonable sharding of the database, which in turn may lead to unbalanced loads on each server node (for example, a server node is overloaded). Summary of the invention

[0004] One purpose of the present invention is to perform reasonable and efficient sharding on a centralized database, thereby preventing or reducing the problem of unbalanced business load in a distributed database.

[0005] Specifically, according to one aspect of the present invention, the present invention provides a distributed database sharding method, which includes:

[0006] Acquire first information of a table object of a database to be processed, wherein the first information includes basic data volume, associated tables, and log information of the table object;

[0007] Determine, according to the first information, a shard type of the table object, where the shard type includes a single table, a broadcast table, and a shard table;

[0008] The table object is fragmented according to the fragmentation type.

[0009] Optionally, determining the shard type of the table object according to the first information includes:

[0010] In response to the SQL statement of the table object only including an insert statement, the basic data volume of the table object is less than or equal to a first preset threshold, and the frequency of its insert statements is less than or equal to a second preset threshold, it is determined to be a single table;

[0011] In response to the SQL statement of the table object only including a query statement, the basic data volume of the table object is less than or equal to a third preset threshold, and the number of its associated tables exceeds a fourth preset threshold, it is determined to be a broadcast table;

[0012] If not, it is determined to be a fragmented table.

[0013] Optionally, sharding the table object according to the sharding type includes:

[0014] In response to the table object being a single table, storing the table object in a database node;

[0015] In response to the table object being a broadcast table, storing the table object in all database nodes;

[0016] In response to the table object being a sharded table, a sharding key is generated according to a first preset sharding key algorithm, and a sharding table is generated according to a preset sharding algorithm and stored in multiple database nodes.

[0017] Optionally, the first preset shard key algorithm includes:

[0018] Obtain a preset sharding key strategy, wherein the preset sharding key strategy includes an association priority strategy;

[0019] In response to the preset sharding key strategy being an association priority strategy, determining whether the SQL statement of the table object includes an association field;

[0020] If the associated fields are included, the associated field with the highest frequency is set as the sharding key; if the associated fields are not included, it is determined whether the SQL statement contains a filter field;

[0021] When the SQL statement contains a filter field, the filter field with the highest frequency is set as the shard key; when the SQL statement does not contain the filter field, a shard key is generated according to a second preset shard key algorithm.

[0022] The second preset sharding key algorithm includes:

[0023] Determine whether the table object has a primary key;

[0024] If there is a primary key, use the primary key as the shard key; if there is no primary key, determine whether the table object has a unique index;

[0025] If the table object has a unique index, the unique index is used as the shard key; if the table object does not have a unique index, the first column is used as the shard key.

[0026] Optionally, the first preset shard key algorithm includes:

[0027] Obtain a preset sharding key strategy, wherein the preset sharding key strategy includes a filter priority strategy;

[0028] In response to the preset sharding key strategy being a filter priority strategy, determining whether the SQL statement of the table object includes a filter field;

[0029] If the filter field is included, the one with the highest frequency in the filter field is set as the sharding key; if the filter field is not included, it is determined whether the SQL statement contains the associated field;

[0030] When the SQL statement contains associated fields, the associated field with the highest frequency is set as the sharding key; when the SQL statement does not contain associated fields, a sharding key is generated according to a second preset sharding key algorithm.

[0031] The second preset sharding key algorithm includes:

[0032] Determine whether the table object has a primary key;

[0033] If there is a primary key, use the primary key as the shard key; if there is no primary key, determine whether the table object has a unique index;

[0034] If the table object has a unique index, the unique index is used as the shard key; if the table object does not have a unique index, the first column is used as the shard key.

[0035] Optionally, the first preset shard key algorithm further includes:

[0036] In response to the preset sharding key strategy being empty, acquiring the execution time of the query statement of the table object;

[0037] Determining whether the execution time of the query statement is greater than a fifth preset threshold;

[0038] If so, setting the preset shard key strategy to the association priority strategy;

[0039] If not, obtaining the server resource occupancy rate of the query statement;

[0040] If the server resource usage is greater than a sixth preset threshold, setting the preset sharding key strategy as an association priority strategy;

[0041] If the server resource usage is less than or equal to the sixth preset threshold, the preset sharding key strategy is set as a filtering priority strategy.

[0042] Optionally, the preset sharding algorithm includes:

[0043] Determine whether the shard key is an auto-increment column and the SQL statement of the table object contains a range query;

[0044] If yes, generate shards using range sharding algorithm;

[0045] If not, generate shards using the hash sharding algorithm.

[0046] Optionally, the obtaining of the first information of the table object of the database to be processed includes:

[0047] Acquire the table object, metadata of the table object, an associated table of the table object, and log information of the database to be processed within a first preset time;

[0048] According to the log information, feature extraction is performed on the SQL statements of the table object to obtain at least information on insert statement frequency, query statement frequency, associated field frequency, filter field frequency, and range query frequency.

[0049] According to another aspect of the present invention, there is also provided a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned distributed database sharding methods are implemented.

[0050] According to another aspect of the present invention, a computer program product is provided, which includes a computer program, and when the computer program is executed by a processor, the steps of any one of the above-mentioned distributed database sharding methods are implemented.

[0051] The sharding method of the distributed database of the present invention obtains the relevant information of the table object of the database to be processed, determines different sharding strategies according to the business characteristics of the table object, and performs sharding. Specifically, for small tables with less write business, it can be set as a single table to facilitate the management and query of the distributed database. For small tables with more read business, it can be set as a broadcast table, which can avoid network transmission delays of data, thereby improving the query performance of the distributed database. Other table objects are set as sharded tables, that is, they are divided into multiple parts (shards), and these shards are distributed on different server nodes, thereby improving the read and write performance and scalability of the data, and can effectively disperse the load and improve query efficiency. Through reasonable sharding, the hot data is evenly distributed on each shard, avoiding the situation where some nodes are overloaded and other node resources are not fully utilized, which can optimize query efficiency, reduce response time, and improve the overall system processing capacity.

[0052] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0053] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0054] Figure 1 is a flow chart of a distributed database sharding method according to an embodiment of the present invention;

[0055] Figure 2 is a schematic diagram of a flow chart of object type analysis of a sharding method according to an embodiment of the present invention;

[0056] Figure 3 is a schematic diagram of a flow chart of object type analysis of a sharding method according to an embodiment of the present invention;

[0057] Figure 4 It is a flowchart of an association priority strategy of a sharding method according to an embodiment of the present invention;

[0058] Figure 5 is a flowchart of a second preset sharding key algorithm of a sharding method according to an embodiment of the present invention;

[0059] Figure 6 It is a flowchart of a filtering priority strategy of a sharding method according to an embodiment of the present invention;

[0060] Figure 7 It is a schematic diagram of a process of setting a preset sharding key strategy according to a sharding method according to an embodiment of the present invention;

[0061] Figure 8 is a flow chart of a preset sharding algorithm of a sharding method according to an embodiment of the present invention;

[0062] Fig. 9 It is a schematic diagram of a flow chart of a sharding table strategy pre-analysis of a sharding method according to an embodiment of the present invention;

[0063] Fig.10 is a schematic flow chart of a sharding method according to an embodiment of the present invention;

[0064] Fig.11 is a schematic diagram of a flow chart of a sharding strategy analysis of a sharding method according to an embodiment of the present invention;

[0065] Fig.12 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0066] Fig.13 is a schematic diagram of a computer program product according to an embodiment of the present invention. DETAILED DESCRIPTION

[0067] The purpose of the distributed database sharding method of this embodiment is to convert the centralized database into a distributed database, and to reasonably and efficiently shard the business table objects of the centralized database, thereby preventing or reducing the problem of unbalanced business load in the distributed database.

[0068] Figure 1 1 is a flow chart of a distributed database sharding method according to an embodiment of the present invention. The method may generally include:

[0069] S100, obtaining first information of a table object of a database to be processed, the first information including basic data volume, associated tables and log information of the table object;

[0070] S200, determining a shard type of a table object according to the first information, where the shard types include a single table, a broadcast table, and a shard table;

[0071] S300: Shard the table object according to the sharding type.

[0072] In this embodiment, the database to be processed may be a centralized database. The table object may be a business-related table in the database. The first information may include the basic data volume of the table object, the associated table, and the historical log information of the database operation. For example, SQL statement information in the log information of the past 7*24 hours may be collected. The first information may also include metadata information of the table object, and the metadata information includes column name, column data type, primary key column, unique index column, index column information, etc.

[0073] After data collection is performed to obtain the first information, a preset program may be set to perform data preprocessing, data feature extraction, and data analysis on the first information.

[0074] Data preprocessing can filter and normalize data. Specifically, it can include retrieving all SQL statements in the log information, excluding SQL statements that do not contain table objects, excluding SQL statements that only contain system tables, that is, only retaining SQL statements that operate on business tables. The same SQL statements are merged and the SQL statements and their frequencies are recorded.

[0075] Data feature extraction includes classifying the pre-processed data according to the table object, column name, column data type, primary key column, unique index column, index column, table object basic data volume, and SQL statement feature dimensions. It also includes classifying the SQL statement features of the business table according to the SQL statement insertion (Insert) frequency, upload (Update) frequency, deletion (Delete) frequency, query (Select) frequency, associated fields and occurrence frequency, filter fields and occurrence frequency, associated tables, range query fields and occurrence frequency dimensions.

[0076] Data analysis can determine the business characteristics of table objects, and then determine different sharding strategies and perform sharding based on the business characteristics of table objects. Specifically, for small tables with less write business (usually referring to table objects with relatively small basic data volume), they can be set as single tables to facilitate the management and query of distributed databases. For small tables with more read business, they can be set as broadcast tables to avoid network transmission delays of data, thereby improving the query performance of distributed databases. Setting other table objects as sharded tables means dividing them into multiple parts (shards) and distributing these shards on different server nodes, thereby improving the read and write performance and scalability of data, effectively dispersing the load and improving query efficiency. Through reasonable sharding, hot data is evenly distributed on each shard, avoiding the situation where some nodes are overloaded and other node resources are not fully utilized, which can optimize query efficiency, reduce response time, and improve the overall system processing capacity.

[0077] In some embodiments of the sharding method of the present invention, the step of obtaining the first information of the table object of the database to be processed includes:

[0078] First, obtain the table object, the metadata of the table object, the associated table of the table object, and the log information of the database to be processed within a first preset time;

[0079] Secondly, based on the log information, feature extraction is performed on the SQL statements of the table objects to obtain at least the frequency of insert statements, the frequency of query statements, the frequency of associated fields, the frequency of filtered fields, and the frequency of range queries.

[0080] In this embodiment, the first preset time may be n*24 hours, where n is set as needed. When the SQL statement feature within the first preset time is empty, the first preset time may be appropriately increased, for example, the first preset time may be set to (n+1)*24, and then data collection and data preprocessing are performed again, and data features are extracted until the SQL statement feature in the business table is not empty.

[0081] In some embodiments of the sharding method of the present invention, Figure 2As shown, the step of determining the shard type of the table object according to the first information includes:

[0082] S211, in response to the SQL statement of the table object only including an insert statement, the basic data volume of the table object is less than or equal to a first preset threshold, and the frequency of its insert statements is less than or equal to a second preset threshold, it is determined to be a single table;

[0083] S212, in response to the SQL statement of the table object only including the query statement, the basic data volume of the table object is less than or equal to the third preset threshold, and the number of its associated tables exceeds the fourth preset threshold, it is determined to be a broadcast table;

[0084] S213: If not, determine it as a fragmented table.

[0085] This embodiment is used to perform object type analysis, and the first preset threshold and the third preset threshold are both preset values, usually in bytes, and are used to limit the size of single tables and broadcast tables. Generally speaking, single tables and broadcast tables are small tables. When the amount of basic data is large, setting them as single tables or broadcast tables will reduce the query performance of the distributed database.

[0086] The second preset threshold and the fourth preset threshold are both preset values, usually in units of the number of executions within a unit time period (e.g., times / second, times / hour, etc.). When an insert-type table object frequently executes insert statements, setting it as a single table will reduce its read performance, and setting it as a shard table can improve the read performance. When a query-type table object has fewer associated tables, setting it as a broadcast table will occupy storage space, resulting in a waste of server node performance, and setting it as a shard table can improve server performance utilization.

[0087] For table objects that are not set as single tables or broadcast tables, they are set as shard tables to disperse the data volume, effectively distribute the load, optimize query efficiency, reduce response time, and improve the overall system processing capability.

[0088] like Figure 3 As shown, the steps of performing object type analysis may specifically include:

[0089] S231, determining whether the SQL statement of the table object only contains insert statements;

[0090] If yes, execute S232; if no, execute S241;

[0091] S232, determining whether the basic data volume of the table object is less than or equal to a first preset threshold;

[0092] If not, execute S233; if so, execute S249;

[0093] S233, determining whether the frequency of insert statements of the table object is less than or equal to a second preset threshold;

[0094] If yes, execute S234; if no, execute S249;

[0095] S234, determined to be a single table;

[0096] S241, determining whether the SQL statement of the table object only contains query statements;

[0097] If yes, execute S242; if no, execute S249;

[0098] S242, determining whether the basic data volume of the table object is less than or equal to a third preset threshold;

[0099] If yes, execute S243; if no, execute S249;

[0100] S243, determining whether the number of associated tables of the table object exceeds a fourth preset threshold;

[0101] If yes, execute S244; if no, execute S249;

[0102] S244, determining that it is a broadcast table;

[0103] S249: Determine it as a fragmentation table.

[0104] In some embodiments of the sharding method of the present invention, Figure 2 As shown, the sharding of the table object according to the sharding type includes:

[0105] S311, in response to the table object being a single table, storing the table object in a database node;

[0106] S312, in response to the table object being a broadcast table, storing the table object in all database nodes;

[0107] S313: In response to the table object being a sharded table, a sharding key is generated according to a first preset sharding key algorithm, and a sharding table is generated according to a preset sharding algorithm, and is stored in multiple database nodes.

[0108] A broadcast table is also called a replicated table, which means that a copy is stored in each database node to avoid network transmission delays when performing connection operations on distributed databases, thereby improving query performance.

[0109] The number of shards in a shard table can be a preset value, which can be set according to actual needs. By splitting the shard table and storing it in multiple database nodes, the amount of data can be dispersed, the load can be effectively dispersed, query efficiency can be optimized, response time can be reduced, and the overall system processing capacity can be improved.

[0110] In some embodiments of the sharding method of the present invention, Figure 4-5As shown, the first preset sharding key algorithm includes:

[0111] S431, obtaining a preset sharding key strategy, where the preset sharding key strategy includes an association priority strategy;

[0112] S432, in response to the preset sharding key strategy being the association priority strategy, determining whether the SQL statement of the table object contains an association field;

[0113] S433: If yes, set the most frequent field in the associated field as the shard key;

[0114] S434, if not, determine whether the SQL statement contains a filter field;

[0115] S435: If yes, set the filter field with the highest frequency as the sharding key;

[0116] S436: If not, generate a shard key according to a second preset shard key algorithm.

[0117] The second preset sharding key algorithm includes:

[0118] S511, determining whether the table object has a primary key;

[0119] S512: If yes, use the primary key as the shard key;

[0120] S513, if not, determine whether the table object has a unique index;

[0121] S514: If yes, use the unique index as the shard key;

[0122] S515: If not, use the first column as the shard key.

[0123] In a distributed scenario, if the sharding keys are different when querying tables, federated queries will be triggered, resulting in poor query performance. If the filtering condition is not the sharding key, full routing will occur during SQL statement execution, resulting in a waste of database resources. To balance the relationship between the two, you can set preset sharding key strategies with different priorities for business tables. The preset sharding key strategy can be set to association priority strategy, filtering priority strategy, etc.

[0124] In this embodiment, the preset sharding key strategy is the association priority strategy. By setting the most frequent field in the association field as the sharding key, it is avoided to cause federated query, thereby improving the query performance of the distributed database.

[0125] The second preset sharding key algorithm is a conventional sharding key algorithm, which will not be described here.

[0126] Exemplarily, in step S433, the following operations may also be included:

[0127] If multiple related fields appear at the same frequency, the related field with the highest frequency in the filter field is selected as the shard key.

[0128] In some embodiments of the sharding method of the present invention, Figure 6 As shown, the first preset sharding key algorithm includes:

[0129] S451, obtaining a preset sharding key strategy, where the preset sharding key strategy includes a filter priority strategy;

[0130] S452, in response to the preset sharding key strategy being the filter priority strategy, determining whether the SQL statement of the table object contains a filter field;

[0131] S453: If yes, set the filter field with the highest frequency as the sharding key;

[0132] S454, if not, determine whether the SQL statement contains a related field;

[0133] S455: If yes, set the most frequent field in the associated field as the shard key;

[0134] S456: If not, generate a shard key according to a second preset shard key algorithm.

[0135] In this embodiment, the preset sharding key strategy is a filter priority strategy. By setting the filter field with the highest frequency as the sharding key, it is possible to avoid causing full routing in the SQL statement execution, thereby improving the utilization rate of distributed database resources.

[0136] Exemplarily, in step S453, the following operations may also be included:

[0137] If multiple filter fields appear with the same frequency, the filter field with the highest frequency in the associated fields is selected as the shard key.

[0138] In some embodiments of the sharding method of the present invention, Figure 7 As shown, the first preset sharding key algorithm also includes:

[0139] S411, in response to the preset sharding key strategy being empty, obtaining the execution time of the query statement of the table object;

[0140] S412, determining whether the execution time of the query statement is greater than a fifth preset threshold;

[0141] S413: If yes, set the preset shard key strategy to the association priority strategy;

[0142] S414, if not, obtaining the server resource occupancy rate of the query statement;

[0143] S415, if the server resource usage is greater than the sixth preset threshold, setting the preset sharding key strategy as the association priority strategy;

[0144] S416: If the server resource usage is less than or equal to the sixth preset threshold, the preset sharding key strategy is set as the filtering priority strategy.

[0145] This embodiment is used when the preset sharding key strategy is not pre-set. The fifth preset threshold is the time upper limit, which can be set as needed, for example, it can be set to 1000ms. In this embodiment, when the query statement execution time is long, it may cause a federated query. To improve query performance, the preset sharding key strategy can be set to an association priority strategy.

[0146] Server resources may include CPU, disk IO, memory and other usage conditions, which are usually acquired by the monitoring feedback module and can be obtained through log information. The server resource occupancy rate may be the weighted occupancy rate of the above resources. The sixth preset threshold is a preset value, which is usually a percentage, for example, it can be set to 70%. In this embodiment, when the server resource occupancy rate is high when the query statement is executed, a federated query may be caused. In order to improve query performance, the preset sharding key strategy can be set to an association priority strategy.

[0147] In some embodiments of the sharding method of the present invention, Figure 8 As shown, the preset sharding algorithms include:

[0148] S611, determine whether the sharding key is an auto-increment column, and the SQL statement of the table object contains a range query;

[0149] S612: If yes, generate fragments using the range fragmentation algorithm;

[0150] S613: If not, generate shards using the hash sharding algorithm.

[0151] Sharding algorithms in distributed scenarios usually include hash sharding algorithms and range sharding algorithms. Hash sharding has good load balancing characteristics, and range sharding has efficient data access performance for a specific range. If the business table sharding key is an auto-increment column and the SQL statement features include range queries, range sharding is used to improve the efficiency of range queries. In other cases, hash sharding algorithms are selected to have better load balancing characteristics.

[0152] In some embodiments of the sharding method of the present invention, Fig. 9 As shown in the figure, after determining the shard type of the table object, you can also perform the following steps:

[0153] S711, determining whether the SQL statement of the table object only contains insert statements;

[0154] If yes, execute S712; if no, execute 721;

[0155] S712, determining whether the basic data volume of the table object is greater than a first preset threshold, or whether the frequency of insert statements of the table object is greater than a second preset threshold;

[0156] If yes, execute S713; if no, execute 721;

[0157] S713, generating a shard key according to a second preset shard key algorithm, and generating shards according to the preset shard algorithm;

[0158] S721, generate a shard key according to a first preset shard key algorithm, and generate shards according to the preset shard algorithm.

[0159] This embodiment is used to perform pre-analysis of sharding table strategies. For insert-type table objects, when the amount of basic data is large or the frequency of insertion is high, it will not cause a federated query during an associated query, nor will it cause a full routing situation during a filtering condition. Sharding is performed directly according to the second preset sharding key algorithm, which can improve the sharding speed and efficiency. For other situations that are prone to causing federated queries or full routing situations, sharding is performed according to the preset sharding key strategy, thereby improving the performance and efficiency of the distributed database.

[0160] In some embodiments of the sharding method of the present invention, Fig.10 As shown, the sharding method may also include:

[0161] S811, data collection and preprocessing;

[0162] S812, data feature extraction;

[0163] S813, Sharding Strategy Analysis;

[0164] S814, monitoring feedback.

[0165] Among them, the sharding strategy analysis may include:

[0166] S821, object type analysis;

[0167] S822, shard table strategy pre-analysis;

[0168] S823, Sharding table strategy analysis.

[0169] In this embodiment, sharding strategy analysis is the data analysis step after data feature extraction. Object type analysis is used to determine the sharding type of the table object, i.e., single table, broadcast table, and sharded table. The sharding table strategy pre-analysis is used to determine whether to use the first preset sharding key algorithm or the second preset sharding key algorithm for sharding. Specifically, for insert-type table objects, when the amount of basic data is large, or the frequency of insertion is large, it will not cause the federated query situation during the associated query, nor will it cause the full routing situation during the filtering condition. Sharding directly according to the second preset sharding key algorithm can improve the sharding speed and efficiency. In other cases, sharding is performed according to the first preset sharding key algorithm to form a balance between associated queries and filtering conditions, thereby improving the performance of the distributed database. The sharding table strategy analysis is used to generate a sharding key according to the second preset sharding key algorithm, and to perform sharding according to the preset sharding algorithm.

[0170] In this embodiment, after the table object is sharded, monitoring feedback can also be performed. Through monitoring feedback, the relevant parameters (first preset threshold, second preset threshold, third preset threshold, fourth preset threshold, fifth preset threshold, sixth preset threshold, first preset time, etc.) in the sharding strategy analysis step are continuously improved, so that the optimal sharding strategy can be intelligently identified according to the actual application business scenario, data characteristics, and resource usage. A reasonable sharding strategy can be determined based on basic data and data access conditions, and business SQL types, and hot data can be evenly distributed on each shard to avoid overloading of some nodes and underutilization of other node resources, which helps to optimize query efficiency, reduce response time, and improve the overall system processing capacity.

[0171] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes may be made to the above method.

[0172] It should be understood that in some embodiments, each part can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0173] This embodiment also provides a computer program product 10 and a computer readable storage medium 20 . Fig.13 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Fig.12is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which implements the steps of any of the above-mentioned sharding methods when executed by a processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11, which implements the steps of the management method of a data table with a large object column in any of the above-mentioned embodiments when executed by a processor 32.

[0174] The computer program 11 for performing the operation of the present invention may be an assembly instruction, an instruction set architecture (ISA) instruction, a machine instruction, a machine-related instruction, a microcode, a firmware instruction, a state setting data, a configuration data of an integrated circuit, or a source code or an object code written in any combination of one or more programming languages ​​and process programming languages. The computer program 11 may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network (including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., using an Internet service provider via the Internet). In some embodiments, in order to perform various aspects of the present invention, an electronic circuit including, for example, a programmable logic circuit, a field programmable gate array (FPGA) or a programmable logic array (PLA) may execute computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit.

[0175] In the description of this embodiment, the computer program product 10 is a related product including the computer program 11 .

[0176] For the purpose of the description of the present embodiment, the computer readable storage medium 20 is a tangible device capable of retaining and storing the computer program 11, which can be any device that can contain, store, communicate, propagate or transmit the computer program 11 for use with an instruction execution system, device or apparatus or in conjunction with these instruction execution systems, devices or apparatuses. More specific examples (a non-exhaustive list) of the computer readable storage medium 20 include the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, and any suitable combination of the above.

[0177] The computer program product 10 can be run on a computer device. The computer device may include a memory, a processor 32, and a computer program 11 stored in the memory and running on the processor 32. The computer device may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device may be a cloud computing node. The computer device may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, target programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device may be implemented in a distributed cloud computing environment where remote processing devices linked through a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0178] The computer device may include a processor 32 adapted to execute stored instructions, and a memory that provides temporary storage space for the operation of the instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0179] The computer device may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows input and output of data with external devices that may be connected to the computer device. The network adapter / interface may provide communication between the computer device and a network, which is usually shown as a communication network.

[0180] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A distributed database sharding method, characterized in that: include: Acquire first information of a table object of a database to be processed, wherein the first information includes basic data volume, associated tables, and log information of the table object; Determine, according to the first information, a shard type of the table object, where the shard type includes a single table, a broadcast table, and a shard table; The table object is fragmented according to the fragmentation type.

2. The sharding method according to claim 1, characterized in that: The determining the shard type of the table object according to the first information includes: In response to the SQL statement of the table object only including an insert statement, the basic data volume of the table object is less than or equal to a first preset threshold, and the frequency of its insert statements is less than or equal to a second preset threshold, it is determined to be a single table; In response to the SQL statement of the table object only including a query statement, the basic data volume of the table object is less than or equal to a third preset threshold, and the number of its associated tables exceeds a fourth preset threshold, it is determined to be a broadcast table; If not, it is determined to be a fragmented table.

3. The sharding method according to claim 1, characterized in that: The sharding of the table object according to the sharding type includes: In response to the table object being a single table, storing the table object in a database node; In response to the table object being a broadcast table, storing the table object in all database nodes; In response to the table object being a sharded table, a sharding key is generated according to a first preset sharding key algorithm, and a sharding table is generated according to a preset sharding algorithm and stored in multiple database nodes.

4. The sharding method according to claim 3, characterized in that: The first preset sharding key algorithm includes: Obtain a preset sharding key strategy, wherein the preset sharding key strategy includes an association priority strategy; In response to the preset sharding key strategy being an association priority strategy, determining whether the SQL statement of the table object includes an association field; If the associated fields are included, the associated field with the highest frequency is set as the sharding key; if the associated fields are not included, it is determined whether the SQL statement contains a filter field; In the case where the SQL statement contains a filter field, the filter field with the highest frequency is set as a shard key; in the case where the SQL statement does not contain the filter field, a shard key is generated according to a second preset shard key algorithm; The second preset sharding key algorithm includes: Determine whether the table object has a primary key; If there is a primary key, use the primary key as the shard key; if there is no primary key, determine whether the table object has a unique index; If the table object has a unique index, the unique index is used as the shard key; if the table object does not have a unique index, the first column is used as the shard key.

5. The sharding method according to claim 3, characterized in that: The first preset sharding key algorithm includes: Obtain a preset sharding key strategy, wherein the preset sharding key strategy includes a filter priority strategy; In response to the preset sharding key strategy being a filter priority strategy, determining whether the SQL statement of the table object includes a filter field; If the filter field is included, the one with the highest frequency in the filter field is set as the sharding key; if the filter field is not included, it is determined whether the SQL statement contains the associated field; If the SQL statement contains associated fields, the associated field with the highest frequency is set as a sharding key; if the SQL statement does not contain associated fields, a sharding key is generated according to a second preset sharding key algorithm; The second preset sharding key algorithm includes: Determine whether the table object has a primary key; If there is a primary key, use the primary key as the shard key; if there is no primary key, determine whether the table object has a unique index; If the table object has a unique index, the unique index is used as the shard key; if the table object does not have a unique index, the first column is used as the shard key.

6. The slicing method according to claim 4 or 5, characterized in that: The first preset shard key algorithm also includes: In response to the preset sharding key strategy being empty, acquiring the execution time of the query statement of the table object; Determining whether the execution time of the query statement is greater than a fifth preset threshold; If so, setting the preset shard key strategy to the association priority strategy; If not, obtaining the server resource occupancy rate of the query statement; If the server resource occupancy is greater than the sixth preset threshold, the preset shard key strategy is set as the association priority strategy; if the server resource occupancy is less than or equal to the sixth preset threshold, the preset shard key strategy is set as the filtering priority strategy.

7. The sharding method according to claim 3, characterized in that: The preset sharding algorithm includes: Determine whether the shard key is an auto-increment column and the SQL statement of the table object contains a range query; If yes, generate shards using range sharding algorithm; If not, generate shards using the hash sharding algorithm.

8. The sharding method according to claim 1, characterized in that: The step of obtaining the first information of the table object of the database to be processed includes: Acquire the table object, metadata of the table object, an associated table of the table object, and log information of the database to be processed within a first preset time; According to the log information, feature extraction is performed on the SQL statements of the table object to obtain at least information on insert statement frequency, query statement frequency, associated field frequency, filter field frequency, and range query frequency.

9. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and when the computer program is executed by a processor, the steps of the distributed database sharding method as described in any one of claims 1 to 8 are implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the distributed database sharding method as described in any one of claims 1 to 8 are implemented.

Images