Method and system for generating sql rewrite rules for sql optimization based on cases

Abstract

The application provides a method and system for generating SQL rewriting rules for SQL optimization based on cases, comprising the following steps: S1, parsing an input SQL query statement to generate a logical plan; S2, generalizing the logical plan pair until generalization cannot be continued, and using the equivalent logical plan pair that cannot be generalized as a rewriting rule; and S3, verifying the equivalence of a pair of logical plans. The application proposes a method for generating rewriting rules based on equivalent SQL statements, so that users can quickly discover potential rules according to a pair of SQL statements, avoid enumerating all possible SQL statements or logical plans, and greatly improve the efficiency of discovering rewriting rules.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of SQL optimization, in particular, to a method and system for generating SQL rewriting rules based on cases for SQL optimization. BACKGROUND

[0002] Relational databases are often used by Internet applications to store and manage data, and Structured Query Language (SQL) is usually used to add, delete, modify and query data. The SQL optimization method based on rewriting rules is an important method in the field of databases, which uses rewriting rules to rewrite SQL statements or their corresponding logical plans, thereby reducing the execution time of SQL statements, and can be used in all scenarios that need to execute SQL statements. However, finding rewriting rules is a fundamental problem in the field of databases. Therefore, database developers have always been looking for more effective methods for discovering SQL rewriting rules.

[0003] There are mainly two methods for discovering rewriting rules: database experts manually write rewriting rules, and automatically discover rewriting rules through brute force enumeration. In the first method, database experts summarize a number of rewriting rules based on their understanding and experience of SQL and its execution methods. The disadvantage of this method is that it requires a lot of manpower to discover and summarize rewriting rules, and is limited by human thinking and experience, which can easily miss potential rules. In the second method, the representative rule discovery tool WeTune enumerates logical plans with a certain range of operator numbers, and calls a verifier to verify the equivalence between these plans. Equivalent plans constitute rewriting rules. This process of discovering rewriting rules is fully automatic and does not require human intervention, but it has several disadvantages: first, it is difficult to exhaust logical plans with a large number of operators, because the number of possible logical plans increases exponentially with the number of operators; second, the enumerated plans do not contain specific aggregation functions and predicates, which limits the discovery of rewriting rules related to specific aggregation functions and predicates; finally, there may be a situation where one rule can be completely replaced by another rule, which will unnecessarily increase the number of rewriting rules, thus increasing the rewriting time and the time from input to execution of SQL statements. Therefore, previous work has certain limitations.

[0004] Patent document CN117194473A discloses a SQL statement processing method, a database system, an equipment and a storage medium, and specifically discloses obtaining a first SQL statement for querying target data, a timestamp field to be segmented for statistics and a segmentation duration; rewriting the first SQL statement according to a set rewriting rule to obtain a second SQL statement for further counting the number of rows according to time periods for the target data; executing the second SQL statement to obtain a corresponding segmented time period statistics result; wherein the rewriting rule includes a selection clause modification sub-rule and a grouping clause modification sub-rule; the selection clause modification sub-rule includes adding a first field and a second field, and retaining the same fields in a selection clause of the first SQL statement as in a grouping clause of the first SQL statement; and the grouping clause modification sub-rule includes adding the first field. The comparative document cannot efficiently and automatically find rewriting rules involving a large number of operators or specific aggregation functions and predicates, and remove replaceable rules. SUMMARY

[0005] In view of the defects in the prior art, the purpose of the present application is to provide a method and system for generating SQL rewriting rules based on cases for SQL optimization.

[0006] According to the method for generating SQL rewriting rules based on cases for SQL optimization provided by the present application, the method comprises the following steps:

[0007] Step S1: parsing an input SQL query statement to generate a logical plan;

[0008] Step S2: generalizing the logical plan pair until generalization cannot continue, and using the equivalent logical plan pair that cannot be generalized as a rewriting rule;

[0009] Step S3: verifying the equivalence of a pair of logical plans.

[0010] Preferably, in the step S1:

