CN113626281A - Slow SQL statement tracking method and device, electronic equipment and storage medium - Google Patents

Slow SQL statement tracking method and device, electronic equipment and storage medium Download PDF

Info

Publication number
CN113626281A
CN113626281A CN202110802805.8A CN202110802805A CN113626281A CN 113626281 A CN113626281 A CN 113626281A CN 202110802805 A CN202110802805 A CN 202110802805A CN 113626281 A CN113626281 A CN 113626281A
Authority
CN
China
Prior art keywords
sql statement
slow
slow sql
work order
tracked
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
CN202110802805.8A
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.)
Beijing Fangjianghu Technology Co Ltd
Original Assignee
Beijing Fangjianghu Technology Co 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 Beijing Fangjianghu Technology Co Ltd filed Critical Beijing Fangjianghu Technology Co Ltd
Priority to CN202110802805.8A priority Critical patent/CN113626281A/en
Publication of CN113626281A publication Critical patent/CN113626281A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • 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/3055—Monitoring arrangements for monitoring the status of the computing system or of the computing system component, e.g. monitoring if the computing system is on, off, available, not available
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/30—Monitoring
    • G06F11/3089—Monitoring arrangements determined by the means or processing involved in sensing the monitored data, e.g. interfaces, connectors, sensors, probes, agents
    • G06F11/3093—Configuration details thereof, e.g. installation, enabling, spatial arrangement of the probes
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F2201/00—Indexing scheme relating to error detection, to error correction, and to monitoring
    • G06F2201/80—Database-specific techniques
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F2201/00—Indexing scheme relating to error detection, to error correction, and to monitoring
    • G06F2201/865—Monitoring of software

Landscapes

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

Abstract

The invention provides a slow SQL statement tracking method, a slow SQL statement tracking device, electronic equipment and a storage medium, wherein a slow SQL statement to be tracked is determined based on an SQL log file; and then determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a work order to be processed corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed. The method and the device can realize the tracking of the slow SQL sentences to be tracked, determine the processing state of the slow SQL sentences to be tracked in real time, enable the slow SQL sentences to be tracked to follow the solution progress in time after being found, improve the processing efficiency of the slow SQL sentences to be tracked, and further reduce the risk of system faults.

Description

