A data distribution optimization method and a distributed database system

Abstract

The application discloses a data distribution optimization method and a distributed database system, comprising: acquiring data distribution, a query plan and temporary data redistribution of each node of the distributed database system; calculating an optimal distribution key by using a preset optimization algorithm; and optimizing query performance based on the query plan and correcting distribution column selection of a related table; acquiring a query request, and optimizing associated queries by using the optimal distribution key and the corrected distribution column selection, so as to schedule related query tasks to appropriate data nodes; and completing data redistribution based on a scheduling result of the query task scheduling and the optimal distribution key, so as to migrate data with affinity to the same data node. By calculating the optimal distribution key and dynamically adjusting the distribution column selection, the application can optimize the query plan and operation, and improve query efficiency.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a data distribution optimization method and a distributed database system. Background Technology

[0002] In modern distributed database systems, data nodes store the actual data and are distributed in shards using a hash algorithm. Distributed systems typically provide methods to manage physical skew, ensuring data is evenly distributed across all nodes. However, during query execution, join operations are frequently required, necessitating a certain affinity between related data, meaning they should be located on the same node. Simply focusing on physical skew cannot prevent frequent cross-node queries, thus impacting query efficiency.

[0003] Currently, some distributed database systems support temporary data redistribution. This means that, based on the query plan and optimization strategy, the system can temporarily migrate data with affinity to the same node when needed, reducing the number of cross-node queries and improving query efficiency. However, existing systems lack the ability to intelligently handle data distribution and cannot dynamically correct and optimize based on the query plan, temporary data redistribution, and table column relationships, thus failing to achieve optimal query performance. Summary of the Invention

[0004] This application provides a data distribution optimization method and a distributed database system to optimize query plans and operations and improve query efficiency.

[0005] This application provides a data distribution optimization method, including:

[0006] To obtain the data distribution, query plan, and temporary data redistribution of each node in a distributed database system;

[0007] Based on the data distribution, query plan, and temporary data redistribution, the optimal distribution key is calculated using a preset optimization algorithm; and,

[0008] Based on the query plan, the selection of the distribution columns of the relevant tables is adjusted to optimize query performance;

[0009] Obtain query requests and optimize related queries using the optimal distribution key and the corrected distribution column selection, so as to schedule related query tasks to appropriate data nodes;

[0010] Based on the scheduling results of the query task and the optimal distribution key, data redistribution is completed to migrate data with affinity to the same data node.

[0011] Optionally, it also includes receiving optimized query tasks, executing query operations on the corresponding data nodes, and returning the results to the query requester.

[0012] Optionally, based on the data distribution, query plan, and temporary data redistribution, calculating the optimal distribution key using a preset optimization algorithm includes:

[0013] Obtain the key value indicators that the query plan hits in the distributed database system, and discretize the key value indicators to obtain the hit probability of the key value indicators; and,

[0014] Statistically analyze the redistribution of temporary data in the distributed database system and record the results.

[0015] The optimal distribution key is obtained by calculating the hit probability of relevant key value indicators and the statistical results according to a preset weight ratio.

[0016] Optionally, the optimal distribution key is obtained by calculating the hit probability of relevant key value indicators and the statistical results according to a preset weight ratio, satisfying the following:

[0017] Distribution Key Ranking Score = α * Key Value Index + β * Temporary Data Redistribution Status Index + γ * Table Column Association Index

[0018] Wherein, α, β, and γ are the corresponding weighting factors, and the relevant key value indicators include the number of keyword hits, the indexes used in the query, and the filtering conditions of the query; the temporary data redistribution status indicators include the creation and use of temporary tables and the uniformity of data distribution; the table column association indicators include the association types between tables and the frequency of use of associated fields.

[0019] Optionally, optimizing query performance by adjusting the distribution column selection of the relevant tables based on the query plan includes:

[0020] The query execution time and data transfer volume metrics are introduced into the distribution key ranking.

[0021] Optionally, the optimal distribution key can be re-evaluated and revised if the data distribution and query patterns change.

