Database slow SQL prediction analysis method and device, equipment and medium

Abstract

The application relates to the technical field of deep learning, and discloses a database slow SQL prediction analysis method, device, equipment and medium. The method comprises the following steps: acquiring SQL statements and database environment data associated with the SQL statements in a real-time manner during database operation; performing feature extraction based on the SQL statements and the database environment data to generate a feature vector; performing slow SQL prediction on the feature vector by using a pre-trained slow SQL prediction model to generate a slow SQL probability corresponding to the SQL statements and a predicted execution time consumption; determining whether the SQL statements are potential slow SQL according to the slow SQL probability and the predicted execution time consumption; and when the SQL statements are determined to be potential slow SQL, determining a root cause category causing the SQL statements to be potential slow SQL based on an attention weight distribution generated when the pre-trained slow SQL prediction model processes the feature vector.

Description

Technical Field

[0001] This invention relates to the field of deep learning technology, and in particular to a method, apparatus, device, and medium for predictive analysis of slow SQL queries in databases. Background Technology

[0002] As the core of data storage and processing in various business systems, the performance of databases directly affects business response efficiency and system stability. Slow SQL (SQL statements whose execution time exceeds a reasonable threshold) is a major cause of database performance bottlenecks, potentially leading to query delays, transaction blocking, connection exhaustion, and even system crashes in severe cases. Therefore, efficient and accurate analysis of slow SQL is crucial. Currently, the analysis of slow SQL mainly relies on three traditional methods: The first type is manual troubleshooting. This method relies entirely on the personal experience of database administrators, requiring manual review of SQL execution plans and database logs line by line. In high-concurrency scenarios, manual troubleshooting is extremely inefficient; processing tens of thousands of SQL queries daily can lead to analysis delays exceeding 24 hours. Furthermore, this method has limited analytical capabilities for nested queries and complex joins, often resulting in a false negative rate exceeding 40%, and lacks standardized processes, making it difficult to scale.

[0003] The second type is the rule engine method. This method uses a set of pre-defined static rules (such as "execution time greater than 5 seconds" or "full table scan occurred") to match and identify slow SQL queries. While it achieves a certain degree of automation, its rules are fixed and difficult to adjust dynamically, making it unable to effectively adapt to complex and ever-changing query logic, dynamically changing data volumes, and scenarios involving index structure changes. This results in a false positive rate of over 30% in complex query scenarios, and the maintenance cost of the rule base increases exponentially with the increase in business complexity.

[0004] The third category is statistical analysis tools (such as SQL Profiler built into the database). These tools are mainly used to collect and statistically analyze surface-level metrics of SQL execution, such as execution frequency and average execution time. However, their analytical depth is insufficient, lacking the ability to correlate the structural characteristics of the SQL statement itself with the underlying database operating environment (such as index validity, table structure, data skew, and system load). Therefore, this method struggles to pinpoint the root causes of performance problems such as "index failure" and "redundant join logic," and its diagnostic accuracy is typically less than 20%, failing to provide direct and effective decision-making support for performance optimization.

[0005] In summary, existing slow SQL analysis methods are generally passive in their process (relying on manual experience or static rules) and limited in performance, exhibiting inefficiencies, poor adaptability to various scenarios, and shallow analytical dimensions. They struggle to meet the demands of modern business scenarios with high concurrency and complex, ever-changing environments for real-time, accurate, and in-depth analysis of slow SQL, and also fail to provide proactive warnings of performance risks. Therefore, a more intelligent, adaptive, and in-depth slow SQL analysis solution is urgently needed. Summary of the Invention

[0006] The technical problem to be solved by the present invention is to provide a method, apparatus, device and medium for predictive analysis of slow SQL queries in databases, so as to solve the above-mentioned technical problem.

[0007] The technical solution of this invention to solve the above-mentioned technical problems is as follows: A method for predicting and analyzing slow SQL statements in a database, comprising: acquiring SQL statements and database environment data associated with the SQL statements in real time during database operation; extracting features based on the SQL statements and the database environment data to generate feature vectors; predicting slow SQL statements using a pre-trained slow SQL prediction model based on the feature vectors to generate the slow SQL probability and predicted execution time corresponding to the SQL statement; determining whether the SQL statement is a potential slow SQL statement based on the slow SQL probability and the predicted execution time; when the SQL statement is determined to be a potential slow SQL statement, determining the root cause category that causes the SQL statement to be a potential slow SQL statement based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing the feature vectors.

[0008] The beneficial effects of this invention are as follows: By acquiring SQL statements and their associated database environment data, and utilizing a pre-trained model to predict slow SQL statements and execution time, this invention significantly improves the accuracy and efficiency of slow SQL identification and effectively reduces false positives. Furthermore, it locates the root cause of performance issues based on the attention weight distribution generated during model processing, overcoming the shortcomings of traditional methods in diagnosing the causes of slow SQL statements and providing support for the continuous stability of database performance.

[0009] Based on the above technical solution, the present invention can be further improved as follows.

[0010] Further, the step of extracting features based on the SQL statement and the database environment data to generate a feature vector includes: preprocessing the SQL statement and the database environment data respectively to obtain preprocessed SQL statement and preprocessed database environment data; extracting features based on the preprocessed SQL statement to obtain SQL text features, wherein the SQL text features are features reflecting the semantic and syntactic structure of the SQL statement; extracting features based on the preprocessed database environment data to obtain environment-related features, wherein the environment-related features are features reflecting the database running status and table structure attributes; and fusing the SQL text features and the environment-related features to obtain the feature vector.

[0011] Furthermore, the step of preprocessing the SQL statement and the database environment data to obtain preprocessed SQL statement and preprocessed database environment data includes: performing data cleaning and data normalization on the SQL statement to obtain preprocessed SQL statement; and performing normalization on the database environment data to obtain preprocessed database environment data.