[0011] parsing an input pair of SQL query statements to generate a pair of logical plans;

[0012] wherein each SQL statement corresponds to a logical plan, and the logical plan is a representation form for describing the operation flow of the SQL query, and includes a series of operators.

[0013] Preferably, in the step S2:

[0014] For any pair of logical plans, a number of pairs of logical plans are generated by predefined generalization rules, the generated pairs of logical plans should be able to completely replace the input pair of logical plans as rewrite rules; the equivalence of the generated pairs of logical plans is verified by the verifier in step S3, the inequivalent ones are discarded, and the equivalent ones are taken as new inputs to execute the generalization step again; if a pair of logical plans does not generate new equivalent logical plans, the pair of logical plans is collected as new rewrite rules.

[0015] Preferably, in the step S2:

[0016] wherein the generalization rule is a rule for generating a number of pairs of logical plans from a pair of logical plans; if a pair of logical plans is regarded as rewrite rules, then the set of SQL statements that can be rewritten by each pair of logical plans generated after applying the generalization rule is a subset of the set of SQL statements that can be rewritten by the pair of logical plans before applying the generalization rule; the equivalence of the pair of logical plans is verified by the verifier in step S3.

[0017] Preferably, in the step S3:

[0018] The verification of the equivalence of the pair of logical plans produces the same result when executed on any database instance.

[0019] The possible output results are equivalence and inequivalence, where equivalence means that the verifier proves that the input logical plans are equivalent, and inequivalence means that the verifier cannot prove that the input logical plans are equivalent; the equivalence and inequivalence of the logical plans mentioned in the generalizer correspond to the two output results of the verifier, respectively.

[0020] According to the system for generating SQL rewrite rules based on cases for SQL optimization provided by the application, the system comprises:

[0021] Module M1: parsing the input SQL query statements to generate logical plans;

[0022] Module M2: generalizing the pair of logical plans until generalization cannot continue, and using the equivalent pair of logical plans that cannot be generalized as rewrite rules;

[0023] Module M3: verifying the equivalence of a pair of logical plans.

[0024] Preferably, in the module M1:

[0025] parsing the input pair of SQL query statements to generate a pair of logical plans;

[0026] wherein each SQL statement corresponds to a logical plan, and the logical plan is a representation form for describing the operation flow of the SQL query, containing a series of operators.

[0027] Preferably, in the module M2:

[0028] For any pair of logical plans, a number of pairs of logical plans are generated by predefined generalization rules, the generated pairs of logical plans should be able to completely replace the input pair of logical plans as rewrite rules; among the generated pairs of logical plans, the inequivalent ones are discarded, the equivalent ones are taken as new inputs, and the generalization step is executed again; if a pair of logical plans does not generate new equivalent logical plans, the pair of logical plans is collected as new rewrite rules.

[0029] Preferably, in the module M2:

[0030] The generalization rules are rules for generating a number of pairs of logical plans from a pair of logical plans; if a pair of logical plans is regarded as rewrite rules, then the set of SQL statements that can be rewritten by each pair of logical plans generated after applying the generalization rules is a subset of the set of SQL statements that can be rewritten by the pair of logical plans before applying the generalization rules; the equivalence of the pair of logical plans is verified by the verifier.

[0031] Preferably, in the module M3:

[0032] The verification of whether the pair of logical plans is equivalent produces the same result when executed on any database instance.

[0033] The possible output results are equivalence and inequivalence, where equivalence means that the verifier proves that the input logical plans are equivalent, and inequivalence means that the verifier cannot prove that the input logical plans are equivalent; the equivalence and inequivalence of the logical plans mentioned in the generalizer correspond to the two output results of the verifier here. Any verifier that can prove the equivalence of logical plans can be used, including existing verifiers.

[0034] Compared with the prior art, the present application has the following beneficial effects:

[0035] 1. The present application proposes a method for generating rewrite rules based on equivalent SQL statements, which enables users to quickly discover potential rules from a pair of SQL statements, avoiding the enumeration of all possible SQL statements or logical plans, and greatly improving the efficiency of discovering rewrite rules;

