Data balanced distribution method, system and equipment

Abstract

The invention discloses a data balanced distribution method, system and equipment, and relates to the technical field of shared-free storage, and the method comprises the following steps: identifying a balanced distribution column and a data offset table by traversing each data table in a target database; according to the balanced distribution column, training a pre-constructed distribution column selection model, so that the distribution column selection model learns a first data feature of the balanced distribution column; extracting a second data feature of each column in the data offset table, and inputting the second data features into the trained distribution column selection model to obtain an optimal distribution column of the data offset table; and performing table structure reconstruction on the data offset table according to the optimal distribution column. By adopting the embodiment of the invention, the optimal distribution column can be automatically identified and is applied to the data offset table, so that the possibility of human errors is reduced, and meanwhile, all tables with data offset are ensured to be accurately optimized.

Description

Technical Field

[0001] This invention relates to the field of shared-nothing storage technology, and in particular to a method, system and device for balanced data distribution. Background Technology

[0002] In a shared-nothing storage architecture, distributed table groups are pre-defined according to requirements, and then data is divided and entered into each node based on the distribution columns in the distributed table groups using a hash function.

[0003] Current distributed table group designs heavily rely on manual experience to select distribution columns, which can impact system performance due to subjective judgment errors. Furthermore, in scenarios with large-scale or complex data structures, it is necessary to manually identify data offsets and redesign distribution columns to address data imbalance, a process that is inefficient. Summary of the Invention

[0004] The present invention aims to provide a data balanced distribution method, system, and device. By intelligently scanning and analyzing the target database, it can automatically identify the optimal distribution column and apply it to the data offset table, reducing the possibility of human error. At the same time, it ensures that all tables with data offsets are accurately optimized, effectively preventing the occurrence of performance bottlenecks and improving processing efficiency and the overall responsiveness of the system.

[0005] In a first aspect, embodiments of the present invention provide a data balanced distribution method, comprising: Traverse each data table in the target database to identify balanced distribution columns and data offset tables; Based on the balanced distribution column, the pre-constructed distribution column selection model is trained, so that the distribution column selection model learns the first data feature of the balanced distribution column; Extract the second data features from each column of the data offset table, and input the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table; Based on the optimal distribution column, the data offset table is restructured.

[0006] As an improvement to the above scheme, the step of traversing each data table in the target database and identifying the balanced distribution column and the data offset table includes: Traverse each data table in the target database and identify balanced distribution tables and data offset tables based on the distribution balance of the data tables on each node; Based on the execution efficiency of the balanced distribution table and the corresponding distribution columns, the balanced distribution columns are selected.

[0007] As an improvement to the above scheme, the step of training a pre-constructed distribution selection model based on the balanced distribution column, so that the distribution selection model learns the first data feature of the balanced distribution column, includes: Extract the first data feature of the balanced distribution column; Based on the first data features, construct training samples; The pre-constructed distribution selection model is iteratively trained using the training samples until the performance of the distribution selection model exceeds a preset performance threshold.

[0008] As an improvement to the above scheme, the extraction of the first data feature of the balanced distribution column includes: Calculate the data dispersion based on the dissimilar values ​​in the balanced distribution column; Randomly sample from the balanced distribution, calculate the standard deviation of the sampled data, and obtain the sampling variance; Calculate the frequency of the associated columns in the evenly distributed columns based on the association conditions of historical association queries; The difference between the maximum and minimum values ​​of the data in the balanced distribution column is calculated to obtain the data range; Obtain the query response time of the current execution plan on each node, and get the maximum response time; The first data feature of the balanced distribution column is obtained based on the data dispersion, the sampling variance, the frequency of the associated column, the data range, and the maximum response time.

[0009] As an improvement to the above scheme, if the pre-built distribution selection model is a random forest model, then the step of iteratively training the pre-built distribution selection model using the training samples until the performance of the distribution selection model exceeds a preset performance threshold includes: The training samples are divided into a training set and a test set; The feature subspace method is used to sample the training set with replacement to construct several independent training subsets; The decision trees in the random forest model are trained one-to-one using the training subset to obtain the trained distribution selection model. The performance of the trained distribution selection model is evaluated using the test set. If the performance is not greater than a preset performance threshold, the pre-constructed distribution selection model is retrained iteratively; otherwise, the trained distribution selection model is considered to have converged.

[0010] As an improvement to the above scheme, the step of training each decision tree in the random forest model using the training subset to obtain the trained distribution selection model includes: The training subset is mapped one-to-one with the initial decision tree in the random forest model; Random features are selected from the training subset, and the optimal segmentation point is determined using the Gini index. Based on the random features and the optimal split point, the corresponding branch nodes of the initial decision tree are set to obtain the decision tree; Based on all the decision trees, the trained distribution selection model is obtained.

