A database query optimization method

Abstract

The present invention discloses a database query optimization method, which includes designing a physical optimization space to obtain multiple groups of query instructions; generating multiple groups of execution plans for query statements according to the multiple groups of query instructions, encoding the multiple groups of execution plans into multiple groups of feature vector sets; designing a prediction model according to the multiple groups of feature vector sets of the multiple groups of execution plans to obtain multiple estimated execution times of the multiple groups of execution plans; designing a sampling method according to the multiple estimated execution times of the multiple groups of execution plans to select a group of execution plans; sending the selected execution plan to an executor to obtain the actual execution time, and then using the feature vector set, the estimated execution time and the actual execution time of the selected execution plan as data samples to train the prediction model, thereby finally improving the query performance; the present invention solves the problem that a traditional query optimizer generates a poor execution plan, which finally leads to poor query performance.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a database query optimization method. Background Art

[0002] In the database field, the query optimizer is one of the most important parts and is crucial for users to obtain good query performance. The query optimizer includes two aspects: logical optimization and physical optimization. First, the user's query statement is generated by the syntax analyzer to generate a query tree. Then the query tree is passed to the query optimizer, and a logical plan is generated through logical optimization. Finally, the logical plan is physically optimized to generate an execution plan. Therefore, the goal of the query optimizer is to obtain a set of optimal execution plans to achieve good query performance. For query statements containing multi-table joins, traditional query optimizers usually use dynamic programming algorithms to determine the connection path, but they require a huge optimization space, which leads to a long optimization time. Although the commonly used genetic algorithm can reduce the optimization time, it is easy to produce poor execution plans, which in turn affects query performance.

[0003] Furthermore, in physical optimization, traditional query optimizers optimize based on cost models, which rely on cardinality estimation, which is usually based on rules such as independence and uniformity. Since real-world data is highly correlated, cardinality estimation errors often occur, causing the query optimizer to select poor scan paths and join paths, thereby generating suboptimal or poor execution plans and reducing query performance. Summary of the invention

[0004] In view of the above-mentioned deficiencies in the prior art, the present invention provides a database query optimization method.

[0005] In order to achieve the above-mentioned object of the invention, the technical solution adopted by the present invention is:

[0006] A database query optimization method comprises the following steps:

[0007] S1. Build a physical optimization space and obtain multiple sets of query commands;

[0008] S2. generating, according to the obtained multiple groups of query commands, multiple groups of execution plans for the query statements in the multiple groups of query commands using a query optimizer;

[0009] S3, respectively encoding the multiple groups of execution plans into multiple groups of feature vector sets, constructing a prediction model based on the obtained multiple groups of feature vector sets, and obtaining the estimated execution time of each group of execution plans through the model;

[0010] S4, select a group of execution plans and send them to the executor to calculate the actual execution time, and use the feature vector set, estimated execution time and actual execution time of the group of execution plans as data samples to train the prediction model;

[0011] S5. Use the trained prediction model to predict the estimated execution time of each of the multiple groups of execution plans, and select the execution plan with the smallest estimated execution time to send to the executor for execution.

[0012] The beneficial effects of the above scheme are as follows: first, multiple groups of query commands are provided, and the query optimizer does not have to generate too many execution plans, thus saving optimization time; then, the execution plan is generated only based on coarse-grained query commands, which can reduce the physical optimization space, and will not interfere too much with the query optimizer, making it easier to integrate with commercial databases; finally, a prediction model is designed to select an execution plan in combination with a sampling method, thereby providing a comprehensive data sample, so that the training effect of the prediction model can be better performed, and the trained prediction model can provide more accurate predictions, so that the query optimizer can select the best execution plan. The present invention will not replace the components of the traditional query optimizer, nor will it abandon the classic ideas formed by the traditional query optimizer over the years, but will serve as a top-level intelligent technology to assist the underlying query optimizer to better select the best execution plan, thereby avoiding the defect that the traditional query optimizer may select a suboptimal execution plan, and will not interfere too much with the traditional query optimizer, making it easier to achieve integration with commercial databases, and ultimately improving query performance.

[0013] Furthermore, the S1 specifically includes the following steps:

[0014] S11, listing scanning paths and connection paths in the physical optimization space;

[0015] S12, merging the scan path and the connection path to obtain multiple groups of query commands;

[0016] Among them, the scanning path includes sequential scanning, index scanning, and bitmap scanning; the connection path includes hash connection, merge connection, and loop nested connection.

