Slow SQL statement tracking method, device, electronic device and storage medium

Abstract

The present invention provides a slow SQL statement tracking method, device, electronic device and storage medium. First, based on the SQL log file, the slow SQL statement to be tracked is determined; then the characteristic information of the slow SQL statement to be tracked is determined. If it is determined that the characteristic information appears for the first time, a to-be-processed work order corresponding to the characteristic information is generated based on a work order generation system, and the processing flow of the to-be-processed work order is tracked to determine the processing status of the to-be-processed work order. The tracking of the slow SQL statement to be tracked can be realized, and the processing status of the slow SQL statement to be tracked can be determined in real time. After the slow SQL statement to be tracked is discovered, the resolution progress can be followed up in time, the processing efficiency of the slow SQL statement to be tracked can be improved, and the risk of system failure can be reduced.

Description

Technical Field

[0001] The present invention relates to the field of computer software technology, and in particular to a slow SQL statement tracking method, device, electronic device and storage medium. Background Art

[0002] Structured Query Language (SQL) is a database query and programming language used to access data as well as query, update and manage relational database systems. It is a language for operating database systems.

[0003] In software programming, slow SQL phenomena may occur due to the programming ability of developers and the frequent iteration of business. The slow SQL phenomenon refers to the situation that after an application has been running for a period of time, some SQL statements frequently perform I / O operations on the database, causing the execution of these SQL statements to occupy the CPU for a long time. The SQL statements corresponding to the slow SQL phenomenon are slow SQL statements. For an application, a small number of slow SQL statements may not affect the normal operation of the business, but as the number of slow SQL statements increases to a certain extent, it will eventually affect the execution of other business functions in the application, and even cause system failures, resulting in system unavailability. Therefore, tracking slow SQL statements plays a vital role in improving the processing efficiency of slow SQL statements and thus reducing the possibility of system failures. Summary of the invention

[0004] The present invention provides a slow SQL statement tracking method, device, electronic device and storage medium, which are used to solve the defects in the prior art.

[0005] The present invention provides a slow SQL statement tracking method, comprising:

[0006] Based on the SQL log file, determine the slow SQL statements to be traced;

[0007] Determine the characteristic information of the slow SQL statement to be tracked. If it is determined that the characteristic information appears for the first time, generate a pending work order corresponding to the characteristic information based on the work order generation system, track the processing flow of the pending work order, and determine the processing status of the pending work order.

[0008] According to a slow SQL statement tracking method provided by the present invention, the SQL log file includes an SQL log file of each business project, and the SQL log file of each business project includes an SQL statement and the execution time of the SQL statement;

[0009] Accordingly, determining the slow SQL statement to be traced based on the SQL log file specifically includes:

[0010] For any SQL statement corresponding to any business item, if it is determined that the execution time of any SQL statement is greater than or equal to a first preset time, then the any SQL statement is determined to be the slow SQL statement to be tracked;

[0011] The first preset duration corresponds to the service item one by one.

[0012] According to a slow SQL statement tracking method provided by the present invention, the determining of the slow SQL statement to be tracked based on the SQL log file further specifically includes:

[0013] If it is determined that the execution time of any SQL statement is greater than or equal to the second preset time, then the any SQL statement is determined to be a slow SQL statement;

[0014] If it is determined that the execution time of the slow SQL statement is greater than or equal to the third preset time, the slow SQL statement is determined to be the slow SQL statement to be tracked;

[0015] The second preset time length corresponds to each of the service items one by one, and the second preset time length corresponding to each of the service items is shorter than the first preset time length; and the third preset time length corresponds to each of the service items.

[0016] According to a slow SQL statement tracking method provided by the present invention, the determining of feature information of the slow SQL statement to be tracked further includes:

[0017] If it is determined that the characteristic information does not appear for the first time, determining the target work order corresponding to the characteristic information, and determining whether the processing status of the target work order is completed;

[0018] If the processing status of the target work order is completed, a pending work order corresponding to the SQL log file is generated based on the work order generation system, and the processing flow of the pending work order is tracked to determine the processing status of the pending work order.

[0019] According to a slow SQL statement tracking method provided by the present invention, the step of determining whether the processing status of the target work order is completed further includes:

[0020] If the processing status of the target work order is unfinished, the SQL log file and the characteristic information are stored.

[0021] According to a slow SQL statement tracking method provided by the present invention, the determining of feature information of the slow SQL statement to be tracked specifically includes:

[0022] A statement template corresponding to the slow SQL statement to be traced is extracted, and the statement template is used as the feature information.

[0023] According to a slow SQL statement tracking method provided by the present invention, the determining of feature information of the slow SQL statement to be tracked further includes:

[0024] The sentence template is encrypted to obtain an identification mark of the sentence template. If it is determined that the identification mark appears for the first time, it is determined that the characteristic information appears for the first time.

[0025] According to a slow SQL statement tracking method provided by the present invention, the determining of feature information of the slow SQL statement to be tracked further includes:

[0026] If it is determined that the characteristic information appears for the first time, a prompt is sent to the user terminal that a slow SQL statement to be tracked is found.

[0027] The present invention also provides a computer program product, including a computer program or instructions, which implements the steps of any of the above-mentioned slow SQL statement tracking methods when executed by a processor.

[0028] The present invention also provides a non-transitory computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned slow SQL statement tracking methods are implemented.

[0029] The slow SQL statement tracking method, device, electronic device and storage medium provided by the present invention first determine the slow SQL statement to be tracked based on the SQL log file; then determine the characteristic information of the slow SQL statement to be tracked, and if it is determined that the characteristic information appears for the first time, generate a to-be-processed work order corresponding to the characteristic information based on the work order generation system, and track the processing flow of the to-be-processed work order to determine the processing status of the to-be-processed work order. The tracking of the slow SQL statement to be tracked can be achieved, and the processing status of the slow SQL statement to be tracked can be determined in real time, so that the progress of the solution can be followed up in time after the slow SQL statement to be tracked is discovered, and the processing efficiency of the slow SQL statement to be tracked is improved, thereby reducing the risk of system failure. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0031] Figure 1 It is a flowchart of the slow SQL statement tracking method provided by the present invention;

[0032] Figure 2 It is a schematic diagram showing the processing status of the work order to be processed provided by the present invention;

[0033] Figure 3 It is a display schematic diagram when the processing status of the to-be-processed work order provided by the present invention is completed;

[0034] Figure 4 This is a schematic diagram of a prompt page for slow SQL statements provided by the present invention;

[0035] Figure 5 It is a complete flow chart of the slow SQL statement tracing method provided by the present invention;

[0036] Figure 6 It is a structural schematic diagram of the slow SQL statement tracking device provided by the present invention;

[0037] Figure 7 It is a structural schematic diagram of the electronic device provided by the present invention. DETAILED DESCRIPTION

[0038] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with the drawings of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0039] Since there is no related technology for tracking slow SQL statements in the prior art, the progress of solving slow SQL statements cannot be followed up in time after they are found, the processing efficiency of slow SQL statements is not ideal, and the risk of system failure is increased. Therefore, a method for tracking slow SQL statements is provided in an embodiment of the present invention.

[0040] Figure 1 A flowchart of a slow SQL statement tracking method provided in an embodiment of the present invention is shown in FIG. Figure 1 As shown, the method includes:

[0041] S1, based on the SQL log file, determine the slow SQL statement to be traced;

[0042] S2, determining the characteristic information of the slow SQL statement to be tracked. If it is determined that the characteristic information appears for the first time, based on the work order generation system, generating a work order to be processed corresponding to the characteristic information, tracking the processing flow of the work order to be processed, and determining the processing status of the work order to be processed.

[0043] Optionally, the slow SQL statement tracking method provided in the embodiment of the present invention is executed by a server, which can be a local server or a cloud server. The local server can specifically be a computer, a tablet computer, a smart phone, etc., which is not specifically limited in the embodiment of the present invention.

[0044] First, execute step S1 to obtain the SQL log file. Among them, the SQL log file can be the SQL log file of each business project. Business projects can be projects that implement specific business functions, such as target house search, house image search and other projects. The number of business projects can be several, that is, the SQL log files of one or more business projects can be obtained. The SQL log file can usually contain several SQL statements and the execution time of each SQL statement. Among them, the unit of the execution time of each SQL statement is ms. When the SQL statement contained in the SQL log file is a slow SQL statement, the SQL log file is a slow SQL log file. The slow SQL log file may include: application ID, instance domain name, instance port, database, Trace ID, execution time, slow SQL statement occurrence time, whether the slow SQL statement is repeated, and the specific form of the slow SQL statement that appears.