[0011] As an improvement to the above scheme, the step of extracting the second data features of each column in the data offset table and inputting the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table includes: Extract the data dispersion, sampling variance, frequency of associated columns, data range, and maximum response time of each column in the data offset table to obtain the second data feature of each column; The second data feature of each column is input into the trained distribution column selection model to obtain the fit of each column; Based on the fitness level, the optimal distribution column is selected from the data offset table.

[0012] As an improvement to the above scheme, the step of reconstructing the table structure of the data offset table based on the optimal distribution column includes: Export the data from the data offset table to obtain the data to be transferred; Based on the optimal distribution, the table structure is reconstructed to obtain a new table; Import the data to be transferred into the new table.

[0013] Secondly, embodiments of the present invention provide a data balanced distribution system, including: The data table traversal module is used to traverse each data table in the target database and identify balanced distribution columns and data offset tables. The model training module is used to train a pre-constructed distribution column selection model based on the balanced distribution column, so that the distribution column selection model learns the first data feature of the balanced distribution column; The optimal distribution column selection module is used to extract the second data features of each column in the data offset table, and input the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table; The table reconstruction module is used to reconstruct the table structure of the data offset table based on the optimal distribution column.

[0014] Thirdly, embodiments of the present invention provide a data equalization distribution device, including a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor, wherein the processor executes the computer program to implement the data equalization distribution method as described above.

[0015] Compared with existing technologies, the present invention discloses a data balanced distribution method, system, and device. This method involves traversing all data tables in a target database to identify balanced distribution columns and data offset tables. Based on the balanced distribution columns, a pre-constructed distribution column selection model is trained, enabling the model to learn the first data features of the balanced distribution columns. Second data features are extracted from each column of the data offset table and input into the trained distribution column selection model to obtain the optimal distribution column of the data offset table. Finally, the table structure of the data offset table is reconstructed based on the optimal distribution column. Using this invention, the optimal distribution column can be automatically identified and applied to the data offset table, reducing the possibility of human error and ensuring that all tables with data offsets are accurately optimized. Attached Figure Description

[0016] Figure 1 This is a flowchart illustrating the steps of a data balanced distribution method provided in an embodiment of the present invention; Figure 2 This is a schematic diagram illustrating the structure and training process of a random forest model provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the structure of a data balanced distribution system provided in an embodiment of the present invention; Figure 4 This is a schematic diagram of the structure of a data equalization distribution device provided in an embodiment of the present invention. Detailed Implementation

[0017] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0018] In the description and claims, it should be understood that the terms "first," "second," etc., used in the description and claims are only for the purpose of distinguishing the description of the same technical features, and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated, nor necessarily the order of description or chronological order. The terms are interchangeable where appropriate. Therefore, a feature defined as "first" or "second" may explicitly or implicitly include at least one of that feature.

[0019] Currently, distributed relational database clusters, such as OceanBase, have abandoned the traditional shared storage architecture of Oracle RAC (Real Application Clusters) and instead adopted the Shared-Nothing design principle. This allows them to construct a logically single database composed of numerous independent database service nodes. The shared-nothing architecture ensures that the resources of each data node or coordinating node are completely independent. Each node can exclusively enjoy its own hardware resources and only needs to maintain a portion of the dataset.

[0020] In OceanBase, a distributed table group is used to assign a distribution column to each data table, and then a hash function is used to evenly distribute the data across the nodes. However, it's important to note that once the distribution column is set, it's fixed; if requirements change, the table must be recreated.

[0021] In practical applications, due to dynamic changes in business operations or improper column selection by business personnel, some nodes may bear excessive data processing loads. According to the "weakest link" principle, the overall system performance is limited by the busiest node. This not only slows down query response time but also affects data import and export efficiency, ultimately restricting the overall performance of the database.

[0022] Currently, the industry mainly determines whether there is a data offset by querying the amount of data in each data node; then manually exporting the data from the data offset table; then selecting a new distribution column based on experience, redesigning the table structure, and importing the exported data into the new table to ensure a balanced distribution of data under the distributed storage architecture.

[0023] However, manually identifying and adjusting data skew is a labor-intensive task. In scenarios with large-scale or complex data, it is extremely inefficient and difficult to respond to rapidly changing data environments in a timely manner.

[0024] Furthermore, the selection of distribution columns in existing technologies relies heavily on human experience. This is not only limited by the operator's understanding of the database structure and data characteristics, but may also lead to the selection of suboptimal distribution columns due to subjective judgment errors, thus affecting system performance.

