A database query optimization method based on data constraints

Abstract

The present invention discloses a database query optimization method based on data constraints. The present invention comprises the following steps: Step 1: Obtaining a query statement; Step 2: Obtaining a table encoding; Step 3: Inputting the query statement into a parsing module and parsing it into a query tree; Step 4: Inputting the query tree into a traditional query optimizer; Step 5: Encoding the plan; Step 6: Predicting the execution time of the plan using a TCNN tree convolutional neural network; Step 7: Cost correction; Step 8: Database execution module; Step 9: Experience replay. The present invention addresses the cost estimation error problem existing in traditional databases by using a tree convolutional neural network to fully learn the structural characteristics of the query plan. On this basis, a cost estimation correction method is designed using data constraints existing between multiple columns to improve the training efficiency of the tree convolutional neural network model. The present invention can improve the training efficiency of the model, provide accurate cost estimates, and ultimately achieve the purpose of query optimization.

Description

Technical Field

[0001] The present invention relates to the fields of database query optimization and reinforcement learning. It utilizes reinforcement learning to construct a plan cost estimation model used in database query optimization, and employs data constraints to correct estimation errors in plan cost estimation caused by data constraints. This approach addresses the problem of inaccurate plan cost estimation, resulting in suboptimal plans and low query efficiency in traditional database query processes. Background Art

[0002] Query optimization remains a challenging topic in database management systems. During the execution of a query statement, it is parsed into a syntax tree, which is then transformed into a query tree through semantic analysis. This query tree is then sent to the query optimizer, which generates multiple candidate queries. Cardinality and cost estimates are then used to select an optimal query plan and send it to the executor. Therefore, cardinality and cost estimates are particularly important in traditional database query optimization, guiding the optimizer's plan selection. However, traditional optimizers' cost estimates do not provide high-quality estimates. One reason for this is that traditional database management systems cannot provide information about the relationships between columns in a table. The relationships between columns in a table are called data constraints. Traditional database management systems use statistical information on individual column data in a table to estimate cardinality, but this estimate can be inaccurate for two columns with data constraints.

[0003] With the development of artificial intelligence, the database community has proposed the concept of "AI4DB," which aims to use AI technology to optimize database queries. One of the proposed concepts is to use AI technology to transform the components of the query optimizer. Because a reasonable query plan is crucial for query processing, replacing the cardinality estimation and cost estimation modules in the query optimizer with appropriate AI technology can improve the efficiency and quality of execution plan generation. In the field of query optimization, some scholars have begun applying machine learning to query optimization. One scholar used reinforcement learning to optimize the join order during query optimization, but this scholar focused only on minimizing the cost of table join order in the query plan, not the cost of the entire plan. Another scholar proposed an end-to-end cost estimator based on deep learning, but training data is difficult to obtain, and the training time is particularly long, a problem that needs to be addressed in the development of "AI4DB."

[0004] The present invention is a database query optimization method based on data constraints. Based on the traditional database query optimizer, this method uses the data constraints between columns to optimize the cost estimation module of the query plan and provide the optimal plan for the query plan execution module. Summary of the Invention

[0005] This paper proposes a database query optimization method based on data constraints. This method addresses the cost estimation errors common in traditional databases by using a tree convolutional neural network (CNN) to fully learn the structural characteristics of query plans. Furthermore, a cost estimation correction method is designed using data constraints between multiple columns to improve the training efficiency of the CNN model. The proposed method primarily involves two aspects: First, it uses data constraints to correct the plan cost estimate. First, the query statement is optimized using data constraints. The optimized query statement achieves better performance than the unoptimized query statement (after being converted into a query tree, it has more accurate cardinality and cost estimates on traditional optimizers). Then, the cost estimate of the query plan for the unoptimized query statement is replaced with the optimized cost estimate. Secondly, for plan cost estimation, feature selection, feature encoding, and model training are performed. The leaf nodes of the plan tree are scan nodes, and the non-leaf nodes are link nodes. When selecting features, the close relationship between the features and the plan cost is considered. The final selected features are the operation type, cost, and cardinality of each node in the plan tree. Features particularly present in scan nodes are the data distribution characteristics of the corresponding table. Operation types are encoded using a single-bit encoding method, while costs and cardinality are encoded using standardized floating-point numbers. Tables use standardized floating-point encoding for integer data distribution, and word vector encoding for character data. Because plans have a tree structure, we use a tree convolutional neural network to predict plan quality. Experience replay technology is incorporated into model training to ensure rapid model convergence.

[0006] In summary, this paper addresses the cost estimation error problem in traditional databases and proposes an optimization method. Its core technology is to use data constraints to correct the cost estimate of the plan, enabling the neural network to learn the corresponding relationship between the plan and the cost. This method can improve the training efficiency of the model, provide accurate cost estimates, and ultimately achieve the goal of query optimization. The specific implementation includes the following steps:

[0007] Step 1: Discover data constraints

[0008] Input: Each table in the database

[0009] 1-1. Use data mining methods to obtain data constraints between multiple columns. The output is a set of data constraints, F∈{F1,F2,...,F k}

[0010] Output: The set of data constraints of all tables F∈{F1, F2, ..., F k}

[0011] Step 2: Get the query statement

[0012] Input: connection graph between database tables, data of all columns in each table.

[0013] 2-1. Randomly select a table containing data constraints based on the database connection diagram;

[0014] 2-2. Randomly select a column from the table, randomly select a data point in this column, and connect it using symbols (the connection symbols for column data are >, <, =, != for integer data and like and not like for character data) to obtain the query predicate;

[0015] The predicate format is: table.column connection symbol data such as: t1.column>data;

[0016] 2-3. Combine the selected table, join relation, and query predicate into a query statement q a ;

[0017] 2-4. If the query statement q a If there are predicates that satisfy the data constraints, it means that the query statement q a If the scan node of the table in the generated plan cannot scan any data, the query predicate of the table can be rewritten to another predicate, such as the primary key of the table is greater than the maximum range of the primary key. In this way, the rewritten query statement q is obtained. b ;

[0018] Output: query statement and optimized query statement (q a ,q b ).

[0019] Step 3: Get the encoding of table data

[0020] Input: data from each table

[0021] 3-1. Use Word2Vec() to train the character type data in the table into a dictionary;

[0022] 3-2. Encode each row of data in the table. Integer data uses standardized floating-point encoding, and character data encoding is obtained from the dictionary in 3-1.

[0023] 3-3. Use the weighted average method to normalize all rows into a row vector represented as v, and finally obtain the table vector V of n tables ts [v1, v2, ...v n ];

[0024] Output: table vector V corresponding to n tables ts [v1, v2, ...v n ];

[0025] Step 4: Query optimization method based on data constraints

[0026] Input: query statement and optimized query statement (q a ,q b );

[0027] 4-1. Input the query statement and the optimized query statement into the traditional query optimizer;

[0028] 4-2. Set parameter K;

[0029] 4-3. Obtain the optimal K query plans given by the traditional optimizer and query q a The K best query plans a [plan a1 , plan a2 ,...,plan ak ],q b The K best query plans b [plan b1 , plan b2 ,...,plan bk ];

[0030] 4-4. Plans a and plans b Encode each plan in the plan. The plan has a tree structure of (parent node, (left child tree), (right child tree)). Leaf nodes are scan nodes, and non-leaf nodes are connection nodes. The operation type, cost, and cardinality of each node are encoded. The operation type is encoded as a single-bit encoding, and the cost and cardinality are encoded as standardized floating-point numbers. After encoding, the tree vector (parent node vector, (left child vector tree), (right child vector tree)) is obtained.

[0031] 4-5.plans a and plans b Among all the leaf nodes in the plan, if the leaf node scans the table t containing data constraints, then the leaf node vector is replaced by the following vector: This node vector is input into a fully connected layer, and the table vector v corresponding to table t is replaced by t From V ts Then, we input another fully connected layer and concatenate the vectors output by the two fully connected layers. a and table vector v t Plans after connection at [plan at1 , plan at2 ,...,plan atk ], and plans b Plans after connecting with table vector bt [plan bt1, plan bt2 ,...,plan btk ];

[0032] 4-6. Plans at Input the tcnn tree convolutional neural network and predict the optimal plan a_best and plan a_best Execution time lantency a_best , obtain (plan a_best , lantency a_best );

[0033] 4-7. Plans bt Input the tcnn tree convolutional neural network and predict the optimal plan b_best and plan a_best Execution time lantency b_best , obtain (plan b_best , lantency b_best );

