Table division method of database and related product

Abstract

The invention provides a table dividing method of a database and a related product. The method comprises the following steps: performing feature extraction on data tables in a database to obtain feature elements of the data tables; clustering the data tables according to the feature elements to obtain a plurality of clusters; obtaining a table type corresponding to each cluster, and determining a table type of each data table according to the cluster where each data table is located and the table type corresponding to each cluster; and determining table division statements of the data tables according to the table types of the data tables to generate table division strategies of the database, and performing table division processing on the database according to the table division strategies. According to the technical scheme, the database partitioning accuracy and the working efficiency can be improved, and the purpose of improving the user experience is achieved.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a database table partitioning method and related products. Background Art

[0002] A centralized database is a database system that stores all data on a central server. Therefore, the data stored on the centralized database has the characteristics of large data volume and multiple data types, which is not convenient for performance testing. In order to solve the above problems, the centralized database can be split into multiple parts, and each part stores a type of data table. Then, the data of each part is tested separately to obtain the overall performance of the centralized database.

[0003] In the process of dividing a centralized database into multiple parts, Sharding technology is needed. Sharding technology is a technology used for database partitioning. The basic idea of ​​this technology is to divide a large database into multiple parts, which are called data fragments. Since each data fragment contains a part of the original database data, and these parts are logically complete, each data fragment can also perform data operations independently.

[0004] In the process of using Sharding technology to shard the centralized database, it is necessary to first determine the type of each data table in the centralized database, then build the corresponding sharding rules according to the type of each data table, and then shard the database according to the sharding rules. At present, each data table is usually divided into three types, namely single table, sharding table, and broadcast table. Among them, the broadcast table refers to a database with a small amount of data and frequent connection queries with data tables with large amounts of data in the business; the single table refers to a small amount of data, and the business query generally only involves a single data table; the sharding table refers to a data table with a large amount of data, few repeated field values, and frequent access.

[0005] In the prior art, in the process of determining the type of a data table, staff are required to analyze the data table according to business needs and experience to determine the type of each data table. Since deviations may occur in the process of staff analyzing the data table, and a large amount of time and labor costs are required when the number of data tables in the centralized database is large, this will cause the database sub-library to have poor accuracy and low work efficiency. Summary of the invention

[0006] The purpose of the present invention is to provide a database table partitioning method and related products, which can improve the accuracy and work efficiency of database partitioning to achieve the purpose of improving user experience.

[0007] In a first aspect, the present invention provides a method for partitioning a database, comprising:

[0008] Performing feature extraction on the data tables in the database to obtain feature elements of each of the data tables;

[0009] Clustering each of the data tables according to each of the characteristic elements to obtain a plurality of clusters;

[0010] Acquire the table type corresponding to each cluster, and determine the table type of each data table according to the cluster to which each data table belongs and the table type corresponding to each cluster;

[0011] The table partitioning statements of each data table are determined according to the table type of each data table to generate a table partitioning strategy for the database, and the database is partitioned according to the table partitioning strategy.

[0012] Furthermore, the step of clustering each of the data tables according to each of the characteristic elements to obtain a plurality of clusters includes:

[0013] Obtaining business characteristics of each table type;

[0014] Selecting a data table of each table type according to each business type to serve as an initial cluster center of each cluster;

[0015] Each data table is clustered according to each of the initial cluster centers and each of the characteristic elements, so as to allocate each of the data tables to a corresponding cluster.

[0016] Furthermore, the step of obtaining the service characteristics of each table type includes:

[0017] If the table type is a broadcast table, the service characteristics include: multiple connection queries with a data table with a larger data volume than itself in the service;

[0018] If the table type is a single table, the business characteristics include: only a single data table is involved in the business query;

[0019] If the table type is a sharded table, the business characteristics include: the repetition value of the field is less than a set repetition threshold, and the access frequency is greater than a set frequency threshold.

[0020] Furthermore, the step of clustering each data table according to each of the initial cluster centers and each of the characteristic elements includes:

[0021] Calculating the distance between each of the data tables and each of the initial cluster centers according to each of the characteristic elements, and assigning each of the data tables to the cluster corresponding to the initial cluster center with the closest distance;

