CN116150190A - Database query optimization processing method and system based on tree QRNN - Google Patents

Abstract

The invention discloses a tree-type QRNN-based database query optimization processing method and system; wherein the method includes: obtaining an input structured query statement; determining the execution plan corresponding to the structured query statement, and obtaining the tree structure of the execution plan Structure, each node in the execution plan tree structure includes tables, operation types and/or predicate conditions; convert the information corresponding to each node in the execution plan tree structure into feature vectors; build and train the query optimizer model; query When the optimizer model is trained, the historical execution plan is used as sample data, and the actual query cost corresponding to the historical execution plan is used as the target value for training; the feature vector of each node in the execution plan tree structure is input into the query optimization The query cost output by the multi-layer perceptron is obtained from the corresponding nodes of the tree-type quasi-cyclic neural network of the machine model, and the target execution plan is determined based on the query cost.

Description

Database query optimization processing method and system based on tree QRNN

technical field

The invention relates to the technical field of database query optimization, in particular to a tree QRNN-based database query optimization processing method and system.

Background technique

The statements in this section merely mention the background technology related to the present invention and do not necessarily constitute the prior art.

Basic software is an important foundation and support for the development of the information industry and the construction of informatization, and the core basic software is increasingly valued by enterprises. Among them, the database is one of the important components of the basic software. The application fields of the database are very extensive, and many companies need to use the database to store data information. The query optimizer in the database can determine the execution performance of the database. The cost estimation is very important to the query optimizer, it can guide the selection of the execution plan, and select an efficient execution plan for the query statement. Moreover, with the advent of the era of big data, the data has grown in a large amount. Therefore, it is of great practical significance to establish a reasonable cost estimation model for the query optimizer that can continuously process new data.

With the continuous development of databases in recent years, databases have begun to combine with various emerging technologies, such as artificial intelligence, blockchain, and dense computing. The combination of database and artificial intelligence is a win-win situation, and both database and artificial intelligence can benefit from this combination. Due to the heuristic-based nature of query optimization, there have been many attempts to apply deep learning to cost estimation in query optimizers. For example, heterogeneous modeling is performed on the tree structure of the execution plan, and a linear method is used to capture the characteristic information of the execution plan. However, this type of method does not encode the predicates in the execution plan, and relies on the estimated cardinality and estimated cost of the relational database, which eventually leads to errors in prediction, and the linear method cannot accurately capture the information of complex execution plans. . At the same time, another part of the research is to use a variant of the cyclic neural network, the long-term short-term memory network (LSTM), to model the nodes in the execution plan, which can solve the problems of gradient disappearance and gradient explosion, but such methods are computationally intensive. , and cannot be calculated in parallel, making training difficult. In addition, some researchers use the Transformer model to capture the information of the nodes in the execution plan and use the tree partial attention mechanism to capture the tree structure characteristics of the execution plan.

Although the above-mentioned methods have achieved certain results, they have not fully considered the factors affecting the cost estimation in the query optimizer; secondly, most of them only consider the situation under static data, ignoring the increase in real-world scenarios. The impact of quantitative data on cost estimation models.

Contents of the invention

In order to solve the deficiencies of the prior art, the present invention provides a tree-type QRNN-based database query optimization processing method and system; the cost is estimated according to the existing execution plan and statistical information, and the incremental learning method is also used to continuously learn new Data, so that the database can better select the execution plan to execute according to the cost estimate, and then achieve the purpose of improving the performance of the database to execute query statements.

In the first aspect, the present invention provides a tree-type QRNN-based database query optimization processing method;

A tree-type QRNN-based database query optimization processing method, including:

Obtain the input structured query statement;

Determine the execution plan corresponding to the structured query statement, obtain the tree structure of the execution plan, each node in the tree structure of the execution plan includes a table, operation type and/or predicate condition; each node in the tree structure of the execution plan The information corresponding to the node is converted into a feature vector;

Build and train the query optimizer model; The query optimizer model includes: a tree-type quasi-cyclic neural network and a multi-layer perceptron; a tree-type quasi-cyclic neural network is a tree structure, and a tree-type quasi-cycle neural network has a tree structure There is a one-to-one correspondence between the nodes in the tree and the nodes in the tree structure of the execution plan; each node of the tree-type quasi-recurrent neural network is realized by a quasi-recurrent neural network, and the root of the tree-like structure of the tree-type quasi-recurrent neural network The nodes are connected to the multi-layer perceptron; when the query optimizer model is trained, the historical execution plan is used as sample data, and the actual query cost corresponding to the historical execution plan is used as the target value for training;

The feature vector of each node in the execution plan tree structure is input into the corresponding node of the tree quasi-cyclic neural network of the query optimizer model, and the query cost output by the multi-layer perceptron is obtained, and the target execution plan is determined based on the query cost.

In a second aspect, the present invention provides a tree-type QRNN-based database query optimization processing system;

Database query optimization processing system based on tree QRNN, including:

An acquisition module configured to: acquire an input structured query statement;