[0034] 4-8. Plan a_best , lantency a_best ) in the lantency a_best Replaced with lantency b_best , obtain (plan a_best , lantency b_best );

[0035] 4-9. Plan a_best Send it to the database execution module to get the actual execution time lantency a_real , we define the actual execution time as reward (Reward), observe the reward. The experience gained (plan a_best , lantency b_best , lantency a_real ), stored in the experience table;

[0036] 4-10. Use the experience in the experience table to train the neural network. The goal is formalized as a loss minimization problem. The loss is defined as:

[0037] loss=∑(lantency a_real -lantency b_best ) 2 ;

[0038] 4-11. The experience replay module completes a training session using the experience stored in the experience table;

[0039] The main contributions of the present invention are:

[0040] First, a data-constrained cost estimation correction method is proposed to provide a more accurate cost estimate for the query plan. The goal of the tree convolutional neural network is to minimize the square of the difference between the more accurate cost estimate and the true cost value. Therefore, this more accurate cost estimate can improve the training efficiency of the model. Secondly, the present invention uses a tree convolutional neural network architecture, which is trained using experience replay technology. After the traditional optimizer generates several candidate plans, it replaces the traditional optimizer's cost estimator and selects the optimal plan to achieve the goal of optimizing the query. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Figure 1 It is a flow chart of the steps for implementing the method of the present invention. DETAILED DESCRIPTION

[0042] like Figure 1 The figure shows a specific flow chart for implementing the database query optimization method based on data constraints, which mainly includes the following steps:

[0043] Step 1: Get the query statement

[0044] 1-1. Randomly select several tables with data constraints in join relationships, extract all tables and join relationships from the join relationships, randomly select several columns and certain data in the extracted tables, and then connect them using the join symbol to obtain predicates; connect these predicates using AND or OR to obtain a query statement in the following format: SELECT COUNT(*) FROM table WHERE join relationship AND predicate OR predicate;

[0045] The predicate format is: table.column connection symbol data such as: t1.column>data;

[0046] 1-2. At the end of two identical query statements, one adds the predicate before rewriting and the other adds the predicate after rewriting, and finally obtains the query statement q before rewriting a : SELECT COUNT(*) FROM table WHERE join relation AND predicate OR predicate AND rewrite the predicate before; and the query statement after rewriting q b :SELECT COUNT(*) FROM table WHERE join relation AND predicate OR predicate AND rewritten predicate;

[0047] Step 2: Get the table encoding

[0048] 2-1. Use Word2Vec() to train the character type data in the table into a dictionary;

[0049] 2-2. Encode each row of data in the table. Integer data uses standardized floating-point encoding, and character data encoding is obtained from the dictionary trained in step 2-1.

[0050] 2-3. Use the weighted average method to normalize all rows into a row vector represented as v, and finally obtain the table vector V of n tables ts [v1, v2, ...v n ];

[0051] Step 3: The query statement input parsing module parses it into a query tree;

[0052] 3-1. Change the query statement q a The input parsing module parses the query q a The corresponding query tree T a ;

[0053] 3-2. Change the query statement q b The input parsing module parses the query q a The corresponding query tree T b ;

[0054] The parsing module is a module that comes with the traditional database.

[0055] Step 4: Query tree input to traditional query optimizer

[0056] 4-1. Get K query plans from the candidate plans given by the traditional optimizer, and specifically query tree T a Input the traditional query optimizer and get the query statement q a K plans a [plan a1 , plan a2 ,...,plan ak ]; query tree T b Input the traditional query optimizer and get the query statement q b K query plans b [plan b1 , plan b2 ,...,plan bk ];

[0057] Step 5: Plan your coding

[0058] 5-1. Plans a and plans bEncode each plan in the plan. The plan has a tree structure of (parent node, (left child tree), (right child tree)). Leaf nodes are scan nodes, and non-leaf nodes are connection nodes. The operation type, cost, and cardinality of each node are encoded. The encoding method of the node operation type is a single-bit effective encoding, and the cost and cardinality are encoded as standardized floating-point numbers. After encoding, the tree vector (parent node vector, (left child vector tree), (right child vector tree)) is obtained.

[0059] 5-2.plans a and plans b For all the leaf nodes in the plan, if the leaf node scans a table t with data constraints, then the leaf node vector is replaced by the following vector: The vector of this leaf node is input into a fully connected layer, and the table vector v corresponding to table t is replaced by t From V ts , input another fully connected layer, and the vectors output by the two fully connected layers are connected using concatenate();

