Dynamic SQL (Structured Query Language) detection method and system, program product and storage medium and device thereof

Abstract

The invention relates to a dynamic SQL (Structured Query Language) detection method and system, a program product and a storage medium and device thereof. The method comprises the following steps: deploying a Jar packet for SQL detection to a target server; configuring a javaagent parameter in a starting parameter of a to-be-detected application service of the target server, wherein the javaagent parameter specifies a path of the Jar packet on the target server; when the to-be-detected application service is started, the Java virtual machine loads the Jar packet according to the Java proxy parameters, and byte code enhancement is carried out on an SQL executor so as to insert detection logic in the SQL execution stage; and when the to-be-detected application service processes a request, executing SQL detection through the detection logic inserted by the byte code enhanced code.

Description

Technical Field

[0001] This invention relates to the field of computer application technology, and more specifically, to a dynamic SQL detection method, system, program product, and its storage medium and device. Background Technology

[0002] With the continuous expansion of internet businesses and the rapid iteration of software systems, the complexity and data volume of systems have increased dramatically under the "code-first" development model. Structured Query Language (SQL), as the core means of interaction between applications and databases, directly determines the performance, stability, and user experience of the system through its execution efficiency.

[0003] Currently, there are significant deficiencies in the detection and analysis of SQL performance during the system testing phase. It is difficult to detect potential performance issues in a timely manner, such as missing indexes and full table scans. Summary of the Invention

[0004] In view of the deficiencies of the prior art, the present invention provides a dynamic SQL detection method, system, program product, and its storage medium and device.

[0005] One embodiment of the present invention relates to a dynamic SQL detection method, which includes:

[0006] Deploy the JAR file used for SQL detection to the target server;

[0007] Configure the javaagent parameter in the startup parameters of the application service to be detected on the target server. The javaagent parameter specifies the path of the Jar package on the target server.

[0008] When the application service to be detected starts, the Java Virtual Machine loads the Jar package according to the Java agent parameters and performs bytecode enhancement on the SQL executor to insert detection logic during the SQL execution phase.

[0009] When the application service to be detected processes a request, the detection logic, which is inserted through the bytecode enhancement, performs SQL detection.

[0010] In some implementations, the SQL detection performed by the bytecode-enhanced code insertion detection logic includes:

[0011] Check if any SQL is being executed in the request;

[0012] If SQL is detected being executed, the executing SQL is obtained through reflection, and the type of the obtained SQL is determined.

[0013] If the obtained SQL is of the type of query or update, then execute the execution plan of the SQL and obtain the result set of the execution plan;

[0014] The result set is parsed and the performance of the obtained SQL is judged according to preset rules.

[0015] In some implementations, the SQL detection performed by the bytecode-enhanced code insertion detection logic further includes:

[0016] If no SQL is being executed in the request or the obtained SQL is of the insert or delete type, it will be allowed directly without detection.

[0017] In some implementations, the judgments performed according to preset rules include:

[0018] If the retrieved SQL uses an index, scans fewer rows than a preset threshold, and does not use a temporary table for querying, the SQL is considered to have executed normally.

[0019] When the obtained SQL performs a full table scan or the number of rows scanned exceeds the preset threshold, it is determined that there is a performance risk and an early warning is triggered.

[0020] In some implementations, the warning includes:

[0021] If an alarm receiver is configured, alarm information will be sent through a third-party communication application.

[0022] If no alarm receiver is configured, print the alarm log.

[0023] In some implementations, the method further includes: specifying the alarm recipient via the bytewatcher.warn.user parameter when configuring the javaagent parameter; and

[0024] When performing bytecode enhancement on the SQL executor, the information of the alarm recipient is loaded into local memory for subsequent use.

[0025] In some implementations, the third-party communication application includes WeChat for Business or email.

[0026] Another embodiment of the present invention relates to a dynamic SQL detection system that performs the detection method described in any embodiment of the present invention.

[0027] Furthermore, embodiments of the present invention also relate to a computer program product comprising a computer program that, when executed by a processor, performs any one of the operations described in any one embodiment of the present invention. Accordingly, embodiments of the present invention provide a computer-readable storage medium storing a computer program, characterized in that, when executed by a processor, the computer program performs any one of the operations described in any one embodiment of the present invention.

