Universal cost estimation optimization method based on multi-task cross-database learning

Abstract

The invention discloses a universal cost estimation optimization method based on multi-task cross-database learning, and relates to the technical field of database system performance optimization, and the method comprises the steps: constructing a vectorization tree structure; designing a multi-task learning model of hard shared parameters, wherein the model comprises a shared feature extraction module and independent branch networks respectively corresponding to cost estimation, query optimization and index selection tasks; cost estimation is defined as a regression task prediction cost value, and query optimization and index selection are defined as advantages and disadvantages of a dichotomy task judgment strategy; a meta-learning algorithm is adopted to jointly train a model on a plurality of heterogeneous database query data sets, and universal initial parameters with the capacity of quickly adapting to a new database are obtained. According to the method, implicit knowledge migration and collaborative optimization among tasks are realized, a single-task barrier is overcome, and the overall performance is improved; a cross-database meta-learning strategy enables the model to obtain a strong generalization general initial state; in the face of a new database environment, the deployment period can be remarkably shortened, and expensive retraining is avoided.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database system performance optimization, in particular to a database cost estimation optimization method based on multi-task cross-database learning. BACKGROUND

[0002] In modern database systems, query performance optimization is the core indicator of evaluating system capability. With the exponential growth of data size and the continuous improvement of query complexity, query optimization is facing severe challenges. The current mainstream optimization techniques are mainly divided into traditional optimization methods and optimization methods based on machine learning.

[0003] Traditional optimization methods, such as the built-in optimizer of relational databases such as PostgreSQL, generally rely on optimization strategies based on cost models. This method estimates the execution cost of the query plan through the cost model, and then selects the optimal plan. However, it has inherent technical defects: the cost model is based on overly simplified assumptions such as uniform data distribution and constant hardware performance, resulting in a large deviation between the predicted cost and the actual execution cost, especially when dealing with complex data correlation and distribution; the model relies on static statistical information, and when the data distribution and workload change, the timeliness and accuracy of the statistical information are difficult to guarantee, leading to a decline in optimization effectiveness and the inability to adapt to dynamically changing system environments.

[0004] In recent years, machine learning technology has been introduced into the field of database optimization in order to solve the above-mentioned defects of traditional methods. However, existing machine learning methods still have the following core technical problems:

[0005] Firstly, the knowledge barrier between tasks limits the optimization effect. Database optimization essentially involves multiple closely related aspects such as cost estimation, query optimization (plan selection), and index selection. Existing methods usually adopt the "single-task learning" paradigm, training and optimizing these tasks in isolation, ignoring their inherent strong correlation. For example, the accuracy of cost estimation is the basis for the effectiveness of query optimization and index selection. This task isolation hinders the sharing and migration of knowledge between different tasks, resulting in limited optimization effect in the local area and difficulty in achieving global optimization at the system level.

[0006] Secondly, the weak generalization ability of the model leads to high application and maintenance costs. Most existing AI models are trained on specific databases and specific workloads, forming "specialized" models. When the database system, data pattern, or workload changes, the model performance will decline sharply, showing poor generalization ability. If it is to be applied to a new database environment, it is necessary to collect a large amount of data and perform expensive model retraining, which greatly limits the practical application value and promotion efficiency of AI optimization methods.

[0007] Therefore, there is an urgent need in the field for a new optimization method that can break through task barriers, realize collaborative learning of multiple optimization tasks, and have strong cross-database generalization capability to adapt to new environments at low cost and high efficiency, thereby providing a more efficient and intelligent general optimization solution for modern database systems. SUMMARY

[0008] The present application provides a general cost estimation optimization method based on multi-task cross-database learning, which can solve the above problems.

[0009] To solve the above problems, the technical scheme adopted by the present application is as follows:

[0010] A general cost estimation optimization method based on multi-task cross-database learning, comprising the following steps:

[0011] S1: converting a database query plan tree into a binary tree structure, and encoding each operator node in the binary tree structure to generate a feature vector representing the node operation attribute, thereby forming a vectorized tree structure for neural network processing;

