Abnormal SQL (Structured Query Language) analysis method and device for database, electronic equipment, storage medium and program product

Abstract

The invention provides an abnormal SQL analysis method and device of a database, electronic equipment, a storage medium and a program product, and relates to the technical field of data processing, a class of support vector machine models are preset through sample feature data point sequence training, and an abnormal SQL recognition model is obtained; the abnormal SQL recognition model can fully learn the distribution condition of the normal sample feature data points, and the accuracy and efficiency of determining the abnormal feature data points are improved. The preset exponential smoothing model is trained according to the normal sample feature data point sequence, so that the prediction model can fully acquire the development and change trend of the feature data points after the database executes the normal SQL, and the efficiency and accuracy of predicting the data points are improved. By means of the abnormal SQL recognition model and the abnormal SQL prediction model, automatic multiple confirmation of the abnormal feature data points is achieved, the accuracy and efficiency of determining the abnormal feature data points are improved, and the processing efficiency of the abnormal SQL of the database is improved.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a method, device, electronic device, storage medium and program product for analyzing abnormal SQL of a database. Background Art

[0002] The current database operation and maintenance adopts the traditional operation and maintenance mode, which collects database server information and general database information from each database system, such as server CPU and memory usage, database space usage, etc., and generates alarms by setting thresholds. It also collects database logs and directly publishes alarms in database logs. When database performance problems occur, database administrators usually need to judge and solve the problems based on their experience, by integrating multiple factors such as database CPU, memory, input / output (IO), database logs, and structured query language (SQL) query plans.

[0003] The existing monitoring platform has many monitoring items, monitoring various indicators, events and logs. Sometimes too much alarm information will lead to serious database problems not being discovered in time. System failures caused by sudden changes in production system database performance are usually not discovered in time. Alarms generated by database performance problems usually lag behind alarms on the business response side, and it takes a long time to solve the problem. Because there are many root causes of database performance problems and the external dependencies required to solve the problems are strong, professional and experienced database administrators are required to handle them in a timely manner; when emergency processing is required, multiple permissions verification is required for login due to security requirements, which is cumbersome and time-consuming.

[0004] From the above, it can be seen that the existing processing efficiency of abnormal SQL in the database is low. Summary of the invention

[0005] The present application provides a method, device, electronic device, storage medium and program product for analyzing abnormal SQL of a database, so as to solve the defect of low processing efficiency of abnormal SQL of a database in the prior art and improve the processing efficiency of abnormal SQL of a database.

[0006] The present application provides an abnormal SQL analysis method for a database, comprising: obtaining feature data points after a database executes a structured query language SQL within a preset time period, and obtaining a time-ordered feature data point sequence; inputting the feature data point sequence into an abnormal SQL recognition model, and obtaining abnormal feature data points output by the abnormal SQL recognition model; the abnormal SQL recognition model is trained based on a sample feature data point sequence, a normal sample feature data point label, and an abnormal sample feature data point label on the basis of a preset first-class support vector machine model; obtaining historical feature data points and weights of the historical feature data points of the abnormal feature data points based on the feature data point sequence, inputting the historical feature data points and the weights of the historical feature data points into a prediction model, and obtaining predicted data points corresponding to the abnormal feature data points output by the prediction model under normal operation; the prediction model is trained based on a normal sample feature data point sequence on the basis of a preset exponential smoothing model; when the difference between the predicted data point and the abnormal feature data point is greater than a set threshold, analyzing the abnormal cause of the abnormal feature data point.

[0007] In one embodiment, the abnormal SQL recognition model is used to obtain abnormal feature data points: perform abnormality detection on the feature data points to identify the initial abnormal feature data points; determine the front kurtosis of the initial abnormal feature data points based on the feature data points that precede the initial abnormal feature data points and the average value of the feature data points; determine the back kurtosis of the initial abnormal feature data points based on the feature data points that follow the initial abnormal feature data points and the average value of the feature data points; when the difference between the front kurtosis and the back kurtosis is greater than the set kurtosis difference, determine that the initial abnormal feature data point is an abnormal feature data point.

[0008] In one embodiment, the abnormal SQL recognition model is used to obtain abnormal feature data points: perform abnormality detection on the feature data points to identify initial abnormal feature data points; determine the interquartile range of the feature data point sequence based on the first quartile and the third quartile of the feature data point sequence; determine the lower limit of the range of the feature data points based on the interquartile range and the first quartile; determine the upper limit of the range of the feature data points based on the interquartile range and the third quartile; determine the normal range of the feature data points based on the upper limit and the lower limit; if the initial abnormal feature data point is not within the normal range, determine the initial abnormal feature data point as an abnormal feature data point.

[0009] In one embodiment, historical feature data points and weights of historical feature data points of abnormal feature data points are obtained based on a feature data point sequence, including: determining a time window length of the historical feature data points based on the number of abnormal feature data points, the complexity of the abnormal feature data points, and the time span of the abnormal feature data points, and obtaining historical feature data points from the feature data point sequence based on the time window length; and determining a weight of each historical feature data point based on a weight attenuation coefficient, a time position of the historical feature data point, and the number of the historical feature data points.

[0010] In one embodiment, the abnormal SQL recognition model is determined based on the following steps: obtaining historical database performance indicator data and historical resource usage data of a sample database; preprocessing, format conversion and feature extraction of the historical database performance indicator data and historical resource usage data to obtain sample feature data points to obtain a time-ordered sample feature data point sequence, obtaining normal sample feature data point labels and abnormal sample feature data point labels of the sample feature data point sequence, and obtaining a labeled sample feature data point sequence; training a preset first-class support vector machine model based on the labeled sample feature data point sequence until the classification error of the hyperplane of the preset first-class support vector machine model for normal sample feature data points and abnormal sample feature data points is less than a set error, thereby obtaining an abnormal SQL recognition model, wherein the classification error is determined based on the loss function of the preset first-class support vector machine model, and the loss function is determined based on the normal vector, regularization parameter and slack variable of the hyperplane.

[0011] In one embodiment, the predicted model is determined based on the following steps: removing abnormal sample feature data points in the sample feature data point sequence to obtain a normal sample feature data point sequence; training a preset exponential smoothing model based on the normal sample feature data point sequence until the mean square error between the sample prediction value output by the preset exponential smoothing model and the corresponding normal sample feature data point is less than the set mean square error, thereby obtaining a prediction model.

[0012] The present application also provides an abnormal SQL analysis device for a database, comprising: a feature data point sequence acquisition module, used to acquire feature data points after the database executes the structured query language SQL within a preset time period, and obtain a time-ordered feature data point sequence; an abnormal feature data point determination module, used to input the feature data point sequence into an abnormal SQL recognition model, and obtain abnormal feature data points output by the abnormal SQL recognition model; the abnormal SQL recognition model is trained based on a sample feature data point sequence, normal sample feature data point labels and abnormal sample feature data point labels on the basis of a preset class of support vector machine models; a predicted data point determination module, used to acquire historical feature data points and weights of historical feature data points of abnormal feature data points based on the feature data point sequence, input the historical feature data points and the weights of the historical feature data points into a prediction model, and obtain the predicted data points corresponding to the abnormal feature data points output by the prediction model under normal operation; the prediction model is trained based on a normal sample feature data point sequence on the basis of a preset exponential smoothing model; and an analysis module, used to analyze the abnormal cause of the abnormal feature data point when the difference between the predicted data point and the abnormal feature data point is greater than a set threshold.

[0013] The present application also 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, an abnormal SQL analysis method for any of the above-mentioned databases is implemented.

[0014] The present application also provides a non-transitory computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the abnormal SQL analysis method for any of the above-mentioned databases is implemented.

[0015] The present application also provides a computer program product, including a computer program, which implements any of the above-mentioned abnormal SQL analysis methods for a database when executed by a processor.

[0016] The abnormal SQL analysis method, device, electronic device, storage medium and program product of the database provided by the present application preset a type of support vector machine model through sample feature data point sequence training to obtain an abnormal SQL recognition model, so that the abnormal SQL recognition model can fully learn the distribution of normal sample feature data points, and improve the accuracy and efficiency of determining abnormal feature data points. According to the normal sample feature data point sequence training preset exponential smoothing model, a prediction model is obtained, so that the prediction model can fully obtain the development and change trend of the feature data points after the database executes normal SQL, which is conducive to improving the efficiency and accuracy of predicting data points. The present application realizes automatic multiple confirmation of abnormal feature data points through abnormal SQL recognition model and prediction model, improves the accuracy and efficiency of determining abnormal feature data points, and is conducive to improving the processing efficiency of abnormal SQL of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in the present application or the prior art, a brief introduction will be given below to the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0018] Figure 1 This is one of the flow charts of the abnormal SQL analysis method for a database provided in this application.

[0019] Figure 2 This is the second flow chart of the abnormal SQL analysis method for a database provided by this application.

[0020] Figure 3 This is the third flow chart of the abnormal SQL analysis method for a database provided by this application.

[0021] Figure 4 It is a structural diagram of the abnormal SQL analysis device for the database provided by this application.

[0022] Figure 5 It is a structural schematic diagram of the electronic device provided by this application. DETAILED DESCRIPTION

[0023] In order to make the purpose, technical solutions and advantages of this application clearer, the technical solutions in this application will be clearly and completely described below in conjunction with the drawings in this application. Obviously, the described embodiments are part of the embodiments of this application, not all of them. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0024] Combine the following Figure 1-Figure 5 The present invention describes a method, device and electronic device for analyzing abnormal SQL in a database.

[0025] Figure 1 This is one of the flow charts of the abnormal SQL analysis method for the database provided by this application, such as Figure 1 As shown, the abnormal SQL analysis method of the database includes steps S100 to S400, and each step is specifically as follows.

[0026] S100: Acquire characteristic data points after executing structured query language SQL in a database within a preset time period, and obtain a sequence of characteristic data points sorted in time.

[0027] A series of database performance indicators and system resource usage of the database within a preset time period are collected regularly to obtain database indicators. Database indicators include but are not limited to: redo write times, log file parallel write events, redo switching times (the frequency of database switching between different redo log files), user commits, transaction rollbacks, number of system calls, number of database connections, wait event distribution, etc. Among them, the log file parallel write event is an event in which the log writer (LGWR) process writes the content in the redo buffer to the redo log files in parallel. Store read and write indicator data - CPU, memory usage data. After collecting database indicators, Kafka can be used as a message queue to quickly transmit database indicators to subsequent data processing systems.

[0028] Data cleaning is used to remove invalid, erroneous or abnormal values ​​in database indicators, for example, deleting duplicate records, filling missing values, correcting erroneous values, etc. Database indicators are converted into a unified format for subsequent analysis, such as numerical scaling, classification or coding. According to analysis requirements, database indicators are mathematically or statistically transformed, such as logarithmic transformation, discretization, etc. Finally, database indicators from different sources are merged together to form a unified set of database indicators for subsequent analysis.

[0029] Extract key indicator data generated by the database after executing each SQL from the database indicator set, such as physical reads, logical reads, execution times, CPU time, and execution time. Convert the key indicator data corresponding to each SQL execution time point into a feature vector to obtain feature data points, and sort the feature data points according to execution time to obtain a feature data point sequence.

[0030] S200: Inputting a sequence of characteristic data points into an abnormal SQL recognition model to obtain abnormal characteristic data points output by the abnormal SQL recognition model.

[0031] The abnormal SQL recognition model is trained based on a preset first-class support vector machine model and based on a sample feature data point sequence, normal sample feature data point labels, and abnormal sample feature data point labels.

[0032] The abnormal SQL recognition model is determined based on the following steps: obtaining historical database performance indicator data and historical resource usage data of a sample database; preprocessing, format conversion and feature extraction of the historical database performance indicator data and historical resource usage data to obtain sample feature data points to obtain a time-ordered sample feature data point sequence, obtaining normal sample feature data point labels and abnormal sample feature data point labels of the sample feature data point sequence, and obtaining a labeled sample feature data point sequence; training a preset one-class support vector machine model based on the labeled sample feature data point sequence until the classification error of the hyperplane of the preset one-class support vector machine model for normal sample feature data points and abnormal sample feature data points is less than a set error, thereby obtaining an abnormal SQL recognition model, and the classification error is determined based on the loss function of the preset one-class support vector machine model, and the loss function is determined based on the normal vector, regularization parameter and slack variable of the hyperplane.

[0033] Obtain historical database performance indicator data and historical resource usage data of the sample database to obtain a sample database indicator set. Perform preprocessing such as data cleaning, format conversion, and feature extraction on the sample database indicator set to obtain a sample feature data point sequence. Determine the label of each sample feature data point in the sample feature data point sequence (for example, a normal sample feature data point label or an abnormal sample feature data point label) to obtain a sample feature data point sequence carrying a label.

[0034] The preset one-class support vector machine (One-Class SVM) model is trained according to the sequence of sample feature data points carrying labels, so that the preset One-Class SVM model can learn the data distribution of normal sample feature data points (feature data points with normal sample feature data point labels) to identify abnormal sample feature data points later. During the training process, the preset One-Class SVM model will try to find an optimal hyperplane to separate normal sample feature data points from abnormal sample feature data points. Among them, the normal sample feature data points are as far away from the hyperplane as possible, and the distance from all sample feature data points to the hyperplane is less than 1, and there is no misjudgment.

[0035] The loss function of the preset one-class support vector machine model is determined according to the normal vector, regularization parameter and slack variable of the hyperplane. The calculation formula of the loss function is as follows.

[0036] ; in, To preset the classification error of a class of support vector machine models, is the normal vector of the hyperplane, is the regularization parameter, is the slack variable, is the number of sample feature data points.

[0037] When the classification error of the preset one-class support vector machine model is less than the set error, it is determined that the training of the preset one-class support vector machine model is completed, and an abnormal SQL recognition model is obtained.

[0038] The present application trains the preset first-class support vector machine model according to the sequence of sample feature data points carrying labels to obtain an abnormal SQL recognition model, thereby improving the efficiency and accuracy of identifying abnormal feature data points. The loss function is determined according to the normal vector, regularization parameter and slack variable of the hyperplane, thereby improving the fault tolerance of the abnormal SQL recognition model and further improving the accuracy of the abnormal SQL recognition model in classifying feature data points.

[0039] like Figure 3As shown, the trained abnormal SQL recognition model is used to perform anomaly detection on each feature data point in the feature data point sequence to identify the initial abnormal feature data point. For example, the database, storage, CPU, memory and other indicator data of each feature data point are analyzed to determine whether the SQL statement corresponding to each feature data point is a normal SQL statement. If the SQL statement is classified as an abnormal SQL statement (initial abnormal feature data point), the database may have security risks or performance problems. The abnormal SQL recognition model corrects the initial abnormal feature data point and identifies the abnormal feature data point in the initial abnormal feature data point. For example, the kurtosis data or interquartile range of the initial abnormal feature data point is obtained. If the kurtosis data or interquartile range of the initial abnormal feature data point meets the set conditions, it means that the initial abnormal feature data point is an abnormal feature data point.

[0040] S300: Obtain historical feature data points and weights of the historical feature data points of abnormal feature data points based on the feature data point sequence, input the historical feature data points and the weights of the historical feature data points into the prediction model, and obtain the prediction data points corresponding to the abnormal feature data points output by the prediction model under normal operation.

[0041] The prediction model is trained based on a preset exponential smoothing model and a sequence of normal sample feature data points.

[0042] The prediction model is determined based on the following steps: removing abnormal sample feature data points in the sample feature data point sequence to obtain a normal sample feature data point sequence; training a preset exponential smoothing model based on the normal sample feature data point sequence until the mean square error between the sample prediction value output by the preset exponential smoothing model and the corresponding normal sample feature data point is less than the set mean square error, thereby obtaining a prediction model.

[0043] According to the above steps, the sample feature data point sequence, the normal sample feature data point label and the abnormal sample feature data point label are obtained. According to the normal sample feature data point label and the abnormal sample feature data point label, the normal sample feature data point and the abnormal sample feature data point in the sample feature data point sequence are identified. The abnormal sample feature data points in the sample feature data point sequence are removed to obtain the normal sample feature data point sequence. The normal sample feature data point sequence represents the changes in the key indicators corresponding to the database after the normal SQL is executed.

[0044] The preset exponential smoothing model (ESM) is trained based on the sequence of normal sample feature data points, so that the preset exponential smoothing model can better understand the development and change trend of the sample feature data points after the sample database executes SQL. The preset exponential smoothing model predicts the sample prediction value after the SQL is executed at the sample time point according to the development and change trend of the sample feature data points. The mean square error is calculated based on the normal sample feature data points and sample prediction values ​​corresponding to the sample prediction value. The calculation formula of the mean square error is as follows.

[0045] ; in, is the mean square error, is the number of sample prediction values, is the normal sample feature data point corresponding to the sample prediction value, is the sample prediction value.

[0046] The mean square error of all sample prediction values ​​is calculated according to the above formula. If the mean square error is less than the set mean square error, it is determined that the training of the preset exponential smoothing model is completed and the prediction model is obtained.

[0047] The present application trains a preset exponential smoothing model based on a sequence of normal sample feature data points, thereby enabling the prediction model to comprehensively obtain the development and change trends of normal SQL operations of the database, which is beneficial to improving the accuracy of determining the prediction data points.

[0048] The prediction model is based on the preset exponential smoothing model (ESM) model and is obtained after training with a sequence of normal sample feature data points. The prediction model can accurately understand the development and change trend of SQL operations in the database.

[0049] In the feature data point sequence, the feature data point that precedes the abnormal feature data point is used as the historical feature data point corresponding to the abnormal feature data point. The historical feature data points of the current abnormal feature data point are input into the prediction model. The prediction model predicts the predicted data point corresponding to the current abnormal feature data point based on the development and change trend of the SQL operation of the database and the historical feature data points. The predicted data point is the level that the database may reach when executing normal SQL.

[0050] S400: When the difference between the predicted data point and the abnormal feature data point is greater than a set threshold, analyzing the abnormal cause of the abnormal feature data point.

[0051] Calculate the difference between the predicted data point and the abnormal feature data point. If the difference is greater than the set threshold, it means that the abnormal feature data point exceeds the prediction range, that is, the abnormal feature data point is caused by an abnormal situation rather than a natural fluctuation of the data. At this point, proceed to the next step to confirm the abnormal cause of the abnormal feature data point. For example, a large number of user registration failures suddenly occur in the database at a certain point in time. At this point, the database will immediately input the historical feature data points before this abnormal feature data point into the prediction model and generate a predicted data point corresponding to the abnormal feature data point. Compare the abnormal feature data point with the predicted data point. If the number of user registration failures corresponding to the abnormal feature data point far exceeds the number of user registration failures corresponding to the predicted data point, further analyze the abnormal cause corresponding to the abnormal feature data point, such as server failure, malicious attack, etc.

[0052] like Figure 3 As shown, according to the abnormal SQL recognition model, determine whether the current abnormal feature data point obviously deviates from the feature data point sequence. Obtain the predicted data point according to the prediction model, and determine whether the abnormal feature data point exceeds the range of the predicted data point. If the abnormal feature data point meets both of the above conditions, it means that the abnormal feature data point is caused by an abnormal situation rather than the natural fluctuation of the data. Further analyze the abnormal cause of the abnormal feature data point.

[0053] Analyzing the abnormal causes of abnormal feature data points mainly includes: conducting in-depth analysis of the database's wait events to identify the wait events that cause database performance bottlenecks. Conducting in-depth analysis of the sessions corresponding to the abnormal feature data points, including session status, executed SQL statements and other information, to determine whether the sessions are normal. Analyzing the SQL indicators of the abnormal feature data points, including SQL execution time, CPU and IO usage, etc., to determine whether there are performance issues with the SQL statements. Analyzing the database objects corresponding to the SQL statements of the abnormal feature data points, including information such as the object's structure and index status, to determine whether there are design problems with the objects. Through the above analysis, the root cause analysis of the abnormal feature data points is given, and solutions are provided.

[0054] After the root cause analysis of the abnormal feature data points is given, context-aware data can be integrated to determine the notification method and priority according to the environment and impact scope of the abnormal feature data points. For example, if the abnormal feature data point occurs in a key business process in the production environment, it can be determined as a serious anomaly, and if the abnormal feature data point occurs in a non-critical process in the development environment, it can be determined as a general anomaly, etc. In addition, different levels of alarms can be set according to the severity and impact scope of the abnormal feature data points. Specifically, serious anomalies can directly notify management personnel through pop-up windows, and send emergency notifications such as text messages or emails. General anomalies can notify relevant personnel by email or instant messaging. Minor anomalies can record the anomaly information in the system log for regular review. The priority of notification can also be set according to the impact of the abnormal feature data points. For example, serious anomalies have the highest priority and require the fastest response.

[0055] like Figure 2 As shown, the present application provides a method for analyzing abnormal SQL of a database, including the following steps. 1. Determine database indicators: Ensure the selection of correct database indicators based on the expertise of internal and external database experts. 2. Data integration: Establish an infrastructure for collecting and storing database performance KPI data. 3. Model building and tuning: Use algorithms and data to establish algorithm models (including abnormal SQL identification models and prediction models) to perform model tuning. 4. Online and continuous model optimization: Continuously adjust and optimize the model based on real-time feature data point sequences.

[0056] The abnormal SQL analysis method of the database provided in the embodiment of the present application is to obtain an abnormal SQL recognition model by training a preset type of support vector machine model through a sequence of sample feature data points, so that the abnormal SQL recognition model can fully learn the distribution of normal sample feature data points, thereby improving the accuracy and efficiency of determining abnormal feature data points. The preset exponential smoothing model is trained according to the sequence of normal sample feature data points to obtain a prediction model, so that the prediction model can fully obtain the development and change trend of the feature data points after the database executes normal SQL, which is conducive to improving the efficiency and accuracy of predicting data points. The present application realizes automatic multiple confirmation of abnormal feature data points through the abnormal SQL recognition model and the prediction model, thereby improving the accuracy and efficiency of determining abnormal feature data points, and is conducive to improving the processing efficiency of abnormal SQL of the database.

[0057] This application provides a database abnormal SQL analysis method based on artificial intelligence for ITOperations (AIOps). Build an AIOps platform, collect database indicators, use data and algorithms to establish abnormal SQL recognition models and prediction models, perform model tuning, and use the One-ClassSVM algorithm and ESM algorithm to quickly and timely discover SQL performance mutation problems.

[0058] Based on the above embodiment, the abnormal SQL recognition model is used to obtain abnormal feature data points, including steps S210 to S240, and each step is specifically as follows.

[0059] S210: Perform anomaly detection on feature data points to identify initial abnormal feature data points.

[0060] S220: Determine the front kurtosis of the initial abnormal feature data point based on the feature data point that precedes the initial abnormal feature data point and the average value of the feature data points.

[0061] S230: Determine the posterior kurtosis of the initial abnormal feature data point based on the feature data points that are arranged after the initial abnormal feature data point and the average value of the feature data points.

[0062] S240: When the difference between the current kurtosis and the back kurtosis is greater than the set kurtosis difference, the initial abnormal feature data point is determined to be an abnormal feature data point.

[0063] Use the trained abnormal SQL recognition model to analyze the database, storage, CPU, memory and other indicator data of each feature data point to perform anomaly detection and identify the initial abnormal feature data points in the feature data point sequence.

[0064] The front kurtosis of the initial abnormal characteristic data point is determined based on the feature data point that precedes the initial abnormal characteristic data point and the average value of the feature data point. The back kurtosis of the initial abnormal characteristic data point is determined based on the feature data point that follows the initial abnormal characteristic data point and the average value of the feature data point. The average value of the feature data point is the average value of all feature data points in the feature data point sequence. The calculation formulas for the front kurtosis and the back kurtosis are as follows.

[0065] ; in, For the feature data points (initial abnormal feature data points), is the number of feature data points in the feature data point sequence, is the average value of the feature data points, is the feature data point that is ranked before the initial abnormal feature data point, is the feature data point that follows the initial abnormal feature data point.

[0066] Compare the front kurtosis and back kurtosis of the initial abnormal feature data point. If the difference between the front kurtosis and the back kurtosis is greater than the set kurtosis difference, it means that the difference between the front kurtosis and the back kurtosis is significant, indicating that the initial abnormal feature data point has obviously deviated from the feature data point sequence, and the initial abnormal feature data point is identified as an abnormal feature data point. Otherwise, the initial abnormal feature data point is not identified as an abnormal feature data point.

[0067] The present application identifies initial abnormal feature data points, and further identifies abnormal feature data points according to the front kurtosis and the back kurtosis, thereby improving the accuracy of identifying abnormal feature data points.

[0068] Based on the above embodiment, the abnormal SQL recognition model is used to obtain abnormal feature data points, including steps S250 to S240, and each step is specifically as follows.

[0069] S250: Perform anomaly detection on the feature data points to identify initial abnormal feature data points.

[0070] S260: Determine the interquartile range of the feature data point sequence based on the first quartile and the third quartile of the feature data point sequence.

[0071] S270: Determine the lower limit of the range of the feature data point based on the interquartile range and the first quartile.

[0072] S280: Determine the upper limit of the range of the characteristic data points based on the interquartile range and the third quartile.

[0073] S290: Determine a normal range of the feature data point based on the upper range limit and the lower range limit.

[0074] S2100: If the initial abnormal feature data point is not within the normal range, the initial abnormal feature data point is determined as an abnormal feature data point.

[0075] Use the trained abnormal SQL recognition model to analyze the database, storage, CPU, memory and other indicator data of each feature data point to perform anomaly detection and identify the initial abnormal feature data points in the feature data point sequence.

[0076] Calculate the first quartile Q1 and the third quartile Q3 of all feature data points in the feature data point sequence. Determine the interquartile range (IQR) based on the first quartile and the third quartile. IQR=Q3-Q1. Determine the upper limit of the range of feature data points based on the interquartile range and the third quartile. Upper limit of range=Q3+1.5×IQR. Determine the lower limit of the range of feature data points based on the interquartile range and the first quartile. Lower limit of range=Q1-1.5×IQR.

[0077] If the initial abnormal feature data point is not within the normal range consisting of the upper range limit and the lower range limit, the initial abnormal feature data point is determined as an abnormal feature data point, otherwise the initial abnormal feature data point is not determined as an abnormal feature data point.

[0078] The present application determines abnormal feature data points based on the interquartile range, the first quartile and the third quartile, comprehensively considers the degree of deviation of the initial abnormal feature data points in the entire feature data point sequence, and improves the accuracy of determining the abnormal feature data points.

[0079] Based on the above embodiment, the historical feature data points of abnormal feature data points and the weights of the historical feature data points are obtained based on the feature data point sequence, including steps S310 to S320, and each step is specifically as follows.

[0080] S310: Determine the time window length of the historical feature data points based on the number of abnormal feature data points, the complexity of the abnormal feature data points and the time span of the abnormal feature data points, and obtain the historical feature data points from the feature data point sequence based on the time window length.

[0081] S320: Determine the weight of each historical feature data point based on the weight decay coefficient, the time position of the historical feature data point, and the number of the historical feature data points.

[0082] The range of historical feature data points is dynamically adjusted according to the number, complexity and time span of abnormal feature data points. After the range of historical feature data points is adjusted, the weight of each historical feature data point in the prediction model is determined according to the time distance between each historical feature data point and the abnormal feature data point.

[0083] First, dynamically adjust the range of historical feature data points according to the number, complexity and time span of abnormal feature data points. (1) Detect the number of abnormal feature data points. When there are multiple abnormal feature data points, lengthen the time window length of the historical feature data points to expand the range of historical feature data points. (2) Perform complexity analysis on abnormal feature data points according to factors such as the number of tables, connection types, and usage of abnormal feature data points. For example, when the abnormal feature data point query involves multiple tables and each table has a complex connection, the complexity of the abnormal feature data point is high. Inner joins are usually simpler than outer joins, and outer joins are more complex. In this case, lengthen the time window length of the historical feature data points to expand the range of historical feature data points. (3) Determine the range of historical feature data points by the time span of the abnormal feature data points. For example, if the time span of the abnormal feature data points is large, lengthen the time window length to expand the range of historical feature data points. By dynamically adjusting the time window, the prediction model can adapt to time window lengths of different sizes, optimize resource utilization, and improve the accuracy of the prediction model.

[0084] After adjusting the range of historical feature data points, obtain historical feature data points. Determine the weight of each historical feature data point in the prediction model based on the time distance between each historical feature data point and the abnormal feature data point. Determine the weight decay coefficient. The weight decay coefficient is a key parameter in the prediction model that determines how the weight decays over time. Usually, the weight decay coefficient is a decimal less than 1, such as 0.3 or 0.5. Historical feature data points that are closest to the abnormal feature data points will be assigned higher weights, while historical feature data points that are farther away from the abnormal feature data points will be assigned lower weights. The weight calculation formula for historical feature data points is as follows.

[0085] ; Among them, the historical feature data points are sorted according to the time position. For the The weight of historical feature data points, is the weight decay coefficient, is the number of historical feature data points.

[0086] The historical feature data points are weighted and adjusted using the calculated weights of the historical feature data points. The historical feature data points and the weights of the historical feature data points are input into the prediction model to obtain the prediction data points output by the prediction model.

[0087] This application determines the historical feature data points according to the length of the time window, and realizes the dynamic and accurate acquisition of historical feature data points. According to the weight of the historical feature data points, different degrees of focus on the historical feature data points are realized, which is conducive to improving the accuracy of the prediction model in determining the prediction data points.

[0088] The abnormal SQL analysis device for a database provided by the present application is described below. The abnormal SQL analysis device for a database described below and the abnormal SQL analysis method for a database described above can be referenced to each other.

[0089] like Figure 4 As shown, a database abnormal SQL analysis device includes: a feature data point sequence acquisition module 401, which is used to obtain feature data points after the database executes structured query language SQL within a preset time period, and obtain a time-ordered feature data point sequence.

[0090] The abnormal feature data point determination module 402 is used to input the feature data point sequence into the abnormal SQL recognition model to obtain the abnormal feature data points output by the abnormal SQL recognition model; the abnormal SQL recognition model is trained based on a preset first-class support vector machine model based on the sample feature data point sequence, normal sample feature data point labels and abnormal sample feature data point labels.

[0091] The prediction data point determination module 403 is used to obtain the historical feature data points and the weights of the historical feature data points of the abnormal feature data points based on the feature data point sequence, input the historical feature data points and the weights of the historical feature data points into the prediction model, and obtain the prediction data points corresponding to the abnormal feature data points output by the prediction model under normal operation; the prediction model is trained based on the normal sample feature data point sequence on the basis of a preset exponential smoothing model.

[0092] The analysis module 404 is used to analyze the abnormal cause of the abnormal feature data point when the difference between the predicted data point and the abnormal feature data point is greater than a set threshold.

[0093] The abnormal SQL analysis device for a database provided in an embodiment of the present application presets a type of support vector machine model through sample feature data point sequence training to obtain an abnormal SQL recognition model, so that the abnormal SQL recognition model can fully learn the distribution of normal sample feature data points, thereby improving the accuracy and efficiency of determining abnormal feature data points. The preset exponential smoothing model is trained according to the normal sample feature data point sequence to obtain a prediction model, so that the prediction model can fully obtain the development and change trend of the feature data points after the database executes normal SQL, which is beneficial to improving the efficiency and accuracy of predicting data points. The present application realizes automatic multiple confirmation of abnormal feature data points through abnormal SQL recognition models and prediction models, thereby improving the accuracy and efficiency of determining abnormal feature data points, and is beneficial to improving the processing efficiency of abnormal SQL of the database.

[0094] In one embodiment, the abnormal feature data point determination module 402 is used to: perform abnormality detection on feature data points to identify initial abnormal feature data points; determine the front kurtosis of the initial abnormal feature data point based on the feature data points that precede the initial abnormal feature data point and the average value of the feature data points; determine the back kurtosis of the initial abnormal feature data point based on the feature data points that follow the initial abnormal feature data point and the average value of the feature data points; when the difference between the front kurtosis and the back kurtosis is greater than the set kurtosis difference, determine that the initial abnormal feature data point is an abnormal feature data point.

[0095] In one embodiment, the abnormal feature data point determination module 402 is used to: perform abnormality detection on feature data points to identify initial abnormal feature data points; determine the interquartile range of the feature data point sequence based on the first quartile and the third quartile of the feature data point sequence; determine the lower limit of the range of the feature data points based on the interquartile range and the first quartile; determine the upper limit of the range of the feature data points based on the interquartile range and the third quartile; determine the normal range of the feature data points based on the upper limit and the lower limit; if the initial abnormal feature data point is not within the normal range, determine the initial abnormal feature data point as an abnormal feature data point.

[0096] In one embodiment, the predicted data point determination module 403 is used to: determine the time window length of the historical feature data points based on the number of abnormal feature data points, the complexity of the abnormal feature data points and the time span of the abnormal feature data points, and obtain the historical feature data points from the feature data point sequence based on the time window length; determine the weight of each historical feature data point based on the weight attenuation coefficient, the time position of the historical feature data points and the number of historical feature data points.

[0097] In one embodiment, the abnormal feature data point determination module 402 is used to: obtain historical database performance indicator data and historical resource usage data of the sample database; preprocess, format convert and feature extract the historical database performance indicator data and historical resource usage data to obtain sample feature data points to obtain a time-ordered sample feature data point sequence, obtain normal sample feature data point labels and abnormal sample feature data point labels of the sample feature data point sequence, and obtain a labeled sample feature data point sequence; train a preset first-class support vector machine model based on the labeled sample feature data point sequence until the classification error of the hyperplane of the preset first-class support vector machine model for normal sample feature data points and abnormal sample feature data points is less than a set error, and obtain an abnormal SQL recognition model, and the classification error is determined based on the loss function of the preset first-class support vector machine model, and the loss function is determined based on the normal vector, regularization parameter and slack variable of the hyperplane.

[0098] In one embodiment, the prediction data point determination module 403 is used to: remove abnormal sample feature data points in the sample feature data point sequence to obtain a normal sample feature data point sequence; train a preset exponential smoothing model based on the normal sample feature data point sequence until the mean square error between the sample prediction value output by the preset exponential smoothing model and the corresponding normal sample feature data point is less than the set mean square error, thereby obtaining a prediction model.

[0099] Figure 5 An example of a physical structure diagram of an electronic device is shown in FIG. Figure 5As shown, the electronic device may include: a processor (processor) 510, a communication interface (Communications Interface) 520, a memory (memory) 530 and a communication bus 540, wherein the processor 510, the communication interface 520, and the memory 530 communicate with each other through the communication bus 540. The processor 510 can call the logic instructions in the memory 530 to execute the abnormal SQL analysis method of the database, which includes: obtaining feature data points after the database executes the structured query language SQL within a preset time period to obtain a time-ordered feature data point sequence; inputting the feature data point sequence into the abnormal SQL recognition model to obtain the abnormal feature data points output by the abnormal SQL recognition model; the abnormal SQL recognition model is trained based on a sample feature data point sequence, normal sample feature data point labels and abnormal sample feature data point labels on the basis of a preset first-class support vector machine model; based on the feature data point sequence, the historical feature data points and the weights of the historical feature data points of the abnormal feature data points are obtained, and the historical feature data points and the weights of the historical feature data points are input into the prediction model to obtain the predicted data points corresponding to the abnormal feature data points output by the prediction model under normal operation; the prediction model is trained based on the normal sample feature data point sequence on the basis of a preset exponential smoothing model; when the difference between the predicted data point and the abnormal feature data point is greater than the set threshold, the abnormal cause of the abnormal feature data point is analyzed.

[0100] In addition, the logic instructions in the above-mentioned memory 530 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including several instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0101] On the other hand, the present application also provides a computer program product, which includes a computer program, which can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the abnormal SQL analysis method of the database provided by the above methods, the method including: obtaining feature data points after the database executes the structured query language SQL within a preset time period, and obtaining a time-sorted feature data point sequence; inputting the feature data point sequence into an abnormal SQL recognition model to obtain abnormal feature data points output by the abnormal SQL recognition model; the abnormal SQL recognition model is trained based on a sample feature data point sequence, a normal sample feature data point label and an abnormal sample feature data point label on the basis of a preset class of support vector machine models; based on the feature data point sequence, the historical feature data points of the abnormal feature data points and the weights of the historical feature data points are obtained, and the historical feature data points and the weights of the historical feature data points are input into a prediction model to obtain the predicted data points corresponding to the abnormal feature data points output by the prediction model under normal operation; the prediction model is trained based on a normal sample feature data point sequence on the basis of a preset exponential smoothing model; when the difference between the predicted data point and the abnormal feature data point is greater than a set threshold, the abnormal cause of the abnormal feature data point is analyzed.

[0102] On the other hand, the present application also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, is implemented to execute the abnormal SQL analysis method of the database provided by the above-mentioned methods, the method comprising: obtaining feature data points after the database executes the structured query language SQL within a preset time period, and obtaining a time-sorted feature data point sequence; inputting the feature data point sequence into an abnormal SQL recognition model, and obtaining abnormal feature data points output by the abnormal SQL recognition model; the abnormal SQL recognition model is trained based on a sample feature data point sequence, a normal sample feature data point label, and an abnormal sample feature data point label on the basis of a preset first-class support vector machine model; based on the feature data point sequence, the historical feature data points and the weights of the historical feature data points of the abnormal feature data points are obtained, and the historical feature data points and the weights of the historical feature data points are input into a prediction model, and the predicted data points corresponding to the abnormal feature data points output by the prediction model under normal operation are obtained; the prediction model is trained based on a normal sample feature data point sequence on the basis of a preset exponential smoothing model; when the difference between the predicted data point and the abnormal feature data point is greater than a set threshold, analyzing the abnormal cause of the abnormal feature data point.

[0103] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative labor.

[0104] Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a necessary general hardware platform, and of course, can also be implemented by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0105] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A method for analyzing abnormal SQL in a database, characterized in that: include: Acquire characteristic data points after executing structured query language SQL in a preset time period in the database, and obtain a sequence of characteristic data points sorted in time; Inputting the characteristic data point sequence into an abnormal SQL recognition model to obtain abnormal characteristic data points output by the abnormal SQL recognition model; the abnormal SQL recognition model is trained based on a preset first-class support vector machine model and based on a sample characteristic data point sequence, normal sample characteristic data point labels, and abnormal sample characteristic data point labels; Based on the feature data point sequence, historical feature data points and weights of the historical feature data points of the abnormal feature data points are obtained, and the historical feature data points and weights of the historical feature data points are input into a prediction model to obtain prediction data points corresponding to the abnormal feature data points output by the prediction model under normal operation; the prediction model is obtained by training based on a preset exponential smoothing model and a normal sample feature data point sequence; When the difference between the predicted data point and the abnormal feature data point is greater than a set threshold, the abnormal cause of the abnormal feature data point is analyzed.

2. The abnormal SQL analysis method of a database according to claim 1, characterized in that: The abnormal SQL recognition model is used to obtain the abnormal feature data points: Performing anomaly detection on the characteristic data points to identify initial abnormal characteristic data points; Determine the front kurtosis of the initial abnormal feature data point based on the feature data point that precedes the initial abnormal feature data point and the average value of the feature data points; Determine the posterior kurtosis of the initial abnormal feature data point based on the feature data points arranged behind the initial abnormal feature data point and the average value of the feature data points; When the difference between the front kurtosis and the back kurtosis is greater than a set kurtosis difference, the initial abnormal feature data point is determined to be the abnormal feature data point.

3. The abnormal SQL analysis method of a database according to claim 1, characterized in that: The abnormal SQL recognition model is used to obtain the abnormal feature data points: Performing anomaly detection on the characteristic data points to identify initial abnormal characteristic data points; Determine the interquartile range of the feature data point sequence based on the first quartile and the third quartile of the feature data point sequence; Determine a lower limit of a range of the feature data point based on the interquartile range and the first quartile; Determine an upper limit of a range of the feature data point based on the interquartile range and the third quartile; Determining a normal range of the feature data point based on the upper range limit and the lower range limit; If the initial abnormal feature data point is not within the normal range, the initial abnormal feature data point is determined as the abnormal feature data point.

4. The abnormal SQL analysis method of a database according to claim 1, characterized in that: The step of acquiring the historical feature data points and the weights of the historical feature data points of the abnormal feature data points based on the feature data point sequence includes: Determine the time window length of the historical feature data points based on the number of abnormal feature data points, the complexity of the abnormal feature data points and the time span of the abnormal feature data points, and obtain the historical feature data points from the feature data point sequence based on the time window length; The weight of each of the historical feature data points is determined based on a weight decay coefficient, a time position of the historical feature data point, and the number of the historical feature data points.

5. The abnormal SQL analysis method of a database according to claim 1, characterized in that: The abnormal SQL recognition model is determined based on the following steps: Obtain historical database performance indicator data and historical resource usage data of the sample database; Preprocessing, format conversion and feature extraction are performed on the historical database performance indicator data and the historical resource usage data to obtain sample feature data points, so as to obtain a time-ordered sequence of the sample feature data points, and obtaining the normal sample feature data point labels and the abnormal sample feature data point labels of the sample feature data point sequence to obtain a sample feature data point sequence carrying labels; The preset first-class support vector machine model is trained based on the sequence of sample feature data points carrying labels until the classification error of the hyperplane of the preset first-class support vector machine model for normal sample feature data points and abnormal sample feature data points is less than a set error, thereby obtaining the abnormal SQL recognition model, wherein the classification error is determined based on the loss function of the preset first-class support vector machine model, and the loss function is determined based on the normal vector, regularization parameter and slack variable of the hyperplane.

6. The abnormal SQL analysis method of a database according to claim 5, characterized in that: The prediction model is determined based on the following steps: Removing the abnormal sample feature data points from the sample feature data point sequence to obtain the normal sample feature data point sequence; The preset exponential smoothing model is trained based on the normal sample feature data point sequence until the mean square error between the sample prediction value output by the preset exponential smoothing model and the corresponding normal sample feature data point is less than the set mean square error, thereby obtaining the prediction model.

7. A database abnormal SQL analysis device, characterized in that: include: The feature data point sequence acquisition module is used to acquire the feature data points after the database executes the structured query language SQL within a preset time period, and obtain a time-ordered feature data point sequence; An abnormal feature data point determination module is used to input the feature data point sequence into an abnormal SQL recognition model to obtain abnormal feature data points output by the abnormal SQL recognition model; the abnormal SQL recognition model is trained based on a preset first-class support vector machine model, based on a sample feature data point sequence, normal sample feature data point labels, and abnormal sample feature data point labels; A prediction data point determination module is used to obtain the historical feature data points and weights of the historical feature data points of the abnormal feature data points based on the feature data point sequence, input the historical feature data points and the weights of the historical feature data points into a prediction model, and obtain the prediction data points corresponding to the abnormal feature data points output by the prediction model under normal operation; the prediction model is obtained by training based on a normal sample feature data point sequence on the basis of a preset exponential smoothing model; The analysis module is used to analyze the abnormal cause of the abnormal feature data point when the difference between the predicted data point and the abnormal feature data point is greater than a set threshold.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the computer program, the abnormal SQL analysis method for the database according to any one of claims 1 to 6 is implemented.

9. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the abnormal SQL analysis method for a database as claimed in any one of claims 1 to 6 is implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the abnormal SQL analysis method for a database as claimed in any one of claims 1 to 6 is implemented.

Images