Automatic SQL tuning method and system combining large language model and database optimizer, computer equipment and storage medium

Abstract

The invention discloses an automatic SQL tuning method and system combined with a large language model and a database optimizer, computer equipment and a storage medium, and the method comprises the steps: generating a query fingerprint according to an SQL query statement input by a user, detecting whether there is an optimization result matched with the query fingerprint in a cache, and only when there is no optimization result, executing the step 1; if yes, the SQL query statements input by the user and the optimization instruction are input into the large language model, and a plurality of candidate SQL query statements are generated; performing semantic equivalence verification on the candidate SQL query statements and an SQL query statement input by a user, and screening out semantic equivalence candidate SQL query statements; submitting the screened candidate SQL query statements to a database optimizer, and obtaining an execution plan and cost information of each candidate SQL query statement; calculating an evaluation score of each candidate SQL query statement, and selecting the candidate SQL query statement with the highest evaluation score as an optimization result; and writing the optimization result and the related execution data into a cache, and returning the optimization result and the related execution data to the user.

Description

Technical Field

[0001] The present invention belongs to the technical field of database query optimization, and in particular relates to an automatic SQL tuning method, system, computer equipment and storage medium combining a large language model and a database optimizer. Background Art

[0002] With the explosive growth of data volumes, database query efficiency has become a key bottleneck in information system performance. Traditional SQL optimization relies primarily on built-in database optimizers or manual tuning. The former often only generates fixed solutions in complex scenarios, making it difficult to fully explore potential optimization space; the latter requires expert intervention, which is costly and time-consuming. In recent years, large language models (LLMs) have been introduced into the field of SQL optimization due to their powerful semantic understanding and code generation capabilities. However, existing methods often suffer from two major shortcomings: first, a single rewriting solution is prone to semantic deviation and cannot guarantee equivalence with the original query; second, there is a lack of quantitative evaluation methods for optimization effects. Even if the generated SQL can be executed, the performance improvement is difficult to predict and verify. Summary of the Invention

[0003] Purpose of the Invention: To address the shortcomings of existing SQL optimization methods, the present invention proposes an automatic SQL tuning method, system, computer device, and storage medium that combine a large language model and a database optimizer. This closed-loop optimization technology automatically generates multiple optimization candidates, strictly verifies semantic equivalence, and performs quantitative evaluation based on execution plans and actual costs. This maximizes execution efficiency and reduces manual intervention costs while ensuring query correctness.

[0004] Technical solution: An automatic SQL tuning method that combines a large language model and a database optimizer, including the following steps:

[0005] Step 1: Receive the SQL query statement entered by the user;

[0006] Step 2: Generate a query fingerprint based on the SQL query statement entered by the user, and check whether there is an optimization result matching the query fingerprint in the cache. If so, extract the optimization result matching the query fingerprint from the cache and return it to the user; otherwise, execute step 3;

[0007] Step 3: Input the SQL query statement and optimization instructions entered by the user into the large language model to generate multiple candidate SQL query statements;

[0008] Step 4: Verify the semantic equivalence between the candidate SQL query statements and the SQL query statement input by the user, and select the semantically equivalent candidate SQL query statements;

[0009] Step 5: Submit the selected candidate SQL query statements to the database optimizer, execute EXPLAIN and EXPLAIN ANALYZE, and obtain the execution plan and cost information for each candidate SQL query statement;

[0010] Step 6: Calculate the evaluation score of each candidate SQL query statement based on the execution plan and cost information, and select the candidate SQL query statement with the highest evaluation score as the optimization result;

[0011] Step 7: The optimization result and its related execution data are written into the cache and returned to the user; the execution data includes the execution plan, cost information and evaluation score.

[0012] Furthermore, the query fingerprint is obtained by performing a hash operation on the SQL query statement input by the user after text normalization.

[0013] Furthermore, in step 6, the evaluation score of each candidate SQL query statement is calculated based on the execution plan and cost information, specifically including:

[0014] An evaluation score for each candidate SQL query statement is calculated using a predefined evaluation algorithm; the predefined evaluation algorithm is a weighted function that combines cost information.

[0015] Furthermore, the cost information includes: estimated cost, actual execution time, CPU usage, memory consumption, and number of I / O operations.

[0016] Furthermore, the multiple candidate SQL query statements are multiple candidate SQL query statements with different optimization strategies, and the optimization strategies include: index utilization optimization strategy, JOIN order adjustment optimization strategy, subquery transformation optimization strategy, and aggregation operation optimization strategy.

[0017] Furthermore, the method further includes the following steps:

[0018] Monitor the actual execution of the optimization results returned to the user and collect relevant monitoring data based on the cost information;

