Large model guiding type SQL script optimization system for execution feedback

Abstract

The invention relates to a large model guide type SQL script optimization system for execution feedback. The system comprises an execution plan perception modeling module, an execution feedback driven large model optimization module and a reinforcement learning driven strategy evolution module. According to the method, the mobility, the semantic consistency and the execution high performance of the SQL among various database platforms can be realized, and the intelligent processing capability and the operation efficiency of a database system in a complex service scene are remarkably improved.

Description

Technical Field

[0001] This invention belongs to the field of SQL script migration technology, and relates to a large-model guided SQL script optimization system, and more particularly to a large-model guided SQL script optimization system with execution feedback. Background Technology

[0002] With the deepening of data-driven development, enterprise database systems are undergoing unprecedented heterogeneous evolution. The widespread deployment of multi-source data access, multi-business module collaboration, and distributed architectures means that enterprise-level data platforms typically employ multiple database systems in parallel, such as MySQL, PostgreSQL, Oracle, and SQL Server, creating a complex and syntactically inconsistent multi-database ecosystem. While seemingly flexible and diverse, in practical engineering, achieving stable migration, semantic preservation, and high execution efficiency of SQL scripts in heterogeneous environments has become a key technical challenge requiring attention.

[0003] Different databases exhibit significant differences in syntax, function architecture, data type support, and execution optimization strategies, making it difficult to directly achieve the "write-and-use" development ideal in multi-platform scenarios. The same business logic often requires repeatedly writing SQL statements on multiple platforms and performing manual tuning, which is not only labor-intensive and demands high levels of expertise from development and operations personnel, but also prone to introducing maintenance risks and consistency issues. With the continuous growth of data scale and rapid business iteration, the highly redundant and dependent work patterns relying on manual migration and tuning are increasingly unable to balance efficiency and stability, impacting the agility and sustainable evolution of database systems to some extent.

[0004] Currently, SQL adaptation in heterogeneous environments primarily relies on rule-based syntax transformation mechanisms. These methods achieve automatic SQL statement conversion and platform migration by constructing a cross-database syntax mapping rule base. However, this process is highly dependent on manual configuration, resulting in a complex rule system, high maintenance costs, and difficulty in responding promptly to structural changes brought about by database version evolution and business logic adjustments. This limits its flexibility and scalability, impacting its effectiveness in complex enterprise environments.

[0005] To improve automation, some platforms have attempted to introduce template-driven SQL transformation tools, enabling rapid matching and refactoring through predefined syntax structures. While these methods offer efficiency advantages when handling scripts with high syntactic standardization and relatively clear structures, they suffer from reduced accuracy and reliability when dealing with complex semantic structures such as multi-level nesting, multi-table joins, window functions, and dynamic conditions. This lack of in-depth semantic parsing and context awareness often necessitates multiple manual corrections by developers, making it difficult to establish stable and reliable end-to-end migration capabilities.

[0006] Meanwhile, while some database vendors offer proprietary migration tools, their applicability is usually limited to their own ecosystems, offering limited support for ensuring semantic consistency across heterogeneous platforms, adjusting index strategies, and evaluating execution performance. When faced with large-scale script migrations, rewriting highly complex queries, or cross-platform deployment requirements, enterprises typically still need to invest significant manpower in script reconstruction and testing, a time-consuming and inefficient process that carries the risk of introducing semantic biases or performance fluctuations, putting pressure on system operation and maintenance.

[0007] In summary, existing SQL script migration and optimization methods still fall short in terms of automation, semantic preservation, and performance controllability in heterogeneous database environments, making it difficult to fully adapt to increasingly complex data scenarios and rapidly evolving business needs.

[0008] Large Language Models (LLMs) have shown great potential in semantic modeling and code generation, providing new ideas for intelligent SQL optimization. In recent years, LLMs have made significant progress in the fields of natural language understanding and code generation, promoting the development of Text-to-SQL technology based on pre-trained models. This enables users to express their query intent in natural language and generate semantically complete SQL scripts, with certain capabilities in syntax correction and structure construction.

[0009] Nevertheless, current applications of large language models in SQL writing and optimization largely remain at the "syntactic intelligence" level, lacking deep integration with the real database runtime environment. Models typically cannot directly obtain runtime feedback information such as execution plans, resource consumption, and index usage, making adaptive optimization based on performance metrics difficult. Furthermore, models have limited understanding of the database context, struggling to timely and comprehensively perceive table structure, field dependencies, and data characteristics. In complex scenarios, generated SQL scripts may still exhibit semantic biases or unsatisfactory execution efficiency.

[0010] In practical applications, the SQL statements generated by the model usually require multiple rounds of manual correction and optimization by developers to meet executability and performance requirements, making it difficult to form an automatic closed loop of "generation-feedback-optimization". Overall, the application of large language models and reinforcement learning technologies in SQL script optimization is still mainly auxiliary, and there is still considerable room for improvement in terms of stability, cross-platform adaptability, and performance guarantee mechanisms when facing production environments.