[0012] Furthermore, the pre-trained slow SQL prediction model includes a CNN layer, a BiLSTM layer, an Attention layer, an activation function layer, and a linear layer. The step of predicting slow SQL statements and generating the slow SQL probability and prediction execution time corresponding to the SQL statement using the pre-trained slow SQL prediction model based on the feature vector includes: extracting local features from the feature vector using the CNN layer to obtain a local feature representation; capturing long-distance dependencies in the SQL statement using the BiLSTM layer based on the local feature representation to obtain a sequence feature representation; calculating attention weights using the Attention layer to strengthen attention to the target features based on the sequence feature representation to obtain a weighted feature representation; and generating the slow SQL probability using the activation function layer and the prediction execution time using the linear layer based on the weighted feature representation.

[0013] Furthermore, determining whether the SQL statement is a potential slow SQL statement based on the slow SQL probability and the predicted execution time includes: comparing the slow SQL probability with a preset probability threshold and comparing the predicted execution time with a preset execution time threshold; when the slow SQL probability is greater than the preset probability threshold or the predicted execution time is greater than the preset execution time threshold, the SQL statement is determined to be a potential slow SQL statement.

[0014] Furthermore, the determination of the root cause category of the SQL statement as a potential slow SQL based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing the feature vector includes: identifying influencing features based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing the feature vector, wherein the influencing features are features whose corresponding weights are greater than a preset weight threshold; and determining the root cause category of the SQL statement as a potential slow SQL based on the influencing features and the preset feature-root cause category mapping relationship.

[0015] Furthermore, the method also includes: periodically collecting SQL statements identified as potentially slow SQL statements and the actual execution performance data of each SQL statement identified as potentially slow SQL statement; adjusting the parameters of the pre-trained slow SQL prediction model based on each SQL statement identified as potentially slow SQL statement and each actual execution performance data, so as to update the pre-trained slow SQL prediction model.

[0016] To address the aforementioned technical problems, the present invention also provides a database slow SQL prediction and analysis device, comprising: The data acquisition module is used to acquire SQL statements and associated database environment data during database operation in real time. The feature extraction module is used to extract features based on the SQL statement and the database environment data, and generate feature vectors; The predictive analysis module is used to predict slow SQL statements based on the feature vectors using a pre-trained slow SQL prediction model, and to generate the slow SQL probability and predicted execution time corresponding to the SQL statement. The risk identification module is used to determine whether the SQL statement is a potential slow SQL statement based on the slow SQL probability and the predicted execution time. The root cause analysis module is used to determine the root cause category that makes the SQL statement a potential slow SQL statement based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing the feature vector.

[0017] To address the aforementioned technical problems, the present invention also provides an electronic 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 database slow SQL predictive analysis method as described above.

[0018] To address the aforementioned technical problems, the present invention also provides a non-transitory computer-readable storage medium storing computer instructions for causing a computer to execute the database slow SQL predictive analysis method as described above. Attached Figure Description

[0019] Figure 1 This is a flowchart of the slow SQL prediction and analysis method for databases according to the present invention; Figure 2 This is a schematic diagram of the slow SQL prediction and analysis device for the database of the present invention; Figure 3 This is a schematic diagram of the electronic device of the present invention. Detailed Implementation

[0020] The principles and features of the present invention are described below. The examples given are only for explaining the present invention and are not intended to limit the scope of the present invention.

[0021] Addressing the core shortcomings of existing slow SQL analysis methods in terms of technology and performance, this invention has a clear and targeted objective, as detailed below: Addressing the shortcomings of manual screening methods, such as reliance on human experience, low processing efficiency, and high false negative rate, this approach aims to automate the analysis of massive amounts of SQL statements, eliminating manual intervention, reducing analysis response time from hours to seconds, and lowering the false negative rate in complex nested query scenarios, thus solving the problem of high-concurrency SQL analysis that cannot be handled manually.

[0022] Addressing the shortcomings of rule-based engines, such as "fixed rules, poor adaptability, and a false positive rate exceeding 30%", this approach aims to dynamically learn SQL features and business scenario changes through deep learning. It eliminates the need for manual maintenance of fixed rules, adapts to complex query logic and data volume fluctuation scenarios, reduces the false positive rate of slow SQL to below 5%, and solves the problem of insufficient adaptability of rule engines.

[0023] Addressing the shortcomings of statistical tools that "only count surface-level indicators and have a root cause diagnosis accuracy of less than 20%", this approach aims to delve deeper into underlying database relationships (such as table structure, index status, and join logic). It not only counts SQL execution data but also accurately locates core causes such as "index failure" and "logical redundancy", improving the root cause diagnosis accuracy to over 90% and solving the problem that tools cannot support targeted optimization.

[0024] Addressing the common shortcomings of the three types of methods—"passive response and lack of early warning capability"—this approach aims to build a slow SQL prediction model to identify potential performance risk SQL in advance, rather than simply analyzing slow SQL that has already occurred. This will enable proactive early warning of database performance risks and prevent business interruptions or losses caused by slow SQL.

[0025] Example 1 Based on this, such as Figure 1 As shown, this embodiment provides a method for predictive analysis of slow SQL queries in a database, including: S101. Real-time acquisition of SQL statements and associated database environment data during database operation.

[0026] Specifically, it parses and captures SQL statements (including complete text and parameter values) in execution based on database protocols (such as MySQL's Binary Protocol and PostgreSQL's Frontend / Backend Protocol).

[0027] By collecting database server hardware metrics (CPU utilization, memory usage, IO wait time), table structure information (field types, index distribution), and execution plan snapshots (scanning method, join type), database environment data can be obtained.

[0028] A circular buffer is used to temporarily store the collected data (supporting high-concurrency scenarios with 100,000 SQL queries per second) to avoid data loss.

[0029] S102. Based on SQL statements and database environment data, feature extraction is performed to generate feature vectors.

[0030] S103. Based on the feature vector, perform slow SQL prediction using a pre-trained slow SQL prediction model to generate the slow SQL probability and prediction execution time corresponding to the SQL statement.