[0019] Compare and analyze the collected monitoring data with the expected performance; adjust the optimization instructions and parameters of the large language model based on the comparative analysis results;

[0020] Dynamically adjust the weighting coefficients in the predefined evaluation algorithm based on the collected monitoring data.

[0021] The present invention also discloses an automatic SQL tuning system combining a large language model and a database optimizer, comprising:

[0022] Query receiving module, used to receive SQL query statements input by users;

[0023] The cache detection module is used to generate a query fingerprint based on the SQL query statement entered by the user, and detect whether there is an optimization result matching the query fingerprint in the cache. If so, the optimization result matching the query fingerprint is extracted from the cache and returned to the user; otherwise, the candidate generation module is called;

[0024] The candidate generation module is used to input the SQL query statement and optimization instructions entered by the user into the large language model to generate multiple candidate SQL query statements;

[0025] The semantic verification module is used to verify the semantic equivalence between the candidate SQL query statements and the SQL query statements input by the user, and to screen out the semantically equivalent candidate SQL query statements;

[0026] The execution plan acquisition module is used to submit the selected candidate SQL query statements to the database optimizer, execute EXPLAIN and EXPLAIN ANALYZE, and obtain the execution plan and cost information of each candidate SQL query statement;

[0027] An evaluation and selection module is used to calculate the evaluation score of each candidate SQL query statement based on the execution plan and cost information using a predefined evaluation algorithm; the predefined evaluation algorithm is a weighted function combined with the cost information, and the candidate SQL query statement with the highest evaluation score is selected as the optimization result;

[0028] A cache update module is used to write the optimization result and its related execution data into the cache; the execution data includes the execution plan, cost information and evaluation score;

[0029] The result return module is used to return the optimization result and its related execution data to the user.

[0030] Furthermore, it also includes:

[0031] The execution effect monitoring module is used to monitor the actual execution of the optimization results returned to the user and collect relevant monitoring data based on the cost information;

[0032] Feedback data processing module, used to compare and analyze the collected monitoring data with the expected performance;

[0033] Model optimization module, used to adjust the optimization instructions and parameters of the large language model based on the comparative analysis results;

[0034] The evaluation algorithm update module is used to dynamically adjust the weighting coefficients in the predefined evaluation algorithm based on the collected monitoring data.

[0035] The present invention also discloses a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the processor implements the steps of an automatic SQL tuning method combining a large language model and a database optimizer.

[0036] The present invention also discloses a storage medium storing an automatic SQL tuning program. When the automatic SQL tuning program is executed by at least one processor, the automatic SQL tuning program implements the steps of an automatic SQL tuning method combining a large language model and a database optimizer.

[0037] Beneficial effects: Compared with the prior art, the present invention has the following advantages:

[0038] (1) The method of the present invention significantly improves the success rate and quality of SQL optimization through a multi-candidate generation and verification mechanism;

[0039] (2) The method of the present invention is based on the evaluation and selection mechanism of the execution plan to ensure that the selected SQL query has the best performance in actual execution;

[0040] (3) The method of the present invention utilizes a cache hit mechanism, which can significantly reduce response time and computing resource consumption;

[0041] (4) The method of the present invention achieves continuous self-optimization of the system through closed-loop feedback. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Figure 1 This is a flowchart for an automated SQL tuning method that combines a large language model with a database optimizer. It illustrates the complete process from user submission of an original SQL query to the return of the optimal SQL query, including key steps such as cache detection, candidate generation, semantic verification, execution plan acquisition, optimal selection, and result caching.

[0043] Figure 2 A schematic diagram of an automatic SQL tuning system that combines a large language model and a database optimizer;

[0044] Figure 3 This is a flowchart for execution plan evaluation and optimal SQL selection. It shows how to obtain the execution plan for each candidate SQL and select the SQL with the best performance through an evaluation algorithm.

[0045] Figure 4 This is a diagram of the multi-candidate SQL generation and semantic verification structure, showing how to use a large language model to generate multiple candidate SQLs and use semantic verification tools to ensure the logical equivalence of the candidate SQLs and the original SQLs. DETAILED DESCRIPTION

[0046] The technical solution of the present invention will now be further described with reference to the accompanying drawings and embodiments.

[0047] Example 1:

[0048] like Figure 1 As shown, this embodiment proposes an automatic SQL tuning method that combines a large language model and a database optimizer. This method achieves efficient optimization of SQL queries through technical means such as a multi-candidate generation and verification mechanism, execution plan evaluation and optimal selection, a cache hit mechanism and asynchronous pre-generation, and closed-loop feedback and continuous improvement. The method mainly includes the following steps:

[0049] Step 1: Receive the SQL query statement entered by the user;

[0050] Step 2: Generate a query fingerprint based on the SQL query statement entered by the user, and check whether there is an optimization result matching the query fingerprint in the cache; if there is a matching optimization result, extract the optimal SQL from the cache and return it to the user; otherwise, execute step 3; in this embodiment, the query fingerprint is obtained by performing a hash operation based on the normalized SQL text.

[0051] Step 3: Call the large language model to generate multiple candidate SQL query statements based on the SQL query statement input by the user; the large language model called in this embodiment is a pre-trained and fine-tuned text-to-SQL statement model.

[0052] Step 4: Use a semantic equivalence verification tool to verify the semantic equivalence of the candidate SQL query statements and the SQL query statements input by the user, screen out semantically equivalent candidate SQL query statements, and filter out unequal SQL statements; in this embodiment, the semantic equivalence verification tool uses an SMT solver to determine the equivalence of the translated logical expressions.

[0053] Step 5: Submit the filtered candidate SQL query statements to the database optimizer, execute EXPLAIN and EXPLAIN ANALYZE, and obtain the execution plan and cost information for each candidate SQL query statement;

[0054] Step 6: Based on the execution plan and cost information, calculate the evaluation score of each candidate SQL query statement, and select the SQL query statement with the highest score as the optimal SQL; in this embodiment, based on indicators such as estimated cost, actual time consumption, I / O and resource utilization, calculate the evaluation score of each candidate SQL query statement through a predefined evaluation algorithm, and select the SQL statement with the highest score as the optimal SQL; in this embodiment, the predefined evaluation algorithm is a weighted function that combines execution cost, actual response time, I / O cost and resource utilization.

[0055] Step 7: Write the optimal SQL and its related execution data into the cache;

[0056] Step 8: Return the optimal SQL to the user.

[0057] In this embodiment, it also includes: asynchronously pre-generating candidate SQL statements for high-frequency queries and updating the cache, and feeding back the actual execution results to the model fine-tuning module to continuously optimize the candidate generation and evaluation algorithm.

[0058] This embodiment leverages multiple candidate generation and verification mechanisms, execution plan evaluation and optimal selection, a cache hit mechanism, and closed-loop feedback, leveraging the diverse rewriting capabilities of the LLM while strictly ensuring query semantic correctness. Ultimately, this approach achieves superior performance compared to a single optimizer in real-world execution. Furthermore, the combined use of caching and asynchronous pre-generation mechanisms significantly reduces online call costs and improves system throughput and response speed. This method achieves efficient SQL query optimization, significantly improving query performance and system response speed.

[0059] Example 2:

[0060] Based on Example 1, this example further describes the implementation of Example 1 in detail, including the following steps:

[0061] Step S1: Cache Detection: The user enters the original SQL query statement to be optimized in the user interface or API layer. A query fingerprint is generated based on the original SQL query statement, and the cache is checked to see if there is an optimized result matching the query fingerprint. The query fingerprint is generated by hashing the normalized SQL statement to ensure that identical or similar queries can be identified. If a query exists, the optimal SQL statement in the cache is directly retrieved and returned, ending the process. Otherwise, the process proceeds to S2.

[0062] Step S2: Generate Multiple Candidate SQL Statements: The large language model is invoked, and the original SQL statement and optimization instructions are provided. The large language model returns multiple candidate SQL query statements with different optimization strategies, such as expanding subqueries, replacing aggregations with window functions, adjusting join order, and adding index hints. A total of N (e.g., 5-10) candidate SQL statements are generated. Optimization strategies include, but are not limited to, optimizing index utilization, adjusting join order, transforming subqueries, and optimizing aggregation operations.

[0063] Step S3: Use the semantic equivalence verification tool to verify the semantic equivalence of the candidate SQL query statement and the original SQL query statement. This embodiment uses the SQLSover tool to convert the candidate and original SQL into logical expressions through the equivalence proof algorithm, and verifies that the two are equivalent under any data set through SMT / SAT solution, ensuring that the candidate SQL produces the same results as the original SQL under all possible data sets. The verification process includes the following steps: normalization conversion, structural analysis, constraint derivation, etc.; screening out candidate SQL query statements that pass the semantic equivalence verification, eliminating candidate SQL that does not meet the semantic equivalence conditions, and assuming that M (M≤N) candidates pass the verification. The above process can be found in Figure 4 .

[0064] Step S4: Obtaining the execution plan: Submit each of the M candidate SQL statements to the database for EXPLAIN ANALYZE execution, obtaining the execution plan and cost information for each candidate SQL statement. The cost information includes estimated cost (Cost), actual execution time (ExecutionTime), CPU usage, memory consumption, and number of I / O operations.