[0060] 5-3. Plans can be obtained through step 5-2 a and table vector v t Plans after connection at [plan at1 , plan at2 ,...,plan atk ], and plans b Plans after connecting with table vector bt [plan bt1 , plan bt2 ,...,plan btk ];

[0061] Step 6: TCNN tree convolutional neural network predicts the execution time of the plan

[0062] 6-1.plans at [plan at1 , plan at2 ,...,plan atk ] Input the tree convolutional neural network and use Pytorch to implement the tree convolutional neural network structure, which goes through the following convolutional layers:

[0063] 6-1-1.BinaryTreeConv(10,256);

[0064] 6-1-2.BinaryTreeConv(256,128);

[0065] 6-1-3.BinaryTreeConv(256,128);

[0066] 6-1-4.BinaryTreeConv(128,64);

[0067] 6-1-5.Linear(64,32);

[0068] 6-1-6.Linear(32,1);

[0069] 6-2. Use argmin() to obtain the plan and cost estimate with the minimum cost estimate:

[0070] (plan a_best , lantency a_best );

[0071] 6-3.plans bt [plan bt1 , plan bt2 ,...,plan btk ] Input into the tree convolutional neural network and pass through the same convolutional layer;

[0072] 6-4. Use argmin() to obtain the plan and cost estimate with the minimum cost estimate:

[0073] (plan b_best , lantency b_best );

[0074] Step 7: Cost revision;

[0075] 7-1. Plan a_best , lantency a_best ) in the lantency a_best Replaced with lantency b_best , obtain (plan a_best , lantency b_best );

[0076] Step 8: Database Execution Module

[0077] 8-1. Plan a_best Send it to the database execution module to get the actual execution time lantency a_real , define the actual execution time as reward (Reward) and observe the reward.

[0078] Step 9: Experience Replay

[0079] 9-1. Experience to be gained (plan a_best, lantency b_best , lantency a_real ) is stored in the experience table;

[0080] 9-2. Usage experience (plan a_best , lantency b_best , lantency a_real )Training the tree convolutional neural network, the goal is formalized as a loss minimization problem, the loss loss is:

[0081] loss=∑(lantency a_real -lantency b_best ) 2 ;

[0082] The present invention is applicable to query statements containing data constraints, realizes query optimization to a certain extent, and designs a convolutional neural network structure that can replace the cost estimation module of the traditional query optimizer, selecting the optimal query plan from K candidate plans given by the traditional optimizer.

Claims

1. A database query optimization method based on data constraints, characterized in that Including the following steps: Step 1: Get the query statement; Input: connection diagram between database tables, data of all columns in each table; Output: query statement and optimized query statement (q a ,q b ); According to the connection diagram of the database, randomly select a table containing data constraints; randomly select a column in the table, randomly select a data in this column, and connect them using symbols to obtain the query predicate; Combine the selected tables, join relations, and query predicates into a query statement q a ; Step 2: Get the table code; Input: data from each table; Output: table vector V corresponding to n tables ts [v1,v2,...v n ]; Step 3: The query statement input parsing module parses it into a query tree; Step 4: The query tree is input into the traditional query optimizer; Step 5: Plan coding; Step 6: TCNN tree convolutional neural network predicts the execution time of the plan; Step 7: Cost correction; Step 8: Database execution module; Step 9: Experience replay.

2. The method for optimizing database query based on data constraints according to claim 1, characterized in that Step 1 is implemented as follows: 1-1. Randomly select several tables whose join relationships contain data constraints, extract all tables and join relationships from the join graph, randomly select several columns and certain data in the extracted tables, and then connect them using the join symbol to obtain predicates; connect these predicates using AND or OR to obtain a query statement in the following format: SELECT COUNT(*) FROM table WHERE join relationship AND predicate OR predicate; 1-2. At the end of two identical query statements, one adds the predicate before rewriting and the other adds the predicate after rewriting, and finally obtains the query statement q before rewriting a : SELECT COUNT(*) FROM table WHERE join relation AND predicate OR predicate AND rewrite the predicate before; and the query statement after rewriting q b : SELECT COUNT(*) FROM table WHERE join relation AND predicate OR predicate AND rewritten predicate.