[0022] Obtaining a new cluster center of each of the clusters, and calculating the distance between each of the data tables and each of the new cluster centers according to each of the characteristic elements, and assigning each of the data tables to the cluster corresponding to the new cluster center with the closest distance;

[0023] Determining whether the clustering result of each of the data tables meets a preset clustering termination condition;

[0024] If not, return to the step of obtaining a new cluster center for each cluster.

[0025] Furthermore, after the step of clustering the data tables according to the characteristic elements, the method further includes:

[0026] Determine whether the clustering effect of each of the data tables meets the expected effect;

[0027] If not, return to the step of clustering each of the data tables according to each of the characteristic elements.

[0028] Furthermore, the step of judging whether the clustering effect of each of the data tables meets a preset effect includes:

[0029] Calculating the silhouette coefficient of each of the data tables, and determining whether each of the silhouette coefficients is greater than a set coefficient threshold;

[0030] If yes, it is determined that the clustering effect meets the expected effect;

[0031] If not, it is determined that the clustering effect does not meet the expected effect.

[0032] Furthermore, the step of determining the table splitting statement of each data table according to the table type of each data table includes:

[0033] If the table type of the data table is a sharded table, the sharding statement is a statement for inputting the table name, sharding key and sharding algorithm of the data table;

[0034] If the table type is a broadcast table, the table partition statement is a statement for inputting the table name of the data table.

[0035] Furthermore, before the step of clustering the data tables according to the characteristic elements, the method further includes:

[0036] Data cleaning is performed on each of the characteristic elements, and / or data in each of the characteristic elements is normalized.

[0037] In a second aspect, the present invention further provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of any one of the above table partitioning methods.

[0038] In a third aspect, the present invention further provides a computer program product, including a computer program, which, when executed by a processor, implements the steps of any of the above table partitioning methods.

[0039] The technical solution provided by the present invention can first cluster each data table according to the characteristic elements of each data table in the database to assign each data table to a corresponding clustering cluster; then determine the table type of each data table according to the table type corresponding to each clustering cluster; finally determine the table partitioning statement of each data table according to the table type of each data table to generate a table partitioning strategy for the database. Due to the technical solution of the present invention, it is possible to cluster data tables according to the characteristic elements of the data tables to accurately determine the table type of each data table. Therefore, compared with the method in the prior art in which the type of the data table is determined by the staff based on work experience, the accuracy of obtaining the table type of the data table can be improved, thereby providing a reliable table partitioning strategy for the database, thereby achieving the purpose of improving the accuracy of database table partitioning.

[0040] 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

[0041] 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:

[0042] Figure 1 is a schematic flow chart of a database table partitioning method according to an embodiment of the present invention;

[0043] Figure 2 is a schematic flow chart of clustering characteristic elements of each data table according to an embodiment of the present invention;

[0044] Figure 3 is a schematic flow chart of clustering data tables using a k-means algorithm according to an embodiment of the present invention;

[0045] Figure 4 is a schematic flow chart of a database table partitioning method according to another embodiment of the present invention;

[0046] Figure 5is a schematic diagram of a computer program product according to an embodiment of the present invention; and

[0047] Figure 6 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention. DETAILED DESCRIPTION

[0048] Refer to the following Figures 1 to 6 To describe a database table subdivision method and related products of an embodiment of the present invention. In the description of this embodiment, it should be understood that the terms "first" and "second" are only used for descriptive purposes, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features, that is, include one or more of the features. In the description of the present invention, the meaning of "multiple" is at least two, such as two, three, etc., unless otherwise clearly and specifically defined. When a feature "includes or contains" one or some of the features it covers, unless otherwise specifically described, this indicates that other features are not excluded and may further include other features.

[0049] This embodiment provides a database table division method, which can divide the database according to the type of each data table in the database during the test of the centralized database, so as to convert the database from centralized to distributed. The technical solution of this embodiment can achieve the purpose of improving work efficiency under the premise of accurately classifying each data table in the database.

[0050] See also Figure 1 , Figure 1 FIG. 1 is a schematic flow chart of a method for partitioning a database table according to an embodiment of the present invention. The method may generally include the following steps:

[0051] Step S102: extracting features from each data table in the database to obtain feature elements of each data table;

[0052] Step S104: clustering each data table according to each characteristic element to assign each data table to a plurality of clusters;

[0053] Step S106: obtaining the table type corresponding to each cluster, and determining the table type of each data table according to the cluster to which each data table belongs and the table type corresponding to each cluster;

[0054] Step S108: determining the table partitioning statement of each data table according to the table type of each data table to generate a table partitioning strategy for the database;

[0055] Step S110: partition the database into tables according to the database partition strategy.

[0056] In the above step S102, feature extraction can be performed on each data table in the database according to the business scenario of the database and the data characteristics of each data table in the database to obtain the feature elements of each data table. For example, the feature elements of the extracted data table may include the table name of the data table, the amount of data in the table, the access frequency of the data table within a specified time, whether it is the right table involved in the connection query, the most commonly used field name in the query and whether the field is a primary key column, the percentage of repeated values ​​in the field, and the distribution of hot data.

[0057] In the above characteristic elements, whether it is the right table participating in the connection query and whether the most commonly used field name in the query is the primary key column are represented by Boolean values, that is, if the data table is the right table participating in the connection query, the value of this characteristic element is 1; if the data table is not the right table participating in the connection query, the value of this characteristic element is 0. If the most commonly used field name in the query is the primary key column, the value of this characteristic element is 1; if the most commonly used field name in the query is not the primary key column, the value of this characteristic element is 0. The name of the data table and the most commonly used field name in the query are character types, and other characteristic elements are all numeric types.

[0058] In the above step S104, a preset calculation method can be first used to calculate the distance between each data table according to each characteristic element; and then a preset clustering algorithm can be used to cluster each data table according to the distance of each data table, so as to assign each data table to a corresponding cluster cluster, thereby realizing clustering of each data table and obtaining a set number of cluster clusters.

[0059] In the above step S106, the table type corresponding to each cluster can be obtained according to the business characteristics of the data table in each cluster, wherein each cluster corresponds to a table type. Then, according to the table type corresponding to each cluster, the table type of each data table is obtained. Taking one of the data tables as an example, if the data table belongs to the first cluster, the table type of the data table is the first table type corresponding to the first cluster.

[0060] In the above step S108, statement generation rules for each table type may be pre-set, and then after obtaining the table type of each data table, table partitioning statements for each data table may be generated according to the statement generation rules corresponding to the table type of each data table to obtain a table partitioning strategy for the database.

[0061] In the above step S110, after the database executes each table division statement in the classification strategy, each data table in the database can be divided according to the table type, so as to facilitate the performance test of the database.

[0062] According to the above content, the database table partitioning method provided by this embodiment can first cluster each data table according to the characteristic elements of each data table in the database to assign each data table to a corresponding clustering cluster; then determine the table type of each data table according to the table type corresponding to each clustering cluster; finally, determine the table partitioning statement of each data table according to the table type of each data table to generate a database table partitioning strategy, and perform table partitioning on the database according to the table partitioning strategy. Due to the technical solution of this embodiment, it is possible to cluster data tables by the characteristic elements of the data tables to accurately determine the table type of each data table. Therefore, compared with the method in the prior art in which the staff determines the type of the data table according to work experience, it is possible to improve the accuracy of obtaining the table type of the data table, thereby providing a reliable table partitioning strategy for the database, and achieving the purpose of improving the accuracy of database table partitioning.

[0063] In some embodiments of the present invention, in the above step S104, the method of clustering each data table according to each characteristic element is as follows: Figure 2 As shown, the following steps are included:

[0064] Step S202: Obtaining business characteristics of each table type;

[0065] Step S204: selecting a data table of each table type from the database according to the business characteristics of each table type, to serve as the initial cluster center of each cluster;

[0066] Step S206: clustering each data table according to the initial cluster center of each cluster and the characteristic elements of each data table, so as to allocate each data table to a corresponding cluster.

[0067] In the above step S202, the business characteristics of each table type may be preset and stored in the database, and when the business characteristics of the table type need to be used, they may be obtained by data reading.