[0012] S2: constructing a multi-task learning model using a hard shared parameter mechanism, which includes a shared feature extraction module and multiple independent task branch networks; the shared feature extraction module is used to extract general feature representations shared by multiple downstream tasks from the vectorized tree structure; the independent task branch networks correspond to the cost estimation task, the query optimization task and the index selection task respectively, each independent task branch network receives the general feature representations and outputs the decision result of the corresponding task;

[0013] S3: configuring learning objectives for the cost estimation task, the query optimization task and the index selection task; wherein the cost estimation task is defined as a regression problem to predict the cost value of query execution as its target; the query optimization task and the index selection task are both defined as binary classification problems to determine whether a specific optimization strategy is better than a benchmark strategy as its target;

[0014] S4: using a meta-learning algorithm to jointly train the multi-task learning model on a query data set derived from multiple heterogeneous databases to learn general initial model parameters that can enable the model to quickly adapt to new database environments.

[0015] Further, in step S1, the feature vector representing the node operation attribute is composed of the operator type information, the cardinality information and the estimated cost information of the node.

[0016] Further, the operator type information is numerically represented by one-hot encoding to uniquely identify the category of the operator in the feature vector.

[0017] Further, in step S2, the shared feature extraction module is a tree convolutional neural network, which captures the structured features of the query plan by performing tree convolution operations on the vectorized tree structure, thereby generating the general feature representation.

[0018] Further, each of the independent task branch networks is a multi-layer perceptron network, which receives the general feature representation generated by the tree convolutional neural network and outputs the decision result through the internal fully connected layer network structure.

[0019] Further, in step S3, the regression problem configured for the cost estimation task is optimized using a Q-error-based loss function; and the binary classification problem configured for the query optimization task and the index selection task are both optimized using a cross-entropy loss function.

[0020] Further, before calculating the loss value using the Q-error-based loss function, the actual query execution cost and the predicted query execution cost of the cost estimation task are normalized based on a robust scaler.

[0021] Further, in step S4, the meta-learning algorithm is a Reptile algorithm, and its training process includes alternately performed inner loop update and outer loop update; the inner loop update is gradient update of model parameters on batch data from a single database or a single task; and the outer loop update is moving the global model parameters to the direction of the parameters optimized for each task after multiple inner loop updates, to update the general initial model parameters.

[0022] Further, the multi-task learning model trained in step S4 is integrated with a database system and an external optimization framework, including: using the predicted execution cost output by the cost estimation task to assist the database system in comparing and selecting execution plans; using the binary classification decision result output by the query optimization task to guide the external query optimization framework to decide whether to adopt the newly generated query plan; and using the binary classification decision result output by the index selection task to score the candidate index configuration set provided by the external index recommendation framework, and selecting the configuration with the highest score for application.

[0023] Compared with the prior art, the present application has the following advantages:

[0024] (1) By constructing a unified multi-task learning framework, the cost estimation, query optimization and index selection tasks are integrated in the architecture of hard-shared parameters. This architecture forces all tasks to share a bottom-layer feature extraction module, thereby realizing the implicit knowledge transfer and sharing between tasks. Each task contributes valuable gradient information to the shared module while optimizing its own objective, making the extracted general feature representation beneficial to all tasks. This design breaks down the knowledge barriers of single-task learning, fully utilizes the internal relevance between tasks, and overcomes the local optimum problem, thereby significantly improving the overall performance of database system optimization.

[0025] (2) The meta-learning training strategy across databases is innovatively introduced. By jointly training on the data of multiple heterogeneous databases, the model's goal is no longer to fit the cost law of a specific database, but to learn a "meta-ability" that can quickly master the cost law of a new database. This makes the trained model have a very strong generalization ability, and when facing a completely new database environment, it can quickly adapt, avoiding the expensive model retraining from scratch, greatly shortening the deployment cycle and reducing the cost of data collection and model maintenance.

[0026] (3) By using Tree-CNN as the shared feature extraction module, the invention can directly operate on the tree structure of the query plan, effectively capturing the parent-child, sibling and other hierarchical relationships between nodes, as well as specific semantic operator combination patterns such as "join-ordering". This deep mining of structured information enables the model to extract more discriminative features, providing high-quality input for the precise decision-making of subsequent optimization tasks.

[0027] (4) Before calculating the cost estimation loss, a robust scaler is used to normalize the cost value. The execution cost of a database query often has a severe skew distribution and outliers (such as extremely fast or slow queries). The robust scaler is based on the interquartile range of the data and is not sensitive to these outliers, effectively preventing them from producing large gradients during training and disrupting model convergence, thereby improving the stability of the training process and ultimately improving the prediction accuracy of the cost estimation model.

[0028] (5) The Reptile meta-learning algorithm is used for training, which only updates the inner and outer loops based on the first-order gradient, greatly simplifying the computational complexity and improving the training efficiency. This makes joint training on multiple tasks and multiple large databases in a complex scenario more computationally feasible, while effectively learning the model's rapid adaptation ability.

[0029] In order to make the above objectives, characteristics and advantages of the present application more obvious and easy to understand, examples of the present application are described below in detail with reference to the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS

[0030] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings needed to be used in the examples will be briefly introduced below. It should be understood that the following drawings only show some embodiments of the present application, and therefore should not be considered as a limitation on the scope. For those skilled in the art, other related drawings can also be obtained without creative labor on the basis of these drawings.

[0031] Figure 1 is a multi-task model structure diagram of the present application;

[0032] Figure 2 is a model learning flowchart of the present application. DETAILED DESCRIPTION

[0033] In order to make the objectives, technical solutions and advantages of the embodiments of the present application more clear, the technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only some of the embodiments of the present application, not all.

[0034] As shown in Figure 1 and Figure 2 , the present application provides a general cost estimation optimization method based on multi-task cross-database learning. The core idea of the present application is to build a unified model that can integrate the three interrelated tasks of cost estimation, query optimization and index selection, and through an innovative training mechanism, it has strong generalization ability across different database environments. The implementation process of this method strictly follows the logical progressive steps, from data preparation, model construction, target definition, model training to the final application integration, forming a complete technical closed loop. First, the present application vectorizes the database query plan tree as the input of the subsequent learning model. In order to improve the generalization ability and knowledge transfer ability of the model on different databases and different tasks, the present application adopts a multi-task learning architecture based on a hard shared parameter mechanism, which captures common knowledge among tasks through a shared feature extraction network, and provides independent decision networks for each task. Then, the present application adopts a meta-learning based training algorithm, which enables the model to learn "how to learn" from cross-database and cross-task experience, so as to quickly adapt to new environments and maintain high performance. Finally, the trained model adopts differentiated application strategies for different tasks, and is deeply integrated with existing database systems or optimization frameworks (such as PostgreSQL, Bao, SWIRL), to realize end-to-end performance improvement.

[0035] The following is a detailed description of the specific implementation steps of the present application:

[0036] Step S1: Generating a structured vector representation of the query plan

[0037] Figure 2 The upper part of the query plan is based on a standard benchmark database (such as IMDB, TPC, Baseball) to generate real query load. The query is executed through the classic SELECT * FROM SQL statement to provide raw data for subsequent analysis.

[0038] In the query plan vectorization stage, the present application constructs a vectorized tree structure for each query plan tree as the training input of the decision model. The purpose of this step is to convert the internal, symbolic query plan of the database into a unified, numerical format that can be processed by machine learning models, i.e., a vectorized tree structure. This process includes two core steps: binaryization of the query plan tree and node feature vectorization.

[0039] 1. Binaryization of the query plan tree:

[0040] In the original query plan tree, some operators, such as Aggregate and Sort, may have multiple child nodes, forming a multi-ary tree structure. To handle such complex structures, the present application first converts the original query plan tree into a strict binary tree structure. The specific conversion rules are as follows: for an operator with only one child node, a virtual node representing "empty" is inserted on its right side to meet the binary structure; for an operator with multiple child nodes, it is split into multiple cascaded binary operations to form a left-depth tree. Through this step, any complex query plan is standardized into a unified binary tree topology, laying the foundation for subsequent feature extraction, Figure 2 The example in the figure schematically shows the operation nodes that the query plan may contain, such as Join, Scan T1, Scan T2, etc. (shown inside the vectorization processing block).

[0041] 2. Feature vector encoding of operator nodes:

[0042] After binaryization, each node in the tree (including real operator nodes and virtual "empty" nodes) is encoded into a feature vector, which represents the node operation attributes. The feature vector mainly contains three parts of information, which are provided by the database query optimizer (such as the EXPLAIN function of PostgreSQL) and integrated into the node vector representation:

[0043] Operator type information is a description of the category of the operation performed by the node (such as merge join, hash join, sorting, etc.). The present application uses One-Hot Encoding to represent it, in order to capture the structural characteristics of the query plan, such as the relationship between the merge join operation and its child node sorting operation.

[0044] Cardinality information is the number of record rows involved in the operation.

[0045] Estimated cost information is the estimated resource consumption of executing the operation.

[0046] In this way, the complex structure of the query plan is effectively converted into a numerical form suitable for processing by a machine learning model.

[0047] Step S2: Construction of a multi-task learning model and feature extraction

[0048] The network structure of the multi-task learning model proposed by the present application is shown in Figure 1 The model mainly consists of a shared feature extraction module and three task-specific independent task branch networks.

[0049] 1. Shared feature extraction module:

[0050] In order to capture common knowledge between tasks and promote knowledge transfer, the present application uses a three-layer Tree-CNN as a shared feature extraction module. Tree-CNN can automatically identify and learn meaningful patterns, such as "merge join-sorting" combinations or "hash join on large relations", etc., by sliding a tree-shaped "filter" over the query plan tree. After three layers of tree convolution processing, the vectorized query plan tree is flattened into a single, high-dimensional shared feature vector by a dynamic pooling layer. This shared feature vector is the general feature representation of the entire query plan.

[0051] 2. Independent task branch network:

[0052] The shared feature vector is then fed into three independent, task-specific multi-layer perceptron (MLP) networks, corresponding to the cost estimation task, the query optimization task, and the index selection task, respectively. This architecture based on the hard shared parameter mechanism forces multiple tasks to share the underlying feature extraction network, not only significantly reducing the number of model parameters and improving computational efficiency, but also promoting knowledge transfer between tasks and enhancing the overall generalization ability of the model. Each MLP branch network receives the general features and outputs the decision results of the corresponding task.

[0053] Step S3: Definition of multi-task learning objectives

[0054] The detailed design of the three database tasks in the application is as follows:

[0055] 1. Cost estimation task

[0056] The cost estimation task is a regression problem. The input of the model is the vectorized query plan tree, and the output is a continuous value, that is, the predicted query execution cost value. The training label of the query is the actual execution cost. In order to solve the problem of different query cost dimensions, the application adopts a robust scaler (Robust Scaler) to normalize the cost data when calculating the loss, and uses a Q-error-based loss function.

[0057] 2. Query optimization task

[0058] The query optimization task is based on the Bao framework, which is a binary classification problem. The Bao framework is a machine learning enhanced database optimizer that dynamically selects the optimal query execution plan through reinforcement learning. The input is the vectorized query plan tree generated by the Bao optimization strategy, and the output label is a binary value, which is used to judge whether the plan generated by Bao is better than the original plan (in terms of actual execution time). The query optimization task adopts a cross-entropy loss function.

[0059] 3. Index selection task

[0060] The index selection task is based on the SWIRL framework, which is also a binary classification problem. SWIRL is an intelligent database index recommendation system that automatically generates and evaluates index configurations through reinforcement learning. The input is the vectorized query plan tree generated after applying the recommended index by SWIRL, and the output label is a binary value, which is used to judge whether using the index configuration is better than not using it (in terms of actual execution time). The index selection task also adopts a cross-entropy loss function.

[0061] Step S4: Cross-database meta-learning training

[0062] The design of the model training process of the application is based on Figure 2 To realize joint training across tasks and databases, the application uses the meta-learning algorithm Reptile. The Reptile algorithm aims to learn the initial parameters of a general model, so that it can quickly adapt to new tasks. Compared with the MAML algorithm which needs to calculate complex second-order derivatives, Reptile significantly improves the computational efficiency while ensuring good cross-task adaptability through the simplified way of multiple intra-task gradient updates and one inter-task parameter approximation, which is more suitable for handling the differentiated multi-task learning scenario in the application.

