Database slow SQL (Structured Query Language) prediction method and system based on large model
By applying a deep learning model based on large models in the database, automatically extracting features from historical SQL statement data, the problem of identifying and predicting slow SQL dependence on manual experience in the existing technology is solved, and more efficient and automated database performance optimization is achieved.
Patent Information
- Application Number
- CN202510215865.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-02-26
- Publication Date
- 2025-06-27
AI Technical Summary
The prior art relies on manual experience and rule-based tools when identifying and predicting slow SQL in databases, with high labor costs and lack of universality.
Using a deep learning model based on large models, extract and learn effective features from historical SQL statement data, and automatically and accurately predict and identify potential slow SQL.
It improves the performance and stability of the database system, reduces the workload of database administrators, and improves the intelligence and automation level of database management.
Smart Images
Figure CN120216531A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of database performance optimization, and more specifically, to a method and system for predicting slow SQL in a database based on a large model. Background Art
[0002] With the development of information technology and the rapid increase in data volume, database systems play an increasingly important role in enterprises and organizations. SQL statements are the main way to interact with databases, and their execution efficiency directly affects the performance of database systems. However, complex SQL statements may lead to low execution efficiency, resulting in slow SQL, which in turn affects the overall performance of database systems. Therefore, how to effectively predict and identify slow SQL has become one of the key issues in database performance optimization.
[0003] Traditional slow SQL detection methods mainly rely on the experience of database administrators and some rule-based tools. These methods usually have high labor costs and lack generality. Summary of the Invention
[0004] The technical task of the present invention is to address the above deficiencies by providing a method and system for predicting slow SQL in a database based on a large model. By leveraging the powerful learning ability of deep learning models, effective features are extracted and learned from historical SQL statement data to automatically and accurately predict and identify potential slow SQL, improve the performance and stability of database systems, reduce the workload of database administrators, and enhance the intelligence and automation level of overall database management.
[0005] The technical solution adopted by the present invention to solve its technical problems is as follows:
[0006] A method for predicting slow SQL in a database based on a large model, the implementation of which includes the following steps:
[0007] Data collection: Extract historical data containing SQL statements and their execution times from database logs;
[0008] Data preprocessing: After data collection is completed, clean and normalize the collected SQL execution data;
[0009] Feature extraction: Extract features from the preprocessed SQL statements that can reflect their execution performance;
[0010] Model training: Train the extracted features to generate a slow SQL prediction model;
[0011] Model evaluation: Evaluate the performance of the trained model on the test set and judge the quality of the model by calculating various metrics;
[0012] Slow SQL Prediction: Predict new SQL statements and determine whether they are potential slow SQLs based on the prediction results.
[0013] This method utilizes the powerful learning ability of large models to extract effective features from historical SQL statement data for modeling and prediction, thereby automatically and accurately identifying potential slow SQLs and improving the performance and stability of the database system.
[0014] Furthermore, the data collection specifically includes:
[0015] Log Parsing: Database systems usually generate log files containing SQL statement execution information. The data collection module can parse log files of different database systems, including MySQL, PostgreSQL, and Oracle files; extract key information during the parsing process, including SQL statements, execution time, execution count, and error information, and store them in a structured manner.
[0016] Data Acquisition: In addition to log files, the data collection module can also obtain SQL execution information in real time through the monitoring interface of the database management system, and periodically collect the latest SQL execution data through scheduled tasks or event triggers to ensure the real-time nature and dynamic update of the data.
[0017] Data Storage: Store the collected SQL execution data in an efficient database for subsequent processing and analysis.
[0018] Furthermore, the data preprocessing specifically includes:
[0019] Data Cleaning: Remove invalid data, including empty SQL statements, records with zero execution time, etc.; remove duplicate data. For cases where the same SQL statement is executed multiple times, only retain the latest or most representative execution record.
[0020] Data Normalization: SQL statement normalization, replace dynamic values (such as constants, dates, etc.) in the SQL statement with unified placeholders to reduce the dimension of the feature space and improve the generalization ability of the model; execution time normalization, perform normalization processing (such as standardization, normalization, etc.) on the execution time to eliminate the impact of different dimensions on model training.
[0021] Slow SQL Marking: According to a preset execution time threshold (e.g., 2 seconds), mark SQL statements with execution time exceeding this threshold as slow SQLs.
[0022] Furthermore, the feature extraction specifically includes:
[0023] (1) SQL statement features, including:
[0024] SQL statement length: The character length of the SQL statement. Usually, longer SQL statements may involve more tables and conditions, thus increasing the execution time;
[0025] Query type: The type of the SQL statement (such as SELECT, INSERT, UPDATE, DELETE, etc.). Different types of queries have different impacts on database performance;
[0026] Number of tables: The number of tables involved in the SQL statement. The more tables there are, the longer the execution time usually is;
[0027] Number of JOIN operations: The number of JOIN operations included in the SQL statement. JOIN operations are usually important factors affecting the execution time;
[0028] (2) Condition features, including:
[0029] Number of conditions in the WHERE clause: The number of conditions included in the WHERE clause of the SQL statement. The more conditions there are, the higher the query complexity;
[0030] Number of subqueries: The number of subqueries included in the SQL statement. Subqueries increase the execution complexity;
[0031] Number of aggregate functions: The number of aggregate functions (such as COUNT, SUM, AVG, etc.) used in the SQL statement. Aggregate operations increase the computational amount;
[0032] (3) Index features, including:
[0033] Number of indexes used: The number of indexes used in the SQL statement. Appropriate indexes can speed up the query;
[0034] Index coverage: Whether the fields involved in the SQL statement are covered by indexes. The higher the index coverage, the higher the query efficiency;
[0035] (4) Natural language processing features, including:
[0036] Bag of Words: Regarding the SQL statement as a series of words and converting it into a feature vector through word frequency statistics;
[0037] TF-IDF: Calculating the importance of terms in the SQL statement to generate a feature vector to reflect the impact of different terms on SQL execution performance;
[0038] After feature extraction is completed, all features will form a feature matrix as the data input for model training.
[0039] Furthermore, the model training specifically includes:
[0040] Data Partition: The preprocessed dataset is partitioned into a training set and a test set in a ratio of 7:3. The training set is used to train the model, and the test set is used to evaluate the model performance.
[0041] Model Training: Use the data in the training set to train the selected SVM algorithm to generate a slow SQL prediction model. During the training process, the model parameters can be optimized through cross-validation to improve the generalization ability of the model.
[0042] Model Optimization: Use grid search to tune the hyperparameters of the model to find the best parameter combination. Through feature selection technology, select the features that contribute more to the prediction results, and eliminate redundant or invalid features to improve the model performance.
[0043] Furthermore, for the model evaluation, the evaluation metrics include:
[0044] Accuracy: It represents the proportion of the number of samples correctly predicted by the model to the total number of samples.
[0045] Precision: It represents the proportion of samples actually being slow SQL among the samples predicted as slow SQL by the model. This metric can measure the accuracy of the model when predicting slow SQL.
[0046] Recall: It represents the proportion of samples actually being slow SQL that are correctly predicted as slow SQL by the model. This metric can measure the coverage ability of the model, that is, the capture ability of the model for slow SQL.
[0047] F1 Score: The harmonic mean of precision and recall. When a balance needs to be achieved between precision and recall, the F1 score is a better evaluation metric.
[0048] ROC-AUC Curve: The AUC-ROC curve is drawn by calculating the true positive rate (TPR) and false positive rate (FPR) at different thresholds. The larger the area under the curve (AUC), the better the model performance.
[0049] By calculating the above evaluation metrics, the performance of the model can be comprehensively evaluated to determine whether it can effectively identify slow SQL.
[0050] Furthermore, for the slow SQL prediction, the prediction process includes:
[0051] Feature Extraction: Extract features from the new SQL statement to generate a feature vector consistent with the training process.
[0052] Model prediction: Input the feature vector into the trained slow SQL prediction model to obtain the prediction result.
[0053] The present invention also claims a database slow SQL prediction system based on a large model, including a data collection module, a data preprocessing module, a feature extraction module, a model training module, a model evaluation module, and a slow SQL prediction module. Each module works collaboratively to jointly achieve automated slow SQL identification and prediction.
[0054] The data collection module is used to extract historical data containing SQL statements and their execution times from the database log.
[0055] The data preprocessing module is used to clean and normalize the collected SQL execution data after the data collection is completed.
[0056] The feature extraction module is used to extract features from the preprocessed SQL statements that can reflect their execution performance.
[0057] The model training module is used to train the extracted features to generate a slow SQL prediction model.
[0058] The model evaluation module is used to evaluate the performance of the trained model on the test set and judge the quality of the model by calculating various metrics.
[0059] The slow SQL prediction module is used to predict new SQL statements and judge whether they are potential slow SQLs according to the prediction results.
[0060] This system realizes the prediction of database slow SQL based on a large model through the above method.
[0061] The present invention also claims a database slow SQL prediction device based on a large model, including: at least one memory and at least one processor.
[0062] The at least one memory is used to store machine-readable programs.
[0063] The at least one processor is used to call the machine-readable program to implement the above method.
[0064] The present invention also claims a computer-readable medium, on which computer instructions are stored. When the computer instructions are executed by a processor, the processor executes the above method.
[0065] A database slow SQL prediction method and system based on a large model of the present invention have the following beneficial effects compared with the prior art:
[0066] First, by introducing a deep learning model, the present invention can learn complex feature relationships from a large amount of historical SQL statement data, significantly improving the accuracy of slow SQL prediction and reducing false positives and false negatives. Secondly, the present invention realizes the automation and intelligence of SQL statement performance analysis, reduces the dependence on the experience of database administrators and manual intervention, and significantly improves work efficiency. BRIEF DESCRIPTION OF THE DRAWINGS
[0067] Figure 1 is a flowchart of a method for predicting slow SQL in a database based on a large model provided by an embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS
[0068] The present invention will be further described below with reference to the accompanying drawings and specific embodiments.
[0069] An embodiment of the present invention provides a method for predicting slow SQL in a database based on a large model. The method first collects historical SQL statements and their execution information from a database system. After data cleaning, deduplication, and normalization processing, multi-dimensional features are extracted using natural language processing technology and database domain knowledge, including syntactic features, structural features, and execution features, etc. Next, a deep learning model is used to train the extracted features. Through steps such as model selection, model construction, model training, and model evaluation, a model that can automatically and accurately predict slow SQL is generated.
[0070] The implementation of the method includes the following steps:
[0071] Data collection: Extract historical data containing SQL statements and their execution times from database logs;
[0072] Data preprocessing: After data collection is completed, clean and normalize the collected SQL execution data;
[0073] Feature extraction: Extract features from the preprocessed SQL statements that can reflect their execution performance;
[0074] Model training: Train the extracted features to generate a slow SQL prediction model;
[0075] Model evaluation: Evaluate the performance of the trained model on the test set, and judge the quality of the model by calculating various metrics;
[0076] Slow SQL prediction: Predict new SQL statements and judge whether they are potential slow SQL according to the prediction results.
[0077] Among them, the data collection specifically includes:
[0078] Log Parsing: Database systems usually generate log files containing information about SQL statement executions. The data collection module can parse log files of different database systems (such as MySQL, PostgreSQL, Oracle files), extract key information during the parsing process, including SQL statements, execution times, execution counts, error messages, and store them in a structured manner;
[0079] Data Collection: In addition to log files, the data collection module can also obtain SQL execution information in real time through the monitoring interface of the database management system, and collect the latest SQL execution data periodically through scheduled tasks or event triggers to ensure data timeliness and dynamic updates;
[0080] Data Storage: Store the collected SQL execution data in an efficient database for subsequent processing and analysis.
[0081] The data preprocessing specifically includes:
[0082] Data Cleaning: Remove invalid data, including empty SQL statements, records with zero execution time, etc.; Remove duplicate data. For cases where the same SQL statement is executed multiple times, only keep the latest or most representative execution record;
[0083] Data Normalization: SQL statement normalization, replace dynamic values (such as constants, dates, etc.) in the SQL statement with unified placeholders to reduce the dimension of the feature space and improve the generalization ability of the model; Execution time normalization, perform normalization processing (such as standardization, normalization, etc.) on the execution time to eliminate the impact of different dimensions on model training;
[0084] Slow SQL Marking: According to a preset execution time threshold (such as 2 seconds), mark SQL statements with execution times exceeding this threshold as slow SQL.
[0085] The feature extraction specifically includes:
[0086] (1) SQL statement features, including:
[0087] SQL statement length: The character length of the SQL statement. Usually, longer SQL statements may involve more tables and conditions, thus increasing the execution time;
[0088] Query type: The type of the SQL statement (such as SELECT, INSERT, UPDATE, DELETE, etc.). Different types of queries have different impacts on database performance;
[0089] Number of tables: The number of tables involved in the SQL statement. The more tables there are, the longer the execution time usually is;
[0090] Number of JOIN operations: The number of JOIN operations included in the SQL statement. JOIN operations are usually important factors affecting the execution time;
[0091] (2) Condition features, including:
[0092] Number of WHERE clause conditions: The number of conditions included in the WHERE clause of the SQL statement. The more conditions, the higher the query complexity;
[0093] Number of subqueries: The number of subqueries included in the SQL statement. Subqueries increase the execution complexity;
[0094] Number of aggregate functions: The number of aggregate functions (such as COUNT, SUM, AVG, etc.) used in the SQL statement. Aggregate operations increase the computational workload;
[0095] (3) Index features, including:
[0096] Number of indexes used: The number of indexes used in the SQL statement. Appropriate indexes can speed up the query;
[0097] Index coverage: Whether the fields involved in the SQL statement are covered by indexes. The higher the index coverage, the higher the query efficiency;
[0098] (4) Natural language processing features, including:
[0099] Bag of Words: Regarding the SQL statement as a series of words and converting it into a feature vector through word frequency statistics;
[0100] TF-IDF: Calculating the importance of terms in the SQL statement to generate a feature vector to reflect the impact of different terms on the SQL execution performance;
[0101] After feature extraction, all features will form a feature matrix as the data input for model training.
[0102] The model training specifically includes:
[0103] Data partitioning: Partitioning the preprocessed dataset into a training set and a test set according to a 7:3 ratio; the training set is used to train the model, and the test set is used to evaluate the model performance;
[0104] Model training: Using the data in the training set to train the selected SVM algorithm to generate a slow SQL prediction model; during the training process, the model parameters can be optimized through cross-validation to improve the generalization ability of the model;
[0105] Model Optimization: Use Grid Search to tune the hyperparameters of the model to find the best parameter combination; through Feature Selection technology, select the features that contribute more to the prediction results, and eliminate redundant or invalid features to improve the model performance.
[0106] The model evaluation, the evaluation metrics include:
[0107] Accuracy: It represents the proportion of the number of samples correctly predicted by the model to the total number of samples.
[0108] Precision: It represents the proportion of the samples actually being slow SQL among the samples predicted as slow SQL by the model; this metric can measure the accuracy of the model when predicting slow SQL.
[0109] Recall: It represents the proportion of the samples actually being slow SQL that are correctly predicted as slow SQL by the model; this metric can measure the coverage ability of the model, that is, the capture ability of the model for slow SQL.
[0110] F1 Score: The harmonic mean of precision and recall; when a balance needs to be achieved between precision and recall, the F1 score is a better evaluation metric.
[0111] ROC-AUC Curve: The AUC-ROC curve is drawn by calculating the true positive rate (TPR) and false positive rate (FPR) at different thresholds. The larger the area under the curve (AUC), the better the model performance.
[0112] By calculating the above evaluation metrics, the performance of the model can be comprehensively evaluated to determine whether it can effectively identify slow SQL.
[0113] The slow SQL prediction, the prediction process includes:
[0114] Feature Extraction: Extract features from the new SQL statement to generate feature vectors consistent with the training process.
[0115] Model Prediction: Input the feature vector into the trained slow SQL prediction model to obtain the prediction result.
[0116] This method utilizes the powerful learning ability of the large model to extract effective features from the historical SQL statement data for modeling and prediction, thereby automatically and accurately identifying potential slow SQL and improving the performance and stability of the database system.
[0117] An embodiment of the present invention also provides a database slow SQL prediction system based on a large model, which includes a data collection module, a data preprocessing module, a feature extraction module, a model training module, a model evaluation module, and a slow SQL prediction module. Each module works collaboratively to jointly achieve automated slow SQL identification and prediction.
[0118] This system realizes database slow SQL prediction based on a large model through the database slow SQL prediction method described in the above embodiment.
[0119] The data collection module is the foundation of the entire system, and its main task is to extract historical data containing SQL statements and their execution times from database logs. To ensure the comprehensiveness and accuracy of the data, the data collection module has the following functions:
[0120] (1) Log parsing: Database systems usually generate log files containing SQL statement execution information. The data collection module has the ability to parse log files of different database systems (such as MySQL, PostgreSQL, Oracle, etc.). During the parsing process, key information such as SQL statements, execution times, execution counts, error information, etc. need to be extracted and stored in a structured manner.
[0121] (2) Data acquisition: In addition to log files, the data collection module can obtain SQL execution information in real time through the monitoring interface of the database management system. Through scheduled tasks or event-triggered methods, the latest SQL execution data is periodically collected to ensure the real-time nature and dynamic update of the data.
[0122] (3) Data storage: The collected SQL execution data is stored in an efficient database for subsequent processing and analysis.
[0123] The data preprocessing module, its main task is to clean and normalize the collected SQL execution data after data collection is completed. The preprocessing process includes the following steps:
[0124] Data cleaning: Remove invalid data, such as empty SQL statements, records with zero execution time, etc.; remove duplicate data. For cases where the same SQL statement is executed multiple times, only keep the latest or most representative execution record.
[0125] Data normalization: SQL statement normalization, replace dynamic values (such as constants, dates, etc.) in the SQL statement with unified placeholders to reduce the dimension of the feature space and improve the generalization ability of the model; execution time normalization, perform normalization processing (such as standardization, normalization, etc.) on the execution time to eliminate the impact of different dimensions on model training.
[0126] Slow SQL Marking: According to a preset execution time threshold (e.g., 2 seconds), SQL statements whose execution time exceeds this threshold are marked as slow SQL.
[0127] The feature extraction module is a key step affecting the model performance. The quality of features directly determines the prediction effect of the model. The main task of the feature extraction module is to extract features from the preprocessed SQL statements that can reflect their execution performance. Features can be classified into the following categories:
[0128] (1) SQL statement features:
[0129] SQL statement length: The character length of the SQL statement. Usually, longer SQL statements may involve more tables and conditions, thus increasing the execution time.
[0130] Query type: The type of the SQL statement (such as SELECT, INSERT, UPDATE, DELETE, etc.). Different types of queries have different impacts on database performance.
[0131] Number of tables: The number of tables involved in the SQL statement. The more tables there are, the longer the execution time usually is.
[0132] Number of JOIN operations: The number of JOIN operations contained in the SQL statement. JOIN operations are usually important factors affecting the execution time.
[0133] (2) Condition features:
[0134] Number of conditions in the WHERE clause: The number of conditions contained in the WHERE clause of the SQL statement. The more conditions there are, the higher the query complexity.
[0135] Number of subqueries: The number of subqueries contained in the SQL statement. Subqueries increase the execution complexity.
[0136] Number of aggregate functions: The number of aggregate functions (such as COUNT, SUM, AVG, etc.) used in the SQL statement. Aggregate operations increase the computational amount.
[0137] (3) Index features:
[0138] Number of indexes used: The number of indexes used in the SQL statement. Appropriate indexes can speed up the query.
[0139] Index coverage: Whether the fields involved in the SQL statement are covered by indexes. The higher the index coverage, the higher the query efficiency.
[0140] (4) Natural language processing features:
[0141] Bag of Words: Consider the SQL statement as a series of words and convert it into a feature vector through word frequency statistics;
[0142] TF-IDF: Calculate the importance of terms in the SQL statement to generate a feature vector, reflecting the impact of different terms on the SQL execution performance.
[0143] After feature extraction is completed, all features will form a feature matrix as the data input for model training.
[0144] The main task of the model training module is to train the extracted features to generate a slow SQL prediction model. The process of model training includes the following steps:
[0145] (1) Data partitioning: Partition the preprocessed dataset into a training set and a test set according to a 7:3 ratio. The training set is used to train the model, and the test set is used to evaluate the model performance.
[0146] (2) Model training: Use the data in the training set to train the selected SVM algorithm to generate a slow SQL prediction model. During the training process, the model parameters can be optimized through cross-validation to improve the generalization ability of the model.
[0147] (3) Model optimization: Use grid search to tune the hyperparameters of the model to find the best parameter combination; through feature selection technology, select the features that contribute more to the prediction results and eliminate redundant or invalid features to improve the model performance.
[0148] The main task of the model evaluation module is to evaluate the performance of the trained model on the test set and judge the quality of the model by calculating various metrics. Commonly used evaluation metrics include:
[0149] (1) Accuracy: Represents the proportion of the number of samples correctly predicted by the model to the total number of samples.
[0150] (2) Precision: Represents the proportion of samples actually being slow SQL among the samples predicted as slow SQL by the model. This metric can measure the accuracy of the model when predicting slow SQL.
[0151] (3) Recall: Represents the proportion of samples actually being slow SQL that are correctly predicted as slow SQL by the model. This metric can measure the coverage ability of the model, that is, the ability of the model to capture slow SQL.
[0152] (4) F1 Score: The harmonic mean of precision and recall. When a balance needs to be struck between precision and recall, the F1 score is a better evaluation metric.
[0153] (5) ROC-AUC Curve: The AUC-ROC curve is plotted by calculating the true positive rate (TPR) and false positive rate (FPR) at different thresholds. The larger the area under the curve (AUC), the better the model performance.
[0154] By calculating the above evaluation metrics, the performance of the model can be comprehensively evaluated to determine whether it can effectively identify slow SQL.
[0155] The main task of the slow SQL prediction module is to use the trained model to predict new SQL statements and determine whether they are potential slow SQL based on the prediction results. The prediction process includes the following steps:
[0156] (1) Feature extraction: Extract features from the new SQL statement to generate a feature vector consistent with the training process.
[0157] (2) Model prediction: Input the feature vector into the trained slow SQL prediction model to obtain the prediction result.
[0158] An embodiment of the present invention also provides a database slow SQL prediction device based on a large model, including: at least one memory and at least one processor;
[0159] The at least one memory is used to store machine-readable programs;
[0160] The at least one processor is used to call the machine-readable program to implement the database slow SQL prediction method based on a large model described in the above embodiment.
[0161] An embodiment of the present invention also provides a computer-readable medium. Computer instructions are stored on the computer-readable medium, and when the computer instructions are executed by a processor, the processor is caused to execute the database slow SQL prediction method based on a large model described in the above embodiment. Specifically, a system or device equipped with a storage medium can be provided. Software program code for implementing the functions of any one of the above embodiments is stored on the storage medium, and the computer (or CPU or MPU) of the system or device is caused to read and execute the program code stored on the storage medium.
[0162] In this case, the program code read from the storage medium itself can implement the functions of any one of the above embodiments. Therefore, the program code and the storage medium storing the program code constitute a part of the present invention.
[0163] Examples of storage media for providing program code include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), magnetic tapes, non-volatile memory cards, and ROMs. Optionally, the program code can be downloaded from a server computer via a communication network.
[0164] In addition, it should be clear that not only can the functions of any one of the above embodiments be realized by executing the program code read by a computer, but also by causing an operating system or the like operating on the computer to perform part or all of the actual operations based on the instructions of the program code.
[0165] Furthermore, it can be understood that the program code read from the storage medium is written into the memory provided in an expansion board inserted into the computer or into the memory provided in an expansion unit connected to the computer, and then the CPU or the like installed on the expansion board or the expansion unit is caused to perform part and all of the actual operations based on the instructions of the program code, thereby realizing the functions of any one of the above embodiments.
[0166] The present invention has been shown and described in detail above through the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above-mentioned multiple embodiments, those skilled in the art can know that more embodiments of the present invention can be obtained by combining the code review means in the above different embodiments, and these embodiments are also within the protection scope of the present invention.
Claims
1. A method for predicting slow SQL statements in a database based on a large model, characterized in that: The implementation of this method includes the following steps: Data collection: extract historical data including SQL statements and their execution time from database logs; Data preprocessing: After data collection is completed, the collected SQL execution data is cleaned and normalized; Feature extraction: extract features that can reflect the execution performance of SQL statements after preprocessing; Model training: Train the extracted features to generate a slow SQL prediction model; Model evaluation: Evaluate the performance of the trained model on the test set and judge the quality of the model by calculating multiple indicators; Slow SQL prediction: predict new SQL statements and determine whether they are potential slow SQL statements based on the prediction results.
2. According to the large model-based database slow SQL prediction method of claim 1, it is characterized in that: The data collection specifically includes: Log parsing: It can parse log files of different database systems, including MySQL, PostgreSQL, and Oracle files; extract key information during the parsing process, including SQL statements, execution time, execution times, and error messages, and store them in a structured manner; Data collection: Real-time acquisition of SQL execution information through the monitoring interface of the database management system. Periodic collection of the latest SQL execution data through scheduled tasks or event triggering to ensure real-time and dynamic update of data. Data storage: Store the collected SQL execution data in an efficient database for subsequent processing and analysis.
3. The method for predicting slow SQL statements of a database based on a large model according to claim 1, characterized in that: The data preprocessing specifically includes: Data cleaning: remove invalid data, including empty SQL statements and records with execution time of zero; remove duplicate data, and only keep the latest or most representative execution record when the same SQL statement is executed multiple times; Data normalization: SQL statement normalization, replacing dynamic values in SQL statements with unified placeholders to reduce the dimension of feature space; execution time normalization, normalizing execution time to eliminate the impact of different dimensions on model training; Slow SQL Marking: Based on the preset execution time threshold, SQL statements whose execution time exceeds the threshold are marked as slow SQL.
4. The method for predicting slow SQL statements of a database based on a large model according to claim 1, characterized in that: The feature extraction specifically includes: (1) SQL statement characteristics, including: SQL statement length: the character length of the SQL statement; Query type: the type of SQL statement; Number of tables: the number of tables involved in the SQL statement; Number of JOIN operations: the number of JOIN operations included in the SQL statement; (2) Conditional characteristics, including: Number of WHERE clause conditions: The number of conditions contained in the WHERE clause in the SQL statement; Number of subqueries: the number of subqueries contained in the SQL statement; Number of aggregate functions: the number of aggregate functions used in the SQL statement; (3) Index features, including: Number of indexes used: the number of indexes used in the SQL statement; Index coverage: whether the fields involved in the SQL statement are covered by the index; (4) Natural language processing features, including: Bag of Words model: treats SQL statements as a series of words and converts them into feature vectors through word frequency statistics; TF-IDF: Calculates the importance of terms in SQL statements and generates feature vectors to reflect the impact of different terms on SQL execution performance; After feature extraction is completed, all features are combined into a feature matrix as data input for model training.
5. The method for predicting slow SQL statements of a database based on a large model according to claim 1, characterized in that: The model training specifically includes: Data partitioning: The preprocessed data set is divided into a training set and a test set in a ratio of 7:3; the training set is used to train the model, and the test set is used to evaluate the model performance; Model training: Use the data in the training set to train the selected SVM algorithm to generate a slow SQL prediction model. During the training process, the model parameters can be optimized through cross-validation to improve the generalization ability of the model. Model optimization: Use grid search to tune the model's hyperparameters and find the best parameter combination; use feature selection technology to select features that contribute more to the prediction results and eliminate redundant or invalid features to improve model performance.
6. The method for predicting slow SQL statements of a database based on a large model according to claim 1, characterized in that: The model evaluation,evaluation indicators include: Accuracy: indicates the ratio of the number of samples correctly predicted by the model to the total number of samples; Precision: indicates the proportion of samples predicted by the model as slow SQL that are actually slow SQL. This indicator can measure the accuracy of the model in predicting slow SQL. Recall rate: indicates the proportion of samples that are actually slow SQL statements that are correctly predicted by the model as slow SQL statements. This indicator can measure the model's coverage capability, that is, the model's ability to capture slow SQL statements. F1 score: The harmonic mean of precision and recall. When a balance between precision and recall is needed, the F1 score is a better evaluation metric. ROC-AUC curve: The AUC-ROC curve is drawn by calculating the true positive rate and false positive rate under different thresholds. The larger the area under the curve AUC, the better the model performance.
7. The method for predicting slow SQL statements of a database based on a large model according to claim 1, characterized in that: The slow SQL prediction process includes: Feature extraction: Extract features from new SQL statements to generate feature vectors consistent with the training process; Model prediction: Input the feature vector into the trained slow SQL prediction model to obtain the prediction result.
8. A database slow SQL prediction system based on a large model, characterized in that: It includes data collection module, data preprocessing module, feature extraction module, model training module, model evaluation module and slow SQL prediction module. Each module works together to realize automatic slow SQL identification and prediction. The data collection module is used to extract historical data including SQL statements and their execution time from the database log; The data preprocessing module is used to clean and normalize the collected SQL execution data after data collection is completed; The feature extraction module is used to extract features that can reflect the execution performance of the preprocessed SQL statement; The model training module is used to train the extracted features and generate a slow SQL prediction model; The model evaluation module is used to evaluate the performance of the trained model on the test set and to judge the quality of the model by calculating multiple indicators; The slow SQL prediction module is used to predict new SQL statements and determine whether they are potential slow SQL statements based on the prediction results; The system implements database slow SQL prediction based on a large model through any method described in claims 1 to 7.
9. A database slow SQL prediction device based on a large model, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is used to call the machine-readable program to implement the method described in any one of claims 1 to 7.
10. A computer-readable medium, characterized in that The computer readable medium stores computer instructions, which, when executed by a processor, cause the processor to execute the method according to any one of claims 1 to 7.