[0065] Step S5: Optimal SQL evaluation: Calculate the evaluation score of each candidate SQL query statement based on the execution plan and cost information. The evaluation score calculation formula is:

[0066] Score=w1*(1 / Cost)+w2*(1 / ExecutionTime)+w3*ResourceEfficiency+w4*IndexUtilization

[0067] Among them, w1 to w4 are weight coefficients, which are dynamically adjusted according to system configuration and business needs.

[0068] Select the candidate SQL with the highest score as the optimal execution plan. The above process can be found in Figure 3 ;

[0069] Step S6: Cache Update and Result Return: Encapsulate the optimal SQL, execution plan, cost information, and evaluation score and write them to the cache, setting an appropriate expiration time. Asynchronously pre-generate and update high-frequency queries regularly, feeding the optimal SQL and execution plan back to the caller, supporting visual display and manual tuning.

[0070] Step S7: Closed-loop feedback: Record the actual execution data and write it into the log / monitoring system for subsequent model fine-tuning and iterative optimization of the scoring function.

[0071] Example 3:

[0072] Reference Figure 2This embodiment proposes an automatic SQL tuning system that combines a large language model and a database optimizer, including the following modules that are functionally interconnected:

[0073] The query receiving module is used to receive the SQL query statement input by the user and perform preliminary processing, such as removing comments and normalizing the format;

[0074] A cache detection module is used to generate query fingerprints and detect whether there are matching optimization results in the cache; in this embodiment, the cache uses distributed key-value storage to support high concurrent access and fast query;

[0075] The candidate generation module is used to call the large language model to generate multiple candidate SQL query statements; specifically, it includes a model call interface, an optimization strategy library, and a candidate generation controller;

[0076] Semantic verification module, used to verify the semantic equivalence of candidate SQL query statements with the original SQL query statements; this module integrates the SQLSover verification tool and includes a verification result analyzer;

[0077] The execution plan acquisition module is used to obtain the execution plan and cost information of the candidate SQL query statement. This module interacts with the target database through the database connection pool and supports multiple database types.

[0078] Evaluation and selection module, used to calculate evaluation scores and select the optimal SQL query statement; this module includes a scoring algorithm library and a decision engine;

[0079] The cache update module is used to store the optimal SQL and related data in the cache; this module implements cache writing, updating and elimination strategies;

[0080] The result return module is used to return the optimal SQL to the user and provide relevant execution plans and optimization analysis.

[0081] This embodiment also proposes a closed-loop feedback and continuous improvement mechanism based on actual execution results, specifically including:

[0082] The execution effect monitoring module is used to monitor the actual execution of the optimal SQL and collect indicators such as execution time and resource consumption.

[0083] The feedback data processing module is used to compare and analyze the monitoring data with the expected performance and identify patterns with poor optimization effects.

[0084] The model optimization module is used to adjust the prompt templates and parameters of the large language model based on feedback data and optimize the candidate SQL generation strategy.

[0085] The evaluation algorithm update module is used to dynamically adjust the weight coefficients in the evaluation score calculation based on the actual execution effect, so that the evaluation results can more accurately reflect the actual performance.

[0086] The knowledge base update module is used to maintain an optimization pattern library, record successful and failed optimization cases, and provide reference for subsequent optimization.

[0087] Through the above closed-loop feedback mechanism, continuous learning and self-optimization can be achieved, continuously improving the accuracy and efficiency of SQL optimization.

[0088] Those skilled in the art will appreciate that all or part of the processes in the above-mentioned embodiment methods can be implemented by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application may include at least one of non-volatile and volatile memory. Non-volatile memory may include read-only memory (ROM), magnetic tape, floppy disk, flash memory or optical memory, etc. Volatile memory may include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM).

[0089] The technical features of the above embodiments can be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0090] The above-described embodiments merely represent several implementation methods of the present application. While the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the present invention. It should be noted that a person skilled in the art could make various modifications and improvements without departing from the spirit of the present application, all of which fall within the scope of protection of the present application. Therefore, the scope of protection of the present patent application shall be determined by the appended claims.

Claims