[0063] The specific training process is as follows: in each iteration, a data batch is randomly extracted from all tasks and heterogeneous databases (such as IMDB and TPC-H). According to the task type to which the batch belongs, the corresponding loss function (Q-error or cross-entropy) is used to perform several steps of gradient update (inner loop update) on the model. After completing the inner loop, the global model parameters are moved a small step in the direction just optimized according to the update rule of Reptile (outer loop update). By alternately performing this process on all tasks, the model parameters are gradually optimized to a general initial model parameter that performs well on all tasks.

[0064] Step S5: Model integration and application

[0065] In the model application stage, the application designs differentiated integration strategies for different tasks, and deeply integrates with the database system or the existing optimization framework:

[0066] (1) In the cost estimation application: the predicted cost value directly output by the model can be used by the database management system to evaluate and select the plan. In the cost estimation optimization experiment, the model of the multi-task cross-database learning framework showed a significant advantage. The experiment selected 20 diverse data sets (including 17 training sets and 3 validation sets such as genome, credit, hepatitis), generated 1000 query statements and divided the training / validation / test set according to 7:1:2. The results show that the traditional PostgreSQL model generally has a high median Q-Error (up to 6-8 times the error of the accidents and employee data sets) due to its dependence on the linear hypothesis, and the bias significantly expands in the complex query scenario. Although the Zero-Shot model performs stably in some data sets (such as credit, genome), the overall fluctuation is large. In contrast, the model of the present application reduces the median Q-Error of 18 data sets to below that of other models through multi-task collaboration and cross-database knowledge transfer, and the optimization rate of the Baseball data set is more than 25%, verifying its generalization ability. Under the Synthetic load of the IMDB data set, the median Q-Error of the model of the present application (1.17) is significantly lower than that of PostgreSQL (3.61) and Zero-Shot (1.36), and the 99th percentile is optimized to 2.27 (98.4% lower than 142.24 of PostgreSQL); in the Scale load of the IMDB data set, the high percentile error of the model (95th = 1.94) is reduced by 44.6% compared with the single-task training model (3.50), proving that multi-task sharing of features effectively suppresses extreme errors. It is worth noting that the model still maintains a median Q-Error of 1.45 on the untrained genome data set, which is better than the 2.92 of Zero-Shot, highlighting the adaptability of cross-database learning to changes in data distribution.

[0067] (2) In query optimization application: the present application combines the model with the Bao framework. The system first uses the cost estimation output of the model to preliminarily evaluate the candidate plans generated by Bao, and then combines the binary classification decision result of the query optimization task to decide whether to adopt the optimization suggestion of Bao, so as to realize the mixed decision of the learning optimizer and the traditional optimizer. In the query optimization experiment, the multi-task model proposed in the present application shows significant advantages in the cross-database scenario. In the experiment, IMDB and TPC-H are selected as the training data set (each containing 1000 queries), and Baseball is selected as the cross-database verification set. By comparing the PostgreSQL native optimizer, the Bao optimizer and the single-task training model, the query execution time is used as the core evaluation index. The model in the present application realizes the lowest execution time on the three data sets: IMDB (163s→142s), TPC-H (148s→124s), and Baseball (109s→81s). Among them, the optimization amplitude on the cross-database Baseball data set is as high as 25.69%, which is an additional improvement of 10.09 percentage points compared with the Bao model. This cross-database generalization ability verifies the adaptability of multi-task learning to heterogeneous data distribution. Under the Synthetic load of the IMDB data set, the 99th percentile execution time of the model in the present application is only 3.26 seconds, which is reduced by 31% compared with PostgreSQL (4.72 seconds), and is significantly better than the single-task model (3.55 seconds); in the high-pressure scene (99th percentile) of the JOB-light load of the IMDB data set, the model compresses the execution time from 8.52 seconds to 4.33 seconds, with a reduction of 49.2%. In the end-to-end test, the average query time of TPC-H is reduced from 2.66 seconds to 2.05 seconds, which provides a stable and efficient optimization scheme for high-concurrency database systems.