[0022] This application also proposes a distributed database system, including a processor and a memory, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, it implements the steps of the data distribution optimization method described above.

[0023] This application also proposes a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of the aforementioned data distribution optimization method.

[0024] This application's embodiments, by processing data distribution and dynamically correcting the selection of distribution columns, can optimize processing based on the query plan and table column relationships. By calculating the optimal distribution key and dynamically adjusting the selection of distribution columns, this invention can optimize query plans and operations, thereby improving query efficiency.

[0025] The above description is only an overview of the technical solution of this application. In order to better understand the technical means of this application and to implement it in accordance with the contents of the specification, and to make the above and other objects, features and advantages of this application more obvious and understandable, the following are specific embodiments of this application. Attached Figure Description

[0026] Various other advantages and benefits will become apparent to those skilled in the art upon reading the following detailed description of preferred embodiments. The accompanying drawings are for illustrative purposes only and are not intended to limit the scope of this application. Furthermore, the same reference numerals denote the same parts throughout the drawings. In the drawings:

[0027] Figure 1 This is an example of the application architecture for the data distribution optimization method in the embodiments of this application;

[0028] Figure 2 This is a flowchart example of the data distribution optimization method in an embodiment of this application;

[0029] Figure 3 This is an example of the data distribution optimization effect of the data distribution optimization method in the embodiments of this application. Detailed Implementation

[0030] Exemplary embodiments of the present disclosure will now be described in more detail with reference to the accompanying drawings. While exemplary embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure may be implemented in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the disclosure to those skilled in the art.

[0031] This application provides an intelligent query optimization method and system for handling data distribution and cross-node queries. The system framework is as follows: Figure 1 As shown, the method of this application collects and analyzes temporarily redistributed data through data tracking, and calculates the optimal distribution key by combining the query plan, temporary redistribution status, and table column relationships, and dynamically adjusts the selection of distribution columns for related tables to achieve query optimization and improve query efficiency. Specifically, this application provides a data distribution optimization method, such as... Figure 2 As shown, it includes the following steps:

[0032] In step S201, the data distribution, query plan, and temporary data redistribution of each node in the distributed database system are obtained.

[0033] In step S202, based on the data distribution, query plan, and temporary data redistribution, the optimal distribution key is calculated using a preset optimization algorithm; and,

[0034] In step S203, the distribution column selection of the relevant tables is adjusted based on the query plan to optimize query performance.

[0035] In step S204, a query request is obtained, and the associated query is optimized using the optimal distribution key and the corrected distribution column selection, so as to schedule the relevant query tasks to appropriate data nodes.

[0036] In step S205, based on the scheduling result of the query task scheduling and the optimal distribution key, data redistribution is completed to migrate data with affinity to the same data node, reducing the need for cross-node queries.

[0037] In some embodiments, the method further includes receiving an optimized query task, performing the query operation on the corresponding data node, and returning the result to the query requester.

[0038] This application's embodiments, by processing data distribution and dynamically correcting the selection of distribution columns, can optimize processing based on the query plan and table column relationships. By calculating the optimal distribution key and dynamically adjusting the selection of distribution columns, this invention can optimize query plans and operations, thereby improving query efficiency.

[0039] In some embodiments, calculating the optimal distribution key using a preset optimization algorithm based on the data distribution, query plan, and temporary data redistribution includes:

[0040] The relevant key values ​​hit by the query plan in the distributed database system are obtained, and discretized based on these key values ​​to obtain the hit probability of the key values. The result set is recorded as a weight consideration for the final optimal recommendation of the distribution key. Additionally, the temporary data redistribution in the distributed database system is statistically analyzed, and the statistical results are recorded. This result set is also recorded as a weight consideration for the final optimal recommendation of the distribution key.

[0041] The optimal distribution key is obtained by calculating the hit probability of relevant key value indicators and the statistical results according to a preset weight ratio.

