Method and system for automatically generating database query rewriting rules

Abstract

The present invention provides a method and system for automatically generating database query rewriting rules, including: step 1: modeling the query plan and query rewriting rules of the database into an enumerable form; step 2: enumerating the constraints between each pair of query plan templates to generate a first query rewriting rule. Compared with the prior art, the present invention can generate new rewriting rules that do not exist in the existing database system, can match and rewrite more complex queries generated by frameworks such as object-relational mapping in Internet applications, and improve the database query rewriting capability and the performance of database queries under Internet applications.

Description

Technical Field

[0001] The present invention relates to the technical field of relational data system query optimization, and in particular to a method and system for automatically generating database query rewriting rules, in particular to a method and system for automatically generating database query rewriting rules based on a compiler super-optimization method. Background Art

[0002] With the booming development of Internet applications, databases are used in the back-end development of Internet applications. For many Internet applications, the latency of database queries is one of the important factors affecting the user experience, and query optimization is also an important part of application development. Query rewriting is one of the important steps in query optimization. It uses the rewriting rules provided by the database to convert the original query into a semantically equivalent new query. Query rewriting rules specify the equivalence relationship between queries. Effective rewriting rules can rewrite queries into more efficient and lower latency queries. Therefore, database developers have been looking for more effective query rewriting rules to increase the opportunities for query rewriting and enhance the database's ability to optimize queries.

[0003] Query rewriting rules in existing relational databases are usually manually formulated by database experts based on experience. However, due to the diversity of query language characteristics and semantics, it is very difficult to formulate and verify the correctness of query rewriting rules, and the accumulation of manually formulated rewriting rules is very slow. At the same time, with the further development of Internet applications, the Object-Relational Mapping (ORM) framework has been widely used. The ORM framework frees developers from the method of manually constructing Structured Query Language (SQL) queries and generates queries through an object-oriented programming language interface. However, this also makes the generated queries complex and counterintuitive, and the queries cannot be matched and rewritten by the existing rewriting rules, resulting in inefficient queries.

[0004] Compiler super-optimization methods are to replace a machine instruction sequence with another instruction sequence that is equivalent to it but executes faster by exhaustively searching for instruction sequences, thereby achieving the purpose of optimizing instruction execution. There is already related work that uses the idea of ​​compiler super-optimization to find rules for rewriting deep neural networks. However, the current use scenarios of this super-optimization method do not involve the field of database query optimization. The exhaustive strategy of the super-optimization method is more complicated to apply to database queries and rewriting rules. Since the query rewriting rules contain the constraint relationship between each parameter on the original query and the new query, it is necessary to appropriately model the query and construct a suitable heuristic algorithm to avoid the exponential explosion in the number caused by exhaustively enumerating these constraint relationships.

[0005] At present, there is no work based on compiler super-optimization method to realize the automatic generation and verification of query rewriting rules. Therefore, the previous work on query optimization rule generation is not comprehensive and there are still some gaps.

[0006] Patent document CN105718593B discloses a database query optimization method and system, which includes: representing a query statement string with a query internal representation structure; performing feature analysis on the query internal representation to generate a semantically equivalent query internal representation; performing syntax analysis on the semantically equivalent query internal representation to generate a semantic database internal representation; performing query optimization processing on the semantic database internal representation to generate a final execution plan; interpreting and executing the final execution plan to generate a query result or error message. However, this method does not involve the technical issues of query optimization rule generation and verification. Summary of the invention

[0007] In view of the defects in the prior art, the object of the present invention is to provide a method and system for automatically generating database query rewriting rules.

[0008] A method for automatically generating database query rewriting rules according to the present invention includes:

[0009] Step 1: Model the database query plan and query rewrite rules into an enumerable form;

[0010] Step 2: Enumerate the constraints between each pair of query plan templates and generate the first query rewrite rule.

[0011] Preferably, step 2 comprises:

[0012] Step 201: enumerate query plan templates, select any two query plan templates, and obtain at least one template pair;

[0013] Step 202: enumerate the constraints of all template pairs to obtain a constraint set;

[0014] Step 203: weakening the constraint set to generate a second query rewriting rule;

[0015] Step 204: De-duplicate the second query rewriting rule to obtain the first query rewriting rule.

[0016] Preferably, step 201 includes:

[0017] Step 2011: enumerate the tree structures of all legal query plan templates, where the tree structure of the query plan template has a unique root node;

[0018] Step 212: For each tree structure, a node type that is not an input node is assigned to each node of the tree using a permutation and combination method, and a specific query plan template is constructed;

[0019] Step 2013: For each tree structure, add an input node to each leaf node, and the number of input nodes is equal to the number of child nodes of the leaf node;

[0020] Step 2014: Select any two query plan templates to obtain at least one template pair.

[0021] Preferably, the query rewrite rule is modeled as a triple, including: an original query plan template, a target query plan template, and a constraint set.

[0022] Preferably, step 202 includes:

[0023] Step 221: For any template pair, use one query plan template as the original query plan template and the other query plan template as the target query plan template, and enumerate all possible constraint relationships between the symbols on the two query plan templates to form a constraint set.

[0024] A database query rewriting rule automatic generation system provided by the present invention comprises:

[0025] Module M1: Model the database query plan and query rewrite rules into an enumerable form;

[0026] Module M2: Enumerate the constraints between each pair of query plan templates and generate the first query rewriting rule.

[0027] Preferably, module M2 comprises:

[0028] Submodule M201: enumerate query plan templates, select any two query plan templates, and obtain at least one template pair;

[0029] Submodule M202: enumerate the constraints of all template pairs to obtain a constraint set;

[0030] Submodule M203: weakening the constraint set to generate a second query rewriting rule;

[0031] Submodule M204: performing deduplication processing on the second query rewriting rule to obtain the first query rewriting rule.

[0032] Preferably, the submodule M201 includes:

[0033] Unit D2011: Enumerate the tree structure of all legal query plan templates. The tree structure of the query plan template has a unique root node;

[0034] Unit D2012: For each tree structure, use permutations and combinations to assign a non-input node type to each node of the tree and construct a specific query plan template;

[0035] Unit D2013: For each tree structure, add an input node to each leaf node, the number of input nodes is equal to the number of child nodes of the leaf node;

[0036] Unit D2014: Select any two query plan templates to obtain at least one template pair.

[0037] Preferably, the query rewrite rule is modeled as a triple, including: an original query plan template, a target query plan template, and a constraint set.

[0038] Preferably, the submodule M202 includes:

[0039] Unit D2021: For any template pair, take one query plan template as the original query plan template and the other query plan template as the target query plan template, and enumerate all possible constraint relationships between the symbols on the two query plan templates to form a constraint set.

[0040] Compared with the prior art, the present invention has the following beneficial effects:

[0041] 1. The present invention realizes the automatic generation of query rewriting rules without manually formulating the rewriting rules.

[0042] 2. The present invention applies compiler super-optimization methods to the automatic generation of query rewriting rules, models query plans and query rewriting rules in an enumerable form, and introduces the enumeration method in compiler super-optimization, which can generate a large number of rewriting rules in a short running time. It is supplemented by a heuristic algorithm to filter out useless rewriting rules, ensuring that the rewriting rules finally generated are helpful to improve the performance of the rewritten query.

[0043] 3. The present invention generates new rewriting rules that do not exist in existing databases through enumeration methods, and some rules can successfully match and rewrite more complex queries generated by frameworks such as object-relational mapping, thereby improving query rewriting capabilities and the performance of database queries under Internet applications. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] Other features, objects and advantages of the present invention will become more apparent from the detailed description of non-limiting embodiments made with reference to the following drawings:

[0045] Figure 1 A schematic diagram of a process of the present invention;