[0068] (3) In index selection application: the present application integrates the model with the SWIRL framework. First, a batch of candidate index configurations are generated by SWIRL, and then the effect of each index configuration under a specific workload is scored (1 point for yes, 0 point for no) using the index selection task output of the model, and finally the index configuration scheme with the highest total score is selected. In the index selection experiment, the multi-task model proposed in the present application shows significant advantages on the TPC-H dataset. The experiment uses the query time reduction ratio as the core indicator and compares the performance of the model and SWIRL under different index configurations. When the index configuration is small (0.5GB-1GB), the query time reduction ratio of the two models maintains around 20%, and the optimization effect is similar. But as the index capacity increases to more than 4GB, the model proposed in the present application shows strong performance improvement potential - the query time reduction ratio breaks through 80% under 6GB index configuration, which is 30 percentage points higher than the 50% of SWIRL, and still maintains more than 80% optimization amplitude under 7GB configuration, which is significantly better than the 55% of SWIRL. The experimental results show that when the system provides sufficient index resources, the model can more efficiently utilize the index resources, avoid the suboptimal selection caused by local optimization of traditional methods, and provide a new idea for the physical design optimization of large-scale database systems.

[0069] The above only describes the preferred embodiments of the present application and is not intended to limit the present application. For those skilled in the art, the present application can have various modifications and changes. Any modification, equivalent replacement, improvement, etc. within the spirit and principles of the present application shall be included in the protection scope of the present application.

Claims

1. A general cost estimation optimization method based on multi-task cross-database learning, characterized in that, The method comprises the following steps: S1: converting a database query plan tree into a binary tree structure, encoding each operator node in the binary tree structure, generating a feature vector representing the node operation attribute, and thus forming a vectorized tree structure for neural network processing; S2: constructing a multi-task learning model using a hard shared parameter mechanism, the model comprising a shared feature extraction module and multiple independent task branch networks; the shared feature extraction module is used to extract general feature representations shared by multiple downstream tasks from the vectorized tree structure; each independent task branch network corresponds to a cost estimation task, a query optimization task, and an index selection task, and each independent task branch network receives the general feature representations and outputs the decision results of the corresponding task; S3: configuring learning objectives for the cost estimation task, the query optimization task, and the index selection task; wherein the cost estimation task is defined as a regression problem to predict the cost value of query execution as its objective; the query optimization task and the index selection task are both defined as binary classification problems to determine whether a specific optimization strategy is better than a benchmark strategy as their objectives; S4: using a meta-learning algorithm to jointly train the multi-task learning model on a query data set derived from multiple heterogeneous databases to learn general initial model parameters that enable the model to quickly adapt to new database environments. 2.The method of claim 1, wherein, In step S1, the feature vector representing the node operation attribute is composed of the operator type information, the cardinality information, and the estimated cost information of the node. 3.The method of claim 2, wherein, The operator type information is numerically represented by one-hot encoding to uniquely identify the category of the operator in the feature vector. 4.The method of claim 1, wherein, In step S2, the shared feature extraction module is a tree convolutional neural network that captures the structured features of the query plan by performing tree convolution operations on the vectorized tree structure, thereby generating the general feature representations. 5.The method of claim 4, wherein, Each independent task branch network is a multi-layer perceptron network that receives the general feature representations generated by the tree convolutional neural network and outputs the decision results through its internal fully connected layer network structure. 6.The method of claim 1, wherein, In step S3, the regression problem configured for the cost estimation task is optimized using a Q-error-based loss function; the binary classification problems configured for the query optimization task and the index selection task are both optimized using a cross-entropy loss function. 7.The method of claim 6, wherein, Before calculating the loss value using the Q-error-based loss function, the real query execution cost value and the predicted query execution cost value of the cost estimation task are normalized based on a robust scaler. 8.The method of claim 1, wherein, In step S4, the meta-learning algorithm is the Reptile algorithm, and its training process includes alternating inner loop updates and outer loop updates; the inner loop update is a gradient update of the model parameters on batch data from a single database or a single task; the outer loop update is to move the global model parameters towards the parameter direction optimized by each task after multiple inner loop updates to update the general initial model parameters. 9.The method of claim 1, wherein, Integrating the multi-task learning model trained in step S4 with a database system and an external optimization framework, including: Using the predicted cost value output by the cost estimation task to assist the database system in comparing and selecting an execution plan; Using the binary classification decision result output by the query optimization task to guide the external query optimization framework to decide whether to adopt the newly generated query plan; Using the binary classification decision result output by the index selection task to score the candidate index configuration set provided by the external index recommendation framework and select the configuration with the highest score for application.

Images