[0028] In addition, embodiments of the present invention also provide a computer device comprising:

[0029] A memory on which computer programs are stored;

[0030] A processor, when executing the computer program, performs any one of the operations described in any embodiment of the present invention.

[0031] Implementing this invention can achieve at least the following beneficial effects.

[0032] This invention deploys the detection logic via a Jar package proxy and specifies the Jar package path through Java Virtual Machine (JVM) parameter configuration, thereby enabling rapid integration and flexible deployment of the detection system. The detection function can be enabled without modifying existing business systems, significantly reducing the complexity of system integration.

[0033] This invention encapsulates SQL detection functionality into a single, independent JAR file module and utilizes bytecode enhancement technology to dynamically embed detection logic during SQL execution, thereby achieving zero-intrusion integration with business code. This approach effectively avoids the risks and maintenance costs associated with manual instrumentation or modification of business code. Furthermore, encapsulating SQL detection functionality into a single, independent JAR file module allows for the accumulation of technical expertise for future development.

[0034] Furthermore, this invention provides diverse early warning mechanisms. When performance risks such as SQL not using indexes, improper index usage, or full table scans are detected, the system can send early warning notifications to designated users via WeChat push notifications or emails; if no message recipients are configured, early warning logs can be automatically printed for subsequent investigation. Through the above design, this invention not only achieves real-time monitoring of SQL performance issues but also improves the maintainability and operational reliability of the system.

[0035] Various aspects, features, advantages, etc., of the embodiments of the present invention will be described in detail below with reference to the accompanying drawings, and will become clearer thereon. Attached Figure Description

[0036] Figure 1 This is a schematic diagram illustrating the main flow of the dynamic SQL detection method according to an embodiment of the present invention.

[0037] Figure 2 This is a schematic diagram illustrating the detection logic of the dynamic SQL detection method according to an embodiment of the present invention.

[0038] Figure 3 This is a schematic diagram illustrating a computing device that can be used to execute the dynamic SQL detection method according to embodiments of the present invention. Detailed Implementation

[0039] The exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. It should be noted that the present invention can be implemented in various forms and is not limited to the specific embodiments described herein or shown in the accompanying drawings.

[0040] The terms “comprising,” “including,” and “having,” etc., are used herein to indicate the presence of certain features, steps, operations, elements, and / or components, but do not exclude the presence or addition of other features, steps, operations, elements, components, or combinations thereof. Unless explicitly defined by context, the terms “first,” “second,” and similar terms do not indicate any priority or order, but are used only to distinguish different elements in the description.

[0041] Although the methods of the embodiments of the present invention have been described or illustrated in a certain order herein and in the accompanying drawings, it should be understood that the methods of the present invention are not necessarily performed in the order described or illustrated. The reference numerals in the accompanying drawings for the method processing, such as S100, S401, etc., do not limit the order of the methods.

[0042] This invention provides a dynamic SQL detection system that can be used to execute the methods described in any of the embodiments herein. Generally, the system provides a Jar file encapsulating the detection logic, which is placed on a specific path on a server. When the system to be detected starts, the Java Virtual Machine loads the Jar file, and when the system receives a business request, it executes the SQL detection logic by running the Jar file.

[0043] Figure 1 This illustrates an exemplary main flow of the dynamic SQL detection method according to an embodiment of the present invention. In one exemplary embodiment, such as Figure 1 As shown, the dynamic SQL detection method includes the following processing.

[0044] In process S100, the Jar package used for SQL detection is deployed to the target server. For example, if the system is running locally, the Jar package is placed in a local disk path. Alternatively, the Jar package can be deployed to another path that has a communication connection with the target server.

[0045] In process S200, the javaagent parameter is configured in the startup parameters of the application service to be detected on the target server. This javaagent parameter specifies the path of the Jar package on the target server. Optionally, the alarm recipient of the detection system is specified through the bytewatcher.warn.user parameter.

[0046] In an optional implementation, process S200 can be executed first to specify the path of the Jar package, and then process S100 can be executed to deploy the Jar package to that path.

[0047] Then, the dynamic SQL detection method proceeds to process S300. When the application service to be detected starts, the Java Virtual Machine loads the Jar package according to the Java agent parameters to perform bytecode enhancement on the SQL executor to insert detection logic during the SQL execution phase. For example, the Jar package will perform bytecode enhancement on the SQL executor (such as com.mysql.cj.jdbc.ClientPreparedStatement) and store the alarm recipient information in memory.