[0046] Figure 2Another schematic diagram of the process of the present invention. DETAILED DESCRIPTION

[0047] The present invention is described in detail below in conjunction with specific embodiments. The following embodiments will help those skilled in the art to further understand the present invention, but are not intended to limit the present invention in any form. It should be noted that, for those of ordinary skill in the art, several changes and improvements can also be made without departing from the concept of the present invention. These all belong to the protection scope of the present invention.

[0048] Figure 1 A schematic diagram of a flow chart of the present invention is shown in FIG. Figure 1 As shown, the present invention provides a method for automatically generating database query rewriting rules, comprising the following steps:

[0049] Step 1: Model the database's query plan and query rewrite rules into an enumerable form.

[0050] The query rewrite rule is modeled as a triple, including: the original query plan template, the target query plan template and the constraint set.

[0051] Specifically, a query rewrite rule is modeled as a triple: <original query plan template Q0, target query plan template Q1, constraint set C>. The query plan is a tree structure composed of relational algebra operator nodes. The database executes the operators on each node from bottom to top. Each node takes the output result of its child node as its input, and the output of the root node is the output result set of the query. The query plan template refers to the symbolized result of the parameters of the operator node in the query plan.

[0052] The present invention models the query plan template as follows.

[0053] First, the parameters of the operator node are symbolized as one of the following symbol types:

[0054] Relation symbol: A relation symbol rel, symbolizes the input relation or input table of a query plan template; attribute list symbol: An attribute list symbol attrs, symbolizes a sequence of column attributes. In particular, a relation symbol rel also corresponds to an attribute list symbol attrs rel Represents all column attributes in this relation; predicate symbol: a predicate symbol pred, symbolizing a predicate expression in a query.

[0055] Secondly, the present invention defines the following types of operator nodes, where the subscript represents the symbol contained in the node.

[0056] Input rel: Input node, representing an initial input relation rel, with 0 child nodes; Projection attrs : Projection node, which projects the input onto the attribute column specified by attrs, with 1 child node; Selection pred.attrs : Filter node, filter the tuples in its input that do not satisfy the predicate pred. Each tuple uses the attribute column in attrs as the input of the tuple to the predicate. The number of child nodes is 1; In-Sub Selection attrs : A correlated subquery filter of the form "attrs IN(...)". The attribute column attrs is the column attribute of the left child node, and the right child node represents the subquery. In the output of the left child node, the tuple whose value in the attribute list attrs is not in the subquery output is filtered. The number of child nodes is 2; Table join node, the output of two child nodes is inner / left / right joined, the join predicate is attrs l =attrs r , the number of child nodes is 2; Deduplication: Deduplication node, removes duplicate tuples in the input, the number of child nodes is 1.

[0057] Step 2: Enumerate the constraints between each pair of query plan templates and generate the first query rewrite rule.

[0058] A constraint is a predicate that specifies the relationship between symbols in the original query plan template and the target query plan template. A query rewrite rule indicates that the original query plan template and the target query plan template are semantically equivalent when all constraints in the constraint set are satisfied.

[0059] Specifically, the present invention defines the following types of constraints:

[0060] RelEq(rel1, rel2): indicates that the two input relations rel1 and rel2 are equal, that is, they have the same tuples; AttrsEq(attrs1, attrs2): indicates that the two attribute lists are equal, that is, they have the same attribute sequence; PredEq(pred1, pred2): indicates that the two predicates are equivalent, that is, SubAttrs(attrs1, attrs2): indicates that any attribute in attrs1 also belongs to attrs2, that is, the attribute column contained in attrs1 is a subset of attrs2; RefAttrs(rel1, attrs1, rel2, attrs2): indicates that the attribute column attrs1 in relation rel1 references the attribute column attrs2 in relation rel2, that is, any tuple projected on attrs1 in relation rel1 will be in the set of tuples projected on attrs2 in relation rel2; Unique(rel, attrs): indicates that any tuple projected on attrs in relation rel is unique; NotNull(rel, attrs): indicates that any tuple projected on attrs in relation rel is non-NULL.