Slow SQL statement tracking method and device, electronic equipment and storage medium
Technical Field
The invention relates to the technical field of computer software, in particular to a slow SQL statement tracking method and device, electronic equipment and a storage medium.
Background
Structured Query Language (SQL) is a database Query and programming Language for accessing data and querying, updating, and managing a relational database system, a Language for operating on database systems.
In software programming, the slow SQL phenomenon may occur due to the developer's programming power and frequent iterations of the business. The slow SQL phenomenon means that after the application runs a service for a period of time, the partial SQL sentences occupy the CPU for a long time when being executed because the partial SQL sentences frequently perform I/O operations on the database. The SQL statement corresponding to the slow SQL phenomenon is a slow SQL statement. For an application, a small number of slow SQL statements may not affect normal operation of a service, but as the slow SQL statements increase to a certain extent, execution of other service functions in the application may be affected, and even a system failure is caused, resulting in unavailability of the system. Therefore, tracking the slow SQL statements plays a crucial role in improving the processing efficiency of the slow SQL statements and further reducing the possibility of system failure.
Disclosure of Invention
The invention provides a slow SQL statement tracking method and device, electronic equipment and a storage medium, which are used for overcoming the defects in the prior art.
The invention provides a slow SQL statement tracking method, which comprises the following steps:
determining a slow SQL statement to be traced based on the SQL log file;
and determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a to-be-processed work order corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the to-be-processed work order, and determining the processing state of the to-be-processed work order.
According to the slow SQL statement tracking method provided by the invention, the SQL log file comprises an SQL log file of each business item, and the SQL log file of each business item comprises an SQL statement and the execution duration of the SQL statement;
correspondingly, the determining a slow SQL statement to be traced based on the SQL log file specifically includes:
for any SQL statement corresponding to any business project, if the execution duration of any SQL statement is judged to be greater than or equal to a first preset duration, determining that any SQL statement is the slow SQL statement to be tracked;
the first preset duration corresponds to the service items one to one.
According to the slow SQL statement tracking method provided by the invention, the slow SQL statement to be tracked is determined based on the SQL log file, and the method further comprises the following steps:
if the execution duration of any SQL statement is judged to be greater than or equal to a second preset duration, determining that any SQL statement is a slow SQL statement;
if the execution duration of the slow SQL statement is judged to be greater than or equal to a third preset duration, determining the slow SQL statement as the slow SQL statement to be tracked;
the second preset time length corresponds to the service items one by one, and the second preset time length corresponding to the service items is less than the first preset time length; and the third preset time length corresponds to each service project.
According to the slow SQL statement tracking method provided by the present invention, the determining the feature information of the slow SQL statement to be tracked further includes:
if the characteristic information is judged to be not appeared for the first time, determining a target work order corresponding to the characteristic information, and judging whether the processing state of the target work order is finished or not;
and if the processing state of the target work order is finished, generating a work order to be processed corresponding to the SQL log file based on the work order generation system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed.
According to the slow SQL statement tracking method provided by the present invention, the determining whether the processing state of the target work order is completed further includes:
and if the processing state of the target work order is not finished, storing the SQL log file and the characteristic information.
According to the slow SQL statement tracking method provided by the invention, the step of determining the characteristic information of the slow SQL statement to be tracked specifically comprises the following steps:
and extracting a statement template corresponding to the slow SQL statement to be tracked, and taking the statement template as the characteristic information.
According to the slow SQL statement tracking method provided by the invention, the determination of the characteristic information of the slow SQL statement to be tracked further comprises the following steps:
and encrypting the statement template to obtain an identification mark of the statement template, and if the identification mark is judged to appear for the first time, determining that the characteristic information appears for the first time.
According to the slow SQL statement tracking method provided by the invention, the determination of the characteristic information of the slow SQL statement to be tracked further comprises the following steps:
and if the characteristic information is judged to appear for the first time, sending a prompt for finding the slow SQL statement to be tracked to the user terminal.
The present invention also provides a computer program product comprising a computer program or instructions which, when executed by a processor, performs the steps of the slow SQL statement tracking method according to any of the above.
The present invention also provides a non-transitory computer readable storage medium having stored thereon a computer program which, when executed by a processor, implements the steps of the slow SQL statement tracking method according to any of the above.
According to the slow SQL statement tracking method, the slow SQL statement tracking device, the electronic equipment and the storage medium, firstly, the slow SQL statement to be tracked is determined based on the SQL log file; and then determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a work order to be processed corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed. The method and the device can realize the tracking of the slow SQL sentences to be tracked, determine the processing state of the slow SQL sentences to be tracked in real time, enable the slow SQL sentences to be tracked to follow the solution progress in time after being found, improve the processing efficiency of the slow SQL sentences to be tracked, and further reduce the risk of system faults.
Drawings
In order to more clearly illustrate the technical solutions of the present invention or the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and it is obvious for those skilled in the art to obtain other drawings based on these drawings without creative efforts.
FIG. 1 is a flow chart diagram of a slow SQL statement tracking method provided by the present invention;
FIG. 2 is a schematic illustration of a display of a processing status of a work order to be processed according to the present invention;
FIG. 3 is a schematic view of the display of the pending work order of the present invention at the end of the processing state;
FIG. 4 is a schematic diagram of a hint page for slow SQL statements to appear according to the present invention;
FIG. 5 is a schematic diagram of the complete flow of the slow SQL statement tracking method provided by the present invention;
FIG. 6 is a schematic structural diagram of a slow SQL statement tracking device provided by the present invention;
fig. 7 is a schematic structural diagram of an electronic device provided by the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention clearer, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings, and it is obvious that the described embodiments are some, but not all embodiments of the present invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
In the prior art, no related technology for tracking the slow SQL statement exists, so that the slow SQL statement cannot follow up the solution progress in time after being found, the processing efficiency of the slow SQL statement is not ideal, and the risk of system failure is increased. Therefore, the embodiment of the invention provides a slow SQL statement tracking method.
Fig. 1 is a schematic flow chart of a slow SQL statement tracking method provided in an embodiment of the present invention, as shown in fig. 1, the method includes:
s1, determining slow SQL sentences to be traced based on the SQL log files;
and S2, determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a to-be-processed work order corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the to-be-processed work order, and determining the processing state of the to-be-processed work order.
Optionally, in the slow SQL statement tracking method provided in the embodiment of the present invention, the execution subject is a server, the server may be a local server or a cloud server, and the local server may specifically be a computer, a tablet computer, a smart phone, and the like, which is not specifically limited in the embodiment of the present invention.
Step S1 is executed first to obtain an SQL log file. The SQL log file may be an SQL log file of each business item. The service item may be an item for implementing a specific service function, such as item for searching a target house source, searching a house source picture, and the like. The number of business items can be several, i.e., SQL log files of one or more business items can be obtained. The SQL log file may typically contain several SQL statements and the execution duration of each SQL statement. The unit of the execution duration of each SQL statement is ms. When the SQL statements contained in the SQL log file are slow SQL statements, the SQL log file is a slow SQL log file. The slow SQL log file may include: the method comprises the steps of application ID, instance domain name, instance port, database, Trace ID, execution duration, slow SQL statement occurrence time, repeated occurrence of slow SQL statements and specific forms of the occurring slow SQL statements.
After the SQL log file is obtained, the slow SQL statement to be traced may be determined according to the SQL log file. The slow SQL statement to be traced refers to a slow SQL statement that needs to be traced to determine whether the slow SQL statement is processed or not and information such as a current processing flow and a current processing state. That is to say, when the slow SQL statement to be tracked is determined, the slow SQL statement to be tracked may be determined directly according to the execution duration of the SQL statement included in the SQL log file, or the slow SQL statement may be determined according to the execution duration of the SQL statement included in the SQL log file, and then it is determined whether the slow SQL statement is the slow SQL statement to be tracked. The specific manner of determining the slow SQL statement to be traced is not specifically limited in the embodiment of the present invention.
Then, step S2 is executed to determine feature information of the slow SQL statement to be tracked, where the feature information may be extracted from the slow SQL statement to be tracked, and may be fingerprint information, template information, or identification information of the slow SQL statement to be tracked, and is used to represent common information of a type of slow SQL statement to which the slow SQL statement to be tracked belongs. For example, if the slow SQL statement 1 is "30 yuan ≦ price ≦ 100 yuan" indicating a target object with a query price between 30 yuan and 100 yuan, the feature information of the slow SQL statement 1 may be a statement template of the slow SQL statement 1, that is, "a ≦ price ≦ B"; the SQL statement 2 is that the "101 Yuan ≦ price ≦ 200 Yuan" which represents the target object with the query price between 101 Yuan and 200 Yuan, and the statement template of the slow SQL statement 2 can be the same as the statement template of the slow SQL statement 1, and is "A ≦ price ≦ B". That is, the characteristic information of the SQL statement may represent a type of SQL statement, and is a fixed query format obtained by ignoring changeable data information.
After determining the characteristic information of the slow SQL statement to be tracked, judging whether the characteristic information of the slow SQL statement to be tracked appears for the first time, if so, storing the SQL log file and the characteristic information of the slow SQL statement to be tracked, generating a work order to be processed corresponding to the characteristic information of the slow SQL statement to be tracked through a work order generating system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed. The work order generation system in the embodiment of the present invention is not particularly limited.
In the embodiment of the invention, the slow SQL log file to be tracked can be input into the work order generation system, the work order generation system generates the work order to be processed corresponding to the characteristic information of the slow SQL statement to be tracked according to the slow SQL log file to be tracked, and the generated work order to be processed can be sent to the target personnel, so that the target personnel can process the work order to be processed. The target person may be a technician who can process the work order to be processed, or a leader who can assign the work order to be processed to the technician, such as a person in charge of the business project, which is not specifically limited in the embodiment of the present invention. The person in charge may also re-designate the handler of the work order to be processed in the work order generation system.
The target personnel continuously processes the work order to be processed in each stage and feeds back the processing result of each stage to the work order generating system, so that the processing flow of the work order to be processed can be tracked in real time through the work order generating system, and the processing state of the work order to be processed is determined. The processing state of the work order to be processed can represent the processing state of the slow SQL statement to be traced, and can include waiting to be processed, in processing, and finishing. Fig. 2 is a schematic view illustrating a processing status of the to-be-processed work order provided in the embodiment of the present invention, where the to-be-processed work order includes 4 to-be-processed work orders in fig. 2, and each to-be-processed work order corresponds to one ID. Fig. 2 shows that all the processing states of the 4 work orders to be processed are unprocessed.
Fig. 3 is a schematic display diagram of the to-be-processed work order provided in the embodiment of the present invention when the processing state is finished. As shown in fig. 3, among others, may be included: the system comprises an operator, an application ID, an instance domain name, an instance port, a database, a Trace ID, an execution duration, the occurrence time of the slow SQL statement, whether the slow SQL statement repeatedly appears or not and the specific form of the appearing slow SQL statement.
The slow SQL statement tracking method provided by the embodiment of the invention comprises the steps of firstly determining slow SQL statements to be tracked based on an SQL log file; and then determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a work order to be processed corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed. The method and the device can realize the tracking of the slow SQL sentences to be tracked, determine the processing state of the slow SQL sentences to be tracked in real time, enable the slow SQL sentences to be tracked to follow the solution progress in time after being found, improve the processing efficiency of the slow SQL sentences to be tracked, and further reduce the risk of system faults.
On the basis of the above embodiment, in the slow SQL statement tracking method provided in the embodiment of the present invention, the SQL log file includes an SQL log file of each business item, and the SQL log file of each business item includes an SQL statement and an execution duration of the SQL statement;
correspondingly, the determining a slow SQL statement to be traced based on the SQL log file specifically includes:
for any SQL statement corresponding to any business project, if the execution duration of any SQL statement is judged to be greater than or equal to a first preset duration, determining that any SQL statement is the slow SQL statement to be tracked;
the first preset duration corresponds to the service items one to one.
Specifically, in the embodiment of the present invention, the SQL log file may include an SQL log file of a plurality of business items, and the SQL log file of each business item may include a plurality of SQL statements and an execution duration of each SQL statement. Therefore, when the slow SQL statement to be tracked is determined, a first preset time length may be set for each service item, and the service items correspond to the first preset time lengths one to one. And determining whether each SQL statement corresponding to each business item belongs to the slow SQL statement to be tracked or not according to the first preset time length corresponding to each business item.
For any SQL statement corresponding to any business item, if the execution duration of any SQL statement is greater than or equal to a first preset duration, determining that any SQL statement is a slow SQL statement to be tracked. The specific value of the first preset duration can be determined according to the specific requirement of the corresponding service item. For example, the first preset duration corresponding to the service item a may be 500ms, the first preset duration corresponding to the service item B may be 800ms, the first preset duration corresponding to the service item C may be 1s, and the first preset duration corresponding to the service item D may be 1.2 ms.
In the embodiment of the invention, the slow SQL sentences to be tracked corresponding to different business items can be determined by setting the corresponding first preset duration for the different business items, so that the determined slow SQL sentences to be tracked have higher pertinence, and the accuracy of the slow SQL sentences to be tracked is improved.
On the basis of the foregoing embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention, which determines a slow SQL statement to be tracked based on the SQL log file, further includes:
if the execution duration of any SQL statement is judged to be greater than or equal to a second preset duration, determining that any SQL statement is a slow SQL statement;
if the execution duration of the slow SQL statement is judged to be greater than or equal to a third preset duration, determining the slow SQL statement as the slow SQL statement to be tracked;
the second preset time length corresponds to the service items one by one, and the second preset time length corresponding to the service items is less than the first preset time length; and the third preset time length corresponds to each service project.
Specifically, in the embodiment of the present invention, when determining the slow SQL statement to be tracked, a second preset duration may be set for each service item, and the service items correspond to the second preset durations one to one. And determining whether each SQL statement corresponding to each business item belongs to the slow SQL statement or not according to the second preset time length corresponding to each business item. After the slow SQL statements are determined, since the second preset durations adopted by the slow SQL statements of different business items during the determination are not necessarily the same, if the slow SQL statements of all the business items are tracked, the tracking cost is increased. Therefore, in the embodiment of the invention, the slow SQL statements for all the business items are not tracked, but the slow SQL statements conforming to the tracking rule are selected for tracking. Namely, the tracking cost is reduced, a third preset time length is set for all business items, and whether the slow SQL statement is the slow SQL statement to be tracked or not is judged according to the third preset time length. The second preset duration is used for judging whether each SQL statement in the corresponding business project is a slow SQL statement, and the third preset duration is used for judging whether the slow SQL statement corresponding to each business project is a slow SQL statement to be tracked.
It should be noted that, since the slow SQL statements are not necessarily identified identically for each business item, the same preset duration cannot be directly set for all business items to determine the slow SQL statements to be tracked, but the slow SQL statements corresponding to each business item are determined according to the second preset duration corresponding to each business item, and then whether the slow SQL statements are slow SQL statements to be tracked or not is determined according to the third preset duration corresponding to each business item. That is, only the slow SQL statement may be the slow SQL statement to be tracked, and if a certain SQL statement is not the slow SQL statement, but the execution duration is greater than or equal to the third preset duration, the certain SQL statement cannot be regarded as the slow SQL statement to be tracked.
For any SQL statement of any business item, if the execution duration of the SQL statement is greater than or equal to a second preset duration corresponding to the business item, it may be determined that the SQL statement is a slow SQL statement. Further, if the execution duration of the slow SQL statement is greater than or equal to a third preset duration corresponding to each business item, it may be determined that the slow SQL statement is a slow SQL statement to be tracked.
In the embodiment of the invention, whether each SQL statement corresponding to each business item belongs to the slow SQL statement or not can be determined through the second preset duration corresponding to each business item, and whether the slow SQL statement is the slow SQL statement to be tracked or not is judged through the third preset duration, so that the accuracy of the slow SQL statement to be tracked can be ensured, the tracking cost can be reduced, and the number of the slow SQL statements to be tracked can be reduced.
On the basis of the foregoing embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention determines, based on the SQL log file, a slow SQL statement to be tracked, and specifically includes:
converting the SQL log file into a standard log file in a json format;
and determining slow SQL sentences to be tracked based on the standard log file.
Optionally, in the embodiment of the present invention, after the SQL log file is obtained, the SQL log file may be converted into a standard log file according to a log specification and a specified log text field, and a specification format of the standard log file may be set according to needs, which is not specifically limited in the embodiment of the present invention. For example, the standard log file may be a json format log file.
The log body field of a standard log file can be represented by table 1:
TABLE 1 Log text field of standard Log File
Figure BDA0003165346480000101
Figure BDA0003165346480000111
The standard log file may be implemented by:
Figure BDA0003165346480000112
Figure BDA0003165346480000121
and then determining the slow SQL statement to be tracked according to the execution duration of the SQL statement contained in the standard log file. The slow SQL statement to be traced can be determined directly according to the execution duration of the SQL statement contained in the standard log file, or the slow SQL statement can be determined according to the execution duration of the SQL statement contained in the standard log file, and then whether the slow SQL statement is the slow SQL statement to be traced or not is judged. The specific manner of determining the slow SQL statement to be traced is not specifically limited in the embodiment of the present invention.
In the embodiment of the invention, the SQL log file is converted into the json format standard log file, so that the slow SQL statement to be tracked can be conveniently determined, the extraction speed of the characteristic information of the slow SQL statement to be tracked can be increased, and the tracking efficiency of the slow SQL statement to be tracked can be further improved.
On the basis of the foregoing embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention specifically determines feature information of a slow SQL statement to be tracked, and includes:
and transmitting the SQL log file to a distributed log system (kafka), and determining the characteristic information of the slow SQL statement to be tracked in a kafka message consumption mode, so that the extraction speed can be increased.
On the basis of the foregoing embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention determines the feature information of the slow SQL statement to be tracked, and then further includes:
if the characteristic information is judged to be not appeared for the first time, determining a target work order corresponding to the characteristic information, and judging whether the processing state of the target work order is finished or not;
and if the processing state of the target work order is finished, generating a work order to be processed corresponding to the SQL log file based on the work order generation system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed.
Optionally, in the embodiment of the present invention, if the feature information of the slow SQL statement to be tracked does not appear for the first time, that is, the feature information of the slow SQL statement to be tracked is already stored at present, it may be stated that a work order corresponding to the feature information has been generated before, and the work order is marked as the target work order. Judging whether the processing state of the target work order is finished, if the processing state of the target work order is finished, indicating that the previously generated slow SQL statement to be tracked is processed, so that the work order to be processed corresponding to the characteristic information needs to be generated again through a work order generation system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed.
In the embodiment of the invention, a processing mode when the characteristic information of the slow SQL statement to be tracked does not appear for the first time is provided, so that the tracking method is more complete and has a wider application range.
On the basis of the foregoing embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention further includes, after determining whether the processing state of the target work order is completed: and if the processing state of the target work order is not finished, storing the SQL log file and the characteristic information.
Optionally, in the embodiment of the present invention, if the feature information of the slow SQL statement to be tracked does not appear for the first time, that is, the feature information of the slow SQL statement to be tracked is already stored at present, it may be stated that a work order corresponding to the feature information has been generated before, and the work order is marked as the target work order. If the processing state of the target work order is not finished, namely the target work order is to be processed or is in processing, the SQL log file and the characteristic information can be stored, and the processing flow of the target work order is continuously tracked until the target work order is finished. When the working personnel processes the target work order, the stored SQL log file and the characteristic information can assist in processing the target work order, and the processing efficiency of the target work order is improved.
On the basis of the foregoing embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention specifically determines the feature information of the slow SQL statement to be tracked, and includes:
and extracting a statement template corresponding to the slow SQL statement to be tracked, and taking the statement template as the characteristic information.
Optionally, in the embodiment of the present invention, the SQL log file may be input to kafka, a statement template corresponding to the slow SQL statement to be tracked is extracted from the SQL log file by consuming a kafka message, and then the statement template is used as the feature information of the slow SQL statement to be tracked.
In the embodiment of the invention, the extracted statement template is used as the characteristic information of the slow SQL statement to be tracked, so that the characteristic information can be acquired more simply and conveniently.
On the basis of the foregoing embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention determines the feature information of the slow SQL statement to be tracked, and then further includes:
and encrypting the statement template to obtain an identification mark of the statement template, and if the identification mark is judged to appear for the first time, determining that the characteristic information appears for the first time.
Optionally, in the embodiment of the present invention, after determining the feature information of the slow SQL statement to be tracked, the statement template may be encrypted to obtain the identification mark of the statement template, and then, by determining whether the identification mark appears for the first time, the determination of whether the feature information appears for the first time is implemented. If the identification mark of the statement template appears for the first time, the characteristic information of the slow SQL statement to be tracked can be determined to appear for the first time.
In the embodiment of the invention, the MD5 encryption algorithm can be adopted to encrypt the statement template so as to simplify the complexity of encryption processing.
In the embodiment of the invention, the judgment of whether the characteristic information appears for the first time is realized by encrypting the extracted statement template and judging whether the identification mark appears for the first time, so that the judgment process of whether the characteristic information appears for the first time can be simplified.
On the basis of the foregoing embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention determines the feature information of the slow SQL statement to be tracked, and then further includes:
and if the characteristic information is judged to appear for the first time, sending a prompt of appearing the slow SQL statement to be tracked to the user terminal.
Optionally, if the feature information of the slow SQL statement to be tracked appears for the first time, a prompt for the appearance of the slow SQL statement to be tracked may be sent to the user terminal while the work order to be processed corresponding to the feature information is generated based on the work order generation system. The user terminal may be a terminal device held by a target person, and the target person may be a technician capable of processing the work order to be processed, or a leader capable of allocating the work order to be processed to the technician, for example, a person in charge of the business project, which is not specifically limited in the embodiment of the present invention. In the embodiment of the present invention, the pushing may be performed through an enterprise internal platform, and may also be performed through qq, wechat, or other manners, which is not specifically limited in this embodiment of the present invention. The hint page where the slow SQL statement to be traced appears may be as shown in fig. 4. In fig. 4, the SQL fingerprint is the feature information of the slow SQL statement to be tracked.
In the embodiment of the invention, when the characteristic information appears for the first time, the prompt is sent to the user terminal, so that the user can find the slow SQL sentence to be tracked in time and carry out subsequent processing, the user experience is improved, and the processing efficiency of the slow SQL sentence to be tracked can be improved.
On the basis of the foregoing embodiment, fig. 5 is a schematic view of a complete flow of the slow SQL statement tracking method provided in the embodiment of the present invention, and as shown in fig. 5, the method includes:
determining a business project;
acquiring a slow SQL log file of a business item;
separating characteristic information of the slow SQL statement to be tracked in a kafka message consumption mode;
judging whether the characteristic information appears for the first time, if so, generating a work order to be processed based on a work order generation system; if not, judging whether the existing target work order corresponding to the characteristic information is finished, and if the target work order is finished, continuing to generate the work order to be processed based on the work order generation system; and if the target work order is not finished, storing the slow SQL log file and the characteristic information so as to update the local data.
As shown in fig. 6, on the basis of the above embodiments, an embodiment of the present invention provides a slow SQL statement tracking apparatus, including: a slow SQL statement to be traced determining module 61 and a tracing module 62.
The slow SQL statement to be tracked determining module 61 is configured to determine a slow SQL statement to be tracked based on the SQL log file;
and the tracking module 62 is configured to determine feature information of the slow SQL statement to be tracked, generate a to-be-processed work order corresponding to the feature information based on a work order generation system if the feature information is judged to appear for the first time, track a processing flow of the to-be-processed work order, and determine a processing state of the to-be-processed work order.
On the basis of the foregoing embodiment, in the slow SQL statement tracking apparatus provided in the embodiment of the present invention, the SQL log file includes an SQL log file of each business item, and the SQL log file of each business item includes an SQL statement and an execution duration of the SQL statement; correspondingly, the slow SQL statement to be tracked determining module is specifically configured to:
for any SQL statement corresponding to any business project, if the execution duration of any SQL statement is judged to be greater than or equal to a first preset duration, determining that any SQL statement is the slow SQL statement to be tracked;
the first preset duration corresponds to the service items one to one.
On the basis of the foregoing embodiment, in the slow SQL statement tracking apparatus provided in the embodiment of the present invention, the to-be-tracked slow SQL statement determination module is further specifically configured to:
if the execution duration of any SQL statement is judged to be greater than or equal to a second preset duration, determining that any SQL statement is a slow SQL statement;
if the execution duration of the slow SQL statement is judged to be greater than or equal to a third preset duration, determining the slow SQL statement as the slow SQL statement to be tracked;
the second preset time length corresponds to the service items one by one, and the second preset time length corresponding to the service items is less than the first preset time length; and the third preset time length corresponds to each service project.
On the basis of the foregoing embodiment, the slow SQL statement tracking apparatus provided in the embodiment of the present invention further includes a first determining module, configured to:
if the characteristic information is judged to be not appeared for the first time, determining a target work order corresponding to the characteristic information, and judging whether the processing state of the target work order is finished or not;
the tracking module is further to: and if the processing state of the target work order is finished, generating a work order to be processed corresponding to the SQL log file based on the work order generation system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed.
On the basis of the foregoing embodiment, the slow SQL statement tracking apparatus provided in the embodiment of the present invention further includes a storage module, configured to:
and if the processing state of the target work order is not finished, storing the slow SQL log file and the characteristic information.
On the basis of the foregoing embodiment, in the slow SQL statement tracking apparatus provided in the embodiment of the present invention, the tracking module is specifically configured to:
and extracting a statement template corresponding to the slow SQL statement to be tracked, and taking the statement template as the characteristic information.
On the basis of the foregoing embodiment, in the slow SQL statement tracking apparatus provided in the embodiment of the present invention, the tracking module is further specifically configured to:
and encrypting the statement template to obtain an identification mark of the statement template, and if the identification mark is judged to appear for the first time, determining that the characteristic information appears for the first time.
On the basis of the foregoing embodiment, in the slow SQL statement tracking apparatus provided in the embodiment of the present invention, the to-be-tracked slow SQL statement determination module is further specifically configured to:
converting the SQL log file into a standard log file in a json format;
and determining the slow SQL statement to be traced based on the standard log file.
On the basis of the foregoing embodiment, the slow SQL statement tracking apparatus provided in the embodiment of the present invention further includes a sending module, configured to:
and if the characteristic information is judged to appear for the first time, sending a prompt of appearing the slow SQL statement to be tracked to the user terminal.
Specifically, the functions of the modules in the slow SQL statement tracking apparatus provided in the embodiment of the present invention correspond to the operation flows of the steps in the method embodiments one to one, and the implementation effect is also consistent.
Fig. 7 illustrates a physical structure diagram of an electronic device, and as shown in fig. 7, the electronic device may include: a processor (processor)710, a communication Interface (Communications Interface)720, a memory (memory)730, and a communication bus 740, wherein the processor 710, the communication Interface 720, and the memory 730 communicate with each other via the communication bus 740. The processor 710 may call logic instructions in the memory 730 to execute the slow SQL statement tracking method provided by the above embodiments, which includes: determining a slow SQL statement to be traced based on the SQL log file; and determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a to-be-processed work order corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the to-be-processed work order, and determining the processing state of the to-be-processed work order.
In addition, the logic instructions in the memory 730 can be implemented in the form of software functional units and stored in a computer readable storage medium when the software functional units are sold or used as independent products. Based on such understanding, the technical solution of the present invention may be embodied in the form of a software product, which is stored in a storage medium and includes instructions for causing a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present invention. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, and other various media capable of storing program codes.
In another aspect, the present invention also provides a computer program product, which includes a computer program stored on a non-transitory computer-readable storage medium, the computer program including program instructions, when the program instructions are executed by a computer, the computer being capable of executing the slow SQL statement tracking method provided by the above embodiments, the method including: determining a slow SQL statement to be traced based on the SQL log file; and determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a to-be-processed work order corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the to-be-processed work order, and determining the processing state of the to-be-processed work order.
In yet another aspect, the present invention further provides a non-transitory computer-readable storage medium, on which a computer program is stored, the computer program being implemented by a processor to perform the slow SQL statement tracking method provided in the foregoing embodiments, the method including: determining a slow SQL statement to be traced based on the SQL log file; and determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a to-be-processed work order corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the to-be-processed work order, and determining the processing state of the to-be-processed work order.
The above-described embodiments of the apparatus are merely illustrative, and the units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of the present embodiment. One of ordinary skill in the art can understand and implement it without inventive effort.
Through the above description of the embodiments, those skilled in the art will clearly understand that each embodiment can be implemented by software plus a necessary general hardware platform, and certainly can also be implemented by hardware. With this understanding in mind, the above-described technical solutions may be embodied in the form of a software product, which can be stored in a computer-readable storage medium such as ROM/RAM, magnetic disk, optical disk, etc., and includes instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the methods described in the embodiments or some parts of the embodiments.
Finally, it should be noted that: the above examples are only intended to illustrate the technical solution of the present invention, but not to limit it; although the present invention 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 solutions described in the foregoing embodiments may still be modified, or some technical features may be equivalently replaced; and such modifications or substitutions do not depart from the spirit and scope of the corresponding technical solutions of the embodiments of the present invention.

Claims (10)

1. A slow SQL statement tracking method is characterized by comprising the following steps:
determining a slow SQL statement to be traced based on the SQL log file;
and determining the characteristic information of the slow SQL statement to be tracked, if the characteristic information is judged to appear for the first time, generating a to-be-processed work order corresponding to the characteristic information based on a work order generation system, tracking the processing flow of the to-be-processed work order, and determining the processing state of the to-be-processed work order.
2. The slow SQL statement tracking method according to claim 1, wherein the SQL log file includes an SQL log file of each business item, and the SQL log file of each business item includes an SQL statement and an execution duration of the SQL statement;
correspondingly, the determining a slow SQL statement to be traced based on the SQL log file specifically includes:
for any SQL statement corresponding to any business project, if the execution duration of any SQL statement is judged to be greater than or equal to a first preset duration, determining that any SQL statement is the slow SQL statement to be tracked;
the first preset duration corresponds to the service items one to one.
3. The slow SQL statement tracking method according to claim 2, wherein the determining the slow SQL statement to be tracked based on the SQL log file further comprises:
if the execution duration of any SQL statement is judged to be greater than or equal to a second preset duration, determining that any SQL statement is a slow SQL statement;
if the execution duration of the slow SQL statement is judged to be greater than or equal to a third preset duration, determining the slow SQL statement as the slow SQL statement to be tracked;
the second preset time length corresponds to the service items one by one, and the second preset time length corresponding to the service items is less than the first preset time length; and the third preset time length corresponds to each service project.
4. The slow SQL statement tracking method according to claim 1, wherein the determining the feature information of the slow SQL statement to be tracked further comprises:
if the characteristic information is judged to be not appeared for the first time, determining a target work order corresponding to the characteristic information, and judging whether the processing state of the target work order is finished or not;
and if the processing state of the target work order is finished, generating a work order to be processed corresponding to the SQL log file based on the work order generation system, tracking the processing flow of the work order to be processed, and determining the processing state of the work order to be processed.
5. The slow SQL statement tracking method according to claim 4, wherein the determining whether the processing state of the target work order is complete further comprises:
and if the processing state of the target work order is not finished, storing the SQL log file and the characteristic information.
6. The slow SQL statement tracking method according to claim 1, wherein the determining the feature information of the slow SQL statement to be tracked specifically includes:
and extracting a statement template corresponding to the slow SQL statement to be tracked, and taking the statement template as the characteristic information.
7. The slow SQL statement tracking method according to claim 6, wherein the determining the feature information of the slow SQL statement to be tracked further comprises:
and encrypting the statement template to obtain an identification mark of the statement template, and if the identification mark is judged to appear for the first time, determining that the characteristic information appears for the first time.
8. The slow SQL statement tracking method according to any one of claims 1 to 7, wherein the determining the feature information of the slow SQL statement to be tracked further comprises:
and if the characteristic information is judged to appear for the first time, sending a prompt of appearing the slow SQL statement to be tracked to the user terminal.
9. A computer program product comprising a computer program or instructions which, when executed by a processor, carry out the steps of the slow SQL statement tracking method of any of claims 1-8.
10. A non-transitory computer readable storage medium having stored thereon a computer program, wherein the computer program when executed by a processor implements the steps of the slow SQL statement tracking method according to any of claims 1 to 8.
CN202110802805.8A 2021-07-15 2021-07-15 Slow SQL statement tracking method and device, electronic equipment and storage medium Pending CN113626281A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202110802805.8A CN113626281A (en) 2021-07-15 2021-07-15 Slow SQL statement tracking method and device, electronic equipment and storage medium

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202110802805.8A CN113626281A (en) 2021-07-15 2021-07-15 Slow SQL statement tracking method and device, electronic equipment and storage medium

Publications (1)

Publication Number Publication Date
CN113626281A true CN113626281A (en) 2021-11-09

Family

ID=78379882

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202110802805.8A Pending CN113626281A (en) 2021-07-15 2021-07-15 Slow SQL statement tracking method and device, electronic equipment and storage medium

Country Status (1)

Country Link
CN (1) CN113626281A (en)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN114880351A (en) * 2022-05-31 2022-08-09 中国电信股份有限公司 Slow query statement identification method and device, storage medium and electronic equipment

Citations (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN112506951A (en) * 2020-12-07 2021-03-16 海南车智易通信息技术有限公司 Processing method, server, computing device and system for database slow query log

Patent Citations (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN112506951A (en) * 2020-12-07 2021-03-16 海南车智易通信息技术有限公司 Processing method, server, computing device and system for database slow query log

Non-Patent Citations (1)

* Cited by examiner, † Cited by third party
Title
CHENKUN1998: "【See】自动化SQL审核 SQL优化管理平台", 《HTTPS://BLOG.51CTO.COM/U_5053795/2175628L》, 15 September 2018 (2018-09-15), pages 1 - 12 *

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN114880351A (en) * 2022-05-31 2022-08-09 中国电信股份有限公司 Slow query statement identification method and device, storage medium and electronic equipment
CN114880351B (en) * 2022-05-31 2024-02-06 中国电信股份有限公司 Recognition method and device of slow query statement, storage medium and electronic equipment

Similar Documents

Publication Publication Date Title
AU2017329098B2 (en) Method and device for processing question clustering in automatic question and answering system
WO2019062081A1 (en) Salesman profile formation method, electronic device and computer readable storage medium
CN111435367B (en) Knowledge graph construction method, system, equipment and storage medium
CN104008087A (en) Automatic typesetting method and system special for copywriter with standard format
CN111445319A (en) Voucher generation method and device, computer equipment and storage medium
EP3961433A2 (en) Data annotation method and apparatus, electronic device and storage medium
CN113626281A (en) Slow SQL statement tracking method and device, electronic equipment and storage medium
CN117171296A (en) Information acquisition method and device and electronic equipment
CN115329759B (en) Information processing method, device, equipment and storage medium
CN108985759B (en) Address generating method, system, equipment and storage medium for cryptocurrency
CN115905322A (en) Service processing method and device, electronic equipment and storage medium
CN114996758A (en) Data desensitization method and device and electronic equipment
CN115544010A (en) Mapping relation determining method and device, electronic equipment and storage medium
CN115422270A (en) Information processing method and device
CN110727677B (en) Method and device for tracing blood relationship of table in data warehouse
US11562126B2 (en) Coaching system and coaching method
CN110851446B (en) Data table generation method and device, computer equipment and storage medium
CN114118937A (en) Information recommendation method and device based on task, electronic equipment and storage medium
CN114218261A (en) Data query method and device, storage medium and electronic equipment
CN113127058A (en) Data annotation method, related device and computer program product
CN112328668A (en) Data visualization implementation method, device, equipment and storage medium
CN116861463B (en) Processing method for SaaS transformation of general information system
CN115470229B (en) Data table processing method and device, electronic equipment and storage medium
CN111858832B (en) Dialogue method, dialogue device, electronic equipment and storage medium
CN108614871B (en) Data query and result generation method, device, equipment and storage medium

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