[0042] Based on the current table relationships in the system, the optimal distribution key ranking score is calculated using a certain weighting of the query plan and temporary redistribution record results, and the optimal data distribution key is selected. In some embodiments, the optimal distribution key is obtained by calculating the hit probability of relevant key value indicators and the statistical results according to a preset weighting ratio, satisfying the following:

[0043] Distribution Key Ranking Score = α * Key Value Index + β * Temporary Data Redistribution Status Index + γ * Table Column Association Index

[0044] Wherein, α, β, and γ are the corresponding weighting factors, and the relevant key value indicators include the number of keyword hits, the indexes used in the query, and the filtering conditions of the query; the temporary data redistribution status indicators include the creation and use of temporary tables and the uniformity of data distribution; the table column association indicators include the association types between tables and the frequency of use of associated fields.

[0045] In this embodiment, the query plan-related key value indicators may include factors such as the number of keyword hits, the indexes used in the query, and the filtering conditions of the query; the temporary data redistribution status indicators may include factors such as the creation and use of temporary tables and the uniformity of data distribution; the table column association indicators may include factors such as the association type between tables and the frequency of use of associated fields; the weighting factors (α, β, γ) can be adjusted according to specific needs to reflect the importance of different factors to the optimal distribution key.

[0046] During the system monitoring phase, additional factors such as access frequency and system load can be considered to determine whether dynamic adjustment of the system's distribution key and triggering of a redistribution process are necessary. In this formula, we consider three factors: query plan-related key value indicators, temporary data redistribution status indicators, and table column association indicators. By multiplying them by the corresponding weighting factors (α, β, γ), we comprehensively calculate the final distribution key ranking score.

[0047] Based on the results of the optimal distribution key calculated in real time, the system takes into account many factors such as access frequency and system default parameters. For example, it sets a dynamic distribution execution threshold. When the access volume reaches the system setting value 'a' and the difference between the current distribution key and the optimal distribution key reaches the system setting threshold 'b', the system redistribution process is triggered.

[0048] After the dynamic correction of the system distribution key is completed, the system will conduct a new round of data collection and calculation, and record the results of the existing optimization strategy as a weight consideration for the next round of dynamic correction of the system distribution key.

[0049] This application also proposes some improvements, including the following:

[0050] Improvements in statistical methods and algorithms allow for more precise discrete calculations when collecting and analyzing key-value hits, temporary data redistribution, and table column relationships in query plans. This ensures that the calculation results more accurately reflect the probability of related key-value hits and other indicators. Specific examples of statistical methods include:

[0051] Regression analysis: Regression analysis can be used to build models of the relationship between the probability of a related key value occurring and other indicators. In this case, the causal relationship between the occurrence of a related key value and other indicators can be modeled, and then a regression model can be used to predict and infer the probability of the occurrence of the event. Regression analysis can help understand the interactions between different factors and predict the probability of a related key value occurring under given conditions.

[0052] Analysis of variance (ANOVA): ANOVA is used to compare whether the differences in means between different groups are significant. In this case, different settings or conditions of the query plan can be divided into several groups, and then ANOVA can be used to determine whether there are significant differences in the probability of related key values ​​hitting events and other indicators between the groups. ANOVA can help determine which factors have a significant impact on the probability of related key values ​​hitting events and other indicators.

[0053] Nonparametric statistical methods: Nonparametric statistical methods do not rely on assumptions about the population distribution and are suitable for situations where the data is not normally distributed or the sample size is small. In such cases, nonparametric statistical methods can be used to compare the distribution of the probability events of associated key values ​​and other indicators under different conditions, such as using the Wilcoxon rank-sum test or the Mann-Whitney U test. Nonparametric methods are more likely to ensure the accuracy of the calculation results when the sample size is small or the data does not meet the normality assumption.

[0054] Dynamically Adjusting Weighting Factors: Depending on the actual situation, weighting factors (α, β, γ) can be dynamically adjusted at runtime. For example, based on changes in system performance and resource utilization, a request volume threshold can be set. When a tiered request volume threshold c is reached, the weighting factors will also be adjusted accordingly. Similarly, when system storage reaches a set threshold, the weighting factors will also be adjusted to optimize the accuracy and performance of distributed key selection.

