A method and system for SQL and index combined closed-loop optimization based on verification results

Abstract

This invention provides a method and system for joint closed-loop optimization of SQL and indexes based on verification results, relating to the field of electronic digital data processing technology. The method includes: acquiring slow query statements from a target database and normalizing their parameters to form original SQL templates, extracting their workload characteristics; generating a set of efficient candidate SQL templates through a large-scale language model, simultaneously constructing a heterogeneous graph and generating a set of candidate index configurations via a graph neural network; pairing the two to form an optimized combination set, inputting it into a multimodal feature prediction model to evaluate performance metrics; selecting the optimal combination for deployment and verification; rolling back if expectations are not met, retaining if they are met; and feeding the optimal combination and its actual performance metrics back to the model for fine-tuning. This invention enables collaborative optimization of SQL and indexes, integrates artificial intelligence and machine learning to improve prediction accuracy, and continuously improves prediction accuracy through a closed-loop feedback mechanism based on verification results, significantly improving database query performance and reducing manual tuning costs.

Description

Technical Field

[0001] This invention relates to the field of electronic digital data processing technology, and in particular to a method and system for joint closed-loop optimization of SQL and index based on verification results. Background Technology

[0002] In modern data-intensive applications, database performance is central to system responsiveness and user experience. With the exponential growth of data volume and the increasing complexity of query logic, performance degradation of Structured Query Language (SQL) has become a common problem. The traditional model, relying on manual optimization by database administrators (DBAs), is labor-intensive, inefficient, dependent on personal experience, and difficult to scale, making it unsuitable for the demands of data explosion and rapid application iteration.

[0003] To overcome this limitation, database optimization techniques have been continuously evolving. Early rule-based optimizers (RBO) relied on static rules and could not adapt to the diversity of data distributions. Cost-based optimizers (CBO), while selecting the best option through statistical information, are prone to decision-making biases due to outdated data or distorted sampling.

[0004] Subsequent automated tools still have significant shortcomings: SQL rewriting tools (such as SQLAdvisor) only analyze single SQL statements and lack a global perspective; while automated index tuning (AIT) tools (such as DTA) can generate index schemes, SQL and index optimization are separated. Specifically, SQL rewriting may invalidate existing indexes, and index design may fail due to changes in SQL logic. Therefore, simple combinations of local optima cannot achieve global optima, limiting the potential for performance improvement.

[0005] In recent years, Artificial Intelligence (AI) and Machine Learning (ML) technologies have been introduced into the field of optimization. Learning-based optimizers enhance Cost-Based Optimizers (CBOs) through ML, intelligent index recommendations use algorithms to search for optimal configurations, and Large Language Models (LLMs) are used for SQL rewriting or execution plan generation. However, these methods still have shortcomings: learning-based optimizers have complex feature engineering and limited generalization ability; while LLMs can understand SQL semantics, they lack awareness of the physical state of the database, making it difficult to guarantee the actual effectiveness of generated suggestions, and they do not solve the problem of joint optimization of SQL and indexes. Summary of the Invention

[0006] In view of this, embodiments of the present invention provide a method and system for joint closed-loop optimization of SQL and index based on verification results, in order to solve the problems of fragmented SQL and index optimization, lack of dynamic closed-loop verification and self-learning mechanism, and semantic and physical disconnect in AI applications in the prior art.

[0007] On the one hand, this invention provides a method for joint closed-loop optimization of SQL and indexes based on verification results, the method comprising the following steps:

[0008] Obtain slow query statements in the target database that do not meet the preset performance indicators, perform parameter normalization on the slow query statements to form corresponding original SQL templates, and extract the workload characteristics generated by the slow query statements corresponding to each original SQL template during the preset time period and under the original index configuration. The workload characteristics include the number of executions, execution time and resource consumption.

[0009] The original SQL template is input into a pre-trained large-scale language model to generate multiple candidate SQL templates that are semantically equivalent to the original SQL template, forming a candidate SQL template set. The large-scale language model is trained based on the SQL template training set, which includes multiple samples containing the SQL template to be optimized, the optimized SQL template, and the execution efficiency improvement of the optimized SQL template compared to the SQL template to be optimized. The optimized SQL template is semantically equivalent to the SQL template to be optimized.

[0010] Extract all column names referenced in all query condition statements in the original SQL template as candidate index columns. Construct a heterogeneous graph based on the candidate index columns, the database tables to which the candidate index columns belong, and the primary and foreign key relationships between the database tables in the target database. Input the heterogeneous graph into a pre-trained graph neural network to generate multiple candidate index configurations, forming a candidate index configuration set.

[0011] The candidate SQL template set and the candidate index configuration set are paired to generate multiple optimized combinations. Features of the corresponding candidate SQL templates and candidate index configurations in each optimized combination are extracted to obtain query features and index features. The query features, index features, workload features, structural features of the target database, and data statistical features are input into a pre-trained multimodal feature prediction model to generate prediction performance indicators for each optimized combination. The data statistical features include the number of rows in the database table, the cardinality of the candidate index columns, the proportion of null values, and the data distribution histogram.

[0012] Based on the predicted performance indicators and the preset optimization objectives, the optimal optimization combination is selected from the multiple optimization combinations and deployed to the target database;

[0013] Within a preset verification period, monitor the actual performance metrics generated by the slow query statements corresponding to the candidate SQL templates in the optimal optimization combination under the candidate index configuration. If the actual performance metrics do not reach the baseline performance metrics, perform a rollback operation; otherwise, retain the deployment. The baseline performance metrics are calculated based on the performance metrics generated by the slow query statements corresponding to the original SQL templates under the original index configuration.

[0014] The optimal combination and its corresponding real performance indicators are stored as incremental training samples in the training sample library. The sample data required for the large-scale language model, the graph neural network and the multimodal feature prediction model are extracted from the incremental training samples for incremental training to form a closed-loop optimization.

[0015] In some embodiments of the present invention, obtaining slow query statements in the target database that do not meet preset performance indicators includes:

[0016] Monitor the slow query logs of the target database and obtain the first query statement whose execution time exceeds the corresponding preset threshold;

[0017] Query the performance monitoring view of the target database to obtain a second query statement that exceeds the corresponding preset threshold for the number of scanned rows or the number of logical reads.

[0018] The first query statement and the second query statement are merged and deduplicated to obtain the slow query statement.

[0019] In some embodiments of the present invention, the method further includes training the large-scale language model, wherein training the large-scale language model includes:

[0020] Construct the training set of the SQL template;

[0021] An initial model is constructed, which includes an input layer, a multi-layer self-attention and feedforward network, and an output layer. The initial model takes the SQL templates to be optimized in the SQL template training set as input and outputs multiple rewritten SQL templates.

[0022] The initial model is trained using the SQL template training set, minimizing the loss between each rewritten SQL template and the optimized SQL template, and using the actual performance improvement as a reward signal. The parameters of the initial model are then fine-tuned using the policy gradient method to finally obtain the large-scale language model.

[0023] In some embodiments of the present invention, constructing the heterogeneous graph includes:

[0024] A node set is constructed by using the candidate index columns as column nodes and the database tables to which the candidate index columns belong as table nodes;

[0025] An edge set is constructed, comprising column-table membership edges, column-column-column-table edges, and table-table primary / foreign key edges. Column-table membership edges connect the candidate index column to its corresponding database table. Column-column-table edges connect two candidate index columns within the same database table. Table-table primary / foreign key edges point from the foreign key database table to the primary key database table. The edge characteristic of column-table membership edges is membership relationship. The edge characteristic of column-column-table edges is the ratio of the number of times the two corresponding candidate index columns appear simultaneously when the slow query statement corresponding to the original SQL template is executed within a preset time period to the total number of times the slow query statement corresponding to the original SQL template is executed within the preset time period. The edge characteristic of table-table primary / foreign key edges includes the ratio of the cardinality of the primary key column to the cardinality of the foreign key column and the number of times the corresponding primary / foreign key pair is referenced in the original SQL template.

[0026] The heterogeneous graph is constructed based on the set of nodes and the set of edges.

[0027] In some embodiments of the present invention, the candidate index configuration set is obtained based on the following steps:

[0028] The heterogeneous graph is input into the graph neural network, which includes convolutional layers, attention layers, and fully connected layers. The convolutional layers update the node embedding representations via a message passing mechanism. The attention layers calculate the attention weights between the column nodes based on the updated node embedding representations. The fully connected layers weight and score the updated node embedding representations based on the attention weights. If the attention weights between the column nodes are lower than a preset association threshold, the value score of the column node as a single-column index is output. Otherwise, the column node and its associated nodes are combined to form a column node combination, and the synergistic effect score of the column node combination forming a composite index is output. The synergistic effect score is obtained based on the attention weights and the edge features of the column-to-column same-table edges.

[0029] Columns whose value scores exceed a first threshold are selected to generate a single-column index set. Columns whose synergy scores exceed a second threshold are selected to generate a composite index set. The single-column index set and the composite index set are combined to obtain the candidate index configuration set.

[0030] In some embodiments of the present invention, the candidate SQL template set and the candidate index configuration set are paired to generate multiple sets of optimized combinations, including: using the Cartesian product method to combine each candidate SQL template in the candidate SQL template set with each candidate index configuration in the candidate index configuration set in pairs to form an optimized combination set containing all possible combinations.

[0031] In some embodiments of the present invention, when verifying the optimal combination within the preset verification period, an A / B verification method is used for verification, including:

[0032] The first set of configurations and the second set of configurations are deployed simultaneously in the target database. The first set of configurations consists of the original SQL template and the original index configuration, and the second set of configurations consists of the candidate SQL template and the candidate index configuration in the optimal combination.

[0033] Within the preset verification period, the performance metrics generated by the slow query statements corresponding to the original SQL templates in the first set of configurations being executed under the original index configuration are collected in parallel to obtain the baseline performance metrics. The actual performance metrics generated by the candidate SQL templates in the second set of configurations being executed under the candidate index configuration are collected in parallel. The baseline performance metrics and the actual performance metrics are compared to obtain the verification result of the optimal combination.

[0034] In some embodiments of the present invention, the multimodal feature prediction model is implemented using a Transformer architecture, including an embedding layer, a feature fusion layer, an encoding layer, and an output layer. The embedding layer converts the query features, the index features, the workload features, the structural features, and the data statistical features into embedded representations, which are then concatenated by the feature fusion layer to generate a fused feature representation. The encoding layer enhances the fused feature representation through a multi-head self-attention mechanism. The output layer performs prediction based on the enhanced fused feature representation and outputs the prediction performance metrics. The prediction performance metrics include the execution time of the slow query statement corresponding to the candidate SQL template, the CPU utilization of the target database, and the storage overhead and maintenance cost of the candidate index configuration.

[0035] In some embodiments of the present invention, selecting the optimal combination for deployment to the target database includes automatic deployment and semi-automatic deployment:

[0036] When the automatic deployment is performed, the system receives authorization information pre-issued by the administrator terminal and automatically executes the following: replacing the original SQL template with the candidate SQL template in the optimal combination, and creating or modifying the data definition language statements required for the candidate index configuration in the optimal combination.

[0037] When using the semi-automatic deployment method, an optimization suggestion report is generated based on the optimal optimization combination and uploaded to the administrator terminal. After receiving the confirmation execution instruction, the following actions are performed: replacing the original SQL template with the candidate SQL template in the optimal optimization combination, and creating or modifying the data definition language statements required for the candidate index configuration in the optimal optimization combination.

[0038] On the other hand, the present invention also provides a SQL and index joint closed-loop optimization system based on verification results, including a processor, a memory, and a computer program / instructions stored in the memory, wherein the processor is used to execute the computer program / instructions, and when the computer program / instructions are executed, the system implements the steps of any of the methods mentioned above.

[0039] This invention provides a method and system for joint closed-loop optimization of SQL and indexes based on verification results, relating to the field of electronic digital data processing technology. The method includes: acquiring slow query statements from a target database and normalizing their parameters to form original SQL templates, extracting their workload characteristics; generating a set of efficient candidate SQL templates through a large-scale language model, simultaneously constructing a heterogeneous graph and generating a set of candidate index configurations via a graph neural network; pairing the two to form an optimized combination set, inputting it into a multimodal feature prediction model to evaluate performance metrics; selecting the optimal combination for deployment and verification; rolling back if expectations are not met, retaining if they are met; and feeding the optimal combination and its actual performance metrics back to the model for fine-tuning. This invention enables collaborative optimization of SQL and indexes, breaking through the performance bottleneck of traditional fragmented tuning, integrating artificial intelligence and machine learning to improve prediction accuracy, and continuously improving prediction accuracy through a closed-loop feedback mechanism based on verification results, significantly improving database query performance and reducing manual tuning costs.

[0040] Additional advantages, objects, and features of the invention will be set forth in part in the description which follows, and will also become apparent in part to those skilled in the art upon studying the description, or may be learned by practice of the invention. The objects and other advantages of the invention can be realized and obtained by means of the structures specifically pointed out in the description and drawings.

[0041] Those skilled in the art will understand that the objectives and advantages achievable with the present invention are not limited to those specifically described above, and that the above and other objectives achievable with the present invention will become clearer from the following detailed description. Attached Figure Description

[0042] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this application, are not intended to limit the scope of the invention. In the drawings:

[0043] Figure 1This is a schematic diagram illustrating the steps of a SQL and index joint closed-loop optimization method based on verification results in one embodiment of the present invention.

[0044] Figure 2 This is a flowchart illustrating a method for joint closed-loop optimization of SQL and index based on verification results in one embodiment of the present invention.

[0045] Figure 3 This is a schematic diagram of the structure of a SQL and index joint closed-loop optimization system based on verification results in one embodiment of the present invention. Detailed Implementation

[0046] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the embodiments and accompanying drawings. Here, the illustrative embodiments and descriptions of this invention are used to explain the invention, but are not intended to limit the invention.

[0047] It should also be noted that, in order to avoid obscuring the invention with unnecessary details, only the structures and / or processing steps closely related to the solution according to the invention are shown in the accompanying drawings, while other details that are not closely related to the invention are omitted.

[0048] It should be emphasized that the term "including / comprises" as used herein refers to the presence of a feature, element, step, or component, but does not exclude the presence or addition of one or more other features, elements, steps, or components.

[0049] It should also be noted that, unless otherwise specified, the term "connection" in this article can refer not only to a direct connection, but also to an indirect connection involving an intermediary.

[0050] In the following description, embodiments of the invention will be illustrated with reference to the accompanying drawings. In the drawings, the same reference numerals represent the same or similar parts, or the same or similar steps.

[0051] It should be emphasized here that the step markers mentioned below are not a limitation on the order of the steps, but should be understood as meaning that the steps can be executed in the order mentioned in the embodiments, or in a different order than in the embodiments, or several steps can be executed simultaneously.

[0052] To address the problems of disconnected SQL and index optimization, lack of dynamic closed-loop verification and self-learning mechanisms, and semantic-physical decoupling in AI applications in existing technologies, this invention provides a joint closed-loop optimization method for SQL and indexes based on verification results, such as... Figure 1 As shown, the method includes the following steps S101~S107:

[0053] Step S101: Obtain slow query statements in the target database that do not meet preset performance indicators, perform parameter normalization on the slow query statements to form corresponding original SQL templates, and extract the workload characteristics generated by the slow query statements corresponding to each original SQL template during the preset time period and under the original index configuration. The workload characteristics include the number of executions, execution time, and resource consumption.

[0054] Step S102: Input the original SQL template into the pre-trained large-scale language model to generate multiple candidate SQL templates that are semantically equivalent to the original SQL template, forming a candidate SQL template set. The large-scale language model is trained on the SQL template training set, which includes multiple samples containing the SQL template to be optimized, the optimized SQL template, and the execution efficiency improvement of the optimized SQL template compared to the SQL template to be optimized. The optimized SQL template is semantically equivalent to the SQL template to be optimized.

[0055] Step S103: Extract all column names referenced in all query condition statements in the original SQL template as candidate index columns. Construct a heterogeneous graph based on the candidate index columns, the database tables to which the candidate index columns belong, and the primary and foreign key relationships between the database tables in the target database. Input the heterogeneous graph into a pre-trained graph neural network to generate multiple candidate index configurations, forming a candidate index configuration set.

[0056] Step S104: Pair the candidate SQL template set and the candidate index configuration set to generate multiple optimized combinations. Extract the features of the corresponding candidate SQL templates and candidate index configurations in each optimized combination to obtain query features and index features. Input the query features, index features, workload features, target database structural features, and data statistical features into a pre-trained multimodal feature prediction model to generate prediction performance metrics for each optimized combination. The data statistical features include the number of rows in the database table, the cardinality of the candidate index columns, the proportion of null values, and the data distribution histogram.

[0057] Step S105: Based on the predicted performance indicators and the preset optimization objectives, select the optimal optimization combination from multiple optimization combinations and deploy it to the target database.

[0058] Step S106: Within the preset verification period, monitor the actual performance metrics generated by the slow query statements corresponding to the candidate SQL templates in the optimal optimization combination under the candidate index configuration. If the actual performance metrics do not reach the baseline performance metrics, perform a rollback operation; otherwise, retain the deployment. The baseline performance metrics are calculated based on the performance metrics generated by the slow query statements corresponding to the original SQL templates under the original index configuration.

[0059] Step S107: Store the optimal combination and its corresponding real performance index as incremental training samples in the training sample library, and extract the sample data required for large-scale language models, graph neural networks and multimodal feature prediction models from the incremental training samples for incremental training to form a closed-loop optimization.

[0060] like Figure 2 The diagram shows a flowchart of the SQL and index joint closed-loop optimization method based on the verification results.

[0061] In step S101, slow query statements in the target database that do not meet the preset performance indicators are obtained.

[0062] In some embodiments, the slow query log of the target database is monitored in real time or near real time. The slow query log records queries whose execution time exceeds a preset time threshold. The execution time refers to the total time taken for the database server to execute a complete SQL statement.

[0063] The system can obtain the number of rows scanned and logical reads in real time or near real time from performance monitoring views (such as MySQL's performance_schema, Oracle's v$session, etc.) to identify queries that exceed preset thresholds. The number of rows scanned refers to the number of data table rows scanned by the database engine to execute the query, and the number of logical reads represents the number of memory data pages accessed by the database buffer pool to meet the query requirements.

[0064] Merge and deduplicate the above records and identify queries that exceed the corresponding preset thresholds, i.e. do not meet the preset performance indicators, and classify them as slow queries.

[0065] In some embodiments, by analyzing the abstract syntax tree (AST) of slow queries, query templates with the same operational logic but different specific parameters are identified. For example, the SQL statements in slow queries are parameterized. Specifically, the specific value in the slow query statement, such as the specific value 123 in WHERE id = 123, is replaced with a placeholder, thereby forming a normalized SQL template. This allows queries that are essentially the same but have different parameters to be aggregated together, facilitating statistical analysis and pattern recognition. To distinguish it from the subsequent optimized SQL template, the normalized SQL template is referred to as the original SQL template.

[0066] For each original SQL template, aggregate the workload characteristics generated by its corresponding slow query statement during a preset time period and under the original index configuration, including the number of executions, resource consumption, average / P95 / maximum execution time, average / maximum number of rows scanned, number of rows returned, and indexes used.

[0067] In step S102, the original SQL template is input into a pre-trained large-scale language model to generate multiple candidate SQL templates that are semantically equivalent to the original SQL template, forming a candidate SQL template set.

[0068] In some embodiments, methods for constructing (training) large-scale language models include:

[0069] Construct a training set of SQL templates. This training set contains multiple samples. Each sample includes the SQL template to be optimized (inefficient), the optimized SQL template (efficient), and the execution efficiency improvement of the optimized SQL template compared to the SQL template to be optimized. The execution efficiency improvement can be used to filter high-quality samples.

[0070] An initial model is constructed, consisting of an input layer, a multi-layer self-attention and feedforward network, and an output layer. This initial model takes the SQL templates to be optimized from the SQL template training set as input and outputs multiple rewritten SQL templates.

[0071] The initial model is trained using a training set, minimizing the loss between each rewritten SQL template and the optimized SQL template in the corresponding sample. Reinforcement learning is introduced, using the actual performance improvement as the reward signal. The parameters of the initial model are fine-tuned using the policy gradient method, ultimately resulting in a large-scale language model. The trained large-scale language model can not only perform conventional equivalent transformations, such as changing OR to UNION, but also generate deeper and more complex transformations that are difficult for human experts to conceive of.

[0072] In step S103, all column names referenced in all query condition statements in the original SQL template are extracted as candidate index columns. A heterogeneous graph is constructed based on the candidate index columns, their respective database tables, and the primary and foreign key relationships between the database tables in the target database. The heterogeneous graph is input into a pre-trained graph neural network to generate multiple candidate index configurations, forming a candidate index configuration set.

[0073] In some embodiments, the query condition statements in the original SQL template include WHERE clauses, JOIN clauses, GROUP BY clauses, and ORDER BY clauses.

[0074] In some embodiments, constructing a heterogeneous graph includes the following steps:

[0075] A node set is constructed by using candidate index columns as column nodes and the database tables to which the candidate index columns belong as table nodes. Column node characteristics include cardinality, data type, and frequency of query condition usage, while table node characteristics include the number of rows, the number of fields, and the update frequency.

[0076] Construct a set of edges, including column-table membership edges, column-column-column-table edges, and table-table primary / foreign key edges.

[0077] Column-table membership edges are undirected edges, connecting a candidate index column to its parent database table. Column-column-table edges are undirected edges, connecting two candidate index columns within the same database table. Table-table primary-foreign key edges are directed edges, pointing from the foreign key database table to the primary key database table.

[0078] For each type of edge, define edge features. Specifically: the edge feature for column-table membership edges is a fixed value of 1, representing the membership relationship; the edge feature for column-column-table edges is the ratio of the number of times the two candidate index columns appear simultaneously when the original SQL template is executed within a preset time period to the total number of times the original SQL template is executed within the preset time period; the edge feature for table-table primary / foreign key edges includes the ratio of the cardinality of the primary key column to the cardinality of the foreign key column, as well as the number of times the primary / foreign key pair is referenced in the original SQL template. The cardinality of the primary key column refers to the number of unique key values ​​in the primary key column.

[0079] Heterogeneous graphs are constructed based on the set of nodes, the set of edges, and the corresponding edge features.

[0080] By constructing heterogeneous graphs, discrete database objects (tables, columns) and their complex relationships are transformed into graph structures, making implicit knowledge such as foreign key relationships between tables and co-occurrence relationships between columns explicit. Graph neural networks (GNNs) can then learn deep patterns that traditional rules cannot capture. Furthermore, while traditional methods evaluate each index independently, heterogeneous graphs can quantify the synergistic effects of multiple column indexes (such as the combination of (user_id, create_time)) and identify redundant indexes.

[0081] The constructed heterogeneous graph is input into a pre-trained graph neural network. The graph neural network learns the embedding representation of each table and column in the heterogeneous graph, predicts the potential value of different index combinations, and effectively considers the interactions between indexes, such as prefix relationships, covering relationships, and redundancy relationships. Finally, a candidate index configuration set is output, as shown in formula (1):

[0082] ; (1)

[0083] in, Indicates the candidate index configuration set; Indicates the first There are 10 candidate index configurations. Each candidate index configuration may contain one or more indexes to be created or modified.

[0084] In some embodiments, the graph neural network includes convolutional layers, attention layers, and fully connected layers.

[0085] The convolutional layer updates the node embedding representation through a message passing mechanism, specifically:

[0086] For each column node, aggregate the embedding vectors of the table nodes it connects to and its neighboring column nodes; for each table node, aggregate the embedding vectors of the column nodes it contains and its associated tables.

[0087] The attention layer computes attention weights between column nodes based on the updated node embedding representation, specifically:

[0088] For column node pairs that appear in the same query condition, calculate attention scores based on their embedding similarity and edge features; for table node pairs with primary and foreign key relationships, calculate attention scores based on the frequency of join queries.

[0089] The fully connected layer weights and scores the updated node embedding representations based on attention weights, specifically:

[0090] If the attention weight between column nodes is lower than a preset association threshold, i.e. there is no strong association edge, then the value score of that column node as a single column index is output. Otherwise, the column node is combined with its associated nodes to form a column node combination, and the synergistic effect score of the column node combination forming a composite index is output. The synergistic effect score is based on the attention weight and the edge characteristics of column-column co-occurrence edges (such as the co-occurrence frequency of the two column nodes).

[0091] Select columns whose value scores exceed the first threshold to generate a single-column index set; select columns whose synergy scores exceed the second threshold to generate a composite index set; and summarize the single-column index set and the composite index set to obtain a candidate index configuration set.

[0092] In step S104, the candidate SQL template set and the candidate index configuration set are paired to form an optimized combination set.

[0093] In some embodiments, the Cartesian product method is used to combine each candidate SQL template in the candidate SQL template set with each candidate index configuration in the candidate index configuration set in pairs to form an optimized combination set containing all possible combinations.

[0094] The optimization set contains multiple optimization combinations. Each optimization combination contains a candidate SQL template and a candidate index configuration. Each optimization combination can be used as an independent unit to be input into the multimodal feature prediction model for evaluation.

[0095] In some embodiments, query features in the optimized combination are extracted using an SQL parser or a pre-trained large-scale language model, such as the embedded representation of candidate SQL template text, the graph embedded representation of the abstract syntax tree, and the one-hot encoding of operator types (JOIN, AGG, SORT, etc.). Index features in the optimized combination are extracted using the graph neural network in step S103, including the column combination of the candidate index configuration, the column order, the index type (B-Tree, Hash, etc.), whether it is a unique index, and whether it is a covering index.

[0096] In some embodiments, structural metadata features are extracted from the target database directory, including the number of database tables, the number of columns, the data types of the columns, primary and foreign key relationships (constructed as a graph structure), and table constraint information in the target database.

[0097] In some embodiments, information statistics are performed on the target database to obtain data statistical characteristics, including the number of rows in the database tables, the cardinality of candidate index columns, the proportion of null values, and data distribution histograms.

[0098] The query features, index features, workload features, structural features of the target database, and data statistics features are input into a pre-trained multimodal feature prediction model for prediction, generating multi-dimensional prediction performance indicators for each optimization combination, such as the change in execution time of the candidate SQL template compared to the original SQL template, the change in CPU utilization of the target database, and the storage overhead and maintenance cost of the candidate index configuration.

[0099] The multimodal features include the aforementioned query features, index features, workload features, target database structural features, and data statistical features. Specifically, based on query features, the model can capture the logical structure, complexity, and core intent of the query; based on index features, the model can accurately encode the physical attributes of the index configuration to be evaluated; based on workload features, the model can capture the importance of the query in the entire system and its historical performance baseline; based on structural features, the model can capture the static structure and constraints of the data; and based on data statistical features, the model can capture the true distribution, skewness, and selectivity of the data.

[0100] In some embodiments, the multimodal feature prediction model is implemented using a Transformer architecture, including an embedding layer, a feature fusion layer, an encoding layer, and an output layer.

[0101] The embedding layer transforms query features, index features, workload features, structural features, and data statistics features into embedded representations. For example, the SQL parser converts the rewritten SQL template into an abstract syntax tree, and a tree-based LSTM is used to generate query feature embeddings; a graph embedding algorithm transforms the target database structural features into structural feature embeddings; and one-hot encoding is performed on the column combinations and types configured in the index to generate index feature embeddings.

[0102] The feature fusion layer concatenates the embedded representations of each feature output from the embedding layer and inputs them into the linear projection layer to generate a fused feature representation.

[0103] The encoding layer enhances the fusion feature representation by discovering potential correlations between different features through a multi-head self-attention mechanism.

[0104] The output layer performs regression or classification predictions based on the enhanced fusion feature representation, and outputs multi-dimensional prediction performance metrics.

[0105] In some embodiments, the training data for the multimodal feature prediction model is multi-source, including historical successful and failed cases of manual or automatic optimization, the results of "what-if" analysis of a large number of candidate solutions in a sandbox environment (i.e., simulation of execution using the EXPLAIN function of the database optimizer), and the training sample library constructed in step S107. The samples in this training sample library are all performance data that have been tested in a real production environment and are one of the most important training data for the multimodal feature prediction model.

[0106] In step S105, based on the predicted performance indicators of each optimization combination generated in step S104 and the preset optimization objectives, the optimal optimization combination is selected from the optimization combination set and deployed to the target database.

[0107] The problem of choosing the optimal combination is modeled as a multi-objective optimization problem. Administrators can configure optimization strategies according to actual needs. For example, the optimization objective can be set to maximize the reduction of P95 query latency while ensuring that the storage overhead of adding new indexes does not exceed 5GB.

[0108] In some embodiments, after selecting the optimal combination, the process includes automatically deploying the optimal combination and semi-automatically deploying the optimal combination. Specifically:

[0109] If automatic deployment is adopted, the system receives the authorization information pre-issued by the administrator terminal, selects the optimal optimization combination, and automatically executes the following: replacing the original SQL template with the candidate SQL template in the optimal optimization combination, and creating or modifying the data definition language (DDL) statements required for the candidate index configuration in the optimal optimization combination.

[0110] If semi-automatic deployment is adopted, an optimization suggestion report containing information such as predicted performance indicators and actual deployment risks is generated based on the optimal combination and uploaded to the administrator terminal. After receiving the confirmation execution command, the following is executed: the candidate SQL template in the optimal combination is replaced with the original SQL template, and the data definition language statements required for the candidate index configuration in the optimal combination are created or modified.

[0111] In step S106, within a preset verification period, the actual performance metrics generated by the slow query statements corresponding to the candidate SQL templates in the optimal optimization combination under the candidate index configuration are monitored. If the actual performance metrics do not reach the baseline performance metrics, a rollback operation is performed; otherwise, the deployment is maintained.

[0112] In some embodiments, A / B verification is used to verify the optimization effect of the optimal combination, including:

[0113] Deploy the first set of configurations and the second set of configurations simultaneously in the target database. The first set of configurations consists of the original SQL template and the original index configuration, while the second set of configurations consists of the candidate SQL template and candidate index configuration from the optimal combination.

[0114] Within a preset verification period (e.g., 24 hours), the performance metrics generated by the query corresponding to the original SQL template in the first set of configurations under the original index configuration are collected in parallel to obtain the baseline performance metrics. The actual performance metrics generated by the query corresponding to the candidate SQL template in the second set of configurations under the candidate index configuration are collected in parallel. The baseline performance metrics and the actual performance metrics are compared to verify the optimization effect of the optimal combination.

[0115] In some embodiments, performance tracking algorithms are used to rigorously compare and analyze performance data before and after optimization. To eliminate interference caused by random fluctuations, statistical tests (such as Welch's t-test) can be used to determine whether the performance change is statistically significant.

[0116] In some embodiments, if the actual performance metric fails to reach the baseline performance metric, i.e. the optimization combination leads to significant performance degradation (Query Performance Regression, QPR), the system will automatically trigger a rollback operation, such as by executing DROP INDEX to undo the newly created index, thereby minimizing the negative impact on the production system and ensuring system stability.

[0117] In step S107, after the verification period ends, all optimized combinations that have been tested in a real environment, regardless of optimization efficiency, will be marked and stored in the training sample library. For example, they will be stored in the format [(SQL_2, IDX_1), performance improvement 30%].

[0118] In some embodiments, at preset time intervals, or when a sufficient number of samples are collected, the training sample set is used to incrementally train or fine-tune the large-scale language model, graph neural network, and multimodal feature prediction model to continuously improve the prediction capability.

[0119] Corresponding to the above method, the present invention also provides a SQL and index joint closed-loop optimization system based on verification results, including a processor, a memory, and a computer program / instructions stored in the memory, characterized in that the processor is used to execute the computer program / instructions, and when the computer program / instructions are executed, the system implements the steps of the aforementioned method.

[0120] In some embodiments, such as Figure 3 As shown, the present invention also provides a SQL and index joint closed-loop optimization system based on verification results, the system comprising:

[0121] The workload capture and analysis module is used to acquire slow query statements in the target database that do not meet preset performance indicators, normalize the parameters of the slow query statements to form raw SQL templates, and extract the workload characteristics generated by the slow query statements corresponding to each raw SQL template within a preset time period and under the original index configuration.

[0122] The candidate generation engine is jointly optimized, including a large-scale language model-driven SQL rewriting sub-engine and a graph neural network-driven index recommendation sub-engine, for generating candidate SQL template sets and candidate index configuration sets.

[0123] The unified impact prediction engine has a built-in pre-trained multimodal feature prediction model, which is used to pair candidate SQL template sets and candidate index configuration sets to form optimized combination sets, and generate multi-dimensional prediction performance indicators for each optimized combination based on query features, index features, workload features, and data statistical features.

[0124] The optimization scheme decision and execution module is used to select the optimal optimization combination and deploy it to the target database based on predicted performance indicators and preset optimization objectives.

[0125] The closed-loop verification and feedback module is used to monitor the actual performance indicators generated by the candidate SQL templates in the optimal optimization combination under the candidate new index configuration within a preset verification period, verify the optimization effect, and store the optimal optimization combination and the corresponding actual performance indicators into the training sample library for incremental training or fine-tuning of the models in the joint optimization candidate generation engine and the unified impact prediction engine.

[0126] Corresponding to the above method, the present invention also provides an electronic device including a computer device, the computer device including a processor and a memory, the memory storing computer instructions, the processor executing the computer instructions stored in the memory, and when the computer instructions are executed by the processor, the electronic device performs the steps of the method as described above.

[0127] This invention also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the aforementioned method. The computer-readable storage medium may be a tangible storage medium, such as random access memory (RAM), main memory, read-only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, register, floppy disk, hard disk, removable storage disk, CD-ROM, or any other form of storage medium known in the art.

[0128] Those skilled in the art will understand that the exemplary components, systems, and methods described in conjunction with the embodiments disclosed herein can be implemented in hardware, software, or a combination of both. Whether implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this invention. When implemented in hardware, it can be, for example, electronic circuits, application-specific integrated circuits (ASICs), appropriate firmware, plug-ins, function cards, etc. When implemented in software, the elements of this invention are programs or code segments used to perform the desired tasks. The programs or code segments can be stored in a machine-readable medium or transmitted over a transmission medium or communication link via data signals carried on a carrier wave.

[0129] It should be clarified that the present invention is not limited to the specific configurations and processes described above and shown in the figures. For the sake of brevity, detailed descriptions of known methods are omitted here. In the above embodiments, several specific steps are described and shown as examples. However, the method process of the present invention is not limited to the specific steps described and shown. Those skilled in the art can make various changes, modifications, and additions, or change the order of steps, after understanding the spirit of the present invention.

[0130] In this invention, features described and / or illustrated for one embodiment may be used in the same or similar manner in one or more other embodiments, and / or combined with or in place of features of other embodiments.

[0131] The above description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, various modifications and variations of the embodiments of the present invention are possible. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A closed-loop optimization method for SQL and indexes based on verification results, characterized in that, The method includes the following steps: Obtain slow query statements in the target database that do not meet the preset performance indicators, perform parameter normalization on the slow query statements to form corresponding original SQL templates, and extract the workload characteristics generated by the slow query statements corresponding to each original SQL template during the preset time period and under the original index configuration. The workload characteristics include the number of executions, execution time and resource consumption. The original SQL template is input into a pre-trained large-scale language model to generate multiple candidate SQL templates that are semantically equivalent to the original SQL template, forming a candidate SQL template set. The large-scale language model is trained based on the SQL template training set, which includes multiple samples containing the SQL template to be optimized, the optimized SQL template, and the execution efficiency improvement of the optimized SQL template compared to the SQL template to be optimized. The optimized SQL template is semantically equivalent to the SQL template to be optimized. Extract all column names referenced in all query condition statements in the original SQL template as candidate index columns. Construct a heterogeneous graph based on the candidate index columns, the database tables to which the candidate index columns belong, and the primary and foreign key relationships between the database tables in the target database. Input the heterogeneous graph into a pre-trained graph neural network to generate multiple candidate index configurations, forming a candidate index configuration set. The candidate SQL template set and the candidate index configuration set are paired to generate multiple optimized combinations. Features of the corresponding candidate SQL templates and candidate index configurations in each optimized combination are extracted to obtain query features and index features. The query features, index features, workload features, structural features of the target database, and data statistical features are input into a pre-trained multimodal feature prediction model to generate prediction performance indicators for each optimized combination. The data statistical features include the number of rows in the database table, the cardinality of the candidate index columns, the proportion of null values, and the data distribution histogram. Based on the predicted performance indicators and the preset optimization objectives, the optimal optimization combination is selected from the multiple optimization combinations and deployed to the target database; Within a preset verification period, monitor the actual performance metrics generated by the slow query statements corresponding to the candidate SQL templates in the optimal optimization combination under the candidate index configuration. If the actual performance metrics do not reach the baseline performance metrics, perform a rollback operation; otherwise, retain the deployment. The baseline performance metrics are calculated based on the performance metrics generated by the slow query statements corresponding to the original SQL templates under the original index configuration. The optimal combination and its corresponding real performance indicators are stored as incremental training samples in the training sample library. The sample data required for the large-scale language model, the graph neural network and the multimodal feature prediction model are extracted from the incremental training samples for incremental training to form a closed-loop optimization.

2. The method according to claim 1, characterized in that, Retrieve slow query statements from the target database that do not meet preset performance metrics, including: Monitor the slow query logs of the target database and obtain the first query statement whose execution time exceeds the corresponding preset threshold; Query the performance monitoring view of the target database to obtain a second query statement that exceeds the corresponding preset threshold for the number of scanned rows or the number of logical reads. The first query statement and the second query statement are merged and deduplicated to obtain the slow query statement.

3. The method according to claim 1, characterized in that, The method further includes training the large-scale language model, wherein training the large-scale language model includes: Construct the training set of the SQL template; An initial model is constructed, which includes an input layer, a multi-layer self-attention and feedforward network, and an output layer. The initial model takes the SQL templates to be optimized in the SQL template training set as input and outputs multiple rewritten SQL templates. The initial model is trained using the SQL template training set, minimizing the loss between each rewritten SQL template and the optimized SQL template, and using the actual performance improvement as a reward signal. The parameters of the initial model are then fine-tuned using the policy gradient method to finally obtain the large-scale language model.

4. The method according to claim 1, characterized in that, Constructing the heterogeneous graph includes: A node set is constructed by using the candidate index columns as column nodes and the database tables to which the candidate index columns belong as table nodes; An edge set is constructed, comprising column-table membership edges, column-column-column-table edges, and table-table primary / foreign key edges. Column-table membership edges connect the candidate index column to its corresponding database table. Column-column-table edges connect two candidate index columns within the same database table. Table-table primary / foreign key edges point from the foreign key database table to the primary key database table. The edge characteristic of column-table membership edges is membership relationship. The edge characteristic of column-column-table edges is the ratio of the number of times the two corresponding candidate index columns appear simultaneously when the slow query statement corresponding to the original SQL template is executed within a preset time period to the total number of times the slow query statement corresponding to the original SQL template is executed within the preset time period. The edge characteristic of table-table primary / foreign key edges includes the ratio of the cardinality of the primary key column to the cardinality of the foreign key column and the number of times the corresponding primary / foreign key pair is referenced in the original SQL template. The heterogeneous graph is constructed based on the set of nodes and the set of edges.

5. The method according to claim 4, characterized in that, The candidate index configuration set is obtained based on the following steps: The heterogeneous graph is input into the graph neural network, which includes convolutional layers, attention layers, and fully connected layers. The convolutional layers update the node embedding representations via a message passing mechanism. The attention layers calculate the attention weights between the column nodes based on the updated node embedding representations. The fully connected layers weight and score the updated node embedding representations based on the attention weights. If the attention weights between the column nodes are lower than a preset association threshold, the value score of the column node as a single-column index is output. Otherwise, the column node and its associated nodes are combined to form a column node combination, and the synergistic effect score of the column node combination forming a composite index is output. The synergistic effect score is obtained based on the attention weights and the edge features of the column-to-column same-table edges. Columns whose value scores exceed a first threshold are selected to generate a single-column index set. Columns whose synergy scores exceed a second threshold are selected to generate a composite index set. The single-column index set and the composite index set are combined to obtain the candidate index configuration set.

6. The method according to claim 1, characterized in that, The candidate SQL template set and the candidate index configuration set are paired to generate multiple optimized combinations, including: The Cartesian product method is used to combine each candidate SQL template in the candidate SQL template set with each candidate index configuration in the candidate index configuration set in pairs to form an optimized combination set containing all possible combinations.

7. The method according to claim 1, characterized in that, When verifying the optimal combination within the preset verification period, A / B verification is performed, including: The first set of configurations and the second set of configurations are deployed simultaneously in the target database. The first set of configurations consists of the original SQL template and the original index configuration, and the second set of configurations consists of the candidate SQL template and the candidate index configuration in the optimal combination. Within the preset verification period, the performance metrics generated by the slow query statements corresponding to the original SQL templates in the first set of configurations being executed under the original index configuration are collected in parallel to obtain the baseline performance metrics. The actual performance metrics generated by the candidate SQL templates in the second set of configurations being executed under the candidate index configuration are collected in parallel. The baseline performance metrics and the actual performance metrics are compared to obtain the verification result of the optimal combination.

8. The method according to claim 1, characterized in that, The multimodal feature prediction model is implemented using a Transformer architecture, including an embedding layer, a feature fusion layer, an encoding layer, and an output layer. The embedding layer converts the query features, index features, workload features, structural features, and data statistical features into embedded representations, which are then concatenated by the feature fusion layer to generate a fused feature representation. The encoding layer enhances the fused feature representation using a multi-head self-attention mechanism. The output layer performs predictions based on the enhanced fused feature representation and outputs the prediction performance metrics. These metrics include the execution time of the slow query statement corresponding to the candidate SQL template, the CPU utilization of the target database, and the storage overhead and maintenance cost of the candidate index configuration.

9. The method according to claim 1, characterized in that, Selecting the optimal combination for deployment to the target database includes both automated and semi-automatic deployment: When the automatic deployment is performed, the system receives authorization information pre-issued by the administrator terminal and automatically executes the following: replacing the original SQL template with the candidate SQL template in the optimal combination, and creating or modifying the data definition language statements required for the candidate index configuration in the optimal combination. When using the semi-automatic deployment method, an optimization suggestion report is generated based on the optimal optimization combination and uploaded to the administrator terminal. After receiving the confirmation execution instruction, the following actions are performed: replacing the original SQL template with the candidate SQL template in the optimal optimization combination, and creating or modifying the data definition language statements required for the candidate index configuration in the optimal optimization combination.

10. A SQL and index joint closed-loop optimization system based on verification results, comprising a processor, a memory, and a computer program / instructions stored in the memory, characterized in that, The processor is configured to execute the computer program / instructions, and when the computer program / instructions are executed, the system implements the steps of the method as described in any one of claims 1 to 9.

Images