[0017] Furthermore, the S2 specifically includes the following steps:

[0018] S21, multiple groups of query commands are input into the query optimizer;

[0019] S22, using the query optimizer to prohibit scan paths and connection paths not included in the query command, to obtain multiple restricted physical optimization spaces;

[0020] S23. The query optimizer generates multiple groups of execution plans according to multiple restricted physical optimization spaces.

[0021] The beneficial effect of the above further solution is that the query optimizer does not use the scan path and connection path not included in the query command, which reduces the physical optimization space for generating the execution plan and saves optimization time. In addition, the execution plan is generated only according to the restricted physical optimization space, which does not interfere too much with the query optimizer's generation of the execution plan, so that the query optimizer has more autonomy, and is easier to integrate into the traditional query optimizer and easier to combine with commercial databases. This is also an engineering optimization.

[0022] Furthermore, the scanning path includes sequential scanning, index scanning, and bitmap scanning; the connection path includes hash connection, merge connection, and loop nested connection.

[0023] The beneficial effect of the above further scheme is: the scan path and the connection path are combined to obtain a limited set of query commands, each set of query commands will correspond to a set of execution plans generated in subsequent steps, limiting the number of execution plans to be selected in subsequent steps, and through the intervention of a limited set of query commands, the query optimizer does not need to generate too many execution plans, saving optimization time.

[0024] Furthermore, the S3 specifically includes:

[0025] S31, converting the multiple groups of execution plans into multiple groups of node sequence sets, performing in-order traversal on the execution plans, and storing the nodes into the node sequence sets in the traversal order;

[0026] S32. Construct a coding scheme according to the execution plan, and encode multiple groups of node sequence sets into multiple groups of feature vector sets.

[0027] S33. Construct a prediction model based on multiple groups of feature vector sets and use the model to predict the estimated execution time of each execution plan of the multiple groups of execution plans.

[0028] Furthermore, the coding scheme in S32 is:

[0029] Each node in the execution plan is encoded as a feature vector, where each node includes a scan path, a join path, a cardinality value, and a cost value. The scan path and the join path are encoded using one-hot encoding, and the cardinality value and the cost value are obtained by the query optimizer.

[0030] The beneficial effects of the above further scheme are: encoding the key information of the node to provide an accurate feature vector for the subsequent steps. And converting the execution plan with a binary tree structure into a node sequence set, and then obtaining a feature vector set, providing a suitable input form for the subsequent steps, so that the subsequent steps reduce the process of processing data.

[0031] Furthermore, the prediction model constructed in S33 is expressed as:

[0032] A m =sigmod(x m U A +y m-1 I A )

[0033] B m =sigmod(x m U B +y m-1 I B )

[0034] C m =tanh(x m U C +B m ⊙y m-1 I C )

[0035] y m =y m-1 ⊙A m +C m ⊙(1-A m )

[0036] t=sigmod(y n P)

[0037] where x m belongs to x1,x2,…,x n , and 1 <m<n,y m-1 is the feature vector containing the key information of nodes 1, 2, ..., m-1, sigmod() and tanh() are activation functions, A m is the degree of information retention between the feature vector of node m and the feature vector of node m-1, U A and I A is the weight parameter matrix with retention effect, B m is the degree of information loss between the feature vector of node m and the feature vector of node m-1, U B and I B is the weight parameter matrix with loss effect, C m is the degree of information fusion between the feature vector of node m and the feature vector of node m-1, U C and I C is a weight parameter matrix with a simple linear transformation effect, ⊙ is the Hadamard product, which is the multiplication of the elements at corresponding positions of two vectors, y m is a high-level feature vector containing key information of nodes 1, 2, …, m, y nis a feature vector containing the key information of nodes 1, 2, …, n, P is a weight parameter column with a simple linear transformation effect, and t is the estimated execution time of the execution plan.

[0038] The beneficial effects of the above further scheme are: the prediction model can accurately capture the key features of the execution plan, such as the overall structural information of the execution plan and the information of each node of the execution plan, and can show good results in predicting the execution time of the execution plan, providing accurate estimated execution time for subsequent steps.

[0039] Furthermore, the method of selecting a set of execution plans in S4 is specifically as follows:

[0040] S41, sorting multiple estimated execution times of multiple groups of execution plans;