[0011] How to deeply integrate large language models with real database contexts, organically combine execution structure modeling, semantic representation and runtime feedback, and build a closed-loop optimization mechanism with structure awareness, semantic preservation and execution feedback linkage capabilities is a problem that needs to be further solved in the existing technology.

[0012] This invention is proposed against the backdrop of the aforementioned technologies, aiming to address the problems of insufficient automation, difficulty in maintaining semantics, and difficulty in ensuring stable performance during the migration and optimization of SQL scripts in heterogeneous database environments. It provides a large-scale model-guided SQL script optimization system with execution feedback that can be deployed in engineering practice, thereby improving the migration efficiency and execution performance of SQL scripts in multi-platform scenarios and reducing the cost of manual tuning.

[0013] A search revealed no patent documents of the same or similar prior art as this invention. Summary of the Invention

[0014] To address the shortcomings of existing technologies, this invention proposes a large-model guided SQL script optimization system with efficient, intelligent, and portable execution feedback for heterogeneous environments.

[0015] This invention is achieved through the following technical solution: A large-scale guided SQL script optimization system with execution feedback includes: The execution plan-aware modeling module is used to obtain the execution plan information of the input SQL statement from the database system and parse the execution plan information into a directed acyclic graph (DAG) structure; it is used to integrate a graph neural network on the DAG graph and use a graph attention mechanism to model key operation nodes, extract structural features and performance features, and output a structured representation vector; the output of the execution plan-aware modeling module is connected to the execution feedback-driven large model optimization module and the reinforcement learning-driven policy evolution module to provide them with structured feature vector input.

[0016] The large-scale model optimization module driven by execution feedback is used to construct a prompt containing structural constraints and performance hints based on the received structured feature vectors and original SQL statements, and input the prompt into the large language model to generate multiple semantically equivalent SQL candidate scripts; it is used to send the SQL candidate scripts to the target database system for execution or to perform execution plan analysis, obtain execution performance indicators, and calculate the comprehensive performance score of each candidate script; the input end of the large-scale model optimization module driven by execution feedback is connected to the execution plan awareness modeling module and the database system, and the output end is connected to the database system and the reinforcement learning-driven policy evolution module, used to submit candidate SQL scripts for execution and provide execution feedback results and comprehensive performance scores.

[0017] The reinforcement learning-driven policy evolution module receives the structured feature vector and the execution feedback result, fuses them to construct an environment state vector, and generates an SQL rewriting policy based on the environment state vector through a policy network. It uses the comprehensive performance score as a reward signal and employs a reinforcement learning algorithm to update the policy network parameters, achieving adaptive evolution of the rewriting policy. The output of the reinforcement learning-driven policy evolution module is connected to both the execution plan awareness modeling module and the execution feedback-driven large model optimization module. It outputs a structured modeling control signal to the execution plan awareness modeling module and a policy control signal to the execution feedback-driven large model optimization module, thereby forming a closed-loop optimization mechanism between execution structure modeling, candidate SQL generation and execution feedback, and policy evolution.

[0018] Moreover, the reinforcement learning-driven policy evolution module also acts on the execution plan perception modeling module and the large model optimization module through control signals to dynamically adjust the modeling dimension or candidate generation range, thereby realizing a self-evolutionary closed loop throughout the entire process.

[0019] Furthermore, the execution plan awareness modeling module includes: The execution plan parsing unit is used to parse the execution plan information returned by the database system and extract the raw data such as physical operators, operation order, table field information, estimated row count, I / O overhead, and join mode; the output of the execution plan parsing unit is connected to the graph structure construction unit. A graph structure construction unit is used to convert the execution plan information into a DAG structure; the output of the graph structure construction unit is connected to the node feature encoding unit. The node feature encoding unit is used to encode the features of each node in the graph; the output of the node feature encoding unit is connected to the graph attention modeling unit. The graph attention modeling unit is used to perform structure learning on the execution plan graph based on GAT; the output of the graph attention modeling unit is connected to the graph vector pooling unit. The graph vector pooling unit is used to aggregate all node embedding vectors to generate a global structure vector, which is used to represent the execution structure characteristics of the entire SQL statement.

[0020] Furthermore, the execution feedback-driven large model optimization module includes: The Prompt construction unit is used to construct the Prompt input content that guides the generation of the large language model based on the original SQL statement, structure vector, and preset template information; its output is connected to the language model generation unit. The language model generation unit receives the Prompt input constructed by the Prompt construction unit, calls the preset large language model to generate multiple candidate SQL scripts; its output is connected to the candidate SQL submission unit. The candidate SQL submission unit is used to send the candidate SQL statements generated by the language model generation unit to the target database system for trial operation or EXPLAIN analysis; its output is connected to the execution feedback extraction unit. The execution feedback extraction unit is used to extract key performance indicators from the information returned by the database and calculate the comprehensive performance score of the candidate SQL; its output is connected to the feedback-driven Prompt update unit and the reinforcement learning policy evolution module, respectively. The feedback-driven Prompt update unit is used to integrate structured feedback information into a readable format, update the Prompt content, guide the language model to regenerate a better SQL solution, and realize the optimization closed loop of "generation-execution-feedback-regeneration"; its output is connected to the Prompt construction unit to form a circular connection.

[0021] Furthermore, the reinforcement learning-driven policy evolution module includes: The state encoding unit is used to fuse the structure vector information from the execution plan modeling module with the execution feedback data to construct a high-dimensional representation of the current optimization state; its output is connected to the policy generation unit. The policy generation unit is used to call the policy network to output the probability distribution of candidate SQL rewriting actions based on the current state vector; its output is connected to the reward evaluation unit. The reward evaluation unit is used to assign a reward score to the selected action based on the actual execution result of the candidate SQL in the database. Its output is connected to the policy update unit. The policy update unit is used to optimize the policy network parameters and update the policy model weights based on the reward evaluation results using the policy gradient algorithm. The updated policy parameters are then output to the policy generation unit.

[0022] The advantages and beneficial effects of this invention are as follows: 1. This invention proposes a large-model guided SQL script optimization system with execution feedback, which can solve the problems of existing SQL scripts being difficult to migrate in heterogeneous database environments, having low execution performance, and poor semantic preservation capabilities. It achieves the portability, semantic consistency, and high performance of SQL across multiple database platforms, significantly improving the intelligent processing capabilities and operating efficiency of database systems in complex business scenarios.

[0023] 2. Significantly improves SQL semantic understanding and structural modeling capabilities. To address the problem that existing large language models only generate syntax and struggle to understand SQL execution semantics, this invention introduces a GAT (Generated Acyclic Graph) modeling mechanism driven by the execution plan graph. This mechanism transforms the physical execution plan generated by the database optimizer into a directed acyclic graph, enabling structured modeling of query operators, data flow paths, and execution costs. This mechanism allows the system to deeply perceive operator dependencies and performance bottlenecks in actual SQL statement execution, overcoming the limitations of traditional models that cannot perceive database structure and context, and significantly improving the model's semantic modeling accuracy and structure awareness.

[0024] 3. Achieve a performance adaptive closed loop of generation-feedback-optimization. To address the issues of existing technologies where models cannot obtain execution feedback and cannot self-optimize based on performance metrics, this invention proposes an execution feedback-driven large-model optimization mechanism. This mechanism dynamically injects runtime performance metrics returned by the database into the Prompt, guiding the large language model to perform the next round of SQL rewriting and generation. The system forms an intelligent optimization closed loop through a "generation-execution-feedback-regeneration" process, achieving synergistic improvement in SQL semantic consistency and performance optimization. This mechanism effectively overcomes the limitations of existing technologies where large models and database runtime environments are disconnected, enabling the system to possess adaptive optimization capabilities based on real performance feedback.

[0025] 4. Achieve strategy self-learning and cross-platform adaptability To address the shortcomings of existing SQL rewriting strategies, which rely on human experience and lack an evolutionary mechanism, this invention introduces a reinforcement learning-driven policy evolution module. This module models the SQL optimization process as a Markov Decision Process (MDP) and dynamically updates the policy network using execution feedback as a reward signal. The system automatically learns the optimal rewriting strategy through multiple rounds of optimization iterations, achieving generalization and transfer capabilities across database platforms and load scenarios. This fundamentally overcomes the deficiencies of traditional optimization methods, which require manual intervention and cannot continuously learn.

[0026] 5. Possesses deployability and engineering implementation capabilities. To address the shortcomings of existing technologies in terms of stability and adaptability to production environments, this invention adopts a modular system architecture that supports embedded database middleware, SQL analysis platforms, and data governance tools, enabling online rewriting, semantic validation, and performance enhancement of SQL statements. This system features end-to-end automation, high scalability, and high stability, allowing for stable operation in high-concurrency scenarios such as data warehouses, OLAP analysis, and reporting systems. It effectively reduces manual tuning costs and significantly improves the efficiency of SQL development and maintenance. Attached Figure Description

[0027] Figure 1 This is a schematic diagram of the structure and data flow of the SQL script optimization system of the present invention; Figure 2 This is a structural diagram of the execution plan perception modeling module of the present invention; Figure 3 This is a structural diagram of the large model optimization module driven by execution feedback in this invention; Figure 4 This is a structural diagram of the reinforcement learning-driven policy evolution module of the present invention. Detailed Implementation

[0028] The embodiments of the present invention will be further described in detail below with reference to the accompanying drawings: This invention constructs a large-scale model-guided SQL script optimization system covering execution feedback from "execution structure modeling – semantic preservation generation – performance-driven evolution," such as... Figure 1 As shown, it includes: The execution plan-aware modeling module is used to obtain the execution plan information of the input SQL statement from the database system and parse the execution plan information into a directed acyclic graph (DAG) structure; it is used to integrate a graph neural network on the DAG graph and use a graph attention mechanism to model key operation nodes, extract structural features and performance features, and output a structured representation vector; the output of the execution plan-aware modeling module is connected to the execution feedback-driven large model optimization module and the reinforcement learning-driven policy evolution module to provide them with structured feature vector input.

[0029] The large-scale model optimization module driven by execution feedback is used to construct a prompt containing structural constraints and performance hints based on the received structured feature vectors and original SQL statements, and input the prompt into the large language model to generate multiple semantically equivalent SQL candidate scripts; it is used to send the SQL candidate scripts to the target database system for execution or to perform execution plan analysis, obtain execution performance indicators, and calculate the comprehensive performance score of each candidate script; the input end of the large-scale model optimization module driven by execution feedback is connected to the execution plan awareness modeling module and the database system, and the output end is connected to the database system and the reinforcement learning-driven policy evolution module, used to submit candidate SQL scripts for execution and provide execution feedback results and comprehensive performance scores.

[0030] The reinforcement learning-driven policy evolution module receives the structured feature vector and the execution feedback result, fuses them to construct an environment state vector, and generates an SQL rewriting policy based on the environment state vector through a policy network. It uses the comprehensive performance score as a reward signal and employs a reinforcement learning algorithm to update the policy network parameters, achieving adaptive evolution of the rewriting policy. The output of the reinforcement learning-driven policy evolution module is connected to both the execution plan awareness modeling module and the execution feedback-driven large model optimization module. It outputs a structured modeling control signal to the execution plan awareness modeling module and a policy control signal to the execution feedback-driven large model optimization module, thereby forming a closed-loop optimization mechanism between execution structure modeling, candidate SQL generation and execution feedback, and policy evolution.

[0031] The functions and roles of each module within the system are further explained below: 1. Execution Plan Awareness Modeling Module: This module is used to obtain the execution plan information of the input SQL statement from the database system and parse it into a Directed Acyclic Graph (DAG) structure. Internally, the module integrates a graph neural network and uses a graph attention network (GAT) mechanism to model the key operation nodes in the DAG, extract their structural and performance features, and output a unified representation vector.

[0032] The output of this module is connected to the large model optimization module to provide structured feature vector input, supporting the subsequent generation of SQL rewriting strategies.

[0033] 2. Execute the feedback-driven large model optimization module: This is used to receive SQL structured representation vectors from the execution plan-aware modeling module, guide the large language model to generate multiple semantically equivalent SQL candidate rewriting schemes under the Prompt-based interactive optimization framework, and send the SQL candidate statements to the target database system for execution, and collect execution feedback data such as response time, I / O consumption, and CPU cost to evaluate the performance of each candidate SQL rewriting scheme.

[0034] The execution feedback information is simultaneously sent back to the reinforcement learning module and the current module to update the generation strategy and improve the quality of the next round of candidate SQL, forming an optimization closed-loop mechanism of "generation-execution-feedback-regeneration".

[0035] 3. Reinforcement learning-driven policy evolution module: This module models SQL rewriting operations as "actions" in reinforcement learning, using the execution performance data returned by the database as "reward signals" to build a long-term adaptive optimization model for multiple database platforms. The module continuously adjusts the rewriting strategy parameters, guiding the large model to generate better solutions and improving the system's generalization and optimization capabilities under different query patterns, data distributions, or operating environments.

[0036] The reinforcement learning module acts on the modeling and optimization modules through control signals to dynamically adjust the modeling dimensions or the candidate generation range, thereby achieving a self-evolutionary closed loop throughout the entire process.

[0037] like Figure 1 As shown, the three modules are tightly coupled through data transmission and control signals, forming an optimization system. The modeling module is responsible for structural understanding, the large model optimization module is responsible for scheme generation and feedback collection, and the reinforcement learning module is responsible for policy updates and system evolution. Together, they construct a continuously learning and adaptively evolving SQL rewriting system. Furthermore, this system supports embedded deployment and can be integrated into database middleware, data governance platforms, or database migration tools. It is suitable for various heterogeneous database platforms, combining theoretical innovation with engineering practicality, and possesses significant intelligent optimization value and promising prospects for widespread application.

[0038] In this embodiment, the execution plan awareness modeling module includes: The execution plan parsing unit is used to parse the execution plan information returned by the database system and extract the raw data such as physical operators, operation order, table field information, estimated row count, I / O overhead, and join mode; the output of the execution plan parsing unit is connected to the graph structure construction unit. A graph structure construction unit is used to convert the execution plan information into a DAG structure; the output of the graph structure construction unit is connected to the node feature encoding unit. The node feature encoding unit is used to encode the features of each node in the graph; the output of the node feature encoding unit is connected to the graph attention modeling unit. The graph attention modeling unit is used to perform structure learning on the execution plan graph based on GAT; the output of the graph attention modeling unit is connected to the graph vector pooling unit. The graph vector pooling unit is used to aggregate all node embedding vectors to generate a global structure vector, which is used to represent the execution structure characteristics of the entire SQL statement.

