A query rewriting method for a database

Abstract

The present application belongs to the technical field of information retrieval, and relates to a database query rewriting method. The present application firstly uses a strategy tree to represent a large number of equivalent queries of any one query statement and a corresponding rewriting order, secondly, the present application selects a high-yield rewriting order on the strategy tree based on a Monte Carlo tree search algorithm to balance query overhead and rewriting frequency; in order to more accurately estimate the overall yield of the query after rewriting, the present application uses a deep attention network to learn the association between the current query, the rewriting rule and the accessed data, and uses deep learning to fit the overall yield of the current query according to the query characteristics and the extracted association. Finally, for a complex query with a large-scale strategy tree, the present application proposes a parallel node selection algorithm, which uses an optimal algorithm to select multiple nodes without ancestor-descendant relationship and with the highest total yield on the strategy tree, and expands the strategy tree in parallel, thereby greatly improving the query rewriting efficiency.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of information retrieval, and relates to a database query rewriting method, in particular to a query rewriting method based on deep learning and Monte Carlo tree search. BACKGROUND

[0002] Query rewriting can improve the performance of slow queries (such as the existence of redundant operators) by a multiple of orders of magnitude through reasonable selection of rewriting operations. As a basic problem in query optimization, query rewriting aims to convert structured query language (SQL) into an equivalent query but with higher performance. Specifically, query rewriting performs logical equivalent transformation (such as deleting redundant operators and promoting logical subqueries) on the query statement, thereby ensuring that (1) the rewritten query is equivalent to the original query; and (2) the execution time of the rewritten query is reduced.

[0003] Query rewriting is a problem of polynomial complexity and non-determinism. Existing methods rewrite SQL queries by sequentially matching queries with predefined rules (such as predicate pushdown rules). However, the limitations of this approach are that they only use the default order (such as top-down rewriting of logical plans) without considering the relative cost-benefit compared to other rewriting orders, thereby leading to local optimization and even performance degradation. A simple strategy is to sample some rewriting orders and rewrite according to the order that reduces the query overhead the most. However, due to the large number of potential rewriting orders, it is difficult to guarantee high rewriting quality through sampling.

[0004] Therefore, the existing rewriting method still faces several challenges. First, the search space of rewriting orders is exponentially related to the number of available rules, and how to efficiently represent different rewriting orders and their relationships. Second, given a large search space, how to find the best order within a limited budget. Third, to choose a good rewriting order, an intuitive idea is to estimate the reduced overhead of rewriting (or a series of multiple rewritings), and if the reduced overhead of rewriting is small, the corresponding pruning is performed. Therefore, the third challenge is how to estimate the reduced overhead of rewriting. SUMMARY

[0005] The purpose of the present application is to provide a database query rewriting method, which, based on deep learning and Monte Carlo tree search, reduces the execution overhead of database slow queries by a large margin for different query statements.

[0006] The database data query rewriting method provided by the application has the advantages that:

[0007] The database query rewriting method provided by the application has the advantages that:

[0008] The database query rewriting method provided by the application has the advantages that: BRIEF DESCRIPTION OF DRAWINGS

[0009] Figure 1 The flowchart of the method of the application is shown in FIG. 1.

[0010] Figure 2 The flowchart of the dynamic programming method involved in the method of the application is shown in FIG. 2.

[0011] Figure 3 The architecture diagram of the rewriting benefit estimation neural network involved in the method of the application is shown in FIG. 3.

[0012] Figure 4 The schematic diagram of an embodiment of query rewriting of the method of the application is shown in FIG. 4. DETAILED DESCRIPTION

[0013] The database data query rewriting method provided by the application utilizes a strategy tree to represent a large number of equivalent queries and corresponding rewriting sequences of any one query statement, selects a high-yield rewriting sequence on the strategy tree based on a Monte Carlo tree search algorithm, balances query overhead and rewriting frequency, utilizes a deep attention network to learn the association between a current query and rewriting rules and access data, and utilizes deep learning to fit the overall yield of the current query according to query characteristics and the extracted association, utilizes a rewriting yield estimation network to select a plurality of nodes without an ancestor-descendant relationship and with the highest total yield on the strategy tree by using an optimal algorithm, and expands the strategy tree in parallel, so that the query rewriting efficiency is greatly improved.

[0014] The database data query rewriting method has a flowchart as shown in Figure 1 The database data query rewriting method specifically includes the following steps:

[0015] (1) inputting a query statement to be rewritten into a database;