[0045] After obtaining the SQL log file, the slow SQL statements to be tracked can be determined based on the SQL log file. The slow SQL statements to be tracked refer to slow SQL statements that need to be tracked to determine whether they have been processed and the current processing flow, processing status and other information. In other words, when determining the slow SQL statements to be tracked, the slow SQL statements to be tracked can be directly determined based on the execution time of the SQL statements contained in the SQL log file, or the slow SQL statements can be first determined based on the execution time of the SQL statements contained in the SQL log file, and then it is determined whether the slow SQL statements are slow SQL statements to be tracked. The specific method for determining the slow SQL statements to be tracked is not specifically limited in the embodiment of the present invention.

[0046] Then, step S2 is executed to determine the characteristic information of the slow SQL statement to be tracked. The characteristic information can be extracted from the slow SQL statement to be tracked, and can be the fingerprint information, template information or identification information of the slow SQL statement to be tracked, and is used to characterize the common information of a class of slow SQL statements to which the slow SQL statement to be tracked belongs. In the embodiment of the present invention, the characteristic information can be represented by the statement template of the slow SQL statement to be tracked. For example, if the slow SQL statement 1 is "30 yuan ≤ price ≤ 100 yuan", it means to query the target object with a price between 30 yuan and 100 yuan, then the characteristic information of the slow SQL statement 1 can be the statement template of the slow SQL statement 1, that is, "A ≤ price ≤ B"; if the SQL statement 2 is "101 yuan ≤ price ≤ 200 yuan", it means to query the target object with a 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, both of which are "A ≤ price ≤ B". That is, the characteristic information of the SQL statement can characterize a class of SQL statements, which is a fixed query format obtained by ignoring the data information that can be changed.

[0047] After determining the characteristic information of the slow SQL statement to be tracked, it is determined whether the characteristic information of the slow SQL statement to be tracked appears for the first time. If it appears for the first time, the SQL log file and the characteristic information of the slow SQL statement to be tracked can be stored, and a work order to be processed corresponding to the characteristic information of the slow SQL statement to be tracked is generated through a work order generation system, and the processing flow of the work order to be processed is tracked to determine the processing status of the work order to be processed. The work order generation system is not specifically limited in the embodiment of the present invention.

[0048] In an embodiment of the present invention, the slow SQL log file to be tracked can be input into the work order generation system, and the work order generation system generates a pending work order corresponding to the characteristic information of the slow SQL statement to be tracked according to the slow SQL log file to be tracked, and can send the generated pending work order to the target person so that the target person can process the pending work order. Among them, the target person can be a technician who can process the pending work order, or a leader who can assign the pending work order to the technician, such as the person in charge of the business project, etc., which is not specifically limited in the embodiment of the present invention. The person in charge can also re-designate the person who handles the pending work order in the work order generation system.

[0049] As the target personnel continuously process the pending work orders at various stages and feed back the processing results of each stage to the work order generation system, the work order generation system can track the processing flow of the pending work orders in real time and determine the processing status of the pending work orders. The processing status of the pending work orders can represent the processing status of the slow SQL statements to be tracked, which can include pending, processing, and completed. Figure 2Schematic diagram showing the processing status of a work order to be processed provided in an embodiment of the present invention. Figure 2 There are 4 pending work orders in total, each of which has an ID. Figure 2 The processing status of the 4 pending work orders is shown as unprocessed.

[0050] Figure 3 This is a schematic diagram of displaying the processing status of a pending work order provided in an embodiment of the present invention when it is completed. Figure 3 As shown in , it may include: the person in charge, application ID, instance domain name, instance port, database, Trace ID, execution time, slow SQL statement occurrence time, whether the slow SQL statement appears repeatedly, and the specific form of the slow SQL statement.

[0051] The slow SQL statement tracking method provided in the embodiment of the present invention first determines the slow SQL statement to be tracked based on the SQL log file; then determines the characteristic information of the slow SQL statement to be tracked, and if it is determined that the characteristic information appears for the first time, generates a to-be-processed work order corresponding to the characteristic information based on the work order generation system, and tracks the processing flow of the to-be-processed work order to determine the processing status of the to-be-processed work order. The tracking of the slow SQL statement to be tracked can be realized, and the processing status of the slow SQL statement to be tracked can be determined in real time, so that the progress of the solution can be followed up in time after the slow SQL statement to be tracked is discovered, thereby improving the processing efficiency of the slow SQL statement to be tracked, and thus reducing the risk of system failure.

[0052] 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 the SQL log file of each business project, and the SQL log file of each business project includes the SQL statement and the execution time of the SQL statement;

[0053] Accordingly, determining the slow SQL statement to be tracked based on the SQL log file specifically includes:

[0054] For any SQL statement corresponding to any business item, if it is determined that the execution time of any SQL statement is greater than or equal to a first preset time, then the any SQL statement is determined to be the slow SQL statement to be tracked;

[0055] The first preset duration corresponds to the service item one by one.

[0056] Specifically, in an embodiment of the present invention, the SQL log file may include the SQL log files of several business projects, and the SQL log file of each business project may include several SQL statements and the execution time of each SQL statement. Therefore, when determining the slow SQL statement to be tracked, a first preset time can be set for each business project, and the business project corresponds to the first preset time one by one. Through the first preset time corresponding to each business project, it is determined whether each SQL statement corresponding to each business project belongs to the slow SQL statement to be tracked.

[0057] For any SQL statement corresponding to any business project, if the execution time of any SQL statement is greater than or equal to the first preset time, then it can be determined that any SQL statement is a slow SQL statement to be tracked. Among them, the specific value of the first preset time can be determined according to the specific needs of the corresponding business project. For example, the first preset time corresponding to business project A can be 500ms, the first preset time corresponding to business project B can be 800ms, the first preset time corresponding to business project C can be 1s, the first preset time corresponding to business project D can be 1.2ms, etc.

[0058] In an embodiment of the present invention, by setting corresponding first preset time lengths for different business items, slow SQL statements to be tracked corresponding to different business items can be determined, which can make the determined slow SQL statements to be tracked more targeted and improve the accuracy of the slow SQL statements to be tracked.

[0059] On the basis of the above embodiment, the slow SQL statement tracing method provided in the embodiment of the present invention, wherein the determining of the slow SQL statement to be traced based on the SQL log file further specifically includes:

[0060] If it is determined that the execution time of any SQL statement is greater than or equal to the second preset time, then the any SQL statement is determined to be a slow SQL statement;

[0061] If it is determined that the execution time of the slow SQL statement is greater than or equal to the third preset time, the slow SQL statement is determined to be the slow SQL statement to be tracked;

[0062] The second preset time length corresponds to each of the service items one by one, and the second preset time length corresponding to each of the service items is shorter than the first preset time length; and the third preset time length corresponds to each of the service items.

[0063] Specifically, in an embodiment of the present invention, when determining the slow SQL statement to be tracked, a second preset duration can be set for each business project, and the business project corresponds to the second preset duration one by one. Through the second preset duration corresponding to each business project, it is determined whether each SQL statement corresponding to each business project belongs to a slow SQL statement. After determining the slow SQL statement, since the second preset duration used by the slow SQL statements of different business projects is not necessarily the same when judging, if the slow SQL statements of all business projects are tracked, the tracking cost will be increased. For this reason, in an embodiment of the present invention, the slow SQL statements for all business projects are not tracked, but the slow SQL statements that meet the tracking rules are selected for tracking. That is, in order to reduce the tracking cost, a third preset duration is set for all business projects, and the third preset duration is used to determine whether the slow SQL statement is a slow SQL statement to be tracked. Among them, the role of the second preset duration is to determine whether each SQL statement in the corresponding business project is a slow SQL statement, and the role of the third preset duration is to determine whether the slow SQL statement corresponding to each business project is a slow SQL statement to be tracked.

[0064] It should be noted that since the identification of slow SQL statements by each business project may not be the same, the same preset duration cannot be directly set for all business projects to determine the slow SQL statements to be tracked. Instead, the slow SQL statements corresponding to each business project must be determined by the second preset duration corresponding to each business project, and then the third preset duration corresponding to each business project must be used to determine whether the slow SQL statements are slow SQL statements to be tracked and whether they need to be tracked. In other words, only slow SQL statements can be slow SQL statements to be tracked. If a SQL statement is not a slow SQL statement, but its execution time is greater than or equal to the third preset duration, it cannot be used as a slow SQL statement to be tracked.

[0065] For any SQL statement of any business project, if the execution time of any SQL statement is greater than or equal to the second preset time corresponding to any business project, then the SQL statement can be determined to be a slow SQL statement. Further, if the execution time of the slow SQL statement is greater than or equal to the third preset time corresponding to each business project, then the slow SQL statement can be determined to be a slow SQL statement to be tracked.