[0036] 2. The rule discovery method proposed by the present application can discover rules without being limited by the number of operators, enhancing the ability to discover rewrite rules containing more operators;

[0037] 3. The rule discovery method proposed by the present application can discover rewrite rules involving specific aggregation functions or predicates, and its discovery ability depends on the proof ability of the verification tool used;

[0038] 4、The rule discovery method can obtain rules with wider application scope according to known rules, effectively remove rules that can be completely replaced by other rules, make the discovered rewriting rule set more concise and more universal, thereby enhancing the SQL optimization capability while reducing the time delay of SQL optimization as much as possible. BRIEF DESCRIPTION OF DRAWINGS

[0039] Other features, objects, and advantages of the application will become more apparent from the following detailed description of non-limiting embodiments thereof, when read in conjunction with the accompanying drawings:

[0040] Figure 1 is a general architecture schematic diagram of the present application;

[0041] Figure 2 is a specific implementation schematic diagram of a generalizer and verifier of the present application. DETAILED DESCRIPTION

[0042] The present application will be described in detail below with specific embodiments. The following embodiments will help those skilled in the art to further understand the present application, but do not limit the present application in any form. It should be pointed out that, for those skilled in the art, without departing from the concept of the present application, a number of changes and improvements can be made. These all belong to the protection scope of the present application.

[0043] Example 1:

[0044] The present application provides a method for generating SQL rewriting rules based on cases for SQL optimization, comprising: a parsing step of parsing the input SQL query statement to generate a logical plan; a generalization step of continuously generalizing the logical plan pair until it cannot be generalized, and using the equivalent logical plan pair that cannot be generalized as rewriting rules; a verification step of verifying the equivalence of a pair of SQL query statements. The present application provides a method for automatically discovering rewriting rules through cases, which significantly enhances the optimization capability of the SQL optimization engine based on rewriting rules.

[0045] According to the method for generating SQL rewriting rules based on cases for SQL optimization provided by the present application, as shown in Figures 1-2 , comprising:

[0046] Step S1: parsing the input SQL query statement to generate a logical plan;

[0047] Specifically, in the step S1:

[0048] parsing the input pair of SQL query statements to generate a pair of logical plans;

[0049] Each SQL statement corresponds to a logical plan, and the logical plan is a representation form for describing the operation flow of the SQL query, and includes a series of operators.

[0050] Step S2: generalizing the logical plan pair until generalization cannot continue, and using the equivalent logical plan pair that cannot be generalized as a rewrite rule;

[0051] Specifically, in the step S2:

[0052] For any pair of logical plans, a plurality of pairs of logical plans are generated through a predefined generalization rule, and the generated pair of logical plans should be able to completely replace the input pair of logical plans as a rewrite rule; among the generated pair of logical plans, the non-equivalent ones are discarded, and the equivalent ones are taken as new inputs to execute the generalization step again; if a pair of logical plans does not generate a new equivalent logical plan, the pair of logical plans is collected as a new rewrite rule.

[0053] Specifically, in the step S2:

[0054] The generalization rule is a rule for generating a plurality of pairs of logical plans from a pair of logical plans; if a pair of logical plans is regarded as a rewrite rule, then the set of SQL statements that can be rewritten by each pair of logical plans generated after applying the generalization rule is a subset of the pair of logical plans before applying the generalization rule; the equivalence of the pair of logical plans is verified by the verifier.

[0055] Step S3: verifying the equivalence of a pair of logical plans.

[0056] Specifically, in the step S3:

[0057] The verification is whether the pair of logical plans is equivalent, and executing on any database instance produces the same result.

[0058] The possible output results are equivalence and non-equivalence, where equivalence means that the verifier proves that the input logical plan is equivalent, and non-equivalence means that the verifier cannot prove that the input logical plan is equivalent; the equivalence and non-equivalence of the logical plan mentioned in the generalizer correspond to the two output results of the verifier here.