[0025] Based on the above considerations, embodiments of the present invention provide a method for balanced data distribution. Please refer to... Figure 1 In this embodiment, the data balanced distribution method is specifically executed through steps S1 to S4: S1. Traverse each data table in the target database to identify the balanced distribution column and the data offset table; S2. Based on the balanced distribution column, train the pre-constructed distribution column selection model so that the distribution column selection model learns the first data feature of the balanced distribution column; S3. Extract the second data features of each column in the data offset table, and input the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table; S4. Reconstruct the table structure of the data offset table based on the optimal distribution column.

[0026] Understandably, the target database contains multiple tables, each organizing and storing data using a two-dimensional row and column structure. A row represents an independent record or instance, and a column represents a data attribute. Within a table, data in the same column share the same attribute. In a distributed storage architecture with no shared storage, data is distributed across different nodes based on attributes; the column corresponding to that attribute is called the distributed column.

[0027] When the distribution column is selected, the data in the data table will be evenly distributed and stored across the nodes. By identifying the distribution of each data table across the nodes, we can identify data tables with data skew and those with balanced data distribution.

[0028] It should be noted that for a data table, its distribution column can be a single column or a combination of multiple columns; furthermore, the target database may contain multiple data tables, each with different column attributes, so the settings of the distribution column are very likely to differ. That is, for the target database, the balanced distribution column varies from data table to data table, and the number of balanced distribution columns fluctuates dynamically depending on the actual situation.

[0029] In some preferred embodiments, in response to scheduled scanning tasks, new data tables are automatically discovered and marked for analysis without frequent full database scans, greatly shortening the time from discovering offsets to taking action, thus meeting the needs of efficient operation and maintenance.

[0030] For example, a Data Administrator (DBA) defines a data distribution analysis and rebalancing task based on requirements. If the task already exists in the task allocation library, the DBA is notified of the current task status. If the task does not exist in the task allocation library, a new task is created and recorded in the task allocation library. When the task starts, it automatically connects to the target database and executes the data balancing distribution method described in this embodiment of the invention.

[0031] Preferably, the task types in the task allocation library include immediate execution tasks and recurring execution tasks. Immediate execution tasks are suitable for scenarios involving large-scale data import into new tables, providing suggestions for column selection; recurring execution tasks are used to periodically analyze existing data tables, identify and resolve data offset issues.

[0032] Data columns in different data tables may be related. In this embodiment of the invention, by comprehensively analyzing the data characteristics of all balanced distribution columns in the target database, the pre-built distribution column selection model can not only learn the setting logic of the balanced distribution columns to ensure that the data is evenly distributed in each partition, but also effectively reduce the need for cross-partition queries to improve query efficiency.

[0033] The process of redistributing a data table with data offsets actually involves reselecting the distribution columns and updating the table structure of the data offset table so that the data in the original data offset table can be evenly distributed among the nodes based on the new distribution column settings.

[0034] It should also be noted that the feature dimensions of the first data feature and the second data feature are the same.

[0035] In this embodiment of the invention, in order to achieve a balanced distribution of data in the overall target database and improve query efficiency, the optimal distribution column is reselected for the data offset table based on the second data features of each column in the data offset table and the learned pattern of the balanced distribution column.

[0036] In the above solution, by intelligently scanning and analyzing the target database, the optimal distribution column can be automatically identified and applied to the data offset table, reducing the possibility of human error. At the same time, it ensures that all tables with data offsets are accurately optimized, effectively preventing the occurrence of performance bottlenecks and improving processing efficiency and the overall responsiveness of the system.

[0037] As a preferred implementation, step S1, traversing each data table in the target database and identifying the balanced distribution column and data offset table, includes: Traverse each data table in the target database and identify balanced distribution tables and data offset tables based on the distribution balance of the data tables on each node; Based on the execution efficiency of the balanced distribution table and the corresponding distribution columns, the balanced distribution columns are selected.

[0038] In some preferred embodiments, the distribution balance is further calculated by calculating the amount of data in each node of the data table.

[0039] For example, a lower distribution balance indicates that the data is more evenly distributed across nodes, while a higher balance indicates that the data distribution across nodes is offset.

[0040] In this case, if the distribution balance is greater than the preset first balance threshold, the data table is identified as a data offset table; if the distribution balance is less than the preset second balance threshold, the data table is identified as a balanced distribution table; wherein the first balance threshold is not less than the second balance threshold.

[0041] It should be noted that when the distribution balance is not greater than a preset first balance threshold and not less than a preset second balance threshold, the data table is considered a regular data table, and no additional analysis or adjustment processing is performed on the distribution columns of the data table. The specific values ​​of the first and second balance thresholds can be adjusted according to the actual scenario, and their values ​​do not affect the beneficial effects produced by the embodiments of the present invention.

