SQL and index combined closed-loop optimization method and system based on verification result

Abstract

The invention provides an SQL and index combined closed-loop optimization method and system based on a verification result, and relates to the technical field of electric digital data processing, and the method comprises the steps: obtaining a slow query statement of a target database, normalizing parameters into an original SQL template, and extracting the workload characteristics of the original SQL template; an efficient candidate SQL template set is generated through a large-scale language model, meanwhile, a heterogeneous graph is constructed, and a candidate index configuration set is generated through a graph neural network; pairing the two to form an optimized combination set, and inputting a multi-modal feature prediction model to evaluate performance indexes; and selecting an optimal combination deployment and verifying, if an expectation is not reached, rolling back, if a standard is reached, retaining, and feeding back the optimal combination and a real performance index thereof to the model for fine tuning. According to the method, collaborative optimization of the SQL and the index can be realized, artificial intelligence and machine learning are fused to improve the prediction precision, the prediction accuracy is continuously improved through a closed-loop feedback mechanism based on the verification result, the database query performance is greatly improved, and the manual tuning cost is reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of electric digital data processing, and in particular to a SQL and index joint closed-loop optimization method and system based on verification results. BACKGROUND

[0002] In modern data-intensive applications, database performance is the core of system response capability and user experience. With the exponential growth of data and the complexity of query logic, SQL performance degradation has become a common problem. The traditional mode of relying on database administrators (Database Administrator, DBA) to manually optimize has limitations such as labor-intensive, low efficiency, dependence on personal experience, and difficulty in scaling, and cannot adapt to the needs of data explosion and rapid application iteration.

[0003] To break through this limitation, database optimization technology has been continuously evolving. Early rule-based optimizers (Rule-Based Optimizer, RBO) cannot adapt to the diversity of data distribution due to their reliance on static rules. Although cost-based optimizers (Cost-Based Optimizer, CBO) can choose the best through statistical information, the statistical information can become outdated or distorted due to data changes, leading to decision bias.

[0004] Subsequent automated tools still have obvious shortcomings: SQL rewriting tools (such as SQLAdvisor) only analyze a single SQL, lacking a global perspective; Automatic Index Tuning (Automatic Index Tuning, AIT) tools (such as DTA) can generate index schemes, but SQL and index optimization are separate, specifically, SQL rewriting can invalidate existing indexes, and index design can be invalidated due to changes in SQL logic, therefore, the simple combination of local optima cannot achieve global optimality, limiting the performance improvement space.

[0005] In recent years, artificial intelligence (Artificial Intelligence, AI) and machine learning (Machine Learning, ML) technologies have been introduced into the optimization field. Learning optimizers enhance CBO through ML, intelligent index recommendations use algorithms to search for better configurations, and large language models (Large Language Model, LLM) are used for SQL rewriting or execution plan generation. However, these methods still have defects: learning optimizers have complex feature engineering and limited generalization ability; LLMs can understand SQL semantics, but lack awareness of the physical state of the database, making it difficult to guarantee the actual effectiveness of generated suggestions, and the joint optimization problem of SQL and index has not been solved. SUMMARY

[0006] In view of this, the embodiment of the present application provides a SQL and index combined closed-loop optimization method and system based on verification results to solve the problems of SQL and index optimization fragmentation, lack of dynamic closed-loop verification and self-learning mechanism, and semantic and physical disconnection in AI application in the prior art.

[0007] In one aspect, the present application provides a SQL and index combined closed-loop optimization method based on verification results, which comprises the following steps: Obtaining slow query statements in a target database that do not meet a preset performance indicator, performing parameter normalization processing on the slow query statements to form corresponding original SQL templates, and extracting work load characteristics generated by the slow query statements corresponding to each original SQL template when executed under original index configuration within a preset time period, the work load characteristics including execution times, execution time and resource consumption; Inputting the original SQL templates into a pre-trained large-scale language model to generate a plurality of candidate SQL templates semantically equivalent to the original SQL templates, constituting a candidate SQL template set, wherein the large-scale language model is trained based on a SQL template training set, the SQL template training set including a plurality of samples containing a to-be-optimized SQL template, an optimized SQL template and an execution efficiency improvement amount of the optimized SQL template compared with the to-be-optimized SQL template, the optimized SQL template being semantically equivalent to the to-be-optimized SQL template; Extracting all column names referenced in all query condition statements in the original SQL templates as candidate index columns, constructing a heterogeneous graph based on the candidate index columns, database tables to which the candidate index columns belong, and primary-foreign key relationships between each database table in the target database, inputting the heterogeneous graph into a pre-trained graph neural network to generate a plurality of candidate index configurations, constituting a candidate index configuration set; Pairing the candidate SQL template set and the candidate index configuration set to generate a plurality of optimization combinations, extracting features of the corresponding candidate SQL template and candidate index configuration in each optimization combination to obtain query features and index features; inputting the query features, the index features, the work load characteristics, the structure features of the target database and the data statistical features into a pre-trained multi-modal feature prediction model to generate predicted performance indicators of each optimization combination, wherein the data statistical features include the number of rows of the database table, the cardinality of the candidate index column, the null value ratio and the data distribution histogram; Based on the predicted performance indicators and a preset optimization target, selecting an optimal optimization combination from the plurality of optimization combinations and deploying it to the target database; In a preset verification period, a real performance index generated by execution of a slow query statement corresponding to a candidate SQL template in the optimal optimization combination under a candidate index configuration is monitored, if the real performance index does not reach a benchmark performance index, a rollback operation is performed, otherwise, deployment is retained, wherein the benchmark performance index is calculated based on a performance index generated by execution of a slow query statement corresponding to the original SQL template under the original index configuration; The optimal optimization combination and the corresponding real performance index are stored as incremental training samples in a training sample library, and sample data required by the large-scale language model, the graph neural network and the multi-modal feature prediction model are extracted from the incremental training samples for incremental training to form a closed-loop optimization.

[0008] In some embodiments of the present application, the slow query statement in the target database that does not meet the preset performance index is obtained, comprising: Monitoring the slow query log of the target database to obtain a first query statement with an execution time exceeding a corresponding preset threshold; Querying the performance monitoring view of the target database to obtain a second query statement with a scan row number or a logical read number exceeding a corresponding preset threshold; Combining and deduplicating the first query statement and the second query statement to obtain the slow query statement.

[0009] In some embodiments of the present application, the method further comprises training the large-scale language model, and the training of the large-scale language model comprises: Constructing the SQL template training set; Constructing an initial model, the initial model comprising an input layer, a multi-layer self-attention and feedforward network, and an output layer; the initial model taking a to-be-optimized SQL template in the SQL template training set as input and outputting a plurality of rewritten SQL templates; Training the initial model using the SQL template training set to minimize the loss between each rewritten SQL template and the optimized SQL template, and taking the actual performance improvement as a reward signal, and using a policy gradient method to fine-tune the parameters of the initial model to finally obtain the large-scale language model.

[0010] In some embodiments of the present application, constructing the heterogeneous graph comprises: Taking the candidate index column as a column node and the database table to which the candidate index column belongs as a table node to construct a node set; constructing an edge set, the edge set including column-table membership edges, column-column same-table edges and table-table primary-foreign key edges, the column-table membership edges being connected between the candidate index columns and the database tables to which the candidate index columns belong, the column-column same-table edges being connected between two candidate index columns in the same database table, and the table-table primary-foreign key edges being pointed from a foreign key database table to a primary key database table; wherein an edge feature of the column-table membership edges is a membership relationship, an edge feature of the column-column same-table edges is a ratio of a number of times that the two candidate index columns corresponding to the original SQL template appear simultaneously when the slow query statement corresponding to the original SQL template is executed in a preset time period to a total number of times that the slow query statement corresponding to the original SQL template is executed in the preset time period, and an edge feature of the table-table primary-foreign key edges includes a ratio of a primary key column cardinality to a foreign key column cardinality and a number of references of the corresponding primary-foreign key pair in the original SQL template; constructing the heterogeneous graph based on the node set and the edge set.

[0011] In some embodiments of the present application, the candidate index configuration set is obtained based on the following steps: inputting the heterogeneous graph into the graph neural network, the graph neural network including a convolution layer, an attention layer and a fully connected layer; wherein the convolution layer updates node embedding representations through a message passing mechanism, the attention layer calculates attention weights between the column nodes based on the updated node embedding representations, and the fully connected layer weights and scores the updated node embedding representations based on the attention weights; if the attention weights between the column nodes are lower than a preset correlation threshold, the column nodes are output as value scores of single-column indexes; otherwise, the column nodes and their associated nodes are combined to form column node combinations, and the column node combinations are output as synergistic effect scores of composite indexes, the synergistic effect scores being obtained based on the attention weights and the edge features of the column-column same-table edges; selecting columns with value scores exceeding a first threshold to generate a single-column index set, selecting columns with synergistic effect scores exceeding a second threshold to generate a composite index set, and summarizing the single-column index set and the composite index set to obtain the candidate index configuration set.

[0012] In some embodiments of the present application, the candidate SQL template set and the candidate index configuration set are paired to generate multiple groups of optimization combinations, including: using a Cartesian product method, each candidate SQL template in the candidate SQL template set is combined with each candidate index configuration in the candidate index configuration set to form an optimization combination set containing all possible combinations.

[0013] In some embodiments of the present application, when the optimal optimization combination is verified in the preset verification period, an A / B verification method is used for verification, including: The first set of configurations and the second set of configurations are simultaneously deployed in the target database, the first set of configurations being the original SQL template and the original index configuration, and the second set of configurations being the candidate SQL template and the candidate index configuration in the optimal optimization combination; In the preset verification period, the performance indicators generated by the execution of the slow query statement corresponding to the original SQL template under the original index configuration are collected in parallel to obtain the baseline performance indicators, and the real performance indicators generated by the execution of the candidate SQL template under the candidate index configuration are collected in parallel, and the baseline performance indicators and the real performance indicators are compared to obtain the verification result of the optimal optimization combination.

[0014] In some embodiments of the present application, the multi-modal 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 feature, the index feature, the workload feature, the structure feature, and the data statistics feature into embedded representations, which are spliced by the feature fusion layer to generate a fusion feature representation. The encoding layer enhances the fusion feature representation through a multi-head self-attention mechanism, and the output layer predicts based on the enhanced fusion feature representation to output the predicted performance indicators, including the execution time of the slow query statement corresponding to the candidate SQL template, the CPU usage of the target database, the storage overhead of the candidate index configuration, and the maintenance cost.

[0015] In some embodiments of the present application, selecting the optimal optimization combination for deployment to the target database includes automatic execution of deployment and semi-automatic execution of deployment: When the automatic execution of deployment is used, the authorization information previously issued by the administrator terminal is received, and the following operations are automatically performed: replacing the candidate SQL template in the optimal optimization combination with the original SQL template, and creating or modifying the data definition language statements required for the candidate index configuration in the optimal optimization combination; When the semi-automatic execution of deployment is used, an optimization suggestion report is generated based on the optimal optimization combination and uploaded to the administrator terminal, and after receiving a confirmation execution instruction, the following operations are performed: replacing the candidate SQL template in the optimal optimization combination with the original SQL template, and creating or modifying the data definition language statements required for the candidate index configuration in the optimal optimization combination.

[0016] On the other hand, the present application also provides a SQL and index joint closed-loop optimization system based on a verification result, including a processor, a memory, and a computer program / instruction stored on the memory. The processor is used to execute the computer program / instruction, and when the computer program / instruction is executed, the system implements the steps of the method according to any one of the above embodiments.

[0017] The present invention provides a method and system for joint closed-loop optimization of SQL and index based on verification results, which relates to the field of electronic digital data processing technology, including: obtaining slow query statements of the target database and normalizing the parameters into original SQL templates, extracting their workload characteristics; generating an efficient candidate SQL template set through a large-scale language model, and at the same time constructing a heterogeneous graph and generating a candidate index configuration set through a graph neural network; pairing the two to form an optimized combination set, and inputting a multimodal feature prediction model to evaluate performance indicators; selecting the optimal combination for deployment and verification, rolling back if it does not meet expectations, retaining it if it meets the standards, and feeding back the optimal combination and its true performance indicators to the model for fine-tuning. The present invention can achieve collaborative optimization of SQL and indexes, break through the performance bottleneck of traditional fragmented tuning, integrate artificial intelligence and machine learning to improve prediction accuracy, and continuously improve prediction accuracy through a closed-loop feedback mechanism based on verification results, greatly improving database query performance and reducing manual tuning costs.

[0018] Additional advantages, objects, and features of the present invention will be set forth in part in the following description and will become apparent to those skilled in the art upon examination of the following or may be learned from practice of the present invention. The objects and other advantages of the present invention may be realized and obtained by the structures particularly pointed out in the description and drawings.

[0019] Those skilled in the art will understand that the purposes and advantages that can be achieved by the present invention are not limited to the above specific descriptions, and the above and other purposes that can be achieved by the present invention will be more clearly understood based on the following detailed description. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] The drawings described herein are used to provide a further understanding of the present invention, constitute a part of this application, and do not constitute a limitation of the present invention. In the drawings: Figure 1 The figure is a schematic diagram of the steps of a SQL and index joint closed-loop optimization method based on verification results in one embodiment of the present invention.

[0021] Figure 2 Schematic diagram of a flow chart of a method for joint closed-loop optimization of SQL and indexes based on verification results in one embodiment of the present invention.

[0022] Figure 3 Schematic diagram of the structure of the SQL and index joint closed-loop optimization system based on verification results in one embodiment of the present invention DETAILED DESCRIPTION

[0023] In order to make the objects, technical solutions and advantages of the present application clearer, further detailed description will be given to the present application in combination with embodiments and drawings. Herein, the illustrative embodiments of the present application and their descriptions are used to explain the present application but not as a limitation to the present application.

[0024] It should be noted that, in order to avoid the present application being obscured by unnecessary details, only the structures and / or processing steps closely related to the solutions according to the present application are shown in the drawings, and other details not closely related to the present application are omitted.

[0025] It should be emphasized that the term "comprising" as used herein means the presence of the stated features, elements, steps or components but does not preclude the presence or addition of one or more other features, elements, steps or components.

[0026] It should be noted that, if not otherwise specified, the term "connected" as used herein means not only direct connection but also indirect connection in the presence of an intermediate.

[0027] Hereinafter, embodiments of the present application will be described with reference to the accompanying drawings. In the drawings, the same reference numerals represent the same or similar components or the same or similar steps.

[0028] It should be emphasized that the step labels mentioned hereinafter are not a limitation to the order of the steps, and it should be understood that the steps can be performed in the order mentioned in the embodiments, can be different from the order in the embodiments, or several steps can be performed simultaneously.

[0029] In order to solve the problems of SQL and index optimization being split, lack of dynamic closed-loop verification and self-learning mechanism, and semantic and physical disconnection in AI application in the prior art, the present application provides a SQL and index joint closed-loop optimization method based on verification results, as shown in Figure 1 The method comprises the following steps S101-S107: Step S101: obtaining slow query statements in a target database that do not meet preset performance indicators, performing parameter normalization processing on the slow query statements to form corresponding original SQL templates, and extracting work load characteristics generated by the slow query statements corresponding to each original SQL template when executed under original index configuration within a preset time period. The work load characteristics include execution times, execution time and resource consumption.

[0030] Step S102: Input the original SQL template 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 a SQL template training set, which includes multiple samples including a SQL template to be optimized, an optimized SQL template, and an improvement in execution efficiency 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.

[0031] 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 the pre-trained graph neural network, generate multiple candidate index configurations, and constitute a candidate index configuration set.

[0032] Step S104: Pair the candidate SQL template set with the candidate index configuration set to generate multiple optimized combinations. Extract the features of the candidate SQL template and candidate index configuration corresponding to 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 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 null value ratio, and the data distribution histogram.

[0033] Step S105: Based on the predicted performance index and the preset optimization goal, the optimal optimization combination is selected from the multiple groups of optimization combinations and deployed to the target database.

[0034] Step S106: Within a preset verification cycle, monitor the actual performance indicators generated by the slow query statements corresponding to the candidate SQL templates in the optimal optimization combination when executed under the candidate index configuration. If the actual performance indicators do not reach the benchmark performance indicators, perform a rollback operation; otherwise, retain the deployment, wherein the benchmark performance indicators are calculated based on the performance indicators generated by the slow query statements corresponding to the original SQL template when executed under the original index configuration.

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

[0036] like Figure 2 FIG. 1 is a flow chart of the SQL and index joint closed-loop optimization method based on the verification results.

[0037] In step S101, a slow query statement that does not meet the preset performance index in the target database is obtained.

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

[0039] The number of scanned rows and the number of logical reads in execution are obtained in real time or quasi-real time from a performance monitoring view (such as performance_schema of MySQL, v$session of Oracle, etc.), and queries exceeding a preset threshold are identified, wherein the number of scanned rows refers to the number of data table rows scanned by the database engine for executing the query, and the number of logical reads refers to the number of memory data pages accessed by the database buffer pool (Buffer Pool) to meet the query requirements.

[0040] The above records and the identified queries exceeding the corresponding preset threshold, i.e., not meeting the preset performance index, are merged and de-duplicated as slow queries.

[0041] In some embodiments, by analyzing the abstract syntax tree (AST) of the slow query, a query template with the same operation logic but different specific parameters is identified, such as parameterizing the SQL statement in the slow query. Specifically, the specific value in the slow query statement, such as 123 in WHERE id = 123, is replaced with a placeholder, thereby forming a normalized SQL template, so as to aggregate queries with the same essence but different parameters together for statistical analysis and pattern recognition. In order to distinguish from the subsequent optimized SQL template, the normalized SQL template is denoted as the original SQL template.

[0042] For each original SQL template, the workload characteristics of its corresponding slow query statements executed within a preset time period under the original index configuration are aggregated, including execution times, resource consumption, average / P95 / maximum execution time, average / maximum scan row number, returned row number, used index, etc.

[0043] In step S102, the original SQL template is input into a pre-trained large-scale language model to generate a plurality of candidate SQL templates semantically equivalent to the original SQL template, constituting a candidate SQL template set.

[0044] In some embodiments, the construction (training) method of the large-scale language model includes: A SQL template training set is constructed, which contains multiple samples, each of which contains a to-be-optimized SQL template (inefficient), an optimized SQL template (efficient), and an execution efficiency improvement amount of the optimized SQL template compared with the to-be-optimized SQL template. The execution efficiency improvement amount can be used to screen high-quality samples.

[0045] 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 to-be-optimized SQL template in the SQL template training set as input and outputs multiple rewritten SQL templates.

[0046] The initial model is trained using the training set to minimize the loss between each rewritten SQL template and the optimized SQL template in the corresponding sample, and reinforcement learning is introduced, taking the actual performance improvement amount as a reward signal. The policy gradient method is used to fine-tune the parameters of the initial model, and finally a large-scale language model is obtained. The large-scale language model obtained by training can not only perform regular equivalent transformations such as OR to UNION, but also generate more complex transformations that are not easily thought of by human experts.

[0047] 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, the database tables to which the candidate index columns belong, and the primary-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, which form a candidate index configuration set.

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

[0049] In some embodiments, constructing the heterogeneous graph includes the following steps: The candidate index columns are taken as column nodes, and the database tables to which the candidate index columns belong are taken as table nodes to construct a node set. The column node features include cardinality, data type, and query condition statement usage frequency, and the table node features include row number, field number, and update frequency.

[0050] An edge set is constructed, including column-table membership edges, column-column same-table edges, and table-table primary-foreign key edges. Among them, The column-table membership edge is a non-directional edge connecting the candidate index column and the database table to which it belongs. The column-column same-table edge is a non-directional edge connecting two candidate index columns in the same database table. The table-table primary-foreign key edge is a directional edge pointing from the foreign key database table to the primary key database table.

[0051] An edge feature is defined for each type of edge. Specifically, the edge feature of a column-table membership edge is a fixed value 1, representing the membership relationship. The edge feature of a column-column co-occurrence edge is the ratio of the number of times that two candidate index columns appear simultaneously in a preset time period when the original SQL template is executed to the total number of times that the original SQL template is executed in the preset time period. The edge feature of a table-table primary-foreign key edge includes the ratio of the primary key column base number to the foreign key column base number and the number of references of the primary-foreign key pair in the original SQL template. The primary key column base number refers to the number of unique key values in the primary key column.

[0052] An heterogeneous graph is constructed based on the node set, the edge set, and the corresponding edge features.

[0053] By constructing the heterogeneous graph, discrete database objects (tables, columns) and their complex associations are converted into a graph structure, making the implicit knowledge such as table inter-foreign key association and column inter-co-occurrence relationship explicit. Graph neural networks (GNNs) can learn deep patterns that traditional rules cannot capture. Meanwhile, traditional methods independently evaluate each index, while the heterogeneous graph can quantify the synergistic effect of multi-column indexes (such as (user_id, create_time) combination) and identify redundant indexes.

[0054] The constructed heterogeneous graph is input into a pre-trained graph neural network, which 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 interaction between indexes such as prefix relationship, coverage relationship, and redundancy relationship. Finally, the candidate index configuration set is output, as shown in equation (1): (1) wherein, represents the candidate index configuration set. represents the i-th candidate index configuration. Each candidate index configuration may include one or more indexes to be created or modified.

[0055] In some embodiments, the graph neural network includes a convolutional layer, an attention layer, and a fully connected layer.

[0056] The convolutional layer updates the node embedding representation through a message passing mechanism. Specifically: For each column node, the embedding vectors of its connected table nodes and adjacent column nodes are aggregated. For each table node, the embedding vectors of its contained column nodes and associated tables are aggregated.

[0057] The attention layer calculates the attention weight between column nodes based on the updated node embedding representation. Specifically: ​For column node pairs appearing in the same query condition, the attention score based on their embedding similarity and edge features is calculated; for table node pairs with primary-foreign key relationships, the attention score based on the frequency of join queries is calculated.

[0058] The full connection layer weights and scores the updated node embedding representation based on the attention weight, specifically: If the attention weight between column nodes is lower than the preset association threshold, i.e., there is no strong association edge, the column node is output as the value score of single-column index, otherwise, the column node and its associated nodes are combined to form a column node combination, and the column node combination is output as the synergy effect score of composite index. The synergy effect score is based on the attention weight and the edge features of the column-column same-table edge (such as the co-occurrence frequency of two column nodes).

[0059] Select columns with value scores exceeding the first threshold to generate a single-column index set, select columns with synergy effect scores exceeding the second threshold to generate a composite index set, and combine the single-column index set and the composite index set to obtain a candidate index configuration set.

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

[0061] 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 to form an optimization combination set containing all possible combinations.

[0062] The optimization combination set contains multiple optimization combinations, each optimization combination contains a candidate SQL template and a candidate index configuration, and each optimization combination can be input into a multi-modal feature prediction model as an independent unit for evaluation.

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

[0064] In some embodiments, the 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, the primary-foreign key relationships (constructed as a graph structure), the constraint information of the tables, and other features.

[0065] In some embodiments, information statistics are performed on the target database to obtain data statistical features, including the number of rows of the database table, the cardinality of the candidate index column, the null value ratio, the data distribution histogram, and the like.

[0066] The query features, the index features, the workload features, the structural features of the target database, and the data statistical features are input into a pre-trained multi-modal feature prediction model to perform prediction, to generate multi-dimensional prediction performance indicators of each optimization combination, such as the change amount of the execution time of the candidate SQL template compared with the original SQL template, the change amount of the CPU usage of the target database, the storage overhead and the maintenance cost of the candidate index configuration, and the like.

[0067] The multi-modal features include the query features, the index features, the workload features, the structural features of the target database, and the data statistical features described above. Specifically, based on the query features, the model can capture the logical structure, the complexity, and the core intent of the query, and the like; based on the index features, the model can accurately encode the physical properties of the index configuration to be evaluated; based on the workload features, the model can capture the importance of the query in the entire system and its historical performance baseline; based on the structural features, the model can capture the static structure and the constraint relationship of the data; and based on the data statistical features, the model can capture the real distribution, the skewness, and the selectivity of the data.

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

[0069] The embedding layer converts the query features, the index features, the workload features, the structural features, and the data statistical features into embedding representations. For example, the rewritten SQL template is converted into an abstract syntax tree through a SQL parser, and a tree LSTM is used to generate query feature embeddings; the target database structural features are converted into structural feature embeddings through a graph embedding algorithm; the column combination and type of the index configuration are One-hot encoded to generate index feature embeddings, and the like.

[0070] The feature fusion layer splices the embedding representations of the features output by the embedding layer and inputs them into a linear projection layer to generate a fusion feature representation.

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

[0072] The output layer performs regression or classification prediction based on the enhanced fusion feature representation, to output multi-dimensional prediction performance indicators.

[0073] In some embodiments, the training data of the multi-modal feature prediction model is multi-source, including successful and failed cases historically optimized manually or automatically, results of "what-if" analysis (i.e., simulation of execution by using the EXPLAIN function of the database optimizer) on a large number of candidate solutions in a sandbox environment, and the training sample library constructed in step S107, wherein each sample in the training sample library is performance data verified in a real production environment, and is one of the most important training data of the multi-modal feature prediction model.

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

[0075] The selection problem of the optimization combination is modeled as a multi-objective optimization problem, and the administrator can configure the optimization strategy according to the actual demand, for example, set the optimization target of maximizing the reduction amplitude of P95 query delay under the premise that the storage overhead of the added index does not exceed 5 GB.

[0076] In some embodiments, after selecting the optimal optimization combination, the deployment of the optimal optimization combination is automatically or semi-automatically executed, specifically: If automatic deployment is adopted, the authorization information previously issued by the administrator terminal is received, and after the optimal optimization combination is selected, the following operations are automatically executed: the candidate SQL template in the optimal optimization combination is replaced with the original SQL template, and the data definition language (DDL) statement required for creating or modifying the candidate index configuration in the optimal optimization combination is created or modified.

[0077] If semi-automatic deployment is adopted, an optimization suggestion report containing predicted performance indicators and actual deployment risks and the like is generated based on the optimal optimization combination, and is uploaded to the administrator terminal, and after receiving the confirmation execution instruction, the following operations are executed: the candidate SQL template in the optimal optimization combination is replaced with the original SQL template, and the data definition language statement required for creating or modifying the candidate index configuration in the optimal optimization combination is created or modified.

[0078] In step S106, within a preset verification period, the real performance indicators generated by the execution of the slow query statement corresponding to the candidate SQL template in the optimal optimization combination under the candidate index configuration are monitored, if the real performance indicators do not reach the benchmark performance indicators, a rollback operation is performed, otherwise the deployment is maintained.

[0079] In some embodiments, the A / B verification method is used to verify the optimization effect of the optimal optimization combination, including: deploying the first set of configurations and the second set of configurations in the target database simultaneously, wherein the first set of configurations is the original SQL templates and the original index configurations, and the second set of configurations is the candidate SQL templates and the candidate index configurations in the optimal optimization combination.

[0080] In a preset verification period (such as 24 hours), the performance indicators generated by executing the original SQL templates in the original index configurations are collected in parallel to obtain baseline performance indicators, and the real performance indicators generated by executing the candidate SQL templates in the candidate index configurations are collected in parallel, and the baseline performance indicators and the real performance indicators are compared to verify the optimization effect of the optimal optimization combination.

[0081] In some embodiments, the performance tracking algorithm is used to strictly compare and analyze the performance data before and after optimization. To exclude the interference caused by random fluctuations, a statistical test method (such as Welch's t-test) can be used to determine whether the change in performance is statistically significant.

[0082] In some embodiments, if the real performance indicators do not reach the baseline performance indicators, that is, the optimization combination leads to significant performance regression (Query Performance Regression, QPR), the system will automatically trigger a rollback operation, such as executing DROP INDEX to revoke the newly created index, thereby minimizing the negative impact on the production system and ensuring system stability.

[0083] In step S107, after the verification period ends, all optimization combinations that have passed the real environment test are marked and stored in the training sample library regardless of optimization efficiency. For example, they are stored in the format [(SQL_2, IDX_1), performance improvement 30%].

[0084] In some embodiments, according to a preset time interval or when enough samples are collected, the training sample set is used to incrementally train or fine-tune large-scale language models, graph neural networks, and multi-modal feature prediction models to continuously improve prediction capabilities.

[0085] Corresponding to the above method, the present application also provides a SQL and index joint closed-loop optimization system based on verification results, comprising a processor, a memory and a computer program / instructions stored on 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 realizes the steps of the above method.

[0086] In some embodiments, as shown in Figure 3 The present application also provides a SQL and index joint closed-loop optimization system based on verification results, which comprises: The work load capturing and analyzing module is used for obtaining slow query statements in the target database which do not meet preset performance indexes, performing parameter normalization on the slow query statements, forming original SQL templates, and extracting work load features generated by the slow query statements corresponding to each original SQL template in a preset time period and under original index configuration.

[0087] The joint optimization candidate generating engine includes a large-scale language model driven SQL rewriting sub-engine and a graph neural network driven index recommendation sub-engine, and is used for generating a candidate SQL template set and a candidate index configuration set.

[0088] The unified influence predicting engine is internally provided with a pre-trained multi-modal feature predicting model, is used for pairing the candidate SQL template set and the candidate index configuration set to form an optimization combination set, and generates multi-dimensional predicted performance indexes of each optimization combination based on query features, index features, work load features and data statistical features.

[0089] The optimization scheme decision and execution module is used for selecting an optimal optimization combination based on the predicted performance indexes and a preset optimization target, and deploying the optimal optimization combination to the target database.

[0090] The closed-loop verification and feedback module is used for monitoring real performance indexes generated by the candidate SQL templates in the optimal optimization combination under the candidate new index configuration in a preset verification period, verifying the optimization effect, and storing the optimal optimization combination and the corresponding real performance indexes into a training sample library, so as to perform incremental training or fine-tuning on the models in the joint optimization candidate generating engine and the unified influence predicting engine.

[0091] Correspondingly, the present application also provides an electronic device, which comprises a computer device including a processor and a memory, the memory storing computer instructions, and the processor being used for executing the computer instructions stored in the memory, and the electronic device implements the steps of the above method when the computer instructions are executed by the processor.

[0092] The present application also provides a computer readable storage medium storing a computer program, and the computer program is executed by the processor to implement the steps of the above method. The computer readable storage medium can be a tangible storage medium, such as a random access memory (RAM), a memory, a read only memory (ROM), an electrically programmable ROM, an electrically erasable programmable ROM, a register, a floppy disk, a hard disk, a removable storage disk, a CD-ROM, or any other form of storage medium known in the technical field.

[0093] It should be understood by those skilled in the art that the various 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 to implement the system in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians may use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present invention. When implemented in hardware, it may be, for example, an electronic circuit, an application-specific integrated circuit (ASIC), appropriate firmware, a plug-in, a function card, etc. When implemented in software, the elements of the present invention are programs or code segments used to perform the required tasks. The programs or code segments can be stored in a machine-readable medium or transmitted over a transmission medium or communication link via a data signal carried in a carrier wave.

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

[0095] In the present 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 replace features of other embodiments.

[0096] The foregoing description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. Those skilled in the art will readily appreciate that various modifications and variations to the present invention are possible. Any modifications, equivalent substitutions, or improvements made within the spirit and principles of the present invention are intended to be within the scope of protection of the present invention.

Claims

1. A closed-loop optimization method for SQL and index based on verification results, characterized in that: The method comprises the following steps: 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 workload characteristics generated by executing the slow query statements corresponding to each original SQL template within a preset time period and under the original index configuration. The workload characteristics include the number of executions, execution time, and resource consumption. Inputting the original SQL template into a pre-trained large-scale language model to generate multiple candidate SQL templates that are semantically equivalent to the original SQL template to form a candidate SQL template set, wherein the large-scale language model is trained based on a SQL template training set, and the SQL template training set includes multiple samples including a SQL template to be optimized, an optimized SQL template, and an improvement in execution efficiency of the optimized SQL template compared to the SQL template to be optimized, and 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, generate multiple candidate index configurations, and constitute a candidate index configuration set; Pairing the candidate SQL template set and the candidate index configuration set to generate multiple groups of optimization combinations, extracting features of the corresponding candidate SQL template and candidate index configuration in each group of optimization combinations to obtain query features and index features; inputting the query features, the index features, the workload features, the structural features and the data statistical features of the target database into a pre-trained multimodal feature prediction model to generate prediction performance indicators for each optimization combination, wherein the data statistical features include the number of rows in the database table, the cardinality of the candidate index column, the null value ratio and the data distribution histogram; Based on the prediction performance index and the preset optimization goal, selecting the optimal optimization combination from the multiple groups of optimization combinations and deploying it to the target database; During a preset verification period, monitor the actual performance indicators generated by the slow query statements corresponding to the candidate SQL templates in the optimal optimization combination when executed under the candidate index configuration. If the actual performance indicators do not reach the benchmark performance indicators, perform a rollback operation; otherwise, retain the deployment. The benchmark performance indicators are calculated based on the performance indicators generated by the slow query statements corresponding to the original SQL templates when executed under the original index configuration. The optimal optimization combination and its corresponding real performance indicators are stored as incremental training samples in the training sample library, and 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 Obtain slow query statements in the target database that do not meet preset performance indicators, including: Monitor the slow query log of the target database and obtain the first query statement whose execution time exceeds the corresponding preset threshold; Querying the performance monitoring view of the target database to obtain a second query statement where the number of scanned rows or the number of logical reads exceeds a corresponding preset threshold; 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: Constructing the SQL template training set; Constructing an initial model, the initial model comprising an input layer, a multi-layer self-attention and feedforward network, and an output layer; the initial model takes the SQL template 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 to minimize the loss between each rewritten SQL template and the optimized SQL template, and the actual performance improvement is used as a reward signal. The policy gradient method is used to fine-tune the parameters of the initial model to ultimately obtain the large-scale language model.

4. The method according to claim 1, wherein Constructing the heterogeneous graph includes: Taking the candidate index column as a column node and the database table to which the candidate index column belongs as a table node, a node set is constructed; Construct an edge set, the edge set including a column-table affiliation edge, a column-column same-table edge, and a table-table primary and foreign key edge. The column-table affiliation edge is connected between the candidate index column and the database table to which it belongs, the column-column same-table edge is connected between two candidate index columns in the same database table, and the table-table primary and foreign key edge is from the foreign key database table to the primary key database table; wherein, the edge feature of the column-table affiliation edge is affiliation, the edge feature of the column-column same-table edge is the ratio of the number of times the corresponding two 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, and the edge features of the table-table primary and foreign key edges include the ratio of the primary key column cardinality to the foreign key column cardinality and the number of references of the corresponding primary and foreign key pairs in the original SQL template; The heterogeneous graph is constructed based on the node set and the edge set.

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 a convolutional layer, an attention layer, and a fully connected layer; wherein the convolutional layer updates the node embedding representation through a message passing mechanism, the attention layer calculates the attention weights between the column nodes based on the updated node embedding representation, and the fully connected layer weights and scores the updated node embedding representation 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 into a column node combination, and the synergy effect score of the composite index formed by the column node combination is output, where the synergy effect score is obtained based on the attention weights and the edge features of the column-column same-table edges; Select the columns whose value scores exceed the first threshold to generate a single-column index set, select the columns whose synergy effect scores exceed the second threshold to generate a composite index set, and aggregate the single-column index set and the composite index set to obtain the candidate index configuration set.

6. The method according to claim 1, characterized in that Pairing the candidate SQL template set with the candidate index configuration set to generate multiple optimization combinations, including: A 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 optimization combination within the preset verification period, the A / B verification method is used for verification, including: Deploying a first group of configurations and a second group of configurations simultaneously in the target database, wherein the first group of configurations is the original SQL template and the original index configuration, and the second group of configurations is the candidate SQL template and the candidate index configuration in the optimal optimization combination; During the preset verification cycle, the performance indicators generated by the slow query statements corresponding to the original SQL templates in the first group of configurations when executed under the original index configuration are collected in parallel to obtain the benchmark performance indicators. The real performance indicators generated by the candidate SQL templates in the second group of configurations when executed under the candidate index configuration are collected in parallel. The benchmark performance indicators and the real performance indicators are compared to obtain the verification result of the optimal optimization 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, the index features, the workload features, the structural features, and the data statistical features into an embedding representation, which is 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 indicator, which includes the execution time of the slow query statement corresponding to the candidate SQL template, the CPU usage 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 automatic deployment and semi-automatic deployment: When the automatic execution deployment is adopted, the authorization information pre-issued by the administrator terminal is received, and the following is automatically executed: replacing the original SQL template with the candidate SQL template in the optimal optimization combination, and creating or modifying the data definition language statement required for the candidate index configuration in the optimal optimization combination; When the semi-automatic execution deployment is adopted, an optimization recommendation report is generated based on the optimal optimization combination and uploaded to the administrator terminal. After receiving the confirmation execution instruction, the following is executed: replacing the original SQL template with the candidate SQL template in the optimal optimization combination, and creating or modifying the data definition language statement 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 / instruction stored in the memory, characterized in that: The processor is configured to execute the computer program / instructions. When the computer program / instructions are executed, the system implements the steps of the method according to any one of claims 1 to 9.

Images