A Database Query Performance Prediction Method and System Based on Cost Factor Calibration

Abstract

The present invention belongs to the technical field of data processing, and provides a method and system for predicting database query performance based on cost factor calibration, including: calibrating the cost factors in the database query optimizer module by means of statistical regression; extracting the category features of the execution plan nodes in the optimizer for the executed historical execution plans by means of dictionary encoding; generating embedded features from the category features of the execution plan nodes through a feature processing neural network; splicing the embedded features into an embedded feature tree according to the execution structure of the original execution plan, and binary-treeifying the embedded feature tree; training a convolutional neural network according to the embedded feature tree, and obtaining the query performance for the execution plan to be processed by using the trained tree-shaped convolutional neural network. A more accurate and effective query performance prediction effect is achieved.

Description

Technical Field

[0001] The present invention belongs to the technical field of data processing, and in particular, relates to a method and system for predicting database query performance based on cost factor calibration. Background Art

[0002] The statements in this part merely provide background technical information related to the present invention, and do not necessarily constitute prior art.

[0003] In recent years, many research works have focused on two aspects: optimizing the traditional cost model of databases and using neural networks to learn and replace the traditional cost model. For the former, it can effectively improve the computing power of the traditional cost model of databases and promote the iterative update of database optimizers. However, the cost calculation formula can never cover all aspects. In many complex scenarios, the existing formulas cannot accurately predict query performance. Moreover, the focus of existing cost formulas is mostly on the relative magnitude values of costs between execution plans, without specific value units. And for different hardware and execution environments, using the default relative ratio between calculation units, the cost calculation results usually cannot well reflect the execution performance of queries. For the latter, neural networks can effectively fit the execution performance of queries. However, in terms of feature construction, it is often of low density and information sparse. Therefore, the features input into the network model cannot well extract the association and structural information between nodes in the execution plan. At the same time, existing machine learning methods often ignore the knowledge of existing cost models in databases, and there is still much room for improvement in the prediction accuracy of query performance.

[0004] For the current two query performance prediction methods, the common advantage is that the technologies used are relatively mature in themselves. However, there is a lack of integration between them, and there are still the following three problems not solved in query performance prediction: First, since the focus of the cost model is on comparing the relative values of plan costs, there is a lack of accuracy in query performance prediction, and a cost formula oriented to query performance needs to be designed for supplementation or correction. Second, many neural networks will ignore the partial order relationship in the execution plan when constructing features, and the encoded information obtained for execution nodes is sparse, and it cannot well capture the mutual correlation between execution nodes and the tree structure information of the execution plan. Third, existing neural networks do not combine the knowledge of the cost model of databases for learning, and fail to effectively integrate the domain knowledge of the database cost model. Summary of the Invention

[0005] To solve at least one of the technical problems existing in the above background art, the present invention provides a database query performance prediction method and system based on cost factor calibration, which effectively integrates the domain knowledge of the cost model in the database query optimizer, and establishes a feature processing neural network and a tree-shaped convolutional neural network for execution plans, effectively capturing the mutual relevance of execution nodes and the tree-shaped structure information of execution plans, and effectively improving the accuracy of query performance prediction results. This method can solve the key problems in query optimization and query scheduling.

[0006] To achieve the above object, the present invention adopts the following technical solutions:

[0007] The first aspect of the present invention provides a database query performance prediction method based on cost factor calibration, including the following steps:

[0008] Obtain the historical data of the execution plan corresponding to the regression query statement;

[0009] According to the historical data of the execution plan corresponding to the regression query statement, adopt the method of statistical regression calibration to fit the values of the cost factors in the cost formula, and obtain the cost model of the database query optimizer;

[0010] Obtain the original training data set, and obtain the training data feature set and training data label set based on the original training data set; based on the training data feature set and training data label set, train the tree-shaped deep convolutional neural network model to obtain the deep calibration model;