[0068] In the above step S204, a data table of each table type can be randomly selected from the data tables of the database according to the business characteristics of each table type to serve as the initial cluster center of each cluster. For example, assuming that the above set number is k, and the value of k is preferably 3, firstly, a data table matching the business characteristics of the first table type is randomly selected from each data table as the initial cluster center of the cluster corresponding to the first table type; then, a data table matching the business characteristics of the second table type is randomly selected from each data table as the initial cluster center of the cluster corresponding to the second table type; finally, a data table matching the business characteristics of the third table type is randomly selected from each data table as the initial cluster center of the cluster corresponding to the third table type.

[0069] In the above step S206, the distance between each data table and each initial clustering center can be calculated based on the characteristic elements of each data table and the characteristic elements of each initial clustering center. The distance can be Euclidean distance or cosine distance; and then each data table is assigned to the cluster corresponding to the initial clustering center that is closest to it, so as to achieve clustering of each data table.

[0070] The technical solution of this embodiment obtains the initial cluster center of the corresponding cluster cluster according to the business characteristics of each table type, which can not only ensure that the number of table types and the number of cluster clusters remain consistent, but also allocate data tables of the same table type to the same cluster cluster to improve the accuracy of clustering each data table.

[0071] In some embodiments of the present invention, in the above step S206, the k-means algorithm can be used to cluster the data tables. The clustering method is as follows: Figure 3 As shown, the following steps are included:

[0072] Step S212: Calculate the distance between each data table and each initial cluster center according to the characteristic elements of each data table;

[0073] Step S214: assigning each data table to the cluster corresponding to the initial cluster center closest to it;

[0074] Step S216: calculating the new cluster center of each cluster, and calculating the distance between each data table and each new cluster center according to the characteristic elements of each data table;

[0075] Step S218: assigning each data table to the cluster corresponding to the new cluster center that is closest to it;

[0076] Step S220: determining whether the clustering result of each data table satisfies a preset clustering termination condition;

[0077] If yes, the clustering of each data table is completed; if no, return to step S216.

[0078] In the above step S212, since each data table has multiple characteristic elements, the distance between each data table and each initial cluster center can be obtained according to the difference between the characteristic values ​​of the corresponding characteristic elements in each data table and each initial cluster center.

[0079] Taking an application scenario as an example, assume that each data table has N characteristic elements, and the characteristic value of the i-th characteristic element of the n-th data table is V n,i , the eigenvalue of the i-th data in the m-th initial cluster center is W m,i , then the distance D between the nth data table and the mth initial cluster center n,mIt can be calculated by the following formula:

[0080]

[0081] In the above step S214, if the mth initial clustering center has the shortest distance from the nth data table among the k initial clustering centers, the nth data table is assigned to the cluster corresponding to the mth initial clustering center. This process is repeated until all data tables are assigned to the clusters corresponding to the initial clustering centers, thus completing the clustering of each data table.

[0082] In the above step S216, taking one of the clusters as an example, the method for calculating the new cluster center of the cluster includes: respectively calculating the average value of the characteristic elements of each data in the cluster, and using the data table constructed by the average value of each data as the new cluster center of the cluster. By analogy, the new cluster center of each cluster is obtained. The method for calculating the relationship between each data table and each new cluster center in this embodiment is the same as the method for calculating each data table and each initial cluster center in the above text.

[0083] In the above step S218, after all the data tables are assigned to the clusters corresponding to the new cluster centers, it is determined that one iteration update of the clusters is completed.

[0084] In the above step S220, the preset clustering termination condition may be that the number of iterative updates of each cluster reaches a set number threshold, or the cluster centers of each cluster obtained by two consecutive clusterings are the same.

[0085] The technical solution of this embodiment adopts the k-means algorithm to cluster each data table. Since the k-means clustering algorithm has the advantages of low time complexity, better clustering effect and strong interpretability, and the number of clustering clusters can be specified, the technical solution of this embodiment can determine the number of clustering clusters according to the number of table types of the data table, so that the number of clustering clusters obtained is consistent with the number of table types, thereby facilitating the determination of the table type of each data table and improving the reliability of the sub-tables.

