CN116150190A - Database query optimization processing method and system based on tree type QRNN - Google Patents
Database query optimization processing method and system based on tree type QRNN Download PDFInfo
- Publication number
- CN116150190A CN116150190A CN202310120892.8A CN202310120892A CN116150190A CN 116150190 A CN116150190 A CN 116150190A CN 202310120892 A CN202310120892 A CN 202310120892A CN 116150190 A CN116150190 A CN 116150190A
- Authority
- CN
- China
- Prior art keywords
- node
- tree
- neural network
- quasi
- execution plan
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24532—Query optimisation of parallel queries
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06N—COMPUTING ARRANGEMENTS BASED ON SPECIFIC COMPUTATIONAL MODELS
- G06N3/00—Computing arrangements based on biological models
- G06N3/02—Neural networks
- G06N3/04—Architecture, e.g. interconnection topology
- G06N3/0463—Neocognitrons
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06N—COMPUTING ARRANGEMENTS BASED ON SPECIFIC COMPUTATIONAL MODELS
- G06N3/00—Computing arrangements based on biological models
- G06N3/02—Neural networks
- G06N3/08—Learning methods
- G06N3/084—Backpropagation, e.g. using gradient descent
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Artificial Intelligence (AREA)
- Health & Medical Sciences (AREA)
- Life Sciences & Earth Sciences (AREA)
- Databases & Information Systems (AREA)
- Biomedical Technology (AREA)
- Biophysics (AREA)
- Evolutionary Computation (AREA)
- General Health & Medical Sciences (AREA)
- Molecular Biology (AREA)
- Computing Systems (AREA)
- Mathematical Physics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a database query optimization processing method machine system based on tree-type QRNN; wherein the method comprises: 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; 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.
Description
Technical Field
The invention relates to the technical field of database query optimization, in particular to a database query optimization processing method and system based on tree QRNN.
Background
The statements in this section merely relate to the background of the present disclosure and may not necessarily constitute prior art.
The basic software is an important foundation and support for information industry development and informatization construction, and the core basic software is more and more important for enterprises. Among them, the database is one of the important components of the basic software, the application field of the database is very wide, and many companies need to use the database to store data information. And a query optimizer in the database can determine the execution performance of the database. The cost estimation is of importance to the query optimizer, which directs the selection of execution plans, and selects an efficient execution plan for the query statement. And with the advent of the big data age, data has grown in large quantities, so it is very important to build a cost estimation model of a query optimizer that is reasonable and can continuously process newly added data.
With the recent development of databases, databases are beginning to be combined with various emerging technologies, such as artificial intelligence, blockchain, secret state computation, etc. The combination of a database and artificial intelligence is a win-win, and both the database and artificial intelligence can benefit from this combination. Because of the heuristic nature of query optimization, many have attempted to apply deep learning to cost estimates in a query optimizer. For example, heterogeneous modeling is performed on a tree structure of the execution plan, and a linear method is adopted to capture characteristic information of the execution plan. However, such methods do not encode predicates in the execution plan, rely on the estimated cardinality and estimated cost of the relational database, ultimately result in errors in the prediction, and cannot accurately capture information of complex execution plans using linear methods. Meanwhile, another part of researches are to model nodes in an execution plan by using a variable long short-term memory network (LSTM) of a cyclic neural network, so that the problems of gradient disappearance and gradient explosion can be solved, but the method has large calculation amount, can not be calculated in parallel and is difficult to train. In addition, researchers have employed a transducer model to capture information about nodes in an execution plan and a tree bias force mechanism to capture tree structural features of the execution plan.
Although the method has certain results, the influence factors of cost estimation in the query optimizer are not comprehensively considered; secondly, most of them only consider the situation under static data, neglect the influence that incremental data produced on the cost estimation model in the real scene.
Disclosure of Invention
In order to solve the defects in the prior art, the invention provides a database query optimization processing method and system based on tree QRNN; and estimating the cost according to the existing execution plan and statistical information, and simultaneously adopting an incremental learning method to continuously learn new data, so that the database can better select the execution plan to execute according to the cost estimation value, and the aim of improving the performance of the database for executing query sentences is fulfilled.
In a first aspect, the invention provides a database query optimization processing method based on tree QRNN;
a database query optimization processing method based on tree type QRNN comprises 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.
In a second aspect, the invention provides a database query optimization processing system based on tree QRNN;
a tree QRNN based database query optimization processing system comprising:
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.
In a third aspect, the present invention also provides an electronic device, including:
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 the first aspect described above.
In a fourth aspect, the invention also provides a storage medium storing non-transitory computer readable instructions, wherein the instructions of the method of the first aspect are performed when the non-transitory computer readable instructions are executed by a computer.
In a fifth aspect, the invention also provides a computer program product comprising a computer program for implementing the method of the first aspect described above when run on one or more processors.
Compared with the prior art, the invention has the beneficial effects that:
firstly, a histogram and a most frequent value are extracted from statistical information of a traditional database, a linear difference method is adopted to redistribute the horizontal axis value of the histogram, and then predicate information is used for carrying out feature coding on the histogram and the most frequent value information, so that the statistical information of the database can be fully utilized.
And modeling the execution plan by using a tree-type quasi-cyclic neural network, wherein the model supports parallel computation and can capture long-term dependence.
Finally, the method adopts incremental learning to process the incremental data, and further iteratively updates the model weight, so that the model is more accurate under the condition of dynamic data.
Drawings
The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this specification, illustrate embodiments of the invention and together with the description serve to explain the invention.
FIG. 1 is a diagram of a model framework in accordance with a first embodiment of the present invention;
fig. 2 is a schematic illustration of a tree-type quasi-cyclic neural network unit according to a first embodiment of the present invention;
FIG. 3 is a schematic illustration of an incremental learning portion according to a first embodiment of the present invention;
FIG. 4 is a diagram illustrating a histogram coding scheme according to a first embodiment of the present invention;
fig. 5 is a schematic diagram of one-to-one correspondence between two tree structures according to a first embodiment of the present invention.
Detailed Description
It should be noted that the following detailed description is exemplary and is intended to provide further explanation of the 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 is noted that the terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of exemplary embodiments according to the present invention. As used herein, unless the context clearly indicates otherwise, the singular forms also are intended to include the plural forms, and furthermore, it is to be understood that the terms "comprises" and "comprising" and any variations thereof are intended to cover non-exclusive inclusions, such as, for example, processes, methods, systems, products or devices that comprise a series of steps or units, are not necessarily limited to those steps or units that are expressly listed, but may include other steps or units that are not expressly listed or inherent to such processes, methods, products or devices.
Embodiments of the invention and features of the embodiments may be combined with each other without conflict.
All data acquisition in the embodiment is legal application of the data on the basis of meeting laws and regulations and agreements of users.
The existing deep learning cost estimation method based on the execution plan does not fully consider influencing factors in cost estimation, and improves parallelism of the model under the condition of ensuring modeling accuracy so as to reduce training time. Furthermore, the iterative update of the model weights under incremental data is not considered.
Example 1
The embodiment provides a database query optimization processing method based on tree-type QRNN;
as shown in fig. 1, the database query optimization processing method based on tree QRNN includes:
s101: acquiring an input structured query statement;
s102: 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;
s103: 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;
S104: 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.
It should be understood that the S101: the method comprises the steps of obtaining an input structured query statement, wherein the structured query statement is a database query and programming language and is used for accessing data and querying, updating and managing data stored in a relational database.
Further, the step S102: determining an execution plan corresponding to the structured query statement, which specifically includes:
the execution plan of the structured query statement SQL is queried in the PostgreSQL database using explain analyse SQL.
It should be understood that in the process of searching and planning paths, the postgreSQL database expresses different execution schemes of the query request by establishing different paths, and after more paths meeting the conditions are generated, the path with the minimum cost is selected, and is converted into an execution plan and transmitted to an 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: each node in the execution plan tree structure is allowed to include predicate conditions, and is allowed to not include predicate conditions.
Further, each node in the execution plan tree structure refers to a node on an execution path in the execution plan.
Further, obtaining a tree structure of the execution plan, including:
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.
Further, the step S102: converting information corresponding to each node in the execution plan tree structure into a feature vector, wherein the method specifically comprises the following steps:
s102-1: respectively carrying out feature coding on a table, an operation type and predicate conditions in each node in the execution plan tree structure;
s102-2: respectively carrying out feature coding on a histogram and a most frequent value in the statistical information of the queried database;
s102-3: and (5) connecting all the feature coding results in series to obtain a feature vector.
Preferably, the operation type refers to: executing the operation type of the current node in the planning tree structure, wherein the operation type comprises the following steps: a full table Scan (Seq Scan), an Index Scan (Index Scan), a Hash Join (Hash Join), and so on.
Further, the predicate condition refers to: filtering conditions or connection conditions used under the current node in the execution plan tree structure. For example: the current node is to perform a full table Scan (Seq Scan) on the A table, and the predicate condition is the filter condition "A.id >12".
It should be appreciated that pg_stats in PostgreSQL store statistics about all tables in the library collected by the PostgreSQL database, in which the histogram and the most frequent value are located. The PostgreSQL firstly screens out the value with highest frequency from the table, and the value is used as the 'most frequent value' to be stored independently, and all the remaining values are subjected to data statistics in the form of 'equal frequency histogram'.
Further, the S102-1: the method comprises the steps of respectively carrying out feature coding on a table, an operation type and predicate conditions in each node in an execution plan tree structure, and specifically comprises the following steps:
one-hot encoding of both table and operation type information, i.e. E T And E is O ；
When the atomic predicate condition in the predicate conditions is encoded, a mode of combining one-hot encoding and word2vec model encoding is adopted, a mode of one-hot encoding is adopted for columns and operators, a mode of word2vec model encoding is adopted for values, and the two modes are connected in series to be used as a vector representation E of the atomic predicate 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 an disjunctive operation 'OR', using a minimum pooling operation for an disjunctive predicate 'AND', AND using the final output result as a characteristic embedded vector representation E of the predicate conditions P 。
Further, the step S102-2: respectively carrying out feature coding on the histogram and the most frequent value in the statistical information of the queried database, wherein the method specifically comprises the following steps:
s102-21: the histograms of all columns are regrouped into the same barrels of N histograms, the horizontal axis of the histograms approaches to a new histogram boundary through linear interpolation, the linear interpolation is supposed to be uniformly distributed in each barrel, then the histograms are encoded by adopting vectors with the size of N, wherein each element corresponds to one barrel, and the satisfaction of predicates is used for marking;
s102-22: and marking the characteristic of the most frequency value as the most frequency value by adopting the satisfaction of the predicate.
Illustratively, the S102-21: as shown in fig. 4, there is a 5-bin histogram, and the respective boundary sets of the histogram horizontal axis are {2000, 2005, 2007, 2019, 2020, 2023}. Then for predicate conditional year >2006, query area covers second bucket
And all of the third bucket, fourth bucket, and fifth bucket, then the histogram is encoded as E H ＝[0,0.5,1,1,1]。
Illustratively, the S102-22: if the existing highest frequency value is 2012, the predicate condition year is satisfied>2006, thus, the code of the most frequent value is E M ＝[1]。
Further, the step S102-3: all feature coding results are connected in series to obtain feature vectors, and the method specifically comprises the following steps: all eigenvectors are processed by the Linear layer and the activation function Relu layer and are connected in series to obtain an eigenvector representation E.
It should be understood that, the feature extraction and encoding mainly extracts the factor information of each factor affecting the cost estimation in a classified manner on the premise of fully considering the factor affecting the cost estimation, adopts a corresponding method to perform feature encoding, and finally connects all feature vectors in series as the input of the next model, so as to make a full lay for the model to learn the execution plan and the correlation between the statistical information of the database and the cost estimation.
Further, the step S103 further includes: the tree structure of the tree-type quasi-cyclic neural network QRNN comprises a plurality of nodes.
Further, a query optimizer model is used to learn the relationships between features in the execution plan nodes and database statistics and query costs.
Further, the multi-layer perceptron MLP takes the output value of the tree-type quasi-cyclic neural network QRNN root node as an input value to estimate the total cost of the execution plan corresponding to the structured query statement.
Further, as shown in fig. 5, the tree-type quasi-cyclic neural network includes:
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.
Further, each node of the tree-type quasi-cyclic neural network is implemented by adopting 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 input value c of the first quasi-cyclic 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 。
Further, the intermediate node may be a plurality of intermediate nodes.
Further, 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 comprises the steps of carrying out a first treatment on the surface of the And (3) with
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 The method comprises the steps of carrying out a first treatment on the surface of the And
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 The method comprises the steps of carrying out a first treatment on the surface of the The specific treatment process is the same.
Further, the quasi-cyclic neural network pair of the leaf nodesMemory cell c 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;
in order to preserve the causality of the model, i.e. to mark the past before the future can be predicted, a masked-convolutions (mask-convolutions) method is used here. To the left of the input sequence is "kernel_size-1". Thus, only the 'sequence_length-kernel_size+1' past tags can predict a given tag; 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:
the pooled component part selects to use the "forget gate" f taking into account the sequence data t And an output gate o t This pooling operation is called fo-pooling and has the following formula:
c t ＝f t ⊙c t-1 +(1-f t )⊙z t
h t ＝o t ⊙c t
wherein "forget 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 gate o t An output of the quasi-cyclic neural network unit is determined.
It should be understood that the QRNN of the quasi-cyclic neural network, as shown in fig. 2, receives data information from left and right nodes, learns the feature vector representation of the combination of information in each node in the execution plan tree structure and its database statistics, and conforms to the data features of the execution plan tree structure and the time sequence of the data in the overall model structure construction. The specific flow is as follows:
(1) Performing planning node information and database statistical information based on QRNN learning of the quasi-cyclic neural network;
to conform to the data timing sequence in tree execution plans, the quasi-cyclic neural network QRNN receives the memory unit c from the left node l Memory cell c associated with right node r And obtaining the memory cell c containing the whole sub-tree information by an averaging process t-1 。
In order for the entire model to capture long-term dependencies and support parallel computing, the method selects a quasi-recurrent neural network (QRNN) as the principal component. There are 2 components in the quasi-recurrent neural network architecture, which correspond to the convolutional components and the pooling (fo-pooling) components in the Convolutional Neural Network (CNN), respectively.
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 the use of an additional innerNuclear pool W f The obtained forget gate output; o is the use of an additional kernel library W f The obtained output gate outputs.
To preserve the causality of the model (i.e. only future results can be predicted using past markers), a concept called mask-convolution (mask-rotations) is used. That is, the left side of the input sequence is "kernel_size-1" zero. Thus, only the 'sequence_length-kernel_size+1' past tags can predict a given tag.
When kernel_size=2, the above formula can be expressed as:
the pooled component part selects to use the "forget gate" f taking into account the sequence data t And an output gate o t This pooling operation is called fo-pooling and has the following formula:
c t ＝f t ⊙c t-1 +(1-f t )⊙z t
h t ＝o t ⊙c t
wherein "forget 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 gate o t An output of the quasi-cyclic neural network unit is determined.
According to the operation, the data information flow from the leaf node of the execution plan to the top node 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 an execution plan is estimated using a multi-layer perceptron:
the vector C including the entire execution plan tree information output from the top of the tree model is used as an input to a multi-layer perceptron (MLP) to estimate the cost of the execution plan. The execution plan dataset is first split into a training set and a testing set. On the training set, the real cost in the execution plan is taken as a true value, the output of the multi-layer perceptron is taken as an estimated cost, and then the loss is calculated, and the loss function can be expressed as:
in an embodiment of the present invention, the query optimizer model is obtained by training a historical execution plan as sample data and an actual query cost corresponding to the historical execution plan as a target value, and includes:
s103-1: constructing a training set, wherein the training set is a historical execution plan with known actual query cost;
s103-2: and inputting the training set into the query optimizer model, training the model, and stopping training when the first loss function value of the model is not reduced any more, so as to obtain the primarily trained query optimizer model.
In the embodiment of the present invention, the first loss function refers to:
wherein, the estimated_cost is the result of cost prediction, and the 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, acquiring an updated training set, inputting the updated training set into the primarily trained query optimizer model, retraining the model, and stopping training when the second loss function value of the model is no longer reduced, so as to obtain a finally trained query optimizer model; and a second loss function, wherein Fisher information matrix and L2 regularization are used for restraining the network weight.
In the embodiment of the present invention, the second loss function refers to:
wherein L is B (θ) represents the loss function of the old training set, i.e., the loss function mentioned in the tree-type quasi-cyclic neural network section, directly trained before the EWC algorithm is not added; f (F) i Representing a Fisher information matrix; θ i Representing the network weights when training the new dataset B;
representing the optimal weight obtained by the old training set A after training; lambda is the regularization coefficient of L2 and L (θ) is the second loss function.
Wherein, (x, y) i For the old task A (estimated_cost) i ，Loss(θ|(x,y) i ) Namely, the Loss function Loss calculates gradients for each parameter theta, accumulates all gradients, and finally divides the gradients by the number of samples to obtain Fisher information matrix items of the corresponding parameters.
It should be understood that S103-3 is an incremental learning dynamic data portion, which mainly adopts an elastic weight consolidation algorithm (EWC, elastic Weight Consolidation), and as shown in fig. 3, the algorithm derives an importance matrix, i.e., a Fisher information matrix, by measuring the importance of the network weights to the old task. In order to ensure that the weights important to the old task in the network do not change greatly when the training of the newly added training set is completed, the algorithm adds an L2 regular term and combines with the Fisher information matrix to restrict the network weights important to the old task when the newly added data set is trained.
Fisher information is an index for measuring information quantity. If we are to model a distribution of a random variable x, the parameter used for modeling is θ, then Fisher information is the amount of information about θ carried by the metric x. That is, in terms of distribution, if the function near a certain point θ appears very steep, this means that the Fisher information amount of x is high, and a good estimate can be made by taking a small number of points; if the function near a certain theta point appears gentle, the Fisher information quantity representing x is low, and a plurality of points need to be sampled to make better estimation. This illustrates that, starting from the variance of the random variable, for a real parameter θ, fisher information for the s-function can be defined as the variance of the s-function:
With the above definition in mind, next, when training the old training set, the square of its gradient is calculated for each network weight to obtain the Fisher information matrix entry for each parameter:
specifically, the model may be fed with samples one by one and the loss function calculated, with the gradient automatically calculated using a neural network framework. And accumulating all gradients for each parameter, and dividing the gradients by the number of samples to obtain Fisher information matrix items of the corresponding parameters.
Constraining network weights with L2 regularization:
the model using the EWC algorithm contains current parameters, old task parameters, and a fisher information matrix.
When a new task is trained, data is input into the model to generate a cost prediction value, the model is restored to the original Loss function, a new Loss term is added, and the Loss term is calculated according to the Fisher information matrix of the model and old task parameters. Assuming the old task is a, the formula of the loss function when training the new task B using EWC is as follows:
wherein L is B (θ) represents the loss function of the old training set, i.e., the loss function mentioned in the tree-type quasi-cyclic neural network section, directly trained before the EWC algorithm is not added; f (F) i Representing a Fisher information matrix; θ i Representing the network weights when training the new dataset B;
representing the optimal weight obtained by the old training set A after training; lambda is the regularization coefficient of L2.
When training on the new data set is completed, the current parameters of the model have been updated, at which point the old task parameters are updated to current parameters for use in the next training. And simultaneously, predicting part or all of data by using the current parameters, calculating a Loss value by using an original Loss function, calculating gradients, and replacing the Fisher information matrix of the model by the calculated gradients.
Example two
The embodiment provides a database query optimization processing system based on tree-type QRNN;
a tree QRNN based database query optimization processing system comprising:
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.
Here, it should be noted that the above-mentioned obtaining module, determining module, processing module and output module correspond to steps S101 to S104 in the first embodiment, and the above-mentioned modules are the same as examples and application scenarios implemented by the corresponding steps, but are not limited to the disclosure of the first embodiment. It should be noted that the modules described above may be implemented as part of a system in a computer system, such as a set of computer-executable instructions.
The foregoing embodiments are directed to various embodiments, and details of one embodiment may be found in the related description of another embodiment.
The proposed system may be implemented in other ways. For example, the system embodiments described above are merely illustrative, such as the division of the modules described above, are merely a logical function division, and may be implemented in other manners, such as multiple modules may be combined or integrated into another system, or some features may be omitted, or not performed.
Example III
The 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 coupled to the memory, the one or more computer programs being stored in the memory, the processor executing the one or more computer programs stored in the memory when the electronic device is running, to cause the electronic device to perform the method of the first embodiment.
It should be understood that in this embodiment, the processor may be a central processing unit CPU, and the processor may 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 device, discrete gate or transistor logic devices, discrete hardware components, or the like. 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 portion of the memory may also include non-volatile random access memory. For example, the memory may also store information of the device type.
In implementation, the steps of the above method may be performed by integrated logic circuits of hardware in a processor or by instructions in the form of software.
The method in the first embodiment may be directly implemented as a hardware processor executing or implemented by a combination of hardware and software modules in the processor. The software modules may be located in a random access memory, flash memory, read only memory, programmable read only memory, or electrically erasable programmable memory, registers, etc. as well known in the art. The storage medium is located in a memory, and the processor reads the information in the memory and, in combination with its hardware, performs the steps of the above method. To avoid repetition, a detailed description is not provided herein.
Those of ordinary skill in the art will appreciate that the elements and algorithm steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware or combinations of computer software and electronic hardware. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present invention.
Example IV
The present embodiment also provides a computer-readable storage medium storing computer instructions that, when executed by a processor, perform the method of embodiment one.
The above description is only of the preferred embodiments of the present invention and is not intended to limit the present invention, but various modifications and variations can be made to the present invention by those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention should be included in 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.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310120892.8A CN116150190A (en) | 2023-02-15 | 2023-02-15 | Database query optimization processing method and system based on tree type QRNN |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310120892.8A CN116150190A (en) | 2023-02-15 | 2023-02-15 | Database query optimization processing method and system based on tree type QRNN |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116150190A true CN116150190A (en) | 2023-05-23 |
Family
ID=86353958
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310120892.8A Pending CN116150190A (en) | 2023-02-15 | 2023-02-15 | Database query optimization processing method and system based on tree type QRNN |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116150190A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN118469618A (en) * | 2024-07-11 | 2024-08-09 | 四川大学 | Part cost estimation method based on long-term and short-term memory network |
| CN118469618B (en) * | 2024-07-11 | 2024-09-24 | 四川大学 | Part cost estimation method based on long-term and short-term memory network |
-
2023
- 2023-02-15 CN CN202310120892.8A patent/CN116150190A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN118469618A (en) * | 2024-07-11 | 2024-08-09 | 四川大学 | Part cost estimation method based on long-term and short-term memory network |
| CN118469618B (en) * | 2024-07-11 | 2024-09-24 | 四川大学 | Part cost estimation method based on long-term and short-term memory network |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Marcus et al. | Plan-structured deep neural network models for query performance prediction | |
| CN110163261B (en) | Unbalanced data classification model training method, device, equipment and storage medium | |
| US20210232376A1 (en) | Vectorized representation method of software source code | |
| CN112418482B (en) | Cloud computing energy consumption prediction method based on time series clustering | |
| CN111581454B (en) | Parallel query performance prediction system and method based on depth map compression algorithm | |
| US20200167659A1 (en) | Device and method for training neural network | |
| CN110163429B (en) | Short-term load prediction method based on similarity day optimization screening | |
| CN111079989B (en) | DWT-PCA-LSTM-based water supply amount prediction device for water supply company | |
| CN115617830A (en) | Data query optimization processing method and device based on machine learning | |
| CN112052081B (en) | Task scheduling method and device and electronic equipment | |
| CN112434024A (en) | Relational database-oriented data dictionary generation method, device, equipment and medium | |
| CN109787821B (en) | Intelligent prediction method for large-scale mobile client traffic consumption | |
| CN117110748A (en) | Transformer substation main equipment operation state abnormality detection method based on fusion terminal | |
| CN116244333A (en) | Database query performance prediction method and system based on cost factor calibration | |
| CN118176511A (en) | Quantum computing monitoring system | |
| CN117494888A (en) | Distributed photovoltaic power prediction method and system based on optimized cascade | |
| CN113920379B (en) | Zero sample image classification method based on knowledge assistance | |
| CN114911844A (en) | Approximate query optimization system based on machine learning | |
| WO2021143686A1 (en) | Neural network fixed point methods and apparatuses, electronic device, and readable storage medium | |
| CN116861373A (en) | Query selectivity estimation method, system, terminal equipment and storage medium | |
| CN112070280B (en) | Real-time traffic flow parallel prediction method, system, terminal and storage medium | |
| CN117111464A (en) | Self-adaptive fault diagnosis method under multiple working conditions | |
| CN115953902B (en) | Traffic flow prediction method based on multi-view space-time diagram convolutional network | |
| CN116150190A (en) | Database query optimization processing method and system based on tree type QRNN | |
| CN116955335A (en) | Address data management method and system based on big data model algorithm |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |