SQL statement performance auditing method and device, electronic equipment and storage medium

Abstract

The invention provides an SQL statement performance auditing method and device, electronic equipment and a storage medium, and relates to database related technologies, the method comprises the following steps: intercepting a to-be-executed SQL statement, analyzing the SQL statement to obtain an attribute parameter of the SQL statement, the attribute parameter at least comprising an operation type; determining an execution time threshold matched with the operation type; after the SQL statement is executed, an execution record of the SQL statement is obtained, and the execution record comprises execution time and attribute parameters of the SQL statement; detecting whether the SQL statement is a slow query or not according to a size relationship between the execution time and an execution time threshold value, and storing a detection result into an execution record; and performing performance analysis on the execution record to obtain a performance analysis result of the SQL statement, and generating an optimization suggestion based on the performance analysis result. According to the method and the device, comprehensive performance auditing can be carried out on the SQL statements, and accurate optimization suggestions can be generated.

Description

Technical Field

[0001] This application relates to the fields of database performance monitoring and SQL optimization technology, and in particular to a method, apparatus, electronic device and storage medium for performance auditing of SQL statements. Background Technology

[0002] In the practice of database SQL performance auditing, the following technical challenges are encountered: 1. Insufficient monitoring granularity: lack of detailed monitoring based on operation type (SELECT, INSERT, UPDATE, etc.); 2. Inflexible threshold settings: inability to set differentiated performance thresholds for different SQL operation types; 3. Incomplete audit information: lack of detailed audit information such as SQL statements, parameters, and number of affected rows.

[0003] Therefore, a new method for performance auditing SQL statements is urgently needed to solve the above problems. Summary of the Invention

[0004] In view of this, this application provides a method, apparatus, electronic device and storage medium for performance auditing of SQL statements, which can perform comprehensive performance auditing of SQL statements and generate accurate optimization suggestions.

[0005] A first aspect of this application provides a performance auditing method for SQL statements, comprising: intercepting an SQL statement to be executed; parsing the SQL statement to obtain attribute parameters of the SQL statement, wherein the attribute parameters include at least an operation type; determining an execution time threshold matching the operation type; after the SQL statement is executed, obtaining an execution record of the SQL statement, wherein the execution record includes the execution time of the SQL statement and the attribute parameters; detecting whether the SQL statement is a slow query based on the relationship between the execution time and the execution time threshold, and storing the detection result in the execution record; performing performance analysis on the execution record to obtain a performance analysis result of the SQL statement, and generating optimization suggestions based on the performance analysis result.

[0006] In one possible implementation, the performance analysis of the execution record includes: performing execution plan analysis, resource usage analysis, index usage analysis, and table structure analysis on the execution record; the generation of optimization suggestions based on the performance analysis results includes: generating configuration optimization suggestions corresponding to the execution plan analysis, query optimization suggestions corresponding to the resource usage analysis, index optimization suggestions corresponding to the index usage analysis, and structure optimization suggestions corresponding to the table structure analysis based on the performance analysis results.

[0007] In one possible implementation, the execution plan analysis includes at least: scan type identification, join method analysis, and sorting operation detection; the resource usage analysis includes at least: CPU usage statistics, memory usage statistics, and input / output statistics; the index usage analysis includes at least: index hit rate detection, index selection analysis, and detection of missing indexes; and the table structure analysis includes at least: table size analysis, data distribution analysis, and statistical information check.

[0008] In one possible implementation, the method further includes: updating the performance data of the SQL statement in real time during the execution of the SQL statement, wherein the performance data includes at least the query rate per second, the average response time, and the number of slow queries whose execution time exceeds a preset threshold per unit time; performing real-time detection on each type of performance data according to a matching preset alarm condition; and sending alarm information based on the target performance data when it is detected that there is target performance data of a target type that meets the matching target preset alarm condition.

[0009] In one possible implementation, sending alarm information based on the target performance data includes: performing data analysis on the target performance data to obtain an alarm level evaluation result; determining the alarm level of the alarm information based on the alarm level evaluation result; and sending the alarm information according to a preset information sending method corresponding to the alarm level.

[0010] In one possible implementation, the step of real-time detection of each type of performance data according to a pre-defined alarm condition includes: for each type of performance data, comparing the size relationship between the performance data and a matching performance data threshold in real time; and when it is detected that there is target performance data of a target type in the performance data that meets the matching target pre-defined alarm condition, sending alarm information based on the target performance data includes: when it is detected that there is target performance data in the performance data that is greater than the matching target performance data threshold, sending alarm information based on the target performance data.

[0011] In one possible implementation, after generating optimization suggestions based on the performance analysis results, the method further includes: generating an initial visualization report based on the performance analysis results and the optimization suggestions; determining the output format of the visualization report; and converting the initial visualization report according to the output format to generate a final visualization report.

[0012] Secondly, embodiments of this application also provide a performance auditing device for SQL statements, comprising: a parsing module, a determining module, an acquiring module, a detecting module, and an analysis and optimization module; the parsing module is used to intercept an SQL statement to be executed, parse the SQL statement, and obtain attribute parameters of the SQL statement, wherein the attribute parameters include at least an operation type; the determining module is used to determine an execution time threshold matching the operation type; the acquiring module is used to obtain an execution record of the SQL statement after the SQL statement is executed, wherein the execution record includes the execution time of the SQL statement and the attribute parameters; the detecting module is used to detect whether the SQL statement is a slow query based on the relationship between the execution time and the execution time threshold, and store the detection result in the execution record; the analysis and optimization module is used to perform performance analysis on the execution record, obtain the performance analysis result of the SQL statement, and generate optimization suggestions based on the performance analysis result.

[0013] Thirdly, embodiments of this application also provide an electronic device, the electronic device including a processor and a memory, the memory being used to store instructions, and the processor being used to call the instructions in the memory, causing the electronic device to execute the performance auditing method for SQL statements as described in the first aspect.

[0014] Fourthly, embodiments of this application also provide a computer-readable storage medium that stores computer instructions that, when executed on an electronic device, cause the electronic device to perform a performance auditing method for SQL statements as described in the first aspect.

[0015] Compared with related technologies, the embodiments of this application have at least the following advantages: By intercepting the SQL statement before execution, the SQL statement can be parsed, and its attribute parameters can be obtained, thus improving the reliability of subsequent performance auditing of the SQL statement. Since the attribute parameters include the operation type, after determining the execution time threshold matching the operation type, the execution time of the SQL statement can be used to accurately detect whether the SQL statement is a slow query. This allows for setting differentiated execution time thresholds for different SQL operation types, enabling detailed monitoring of SQL statements based on operation type. Furthermore, by obtaining the execution record after the SQL statement execution is completed and then performing performance analysis on the execution record, on the one hand, the execution record includes attribute parameters and detection results, enabling comprehensive performance auditing of the SQL statement; on the other hand, by generating optimization suggestions based on the performance analysis results, the optimization suggestions are more targeted and thus more accurate.

[0016] The technical effects achieved by the second, third, and fourth aspects mentioned above are similar to those achieved by the corresponding technical means in the first aspect, and will not be repeated here. Attached Figure Description

[0017] Figure 1 A flowchart illustrating the steps of a performance auditing method for SQL statements provided in an embodiment of this application; Figure 2 A flowchart illustrating another step of the SQL statement performance auditing method provided in an embodiment of this application; Figure 3 A flowchart illustrating another step of the SQL statement performance auditing method provided in an embodiment of this application; Figure 4 A functional block diagram of an SQL statement performance auditing device provided in an embodiment of this application; Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0018] To better understand the above-mentioned objectives, features, and advantages of this application, the application will be described in detail below with reference to the accompanying drawings and specific embodiments. It should be noted that, unless otherwise specified, the embodiments and features described in these embodiments can be combined with each other.

[0019] The following description sets forth many specific details to provide a full understanding of this application. The described embodiments are only some, not all, of the embodiments of this application.

[0020] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein in the specification of this application is for the purpose of describing particular embodiments only and is not intended to be limiting of this application.

[0021] It should be further noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0022] In this application, "at least one" means one or more, and "more than one" means two or more. "And / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A alone, A and B simultaneously, or B alone, where A and B can be singular or plural. The terms "first," "second," "third," "fourth," etc. (if present) in the specification, claims, and drawings of this application are used to distinguish similar objects, not to describe a specific order or sequence.

[0023] In the embodiments of this application, the terms "exemplary" or "for example" are used to indicate examples, illustrations, or descriptions. Any embodiment or design described as "exemplary" or "for example" in the embodiments of this application should not be construed as being more preferred or advantageous than other embodiments or designs. Specifically, the use of terms such as "exemplary" or "for example" is intended to present the relevant concepts in a specific manner.

[0024] For ease of understanding, some concepts related to the embodiments of this application are illustrated and explained by way of example for reference.

[0025] SQL (Structured Query Language) is the standard language for relational databases, used for querying, updating, defining, and controlling data. SQL is a high-level declarative language; users only need to operate based on the relational model without needing to concern themselves with the underlying storage and implementation details. Its syntax is highly unified and portable, and it is widely supported by various mainstream database systems. SQL not only supports complex expressions such as nested queries but also possesses strong flexibility and extensibility, thus becoming the core tool for managing and operating relational databases, holding a fundamental and authoritative position in the database field.

[0026] Please refer to Figure 1 , Figure 1 This is a flowchart illustrating the steps of an embodiment of the SQL statement performance auditing method of this application. Depending on different requirements, the order of the steps in this flowchart can be changed, and some steps can be omitted.

[0027] It should be noted that the SQL statement performance auditing method of this application embodiment can be applied to SQL statement execution scenarios, and its execution subject can be an SQL statement performance auditing device. For example, the SQL statement performance auditing device can be used to audit the performance of the SQL statement throughout its entire execution process. Of course, the SQL statement performance auditing method of this application embodiment can also be applied to other scenarios that require SQL statement performance auditing, and this application does not specifically limit it in this regard.

[0028] The specific process of this embodiment is as follows: Figure 1 As shown, it includes the following steps: S101, intercept the SQL statement to be executed, parse the SQL statement, and obtain the attribute parameters of the SQL statement, wherein the attribute parameters include at least the operation type.

[0029] In some embodiments, the attribute parameters also include statement structure and parameter information.

[0030] In some embodiments, SQL statements and parameters are captured in pre- and post-execution hooks by an SQL execution interceptor, and preliminary statement structure extraction and hash calculation are performed. Syntax parsing is then performed in conjunction with the database driver and built-in parser (ANTLR, database native execution plan) to obtain statement structure, operation type and parameter information.

[0031] In some embodiments, the process of parsing an SQL statement includes identifying the operation type of the SQL statement. Specifically, this involves extracting keywords from the beginning of the SQL statement, such as const operationType='SELECT'. That is, the operation type of the SQL statement is identified by scanning the first keywords (such as SELECT, INSERT, UPDATE, DELETE, etc.) of the SQL statement through string matching or regular expressions.

[0032] In some embodiments, the operation types include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, etc.

[0033] S102, Determine the execution time threshold that matches the operation type.

[0034] In some embodiments, an independent execution time threshold is set for each operation type. Specifically, the execution time threshold is set to 1000ms for the operation type SELECT, 500ms for the operation type INSERT, 500ms for the operation type UPDATE, 500ms for the operation type DELETE, 2000ms for the operation type CREATE, 2000ms for the operation type DROP, and 3000ms for the operation type ALTER.

[0035] S103. After the SQL statement is executed, obtain the execution record of the SQL statement, which includes the execution time and attribute parameters of the SQL statement.

[0036] In some embodiments, the execution record specifically includes: id (unique identifier), user_id (user ID), sql_statement (SQL statement), sql_hash (SQL hash value), parameters (parameters), execution_time_ms (execution time in milliseconds), rows_affected (number of rows affected), database_name (database name), table_names (array of table names involved), operation_type (operation type, such as 'SELECT'), is_slow_query (whether it is a slow query), slow_query_threshold_ms (threshold), execution_status (execution status, such as 'success'), and created_at (creation time).

[0037] S104: Detect whether an SQL statement is a slow query based on the relationship between execution time and execution time threshold, and store the detection result in the execution record.

[0038] Specifically, if the execution time exceeds the execution time threshold, the SQL statement is considered a slow query and is marked as such.

[0039] S105: Perform performance analysis on the execution records to obtain the performance analysis results of the SQL statements, and generate optimization suggestions based on the performance analysis results.

[0040] In some embodiments, performance analysis of execution records includes: execution plan analysis, resource usage analysis, index usage analysis, and table structure analysis of execution records; and generating optimization suggestions based on the performance analysis results, including: generating configuration optimization suggestions corresponding to execution plan analysis, query optimization suggestions corresponding to resource usage analysis, index optimization suggestions corresponding to index usage analysis, and structure optimization suggestions corresponding to table structure analysis based on the performance analysis results.

[0041] Specifically, execution plan analysis should include at least: scan type identification, join method analysis, and sorting operation detection; resource usage analysis should include at least: CPU usage statistics, memory usage statistics, and input / output statistics; index usage analysis should include at least: index hit rate detection, index selection analysis, and detection of missing indexes; table structure analysis should include at least: table size analysis, data distribution analysis, and statistical information check.

[0042] More specifically, the performance analysis engine identifies performance bottlenecks in execution records, including parsing execution plans, analyzing resource usage, evaluating index usage, and extracting a bottleneck list. Based on the degree and type of impact (such as sorting operations, join operations, and missing indexes), it automatically identifies and provides optimization suggestions for different types of issues by combining historical data and machine learning models.

[0043] Compared with related technologies, the embodiments of this application have at least the following advantages: By intercepting the SQL statement before execution, the SQL statement can be parsed, thereby obtaining the attribute parameters of the SQL statement and improving the reliability of subsequent performance auditing of the SQL statement. Since the attribute parameters include the operation type, after determining the execution time threshold matching the operation type, the execution time of the SQL statement can be used to accurately detect whether the SQL statement is a slow query, realizing the setting of differentiated execution time thresholds for different SQL operation types, thereby enabling detailed monitoring of SQL statements according to operation type. In addition, by obtaining the execution record after the SQL statement is executed, and then performing performance analysis on the execution record, on the one hand, the execution record includes attribute parameters and detection results, enabling comprehensive performance auditing of the SQL statement; on the other hand, by generating optimization suggestions based on the performance analysis results, the optimization suggestions are more targeted, thereby making the optimization suggestions more accurate.

[0044] Please refer to Figure 2 , Figure 2 This is a flowchart illustrating the steps of an embodiment of the SQL statement performance auditing method of this application. Depending on different requirements, the order of the steps in this flowchart can be changed, and some steps can be omitted. This SQL statement performance auditing method can be applied to the aforementioned SQL statement performance auditing device, but is not limited thereto, and this embodiment of the application does not limit it in this regard.

[0045] This embodiment is a further improvement upon the foregoing embodiment. The main improvement lies in that, during the execution of the SQL statement, the performance data of the SQL statement is updated in real time, and each type of performance data is detected in real time according to matching preset alarm conditions. This approach further enhances the monitoring capability during SQL statement execution, thereby making the performance auditing method for SQL statements in this embodiment more reliable.

[0046] The specific process of this embodiment is as follows: Figure 2 As shown, it includes the following steps: S201, intercept the SQL statement to be executed, parse the SQL statement, and obtain the attribute parameters of the SQL statement, wherein the attribute parameters include at least the operation type.

[0047] S202, Determine the execution time threshold that matches the operation type.

[0048] S203: After the SQL statement is executed, obtain the execution record of the SQL statement. The execution record includes the execution time and attribute parameters of the SQL statement.

[0049] S204: Detect whether an SQL statement is a slow query based on the relationship between its execution time and the execution time threshold, and store the detection result in the execution record.

[0050] S205 performs performance analysis on the execution records, obtains the performance analysis results of the SQL statements, and generates optimization suggestions based on the performance analysis results.

[0051] S201 to S205 of this embodiment are similar to S101 to S105 of the previous embodiment. To avoid repetition, they will not be described again here.

[0052] S206 updates the performance data of the SQL statement in real time during the execution of the SQL statement.

[0053] Specifically, performance data should include at least the query rate per second, average response time, and the number of slow queries whose execution time exceeds a preset threshold per unit of time.

[0054] In some embodiments, performance data may also include total slow queries over a time period, average execution time, total execution time, operation type distribution, top slow query list, and recommended measures.

[0055] S207 performs real-time detection of each type of performance data based on matching preset alarm conditions.

[0056] In some embodiments, each type of performance data is detected in real time according to a preset alarm condition that matches it, including: for each type of performance data, comparing the magnitude of the performance data with the magnitude of the matching performance data threshold in real time.

[0057] S208, if it is detected that there is target performance data of the target type in the performance data that meets the target preset alarm conditions, an alarm message is sent according to the target performance data.

[0058] In some embodiments, if a target performance data is detected to be greater than a matching target performance data threshold, an alarm message is sent based on the target performance data.

[0059] In some embodiments, sending alarm information based on target performance data can be achieved by: performing data analysis on the target performance data to obtain alarm level evaluation results; determining the alarm level of the alarm information based on the alarm level evaluation results; and sending the alarm information according to a preset information sending method corresponding to the alarm level.

[0060] Specifically, alert level assessment is based on severity (e.g., 'warning' / 'critical'), impact scope (number of rows affected, number of users affected), and duration, combined with bottleneck impact (high / medium / low) and historical frequency. For example, a peak in slow query rate combined with high-impact operations (e.g., UPDATE a large table) is assessed as a 'warning', while resource exhaustion is assessed as 'critical', with dynamic scoring through a rules engine.

[0061] In some embodiments, alarm levels can be divided into three levels: emergency, warning, and information. When the alarm level is emergency, alarm information is immediately sent through multiple channels; when the alarm level is warning, alarm information is sent with a delay or at a time; when the alarm level is information, alarm information can be directly recorded in the log.

[0062] It is worth noting that the alarm information in this embodiment includes, but is not limited to, slow query alarms after a surge in slow queries, performance alarms after performance degradation, error alarms after an increase in error rate, and resource alarms after resource anomalies.

[0063] Compared with related technologies, the embodiments of this application have at least the following advantages: By intercepting the SQL statement before execution, the SQL statement can be parsed, thereby obtaining the attribute parameters of the SQL statement and improving the reliability of subsequent performance auditing of the SQL statement. Since the attribute parameters include the operation type, after determining the execution time threshold matching the operation type, the execution time of the SQL statement can be used to accurately detect whether the SQL statement is a slow query, realizing the setting of differentiated execution time thresholds for different SQL operation types, thereby enabling detailed monitoring of SQL statements according to operation type. In addition, by obtaining the execution record after the SQL statement is executed, and then performing performance analysis on the execution record, on the one hand, the execution record includes attribute parameters and detection results, enabling comprehensive performance auditing of the SQL statement; on the other hand, by generating optimization suggestions based on the performance analysis results, the optimization suggestions are more targeted, thereby making the optimization suggestions more accurate.

[0064] Please refer to Figure 3 , Figure 3This is a flowchart illustrating the steps of an embodiment of the SQL statement performance auditing method of this application. Depending on different requirements, the order of the steps in this flowchart can be changed, and some steps can be omitted. This SQL statement performance auditing method can be applied to the aforementioned SQL statement performance auditing device, but is not limited thereto, and this embodiment of the application does not limit it in this regard.

[0065] This embodiment is a further improvement upon the foregoing embodiment. The main improvement lies in the fact that, in this embodiment, a visual report containing performance analysis results and optimization suggestions is also generated. This allows users to perform intuitive interactive analysis through the visual report, improving user convenience and experience.

[0066] The specific process of this embodiment is as follows: Figure 2 As shown, it includes the following steps: S301 intercepts the SQL statement to be executed, parses the SQL statement, and obtains the attribute parameters of the SQL statement, where the attribute parameters include at least the operation type.

[0067] S302, Determine the execution time threshold that matches the operation type.

[0068] S303: After the SQL statement is executed, obtain the execution record of the SQL statement. The execution record includes the execution time and attribute parameters of the SQL statement.

[0069] S304: Detect whether an SQL statement is a slow query based on the relationship between its execution time and the execution time threshold, and store the detection result in the execution record.

[0070] S305 performs performance analysis on the execution records, obtains the performance analysis results of the SQL statements, and generates optimization suggestions based on the performance analysis results.

[0071] S301 to S305 in this embodiment are similar to S101 to S105 in the previous embodiment. To avoid repetition, they will not be described again here.

[0072] S306 generates an initial visualization report based on performance analysis results and optimization suggestions.

[0073] In some embodiments, the initial visualization report includes, but is not limited to, a performance overview report of SQL statements, a slow query analysis report, a trend analysis report, and an optimization suggestion report.

[0074] Specifically, the performance overview report includes statistics on SQL statement performance metrics, distribution of operation types, and statistics on user activity; the slow query analysis report includes the top slow queries for SQL statements, slow query trend charts, and slow query classification statistics; the trend analysis report includes performance trend charts for SQL statements, time series analysis, and periodic pattern recognition; the optimization suggestion report has been described in detail in the aforementioned embodiments and will not be repeated here.

[0075] S307, Determine the output format of the visualization report.

[0076] In some embodiments, the visual report supports output in multiple formats such as PDF, HTML, and Excel.

[0077] S308 converts the format of the initial visualization report according to the output format to generate the final visualization report.

[0078] Compared with related technologies, the embodiments of this application have at least the following advantages: By intercepting the SQL statement before execution, the SQL statement can be parsed, thereby obtaining the attribute parameters of the SQL statement and improving the reliability of subsequent performance auditing of the SQL statement. Since the attribute parameters include the operation type, after determining the execution time threshold matching the operation type, the execution time of the SQL statement can be used to accurately detect whether the SQL statement is a slow query, realizing the setting of differentiated execution time thresholds for different SQL operation types, thereby enabling detailed monitoring of SQL statements according to operation type. In addition, by obtaining the execution record after the SQL statement is executed, and then performing performance analysis on the execution record, on the one hand, the execution record includes attribute parameters and detection results, enabling comprehensive performance auditing of the SQL statement; on the other hand, by generating optimization suggestions based on the performance analysis results, the optimization suggestions are more targeted, thereby making the optimization suggestions more accurate.

[0079] Based on the same idea as the SQL statement performance auditing method in the above embodiments, this application also provides an SQL statement performance auditing device, which can be used to execute the above SQL statement performance auditing method. For ease of explanation, the structural diagram of the SQL statement performance auditing device embodiment only shows the parts related to the embodiments of this application. Those skilled in the art will understand that the illustrated structure does not constitute a limitation on the device, and may include more or fewer components than illustrated, or combine certain components, or have different component arrangements.

[0080] like Figure 4As shown, the SQL statement performance auditing device 40 includes a parsing module 401, a determining module 402, an acquiring module 403, a detecting module 404, and an analysis and optimization module 405. In some embodiments, the above modules can be programmable software instructions stored in memory and executable by a processor. It is understood that in other embodiments, the above modules can also be program instructions or firmware embedded in the processor.

[0081] The parsing module 401 is used to intercept the SQL statement to be executed, parse the SQL statement, and obtain the attribute parameters of the SQL statement, wherein the attribute parameters include at least the operation type; The determining module 402 is used to determine the execution time threshold that matches the operation type; The acquisition module 403 is used to acquire the execution record of the SQL statement after the SQL statement is executed, wherein the execution record includes the execution time of the SQL statement and the attribute parameters; The detection module 404 is used to detect whether the SQL statement is a slow query based on the relationship between the execution time and the execution time threshold, and to store the detection result in the execution record; The analysis and optimization module 405 is used to perform performance analysis on the execution record, obtain the performance analysis results of the SQL statement, and generate optimization suggestions based on the performance analysis results.

[0082] The SQL statement performance auditing device 40 provided in the above embodiments can implement the technical solutions described in the SQL statement performance auditing method embodiments. The specific implementation principles of each module or unit can be found in the corresponding content in the SQL statement performance auditing method embodiments, which will not be repeated here.

[0083] Please refer to Figure 5 , Figure 5 This is a schematic diagram of an embodiment of the electronic device of this application. In this embodiment of the invention, the electronic device 500 includes a processor 501, a memory 502, and a display 503. Figure 5 Only some components of the electronic device 500 are shown, but it should be understood that it is not required to implement all the components shown, and more or fewer components may be implemented instead.

[0084] In some embodiments, processor 501 may be a central processing unit (CPU), microprocessor, or other data processing chip, used to run program code stored in memory 502 or process data, such as the performance auditing method for SQL statements in this invention.

[0085] In some embodiments, processor 501 may be a single server or a group of servers. The server group may be centralized or distributed. In some embodiments, processor 501 may be local or remote. In some embodiments, processor 501 may be implemented on a cloud platform. In one embodiment, the cloud platform may include a private cloud, public cloud, hybrid cloud, community cloud, distributed cloud, intranet, multi-cloud, etc., or any combination thereof.

[0086] In some embodiments, memory 502 may be an internal storage unit of electronic device 500, such as a hard disk or memory of electronic device 500. In other embodiments, memory 502 may also be an external storage device of electronic device 500, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc. equipped on electronic device 500.

[0087] Furthermore, the memory 502 may include both internal storage units of the electronic device 500 and external storage devices. The memory 502 is used to store application software and various types of data installed on the electronic device 500.

[0088] In some embodiments, display 503 may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, or an OLED (Organic Light-Emitting Diode) touchscreen. Display 503 is used to display information from electronic device 500 and to display visual user applications. Components 501-503 of electronic device 500 communicate with each other via a system bus.

[0089] In one embodiment, when processor 501 executes a performance auditing program for SQL statements in memory 502, the following steps can be implemented: Intercept the SQL statement to be executed, parse the SQL statement, and obtain the attribute parameters of the SQL statement, wherein the attribute parameters include at least the operation type; Determine the execution time threshold that matches the operation type; After the SQL statement is executed, the execution record of the SQL statement is obtained, wherein the execution record includes the execution time of the SQL statement and the attribute parameters; Based on the relationship between the execution time and the execution time threshold, the SQL statement is detected as a slow query, and the detection result is stored in the execution record. The execution records are subjected to performance analysis to obtain the performance analysis results of the SQL statements, and optimization suggestions are generated based on the performance analysis results.

[0090] It should be understood that when the processor 501 executes the performance audit program for the SQL statements in the memory 502, in addition to the functions mentioned above, it can also perform other functions, as can be found in the description of the corresponding method embodiments above.

[0091] Furthermore, this embodiment of the invention does not specifically limit the type of electronic device 500 mentioned. Electronic device 500 can be a mobile phone, tablet computer, personal digital assistant (PDA), wearable device, laptop computer, or other portable electronic device. Exemplary embodiments of portable electronic devices include, but are not limited to, portable electronic devices running iOS, Android, Microsoft, or other operating systems. The aforementioned portable electronic device can also be other portable electronic devices, such as a laptop computer with a touch-sensitive surface (e.g., a touch panel). It should also be understood that in some other embodiments of the invention, electronic device 500 may not be a portable electronic device, but rather a desktop computer with a touch-sensitive surface (e.g., a touch panel).

[0092] Accordingly, this application also provides a computer-readable storage medium for storing a computer-readable program or instruction. When the program or instruction is executed by a processor, it can implement the steps or functions of the SQL statement performance auditing method provided in the above-described method embodiments.

[0093] Those skilled in the art will understand that all or part of the processes of the methods described in the above embodiments can be implemented by a computer program instructing related hardware (such as a processor, controller, etc.), and the computer program can be stored in a computer-readable storage medium. The computer-readable storage medium may be a disk, optical disk, read-only memory, or random access memory, etc.

[0094] The performance auditing method, apparatus, electronic device, and computer-readable storage medium for SQL statements provided in this application have been described in detail above. Specific examples have been used to illustrate the principles and implementation methods of this application. The descriptions of the above embodiments are only for the purpose of helping to understand the method and core ideas of this application. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this application. Therefore, the content of this specification should not be construed as a limitation of this application.

Claims

1. A method for performance auditing SQL statements, characterized in that, include: Intercept the SQL statement to be executed, parse the SQL statement, and obtain the attribute parameters of the SQL statement, wherein the attribute parameters include at least the operation type; Determine the execution time threshold that matches the operation type; After the SQL statement is executed, the execution record of the SQL statement is obtained, wherein the execution record includes the execution time of the SQL statement and the attribute parameters; Based on the relationship between the execution time and the execution time threshold, the SQL statement is detected as a slow query, and the detection result is stored in the execution record. The execution records are subjected to performance analysis to obtain the performance analysis results of the SQL statements, and optimization suggestions are generated based on the performance analysis results.

2. The SQL statement performance auditing method according to claim 1, characterized in that, The performance analysis of the execution records includes: The execution records are analyzed for execution plan, resource usage, index usage, and table structure. The process of generating optimization suggestions based on the performance analysis results includes: Based on the performance analysis results, configuration optimization suggestions corresponding to the execution plan analysis, query optimization suggestions corresponding to the resource usage analysis, index optimization suggestions corresponding to the index usage analysis, and structure optimization suggestions corresponding to the table structure analysis are generated.

3. The SQL statement performance auditing method according to claim 2, characterized in that, The execution plan analysis includes at least: scan type identification, connection method analysis, and sorting operation detection; The resource usage analysis includes at least: CPU usage statistics, memory usage statistics, and input / output statistics; The index usage analysis includes at least: index hit rate detection, index selection analysis, and detection of whether there is a lack of indexes; The table structure analysis includes at least: table size analysis, data distribution analysis, and statistical information check.

4. The SQL statement performance auditing method according to claim 1, characterized in that, The method further includes: During the execution of the SQL statement, the performance data of the SQL statement is updated in real time. The performance data includes at least the query rate per second, the average response time, and the number of slow queries whose execution time exceeds a preset threshold per unit time. The performance data for each type is detected in real time according to the matching preset alarm conditions; If it is detected that there is target performance data of the target type in the performance data that meets the matching target preset alarm conditions, an alarm message is sent according to the target performance data.

5. The SQL statement performance auditing method according to claim 4, characterized in that, Sending alarm information based on the target performance data includes: Data analysis is performed on the target performance data to obtain alarm level evaluation results; The alarm level of the alarm information is determined based on the alarm level evaluation results; The alarm information is sent according to the preset information sending method corresponding to the alarm level.

6. The SQL statement performance auditing method according to claim 4, characterized in that, The performance data for each type is detected in real time according to preset alarm conditions that match it, including: For each type of performance data, the relationship between the performance data and the matching performance data threshold is compared in real time. When it is detected that there is target performance data of the target type in the performance data that meets the matching target preset alarm conditions, sending alarm information according to the target performance data includes: If it is detected that the target performance data is greater than the matching target performance data threshold, an alarm message is sent based on the target performance data.

7. The performance auditing method for SQL statements according to any one of claims 1 to 6, characterized in that, After generating optimization suggestions based on the performance analysis results, the method further includes: An initial visualization report is generated based on the performance analysis results and optimization suggestions. Determine the output format of the visualization report; The initial visualization report is converted according to the output format to generate the final visualization report.

8. A performance auditing device for SQL statements, characterized in that, include: The module includes a parsing module, a determination module, an acquisition module, a detection module, and an analysis and optimization module. The parsing module is used to intercept the SQL statement to be executed, parse the SQL statement, and obtain the attribute parameters of the SQL statement, wherein the attribute parameters include at least the operation type; The determining module is used to determine the execution time threshold that matches the operation type; The acquisition module is used to acquire the execution record of the SQL statement after the SQL statement is executed, wherein the execution record includes the execution time of the SQL statement and the attribute parameters; The detection module is used to detect whether the SQL statement is a slow query based on the relationship between the execution time and the execution time threshold, and to store the detection result in the execution record. The analysis and optimization module is used to perform performance analysis on the execution records, obtain the performance analysis results of the SQL statements, and generate optimization suggestions based on the performance analysis results.

9. An electronic device, the electronic device comprising a processor and a memory, characterized in that, The memory is used to store instructions, and the processor is used to invoke the instructions in the memory to cause the electronic device to execute the performance auditing method for the SQL statement as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that, when executed on an electronic device, cause the electronic device to perform a performance auditing method for SQL statements as described in any one of claims 1 to 7.

Images