[0086] In some embodiments of the present invention, the types of the set quantity tables in the above step S202 are respectively a broadcast table, a single table and a shard table, wherein:

[0087] If the table type is a broadcast table, the business characteristics of this table type include: multiple join queries with data tables with a larger data volume than itself in the business;

[0088] If the table type is a single table, the business characteristics of this table type include: only a single data table is involved in the business query;

[0089] If the table type is a sharded table, the business characteristics of the table type include: the repeated value of the field is less than the set repeated threshold, and the access frequency is greater than the set frequency threshold.

[0090] Through the technical solution of this embodiment, table types can be divided into broadcast tables, single tables and sharded tables according to business characteristics, so that in the process of testing the database, data table shards with the same business characteristics can be tested to improve the convenience and reliability of database testing.

[0091] In some embodiments of the present invention, after clustering the characteristic elements of each data table in the above step S104, the method further includes:

[0092] Evaluate the clustering effect of each data table to determine whether the clustering effect of each data table meets the set expected effect;

[0093] If not, cluster the characteristic elements of each data table again.

[0094] Through the technical solution of this embodiment, the clustering effect of each data table can be evaluated to ensure that the clustering effect of each data table can achieve the set expected effect, thereby ensuring the reliability of clustering of each data table.

[0095] In some embodiments of the present invention, the method of evaluating the clustering effect of each data table to determine whether the clustering effect of each data table meets the set expected effect includes:

[0096] First, calculate the silhouette coefficient of each data table; then, determine whether the silhouette coefficient of each table data is greater than the set silhouette threshold;

[0097] If yes, it is determined that the clustering effect of each data table meets the expected effect;

[0098] If not, it is determined that the clustering effect on each data table does not meet the expected effect.

[0099] In this embodiment, the silhouette coefficient is an internal evaluation index based on the distance between data points, and is used to measure the compactness of the data points in the cluster to which they belong and the separation between the data points and other clusters.

[0100] In this embodiment, the calculation formula of the silhouette coefficient of the data table is:

[0101] S(i)=(b(i)-a(i)) / max{a(i),b(i)}

[0102] Among them, S(i) represents the silhouette coefficient of the ith data table, a(i) represents the average distance between the ith data table and other data tables in the cluster to which it belongs, which can reflect the compactness (cohesion) of each cluster, and the smaller the value of a(i), the closer the ith data table is to other data tables in the cluster to which it belongs, and the more compact the cluster to which the ith data table belongs. b(i) represents the average distance between the ith data table and the data tables in the cluster in which the cluster center is closest to the ith data table in the cluster to which it does not belong, which can reflect the separation between the clusters, that is, the larger the value of b(i), the farther the data table is from the data tables in other clusters, and the higher the separation between the clusters.

[0103] Through the technical solution of this embodiment, after clustering the characteristic elements, the clustering effect can be evaluated according to the silhouette coefficient of each characteristic element. Since the silhouette coefficient can accurately measure the compactness of the data point in the cluster to which it belongs and the separation degree between it and other clusters, the accuracy of the clustering effect evaluation of each characteristic element can be improved.

[0104] In some embodiments of the present invention, in the above step S108, the method of determining the table splitting statement of each data table according to the table type of each data table includes:

[0105] If the table type of the data table is a sharded table, the sharding statement of the data table is a statement for passing in the table name, sharding key and sharding algorithm of the data table; if there is a primary key in the data table, the sharding key of the data table is the primary key of the data table; if there is no primary key in the data table, the sharding key of the data table is the data column with the least duplicate values ​​in the data table. If the sharding algorithm of the data table cannot be passed in, the sharding algorithm in the sharding statement of the data table is set to a default value.

[0106] If the table type of the data table is a broadcast table, the table splitting statement of the data table is a statement used to pass in the table name of the data table.

[0107] If the data table type is a single table, there is no need to generate a table splitting statement.

[0108] Through the technical solution of this embodiment, it is possible to confirm that the table type of each data table is a sharding table, a broadcast table, and a single table, and it is possible to generate table partitioning statements that match the business characteristics of the sharding table and the broadcast table, respectively, so that the database can accurately partition the database according to the table partitioning statements, thereby improving the convenience and accuracy of database table partitioning.

[0109] In some embodiments of the present invention, before clustering the data tables according to the characteristic elements in the above step S104, the method further includes:

[0110] Clean the characteristic elements of each data table to filter out abnormal data in each characteristic element;

[0111] And / or normalize the data in the characteristic elements of each data table.

[0112] In this embodiment, the abnormal data in the characteristic elements may include null values ​​and abnormal values ​​in the characteristic elements, that is, the characteristic elements of each data table are cleaned, including removing null values ​​in each characteristic element and removing abnormal values ​​in each characteristic element. Therefore, in order to clean the elements of the data table, it is necessary to identify the null values ​​and abnormal values ​​in each characteristic element.

[0113] The method for identifying null values ​​in feature elements may include: detecting whether each data in the feature element has a corresponding feature value; if there is data in a feature element without a corresponding feature value, it is determined that there is a null value in the feature element, and thus the feature element is deleted for cleaning processing.

[0114] The method for identifying abnormal values ​​in characteristic elements may include: first determining the preset value range of each type of data in each characteristic element, and then detecting whether the characteristic value of each data in each characteristic element is within the corresponding preset value range; if the characteristic value of the data in a certain characteristic element is not within the corresponding preset value range, then there is an abnormal value in the characteristic element, and therefore the characteristic element is deleted for cleaning processing.

[0115] For example, in the characteristic elements of a data table, the characteristic value corresponding to whether the data table is the right table participating in the join query should be 0 or 1. If the characteristic value of whether the data table is the right table participating in the join query in a certain characteristic element is neither 0 nor 1, it is determined that there is an abnormal value in the data of the characteristic element.

[0116] The technical solution of this embodiment can filter out abnormal data in each characteristic element by cleaning the characteristic elements of each data table, thereby ensuring the accuracy and reliability of each characteristic element, thereby ensuring the accuracy of clustering in the process of clustering each characteristic element.

[0117] In some embodiments of the present invention, before clustering the data tables according to the characteristic elements in the above step S104, the method further includes:

[0118] In this embodiment, the method for normalizing the data in the characteristic elements of each data table includes: setting the maximum value of the characteristic values ​​of the characteristic elements of each data table as X max , the minimum value is X min , if the eigenvalue of one of the feature elements is X, then after normalization, the eigenvalue X′ of the feature element is

[0119]

[0120] The technical solution of this embodiment can filter out abnormal data in each characteristic element by cleaning the characteristic elements of each data table, ensure the accuracy and reliability of each characteristic element, and thus ensure the accuracy of clustering in the process of clustering each characteristic element. After normalizing the data in each characteristic element, the dimensional difference of the data in each characteristic element can be eliminated, so that the convergence effect can be achieved quickly in the clustering process, and the accuracy and generalization ability of clustering each characteristic element can be improved.

[0121] In some embodiments of the present invention, the process of the database table partitioning method is as follows: Figure 4 As shown, the following steps are included:

[0122] Step S302: extracting features from each data table in the database to obtain feature elements of each data table;

[0123] Step S304: performing data cleaning and normalization processing on the characteristic elements of each data table;

[0124] Step S306: obtaining the business characteristics of each table type, and selecting a data table of each table type from the database according to the business characteristics of each table type, to serve as the initial cluster center of each cluster;

[0125] Step S308: Calculate the distance between each data table and each initial cluster center according to the characteristic elements of each data table;

[0126] Step S310: assigning each data table to a cluster corresponding to the initial cluster center that is closest to the data table;

[0127] Step S312: calculating the new cluster center of each cluster, and calculating the distance between each data table and each new cluster center according to the characteristic elements of each data table;

[0128] Step S314: assigning each data table to the cluster corresponding to the new cluster center that is closest to it;

[0129] Step S316: determining whether the clustering result of each data table satisfies a preset clustering termination condition;

[0130] If yes, execute step S318; if no, return to step S312;

[0131] Step S318: Calculate the silhouette coefficient of each data table, and determine whether the silhouette coefficient of each data table is greater than a set threshold coefficient;

[0132] If yes, execute step S320; if no, return to step S306;