[0039] The following section further explains the composition, functions, and roles of the execution plan graph-driven GAT modeling module: The SQL automatic rewriting system of this invention includes an execution plan-aware modeling module. This module is used to convert the SQL execution plan generated by the database system into a structured graph representation and construct a unified structural semantic vector as input for subsequent optimization and strategy learning. The input end of this module is connected to the database system to receive execution plan information; the output end is connected to the execution feedback-driven large model optimization module to transmit the graph vector.

[0040] The execution plan awareness modeling module includes the following functional units, which are connected in the following order: (1) Unit 1: Execution Plan Analysis Unit This unit is used to parse the execution plan information returned by the database system, extracting raw data such as physical operators, operation order, table field information, estimated row count, I / O overhead, and join methods, providing a foundation for subsequent graph structure construction. The output of this unit is connected to the second unit.

[0041] (2) Second Unit: Graph Structure Construction Unit This unit is used to convert the execution plan information into a DAG structure, where the nodes of the graph represent various physical operators (such as sequential scan, hash join, etc.), and the edges of the graph represent the dependencies and execution order between operations. This unit receives the operator information output by the first unit, and its output is connected to the third unit.

[0042] (3) Third unit: Node feature coding unit This unit is used to encode features for each node in the graph. These features include, but are not limited to, operator type, input table, field attributes, estimated cost, connection depth, and selectivity. The unit encodes these features into a dense vector, which is used as input for neural network modeling. Its output is connected to the fourth unit.

[0043] (4) Unit 4: Graph Attention Modeling Unit This unit is used for structure learning of the execution plan graph based on GAT. It introduces an attention mechanism to weighted aggregate neighbor information of nodes, generating a contextual embedding representation for each node. The fourth unit receives the node feature vectors output from the third unit and performs embedding propagation across the entire graph.

[0044] (5) Unit 5: Graph Vector Pooling Unit This module aggregates all node embedding vectors output from the fourth unit to generate a global structure vector, which represents the execution structure characteristics of the entire SQL statement. This structure vector serves as the final output of this module and is input into the large model optimization module to participate in candidate SQL solution generation and feedback closed-loop control.

[0045] like Figure 2 As shown, through the sequential collaborative processing of the above five functional units, the execution plan awareness modeling module can complete the entire process modeling from the original execution plan to the structural semantic representation vector, realize the structural understanding of the SQL execution path and the extraction of performance features, and provide a unified and highly adaptable input foundation for subsequent modules.

[0046] In this embodiment, the feedback-driven large model optimization module includes: The Prompt construction unit is used to construct the Prompt input content that guides the generation of the large language model based on the original SQL statement, structure vector, and preset template information; its output is connected to the language model generation unit. The language model generation unit receives the Prompt input constructed by the Prompt construction unit, calls the preset large language model to generate multiple candidate SQL scripts; its output is connected to the candidate SQL submission unit. The candidate SQL submission unit is used to send the candidate SQL statements generated by the language model generation unit to the target database system for trial operation or EXPLAIN analysis; its output is connected to the execution feedback extraction unit. The execution feedback extraction unit is used to extract key performance indicators from the information returned by the database and calculate the comprehensive performance score of the candidate SQL; its output is connected to the feedback-driven Prompt update unit and the reinforcement learning policy evolution module, respectively. The feedback-driven Prompt update unit is used to integrate structured feedback information into a readable format, update the Prompt content, guide the language model to regenerate a better SQL solution, and realize the optimization closed loop of "generation-execution-feedback-regeneration"; its output is connected to the Prompt construction unit to form a circular connection.

[0047] The following section further explains the composition, function, and role of the feedback-driven large model optimization module: The SQL automatic rewriting system provided by this invention includes an execution feedback-driven large model optimization module. This module is used to drive a large language model to generate an SQL rewriting scheme that meets the goals of semantic consistency and performance optimization based on the execution plan structure representation and runtime feedback information, thereby realizing iterative intelligent optimization of SQL statements.

[0048] The input end of the module is connected to the execution plan awareness modeling module and the database system to receive structure vectors and actual execution feedback data; the output end is connected to the database system and the reinforcement learning policy evolution module to submit candidate SQL statements for execution and provide feedback information.

[0049] The large model optimization module includes the following functional units, which are connected in the following order: (1) First unit: Prompt construction unit This unit is used to construct the Prompt input content to guide the generation of the large language model, based on the original SQL statement, structure vector, and preset template information. This Prompt includes execution context information, structural feature summaries, and historical feedback summaries, serving as input conditions for subsequent language model generation. The output of this unit is connected to the second unit.

