Query optimizer optimization method based on MIMO architecture
By adopting the uncertainty-aware learning sorting method based on MIMO architecture in the query optimizer, the problem of insufficient robustness of the query optimizer in the dynamic environment in the prior art is solved, and higher prediction accuracy and robustness are achieved.
Patent Information
- Application Number
- CN202510143749.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-02-10
- Publication Date
- 2025-05-13
AI Technical Summary
Existing learning query optimizers tend to lead to performance degradation and insufficient robustness when handling dynamic workloads, and fail to effectively quantify and exploit uncertainty in the selection of candidate execution plans.
The uncertainty-aware learning sorting query optimizer based on MIMO architecture is adopted to independently predict the relative ranking scores and uncertainties of candidate query plans through multiple subnets, and combine uncertainty to guide the selection of candidate execution plans, enhancing the robustness of the optimizer in a dynamic environment.
While maintaining high prediction accuracy, the robustness of the query optimizer is significantly enhanced, allowing more efficient handling of dynamic workloads and integration into existing DBMS platforms without large modifications.
Smart Images
Figure CN119988431A_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the technical field of database query optimization, and in particular relates to a query optimizer optimization method based on a MIMO architecture. Background Art
[0002] In the field of database query optimization, query optimizer plays a key role in database management systems and aims to improve query performance by carefully selecting the most efficient candidate query plan, thereby reducing query execution time and optimizing resource utilization.
[0003] The existing learning query optimizers usually use a cost model based on a deep neural network to select the optimal query plan, which can be roughly described as the following process:
[0004] 1. Collect historical queries and their execution plans, execution times and other related information from the database as training data.
[0005] 2. Build a cost model based on a deep neural network, such as a tree-convolutional neural network, a tree-long short-term memory network, etc. that can receive a query plan tree as input. Input the preprocessed training data into the deep neural network for training, with the goal of minimizing the error between the execution cost predicted by the cost model and the actual execution cost. After training, the model will learn the mapping relationship between the query plan tree and the execution cost.
[0006] 3. Apply the trained cost model to the database query optimization task. The usual practice is that according to the input SQL query, the traditional query optimizer of the database management system will generate several candidate query plans in a tree structure, and then the cost model will vectorize each query plan tree and then predict the execution cost of each candidate query plan.
[0007] 4. The query optimizer will select the best query plan from a large number of candidate query plans according to certain criteria, such as the lowest execution cost, the lowest relative ranking score, or the lowest execution time, as the final query plan, and input it into the execution engine for execution.
[0008] However, the above implementation process has the following disadvantages:
[0009] 1. The cost models of these query optimizers often ignore the uncertainty inherent in their predictions, assuming that the variance of the results such as cost, ranking or execution time predicted by the neural network module is zero. This oversight leads to a lack of quantification of uncertainty in the decision-making process, which results in the final plan selection based only on the predicted result value without considering the level of uncertainty.
[0010] 2. Learning query optimizers’ heavy reliance on training data makes them prone to performance degradation below standard benchmarks and compromised robustness when exposed to new workloads with changing data distribution and query patterns, if they lack corresponding means - a critical flaw in query optimization practice. Summary of the invention
[0011] In order to solve the shortcomings existing in the above-mentioned background technology, the present invention proposes an uncertainty-aware learning sorting query optimizer optimization method based on the MIMO architecture. For query optimization tasks, a plan sorter and plan selection strategy based on the MIMO architecture are adopted to quantify uncertainty and use uncertainty to guide the selection of candidate execution plans, so that the optimizer can make wise decisions. While maintaining high prediction accuracy, the robustness of query optimization in dynamic environments is significantly enhanced, and it can be seamlessly integrated into the existing DBMS platform without a lot of modifications.
[0012] The technical solution adopted by the present invention is:
[0013] A query optimizer optimization method based on MIMO architecture includes the following steps:
[0014] S1. For each SQL query, it is first parsed into a syntax parse tree by the parser in the database management system, and then N candidate execution plan trees are generated by the native query optimizer;
[0015] S2. For each candidate execution plan tree, use the information encoding to query the plan tree nodes and convert them into a binary tree, thereby obtaining a vectorized execution plan tree.
[0016] S3. Through the plan sorter module based on the MIMO architecture, the execution plan tree is predicted to be relatively ranked, and the relative ranking scores and uncertainties of the candidate execution plans are output. The specific method is as follows:
[0017] The plan sorter module includes multiple subnetworks. Each subnetwork uses a tree convolutional neural network to extract the nodes and structural features of the execution plan tree. The extracted structural features are then input into the prediction layer to predict the relative ranking score of the execution plan among many candidate execution plans.
[0018] By inputting the execution plan tree into each subnet for independent prediction, the relative ranking scores of all subnet predictions are collected;
[0019] The average value of all subnet predictions is used as the output of the plan sorter to obtain the relative ranking score of the current execution plan tree, which is defined as S(p). At the same time, the variance of all subnet prediction values is used as the quantitative uncertainty of the current execution plan tree, which is defined as U(p). In this way, the relative ranking scores and uncertainties of N execution plan trees are obtained respectively.
[0020] S4. The following two plan selection strategies that take uncertainty into consideration are used to select the optimal execution plan as the final plan, combining the prediction results and uncertainty factors. Specifically:
[0021] The first plan selection strategy: set an uncertainty threshold τ, and filter out plans whose uncertainty exceeds the threshold τ based on all the U(p) obtained, so that the execution plan with the lowest relative ranking score in the remaining execution plan group is selected as the final candidate plan;
[0022] Second plan selection strategy: Calculate all execution plans by r·U(p)+S(p), and select the execution plan with the lowest value as the final candidate plan, where r is the set empirical parameter;
[0023] S5. Input the selected final candidate plan into the execution engine for execution.
[0024] The beneficial effect of the present invention is that the present invention can be seamlessly integrated into a database management system, so that the existing query optimizer has significant improvements in robust query optimization while maintaining high prediction accuracy. BRIEF DESCRIPTION OF THE DRAWINGS
[0025] Figure 1 Design of query optimization system architecture diagram for the present invention
[0026] Figure 2 Designing a query plan tree vectorized graph for the present invention
[0027] Figure 3 An uncertainty perception map based on a MIMO architecture is designed for the present invention. DETAILED DESCRIPTION
[0028] The present invention is described in detail below in conjunction with the accompanying drawings:
[0029] The uncertainty-aware learning-sorting query optimizer based on the MIMO architecture of the present invention performs query optimization, specifically adopting the following steps:
[0030] S1. For each SQL query, it is first parsed into a syntax parse tree by the parser in the database management system, and then N candidate execution plans are generated by the native query optimizer.
[0031] S2. For each candidate execution plan tree, encode the query plan tree node using information such as node operator, cardinality estimation, row width, and operation table, and convert it into a binary tree to obtain a vectorized execution plan tree to prepare for subsequent operations.
[0032] S3. Through the plan sorter module based on the MIMO architecture, the candidate execution plans are predicted to be relatively ranked, and the relative ranking scores and uncertainties of the candidate execution plans are output.
[0033] S4. Through a carefully designed plan selection strategy that takes uncertainty into consideration, combined with the forecast results and uncertainty factors, the optimal execution plan is selected as the final plan according to certain standards.
[0034] S5. Input the selected final plan into the execution engine for execution.
[0035] The detailed implementation method of each step is as follows:
[0036] First, the query optimizer performs query optimization tasks, aiming to estimate the execution cost or execution time of multiple candidate query plans corresponding to a SQL query through a cost model, and select the candidate query plan with the smallest execution cost or execution time to be thrown into the execution engine for execution. However, existing practices usually do not quantify uncertainty and combine uncertainty to select candidate plans. However, doing so usually makes it difficult for the query optimizer to handle dynamic workloads and cannot consistently achieve robust query optimization results. The uncertainty-aware learning ranking query optimizer based on the MIMO architecture, such as Figure 1 As shown, we exploit the power of multiple sub-networks to independently predict candidate query plans and generate robust composite predictions while better supporting the measurement of uncertainty in predicting query plan ranking scores. We design two uncertainty-aware plan selection strategies. These innovative approaches significantly enhance the robustness of the query optimizer in dynamic environments.
[0037] In the S1 part, for each SQL query, it is first parsed into a syntax parse tree by the parser in the database management system, and then N candidate execution plans are generated by the native query optimizer.
[0038] In part S2, for each candidate query plan tree, each node is represented as a node vector. Our method considers several key factors that affect node cost, including physical operator information, cardinality estimation information, row width information, and table operation information. Finally, we use the tree structure binarization method to convert the potential non-binary query plan tree into a strict binary query plan tree, which greatly simplifies the tree convolution. The query plan encoding of a simple SQL query is as follows: Figure 2 shown.
[0039] For the vectorized query plan tree p, we designed a plan sorter based on the MIMO architecture, such as Figure 3As shown in the figure, it consists of M input-output pairs, each input-output pair is mapped to a subnetwork, and each subnetwork independently uses a tree convolutional neural network to extract the features of the vector tree, and then outputs the relative ranking score of the plan tree through the prediction layer. We calculate the average of the predicted values of the M subnetworks as the final relative ranking score S(p) of the plan tree, and the variance is used as the uncertainty U(p) of predicting the plan tree.
[0040] Part S4 outputs the relative ranking score S(p) and uncertainty U(p) of each plan p for the plan sorter, using two uncertainty-aware plan evaluation strategies.
[0041] The first strategy is to select a plan by screening. This strategy assumes that if the prediction of a candidate execution plan has high uncertainty, the plan is unreliable even if its predicted relative ranking score is low. The strategy is as follows: Given a query Q, a set of candidate query plans will be generated after the above steps, where each plan p has a predicted ranking score S(p) and uncertainty U(p). We set an uncertainty threshold τ for this set of candidate plans based on the ratio Ra, and first filter out plans with uncertainty exceeding the threshold τ. Then, we select the candidate plan with the lowest relative ranking score in the remaining candidate plan group as the final candidate plan.
[0042] The second strategy is to perform plan selection by calculating a mixed score. This strategy assumes that the actual rank of a plan is lower than its estimated rank, and this difference is proportional to the variance of its estimated ranking score. This strategy eliminates plans with higher rankings but higher uncertainty (i.e., plans with larger variance). The strategy is as follows: Given a query Q, the above steps will generate a set of candidate query plans, where each plan p has a predicted ranking score S(p) and uncertainty U(p). We select the plan with the lowest r·U(p)+S(p) as the final candidate plan.
[0043] The S5 part is to input the selected final plan into the execution engine for execution.
Claims
1. A query optimizer optimization method based on MIMO architecture, characterized in that: The following steps are involved: S1. For each SQL query, it is first parsed into a syntax parse tree by the parser in the database management system, and then N candidate execution plan trees are generated by the native query optimizer; S2. For each candidate execution plan tree, use the information encoding to query the plan tree node and convert it into a binary tree, thereby obtaining N vectorized execution plan trees; S3. Through the plan sorter module based on the MIMO architecture, the execution plan tree is predicted to be relatively ranked, and the relative ranking score and uncertainty of each candidate execution plan are output. The specific method is as follows: The plan sorter module includes multiple subnetworks. Each subnetwork uses a tree convolutional neural network to extract the nodes and structural features of the execution plan tree. The extracted structural features are then input into the prediction layer to predict the relative ranking score of the execution plan among many candidate execution plans. By inputting the execution plan tree into each subnet for independent prediction, the relative ranking scores of all subnet predictions are collected; The average value of all subnet predictions is used as the output of the plan sorter to obtain the relative ranking score of the current execution plan tree, which is defined as S(p). At the same time, the variance of all subnet prediction values is used as the quantitative uncertainty of the current execution plan tree, which is defined as U(p). In this way, the relative ranking scores and uncertainties of N execution plan trees are obtained respectively. S4. The following two plan selection strategies that take uncertainty into consideration are used to select the optimal execution plan as the final plan, combining the prediction results and uncertainty factors. Specifically: The first plan selection strategy: set an uncertainty threshold τ, and filter out plans whose uncertainty exceeds the threshold τ based on all the U(p) obtained, so that the execution plan with the lowest relative ranking score in the remaining execution plan group is selected as the final candidate plan; Second plan selection strategy: Calculate all execution plans by r·U(p)+S(p), and select the execution plan with the lowest value as the final candidate plan, where r is the set empirical parameter; S5. Input the selected final candidate plan into the execution engine for execution.