[0133] Step S320: obtaining the table type of each data table according to the table type corresponding to the cluster where each characteristic element is located;

[0134] Step S322: determining the table partitioning statement of each data table according to the table type of each data table to generate a table partitioning strategy for the database, and performing table partitioning processing on the database according to the table partitioning strategy.

[0135] 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 ideas provided by the present embodiment method, additional changes can be made to the above method.

[0136] 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.

[0137] This embodiment also provides a computer program product 10 and a computer readable storage medium 20 . Figure 5 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 6 1 is 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 database table partitioning methods when the computer program 11 is executed by the processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11 thereon, and the computer program 11 implements the steps of any of the above-mentioned database table partitioning methods when the computer program 11 is executed by the processor 32.

[0138] 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.

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

[0140] 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 use the computer program 11 for 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.

[0141] 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 method for partitioning a database, characterized in that: include: Performing feature extraction on the data tables in the database to obtain feature elements of each of the data tables; Clustering each of the data tables according to each of the characteristic elements to obtain a plurality of clusters; Acquire the table type corresponding to each cluster, and determine the table type of each data table according to the cluster to which each data table belongs and the table type corresponding to each cluster; The table partitioning statements of each data table are determined according to the table type of each data table to generate a table partitioning strategy for the database, and the database is partitioned according to the table partitioning strategy.

2. The table division method according to claim 1, characterized in that: The step of clustering the data tables according to the characteristic elements to obtain a plurality of clusters includes: Obtaining business characteristics of each table type; Selecting a data table of each table type according to each business type to serve as an initial cluster center of each cluster; Each data table is clustered according to each of the initial cluster centers and each of the characteristic elements, so as to allocate each of the data tables to a corresponding cluster.

3. The table division method according to claim 2, characterized in that: The step of obtaining the service characteristics of each table type includes: If the table type is a broadcast table, the service characteristics include: multiple connection queries with a data table with a larger data volume than itself in the service; If the table type is a single table, the business characteristics include: only a single data table is involved in the business query; If the table type is a sharded table, the business characteristics include: the repetition value of the field is less than a set repetition threshold, and the access frequency is greater than a set frequency threshold.

4. The table division method according to claim 2, characterized in that: The step of clustering each data table according to each of the initial cluster centers and each of the characteristic elements comprises: Calculating the distance between each of the data tables and each of the initial cluster centers according to each of the characteristic elements, and assigning each of the data tables to the cluster corresponding to the initial cluster center with the closest distance; Obtaining a new cluster center of each of the clusters, and calculating the distance between each of the data tables and each of the new cluster centers according to each of the characteristic elements, and assigning each of the data tables to the cluster corresponding to the new cluster center with the closest distance; Determining whether the clustering result of each of the data tables meets a preset clustering termination condition; If not, return to the step of obtaining a new cluster center for each of the clusters.

5. The table division method according to claim 1, characterized in that: After the step of clustering the data tables according to the characteristic elements, the method further includes: Determine whether the clustering effect of each of the data tables meets the expected effect; If not, return to the step of clustering each of the data tables according to each of the characteristic elements.

6. The table division method according to claim 5, characterized in that: The step of judging whether the clustering effect of each data table meets the preset effect includes: Calculating the silhouette coefficient of each of the data tables, and determining whether each of the silhouette coefficients is greater than a set coefficient threshold; If yes, it is determined that the clustering effect meets the expected effect; If not, it is determined that the clustering effect does not meet the expected effect.

7. The table division method according to claim 1, characterized in that: The step of determining the table splitting statements of each data table according to the table type of each data table comprises: If the table type of the data table is a sharded table, the sharding statement is a statement for inputting the table name, sharding key and sharding algorithm of the data table; If the table type is a broadcast table, the table partition statement is a statement used to input the table name of the data table.

8. The table division method according to claim 1, characterized in that: Before the step of clustering the data tables according to the characteristic elements, the method further includes: Data cleaning is performed on each of the characteristic elements, and / or data in each of the characteristic elements is normalized.

9. A computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the steps of the table partitioning method according to 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 table partitioning method according to any one of claims 1 to 8 are implemented.

Images