[0011] For the execution plan to be predicted, combine the deep calibration model to obtain the predicted error time offset, obtain the cost time based on the cost model of the database query optimizer, and sum the predicted error time offset and the cost time to obtain the execution time of the query for the execution plan to be predicted.

[0012] The second aspect of the present invention provides a database query performance prediction system based on cost factor calibration, including:

[0013] A data acquisition module for obtaining the historical data of the execution plan corresponding to the regression query statement;

[0014] A cost model construction module for fitting the values of the cost factors in the cost formula by using the method of statistical regression calibration according to the historical data of the execution plan corresponding to the regression query statement, and obtaining the cost model of the database query optimizer;

[0015] A deep calibration model construction module for obtaining the original training data set, obtaining the training data feature set and training data label set based on the original training data set; based on the training data feature set and training data label set, training the tree-shaped deep convolutional neural network model to obtain the deep calibration model;

[0016] The query performance prediction module is used to obtain the predicted error time offset for the execution plan to be predicted, in combination with the deep calibration model, obtain the cost time based on the database query optimizer cost model, and sum the predicted error time offset and the cost time to obtain the execution time of the query for the execution plan to be predicted.

[0017] The third aspect of the present invention provides a computer-readable storage medium.

[0018] A computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, it implements the steps in a database query performance prediction method based on cost factor calibration as described above.

[0019] The fourth aspect of the present invention provides a computer device.

[0020] A computer device includes a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the program, it implements the steps in a database query performance prediction method based on cost factor calibration as described above.

[0021] Compared with the prior art, the beneficial effects of the present invention are:

[0022] 1. The neural network prediction scheme used in the present invention effectively integrates the domain knowledge of the cost model in the database query optimizer, which can effectively improve the accuracy of the prediction results.

[0023] The cost factors are calibrated by means of statistical regression, and each cost factor is adjusted to a value adapted to the current hardware environment, which represents the real execution time of the cost factor. Therefore, the result calculated by the cost formula is more referential.

[0024] 3. The encoding of the execution plan nodes in the present invention uses a feature processing neural network to convert the sparse feature vector of information into a dense embedding vector of information, effectively enriching the feature information of the execution nodes.

[0025] 4. For the performance prediction model of the execution plan, a tree-shaped convolutional neural network oriented to the execution plan is established, which effectively captures the mutual relevance of the execution nodes and the tree-shaped structure information of the execution plan. The use of the model to predict the error time offset and jointly predict the query performance with the result of the database cost formula is more accurate.

[0026] The advantages of the additional aspects of the present invention will be partly given in the following description, partly will become obvious from the following description, or will be understood through the practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] The accompanying drawings forming a part of this invention are used to provide a further understanding of the invention. The schematic embodiments and descriptions thereof of the invention are used to explain the invention and shall not unduly limit the invention.

[0028] Figure 1 It is the overall prediction flowchart of the database query performance prediction method in the first embodiment of the present invention;

[0029] Figure 2 It is the overall framework flowchart of the implementation of the method in the first embodiment of the present invention;

[0030] Figure 3 It is the schematic diagram of the feature processing neural network structure in the first embodiment of the present invention;

[0031] Figure 4 It is the schematic diagram of the binary tree conversion of the query execution plan in the first embodiment of the present invention;

[0032] Figure 5 It is the schematic diagram of the structure of the tree-shaped convolutional neural network in the first embodiment of the present invention. Detailed implementation manners

[0033] The present invention will be further described below in conjunction with the accompanying drawings and embodiments.

[0034] It should be noted that the following detailed descriptions are all illustrative and are intended to provide further descriptions of the present invention. Unless otherwise specified, all technical and scientific terms used herein have the same meaning as commonly understood by those of ordinary skill in the technical field to which the present invention belongs.

[0035] It should be noted that the terms used herein are only for describing specific implementation manners and are not intended to limit the exemplary embodiments according to the present invention. As used herein, unless otherwise clearly specified in the context, the singular form is also intended to include the plural form. In addition, it should be understood that when the terms "comprise" and / or "include" are used in this specification, they indicate the presence of features, steps, operations, devices, components, and / or combinations thereof.

[0036] Embodiment 1

[0037] As Figure 1 - Figure 2 shown, this embodiment provides a database query performance prediction method based on cost factor calibration, including the following steps:

[0038] S1: Obtain the historical data of the execution plan corresponding to the regression query statement;

[0039] S2: According to the historical data of the execution plan corresponding to the regression query statement, adopt the method of statistical regression calibration to fit the values of the cost factors in the cost formula to obtain the database query optimizer cost model;

[0040] S3: Obtain the original training dataset, and based on the original training dataset, obtain the training data feature set and the training data label set; based on the training data feature set and the training data label set, train the tree-shaped deep convolutional neural network model to obtain the depth calibration model;

[0041] S4: For the execution plan to be predicted, in combination with the depth calibration model, obtain the predicted error time offset, obtain the cost time based on the database query optimizer cost model, and sum the predicted error time offset and the cost time to obtain the execution time for querying the execution plan to be predicted.

[0042] The historical data obtained for the execution plan corresponding to the regression query statement in S1 includes:

[0043] Input and run a set of regression query statements into the database, and obtain and store the historical data of the execution plan corresponding to the regression query statement, including information such as the cardinality, the number of scanned blocks, and the execution time.

[0044] In this embodiment, the regression query includes the following five queries Q1 to Q5, where R and T are relation tables existing in the database, and A and B are index columns on the R relation table.

[0045] Q1: SELECT * FROM R

[0046] Q2: SELECT * FROM T

[0047] Q3: SELECT COUNT(*) FROM R

[0048] Q4: SELECT * FROM R where R.A < a

[0049] Q5: SELECT * FROM R where R.B < b

[0050] The historical data of the execution plan corresponding to the regression query statement includes: the actual execution times t1 - t5 corresponding to Q1 - Q5, the cardinality n of the R table R , the number of data pages p occupied by the R table R , the cardinality n of the T table T , the number of data pages p occupied by the T table T , the number of rows n processed by the COUNT function F , the number of rows n processed by the R.A index RA , the number of data pages p occupied by the R.A index RA , the number of rows n processed by the R.A < a filtering operation FA , the number of rows n processed by the R.B index RB , the number of data pages p occupied by the R.B index RB , the number of rows n processed by the R.B < b filtering operationFB 。

[0051] S2: According to the historical data of the execution plan corresponding to the regression query statement, using the method of statistical regression calibration, fit the values of the cost factors in the cost formula to obtain the cost model of the database query optimizer;

[0052] Calibrate the cost model of the database query optimizer according to the actual execution time of the regression query statement. Using the method of statistical regression calibration, fit the values of the cost factors in the cost formula, and endow the cost factors with actual representational meanings - measured in execution time.

[0053] The cost formulas corresponding to regression queries Q1 to Q5 are as follows:

[0054] Q1: Cost = p R ·c s +n R ·c t

[0055] Q2: Cost = p T ·c s +n T ·c t

[0056] Q3: Cost = p R ·c s +n R ·c t +n F ·co

[0057] Q4: Cost = p RA ·c r +n RA ·c t +n RA ·c i +n FA ·c o

[0058] Q5: Cost = p RB ·c r +n RB ·c t +n RB ·c i +n FB ·c o

[0059] Among them, the cost factors of statistical regression calibration include: the I / O cost factor c for sequentially scanning a data page s 、the I / O cost factor c for randomly scanning a data page r 、the CPU cost factor c for processing one row of data t, the CPU cost factor c for processing an index item i , the CPU cost factor c for processing each function or operator o .

[0060] The cost calculation formula for each query in a set of regression queries contains different types of cost factors. Designing in this way ensures a sufficient number of regression queries. Execute and collect the actual execution time of each query and the number of operations for each cost factor as the true value result and coefficients of the cost calculation formula. Statistically regress to obtain the values of different cost factors through different query combinations;