A determining module configured to: determine the execution plan corresponding to the structured query statement, obtain a tree structure of the execution plan, each node in the tree structure of the execution plan includes a table, an operation type and/or a predicate condition; The information corresponding to each node in the execution plan tree structure is converted into a feature vector;

The processing module is configured to: build and train a query optimizer model; the query optimizer model includes: a tree-type quasi-cyclic neural network and a multi-layer perceptron; the tree-type quasi-cyclic neural network is a tree structure, and the tree type There is a one-to-one correspondence between the nodes in the tree structure of the quasi-cyclic neural network and the nodes in the tree structure of the execution plan; The root node of the tree structure of the neural network is connected to the multi-layer perceptron; when the query optimizer model is trained, the historical execution plan is used as the sample data, and the actual query cost corresponding to the historical execution plan is used as the target value for training;

The output module is configured to: input the feature vector of each node in the execution plan tree structure to the corresponding node of the tree quasi-cyclic neural network of the query optimizer model, and obtain the query cost output by the multi-layer perceptron, based on The query cost determines the target execution plan.

In a third aspect, the present invention also provides an electronic device, comprising:

memory for non-transitory storage of computer readable instructions; and

a processor for executing said computer readable instructions,

Wherein, when the computer-readable instructions are executed by the processor, the method described in the first aspect above is performed.

In a fourth aspect, the present invention also provides a storage medium that non-transitorily stores computer-readable instructions, wherein when the non-transitory computer-readable instructions are executed by a computer, the instructions of the method described in the first aspect are executed.

In a fifth aspect, the present invention also provides a computer program product, including a computer program, which is used to implement the method described in the first aspect when running on one or more processors.

Compared with prior art, the beneficial effect of the present invention is:

First, the histogram and the most frequent value are extracted from the statistical information of the traditional database, and the linear difference method is used to redistribute the horizontal axis value of the histogram, and then the histogram and the most frequent value information are encoded by the predicate information, which can Take advantage of database statistics.

Then, the tree-type quasi-cyclic neural network is used to model the execution plan. The model supports parallel computing and can capture long-term dependencies.

Finally, the method uses incremental learning to process the incremental data and further iteratively update the model weights, making the model more accurate in the case of dynamic data.

Description of drawings

The accompanying drawings constituting a part of the present invention are used to provide a further understanding of the present invention, and the schematic embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute improper limitations to the present invention.

Fig. 1 is a model frame diagram of Embodiment 1 of the present invention;

Fig. 2 is an example diagram of the principle of a tree-type quasi-cyclic neural network unit in Embodiment 1 of the present invention;

FIG. 3 is an example diagram of the principle of incremental learning in Embodiment 1 of the present invention;

FIG. 4 is a schematic diagram of histogram encoding in Embodiment 1 of the present invention;

FIG. 5 is a schematic diagram of the one-to-one correspondence between two tree structures in Embodiment 1 of the present invention.

Detailed ways

It should be noted that the following detailed description is exemplary and intended to provide further explanation of the present invention. Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs.

It should be noted that the terminology used here is only for describing specific embodiments, and is not intended to limit exemplary embodiments according to the present invention. As used herein, unless the context clearly dictates otherwise, the singular is intended to include the plural, and it should also be understood that the terms "comprising" and "having" and any variations thereof are intended to cover a non-exclusive Comprising, for example, a process, method, system, product, or device comprising a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include steps or units not explicitly listed or for these processes, methods, Other steps or units inherent in a product or equipment.

In the case of no conflict, the embodiments and the features in the embodiments of the present invention can be combined with each other.

The acquisition of all data in this embodiment is based on compliance with laws and regulations and user consent, and the legal application of data.

Existing deep learning cost estimation methods based on execution plans do not fully consider the influencing factors in cost estimation, and do not increase the parallelism of the model to reduce the training time while ensuring accurate modeling. In addition, the iterative update of model weights under incremental data is not considered.

Embodiment one

This embodiment provides a tree-type QRNN-based database query optimization processing method;

As shown in Figure 1, the tree-type QRNN-based database query optimization processing method includes:

S101: Obtain an input structured query statement;

S102: Determine the execution plan corresponding to the structured query statement, obtain the tree structure of the execution plan, each node in the tree structure of the execution plan includes a table, operation type and/or predicate condition; The information corresponding to each node is converted into a feature vector;

S103: Construct and train a query optimizer model; the query optimizer model includes: a tree-type quasi-cyclic neural network and a multi-layer perceptron; a tree-type quasi-cyclic neural network is a tree structure, a tree of a tree-type quasi-cyclic neural network There is a one-to-one correspondence between the nodes in the tree-like structure and the nodes in the tree-like structure of the execution plan; each node of the tree-type quasi-cyclic neural network is realized by a quasi-cyclic neural network, and the tree-like structure of the tree-type quasi-cyclic neural network The root node of is connected to the multi-layer perceptron; when the query optimizer model is trained, the historical execution plan is used as sample data, and the actual query cost corresponding to the historical execution plan is used as the target value for training;

S104: Input the eigenvector of each node in the execution plan tree structure to the corresponding node of the tree-type quasi-cyclic neural network of the query optimizer model, obtain the query cost output by the multi-layer perceptron, and determine the target execution plan based on the query cost .

It should be understood that S101: Obtain an input structured query statement, where the structured query statement is a database query and programming language used for accessing data and querying, updating and managing data stored in a relational database.

Further, the S102: determine the execution plan corresponding to the structured query statement, specifically including:

Use explain analyze SQL to query the execution plan of the structured query statement SQL in the PostgreSQL database.

It should be understood that in the process of query planning path of PostgreSQL database, different execution schemes of query requests are expressed by establishing different paths. Convert it into an execution plan and pass it to the executor for execution.

Further, each node in the execution plan tree structure includes a table, an operation type and/or a predicate condition, wherein each node in the execution plan tree structure includes: a table and an operation type, and the execution plan tree structure Each node in the structure allows predicate conditions to be included or not.

Further, each node in the execution plan tree structure refers to a node on the execution path in the execution plan.

Further, obtain the tree structure of the execution plan, including:

If there is a parent-child connection relationship between the current node and the adjacent node, then there is a connection relationship between the current node and the node;

If there is no parent-child connection relationship between the current node and the adjacent node, then there is no connection relationship between the current node and the adjacent node.

Further, said S102: converting the information corresponding to each node in the execution plan tree structure into a feature vector, specifically including:

S102-1: Perform feature encoding on tables, operation types and predicate conditions in each node in the tree structure of the execution plan;

S102-2: Perform feature encoding on the histogram and mode values in the statistical information of the queried database;

S102-3: Concatenate all feature encoding results together to obtain a feature vector.

Preferably, the operation type refers to: the operation type of the current node in the execution plan tree structure, and the operation type includes: full table scan (Seq Scan), index scan (Index Scan), hash join (Hash Join) and other scanning types.

Further, the predicate condition refers to a filter condition or connection condition used under the current node in the execution plan tree structure. For example: the current node is performing a full table scan (Seq Scan) on table A, and its predicate condition is the filter condition "A.id>12".

It should be understood that pg_stats in PostgreSQL stores statistical information about all tables in the database collected by the PostgreSQL database, including the histogram and the most frequent values. PostgreSQL first filters out the value with the highest frequency from the table, and stores it separately as the "most frequent value", and then uses the "equal frequency histogram" for data statistics on all remaining values.

Further, said S102-1: performing feature encoding on tables, operation types, and predicate conditions in each node in the execution plan tree structure, specifically including:

Perform one-hot encoding on the two types of information, table and operation type, namely E _T and E _O ;

When encoding the atomic predicate conditions in the predicate conditions, the combination of one-hot encoding and word2vec model encoding is adopted, the form of one-hot encoding is used for columns and operators, and the form of word2vec model encoding is used for values, and Concatenating the two, the vector representation E _A as an atomic predicate,

When encoding the compound predicate conditions in the predicate conditions, the maximum-minimum pooling operation is used to process the atomic predicate conditions, the maximum pooling operation is used for the disjunctive operation "OR", and the conjunction predicate "AND" is used The minimum pooling operation, and the final output as the feature embedding vector representation of the predicate condition E _P .

Further, said S102-2: respectively performing feature encoding on the histogram and mode values in the statistical information of the queried database, specifically including:

S102-21: Regroup the histograms of all columns into the same N histogram buckets, and approach the new histogram boundary by linear interpolation on the horizontal axis of the histogram. The linear interpolation is assumed to be uniformly distributed in each bucket, Then encode the histogram with a vector of size N, where each element corresponds to a bucket and is marked with the satisfiability of the predicate;

S102-22: Characterize the most frequent value as the most frequent value, and use the satisfiability of the predicate to mark it.

以及第三个桶、第四个桶、第五个桶的全部，那么该直方图编码为E_H＝[0,0.5,1,1,1]。Exemplarily, the S102-21: as shown in FIG. 4 , there is a histogram with 5 buckets, and each boundary set of the horizontal axis of the histogram is {2000, 2005, 2007, 2019, 2020, 2023}. Then for the predicate condition year>2006, the query area covers the second bucket

and all of the third bucket, the fourth bucket, and the fifth bucket, then the histogram is coded as E _H =[0,0.5,1,1,1].

Exemplarily, the S102-22: if the existing mode value is 2012, then the predicate condition year>2006 is satisfied, and thus, the coding of the mode value is E _M =[1].

Further, said S102-3: concatenating all feature encoding results together to obtain a feature vector, specifically including: processing all feature vectors with a Linear layer and an activation function Relu layer, and concatenating them to obtain a feature vector representation e.

It should be understood that the feature extraction and encoding is mainly to extract the information of each factor that affects cost estimation under the premise of fully considering the influencing factors of cost estimation, and use the corresponding method for feature encoding, and finally all the feature vectors In series, as the input of the next model, it will fully pave the way for the model to learn the execution plan and the relationship between database statistical information and cost estimation.

Further, the S103 also includes: the tree structure of the tree-type quasi-recurrent neural network QRNN includes several nodes.

Further, the query optimizer model is used to learn the relationship between the execution plan nodes and the characteristics in the database statistical information and the query cost.

Furthermore, the multi-layer perceptron MLP uses the output value of the root node of the tree quasi-recurrent neural network QRNN as an input value to estimate the total cost of the execution plan corresponding to the structured query statement.

Further, as shown in Figure 5, the tree quasi-cyclic neural network includes:

If the current node is connected to adjacent nodes in the tree structure of the execution plan, then there is also a connection relationship between the current node and the adjacent nodes in the tree structure of the query optimizer model corresponding to the tree structure of the execution plan;

If the current node is not connected to the adjacent nodes in the tree structure of the execution plan, then there is no connection relationship between the current node and the adjacent nodes in the tree structure of the query optimizer model corresponding to the tree structure of the execution plan.

Further, each node of the tree-type quasi-cyclic neural network is realized by a quasi-cyclic neural network, including:

Divide all nodes of the tree-type quasi-cyclic neural network into leaf nodes, middle nodes and root nodes according to the location of the nodes; the output of the root node is connected to the input of the multi-layer perceptron; the input of the root node is respectively connected to the left middle The output end of the node is connected to the output end of the right middle node; the input end of the left middle node is respectively connected to the output end of the left leaf node and the output end of the right leaf node;

For a leaf node, the input value c _l of the quasi-cyclic neural network corresponding to the leaf node is equal to 0, the input value c _r of the first quasi-cyclic neural network is equal to 0, and the quasi-cyclic neural network of the leaf node has an input value c _l and The input value c _r is averaged to obtain the memory unit c _t-1 ; the quasi-cyclic neural network of the leaf node processes the memory unit c _t-1 and the input sequence X to obtain the memory unit c _t of the leaf node; the input sequence X refers to the feature vector of the execution plan node corresponding to the current quasi-cyclic neural network node;

For the middle node, the input value c _l of the quasi-recurrent neural network corresponding to the middle node is equal to the memory unit c _t of the left leaf node connected to the middle node; the input value C _r of the quasi-recurrent neural network corresponding to the middle node It is equal to the memory unit C _t of the right leaf node connected to the middle node, and the quasi-circular neural network of the middle node averages the input value c _l and the input value C _r to obtain the memory unit C _t-1 ; the quasi-cyclic neural network of the middle node The cyclic neural network processes the memory unit c _t-1 and the input sequence X to obtain the memory unit c _t of the intermediate node;

For the root node, the input value c _l of the quasi-recurrent neural network corresponding to the root node is equal to the memory unit c _t of the left middle node connected to the root node; the input value c _r of the quasi-recurrent neural network corresponding to the root node equal to the memory unit c _t of the middle right node connected to the root node, the quasi-circular neural network of the root node averages the input value c _l and the input value c _r to obtain the memory unit c _t-1 ; the quasi-circular neural network of the root node The cyclic neural network processes the memory unit c _t-1 and the input sequence X to obtain the memory unit c _t of the root node.

Further, the intermediate node may be multiple intermediate nodes.

Further, the quasi-cyclic neural network of the leaf node processes the memory unit c _t-1 and the input sequence X to obtain the memory unit c _t of the leaf node; and

The quasi-recurrent neural network of the intermediate node processes the memory unit c _t-1 and the input sequence X to obtain the memory unit c _t of the intermediate node; and

The quasi-cyclic neural network of the root node processes the memory unit c _t-1 and the input sequence X to obtain the memory unit c _t of the root node; the specific processing process is the same.

Further, the quasi-cyclic neural network of the leaf node processes the memory unit c _t-1 and the input sequence X to obtain the memory unit c _t of the leaf node, which specifically includes:

For the input sequence X, Z, F, and O are obtained through three convolutional layers and a nonlinear layer respectively:

Z＝tanh(W _z *X)

F＝σ(W _f *X)

O=σ(W _o *X)

Among them, * is the convolution operation; Z is the output of the "input gate" obtained by using the additional kernel library W _z ; F is the output of the "forget gate" obtained by using the additional kernel library W _f ; O is the output of the "forget gate" obtained by using the additional kernel library W The output of the "output gate" obtained by _f ;

In order to maintain the causal relationship of the model, that is, to mark the past to predict the future, the method of masked-convolutions is used here. The left side of the input sequence is "kernel_size-1". Therefore, only 'sequence_length-kernel_size+1' past tokens can predict a given token; kernel_size represents the kernel size of the convolutional hidden layer; sequence_length is the sequence length;

When kernel_size=2, the formula is expressed as:

In the case of sequential data, the pooling component part chooses to use the "forget gate" f _t and the "output gate" o _t . This pooling operation is called fo-pooling, and its formula is as follows:

c _t ＝f _t ⊙c _t-1 +(1-f _t )⊙z _t

h _t = o _t ⊙c _t

Among them, the "forget gate" f _t determines the retention degree of the quasi-cyclic neural network unit for the input sequence at the current moment; the memory unit c _t determines the retention degree of the quasi-cyclic neural network unit for the sequence information transmitted from the previous moment; the "output gate ” o _t determines the output of the quasi-recurrent neural network unit.

It should be understood that the quasi-cyclic neural network QRNN, as shown in Figure 2, receives data information from the left and right nodes, and learns the eigenvector representation of the combination of information in each node in the execution plan tree structure and its database statistical information, In the construction of the overall model structure, it conforms to the data characteristics of the execution plan tree structure and the chronological order of the data. The specific process is as follows:

(1) Learning execution plan node information and database statistical information based on quasi-cyclic neural network QRNN;

In order to conform to the sequence of data in the tree-type execution plan, the quasi-cyclic neural network QRNN receives the memory unit c _l from the left node and the memory unit cr _r from the right node, and obtains the memory containing the information of all subtrees through an averaging process Unit c _t-1 .

In order for the whole model to capture long-term dependencies and support parallel computing, the method chooses quasi-recurrent neural network (QRNN) as the main component. There are two components in the quasi-recurrent neural network architecture, which correspond to the convolutional component and the pooling (fo-pooling) component in the convolutional neural network (CNN).

For the input sequence X, Z, F, and O are obtained through three convolutional layers and a nonlinear layer respectively:

Z＝tanh(W _z *X)

F＝σ(W _f *X)

O=σ(W _o *X)

Among them, * is the convolution operation; Z is the output of the "input gate" obtained by using the additional kernel library W _z ; F is the output of the "forget gate" obtained by using the additional kernel library W _f ; O is the output of the "forget gate" obtained by using the additional kernel library W The output of the "output gate" obtained by _f .

In order to preserve the causality of the model (i.e. only using past labels can predict future outcomes), a concept called masked-convolutions is used. That is, "kernel_size-1" zeros to the left of the input sequence. Therefore, only 'sequence_length-kernel_size+1' past tokens can predict a given token.

When kernel_size=2, the above formula can be expressed as:

In the case of sequential data, the pooling component part chooses to use the "forget gate" f _t and the "output gate" o _t . This pooling operation is called fo-pooling, and its formula is as follows:

c _t ＝f _t ⊙c _t-1 +(1-f _t )⊙z _t

h _t ＝o _t ⊙c _t

Among them, the "forget gate" f _t determines the retention degree of the quasi-cyclic neural network unit for the input sequence at the current moment; the memory unit c _t determines the retention degree of the quasi-cyclic neural network unit for the sequence information transmitted from the previous moment; the "output gate ” o _t determines the output of the quasi-recurrent neural network unit.

According to the above operations, the data information flow from the leaf nodes of the execution plan to the top nodes of the execution plan can be learned, and a reasonable tree structure model can be constructed.

It should be appreciated that the overall cost of the execution plan is estimated using a multi-layer perceptron:

The vector C containing the information of the entire execution plan tree output from the top of the tree model is used as the input of the multi-layer perceptron (MLP) to estimate the cost of the execution plan. Firstly, the execution plan dataset is split into training set and test set. On the training set, the real cost in the execution plan is taken as the true value, the output of the multi-layer perceptron is taken as the estimated cost, and then the loss is calculated. The loss function can be expressed as:

In the embodiment of the present invention, the query optimizer model is obtained by training the historical execution plan as sample data and the actual query cost corresponding to the historical execution plan as the target value, including:

S103-1: Construct a training set, where the training set is a historical execution plan with known actual query costs;

S103-2: Input the training set into the query optimizer model, train the model, stop the training when the value of the first loss function of the model no longer decreases, and obtain the query optimizer model after preliminary training.

In the embodiment of the present invention, the first loss function refers to:

Among them, estimated_cost is the result of cost prediction, and cost is the target cost value.

In an embodiment of the present invention, the method further includes:

S103-3: When the training set is updated, obtain the updated training set, input the updated training set into the query optimizer model after preliminary training, and train the model again. When the second loss function of the model When the value no longer drops, stop training to obtain the final trained query optimizer model; the second loss function uses Fisher information matrix and L2 regularization to constrain the network weight.

In the embodiment of the present invention, the second loss function refers to:

表示训练完旧的训练集A得到的最优权重；λ为L2的正则化系数，L(θ)是第二损失函数。Among them, L _B (θ) represents the loss function of directly training the old training set before adding the EWC algorithm, that is, the loss function mentioned in the part of the tree quasi-circular neural network; F _i represents the Fisher information matrix; θ _i represents the training The network weight of the new data set B;

Indicates the optimal weight obtained after training the old training set A; λ is the regularization coefficient of L2, and L(θ) is the second loss function.

Among them, (x, y) _i is (estimated_cost, cost) _i in the old task A, and Loss(θ|(x, y) _i ) is the above loss function Loss. For each parameter θ, calculate the gradient and accumulate All the gradients are finally divided by the number of samples to obtain the Fisher information matrix items of the corresponding parameters.

It should be understood that S103-3 is the dynamic data part of incremental learning, which mainly uses the elastic weight consolidation algorithm (EWC, Elastic Weight Consolidation), as shown in Figure 3, the algorithm measures the importance of the network weight to the old task , deduce the importance matrix, namely the Fisher information matrix. In order to ensure that the weights in the network that are important to the old tasks do not change much when the training of the newly added training set is completed, the algorithm adds an L2 regularization term and combines the Fisher information matrix to correct the old tasks when training the newly added data set. Important network weights are constrained.

Fisher information is a measure of the amount of information. If we want to model the distribution of a random variable x, and the parameter used for modeling is θ, then Fisher information is to measure the amount of information about θ carried by x. That is, in terms of distribution, if the function near a certain θ point is very steep, it means that the Fisher information of x is high, and a small number of points can be used to make a good estimate; if it is near a certain θ point The function of is smooth, which means that the Fisher information of x is low, and a lot of points need to be sampled to make a better estimate. This shows that we can start with the variance of the random variable. For a real parameter θ, the Fisher information of the s-function can be defined as the variance of the s-function:

Considering the above definitions, next, when training the old training set, for each network weight, calculate the square of its gradient to obtain the Fisher information matrix item for each parameter:

Specifically, the model can be fed samples one by one, and the loss function is calculated, and the gradient is automatically calculated using the neural network framework. For each parameter, all the gradients are accumulated, and finally divided by the number of samples to obtain the Fisher information matrix item of the corresponding parameter.

Constrain the network weights with L2 regularization:

The model using the EWC algorithm contains current parameters, old task parameters and fisher information matrix.

When training a new task, input data to the model to generate a cost prediction value, the model will add a new Loss item to the original loss function, and the loss item will be based on the Fisher information matrix of the model and the old task parameters. calculate. Assuming that the old task is A, the formula of the loss function when using EWC to train the new task B is as follows:

表示训练完旧的训练集A得到的最优权重；λ为L2的正则化系数。Among them, L _B (θ) represents the loss function of directly training the old training set before adding the EWC algorithm, that is, the loss function mentioned in the part of the tree quasi-circular neural network; F _i represents the Fisher information matrix; θ _i represents the training The network weight of the new data set B;

Indicates the optimal weight obtained after training the old training set A; λ is the regularization coefficient of L2.

After the training on the new data set is completed, the current parameters of the model have been updated. At this time, the old task parameters are updated to the current parameters for use in the next training. At the same time, the model uses the current parameters to predict part or all of the data, and uses the original loss function to calculate the Loss value, calculate the gradient, and replace the Fisher information matrix of the model with these calculated gradients.

Embodiment two

The present embodiment provides a tree-type QRNN-based database query optimization processing system;

Database query optimization processing system based on tree QRNN, including:

An acquisition module configured to: acquire an input structured query statement;

A determining module configured to: determine the execution plan corresponding to the structured query statement, obtain a tree structure of the execution plan, each node in the tree structure of the execution plan includes a table, an operation type and/or a predicate condition; The information corresponding to each node in the execution plan tree structure is converted into a feature vector;

The processing module is configured to: build and train a query optimizer model; the query optimizer model includes: a tree-type quasi-cyclic neural network and a multi-layer perceptron; the tree-type quasi-cyclic neural network is a tree structure, and the tree type There is a one-to-one correspondence between the nodes in the tree structure of the quasi-cyclic neural network and the nodes in the tree structure of the execution plan; The root node of the tree structure of the neural network is connected to the multi-layer perceptron; when the query optimizer model is trained, the historical execution plan is used as the sample data, and the actual query cost corresponding to the historical execution plan is used as the target value for training;

The output module is configured to: input the feature vector of each node in the execution plan tree structure to the corresponding node of the tree quasi-cyclic neural network of the query optimizer model, and obtain the query cost output by the multi-layer perceptron, based on The query cost determines the target execution plan.

It should be noted here that the acquisition module, determination module, processing module, and output module above correspond to steps S101 to S104 in Embodiment 1, and the examples and application scenarios implemented by the above modules are the same as those of the corresponding steps, but are not limited to the above The content disclosed in the first embodiment. It should be noted that, as a part of the system, the above-mentioned modules can be executed in a computer system such as a set of computer-executable instructions.

The description of each embodiment in the foregoing embodiments has its own emphases, and for parts not described in detail in a certain embodiment, reference may be made to relevant descriptions of other embodiments.

The proposed system can be implemented in other ways. For example, the above-described system embodiments are only illustrative. For example, the division of the above modules is only a logical function division. In actual implementation, there may be other division methods, for example, multiple modules can be combined or integrated into another A system, or some feature, can be ignored, or not implemented.

Embodiment Three

This embodiment also provides an electronic device, including: one or more processors, one or more memories, and one or more computer programs; wherein, the processor is connected to the memory, and the one or more computer programs are programmed Stored in the memory, when the electronic device is running, the processor executes one or more computer programs stored in the memory, so that the electronic device executes the method described in Embodiment 1 above.

It should be understood that in this embodiment, the processor can be a central processing unit CPU, and the processor can also be other general-purpose processors, digital signal processors DSP, application specific integrated circuits ASIC, off-the-shelf programmable gate array FPGA or other programmable logic devices , discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor, or the processor may be any conventional processor, or the like.

The memory may include read-only memory and random access memory, and provide instructions and data to the processor, and a part of the memory may also include non-volatile random access memory. For example, the memory may also store device type information.

In the implementation process, each step of the above method can be completed by an integrated logic circuit of hardware in a processor or an instruction in the form of software.

The method in Embodiment 1 can be directly implemented by a hardware processor, or implemented by a combination of hardware and software modules in the processor. The software module may be located in a mature storage medium in the field such as random access memory, flash memory, read-only memory, programmable read-only memory or electrically erasable programmable memory, register. The storage medium is located in the memory, and the processor reads the information in the memory, and completes the steps of the above method in combination with its hardware. To avoid repetition, no detailed description is given here.

Those skilled in the art can appreciate that the units and algorithm steps of the examples described in this embodiment can be implemented by electronic hardware or a combination of computer software and electronic hardware. Whether these functions are executed by hardware or software depends on the specific application and design constraints of the technical solution. Skilled artisans may use different methods to implement the described functions for each specific application, but such implementation should not be regarded as exceeding the scope of the present invention.

Embodiment four

This embodiment also provides a computer-readable storage medium for storing computer instructions, and when the computer instructions are executed by a processor, the method described in the first embodiment is completed.

The above descriptions are only preferred embodiments of the present invention, and are not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and changes. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included within the protection scope of the present invention.

Claims (10)

1. The database query optimization processing method based on tree QRNN is characterized by comprising the following steps:

acquiring an input structured query statement;

determining an execution plan corresponding to the structured query statement, and acquiring a tree structure of the execution plan, wherein each node in the tree structure of the execution plan comprises a table, an operation type and/or predicate conditions; converting information corresponding to each node in the execution plan tree structure into a feature vector;

constructing and training a query optimizer model; the query optimizer model includes: tree-type quasi-cyclic neural network and multi-layer perceptron; the tree-type quasi-cyclic neural network is in a tree-type structure, and nodes in the tree-type structure of the tree-type quasi-cyclic neural network are in one-to-one correspondence with nodes in the tree-type structure of the execution plan; each node of the tree-type quasi-cyclic neural network is realized by adopting the quasi-cyclic neural network, and a root node of a tree-type structure of the tree-type quasi-cyclic neural network is connected with the multi-layer perceptron; when the query optimizer model is trained, a historical execution plan is used as sample data, and the actual query cost corresponding to the historical execution plan is used as a target value for training;

And inputting the feature vector of each node in the execution plan tree structure into the corresponding node of the tree quasi-cyclic neural network of the query optimizer model to obtain the query cost output by the multi-layer perceptron, and determining the target execution plan based on the query cost.

2. The method for optimizing database query based on tree QRNN according to claim 1, wherein obtaining the tree structure of the execution plan comprises:

if the connection relation of the parent-child nodes exists between the current node and the adjacent node, the connection relation exists between the current node and the node;

if the current node and the adjacent node have no connection relationship of the parent-child nodes, the current node and the adjacent node have no connection relationship.

3. The method for optimizing database query based on tree QRNN according to claim 1, wherein the step of converting information corresponding to each node in the execution plan tree structure into a feature vector comprises:

respectively carrying out feature coding on a table, an operation type and predicate conditions in each node in the execution plan tree structure;

respectively carrying out feature coding on a histogram and a most frequent value in the statistical information of the queried database;

And (5) connecting all the feature coding results in series to obtain a feature vector.

4. The method for optimizing database query based on tree QRNN according to claim 3, wherein the feature encoding is performed on the table, the operation type and the predicate condition in each node in the tree structure of the execution plan, respectively, and specifically comprises:

performing one-hot coding on the information of the table type and the operation type to obtain E _T And E is _O ；

When the atomic predicate condition in the predicate condition is encoded, a mode of combining one-hot encoding and word2vec model encoding is adopted, a mode of one-hot encoding is adopted for the columns and operators,the value is encoded by word2vec model, and the two are connected in series as the vector representation E of atomic predicates _A ，

When encoding compound predicate conditions in predicate conditions, processing the atomic predicate conditions by using a maximum-minimum pooling operation, using a maximum pooling operation for a disjunctive operation OR, using a minimum pooling operation for a disjunctive predicate AND, AND using the final output result as a characteristic embedded vector representation E of the predicate conditions _P 。

5. The method for optimizing database query based on tree QRNN according to claim 1, wherein the tree quasi-cyclic neural network comprises:

If the current node is connected with the adjacent node in the tree structure of the execution plan, the connection relationship exists between the current node and the adjacent node in the tree structure of the query optimizer model corresponding to the tree structure of the execution plan;

if the current node is not connected with the adjacent node in the tree structure of the execution plan, no connection relationship exists between the current node and the adjacent node in the tree structure of the query optimizer model corresponding to the tree structure of the execution plan.

6. The database query optimization processing method based on tree QRNN as claimed in claim 1, wherein each node of the tree quasi-cyclic neural network is implemented by using a quasi-cyclic neural network, including:

dividing all nodes of the tree quasi-cyclic neural network into leaf nodes, intermediate nodes and root nodes according to the positions of the nodes; the output end of the root node is connected with the input end of the multi-layer perceptron; the input end of the root node is respectively connected with the output end of the left middle node and the output end of the right middle node; the input end of the left middle node is respectively connected with the output end of the Zuo Shezi node and the output end of the right leaf node;