[0066] In the embodiment of the present invention, the second preset time corresponding to each business item can be used to determine whether each SQL statement corresponding to each business item is a slow SQL statement, and then the third preset time can be used to determine whether the slow SQL statement is a slow SQL statement to be tracked. While ensuring the accuracy of the slow SQL statements to be tracked, the tracking cost can be reduced and the number of slow SQL statements to be tracked can be reduced.

[0067] On the basis of the above embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention, wherein the slow SQL statement to be tracked is determined based on the SQL log file, specifically includes:

[0068] Convert the SQL log file into a standard log file in json format;

[0069] Based on the standard log file, a slow SQL statement to be traced is determined.

[0070] Optionally, in the embodiment of the present invention, after obtaining the SQL log file, the SQL log file can be converted into a standard log file according to the log specification and the specified log text field. The standard format of the standard log file can be set as needed, which is not specifically limited in the embodiment of the present invention. For example, the standard log file can be a log file in json format.

[0071] The log text fields of a standard log file can be represented by Table 1:

[0072] Table 1 Log text fields of standard log files

[0073]

[0074]

[0075] The standard log file can be implemented by the following code:

[0076]

[0077]

[0078] Then, the slow SQL statements to be tracked are determined by the execution time of the SQL statements contained in the standard log file. The slow SQL statements to be tracked can be determined directly according to the execution time of the SQL statements contained in the standard log file, or the slow SQL statements can be first determined according to the execution time of the SQL statements contained in the standard log file, and then it is determined whether the slow SQL statements are the slow SQL statements to be tracked. The specific method for determining the slow SQL statements to be tracked is not specifically limited in the embodiment of the present invention.

[0079] In an embodiment of the present invention, converting an SQL log file into a standard log file in json format can facilitate the determination of the slow SQL statements to be tracked, improve the speed of extracting feature information of the slow SQL statements to be tracked, and further improve the tracking efficiency of the slow SQL statements to be tracked.

[0080] On the basis of the above-mentioned embodiment, the method for tracing slow SQL statements provided in the embodiment of the present invention, wherein determining the characteristic information of the slow SQL statement to be traced specifically includes:

[0081] The SQL log file is transferred to a distributed log system (kafka), and the characteristic information of the slow SQL statement to be tracked is determined by consuming the kafka message, which can speed up the extraction speed.

[0082] On the basis of the above embodiment, the method for tracing slow SQL statements provided in the embodiment of the present invention, wherein the feature information of the slow SQL statement to be traced is determined, further comprising:

[0083] If it is determined that the characteristic information does not appear for the first time, determining the target work order corresponding to the characteristic information, and determining whether the processing status of the target work order is completed;

[0084] If the processing status of the target work order is completed, a pending work order corresponding to the SQL log file is generated based on the work order generation system, and the processing flow of the pending work order is tracked to determine the processing status of the pending work order.

[0085] Optionally, in an embodiment of the present invention, if the characteristic information of the slow SQL statement to be tracked does not appear for the first time, that is, the characteristic information of the slow SQL statement to be tracked has been stored, it can be said that a work order corresponding to the characteristic information has been generated before, and it is marked as a target work order. It is determined whether the processing status of the target work order is completed. If the processing status of the target work order is completed, it means that the previously generated slow SQL statement to be tracked has been processed, so it is necessary to generate a work order to be processed corresponding to the characteristic information again through the work order generation system, and track the processing flow of the work order to be processed to determine the processing status of the work order to be processed.

[0086] In the embodiment of the present invention, a processing method is provided when the characteristic information of the slow SQL statement to be tracked does not appear for the first time, which can make the tracking method more complete and have a wider scope of application.

[0087] Based on the above embodiment, the slow SQL statement tracking method provided in the embodiment of the present invention, the step of judging whether the processing status of the target work order is completed, further includes: if the processing status of the target work order is not completed, storing the SQL log file and the characteristic information.

[0088] Optionally, in an embodiment of the present invention, if the characteristic information of the slow SQL statement to be tracked does not appear for the first time, that is, the characteristic information of the slow SQL statement to be tracked has been stored, it can be said that a work order corresponding to the characteristic information has been generated before, and it is marked as a target work order. If the processing status of the target work order is unfinished, that is, pending or being processed, the SQL log file and characteristic information can be stored, and the processing flow of the target work order can continue to be tracked until the target work order is completed. When the staff processes the target work order, the stored SQL log file and characteristic information can be used to assist in processing the target work order, thereby improving the processing efficiency of the target work order.