[0059] Embodiment 2:

[0060] Embodiment 2 is a preferred example of Embodiment 1, which more specifically illustrates the present application.

[0061] The application further provides a system for generating SQL rewriting rules for SQL optimization based on cases, which can be implemented by executing the process steps of the method for generating SQL rewriting rules for SQL optimization based on cases, i.e., the method for generating SQL rewriting rules for SQL optimization based on cases can be understood as the preferred embodiment of the system for generating SQL rewriting rules for SQL optimization based on cases.

[0062] The system for generating SQL rewriting rules for SQL optimization based on cases provided by the application comprises:

[0063] Module M1: parsing input SQL query statements to generate logical plans;

[0064] Specifically, in the module M1:

[0065] An input pair of SQL query statements is parsed to generate a pair of logical plans;

[0066] Each SQL statement corresponds to a logical plan, and the logical plan is a representation form for describing the operation flow of the SQL query and comprises a series of operators.

[0067] Module M2: generalizing the pair of logical plans until generalization cannot be continued, and using the equivalent logical plan pair that cannot be generalized as a rewriting rule;

[0068] Specifically, in the module M2:

[0069] For any pair of logical plans, a plurality of pairs of logical plans are generated through a predefined generalization rule, and the generated pair of logical plans should be able to completely replace the input pair of logical plans; among the generated pair of logical plans, the non-equivalent ones are discarded, and the equivalent ones are taken as new inputs to execute the generalization step again; if a pair of logical plans does not generate a new equivalent logical plan, the pair of logical plans is collected as a new rewriting rule.

[0070] Specifically, in the module M2:

[0071] The generalization rule is a rule for generating a plurality of pairs of logical plans from a pair of logical plans; if a pair of logical plans is regarded as a rewriting rule, then the set of SQL statements that can be rewritten by each pair of logical plans generated after the generalization rule is applied is a subset of the pair of logical plans before the generalization rule is applied; the equivalence of the pair of logical plans is verified by the verifier.

[0072] Module M3: verifying the equivalence of a pair of logical plans.

[0073] Specifically, in the module M3:

[0074] The verification logic plan is equivalent to whether the same result is executed on any database instance.

[0075] The possible output results are equivalence and non-equivalence, where equivalence means that the verifier proves that the input logic plan is equivalent, and non-equivalence means that the verifier cannot prove that the input logic plan is equivalent; the logic plan equivalence and non-equivalence mentioned in the generalizer correspond to the two output results of the verifier.

[0076] Embodiment 3:

[0077] Embodiment 3 is a preferred example of embodiment 1, to more specifically illustrate the present application.

[0078] The embodiment of the present application provides a system for generating SQL rewriting rules for SQL optimization based on cases, generalizes a pair of SQL statements and submits them to a verifier for verification of equivalence, thereby obtaining rewriting rules. Compared with the prior art, the present application can generate rewriting rules involving more operators and specific aggregation functions and predicates, and remove redundant rewriting rules, solving the problems that existing work is difficult to exhaust rewriting rules involving more operators and specific aggregation functions and predicates, and difficult to remove redundant rewriting rules, greatly enhancing the ability of the rule-based SQL optimization engine.

[0079] The embodiment of the present application provides an automated system for implementing a method of generating SQL rewriting rules for SQL optimization based on cases, parses specific pairs of SQL statements into pairs of logic plans and generalizes them, generates a number of pairs of logic plans, and calls a verifier to check the equivalence of the pairs of logic plans; the equivalent logic plan pair that cannot be generalized is the rewriting rule. Referring to Figure 1 The modules that make up the system include:

[0080] The parser: parses the input SQL query statement to generate a logic plan; wherein each SQL statement corresponds to a logic plan, and the logic plan is a representation form for describing the operation flow of the SQL query, including a series of operators.