[0042] In some preferred embodiments, the execution efficiency of the balanced distribution table is calculated jointly by query performance and resource utilization. When the execution efficiency of the balanced distribution table is greater than a preset efficiency threshold, the distribution column corresponding to the balanced distribution table is directly used as the balanced distribution column.

[0043] In the above scheme, the data tables are first classified based on the distribution balance of the data to identify balanced distribution tables and data offset tables, so as to initially screen the data tables to be learned and the data tables to be adjusted; then, balanced distribution columns are further screened from the balanced distribution table based on execution efficiency to ensure that the distribution column selection model can learn the data characteristics of the distribution columns with the best performance in the target database.

[0044] In a preferred implementation, step S2 involves training a pre-constructed distribution selection model based on the balanced distribution, enabling the distribution selection model to learn the first data features of the balanced distribution, and executing steps S21-S23. S21. Extract the first data feature of the balanced distribution column; S22. Construct training samples based on the first data features; S23. The pre-constructed distribution selection model is iteratively trained using the training samples until the performance of the distribution selection model is greater than a preset performance threshold.

[0045] It should be noted that, in the embodiments of the present invention, the distribution selection model can be a neural network model, a support vector machine model, a random forest model, etc. The architecture of the distribution selection model does not affect the beneficial effects produced by the embodiments of the present invention. The model goal is to obtain the optimal distribution based on the input data features.

[0046] It should also be noted that the pre-construction of the distribution selection model is the construction of the basic architecture, and its specific parameters are adjusted during training. For example, for the random forest model, the pre-construction process is to build the architecture of the decision trees, and the specific branch point values ​​within each decision tree and the weight relationships between decision trees are obtained through training.

[0047] In the above scheme, the training samples constructed by the first data features of the balanced distribution ensure that the distribution selection model can learn the data patterns of the better distribution in the target database and extend them to new data.

[0048] Further, preferably, step S21, extracting the first data feature of the balanced distribution column, includes: Calculate the data dispersion based on the dissimilar values ​​in the balanced distribution column; Randomly sample from the balanced distribution, calculate the standard deviation of the sampled data, and obtain the sampling variance; Calculate the frequency of the associated columns in the evenly distributed columns based on the association conditions of historical association queries; The difference between the maximum and minimum values ​​of the data in the balanced distribution column is calculated to obtain the data range; Obtain the query response time of the current execution plan on each node, and get the maximum response time; The first data feature of the balanced distribution column is obtained based on the data dispersion, the sampling variance, the frequency of the associated column, the data range, and the maximum response time.

[0049] In some preferred embodiments, data dispersion Represented as: ; in, For data columns; This indicates the number of unique values ​​in column C.

[0050] Preferably, after random sampling from a balanced distribution, the sampled data is preprocessed. Exemplarily, the preprocessing operations include: removing newline and carriage return characters, converting Chinese characters to decimal representation, and cleaning up all special symbols.

[0051] It should be noted that the sampling variance is the standard deviation of the sample mean, reflecting the average error between the sampled index and the population index. In some preferred embodiments, the sampling variance is expressed as: ; in, The number of samples; Let i be the value of the i-th sample. This is the sample mean.

[0052] In a distributed OceanBase database environment, the performance of join queries depends on data partition alignment. When two join tables are partitioned according to the same distribution columns, related rows are stored in the same partition as much as possible, thus avoiding data movement across partitions. This is the foundation for optimizing query performance. If the data involved in a join query is not located in the same partition, data must be transferred between partitions over the network, which undoubtedly increases latency, consumes network resources, and becomes a performance bottleneck.

[0053] Therefore, choosing the right distribution columns is crucial. Ideally, distribution columns should be those frequently used in join operations or filtering conditions, or a set of columns that effectively balances data distribution. By setting these columns as distribution columns, you can ensure that data is evenly distributed across partitions, reducing the need for cross-partition queries and thus significantly improving query efficiency.

[0054] The frequency of related columns refers to the frequency with which data columns are frequently used in historical related queries. In some preferred embodiments, the frequency of related columns is expressed as: ; in, The number of queries for the join conditions; This represents the total number of queries.

[0055] The data range, which is the difference between the maximum and minimum values ​​in a balanced distribution, can intuitively show the range of the data.

[0056] In some preferred embodiments, the feature dimension of the first data feature further includes data type. For example, the data type can be obtained through metadata, and data types include integers, strings, dates, floating-point numbers, and boolean values.

[0057] It's important to note that in distributed databases, a query is typically broken down into multiple subtasks. These subtasks constitute the current execution plan, which reflects how the query is executed within the current distributed table group. By obtaining the response time of the current execution plan on each node and then determining the maximum response time, we can reflect the actual user experience's response time.

[0058] In one embodiment, the first data feature of the balanced distribution column of the target database is shown in Table 1. In this embodiment, the balanced distribution column has 6 items, which are distinguished by sample ID.

[0059] Table 1

[0060] In the above scheme, data features are extracted from multiple dimensions such as data dispersion, sampling variance, correlation column frequency, data range, and maximum response time, which enables the distribution column selection model to learn the characteristics of the balanced distribution column as comprehensively as possible.

[0061] Preferably, the pre-constructed distribution selection model is a random forest model.

[0062] Random forest models are an ensemble learning approach that incorporates multiple decision trees as classifiers. By constructing multiple decision trees and combining their predictions, the accuracy and robustness of the model can be improved.

[0063] By employing the random forest model, a large number of features can be processed and effectively applied to classification tasks. In this embodiment of the invention, the ultimate goal of the random forest model is to classify the data offset table based on the input data features in order to obtain the optimal distribution.

[0064] Based on this, as a preferred implementation, step S23, iteratively training the pre-constructed distribution selection model using the training samples until the performance of the distribution selection model exceeds a preset performance threshold, includes: The training samples are divided into a training set and a test set; The feature subspace method is used to sample the training set with replacement to construct several independent training subsets; The decision trees in the random forest model are trained one-to-one using the training subset to obtain the trained distribution selection model. The performance of the trained distribution selection model is evaluated using the test set. If the performance is not greater than a preset performance threshold, the pre-constructed distribution selection model is retrained iteratively; otherwise, the trained distribution selection model is considered to have converged.

[0065] In this embodiment of the invention, by performing multiple sampling processes with replacement, several independent training subsets can be created from the training set, and each sampling ensures the randomness and representativeness of the data, laying the foundation for building diverse decision trees.

[0066] Please see Figure 2 , Figure 2This paper presents a schematic diagram of the structure and training process of a random forest model. First, n training subsets are obtained from the training set. The first set of data features in each training subset constitutes a feature subset. The decision trees are trained using these feature subsets, with the number of feature subsets matching the number of decision trees. Next, the training results of each decision tree are integrated, and the performance of the distribution selection model is tested using a test set, resulting in a final distribution selection model that can output effective prediction results.

[0067] In some preferred embodiments, the decision tree grows continuously after multiple branch identifications. Irrelevant data is then filtered out through pruning, ultimately forming N random forest decision trees with equal weights to output feature classification results.

[0068] After model creation and training, the model needs to be evaluated and optimized using a test set. In this embodiment of the invention, a distributed selection model is used to evaluate its performance.

[0069] In some preferred embodiments, the performance of the distribution column selection model is obtained by calculating classification accuracy, precision, and information recall.

[0070] For example, classification accuracy measures the proportion of samples correctly classified by the model out of the total samples, reflecting the overall correct predictive ability of the model, and is expressed as: ; in, For true cases, it represents the number of samples that are actually positive and correctly predicted as positive. A true negative example represents the number of samples that are actually negative and were correctly predicted. False positives represent the number of samples that are actually negative but were incorrectly predicted as positive. False negatives represent the number of samples that are actually positive but are predicted as negative.

[0071] Precision measures the proportion of samples predicted as positive that are actually positive. It is more critical for scenarios where the accuracy of positive predictions is of interest, and is expressed as: .

[0072] Information recall measures the proportion of samples that are actually positive that are correctly identified by the model. It reflects the ability of the distribution selection model to capture actual positive examples and is expressed as: .

[0073] In a preferred embodiment, the distribution selection model is evaluated using both the training set and the test set, and the evaluation results are shown in Table 2.

[0074] Table 2

[0075] In this embodiment, the distributed selection model demonstrated excellent learning ability on the training set, perfectly fitting the training data. Furthermore, its performance on unknown data (test set) maintained a high recall and precision rate exceeding 0.9. This not only validated the model's generalization ability but also proved its high accuracy and practicality in real-world applications. It means the model not only learned the patterns in the training data but can also effectively generalize to new data, exhibiting strong predictive ability and stability, making it suitable for practical tasks requiring high classification accuracy.

[0076] In the above scheme, the construction of training subsets through the feature subspace method and the training of their respective decision trees can ensure data diversity, thereby significantly improving the resolution of the feature space and thus improving the performance of the model; furthermore, the model can be tested and evaluated through the test set to ensure its usability.

[0077] Furthermore, preferably, the step of training each decision tree in the random forest model using the training subset to obtain the trained distribution selection model includes: The training subset is mapped one-to-one with the initial decision tree in the random forest model; Random features are selected from the training subset, and the optimal segmentation point is determined using the Gini index. Based on the random features and the optimal split point, the corresponding branch nodes of the initial decision tree are set to obtain the decision tree; Based on all the decision trees, the trained distribution selection model is obtained.

