SQL performance analysis method, system, device and medium based on tracking technology
Through the SQL performance analysis method based on tracking technology, dynamic tracking tools are used to obtain and analyze SQL statements, and performance detection is carried out in combination with execution plans, which solves the problems of low efficiency and high cost of manual detection, and realizes efficient and automated SQL performance monitoring and early warning. .
Patent Information
- Application Number
- CN202010939020.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2020-09-09
- Publication Date
- 2024-11-29
- Estimated Expiration
- 2040-09-09
AI Technical Summary
In the prior art, manual detection database performance is inefficient and costly, making it difficult to effectively solve SQL performance problems, affecting the response time and business processing of the application system.
Using SQL performance analysis method based on tracking technology, the application's original SQL statements and parameters are obtained through dynamic tracking tools, match and assemble, perform performance analysis in combination with execution plans, and send risk warnings when the number of scan lines exceeds the threshold to achieve automation performance detection.
It improves the efficiency and accuracy of SQL performance detection, reduces costs, can detect performance risks before going online, prevent major online events, and make the detection process easier and comprehensive.
Smart Images

Figure CN112069202B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of testing, and in particular to a SQL performance analysis method, system, device and medium based on tracing technology. Background Art
[0002] At present, the performance of the database is increasingly becoming the performance bottleneck of the entire application system. The quality of the data layer will cause the application to run slower, and will also affect the response time of the application system, resulting in poor user experience. The database business processing volume used by the online payment application system is huge. Once the database has performance risks, it may directly affect the business processing of the payment application system and cause order losses. However, SQL (Structured Query Language) performance testing is difficult, and manual testing of database performance is inefficient and costly. Summary of the invention
[0003] The technical problem to be solved by the present invention is to overcome the defects of low efficiency and high cost in the manual detection of database performance in the prior art, and to provide a SQL performance analysis method, system, device and medium based on tracking technology.
[0004] The present invention solves the above technical problems through the following technical solutions:
[0005] A SQL performance analysis method based on tracing technology, including:
[0006] Obtain the original SQL statements and corresponding parameters run by the application through the tracing plug-in;
[0007] Matching and assembling the original SQL statement with the parameters to obtain the SQL statement to be analyzed;
[0008] The SQL statement to be analyzed is subjected to performance analysis according to the execution plan to obtain a SQL performance analysis result.
[0009] Preferably, the step of obtaining the original SQL statement and corresponding parameters executed by the application program through the tracking plug-in specifically includes:
[0010] The dynamic tracing tool Btrace is used to set tracking points in the application to obtain the original SQL statements and corresponding parameters run by the application.
[0011] Preferably, the step of matching and assembling the original SQL statement with the parameters to obtain the SQL statement to be analyzed specifically includes:
[0012] Match the original SQL statement with the parameters and send them to the message queue;
[0013] The matched SQL statements in the message queue are received and assembled to form the SQL statement to be analyzed.
[0014] Preferably, the step of performing performance analysis on the SQL statement to be analyzed according to the execution plan to obtain the SQL performance analysis result specifically includes:
[0015] A performance analysis is performed on the SQL statement to be analyzed according to whether the number of scanned rows in the execution plan exceeds a threshold to obtain a SQL performance analysis result.
[0016] Preferably, after the step of performing performance analysis on the SQL statement to be analyzed according to whether the number of scanned rows in the execution plan exceeds a threshold to obtain a SQL performance analysis result, the SQL performance analysis method further comprises the following steps:
[0017] When the number of the scanned rows exceeds the threshold, a risk warning email is sent.
[0018] Preferably, before the step of obtaining the original SQL statements and corresponding parameters of the application program through the tracking plug-in, the SQL performance analysis method further includes:
[0019] Obtaining the running status of the application through the tracking plug-in;
[0020] Determining whether the operating state is abnormal;
[0021] If yes, stop acquiring the original SQL statement and the corresponding parameters and send a running abnormality message; and / or,
[0022] Before the step of obtaining the original SQL statement and corresponding parameters of the application program through the tracking plug-in, the SQL performance analysis method further includes:
[0023] The tracing plug-in receives the filtering information and stops acquiring the corresponding original SQL statement and parameters based on the filtering information.
[0024] A SQL performance analysis system based on tracing technology, comprising: a data tracing module and a data analysis module;
[0025] The data tracking module is used to obtain the original SQL statement and corresponding parameters of the application program through the tracking plug-in, and match the original SQL statement with the parameters to obtain a matching SQL statement, and send it to the data analysis module;
[0026] The data analysis module is used to obtain the matching SQL statements and assemble them to obtain the SQL statements to be analyzed, and perform performance analysis on the SQL statements to be analyzed according to the execution plan to obtain SQL performance analysis results.
[0027] Preferably, the data tracking module further comprises a data storage module;
[0028] The tracking plug-in is also used to match the original SQL statement with the parameters and send them to a message queue, and the message queue is set in the data storage module;
[0029] The data analysis module is also used to receive the matched SQL statements in the message queue in the data storage module and assemble them to form the SQL statement to be analyzed;
[0030] and / or,
[0031] The data analysis module is also used to determine whether the number of scan lines in the execution plan exceeds a threshold, and send a risk warning email when the number of scan lines exceeds the threshold.
[0032] Preferably, the tracking plug-in is also used to obtain the running status of the application and send it to the data analysis module;
[0033] The data analysis module is also used to determine whether the running state is abnormal, and if so, to send a running abnormality message to the tracking plug-in;
[0034] The tracing plug-in is further used to stop obtaining the original SQL statement and corresponding parameters based on the operation exception information.
[0035] Preferably, the data analysis module is further used to set filtering information and send the filtering information to the data tracking module, wherein the filtering information is used to filter preset SQL statements and corresponding parameter information;
[0036] The data tracking module is also used to receive the filtering signal through the tracking plug-in and stop obtaining the corresponding original SQL statement and parameters based on the filtering information.
[0037] An electronic device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, any one of the above-mentioned SQL performance analysis methods based on tracing technology is implemented.
[0038] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of any one of the above-mentioned SQL performance analysis methods based on tracing technology.
[0039] The positive and progressive effect of the present invention is that the present invention uses a tracking plug-in to obtain the original SQL statements and parameters of the application program, and performs performance analysis on the SQL statements through an execution plan after parameter matching and assembly. It is able to discover SQL performance risks before going online, resolve risks in a timely manner, and prevent major online incidents. Compared with manual testing of SQL performance in the system, it has high efficiency, low cost, and an easier and more comprehensive detection process. BRIEF DESCRIPTION OF THE DRAWINGS
[0040] Figure 1 This is a flowchart of the SQL performance analysis method based on tracing technology according to Example 1 of the present invention.
[0041] Figure 2 This is a flowchart of a SQL performance analysis method based on tracing technology according to Embodiment 2 of the present invention.
[0042] Figure 3 This is a flow chart of the application of the SQL performance analysis method based on tracing technology in Example 2 of the present invention.
[0043] Figure 4 This is a multi-application tracing diagram of the SQL performance analysis method based on tracing technology in Example 2 of the present invention.
[0044] Figure 5 This is a schematic diagram of the structure of the SQL performance analysis system based on tracing technology according to Example 3 of the present invention.
[0045] Figure 6 This is a schematic diagram of the structure of a SQL performance analysis system based on tracing technology according to Embodiment 4 of the present invention.
[0046] Figure 7 This is a schematic diagram of the structure of an electronic device of an SQL performance analysis method based on tracing technology according to Embodiment 5 of the present invention. DETAILED DESCRIPTION
[0047] The present invention is further described below by way of examples, but the present invention is not limited to the scope of the examples.
[0048] Example 1
[0049] The SQL performance analysis method based on tracing technology in this embodiment is as follows: Figure 1 As shown, the following steps are included:
[0050] S101, obtaining the original SQL statement and corresponding parameters run by the application program through the tracking plug-in;
[0051] S102, matching and assembling the original SQL statement with the parameters to obtain the SQL statement to be analyzed;
[0052] S103: Perform performance analysis on the SQL statement to be analyzed according to the execution plan to obtain SQL performance analysis results.
[0053] The SQL performance analysis method based on tracing technology in this embodiment uses a tracing plug-in to obtain the original SQL statements and parameters of the application program, and performs performance analysis on the SQL statements through an execution plan after parameter matching and assembly. This method can detect SQL performance risks before going online, resolve risks in a timely manner, and prevent major online incidents. It has high efficiency, low cost, and an easier and more comprehensive detection process.
[0054] Example 2
[0055] Based on Example 1, this example provides a SQL performance analysis method based on tracking technology, such as Figure 2 As shown,
[0056] Specifically, step S101 includes:
[0057] S1011. Use the dynamic tracing tool Btrace (a tracing tool) to set tracking points in the application to obtain the original SQL statements and corresponding parameters run by the application.
[0058] The SQL performance analysis method based on tracing technology in this embodiment uses the secure JVM (Java virtual machine) dynamic tracing tool Btrace to obtain the original SQL statements and corresponding parameters of the application program. By executing the written SQLJava (an object-oriented programming language) script and compiling it into a bytecode file, the agent package is dynamically attached to the JVM to be monitored using the Attach API (a Java application program interface), the SQL Java script is parsed by the agent package, and the class java.sql.Connection (a Java class) and java.sql.PreparedStatement (a Java class) are modified using ASM (Java bytecode manipulation and analysis framework), and the class is dynamically replaced using JDK Instrumentation (an instruction), so as to set a tracking point in the application program to obtain the original SQL statements and corresponding parameters of the application program and record them in a file.
[0059] Step S102 specifically includes:
[0060] S1021, matching the original SQL statement with the parameters and sending them to the message queue;
[0061] S1022: Receive matched SQL statements in the message queue and assemble them to form a SQL statement to be analyzed.
[0062] Step S103 specifically includes:
[0063] S1031, performing performance analysis on the SQL statement to be analyzed according to whether the number of scanned rows in the execution plan exceeds a threshold to obtain a SQL performance analysis result;
[0064] In the SQL performance analysis method based on tracking technology of this embodiment, the execution plan of SQL executed in the production environment is used to perform performance analysis on the SQL statement to be analyzed through the explain interface provided by the DBA (database administrator). The threshold of this embodiment is set to 5000, and the threshold can be flexibly modified according to the actual situation and is not limited to the set value of this embodiment. The SQL performance analysis method based on tracking technology of this embodiment can also perform SQL performance analysis according to whether the type in the execution plan meets the preset conditions, and the specific SQL performance analysis method is not limited to the above content.
[0065] The SQL performance analysis method based on tracing technology in this embodiment further includes the following steps after step S1031:
[0066] S104, sending a risk warning email when the number of scanned rows exceeds a threshold;
[0067] In the SQL performance analysis method based on tracing technology of this embodiment, when it is determined that the number of scanned rows in the execution plan exceeds the threshold, it is determined that there is a performance risk in SQL, and a risk warning email is sent to relevant testers and developers using a soa (service-oriented architecture) email. The SQL performance analysis method based on tracing technology of this embodiment can also issue an early warning by outputting risk reminder information or risk reminder warning lights.
[0068] Before step S101, the SQL performance analysis method based on tracing technology in this embodiment further includes the following steps:
[0069] S105, obtaining the running status of the application program through the tracking plug-in;
[0070] S106, determining whether the operating state is abnormal;
[0071] If yes, go to step S107; if no, go to step S101;
[0072] S107: Stop acquiring the original SQL statement and the corresponding parameters and send operation exception information.
[0073] In the SQL performance analysis method based on tracing technology in this embodiment, the running status of the application refers to whether the business line application where the application is located is running normally. If the business line application is stopped, it means that the running status is abnormal. The tracing plug-in stops obtaining the original SQL statements and corresponding parameters in the application, and sends running exception information to realize the running status monitoring of the business line application, avoid obtaining invalid SQL statements and parameters, and improve the accuracy of SQL performance analysis.
[0074] Before step S101, the SQL performance analysis method based on tracing technology in this embodiment further includes:
[0075] The tracing plug-in receives the filtering information and stops obtaining the corresponding original SQL statements and parameters based on the filtering information.
[0076] The SQL performance analysis method based on tracing technology in this embodiment can filter out preset SQL statements and parameters by setting filtering information, thereby improving the flexibility of configuration.
[0077] like Figure 3 and Figure 4 As shown, the specific application scenarios of the SQL performance analysis method based on tracing technology in this embodiment are as follows: the tracing area uses multiple tracing plug-ins apptrace-agent, each tracing plug-in apptrace-agent is connected to a business line application, and is used to obtain the original SQL statements and corresponding parameters running in the business line application, and match the original SQL statements with the parameters to determine the index value of the parameters and send them to the message queue for data storage. The SQL analysis area uses the SQL risk analysis system (apptrace-server) to perform SQL performance analysis. Apptrace-server obtains SQL statements from the data storage, assembles SQL statements, and then executes the explain (execution plan) of the SQL in the production standby database through the explain interface provided by the DBA. The SQL performance risk is determined based on the threshold analysis of the execution plan results. For risky SQL, the soa email method is used to push alarms to testers and development managers until the SQL optimization is risk-free and the alarm is terminated.
[0078] Specifically, the tracing plug-in apptrace-agent is internally provided with the Btrace client btrace-client and the Btrace server btrace-agent. After the application is deployed successfully, the SQL tracing plug-in apptrace-agent runs automatically. The Btrace client btrace-client compiles the Btrace monitoring script, which is a SQLJava script written by the tester. The Btrace server btrace-agent is started through JVM attach (a Java instruction) and the iar (a Java instruction) package of the Btrace server btrace-agent is loaded, and the code is submitted to the Btrace server btrace-agent. The Btrace server btrace-agent listens to the specified pid (process) and performs bytecode operations to set tracking points in the application. After the bytecode operation, update is performed to reload the corresponding class to obtain the original SQL statement and corresponding parameters in the application. The tracing plug-in apptrace-agent generates the traced SQL file and parses it to match the original SQL statement with the parameters, determines the index value of the parameter and sends it to the message queue, such as rabbitmq (Advanced Message Queuing Protocol) for data storage. The SQL risk analysis system (apptrace-server) receives the SQL statements in the message queue, assembles the SQL, analyzes the SQL performance according to the execution plan, and generates a report. When there is a risk in the SQL, an alarm is issued by email.
[0079] The tracing plug-in apptrace-agent is placed on the same server as the business line application, and is placed on a different server from the SQL risk analysis system (apptrace-server) to avoid mutual impact.
[0080] The tracing plug-in apptrace-agent is also used to obtain the running status signal of the business line application and send it to apptrace-server to monitor whether the business line application is running normally and stop obtaining SQL statements when an exception occurs.
[0081] apptrace-server is also used to set filtering information, such as whitelist information. If you do not want to continue tracing a certain SQL statement, you can send filtering information to the tracing plug-in apptrace-agent to stop tracing the corresponding SQL statement and parameters to achieve automatic configuration.
[0082] The SQL performance analysis method based on tracking technology in this embodiment can send email alarms when SQL has performance risks, and can monitor the running status information of business line applications. At the same time, it can set filtering information and stop tracking specific SQL statements to achieve the purpose of early warning of performance risks after SQL goes online, and realize flexible configuration.
[0083] Example 3
[0084] The SQL performance analysis system based on tracking technology in this embodiment is as follows: Figure 5 As shown, it includes: a data tracking module 1 and a data analysis module 2;
[0085] The data tracking module 1 is in communication connection with the data analysis module 2;
[0086] The data tracking module 1 is used to obtain the original SQL statement and corresponding parameters of the application program through the tracking plug-in, match the original SQL statement with the parameters to obtain the matching SQL statement, and send it to the data analysis module 2;
[0087] The data analysis module 2 is used to obtain matching SQL statements and assemble them to obtain SQL statements to be analyzed, and to perform performance analysis on the SQL statements to be analyzed according to the execution plan to obtain SQL performance analysis results.
[0088] The SQL performance analysis system based on tracing technology in this embodiment uses a tracing plug-in to obtain the original SQL statements and parameters of the application program, and performs performance analysis on the SQL statements through an execution plan after parameter matching and assembly. It can discover SQL performance risks before going online, resolve risks in a timely manner, and prevent major online incidents. It has high efficiency, low cost, and an easier and more comprehensive detection process.
[0089] Example 4
[0090] Based on Example 3, this example provides a SQL performance analysis system based on tracking technology, such as Figure 6 shown.
[0091] The data tracking module 1 includes a plurality of tracking plug-ins 11, and the tracking plug-ins 11 are connected to the external business line application for communication and are used to obtain the original SQL statements and corresponding parameters running in the business line application.
[0092] The data tracking module 1 also includes a data storage module 12;
[0093] The tracking plug-in 11 is also used to match the original SQL statement with the parameter to determine the index value of the parameter and send it to the message queue, which is set in the data storage module 12;
[0094] The data analysis module 2 is also used to receive original SQL statements in the message queue in the data storage module 12 and assemble them to form SQL statements to be analyzed.
[0095] The SQL performance analysis system based on tracing technology in this embodiment uses the secure JVM (Java virtual machine) dynamic tracing tool Btrace to obtain the original SQL statements and corresponding parameters of the application program. By executing the written SQL Java script and compiling it into a bytecode file, the agent package is dynamically attached to the JVM to be monitored using the Attach API, the SQL Java script is parsed by the agent package, and the classes java.sql.Connection and java.sql.PreparedStatement are modified using ASM (Java bytecode manipulation and analysis framework), and the classes are dynamically replaced using JDK Instrumentation to set tracking points in the application program to obtain the original SQL statements and corresponding parameters of the application program and record them in a file.
[0096] The SQL performance analysis system based on the tracking technology of this embodiment performs performance analysis on the SQL statement to be analyzed by executing the SQL execution plan in the production environment through the explain interface provided by the DBA (database administrator).
[0097] The data analysis module 2 is also used to determine whether the number of scan lines in the execution plan exceeds a threshold, and send a risk warning email when the number of scan lines exceeds the threshold.
[0098] In the SQL performance analysis system based on tracing technology of this embodiment, the threshold is set to 5000, and the threshold can be flexibly modified according to the actual situation and is not limited to the set value of this embodiment. The SQL performance analysis system based on tracing technology of this embodiment can also make risk judgments based on whether the types in the execution plan meet the preset conditions, and the specific risk judgment settings are not limited to the settings of this embodiment. When it is judged that the number of scanned rows in the execution plan exceeds the threshold, it is determined that there is a performance risk in SQL, and a risk warning email is sent to relevant testers and developers using the soa email method. The SQL performance analysis system based on tracing technology of this embodiment can also issue warnings by outputting risk reminder information or risk reminder warning lights.
[0099] The tracking plug-in 11 is also used to obtain the running status of the application and send it to the data analysis module 2;
[0100] In the SQL performance analysis system based on tracing technology of this embodiment, the data analysis module 2 is also used to determine whether the running state is abnormal, and if so, send the running abnormality information to the tracing plug-in 11;
[0101] The tracking plug-in 11 is also used to stop obtaining the original SQL statement and the corresponding parameters based on the abnormal operation information.
[0102] The running status of the application in the SQL performance analysis system based on tracking technology in this embodiment refers to whether the business line application where the application is located is running normally. If the business line application is stopped, it means that the running status is abnormal. The tracking plug-in stops obtaining the original SQL statements and corresponding parameters in the application, and sends running exception information to realize the running status monitoring of the business line application, avoid obtaining invalid SQL statements and parameters, and improve the accuracy of SQL performance analysis.
[0103] In the SQL performance analysis system based on tracking technology of this embodiment, the data analysis module 2 is also used to set filtering information and send the filtering information to the data tracking module 1, and the filtering information is used to filter the preset SQL statements and corresponding parameter information;
[0104] The data tracking module 1 is also used to receive the filtering signal through the tracking plug-in 11 and stop obtaining the corresponding original SQL statement and parameters based on the filtering information.
[0105] The SQL performance analysis system based on the tracking technology in this embodiment can filter out preset SQL statements and parameters by setting filtering information, thereby improving the flexibility of configuration.
[0106] The specific application scenario of the SQL performance analysis system based on tracing technology in this embodiment is similar to the application scenario in Example 2, and will not be repeated here.
[0107] The SQL performance analysis system based on tracking technology in this embodiment can send email alarms when SQL has performance risks, and can monitor the running status information of business line applications. At the same time, it can set filtering information and stop tracking specific SQL statements to achieve the purpose of early warning of performance risks after SQL goes online, and realize flexible configuration.
[0108] Example 5
[0109] Figure 7 A schematic diagram of the structure of an electronic device for implementing a SQL performance analysis method based on tracing technology provided in Example 5 of the present invention. The electronic device includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, the SQL performance analysis method based on tracing technology of Example 1 or 2 is implemented. Figure 7 The electronic device 30 shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.
[0110] like Figure 7As shown, the electronic device 30 may be in the form of a general-purpose computing device, for example, it may be a server device. The components of the electronic device 30 may include, but are not limited to: at least one processor 31, at least one memory 32, and a bus 33 connecting different system components (including the memory 32 and the processor 31).
[0111] The bus 33 includes a data bus, an address bus, and a control bus.
[0112] The memory 32 may include a volatile memory, such as a random access memory (RAM) 321 and / or a cache memory 322 , and may further include a read-only memory (ROM) 323 .
[0113] The memory 32 may also include a program / utility 325 having a set (at least one) of program modules 324, such program modules 324 including but not limited to: an operating system, one or more application programs, other program modules, and program data, each of which or some combination may include an implementation of a network environment.
[0114] The processor 31 executes various functional applications and data processing by running the computer program stored in the memory 32, such as the SQL performance analysis method based on tracing technology provided in Embodiment 1 or 2 of the present invention.
[0115] The electronic device 30 may also communicate with one or more external devices 34 (e.g., keyboards, pointing devices, etc.). Such communication may be performed via an input / output (I / O) interface 35. Furthermore, the model generating device 30 may also communicate with one or more networks (e.g., a local area network (LAN), a wide area network (WAN), and / or a public network, such as the Internet) via a network adapter 36. Figure 7 As shown, the network adapter 36 communicates with other modules of the model-generated device 30 via the bus 33. It should be understood that, although not shown in the figure, other hardware and / or software modules can be used in conjunction with the model-generated device 30, including but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID (disk array) systems, tape drives, and data backup storage systems, etc.
[0116] It should be noted that although several units / modules or sub-units / modules of the electronic device are mentioned in the above detailed description, this division is merely exemplary and not mandatory. In fact, according to an embodiment of the present invention, the features and functions of two or more units / modules described above can be embodied in one unit / module. Conversely, the features and functions of one unit / module described above can be further divided into multiple units / modules to be embodied.
[0117] Example 6
[0118] This embodiment provides a computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, the steps of the SQL performance analysis method based on tracing technology provided in Embodiment 1 or 2 are implemented.
[0119] The readable storage medium may include but is not limited to: a portable disk, a hard disk, a random access memory, a read-only memory, an erasable programmable read-only memory, an optical storage device, a magnetic storage device or any suitable combination of the above.
[0120] In a possible implementation manner, the present invention may also be implemented in the form of a program product, which includes program code. When the program product runs on a terminal device, the program code is used to enable the terminal device to execute the steps in the SQL performance analysis method based on tracing technology in embodiment 1 or 2.
[0121] Among them, the program code for executing the present invention can be written in any combination of one or more programming languages, and the program code can be executed completely on the user device, partially on the user device, as an independent software package, partially on the user device and partially on a remote device, or completely on the remote device.
[0122] Although the specific embodiments of the present invention are described above, it should be understood by those skilled in the art that this is only for illustration and the protection scope of the present invention is defined by the appended claims. Those skilled in the art may make various changes or modifications to these embodiments without departing from the principles and essence of the present invention, but these changes and modifications all fall within the protection scope of the present invention.
Claims
1. A SQL performance analysis method based on tracing technology, characterized in that: include: Obtain the original SQL statements and corresponding parameters run by the application through the tracing plug-in; Matching and assembling the original SQL statement with the parameters to obtain the SQL statement to be analyzed; The step of obtaining the original SQL statement and corresponding parameters of the application program through the tracking plug-in specifically includes: Using the dynamic tracing tool Btrace, a tracking point is set in the application to obtain the original SQL statement and corresponding parameters run by the application, match the original SQL statement with the parameters and send them to the message queue; The step of matching and assembling the original SQL statement with the parameters to obtain the SQL statement to be analyzed specifically includes: Match the original SQL statement with the parameters and send them to the message queue; Receiving the matched SQL statements in the message queue and assembling them to form the SQL statement to be analyzed; Performing performance analysis on the SQL statement to be analyzed according to the execution plan to obtain a SQL performance analysis result; Before the step of obtaining the original SQL statement and corresponding parameters of the application program through the tracking plug-in, the SQL performance analysis method further includes: Obtaining the running status of the application through the tracking plug-in; Determining whether the operating state is abnormal; If yes, stop acquiring the original SQL statement and the corresponding parameters and send a running abnormality message; Before the step of obtaining the original SQL statement and corresponding parameters of the application program through the tracking plug-in, the SQL performance analysis method further includes: The tracing plug-in receives the filtering information and stops acquiring the corresponding original SQL statement and parameters based on the filtering information.
2. The SQL performance analysis method based on tracing technology according to claim 1, characterized in that: The step of performing performance analysis on the SQL statement to be analyzed according to the execution plan to obtain the SQL performance analysis result specifically includes: A performance analysis is performed on the SQL statement to be analyzed according to whether the number of scanned rows in the execution plan exceeds a threshold to obtain a SQL performance analysis result.
3. The SQL performance analysis method based on tracing technology according to claim 2, characterized in that: After the step of performing performance analysis on the SQL statement to be analyzed according to whether the number of scanned rows in the execution plan exceeds a threshold to obtain a SQL performance analysis result, the SQL performance analysis method further comprises the following steps: When the number of the scanned rows exceeds the threshold, a risk warning email is sent.
4. A SQL performance analysis system based on tracing technology, characterized in that: include: Data tracking module and data analysis module; The data tracking module is used to obtain the original SQL statement and corresponding parameters of the application program through the tracking plug-in, and match the original SQL statement with the parameters to obtain a matching SQL statement, and send it to the data analysis module; The data analysis module is used to obtain the matching SQL statements and assemble them to obtain the SQL statements to be analyzed, and perform performance analysis on the SQL statements to be analyzed according to the execution plan to obtain SQL performance analysis results; The data tracking module also includes a data storage module; The tracking plug-in is also used to match the original SQL statement with the parameters and send them to a message queue, and the message queue is set in the data storage module; The data analysis module is also used to receive the matched SQL statements in the message queue in the data storage module and assemble them to form the SQL statement to be analyzed; The tracking plug-in is also used to obtain the running status of the application and send it to the data analysis module; The data analysis module is also used to determine whether the running state is abnormal, and if so, to send a running abnormality message to the tracking plug-in; The tracking plug-in is also used to stop obtaining the original SQL statement and corresponding parameters based on the operation exception information; The data analysis module is also used to set filtering information and send the filtering information to the data tracking module, wherein the filtering information is used to filter preset SQL statements and corresponding parameter information; The data tracking module is also used to receive the filtering information through the tracking plug-in and stop obtaining the corresponding original SQL statement and parameters based on the filtering information.
5. The SQL performance analysis system based on tracing technology as claimed in claim 4, characterized in that: The data analysis module is also used to determine whether the number of scan lines in the execution plan exceeds a threshold, and send a risk warning email when the number of scan lines exceeds the threshold.
6. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the SQL performance analysis method based on tracing technology described in any one of claims 1 to 3 is implemented.
7. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the steps of any one of the SQL performance analysis methods based on tracing technology in claims 1 to 3 are implemented.
Citation Information
Patent Citations
SQL (structured query language) information acquiring and auditing system based on MSSQL (Microsoft SQL server) database
CN102609541A
Structured query language SQL execution efficiency tracking method and device
CN106372135A
Method and device for detecting efficiency of database SQL
CN106445795A
Performance analysis method, performance analysis apparatus, storage medium and electronic apparatus
CN109240901A
Tracking method and device based on dynamic buried points, compiling method and device based on dynamic buried points and electronic equipment
CN111124906A