[0041] S42. According to the sorting result of step S41, select one of the groups of execution plans based on a sampling method, wherein the sampling method is set to: randomly select an execution plan with a probability of γ, and select an execution plan with the minimum estimated execution time with a probability of 1-γ, where 0<γ<1.

[0042] The beneficial effect of the above further scheme is that the designed sampling method can not only utilize existing knowledge, such as the sorting results of multiple estimated execution times, to select the best execution plan, but also explore the execution plan with the smallest actual execution time. For the execution plan with the smallest estimated execution time, it cannot be completely guaranteed that the actual execution time of this execution plan is also the smallest. Therefore, setting an appropriate γ balances the degree of utilization and exploration, providing a very comprehensive data sample for subsequent steps.

[0043] Furthermore, the method of training the prediction model in S4 is:

[0044] S41. The loss function of the prediction model is constructed based on the data sample, which is expressed as:

[0045] L loss =(Tt) 2

[0046] Where L loss is the loss function, T is the actual execution time, and t is the estimated execution time;

[0047] S42. According to the loss function of step S41, the prediction model is trained by back propagation of the gradient of the prediction model.

[0048] The beneficial effects of the above further solution are: unlike standard supervised learning, data samples are obtained through sampling, which can improve the training effect of the prediction model. In addition, the trained prediction model can more accurately predict the execution time of the execution plan, and then directly select the execution plan with the smallest estimated time and send it to the executor, thereby avoiding the query optimizer from selecting a suboptimal execution plan according to the original method, and ultimately improving query performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] Figure 1 The present invention is a flowchart of a method for optimizing database query.

[0050] Figure 2 Schematic diagram of execution plan E1 and coding scheme of an embodiment of the present invention.

[0051] Figure 3 Schematic diagram of a node sequence set for executing plan E1 according to an embodiment of the present invention.

[0052] Figure 4 Schematic diagram of the structure of the feature vector set V1 of the execution plan E1 according to an embodiment of the present invention.

[0053] Figure 5 This is a diagram of the prediction model architecture of an embodiment of the present invention.

[0054] Figure 6 Schematic diagram of a query statement according to an embodiment of the present invention. DETAILED DESCRIPTION

[0055] The specific implementation modes of the present invention are described below so that those skilled in the art can understand the present invention. However, it should be clear that the present invention is not limited to the scope of the specific implementation modes. For those of ordinary skill in the art, as long as various changes are within the spirit and scope of the present invention as defined and determined by the attached claims, these changes are obvious, and all inventions and creations utilizing the concept of the present invention are protected.

[0056] A database query optimization method, such as Figure 1 As shown, the following steps are included:

[0057] S1. Build a physical optimization space and obtain multiple sets of query commands;

[0058] In this embodiment, three scanning paths and three connection paths are combined to obtain 26 groups of query commands, that is, step S1 includes the following sub-steps:

[0059] S11, listing scanning paths and connection paths in the physical optimization space;

[0060] Commonly used scanning paths in step S11 include sequential scanning, index scanning, and bitmap scanning; and connection paths include hash connection, merge connection, and loop nested connection.

[0061] S12, merging the scan path and the connection path to obtain multiple groups of query commands;