[0078] During the construction of each tree in the random forest, at each branch node, a feature i is randomly selected from all available features. The Gini index is then used to evaluate and determine the optimal split point for this random feature, thereby constructing the decision path.

[0079] The Gini index is used to quantify the degree to which different categories are mixed in a dataset. In some preferred embodiments, the Gini index is expressed as: ; Where N is the sample set; C is the total number of categories; This represents the proportion of samples in the i-th class.

[0080] A smaller Gini index indicates lower impurity in the dataset. A Gini index of 0 means all samples in the dataset belong to the same class; a Gini index of 0.5 means the samples in the dataset are evenly distributed between the two classes.

[0081] In the above scheme, by randomly selecting data features and determining the branch nodes of each decision tree based on the Gini index, the diversity of the model can be increased, and the stability and accuracy of the model can be improved.

[0082] As a preferred implementation, step S3, extracting the second data features of each column in the data offset table and inputting the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table, includes: Extract the data dispersion, sampling variance, frequency of associated columns, data range, and maximum response time of each column in the data offset table to obtain the second data feature of each column; The second data feature of each column is input into the trained distribution column selection model to obtain the fit of each column; Based on the fitness level, the optimal distribution column is selected from the data offset table.

[0083] It should be noted that the feature dimensions of the second data feature are consistent with those of the first data feature. When the first data feature includes a data type, the second data feature also includes a data type. The specific selection of feature dimensions does not affect the beneficial effects produced by the embodiments of the present invention.

[0084] By inputting the second data features of each column into the trained distribution column selection model, the distribution column selection model can match and classify the data features of each column in the data offset table with the learned balanced distribution column. In this embodiment of the invention, the classification result is reflected by the fitness.

[0085] In some preferred embodiments, the fitness is the degree of matching between each column and the classification results under each decision tree in the distribution column selection model; in other preferred embodiments, the fitness is the classification prediction result obtained after passing through the ensemble classifier of the distribution column selection model.

[0086] The above scheme can apply the trained distribution selection model with good classification performance to the actual data offset table, so as to obtain the optimal distribution in a timely manner through model calculation, which is more accurate than manual selection of distribution.

[0087] As a preferred implementation, step S4, reconstructing the table structure of the data offset table based on the optimal distribution column, includes: Export the data from the data offset table to obtain the data to be transferred; Based on the optimal distribution, the table structure is reconstructed to obtain a new table; Import the data to be transferred into the new table.

[0088] In some preferred embodiments, after the data to be transferred is imported into the new table, implementation logs are recorded, and the task execution status, impact table, and distribution column details in the task allocation database are updated to facilitate subsequent tracking and auditing, ensure the rationality of data distribution, and improve database performance.

[0089] The above scheme can automatically adjust the data offset table based on the optimal distribution column selected by the model, and perform data redistribution to ensure efficient data management and resource utilization.

[0090] The data balanced distribution method provided in this embodiment of the invention can automatically identify the optimal distribution column by intelligently scanning and analyzing the target database and applying it to the data offset table, reducing the possibility of human error. At the same time, it ensures that all tables with data offsets are accurately optimized, effectively preventing the occurrence of performance bottlenecks and improving processing efficiency and the overall responsiveness of the system.

[0091] This invention provides a data balanced distribution system. Please refer to [link / reference]. Figure 3 The data balanced distribution system includes a data table traversal module 11, a model training module 12, an optimal distribution column selection module 13, and a table reconstruction module 14, wherein: The data table traversal module 11 is used to traverse each data table in the target database and identify the balanced distribution column and the data offset table. The model training module 12 is used to train the pre-constructed distribution column selection model based on the balanced distribution column, so that the distribution column selection model learns the first data feature of the balanced distribution column. The optimal distribution column selection module 13 is used to extract the second data features of each column in the data offset table, and input the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table. Table reconstruction module 14 is used to reconstruct the table structure of the data offset table based on the optimal distribution column.

[0092] In a preferred embodiment, the data table traversal module 11 is specifically used for: Traverse each data table in the target database and identify balanced distribution tables and data offset tables based on the distribution balance of the data tables on each node; Based on the execution efficiency of the balanced distribution table and the corresponding distribution columns, the balanced distribution columns are selected.

[0093] In a preferred embodiment, the model training module 12 includes: The first feature extraction unit is used to extract the first data features of the balanced distribution column; A training sample construction unit is used to construct training samples based on the first data features; The model iterative training unit is used to iteratively train the pre-constructed distribution selection model using the training samples until the performance of the distribution selection model is greater than a preset performance threshold.