[0061] Based on the obtained execution plan historical data, including information such as cardinality, number of scanned blocks, execution time, etc., substitute into the Q1 - Q5 cost formulas and solve the equations simultaneously to obtain the values of the cost factors.

[0062] S3: Obtain the original training data set, and based on the original training data set, obtain the training data feature set and the training data label set; based on the training data feature set and the training data label set, train the tree - shaped deep convolutional neural network model to obtain the depth calibration model;

[0063] In S3, the method for obtaining the original training data set is as follows:

[0064] Input and run a set of SQL query workloads into the database system. The database system will generate corresponding execution plans for each SQL. Each execution plan contains multiple operations and execution time. After each SQL is executed, store the obtained execution plan as the original training data set.

[0065] In S3, perform feature encoding processing on the execution plans of the original training data set, as Figure 3 - Figure 4 shown.

[0066] First, extract category features for the execution plan nodes, extract the database system table data and generate a dictionary table, and perform dictionary encoding on the execution plan nodes according to the dictionary table;

[0067] The category features included in the dictionary include category features such as node type, connection method, scan direction, relationship table name, relationship table alias, index name, aggregation strategy, etc.;

[0068] Among them, the node types include: scan node type, control node type, materialized node type, and join node type. The scan node type includes Seq Scan, Index Scan, Bitmap Index / Heap Scan, etc. The control node type includes append, etc. The materialized node type includes Materialize, Sort, Group, Aggregation, etc. The join node type includes Nested Loop Join, Hash Join, Merge Join, etc.

[0069] Secondly, according to the extracted metadata information, i.e., the database system table data, construct the dictionary table data. The value of each item in the dictionary is one-hot encoded. For example, if the node type information of the node extracted from the execution plan is Seq Scan, the value at the corresponding position of Seq Scan is 1, and the values at the other positions of the node type are 0.

[0070] Based on the original training dataset, extract the information of each node in each execution plan in the order of post-order traversal, encode the nodes according to the category feature information in the dictionary table to obtain the dictionary encoding features, and use the running time of the nodes as the labels corresponding to these features;

[0071] Train the feature processing neural network based on the dictionary encoding features and the corresponding labels to obtain the trained feature processing neural network;

[0072] The structure of the feature neural network is that the first layer is the input layer, the middle six layers are hidden layers, and the last layer is the output layer. The first two hidden layers are RNN layers, the third hidden layer is a flattening layer, and the last three hidden layers are fully connected layers. The activation functions used in the hidden layers and the output layer are all Relu. The first two RNN hidden layers each output 35 nodes, and the dimension of each node is 128. The number of nodes output by the flattening layer is 4480. The number of nodes in the last three fully connected layers is 256, 128, and 64 in sequence. The loss function is the mean squared error cost function; the obtained embedding vector is the end hidden layer of the feature processing neural network, and the output layer is the real running time of the execution plan node.

[0073] Based on the original training dataset and the trained feature processing neural network, obtain the embedding vector of each node in the execution plan of the original training dataset.

[0074] The obtaining of the training data feature set and the training data label set based on the original training dataset includes:

[0075] Based on the embedding vector of each node in the execution plan of the original training dataset, combine the embedding vectors according to the tree structure of the execution plan, and binary treeify the embedding vector tree to generate the feature vector binary tree of each plan, and obtain the training data feature set;

[0076] Calculate the difference between the actual execution time of each execution plan in the original training data set and the cost time calculated by the cost factor to obtain the error time offset, and obtain the training data label set.

[0077] In S3, based on the training data feature set and the training data label set, train the tree-shaped deep convolutional neural network model to obtain the depth calibration model;

[0078] The model structure of the depth calibration model is as Figure 5 shown. The first layer is the input layer, the middle 7 layers are hidden layers, and the last layer is the output layer. The first three hidden layers are convolutional layers, the fourth hidden layer is a pooling layer, and the last three hidden layers are fully connected layers. The activation functions used in the hidden layers and the output layer are all Relu. The number of nodes in the first three convolutional layers is 512, 256, and 128 in sequence. The number of nodes output by the pooling layer of the hidden layer is 128. The number of nodes in the last three fully connected layers is 128, 64, and 32 in sequence. The loss function is the mean squared error cost function; the obtained output layer is the error time offset of the execution plan node.

