A self-updating SQL query rewriting method

Abstract

The application discloses a self-updating SQL query rewriting method and relates to the technical field of SQL optimization. The method comprises the following steps: extracting candidate rewriting rules from a current SQL query statement rewriting success case set; jointly constructing an SQL query statement rewriting rule library by using the candidate rewriting rules which pass through equivalence verification and original rewriting rules of a database optimization system; performing online rewriting rule matching on a SQL query statement to be rewritten based on the SQL query statement rewriting rule library, and performing equivalence rewriting according to the matched rewriting rules; collecting execution feedback information before and after the SQL query statement is rewritten, screening out successful SQL query statement rewriting cases which meet preset conditions, and adding the successful SQL query statement rewriting cases to the SQL query statement rewriting success case set; and repeatedly performing the above process by taking the updated SQL query statement rewriting success case set as the current SQL query statement rewriting success case set, so that the SQL query statement rewriting rule library is updated.

Description

Technical Field

[0001] This invention relates to the field of SQL optimization technology, specifically to a self-updating SQL query rewriting method. Background Technology

[0002] In the data-driven era, Database Management Systems (DBMS) have become core tools for data storage and management across various industries, including scientific research, finance, and healthcare. In practical applications, a key application requirement is achieving fast queries while maintaining accuracy when dealing with complex schemas and massive amounts of data. As business systems expand, query requests exhibit high concurrency and low latency characteristics. Furthermore, SQL statements are often automatically generated and assembled using ORM (Object-Relational Mapping) or dynamic SQL components. The structure of SQL query statements frequently adjusts with business logic, making SQL query performance susceptible to redundancy in the SQL query structure and the coverage of SQL query optimization strategies. Therefore, query optimization remains a crucial research topic in key database technologies.

[0003] SQL query rewriting, as a core method that directly improves the execution performance of SQL queries without changing the original query intent, is an important component of SQL query optimization. Most existing database systems employ rule-based query rewriting optimization, building rewriting rule bases through manual summarization and accumulation by database experts. These rules eliminate query redundancy and improve execution efficiency based on equivalent transformation rules. In business scenarios where SQL statements are dynamically generated, the overlapping of filtering conditions or subquery conditions across different modules can easily lead to structural redundancy such as duplicate predicates and redundant subqueries. This places a higher emphasis on the rule base's ability to identify and eliminate redundancy. Core optimization techniques include subquery rewriting, join optimization, aggregation rewriting, and predicate optimization.

[0004] The rule-based query rewriting method described above has significant limitations: the statically predefined rule base is incompatible with dynamic data environments and complex application scenarios; the coverage and triggering priority of the rule base are determined during the design phase; when the SQL query structure changes frequently with business logic iterations, or when the SQL query contains complex structures such as multi-table joins and multi-level nesting, SQL query rewriting can only perform equivalent rewriting within a local scope limited by a fixed set of rewriting rules, making it difficult to continuously cover more optimizable structures and easily leading to limited rewriting space or getting trapped in local optima. Therefore, how to break the limitations of a fixed rule set, enable query rewriting rules to be continuously updated and maintain availability as query patterns change, improve the application scope and optimization effect of SQL query rewriting, and meet the query optimization needs in dynamic and complex business scenarios remains an important problem that needs to be solved. Summary of the Invention

[0005] In view of the shortcomings of the prior art, the purpose of this invention is to provide a self-updating SQL query rewriting method, particularly suitable for scenarios with dynamically generated SQL query loads. This method can automatically learn reusable SQL rewriting rules, or "principles," from historical successful SQL query rewriting cases, and generate, verify, and update an SQL query rewriting rule base. It eliminates redundant structures such as duplicate IN subqueries and duplicate predicates without requiring continuous manual maintenance of the SQL query rewriting rules, achieving online optimization and continuous updates. The "principles" are natural language summaries, derived from multiple successful SQL query rewriting cases—cases where rewriting improved execution performance—that can be understood and interpreted by humans. These summaries define "what SQL query structures can be equivalently rewritten under what conditions," guiding the subsequent automatic generation of specific SQL query rewriting rule templates and constraints.

[0006] The technical solution of this invention is:

[0007] A self-updating SQL query rewriting method includes: extracting candidate rewriting rules for SQL queries from a set of successful SQL query rewriting cases; constructing an SQL query rewriting rule library by combining the candidate rewriting rules that pass equivalence verification with the original rewriting rules of the database optimization system; performing online rewriting rule matching on the SQL query to be rewritten based on the SQL query rewriting rule library, and performing equivalent rewriting according to the matched rewriting rules; collecting execution feedback information before and after the SQL query rewriting, and filtering out SQL query rewriting cases that meet preset conditions based on the execution feedback information, and defining the SQL query rewriting cases that meet the preset conditions as successful SQL query rewriting cases; adding the filtered successful SQL query rewriting cases to the set of successful SQL query rewriting cases to update the current set of successful SQL query rewriting cases; and repeating the above process with the updated set of successful SQL query rewriting cases as the current set of successful SQL query rewriting cases to update the SQL query rewriting rule library.

[0008] Optionally, according to the SQL query rewriting method, the method further includes: extracting candidate rewriting rules for SQL query statements from the current successful SQL query statement rewriting case set, which includes: extracting executable SQL query statement rewriting rule templates and corresponding constraints from the current successful SQL query statement rewriting case set; combining the SQL query statement rewriting rule templates and corresponding constraints together to form candidate rewriting rules for SQL query statements.

[0009] Optionally, according to the SQL query rewriting method, the method further includes: the method of extracting executable SQL query rewriting rule templates and corresponding constraints from the current SQL query rewriting success case set includes: based on the structural differences and performance changes of the SQL query statements before and after rewriting in each rewriting case in the current SQL query rewriting success case set, extracting reusable SQL query rewriting principles described in natural language; translating the SQL query rewriting principles into executable SQL query rewriting rule templates and constraints; the content of the SQL query rewriting principles includes at least a natural language description of the structural changes before and after rewriting of the SQL query statement to be rewritten and a natural language description of the limiting conditions required for the structural changes to be valid.

[0010] Optionally, according to the SQL query rewriting method, the method further includes: based on the structural differences and performance changes of the SQL query statements before and after rewriting in each rewriting case in the current successful SQL query rewriting case set, extracting reusable SQL query rewriting principles described in natural language, including:

[0011] Step 2.1: Based on the keywords of the SQL query statement, perform clause segmentation on the original SQL query statement and the rewritten SQL query statement of each case in the successful SQL query statement rewriting case set to obtain the clause set of the SQL query statement;

[0012] Step 2.2: Based on the set of clauses of the SQL query statement, by parsing the expressions and nesting structures of each clause of each SQL query statement, structural features that represent the key semantic structure of the SQL query statement are obtained; the structural features include at least the main table information, the set of selection expressions, the set of filtering predicates, and the subquery structure information;

[0013] Step 2.3: By establishing a symbol mapping relationship, uniform symbol replacement is performed on the table names, column names, aliases and constants appearing in the structural features of the SQL query statement to obtain the standardized structural features of the SQL query statement;

[0014] Step 2.4: For each case in the successful SQL query rewriting case set, execute steps 2.1 to 2.3 for the original SQL query statement and the rewritten SQL query statement to obtain the normalized structural features of the original SQL query statement and the normalized structural features of the rewritten SQL query statement, i.e., a pair of normalized structural features of the original-rewritten SQL query statement. Perform difference analysis on the normalized structural features of the original-rewritten SQL query statement to obtain the structural difference features and performance change information before and after the SQL query statement rewriting.

[0015] Step 2.5: Analyze the structural differences and performance changes of multiple pairs of original-rewritten SQL query statements, summarize the common structural changes before and after SQL query statement rewriting in successful SQL query statement rewriting cases, as well as the constraints required for these structural changes to take effect, and describe them in natural language to obtain the corresponding reusable SQL query statement rewriting principles described in natural language.

[0016] Optionally, according to the SQL query rewriting method, the method further includes: in step 2.5, using a large language model to analyze the structural differences and performance changes of multiple pairs of original-rewritten SQL query statements, summarizing the common structural changes before and after SQL query statement rewriting in successful SQL query statement rewriting cases, as well as the constraints required for the structural changes to take effect, and describing them in natural language, thereby obtaining the corresponding reusable SQL query statement rewriting principles described in natural language.

[0017] Optionally, according to the SQL query rewriting method, the method further includes: translating the SQL query rewriting principles into executable SQL query rewriting rule templates and constraints, including:

[0018] (I) Read the SQL query rewriting principle, extract the fixed structure and variable slots in the SQL query rewriting principle through slot parsing, and obtain the slotted SQL query rewriting principle template; the slot parsing is to abstract the variable objects in the SQL query rewriting principle into variable slots;

[0019] (II) Based on the SQL query statement rewriting principles and the slotted SQL query statement rewriting principle template, generate an SQL query statement rewriting rule template; the SQL query statement rewriting rule template includes a matching template q_src and a replacement template q_dst; the matching template q_src is used to match the original SQL query statement to determine the structure that the original SQL query statement needs to match when rewriting the SQL query statement; the replacement template q_dst is the target structure after rewriting the structure of the original SQL query statement that successfully matches q_src.

[0020] (III) Generate a set of constraints C that the database optimization system can check based on the constraints in the SQL query rewriting principle, which is used to limit the triggering scope of the SQL query rewriting rule template.

[0021] Optionally, according to the SQL query rewriting method, the method further includes: the slotted SQL query statement rewriting principle template, including pattern_src representing the source structure, indicating the structure that needs to be matched in the original SQL query statement; pattern_dst representing the target structure, indicating the structure obtained after rewriting the structure in the matched original SQL query statement; and slots representing the positions where content needs to be extracted from the specific SQL query statement and filled in.

[0022] Optionally, according to the SQL query rewriting method, the method further includes: the matching template q_src, including match_scope, pattern, type, and bind; the match_scope represents the matching range, used to determine the structure that needs to be matched in the original SQL query statement when rewriting the SQL query statement; the pattern represents the structural element that needs to be matched within the match_scope, which comes from the splitting result of the structure in pattern_src and pattern_dst, and corresponds one-to-one with the normalized structural feature fields of the SQL query statement; the type represents the structure type identifier, used to indicate the category of each structural element in pattern / construct; the bind represents the binding slot instruction, indicating that when the structural element in the pattern successfully matches the specific structure in the original SQL query statement, the corresponding field is extracted from the specific structure of the original SQL query statement and bound to the corresponding slot variable;

[0023] The replacement template q_dst includes rewrite_scope, construct, and use; rewrite_scope indicates the rewrite scope, i.e., in which clause structure of the original SQL query statement the replacement structure is output; construct represents the structure element to be replaced within rewrite_scope, which comes from the slot variable that was bound after the original SQL query statement successfully matched with q_src; use represents a reuse instruction, indicating that when constructing the replacement structure, the value of the slot variable that was bound when matching with q_src is directly referenced.

[0024] Optionally, according to the SQL query rewriting method, the method further includes: continuously rewriting new SQL query statements using rules in the SQL query statement rewriting rule base.

[0025] When a rule can only be applied to a small number of SQL query statements to be rewritten, and the equivalence verification result of the rule shows that it can still pass the equivalence verification under more relaxed constraints, the constraints can be relaxed. That is, without changing the rule template, some constraints can be deleted or weakened, and the rule can be added to the SQL query statement rewriting rule library after passing the equivalence verification again.

[0026] If the equivalence verification of a rewrite rule fails or the negative optimization records in the execution feedback information are concentrated in SQL query statements with a specific structure, then the application conditions of the rewrite rule will be tightened, that is, more stringent constraints will be added, and the restrictions in the corresponding principles will be updated simultaneously to prevent the rule from being triggered in inapplicable scenarios.

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

[0028] (1) This invention automatically learns the relationship between the structure of SQL query statements and the optimization effect from historical successful SQL query statement rewriting cases, and extracts reusable SQL query rewriting rules, thereby reducing the dependence on manually designed SQL query rewriting rules and improving the efficiency of SQL query rewriting rule discovery.

[0029] (2) This invention does not use the large language model to directly rewrite SQL queries. Instead, it uses its powerful natural language understanding and reasoning capabilities to analyze successful cases of query rewriting and extract the principles of natural language expression from them. This can reduce the uncertainty and error risk of SQL statement rewriting effect caused by the illusion problem caused by the direct use of the large language model.

[0030] (3) This invention transforms the learned query rewriting principles into a constrained query rewriting rule generation space, automatically generates a new set of query rewriting rule candidates, and combines equivalence verification to ensure the semantic consistency of the new rules on different database instances, and reduces the cost of manual verification.

[0031] (4) This invention continuously collects the application effects of rules under real query loads and dynamically updates the SQL query rewriting principle set and SQL query rewriting rule base according to execution feedback, so that the query rewriting optimization process can continuously evolve with the changes in query mode and data characteristics. Attached Figure Description

[0032] Figure 1 This is a schematic diagram illustrating the core idea of ​​the self-updating SQL query rewriting method in this embodiment.

[0033] Figure 2 This is a flowchart illustrating the specific implementation of the self-updating SQL query rewriting method in this embodiment. Detailed Implementation

[0034] To facilitate understanding of this application, a more comprehensive description of this application will be provided below with reference to the accompanying drawings.

[0035] Figure 1 This is a schematic diagram illustrating the core idea of ​​the self-updating SQL statement query rewriting method in this implementation method, as shown below. Figure 1 As shown, the core idea of ​​this application is as follows: construct a set of successful SQL query rewriting cases from historical successful SQL query rewriting cases, and use the structural differences and performance changes of the SQL query statements before and after rewriting recorded in each rewriting case in the set of successful SQL query rewriting cases as learning signals. First, extract SQL query rewriting principles that can be read by a natural person and described in natural language to construct a set of SQL query rewriting principles. Then, translate the principles into executable SQL query rewriting rule templates and corresponding constraints. Rule templates and corresponding constraints are combined to form candidate rewriting rules for SQL query statements. These candidate rewriting rules, verified for equivalence, are then combined with existing rules from the database optimization system to construct an SQL query rewriting rule library. Subsequently, as new SQL query statements are continuously rewritten using rules from this library, execution feedback information before and after the rewriting process is continuously collected. New successful SQL query rewriting cases, selected based on this feedback, are added to a successful SQL query rewriting case set. Based on this, the existing SQL query rewriting principle set and rule library are updated, thus achieving continuous self-updating of the SQL query rewriting rule library.

[0036] Figure 2This is a flowchart illustrating a preferred embodiment of a natural language-guided self-updating SQL query rewriting method. The original SQL query in this preferred embodiment is "SELECT o.order_id FROM orders o WHERE o.status = 'PAID' AND o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc) AND o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc)"; the rewritten SQL query is "SELECT o.order_id FROM orders o WHERE o.status = 'PAID' AND o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc)". Figure 2 As shown, the natural language-guided self-updating SQL statement rewriting method includes the following steps:

[0037] Step 1: Based on the SQL rewrite logs and execution feedback information generated by the DBMS during the SQL query optimization phase, collect and filter historically successful SQL query rewrite records to build a set of successful SQL query rewrite cases.

[0038] Each DBMS generates an execution plan for its SQL queries during the optimization process to determine how to execute the queries efficiently. These execution plans can be obtained by calling the DBMS's built-in performance views and logs, such as MySQL's slow query log and EXPLAIN analysis, Oracle's V$SQL, V$SQL_PLAN, and AWR / ASH reports, and PostgreSQL's pg_stat_statements and EXPLAIN ANALYZE.

[0039] This preferred embodiment obtains historical SQL query rewrite records from the DBMS execution plan. The historical SQL query rewrite records contain the original SQL query, the performance information of the original SQL query (estimated execution time and execution cost), the rewritten SQL query, the performance information of the rewritten SQL query (estimated execution time and execution cost), and database context information (table structure, constraints, and statistics).

[0040] After obtaining historical SQL query rewriting records, these records are filtered based on changes in performance information between the original and rewritten SQL queries. For example, filtering can be based on criteria such as "the execution time of the rewritten SQL query is lower than that of the original SQL query" or "the estimated execution cost of the rewritten SQL query is lower than that of the original SQL query," or more specifically, predefined conditions such as "the execution time improvement of the rewritten SQL query reaches a preset threshold (e.g., an improvement exceeding X% or a reduction exceeding Y ms)." In this preferred embodiment, SQL query rewriting records that meet the preset conditions are considered successful SQL query rewriting cases, and a successful SQL query rewriting case set is constructed using these historical successful SQL query rewriting cases.

[0041] The following is a record of a successful SQL query rewriting case from the set of successful SQL query rewriting cases of this preferred embodiment:

[0042] { "case_id": "case_0001",

[0043] "sql_before": "SELECT o.order_id ...",

[0044] "sql_after": "SELECT o.order_id ...",

[0045] "perf_before": { "latency_ms": 120, "cost": 1000},

[0046] "perf_after": { "latency_ms": 85, "cost": 700},

[0047] "schema_context_id": "ctx_001"}

[0048] Wherein, case_id represents the number of the successful SQL query rewriting case provided in this preferred embodiment in the SQL query rewriting successful case set; sql_before represents the original SQL query statement of this preferred embodiment (the complete content is recorded above); sql_after represents the rewritten SQL query statement of this preferred embodiment (the complete content is recorded above); perf_before represents the performance information of the original SQL query statement of this preferred embodiment, and perf_after represents the performance information of the rewritten SQL query statement of this preferred embodiment; schema_context_id is the identifier of the database context information of this preferred embodiment.

[0049] Step 2: Preprocess the SQL queries (including original and rewritten SQL queries) in the latest successful SQL query rewriting case set to obtain the normalized structural features of the SQL queries and perform difference analysis to obtain the structural differences and performance changes of the SQL queries before and after rewriting. In this way, we can summarize the common structural changes of successful SQL query rewriting cases and the constraints required for the structural changes to be valid. We can also obtain reusable SQL query rewriting principles through natural language description and construct a rewriting principle set.

[0050] After obtaining the successful case set of rewritten SQL queries, the case records need to be preprocessed, and structural features to be used for subsequent difference analysis and principle learning need to be extracted. The purpose of preprocessing is to transform the SQL query into a structured representation that can be clustered, compared, and generalized, thereby eliminating the interference that table names, column names, and constant differences in the SQL query may cause to subsequent difference analysis and principle learning if not preprocessed. This step specifically includes:

[0051] Step 2.1: Based on the keywords of the SQL query statement, perform clause segmentation on the original SQL query statement and the rewritten SQL query statement of each case in the successful SQL query statement rewriting case set to obtain the SQL query statement clause set.

[0052] Keywords in SQL query statements mainly refer to reserved words in the SQL query syntax used to delineate the structure of SQL query statements; that is, words used to identify the boundaries of clauses in an SQL query statement. When segmenting clauses, the following words are generally preferred:

[0053] 1. SELECT: Select the beginning of the list;

[0054] 2. FROM: Starts with the data source (table / subquery);

[0055] 3. WHERE: Start filtering conditions;

[0056] 4. GROUP BY: Start grouping;

[0057] 5. HAVING: Filtering begins after grouping;

[0058] 6. ORDER BY: Start sorting;

[0059] 7. LIMIT / OFFSET / FETCH: Start of page break / truncation (may vary by dialect);

[0060] 8. WITH: Starts with a Common Table Expression (CTE) (if it exists);

[0061] For SQL query statements involving set operations or nested structures, keywords also include UNION / UNION ALL / INTERSECT / EXCEPT used to indicate the boundaries of set operations, as well as JOIN and its collocations such as INNER / LEFT / RIGHT / FULL / CROSS JOIN, and the word ON indicating the start of a join condition.

[0062] By identifying the locations of the aforementioned keywords in the SQL query statements, the original and rewritten SQL query statements of each record in the successful SQL query rewriting case set are segmented into structures such as SELECT clause, FROM clause, and WHERE clause, forming a set of clauses for the SQL query statement.

[0063] In this preferred embodiment, the clause segmentation result of the original SQL query statement is as follows:

[0064] {"select": "o.order_id", "from": "orders o",

[0065] "where": "o.status = 'PAID' AND o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc) AND o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc)"}.

[0066] Step 2.2: Based on the set of clauses in the SQL query statement, by parsing the expressions and nested structures of each clause in each SQL query statement, structural features representing the key semantic structure of the SQL query statement are obtained. These structural features include at least the main table information, the set of selection expressions, the set of filtering predicates, and the subquery structure information; when structures such as JOIN / GROUPBY / ORDER BY exist, the structural features also include the set of join tables, the set of join conditions, the set of grouping fields, the set of aggregation expressions, and the set of sorting fields.

[0067] Parsing the clause segmentation results of the original SQL query statement in this preferred embodiment yields the structural features of its key semantic structure. These structural features are presented in a field-based format in this preferred embodiment, and further include the structure and statistical information of the subquery (another SQL query statement nested within an SQL query statement, usually enclosed in parentheses, commonly used in SELECT, FROM, and WHERE clauses), as shown below:

[0068] {"base_table": "orders",

[0069] "base_alias": "o",

[0070] "select_exprs": ["o.order_id"],

[0071] "where_conjuncts": [

[0072] "o.status = 'PAID'",

[0073] "o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc)",

[0074] "o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc)" ],

[0075] "subqueries": [

[0076] {"type": "IN_SUBQUERY", "outer_col": "o.customer_id", "subquery_select": "rc.customer_id", "subquery_from": "risk_customers rc"},

[0077] {"type": "IN_SUBQUERY", "outer_col": "o.customer_id", "subquery_select": "rc.customer_id", "subquery_from": "risk_customers rc"}

[0078] ],

[0079] "counts": { "num_in_subqueries": 2, "num_where_conjuncts": 3}}

[0080] Here, `base_table` represents the main table, which is the most important table name in the FROM clause of the outer query of the SQL query statement. In this example, it is `orders`. `base_alias` represents the alias of the main table, which is the abbreviated name given to the main table in the outer query of the SQL query statement. It is used to reference columns of this table in the SQL query statement. In this example, it is `o`, so `o.order_id` represents the `order_id` column of the `orders` table. `select_exprs` represents the list of selection expressions, which is the set of fields or expressions to be output in the SELECT clause. In this example, there is only one output field, `o.order_id`. `where_conjuncts` represents the list of conjunctive conditions in the WHERE clause, which is the list after breaking down the top-level conditions connected by AND in the WHERE clause. In this example, it is broken down into 3 conditions: `o.status='PAID'`, and two identical conditions `o.customer_id IN (SELECT …)`. `subqueries` represents the list of subqueries, which is the subqueries that appear in the WHERE conditions. Their types and key structures are recorded separately to facilitate subsequent comparisons of whether structural features are repeated or can be merged. `type` specifies the subquery type, indicating the structure in which the subquery appears. In this example, it's `IN_SUBQUERY`, meaning "a certain column IN (subquery result set)". `outer_col` represents the outer column, the field being evaluated before the IN subquery; in this example, it's `o.customer_id`. `subquery_select` represents the subquery output column, the column output by the SELECT clause of the subquery, used for the IN condition with `outer_col`; in this example, it's `rc.customer_id`. `subquery_from` indicates the data source of the subquery, i.e., the table and its alias in the FROM clause of the subquery; in this example, they are `risk_customers` and `rc`, respectively. `counts` represents statistical information, a simple count of the structure of the SQL query statement, used to determine if there are duplicate structures or changes in complexity. In this example, there's `num_in_subqueries` representing the number of subqueries, and `num_where_conjuncts` representing the number of top-level AND conditions in the WHERE clause.

[0081] Step 2.3: Based on the structural features of the key semantic structure of the SQL query statement, establish symbol mapping relationships and perform symbol substitution to standardize the table names, column names, aliases and constants involved in the structural features of the SQL query statement, thereby obtaining the standardized structural features of the SQL query statement.

[0082] To reduce the impact of specific table names, column names, aliases, and constants in SQL query statements on subsequent principle learning and rule generation, this invention uses the structural features obtained in step 2.2 to represent the key semantic structure of SQL query statements, and employs symbol renaming to uniformly replace the table names, column names, aliases, and constants that appear therein.

[0083] Taking the result of step 2.2 as an example, the table name is mapped as follows: orders → t1, risk_customers → t2; the alias is mapped as follows: o → a1, rc → a2; the column name is mapped as follows: o.order_id → a1.c1, o.status → a1.c2, o.customer_id → a1.c3, rc.customer_id → a2.c4; the constant is mapped as follows: 'PAID' → 'L1'.

[0084] Based on the above symbol mapping table, the structural features obtained in step 2.2 are replaced with symbols to obtain normalized structural features.

[0085] The structural features before replacement were:

[0086] {"base_table": "orders",

[0087] "base_alias": "o",

[0088] "select_exprs": ["o.order_id"],

[0089] "where_conjuncts": [

[0090] "o.status = 'PAID'",

[0091] "o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc)",

[0092] "o.customer_id IN (SELECT rc.customer_id FROM risk_customers rc)"

[0093] ],

[0094] "subqueries": [

[0095] {"type": "IN_SUBQUERY", "outer_col": "o.customer_id", "subquery_select": "rc.customer_id", "subquery_from": "risk_customers rc"},

[0096] {"type": "IN_SUBQUERY", "outer_col": "o.customer_id", "subquery_select": "rc.customer_id", "subquery_from": "risk_customers rc"}

[0097] ,

[0098] "counts": {"num_in_subqueries": 2, "num_where_conjuncts": 3}}

[0099] The corresponding normalized structural features are as follows:

[0100] {"base_table": "t1",

[0101] "base_alias": "a1",

[0102] "select_exprs": ["a1.c1"],

[0103] "where_conjuncts":

[0104] "a1.c2 = 'L1'",

[0105] "a1.c3 IN (SELECT a2.c4 FROM t2 a2)",

[0106] "a1.c3 IN (SELECT a2.c4 FROM t2 a2)"],

[0107] "subqueries":

[0108] {"type": "IN_SUBQUERY", "outer_col": "a1.c3", "subquery_select": "a2.c4", "subquery_from": "t2 a2"},

[0109] {"type": "IN_SUBQUERY", "outer_col": "a1.c3", "subquery_select": "a2.c4", "subquery_from": "t2 a2"}

[0110] ],

[0111] "counts": {"num_in_subqueries": 2, "num_where_conjuncts": 3}}

[0112] Step 2.4: Execute steps 2.1 to 2.3 on the original SQL query statements and rewritten SQL query statements of each case record in the successful SQL query statement rewriting case set to obtain the normalized structural features of each pair of original-rewritten SQL query statements. Perform difference analysis on the normalized structural features of the original-rewritten SQL query statements to obtain the structural differences and performance changes before and after rewriting.

[0113] In this invention, when performing a difference analysis on the original SQL query statement and the rewritten SQL query statement in each record of the successful SQL query statement rewriting case set, the normalized structural features of the original SQL query statement and the rewritten SQL query statement before and after rewriting in the same record are compared item by item to find out which conditions, subqueries, and other structures have been added, deleted, or merged, and the performance changes are recorded at the same time.

[0114] The structural differences and performance changes of the SQL query statement before and after rewriting, obtained through the above operations, will be used to guide subsequent principle learning. In a preferred embodiment, the structural differences and performance changes of the original SQL query statement and the rewritten SQL query statement are recorded as follows:

[0115] {"delta": {

[0116] "num_in_subqueries": { "before": 2, "after": 1},

[0117] "num_where_conjuncts": { "before": 3, "after": 2},

[0118] "removed_conjuncts": ["a1.c3 IN (SELECT a2.c4 FROM t2 a2)"]},

[0119] "perf_gain": { "latency_ms": -35, "cost": -300}}

[0120] Here, delta represents the result of structural difference analysis, used to describe the structural changes between the original and rewritten SQL query statements in a case record of a successful SQL query rewriting case set. perf_gain records the performance changes, mainly including changes in execution latency (in milliseconds) and execution cost. Here, cost is the cost estimate given by the DBMS optimizer, and the unit of the value can be determined according to the specific DBMS used. This method only compares the relative changes before and after rewriting.