[0081] Generalizer: For any pair of logical plans, generate several pairs of logical plans by predefined generalization rules, discard the inequivalent ones, and take the equivalent ones as new inputs to execute this generalization step again; if no new logical plan is generated from a pair of logical plans, then take this pair of logical plans as a new rewriting rule. Among them, the generalization rule is the rule for generating several pairs of logical plans from a pair of logical plans; if a pair of logical plans is regarded as a rewriting rule, then the set of SQL statements that can be rewritten by each pair of logical plans generated after applying the generalization rule should be a subset of the set of SQL statements that can be rewritten by the pair of logical plans before applying the generalization rule; the equivalence of the pair of logical plans is verified by the verifier.

[0082] Verifier: Verify whether a pair of logical plans is equivalent, that is, executing on any database instance produces the same result. Its possible output results are two: "equivalent" and "inequivalent", where "equivalent" means that the verifier proves that the input logical plans are equivalent, and "inequivalent" means that the verifier cannot prove that the input logical plans are equivalent; the "equivalent" and "inequivalent" of the logical plans mentioned in the generalizer correspond to the two output results of the verifier here.

[0083] The complete implementation process of the present application is described as follows:

[0084] The present system receives a pair of SQL statements and their context as input, wherein the context is the definition of the database tables involved, including the column names, data types and integrity constraints of the tables.

[0085] Parser: Parse the SQL statements, first convert each SQL statement into an abstract syntax tree according to the SQL syntax, the abstract syntax tree represents the syntax structure of the SQL statement, which is convenient for subsequent analysis; then analyze the semantics implied by the abstract syntax tree, and generate a logical plan according to the semantics of the abstract syntax tree, one logical plan is generated for each abstract syntax tree, and the logical plan represents the specific flow of executing the SQL statement.

[0086] Generalizer: Input a pair of logical plans, generalize them to generate several pairs of logical plans, and collect potential rewriting rules among them. Specifically, referring to Figure 2 As shown, the generalizer is composed of several modules, among which the generalization queue is used to store the pairs of logical plans waiting for generalization; the controller is responsible for flow control; abstract replacement and symbol renaming are two generalization rules for generalizing a pair of logical plans and generating several pairs of logical plans. In the initial state, the generalization queue only stores the input pair of logical plans. The internal flow of the generalizer is controlled by the controller, and a complete generalization flow is implemented by the following steps in turn:

[0087] 1. If the generalization queue is not empty, take a pair of logical plans from the generalization queue. Otherwise, this generalization flow is terminated.

[0088] 2. Apply abstract substitution and symbol renaming to the pair of logical plans, respectively, to obtain a number of pairs of logical plans. Specifically, abstract substitution replaces specific sub-plans or predicates in the two logical plans in whole or in part with abstract symbols; symbol renaming replaces a number of identical symbols in the two logical plans with different symbols, where a symbol includes a table name and a column name.

[0089] To explain abstract substitution and symbol renaming, first the form of a logical plan needs to be introduced. Take the logical plan project(a0, filter(union(t1, t2), a0=a1)) as an example, which represents the following execution flow: first take database tables t1 and t2, apply the union operator to them to produce a new table, then apply the filter operator (with parameter a0=a1) to the resulting table to produce a new table, and finally apply the project operator (with parameter a0) to the resulting table to produce the final result. Here project, filter, and union are operators, each of which represents an operation, the input of an operator is a number of tables (e.g., t1 and t2 in union(t1, t2), and union(t1, t2) in filter(union(t1, t2), a0=a1)), and some operators require additional parameters (e.g., a0=a1 in filter(union(t1, t2), a0=a1) is an additional parameter of filter, which indicates that filter selects rows that satisfy the condition a0=a1 from its input to form a new table as output), and the output of an operator is a table.

[0090] To explain abstract substitution, as an example, assume that the current pair of logical plans is project(a0, filter(union(t1, t2), a0=a1)) and project(a1, filter(union(t1, t2), a0=a1)), where union(t1, t2) appears in both logical plans, and thus can be replaced with a same symbol r to produce a new pair of logical plans project(a0, filter(r, a0=a1)) and project(a1, filter(r, a0=a1)). The process of replacing identical sub-plans (e.g., union(t1, t2) in the example) or predicates (e.g., a0=a1 in the example, which is not replaced in this example) in a pair of logical plans with a same symbol is called abstract substitution. Since r can represent any table, and the output of union(t1, t2) is a table, the original pair of logical plans can be regarded as a special case of the new pair of logical plans when r=union(t1, t2); the new pair of logical plans is more general as a rewrite rule.

[0091] To explain the symbol renaming, as an example, assume that the current pair of logical plans is project(r.a0, join(r, r, r.a0 = r.a1)) and project(r.a1, join(r, r, r.a0 = r.a1)), where the symbol r represents an arbitrary table and all occurrences of r represent the same table. A different symbol can be used to replace a portion of the occurrences of r, resulting in a new pair of logical plans project(r.a0, join(r, r0, r.a0 = r0.a1)) and project(r0.a1, join(r, r0, r.a0 = r0.a1)). The process of replacing a portion of the occurrences of the same symbol in a pair of logical plans with another symbol is called conformant renaming. Since r0 and r can represent the same table or two different tables, the original pair of logical plans can be considered as a special case of the new pair of logical plans when r0 = r; the new pair of logical plans is more general as rewrite rules.

[0092] 3. The pair of logical plans obtained in the previous step is input to the verifier, which judges whether the two are equivalent. If no pair of logical plans is generated in the previous step or the pair of logical plans generated in the previous step is judged to be all inequivalent in this step, it is considered that the pair of logical plans obtained in the first step cannot be generalized any more, and the pair of logical plans is output to the rewrite rule set, and the execution is started from the first step again; otherwise, the pair of logical plans judged to be equivalent in this step is inserted into the generalization queue.

[0093] The verifier: input a pair of logical plans and the context, and judge whether they are equivalent. Specifically, as shown in FIG. 2, the verifier is composed of two modules, wherein the verifier front end is used to convert a pair of logical plans and the context into a pair of SQL statements and the definition of the tables involved; the SQL verifier is used to prove the equivalence of a pair of SQL statements under the definition of the given tables. The SQL verifier can use existing SQL equivalence verification tools. Figure 2

[0094] ​The verifier front end constructs a pair of SQL statements and the definition of the tables involved according to the input pair of logical plans and the context. The conversion of the logical plan to the SQL statement can start from the bottommost table symbol and convert the SQL statement layer by layer from bottom to top, so as to complete the conversion of the entire logical plan; this conversion process does not need to consider the execution efficiency of the converted SQL statement, but only needs to ensure that the conversion process does not change the query result in all cases. The column names and data types of the table are directly obtained from the context; for the new symbol generated in the generalization process, the column names and data types of the table represented by the symbol can be inferred from the table (database table or operator output) replaced by the symbol. The integrity constraints of the table are directly obtained from the integrity constraints in the context, and the table represented by the new symbol generated in the generalization process has no integrity constraints, which will not affect the reliability of the verifier.

[0095] Finally, the rewriting rule set output by the generalizer is the final output of the system.

[0096] The present application generates one rewriting rule according to two pairs of equivalent queries, both of which can reduce the query delay by at most 34%. One of the rewriting rules can optimize SQL query statements that cannot be optimized by the SQL optimization engine of the existing database system SQL Server in the industry.

[0097] Those skilled in the art know that, in addition to implementing the system provided by the present application and each device, module and unit thereof in the form of pure computer readable program code, the same function can also be achieved by logically programming the method steps to make the system provided by the present application and each device, module and unit thereof in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers and embedded microcontrollers. Therefore, the system provided by the present application and each device, module and unit thereof can be considered as a hardware component, and the devices, modules and units included therein for achieving various functions can also be considered as structures within the hardware component; the devices, modules and units for achieving various functions can also be considered as both software modules for implementing methods and structures within hardware components.