[0079] S4: For the execution plan to be predicted, combine the depth calibration model to obtain the predicted error time offset. Based on the database query optimizer cost model, obtain the cost time, and sum the predicted error time offset and the cost time to obtain the execution time of querying the execution plan to be predicted.

[0080] Embodiment 2

[0081] This embodiment provides a database query performance prediction system based on cost factor calibration, including:

[0082] A data acquisition module, configured to acquire historical data of the execution plan corresponding to the regression query statement;

[0083] A cost model construction module, configured to fit the numerical value of the cost factor in the cost formula by using the method of statistical regression calibration according to the historical data of the execution plan corresponding to the regression query statement, and obtain the database query optimizer cost model;

[0084] A depth calibration model construction module, configured to obtain the original training data set, and obtain the training data feature set and the training data label set based on the original training data set; based on the training data feature set and the training data label set, train the tree-shaped deep convolutional neural network model to obtain the depth calibration model;

[0085] A query performance prediction module, configured to, for the execution plan to be predicted, combine the depth calibration model to obtain the predicted error time offset, obtain the cost time based on the database query optimizer cost model, and sum the predicted error time offset and the cost time to obtain the execution time of querying the execution plan to be predicted.

[0086] Example 3

[0087] This embodiment provides a computer-readable storage medium, on which a computer program is stored. When the program is executed by a processor, it implements the steps in a database query performance prediction method based on cost factor calibration as described above.

[0088] Example 4

[0089] This embodiment provides a computer device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the program, it implements the steps in a database query performance prediction method based on cost factor calibration as described above.

[0090] Those skilled in the art should understand that the embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of hardware embodiments, software embodiments, or embodiments combining software and hardware aspects. Moreover, the present invention can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk memories and optical memories, etc.) containing computer-usable program code.

[0091] The present invention is described with reference to the flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to the embodiments of the present invention. It should be understood that each flow and / or block in the flowcharts and / or block diagrams, and the combination of flows and / or blocks in the flowcharts and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to the processors of general-purpose computers, special-purpose computers, embedded processors, or other programmable data processing devices to generate a machine, so that the instructions executed by the processors of the computer or other programmable data processing devices generate means for implementing the functions specified in Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0092] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory generate a manufactured product including instruction means, and the instruction means implements the functions specified in Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0093] These computer program instructions can also be loaded onto a computer or other programmable data processing device, so that a series of operation steps are executed on the computer or other programmable device to generate a computer-implemented process, and thus the instructions executed on the computer or other programmable device provide for implementing the process Figure 1 in one process or multiple processes and / or blocks Figure 1 steps for the functions specified in one block or multiple blocks.

[0094] Those of ordinary skill in the art can understand that all or part of the processes in the methods of the above embodiments can be completed by instructing relevant hardware through a computer program. The program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above methods. Among them, the storage medium can be a magnetic disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM), etc.

[0095] The foregoing is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention can have various changes and modifications. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

Claims

1. A method for predicting the performance of database queries based on cost factor calibration, characterized in that The steps include: Obtain historical data of the execution plan corresponding to the regression query statement; Based on the historical data of the execution plan corresponding to the regression query statement, the statistical regression calibration method is used to fit the value of the cost factor in the cost formula to obtain the database query optimizer cost model; Obtaining an original training data set, and obtaining a training data feature set and a training data label set based on the original training data set; training a tree-shaped deep convolutional neural network model based on the training data feature set and the training data label set to obtain a deep calibration model; For the execution plan to be predicted, the deep calibration model is combined to obtain the predicted error time offset. The cost time is obtained based on the database query optimizer cost model. The predicted error time offset and the cost time are summed to obtain the execution time of the query execution plan to be predicted. The cost factors in the cost formula include the I / O cost factor of sequentially scanning a data page, the I / O cost factor of randomly scanning a data page, the CPU cost factor of processing a row of data, the CPU cost factor of processing an index item, and the CPU cost factor of processing each function or operator; The step of obtaining a training data feature set and a training data label set based on the original training data set includes: Based on the embedding vector of each node of the execution plan of the original training data set, the embedding vectors are combined according to the tree structure of the execution plan, and the embedding vector tree is binarized to generate a binary tree of feature vectors for each plan to obtain the training data feature set; The actual execution time of each execution plan in the original training data set is subtracted from the cost time calculated by the cost factor to obtain the error time offset and the training data label set.