[0050] (2) Unit 2: Language Model Generation Unit This unit receives the Prompt input constructed by the first unit, calls a preset large language model (such as LLM) to generate multiple candidate SQL scripts. This unit supports sequence-to-sequence semantic modeling and outputs SQL candidate schemes that are different in form but consistent in semantics. Its output is connected to the third unit.

[0051] (3) Third Unit: Candidate SQL Submission Unit This unit is used to send the candidate SQL statements generated by the second unit to the target database system for trial operation or EXPLAIN analysis. This unit interacts with the database and synchronously obtains execution feedback, including operational metrics such as response time, I / O cost, index usage, and number of rows scanned. The output of this unit is connected to the fourth unit.

[0052] (4) Fourth Unit: Execution Feedback Extraction Unit This unit is used to extract key performance indicators from information returned from the database and calculate the comprehensive performance score of candidate SQL queries. Based on a pre-defined evaluation function, this unit weights dimensions such as latency, resource consumption, and index usage, outputting structured feedback results to guide the optimization of the generation strategy. The output of this unit connects to the fifth unit and the reinforcement learning policy evolution module.

[0053] (5) Unit 5: Feedback-driven Prompt update unit This is used to integrate structured feedback information into a readable format, update the Prompt content, guide the language model to regenerate a better SQL solution, and achieve an optimization closed loop of "generation-execution-feedback-regeneration". The updated content includes performance bottleneck prompts, optimization suggestion prompts, execution cost summaries, etc., and is output to the first unit to form a circular connection.

[0054] like Figure 3 As shown, through the interactive processing of the above five functional units, the module realizes an SQL rewriting mechanism based on large model generation and execution feedback control. This not only improves the performance controllability of the generated results, but also ensures the semantic consistency and adaptability of the SQL rewriting process, providing high-quality training data and feedback signals for the strategy optimization module.

[0055] In this embodiment, the reinforcement learning-driven policy evolution module includes: The state encoding unit is used to fuse the structure vector information from the execution plan modeling module with the execution feedback data to construct a high-dimensional representation of the current optimization state; its output is connected to the policy generation unit. The policy generation unit is used to call the policy network to output the probability distribution of candidate SQL rewriting actions based on the current state vector; its output is connected to the reward evaluation unit. The reward evaluation unit is used to assign a reward score to the selected action based on the actual execution result of the candidate SQL in the database. Its output is connected to the policy update unit. The policy update unit is used to optimize the policy network parameters and update the policy model weights based on the reward evaluation results using the policy gradient algorithm. The updated policy parameters are then output to the policy generation unit.

[0056] The following section further explains the composition, function, and role of the reinforcement learning-driven policy evolution module: The SQL automatic rewriting system of the present invention further includes a reinforcement learning-driven policy evolution module. This module is used to build a self-learning optimization mechanism based on execution feedback, model the SQL rewriting process as a Markov decision process (MDP), and improve the system's adaptability and long-term optimization capabilities in multiple platforms and multiple query scenarios through iterative updates of the policy network.

[0057] The input end of the module is connected to the execution feedback extraction unit and the execution plan modeling module respectively, and is used to receive structure vectors and feedback information; the output end is connected to the language model optimization module and the modeling module, and is used to transmit strategy parameters and control commands.

[0058] The policy evolution module includes the following functional units, which are connected in sequence to form an overall reinforcement learning closed loop: (1) First unit: State coding unit This unit is used to fuse structure vector information from the execution plan modeling module with execution feedback data to construct a high-dimensional representation of the current optimization state. This state vector serves as input to the reinforcement learning model, reflecting the contextual features of the current SQL statement, such as its structure, performance, and historical behavior. The output of this unit connects to the second unit.

[0059] (2) Second unit: Strategy generation unit This unit is used to invoke the probability distribution of candidate SQL rewriting actions output by the policy network based on the current state vector. These actions include, but are not limited to: adjusting the JOIN order, introducing hints, rewriting the subquery structure, and replacing operators. This unit executes the policy based on probability sampling and outputs the results to the third unit.

[0060] (3) Unit 3: Reward Evaluation Unit This unit is used to reward and score selected actions based on the actual execution results of candidate SQL statements in the database. The reward function comprehensively considers the performance improvement (such as reduced running time) and the degree of semantic preservation (such as consistency of result sets) to calculate the immediate reward value during the reinforcement learning process. The output of this unit is connected to the fourth unit.

[0061] (4) Unit 4: Strategy Update Unit This unit optimizes the policy network parameters and updates the policy model weights based on the reward evaluation results using the policy gradient algorithm. It introduces an experience replay mechanism and mini-batch training, sampling effective trajectories from historical interactions to improve the model's convergence speed and stability. The updated policy parameters are then output to the second unit.