[0121] Step 2.5: Based on the structural differences and performance changes of the original and rewritten SQL query statements in each record of the successful SQL query statement rewriting case set, summarize the common structural changes of the successful SQL query statement rewriting cases and the constraints required for the structural changes to take effect, and describe them in natural language to obtain reusable SQL query statement rewriting principles, and then establish a set of SQL query statement rewriting principles.

[0122] The principles described are derived from numerous successful SQL query rewriting case studies and are human-readable and understandable natural language summaries of "what SQL query structures can be equivalently rewritten under what conditions." They provide a readable description of "under what structures, under what conditions, what equivalent rewriting should be performed, and what performance benefits it typically brings." The principles themselves do not directly perform rewriting but guide the subsequent generation of verifiable and deployable rewriting rules.

[0123] Using Large Language Models (LLMs), under the constraints of preset prompts, we analyze the structural differences and performance changes of multiple original-rewritten SQL query statements. Combining the performance change information, we retain the common structural changes that can consistently bring benefits, and then express the above content as reusable SQL query rewriting principles.

[0124] The common structural changes here refer to the SQL query structure changes that occur repeatedly in multiple records of the successful SQL query rewriting case set and are related to performance improvement, such as "merging repeated IN subqueries into one IN subquery".

[0125] At the same time, LLMs will summarize the constraints required for these SQL query structure changes to be valid and add them to the corresponding SQL query rewriting principles. For example, for the SQL query structure change of "merging repeated IN subqueries into one IN subquery", the constraint is that "the outer columns of the two IN subqueries are the same, and the columns in the FROM and SELECT of the subqueries are consistent".

[0126] In the preferred embodiment, a SQL query rewriting principle with the following structure can be obtained:

[0127] {"principle_id": "P-IN-REDUCE",

[0128] "nl_text": "When there are two identical IN subqueries in the AND condition of the WHERE clause, and their outer comparison columns are the same, and the FROM and SELECT columns of the subqueries are the same, one of the IN subqueries can be deleted to eliminate redundancy. That is, AND(InSub(x,S), InSub(x,S)) can be reduced to InSub(x,S)".

[0129] "preconditions": [

[0130] "The outer_col of the two IN subqueries is the same",

[0131] "The FROM structures of the two subqueries are equivalent",

[0132] "The SELECT output columns of the two subqueries are equivalent"

[0133] "source_cases": ["case_0001"]}

[0134] Where principle_id represents the principle number; nl_text represents the principle described in natural language, which is not a rule that can be executed directly, but rather the basis for generating SQL query rewriting rule templates and constraints; preconditions represents the restrictions for using the current principle; and source_cases represents the set of SQL query rewriting case numbers from which the current principle originates.

[0135] By analyzing the common structural changes and summarizing the constraints of all records in the successful SQL query rewriting case set, all the reusable principles obtained constitute a rewriting principle set.

[0136] This step uses LLMs to discover and generate principles, rather than directly rewriting SQL queries using LLMs. This not only avoids the illusion problem that may arise from directly using LLMs, but also allows for the continuous accumulation and updating of principles during the rewriting of SQL queries. In practical use, this gradually covers high-frequency redundant structures in various SQL queries, thereby improving long-term availability and engineering feasibility.

[0137] Step 3: Based on the SQL query rewriting principles in the rewriting principle set and the normalized structure characteristics of the SQL query obtained in Step 2, generate SQL query rewriting rule templates and constraints, and generate candidate rewriting rules and candidate rewriting rule sets for SQL queries based on the SQL query rewriting rule templates and constraints.

[0138] Since the SQL query rewriting principles in the rewriting principle set cannot be directly used by the DBMS optimizer to rewrite SQL queries, it is necessary to further transform these principles into SQL query rewriting rule templates and constraints to construct a candidate rewriting rule set for SQL queries. The reason for calling them "candidates" is that the rules generated in this step will subsequently undergo equivalence verification; only rules that pass the equivalence verification will be formally added to the SQL query rewriting rule base.

[0139] Step 3.1: Read the SQL query rewriting principles from the rewriting principle set, extract the fixed structure and variable slots in the SQL query rewriting principles through slot parsing, and obtain the slotted SQL query rewriting principle template.

[0140] In this invention, slotting refers to abstracting the variable objects (tables, columns, subquery fragments, predicates, etc.) in the SQL query rewriting principle into variable slots, which are essentially "fill-in slots" that can be filled in based on the specific content of the SQL query matched by the SQL query rewriting principle. This approach enables the reuse of the same principle across different SQL query statements with similar structures.

[0141] In the preferred embodiment, the principle is as follows: P-IN-REDUCE yields the following results through slotting:

[0142] {"principle_id": "P-IN-REDUCE",

[0143] "slot_template": {

[0144] "pattern_src": "AND(InSub(x,S), InSub(x,S), other_preds)",

[0145] "pattern_dst": "AND(InSub(x,S), other_preds)",

[0146] "slots": ["x", "S", "other_preds"]}}

[0147] Where `principle_id` represents the principle number, indicating the SQL query rewriting principle corresponding to the current slotted SQL query rewriting principle template; `slot_template` represents the current slotted SQL query rewriting principle template; `pattern_src` represents the source structure, indicating the structure to be matched in the original SQL query; `pattern_dst` represents the target structure, indicating the structure obtained after rewriting the matched structure in the original SQL query; and `slots` represents the positions to be extracted and filled from the specific SQL query. Based on these meanings, the above slotting result can be interpreted as: "In the AND condition of WHERE, if two identical InSub(x, S) appear, one is deleted, while the other conditions remain unchanged; where x, S, and other conditions other_preds are all extracted from the specific SQL query and filled in."

[0148] Step 3.2: Based on the SQL query rewriting principles obtained in Step 2.5 and the slotted SQL query rewriting principle template obtained in Step 3.1, generate SQL query rewriting rule templates and constraints. Combine the two to form candidate rewriting rules for SQL queries and construct a set of candidate rewriting rules for SQL queries.

[0149] In a preferred embodiment, the SQL query rewriting rule template includes a matching template q_src and a replacement template q_dst. The matching template is used to match the original SQL query statement to determine the structure that the original SQL query statement needs to match when rewriting the SQL query statement; the replacement template is the target structure after rewriting the structure of the original SQL query statement that successfully matches q_src.

[0150] In a preferred embodiment, the SQL query rewriting rule template is expressed as follows:

[0151] {"q_src": {

[0152] "match_scope": "WHERE_CONJUNCTS",

[0153] "pattern": [

[0154] {"type": "IN_SUBQUERY", "bind": {"outer_col": "x", "subquery": "S"}},

[0155] {"type": "IN_SUBQUERY", "bind": {"outer_col": "x", "subquery": "S"}},

[0156] {"type": "OTHER_PREDS", "bind": {"pred_list": "other_preds"}}]},

[0157] "q_dst": {

[0158] "rewrite_scope": "WHERE_CONJUNCTS",

[0159] "construct": [

[0160] {"type": "OTHER_PREDS", "use": "other_preds"},

[0161] {"type": "IN_SUBQUERY", "use": {"outer_col": "x", "subquery": "S"}}]}}

[0162] Wherein, q_src represents the matching template; match_scope represents the matching scope, used to determine the structures that need to be matched in the original SQL query statement when rewriting the SQL query statement; pattern represents the structural elements that need to be matched within match_scope. These structural elements come from the structural decomposition results of pattern_src and pattern_dst in the slotted SQL query statement rewriting principle template obtained in step 3.1, and correspond one-to-one with the normalized structural feature fields obtained in step 2. type represents the structural type identifier, used to indicate the category of each structural element in pattern / construct, such as IN subquery predicate or other predicate sets; bind represents the binding slot instruction, indicating that when the structural elements in pattern successfully match the specific structure in the original SQL query statement, the predetermined fields are extracted from the specific structure of the original SQL query statement and bound to the corresponding slot variables.

[0163] `q_dst` represents the replacement template, indicating what replacement structure should be constructed as the rewrite result within the same matching scope when an original SQL query successfully matches `q_src`. `rewrite_scope` represents the rewrite scope, specifying which clause structure in the original SQL query should output the replacement structure. `construct` represents the structure element to be replaced within `rewrite_scope`, derived from the slot variables bound after the original SQL query successfully matches `q_src`. `use` represents the reuse instruction, indicating that the value of the slot variable bound during the matching with `q_src` should be directly referenced when constructing the replacement structure, ensuring consistency between the rewritten structure and the original SQL query structure.

[0164] In this invention, a set of constraint conditions C is generated based on the constraints of the SQL query statement rewriting principle, and the set of constraint conditions C is written into a condition expression that can be checked by the database optimization system, which is used to limit the triggering scope of the SQL query statement rewriting rule template.

[0165] In a preferred embodiment, the SQL query rewriting principles given in step 3 are as follows:

[0166] {"principle_id": "P-IN-REDUCE",

[0167] "nl_text": "When there are two identical IN subqueries in the AND condition of the WHERE clause, and their outer comparison columns are the same, and the FROM and SELECT columns of the subqueries are the same, one of the IN subqueries can be deleted to eliminate redundancy. That is, AND(InSub(x,S), InSub(x,S)) can be reduced to InSub(x,S)".

[0168] "preconditions": [

[0169] "The outer_col of the two IN subqueries is the same",

[0170] "The FROM structures of the two subqueries are equivalent",

[0171] "The SELECT output columns of the two subqueries are equivalent"

[0172] "source_cases": ["case_0001"]}

[0173] Based on the contents of its preconditions, the generated set of constraints C includes at least the following:

[0174] C1: The two IN subqueries have the same outer_col (corresponding to the same x).

[0175] C2: The two subqueries FROM are equivalent (corresponding to S.from).

[0176] C3: The output columns of the two subqueries are equivalent (corresponding to S.select).

[0177] The constraint set C of this preferred embodiment is described as follows:

[0178] "C": [

[0179] "x1 == x2",

[0180] "S1.subquery_from == S2.subquery_from",

[0181] "S1.subquery_select == S2.subquery_select"]

[0182] By combining the aforementioned q_src, q_dst with their constraint set C, the following is generated:<q_src,q_dst,C> This yields a candidate SQL query rewriting rule. In this preferred embodiment, the obtained candidate SQL query rewriting rule is as follows:

[0183] {"rule_candidate_id": "R-IN-REDUCE-0001",

[0184] "principle_id": "P-IN-REDUCE",

[0185] "q_src": {

[0186] "match_scope": "WHERE_CONJUNCTS",

[0187] "pattern": [

[0188] {"type": "IN_SUBQUERY", "bind": {"outer_col": "x", "subquery": "S"}},

[0189] {"type": "IN_SUBQUERY", "bind": {"outer_col": "x", "subquery": "S"}},

[0190] {"type": "OTHER_PREDS", "bind": {"pred_list": "other_preds"}}]},

[0191] "q_dst": {

[0192] "rewrite_scope": "WHERE_CONJUNCTS",

[0193] "construct": [

[0194] {"type": "OTHER_PREDS", "use": "other_preds"},

[0195] {"type": "IN_SUBQUERY", "use": {"outer_col": "x", "subquery": "S"}}]}

[0196] "C": ["x1 == x2","S1.subquery_from == S2.subquery_from","S1.subquery_select == S2.subquery_select"]

[0197] "source_cases": ["case_0001"]}

[0198] After performing the above operations on all SQL query rewriting principles in the rewriting principle set, the resulting candidate SQL query rewriting rules constitute the candidate rewriting rule set.

[0199] Step 4: Perform equivalence verification on each rule in the candidate rewriting rule set of the SQL query statement. Write the rewriting rules that pass the verification into the SQL query statement rewriting rule base. The rewriting rules that fail the verification will be fed back to tighten the constraints or modify the principle records.

[0200] In a preferred embodiment, for the candidate rewriting rule set of SQL query statements, each rule in the candidate rule set is validated for equivalence using existing SQL query equivalence validators such as SQL Solver and SMT solver. The validated rewriting rules are written into the SQL query rewriting rule base (containing all existing SQL query rewriting rules of the DBMS), while the rewriting rules that fail validation are fed back to tighten constraints or modify principle records.

[0201] Step 5: Based on the SQL query rewriting rule base, perform online SQL query rewriting rule matching and equivalent SQL query rewriting on the SQL query to be rewritten, and update the SQL query rewriting success case set, principle set and SQL query rewriting rule base based on the feedback information after executing the SQL query rewriting.

[0202] For an SQL query statement to be rewritten, this invention first performs structural parsing to obtain a structural feature representation consistent with step 2, and then executes it in the rewriting rule base:

[0203] Rule matching: Matches the structure fragment described by q_src;

[0204] Constraint checking: Checks whether the set of constraints C is satisfied;

[0205] Perform the replacement: Under the premise of satisfying C, replace the matching fragment with q_dst to obtain the rewritten SQL query statement.

[0206] After executing the rewritten SQL query, collect its execution feedback information, including at least: execution time, changes in execution cost estimate, and whether negative optimization occurs (increased execution time, increased cost estimate), generate new SQL query rewriting records, and write the records with performance improvements (reduced execution time, reduced cost estimate) in the execution feedback back to the SQL query rewriting success case set in step 1 as successful SQL query rewriting cases.

[0207] The processing of newly added records includes the following two scenarios:

[0208] If a common structural change corresponding to a certain SQL query rewriting rule template (including the matching template q_src and the replacement template q_dst) continuously generates performance benefits in multiple SQL query rewriting records, then the rewriting record corresponding to the common structural change is recorded as a new successful SQL query rewriting case and added to the SQL query rewriting successful case set, triggering step 3 to update the principle set; when a rule can only be applied to a small number of SQL queries to be rewritten, and the equivalence verification result of the rule shows that it can still pass the equivalence verification in step 4 under more relaxed constraints, the constraint set C can be relaxed, that is, without changing the rule template, some restrictions in the constraint set C can be deleted or weakened, and step 4 verification can be re-executed before being entered into the database.

[0209] If the equivalence verification of a rewrite rule fails or the negative optimization records in the execution feedback information appear in SQL query statements with semantically vulnerable structures such as NULL handling, outer joins, DISTINCT / aggregation, and correlated subqueries: transform the failure reason into a more stringent set of constraints C, and synchronously update the preconditions in the principle set to prevent the rule from being triggered in inapplicable scenarios.

[0210] After the rule base is updated, this preferred embodiment uses the updated rule set for online rule matching and equivalent rewriting of subsequent SQL query statements to be rewritten, and continuously collects execution feedback information to form a cycle of "execution - feedback - storage - relearning - reverification - redeployment", thereby realizing the self-updating and continuous evolution of the SQL query statement rewriting rule base, principle set and case dataset.

[0211] The method of this invention can be applied to actual business systems in the following manner:

[0212] 1) Database middleware / proxy mode: Deploy the SQL query statement rewriting method proposed in this invention between the application and the database, receive the SQL query statement to be optimized sent by the application, call the rules in the SQL query statement rewriting rule base to rewrite it, and then send it to the database for execution;

[0213] 2) Optimizer plug-in mode: The method of this invention is connected as a plug-in or extension module in the database optimizer rewriting stage, so that rule matching and SQL query rewriting occur in the logical optimization stage;

[0214] 3) Application-side SDK (Software Development Kit) mode: A unified SQL query statement sending interface is encapsulated in the ORM (Object-Relational Mapping) or data access layer, and rule matching and rewriting are performed before the SQL query statement is sent to the database.

[0215] The method of this invention is applied to common business scenarios such as e-commerce risk control, enterprise report analysis, and recommendation retrieval, according to the three modes described above: database middleware / proxy, optimizer plugin, or application-side SDK. These scenarios share the same pain point: SQL is often constructed from ORM or dynamic SQL query statements. Such SQL queries are a common business input for intelligent database systems. The structure of these SQL queries changes rapidly, and repetitive conditions are common, making it difficult for a fixed rule base to consistently cover them. In practical applications, the method of this invention only enables rules that have passed equivalence verification and employs shadow testing and canary deployment to control the risk of rule deployment, both of which are commonly used engineering deployment methods in practice.

[0216] Case 1: Optimization of E-commerce Risk Control Order Inquiry

[0217] Business pain points: E-commerce risk control queries involve many filtering conditions, and different modules often add the same conditions repeatedly. SQL query statements are dynamically constructed, which easily leads to redundant structures such as repeated IN subqueries and repeated predicates, resulting in slower queries.

[0218] Implementation Solution: The method of this invention can be applied according to the database middleware / proxy model: The e-commerce platform sends an SQL query statement → the middleware performs rule matching and rewriting → the rewritten SQL query statement is sent to the database for execution, and the execution effect is recorded. The original-rewritten SQL query statement and its execution feedback information are written back to the SQL query statement rewriting case set as a new SQL query statement rewriting record. The SQL query statement rewriting principle set and SQL query statement rewriting rule library will be updated in the future.

[0219] Technical Adaptation: Candidate rewriting rules are derived from the database system's original rule base and the SQL query rewriting principle extraction and rule generation driven by a successful SQL query rewriting case set. Equivalence verification serves as a safeguard, and verification failure triggers constraint tightening and principle modification. When SQL query rewriting rules are deployed online, shadow testing and canary release control are used, and rollback is supported.

[0220] Application value: Reduces redundant filtering caused by dynamic splicing; SQL query statement rewriting rule base is continuously updated as business changes occur; online deployment can be phased out and rolled back, reducing the risk of introducing new rules.

[0221] Case 2: Optimization of Enterprise Reports and Business Intelligence Queries

[0222] Business pain points: A large number of queries in the enterprise reporting system are generated from report templates. They have similar structures but different parameters. When requirements are iterated, the structure of SQL query statements changes rapidly, which can easily introduce problems such as repeated conditions, redundant subqueries or excessive nesting, resulting in slow response. Manually maintaining the SQL query statement rewriting rule base is difficult to cover the constantly changing report patterns.

[0223] Implementation Solution: The method of this invention is integrated using an optimizer plugin model. During the logical optimization phase of SQL query statements in the database, rule matching and rewriting of SQL queries are performed. By learning from successful historical SQL query rewriting cases, SQL query rewriting principles are studied, and SQL query rewriting rules are generated, gradually forming a self-updating SQL query rewriting rule library tailored to the enterprise's data model.

[0224] Technical adaptation: SQL query statements are segmented into clauses, parsed, and normalized to facilitate clustering and difference analysis; the SQL query statement rewrite rule base records rule versions and verification information, and supports rollback to the previous stable rule set.

[0225] Application value: The SQL query rewriting rule base can be continuously updated during report iteration; it reduces the recurrence of inefficient SQL queries; and it improves overall query stability without changing business logic.

[0226] Case 3: Recommendation Search and User Profile Query

[0227] Business pain points: Recommendation and retrieval systems require a large number of online queries for user profiles, tags, or behavioral statistics. While SQL query structures are similar, parameters vary significantly, leading to high concurrency and latency sensitivity. Dynamically concatenating conditions can create redundant structures, causing fluctuations in latency and impacting the stability of online services.

[0228] Implementation Solution: The method of this invention is deployed using a database middleware / proxy model. SQL queries entering the database are rewritten online and then fed back for execution feedback. High-risk rules are first shadow verified, then deployed via canary deployment, to control online risks.

[0229] Technical Adaptation: Supports fine-grained on / off control of the enable ratio and rollback threshold based on rule number or query type; execution failure cases trigger constraint tightening and updates to SQL query statement rewriting principles.

[0230] Application value: Maintaining stable query latency in high-concurrency scenarios; reducing performance fluctuations caused by redundant structures; and enabling rules to evolve sustainably to adapt to business changes.

[0231] The above examples only illustrate typical applications such as e-commerce risk control, enterprise report analysis and business intelligence queries, recommendation retrieval and user profile queries, demonstrating the implementation and effectiveness of this invention in real business systems. For other scenarios that also exhibit characteristics such as "SQL query statements generated by concatenating ORM or dynamic SQL query statements, rapidly changing query structures, common repetitive conditions and redundant structures, and difficulty in continuously covering the fixed SQL query statement rewriting rule base," such as online transaction analysis, operational monitoring, advertising analysis, and log audit queries, this invention can also be integrated through a database middleware / proxy, optimizer plugin, or application-side SDK to achieve automatic discovery, verification, and continuous updating of SQL query statement rewriting rules, thereby improving the long-term applicability and stability of query rewriting optimization.

[0232] It should be understood that, inspired by the technical concept of this invention, those skilled in the art can make various improvements or modifications based on the above content without departing from the scope of this invention, and these modifications still fall within the protection scope of this invention.

Claims

1. A self-updating SQL query rewriting method, characterized in that, The method includes: extracting candidate rewriting rules for SQL queries from the current set of successful SQL query rewriting cases; constructing an SQL query rewriting rule library by combining the candidate rewriting rules that pass equivalence verification with the original rewriting rules of the database optimization system; performing online rewriting rule matching on the SQL query to be rewritten based on the SQL query rewriting rule library, and performing equivalent rewriting according to the matched rewriting rules; collecting execution feedback information before and after the SQL query rewriting, and selecting SQL query rewriting cases that meet preset conditions based on the execution feedback information, and defining the SQL query rewriting cases that meet the preset conditions as successful SQL query rewriting cases; adding the selected successful SQL query rewriting cases to the set of successful SQL query rewriting cases to update the current set of successful SQL query rewriting cases; and repeating the above process with the updated set of successful SQL query rewriting cases as the current set of successful SQL query rewriting cases to update the SQL query rewriting rule library.

2. The SQL query rewriting method according to claim 1, characterized in that, The process of extracting candidate rewriting rules for SQL queries from the current set of successful SQL query rewriting cases includes: extracting executable SQL query rewriting rule templates and corresponding constraints from the current set of successful SQL query rewriting cases; and combining the SQL query rewriting rule templates and corresponding constraints together to form candidate rewriting rules for SQL queries.

3. The SQL query rewriting method according to claim 2, characterized in that, The method for extracting executable SQL query rewriting rule templates and corresponding constraints from the current successful SQL query rewriting case set includes: extracting reusable SQL query rewriting principles described in natural language based on the structural differences and performance changes of the SQL query statements before and after rewriting in each rewriting case in the current successful SQL query rewriting case set; translating the SQL query rewriting principles into executable SQL query rewriting rule templates and constraints; the content of the SQL query rewriting principles includes at least a natural language description of the structural changes before and after rewriting of the SQL query statement to be rewritten, and a natural language description of the constraints required for the structural changes to be valid.

4. The SQL query rewriting method according to claim 3, characterized in that, Based on the structural differences and performance changes of the SQL queries before and after rewriting in each case in the current successful SQL query rewriting case set, reusable SQL query rewriting principles described in natural language are extracted, including: Step 2.1: Based on the keywords of the SQL query statement, perform clause segmentation on the original SQL query statement and the rewritten SQL query statement of each case in the successful SQL query statement rewriting case set to obtain the clause set of the SQL query statement; Step 2.2: Based on the set of clauses of the SQL query statement, by parsing the expressions and nesting structures of each clause of each SQL query statement, structural features that represent the key semantic structure of the SQL query statement are obtained; the structural features include at least the main table information, the set of selection expressions, the set of filtering predicates, and the subquery structure information; Step 2.3: By establishing a symbol mapping relationship, uniform symbol replacement is performed on the table names, column names, aliases and constants appearing in the structural features of the SQL query statement to obtain the standardized structural features of the SQL query statement; Step 2.4: For each case in the successful SQL query rewriting case set, execute steps 2.1 to 2.3 for the original SQL query statement and the rewritten SQL query statement to obtain the normalized structural features of the original SQL query statement and the normalized structural features of the rewritten SQL query statement, i.e., a pair of normalized structural features of the original-rewritten SQL query statement. Perform difference analysis on the normalized structural features of the original-rewritten SQL query statement to obtain the structural difference features and performance change information before and after the SQL query statement rewriting. Step 2.5: Analyze the structural differences and performance changes of multiple pairs of original-rewritten SQL query statements, summarize the common structural changes before and after SQL query statement rewriting in successful SQL query statement rewriting cases, as well as the constraints required for these structural changes to take effect, and describe them in natural language to obtain the corresponding reusable SQL query statement rewriting principles described in natural language.

5. The SQL query rewriting method according to claim 4, characterized in that, In step 2.5, the structural differences and performance changes of multiple pairs of original-rewritten SQL query statements are analyzed using a large language model. The common structural changes before and after the rewriting of SQL query statements in successful rewriting cases, as well as the constraints required for the structural changes to take effect, are summarized and described in natural language. This yields the corresponding reusable SQL query statement rewriting principles described in natural language.

6. The SQL query rewriting method according to claim 3, characterized in that, The process of translating SQL query rewriting principles into executable SQL query rewriting rule templates and constraints includes: (I) Read the SQL query rewriting principle, extract the fixed structure and variable slots in the SQL query rewriting principle through slot parsing, and obtain the slotted SQL query rewriting principle template; the slot parsing is to abstract the variable objects in the SQL query rewriting principle into variable slots; (II) Based on the SQL query statement rewriting principles and the slotted SQL query statement rewriting principle template, generate an SQL query statement rewriting rule template; the SQL query statement rewriting rule template includes a matching template q_src and a replacement template q_dst; the matching template q_src is used to match the original SQL query statement to determine the structure that the original SQL query statement needs to match when rewriting the SQL query statement; the replacement template q_dst is the target structure after rewriting the structure of the original SQL query statement that successfully matches q_src. (III) Generate a set of constraints C that the database optimization system can check based on the constraints in the SQL query rewriting principle, which is used to limit the triggering scope of the SQL query rewriting rule template.

7. The SQL query rewriting method according to claim 6, characterized in that, The slotted SQL query rewriting principle template includes: pattern_src representing the source structure, indicating the structure to be matched in the original SQL query; pattern_dst representing the target structure, indicating the structure obtained after rewriting the structure in the matched original SQL query; and slots representing the positions where content needs to be extracted from the specific SQL query and filled in.

8. The SQL query rewriting method according to claim 7, characterized in that, The matching template q_src includes match_scope, pattern, type, and bind. match_scope represents the matching range, used to determine the structures that need to be matched in the original SQL query statement when rewriting the SQL query statement. pattern represents the structural elements that need to be matched within match_scope. These structural elements come from the splitting results of the structures in pattern_src and pattern_dst, and correspond one-to-one with the normalized structural feature fields of the SQL query statement. type represents the structure type identifier, used to indicate the category of each structural element in pattern / construct. bind represents the binding slot instruction, indicating that when a structural element in pattern successfully matches a specific structure in the original SQL query statement, the corresponding fields are extracted from the specific structure of the original SQL query statement and bound to the corresponding slot variable. The replacement template q_dst includes rewrite_scope, construct, and use; rewrite_scope indicates the rewrite scope, i.e., in which clause structure of the original SQL query statement the replacement structure is output; construct represents the structure element to be replaced within rewrite_scope, which comes from the slot variable that was bound after the original SQL query statement successfully matched with q_src; use represents a reuse instruction, indicating that when constructing the replacement structure, the value of the slot variable that was bound when matching with q_src is directly referenced.

9. The SQL query rewriting method according to claim 3, characterized in that, The method also includes: continuously rewriting new SQL queries using rules in the SQL query rewriting rule base. When a rule can only be applied to a small number of SQL query statements to be rewritten, and the equivalence verification result of the rule shows that it can still pass the equivalence verification under more relaxed constraints, the constraints can be relaxed. That is, without changing the rule template, some constraints can be deleted or weakened, and the rule can be added to the SQL query statement rewriting rule library after passing the equivalence verification again. If the equivalence verification of a rewrite rule fails or the negative optimization records in the execution feedback information are concentrated in SQL query statements with a specific structure, then the application conditions of the rewrite rule will be tightened, that is, more stringent constraints will be added, and the restrictions in the corresponding principles will be updated simultaneously to prevent the rule from being triggered in inapplicable scenarios.

Images