2. The database query performance prediction method based on cost factor calibration according to claim 1, characterized in that The statistical regression calibration method is used based on the historical data of the execution plan corresponding to the regression query statement, and the value of the cost factor in the fitting cost formula includes: Obtain known quantities in the cost formula corresponding to all regression query statements based on historical data; Substitute the known quantity into the corresponding cost formula, solve all the cost formulas together to obtain the value of the cost factor.

3. The database query performance prediction method based on cost factor calibration according to claim 1, characterized in that: The cost calculation formula involved in each query in the regression query contains different types of cost factors. The actual execution time of each query and the number of operations of each cost factor are executed and collected as the true value result and coefficient of the cost calculation formula. Statistical regression is performed through different query combinations to obtain the numerical values of different cost factors.

4. The database query performance prediction method based on cost factor calibration according to claim 1, wherein The method for generating the embedding vector of each node in the execution plan of the original training dataset is: Based on the original training data set, the node information in each execution plan is extracted in sequence through subsequent traversal. The nodes are encoded according to the category feature information in the dictionary table to obtain dictionary encoding features, and the node running time is used as the label corresponding to the feature. The feature processing neural network is trained based on the dictionary encoding features and the corresponding labels to obtain a trained feature processing neural network. Based on the original training dataset and the trained feature processing neural network, the embedding vector of each node of the execution plan of the original training dataset is obtained.

5. The database query performance prediction method based on cost factor calibration according to claim 3, characterized in that: The historical data of the execution plan corresponding to the regression query statement includes: cardinality, number of scan blocks and execution time.

6. A database query performance prediction system based on cost factor calibration, characterized in that, include: The data acquisition module is used to obtain historical data of the execution plan corresponding to the regression query statement; A cost model construction module is used to fit the values of cost factors in a cost formula using a statistical regression calibration method based on historical data of execution plans corresponding to regression query statements to obtain a database query optimizer cost model; wherein the cost factors in the cost formula include the I / O cost factor of sequentially scanning a data page, the I / O cost factor of randomly scanning a data page, the CPU cost factor of processing a row of data, the CPU cost factor of processing an index item, and the CPU cost factor of processing each function or operator; A deep calibration model construction module is used to obtain an original training data set, obtain a training data feature set and a training data label set based on the original training data set; and train a tree-shaped deep convolutional neural network model based on the training data feature set and the training data label set to obtain a deep calibration model. The step of obtaining a training data feature set and a training data label set based on the original training data set includes: Based on the embedding vector of each node of the execution plan of the original training data set, the embedding vectors are combined according to the tree structure of the execution plan, and the embedding vector tree is binarized to generate a binary tree of feature vectors for each plan to obtain the training data feature set; Subtract the actual execution time of each execution plan in the original training dataset from the cost time calculated using the cost factor to obtain the error time offset and the training data label set. The query performance prediction module is used to obtain the predicted error time offset for the execution plan to be predicted in combination with the deep calibration model, obtain the cost time based on the database query optimizer cost model, and sum the predicted error time offset and the cost time to obtain the execution time of the query execution plan to be predicted.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by a processor, the steps of the database query performance prediction method based on cost factor calibration as described in any one of claims 1 to 5 are implemented.

8. A computer device, comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, When the processor executes the program, the steps of the database query performance prediction method based on cost factor calibration according to any one of claims 1 to 5 are implemented.

Images