[0062] In step S12, the scan path and the connection path are combined to obtain multiple groups of query commands, wherein the first group is (sequential scan, index scan, bitmap scan, hash join, merge join, loop nested join); the second group is (sequential scan, index scan, bitmap scan, hash join, merge join); the third group is (sequential scan, bitmap scan, hash join, loop nested join); the fourth group is (sequential scan, bitmap scan, hash join); the fifth group is (sequential scan, index scan, bitmap scan, hash join, loop nested join); The 6th group is (bitmap scan, hash join, merge join, loop nested join); the 7th group is (index scan, hash join, merge join, loop nested join); the 8th group is (bitmap scan, merge join, loop nested join); the 9th group is (bitmap scan, hash join); the 10th group is (index scan, bitmap scan, hash join, loop nested join); the 11th group is (sequential scan, index scan, bitmap scan, hash join); the 12th group is (sequential scan, bitmap scan, hash join, merge join, loop nested 14th group is (index scan, hash join, loop nested join); 15th group is (index scan, loop nested join); 16th group is (sequential scan, index scan, merge join, loop nested join); 17th group is (index scan, bitmap scan, loop nested join); 18th group is (index scan, bitmap scan, hash join, merge join, loop nested join); 19th group is (index scan, merge join, loop nested join); The 20th group is (sequential scan, bitmap scan, merge join, loop nested join); the 21st group is (sequential scan, index scan, bitmap scan, loop nested join); the 22nd group is (index scan, bitmap scan, hash join, merge join); the 23rd group is (bitmap scan, hash join, merge join); the 24th group is (sequential scan, index scan, hash join, loop nested join); the 25th group is (index scan, hash join); the 26th group is (bitmap scan, hash join, loop nested join).

[0063] S2. generating, according to the obtained multiple groups of query commands, multiple groups of execution plans for the query statements in the multiple groups of query commands using a query optimizer;

[0064] It will be built on the Postgresql database to assist the Postgresql query optimizer in better selecting execution plans. The workload benchmark is the Join Order Benchmark, which contains 21 tables, 113 queries, and a data volume of 3.6GB. For ease of explanation, Figure 6 A simplified query statement in the Join Order Benchmark is given in Figure 1. Based on 26 sets of query instructions, the query optimizer of Postgresql is Figure 6 The query statement generates 26 sets of execution plans.

[0065] The S2 specifically includes the following steps:

[0066] S21, multiple groups of query commands are input into the query optimizer;

[0067] S22, using the query optimizer to prohibit scan paths and connection paths not included in the query command, to obtain multiple restricted physical optimization spaces;

[0068] S23. The query optimizer generates multiple groups of execution plans according to multiple restricted physical optimization spaces.

[0069] S3, respectively encoding the multiple groups of execution plans into multiple groups of feature vector sets, constructing a prediction model based on the obtained multiple groups of feature vector sets, and obtaining the estimated execution time of each group of execution plans through the model;

[0070] In this embodiment, the query optimizer of Postgresql is Figure 6 The query statement generates execution plan E1 to execution plan E 26 , a total of 26 execution plans.

[0071] S31, converting the multiple groups of execution plans into multiple groups of node sequence sets, performing in-order traversal on the execution plans, and storing the nodes into the node sequence sets in the traversal order;

[0072] In this embodiment, the structure of the execution plan is a binary tree structure, and the encoding scheme is defined as encoding each node in the execution plan into a feature vector (with a dimension of 8 bits), wherein each node is composed of a scan path, a connection path, a cardinality value, and a cost value. The scan path and the connection path are encoded using one-hot encoding (with a dimension of 6 bits), and the cardinality value and the cost value are provided by the query optimizer (with a dimension of 2 bits). In this embodiment, a feature vector set V1 of an execution plan E1 is given as an example, such as Figure 3 As shown. And execute plan E2,…,E 26 Corresponding feature vector set V2,…,V 26 .

[0073] S32. Construct a coding scheme according to the execution plan, and encode multiple groups of node sequence sets into multiple groups of feature vector sets.

[0074] In this embodiment, the encoding scheme is as follows Figure 2 As shown, each node in the execution plan is encoded as a feature vector, where each node includes a scan path, a join path, a cardinality value, and a cost value. The scan path and the join path are encoded using one-hot, and the cardinality value and the cost value are obtained by the query optimizer.

[0075] S33. Construct a prediction model based on multiple groups of feature vector sets and use the model to predict the estimated execution time of each execution plan of the multiple groups of execution plans.

[0076] In this embodiment, the architecture of the prediction model is as follows Figure 5 As shown, the execution plan E1,…,E 26 The set of feature vectors V1,…,V 26 , respectively, as the input of the prediction model to obtain the corresponding estimated execution time t1,…,t 26 .

[0077] The input of the prediction model is defined as the feature vector set of the execution plan (x1, x2, ..., x n ), where x1,x2,…,x n is the feature vector, n is the number of nodes in the execution plan; the output of the prediction model is defined as the estimated execution time t of the execution plan, and then the prediction model is constructed. The calculation formula is as follows:

[0078] A m =sigmod(x m U A +y m-1 I A )

[0079] B m =sigmod(x m U B +y m-1 I B )

[0080] C m =tanh(x m U C +B m ⊙y m-1 I C )

[0081] y m =y m-1 ⊙A m +C m ⊙(1-A m )

[0082] t=sigmod(y n P)

[0083] where x m belongs to x1,x2,…,x n , and 1 <m<n,y m-1 is the feature vector containing the key information of nodes 1, 2, ..., m-1, sigmod() and tanh() are activation functions, A m is the degree of information retention between the feature vector of node m and the feature vector of node m-1, U A and I A is the weight parameter matrix with retention effect, B m is the degree of information loss between the feature vector of node m and the feature vector of node m-1, U B and I B is the weight parameter matrix with loss effect, C m is the degree of information fusion between the feature vector of node m and the feature vector of node m-1, U C and I C is a weight parameter matrix with a simple linear transformation effect, ⊙ is the Hadamard product, which is the multiplication of the elements at corresponding positions of two vectors, y m is a high-level feature vector containing key information of nodes 1, 2, …, m, y n is a feature vector containing key information of nodes 1, 2, …, n, P is a weight parameter column with a simple linear transformation effect, and t is the estimated execution time of the execution plan.

[0084] In this embodiment, step S42 takes a set of feature vectors V1 of execution plan E1 as an example to illustrate, and V1 is used as the prediction model input. Figure 4 The first vector in the feature vector set is taken as x1, the second feature vector is taken as x2, and so on, which conforms to the input format of the prediction model. Then, after being processed by the prediction model, the estimated execution time t1 of the execution plan E1 is output. Similarly, the execution plans E2,…,E 26 Corresponding estimated execution time t2,…,t 26 .

[0085] S4, select a group of execution plans and send them to the executor to calculate the actual execution time, and use the feature vector set, estimated execution time and actual execution time of the group of execution plans as data samples to train the prediction model;

[0086] Specifically,

[0087] S41, sorting multiple estimated execution times of multiple groups of execution plans;

[0088] In this embodiment, plans E2, ..., E 26 The estimated execution time t1,…,t 26 Sort and select a set of execution plans according to the sampling method. For the convenience of subsequent steps, assume that the sorting result is the estimated execution time t1 <t2<,…,<t 26 .

[0089] S42. According to the sorting result of step S41, select one of the execution plans based on a sampling method, wherein the sampling method is set to: randomly select an execution plan with a probability of γ, and select an execution plan with the minimum estimated execution time with a probability of 1-γ, where 0<γ<1.

[0090] Assume that the execution plan E1 corresponding to the estimated execution time t1 is selected with a probability of 1-γ. The actual situation is different. It is possible to randomly select a set of execution plans with a probability of γ.

[0091] In this embodiment, the method of training the prediction model is:

[0092] The selected execution plan is sent to the executor to obtain the actual execution time T;

[0093] The feature vector set, estimated execution time t, and actual execution time T of the selected execution plan are used as data samples.

[0094] The feature vector set V1, estimated execution time t1 and actual execution time T1 of the execution plan E1 are used as data samples to provide a set of training data for subsequent steps.

[0095] S41. The loss function of the prediction model is constructed based on the data sample, which is expressed as:

[0096] L loss =(Tt) 2

[0097] Where L loss is the loss function, T is the actual execution time, and t is the estimated execution time;

[0098] S42. According to the loss function of step S41, the prediction model is trained by back propagation of the gradient of the prediction model.

[0099] S5. Use the trained prediction model to predict the estimated execution time of each of the multiple groups of execution plans, and select the execution plan with the smallest estimated execution time to send to the executor for execution.

[0100] Based on the trained prediction model, multiple estimated execution times of multiple groups of execution plans can be accurately predicted, so that the query optimizer only selects one group of execution plans with the shortest estimated execution time and sends it to the executor, ultimately improving query performance.

[0101] In this embodiment, the query statements in the Join Order Benchmark are divided into a training set and a test set. Figure 6 The query statements shown are from the training set. The prediction model is trained multiple times based on the training set. The trained prediction model can more accurately predict the execution time for multiple groups of execution plans of query statements in the test set, thereby enabling the query optimizer to select only one group of execution plans with the shortest estimated execution time and send them to the executor, thereby ultimately improving query performance.

[0102] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0103] These computer program instructions may also be stored in a computer readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture including an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0104] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process in the computer or other programmable device. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0105] The present invention uses specific embodiments to illustrate the principles and implementation methods of the present invention. The description of the above embodiments is only used to help understand the method of the present invention and its core idea. At the same time, for those skilled in the art, according to the idea of ​​the present invention, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on the present invention.

