A sql time-consuming analysis method combining supervised and unsupervised machine learning
By combining supervised and unsupervised machine learning methods, clustering and classification of massive SQL data is performed, solving the problems of inaccurate classification and high consumption of human and material resources in existing technologies, and realizing efficient and accurate SQL time consumption analysis.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- NANJING FIBERHOME STARRYSKY CO LTD
- Filing Date
- 2024-03-29
- Publication Date
- 2026-06-02
AI Technical Summary
Existing SQL time consumption analysis methods are not accurate enough in classifying complex SQL, and the human experience base needs to be updated regularly, resulting in high consumption of human and material resources and making it difficult to meet the analysis needs of highly complex SQL in massive databases.
Combining supervised and unsupervised machine learning methods, this paper uses an agglomerative hierarchical clustering algorithm to cluster massive SQL data, and uses supervised machine learning algorithms to train a classification model to avoid human experience labeling. The training set data is updated regularly to adapt to the emergence of new SQL.
It achieves efficient classification of massive SQL data, reduces manpower and material resources consumption, improves the accuracy and adaptability of SQL time consumption analysis, and is suitable for predicting the time consumption of SQL in massive databases.
Smart Images
Figure CN118427239B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data optimization and analysis technology, and more specifically to a method for SQL time consumption analysis that combines supervised and unsupervised machine learning, which can be applied to massive database products that provide SQL time consumption analysis. Background Technology
[0002] SQL (Structured Query Language) execution time analysis aims to estimate the execution time of SQL queries based on various statistical analysis methods, thereby reminding database developers to adjust SQL statements or database design in advance to ensure the performance of the database system. However, with the advent of the big data era, the amount of data managed by database management systems is growing exponentially. The increased data volume queried by SQL statements leads to increased SQL execution time, potentially causing database system users to waste a significant amount of time waiting for SQL execution to complete, resulting in a decrease in the query performance of the database management system. Therefore, it is essential to propose an effective method for estimating SQL execution time.
[0003] Currently, the main methods for estimating and analyzing the time consumption of SQL queries in the publicly available literature are cost-based optimizer models and statistical analysis models.
[0004] Cost-based optimizer models use the execution plan of SQL statements to predict the execution time of SQL statements. However, these methods are intrusive to the database and SQL engine kernel. Some of these methods also require accurate estimation of real-time resources such as CPU, disk access, user concurrency, and network I / O in the database system.
[0005] The statistical analysis model-based method refers to analyzing or estimating the execution time of SQL statements by associating the feature vectors of SQL statements with their past performance on a database management system. By finding similar SQL statements in a statistical model built based on past SQL statements, the execution time of the new SQL statement to be executed can be predicted. The method of this invention belongs to the statistical analysis model-based approach. In existing statistical analysis model-based methods, the feature vectors used to characterize SQL consider common query keywords (such as group by, order by, etc.), but do not consider the features corresponding to complex SQL structures (such as subqueries, non-equivalence joins, etc.), resulting in inaccurate classification of complex SQL statements.
[0006] Secondly, existing statistical analysis methods, when using classification models to analyze the execution time of similar SQL queries, require labeling past SQL performance data based on human experience. Furthermore, as new SQL queries are issued and their complexity increases, this human experience base also needs to be updated periodically. The sheer volume and complexity of SQL queries issued to massive databases mean that the labeling step and the periodic updates to the human experience base in existing methods consume significant resources. Therefore, this paper proposes a SQL execution time analysis method that combines supervised and unsupervised machine learning. Summary of the Invention
[0007] This invention provides a SQL time consumption analysis method that combines supervised and unsupervised machine learning. It establishes a classification model required for SQL time consumption analysis by combining supervised and unsupervised machine learning methods. An unsupervised agglomerative hierarchical clustering algorithm is used to cluster massive amounts of SQL data, obtaining data with cluster labels. Subsequently, a supervised machine learning algorithm is used to train a model on the clustered data features with existing labels to obtain a classification model. Finally, an unsupervised clustering algorithm is used to cluster massive amounts of SQL feature data, obtaining feature data with cluster labels. This method avoids the significant manpower and resources wasted on manually labeling massive amounts of SQL data based on human experience, effectively solving the problems mentioned in the background technology.
[0008] The specific technical solution of this invention is as follows:
[0009] A method for SQL time consumption analysis combining supervised and unsupervised machine learning is proposed. The specific steps of the time consumption analysis method are as follows:
[0010] S1: Design a table structure to collect the required past SQL execution data, and collect the data records of this table as the data to be analyzed;
[0011] S2: Based on the collected data, feature vectors are extracted to build a feature database;
[0012] S3: Then, perform data preprocessing on the feature database in S2, including data cleaning, data normalization, and constructing a dataset;
[0013] S4: For the data processed in S3, use an agglomerative hierarchical clustering algorithm to cluster the feature data and output data with cluster labels;
[0014] S5: For the labeled data output in S4, build a classification model, and use a supervised machine learning algorithm to train and test the classification model, and calculate the reference values for the time consumption of various SQL queries in the past for the labeled data.
[0015] S6: By combining the classification model obtained in S5 and the time-consuming reference values of various past SQLs, after classifying the newly issued SQL to be executed, a time-consuming reference value for the newly issued SQL is given according to the classification result;
[0016] Since database users may issue new types of SQLs that have never appeared in past SQLs, it is necessary to update the SQL classification model regularly. The essence of updating the classification model is to update the training set data. Based on the updated training set, the classification model is updated according to S2 to S5.
[0017] In a preferred technical solution, in S2, for the table data collected in S1, the Hive UDF function is used to extract keywords in the SQL statement, as well as complex features such as the time partition number corresponding to the SQL query data, the number of occurrences of regular expression matching keywords, whether there is a non-equivalent Join and subquery, etc. as classification features.
[0018] In a preferred technical solution, in S3, for the classification features obtained in S2, the missing values and outliers in the feature data are replaced with 0, and then data normalization operations are performed on the normal data.
[0019] In a preferred technical solution, in S4, for the data set constructed in S3, the specific steps of using the agglomerative hierarchical clustering algorithm are as follows:
[0020] S4.1: Assume that there are n feature samples in the data set. In the initial state, each feature sample is marked as a separate class, then the initial classification labels corresponding to the n feature samples are L = {L0, L1, …, L n-1};
[0021] S4.2: Assume that the current samples to be clustered are m feature samples (0 < m <= n). Use the Euclidean distance formula to calculate the distance between the i-th feature sample and the j-th feature sample among the m feature samples in turn (0 <= i <= m, i + 1 <= j <= m). Assume that the distance threshold is t. When the distance between the i-th sample and the j-th sample is less than or equal to the distance threshold t, the two samples are marked as the same class, and the class labels of the i-th sample and the j-th sample are replaced;
[0022] S4.3: After calculating the distances between the m feature samples pairwise, assume that the m samples are aggregated into p new classes (0 <= p <= m). For each class in the p classes of samples, recalculate the average value of all feature samples in the class as the representative feature value of the class, so as to obtain p new feature samples;
[0023] S4.4: Determine whether to stop clustering: If all feature samples have not yet been clustered into one class, and there are two samples in the current p new feature samples whose distance is less than or equal to t, then use the current p new feature samples as input samples for the next round of clustering.
[0024] S4.5: Repeat steps S4.2 and S4.4 for multiple rounds of clustering until the distance between samples is greater than t or all samples are clustered into one class, then the clustering ends, resulting in n feature samples with classification labels.
[0025] In a preferred technical solution, in S5, for the n feature samples with clustering labels, i.e. category labels, obtained in S4, the samples are divided into a training subset and a validation subset according to the proportion. The training subset is used to train the model, and the validation subset is used to verify the performance of the model. Then, a supervised random forest classification algorithm is used to fit and construct a classification model based on the training subset, and the classification performance of the subset classification model is verified.
[0026] Meanwhile, the n feature samples with clustering labels (i.e., category markers) obtained in S4 are statistically analyzed to obtain the average time consumption value corresponding to each type of SQL sample.
[0027] In a preferred embodiment, step S6 includes the following sub-steps:
[0028] S6.1: For newly issued SQL statements, call the Hive UDF function to extract keywords from the SQL statement, as well as complex features such as the number of time partitions corresponding to the SQL query data, the number of occurrences of keywords in regular expression matching, and whether there are non-equal value JOINs and subqueries, as classification features;
[0029] S6.2: Use the classification features of the newly issued SQL as the input vector of the classification model obtained in S5, and obtain SQL feature samples with classification labels after classification by the classification model;
[0030] S6.3: For the SQL feature samples with classification labels obtained in S6.2, based on the average time consumption value of each type of SQL executed in the past obtained in S5, the average time consumption value of the same type of past SQL for newly issued SQL is obtained, and this average time consumption value is pushed to the user as the time consumption reference value of newly issued SQL.
[0031] A preferred technical solution involves updating the classification model in S6, with the following specific steps:
[0032] (1) By writing a script to collect data regularly, a new batch of data is collected as a new training set according to S2 after each time period;
[0033] (2) For the newly collected training set data, retrain the classification model according to S3 to S5, and then replace the old classification model.
[0034] Compared with the prior art, the present invention has the following beneficial effects:
[0035] 1. This invention establishes the classification model required for SQL time consumption analysis by combining supervised and unsupervised machine learning methods. First, it uses an unsupervised agglomerative hierarchical clustering algorithm to cluster massive SQL data and obtain feature data with cluster labels. This avoids the problem of consuming a lot of manpower and resources by labeling massive SQL data based on human experience.
[0036] 2. This invention uses a supervised machine learning algorithm to train a classification model on the clustering data features of existing labels. Compared with the method of labeling massive SQL data based on human experience, the method of labeling SQL data based on clustering algorithm does not require regular updates to the human experience database and the model update speed is faster. For application scenarios of time-consuming analysis of SQL in massive databases, this invention is more practical and feasible. Attached Figure Description
[0037] Figure 1 This is a flowchart of the SQL time consumption analysis method of the present invention;
[0038] Figure 2 This is the feature data analysis diagram of the agglomerative hierarchical clustering algorithm in step 3 of this invention;
[0039] Figure 3 This is a flowchart of the SQL time consumption reference value analysis process of the present invention. Detailed Implementation
[0040] The embodiments of the present invention will be described in further detail below with reference to the accompanying drawings and examples. The following examples are for illustrative purposes only and should not be construed as limiting the scope of the invention.
[0041] Example 1:
[0042] like Figure 1-3 As shown, this invention provides a SQL time consumption analysis method that combines supervised and unsupervised machine learning. The specific steps of the time consumption method are as follows:
[0043] To provide reference values for the execution time of SQL statements based on past SQL execution data, this invention fully mines the feature vectors of SQL statements and combines supervised and unsupervised machine learning methods to design a method for classifying similar SQL statements to further statistically analyze the execution time of historical similar SQL statements. (See attached...) Figure 1 The technical solution provided by this invention includes the following steps:
[0044] Step 1: Collect past SQL execution data;
[0045] 1.1 Design a table structure in the database management system to collect the required SQL execution data, recording the SQL execution time, the content of the SQL statement itself, the SQL execution status, etc.
[0046] 1.2 Collect the data records of this table from the relevant massive database management system as the data to be analyzed.
[0047] Step 2: Based on the collected data, extract feature vectors to build a feature database;
[0048] 2.1. Based on the table data collected in Step 1, a massive database management system is built to store and manage the collected data. The system uses Hive UDF functions to extract traditional features from SQL statements (such as group by, join, direct) and complex features such as the number of time partitions corresponding to the SQL query data, the frequency of occurrence of regular expression matching keywords, and the presence of non-equality joins and subqueries as classification features. Specific classification features are shown in Table 1. When the SQL statement contains a certain keyword or predicate, the corresponding feature value is 1; otherwise, the corresponding feature value is 0.
[0049] Table 1 shows the classification characteristics of SQL.
[0050]
[0051]
[0052]
[0053] Step 3: Perform data preprocessing on the feature database, including data cleaning, data normalization, and dataset partitioning.
[0054] 3.1 For the classification features obtained in step (2.1), replace the missing values and outliers in the feature data with 0;
[0055] 3.2. For the normal data obtained in step (3.1), continue with the data normalization operation to obtain a data set that can be used to construct a classification model. In order to normalize all feature data to values between [0, 1], the present invention obtains the maximum value of each feature in the training set, and divides the feature values whose initial value ranges are not within [0, 1] by the historical maximum value of their corresponding features, so that the feature values are normalized to the data range of [0, 1]. Specifically, the features whose initial value ranges are not within [0, 1] are likeCnt, rlikeCnt, regexpCnt, inValueNum, regValueNum, regexpExtractCnt, regexpReplaceCnt, regexpSubstrCnt, stringKeyMatchCnt, and partitionNums in Table 1.
[0056] Step Four: As shown in the appendix Figure 2 , for the data processed in step three, use the agglomerative hierarchical clustering algorithm to cluster the feature data and output the data with clustering labels;
[0057] 4.1. For the data set output in step (3.2), assume that there are n feature samples in the data set. In the initial state, each feature sample is marked as a separate class, so the initial classification labels corresponding to the n feature samples are L = {L0, L1, …, Ln-1}.
[0058] 4.2. Assume that the current samples to be clustered are m feature samples (0 < m <= n). Use the Euclidean distance formula to calculate the distance between the i-th feature sample and the j-th feature sample among the m feature samples in turn (0 <= i <= m, i + 1 <= j <= m). Let the distance threshold be t. When the distance between the i-th sample and the j-th sample is less than or equal to the distance threshold t, mark the two samples as the same class and replace the class labels of the i-th sample and the j-th sample.
[0059] 4.3. After calculating the distances between all pairs of the m feature samples, assume that the m samples are aggregated into p classes (0 < p <= m). For each class among the p classes of samples, recalculate the average value of all feature samples in this class as the representative feature value of this class, so as to obtain p new feature samples.
[0060] In the first example of the present invention, the calculated values of aggregating two samples in a certain class to obtain new feature samples are shown in Table 2. There are only two feature samples in this class in total. Calculate the average value of each feature of these two feature samples to obtain a new feature sample. This new feature sample is used as the representative feature sample of this class and as the input feature for the next round of aggregation operation.
[0061] Table 2 shows an example of how two samples of a certain type are aggregated to obtain new feature samples.
[0062]
[0063]
[0064]
[0065] 4.4 Determine whether to stop clustering: If all feature samples are not clustered into one class, and there are two samples in the current p new feature samples whose distance is less than or equal to t, then use the current p new feature samples as input samples for the next round of clustering, and then repeat steps (4.2) to (4.4) for the next round of clustering.
[0066] In this invention example, the distance threshold t is set to 0.05, and steps (4.2) and (4.4) are executed repeatedly to perform multiple rounds of clustering until the distance between samples is greater than t or all samples are clustered into one class, and then the clustering ends, resulting in n feature samples with classification labels.
[0067] Step 5: Use supervised machine learning algorithms to train and test the classification model on the labeled data output in Step 4 to obtain the classification model, and calculate the reference values of the time consumption of various SQL queries in the past for the labeled data.
[0068] 5.1 For the feature samples with clustering labels (i.e., category markers) obtained in step four, the samples are divided into a training subset and a validation subset in a 7:3 ratio. The training subset is used to train the model, and the validation subset is used to verify the model's performance. Subsequently, a supervised random forest classification algorithm is used to fit a classification model based on the training subset, and the classification performance of the model is verified based on the validation subset. In Example 1 of this invention, the n_estimators parameter of the random forest classification algorithm is set to 100, indicating that 100 basic weak classifiers are used for classification prediction.
[0069] 5.2. Statistical analysis is performed on the feature samples with clustering labels (i.e., category markers) obtained in step four to obtain the average time consumption value corresponding to each type of SQL sample. In this invention example, the time consumption values of various past SQL queries in the training set are shown in Table 3.
[0070] Table 3. Time consumption values of various types of past SQL queries in a training set, as an example of the invention.
[0071] Category Tags The time (in seconds) for each type of past SQL query. 0 2.36 1 3.41 2 79.19 3 29.58 4 2.35 5 94.23
[0072] Step Six: As attached Figure 3Combining the classification model obtained in step five with the time consumption reference values of various types of SQL in the past, the newly issued SQL to be executed is classified, and the time consumption reference value of the newly issued SQL is given according to the classification results.
[0073] 6.1 For newly issued SQL statements, call the Hive UDF function in the massive database management system to extract traditional features such as keywords (e.g., group by, join, direct) and complex features such as the number of time partitions corresponding to the SQL query data, the number of occurrences of keywords in regular expression matching, and whether there are non-equality joins and subqueries as classification features.
[0074] 6.2. Use the classification features of the newly issued SQL as input to the classification model obtained in step (5.1), and use the classification model to classify to obtain SQL feature samples with classification labels.
[0075] 6.3 For the SQL feature samples with classification labels obtained in step (6.2), based on the average time consumption value of each type of SQL that has been executed in the past obtained in step (5.2), the average time consumption value of the same type of past SQL for newly issued SQL is obtained, and this average time consumption value is pushed to the user as the time consumption reference value for newly issued SQL.
[0076] In this embodiment of the invention, the user sends two SQL statements to the classifier. The classifier performs steps (6.1) to (6.3) and returns the time consumption values of similar SQL statements in the past to the user within 2 seconds. The specific classification results and time consumption values are shown in Table 4. The user can adjust system resources or optimize SQL statements based on the time consumption values of similar SQL statements in the past.
[0077] Table 4 shows the classification results and time consumption values of the two test SQL statements in the example.
[0078] SQL label Category Tags The time (in seconds) for each type of past SQL query. 1 0 2.36 2 3 29.58
[0079] Because database management system users may issue new types of SQL that have not appeared in previous SQL queries, the SQL classification model needs to be updated periodically. Updating the classification model essentially involves updating the training set data. Based on the updated training set, steps two through five are performed to complete the update of the classification model.
[0080] By writing a script to collect data regularly, a new batch of data is collected every month, following step two, to serve as a new training set.
[0081] For the newly collected training set data, the classification model is retrained according to steps three through five, and then the old classification model is replaced. This invention stores the classification model on HDFS (Hadoop Distributed File System) and uses the `-put` command to replace the classification model in HDFS.
[0082] The embodiments of the present invention are given for the purposes of illustration and description, and are not intended to be exhaustive or to limit the invention to the forms disclosed. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features.
Claims
1. A method for SQL time consumption analysis combining supervised and unsupervised machine learning, characterized in that, The specific steps of the time consumption analysis method are as follows: S1: Design a table structure to collect the required past SQL execution data, and collect the data records of this table as the data to be analyzed; S2: Based on the collected data, feature vectors are extracted to build a feature database; S3: Then, perform data preprocessing on the feature database in S2, including data cleaning, data normalization, and constructing a dataset; S4: For the data processed in S3, use an agglomerative hierarchical clustering algorithm to cluster the feature data and output data with cluster labels; S5: For the labeled data output in S4, build a classification model, and use a supervised machine learning algorithm to train and test the classification model, and calculate the reference values for the time consumption of various SQL queries in the past for the labeled data. S6: By combining the classification model obtained in S5 and the time consumption reference values of various types of SQL in the past, after classifying the newly issued SQL to be executed, the time consumption reference value of the newly issued SQL is given according to the classification result. S6 includes the following sub-steps: S6.1: For newly issued SQL statements, call Hive UDF functions to extract keywords from the SQL statements, as well as complex features such as the number of time partitions corresponding to the SQL query data, the number of occurrences of keywords in regular expression matching, and whether there are non-equality joins and subqueries, as classification features; S6.2: Use the classification features of the newly issued SQL as the input vector of the classification model obtained in S5, and obtain SQL feature samples with classification labels after classification by the classification model; S6.3: For the SQL feature samples with classification labels obtained in S6.2, based on the average time consumption value of each type of SQL executed in the past obtained in S5, the average time consumption value of the same type of past SQL for newly issued SQL is obtained, and this average time consumption value is pushed to the user as the time consumption reference value of newly issued SQL. Since database users may issue new types of SQL that have not appeared in previous SQL queries, the SQL classification model needs to be updated regularly. The essence of updating the classification model is to update the training set data. Based on the updated training set, the classification model is updated according to S2 to S5.
2. The SQL time consumption analysis method combining supervised and unsupervised machine learning as described in claim 1, characterized in that: In S2, for the table data collected in S1, Hive UDF functions are used to extract keywords in the SQL statement and complex features such as the number of time partitions corresponding to the SQL query data, the number of occurrences of keywords in regular expression matching, and whether there are non-equality joins and subqueries as classification features.
3. The SQL time consumption analysis method combining supervised and unsupervised machine learning as described in claim 2, characterized in that: In S3, for the classification features obtained in S2, missing values and outliers in the feature data are replaced with 0, and then data normalization is performed on the normal data.
4. The SQL time consumption analysis method combining supervised and unsupervised machine learning as described in claim 1, characterized in that: In S4, the specific steps of using the agglomerative hierarchical clustering algorithm on the dataset constructed in S3 are as follows: S4.1: Suppose there are n feature samples in the dataset. Initially, each feature sample is labeled as a separate class. Then the initial classification labels for the n feature samples are L = {L0, L1, ..., L...} n-1 }; S4.2: Let the current samples to be clustered be m feature samples, where 0 < m <= n. Use the Euclidean distance formula to calculate the distance between the i-th and j-th feature samples among the m feature samples in turn, where 0 <= i <= m and i + 1 <= j <= m. Let the distance threshold be t. When the distance between the i-th and j-th samples is less than or equal to the distance threshold t, mark the two samples as the same class and replace the class labels of the i-th and j-th samples. S4.3: After calculating the distances pairwise for the m feature samples, assume that the m samples are aggregated into p new classes, where 0 <= p <= m. For each of the p classes of samples, recalculate the average value of all the feature samples in the class as the representative feature value of the class, thus obtaining p new feature samples. S4.4: Determine whether to stop clustering: If not all the feature samples have been clustered into one class and there is a distance less than or equal to t between two samples among the current p new feature samples, then use the current p new feature samples as the input samples for the next round of clustering. S4.5: Loop through S4.2 and S4.4 to perform multiple rounds of clustering until the distance between samples is greater than t or all samples are clustered into one class, and then end the clustering to obtain n feature samples with classification labels.
5. The SQL time consumption analysis method combining supervised and unsupervised machine learning as described in claim 4, characterized in that: In S5, for the n feature samples with clustering labels (i.e., class labels) obtained in S4, divide the samples into a training subset and a validation subset according to a ratio. The training subset is used to train the model, and the validation subset is used to verify the performance of the model. Subsequently, use the supervised random forest classification algorithm to fit and construct a classification model based on the training subset, and verify the classification performance of the classification model with the validation subset. At the same time, count the n feature samples with clustering labels (i.e., class labels) obtained in S4 to obtain the average time-consuming value corresponding to each class of SQL samples.
6. The SQL time consumption analysis method combining supervised and unsupervised machine learning as described in claim 1, characterized in that: The update of the classification model in S6 is as follows: (1) By writing a script to collect data regularly, every time a time period passes, collect a new batch of data as a new training set according to S2. (2) For the newly collected training set data, retrain the classification model according to S3 to S5, and then replace the old classification model.