[0048] In processing S400, when the application service to be detected processes a request, SQL detection is performed through the detection logic of the bytecode-enhanced code insertion.

[0049] In some implementations, such as Figure 2 As shown, the SQL detection performed by the bytecode-enhanced code insertion detection logic includes: S401, receiving the application service processing request to be detected; S402, detecting whether the request contains SQL being executed; S403, if SQL is detected being executed, obtaining the executing SQL through reflection; S404, determining the type of the obtained SQL (including query, update, insert, delete, etc.); S405, if the obtained SQL is of type query or update, obtaining the data source connection of the current SQL, executing the execution plan of the SQL, and obtaining the result set of the execution plan; S406, parsing the result set and performing performance judgment on the obtained SQL according to preset rules. In some embodiments, the SQL detection further includes: S408, if the request does not contain SQL being executed or the obtained SQL is of type insert or delete, then it is directly allowed and not detected.

[0050] In some implementations, the judgments based on preset rules include: if the obtained SQL uses an index, scans fewer rows than a preset threshold (e.g., 1000), and does not use a temporary table for querying, then the SQL is judged to be executing normally and no warning is issued; if the obtained SQL performs a full table scan or scans more rows than the preset threshold, then it is judged to have performance risks and a warning is triggered.

[0051] In some implementations, the alert includes: if an alert receiver is configured, sending the alert information via a third-party communication application; if no alert receiver is configured, printing the alert log.

[0052] In some implementations, the method further includes: specifying the alarm recipient through the bytewatcher.warn.user parameter when configuring the javaagent parameter; and loading the information of the alarm recipient into local memory for subsequent calls when performing bytecode enhancement on the SQL executor.

[0053] In some implementations, the third-party communication application includes WeChat for Business or email, etc.

[0054] It should be understood that the steps, processes, and operations described in the above embodiments can be implemented by computer software programs. Therefore, the embodiments of the present invention also relate to a computer program product, which includes a computer program that, when executed by a processor, implements the steps, processes, or operations of the dynamic SQL detection method described in any embodiment of the present invention. In some embodiments, program code for executing the computer program of the embodiments of the present invention can be written in any combination of one or more programming languages. For example, the computer program can be written using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, languages ​​such as Java, C++, Python, "C", or similar programming languages. The program code can be executed entirely on a personal computing device, partially on a personal computing device, partially on a remote computing device, or entirely on a remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the personal computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).

[0055] It should also be understood that the computer program product may be stored on a computer-readable storage medium, such as hard disks, floppy disks, magnetic tapes, optical disks, solid-state drives, flash memory, etc.

[0056] Furthermore, it can be understood that the dynamic SQL detection method of any embodiment of the present invention can be executed by a computer device. Accordingly, embodiments of the present invention also relate to a computer device comprising: a memory storing a computer program thereon; and a processor that, when executing the computer program, implements the steps (processing, operations, etc.) of the dynamic SQL detection method described in any embodiment of the present disclosure.

[0057] Figure 3 An exemplary structure of a computer device is shown. Figure 3 As shown, the computer device 100 may include at least a processor 101 and a memory 102, wherein the memory 102 may be or include a storage medium storing a computer program (or computer-readable instructions). The processor 101 executes the computer program to perform some or all of the operations in the dynamic SQL detection method described in any embodiment of this disclosure, including but not limited to: deploying a Jar package for SQL detection to a target server; configuring a javaagent parameter in the startup parameters of the application service to be detected on the target server, the javaagent parameter specifying the path of the Jar package on the target server; when the application service to be detected starts, the Java Virtual Machine loads the Jar package according to the Java agent parameter, performs bytecode enhancement on the SQL executor to insert detection logic during the SQL execution phase; when the application service to be detected processes a request, the SQL detection is performed through the detection logic inserted by the bytecode enhancement code.

[0058] In some implementations, the processor 101 executes the computer program to perform: detecting whether the request has SQL being executed; if SQL is detected being executed, obtaining the executing SQL through a reflection mechanism and determining the type of the obtained SQL; if the type of the obtained SQL is a query or update, executing the execution plan of the SQL and obtaining the result set of the execution plan; parsing the result set and performing performance judgment on the obtained SQL according to preset rules.