[0094] Further, preferably, the first feature extraction unit is specifically used for: Calculate the data dispersion based on the dissimilar values ​​in the balanced distribution column; Randomly sample from the balanced distribution, calculate the standard deviation of the sampled data, and obtain the sampling variance; Calculate the frequency of the associated columns in the evenly distributed columns based on the association conditions of historical association queries; The difference between the maximum and minimum values ​​of the data in the balanced distribution column is calculated to obtain the data range; Obtain the query response time of the current execution plan on each node, and get the maximum response time; The first data feature of the balanced distribution column is obtained based on the data dispersion, the sampling variance, the frequency of the associated column, the data range, and the maximum response time.

[0095] Preferably, the pre-constructed distribution selection model is a random forest model, then the model iterative training unit is specifically used for: The training samples are divided into a training set and a test set; The feature subspace method is used to sample the training set with replacement to construct several independent training subsets; The decision trees in the random forest model are trained one-to-one using the training subset to obtain the trained distribution selection model. The performance of the trained distribution selection model is evaluated using the test set. If the performance is not greater than a preset performance threshold, the pre-constructed distribution selection model is retrained iteratively; otherwise, the trained distribution selection model is considered to have converged.

[0096] Furthermore, preferably, the step of training each decision tree in the random forest model using the training subset to obtain the trained distribution selection model includes: The training subset is mapped one-to-one with the initial decision tree in the random forest model; Random features are selected from the training subset, and the optimal segmentation point is determined using the Gini index. Based on the random features and the optimal split point, the corresponding branch nodes of the initial decision tree are set to obtain the decision tree; Based on all the decision trees, the trained distribution selection model is obtained.

[0097] In a preferred embodiment, the optimal distribution column selection module 13 is specifically used for: Extract the data dispersion, sampling variance, frequency of associated columns, data range, and maximum response time of each column in the data offset table to obtain the second data feature of each column; The second data feature of each column is input into the trained distribution column selection model to obtain the fit of each column; Based on the fitness level, the optimal distribution column is selected from the data offset table.

[0098] In a preferred embodiment, the table reconstruction module 14 is specifically used for: Export the data from the data offset table to obtain the data to be transferred; Based on the optimal distribution, the table structure is reconstructed to obtain a new table; Import the data to be transferred into the new table.

[0099] The data balanced distribution system provided by this invention can automatically identify the optimal distribution column by intelligently scanning and analyzing the target database and applying it to the data offset table. This reduces the possibility of human error and ensures that all tables with data offsets are accurately optimized, effectively preventing the occurrence of performance bottlenecks and improving processing efficiency and the overall responsiveness of the system.

[0100] Please see Figure 4 , Figure 4 This is a structural block diagram of a data equalization distribution device provided in an embodiment of the present invention. The data equalization distribution device includes a processor 31, a memory 32, and a computer program stored in the memory 32 and executable on the processor 31. When the processor 31 executes the computer program, it implements the steps in the above-described embodiments of the data equalization distribution method, such as steps S1 to S4.

[0101] For example, the computer program can be divided into one or more modules / units, which are stored in the memory 32 and executed by the processor 31 to complete the present invention. The one or more modules / units can be a series of computer program instruction segments capable of performing specific functions, which describe the execution process of the computer program in the data equalization distribution device.

[0102] The data equalization distribution device may include, but is not limited to, a processor 31 and a memory 32. Those skilled in the art will understand that the schematic diagram is merely an example of a data equalization distribution device and does not constitute a limitation on the data equalization distribution device. It may include more or fewer components than illustrated, or combine certain components, or different components. For example, the data equalization distribution device may also include input / output devices, network access devices, buses, etc.

[0103] The processor 31 can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor. The processor 31 is the control center of the data equalization distribution device, connecting various parts of the entire data equalization distribution device through various interfaces and lines.

[0104] The memory 32 can be used to store the computer programs and / or modules. The processor 31 implements various functions of the data equalization distribution device by running or executing the computer programs and / or modules stored in the memory 32 and calling the data stored in the memory 32. The memory 32 may mainly include a program storage area and a data storage area. The program storage area may store the operating system, at least one application program required for a function (such as sound playback function, image playback function, etc.), etc.; the data storage area may store data created according to the use of the mobile phone (such as audio data, phonebook, etc.). In addition, the memory 32 may include high-speed random access memory, and may also include non-volatile memory, such as hard disk, memory, plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, at least one disk storage device, flash memory device, or other volatile solid-state storage device.

[0105] If the modules / units integrated in the data equalization distribution device are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by the processor 31, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording media, USB flash drives, portable hard drives, magnetic disks, optical disks, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc.

