SQL (Structured Query Language) statement auditing method, system, equipment and medium

Abstract

The invention is applicable to the technical field of computers, and provides an SQL (Structured Query Language) statement auditing method which comprises the following steps: receiving a to-be-audited SQL statement, generating an execution plan tree from the SQL statement, and extracting an operator node sequence from the execution plan tree; performing atomic feature extraction on each operator node in the operator node sequence to obtain a multi-modal feature vector; for each operator node, cost prediction and performance prediction are carried out based on the multi-modal feature vector, and a multi-modal feature portrait is constructed; performing multi-target causal analysis on the basis of the multi-modal feature portrait, and determining dominant features; on the basis of the dominant features, optimization suggestions are generated in combination with a large language model, the limitation of single-dimensional analysis is broken through, multi-objective collaborative optimization of performance and cost is achieved, the professional threshold and workload of database administrators and developers are greatly reduced, and the efficiency of SQL auditing is improved.

Description

Technical Field

[0001] This application belongs to the field of computer technology, and in particular relates to a method, system, device and medium for auditing SQL statements. Background Technology

[0002] With the rapid development of big data technology, various types of databases are widely used. Developers need to write complex SQL statements to query and analyze data. The quality of SQL statements directly affects query performance and system stability. In the cloud era and big data scenarios, cloud databases are usually paid on demand. When the data query volume is large, each line of inefficient SQL code will directly affect the corresponding operating costs. Therefore, it is necessary to review SQL statements in conjunction with costs.

[0003] With the development of AI technology, existing technologies have emerged that use AI to analyze SQL statements. However, AI is usually treated as an isolated classification or prediction module, which transforms SQL statements into flat feature vectors and inputs them into classification or regression models for prediction. Its analysis dimension is singular and lacks correlation with cost, resulting in developers having no intuitive economic incentive to optimize it.

[0004] Therefore, there is an urgent need for a technical solution that can combine SQL statements with resource consumption costs for multi-dimensional correlation analysis. Summary of the Invention

[0005] This application provides an SQL statement auditing method, system, device, and medium that can solve one of the aforementioned problems in the prior art.

[0006] Firstly, embodiments of this application provide a method for auditing SQL statements, including: Receive the SQL statement to be reviewed, generate an execution plan tree from the SQL statement, and extract the operator node sequence from the execution plan tree; Atomic feature extraction is performed on each operator node in the operator node sequence to obtain a multimodal feature vector; For each operator node, cost prediction and performance prediction are performed based on the multimodal feature vector to construct a multimodal feature profile; Based on the multimodal feature profile, perform multi-objective causal analysis to determine the dominant features; Based on the aforementioned dominant features, optimization suggestions are generated using a large language model.

[0007] Furthermore, the step of generating an execution plan tree from the SQL statement and extracting the operator node sequence from the execution plan tree includes: An execution plan tree is generated using a database query optimizer, and then the execution plan tree is traversed using an execution plan parser. Identify various operator nodes in the execution plan tree and generate an operator node sequence; In the operator node sequence, the parent-child relationship and data flow between each operator node are extracted to construct an operator dependency graph. The operator type, data flow, operator implementation algorithm and data sorting method of each operator node are recorded. The operator type includes scan operator, join operator, aggregation operator and sorting operator.

[0008] Furthermore, the step of extracting atomic features from each operator node in the operator node sequence to obtain a multimodal feature vector includes: Calculate the selection coefficient of the filtering conditions based on the column cardinality or data distribution histogram of database statistics; The data skewness coefficient is obtained by analyzing the distribution of data in the database statistics after grouping by grouping key values; Extract the parameter configuration for task parallelism from the execution plan tree to obtain the parallelism coefficient; The operator type, data flow, operator implementation algorithm, and data sorting method of each operator node are obtained. Combined with the filtering condition selection coefficient, the data skew coefficient, and the parallel coefficient, a multimodal feature vector is generated.

[0009] Furthermore, for each operator node, based on the multimodal feature vector, cost prediction and performance prediction are performed to construct a multimodal feature profile, including: The multimodal feature vectors are nonlinearly exchanged and fused using a feature interaction network to generate a fused feature representation. The fused feature representation is input into the feature-resource mapping model to predict resource consumption, thereby obtaining a resource consumption vector. Based on the resource consumption vector and combined with resource cost, resource consumption is mapped to a cost scalar. The fused feature representation is input into the performance prediction model to obtain performance metrics; A weighted concatenation mechanism is adopted to fuse the multimodal feature vector, the resource consumption vector, the cost scalar, and the performance index into a multimodal feature profile of the corresponding operator node.

[0010] Furthermore, the nonlinear exchange and feature fusion of the multimodal feature vectors through the feature interaction network to generate a fused feature representation includes: The multi-head attention mechanism of the feature interaction network is used to enhance each feature in the multimodal feature vector to generate enhanced feature representations; Based on the enhanced feature representation, the multimodal feature vectors are fused to generate a fused feature representation containing feature interaction information.

[0011] Furthermore, the step of performing multi-objective causal analysis based on the multimodal feature profile to generate dominant features includes: Construct a hierarchical cause-effect graph, which includes a technical feature layer, a resource / performance layer, and a cost layer; Based on the hierarchical causal graph, the causal contribution of each feature node in the technical feature layer to the cost scalar and performance index is calculated in parallel through a structural causal model, and a feature trade-off matrix is ​​constructed to identify the comprehensive impact of the adjustment of each feature on the performance index and cost scalar. Based on the causal contribution, performance-sensitive features and cost-sensitive features are identified, and the trade-off between the performance-sensitive features and the cost-sensitive features is analyzed to determine the performance-dominant features and cost-dominant features.

[0012] Furthermore, the step of generating optimization suggestions based on the dominant features and in conjunction with a large language model includes: Construct a structured optimization suggestion template based on the aforementioned feature trade-off matrix; Based on the structured optimization prompt template, a large language model is used to generate SQL rewriting schemes or configuration adjustment suggestions.

[0013] Secondly, embodiments of this application provide an SQL statement auditing system, including: First processing module: used to receive SQL statements to be reviewed, generate an execution plan tree from the SQL statements, and extract the operator node sequence from the execution plan tree; The second processing module is used to extract atomic features from each operator node in the operator node sequence to obtain a multimodal feature vector. The third processing module is used to perform cost prediction and performance prediction for each operator node based on the multimodal feature vector, and to construct a multimodal feature profile. The fourth processing module is used to perform multi-target causal analysis based on the multimodal feature profile to determine the dominant feature. The fifth processing module is used to generate optimization suggestions based on the dominant features and the large language model.

[0014] Thirdly, embodiments of this application provide a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the above-described SQL statement auditing method.

[0015] Fourthly, embodiments of this application provide a computer-readable storage medium, including a computer program stored in the computer-readable storage medium, wherein the computer program, when executed by a processor, implements the SQL statement auditing method described above.

[0016] The beneficial effects of the embodiments in this application compared with the prior art are: This application discloses an SQL statement auditing method. By constructing a multimodal feature profile, it combines atomic features such as filter condition selection coefficients, data skew coefficients, and parallelism coefficients with basic features such as operator type, data flow, operator implementation algorithms, and data sorting methods. These features are then correlated and integrated with performance metrics such as query latency and throughput, as well as cost. This allows developers to clearly see the performance and economic losses caused by inefficient SQL statements, greatly enhancing their motivation and sense of urgency for SQL optimization. Furthermore, it overcomes the limitations of single-dimensional analysis, achieving multi-objective collaborative optimization of performance and cost. In the process of constructing the multimodal feature profile, the method extracts operator node sequences from the execution plan tree and utilizes features... Interactive networks perform nonlinear transformations, enabling deeper mining and characterization of the complex structure and execution semantics of SQL statements. This data is then used as input to performance and cost prediction models, significantly improving the accuracy of predictions for key indicators such as query latency, system throughput, and cost, thus ensuring the reliability of subsequent analysis and optimization suggestions. Furthermore, by introducing multi-objective causal analysis and hierarchical causal graphs, the dual causal contributions of various technical features to performance and cost can be calculated in parallel. Based on this, a performance-cost feature trade-off matrix is ​​constructed for generating subsequent large language model optimization suggestions. This makes the optimization suggestions both intelligent and interpretable, greatly reducing the professional threshold and workload for database administrators and developers, and improving the efficiency of SQL review. Attached Figure Description

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

[0018] Figure 1 This is a flowchart illustrating an SQL statement auditing method according to an embodiment of the present invention; Figure 2 This is a schematic diagram of the structure of an SQL statement review system provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the structure of a computer device provided in an embodiment of the present invention. Detailed Implementation

[0019] In the following description, specific details such as particular system architectures and techniques are set forth for illustrative purposes and not for limitation, in order to provide a thorough understanding of the embodiments of this application. However, those skilled in the art will understand that this application may also be implemented in other embodiments without these specific details. In other instances, detailed descriptions of well-known systems, apparatuses, circuits, and methods have been omitted so as not to obscure the description of this application with unnecessary detail.

[0020] It should be understood that, when used in this application specification and the appended claims, the term "comprising" indicates the presence of the described features, integrals, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or a collection thereof.

[0021] It should also be understood that the term “and / or” as used in this application specification and the appended claims means any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0022] As used in this application specification and the appended claims, the term "if" may be interpreted, depending on the context, as "when," "once," "in response to determination," or "in response to detection." Similarly, the phrase "if determined" or "if detected [the described condition or event]" may be interpreted, depending on the context, as meaning "once determined," "in response to determination," "once detected [the described condition or event]," or "in response to detection [the described condition or event]."

[0023] Furthermore, in the description of this application and the appended claims, the terms "first," "second," "third," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0024] References to "one embodiment" or "some embodiments" as described in this specification mean that one or more embodiments of this application include a specific feature, structure, or characteristic described in connection with that embodiment. Therefore, the phrases "in one embodiment," "in some embodiments," "in other embodiments," "in still other embodiments," etc., appearing in different parts of this specification do not necessarily refer to the same embodiment, but rather mean "one or more, but not all, embodiments," unless otherwise specifically emphasized. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless otherwise specifically emphasized.

[0025] Please see Figure 1As shown, this invention is a method for auditing SQL statements, comprising the following steps: S100: Receive the SQL statement to be reviewed, generate an execution plan tree from the SQL statement, and extract the operator node sequence from the execution plan tree; In some embodiments, step S100 above includes: An execution plan tree is generated using a database query optimizer, and then the execution plan tree is traversed using an execution plan parser. Identify various operator nodes in the execution plan tree and generate an operator node sequence; In the operator node sequence, the parent-child relationship and data flow between each operator node are extracted to construct an operator dependency graph. The operator type, data flow, operator implementation algorithm and data sorting method of each operator node are recorded. The operator type includes scan operator, join operator, aggregation operator and sorting operator.

[0026] In this embodiment, the database query optimizer is a core component of the database management system. By analyzing the query logic structure of the SQL statement, the data distribution of the database, and the database system resources, it converts the SQL statement into an execution plan tree. Specifically, it connects to the target database via JDBC / ODBC. When a user submits a query request SQL statement to the target database, the database calls the query optimizer interface, such as PostgreSQL's EXPLAIN (FORMAT JSON) or MySQL's EXPLAIN FORMAT=JSON, to obtain structured execution plan data. This converts the SQL statement into the optimal execution path and stores it in a tree structure for easy data tracing and retrieval later.

[0027] Furthermore, operators are the basic execution units of the execution plan. Different types of operators have different resource consumption characteristics and execution logic. Therefore, identifying the various types of operators in the execution plan tree provides a foundation for subsequent feature extraction. Specifically, the execution plan parser traverses the above execution plan tree. The execution plan parser has a built-in recognizer for identifying operator types. It can start from the root node of the execution plan tree, visit each node of the execution plan tree in sequence, obtain each operator node, and form an operator node sequence.

[0028] It's worth noting that the built-in identifiers in the execution plan parser include various types of operators, such as scan operators (used to read data from tables or indexes, such as table scans, index scans, and unique index scans); join operators (used to associate data from multiple tables, such as nested loop joins, hash joins, merge joins, inner joins, and outer joins (left outer join, right outer join, and full outer join); aggregation operators (used to group data and calculate aggregate values, such as hash aggregation and sorting aggregation); and sorting operators (used to calculate the sum, average, or count of data in a column); and sorting operators (used to sort data, such as explicit sorting and implicit sorting) to meet the ordering requirements of query results. When an operator is identified, the corresponding operator type is determined based on its specific implementation and configuration parameters.

[0029] More specifically, based on the operator node sequence, the parser further analyzes the parent-child relationships and data flow between each operator node. The parent-child relationship indicates that the output of one operator node is the input of another operator node, and the data flow clarifies the direction of data transmission between operators. For example, the output data of a scan operator may serve as the input data for a join operator. Based on the extracted parent-child relationships and data flow, an operator dependency graph is constructed. This constructed operator dependency graph is a directed graph, where nodes represent operator nodes, and edges represent the dependencies and data flow between operator nodes. Through the operator dependency graph, the interrelationships between operators in the SQL statement's execution plan can be determined. Simultaneously with constructing the operator dependency graph, the parser records the operator type, data flow, operator implementation algorithm, and data sorting method for each operator node. Data flow includes the amount of input data and output data. The operator implementation algorithm is the algorithm used by the operator to implement the corresponding query process, such as using a hash table to accelerate joins. The data sorting method is the sorting method of the output data, such as sorting by index or not changing the sorting.

[0030] S200. Perform atomic feature extraction on each operator node in the operator node sequence to obtain a multimodal feature vector; In some embodiments, step S200 above includes: Calculate the selection coefficient of the filtering conditions based on the column cardinality or data distribution histogram of database statistics; The data skewness coefficient is obtained by analyzing the distribution of data in the database statistics after grouping by grouping key values; Extract the parameter configuration for task parallelism from the execution plan tree to obtain the parallelism coefficient; The operator type, data flow, operator implementation algorithm, and data sorting method of each operator node are obtained. Combined with the filtering condition selection coefficient, the data skew coefficient, and the parallel coefficient, a multimodal feature vector is generated.

[0031] In this embodiment, the database statistics of the target database are obtained. It can be understood that the database statistics are the statistical data obtained by querying the target database based on SQL statements, such as column cardinality or data distribution histogram. The column cardinality is the number of different values ​​in a column. For example, in one embodiment, the column cardinality of the gender column is 2, specifically ('M', 'F'), indicating that the values ​​M and F in the gender column appear repeatedly. Another example is that the column cardinality of the customer_id column is 100, indicating that the value in the customer_id column does not appear repeatedly 100 times. For the data distribution histogram, the column values ​​are divided into several buckets, and the number or frequency of values ​​in each bucket is counted to approximate the data distribution. For example, in one embodiment, the data distribution histogram of the age column is divided into 10 buckets, each bucket representing an age range, such as 0-10 years old, 11-20 years old, etc., and the number of rows in each age range is recorded.

[0032] Specifically, each operator node is analyzed separately to obtain the filter condition selection coefficient and the data skew coefficient. The filter condition selection coefficient is specifically the filter condition ratio coefficient of WHERE condition, JOIN condition and other conditions in the analyzed operator node, while the data skew coefficient is specifically the uniformity of data distribution after grouping by grouping key values ​​such as GROUP BY key and PARTITION key.

[0033] Furthermore, for the selection coefficient of the filtering condition, the filtering conditions such as WHERE or JOIN in the operator node are identified, and the corresponding calculation formula is selected to calculate the corresponding selection coefficient of the filtering condition according to the value of the filtering condition. Specifically, for the equality condition, that is, the result of the filtering condition is a unique value, the cardinality of the selected column is obtained, and the selection coefficient of the filtering condition is calculated. Specifically, the selection coefficient of the filtering condition = 1 / cardinality of the column. For the range condition, that is, the result of the filtering condition is a range, the selected column is generated into a corresponding data distribution histogram according to the filtering condition, and the selection coefficient of the filtering condition is calculated accordingly. Specifically, the selection coefficient of the filtering condition = number of rows that meet the condition / total number of rows. For example, in one embodiment, the filtering condition is WHERE age>30, so the age column is converted into a data distribution histogram, and the amount of data in the buckets >30 is selected to determine the number of rows that meet the condition. Then, combined with the total number of rows of age, the corresponding selection coefficient of the filtering condition is obtained. For the composite filtering condition connected by AND / OR, the conjunction / disjunction selective combination rules in probability theory are applied for comprehensive calculation.

[0034] Furthermore, for the data skew coefficient, the key values ​​such as GROUP BY or PARTITION in the operator node are identified, and the distribution of data volume in each group after grouping by the grouping key is statistically analyzed. The data skew coefficient is obtained by statistical methods such as variance, standard deviation, and Gini coefficient. For example, in one embodiment, after the data is grouped by department_id, 10 appears 5000 times, 20 appears 300 times, and 30 appears 200 times. The average value of the data volume of all groups is calculated to obtain the mean. Then, the square of the difference between each group and the mean is calculated to obtain the variance of the data group. The standard deviation is then calculated based on the variance. The ratio of the standard deviation to the mean is then calculated to obtain the data skew coefficient. It can be understood that a data skew coefficient close to 1 indicates that the data distribution is relatively uniform, while a value much greater than 1 indicates that there is serious data skew.

[0035] Specifically, taking a pending SQL statement as an example, "SELECT region, COUNT(*) FROM orders WHERE customer_id = 100 GROUP BY region;", the analysis process is explained as follows: The above SQL statement specifically queries the orders with customer_id=100, groups them by region, and counts the number of orders in each group. Specifically, the database query optimizer generates an execution plan tree, and the specific results are shown below: HashAggregate Group BY: region Output: region, COUNT(*) -> Seq Scan on orders Filter: (customer_id = 100) Here, HashAggregate and Seq Scan represent the aggregation operator and the sequential scan operator, respectively, and an operator dependency graph is constructed based on this, specifically Scan(Filter: customer_id=100) → HashAggregate(GROUPBY region). Scan is the parent node of HashAggregate, providing input data to HashAggregate. That is, data flows from Scan to HashAggregate. It can be understood that the above SQL statement includes two operator nodes, Scan and HashAggregate. The node attributes of the Scan operator node are ["scan operator", "number of rows in the whole table", "number of rows after filtering", "full table scan", "unordered"], which correspond to the operator type, input data flow, output data flow, operator implementation algorithm, and data sorting method, respectively. Similarly, the node attributes of the HashAggregate node are ["aggregation operator", "number of rows after filtering", "number of groups", "hash aggregation", "sorted by region"].

[0036] Furthermore, atomic feature extraction is performed on the Scan operator node and the HashAggregate operator node respectively. Specifically, in the Scan operator node, there is a filter condition Filter: (customer_id = 100). Based on the value of this filter condition, the filter condition selection coefficient is calculated as 1 / column cardinality. Since there is no grouping key value in the Scan operator node, the corresponding data skew coefficient is empty. Similarly, in the HashAggregate operator node, there is no filter condition, so the corresponding filter condition selection coefficient is empty. For the data skew coefficient, the frequency distribution of each value in the region column is obtained, such as region='A' appearing 10 times, region='B' appearing 20 times, etc., and the data skew coefficient is calculated accordingly.

[0037] Furthermore, based on the execution plan tree, the number of parallel tasks when executing the operator node is extracted to quantify the parallelism coefficient of each operator node. In subsequent analysis, the parallelism coefficient is used for evaluation to balance performance and cost. Specifically, the types of operators that can be parallelized are obtained. For example, operators used for data scanning can be parallelized and can synchronously scan different parts of the table. Operators used for aggregation / join can be parallelized in the case of partition operations. Specifically, in the execution plan tree, the degree of parallelism is usually marked for operands and can be directly extracted. For example, if the Scan operator node is marked as Parallel Seq Scan (workers=4), then the number of parallel tasks is 4. In some cases, the execution plan tree does not configure the corresponding degree of parallelism, which can be inferred by combining the number of partitions or the system default value. For example, assuming that the orders table is divided into 4 partitions, the number of parallel tasks of the Scan operator node may be 4.

[0038] In this embodiment, atomic features of each operator node are extracted, namely the aforementioned filter condition selection coefficient, data skew coefficient, and parallel coefficient. The filter condition selection coefficient and data skew coefficient together determine the amount of data to be processed, which in turn affects the rationality of the number of parallel tasks in the system and the selection of operator algorithms, ultimately affecting the result of resource consumption. At the same time, combined with the data characteristics of the operator itself, such as operator type, data flow, operator implementation algorithm, and data sorting method, a multimodal feature vector is generated as the data basis for subsequent analysis, providing multidimensional perspectives for analysis and ensuring the reliability of the analysis.

[0039] S300. For each operator node, based on the multimodal feature vector, perform cost prediction and performance prediction to construct a multimodal feature profile. In this application, by constructing a multimodal feature profile, atomic features such as filter condition selection coefficients, data skew coefficients, and parallel coefficients are combined with basic features such as operator type, data flow, operator implementation algorithm, and data sorting method. These features are then correlated and integrated with performance indicators such as query latency and throughput, as well as cost. This allows developers to clearly see the performance and economic losses caused by inefficient SQL statements, greatly enhancing the intuitive motivation and urgency for SQL optimization. At the same time, it breaks through the limitations of single-dimensional analysis and achieves multi-objective collaborative optimization of performance and cost.

[0040] In some embodiments, step S300 above includes: The multimodal feature vectors are nonlinearly exchanged and fused using a feature interaction network to generate a fused feature representation. The fused feature representation is input into the feature-resource mapping model to predict resource consumption, thereby obtaining a resource consumption vector. Based on the resource consumption vector and combined with resource cost, resource consumption is mapped to a cost scalar. The fused feature representation is input into the performance prediction model to obtain performance metrics; A weighted concatenation mechanism is adopted to fuse the multimodal feature vector, the resource consumption vector, the cost scalar, and the performance index into a multimodal feature profile of the corresponding operator node.

[0041] In this embodiment, a feature interaction network is used to perform nonlinear exchanges on the features of the multimodal feature vector to generate a fused feature representation. This captures the complex interaction relationships between different features in the multimodal feature vector, which are then used for the subsequent prediction and generation of cost scalars and performance indicators, thereby improving the confidence of the prediction.

[0042] In this embodiment, the fused feature representation is input into the feature-resource mapping model, which has a multi-layer perception layer and a cost mapping layer. The multi-layer perception layer maps the fused feature representation into resource consumption vectors of CPU cores per second, memory GB per second, and network transmission GB. The cost mapping layer connects to the cloud resource pricing API, which can obtain the unit price of CPU, memory, and network resources in the current environment in real time. Combined with the predicted amount of resource consumption, the corresponding cost scalar is obtained, enabling developers and operations personnel to intuitively understand the financial impact of different SQL writing methods and different parameter configurations, greatly enhancing the motivation and pertinence of cost optimization.

[0043] Specifically, the multilayer perceptron is a multilayer perceptron model, which typically contains 2-3 hidden layers. It uses the ReLU activation function to introduce non-linear features and employs linear activation in the output layer to generate a three-dimensional resource consumption vector: [CPU cores - seconds, memory GB - seconds, network transmission GB]. In essence, the multilayer perceptron model is trained under supervision by converting a large amount of historical SQL statement execution data into training data that represents the correspondence between specific features and actual resource consumption. This allows it to learn a complex mapping function from a complex augmented feature space to a multidimensional resource consumption space. Furthermore, the neurons in each hidden layer of the multilayer perceptron model can learn feature combination patterns at different levels of abstraction, thus achieving the prediction from abstract features to physical resource consumption.

[0044] In one embodiment, the performance prediction model is a multi-task learning neural network to quantitatively evaluate the execution performance of SQL statements. It can predict multiple key performance indicators in parallel. Specifically, the multi-task learning neural network adopts a multi-task learning architecture with a shared bottom layer and independent output heads. It includes a shared feature encoding layer and independent prediction sub-networks. The above-mentioned fused feature representation is input into the shared feature encoding layer to extract general feature patterns related to performance. Then, the general feature patterns are input into the independent prediction sub-networks for task prediction. The independent prediction sub-networks are specifically two independent sub-networks, which are responsible for predicting query latency and system throughput, respectively. Each sub-network is a shallow multilayer perceptron used to output the corresponding prediction task output value.

[0045] Specifically, in the query latency prediction subnetwork, its input dimension includes not only the output of the shared feature encoding layer, but also the operator dependency graph. By combining the parent-child relationship and data flow structure information in the operator dependency graph, the critical path in the execution plan can be identified, which is beneficial to the prediction of the total latency. During training, mean squared logarithmic error (MSLE) is used as the loss function to treat latency prediction errors of different orders of magnitude equally, and finally outputs a continuous value representing the total execution time.

[0046] More specifically, in the system throughput prediction sub-network, the output of the shared feature encoding layer is used as the input dimension. Its network structure can capture parallelism features and system resource boundaries, such as the number of CPU cores and disk I / O bandwidth, and then learn the maximum data processing rate that the execution plan can achieve under given resource constraints. Finally, the output layer outputs a predicted continuous value representing the number of rows or the amount of data processed per second (MB / s).

[0047] Furthermore, multimodal feature vectors, resource consumption vectors, cost scalars, and performance indicators are fused into a multimodal feature profile, with one operator node corresponding to one multimodal feature profile. This multimodal feature profile integrates data features from multiple dimensions, and the corresponding operator nodes are analyzed based on the multimodal feature profile to improve the reliability of causal analysis.

[0048] In some embodiments, the nonlinear exchange of the multimodal feature vectors through the feature interaction network to generate a fused feature representation includes: In the process of constructing multimodal feature profiles, this application extracts operator node sequences from the execution plan tree and uses a feature interaction network for nonlinear transformation. This enables a deeper understanding and representation of the complex structure and execution semantics of SQL statements, which are then used as input to a performance and cost prediction model. This significantly improves the accuracy of predictions for key indicators such as query latency, system throughput, and cost, thereby ensuring the reliability of subsequent analysis and optimization suggestions.

[0049] The multi-head attention mechanism of the feature interaction network is used to enhance each feature in the multimodal feature vector to generate enhanced feature representations; Based on the enhanced feature representation, the multimodal feature vectors are fused to generate a fused feature representation containing feature interaction information.

[0050] In this embodiment, a multi-head self-attention layer is constructed in the feature interaction network to perform nonlinear exchanges on atomic features. Specifically, the input of the multi-head self-attention layer is a feature matrix composed of multimodal feature vectors. By fusing and enhancing based on attention weights, an enhanced feature representation is generated, which has high information density and good interpretability. The enhanced feature representation is then fused with the original multimodal feature vectors to generate a fused feature representation, which is used for subsequent cost and performance prediction to improve prediction confidence.

[0051] Specifically, based on operator type, data flow, operator implementation algorithm, data sorting method, and filtering condition selection coefficients, data skew coefficients, and parallelism coefficients, the input feature matrix X∈R is constructed. n×d Where n represents the number of operator nodes, and d represents the feature dimension of each operator node, namely, operator type, data flow, operator implementation algorithm, data sorting method, filter condition selection coefficient, data skew coefficient, and parallel coefficient, etc.

[0052] Furthermore, three learnable weight matrices W are defined. Q ∈R d×d_k W K ∈R d×d_k and W V ∈R d×d_v Where d_k and d_v are the dimensions of the key vector and value vector, respectively. For each feature vector x_i (i=1,2,...,n) in the input feature matrix X, the query vector q_i, key vector k_i, and value vector v_i are generated through the following linear transformation: specifically, q_i = x_i × W Q k_i = x_i × W K v_i = x_i × W V Thus, the query matrix Q=[q_1,q_2,...,q_n] is obtained. T ∈R n×d_k The bond matrix K = [k_1, k_2, ..., k_n] T ∈R n×d_k Sum matrix V = [v_1, v_2, ..., v_n] T ∈R n×d_v .

[0053] Furthermore, the query matrix Q, key matrix K, and value matrix V are segmented along the feature dimension, thereby dividing the corresponding matrix into multiple heads, and attention scores are calculated independently. Specifically, for the query matrix Q, it is segmented into h submatrices Q1, Q2, ..., Qh, where Q_j∈R n×d_k / h (j=1,2,...,h), similarly, the key matrix K and value matrix V are also partitioned into K1,K2,...,Kh and V1,V2,...,Vh, respectively, where K_j∈R n×d_k / h V_j∈R n×d_v / h Then, for each head j, the attention score matrix A_j is calculated, and ,in, This is used to calculate the similarity matrix between the query vector and the key vector, and its size is n×n. This is a scaling factor used to prevent the value after the dot product from becoming too large, which could cause the gradient of the softmax function to vanish. The softmax function converts each element in the similarity matrix into a probability value, representing the weight of the value vector corresponding to each key vector when considering the current query vector. Then, the attention score matrix A_j is used to perform a weighted summation on the value matrix V_j to obtain the output O_j of each head. Specifically, O_j = A_j × V_j, O_j ∈ R. n×d_v / h Then, the outputs O1, O2, ..., Oh of all the heads are concatenated along the feature dimension to obtain the concatenated matrix O = [O1, O2, ..., Oh] ∈ R. n×d_v Finally, the concatenated matrix O is mapped to the desired output dimension through a learnable linear projection layer. Understandably, this concatenated matrix O is a value vector transformed by a multi-head attention mechanism. For each operator node, the corresponding value vector contains enhanced feature representations after nonlinear interaction and fusion of features such as operator type, data flow, operator implementation algorithm, data sorting method, filtering condition selection coefficient, data skew coefficient, and parallelism coefficient. Thus, the multi-head self-attention layer can capture the complex interaction relationships between different features in the multimodal feature vector. Furthermore, the original multimodal feature vector is fused with the enhanced feature representation transformed by the attention mechanism, so that the final output fused feature representation contains both the independent information of the original multimodal feature vectors and the complex interaction information between them. This fused feature representation is then used for subsequent prediction of cost scalars and performance indicators, improving the prediction confidence.

[0054] S400. Based on the multimodal feature profile, perform multi-target causal analysis to determine the dominant feature; This application introduces multi-objective causal analysis and hierarchical causal graphs to calculate the dual causal contribution of each technical feature to performance and cost in parallel. Based on this, a performance-cost feature trade-off matrix is ​​constructed for the generation of subsequent large language model optimization suggestions, making the optimization suggestions both intelligent and interpretable. This greatly reduces the professional threshold and workload of database administrators and developers, and improves the efficiency of SQL auditing.

[0055] In some embodiments, step S400 above includes: Construct a hierarchical cause-effect graph, which includes a technical feature layer, a resource / performance layer, and a cost layer; Based on the hierarchical causal graph, the causal contribution of each feature in the technical feature layer to the cost scalar and performance index is calculated in parallel through a structural causal model, and a feature trade-off matrix is ​​constructed to identify the comprehensive impact of the adjustment of each feature on the performance index and cost scalar. Based on the causal contribution, performance-sensitive features and cost-sensitive features are identified, and the trade-off between the performance-sensitive features and the cost-sensitive features is analyzed to determine the performance-dominant features and cost-dominant features.

[0056] In this embodiment, a hierarchical causal graph is constructed as the basic model structure for causal analysis. This hierarchical causal graph is a three-level directed acyclic graph, including a root node layer, an intermediate node layer, and a leaf node layer. The root node layer is the technical feature layer, including atomic feature nodes and basic feature nodes. The atomic feature nodes specifically include filter condition selection coefficients, data skew coefficients, and parallel coefficients. The basic feature nodes specifically include operator types, data flow, operator implementation algorithms, and data sorting methods. The intermediate node layer is the resource / performance layer, including performance indicator nodes and resource consumption nodes. The performance indicator nodes are the predicted values ​​of each performance indicator in the multimodal feature profile, specifically query latency and system throughput. The resource consumption nodes are the resource consumption vectors in the multimodal feature profile, specifically CPU cores per second, memory GB per second, and network GB. The leaf node layer is the cost layer, specifically the cost scalars in the multimodal feature profile.

[0057] Furthermore, in the hierarchical causal graph, directed edges represent causal paths, such as filter selection coefficient -> query latency, data skew coefficient -> memory consumption, CPU consumption -> total cost, etc. It is worth noting that the existence of edges is predefined based on database domain knowledge, and the strength of their causal effects is estimated by data-driven methods. Through the above hierarchical structure, a complete causal chain from features to performance / resources, and finally to cost, is realized, decomposing complex behavioral relationships into a series of interpretable local causal relationships, which is beneficial for data analysis and inference.

[0058] Furthermore, on the hierarchical causal graph defined above, a structural causal model is used for quantitative analysis. For each variable in the hierarchical causal graph, a structural equation is used to represent it: Y = f(Pa(Y), ε), where Pa(Y) is the set of parent nodes of Y in the causal graph, ε is unobserved noise, and based on the technical characteristics, resources / performance and costs in historical data, the function f in each structural equation is fitted by machine learning methods.

[0059] Furthermore, the Shapley value or average causal effect method is used to calculate the causal contribution. Specifically, for a certain technical feature node, such as the data skew coefficient, the do-operator is used to intervene and simulate the expected value changes of its performance node and cost node when it is fixed at different values, thereby obtaining the corresponding causal contribution.

[0060] Furthermore, for atomic or basic feature nodes in the technical feature layer, variable technical features are selected, such as operator implementation algorithms and data sorting methods in basic feature nodes, and each atomic feature node. Their causal contribution to performance indicators and cost scalars is obtained, resulting in corresponding performance sensitivity and cost sensitivity. If performance sensitivity > cost sensitivity, and performance sensitivity exceeds a preset threshold, then the feature is considered performance-sensitive and is identified as a performance-dominant feature. If cost sensitivity > performance sensitivity, and cost sensitivity exceeds a preset threshold, then the feature is considered cost-sensitive and is identified as a cost-dominant feature. If both cost-sensitive and performance-sensitive features exceed the preset thresholds, then the feature is considered dual-sensitive and requires a trade-off analysis. Specifically, the trade-off ratio of the feature is calculated as: Trade-off Ratio = Performance Sensitivity / Cost Sensitivity. If the trade-off ratio > 1, it indicates that the benefit of performance optimization outweighs the pressure of increased cost; conversely, the opposite is also true. This determines the corresponding performance-dominant and cost-dominant features.

[0061] Furthermore, a feature tradeoff matrix is ​​constructed to quantify the comprehensive impact of adjusting each feature on system performance and economic costs. This feature tradeoff matrix is ​​a two-dimensional data structure M. Its rows represent the features to be analyzed, such as operator implementation algorithms and data sorting methods, as well as selection coefficients for filtering conditions, data skew coefficients, and parallelism coefficients. Its columns represent optimization objectives, including performance and cost objectives. The performance objectives are specifically query latency L and system throughput T, while the cost objective is specifically the cost scalar C. Each cell M[i,j] in this matrix stores a tradeoff tuple in the format (ΔImpact, Direction, Confidence). Specifically, ΔImpact is the impact increment, representing the quantified impact on the j-th target indicator when the i-th feature is adjusted in a favorable direction. Direction is the direction of influence, specifically an enumerated value {+1, -1, ..., ...}. 0} represents the positive, negative, or negligible impact of the adjustment of the feature on the target indicator, respectively. Confidence represents the confidence level, a value between 0 and 1 calculated based on historical data and statistical tests, indicating the reliability of the impact relationship. This organizes the complex causal relationship into a visual matrix for the generation of subsequent optimization suggestions.

[0062] In some embodiments, traffic light colors are used to indicate the direction of influence. For example, green indicates that adjusting the feature can improve the target metric, such as reducing latency or cost; red indicates that it will worsen the target metric; and yellow indicates that the influence is weak, which facilitates the subsequent identification of the feature trade-off matrix by the large language model.

[0063] S500. Based on the dominant features, generate optimization suggestions in conjunction with the large language model.

[0064] In some embodiments, step S500 above includes: Construct a structured optimization suggestion template based on the aforementioned feature trade-off matrix; Based on the structured optimization prompt template, a large language model is used to generate SQL rewriting schemes or configuration adjustment suggestions.

[0065] In this embodiment, a structured prompt template is generated based on the feature trade-off matrix and used as input to the large language model. This template clarifies the role of the large language model and extracts the direction and degree of influence of each feature on performance and cost metrics from the trade-off matrix. This information is organized into a structured text prompt, including key performance bottlenecks and cost drivers identified in the current SQL execution plan, the current value and adjustable range of each technical feature, the specific impact of adjusting that feature on performance and cost, and features with trade-offs, i.e., situations where adjusting a feature results in opposite directions of performance and cost change. The structured prompt is then input into the large language model. This large language model is fine-tuned and capable of understanding database optimization domain knowledge. Finally, based on the information in the prompt, the large language model analyzes the advantages and disadvantages of adjusting each feature and comprehensively considers performance and cost to generate multiple optimization schemes. These schemes include technical adjustment measures, such as rewriting SQL statements to improve filter condition selection coefficients, data skew coefficients, or resetting the parallel coefficients of task parallelism, and predicting the changes in performance and cost after the adjustments.

[0066] Please see Figure 2 As shown, the present invention also provides an SQL statement auditing system, the system comprising: First processing module 201: used to receive SQL statements to be reviewed, generate an execution plan tree from the SQL statements, and extract operator node sequences from the execution plan tree; The second processing module 202 is used to perform atomic feature extraction on each operator node in the operator node sequence to obtain a multimodal feature vector; The third processing module 203 is used to perform cost prediction and performance prediction for each operator node based on the multimodal feature vector, and to construct a multimodal feature profile. The fourth processing module 204 is used to perform multi-target causal analysis based on the multimodal feature profile to determine the dominant feature; Fifth processing module 205: used to generate optimization suggestions based on the dominant features and in combination with the large language model.

[0067] It is understandable that, such as Figure 1 The content of the SQL statement auditing method embodiments shown are all applicable to this SQL statement auditing system embodiment. The specific functions implemented by this SQL statement auditing system embodiment are the same as those shown below. Figure 1 The SQL statement auditing method shown in the example is the same, and the beneficial effects achieved are the same as those shown. Figure 1 The beneficial effects achieved by the SQL statement auditing method embodiment shown are also the same.

[0068] It should be noted that the information interaction and execution process between the above systems are based on the same concept as the method embodiments of the present invention. For details on their specific functions and technical effects, please refer to the method embodiments section, which will not be repeated here.

[0069] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is merely an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the system can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiments can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit. Furthermore, the specific names of the functional units and modules are only for easy differentiation and are not intended to limit the scope of protection of this application. The specific working process of the units and modules in the above system can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0070] Please see Figure 3 As shown, this embodiment of the invention also provides a computer device 3, including: a memory 302 and a processor 301, and a computer program 303 stored on the memory 302. When the computer program 303 is executed on the processor 301, it implements the SQL statement auditing method as described in any of the above methods.

[0071] The computer device 3 may be a desktop computer, laptop, handheld computer, or cloud server, etc. The computer device 3 may include, but is not limited to, a processor 301 and a memory 302. Those skilled in the art will understand that... Figure 3 The computer device 3 is merely an example and does not constitute a limitation on the computer device 3. It may include more or fewer components than shown in the figure, or combine certain components, or different components, such as input / output devices, network access devices, etc.

[0072] The processor 301 may be a Central Processing Unit (CPU), or it may be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or any conventional processor.

[0073] In some embodiments, the memory 302 may be an internal storage unit of the computer device 3, such as a hard disk or memory of the computer device 3. In other embodiments, the memory 302 may be an external storage device of the computer device 3, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the computer device 3. Furthermore, the memory 302 may include both internal and external storage units of the computer device 3. The memory 302 is used to store the operating system, applications, bootloader, data, and other programs, such as the program code of the computer program. The memory 302 can also be used to temporarily store data that has been output or will be output.

[0074] This invention also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the SQL statement auditing method as described in any of the above methods.

[0075] In this embodiment, if the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include at least: any entity or device capable of carrying computer program code to a photographic device / computer device, a recording medium, a computer memory, a read-only memory (ROM), a random access memory (RAM), an electrical carrier signal, a telecommunication signal, and a software distribution medium. Examples include USB flash drives, portable hard drives, magnetic disks, or optical disks. In some jurisdictions, according to legislation and patent practice, computer-readable media cannot be electrical carrier signals or telecommunication signals.

[0076] The above-described embodiments are only used to illustrate the technical solutions of this application, and are not intended to limit them. Although this application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application, and should all be included within the protection scope of this application.

Claims

1. A method for auditing SQL statements, characterized in that, include: Receive the SQL statement to be reviewed, generate an execution plan tree from the SQL statement, and extract the operator node sequence from the execution plan tree; Atomic feature extraction is performed on each operator node in the operator node sequence to obtain a multimodal feature vector; For each operator node, cost prediction and performance prediction are performed based on the multimodal feature vector to construct a multimodal feature profile; Based on the multimodal feature profile, perform multi-objective causal analysis to determine the dominant features; Based on the aforementioned dominant features, optimization suggestions are generated using a large language model.

2. The method as described in claim 1, characterized in that, The step of generating an execution plan tree from the SQL statement and extracting the operator node sequence from the execution plan tree includes: An execution plan tree is generated using a database query optimizer, and then the execution plan tree is traversed using an execution plan parser. Identify various operator nodes in the execution plan tree and generate an operator node sequence; In the operator node sequence, the parent-child relationship and data flow between each operator node are extracted to construct an operator dependency graph. The operator type, data flow, operator implementation algorithm and data sorting method of each operator node are recorded. The operator type includes scan operator, join operator, aggregation operator and sorting operator.

3. The method as described in claim 1, characterized in that, The step of extracting atomic features from each operator node in the operator node sequence to obtain a multimodal feature vector includes: Calculate the selection coefficient of the filtering conditions based on the column cardinality or data distribution histogram of database statistics; The data skewness coefficient is obtained by analyzing the distribution of data in the database statistics after grouping by grouping key values; Extract the parameter configuration for task parallelism from the execution plan tree to obtain the parallelism coefficient; The operator type, data flow, operator implementation algorithm, and data sorting method of each operator node are obtained. Combined with the filtering condition selection coefficient, the data skew coefficient, and the parallel coefficient, a multimodal feature vector is generated.

4. The method as described in claim 1, characterized in that, For each operator node, based on the multimodal feature vector, cost prediction and performance prediction are performed to construct a multimodal feature profile, including: The multimodal feature vectors are nonlinearly exchanged and fused using a feature interaction network to generate a fused feature representation. The fused feature representation is input into the feature-resource mapping model to predict resource consumption, thereby obtaining a resource consumption vector. Based on the resource consumption vector and combined with resource cost, resource consumption is mapped to a cost scalar. The fused feature representation is input into the performance prediction model to obtain performance metrics; A weighted concatenation mechanism is adopted to fuse the multimodal feature vector, the resource consumption vector, the cost scalar, and the performance index into a multimodal feature profile of the corresponding operator node.

5. The method as described in claim 4, characterized in that, The process of nonlinearly exchanging and fusing the multimodal feature vectors through a feature interaction network to generate a fused feature representation includes: The multi-head attention mechanism of the feature interaction network is used to enhance each feature in the multimodal feature vector to generate enhanced feature representations; Based on the enhanced feature representation, the multimodal feature vectors are fused to generate a fused feature representation containing feature interaction information.

6. The method as described in claim 1, characterized in that, The process of performing multi-objective causal analysis based on the multimodal feature profile to generate dominant features includes: Construct a hierarchical cause-effect graph, which includes a technical feature layer, a resource / performance layer, and a cost layer; Based on the hierarchical causal graph, the causal contribution of each feature node in the technical feature layer to the cost scalar and performance index is calculated in parallel through a structural causal model, and a feature trade-off matrix is ​​constructed to identify the comprehensive impact of the adjustment of each feature on the performance index and cost scalar. Based on the causal contribution, performance-sensitive features and cost-sensitive features are identified, and the trade-off between the performance-sensitive features and the cost-sensitive features is analyzed to determine the performance-dominant features and cost-dominant features.

7. The method as described in claim 6, characterized in that, The process of generating optimization suggestions based on the dominant features and in conjunction with a large language model includes: Construct a structured optimization suggestion template based on the aforementioned feature trade-off matrix; Based on the structured optimization prompt template, a large language model is used to generate SQL rewriting schemes or configuration adjustment suggestions.

8. An SQL statement auditing system, characterized in that, include: First processing module: used to receive SQL statements to be reviewed, generate an execution plan tree from the SQL statements, and extract the operator node sequence from the execution plan tree; The second processing module is used to extract atomic features from each operator node in the operator node sequence to obtain a multimodal feature vector. The third processing module is used to perform cost prediction and performance prediction for each operator node based on the multimodal feature vector, and to construct a multimodal feature profile. The fourth processing module is used to perform multi-target causal analysis based on the multimodal feature profile to determine the dominant feature. The fifth processing module is used to generate optimization suggestions based on the dominant features and the large language model.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method as described in any one of claims 1 to 7.

10. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the method as described in any one of claims 1 to 7.

Images