[0031] S104. Based on the probability of slow SQL and the predicted execution time, determine whether the SQL statement is a potential slow SQL.

[0032] S105. When an SQL statement is determined to be a potential slow SQL statement, the root cause category that causes the SQL statement to be a potential slow SQL statement is determined based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing feature vectors.

[0033] This method significantly improves the accuracy and efficiency of slow SQL identification and effectively reduces false positives by acquiring SQL statements and their associated database environment data, and using a pre-trained model to predict slow SQL statements and execution times. Furthermore, it locates the root cause of performance issues based on the attention weight distribution generated during model processing, overcoming the limitation of traditional methods in diagnosing the causes of slow SQL statements and providing support for the continuous stability of database performance.

[0034] Optionally, in this embodiment, feature extraction based on SQL statements and database environment data is performed to generate a feature vector, including: preprocessing the SQL statements and database environment data respectively to obtain preprocessed SQL statements and preprocessed database environment data; extracting features based on the preprocessed SQL statements to obtain SQL text features, which are features reflecting the semantic and syntactic structure of the SQL statements; extracting features based on the preprocessed database environment data to obtain environment-related features, which are features reflecting the database running status and table structure attributes; and fusing the SQL text features and environment-related features to obtain a feature vector.

[0035] Optionally, in the embodiments, the SQL statement and the database environment data are preprocessed to obtain the preprocessed SQL statement and the preprocessed database environment data, including: performing data cleaning and data normalization on the SQL statement to obtain the preprocessed SQL statement; and performing normalization on the database environment data to obtain the preprocessed database environment data.

[0036] First, the raw collected data is preprocessed to output structured data suitable for model processing. Specifically, the SQL statements undergo data cleaning and data normalization. Data cleaning includes removing duplicate SQL statements (using statement hashing) and invalid statements (such as empty statements and SQL statements with syntax errors). Data normalization includes converting SQL statements to lowercase and replacing parameter values ​​in the statements with placeholders (e.g., in select statements). from order where id=123→select (from order where id=?), to eliminate the interference of parameter differences on feature extraction.

[0037] Normalization of database environment data is performed, specifically by performing min-max normalization on hardware indicators (such as CPU utilization 0-100%) and table structure parameters (such as number of rows and number of indexes) in the database environment data, mapping them to the [0,1] interval.

[0038] High-dimensional features are then extracted from the preprocessed SQL statements and database environment data, serving as input to the deep learning model. These SQL text features include word vector features and structural features. Specifically, the BERT (Bidirectional Encoder Representations from Transformers) model is used to segment the SQL statements (using keywords such as "select", "join", and "where"), generating 768-dimensional word embedding vectors, thus obtaining the word vector features. Structural features (20 dimensions in total) are extracted through a parse tree, including query type (SELECT / UPDATE / DELETE), number of table joins, number of nested subqueries, and usage of aggregate functions (COUNT / SUM, etc.), thus obtaining the structural features.

[0039] Environment-related features include table attribute features and execution context features. Specifically, table attribute features are obtained by extracting data such as the number of table rows, the number of fields, index coverage (effective indexes / total number of fields), and data skew (maximum number of partition rows / average number of partition rows), totaling 15 dimensions. Execution context features are obtained by extracting data such as the current number of database connections, the number of lock waits, and the historical average execution time, totaling 10 dimensions.

[0040] Finally, word vector features, structural features, table attribute features, and execution context features are combined to form a 1013-dimensional feature vector corresponding to a single SQL statement.

[0041] Optionally, in this embodiment, the pre-trained slow SQL prediction model includes a CNN layer, a BiLSTM layer, an Attention layer, an activation function layer, and a linear layer. Based on the feature vector, slow SQL prediction is performed using the pre-trained slow SQL prediction model to generate the slow SQL probability and prediction execution time corresponding to the SQL statement. This includes: extracting local features contained in the feature vector through the CNN layer to obtain a local feature representation; capturing long-distance dependencies in the SQL statement through the BiLSTM layer based on the local feature representation to obtain a sequence feature representation; calculating attention weights through the Attention layer to strengthen the focus on the target features based on the sequence feature representation to obtain a weighted feature representation; and generating the slow SQL probability through the activation function layer and the prediction execution time through the linear layer based on the weighted feature representation.

[0042] The pre-trained slow SQL prediction model employs a hybrid network architecture of "CNN + BiLSTM + Attention". Specifically, the Convolutional Neural Network (CNN) layer extracts local features of the SQL text (such as keyword combination patterns) through three convolutional kernels (3×3, 5×5, and 7×7 respectively). The Bidirectional Long Short-Term Memory (BiLSTM) layer captures long-distance dependencies in the SQL statement (such as the logical relationship between subqueries and the main query). The Attention layer strengthens the weight allocation of key features that affect performance, i.e., target features (such as features corresponding to "full table scan" and "no index used").

[0043] The output layer includes a classification branch and a regression branch. Specifically, the classification branch outputs the predicted probability of "slow SQL" using the sigmoid activation function (the predicted probability value is in the range of 0-1). The regression branch predicts the SQL execution time (in milliseconds) through a linear layer to help determine the performance risk level.

[0044] In this embodiment, the pre-trained slow SQL prediction model is specifically trained in the following way: Annotated historical SQL data (including 1 million normal SQL statements and 100,000 known slow SQL statements) was obtained and divided into training and validation sets in an 8:2 ratio. A joint loss function of weighted cross-entropy loss (classification) and mean squared error loss (regression) was used (weight ratio 1:0.5). The Adam optimizer was used (initial learning rate 0.001, decaying by 10% every 5 epochs). The initial slow SQL prediction model was trained using the above training data, loss function, and optimizer until the validation set accuracy was ≥95%. The initial slow SQL prediction model has the same architecture as the pre-trained slow SQL prediction model, namely a hybrid network architecture of "CNN+BiLSTM+Attention".

[0045] Load the pre-trained slow SQL prediction model, input the 1013-dimensional feature vector obtained after feature extraction into the model for prediction, and obtain the slow SQL probability and prediction execution time.

[0046] Optionally, in an embodiment, determining whether an SQL statement is a potential slow SQL statement based on the slow SQL probability and the predicted execution time includes: comparing the slow SQL probability with a preset probability threshold and comparing the predicted execution time with a preset execution time threshold; when the slow SQL probability is greater than the preset probability threshold or the predicted execution time is greater than the preset execution time threshold, the SQL statement is determined to be a potential slow SQL statement.

[0047] In this embodiment, the preset probability threshold is set to 0.7, and the preset execution time threshold is set to 500ms. If the slow SQL probability is ≥0.7 or the predicted execution time is ≥500ms, the SQL statement is determined to be a potential slow SQL statement; otherwise, the SQL statement is determined not to be a potential slow SQL statement.

[0048] Optionally, in an embodiment, determining the root cause category of an SQL statement as a potential slow SQL based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing feature vectors includes: identifying influencing features based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing feature vectors, wherein the influencing features are features whose corresponding weights are greater than a preset weight threshold; and determining the root cause category of an SQL statement as a potential slow SQL based on the influencing features and the preset feature-root cause category mapping relationship.

[0049] By tracing back the weights of the model's Attention layer, key influencing features can be identified (e.g., "full table scan" feature weight exceeds 40% → root cause category is "missing index"; high feature weight of subquery nesting level → root cause category is "logical redundancy").

[0050] Optionally, in an embodiment, the method further includes: periodically collecting SQL statements identified as potentially slow SQL and the actual execution performance data of each SQL statement identified as potentially slow SQL; adjusting the parameters of the pre-trained slow SQL prediction model based on each SQL statement identified as potentially slow SQL and each actual execution performance data, so as to update the pre-trained slow SQL prediction model.

[0051] This method also includes: outputting prediction results in a visual and interface format, supporting real-time alerts and optimization suggestions. Specifically, high-risk SQL information, i.e., relevant information (including statement text, predicted execution time, and root cause category label) of statements identified as potentially slow SQL, is pushed via SMS, email, or monitoring platform API. Optimization solutions are automatically generated based on the root cause (e.g., "suggest creating a B+ tree index for the user_id field of the order table," "suggest rewriting the subquery as a join"). The prediction results (including feature vectors, model output, and processing time) are stored in a time-series database (such as InfluxDB) for iterative model optimization.

[0052] This method addresses the shortcomings of existing slow SQL identification methods through the following key technologies: 1. A multi-dimensional feature integration method for SQL performance risk characterization: Breaking through the limitations of traditional methods that rely solely on surface-level metrics such as execution time and number of rows scanned, this method innovatively integrates SQL text semantic features (word vectors extracted through the BERT model), structural features (subquery nesting levels, table join logic, etc.), and underlying database environment features (index distribution, data skew, hardware load) into a 1013-dimensional high-dimensional feature vector. This enables a comprehensive characterization of slow SQL risks and solves the problem that a single feature cannot accurately reflect performance bottlenecks.

[0053] 2. A hybrid deep learning model architecture combining CNN, BiLSTM, and Attention: This architecture addresses the "structured text" characteristics of SQL statements by employing a hybrid neural network. The CNN layer captures local features such as keyword combinations, the BiLSTM layer models the long-distance logical dependencies between subqueries and the main query, and the Attention layer dynamically strengthens the weights of key risk features such as "full table scan" and "index failure." This architecture improves the accuracy of complex scene recognition by over 40% compared to traditional rule engines and reduces the false positive rate to below 5% compared to a single LSTM model.

[0054] 3. Slow SQL Root Cause Backtracking Mechanism Based on Weight Distribution of Model Attention Layer: Innovatively realizes backtracking from prediction results to features, that is, by quantifying the contribution of each feature (such as the weight ratio of the feature "index coverage < 30%)" to the prediction results, it directly locates the root causes such as "missing index", "subquery redundancy", and "data skew", solving the technical pain point of traditional tools that can only identify slow SQL but cannot explain the cause. The root cause diagnosis accuracy rate is over 85%.

[0055] 4. To address the real-time data processing needs in high-concurrency scenarios, a highly efficient processing mechanism combining a circular buffer with incremental feature extraction is proposed, achieving a low-latency, high-throughput data processing chain: supporting real-time interception and caching of 100,000 SQL queries per second, and achieving end-to-end analysis latency of ≤10ms through incremental inference of a pre-trained BERT model (feature extraction time per SQL query ≤5ms), combined with lightweight model deployment (TensorRT to accelerate inference), meeting the real-time requirements of high-concurrency businesses (such as e-commerce promotions).

[0056] 5. Adaptive scenario model dynamic iteration mechanism: By accumulating prediction results and actual execution data, an incremental learning dataset is built. The model parameters are fine-tuned regularly (e.g., weekly) based on new data, so that the model can automatically adapt to changes in scenarios such as data volume growth, table structure changes, and query logic iteration. This solves the maintenance problem of traditional rule engines that require frequent manual rule updates, and the model adaptability continues to improve with business iteration.

[0057] 6. End-to-end closed-loop output of "prediction-diagnosis-optimization": Based on slow SQL prediction, this method automatically generates executable optimization suggestions (such as index creation schemes and SQL statement rewriting examples) by combining the results of root cause analysis, forming a complete closed loop from risk identification to problem solving. Compared with traditional tools that only output a list of slow SQL, this method can improve the processing efficiency of operation and maintenance personnel by more than 3 times, realizing the direct transformation of technical value into business value.

[0058] In summary, this method constructs a complete technical framework encompassing "data acquisition, feature engineering, model training, predictive analysis, and result output," utilizing deep learning models to accurately predict potential performance risks of database SQL statements, thereby achieving early identification and root cause diagnosis of slow SQL queries. This method covers the entire process from data preprocessing to model deployment, overcoming the shortcomings of traditional methods such as low efficiency, poor adaptability, and lack of early warning.

[0059] To objectively verify the technical effects of the present invention, the controlled variable method was used to compare and test the present method with three traditional analysis methods under a unified experimental environment to ensure the validity and comparability of the experimental results.

[0060] The experimental conditions and methods are as follows: Hardware configuration: Database server (CPU: Intel Xeon E5-2680 v4, memory: 64GB, hard drive: 1TB SSD); Analysis server (CPU: Intel Xeon Gold 6330, memory: 128GB, GPU: NVIDIA A100 40GB); Client (100 PCs with equivalent configurations, used to simulate concurrent requests).

[0061] Software environment: The database uses MySQL 8.0 (configured for mixed OLTP and OLAP scenarios); the operating system is CentOS 7.9; traditional methods and tools (SQL Profiler 2019, self-developed rule engine); the software stack of this invention (Python 3.9, PyTorch 1.12, BERT model, InfluxDB time series database).

[0062] Experimental dataset: Collected real-world data from an e-commerce platform over 3 months, including 1.1 million SQL statements (of which 100,000 were marked as slow SQL statements, covering types such as SELECT / UPDATE / DELETE, including simple queries, nested queries, and multi-table joins). Simultaneously, environmental data such as hardware load (CPU utilization, memory usage) and table structure (index distribution, data volume) for the corresponding time periods were collected.

[0063] The experimental comparison scheme is as follows: Four test groups were set up: Group A (manual investigation method, consisting of a team of 3 engineers with more than 5 years of operation and maintenance experience), Group B (rule engine method, with 15 preset typical rules, such as "execution time > 500ms", "full table scan", "subquery nesting > 3 levels"), Group C (traditional statistical tool method, using SQL Profiler), and Group D (the method of this invention).

[0064] Test metrics include: processing efficiency (time spent analyzing a single SQL statement, total time spent processing millions of SQL statements), identification ability (accuracy, recall, F1 score, false positive rate), root cause diagnosis ability (root cause location accuracy), and early warning ability (accuracy of predicting potential slow SQL statements, early warning time).

[0065] The experimental procedure is as follows: 1) Simulate different concurrency scenarios (1000 QPS, 5000 QPS, 10000 QPS) through the client and send SQL statements of the experimental dataset to the database; 2) The four test groups simultaneously analyzed the running SQL and recorded the data of each indicator; 3) Repeat the experiment with three different datasets from different industries (financial work order system, logistics scheduling system, and government data platform) to verify the generality of the method; 4) Perform statistical analysis on the experimental data, remove outliers, and take the average value as the final result.

[0066] Experimental results show that this method significantly outperforms traditional methods in four core dimensions: processing efficiency, identification ability, root cause diagnosis, and early warning ability. Specific data comparison and analysis are as follows: Processing efficiency: Millions of SQL queries can be processed in seconds, far exceeding traditional methods.

[0067] Processing efficiency directly determines the applicability of a method in high-concurrency scenarios. Experimental data are shown in Table 1: Table 1. Comparison of Processing Efficiency Performance Analysis: This method achieves a breakthrough in efficiency through three major innovations: First, it employs real-time interception technology based on database protocols, achieving a data acquisition latency of ≤10ms; second, it utilizes incremental inference of the BERT model and TensorRT acceleration, compressing feature extraction and model prediction time to ≤5ms per record; and third, its circular buffer design supports high-concurrency data caching, preventing data loss. In contrast, manual screening relies entirely on manpower, resulting in low efficiency and an inability to handle high concurrency; rule engines and statistical tools are limited by serial matching and acquisition bottlenecks, frequently timing out in scenarios with 10,000 QPS.

[0068] In terms of recognition capability, this method achieves an accuracy rate of over 95% in complex scenarios, with a significant reduction in the false positive rate.

[0069] The accuracy and false positive rate of slow SQL identification directly affect the operation and maintenance costs. In the experiment, "whether the labeled slow SQL was accurately identified" was used as the evaluation criterion. The data is shown in Table 2: Table 2 Comparison of Recognition Capabilities Performance Analysis: The high recognition capability of this invention stems from a multi-dimensional feature fusion and hybrid model architecture, which integrates SQL text semantic features (BERT word vectors), structural features (nesting levels, table joins), and environmental features (index distribution, hardware load). It captures key local features and long-distance logical dependencies through a "CNN+BiLSTM+Attention" model, dynamically adjusting feature weights. Traditional methods, however, have significant shortcomings: manual screening relies on experience, achieving only a 65% recall rate for complex nested queries; rule engines, based on fixed thresholds, have a misclassification rate exceeding 30% for "low-time-consuming but potentially risky complex SQL" and "high-time-consuming but normal statistical queries"; and SQL Profiler relies only on surface-level metrics, failing to distinguish between "truly slow SQL" and "pseudo-slow SQL caused by temporary load."

[0070] In terms of root cause diagnosis capabilities, this invention achieves a transformation from "identification" to "diagnosis," with a localization accuracy rate exceeding 85%.

[0071] Traditional methods can generally only identify slow SQL queries but cannot pinpoint the root cause, requiring maintenance personnel to invest a significant amount of extra time in troubleshooting. This invention utilizes an Attention weighted reverse tracing mechanism to achieve precise root cause localization. The experiment used "consistency between the root cause diagnosis result and the conclusion of manual review" as the evaluation criterion, and the data is shown in Table 3. Table 3 Comparison of Root Cause Diagnostic Capabilities Performance Analysis: The root cause diagnosis mechanism of this invention quantifies the contribution of each feature to slow SQL through the Attention layer. For example, when the weight of the feature "index coverage < 30%" exceeds 40%, the root cause is directly marked as "index missing"; when the feature weight of the number of nested subqueries is high, it is identified as "logical redundancy". In contrast, rule engines can only match preset root cause labels and are powerless against hidden problems such as "data skew"; SQL Profiler cannot associate SQL logic with the underlying environment, and the root cause diagnosis accuracy is less than 20%; although manual investigation has high accuracy, it is extremely inefficient and cannot meet the needs of large-scale business.

[0072] In terms of early warning capabilities, this method can avoid risks 30 minutes in advance, breaking the "passive response" dilemma.

[0073] Traditional methods are all "post-event analysis," intervening only after slow SQL queries occur, and cannot proactively avoid performance risks. This invention achieves "pre-event warning" by predicting potential slow SQL queries using a model. The experiment used "predicted as potential slow SQL and actually occurring within 1 hour" as the evaluation criterion, and the data is shown in Table 4: Table 4 Comparison of Early Warning Capabilities Performance Analysis: The early warning capability of this invention stems from the pattern mining of historical data using a deep learning model. Specifically, by training the model to learn the correlation between "SQL features - execution time - environmental changes," an early warning is immediately triggered when the feature vector of a new SQL query matches the risk pattern of historical slow SQL queries. In a simulated e-commerce promotion scenario, this invention predicted the potential slow SQL query of "full table scan of the order table" 35 minutes in advance. Operations personnel optimized the process promptly by creating indexes, avoiding order processing delays. In contrast, traditional methods only detect problems after the slow SQL query occurs, resulting in approximately 15 minutes of business disruption.

[0074] This invention has the following core advantages and application value: Adaptive to changing scenarios: Through an incremental learning mechanism, the model parameters are fine-tuned periodically using new running data, automatically adapting to scenarios such as data volume growth, table structure changes, and query logic iterations. This solves the problem of traditional rule engines requiring frequent manual rule updates, and the model's adaptability continues to improve with business iterations.

[0075] Low database load impact: The non-intrusive data collection method based on protocol parsing eliminates the need to obtain the database execution plan (traditional methods of obtaining the execution plan will increase the database load by 10%-15%). The impact of the data collection process on the database load is ≤1%, ensuring the stable operation of the business system.

[0076] The value of the entire closed-loop process: From "predicting slow SQL" to "locating the root cause" and then to "generating optimization suggestions", a complete closed loop is formed. Traditional methods can only complete a single link. This invention directly transforms technical analysis into an executable operation and maintenance solution, which greatly reduces the operation and maintenance threshold.

[0077] Practical application value: This invention has demonstrated significant value in pilot applications across multiple industries, including finance, e-commerce, and government. Specifically, after its application in a financial work order system, the number of system crashes caused by slow SQL queries decreased from 3-5 times per month to 0 times, and the time spent by maintenance personnel dealing with slow SQL queries was reduced from 4 hours per day to 30 minutes. During a major promotional period, an e-commerce platform optimized 23 potential slow SQL queries in advance using the early warning function of this invention, resulting in a 40% increase in peak order processing efficiency and a 15% increase in sales compared to the same period last year.

[0078] In summary, this invention achieves a breakthrough improvement over traditional methods in terms of efficiency, accuracy, root cause diagnosis, and early warning capabilities for slow SQL analysis through core technological innovations such as multi-dimensional feature fusion, a hybrid model of "CNN+BiLSTM+Attention," attention root cause tracing, and incremental learning. Experimental data shows that this invention can achieve second-level processing of millions of SQL queries, an accuracy rate of ≥95% for complex scene identification, an accuracy rate of ≥85% for root cause diagnosis, and early warning of potential risks 30 minutes in advance, with minimal impact on database load. Its end-to-end closed-loop design not only solves the inherent defects of traditional methods but also upgrades database performance assurance from "passive response" to "proactive prevention," providing an efficient and intelligent solution for database operation and maintenance in high-concurrency and complex business scenarios, possessing extremely high technical value and commercial application prospects.

[0079] Example 2 like Figure 2 As shown, this embodiment provides a database slow SQL prediction and analysis device 200, including: The data acquisition module 201 is used to acquire SQL statements and associated database environment data during database operation in real time. Feature extraction module 202 is used to extract features based on SQL statements and database environment data, and generate feature vectors; The predictive analysis module 203 is used to predict slow SQL based on feature vectors using a pre-trained slow SQL prediction model, and generate the slow SQL probability and predicted execution time corresponding to the SQL statement. The risk identification module 204 is used to determine whether an SQL statement is a potential slow SQL statement based on the probability of slow SQL and the predicted execution time. The root cause analysis module 205 is used to determine the root cause category that makes an SQL statement a potential slow SQL statement based on the attention weight distribution generated when the pre-trained slow SQL prediction model processes feature vectors.

[0080] Optionally, in an embodiment, the feature extraction module 202 includes: The preprocessing unit is used to preprocess the SQL statement and the database environment data respectively, to obtain the preprocessed SQL statement and the preprocessed database environment data; The first feature extraction unit is used to extract features from the preprocessed SQL statement to obtain SQL text features, which are features that reflect the semantic and syntactic structure of the SQL statement. The second feature extraction unit is used to extract features based on the preprocessed database environment data to obtain environment-related features, which are features that reflect the database operating status and table structure attributes. The feature vector generation unit is used to fuse SQL text features and environment-related features to obtain feature vectors.

[0081] Optionally, in an embodiment, the preprocessing unit includes: The first preprocessing subunit is used to perform data cleaning and data normalization on the SQL statement to obtain the preprocessed SQL statement. The second preprocessing subunit is used to normalize the database environment data to obtain preprocessed database environment data.

[0082] Optionally, in this embodiment, the pre-trained slow SQL prediction model includes a CNN layer, a BiLSTM layer, an Attention layer, an activation function layer, and a linear layer; the prediction analysis module 203 includes: The first processing unit is used to extract local features contained in the feature vector through the CNN layer to obtain the local feature representation; The second processing unit is used to capture long-distance dependencies in SQL statements through a BiLSTM layer based on local feature representations to obtain sequence feature representations; The third processing unit is used to calculate attention weights through the Attention layer based on the sequence feature representation to strengthen the attention to the target features and obtain the weighted feature representation. The fourth processing unit is used to generate slow SQL probabilities through the activation function layer and predict execution time through the linear layer based on the weighted feature representation.

[0083] Optionally, in an embodiment, the risk identification module 204 includes: The comparison unit is used to compare the slow SQL probability with a preset probability threshold and the predicted execution time with a preset time threshold. The risk identification unit is used to determine that an SQL statement is a potential slow SQL statement when the probability of a slow SQL statement is greater than a preset probability threshold or the predicted execution time is greater than a preset execution time threshold.

[0084] Optionally, in an embodiment, the root cause analysis module 205 includes: The feature recognition unit is used to identify the influencing features based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing feature vectors. The influencing features are those whose weights are greater than a preset weight threshold. The root cause determination unit is used to determine the root cause category that causes an SQL statement to be a potential slow SQL statement, based on the impact characteristics and the preset mapping relationship between characteristics and root cause categories.

[0085] Optionally, in an embodiment, the apparatus further includes: The data collection module is used to periodically collect SQL statements that are identified as potentially slow SQL statements and the actual execution performance data of each SQL statement identified as potentially slow SQL statement; The model update module is used to adjust the parameters of the pre-trained slow SQL prediction model based on each SQL statement identified as a potential slow SQL and each actual execution performance data, so as to update the pre-trained slow SQL prediction model.

[0086] In some embodiments, the slow SQL prediction analysis device 200 of the present invention can be implemented in a combination of hardware and software. As an example, the slow SQL prediction analysis device 200 of the present invention can be a processor in the form of a hardware decoding processor, which is programmed to execute the slow SQL prediction analysis method of the present invention. For example, the processor in the form of a hardware decoding processor can be one or more application-specific integrated circuits (ASICs), DSPs, programmable logic devices (PLDs), complex programmable logic devices (CPLDs), field-programmable gate arrays (FPGAs), or other electronic components.

[0087] The modules described in the embodiments of this invention can be implemented in software or hardware. The names of the modules are not, in some cases, limiting the scope of the module itself.

[0088] Example 3 like Figure 3 As shown, this embodiment provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the database slow SQL predictive analysis method as described in Embodiment 1.

[0089] In other words, an electronic device according to an embodiment of the present invention may include, but is not limited to: a processor and a memory; the memory is used to store computer programs; the processor is used to execute the database slow SQL predictive analysis method shown in any embodiment of the present invention by calling the computer program.

[0090] In one alternative embodiment, an electronic device is provided, such as Figure 3 As shown, Figure 3The illustrated electronic device 300 includes a processor 301 and a memory 303. The processor 301 and the memory 303 are connected, for example, via a bus 302. Optionally, the electronic device 300 may further include a transceiver 304, which can be used for data interaction between the electronic device and other electronic devices, such as sending and / or receiving data. It should be noted that in practical applications, the transceiver 304 is not limited to one type, and the structure of the electronic device 300 does not constitute a limitation on the embodiments of the present invention.

[0091] Processor 301 may be a CPU (Central Processing Unit), a general-purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It can implement or execute the various exemplary logic blocks, modules, and circuits described in conjunction with the disclosure of this invention. Processor 301 may also be a combination that implements computational functions, such as including one or more microprocessor combinations, a combination of a DSP and a microprocessor, etc.

[0092] Bus 302 may include a path for transmitting information between the aforementioned components. Bus 302 may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, etc. Bus 302 can be divided into address bus, data bus, control bus, etc. For ease of representation, Figure 3 The bus 302 is represented by only one thick line, but this does not mean that there is only one bus or one type of bus.

[0093] The memory 303 may be a ROM (Read Only Memory) or other type of static storage device capable of storing static information and instructions, RAM (Random Access Memory) or other type of dynamic storage device capable of storing information and instructions, or an EEPROM (Electrically Erasable Programmable Read Only Memory), CD-ROM (Compact Disc Read Only Memory) or other optical disc storage, optical disc storage (including compressed optical discs, laser discs, optical discs, digital universal optical discs, Blu-ray discs, etc.), magnetic disk storage media or other magnetic storage devices, or any other medium capable of carrying or storing desired program code in the form of instructions or data structures and accessible by a computer, but not limited thereto.

[0094] The memory 303 is used to store application code (computer program) for executing the present invention, and its execution is controlled by the processor 301. The processor 301 is used to execute the application code stored in the memory 303 to implement the content shown in the foregoing method embodiments.

[0095] Among them, electronic devices can also be terminal devices, which can be any device that can install applications, including at least one of smartphones, tablets, laptops, desktop computers, smart speakers, smartwatches, smart TVs, and smart in-vehicle devices.

[0096] It should be noted that, Figure 3 The electronic device shown is merely an example and should not be construed as limiting the functionality and scope of use of the embodiments of the present invention.

[0097] Example 4 This embodiment provides a non-transitory computer-readable storage medium that stores computer instructions for causing a computer to execute a database slow SQL prediction and analysis method as described in Embodiment 1.

[0098] Alternatively, the computer-readable storage medium may be a read-only memory (ROM), a random access memory (RAM), a compact disc read-only memory (CD-ROM), magnetic tape, a floppy disk, and an optical data storage device, etc.

[0099] In an exemplary embodiment, a computer program product or computer program is also provided, which includes computer instructions stored in a computer-readable storage medium. A processor of an electronic device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the electronic device to perform the aforementioned slow SQL predictive analysis method for databases.

[0100] Computer program code for performing the operations of this invention can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, and conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0101] It should be understood that the flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of methods and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0102] The computer-readable storage medium provided in this invention can be, but is not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of a computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EEPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this invention, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0103] The aforementioned computer-readable storage medium carries one or more programs, which, when executed by the electronic device, cause the electronic device to perform the method shown in the above embodiments.

[0104] The above description is merely a preferred embodiment of the present invention and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of disclosure in this invention is not limited to technical solutions formed by specific combinations of the above-described technical features, but should also cover other technical solutions formed by arbitrary combinations of the above-described technical features or their equivalents without departing from the above-disclosed concept. For example, technical solutions formed by substituting the above features with (but not limited to) technical features with similar functions disclosed in this invention.

[0105] It should be noted that the terms "first," "second," etc., used in the specification and claims of this application are used to distinguish similar objects and represent a limitation on a specific order or sequence. Where appropriate, the order of use for similar objects can be interchanged so that the embodiments of this application described herein can be implemented in an order other than that shown or described.

[0106] Those skilled in the art will recognize that this invention can be implemented as a system, method, or computer program product. Therefore, this invention can be specifically implemented in the following forms: it can be entirely hardware, entirely software (including firmware, resident software, microcode, etc.), or a combination of hardware and software, generally referred to herein as a "circuit," "module," or "system." Furthermore, in some embodiments, this invention can also be implemented as a computer program product contained in one or more computer-readable media, which includes computer-readable program code.

[0107] Although embodiments of the present invention have been shown and described above, it is understood that the above embodiments are exemplary and should not be construed as limiting the present invention. Those skilled in the art can make changes, modifications, substitutions and variations to the above embodiments within the scope of the present invention.

Claims

1. A method for predictive analysis of slow SQL queries in a database, characterized in that, include: Real-time acquisition of SQL statements during database operation and database environment data associated with those SQL statements; Feature vectors are generated by extracting features based on the SQL statements and the database environment data. Based on the feature vector, slow SQL prediction is performed using a pre-trained slow SQL prediction model to generate the slow SQL probability and predicted execution time corresponding to the SQL statement; Based on the slow SQL probability and the predicted execution time, determine whether the SQL statement is a potential slow SQL statement; When the SQL statement is determined to be a potential slow SQL statement, the root cause category that causes the SQL statement to be a potential slow SQL statement is determined based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing the feature vector.

2. The database slow SQL prediction and analysis method according to claim 1, characterized in that, The step of extracting features based on the SQL statement and the database environment data to generate feature vectors includes: The SQL statement and the database environment data are preprocessed respectively to obtain the preprocessed SQL statement and the preprocessed database environment data; Feature extraction is performed on the preprocessed SQL statement to obtain SQL text features, which are features that reflect the semantic and syntactic structure of the SQL statement; Based on the preprocessed database environment data, feature extraction is performed to obtain environment-related features, which are features that reflect the database operating status and table structure attributes. The feature vector is obtained by fusing the SQL text features and the environment-related features.

3. The database slow SQL prediction and analysis method according to claim 2, characterized in that, The step of preprocessing the SQL statement and the database environment data to obtain preprocessed SQL statements and preprocessed database environment data includes: The SQL statement is subjected to data cleaning and data normalization processes to obtain a preprocessed SQL statement. The database environment data is normalized to obtain preprocessed database environment data.

4. The database slow SQL prediction and analysis method according to claim 1, characterized in that, The pre-trained slow SQL prediction model includes a CNN layer, a BiLSTM layer, an Attention layer, an activation function layer, and a linear layer. The step of predicting slow SQL statements based on the feature vectors using the pre-trained slow SQL prediction model, generating the slow SQL probability and predicted execution time for each SQL statement, includes: The local features contained in the feature vector are extracted through the CNN layer to obtain the local feature representation; Based on the local feature representation, the long-distance dependencies in the SQL statement are captured by the BiLSTM layer to obtain the sequence feature representation; Based on the sequence feature representation, attention weights are calculated through the Attention layer to enhance attention to the target features, resulting in a weighted feature representation. Based on the weighted feature representation, the slow SQL probability is generated through the activation function layer, and the predicted execution time is generated through the linear layer.

5. The database slow SQL prediction and analysis method according to claim 1, characterized in that, The step of determining whether an SQL statement is a potential slow SQL statement based on the slow SQL probability and the predicted execution time includes: The probability of slow SQL is compared with a preset probability threshold, and the predicted execution time is compared with a preset time threshold. When the probability of slow SQL is greater than a preset probability threshold or the predicted execution time is greater than a preset time threshold, the SQL statement is determined to be a potential slow SQL.

6. The database slow SQL prediction and analysis method according to claim 1, characterized in that, The attention weight distribution generated by the pre-trained slow SQL prediction model when processing the feature vector determines the root cause category that makes the SQL statement a potential slow SQL statement, including: Based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing the feature vector, the influencing features are identified, and the influencing features are those whose corresponding weights are greater than a preset weight threshold. Based on the impact features and the preset feature-root cause category mapping relationship, the root cause category that causes the SQL statement to be a potential slow SQL is determined.

7. The database slow SQL prediction and analysis method according to claim 1, characterized in that, Also includes: Periodically collect SQL statements identified as potentially slow SQL statements and the actual execution performance data of each identified potentially slow SQL statement; Based on each SQL statement identified as a potential slow SQL and each actual execution performance data, the parameters of the pre-trained slow SQL prediction model are adjusted to update the pre-trained slow SQL prediction model.

8. A database slow SQL predictive analysis device, characterized in that, include: The data acquisition module is used to acquire SQL statements and associated database environment data during database operation in real time. The feature extraction module is used to extract features based on the SQL statement and the database environment data, and generate feature vectors; The predictive analysis module is used to predict slow SQL statements based on the feature vectors using a pre-trained slow SQL prediction model, and to generate the slow SQL probability and predicted execution time corresponding to the SQL statement. The risk identification module is used to determine whether the SQL statement is a potential slow SQL statement based on the slow SQL probability and the predicted execution time. The root cause analysis module is used to determine the root cause category that makes the SQL statement a potential slow SQL statement based on the attention weight distribution generated by the pre-trained slow SQL prediction model when processing the feature vector.

9. An electronic device, characterized in that, The method includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, implements the database slow SQL predictive analysis method as described in any one of claims 1 to 7.

10. A non-transitory computer-readable storage medium storing computer instructions, characterized in that, The computer instructions are used to cause the computer to execute the database slow SQL predictive analysis method according to any one of claims 1 to 7.

Images