[0062] Figure 4 The diagram illustrates the structure of the module. Through the coordinated operation of the four functional units, the reinforcement learning-driven policy evolution module can continuously optimize the SQL rewriting strategy, enabling the system to possess long-term learning capabilities and cross-platform generalization capabilities. Each round of optimization serves as a learning sample, and the strategy is continuously iterated and adjusted, thereby improving the execution efficiency and platform adaptability of the final SQL script.

[0063] Furthermore, this module collaborates with the previous two modules to construct a three-layer closed-loop architecture: Module 1 provides structured state coding, Module 2 provides real execution feedback, and this module performs strategy evolution based on these, realizing an intelligent tuning path throughout the entire process from perception and optimization to evolution. This mechanism supports robust implementation of automatic SQL rewriting in complex query and dynamic data scenarios, demonstrating significant engineering deployability and commercial transformation value.

[0064] Example 1: This invention provides an SQL script optimization system suitable for heterogeneous database environments. Its core consists of three main modules: a GAT modeling module driven by an execution plan graph, an execution feedback optimization module guided by a large model, and a reinforcement learning-driven policy evolution module. The following description, based on engineering practice, details the implementation from three aspects: system deployment, module data flow, and optimization strategies.

[0065] 1 System Deployment Environment This system is deployed on an integrated optimization platform. The front end receives raw SQL scripts submitted by users, while the back end consists of an optimization engine (including an LLM API call interface, an execution feedback collector, a GAT modeler, and RL training units), and is compatible with multiple database systems (such as MySQL, PostgreSQL, and Oracle). The system supports GPU-accelerated environments (recommended configuration: NVIDIA A100 40G, CUDA 11.6). The core logic is written in Python, the deep learning part is implemented in PyTorch, and the database interaction module is encapsulated in SQLAlchemy.

[0066] 2. Module 1: GAT Modeling for Execution Plan Charts The original SQL is first processed by the ANTLR parser to generate an AST, and the AST is then mapped into an execution plan graph by a transformer. .in This is a set of operator nodes (such as Hash Join, Seq Scan, Nested Loop, etc.). This indicates data flow / control flow dependencies. Each node contains the following attributes: operator type (op_type), input / output table information (table_ref), estimated cost (cost), and estimated number of output rows (cardinality).

[0067] The above graph structure is input into a multilayer graph attention network (GAT), and its node update formula is as follows: (1) Attention weight The calculation method is as follows: (2) Finally, the embedding vector is obtained through full graph pooling. , is used to represent the structural characteristics of the current SQL statement.

[0068] Module 2: Execution Feedback Optimization Guided by a Large Model The structure vector output by GAT is used as a Prompt condition and passed to a large language model (such as DeepSeek LLM) to generate multiple candidate SQL rewrite versions. Each SQL statement is injected into a real database for execution, and the system records feedback information such as latency, I / O cost, index hit rate, and error codes to calculate a comprehensive performance score. (3) The score serves as the sorting criterion for the SQL script and also as a reward signal for the downstream reinforcement learning module.

[0069] The module process includes: Prompt constructor (generates context based on structure vector) SQL Generator (LLM-based Output Candidates) Feedback collector (connects to the database and collects execution metrics) Policy controller (scoring and ranking logic) Module 3: Evolution of Reinforcement Learning-Driven Optimization Strategies The entire optimization process is modeled as an MDP, where: state From structure vector Execution feedback assembly; action This represents a candidate SQL script; The reward function is: (4) Reinforcement learning algorithms use the policy gradient method to optimize the objective function: (5) The convergence speed is improved by using an experience replay buffer + mini-batch strategy.

[0070] 5. Comprehensive Closed-Loop Execution Process The entire system operates as follows: (1) Input SQL → AST parsing → Execution plan diagram → GAT encoding → Obtain the structure representation; (2) Prompt + LLM → Generate multiple SQL candidates; (3) Inject into the database → Get feedback → Score + Update strategy; (4) Feedback is fed into the RL module to learn the strategy with the best long-term returns; (5) Form an optimization closed loop and adaptively iteratively update the SQL output.

[0071] This invention combines the three characteristics of structure awareness, semantic preservation, and execution feedback linkage, making it highly implementable and suitable for SQL-intensive scenarios such as big data systems, government cloud platforms, and financial risk control engines.

[0072] It should be emphasized that the embodiments described in this invention are illustrative rather than limiting. Therefore, this invention includes, but is not limited to, the embodiments described in the specific implementation. Any other implementations derived by those skilled in the art based on the technical solutions of this invention are also within the scope of protection of this invention.

Claims