[0059] In some implementations, the processor 101 executes the computer program to perform the following: if no SQL is being executed in the request or the type of the obtained SQL is insert or delete, then the request is directly allowed without detection.

[0060] In some implementations, the processor 101 executes the computer program to perform the following: when the acquired SQL uses an index, scans fewer rows than a preset threshold, and does not use a temporary table for querying, the SQL is determined to be executing normally; when the acquired SQL performs a full table scan or scans more rows than the preset threshold, a performance risk is determined to exist, and a warning is triggered.

[0061] In some embodiments, the computer device 100 may further include a data storage device 103, a display 104, a speaker 105, and a communication module 106. The processor 101, memory 102, and data storage device 103 communicate with each other via a bus and interact with peripheral devices, such as the display 104 and communication module 106, via the bus and I / O module 107.

[0062] Specifically, data storage device 103 can store application programs and their various configuration files and data. Computer programs are stored on memory 102. Processor 101 executes the computer programs to perform various processes, operations, or steps in the dynamic SQL detection method. Display 104 can be used to display various data, images, etc.

[0063] In optional embodiments, the communication module 106 may be omitted where communication with the other systems, devices, or equipment is not required. In some embodiments, the computer device 100 may further include a speaker 105 for voice output.

[0064] In some implementations, processor 101 may include any suitable semiconductor-based electronic processing unit, chip, microchip, or integrated circuit (IC). Memory 102 is a programmable memory, which may include any suitable electronic storage device configured to store instructions and be reprogrammable. For example, programmable memory may include an erasable programmable read-only memory (EPROM) device. Data storage device 103 may be a persistent storage device, which may include any suitable electronic storage configured to retain stored information as power cycles. For example, data storage device 103 may include a hard disk drive, solid-state drive (SSD), flash memory drive, hybrid drive, etc., or any combination thereof.

[0065] Therefore, processor 101 can control the output of information on I / O module 107 by storing information in memory and / or executing programs / instructions stored in memory. For example, several aspects of the methods described herein can be executed by processor 101 according to programs / instructions stored in memory (e.g., memory 102 and / or data storage device 103).

[0066] Furthermore, the processor 101 can electronically communicate with the I / O module 107 and / or the communication module 106 to receive or send relevant instructions and information. The I / O module 107 may include any suitable mechanical or virtual user interface configured to enable a user to interact with the computer device 100 or to allow the user to perform one or more functions of the computer device 100 itself, such as a graphical user interface (GUI) on a screen or other display. In some instances, the user interface may include a voice interface capable of speech recognition, through which an operator can provide voice commands to the processor.

[0067] The communication module 106 may include any suitable devices and / or structures configured to facilitate information exchange between the computer device 100 and external electronic devices (e.g., a printing device for printing alert logs). The communication module 106 may include devices configured to send and / or receive wireless or wired information with other devices. For example, the communication module 106 may include an antenna, a transceiver, a connector for wired reception and / or transmission of data, a data exchange device, etc., or any combination thereof. The communication module 106 may also include ancillary components, such as filtering circuits, encryption / decryption circuits, and / or integrated circuit (IC) chips for processing signals (e.g., (Chip). In some implementations, the communication module 106 may include a WiFi device configured to connect to a local wireless network.

[0068] In some embodiments, computer device 100 may include a smartphone, wearable computer, portable / mobile electronic device, tablet computer, smartwatch, personal digital assistant (PDA), personal computer (PC), desktop computer, laptop computer, server, etc. Computer device 100 may include or have installed one or more application programs (APPs), one of which is configured to perform the dynamic SQL detection method described herein.

[0069] Although not shown, it should be understood that the computer device 100 also includes a power supply component, which may include any suitable device and / or structure configured to provide an electrical interface between the computer device 100 and a power source. The power source may include any suitable source of electrical energy, such as a battery, socket, capacitor, fuel cell, etc., or any combination thereof. Additionally or optionally, the power source may be included within the power supply component. For example, a battery or battery pack may be included within the computer device 100. In some embodiments, the battery may be rechargeable, for example, charged via a cable or an interface provided by the power supply component. In some embodiments, the power supply component may share features with or repeat features of the communication module 106. For example, a USB or micro-USB cable connector may be included in the computer device 100, so that power supply or data communication is performed by the same component.

[0070] As described above, the embodiments of the present invention deploy the detection logic through a Jar package proxy and specify the Jar package path through Java Virtual Machine (JVM) parameter configuration, thereby achieving rapid access and flexible deployment of the detection system. The detection function can be enabled without modifying the existing business system code, greatly reducing the complexity of system integration.

[0071] This invention encapsulates SQL detection functionality into a single, independent JAR file module and utilizes bytecode enhancement technology to dynamically embed detection logic during SQL execution, thereby achieving zero-intrusion integration with business code. This approach effectively avoids the risks and maintenance costs associated with manual instrumentation or modification of business code. Furthermore, encapsulating SQL detection functionality into a single, independent JAR file module allows for the accumulation of technical expertise for future development.

[0072] Furthermore, this invention provides diverse early warning mechanisms. When performance risks such as SQL not using indexes, improper index usage, or full table scans are detected, the system can send early warning notifications to designated users via WeChat push notifications or emails. If no message recipient is configured, early warning logs can be automatically printed for subsequent investigation. Through the above design, this invention not only achieves real-time monitoring of SQL performance issues but also improves the maintainability and operational reliability of the system.

[0073] Those skilled in the art should understand that the above disclosure is merely illustrative of embodiments of the present invention, and the scope of patent protection claimed in this application is not limited thereto. Various modifications, alterations, substitutions, and other changes can be made to the embodiments disclosed herein without departing from the spirit of the invention, and these changes are also within the scope covered by the claims of this application.

Claims

1. A dynamic SQL detection method, characterized in that, include: Deploy the JAR file used for SQL detection to the target server; Configure the javaagent parameter in the startup parameters of the application service to be detected on the target server. The javaagent parameter specifies the path of the Jar package on the target server. When the application service to be detected starts, the Java Virtual Machine loads the Jar package according to the Java agent parameters and performs bytecode enhancement on the SQL executor to insert detection logic during the SQL execution phase. When the application service to be detected processes a request, the detection logic, which is inserted through the bytecode enhancement, performs SQL detection.

2. The detection method according to claim 1, characterized in that, SQL detection performed through the bytecode-enhanced code insertion detection logic includes: Check if any SQL is being executed in the request; If SQL is detected being executed, the executing SQL is obtained through reflection, and the type of the obtained SQL is determined. If the obtained SQL is of the type of query or update, then execute the execution plan of the SQL and obtain the result set of the execution plan; The result set is parsed and the performance of the obtained SQL is judged according to preset rules.

3. The detection method according to claim 2, characterized in that, SQL detection performed through the bytecode-enhanced code insertion detection logic also includes: If no SQL is being executed in the request or the obtained SQL is of the insert or delete type, the request is allowed directly without detection.

4. The detection method according to claim 2, characterized in that, The judgments performed according to the preset rules include: If the retrieved SQL uses an index, scans fewer rows than a preset threshold, and does not use a temporary table for querying, the SQL is considered to have executed normally. When the obtained SQL performs a full table scan or the number of rows scanned exceeds the preset threshold, it is determined that there is a performance risk and an early warning is triggered.

5. The detection method according to claim 4, characterized in that, The warning includes: If an alarm receiver is configured, alarm information will be sent through a third-party communication application. If no alarm receiver is configured, print the alarm log.

6. The detection method according to claim 5, characterized in that, Also includes: When configuring the javaagent parameters, the alarm recipient is specified through the bytewatcher.warn.user parameter; as well as When performing bytecode enhancement on the SQL executor, the information of the alarm recipient is loaded into local memory for subsequent use.

7. The detection method according to claim 5, characterized in that, The third-party communication applications include WeChat for Business or email.

8. A dynamic SQL detection system, characterized in that, Perform the detection method according to any one of claims 1 to 7.

9. A computer program product comprising a computer program, characterized in that, When the computer program is executed by a processor, it performs any one of the operations described in 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, it performs any one of the operations described in any one of claims 1 to 7.

11. A computer device, characterized in that, include: A memory on which computer programs are stored; A processor that, when executing the computer program, performs any one of the operations described in any one of claims 1 to 7.

Images