[0016] (2) replacing the query statement in step (1) into an equivalent logical plan by using a transformation method based on relational algebra, initializing a strategy tree with only a root node, the root node representing the logical plan of the input query, each node attribute on the strategy tree including a global yield value (the maximum yield that can be obtained by continuing rewriting from the node) and an access frequency, expanding all child nodes belonging to the root node on the strategy tree according to a plurality of rewriting modes of the query statement, each child node representing a query statement obtained by rewriting the root node by using one rewriting mode, and obtaining an initial strategy tree;

[0017] (3) searching the initial strategy tree in step (2) to obtain a query statement with the highest global yield value, including the following steps:

[0018] (3-1) setting a maximum search number N (the setting of the search number can be determined according to memory resources or time overhead of query rewriting);

[0019] (3-2) utilizing a dynamic programming method, the flowchart of the dynamic programming method is as shown in Figure 2 , selecting k nodes from the root node and a plurality of child nodes of the initial strategy tree in step (2), taking the query statements of all rewriting modes corresponding to the k selected nodes as new child nodes, expanding to the initial decision tree, and obtaining an n-1 decision tree, n [1, N]; to ensure that all selected k nodes have no ancestor-descendant relationship and have the maximum total yield value;

[0020] (3-3) utilizing a rewriting yield estimation neural network, the architecture diagram of the rewriting yield estimation neural network is as shown in Figure 3 , estimating the subsequent yield value of the k selected nodes, the subsequent yield value including the selected frequency of the node and the query time, and the specific process is as follows:

[0021] (3-3-1) Encode the query statements, various rewrite methods of the query statements, and the database metadata corresponding to the query statements corresponding to the k nodes in step (3-2) respectively to obtain an encoding vector;

[0022] (3-3-2) Input the query statement, the rewriting method of the query statement, and the encoding code of the database metadata corresponding to the query statement into the first attention network of the rewriting revenue estimation neural network respectively, and output the correlation between various rewriting methods. Based on the features of the query statement and the database metadata, generate a compressed vector for each rewriting method.

[0023] (3-3-3) Input the compressed vector of the rewriting method in step (3-3-2) into the hidden layer of the rewriting profit estimation neural network, normalize the compressed vector of all rewriting methods using the sigmoid activation function (SIGMOID), and fit it using a two-layer fully connected neural network to obtain the output.

[0024] (3-3-4) Input the compressed vector of the rewriting method with the highest profit in step (3-3-3) and the compressed vector of the query statement in step (3-3-2) into the second attention network of the rewriting profit estimation neural network, calculate the relationship between the operator features of the rewriting method and the query statement, and output a compressed vector. Input the compressed vector into the output layer of the rewriting profit estimation neural network, perform nonlinear transformation, and output the subsequent profit value of the query statement corresponding to the node on the n-1 decision tree.

[0025] (3-5) The nodes on the n-1 decision tree pass their respective subsequent payoff values ​​to their respective ancestor nodes. Each ancestor node compares the received subsequent payoff value with the original payoff value. If the subsequent payoff value is greater than the original payoff value, the original payoff value is updated with the subsequent payoff value to obtain the n-th policy tree. If the subsequent payoff value is less than or equal to the original payoff value, it continues to compare with the payoff values ​​of other ancestor nodes to obtain the n-th policy tree.

[0026] (3-6) Repeat steps (3-2)-(3-5) until N iterations are completed to obtain the Nth policy tree;

[0027] (4) Compare the subsequent revenue values ​​of all nodes on the Nth policy tree, and output the query statement corresponding to the node with the shortest query time on the Nth policy tree to the user, thereby implementing the database query rewriting method.

[0028] In step (3-2) of the query rewriting method described above, the selection of k nodes involves exploring multiple rewriting sequences with the highest total return to improve the efficiency of query rewriting. The specific process is as follows:Figure 2 As shown, it includes the following steps:

[0029] (1) On the strategy tree of step (3-2) of the query rewriting method described above, select the optimal k nodes without ancestor-descendant relationships in a bottom-up manner. Let... This indicates selecting the i nodes with the largest total reward value that have no ancestor-descendant relationship under node v. Let U(u) represent the corresponding total revenue value, where U(u) represents the global revenue value of node u. The calculation is divided into the following two cases:

[0030] (1) When v is a leaf node, calculate and for

[0031] (2) When v is a non-leaf node, calculate based on v's child nodes. and If v is not a leaf node, then let {c1, c2, ..., c...} x} represents the set of child nodes of node v in the strategy tree. For any 1≤z≤x, 1≤i≤k, this invention first calculates all child nodes c. z of and Clearly, v has an ancestor-descendant relationship with any of its child nodes, therefore v can only appear in... In, and not in First, consider the case where v is not selected:

[0032] Let M denote a matrix with k rows and x columns, and M[i,j] be a subset of {c1, ..., c2}. j Select the i nodes with the maximum profit value from the nodes under}: when i=1, When i > 1, consider the following three cases to calculate M[i, j]: (i) Do not select node c j and node c j (ii) any child node under c, i.e., M[i,j] = M[i,j-1]; j Select i nodes. Therefore, we have (iii) In c j Next, select z nodes where 1 ≤ z < i, that is, select iz nodes from the first j-1 children:

[0033]

[0034] The I matrix is ​​calculated using the dynamic programming algorithm based on the above formula. Then, we can obtain... And obtain by selecting the node that maximizes I[i, x]. Next, if we choose a node v, we only need to update and If then update Finally, output the selected k nodes, which guarantee that the selected nodes have no ancestor-descendant relationship and the total revenue value is the highest. The selected k nodes are output, which guarantees that the selected nodes have no ancestor-descendant relationship and the total revenue value is the highest.

[0035] In step (3-3) of the above query rewriting method, the construction and training of the rewritten revenue estimation network, as shown in Figure 3 , includes the following steps:

[0036] (1) The rewritten revenue estimation network is composed of a first attention network, a hidden layer neural network, a second attention network, and an output neural network. The first attention network and the second attention network are both composed of a cosine calculation unit and a single-layer fully connected neural network. The hidden layer neural network is a two-layer fully connected neural network. The output neural network is a single-layer fully connected neural network. The input layer size of the single-layer fully connected neural network of the first attention network is n x (m+k), and the output layer size is n x (m+k). n, m, and k represent the number of rewriting methods, the number of query statement features, and the number of database metadata features, respectively. The input layer size of the first-layer fully connected neural network of the hidden layer neural network is n x (m+k), and the output layer size is n x (m+k). The input layer size of the second-layer fully connected neural network of the hidden layer neural network is n x (m+k), and the output layer size is 1 x (m+k). The input layer size of the single-layer fully connected neural network of the second attention network is 1 x (m+k), and the output layer size is 1 x (m+k). The input layer size of the single-layer fully connected neural network of the output neural network is 1 x (m+k), and the output layer size is 1 x 1.

[0037] (2) According to the access features of the database query statement, the query statements in the database are clustered. In each cluster, a query statement is selected, and the query time of all rewriting methods of the query statement is calculated. The shortest query time is taken as the query time of all query statements in the cluster.

[0038] (3) 80% of the query statements in the database are divided as a training set. The format of the training data in the training set is <q, R, D, C ↓ (q)>, where q represents the query statement, R represents the rewriting rule set of the query statement, D represents the metadata feature of the query statement, and C ↓ (q) represents the maximum obtainable future revenue value (revenue value represents the query time that can be reduced) of the query statement q after subsequent query rewriting.

[0039] (4) using the training set obtained in step (3) to train the rewritten revenue estimation network in step (1), that is, inputting the encoding of each set of rewritten mode, query statement and database metadata in the training set into the first attention network, and outputting a representation vector from the first attention network; inputting the representation vector into the hidden layer neural network, and outputting a hidden vector from the hidden layer neural network; inputting the hidden vector into the second attention network, and outputting a compressed vector of the rewritten mode with the maximum revenue from the second attention network; inputting the compressed vector into the output network, and outputting the estimated follow-up revenue value F(q) of the query statement q from the output network; calculating the mean square error MSE of the estimated follow-up revenue value of the query statement, MSE = (F(q) - C ↓ (q)) 2 , wherein F(q) represents the estimated follow-up revenue value of the query statement, and C ↓ (q) represents the actual follow-up revenue value (a known quantity) of the query statement q;

