CN116627786A - SQL monitoring method, device, equipment and storage medium - Google Patents

SQL monitoring method, device, equipment and storage medium Download PDF

Info

Publication number
CN116627786A
CN116627786A CN202310604327.9A CN202310604327A CN116627786A CN 116627786 A CN116627786 A CN 116627786A CN 202310604327 A CN202310604327 A CN 202310604327A CN 116627786 A CN116627786 A CN 116627786A
Authority
CN
China
Prior art keywords
sql
target
interceptor
log
global
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202310604327.9A
Other languages
Chinese (zh)
Inventor
崔家祥
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Bank of China Ltd
Original Assignee
Bank of China Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Bank of China Ltd filed Critical Bank of China Ltd
Priority to CN202310604327.9A priority Critical patent/CN116627786A/en
Publication of CN116627786A publication Critical patent/CN116627786A/en
Pending legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/34—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment
    • G06F11/3409—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment for performance assessment
    • G06F11/3419—Recording or statistical evaluation of computer activity, e.g. of down time, of input/output operation ; Recording or statistical evaluation of user activity, e.g. usability assessment for performance assessment by assessing time
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/3003—Monitoring arrangements specially adapted to the computing system or computing system component being monitored
    • G06F11/302—Monitoring arrangements specially adapted to the computing system or computing system component being monitored where the computing system component is a software system
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/3065—Monitoring arrangements determined by the means or processing involved in reporting the monitored data
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • General Engineering & Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Quality & Reliability (AREA)
  • General Physics & Mathematics (AREA)
  • Computing Systems (AREA)
  • Computer Hardware Design (AREA)
  • Mathematical Physics (AREA)
  • Debugging And Monitoring (AREA)

Abstract

The application provides an SQL monitoring method, device, equipment and storage medium, which can be used in the field of big data. The method comprises the following steps: the method comprises the steps that a server intercepts target SQL to be executed through a global SQL interceptor, the target SQL is executed through the global SQL interceptor, the starting time of the target SQL is recorded, and when the target SQL is executed, the ending time of the target SQL is recorded; and determining the execution time length corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time, and outputting a target log corresponding to the target SQL through the global SQL interceptor if the execution time length is greater than a time length threshold value, so as to perform early warning according to the target log, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time length threshold value is dynamically configurable. The method and the device can greatly improve the efficiency of monitoring the execution time of SQL, and have higher reusability and usability.

Description

SQL monitoring method, device, equipment and storage medium
Technical Field
The present application relates to the field of big data, and in particular, to a method, an apparatus, a device, and a storage medium for monitoring SQL.
Background
In an application system, the execution time of SQL is one of important indexes for measuring the validity and availability of the application system. The method can monitor the execution time of the SQL, and can provide an effective guarantee means for the analysis and optimization of the subsequent SQL and the standardized design of the SQL.
Currently, the execution duration of SQL is typically monitored by: and recording the start time and the end time of SQL execution by a developer at each position of the SQL in the code of the application system, so as to obtain the execution time of SQL, writing the essential items such as the SQL execution time into a log, outputting the log, and monitoring the SQL execution time according to the log. However, the efficiency is low by monitoring the execution time of SQL through the mode.
Disclosure of Invention
The application provides an SQL monitoring method, device, equipment and storage medium, which are used for solving the problem of low efficiency in monitoring the execution time of SQL in the current mode.
In a first aspect, the present application provides an SQL monitoring method, applied to a server, where the server is equipped with a global SQL interceptor, the SQL monitoring method includes:
intercepting target SQL to be executed by a global SQL interceptor;
executing the target SQL through the global SQL interceptor and recording the starting time of the target SQL;
when the target SQL is executed, recording the ending time of the execution of the target SQL through a global SQL interceptor;
determining the execution duration corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time;
if the execution time is longer than the time threshold, outputting a target log corresponding to the target SQL through the global SQL interceptor so as to perform early warning according to the target log, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time threshold is dynamically configurable.
In a second aspect, the present application provides an SQL monitoring device, applied to a server, where the server is equipped with a global SQL interceptor, the SQL monitoring device includes:
the interception module is used for intercepting target SQL to be executed through the global SQL interceptor;
the first processing module is used for executing the target SQL through the global SQL interceptor and recording the starting time of the target SQL execution; when the target SQL is executed, recording the ending time of the execution of the target SQL through a global SQL interceptor;
the determining module is used for determining the execution duration corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time;
the output module is used for outputting a target log corresponding to the target SQL through the global SQL interceptor if the execution time length is greater than a time length threshold value, so as to perform early warning according to the target log, wherein the target log comprises information of a target log element item corresponding to the target SQL, and the time length threshold value is dynamically configurable.
In a third aspect, the present application provides an electronic device comprising: a processor, a memory communicatively coupled to the processor;
the memory stores computer-executable instructions;
the processor executes the computer-executable instructions stored in the memory to implement the SQL monitoring method according to the first aspect of the application.
In a fourth aspect, the present application provides a computer readable storage medium having stored therein computer program instructions which, when executed, implement the SQL monitoring method according to the first aspect of the present application.
In a fifth aspect, the present application provides a computer program product comprising a computer program which when executed implements the SQL monitoring method according to the first aspect of the present application.
The SQL monitoring method, the SQL monitoring device, the SQL monitoring equipment and the SQL monitoring storage medium are applied to a server, and the server is provided with a global SQL interceptor; intercepting target SQL to be executed by a global SQL interceptor, executing the target SQL by the global SQL interceptor, recording the start time of the target SQL execution, and recording the end time of the target SQL execution when the target SQL is executed; and determining the execution time length corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time, and outputting a target log corresponding to the target SQL through the global SQL interceptor if the execution time length is greater than a time length threshold value, so as to perform early warning according to the target log, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time length threshold value is dynamically configurable. According to the method and the device, the target SQL to be executed is intercepted through the global SQL interceptor, the target SQL is executed, and the target log is output according to the obtained execution time length corresponding to the target SQL, so that the codes for obtaining the execution time length corresponding to the SQL and outputting the log do not need to be added at each position for calling the SQL in the codes of the application system, namely the logic of the original application system is not required to be modified, the efficiency of monitoring the execution time length of the SQL can be greatly improved, the reusability and the usability are high, and the maintenance of the later application system is facilitated. In addition, the duration threshold value is dynamically configurable, so that SQL monitoring is more flexible; and early warning is carried out according to the target log, so that relatively perfect SQL monitoring of slow query can be realized.
Drawings
In order to more clearly illustrate the embodiments of the present application or the technical solutions of the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, and it is obvious that the drawings in the following description are some embodiments of the present application, and other drawings may be obtained according to the drawings without inventive effort to a person skilled in the art.
Fig. 1 is a schematic view of an application scenario provided in an embodiment of the present application;
FIG. 2 is a flow chart of a SQL monitoring method according to an embodiment of the application;
FIG. 3 is a flow chart of a SQL monitoring method according to another embodiment of the application;
FIG. 4 is a schematic diagram of a SQL monitoring device according to an embodiment of the application;
FIG. 5 is a schematic diagram of a SQL monitoring device according to another embodiment of the present application;
fig. 6 is a schematic structural diagram of an electronic device according to an embodiment of the application.
Specific embodiments of the present application have been shown by way of the above drawings and will be described in more detail below. The drawings and the written description are not intended to limit the scope of the inventive concepts in any way, but rather to illustrate the inventive concepts to those skilled in the art by reference to the specific embodiments.
Detailed Description
For the purpose of making the objects, technical solutions and advantages of the embodiments of the present application more apparent, the technical solutions of the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present application, and it is apparent that the described embodiments are some embodiments of the present application, but not all embodiments of the present application. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.
It should be noted that, the user information (including but not limited to user equipment information, user personal information, etc.) and the data (including but not limited to data for analysis, stored data, presented data, etc.) related to the present application are information and data authorized by the user or fully authorized by each party, and the collection, use and processing of the related data need to comply with related laws and regulations and standards, and provide corresponding operation entries for the user to select authorization or rejection.
It should be noted that the method, the device, the equipment and the storage medium for monitoring SQL of the present application can be used in the big data field, and can also be used in any field except the big data field, and the application fields of the method, the device, the equipment and the storage medium for monitoring SQL of the present application are not limited.
First, some technical terms related to the present application will be explained:
the structured query language (Structured Query Language, SQL) is a database query and programming language for accessing data and querying, updating and managing relational database systems.
The execution time of SQL is one of important indexes for measuring the effectiveness and availability of the application system, and the SQL with the overlong execution time or the error in the execution process affects the normal operation of the application system and has potential safety hazards. The method monitors the execution time of the SQL, particularly takes the SQL with the execution time exceeding a certain time length (namely slow query SQL) as a key monitoring object, and can provide an effective guarantee means for analysis optimization of the subsequent SQL and SQL standardization design.
At present, the start time and the end time of SQL execution are recorded at each position of SQL calling in the code of an application system, so that the execution time of SQL is obtained, the essential items such as the SQL execution time are written into a log, the log is output, and the monitoring of the SQL execution time is realized according to the log. That is, a call for implementing the SQL monitoring method needs to be added at each location for calling SQL in the code of the application system, or a public method for SQL monitoring is written, and the public method is called at each location for executing SQL in the code of the application system. However, by monitoring the execution time of SQL in the mode, the code distribution is scattered, the redundancy is high, and the efficiency is low. If the application system is updated or modified, the codes of the positions calling SQL are required to be modified, the workload is high, and the later maintenance cost is high. In addition, a time threshold for determining whether the execution duration of the SQL is excessively long cannot be set, that is, the slow query cannot be monitored.
Based on the above problems, the application provides an SQL monitoring method, device, equipment and storage medium, which are used for intercepting target SQL to be executed through a global SQL interceptor, executing the target SQL and outputting a target log according to the execution time corresponding to the obtained target SQL, wherein the execution time is greater than a time threshold, so that the efficiency of monitoring the execution time of the SQL can be greatly improved, the method, the device and the storage medium have higher reusability and availability, the SQL can be monitored more efficiently and more robustly, and the maintenance of a later application system is facilitated. In addition, the duration threshold value is dynamically configurable, so that SQL monitoring is more flexible; the method can realize relatively perfect slow query SQL monitoring according to the target log, and provides an effective guarantee means for the analysis and optimization of subsequent SQL and the standardized design of SQL.
In the following, first, an application scenario of the solution provided by the present application is illustrated.
Fig. 1 is a schematic view of an application scenario provided in an embodiment of the present application. As shown in fig. 1, in the present application scenario, a user uses an application a through a client 101, and a server of the application a corresponds to a server 102. When the application A executes SQL, the server 102 intercepts SQL through the global SQL interceptor and outputs a target log corresponding to SQL with the execution time longer than a time length threshold so as to perform early warning according to the target log. The specific implementation process of the server 102 intercepting the SQL through the global SQL interceptor and outputting the target log corresponding to the SQL with the execution time longer than the duration threshold may be referred to the schemes of the following embodiments.
It should be noted that fig. 1 is only a schematic diagram of an application scenario provided by an embodiment of the present application, and the embodiment of the present application does not limit the devices included in fig. 1 or limit the positional relationship between the devices in fig. 1.
The following describes the technical scheme of the present application and how the technical scheme of the present application solves the above technical problems in detail with specific embodiments. The following embodiments may be combined with each other, and the same or similar concepts or processes may not be described in detail in some embodiments. Embodiments of the present application will be described below with reference to the accompanying drawings.
Fig. 2 is a flowchart of an SQL monitoring method according to an embodiment of the present application, which is applied to a server, and the server is equipped with a global SQL interceptor. As shown in fig. 2, the method of the embodiment of the present application includes:
s201, intercepting target SQL to be executed through a global SQL interceptor.
In the embodiment of the application, when the application of the server executes the SQL session (session) corresponding to the target SQL, the target SQL to be executed is intercepted by the global SQL interceptor, and then the application enters the global SQL interceptor.
Optionally, the global SQL interceptor is a Mybatis interceptor.
Illustratively, the Mybatis Interceptor (Interceptor) is a plug-in to Mybatis, which can be applied to Mybatis programming. Using the Spring (an open source application framework) principle of tangent plane oriented programming (Aspect Oriented Programming, AOP), mybatis interceptors can be injected into an application as a Bean (a software component model describing Java). When the application of the server executes to the target SQL, the target SQL is intercepted by a Mybatis interceptor, and in the Mybatis interceptor, the design of a target log element item corresponding to the target SQL can be realized, wherein the target log element item is for example, the start time of the target SQL execution, the identification of the target SQL (for example, the identification is expressed by sqlID), the end time of the target SQL execution, the execution time corresponding to the target SQL, the execution statement of the target SQL and the like.
S202, executing the target SQL through the global SQL interceptor and recording the starting time of the target SQL.
Illustratively, assuming that the global SQL interceptor is a Mybatis interceptor, the target SQL may be executed by the global SQL interceptor invoking the method of invoke (for invoking the intercepted method) and recording the start time of target SQL execution (e.g., as denoted by t 1).
S203, when the target SQL is executed, recording the ending time of the target SQL execution through the global SQL interceptor.
Illustratively, when the target SQL is executed, the end time of the target SQL execution (e.g., as represented by t 2) may be recorded by the global SQL interceptor.
S204, determining the execution duration corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time.
For example, after the start time t1 and the end time t2 are obtained, a corresponding execution duration (e.g., denoted as costTime) of the target SQL may be determined by the global SQL interceptor according to a difference between the start time t1 and the end time t2, i.e., costtime=t2-t 1.
S205, if the execution time length is greater than a time length threshold, outputting a target log corresponding to the target SQL through the global SQL interceptor so as to perform early warning according to the target log, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time length threshold is dynamically configurable.
In this step, the duration threshold is dynamically configurable, and may be configured according to the service requirement, where the duration threshold is, for example, 1 second. The information of the target log element item corresponding to the target SQL is the target log element item and the value corresponding to the target log element item, the target log element item can be configured according to the requirement, and the embodiment of the application does not limit the target log element item. After the execution duration corresponding to the target SQL is determined through the global SQL interceptor, the execution duration corresponding to the target SQL can be compared with a duration threshold, if the execution duration is greater than the duration threshold, the target SQL can be formatted through the global SQL interceptor, a target log corresponding to the target SQL is output according to a preset log output format, and the target log comprises information of target log element items corresponding to the target SQL, namely, the information of the target log element items needing to be focused is obtained, so that early warning is carried out according to the target log. For specific pre-warning according to the target log, reference may be made to the following embodiments, which are not described here again. The preset log output format is, for example, that target log element items are sequentially output according to a preset sequence, and target logs corresponding to target SQL are output by using a unified preset log output format, so that specific log contents of the target logs can be conveniently checked in a visual mode, and early warning, summarizing statistics and the like are conveniently carried out. Target SQL with a time length greater than a time length threshold is executed and can be understood as slow query SQL. And after outputting the target log corresponding to the target SQL, exiting the global SQL interceptor.
Optionally, if the execution duration is less than or equal to the duration threshold, exiting the global SQL interceptor.
The SQL monitoring method provided by the embodiment of the application is applied to a server, and the server is provided with a global SQL interceptor; intercepting target SQL to be executed by a global SQL interceptor, executing the target SQL by the global SQL interceptor, recording the start time of the target SQL execution, and recording the end time of the target SQL execution when the target SQL is executed; and determining the execution time length corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time, and outputting a target log corresponding to the target SQL through the global SQL interceptor if the execution time length is greater than a time length threshold value, so as to perform early warning according to the target log, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time length threshold value is dynamically configurable. According to the embodiment of the application, the target SQL to be executed is intercepted through the global SQL interceptor, the target SQL is executed, and the target log is output according to the obtained execution time length corresponding to the target SQL, so that the codes for obtaining the execution time length corresponding to the SQL and outputting the log are not required to be added at each position for calling the SQL in the codes of the application system, namely, the logic of the original application system is not required to be modified, therefore, the efficiency of monitoring the execution time length of the SQL can be greatly improved, the reusability and the usability are higher, and the maintenance of the later application system is convenient. In addition, the duration threshold value is dynamically configurable, so that SQL monitoring is more flexible; and early warning is carried out according to the target log, so that relatively perfect SQL monitoring of slow query can be realized.
Fig. 3 is a flowchart of an SQL monitoring method according to another embodiment of the present application. Based on the above embodiments, the embodiment of the present application further describes an SQL monitoring method. As shown in fig. 3, the method of the embodiment of the present application may include:
s301, intercepting target SQL to be executed through a global SQL interceptor.
A detailed description of this step may be referred to the related description of S201 in the embodiment shown in fig. 2, and will not be repeated here.
S302, responding to configuration operation of the log element items corresponding to the SQL, and acquiring target log element items through a global SQL interceptor.
For example, the journal element items corresponding to the SQL may be configured as required to obtain the information of the target journal element items needing to be focused on. In this step, in response to a configuration operation on a log element item corresponding to SQL, a target log element item may be recorded by a diagnostic context map (Mapped Diagnostic Contexts, MDC) of a simple log portal (Simple Logging Facade for Java, slf4 j) in the global SQL interceptor.
S303, responding to the configuration operation of the time length threshold value, and acquiring the time length threshold value configured by the configuration operation through the global SQL interceptor.
In this step, the duration threshold is dynamically configurable, and may be configured according to the service requirement, where the duration threshold is, for example, 1 second. In response to a configuration operation of the time length threshold, the time length threshold configured by the configuration operation can be acquired through the global SQL interceptor.
It should be noted that, in the embodiment of the present application, the execution sequence of the steps S302 and S303 is not limited, and S302 may be executed first, then S303 may be executed, or S303 may be executed first, then S302 may be executed.
S304, executing the target SQL through the global SQL interceptor and recording the starting time of the target SQL.
A detailed description of this step may be referred to the related description of S202 in the embodiment shown in fig. 2, and will not be repeated here.
S305, when the target SQL is executed, recording the ending time of the target SQL execution through the global SQL interceptor.
A detailed description of this step may be referred to the related description of S203 in the embodiment shown in fig. 2, and will not be repeated here.
S306, determining the execution duration corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time.
A detailed description of this step may be referred to as S204 in the embodiment shown in fig. 2, and will not be described herein.
S307, if the execution time is greater than a time length threshold, outputting a target log corresponding to the target SQL through the global SQL interceptor, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time length threshold is dynamically configurable.
A specific description of this step may be referred to in the embodiment shown in fig. 2 with respect to S205, and for a specific implementation process of early warning according to the target log, reference may be made to the following steps S309 and S310.
S308, if the execution duration is less than or equal to the duration threshold, exiting the global SQL interceptor.
In this step, the execution duration of the SQL with the duration less than or equal to the duration threshold may be understood as the normal execution of the SQL, that is, the SQL is not a slow query, and the corresponding log does not need to be output through the global SQL interceptor.
S309, if the first log quantity of the target log in the first preset time period is greater than the quantity threshold value, outputting first early warning information.
The first early warning information is used for indicating that a first log quantity in a first preset duration is larger than a quantity threshold value.
In this step, the first preset time period may be set as required, and the first preset time period is, for example, one day. The number threshold may be set as desired. The embodiment of the application does not limit the first preset time length and the quantity threshold value. After the target log is obtained, a first log amount of the target log in a first preset time period can be obtained, the first log amount is compared with a quantity threshold value, and if the first log amount is larger than the quantity threshold value, first early warning information is output, so that operation and maintenance personnel can check the first early warning information in time and perform corresponding processing.
And S310, outputting second early warning information if the execution duration of the SQL corresponding to the target log is longer than the preset early warning duration.
The second early warning information is used for indicating that the execution duration of SQL corresponding to the target log is longer than the preset early warning duration.
In this step, the preset early warning duration can be set as required, and the embodiment of the application does not limit the preset early warning duration. After the target log is obtained, the execution time length of the SQL corresponding to the target log can be compared with the preset early warning time length, and if the execution time length of the SQL corresponding to the target log is longer than the preset early warning time length, the second early warning information is output, so that operation and maintenance personnel can check the second early warning information in time and perform corresponding processing.
S311, responding to the query operation of the target log, and outputting the target log.
In the step, after the target log is obtained and stored in the server, the operator can query the target log. Correspondingly, the server side responds to the query operation on the target log and outputs the target log, so that an operator can conveniently check the information of the target log element item corresponding to the target SQL in the target log, such as the execution time of the target SQL in a visual way.
S312, obtaining a second log amount in a second preset time period; and outputting trend change conditions corresponding to the logs according to the second log quantity.
In this step, the second preset time period may be set as required, for example, the second preset time period is one day, and the embodiment of the present application does not limit the second preset time period. The second log amount of each day is obtained, and the trend change condition corresponding to the log is output according to the second log amount. This step can be understood as a statistical summary of the target log.
It should be noted that, the embodiment of the present application does not limit the execution sequence of the steps S309, S310, S311, and S312.
The SQL monitoring method provided by the embodiment of the application is applied to a server, and the server is provided with a global SQL interceptor; intercepting target SQL to be executed by a global SQL interceptor, and responding to configuration operation of a log element item corresponding to the SQL, and acquiring the target log element item by the global SQL interceptor; responding to configuration operation of the time length threshold value, and acquiring the time length threshold value configured by the configuration operation through a global SQL interceptor; executing the target SQL through the global SQL interceptor, recording the start time of the target SQL, and recording the end time of the target SQL when the target SQL is executed; determining the execution time length corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time, and outputting a target log corresponding to the target SQL through the global SQL interceptor if the execution time length is greater than a time length threshold value, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time length threshold value is dynamically configurable; if the execution duration is less than or equal to the duration threshold, exiting the global SQL interceptor; if the first log quantity of the target logs in the first preset time period is larger than the quantity threshold value, outputting first early warning information; if the execution duration of SQL corresponding to the target log is longer than the preset early warning duration, outputting second early warning information; responding to the query operation of the target log, and outputting the target log; acquiring a second log amount in a second preset time period; and outputting trend change conditions corresponding to the logs according to the second log quantity. According to the embodiment of the application, the target SQL to be executed is intercepted through the global SQL interceptor, the target SQL is executed, and the target log is output according to the obtained execution time length corresponding to the target SQL, so that the codes for obtaining the execution time length corresponding to the SQL and outputting the log are not required to be added at each position for calling the SQL in the codes of the application system, namely, the logic of the original application system is not required to be modified, therefore, the efficiency of monitoring the execution time length of the SQL can be greatly improved, the reusability and the usability are higher, and the maintenance of the later application system is convenient. In addition, the duration threshold value is dynamically configurable, so that SQL monitoring is more flexible; and early warning is carried out according to the target log, log inquiry is supported, statistics and summarization of the log are carried out, and relatively perfect SQL monitoring of slow inquiry can be realized.
The following are examples of the apparatus of the present application that may be used to perform the method embodiments of the present application. For details not disclosed in the embodiments of the apparatus of the present application, please refer to the embodiments of the method of the present application.
Fig. 4 is a schematic structural diagram of an SQL monitoring device according to an embodiment of the present application, which is applied to a server, and the server is equipped with a global SQL interceptor. As shown in fig. 4, the SQL monitoring apparatus 400 of the embodiment of the present application includes: an interception module 401, a first processing module 402, a determination module 403 and an output module 404. Wherein:
the interception module 401 is configured to intercept, by using a global SQL interceptor, a target SQL to be executed.
A first processing module 402, configured to execute the target SQL through the global SQL interceptor and record a start time of the target SQL execution; and recording the end time of the target SQL execution through the global SQL interceptor when the target SQL is executed.
A determining module 403, configured to determine, according to the start time and the end time, an execution duration corresponding to the target SQL through the global SQL interceptor.
And the output module 404 is configured to output, through the global SQL interceptor, a target log corresponding to the target SQL if the execution time is greater than the time threshold, so as to perform early warning according to the target log, where the target log includes information of a target log element item corresponding to the target SQL, and the time threshold is dynamically configurable.
In some embodiments, the SQL monitoring apparatus 400 may further include a first obtaining module 405 configured to obtain, by the global SQL interceptor, the target log element item in response to a configuration operation on the log element item corresponding to the SQL before the first processing module 402 executes the target SQL by the global SQL interceptor.
In some embodiments, the SQL monitoring apparatus 400 may further include a second obtaining module 406 configured to obtain, by the global SQL interceptor, a duration threshold configured by the configuration operation in response to the configuration operation of the duration threshold before the first processing module 402 executes the target SQL by the global SQL interceptor.
Optionally, the SQL monitoring apparatus 400 may further include a second processing module 407, configured to output first early warning information if the first log amount of the target log in the first preset duration is greater than the number threshold, where the first early warning information is used to indicate that the first log amount in the first preset duration is greater than the number threshold; if the execution time of the SQL corresponding to the target log is longer than the preset early warning time, outputting second early warning information, wherein the second early warning information is used for indicating that the execution time of the SQL corresponding to the target log is longer than the preset early warning time.
Optionally, the SQL monitoring apparatus 400 may further include a third processing module 408, configured to output the target log in response to a query operation on the target log after the output module 404 outputs the target log corresponding to the target SQL.
Optionally, the SQL monitoring device further includes a fourth processing module 409, configured to obtain a second log amount within a second preset duration after the output module 404 outputs a target log corresponding to the target SQL; and outputting trend change conditions corresponding to the logs according to the second log quantity.
Optionally, the global SQL interceptor is a Mybatis interceptor.
The device of the embodiment of the present application may be used to execute the technical solution of any of the above-described embodiments of the method, and its implementation principle and technical effects are similar, and are not repeated here.
Fig. 5 is a schematic structural diagram of an SQL monitoring device according to another embodiment of the present application, which is applied to a server, and the server is equipped with a global SQL interceptor. As shown in fig. 5, the SQL monitoring apparatus 500 of the embodiment of the present application includes: an interceptor module 501 and a log module 502. Wherein:
the interceptor module 501 is configured to intercept, by using the global SQL interceptor, the target SQL to be executed, and obtain, by using the global SQL interceptor, the target log element item in response to a configuration operation on the log element item corresponding to the SQL; responding to configuration operation of the time length threshold value, and acquiring the time length threshold value configured by the configuration operation through a global SQL interceptor; executing the target SQL through the global SQL interceptor, recording the start time of the target SQL, and recording the end time of the target SQL when the target SQL is executed; determining the execution time length corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time, and outputting a target log corresponding to the target SQL through the global SQL interceptor if the execution time length is greater than a time length threshold value, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time length threshold value is dynamically configurable; if the execution duration is less than or equal to the duration threshold, exiting the global SQL interceptor.
The log module 502 is configured to obtain a target log; if the first log quantity of the target logs in the first preset time period is judged to be larger than the quantity threshold value, outputting first early warning information; if the execution time of SQL corresponding to the target log is longer than the preset early warning time, outputting second early warning information, namely realizing the monitoring early warning of the slow query SQL; responding to the query operation of the target log, and outputting the target log, namely realizing the query of the log; acquiring a second log amount in a second preset time period; and outputting trend change conditions corresponding to the logs according to the second log quantity, namely realizing statistics and summarization of the logs. It will be appreciated that when the log module 502 outputs the target log, the field entries in the target log and the output order of the field entries are consistent with the target log element entries in the interceptor module 501, i.e., the field entries are identical and output in the same order. In some embodiments, the log module 502 may also collect the obtained target logs periodically on a unified log server, and then import the collected target logs into the log system at the service end in the embodiment of the present application, where the log system needs to have complete log processing capability and early warning capability, for example, a currently commonly used Kibana system (an open source analysis and visualization platform) may be tightly combined with an elastic search (a distributed search analysis engine) to provide a visual interface, which may be used to perform full-text search of the log in a distributed environment.
It can be appreciated that the function of the interceptor module in the embodiment of the present application is similar to that of the interception module, the first processing module, the determination module, the output module, the first acquisition module, and the second acquisition module in the above embodiment; the log module in the embodiment of the present application has functions similar to those of the second, third, and fourth processing modules in the above-described embodiment.
Fig. 6 is a schematic structural diagram of an electronic device according to an embodiment of the application. As shown in fig. 6, the electronic device 600 may include: at least one processor 601 and a memory 602.
A memory 602 for storing programs. In particular, the program may include program code including computer-executable instructions.
The memory 602 may include high-speed random access memory (Random Access Memory, RAM) and may also include non-volatile memory (non-volatile memory), such as at least one disk memory.
The processor 601 is configured to execute computer-executable instructions stored in the memory 602 to implement the SQL monitoring method described in the foregoing method embodiments. The processor 601 may be a central processing unit (Central Processing Unit, CPU), or an application specific integrated circuit (Application Specific Integrated Circuit, ASIC), or one or more integrated circuits configured to implement embodiments of the present application. Specifically, when implementing the SQL monitoring method described in the foregoing method embodiment, the electronic device may be, for example, an electronic device having a processing function, such as a server.
Optionally, the electronic device 600 may also include a communication interface 603. In a specific implementation, if the communication interface 603, the memory 602, and the processor 601 are implemented independently, the communication interface 603, the memory 602, and the processor 601 may be connected to each other through buses and perform communication with each other. The bus may be an industry standard architecture (Industry Standard Architecture, ISA) bus, an external device interconnect (Peripheral Component, PCI) bus, or an extended industry standard architecture (Extended Industry Standard Architecture, EISA) bus, among others. Buses may be divided into address buses, data buses, control buses, etc., but do not represent only one bus or one type of bus.
Alternatively, in a specific implementation, if the communication interface 603, the memory 602, and the processor 601 are integrated on a chip, the communication interface 603, the memory 602, and the processor 601 may complete communication through internal interfaces.
The electronic device is configured to execute the SQL monitoring method in any of the foregoing method embodiments, and its implementation principle and technical effects are similar, and are not described herein again.
The application also provides a computer readable storage medium, wherein the computer readable storage medium stores computer program instructions, and when the processor executes the computer program instructions, the scheme of the SQL monitoring method is realized.
The application also provides a computer program product comprising a computer program which when executed by a processor implements the solution of the SQL monitoring method as above.
The computer readable storage medium described above may be implemented by any type or combination of volatile or non-volatile Memory devices, such as static random access Memory (Static Random Access Memory, SRAM), electrically erasable programmable Read-Only Memory (Electrically Erasable Programmable Read Only Memory, EEPROM), erasable programmable Read-Only Memory (Erasable Programmable Read Only Memory, EPROM), programmable Read-Only Memory (Programmable Read Only Memory, PROM), read Only Memory (ROM), magnetic Memory, flash Memory, magnetic disk, or optical disk. A readable storage medium can be any available medium that can be accessed by a general purpose or special purpose computer.
An exemplary readable storage medium is coupled to the processor such the processor can read information from, and write information to, the readable storage medium. In the alternative, the readable storage medium may be integral to the processor. The processor and the readable storage medium may reside in an application specific integrated circuit. Of course, the processor and the readable storage medium may reside as discrete components in an SQL monitor.
Those of ordinary skill in the art will appreciate that: all or part of the steps for implementing the method embodiments described above may be performed by hardware associated with program instructions. The foregoing program may be stored in a computer readable storage medium. The program, when executed, performs steps including the method embodiments described above; and the aforementioned storage medium includes: various media that can store program code, such as ROM, RAM, magnetic or optical disks.
Finally, it should be noted that: the above embodiments are only for illustrating the technical solution of the present application, and not for limiting the same; although the application has been described in detail with reference to the foregoing embodiments, it will be understood by those of ordinary skill in the art that: the technical scheme described in the foregoing embodiments can be modified or some or all of the technical features thereof can be replaced by equivalents; such modifications and substitutions do not depart from the spirit of the application.

Claims (10)

1. The SQL monitoring method is characterized by being applied to a server, wherein the server is provided with a global SQL interceptor, and the SQL monitoring method comprises the following steps:
intercepting target SQL to be executed by the global SQL interceptor;
executing the target SQL through the global SQL interceptor and recording the starting time of the target SQL execution;
when the target SQL is executed, recording the ending time of the execution of the target SQL through the global SQL interceptor;
determining the execution duration corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time;
and if the execution time is longer than a time length threshold, outputting a target log corresponding to the target SQL through the global SQL interceptor so as to perform early warning according to the target log, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time length threshold is dynamically configurable.
2. The method of claim 1, further comprising, prior to the execution of the target SQL by the global SQL interceptor:
and responding to configuration operation of the log element items corresponding to the SQL, and acquiring the target log element items through the global SQL interceptor.
3. The method of claim 1, further comprising, prior to the execution of the target SQL by the global SQL interceptor:
and responding to the configuration operation of the time length threshold value, and acquiring the time length threshold value configured by the configuration operation through the global SQL interceptor.
4. A method according to any one of claims 1 to 3, wherein the pre-warning according to the target log comprises:
outputting first early warning information if the first log amount of the target log in the first preset time period is larger than a quantity threshold value, wherein the first early warning information is used for indicating that the first log amount in the first preset time period is larger than the quantity threshold value;
and if the execution time of the SQL corresponding to the target log is longer than the preset early warning time, outputting second early warning information, wherein the second early warning information is used for indicating that the execution time of the SQL corresponding to the target log is longer than the preset early warning time.
5. The SQL monitoring method according to any one of claims 1 to 3, wherein after the outputting the target log corresponding to the target SQL, further comprising:
and responding to the query operation of the target log, and outputting the target log.
6. The SQL monitoring method according to any one of claims 1 to 3, wherein after the outputting the target log corresponding to the target SQL, further comprising:
acquiring a second log amount in a second preset time period;
and outputting trend change conditions corresponding to the logs according to the second log quantity.
7. A method of monitoring SQL according to any one of claims 1 to 3, wherein the global SQL interceptor is a Mybatis interceptor.
8. The SQL monitoring device is characterized by being applied to a server, wherein the server is provided with a global SQL interceptor, and the SQL monitoring device comprises:
the interception module is used for intercepting target SQL to be executed through the global SQL interceptor;
the first processing module is used for executing the target SQL through the global SQL interceptor and recording the starting time of the target SQL execution; recording the ending time of the execution of the target SQL through the global SQL interceptor when the execution of the target SQL is completed;
the determining module is used for determining the execution duration corresponding to the target SQL through the global SQL interceptor according to the starting time and the ending time;
and the output module is used for outputting a target log corresponding to the target SQL through the global SQL interceptor if the execution time is longer than a time threshold value so as to perform early warning according to the target log, wherein the target log comprises information of target log element items corresponding to the target SQL, and the time threshold value is dynamically configurable.
9. An electronic device, comprising: a processor, and a memory communicatively coupled to the processor;
the memory stores computer-executable instructions;
the processor executes computer-executable instructions stored by the memory to implement the SQL monitoring method of any one of claims 1 to 7.
10. A computer readable storage medium, characterized in that the computer readable storage medium has stored therein computer program instructions, which when executed, implement the SQL monitoring method according to any one of claims 1 to 7.
CN202310604327.9A 2023-05-26 2023-05-26 SQL monitoring method, device, equipment and storage medium Pending CN116627786A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202310604327.9A CN116627786A (en) 2023-05-26 2023-05-26 SQL monitoring method, device, equipment and storage medium

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202310604327.9A CN116627786A (en) 2023-05-26 2023-05-26 SQL monitoring method, device, equipment and storage medium

Publications (1)

Publication Number Publication Date
CN116627786A true CN116627786A (en) 2023-08-22

Family

ID=87591566

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202310604327.9A Pending CN116627786A (en) 2023-05-26 2023-05-26 SQL monitoring method, device, equipment and storage medium

Country Status (1)

Country Link
CN (1) CN116627786A (en)

Similar Documents

Publication Publication Date Title
CN109284269B (en) Abnormal log analysis method and device, storage medium and server
KR102033971B1 (en) Data quality analysis
US10437703B2 (en) Correlation of source code with system dump information
CN111159706A (en) Database security detection method, device, equipment and storage medium
CN103699480B (en) A kind of WEB dynamic security leak detection method based on JAVA
CN113312241A (en) Abnormal alarm method, access log generation method and operation and maintenance system
WO2006128062A2 (en) Database caching of queries and stored procedures using database provided facilities for dependency analysis and detected database updates for invalidation
CN112307057A (en) Data processing method and device, electronic equipment and computer storage medium
CN110716878B (en) Automatic interface testing method, device and system
US20170351599A1 (en) Automatically rerunning test executions
US20160224635A1 (en) Analysis of data utilization
US20190005252A1 (en) Device for self-defense security based on system environment and user behavior analysis, and operating method therefor
CN113760491A (en) Task scheduling system, method, equipment and storage medium
CN111435327B (en) Log record processing method, device and system
CN111784176A (en) Data processing method, device, server and medium
CN116627786A (en) SQL monitoring method, device, equipment and storage medium
US8935200B2 (en) Dynamic database dump
CN116610567A (en) Early warning method and device for abnormal application program, processor and electronic equipment
CN114357001A (en) Multi-cluster data query method and device, monitoring platform and storage medium
US11100131B2 (en) Simulation of a synchronization of records
CN115374074A (en) Log processing method and device, storage medium and electronic equipment
CN114143236A (en) Business system testing method and device, electronic equipment and readable storage medium
CN112543127A (en) Monitoring method and device of micro-service architecture
CN113641572B (en) Debugging method for massive big data computing development based on SQL
CN116661758B (en) Method, device, electronic equipment and medium for optimizing log framework configuration

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination