SQL (Structured Query Language) statement processing method and device, equipment and medium

Abstract

The invention provides an SQL statement processing method which can be applied to the field of big data or cloud computing. The method comprises the steps of monitoring an execution log of a target database, and obtaining an SQL statement in the execution log; multi-dimensional feature extraction is carried out on the SQL statement to obtain a multi-dimensional data feature value, and the dimensions at least comprise a grammar structure, data operation, resource consumption and user behaviors; based on the multi-dimensional data feature values, a depth portrait is constructed for each SQL statement, and the depth portraits are used for representing metadata description of the SQL statements; and analyzing each depth portrait to obtain an analysis result corresponding to the SQL statement, and performing prompting and early warning processing according to the analysis result. The invention further provides an SQL statement processing device and equipment, a storage medium and a program product.

Description

Technical Field

[0001] The present disclosure relates to the field of big data or cloud computing, and more specifically to a method, device, equipment, medium and program product for processing SQL statements. Background Art

[0002] Database management systems can efficiently process and analyze massive amounts of data and meet data requirements in complex business scenarios. They have been widely used in many fields. In practical applications, SQL statements are the core means of interacting with the database, and their execution efficiency has a crucial impact on the performance of the entire database system. However, with the continuous growth of data volume and the increase in the complexity of SQL statements, how to effectively manage and optimize these SQL statements has become a key challenge in database performance optimization. At present, although there are some SQL analysis tools and methods that can help users identify and optimize the performance bottlenecks of SQL statements to a certain extent, they often lack in-depth portrait statistics and analysis technology for specific database characteristics, making it difficult to accurately capture its performance bottlenecks and effectively optimize them. Summary of the invention

[0003] In view of the above problems, embodiments of the present disclosure provide a method, apparatus, device, medium and program product for processing a SQL statement.

[0004] According to a first aspect of the present disclosure, a method for processing SQL statements is provided, the method comprising: monitoring an execution log of a target database, obtaining SQL statements in the execution log; performing multi-dimensional feature extraction on the SQL statements to obtain multi-dimensional data feature values, wherein the dimensions at least include grammatical structure, data operation, resource consumption, and user behavior; constructing a depth profile for each of the SQL statements based on the multi-dimensional data feature values, wherein the depth profile is used to characterize a metadata description of the SQL statement; and analyzing each of the depth profiles to obtain an analysis result corresponding to the SQL statement, and performing prompt and warning processing according to the analysis result.

[0005] According to an embodiment of the present disclosure, the method also includes: using a machine learning algorithm to cluster each of the depth portraits to obtain different statement clusters; and based on pre-established business rules, using a rule engine to classify each SQL statement in each of the statement clusters to obtain the classified SQL statements in each of the statement clusters.

[0006] According to an embodiment of the present disclosure, analyzing each of the depth portraits to obtain analysis results corresponding to the SQL statements includes: using the depth portraits to perform comparative analysis on the resource consumption dimension of each of the statement clusters to locate the SQL statements with performance bottlenecks; and automatically or semi-automatically generating corresponding optimization suggestions for the SQL statements with performance bottlenecks.

[0007] According to an embodiment of the present disclosure, the analyzing each of the depth portraits to obtain an analysis result corresponding to the SQL statement also includes: using the depth portrait to perform a comparative analysis of the SQL statement in the user behavior dimension to obtain a business usage pattern of a target database; and adjusting the target database according to the business usage pattern.

[0008] According to an embodiment of the present disclosure, the method also includes: based on the deep profile of each of the SQL statements, using a pre-trained trend prediction model, predicting the execution trend of the SQL statement in a future time period to obtain a prediction result; comparing the prediction result with a preset threshold to determine whether the prediction result is greater than the preset threshold; and when the prediction result is greater than the preset threshold, triggering an anomaly detection mechanism and generating early warning information.

[0009] According to an embodiment of the present disclosure, the method further includes: converting the analysis result into a statistical report so that the target personnel can obtain the performance status of the target database and the execution status of the SQL statement.

[0010] According to an embodiment of the present disclosure, the method further includes: performing data cleaning on the SQL statements and removing duplicate SQL statements; and performing formatting processing on the cleaned and deduplicated SQL statements.

[0011] A second aspect of the present disclosure provides a device for processing SQL statements, the device comprising: an acquisition module, used to monitor the execution log of a target database, and obtain SQL statements in the execution log; an extraction module, used to perform multi-dimensional feature extraction on the SQL statements, and obtain multi-dimensional data feature values, wherein the dimensions at least include grammatical structure, data operation, resource consumption, and user behavior; a construction module, used to construct a deep portrait for each of the SQL statements based on the multi-dimensional data feature values, wherein the deep portrait is used to characterize the metadata description of the SQL statement; and an analysis module, used to analyze each of the deep portraits, obtain an analysis result corresponding to the SQL statement, and perform prompt and warning processing according to the analysis result.

[0012] A third aspect of the present disclosure provides an electronic device, comprising: one or more processors; a storage device for storing one or more programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the above-mentioned SQL statement processing method.

[0013] A fourth aspect of the present disclosure further provides a computer-readable storage medium on which a computer program is stored, and when the computer program is executed by a processor, the steps of the above-mentioned SQL statement processing method are implemented.

[0014] The fifth aspect of the present disclosure further provides a computer program product, including a computer program, which implements the steps of the above-mentioned SQL statement processing method when executed by a processor.

[0015] In the embodiments of the present disclosure, by deeply characterizing the SQL statements in the target database and using the deep portraits for efficient analysis, the SQL statements are optimized according to preset statistical requirements, thereby effectively improving the overall operating efficiency of the database system, reducing resource usage, and providing users with more intelligent and personalized query optimization services. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] The above contents and other purposes, features and advantages of the present disclosure will become more apparent through the following description of the embodiments of the present disclosure with reference to the accompanying drawings, in which:

[0017] Figure 1 The application scenario diagram schematically shows the method, apparatus, device, medium and program product for processing SQL statements according to the embodiments of the present disclosure;

[0018] Figure 2 A flowchart schematically shows a method for processing SQL statements according to an embodiment of the present disclosure;

[0019] Figure 3 A flowchart for predicting the execution trend of SQL statements according to an embodiment of the present disclosure is schematically shown;

[0020] Figure 4 A structural block diagram schematically shows a device for processing SQL statements according to an embodiment of the present disclosure; and

[0021] Figure 5 A block diagram schematically shows an electronic device for a method for processing SQL statements according to an embodiment of the present disclosure. DETAILED DESCRIPTION

[0022] Hereinafter, embodiments of the present disclosure will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present disclosure. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of the embodiments of the present disclosure. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of the concepts of the present disclosure.

[0023] The terms used herein are only for describing specific embodiments and are not intended to limit the present disclosure. The terms "comprise", "include", etc. used herein indicate the existence of the features, steps, operations and / or components, but do not exclude the existence or addition of one or more other features, steps, operations or components.

[0024] All terms (including technical and scientific terms) used herein have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0025] When using expressions such as "at least one of A, B, and C, etc.", they should generally be interpreted according to the meaning of the expression commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0026] In the technical solution of the present invention, the user information (including but not limited to user personal information, user image information, user device information, such as location information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved are all information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of the relevant data comply with relevant laws, regulations and standards, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0027] In the scenario of using personal information for automated decision-making, the methods, devices, and systems provided by the embodiments of the present disclosure provide users with corresponding operation portals for users to choose to agree or reject the automated decision-making results; if the user chooses to reject, the expert decision-making process will be entered. The expression "automated decision-making" here refers to the activity of automatically analyzing and evaluating a person's behavioral habits, interests and hobbies, or economic, health, credit status, etc. through computer programs, and making decisions. The expression "expert decision-making" here refers to the activity of making decisions by people who specialize in a certain field, have specialized experience, knowledge and skills, and have reached a certain level of professionalism.

[0028] Figure 1 The application scenario diagram schematically shows the method, apparatus, device, medium and program product for processing SQL statements according to the embodiments of the present disclosure.

[0029] like Figure 1 As shown, the application scenario 100 according to this embodiment may include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104, and a server 105. The network 104 is used to provide a medium for a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103, and the server 105. The network 104 may include various connection types, such as wired, wireless communication links, or optical fiber cables, etc.

[0030] The user can use the first terminal device 101, the second terminal device 102, and the third terminal device 103 to interact with the server 105 through the network 104 to receive or send messages, etc. Various communication client applications can be installed on the first terminal device 101, the second terminal device 102, and the third terminal device 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (only for example).

[0031] The first terminal device 101, the second terminal device 102, and the third terminal device 103 may be various electronic devices having display screens and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and the like.

[0032] The server 105 may be a server that provides various services, such as a background management server (only as an example) that provides support for websites browsed by users using the first terminal device 101, the second terminal device 102, and the third terminal device 103. The background management server may analyze and process the received data such as user requests, and feed back the processing results (such as web pages, information, or data obtained or generated according to user requests) to the terminal device.

[0033] It should be noted that the SQL statement processing method provided in the embodiment of the present disclosure can generally be executed by the server 105. Accordingly, the SQL statement processing device provided in the embodiment of the present disclosure can generally be set in the server 105. The SQL statement processing method provided in the embodiment of the present disclosure can also be executed by a server or server cluster that is different from the server 105 and can communicate with the first terminal device 101, the second terminal device 102, the third terminal device 103 and / or the server 105. Accordingly, the SQL statement processing device provided in the embodiment of the present disclosure can also be set in a server or server cluster that is different from the server 105 and can communicate with the first terminal device 101, the second terminal device 102, the third terminal device 103 and / or the server 105.

[0034] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to the implementation requirements.

[0035] Figure 2 The flowchart schematically shows a method for processing SQL statements according to an embodiment of the present disclosure.

[0036] like Figure 2 As shown, the SQL statement processing method of this embodiment includes operations S210 to S240.

[0037] In operation S210, the execution log of the target database is monitored, and the SQL statement in the execution log is obtained.

[0038] In an embodiment of the present disclosure, before obtaining the SQL statements in the execution log, the user's consent or authorization is obtained. For example, before operation S210, a request to obtain the SQL statements in the execution log is issued to the user. When the user agrees or authorizes that the data set to be processed can be obtained, operation S210 is performed.

[0039] Exemplarily, the target database may be a Gaussian database, which is a high-performance, distributed relational database management system designed for large-scale data processing. It can efficiently process and analyze massive amounts of data and meet data requirements in complex business scenarios.

[0040] In some exemplary embodiments, a real-time monitoring tool is used to view the execution log of the target database (such as Gaussian database) in real time. If the log file is large, you can consider enabling the log rotation function to archive the old log file and create a new log file, so as to keep the size of the log file within a manageable range and prevent the log file from occupying too much disk space. If the log format allows, SQL statements can be read directly from the log file. If the format does not allow, a third-party tool can be used to parse the log file and capture all executed SQL statements.

[0041] After the SQL statement is obtained, operations S211 to S212 are executed.

[0042] In operation S211, data cleaning is performed on the SQL statements, and repeated SQL statements are removed.

[0043] In some exemplary embodiments, data cleaning is performed on SQL statements, such as removing the comment part in the SQL statement to avoid unnecessary interference. For the problem that blank characters such as spaces and line breaks in SQL statements may be inconsistent, the spaces can be normalized, that is, all spaces and line breaks are replaced with a unified format. At the same time, the captured SQL statements may have grammatical errors, such as spelling errors, missing keywords, etc. During the cleaning process, these errors can be identified and corrected to ensure the correctness of the SQL statements. In addition, sometimes SQL statements may contain additional information that is not related to business logic, such as specific database names, table name prefixes, etc., which may be redundant for analysis and can therefore be removed during the cleaning process.

[0044] In order to remove duplicate SQL statements, you can use a hash algorithm to calculate the hash of each SQL statement, and then compare the hash values. If the hash values ​​of two SQL statements are the same, they are considered duplicates. For duplicate SQL statements that cannot be accurately identified by hash comparison (such as SQL statements that appear different but are actually the same due to differences in spaces, line breaks, etc.), you can use a line-by-line comparison method to split the SQL statement by line, and then compare the content of each line line by line to determine whether there is a duplication.

[0045] In operation S212, formatting is performed on the cleaned and deduplicated SQL statements.

[0046] Unify the format of SQL statements to the standard SQL format, such as capitalizing keywords, lowercase table and column names, and using consistent indentation and line breaks. When aliases are used in SQL statements, make all aliases follow consistent naming rules (such as using lowercase letters, underscores, etc.) and remove unnecessary aliases.

[0047] It is understandable that pre-processing operations such as cleaning, deduplication and formatting of captured SQL statements can help eliminate redundant information and errors in SQL statements, and can also make SQL statements more standardized and consistent, thereby significantly improving the accuracy and efficiency of subsequent analysis.

[0048] In operation S220, multi-dimensional feature extraction is performed on the SQL statement to obtain multi-dimensional data feature values, where the dimensions at least include grammatical structure, data operation, resource consumption, and user behavior.

[0049] Perform multi-dimensional feature extraction on the preprocessed SQL statements, including but not limited to: grammatical structure features, such as query type (SELECT, INSERT, UPDATE, DELETE, etc.), number of subquery levels, connection type and number, and usage of aggregate functions. Data operation features, such as the number of tables involved, the number of fields, index usage, and the complexity of data screening conditions. Resource consumption features, such as performance indicators such as execution time, CPU usage, memory usage, and disk I / O. User behavior features, such as the user ID who executes the SQL, execution time distribution, frequency, and whether it is a batch job. Then, based on the various extracted features, multi-dimensional data feature values ​​can be integrated.

[0050] In operation S230, a depth profile is constructed for each of the SQL statements based on the multi-dimensional data feature values, where the depth profile is used to characterize the metadata description of the SQL statement.

[0051] Based on the above feature values, a detailed "portrait" is created for each SQL statement, that is, a structured metadata description. This portrait not only contains the original SQL text, but also includes all the extracted feature information, forming a data model for in-depth analysis.

[0052] In operation S240, each of the depth portraits is analyzed to obtain an analysis result corresponding to the SQL statement, and prompt and warning processing is performed according to the analysis result.

[0053] In the embodiment of the present disclosure, a corresponding operation entry is provided for the user to choose to agree or reject the automated decision result. That is, before analyzing and processing each depth portrait, an instruction to agree or reject the processing / decision is obtained from the user through the corresponding operation entry. If the user agrees to perform the processing / decision, the analysis and processing / decision of each depth portrait is performed, that is, step S240 is executed. If the user refuses to perform the processing / decision, the expert decision process is entered.

[0054] In some exemplary embodiments, after obtaining the deep profile of the SQL statement, the deep profile can be mined using data analysis algorithms (such as cluster analysis, association analysis, etc.) to identify potential problems such as performance bottlenecks and abnormal behaviors. Then, a detailed report can be generated based on the analysis results, including a list of problematic SQL statements, problem types, and impact levels. Prompt and warning information is then sent to database administrators or relevant developers through emails, text messages, and system messages so that they can take timely measures to solve the problem. Finally, the processing results are recorded and analyzed to optimize the system's prompt and warning mechanisms and improve the accuracy and practicality of the system.

[0055] It should be noted that as the target database runs and SQL statements change, the deep profile can be updated regularly to ensure the accuracy and timeliness of the profile. Then, based on the new deep profile and performance data, the analysis algorithm and process can be continuously adjusted and optimized to improve the accuracy and efficiency of the analysis.

[0056] It can be understood that the SQL statement processing method provided in the embodiment of the present disclosure, through innovative data mining and intelligent analysis technology, deeply characterizes the SQL statements in the target database, and efficiently analyzes each deep portrait, thereby improving database performance monitoring, query optimization and system operation and maintenance efficiency.

[0057] In an embodiment of the present disclosure, the method also includes: using a machine learning algorithm to cluster each of the deep portraits to obtain different statement clusters; and based on pre-established business rules, using a rule engine to classify each SQL statement in each of the statement clusters to obtain the classified SQL statements in each of the statement clusters.

[0058] According to the characteristic dimensions and data distribution characteristics of the SQL statement portrait, select appropriate open source machine learning clustering algorithms, such as K-means (suitable for data with spherical cluster distribution), DBSCAN (suitable for clusters of arbitrary shapes and can identify noise points), decision trees, etc. Then, the deep portrait of the SQL statement is used as input data and input into the selected clustering algorithm. The algorithm will calculate the similarity of the execution characteristics between SQL statements based on the multidimensional data feature values ​​in the portrait, and classify SQL statements with high similarity into one category to form different statement clusters. At the same time, combined with business knowledge and actual needs, formulate a series of business rules for classifying SQL statements, such as the keywords of SQL statements, the table names involved, field names, execution time windows, execution frequencies, and other features, as well as their associations with specific business scenarios. The clustered statement clusters and the formulated business rules are input into the rule engine. The rule engine matches and classifies each SQL statement in the statement cluster according to the rules. For example, query statements involving key business data are classified as "key business queries", statements that are executed regularly and generate reports are classified as "regular report generation", and statements involving data input and output are classified as "data import and export".

[0059] It should be noted that when classifying SQL statements, you can classify them within each statement cluster or for SQL statements that have not been clustered, based on factors such as the structure of the SQL statements, query conditions, tables involved and data volume, so as to quickly identify SQL statements with similar characteristics or potential problems.

[0060] It is understandable that by clustering SQL statements and grouping SQL statements with similar execution characteristics into one category, the workload of subsequent analysis can be significantly reduced and the analysis efficiency can be improved. At the same time, classifying SQL statements in combination with business rules can enable database administrators and developers to more clearly understand the business meaning and purpose of SQL statements, which helps to better manage and optimize the database.

[0061] Based on the above embodiments, in this embodiment, the analysis of each of the depth portraits to obtain the analysis results corresponding to the SQL statements includes: using the depth portraits to perform comparative analysis on the resource consumption dimension of each of the statement clusters to locate the SQL statements with performance bottlenecks; and automatically or semi-automatically generating corresponding optimization suggestions for the SQL statements with performance bottlenecks.

[0062] Within each statement cluster, the resource consumption characteristics (such as execution time, CPU usage, etc.) in the deep profile are used for comparative analysis to locate performance bottlenecks, that is, SQL statements or clusters with abnormally high resource consumption or poor performance. Then, the performance bottleneck can be diagnosed in detail, including analyzing the execution plan, finding hot data, and evaluating the system load. After obtaining the analysis results, targeted SQL optimization suggestions are automatically or semi-automatically generated. Optimization suggestions include but are not limited to index recommendations (such as creating or deleting indexes, adjusting index structures, etc.), query rewriting (such as optimizing query conditions, reducing unnecessary table connections, etc.), data partition adjustment (such as adjusting partition strategies according to query frequency and data volume), etc. For the automatically generated optimization suggestions, preliminary verification and evaluation can also be performed to ensure the effectiveness and feasibility of the suggestions. Finally, the generated optimization suggestions are provided to database administrators or developers to guide them in optimizing SQL statements. After implementing the optimization suggestions, the system performance and database load are continuously monitored, the optimization effect is evaluated, and the optimization suggestions are iterated and improved based on the evaluation results to continuously improve the performance and stability of the database.

[0063] It is understandable that by analyzing the resource consumption characteristics of SQL statements, the performance level of SQL statements can be accurately evaluated and performance bottlenecks can be identified, so that targeted measures can be taken to improve the overall performance of the database.

[0064] Based on the above embodiments, in this embodiment, the analyzing each of the depth portraits to obtain the analysis result corresponding to the SQL statement also includes: using the depth portrait to perform a comparative analysis of the SQL statement in the user behavior dimension to obtain the business usage pattern of the target database; and adjusting the target database according to the business usage pattern.

[0065] In the process of deep profiling analysis, key behavioral features such as the time distribution, frequency, and operation type (such as query, insert, update, delete, etc.) of the user's SQL statement execution can also be used. This feature can reflect the user's actual use and preference of the target database. By comparing the extracted user behavior features with the preset standards or historical data, the changing trends and anomalies of user behavior can be analyzed. The behavioral characteristics of different users or user groups can also be compared to reveal the differences in user behavior in different business scenarios. Then, based on the results of the user behavior comparison analysis, combined with the business background and needs, the business usage pattern of the target database can be identified, such as the distribution of peak and trough periods, the frequency and characteristics of key business operations, and the hot spots of user activities. Then, based on the user behavior characteristics and data growth trends in the business usage pattern, the future database capacity requirements can be predicted to provide data support for the formulation of database expansion, backup and recovery strategies. At the same time, a reasonable service level agreement (SLA) can be formulated in combination with the key business operations in the business usage pattern and the user's expected response time, and the service level can be dynamically adjusted to meet the actual needs of users according to changes in user behavior and adjustments in business needs. In addition, the database performance and resource allocation strategy can be optimized based on the peak and trough distribution in the business usage pattern, increasing resource input during peak periods to improve response speed, and releasing resources during trough periods to reduce costs.

[0066] It is understandable that by analyzing and comparing the user behavior characteristics in the deep portrait, the business usage pattern of the target database can be revealed, so that potential risks and problems can be discovered in a timely manner, and early warning and decision support can be provided for database troubleshooting, performance tuning and risk management.

[0067] Figure 3 The flowchart for predicting the execution trend of SQL statements according to an embodiment of the present disclosure is schematically shown.

[0068] like Figure 3 As shown, in an embodiment of the present disclosure, the method also includes: based on the deep profile of each of the SQL statements, using a pre-trained trend prediction model, predicting the execution trend of the SQL statement in a future time period to obtain a prediction result; comparing the prediction result with a preset threshold to determine whether the prediction result is greater than the preset threshold; and when the prediction result is greater than the preset threshold, triggering an anomaly detection mechanism and generating early warning information.

[0069] In some exemplary embodiments, the algorithm selection of the trend prediction model is first performed, for example, based on time series analysis or deep learning algorithms. Time series analysis is applicable to data with obvious time correlation and can capture the trend and periodicity of data changes over time. Deep learning algorithms (such as recurrent neural network RNN, long short-term memory network LSTM, gated recurrent unit GRU, etc.) can handle more complex nonlinear relationships and are applicable to large-scale, high-dimensional data. Before model training, feature engineering can be performed on the deep profile data of SQL statements, including but not limited to selecting key performance indicators (such as execution time, return result set size and execution frequency, etc.) as input features, as well as possible preprocessing steps (such as data cleaning, missing value processing and feature scaling, etc.). Then, according to the selected algorithm and data characteristics, a suitable model structure is designed. For example, for a deep learning model, it is necessary to determine the number of layers of the network, the number of neurons in each layer, the activation function, etc. For a time series analysis model, it is necessary to select appropriate parameters such as window size and sliding step size. Then, the model is trained using historical data, and the model parameters are continuously adjusted so that it can accurately predict the execution of SQL statements in future time periods. Finally, in order to ensure the stability and reliability of the model, cross-validation techniques can be used to evaluate the performance of the model to obtain more accurate performance evaluation results.

[0070] Furthermore, after the model training and optimization are completed, the current and historical deep profile data are input into the trained trend prediction model, and the model will output the execution trend prediction results of SQL statements in the future time period, such as the predicted values ​​and confidence intervals of key indicators such as execution frequency and execution time, as well as the growth rate of resource consumption and the probability of abnormal behavior. In addition, the historical performance and future development trend of the database can be fully considered, and reasonable warning thresholds can be set according to business needs and database performance requirements, such as the maximum value, minimum value or change rate of key indicators such as execution frequency and execution time. Then, the prediction results output by the trend prediction model are compared with the preset warning threshold. If one or more key indicators in the prediction results exceed the preset threshold, it indicates that there may be performance problems or anomalies in the future. At the same time, when the prediction results exceed the warning threshold, the anomaly detection mechanism is triggered to achieve further analysis of SQL statements, real-time monitoring of database performance, and troubleshooting of potential problems. Then, a warning message is generated and notified to relevant personnel through emails, text messages, system logs, etc. The warning information can include detailed prediction results, abnormal cause analysis, and recommended countermeasures.

[0071] It is understandable that by accurately predicting the execution trend of future SQL statements and setting reasonable warning thresholds for anomaly detection and warning, potential performance problems can be discovered and prevented in a timely manner, so that response strategies can be formulated in advance to ensure the stable operation and efficient performance of the database.

[0072] In an embodiment of the present disclosure, the method further includes: converting the analysis result into a statistical report so that the target personnel can obtain the performance status of the target database and the execution status of the SQL statement.

[0073] After obtaining the analysis results, you can first organize and summarize the data, for example, classify the analysis results according to different dimensions such as time, SQL statement type, database table and user, and calculate the corresponding statistical indicators (such as the number of executions, average execution time, maximum execution time, error rate, etc.). Then, according to the characteristics of the analysis results and the needs of the target personnel, select the appropriate chart type for visual display. For example, you can use a line chart to show the trend of SQL statement execution time, a bar chart to show the number of executions of different SQL statements, and a pie chart to show the distribution of error rates. You can also use a comprehensive dashboard to integrate multiple charts and statistical data so that the target personnel can fully understand the database performance status and SQL statement execution. In addition, in the visual display, key data and trends can be marked and interpreted so that the target personnel can quickly understand the meaning of the data and the reasons behind it. For example, you can use visual elements such as arrows and colors to highlight abnormal data or important trends.

[0074] It should be noted that the dashboard should have good interactivity, allowing target personnel to freely view and analyze data by clicking, dragging, etc.

[0075] It is understandable that converting the analysis results into statistical reports and presenting them through visual displays can enable the target personnel to fully understand the performance status of the target database and the execution of SQL statements, so as to make decisions quickly and help them better manage and optimize database performance.

[0076] The SQL statement processing method provided in the embodiment of the present disclosure can be implemented by a software system, which includes an SQL execution module designed for a target database and a portrait analysis engine linked thereto, which can automatically generate optimized SQL statements according to preset statistical requirements and visualize the results.

[0077] Figure 4 The structural block diagram of the SQL statement processing device according to the embodiment of the present disclosure is schematically shown.

[0078] like Figure 4 As shown, the SQL statement processing device 400 according to this embodiment includes an acquisition module 410 , an extraction module 420 , a construction module 430 and an analysis module 440 .

[0079] The acquisition module 410 is used to monitor the execution log of the target database and acquire the SQL statements in the execution log. In one embodiment, the acquisition module 410 can be used to perform the operation S210 described above, which will not be described in detail here.

[0080] The extraction module 420 is used to perform multi-dimensional feature extraction on the SQL statement to obtain multi-dimensional data feature values, where the dimensions at least include grammatical structure, data operation, resource consumption and user behavior. In one embodiment, the extraction module 420 can be used to perform the operation S220 described above, which will not be repeated here.

[0081] The construction module 430 is used to construct a depth profile for each SQL statement based on the multidimensional data feature value, and the depth profile is used to characterize the metadata description of the SQL statement. In one embodiment, the construction module 430 can be used to perform the operation S230 described above, which will not be repeated here.

[0082] The analysis module 440 is used to analyze each of the depth portraits, obtain analysis results corresponding to the SQL statements, and perform prompt and warning processing according to the analysis results. In one embodiment, the analysis module 440 can be used to perform the operation S240 described above, which will not be repeated here.

[0083] In an embodiment of the present disclosure, the construction module 430 can also be used to: use a machine learning algorithm to cluster each of the depth portraits to obtain different statement clusters; and based on pre-established business rules, use a rule engine to classify each SQL statement in each of the statement clusters to obtain the classified SQL statements in each of the statement clusters.

[0084] In the embodiment of the present disclosure, the analysis module 440 is specifically used to: utilize the deep profile to perform comparative analysis on the resource consumption dimension of each statement cluster to locate the SQL statements with performance bottlenecks; and automatically or semi-automatically generate corresponding optimization suggestions for the SQL statements with performance bottlenecks.

[0085] In an embodiment of the present disclosure, the analysis module 440 may also be used to: utilize the deep profile to perform comparative analysis on the SQL statement in the user behavior dimension to obtain a business usage pattern of the target database; and adjust the target database according to the business usage pattern.

[0086] In an embodiment of the present disclosure, the analysis module 440 can also be used to: based on the deep profile of each of the SQL statements, use a pre-trained trend prediction model to predict the execution trend of the SQL statement in the future time period to obtain a prediction result; compare the prediction result with a preset threshold to determine whether the prediction result is greater than the preset threshold; and when the prediction result is greater than the preset threshold, trigger an anomaly detection mechanism and generate early warning information.

[0087] In the embodiment of the present disclosure, the analysis module 440 may also be used to convert the analysis results into statistical reports so that target personnel can obtain the performance status of the target database and the execution status of the SQL statements.

[0088] In the embodiment of the present disclosure, the acquisition module 410 may also be used to: perform data cleaning on the SQL statements and remove duplicate SQL statements; and perform formatting processing on the cleaned and deduplicated SQL statements.

[0089] According to an embodiment of the present disclosure, any multiple modules among the acquisition module 410, the extraction module 420, the construction module 430 and the analysis module 440 can be combined into one module for implementation, or any one of the modules can be split into multiple modules. Alternatively, at least part of the functions of one or more of these modules can be combined with at least part of the functions of other modules and implemented in one module. According to an embodiment of the present disclosure, at least one of the acquisition module 410, the extraction module 420, the construction module 430 and the analysis module 440 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application specific integrated circuit (ASIC), or can be implemented by hardware or firmware such as any other reasonable way of integrating or packaging the circuit, or implemented in any one of the three implementation methods of software, hardware and firmware or in any appropriate combination of any of them. Alternatively, at least one of the acquisition module 410, the extraction module 420, the construction module 430 and the analysis module 440 can be at least partially implemented as a computer program module, and when the computer program module is run, the corresponding function can be executed.

[0090] Figure 5 A block diagram schematically shows an electronic device for a method for processing SQL statements according to an embodiment of the present disclosure.

[0091] like Figure 5As shown, the electronic device 500 according to an embodiment of the present invention includes a processor 501, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 502 or a program loaded from a storage part 508 to a random access memory (RAM) 503. The processor 501 may include, for example, a general-purpose microprocessor (such as a CPU), an instruction set processor and / or a related chipset and / or a special-purpose microprocessor (for example, an application-specific integrated circuit (ASIC)), etc. The processor 501 may also include an onboard memory for caching purposes. The processor 501 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present invention.

[0092] In RAM 503, various programs and data required for the operation of electronic device 500 are stored. Processor 501, ROM 502 and RAM 503 are connected to each other via bus 504. Processor 501 performs various operations of the method flow according to the embodiment of the present invention by executing the program in ROM 502 and / or RAM 503. It should be noted that the program can also be stored in one or more memories other than ROM 502 and RAM 503. Processor 501 can also perform various operations of the method flow according to the embodiment of the present invention by executing the program stored in the one or more memories.

[0093] According to an embodiment of the present invention, the electronic device 500 may further include an input / output (I / O) interface 505, which is also connected to the bus 504. The electronic device 500 may further include one or more of the following components connected to the input / output (I / O) interface 505: an input portion 506 including a keyboard, a mouse, etc.; an output portion 507 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage portion 508 including a hard disk, etc.; and a communication portion 509 including a network interface card such as a LAN card, a modem, etc. The communication portion 509 performs communication processing via a network such as the Internet. A drive 510 is also connected to the input / output (I / O) interface 505 as needed. A removable medium 511, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 510 as needed, so that the computer program read therefrom is installed into the storage portion 508 as needed.

[0094] The present invention also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiment; or may exist independently without being assembled into the device / apparatus / system. The above computer-readable storage medium carries one or more programs, and when the above one or more programs are executed, the method according to the embodiment of the present invention is implemented.

[0095] According to an embodiment of the present invention, the computer-readable storage medium may be a non-volatile computer-readable storage medium, for example, may include but is not limited to: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present invention, the computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, an apparatus or a device. For example, according to an embodiment of the present invention, the computer-readable storage medium may include the ROM 502 and / or RAM 503 described above and / or one or more memories other than ROM 502 and RAM 503.

[0096] The embodiment of the present disclosure also includes a computer program product, which includes a computer program, and the computer program contains program code for executing the method shown in the flowchart. When the computer program product is run in a computer system, the program code is used to enable the computer system to implement the SQL statement processing method provided by the embodiment of the present disclosure.

[0097] The above functions defined in the system / device of the embodiment of the present disclosure are performed when the computer program is executed by the processor 501. According to the embodiment of the present disclosure, the system, device, module, unit, etc. described above can be implemented by a computer program module.

[0098] In one embodiment, the computer program may rely on tangible storage media such as optical storage devices, magnetic storage devices, etc. In another embodiment, the computer program may also be transmitted and distributed in the form of signals on a network medium, and downloaded and installed through the communication part 509, and / or installed from the removable medium 511. The program code contained in the computer program may be transmitted using any appropriate network medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0099] In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 509, and / or installed from the removable medium 511. When the computer program is executed by the processor 501, the above functions defined in the system of the embodiment of the present disclosure are performed. According to the embodiment of the present disclosure, the system, device, means, module, unit, etc. described above can be implemented by a computer program module.

[0100] According to an embodiment of the present disclosure, the program code for executing the computer program provided by the embodiment of the present disclosure can be written in any combination of one or more programming languages. Specifically, these computing programs can be implemented using high-level process and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, Java, C++, python, "C" language or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on the remote computing device, or entirely on the remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, using an Internet service provider to connect through the Internet).

[0101] The flow charts and block diagrams in the accompanying drawings illustrate the possible architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present disclosure. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0102] It will be appreciated by those skilled in the art that the features described in the various embodiments of the present disclosure may be combined and / or combined in a variety of ways, even if such combinations or combinations are not explicitly described in the present disclosure. In particular, without departing from the spirit and teachings of the present disclosure, the features described in the various embodiments of the present disclosure may be combined and / or combined in a variety of ways. All of these combinations and / or combinations fall within the scope of the present disclosure.

[0103] The embodiments of the present disclosure are described above. However, these embodiments are only for illustrative purposes and are not intended to limit the scope of the present disclosure. Although the embodiments are described above, this does not mean that the measures in the various embodiments cannot be used in combination to advantage. Without departing from the scope of the present disclosure, those skilled in the art may make a variety of substitutions and modifications, which should all fall within the scope of the present disclosure.

Claims

1. A method for processing SQL statements, characterized in that: The method comprises: Monitor the execution log of the target database and obtain the SQL statements in the execution log; Performing multi-dimensional feature extraction on the SQL statement to obtain multi-dimensional data feature values, wherein the dimensions at least include grammatical structure, data operation, resource consumption, and user behavior; Based on the multidimensional data feature values, construct a depth profile for each of the SQL statements, wherein the depth profile is used to characterize the metadata description of the SQL statement; and Each of the depth portraits is analyzed to obtain an analysis result corresponding to the SQL statement, and prompts and warnings are performed according to the analysis result.

2. The method according to claim 1, characterized in that The method further comprises: Using a machine learning algorithm, clustering the depth portraits to obtain different sentence clusters; and Based on pre-defined business rules, each SQL statement in each of the statement clusters is classified using a rule engine to obtain the classified SQL statements in each of the statement clusters.

3. The method according to claim 2, characterized in that The analyzing of each of the depth portraits to obtain an analysis result corresponding to the SQL statement includes: Using the deep profile, performing comparative analysis on the resource consumption dimension for each of the statement clusters to locate the SQL statements with performance bottlenecks; and For the SQL statements with performance bottlenecks, corresponding optimization suggestions are automatically or semi-automatically generated.

4. The method according to claim 2 or 3, characterized in that: The analyzing each of the depth portraits to obtain an analysis result corresponding to the SQL statement further includes: Using the deep profile, performing comparative analysis on the SQL statements in terms of the user behavior dimension to obtain a business usage pattern of the target database; and The target database is adjusted according to the business usage pattern.

5. The method according to claim 1, characterized in that: The method further comprises: Based on the deep profile of each of the SQL statements, using a pre-trained trend prediction model, the execution trend of the SQL statement in a future time period is predicted to obtain a prediction result; Compare the prediction result with a preset threshold to determine whether the prediction result is greater than the preset threshold; and When the prediction result is greater than the preset threshold, the anomaly detection mechanism is triggered and an early warning message is generated.

6. The method according to claim 1 or 5, characterized in that: The method further comprises: The analysis results are converted into statistical reports so that target personnel can obtain the performance status of the target database and the execution status of the SQL statements.

7. The method according to claim 1, characterized in that The method further comprises: Performing data cleaning on the SQL statements and removing duplicate SQL statements; and The SQL statements after cleaning and deduplication are formatted.

8. A SQL statement processing device, characterized in that: The device comprises: An acquisition module, used to monitor the execution log of the target database and acquire the SQL statements in the execution log; An extraction module, used for performing multi-dimensional feature extraction on the SQL statement to obtain multi-dimensional data feature values, wherein the dimensions at least include grammatical structure, data operation, resource consumption and user behavior; A construction module, configured to construct a depth profile for each of the SQL statements based on the multidimensional data feature values, wherein the depth profile is used to characterize a metadata description of the SQL statement; and The analysis module is used to analyze each of the depth portraits to obtain analysis results corresponding to the SQL statements, and to perform prompts and warnings based on the analysis results.

9. An electronic device, comprising: one or more processors; a storage device for storing one or more computer programs, It is characterized in that the one or more processors execute the one or more computer programs to implement the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

11. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images