1. A large-scale model-guided SQL script optimization system with execution feedback, characterized in that: include: The execution plan-aware modeling module is used to obtain the execution plan information of the input SQL statement from the database system and parse the execution plan information into a directed acyclic graph (DAG) structure. The module is used to integrate a graph neural network on the DAG graph and use a graph attention mechanism to model key operation nodes, extract structural features and performance features, and output a structured representation vector; the output of the execution plan perception modeling module is connected to the execution feedback-driven large model optimization module and the reinforcement learning-driven policy evolution module to provide structured feature vector input to both. The large model optimization module, which is executed based on the received structured feature vector and the original SQL statement, constructs a prompt containing structural constraints and performance hints, and inputs the prompt into the large language model to generate multiple semantically equivalent SQL candidate scripts. Used to send the SQL candidate scripts to the target database system for execution or to perform execution plan analysis, obtain execution performance indicators, and calculate the comprehensive performance score of each candidate script; The input of the execution feedback-driven large model optimization module is connected to the execution plan awareness modeling module and the database system, and the output is connected to the database system and the reinforcement learning-driven policy evolution module. It is used to submit candidate SQL scripts for execution and provide execution feedback results and comprehensive performance scores. The reinforcement learning-driven policy evolution module is used to receive the structured feature vector and the execution feedback result, fuse the two to construct an environment state vector, and generate an SQL rewriting policy based on the environment state vector through a policy network. The comprehensive performance score is used as a reward signal to update the policy network parameters using a reinforcement learning algorithm, thereby achieving adaptive evolution of the rewritten policy. The output of the reinforcement learning-driven policy evolution module is connected to the execution plan awareness modeling module and the execution feedback-driven large model optimization module, respectively. It is used to output structural modeling control signals to the execution plan awareness modeling module and policy control signals to the execution feedback-driven large model optimization module, thereby forming a closed-loop optimization mechanism between execution structural modeling, candidate SQL generation and execution feedback, and policy evolution.

2. The large-model guided SQL script optimization system with execution feedback as described in claim 1, characterized in that: The reinforcement learning-driven policy evolution module also acts on the execution plan perception modeling module and the large model optimization module through control signals to dynamically adjust the modeling dimension or candidate generation range, thereby realizing a closed loop of self-evolution throughout the entire process.

3. The large-scale model-guided SQL script optimization system with execution feedback as described in claim 1, characterized in that: The execution plan awareness modeling module includes: The execution plan parsing unit is used to parse the execution plan information returned by the database system and extract the raw data such as physical operators, operation order, table field information, estimated row count, I / O overhead, and join mode; the output of the execution plan parsing unit is connected to the graph structure construction unit. A graph structure construction unit is used to convert the execution plan information into a DAG structure; the output of the graph structure construction unit is connected to the node feature encoding unit. The node feature encoding unit is used to encode the features of each node in the graph; the output of the node feature encoding unit is connected to the graph attention modeling unit. The graph attention modeling unit is used to perform structure learning on the execution plan graph based on GAT; the output of the graph attention modeling unit is connected to the graph vector pooling unit. The graph vector pooling unit is used to aggregate all node embedding vectors to generate a global structure vector, which is used to represent the execution structure characteristics of the entire SQL statement.

4. The large-scale model-guided SQL script optimization system with execution feedback as described in claim 1, characterized in that: The execution feedback-driven large model optimization module includes: The Prompt construction unit is used to construct the Prompt input content that guides the generation of the large language model based on the original SQL statement, structure vector, and preset template information; its output is connected to the language model generation unit. The language model generation unit receives the Prompt input constructed by the Prompt construction unit, calls the preset large language model to generate multiple candidate SQL scripts; its output is connected to the candidate SQL submission unit. The candidate SQL submission unit is used to send the candidate SQL statements generated by the language model generation unit to the target database system for trial operation or EXPLAIN analysis; its output is connected to the execution feedback extraction unit. The execution feedback extraction unit is used to extract key performance indicators from the information returned by the database and calculate the comprehensive performance score of the candidate SQL; its output is connected to the feedback-driven Prompt update unit and the reinforcement learning policy evolution module, respectively. The feedback-driven Prompt update unit is used to integrate structured feedback information into a readable format, update the Prompt content, guide the language model to regenerate a better SQL solution, and realize the optimization closed loop of "generation-execution-feedback-regeneration"; its output is connected to the Prompt construction unit to form a circular connection.

5. The large-model guided SQL script optimization system with execution feedback as described in claim 1, characterized in that: The reinforcement learning-driven policy evolution module includes: The state encoding unit is used to fuse the structure vector information from the execution plan modeling module with the execution feedback data to construct a high-dimensional representation of the current optimization state; its output is connected to the policy generation unit. The policy generation unit is used to call the policy network to output the probability distribution of candidate SQL rewriting actions based on the current state vector; its output is connected to the reward evaluation unit. The reward evaluation unit is used to score the selected action based on the actual execution result of the candidate SQL in the database; its output is connected to the policy update unit. The policy update unit is used to optimize the policy network parameters and update the policy model weights based on the reward evaluation results using the policy gradient algorithm. The updated policy parameters are then output to the policy generation unit.

Images