SQL statement optimization method and device based on analysis model, equipment and medium

Abstract

The application relates to an SQL statement optimization method and device based on an analysis model, equipment and a storage medium. The method comprises the following steps: acquiring an SQL statement and an execution plan of the SQL statement, extracting a semantic feature vector of the SQL statement and an execution plan feature vector of the execution plan, inputting the semantic feature vector and the execution plan feature vector into a pre-trained analysis model, obtaining performance bottleneck information and an execution performance prediction value, generating a candidate index optimization scheme and a candidate query rewriting scheme based on the performance bottleneck information and the execution performance prediction value, and determining a target optimization strategy of the SQL statement based on the candidate index optimization scheme and the candidate query rewriting scheme. The application can improve the accuracy of SQL statement optimization.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, apparatus, device and storage medium for optimizing SQL statements based on an analysis model. Background Technology

[0002] Currently, traditional SQL optimization methods mainly rely on the database administrator's experience or the database system's built-in optimizer. These methods often struggle to accurately identify performance bottlenecks when faced with complex queries, resulting in limited optimization effectiveness and low efficiency. Existing technologies typically divide SQL optimization into two independent directions: query rewriting, which generates equivalent but more efficient SQL statements through syntax transformation; and index recommendation, which suggests index creation based on table structure and query patterns. However, there is a clear disconnect between these two methods: query rewriting does not fully consider index availability, and index recommendation fails to tailor optimization to the specific execution characteristics of the query, leading to poor combined optimization results.

[0003] Therefore, there is an urgent need for an intelligent SQL optimization method that can integrate SQL semantic analysis and execution plan parsing, and achieve collaborative decision-making between query rewriting and index optimization, in order to improve the accuracy of database query performance optimization. Summary of the Invention

[0004] In view of the above, this application provides a method, apparatus, device and storage medium for optimizing SQL statements based on an analytical model, the purpose of which is to solve the above-mentioned technical problems.

[0005] Firstly, this application provides a SQL statement optimization method based on an analysis model, the method comprising: Obtain the SQL statement and its execution plan, and extract the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan; The semantic feature vector and execution plan feature vector are input into a pre-trained analysis model to obtain performance bottleneck information and execution performance predictions. Based on performance bottleneck information and execution performance predictions, candidate index optimization schemes and candidate query rewriting schemes are generated. Based on candidate index optimization schemes and candidate query rewriting schemes, the target optimization strategy for SQL statements is determined.

[0006] Secondly, this application provides an SQL statement optimization device based on an analysis model, which includes: Extraction module: used to obtain the SQL statement and its execution plan, and extract the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan; Analysis module: Used to input semantic feature vectors and execution plan feature vectors into a pre-trained analysis model to obtain performance bottleneck information and execution performance predictions; The generation module is used to generate candidate index optimization schemes and candidate query rewriting schemes based on performance bottleneck information and execution performance predictions. Optimization module: Used to determine the target optimization strategy for SQL statements based on candidate index optimization schemes and candidate query rewriting schemes.

[0007] Thirdly, this application provides an electronic device, including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus; Memory, used to store computer programs; When a processor executes a program stored in memory, it implements the steps of the SQL statement optimization method based on the analysis model as described in any embodiment of the first aspect.

[0008] Fourthly, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the steps of the SQL statement optimization method based on the analysis model as described in any embodiment of the first aspect.

[0009] The technical solutions provided in this application have the following advantages compared with the prior art: By constructing an analytical model that integrates SQL semantic features and execution plan features, we achieved accurate identification of SQL performance bottlenecks and accurate prediction of execution performance, solving the problem of the separation between semantic analysis and execution plan analysis in traditional methods. Through cross-modal feature alignment and self-attention mechanisms, we established a deep association between SQL statement terms and execution plan operator nodes, improving the accuracy of performance bottleneck location. Based on performance bottleneck information, we collaboratively generated candidate index optimization schemes and candidate query rewriting schemes. By verifying the feasibility and scoring the benefits of the joint optimization strategy, we achieved collaborative decision-making for query rewriting and index optimization, avoiding the limitations of single optimization methods and improving the accuracy and automation of SQL optimization. Attached Figure Description

[0010] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0011] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0012] Figure 1 This is a flowchart illustrating a preferred embodiment of the SQL statement optimization method based on the analysis model of this application. Figure 2 This is a schematic diagram of a preferred embodiment of the SQL statement optimization device based on the analysis model of this application; Figure 3 This is a schematic diagram of a preferred embodiment of the electronic device of this application; The realization of the purpose, functional features and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0013] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application. All other embodiments obtained by those skilled in the art based on the embodiments in this application without inventive effort are within the scope of protection of this application.

[0014] It should be noted that the use of terms such as "first" and "second" in this application is for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined as "first" or "second" may explicitly or implicitly include at least one of those features. Furthermore, the technical solutions of the various embodiments can be combined with each other, but this must be based on the ability of those skilled in the art to implement them. If the combination of technical solutions is contradictory or impossible to implement, such a combination of technical solutions should be considered non-existent and not within the scope of protection claimed in this application.

[0015] Reference Figure 1 The diagram shown is a flowchart illustrating an embodiment of the SQL statement optimization method based on an analysis model according to this application. The method is executed by an electronic device, which can be implemented by a software system and / or a hardware system. The SQL statement optimization method based on the analysis model includes: Step S10: Obtain the SQL statement and its execution plan, and extract the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan; Step S20: Input the semantic feature vector and execution plan feature vector into the pre-trained analysis model to obtain performance bottleneck information and execution performance prediction values; Step S30: Based on performance bottleneck information and execution performance predictions, generate candidate index optimization schemes and candidate query rewriting schemes; Step S40: Based on the candidate index optimization scheme and the candidate query rewriting scheme, determine the target optimization strategy for the SQL statement.

[0016] In this embodiment, an SQL statement refers to a standard Structured Query Language instruction submitted by the user to the database, used to express the intent to query, update, or define data. An execution plan is an internal operation flow description generated by the database optimizer to execute the SQL statement. It is usually presented in a tree structure and includes the execution order, access path, estimated row count, and cost information of each operator such as table scan, index lookup, hash join, and sorting. The purpose of obtaining these two types of data is to construct a complete analysis context that simultaneously includes user intent and system behavior. Specifically, the system actively captures the original SQL statement through the database interface and synchronously triggers or simulates its execution to obtain a real or near-real execution plan. For example, when a query statement involving multiple table joins and time range filtering is received, the system not only records the SQL text but also obtains key details displayed in its execution plan, such as the join type, whether each table uses an index scan, and whether a full table scan exists. Subsequently, the SQL statement is lexically and syntactically parsed to construct an abstract syntax tree, from which key semantic elements are extracted, including the base tables involved, join predicates, filter predicates, projection columns, etc. These elements are then mapped into fixed-dimensional semantic feature vectors through predefined encoding rules.

[0017] Simultaneously, the execution plan undergoes structured parsing, traversing each node of the execution plan tree to extract attributes such as operator type, input table name, index name used, estimated number of output rows, actual execution time, and buffer hit rate. These attributes are then organized into execution plan feature vectors aligned with semantic feature vectors. This feature extraction process can employ a rule-based parser or combine a lightweight machine learning model to embed the syntax tree and plan tree. The specific implementation can be flexibly configured according to the database type and performance requirements, thereby providing a structured input representation for subsequent analysis models.

[0018] After extracting the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan, the semantic feature vector and the execution plan feature vector are input into a pre-trained deep learning analysis model to obtain performance bottleneck information and execution performance predictions. The analysis model is a multimodal neural network trained on a large amount of historical SQL execution logs, aiming to learn the complex mapping relationship between the SQL logical structure and its physical execution efficiency. Specifically, the model first encodes the semantic feature vector and the execution plan feature vector separately. For the semantic part, a Transformer or graph neural network can be used to process the topology of the abstract syntax tree; for the execution plan part, it can be treated as a directed acyclic graph, and a graph attention network or graph convolutional network is used for node embedding aggregation. Subsequently, the embedding vectors of the two modalities are fed into a fully connected layer through a cross-attention mechanism or concatenation, ultimately outputting two types of results. One type is the performance bottleneck information output by the classification task, such as fine-grained labels like missing indexes on highly selective columns, intermediate result bloat due to join order, and unused indexes in sorting; the other type is the execution performance prediction value output by the regression task, i.e., the expected end-to-end execution time of the SQL in the current environment, in milliseconds. For example, if the execution plan shows that a certain time field is used for range filtering but not indexed, the model might output a bottleneck label indicating that the field lacks a valid index and predict an execution time of 1850 milliseconds. The model's training data comes from a long-running database monitoring system. Each sample contains SQL text, execution plan, actual execution time, and manually labeled bottleneck types. End-to-end training is performed using supervised learning.

[0019] Based on performance bottleneck information and execution performance predictions, the system generates candidate index optimization schemes and candidate query rewriting schemes. Specifically, the system maintains an extensible optimization strategy engine, which includes an index recommendation module and a query rewriting module. The index recommendation module matches a predefined index template library based on keywords in the performance bottleneck information. For example, if the bottleneck information indicates that a column has a high-selectivity range query but is not using an index, the system will check the data distribution characteristics of that column and generate corresponding candidate index optimization schemes. If multiple filtering conditions exist simultaneously, a composite index scheme may be generated. The query rewriting module triggers rewriting rules based on inefficient patterns exposed in the execution plan feature vector. For example, if the original SQL contains correlated subqueries and the execution plan shows multiple scans of the same table, the system will apply subquery-to-join rules to generate a semantically equivalent but more efficient rewritten form. All generated candidate schemes maintain semantic equivalence to the original SQL and are ensured syntactic correctness through a built-in SQL validator.

[0020] Based on candidate index optimization and query rewriting schemes, the system determines the target optimization strategy for the SQL statement. Specifically, all candidate schemes undergo feasibility verification. Index schemes are checked for duplicate indexes, exceeding the single-table index limit, and involving data types that do not support indexing. Query rewriting schemes are verified for syntactic validity by the database parser and optionally, semantic equivalence checks are performed. Schemes that pass verification are grouped into feasible index optimization subsets and feasible query rewriting subsets. These two subsets are then combined to generate several joint optimization strategies. For each joint strategy, the system calls the database's EXPLAIN interface or a lightweight cost simulator to regenerate the execution plan and calculates new execution performance predictions based on this plan. The optimization benefit score can be defined as the ratio or difference between the original prediction time and the new prediction time, and a weighting factor can be introduced to balance index storage overhead and query acceleration benefits. Finally, the joint strategy with the highest score is selected as the target optimization strategy.

[0021] In one embodiment, obtaining the SQL statement and its execution plan, and extracting the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan, includes: Obtain the SQL statement and its execution plan, perform syntax parsing on the SQL statement to generate an abstract syntax tree, and perform operator parsing on the execution plan to extract the operator sequence; Based on the abstract syntax tree, SQL operation types, table names, and field names are extracted to construct a structured representation of the SQL statement, and cost estimates for each operator are extracted based on the operator sequence. The structured representation of the SQL statement is input into a pre-trained feature extraction model to generate a semantic feature vector. Based on the cost estimates of each operator and the operator sequence, the execution plan feature vector of the execution plan is extracted.

[0022] The SQL statement itself only reflects the user's data manipulation intent, while the execution plan reflects the actual physical implementation path of that intent by the database system. The combination of the two can fully characterize the query's behavioral features. Specifically, the system actively captures the original SQL statement through the database client interface and synchronously calls the database's built-in execution plan generation command to obtain its corresponding execution plan. For example, when the input SQL statement is "SELECT u.name, o.total FROM users u JOIN orders oON u.id = o.user_id WHERE o.create_time>'2025-01-01'", the system not only records the text but also obtains the sequence of operator nodes contained in its execution plan. By parsing the SQL statement and using tools such as ANTLR to generate an abstract syntax tree, and simultaneously parsing the execution plan, it converts it into a sequence of operators arranged in execution order.

[0023] By extracting SQL operation types, table names, and field names from the abstract syntax tree (AST), a structured representation of the SQL statement is constructed. Furthermore, cost estimates for each operator are extracted based on the operator sequence. This transforms unstructured SQL text and tree-like execution plans into machine-processable structured data. Specifically, the nodes of the AST are traversed, the root node type is identified to determine the SQL operation type, and the base table name in the FROM clause and the field names involved in clauses such as WHERE, SELECT, and JOIN are recursively extracted. Simultaneously, each operator node in the operator sequence is traversed, and cost estimates provided by the database optimizer are parsed from the JSON or text output of the execution plan, including numerical attributes such as startup cost, total cost, and estimated row count. For example, in the execution plan of the aforementioned query, the system extracts specific values ​​such as a total cost of 1200.5 for "Seq Scan on orders" and a total cost of 8.3 for "Index Scan using idx_users_id on users". Through this structuring process, the original query is transformed into a hybrid representation composed of discrete symbols and continuous numerical values.

[0024] The structured representation of SQL statements is input into a pre-trained feature extraction model to generate semantic feature vectors. The goal is to map the symbolic SQL logical structure to a continuous vector space so that the deep learning model can understand its semantics. Specifically, a feature extraction model pre-trained on a large-scale SQL corpus is used. This model can be a sequence model based on a Transformer encoder or a graph neural network that processes tree structures. Elements such as SQL operation types, table names, and field names in the structured representation are sequentially converted into embedding vectors, which are then input into the model for context-aware encoding, ultimately outputting a fixed-dimensional semantic feature vector. For example, the combination "SELECT + users + orders + u.id=o.user_id + o.create_time>'2025-01-01'" in a query will be encoded into a dense vector that reflects the semantics of multi-table joins and time filtering. Since the model has learned the distribution patterns of SQL patterns on a large number of historical queries, the generated semantic feature vector can capture the logical complexity and access pattern characteristics of the query to a certain extent.

[0025] Based on the cost estimates of each operator and the operator sequence, an execution plan feature vector is extracted to quantify the physical behavior of the execution plan into a computable numerical representation. Specifically, each operator type in the operator sequence (e.g., Seq Scan, Index Scan, Hash Join) is mapped to a One-Hot encoded or pre-trained operator embedding, which is then concatenated with the corresponding cost estimate to form the feature of each node. Subsequently, the feature vector of the entire execution plan can be obtained by aggregating the features of all nodes (e.g., summation or max pooling). For example, the three operators in the execution plan generate three sets of (operator embedding, cost value) pairs, which are then aggregated to output an execution plan feature vector with the same dimension as the semantic feature vector. Since the execution plan feature vector integrates the operator type and the resource consumption estimated by the optimizer, it can reflect the efficiency bottleneck and resource overhead of the current execution path.

[0026] In one embodiment, inputting the SQL semantic features and the features of the execution plan into the analysis model to obtain performance bottleneck information and execution performance prediction values ​​includes: The semantic feature vector and execution plan feature vector, which have been aligned across modalities, are input into the input layer of the analysis model, and a linear projection transformation is performed on the semantic feature vector and execution plan feature vector. The association weights between each word in the semantic feature vector and each operator node in the execution plan feature vector are calculated using a self-attention mechanism to generate a weighted fusion feature matrix. The weighted fusion feature matrix is ​​subjected to a nonlinear transformation to extract high-level abstract features of the performance bottleneck; Based on the aforementioned high-level abstract features, the performance bottleneck information and execution performance prediction values ​​are output through the multi-task learning branch of the analysis model.

[0027] Cross-modal alignment refers to ensuring that the semantic feature vector and the execution plan feature vector are identical in dimension, thus guaranteeing the feasibility of subsequent fusion operations. Linear projection applies two independent, learnable weight matrices to the semantic feature vector and the execution plan feature vector, respectively, mapping them into a unified latent space, which facilitates subsequent interaction modeling. For example, for the query "SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE o.create_time>'2025-01-01'", the semantic feature vector, after projection, may emphasize the semantic signals of "multi-table join" and "time range filtering," while the execution plan feature vector, after projection, highlights the physical signal of the high-cost node "Seq Scan on orders."

[0028] A weighted fusion feature matrix is ​​generated by calculating the association weights between each word in the semantic feature vector and each operator node in the execution plan feature vector using a self-attention mechanism. Specifically, the projected semantic features are treated as queries, and the execution plan features are treated as keys and values. Alternatively, a bidirectional cross-attention structure is used to calculate the dot product similarity between each semantic word (such as an embedding representing the "orders table" or the "create_time field") and each operator node (such as Seq Scan or Hash Join), and this similarity is normalized to obtain the attention weight. This weight reflects the alignment between the logical intent and the physical implementation. For example, the condition "o.create_time>'2025-01-01'" may have a high-weight association with the "Seq Scan on orders" operator. Through a weighted summation operation, a feature matrix that fuses semantic-physical interaction information is generated, where each row corresponds to a fused context-aware representation.

[0029] The weighted fusion feature matrix is ​​subjected to a nonlinear transformation to extract high-level abstract features of performance bottlenecks. This process is achieved by stacking several fully connected layers and introducing activation functions, which can identify key patterns affecting execution efficiency from the fusion features. For example, when the fusion features simultaneously contain the semantic signal of "full table scan" and the physical signal of "high I / O cost Seq Scan", the nonlinear transformation layer can abstract it into the high-level feature of "scan bottleneck caused by lack of effective indexes". Similarly, the combination of "non-selective filtering of multi-table joins" and "hash join memory overflow risk" may be abstracted into the bottleneck pattern of "insufficient join order or predicate pushdown". These high-level abstract features are no longer limited to original symbols or values, but encode the potential problems under the synergy of logical and physical levels.

[0030] Based on the aforementioned high-level abstract features, performance bottleneck information and execution performance predictions are output through the multi-task learning branch of the analysis model. The multi-task learning branch contains two parallel output heads: one is a classification head, used to predict the performance bottleneck category (e.g., "missing index," "outdated statistics," etc.), typically using a Softmax output probability distribution. The other is a regression head, used to predict execution time or the total cost of the optimizer, typically using a single-neuron linear output. The two branches share parameters from all preceding layers, jointly optimizing cross-entropy loss and mean squared error loss during the training phase. For example, for the aforementioned query, the model might output "missing index" as the primary bottleneck category (probability 0.82), while predicting an execution time of 420 milliseconds. Because the two tasks share the contextual representation during feature extraction, the performance bottleneck identification results and execution performance predictions are semantically consistent, thus providing a reliable basis for generating optimization strategies.

[0031] In one embodiment, generating candidate index optimization schemes and candidate query rewriting schemes based on performance bottleneck information and execution performance predictions includes: Based on the performance bottleneck information and the predicted execution performance values, the key tables, key columns, and inefficient operator nodes that affect SQL execution efficiency are identified. Based on the key table, the key column, the inefficient operator node, and the predefined index template library, generate multiple candidate index optimization schemes; Based on the inefficient operator node and its context structure in the execution plan, query the rewrite rule base to generate multiple candidate query rewrite schemes.

[0032] When performance bottleneck information indicates a specific type of problem, such as missing indexes, poor join order, or predicate not being pushed down, the system locates the corresponding high-cost or structurally abnormal operator nodes in the execution plan feature vector. It then combines this with table names and field names extracted from the semantic feature vector to establish a mapping between logical elements and physical operations. Specifically, it traverses the operator sequence of the execution plan, identifying operators with significantly higher startup or total costs than other nodes as inefficient operator nodes. Simultaneously, it selects the base table directly associated with the bottleneck operator as the key table based on the bottleneck category, and extracts the fields involved in filtering, joining, or sorting from the semantic features as key columns. This mapping mechanism ensures that subsequent optimization suggestions accurately align with the root cause of the actual performance problem.

[0033] Based on the identified key tables, key columns, inefficient operator nodes, and a predefined index template library, multiple candidate index optimization schemes are generated. The index template library is a structured set of rules, where each template defines the type of index that can be built under a given column combination and query pattern, including single-column B-tree indexes, multi-column composite indexes, covering indexes, and conditional partial indexes. Key columns are categorized according to their role in the WHERE clause, JOIN condition, or ORDER BY clause, and matching templates are selected based on the type of inefficient operator node. For example, if the inefficient operator is a full table scan and the key column appears under filtering conditions, a single-column or composite index is preferentially generated; if the query also involves projected columns, it is expanded into a covering index to avoid table lookups. Each candidate scheme is generated in the form of standard SQL DDL statements, and its applicable scenarios, expected scope of effect, and potential storage overhead are recorded for use by the subsequent evaluation module.

[0034] Based on inefficient operator nodes and their context in the execution plan, and combined with a predefined query rewriting rule base, multiple candidate query rewriting schemes are generated. The query rewriting rule base stores a series of transformation rules that reverse-engineer the logic from the execution plan. Each rule includes triggering conditions and corresponding SQL syntax transformation logic. Triggering conditions describe specific operator combinations or structural characteristics in the execution plan, such as a Filter operator above a Join operator whose predicate only involves internal tables, a Subquery Scan not being expanded into a Join, or an OR condition causing an Index Scan to fail. The transformation logic defines how to modify the original SQL statement to prompt the optimizer to generate a better execution path.

[0035] After locating inefficient child nodes in the execution plan, its parent and child nodes are traversed upwards and downwards to construct a local context subgraph. This subgraph is then used for pattern matching against trigger conditions in the rule base. If a match is successful, the corresponding transformation logic is invoked to reconstruct the original SQL statement at the syntax tree level, generating a new query text that is semantically equivalent but structurally different. All generated rewrite schemes undergo syntax validation and retain semantic consistency constraints with the original query, ensuring that the query results are not altered. This approach transforms inefficiencies at the execution plan level into actionable logical-level optimization suggestions, forming an optimization path that complements the indexing scheme.

[0036] In one embodiment, determining the target optimization strategy for the SQL statement based on candidate index optimization schemes and candidate query rewriting schemes includes: The feasibility of the candidate index optimization scheme and the candidate query rewriting scheme is verified to obtain a feasible index optimization subset and a feasible query rewriting subset. The feasible index optimization subset and the feasible query rewriting subset are combined to generate multiple joint optimization strategies; Based on the cost evaluation model and the predicted execution performance, the optimization benefit score for each joint optimization strategy is calculated. Based on the optimization benefit score, the target optimization strategy for the SQL statement is determined from the joint optimization strategies.

[0037] Feasibility verification involves checks across multiple dimensions. For candidate index optimization schemes, it verifies whether the target table exists, whether the columns are valid, whether the index type is supported by the current database engine, and assesses whether its storage overhead exceeds a preset threshold. It also checks for semantic conflicts, such as avoiding redundant creation when indexes with the same column combinations already exist. For candidate query rewriting schemes, the parser performs syntax and semantic validity verification on the rewritten SQL statements to ensure they conform to SQL standards and maintain equivalence to the original query. Furthermore, it confirms that the database features upon which the rewriting depends (e.g., subquery expansion, window function support, etc.) are available in the target environment. Through this verification process, feasible subsets of index optimization and query rewriting are obtained, eliminating infeasible or high-risk suggestions.

[0038] Multiple joint optimization strategies are generated by combining feasible index optimization subsets and feasible query rewrite subsets through a Cartesian product. Each joint optimization strategy consists of a feasible index scheme and a feasible query rewrite scheme, representing a comprehensive optimization path that simultaneously applies physical structure changes and logical structure adjustments. To improve efficiency, pruning strategies can also be introduced. For example, when a query rewrite scheme eliminates the need for a certain index, the combination with that index can be skipped; or when the index scheme and the rewrite scheme operate on different subqueries and have no interaction effect, they can be merged for evaluation to reduce redundant calculations. All generated joint optimization strategies are stored in a structured format, including the selected index definitions, the rewritten SQL text, and their combination identifiers.

[0039] Based on the cost evaluation model and the original execution performance predictions, an optimization benefit score is calculated for each joint optimization strategy. The cost evaluation model can employ the database's built-in optimizer's EXPLAIN ANALYZE mechanism or a lightweight proxy model to estimate the total cost or predicted execution time of the new execution plan after applying the joint strategy. Specifically, first, candidate indexes are simulated (e.g., temporarily registered as virtual indexes in optimizer statistics). Then, a rewritten SQL statement is used to request the optimizer to generate a new execution plan, and its estimated total cost or execution time is extracted as the post-optimization performance metric. The optimization benefit score is defined as the relative decrease in the original execution performance prediction compared to the post-optimization performance metric. It can also be weighted and adjusted based on factors such as index maintenance costs and storage growth to reflect the overall benefit. The scores for all joint strategies are normalized and ranked, forming a quantitative basis for the optimization potential.

[0040] The target optimization strategy for the SQL statement is determined from the joint optimization strategies based on the optimization benefit score. Various criteria can be used to select the strategy, such as choosing the single strategy with the highest score, or prioritizing the non-intrusive solution involving only query rewriting among multiple strategies with similar scores to reduce operational risks. If the environment allows for automated deployment, the joint strategy with the highest overall benefit and controllable resource consumption can also be selected. The determined target optimization strategy will serve as the final output, guiding subsequent index creation operations and SQL rewriting implementation, thereby achieving efficient optimization of the original SQL statement.

[0041] In one embodiment, calculating the optimization benefit score for each joint optimization strategy based on the cost evaluation model and the predicted execution performance includes: Obtain the rewritten SQL statement and index change description corresponding to each joint optimization strategy; Based on the index change description, simulate the creation of the corresponding index, and submit the rewritten SQL statement to the database optimizer to generate a simulated execution plan; Based on the simulated execution plan, the cost evaluation model is invoked to calculate the new execution performance prediction value corresponding to the joint optimization strategy; Using the original execution performance prediction value and the new execution performance prediction value corresponding to the joint optimization strategy, the optimization benefit score of the joint optimization strategy is calculated through a preset scoring function.

[0042] The rewritten SQL statement is a semantically equivalent but structurally optimized query text generated by the query rewriting module. The index change description includes metadata such as the target table name of the index to be created, the list of index columns, the index type, and whether it is a covering index or a partial index.

[0043] Based on the index change description, the corresponding index is virtually created in a simulation environment without actually modifying the physical structure of the production database. The simulation process is achieved by injecting temporary statistical metadata into the database optimizer. For example, in databases that support virtual index mechanisms, a hypothetical index is registered using the optimizer interface, used only for planned generation. In systems that do not support this feature, shadow statistics are constructed or a proxy optimizer model is used to approximate the cost estimation behavior when the index exists. After the index simulation is complete, the rewritten SQL statement is submitted to the database optimizer, requesting it to generate an execution plan. This execution plan, generated under the assumption that the index already exists and the query has been rewritten, is called the simulated execution plan and reflects the possible changes in the execution path brought about by the joint optimization strategy.

[0044] The cost evaluation model is a regression model used to predict SQL execution performance. Its purpose is to map the structured features of the execution plan into quantifiable performance metrics (e.g., execution time or resource consumption). The model's input is an execution plan feature vector, generated from the parsed simulated execution plan. This vector contains information across multiple dimensions, such as the type of each operator node, the estimated startup and total costs for each operator, the estimated number of output rows, I / O read / write operations, memory usage, whether temporary files are involved, parallelism information, and the nesting relationships and data flow paths between operators. These features are automatically extracted by traversing the execution plan tree structure and encoded into a fixed-length numerical vector. The model's output is a scalar value, the new execution performance prediction, typically represented as the predicted execution time or a normalized performance score.

[0045] By comparing the original performance prediction of the original SQL statement with the new performance prediction corresponding to the joint optimization strategy, an optimization benefit score is calculated using a pre-defined scoring function. The scoring function can be designed as a measure of relative performance improvement; for example, dividing the difference between the two by the original performance prediction yields a normalized benefit ratio, or using a logarithmic time reduction to enhance sensitivity to high-latency queries. In some implementations, the scoring function may also introduce a penalty term, such as imposing storage or maintenance cost weights on strategies involving multi-column composite indexes or large table index creation, to balance performance gains with resource overhead. Through this scoring mechanism, each joint optimization strategy is assigned a comparable numerical metric, providing a quantitative basis for subsequently selecting the optimal target strategy.

[0046] In one embodiment, the method further includes: Execute the target optimization strategy and obtain the performance metrics after executing the target optimization strategy; The semantic feature vector, execution plan feature vector, and performance metrics are used as training samples to update the parameters of the analysis model.

[0047] If the strategy includes index creation, the corresponding index is deployed in the database. If it includes query rewriting, the rewritten SQL statement is used to replace the original statement. After execution, actual runtime performance metrics are collected, including execution time, CPU consumption, I / O counts, or optimizer estimated costs, as real feedback on the strategy's effectiveness. The semantic feature vector of the original SQL statement corresponding to this optimization, the feature vector of the original execution plan, and the aforementioned actual performance metrics are combined into a new training sample. This sample is added to the training dataset and used to update the parameters of the analysis model. The update process can employ online learning or periodic batch training, using mechanisms such as backpropagation and gradient descent to enable the model to more accurately evaluate the performance impact of different optimization schemes in subsequent predictions, thereby continuously improving the quality and adaptability of optimization suggestions.

[0048] Reference Figure 2 The diagram shown is a functional module schematic of the SQL statement optimization device 100 based on the analysis model of this application.

[0049] The SQL statement optimization device 100 based on the analysis model described in this application is installed in an electronic device. Depending on the functions implemented, the SQL statement optimization device 100 based on the analysis model includes an extraction module 110, an analysis module 120, a generation module 130, and an optimization module 140. These modules can also be referred to as units, which are a series of computer program segments that can be executed by the processor of an electronic device and can perform a fixed function, and are stored in the memory of the electronic device.

[0050] In this embodiment, the functions of each module / unit are as follows: Extraction module 110: used to obtain the SQL statement and the execution plan of the SQL statement, and extract the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan; Analysis module 120: Used to input semantic feature vectors and execution plan feature vectors into a pre-trained analysis model to obtain performance bottleneck information and execution performance prediction values; Generation module 130: Used to generate candidate index optimization schemes and candidate query rewriting schemes based on performance bottleneck information and execution performance prediction values; Optimization module 140: Used to determine the target optimization strategy for SQL statements based on candidate index optimization schemes and candidate query rewriting schemes.

[0051] The specific implementation of the SQL statement optimization device based on the analysis model in this application is largely the same as the specific implementation of the SQL statement optimization method based on the analysis model described above, and will not be repeated here.

[0052] Reference Figure 3 The diagram shown is a schematic representation of a preferred embodiment of the electronic device of this application.

[0053] The electronic device includes a processor 111, a communication interface 112, a memory 113, and a communication bus 114, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114. Memory 113 is used to store computer programs, such as SQL statement optimization programs based on analysis models; Figure 3 Only an electronic device with components 111-114 is shown; however, it should be understood that it is not required to implement all of the components shown, and more or fewer components may be implemented instead.

[0054] In one embodiment of this application, when the processor 111 executes the program stored in the memory 113, it implements the SQL statement optimization method based on the analysis model provided in any of the foregoing method embodiments, including: Obtain the SQL statement and its execution plan, and extract the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan; The semantic feature vector and execution plan feature vector are input into a pre-trained analysis model to obtain performance bottleneck information and execution performance predictions. Based on performance bottleneck information and execution performance predictions, candidate index optimization schemes and candidate query rewriting schemes are generated. Based on candidate index optimization schemes and candidate query rewriting schemes, the target optimization strategy for SQL statements is determined.

[0055] For a detailed explanation of the above steps, please refer to the above. Figure 1 A flowchart illustrating an embodiment of the SQL statement optimization method based on an analytical model.

[0056] Furthermore, this application also proposes a computer-readable storage medium that is both non-volatile and volatile. This computer-readable storage medium is any one or any combination of several of the following: hard disk, multimedia card, SD card, flash memory card, SMC, read-only memory (ROM), erasable programmable read-only memory (EPROM), portable compact disc read-only memory (CD-ROM), USB memory, etc. The computer-readable storage medium includes a data storage area and a program storage area. The program storage area stores an SQL statement optimization program based on an analysis model. When executed by a processor, the SQL statement optimization program based on the analysis model performs the following operations: Obtain the SQL statement and its execution plan, and extract the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan; The semantic feature vector and execution plan feature vector are input into a pre-trained analysis model to obtain performance bottleneck information and execution performance predictions. Based on performance bottleneck information and execution performance predictions, candidate index optimization schemes and candidate query rewriting schemes are generated. Based on candidate index optimization schemes and candidate query rewriting schemes, the target optimization strategy for SQL statements is determined.

[0057] The specific implementation of the computer-readable storage medium in this application is largely the same as the specific implementation of the SQL statement optimization method based on the analysis model described above, and will not be repeated here.

[0058] It should be noted that the sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, apparatus, article, or method that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, apparatus, article, or method. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, apparatus, article, or method that includes that element.

[0059] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware simulation platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes several instructions to cause a terminal device to execute the methods described in the various embodiments of this application.

[0060] The above are merely preferred embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.

Claims

1. A SQL statement optimization method based on an analytical model, characterized in that, The method includes: Obtain the SQL statement and its execution plan, and extract the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan; The semantic feature vector and execution plan feature vector are input into a pre-trained analysis model to obtain performance bottleneck information and execution performance predictions. Based on performance bottleneck information and execution performance predictions, candidate index optimization schemes and candidate query rewriting schemes are generated. Based on candidate index optimization schemes and candidate query rewriting schemes, the target optimization strategy for SQL statements is determined.

2. The SQL statement optimization method based on the analysis model as described in claim 1, characterized in that, The process of obtaining the SQL statement and its execution plan, and extracting the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan, includes: Obtain the SQL statement and its execution plan, perform syntax parsing on the SQL statement to generate an abstract syntax tree, and perform operator parsing on the execution plan to extract the operator sequence; Based on the abstract syntax tree, SQL operation types, table names, and field names are extracted to construct a structured representation of the SQL statement, and cost estimates for each operator are extracted based on the operator sequence. The structured representation of the SQL statement is input into a pre-trained feature extraction model to generate a semantic feature vector. Based on the cost estimates of each operator and the operator sequence, the execution plan feature vector of the execution plan is extracted.

3. The SQL statement optimization method based on the analysis model as described in claim 1, characterized in that, The step of inputting SQL semantic features and execution plan features into the analysis model to obtain performance bottleneck information and execution performance prediction values ​​includes: The semantic feature vector and execution plan feature vector, which have been aligned across modalities, are input into the input layer of the analysis model, and a linear projection transformation is performed on the semantic feature vector and execution plan feature vector. The association weights between each word in the semantic feature vector and each operator node in the execution plan feature vector are calculated using a self-attention mechanism to generate a weighted fusion feature matrix. The weighted fusion feature matrix is ​​subjected to a nonlinear transformation to extract high-level abstract features of the performance bottleneck; Based on the aforementioned high-level abstract features, the performance bottleneck information and execution performance prediction values ​​are output through the multi-task learning branch of the analysis model.

4. The SQL statement optimization method based on the analysis model as described in claim 1, characterized in that, The process of generating candidate index optimization schemes and candidate query rewriting schemes based on performance bottleneck information and execution performance predictions includes: Based on the performance bottleneck information and the predicted execution performance values, the key tables, key columns, and inefficient operator nodes that affect SQL execution efficiency are identified. Based on the key table, the key column, the inefficient operator node, and the predefined index template library, generate multiple candidate index optimization schemes; Based on the inefficient operator node and its context structure in the execution plan, query the rewrite rule base to generate multiple candidate query rewrite schemes.

5. The SQL statement optimization method based on the analysis model as described in claim 1, characterized in that, The process of determining the target optimization strategy for SQL statements based on candidate index optimization schemes and candidate query rewriting schemes includes: The feasibility of the candidate index optimization scheme and the candidate query rewriting scheme is verified to obtain a feasible index optimization subset and a feasible query rewriting subset. The feasible index optimization subset and the feasible query rewriting subset are combined to generate multiple joint optimization strategies; Based on the cost evaluation model and the predicted execution performance, the optimization benefit score for each joint optimization strategy is calculated. Based on the optimization benefit score, the target optimization strategy for the SQL statement is determined from the joint optimization strategies.

6. The SQL statement optimization method based on the analysis model as described in claim 5, characterized in that, The optimization benefit score for each joint optimization strategy is calculated based on the cost evaluation model and the predicted execution performance, including: Obtain the rewritten SQL statement and index change description corresponding to each joint optimization strategy; Based on the index change description, simulate the creation of the corresponding index, and submit the rewritten SQL statement to the database optimizer to generate a simulated execution plan; Based on the simulated execution plan, the cost evaluation model is invoked to calculate the new execution performance prediction value corresponding to the joint optimization strategy; Using the original execution performance prediction value and the new execution performance prediction value corresponding to the joint optimization strategy, the optimization benefit score of the joint optimization strategy is calculated through a preset scoring function.

7. The SQL statement optimization method based on the analysis model as described in claim 1, characterized in that, The method further includes: Execute the target optimization strategy and obtain the performance metrics after executing the target optimization strategy; The semantic feature vector, execution plan feature vector, and performance metrics are used as training samples to update the parameters of the analysis model.

8. A SQL statement optimization device based on an analytical model, characterized in that, The device includes: Extraction module: used to obtain the SQL statement and its execution plan, and extract the semantic feature vector of the SQL statement and the execution plan feature vector of the execution plan; Analysis module: Used to input semantic feature vectors and execution plan feature vectors into a pre-trained analysis model to obtain performance bottleneck information and execution performance predictions; The generation module is used to generate candidate index optimization schemes and candidate query rewriting schemes based on performance bottleneck information and execution performance predictions. Optimization module: Used to determine the target optimization strategy for SQL statements based on candidate index optimization schemes and candidate query rewriting schemes.

9. An electronic device, characterized in that, It includes a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, when executing a program stored in memory, implements the SQL statement optimization method based on the analysis model as described in any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the SQL statement optimization method based on the analysis model as described in any one of claims 1 to 7.

Images