[0106] The above description represents the preferred embodiments of the present invention. It should be noted that those skilled in the art can make various improvements and modifications without departing from the principles of the present invention, and these improvements and modifications are also considered to be within the scope of protection of the present invention.

Claims

1. A method for balanced data distribution, characterized in that, include: Traverse each data table in the target database to identify balanced distribution columns and data offset tables; Based on the balanced distribution column, the pre-constructed distribution column selection model is trained, so that the distribution column selection model learns the first data feature of the balanced distribution column; Extract the second data features from each column of the data offset table, and input the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table; Based on the optimal distribution column, the data offset table is restructured.

2. The data balanced distribution method as described in claim 1, characterized in that, The process of traversing each data table in the target database and identifying balanced distribution columns and data offset tables includes: Traverse each data table in the target database and identify balanced distribution tables and data offset tables based on the distribution balance of the data tables on each node; Based on the execution efficiency of the balanced distribution table and the corresponding distribution columns, the balanced distribution columns are selected.

3. The data balanced distribution method as described in claim 1, characterized in that, The step of training a pre-constructed distribution selection model based on the balanced distribution column, so that the distribution selection model learns the first data features of the balanced distribution column, includes: Extract the first data feature of the balanced distribution column; Based on the first data features, construct training samples; The pre-constructed distribution selection model is iteratively trained using the training samples until the performance of the distribution selection model exceeds a preset performance threshold.

4. The data balanced distribution method as described in claim 3, characterized in that, The extraction of the first data feature of the balanced distribution column includes: Calculate the data dispersion based on the dissimilar values ​​in the balanced distribution column; Randomly sample from the balanced distribution, calculate the standard deviation of the sampled data, and obtain the sampling variance; Calculate the frequency of the associated columns in the evenly distributed columns based on the association conditions of historical association queries; The difference between the maximum and minimum values ​​of the data in the balanced distribution column is calculated to obtain the data range; Obtain the query response time of the current execution plan on each node, and get the maximum response time; The first data feature of the balanced distribution column is obtained based on the data dispersion, the sampling variance, the frequency of the associated column, the data range, and the maximum response time.

5. The data balanced distribution method as described in claim 3, characterized in that, If the pre-built distribution selection model is a random forest model, then the step of iteratively training the pre-built distribution selection model using the training samples until the performance of the distribution selection model exceeds a preset performance threshold includes: The training samples are divided into a training set and a test set; The feature subspace method is used to sample the training set with replacement to construct several independent training subsets; The decision trees in the random forest model are trained one-to-one using the training subset to obtain the trained distribution selection model. The performance of the trained distribution selection model is evaluated using the test set. If the performance is not greater than a preset performance threshold, the pre-constructed distribution selection model is retrained iteratively; otherwise, the trained distribution selection model is considered to have converged.

6. The data balanced distribution method as described in claim 5, characterized in that, The step of training each decision tree in the random forest model using the training subset to obtain the trained distribution selection model includes: The training subset is mapped one-to-one with the initial decision tree in the random forest model; Random features are selected from the training subset, and the optimal segmentation point is determined using the Gini index. Based on the random features and the optimal split point, the corresponding branch nodes of the initial decision tree are set to obtain the decision tree; Based on all the decision trees, the trained distribution selection model is obtained.

7. The data balanced distribution method as described in claim 1, characterized in that, The step of extracting the second data features of each column in the data offset table and inputting the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table includes: Extract the data dispersion, sampling variance, frequency of associated columns, data range, and maximum response time of each column in the data offset table to obtain the second data feature of each column; The second data feature of each column is input into the trained distribution column selection model to obtain the fit of each column; Based on the fitness level, the optimal distribution column is selected from the data offset table.

8. The data balanced distribution method as described in claim 1, characterized in that, The step of reconstructing the table structure of the data offset table based on the optimal distribution column includes: Export the data from the data offset table to obtain the data to be transferred; Based on the optimal distribution, the table structure is reconstructed to obtain a new table; Import the data to be transferred into the new table.

9. A data balanced distribution system, characterized in that, include: The data table traversal module is used to traverse each data table in the target database and identify balanced distribution columns and data offset tables. The model training module is used to train a pre-constructed distribution column selection model based on the balanced distribution column, so that the distribution column selection model learns the first data feature of the balanced distribution column; The optimal distribution column selection module is used to extract the second data features of each column in the data offset table, and input the second data features into the trained distribution column selection model to obtain the optimal distribution column of the data offset table; The table reconstruction module is used to reconstruct the table structure of the data offset table based on the optimal distribution column.

10. A data distribution equalization device, characterized in that, It includes a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor, wherein the processor, when executing the computer program, implements the data balanced distribution method as described in any one of claims 1 to 8.

Images