[0106] Those skilled in the art will appreciate that the embodiments described herein are intended to help readers understand the principles of the present invention, and should be understood that the protection scope of the present invention is not limited to such specific statements and embodiments. Those skilled in the art can make various other specific variations and combinations that do not deviate from the essence of the present invention based on the technical revelations disclosed by the present invention, and these variations and combinations are still within the protection scope of the present invention.

Claims

1. A database query optimization method, characterized in that: The steps include: S1. Build a physical optimization space and obtain multiple sets of query commands; S2. generating, according to the obtained multiple groups of query commands, multiple groups of execution plans for the query statements in the multiple groups of query commands using a query optimizer; S3. Encode the multiple groups of execution plans into multiple groups of feature vector sets respectively, build a prediction model based on the obtained multiple groups of feature vector sets, and obtain the estimated execution time of each group of execution plans through the model, specifically including: S31, converting the multiple groups of execution plans into multiple groups of node sequence sets, performing in-order traversal on the execution plans, and storing the nodes into the node sequence sets in the traversal order; S32, constructing a coding scheme according to the execution plan, and encoding the multiple sets of node sequences into multiple sets of feature vectors, wherein the coding scheme is: encoding each node in the execution plan into a feature vector, wherein each node includes a scan path, a connection path, a cardinality value, and a cost value, the scan path and the connection path are encoded using one-hot, and the cardinality value and the cost value are obtained by the query optimizer; S33, construct a prediction model based on multiple sets of feature vector sets and use the model to predict the estimated execution time of each execution plan in the multiple sets of execution plans. The prediction model is expressed as: A m =sigmod(x m U A + y m-1 I A ) B m =sigmod(x m U B + y m-1 I B ) C m =tanh(x m U C +B m ⊙ y m-1 I C ) y m = y m-1 ⊙ A m + C m ⊙ ( 1 -A m ) t = sigmod(y n P) in, x m belong x 1, x 2, …, x n , and 1< m < n , y m-1 is a node containing nodes 1, 2, …, m -1 is the key information feature vector, sigmod ()and tanh () is the activation function, A m For Node m The eigenvectors and nodes of m The degree of information preservation of the eigenvector of -1, U A and I A is the weight parameter matrix with retention effect, B m For Node m The eigenvectors and nodes of m The degree of information loss of the eigenvector of -1, U B and I B is the weight parameter matrix with loss effect, C m For Node m The eigenvectors and nodes of m -1’s eigenvector information fusion degree, U C and I C is a weight parameter matrix with a simple linear transformation effect, ⊙ is the Hadamard product, and the Hadamard product is the multiplication of the elements at corresponding positions of two vectors. y m is a node containing nodes 1, 2, …, m The high-level feature vector of key information, y n is a node containing nodes 1, 2, …, n The feature vector of the key information, P is a weight parameter sequence with a simple linear transformation effect, t The estimated time for the execution of the plan; S4, select a group of execution plans and send them to the executor to calculate the actual execution time, and use the feature vector set, estimated execution time and actual execution time of the group of execution plans as data samples to train the prediction model; S5. Use the trained prediction model to predict the estimated execution time of each group of execution plans in the multiple groups of execution plans, and select the execution plan with the smallest estimated execution time to send to the executor for execution.