1. An automatic SQL tuning method combining a large language model and a database optimizer, characterized by: The following steps are involved: Step 1: Receive the SQL query statement entered by the user; Step 2: Generate a query fingerprint based on the SQL query statement entered by the user, and check whether there is an optimization result matching the query fingerprint in the cache. If so, extract the optimization result matching the query fingerprint from the cache and return it to the user; Otherwise, go to step 3; Step 3: Input the SQL query statement and optimization instructions entered by the user into the large language model to generate multiple candidate SQL query statements; Step 4: Verify the semantic equivalence between the candidate SQL query statements and the SQL query statement input by the user, and select the semantically equivalent candidate SQL query statements; Step 5: Submit the selected candidate SQL query statements to the database optimizer, execute EXPLAIN and EXPLAINANALYZE to obtain the execution plan and cost information of each candidate SQL query statement; Step 6: Calculate the evaluation score of each candidate SQL query statement based on the execution plan and cost information, and select the candidate SQL query statement with the highest evaluation score as the optimization result; Step 7: The optimization result and its related execution data are written into the cache and returned to the user; the execution data includes the execution plan, cost information and evaluation score.

2. The automatic SQL tuning method combining a large language model and a database optimizer according to claim 1, characterized in that: The query fingerprint is obtained by performing a hash operation on the SQL query statement input by the user after text normalization.

3. The automatic SQL tuning method combining a large language model and a database optimizer according to claim 1, characterized in that: In step 6, the evaluation score of each candidate SQL query statement is calculated based on the execution plan and cost information, specifically including: An evaluation score for each candidate SQL query statement is calculated using a predefined evaluation algorithm; the predefined evaluation algorithm is a weighted function that combines cost information.

4. The automatic SQL tuning method combining a large language model and a database optimizer according to claim 3, characterized in that: The cost information includes: estimated cost, actual execution time, CPU usage, memory consumption, and number of I / O operations.

5. The automatic SQL tuning method combining a large language model and a database optimizer according to claim 1, characterized in that: The multiple candidate SQL query statements are multiple candidate SQL query statements with different optimization strategies, where the optimization strategies include: index utilization optimization strategy, JOIN order adjustment optimization strategy, subquery conversion optimization strategy, and aggregation operation optimization strategy.

6. The automatic SQL tuning method combining a large language model and a database optimizer according to claim 3, characterized in that: The following steps are also included: Monitor the actual execution of the optimization results returned to the user and collect relevant monitoring data based on the cost information; Compare and analyze the collected monitoring data with the expected performance; Adjust the optimization instructions and parameters of the large language model based on the comparative analysis results; Dynamically adjust the weighting coefficients in the predefined evaluation algorithm based on the collected monitoring data.

7. An automatic SQL tuning system combining a large language model and a database optimizer, characterized by: include: Query receiving module, used to receive SQL query statements input by users; The cache detection module is used to generate a query fingerprint based on the SQL query statement entered by the user, and detect whether there is an optimization result matching the query fingerprint in the cache. If so, the optimization result matching the query fingerprint is extracted from the cache and returned to the user; otherwise, the candidate generation module is called; The candidate generation module is used to input the SQL query statement and optimization instructions entered by the user into the large language model to generate multiple candidate SQL query statements; The semantic verification module is used to verify the semantic equivalence between the candidate SQL query statements and the SQL query statements input by the user, and to screen out the semantically equivalent candidate SQL query statements; The execution plan acquisition module is used to submit the selected candidate SQL query statements to the database optimizer, execute EXPLAIN and EXPLAIN ANALYZE, and obtain the execution plan and cost information of each candidate SQL query statement; An evaluation and selection module is used to calculate the evaluation score of each candidate SQL query statement based on the execution plan and cost information using a predefined evaluation algorithm; the predefined evaluation algorithm is a weighted function combined with the cost information, and the candidate SQL query statement with the highest evaluation score is selected as the optimization result; A cache update module is used to write the optimization result and its related execution data into the cache; the execution data includes the execution plan, cost information and evaluation score; The result return module is used to return the optimization result and its related execution data to the user.

8. The automatic SQL tuning system combining a large language model and a database optimizer according to claim 7, characterized in that: Also includes: The execution effect monitoring module is used to monitor the actual execution of the optimization results returned to the user and collect relevant monitoring data based on the cost information; Feedback data processing module, used to compare and analyze the collected monitoring data with the expected performance; Model optimization module, used to adjust the optimization instructions and parameters of the large language model based on the comparative analysis results; The evaluation algorithm update module is used to dynamically adjust the weighting coefficients in the predefined evaluation algorithm based on the collected monitoring data.

9. A computer device, characterized in that: The invention comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the steps of the automatic SQL tuning method combining a large language model and a database optimizer according to any one of claims 1 to 6 are implemented.

10. A storage medium, characterized in that: The storage medium stores an automatic SQL tuning program, which, when executed by at least one processor, implements the steps of the automatic SQL tuning method combining a large language model and a database optimizer according to any one of claims 1 to 6.

Images