for a leaf node, the input value c of the quasi-cyclic neural network corresponding to the leaf node _l Equal to 0, the first quasi-cyclicInput value c of neural network _r Input value c of quasi-cyclic neural network pair of leaf nodes equal to 0 _l And input value c _r Performing an averaging process to obtain a memory cell c _t-1 The method comprises the steps of carrying out a first treatment on the surface of the Quasi-cyclic neural network pair memory unit c of leaf node _t-1 And inputting the sequence X to obtain the memory unit c of the leaf node _t The method comprises the steps of carrying out a first treatment on the surface of the The input sequence X refers to the feature vector of the execution plan node corresponding to the current quasi-cyclic neural network node;

for the intermediate node, the input value c of the quasi-cyclic neural network corresponding to the intermediate node _l Memory cell c equal to the left leaf node connected to the intermediate node _t The method comprises the steps of carrying out a first treatment on the surface of the Input value c of quasi-cyclic neural network corresponding to intermediate node _r Memory cell c equal to the right leaf node connected to the intermediate node _t Quasi-cyclic neural network pair input value c of intermediate node _l And input value c _r Performing an averaging process to obtain a memory cell c _t-1 The method comprises the steps of carrying out a first treatment on the surface of the Quasi-cyclic neural network pair memory unit c of intermediate node _t-1 And input sequence X to obtain memory unit c of intermediate node _t ；

For the root node, the input value c of the quasi-cyclic neural network corresponding to the root node _l Memory cell c equal to the left intermediate node connected to the root node _t The method comprises the steps of carrying out a first treatment on the surface of the Input value c of quasi-cyclic neural network corresponding to root node _r Memory cell c equal to the right intermediate node connected to the root node _t Quasi-cyclic neural network pair input value c of root node _l And input value c _r Performing an averaging process to obtain a memory cell C _t-1 The method comprises the steps of carrying out a first treatment on the surface of the Quasi-cyclic neural network pair memory unit c of root node _t-1 And the input sequence X is processed to obtain a memory unit c of the root node _t 。

7. The method for optimizing database query based on tree QRNN according to claim 6, wherein the quasi-cyclic neural network pair memory unit c of the leaf node _t-1 And inputting the sequence X to obtain the memory unit c of the leaf node _t The method specifically comprises the following steps:

for input sequence X, Z, F, O is obtained by three convolution and nonlinear layers, respectively:

Z＝tanh(W _z *X)

F＝σ(W _f *X)

O＝σ(W _o *X)

wherein, is convolution operation; z is the use of an additional kernel library W _z The obtained input gate output; f is to use an additional kernel library W _f The obtained forget gate output; o is the use of an additional kernel library W _f The obtained output gate outputs;

when the kernel size kernel_size=2 of the convolution hidden layer, the formula is:

c _t ＝f _t ⊙c _t-1 +(1-f _t )⊙z _t

h _t ＝o _t ⊙c _t

wherein forget about the door f _t Determining the reservation degree of the quasi-cyclic neural network unit on the input sequence at the current moment; memory cell c _t Determining the reservation degree of the quasi-cyclic neural network unit on the sequence information transmitted from the last moment; output door o _t An output of the quasi-cyclic neural network unit is determined.

8. The database query optimization processing system based on tree QRNN is characterized by comprising the following components:

an acquisition module configured to: acquiring an input structured query statement;

a determination module configured to: determining an execution plan corresponding to the structured query statement, and acquiring a tree structure of the execution plan, wherein each node in the tree structure of the execution plan comprises a table, an operation type and/or predicate conditions; converting information corresponding to each node in the execution plan tree structure into a feature vector;

a processing module configured to: constructing and training a query optimizer model; the query optimizer model includes: tree-type quasi-cyclic neural network and multi-layer perceptron; the tree-type quasi-cyclic neural network is in a tree-type structure, and nodes in the tree-type structure of the tree-type quasi-cyclic neural network are in one-to-one correspondence with nodes in the tree-type structure of the execution plan; each node of the tree-type quasi-cyclic neural network is realized by adopting the quasi-cyclic neural network, and a root node of a tree-type structure of the tree-type quasi-cyclic neural network is connected with the multi-layer perceptron; when the query optimizer model is trained, a historical execution plan is used as sample data, and the actual query cost corresponding to the historical execution plan is used as a target value for training;

An output module configured to: and inputting the feature vector of each node in the execution plan tree structure into the corresponding node of the tree quasi-cyclic neural network of the query optimizer model to obtain the query cost output by the multi-layer perceptron, and determining the target execution plan based on the query cost.

9. An electronic device, comprising:

a memory for non-transitory storage of computer readable instructions; and

a processor for executing the computer-readable instructions,

wherein the computer readable instructions, when executed by the processor, perform the method of any of the preceding claims 1-7.

10. A storage medium, characterized by non-transitory storing computer-readable instructions, wherein the instructions of the method of any one of claims 1-7 are performed when the non-transitory computer-readable instructions are executed by a computer.

Images