[0040] (5) according to the mean square error in step (4), calculating the Laplace regularization term L reg : L reg = ∑ q’ μ q′ ||F(q) - F(q')||, wherein q' is any one query statement in the same cluster as q except q, and ||F(q) - F(q')|| represents the L1 distance, so as to minimize the sum of absolute differences; L reg represents that the queries in the same cluster have similar overheads and should have similar follow-up overhead reduction; and updating the rewritten revenue estimation network with the Laplace regularization term;

[0041] (6) setting a verification threshold γ of the mean square error of the follow-up revenue value of a query statement, taking the remaining 20% of the query statements in the database as a verification set, inputting all the query statements in the verification set into the rewritten revenue estimation network in step (5) respectively, obtaining the mean square error of the estimated follow-up revenue value of the query statement respectively, calculating the average value of all the mean square errors , and comparing the average value with the actual follow-up revenue value of the query statement q, if , repeating steps (4) to (5) until , and obtaining the trained rewritten revenue estimation network.

[0042] The following introduces an embodiment of the method of the application, and a flowchart of the embodiment is shown in Figure 4 .

[0043] It is assumed that the rewritten revenue estimation network of the data in the method of the application has been constructed and trained in advance, and the query process of the data in the database is as follows:

[0044] (1): input a query statement to be rewritten (the original query time exceeds 20 minutes), and use the query preprocessing module to replace the query with an equivalent logical plan;

[0045] (2) use the strategy tree construction module to initialize a strategy tree with only a root node, representing the input query;

[0046] (3) use the deployed parallel rewriting module to select three nodes on the strategy tree each time, such as {v3, v 12 , v 13}, to ensure that there is no ancestor-descendant relationship between the three nodes, and the sum of the global benefit values of the three nodes is maximum;

[0047] (4) use the deployed strategy tree search method to expand the strategy tree from the selected three nodes, such as node v3 which can be rewritten in a rewriting way, so that v3 expands a child node;

[0048] (5) estimate the subsequent benefit value of each selected node in step (4) using the rewriting benefit estimation network: for each query statement q corresponding to a node, input the query statement q, all rewriting methods and database metadata into the first attention network, and output the representation vector from the first attention network; input the representation vector into the hidden layer neural network, and output the hidden vector from the hidden layer neural network; input the hidden vector into the second attention network, and output the compressed vector of the rewriting method with the maximum benefit from the second attention network; input the compressed vector into the output network, and output the estimated subsequent benefit value of the query statement q from the output network;

[0049] (6) according to the subsequent benefit values of the three selected nodes obtained in step (5), update the global benefit values of the three selected nodes and their ancestor nodes, i.e. if the sum of the subsequent benefit value of the selected node v and the global benefit value of the selected node v is greater than the global benefit value of the node v' (v' is the node v or the ancestor node of the node v), then make the global benefit value of the node v' equal to the sum of the subsequent benefit value of the selected node v and the global benefit value of the selected node v;

[0050] (7) repeat steps (3)-(6) until the target benefit value or the maximum number of iterations is reached;

[0051] (8) output the query statement corresponding to the node with the lowest global benefit value on the strategy tree to the user.

Claims

1. A method of query rewriting of database data, characterized by The method utilizes a strategy tree to represent a large number of equivalent queries and corresponding rewriting sequences of any one query statement, selects a high-yield rewriting sequence on the strategy tree based on a Monte Carlo tree search algorithm, balances query overhead and rewriting frequency, utilizes a deep attention network to learn the association between the current query and rewriting rules and access data, and utilizes deep learning to fit the overall yield of the current query according to the query characteristics and the extracted association, utilizes a rewriting yield estimation network, selects multiple nodes without ancestor-descendant relationship and with the highest total yield on the strategy tree by using an optimal algorithm, and expands the strategy tree in parallel, so as to greatly improve the query rewriting efficiency. The method specifically comprises the following steps: (1) inputting a query statement to be rewritten into a database; (2) replacing the query statement in step (1) into an equivalent logical plan by using a transformation method based on relational algebra, initializing a strategy tree with only a root node, the root node representing the logical plan of the input query, expanding all child nodes of the root node on the strategy tree according to various rewriting modes of the query statement, each child node representing a query statement obtained by rewriting the root node by using a rewriting mode, and obtaining an initial strategy tree; (3) searching the initial strategy tree in step (2) to obtain a query statement with the highest global yield value, comprising the following steps: (3-1) setting a maximum search number N; (3-2) selecting k nodes from the root node and multiple child nodes of the initial strategy tree in step (2) by using a dynamic programming method, expanding all query statements of the rewriting modes corresponding to the k selected nodes as new child nodes to the initial decision tree to obtain an n-1 decision tree, n = 1, 2,..., N; (3-3) estimating the subsequent yield values of the k selected nodes by using a rewriting yield estimation neural network; (3-4) the nodes on the n-1 decision tree respectively pass the respective subsequent yield values to the respective ancestor nodes, each ancestor node compares the received subsequent yield value with the original yield value, if the subsequent yield value is greater than the original yield value, the subsequent yield value is used to update the original yield value to obtain an n strategy tree, if the subsequent yield value is less than or equal to the original yield value, the yield value is compared with the yield values of other ancestor nodes to obtain an n strategy tree; (3-5) repeating steps (3-2) to (3-4) until N iterations are completed to obtain an N strategy tree; (4) comparing the subsequent yield values of all nodes on the N strategy tree, outputting the query statement corresponding to the node with the shortest query time on the N strategy tree to the user, and implementing the query rewriting method of the database.

2. The query rewrite method of claim 1, wherein, Step (3-3) utilizes a rewriting yield estimation neural network to estimate the subsequent yield values of the k selected nodes, comprising the following steps: (3-3-1) encoding the query statement corresponding to the k nodes in step (3-2), the multiple rewriting modes of the query statement, and the database metadata corresponding to the query statement respectively to obtain an encoding vector; (3-3-2) input the query statement, the rewriting manner of the query statement and the encoding of the database metadata corresponding to the query statement in (3-3-1) into the first attention network of the rewriting benefit estimation neural network respectively, output the correlation between multiple rewriting manners, and generate a compression vector for each rewriting manner according to the query statement and the database metadata characteristics; (3-3-3) input the compression vector of the rewriting manner in step (3-3-2) into the hidden layer neural network of the rewriting benefit estimation neural network, normalize the compression vectors of all rewriting manners using the S-shaped excitation function SIGMOID, and fit using a two-layer fully connected neural network; (3-3-4) input the compression vector of the rewriting manner with the highest benefit in step (3-3-3) and the compression vector of the query statement in step (3-3-2) into the second attention network of the rewriting benefit estimation neural network, calculate the relationship between the rewriting manner and the operator characteristics of the query statement, output a compression vector, input the compression vector into the output layer of the rewriting benefit estimation neural network, and perform nonlinear conversion to output the subsequent benefit value of the query statement corresponding to the node on the n-1 decision tree.

3. The query rewrite method of claim 1, wherein, The construction and training of the rewriting benefit estimation network in step (3-3) include the following steps: (1) The rewritten benefit estimation network is composed of a first attention network, a hidden layer neural network, a second attention network and an output neural network, the first attention network and the second attention network are both composed of a cosine calculation unit and a single-layer full connection neural network; the hidden layer neural network is a two-layer full connection neural network; the output neural network is a single-layer full connection neural network; the input layer size of the single-layer full connection neural network of the first attention network is , the output layer size is , n, m and k respectively represent the number of rewriting modes, the number of query statement features and the number of database metadata features; the input layer size of the first layer full connection neural network of the hidden layer neural network is , the output layer size is ; the input layer size of the second layer full connection neural network of the hidden layer neural network is , the output layer size is ; the input layer size of the single-layer full connection neural network of the second attention network is , the output layer size is ; the input layer size of the single-layer full connection neural network of the output neural network is , the output layer size is ; (2) according to the access characteristics of the database query statement, cluster the query statements in the database, select one query statement in each cluster, and calculate the query time of all rewriting manners of the query statement, and take the shortest query time as the query time of all query statements in the cluster; (3) 80% of the query statements in the database are divided as a training set, and the format of the training data in the training set is <q, R, D, C ↓ (q)>, where q represents a query statement, R represents a rewriting rule set of the query statement, and D represents a metadata feature of the query statement; (4) training the rewritten revenue estimation network of step (1) with the training set obtained in step (3), that is, inputting the encoding of each set of rewriting mode, query statement and database metadata of the training set into the first attention network, and the first attention network outputs a representation vector; inputting the representation vector into the hidden layer neural network, and the hidden layer neural network outputs a hidden vector; inputting the hidden vector into the second attention network, and the second attention network outputs a compressed vector of the rewritten mode with the maximum revenue; inputting the compressed vector into the output network, and the output network outputs the estimated subsequent revenue value of the query statement q ; calculating the mean square error MSE of the estimated subsequent revenue value of the query statement, MSE , wherein represents the estimated subsequent revenue value of the query statement; (5) Calculate Laplace regularization term according to the mean square error of step (4) : wherein, is any one query statement in the same cluster as q except q, denotes L1 distance to minimize the sum of absolute differences, and the revenue estimation network is updated with the Laplace regularization term. (6) Set a validation threshold value of the mean square error of the subsequent revenue value of a query statement, take the remaining 20% of the query statements in the database as a validation set, input all the query statements in the validation set into the rewritten revenue estimation network of step (5) respectively, and obtain the estimated mean square error of the subsequent revenue value of the query statement respectively, calculate the average value of all the mean square errors , compare the average value with the true subsequent revenue value of the query statement q , if , repeat steps (4)-(5) until , obtain the trained rewritten revenue estimation network.

Images