3. The method for optimizing database query based on data constraints according to claim 2, characterized in that Step 2 is implemented as follows: 2-1. Use Word2Vec() to train the character type data in the table into a dictionary; 2-2. Encode each row of data in the table. Integer data uses standardized floating-point encoding, and character data encoding is obtained from the dictionary trained in step 2-1. 2-3. Use the weighted average method to normalize all rows into a row vector represented as v, and finally obtain the table vector V of n tables ts [v1,v2,...v n ].

4. The method for optimizing database query based on data constraints according to claim 2, characterized in that Step 3 is implemented as follows: 3-1. Change the query statement q a The input parsing module parses the query q a The corresponding query tree T a ; 3-2. Change the query statement q b The input parsing module parses the query q a The corresponding query tree T b .

5. The method for optimizing database query based on data constraints according to claim 2, characterized in that Step 4 is implemented as follows: 4-1. Get K query plans from the candidate plans given by the traditional optimizer, and specifically query tree T a Input the traditional query optimizer and get the query statement q a K plans a [plan a1 ,plan a2 ,...,plan ak ]; query tree T b Input the traditional query optimizer and get the query statement q b K query plans b [plan b1 ,plan b2 ,...,plan bk ].

6. The method for optimizing database query based on data constraints according to claim 2, characterized in that Step 5 is implemented as follows: 5-1. Plans a and plans b Code each plan in The plan is a tree structure with the structure (parent node, (left child tree), (right child tree)). Leaf nodes are scan nodes, and non-leaf nodes are connection nodes. The operation type, cost, and cardinality of each node are encoded. The encoding method of the node operation type is a single-bit effective encoding, and the cost and cardinality are encoded as standardized floating-point numbers. After encoding, the tree vector (parent node vector, (left child vector tree), (right child vector tree)) is obtained. 5-2.plans a and plans b For all leaf nodes in the plan, if the leaf node scans a table t containing data constraints, then the leaf node vector is replaced by the following vector: The vector of this leaf node is input into a fully connected layer, and the table vector v corresponding to table t is replaced by t From V ts , input another fully connected layer, and the vectors output by the two fully connected layers are connected using concatenate(); 5-3. Plans can be obtained through step 5-2 a and table vector v t Plans after connection at [plan at1 ,plan at2 ,...,plan atk ], and plans b Plans after connecting with table vector bt [plan bt1 ,plan bt2 ,...,plan btk ].

7. The method for optimizing database query based on data constraints according to claim 2, characterized in that Step 6 is implemented as follows: 6-1.plans at [plan at1 ,plan at2 ,...,plan atk ] Input the tree convolutional neural network and use Pytorch to implement the tree convolutional neural network structure, which goes through the following convolutional layers: 6-1-1.BinaryTreeConv(10,256); 6-1-2.BinaryTreeConv(256,128); 6-1-3.BinaryTreeConv(256,128); 6-1-4.BinaryTreeConv(128,64); 6-1-5.Linear(64,32); 6-1-6.Linear(32,1); 6-2. Use argmin() to obtain the plan and cost estimate with the minimum cost estimate: (plan a_best ,lantency a_best ); 6-3.plans bt [plan bt1 ,plan bt2 ,...,plan btk ] Input into the tree convolutional neural network and pass through the same convolutional layer; 6-4. Use argmin() to obtain the plan and cost estimate with the minimum cost estimate: (plan b_best ,lantency b_best )。 8. The method for optimizing database query based on data constraints according to claim 2, characterized in that Step 7 is implemented as follows: 7-1. Plan a_best ,lantency a_best ) in the lantency a_best Replaced with lantency b_best , obtain (plan a_best ,lantency b_best ).

9. The method for optimizing database query based on data constraints according to claim 2, characterized in that Step 8 is implemented as follows: 8-1. Plan a_best Send it to the database execution module to get the actual execution time lantency a_real , define the actual execution time as reward (Reward) and observe the reward.

10. The method for optimizing database query based on data constraints according to claim 2, characterized in that Step 9 is implemented as follows: 9-1. Experience to be gained (plan a_best ,lantency b_best ,lantency a_real ) is stored in the experience table; 9-2. Usage experience (plan a_best ,lantency b_best ,lantency a_real )Training the tree convolutional neural network, the goal is formalized as a loss minimization problem, the loss loss is: loss=∑(lantency a_real -lantency b_best ) 2 。

Images