[0061] In an optional implementation, step 2 includes: step 201: enumerate the query plan templates, select any two query plan templates, and obtain at least one template pair; step 202: enumerate the constraints of all template pairs to obtain a constraint set; step 203: weaken the constraint set to generate a second query rewrite rule; step 204: deduplicate the second query rewrite rule to obtain the first query rewrite rule.

[0062] Wherein, step 201 includes: step 2011: enumerate the tree structure of all legal query plan templates, the tree structure of the query plan template has a unique root node; step 2012: for each tree structure, use the permutation and combination method to assign a node type that is not an input node to each node of the tree, and construct a specific query plan template; step 2013: for each tree structure, add an input node to each leaf node, and the number of input nodes is equal to the number of child nodes of the leaf node; step 2014: select any two query plan templates to obtain at least one template pair.

[0063] Further, step 202 includes: step 2021: for any template pair, one of the query plan templates is used as the original query plan template, and the other query plan template is used as the target query plan template, and all possible constraint relationships between the symbols on the two query plan templates are enumerated to form a constraint set.

[0064] Steps 201 to 204 are described in detail below.

[0065] First, enumerate the query plan templates, that is, enumerate the tree structure of all legal query plan templates, and enumerate the types of each operator node on each tree structure. Including: enumerate the tree structure of all legal query plan templates, which has a unique root node. Each node in the tree is an empty node, that is, no specific node type is assigned to it; for each tree structure, use permutations and combinations to assign a non-input node type to each node of the tree to construct a specific query plan template; for each tree structure, add an input node to each leaf node, and the number of input nodes is equal to the number of child nodes of the node.

[0066] Among them, a runtime parameter N is defined, N is an integer, and the number of nodes in the constructed tree is limited to N or less. Under the premise of ensuring the diversity of query plan templates, the number of enumerated query plan templates is prevented from increasing exponentially with the number of nodes, resulting in a long program running time. Since the number of child nodes of nodes in all non-input node types is 1 or 2, two types of nodes are used to construct the tree structure: the first type has one child node, and the second type has two child nodes.

[0067] Then, enumerate the constraints, that is, any two query plan templates can be matched to form a pair of templates, and list all template pairs. For any template pair, take one side as the original query plan template Q0 and the other side as the target query plan template Q1, and enumerate all possible constraint relationships between the symbols on the two templates to form a constraint set C * , and generate a triple representation <Q0,Q1,C * >.

[0068] In the present invention, the number of operator nodes of the target query plan template Q1 is limited to not be more than Q0. This restriction indicates that the generated rewrite rules will not make the original query more complex, and thus are more likely to improve the performance of the rewritten query.

[0069] Then, the constraint set is weakened to generate the second query rewriting rule, that is, to weaken the application conditions of the rewriting rule so that it can match more actual queries and have more rewriting opportunities. * Perform a "weakening" operation to find a set of constraints that satisfy the "weakest equivalent" property and generate a second query rewrite rule.

[0070] Among them, the "weakened" constraint set C * , that is, to find whether C *A proper subset C′ of the set C′ is found, so that satisfying only the constraints in C′ can ensure that the original query plan template Q0 is semantically equivalent to the target query plan template Q1. At the same time, the constraint set after removing any constraint from the set C′ cannot guarantee the equivalence relationship between the two query plan templates. There are five specific steps as follows: Step S1: Initialize the constraint set C←C*; Step S2: For any constraint c in the constraint set C, try to remove the constraint, and verify whether the obtained subset C′←C\{c} can satisfy the equivalence of Q0 and Q1; Step S3: If the subset C′ obtained by removing any constraint c cannot satisfy the equivalence of Q0 and Q1, then stop the "weakening" operation, and the original set C satisfies the "weakest equivalence" property, and add it to the result set S; Step S4: If the constraint set C′ obtained by removing a constraint c can still satisfy the equivalence of Q0 and Q1, for all such constraints c, let C←C\{c}, and recursively execute step S2; Step S5: Combine each constraint set in the result set S with Q0 and Q1 to form a triple, thereby generating one or more correct second query rewrite rules.

[0071] Specifically, for the problem of proving that a pair of query plan templates are equivalent under a set of constraints, the present invention uses the SPES (SPES can refer to the paper: SPES: A Two-Stage Query Equivalence Verifier) ​​query equivalence solver, which is verified based on Satisfiability Modulo Theories (SMT).

[0072] In order to reduce the search space of constraint subsets and reduce algorithm overhead, the present invention directly skips the constraint set that meets one of the following conditions by default, and does not judge the equivalence of two query plan templates under the constraint set, that is, it considers that the set is illegal or must not be the "weakest equivalent", and continues to try to delete constraints in the set for further "weakening" operations. Specifically, it includes the following four aspects.

[0073] First, there is a constraint in the constraint set, and the symbols involved are all from the target query plan template Q1, which has no effect on proving the equivalence of the two query plan templates.

[0074] Second aspect: the constraint set is not a closure, that is, one or more constraints in the constraint set can derive other constraints that do not exist in the constraint set.

[0075] Third aspect: There are contradictory constraints in the constraint set, which makes the query plan template illegal. For example, an attribute list symbol attrs comes from two different input relations rel1 and rel2 at the same time, that is, the constraint set contains the constraints SubAttrs(attrs1, attrsrel1 ) and SubAttrs(attrs1, attrs rel2 ).

[0076] Fourthly, there exists a subset of the constraint set such that the two query plan templates are equivalent under the constraints of the subset; or there exists a superset of the constraint set such that the two query plan templates are not equivalent under the constraints of the superset.

[0077] Finally, for all the generated correct second query rewriting rules, redundant rewriting rules are removed to obtain a final non-redundant rule set, namely, the first query rewriting rules.

[0078] Specifically, for a second query rewriting rule, if the rewriting result of any query that can be rewritten by the rule is the same as the rewriting result obtained after one or more other rules are continuously applied to the query, then the rule is a redundant rule and can be removed.

[0079] Embodiment 1

[0080] Figure 2 Another schematic diagram of the present invention is shown in FIG. Figure 2 As shown, it includes: Step P1: enumerate the query plan templates, enumerate the tree structures of all legal query plan templates, and enumerate the node types of each node on each tree structure, where the number of nodes n = 1, 2, ..., N, N is an integer, and the node types include: Filter node Selection pred.attrs ,exist Figure 2 Abbreviated as Sel, projection node Projection attrs , abbreviated as Proj, table connection node Abbreviated as Join, In-Sub Selection attrs , abbreviated as In-Sub and Input rel , abbreviated as Input. Among them, the node types involved in the enumeration also include: Deduplication node, abbreviated as Dedup.

[0081] Step P2: Any two query plan templates can be matched to form a pair of templates, and all template pairs are listed. For any template pair, one side is used as the original query plan template Q0, and the other side is used as the target query plan template Q1, and all possible constraint relationships between the symbols on the two templates are enumerated to form a constraint set C*, and a triple representation is generated. <Q0,Q1,C * >. Taking one of the template pairs as an example, enumerate all constraints and enumerate the set of all constraints as follows: Figure 2 As shown, including: Projection node Projection a2and Projection a3 、InnerJoin a0，a1 , a1 and input node Input r0 、Input r1 and Input r2 , the original query plan template Q0 is Projection a2 (InnerJoin a0，a1 (Input r0 , Input r1 )), target query plan template Q1 is Projection a3 (Input r2 ), all possible constraint sets C between Q0 and Q1 * :C * ={RelEq(r0, r1), RelEq(r0, r2), RelEq(r1, r2)}∪{AttrsEq(a0, a1), AttrsEq(a0, a2), AttrsEq(a0, a3)}∪{At trsEq(a1, a2), AttrsEq(a2, a3), AttrsEq(a1, a3)}∪{SubAttrs(a0, r0), SubAttrs(a1, r1), SubAttrs(a2, r0) , SubAttrs(a2, r1)}∪{Unique(r0, a0), Unique(r1, a1), Unique(r0, a2), Unique(r1, a2)}∪{NotNull(r0, a0), NotNull(r1, a1), NotNull(r0, a2), NotNull(r1, a2)}∪{RefAttrs(r0, a0, r1, a1), RefAttrs(r1, a1, r0, a0)}.

[0082] Among them, since the number of operations of the original query plan template Q0 is more than the number of operations of the target query plan template Q1, Q1 cannot be used as the original template and Q2 cannot be used as the target template for constraint enumeration.

[0083] Step P3: Weaken the constraint set and generate the second query rewrite rule: * Perform a "weakening" operation to find a set of constraints that satisfy the "weakest equivalent" property. * Try to delete any constraint in , when you choose to delete the constraint RelEq(r0, r1), you get the subset C * \{RelEq(r0, r1)}, this set can satisfy the equivalence of two query plan templates; from the set C *Try to delete any constraint in \{RelEq(r0, r1)}. When you choose to delete the constraint RelEq(r1, r2), you get the subset C * \{RelEq(r0, r1), RelEq(r0, r2)}, this set can also satisfy the equivalence; ...; when selecting from the set C * Continuously delete the set C in sequence dropped When deleting each constraint in each step, the subset obtained by deleting a constraint in each step can either satisfy the equivalence of the two query plan templates, or be judged to be illegal, or be judged to be definitely not the "weakest equivalent". Under such a "weakening" operation, the constraint set C is obtained. * \C dropped , denoted as C′. Verify that C′ can still satisfy the equivalence of the two query plan templates, and continue to try to delete any constraint in C′. At this time, it is found that deleting any constraint c will result in a set C′\{c} that cannot satisfy the equivalence of the two query plans. Therefore, C′ is a "weakest equivalent" constraint set. Finally, after the entire "weakening" process, we get C * The only "weakest equivalent" subset C' of , thus obtaining a rewriting rule<Q0,Q1,C′> , which can rewrite the SQL query statement of the form "SELECT t0.c2 FROM t0 INNER JOIN t1 ON t0.c0=t1.c1" into the form of "SELECT t0.c2 FROM t0". Further, we get R=R∪{<Q0,Q1,C′>}.

[0084] Among them, C dropped and C′ can be expressed by formula (1) and formula (2) respectively.

[0085] C dropped ={RelEq(r0, r1), RelEq(r1, r2)}∪{AttrsEq(a0, a1), AttrsEq(a0, a2), AttrsEq(a0, a3)}∪{AttrsEq(a1, a2), AttrsEq(a1, a3)}∪{SubAttrs(a2 , r1)}∪{Unique(r0, a0), Unique(r0, a2), Unique(r1, a2)}∪{NotNull(r1, a1), NotNull(r0, a2), NotNull(r1, a2)}∪{RefAttrs(r0, a0, r1, a6)}; (1)

[0086] C′=C * \C dropped={RelEq(r0, r2), AttrsEq(a2, a3)} ∪ {SubAttrs (a0, r0), SubAttrs (a1, r1), SubAttrs (a2, r0)} ∪ {Unique (r1, a1), NotNull (r0, a0), RefAttrs (r0, a0, r1, a1)}. (2)

[0087] Step P4: For the set R of all correct second query rewrite rules generated, remove the redundant rewrite rules therein to obtain a rewrite rule set R′=dropRedundant(R) without redundant rules, i.e., the first query rewrite rule. The dropRedundant(·) function inputs a rewrite rule set R and outputs a subset R' of the set R, where all elements of R' are non-redundant rewrite rules. Its input set R is the set of all second query rewrite rules collected after executing steps P2 and P3 on all query plan templates.

[0088] Specifically, to determine whether a rule r∈R is a redundant rule, firstly, a corresponding SQL query q is generated by rule r. The query must be the minimum SQL pattern that can be rewritten by rule r, that is, any query that can be rewritten by rule r contains the SQL pattern of query q. For the query q, it is rewritten by rule set R and set R{r} respectively. If the rewritten results are consistent, then the rule r is a redundant rule in rule set R, otherwise it is not a redundant rule.

[0089] Furthermore, when query rewrite rules are automatically generated, all generated data can be stored in memory, the generated rewrite rules can be converted into plain text and stored in a file, or the generated new rewrite rules can be implemented into an existing database system (such as MySQL, PostgreSQL, etc.) through certain engineering implementations.

[0090] The present invention provides a method for automatically generating database query rewriting rules based on a compiler super-optimization method. The present invention models database query plans and query rewriting rules in an enumerable form, and based on the compiler super-optimization instruction sequence enumeration method, enumerates query plan templates and constraints between each pair of templates, and automatically discovers and generates correct query rewriting rules. The present invention can generate new rewriting rules that do not exist in existing database systems, can match and rewrite more complex queries generated by ORM and other frameworks in Internet applications, and improve the database query rewriting capability and the performance of database queries under Internet applications.

[0091] The present invention also provides a database query rewriting rule automatic generation system, comprising the following modules:

[0092] Module M1: Model the query plan and query rewrite rules of the database into an enumerable form.

[0093] In the present invention, the query rewrite rule is modeled as a triple, including: an original query plan template, a target query plan template and a constraint set.

[0094] Module M2: Enumerate the constraints between each pair of query plan templates and generate the first query rewriting rule.

[0095] Among them, module M2 includes: sub-module M201: enumerate the query plan templates, select any two query plan templates, and obtain at least one template pair; sub-module M202: enumerate the constraints of all template pairs to obtain a constraint set; sub-module M203: weaken the constraint set to generate a second query rewrite rule; sub-module M204: deduplicate the second query rewrite rule to obtain the first query rewrite rule.

[0096] Specifically, submodule M201 includes: unit D2011: enumerate the tree structure of all legal query plan templates, the tree structure of the query plan template has a unique root node; unit D2012: for each tree structure, use permutations and combinations to assign a node type that is not an input node to each node of the tree, and construct a specific query plan template; unit D2013: for each tree structure, add an input node to each leaf node, and the number of input nodes is equal to the number of child nodes of the leaf node; unit D2014: select any two query plan templates to obtain at least one template pair.

[0097] Specifically, submodule M202 includes: Unit D2021: for any template pair, one of the query plan templates is used as the original query plan template, and the other query plan template is used as the target query plan template, and all possible constraint relationships between the symbols on the two query plan templates are enumerated to form a constraint set.

[0098] Compared with the prior art, the present invention has the following beneficial effects:

[0099] 1. The present invention realizes the automatic generation of query rewriting rules without manually formulating the rewriting rules.

[0100] 2. The present invention applies compiler super-optimization methods to the automatic generation of query rewriting rules, models query plans and query rewriting rules in an enumerable form, and introduces the enumeration method in compiler super-optimization, which can generate a large number of rewriting rules in a short running time. It is supplemented by a heuristic algorithm to filter out useless rewriting rules, ensuring that the rewriting rules finally generated are helpful to improve the performance of the rewritten query.

[0101] 3. The present invention generates new rewriting rules that do not exist in existing databases through enumeration methods, and some rules can successfully match and rewrite more complex queries generated by frameworks such as object-relational mapping, thereby improving query rewriting capabilities and the performance of database queries under Internet applications.