[0089] On the basis of the above embodiment, the slow SQL statement tracing method provided in the embodiment of the present invention, the determining of the characteristic information of the slow SQL statement to be traced specifically includes:

[0090] A statement template corresponding to the slow SQL statement to be traced is extracted, and the statement template is used as the feature information.

[0091] Optionally, in an embodiment of the present invention, the SQL log file can be input into Kafka, and the statement template corresponding to the slow SQL statement to be tracked can be extracted from the SQL log file by consuming Kafka messages, and then the statement template can be used as the feature information of the slow SQL statement to be tracked.

[0092] In the embodiment of the present invention, by using the extracted statement template as the characteristic information of the slow SQL statement to be traced, it can be made simpler and more convenient to obtain the characteristic information.

[0093] On the basis of the above embodiment, the method for tracing slow SQL statements provided in the embodiment of the present invention, wherein the feature information of the slow SQL statement to be traced is determined, further comprising:

[0094] The sentence template is encrypted to obtain an identification mark of the sentence template. If it is determined that the identification mark appears for the first time, it is determined that the characteristic information appears for the first time.

[0095] Optionally, in the embodiment of the present invention, after determining the characteristic information of the slow SQL statement to be traced, the statement template may be encrypted to obtain an identification mark of the statement template, and then the identification mark may be judged to determine whether the characteristic information is the first appearance by judging whether the identification mark is the first appearance. If the identification mark of the statement template is the first appearance, it can be determined that the characteristic information of the slow SQL statement to be traced is the first appearance.

[0096] In the embodiment of the present invention, the MD5 encryption algorithm may be used to encrypt the statement template to simplify the complexity of the encryption process.

[0097] In the embodiment of the present invention, by encrypting the extracted sentence template and then judging whether the identification mark appears for the first time, whether the characteristic information appears for the first time is judged, which can simplify the judging process of whether the characteristic information appears for the first time.

[0098] On the basis of the above embodiment, the method for tracing slow SQL statements provided in the embodiment of the present invention, wherein the feature information of the slow SQL statement to be traced is determined, further comprising:

[0099] If it is determined that the characteristic information appears for the first time, a prompt indicating that the slow SQL statement to be tracked appears is sent to the user terminal.

[0100] Optionally, if the characteristic information of the slow SQL statement to be tracked appears for the first time, while generating a pending work order corresponding to the characteristic information based on the work order generation system, a prompt of the appearance of the slow SQL statement to be tracked can also be sent to the user terminal. The user terminal can be a terminal device held by the target person, and the target person can be a technician who can handle the pending work order, or a leader who can assign the pending work order to the technician, such as the person in charge of the business project, etc., which is not specifically limited in the embodiment of the present invention. In the embodiment of the present invention, it can be pushed through the internal platform of the enterprise, and can also be pushed through QQ, WeChat or other methods, which is not specifically limited in the embodiment of the present invention. The prompt page for the appearance of the slow SQL statement to be tracked can be as follows Figure 4 shown. Figure 4 The SQL fingerprint is the feature information of the slow SQL statement to be tracked.

[0101] In the embodiment of the present invention, when the characteristic information appears for the first time, a prompt is sent to the user terminal, so that the user can promptly find the slow SQL statement to be tracked and perform subsequent processing, which not only improves the user experience, but also improves the processing efficiency of the slow SQL statement to be tracked.

[0102] Based on the above embodiments, Figure 5 The complete flow chart of the slow SQL statement tracking method provided in the embodiment of the present invention is as follows: Figure 5 As shown, the method includes:

[0103] Identify business projects;

[0104] Get the slow SQL log file of the business project;

[0105] Separate the characteristic information of the slow SQL statements to be tracked by consuming Kafka messages;

[0106] Determine whether the feature information appears for the first time. If so, generate a pending work order based on the work order generation system. If not, determine whether the target work order corresponding to the existing feature information is completed. If the target work order is completed, continue to generate a pending work order based on the work order generation system. If the target work order is not completed, store the slow SQL log file and feature information to update local data.

[0107] like Figure 6 As shown, based on the above embodiments, a slow SQL statement tracing device is provided in an embodiment of the present invention, including: a slow SQL statement to be traced determining module 61 and a tracing module 62.

[0108] A slow SQL statement to be traced determining module 61 is used to determine the slow SQL statement to be traced based on the SQL log file;

[0109] The tracking module 62 is used to determine the characteristic information of the slow SQL statement to be tracked. If it is determined that the characteristic information appears for the first time, a work order to be processed corresponding to the characteristic information is generated based on the work order generation system, and the processing flow of the work order to be processed is tracked to determine the processing status of the work order to be processed.

[0110] On the basis of the above embodiment, in the slow SQL statement tracking device provided in the embodiment of the present invention, the SQL log file includes the SQL log file of each business project, and the SQL log file of each business project includes the SQL statement and the execution time of the SQL statement; accordingly, the slow SQL statement determination module to be tracked is specifically used for:

[0111] For any SQL statement corresponding to any business item, if it is determined that the execution time of any SQL statement is greater than or equal to a first preset time, then the any SQL statement is determined to be the slow SQL statement to be tracked;

[0112] The first preset duration corresponds to the service item one by one.

[0113] On the basis of the above-mentioned embodiment, in the slow SQL statement tracing device provided in the embodiment of the present invention, the slow SQL statement to be traced determining module is further specifically used for:

[0114] If it is determined that the execution time of any SQL statement is greater than or equal to the second preset time, then the any SQL statement is determined to be a slow SQL statement;

[0115] If it is determined that the execution time of the slow SQL statement is greater than or equal to the third preset time, the slow SQL statement is determined to be the slow SQL statement to be tracked;

[0116] The second preset time length corresponds to each of the service items one by one, and the second preset time length corresponding to each of the service items is shorter than the first preset time length; and the third preset time length corresponds to each of the service items.

[0117] On the basis of the above embodiment, the slow SQL statement tracking device provided in the embodiment of the present invention further includes a first judgment module, which is used to:

[0118] If it is determined that the characteristic information does not appear for the first time, determining the target work order corresponding to the characteristic information, and determining whether the processing status of the target work order is completed;

[0119] The tracking module is also used to: if the processing status of the target work order is completed, then based on the work order generation system, generate a pending work order corresponding to the SQL log file, track the processing flow of the pending work order, and determine the processing status of the pending work order.

[0120] On the basis of the above embodiment, the slow SQL statement tracking device provided in the embodiment of the present invention further includes a storage module for:

[0121] If the processing status of the target work order is unfinished, the slow SQL log file and the characteristic information are stored.

[0122] On the basis of the above-mentioned embodiment, in the slow SQL statement tracking device provided in the embodiment of the present invention, the tracking module is specifically used for:

[0123] A statement template corresponding to the slow SQL statement to be traced is extracted, and the statement template is used as the feature information.

[0124] On the basis of the above embodiment, in the slow SQL statement tracking device provided in the embodiment of the present invention, the tracking module is further specifically used for:

[0125] The sentence template is encrypted to obtain an identification mark of the sentence template. If it is determined that the identification mark appears for the first time, it is determined that the characteristic information appears for the first time.

[0126] On the basis of the above-mentioned embodiment, in the slow SQL statement tracing device provided in the embodiment of the present invention, the slow SQL statement to be traced determining module is further specifically used for:

[0127] Convert the SQL log file into a standard log file in json format;

[0128] The slow SQL statement to be traced is determined based on the standard log file.

[0129] On the basis of the above embodiment, the slow SQL statement tracking device provided in the embodiment of the present invention further includes a sending module, which is used to:

[0130] If it is determined that the characteristic information appears for the first time, a prompt indicating that the slow SQL statement to be tracked appears is sent to the user terminal.

[0131] Specifically, the functions of each module in the slow SQL statement tracking device provided in the embodiment of the present invention correspond one-to-one to the operation flow of each step in the above method embodiment, and the effects achieved are also consistent. Please refer to the above embodiment for details, which will not be repeated in the embodiment of the present invention.

[0132] Figure 7 An example of a physical structure diagram of an electronic device is shown in FIG. Figure 7 As shown, the electronic device may include: a processor 710, a communication interface 720, a memory 730 and a communication bus 740, wherein the processor 710, the communication interface 720 and the memory 730 communicate with each other through the communication bus 740. The processor 710 may call the logic instructions in the memory 730 to execute the slow SQL statement tracking method provided in the above embodiments, the method comprising: determining the slow SQL statement to be tracked based on the SQL log file; determining the characteristic information of the slow SQL statement to be tracked, if it is determined that the characteristic information appears for the first time, then generating a to-be-processed work order corresponding to the characteristic information based on the work order generation system, tracking the processing flow of the to-be-processed work order, and determining the processing status of the to-be-processed work order.

[0133] In addition, the logic instructions in the above-mentioned memory 730 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk and other media that can store program codes.

[0134] On the other hand, the present invention also provides a computer program product, which includes a computer program stored on a non-transitory computer-readable storage medium, and the computer program includes program instructions. When the program instructions are executed by a computer, the computer can execute the slow SQL statement tracking method provided by the above-mentioned embodiments, and the method includes: determining a slow SQL statement to be tracked based on an SQL log file; determining characteristic information of the slow SQL statement to be tracked, and if it is judged that the characteristic information appears for the first time, generating a work order to be processed corresponding to the characteristic information based on a work order generation system, and tracking the processing flow of the work order to be processed, and determining the processing status of the work order to be processed.

[0135] On the other hand, the present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, it is implemented to execute the slow SQL statement tracking method provided by the above-mentioned embodiments, the method comprising: determining the slow SQL statement to be tracked based on the SQL log file; determining the characteristic information of the slow SQL statement to be tracked, and if it is judged that the characteristic information appears 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 status of the work order to be processed.

[0136] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative labor.

[0137] Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a necessary general hardware platform, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0138] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A slow SQL statement tracking method, characterized in that: include: Based on the SQL log file, determine the slow SQL statements to be traced; Determine the characteristic information of the slow SQL statement to be tracked. If it is determined that the characteristic information appears for the first time, generate a to-be-processed work order corresponding to the characteristic information based on a work order generation system, track the processing flow of the to-be-processed work order, and determine the processing status of the to-be-processed work order; The step of determining the characteristic information of the slow SQL statement to be traced further includes: If it is determined that the characteristic information does not appear for the first time, determining the target work order corresponding to the characteristic information, and determining whether the processing status of the target work order is completed; If the processing status of the target work order is completed, based on the work order generation system, a to-be-processed work order corresponding to the SQL log file is generated, and the processing flow of the to-be-processed work order is tracked to determine the processing status of the to-be-processed work order; The step of determining whether the processing status of the target work order is completed further includes: If the processing status of the target work order is unfinished, the SQL log file and the characteristic information are stored.

2. The slow SQL statement tracking method according to claim 1, characterized in that: The SQL log file includes the SQL log file of each business project, and the SQL log file of each business project includes an SQL statement and the execution time of the SQL statement; Accordingly, determining the slow SQL statement to be traced based on the SQL log file specifically includes: For any SQL statement corresponding to any business item, if it is determined that the execution time of any SQL statement is greater than or equal to a first preset time, then the any SQL statement is determined to be the slow SQL statement to be tracked; The first preset duration corresponds to the service item one by one.

3. The slow SQL statement tracking method according to claim 2, characterized in that: The determining the slow SQL statement to be tracked based on the SQL log file further specifically includes: If it is determined that the execution time of any SQL statement is greater than or equal to the second preset time, then the any SQL statement is determined to be a slow SQL statement; If it is determined that the execution time of the slow SQL statement is greater than or equal to the third preset time, the slow SQL statement is determined to be the slow SQL statement to be tracked; The second preset time length corresponds to each of the service items one by one, and the second preset time length corresponding to each of the service items is smaller than the third preset time length; and the third preset time length corresponds to each of the service items.

4. The slow SQL statement tracing method according to claim 1, characterized in that: The determining the characteristic information of the slow SQL statement to be traced specifically includes: A statement template corresponding to the slow SQL statement to be traced is extracted, and the statement template is used as the feature information.

5. The slow SQL statement tracing method according to claim 4, characterized in that: The step of determining the characteristic information of the slow SQL statement to be traced further includes: The sentence template is encrypted to obtain an identification mark of the sentence template. If it is determined that the identification mark appears for the first time, it is determined that the characteristic information appears for the first time.

6. The method for tracing slow SQL statements according to any one of claims 1 to 5, characterized in that: The step of determining the characteristic information of the slow SQL statement to be traced further includes: If it is determined that the characteristic information appears for the first time, a prompt indicating that the slow SQL statement to be tracked appears is sent to the user terminal.

7. A computer program product comprising a computer program or instructions, characterized in that When the computer program or instruction is executed by a processor, the steps of the slow SQL statement tracking method according to any one of claims 1 to 6 are implemented.

8. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the slow SQL statement tracking method according to any one of claims 1 to 6 are implemented.

Images