2. A database query optimization method according to claim 1, characterized in that: The S1 specifically includes the following steps: S11, listing scanning paths and connection paths in the physical optimization space; S12: Merge the scan path and the connection path to obtain multiple groups of query commands.

3. A database query optimization method according to claim 2, characterized in that: The S2 specifically includes the following steps: S21, inputting multiple groups of query commands into a query optimizer; S22, using the query optimizer to prohibit query commands that do not include scan paths and connection paths, to obtain multiple restricted physical optimization spaces; S23. The query optimizer generates multiple groups of execution plans according to multiple restricted physical optimization spaces.

4. A database query optimization method according to claim 3, characterized in that: The scanning path includes sequential scanning, index scanning, and bitmap scanning; the connection path includes hash connection, merge connection, and loop nested connection.

5. A database query optimization method according to claim 1, characterized in that: The specific method of selecting a set of execution plans in S4 is: S41, sorting multiple estimated execution times of multiple groups of execution plans; S42. According to the sorting result of step S41, one set of execution plans is selected based on a sampling method, wherein the sampling method is set to: γ The probability of randomly selecting an execution plan is 1- γ The probability of selecting an execution plan with the smallest estimated execution time, where 0< γ <1.

6. A database query optimization method according to claim 5, characterized in that: The method of training the prediction model in S4 is: S41. The loss function of the prediction model is constructed based on the data sample, which is expressed as: L loss =( T - t ) 2 in L loss is the loss function, T is the actual execution time, t is the estimated execution time; S42. According to the loss function of step S41, the prediction model is trained by back propagation of the gradient of the prediction model.

Images