[0098] The specific embodiments of the present application are described above. It should be understood that the present application 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 essential content of the present application. In the case of no 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 generating SQL rewriting rules based on case studies for SQL optimization, characterized in that, include: Step S1: Parse the input SQL query statement to generate a logical plan; Step S2: Generalize the logical plan pair until it can no longer be generalized, and use the equivalent logical plan pair that cannot be generalized as the rewrite rule; Step S3: Verify the equivalence of a pair of logical plans; In step S2: For any pair of logical plans, several pairs of logical plans are generated through predefined generalization rules. The generated pairs of logical plans, as rewriting rules, should be able to completely replace the input pairs of logical plans. Among the generated pairs of logical plans, those that are not equivalent are discarded, and those that are equivalent are used as new inputs. This generalization step is then performed again. If a pair of logical plans does not generate a new equivalent logical plan, then collect the pair of logical plans as a new rewriting rule; In step S2: Among them, the generalization rule is the rule for generating several pairs of logical plans based on a pair of logical plans; if a pair of logical plans is regarded as a rewriting rule, then the set of SQL statements that can be rewritten by each pair of logical plans generated after applying the generalization rule is a subset of the logical plan pair before applying the generalization rule. The equivalence of logical plan pairs is verified by the validator.

2. The method for generating SQL rewriting rules based on cases for SQL optimization according to claim 1, characterized in that, In step S1: Parse the input pair of SQL queries to generate a pair of logical plans; Each SQL statement corresponds to a logical plan, which is a representation of the SQL query operation flow and contains a series of operators.

3. The method for generating SQL rewriting rules based on cases for SQL optimization according to claim 1, characterized in that, In step S3: Verify whether the logical plans are equivalent; executing them on any database instance will produce the same result. The possible outputs are equivalent and not equivalent. Equivalent means that the verifier has proven the input logical plan to be equivalent, while not equivalent means that the verifier cannot prove the input logical plan to be equivalent. The logical plan equivalence and not equivalent mentioned in the generalizer correspond to the two outputs of the verifier here.

4. A system for generating SQL rewriting rules based on case studies for SQL optimization, characterized in that, include: Module M1: Parses the input SQL query statement to generate a logical plan; Module M2: Generalize logical plan pairs until generalization can no longer be achieved, and use the equivalent logical plan pairs that cannot be generalized as rewriting rules; Module M3: Verifies the equivalence of a pair of logical plans; In module M2: For any pair of logical plans, several pairs of logical plans are generated through predefined generalization rules. The generated pairs of logical plans, as rewriting rules, should be able to completely replace the input pairs of logical plans. Among the generated pairs of logical plans, those that are not equivalent are discarded, and those that are equivalent are used as new inputs. This generalization step is then performed again. If a pair of logical plans does not generate a new equivalent logical plan, then collect the pair of logical plans as a new rewriting rule; In module M2: Among them, the generalization rule is the rule for generating several pairs of logical plans based on a pair of logical plans; if a pair of logical plans is regarded as a rewriting rule, then the set of SQL statements that can be rewritten by each pair of logical plans generated after applying the generalization rule is a subset of the logical plan pair before applying the generalization rule. The equivalence of logical plan pairs is verified by the validator.

5. The system for generating SQL rewriting rules based on cases for SQL optimization according to claim 4, characterized in that, In module M1: Parse the input pair of SQL queries to generate a pair of logical plans; Each SQL statement corresponds to a logical plan, which is a representation of the SQL query operation flow and contains a series of operators.

6. The system for generating SQL rewriting rules based on cases for SQL optimization according to claim 4, characterized in that, In module M3: Verify whether the logical plans are equivalent; executing them on any database instance will produce the same result. The possible outputs are equivalent and not equivalent. Equivalent means that the verifier has proven the input logical plan to be equivalent, while not equivalent means that the verifier cannot prove the input logical plan to be equivalent. The logical plan equivalence and not equivalent mentioned in the generalizer correspond to the two outputs of the verifier here.

Images