[0055] Refining metrics and data collection: When determining key metrics related to the query plan, temporary data redistribution status metrics, and table column association metrics, the scope of the metrics can be further refined and expanded. For example, among the key metrics related to the query plan, the impact of different types of queries (such as range queries and join queries) on the optimal distribution key can be considered. At the same time, ensuring the comprehensiveness and accuracy of data collection is crucial to obtaining more reliable calculation results.

[0056] In some embodiments, optimizing query performance by adjusting the distribution column selection of related tables based on the query plan includes introducing query execution time and data transfer volume metrics into the distribution key ranking. In specific examples, in addition to considering factors such as the query plan and table column relationships, performance evaluation metrics can be introduced to comprehensively evaluate the performance of different distribution keys. For example, metrics such as query execution time and data transfer volume can be considered to select the optimal distribution key that satisfies both data distribution uniformity requirements and improves query performance.

[0057] Regular evaluation and optimization: Regularly evaluate the effectiveness of the distributed key selection technique and optimize and improve it based on actual conditions. Adjust weighting factors and other parameters by analyzing actual data and performance indicators to adapt to changes in different scenarios and needs.

[0058] In some embodiments, the optimal distribution key is re-evaluated and corrected when data distribution and query patterns change. Considering data changes and evolution: after dynamically correcting the system distribution key, the changes and evolution trends of the data should be continuously monitored. If the data distribution and query plan change, relevant information is statistically calculated, and if a threshold is reached, the distribution key correction process is triggered. After this process phase ends, the changes and evolution trends of the data are continuously monitored. When changes are captured again, the previous process is triggered to achieve dynamic optimization, maintaining the system's efficiency and scalability.

[0059] In traditional distributed database systems, data is distributed across different nodes, leading to frequent cross-node queries and low query efficiency when performing join operations. This application addresses the problem of frequent cross-node queries in existing technologies by intelligently processing data distribution and dynamically correcting the selection of distribution columns. By temporarily redistributing data, data with affinity is concentrated on the same node, reducing cross-node query operations. Therefore, queries can be performed directly on the local node, avoiding the latency and resource consumption caused by cross-node queries. Thus, the method of this application significantly improves query efficiency, enabling distributed database systems to achieve higher performance.

[0060] This application's method solves the problem of low query efficiency in existing technologies by intelligently processing data distribution and dynamically correcting the selection of distribution columns. Existing solutions lack intelligent query optimization capabilities and cannot dynamically correct and optimize based on query plans, temporary data redistribution, and table column relationships. In contrast, this invention, by calculating the optimal distribution key and dynamically adjusting the selection of distribution columns, can optimize processing based on query plans and table column relationships. Through intelligent data distribution and column selection strategies, this invention can improve the efficiency of query plans and reduce redundant query operations. Therefore, this invention can better utilize resources and optimize query plans and operations during the query process, thereby improving query efficiency.

[0061] This application also proposes a distributed database system, including a processor and a memory, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, it implements the steps of the data distribution optimization method described above.

[0062] For some specific examples, see, for example... Figure 1 The distributed database system of this application, as shown in the architecture, includes the following functional modules:

[0063] Data collection and analysis module:

[0064] By using data tracking technology, information such as data distribution, query plans, and temporary data redistribution can be collected on various nodes in a distributed database system.

[0065] The collected data is transmitted to the data analysis module for processing.

[0066] Data Analysis and Computation Module:

[0067] Receive and process data transmitted from the data collection and analysis module, including data distribution, query plans, and temporary data redistribution.

[0068] Based on this data, the optimal distribution bond is calculated using algorithms and computational models.

[0069] By combining the query plan and table column relationships, the selection of distribution columns for related tables is dynamically adjusted to optimize query performance.

[0070] Query optimization module:

[0071] Receive query requests and query plans.

[0072] The query is optimized by using the optimal distribution key and the corrected distribution column selection.

[0073] Based on the relevant information in the query plan, the query task is scheduled to be executed on the appropriate data node.

[0074] Data redistribution module:

[0075] Data redistribution is achieved based on the scheduling results of the query optimization module and the optimal distribution key.

[0076] Migrate data with affinity to the same node to reduce the need for cross-node queries. Figure 3 The results of the data distribution optimization are shown.

[0077] Query execution module:

[0078] Receive optimized query tasks.

[0079] Perform a query operation on the specified data node and return the result to the query requester.

[0080] The system presented in this application significantly improves the performance and efficiency of distributed database systems. By intelligently processing data distribution and dynamically correcting the selection of distribution columns, the system reduces the need for cross-node queries during the query process, optimizes the query plan and operations, thereby reducing query latency and resource consumption. This application has significant advantages in improving the query performance and efficiency of distributed database systems.

[0081] This application also proposes a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of the aforementioned data distribution optimization method.

[0082] It should be noted that, in the embodiments of this application, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0083] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0084] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes several instructions to cause a terminal (which may be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in the various embodiments of this application.

[0085] The embodiments of this application have been described above with reference to the accompanying drawings. However, this application is not limited to the specific embodiments described above. The specific embodiments described above are merely illustrative and not restrictive. Those skilled in the art can make many other forms under the guidance of this application without departing from the spirit and scope of the claims. All of these forms are within the protection scope of this application.

Claims

1. A data distribution optimization method, characterized in that, include: To obtain the data distribution, query plan, and temporary data redistribution of each node in a distributed database system; Based on the data distribution, query plan, and temporary data redistribution, the optimal distribution key is calculated using a preset optimization algorithm; and, Based on the query plan, the selection of the distribution columns of the relevant tables is adjusted to optimize query performance; Obtain query requests and optimize related queries using the optimal distribution key and the corrected distribution column selection, so as to schedule related query tasks to appropriate data nodes; Based on the scheduling results of the query task scheduling and the optimal distribution key, data redistribution is completed to migrate data with affinity to the same data node. Based on the data distribution, query plan, and temporary data redistribution, the optimal distribution key is calculated using a preset optimization algorithm, including: Obtain the key value indicators that the query plan hits in the distributed database system, and discretize the key value indicators to obtain the hit probability of the key value indicators; and, Statistically analyze the redistribution of temporary data in the distributed database system and record the results. The optimal distribution key is obtained by calculating the hit probability of relevant key value indicators and the statistical results according to a preset weight ratio.

2. The data distribution optimization method as described in claim 1, characterized in that, It also includes receiving optimized query tasks, executing query operations on the corresponding data nodes, and returning the results to the query requester.

3. The data distribution optimization method as described in claim 1, characterized in that, Based on the hit probability of relevant key value indicators, the statistical results are weighted according to a preset ratio to obtain the optimal distribution key that satisfies: The distribution key ranking score = α * relevant key value index + β * temporary data redistribution status index + γ * table column association index, where α, β, and γ are corresponding weighting factors. The relevant key value index includes the number of keyword hits, the indexes used in the query, and the filtering conditions of the query. The temporary data redistribution status index includes the creation and usage of temporary tables and the uniformity of data distribution. The table column association index includes the association types between tables and the frequency of use of associated fields.

4. The data distribution optimization method as described in claim 3, characterized in that, Based on the query plan, the selection of distribution columns for the relevant tables is modified to optimize query performance, including introducing query execution time and data transfer volume metrics into the distribution key ranking.

5. The data distribution optimization method as described in claim 1, characterized in that, When data distribution and query patterns change, the optimal distribution key is re-evaluated and revised.

6. A distributed database system, characterized in that, It includes a processor and a memory, wherein the memory stores a computer program that, when executed by the processor, implements the steps of the data distribution optimization method as described in any one of claims 1 to 5.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the steps of the data distribution optimization method as described in any one of claims 1 to 5.

Images