[0102] Those skilled in the art know that, in addition to implementing the system, device and its various modules provided by the present invention in a purely computer-readable program code, it is entirely possible to implement the same program in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers and embedded microcontrollers by logically programming the method submodule M. Therefore, the system, device and its various modules provided by the present invention can be considered as a hardware component, and the modules included therein for implementing various programs can also be considered as structures within the hardware component; the modules for implementing various functions can also be considered as both software programs for implementing methods and structures within hardware components.

[0103] The above describes the specific embodiments of the present invention. It should be understood that the present invention is not limited to the above specific embodiments, and those skilled in the art can make various changes or modifications within the scope of the claims, which does not affect the essence of the present invention. In the absence of conflict, the embodiments of the present application and the features in the embodiments can be combined with each other arbitrarily.

Claims

1. A method for automatically generating database query rewriting rules, characterized in that: include: Step 1: Model the database query plan and query rewrite rules into an enumerable form; Step 2: Enumerate the constraints between each pair of query plan templates and generate the first query rewrite rule; The step 2 comprises: Step 201: enumerate the query plan templates, select any two of the query plan templates, and obtain at least one template pair; Step 202: enumerate the constraints of all template pairs to obtain a constraint set; Step 203: weakening the constraint set to generate a second query rewriting rule; Step 204: performing deduplication processing on the second query rewriting rule to obtain the first query rewriting rule; The step 201 includes: Step 211: enumerate the tree structures of all legal query plan templates, where the tree structure of the query plan template has a unique root node; Step 212: For each tree structure, a node type that is not an input node is assigned to each node of the tree using a permutation and combination method, and a specific query plan template is constructed; Step 2013: For each tree structure, add an input node to each leaf node, and the number of input nodes is equal to the number of child nodes of the leaf node; Step 2014: Select any two of the query plan templates to obtain at least one template pair.

2. The method for automatically generating database query rewriting rules according to claim 1, characterized in that: The query rewriting rule is modeled as a triple, including: an original query plan template, a target query plan template and a constraint set.

3. The method for automatically generating database query rewriting rules according to claim 2, characterized in that: The step 202 includes: Step 2021: For any template pair, use one of the query plan templates as the original query plan template and the other query plan template as the target query plan template, and enumerate all possible constraint relationships between the symbols on the two query plan templates to form the constraint set.

4. A database query rewriting rule automatic generation system, characterized in that: include: Module M1: Model the database query plan and query rewrite rules into an enumerable form; Module M2: enumerate the constraints between each pair of query plan templates and generate the first query rewriting rule; The module M2 comprises: Submodule M201: enumerate the query plan templates, select any two of the query plan templates, and obtain at least one template pair; Submodule M202: enumerate the constraints of all template pairs to obtain a constraint set; Submodule M203: weakening the constraint set to generate a second query rewriting rule; Submodule M204: performing deduplication processing on the second query rewriting rule to obtain the first query rewriting rule; The submodule M201 includes: Unit D2011: enumerate the tree structures of all legal query plan templates, where the tree structures of the query plan templates have a unique root node; Unit D2012: For each tree structure, use permutations and combinations to assign a non-input node type to each node of the tree and construct a specific query plan template; Unit D2013: For each tree structure, add an input node to each leaf node, and the number of input nodes is equal to the number of child nodes of the leaf node; Unit D2014: Select any two of the query plan templates to obtain at least one template pair.

5. The database query rewriting rule automatic generation system according to claim 4, characterized in that: The query rewriting rule is modeled as a triple, including: an original query plan template, a target query plan template and a constraint set.

6. The database query rewriting rule automatic generation system according to claim 5, characterized in that: The submodule M202 includes: Unit D2021: For any template pair, one of the query plan templates is used as the original query plan template and the other query plan template is used as the target query plan template, and all possible constraint relationships between the symbols on the two query plan templates are enumerated to form the constraint set.

Images