A slow query statement optimization processing method, device, system and storage medium

Abstract

The application provides a slow query statement optimization processing method, device and system and a storage medium. The method comprises the following steps: collecting a slow query statement, and determining whether the type of the slow query statement is a high-risk type according to a preset screening condition; if the type of the slow query statement is the high-risk type, determining that there is no statement template matched with the slow query statement in a knowledge base by querying the knowledge base; then, performing optimization processing on the slow query statement and / or metadata information corresponding to the slow query statement to obtain an optimized slow query statement and / or metadata information, and performing a preview process based on the optimized slow query statement and / or the optimized metadata information to obtain a performance result; if the performance result is performance improvement, performing subsequent execution processing by using the optimized slow query statement and / or the optimized metadata information, and storing an optimization mode of the optimized slow query statement and / or the optimized metadata information and a statement template matched with the slow query statement in the knowledge base.

Description

Technical Field

[0001] This application relates to the field of computers, and in particular to a method, apparatus, system, and storage medium for optimizing slow query statements. Background Technology

[0002] Slow queries generally refer to Structured Query Language (SQL) queries that take more than one second to complete in a database. Therefore, slow queries are a significant indicator affecting database performance and stability. Now, with business growth, business systems are becoming increasingly complex, and the user base is expanding. A sudden surge in concurrent slow queries can lead to more timeouts, resulting in users having to wait significantly longer to retrieve information.

[0003] In existing technologies, slow queries are typically optimized using the following methods: Before a slow query occurs, SQL development standards are established to constrain the coding behavior of developers. When a slow query occurs, an alert is sent to relevant maintenance personnel, who then query the SQL statement that generated the slow query in real time and perform troubleshooting. After a slow query occurs, the relevant maintenance personnel save the corresponding SQL statement and distribute these saved SQL statements to the responsible personnel for review and optimization.

[0004] However, while slow query optimization methods constrain developers' coding behavior to some extent before slow queries occur, inadequate technical skills or management practices can still lead to slow queries in practical applications, requiring intervention from other personnel. Similarly, when slow queries occur, this method still requires real-time manual monitoring, resulting in poor timeliness. Furthermore, after slow queries occur, this method requires professional database administrators to optimize SQL statements, and relevant personnel need to track the optimization progress, consuming significant human and material resources. Therefore, regardless of the stage of slow query optimization, manual intervention is necessary, leading to delayed processing of slow queries and consequently, slower application response and a poor user experience. Summary of the Invention

[0005] This application provides a method, apparatus, system, and storage medium for optimizing slow query statements, in order to solve the technical problem that slow queries cannot be processed in a timely manner due to human optimization of slow queries, thereby causing the application to respond slowly.

[0006] Firstly, this application provides an optimization method for slow query statements, including:

[0007] Collect slow query statements and determine whether the type of the slow query statement is a high-risk type based on preset filtering conditions;

[0008] If it is determined that the slow query statement is of a high-risk type, then the knowledge base is queried to determine whether there is a statement template that matches the slow query statement.

[0009] If it is determined that there is no statement template matching the slow query statement, the optimization engine is triggered to optimize the slow query statement and / or the metadata information corresponding to the slow query statement to obtain the optimized slow query statement and / or metadata information, and a pre-running process is performed based on the optimized slow query statement and / or the optimized metadata information to obtain performance results.

[0010] If the performance result is a performance improvement, the optimization engine is triggered to perform subsequent execution processing using the optimized slow query statement and / or the optimized metadata information, and stores the optimization method of the optimized slow query statement and / or the optimized metadata information, as well as the statement template matched by the slow query statement, in the knowledge base.

[0011] In one possible implementation, the trigger optimization engine optimizes the slow query statement and / or the metadata information corresponding to the slow query statement to obtain optimized slow query statement and / or metadata information, including:

[0012] The optimization engine is triggered to rewrite the slow query statement to obtain an optimized slow query statement.

[0013] In one possible implementation, the trigger optimization engine optimizes the slow query statement and / or the metadata information corresponding to the slow query statement to obtain optimized slow query statement and / or metadata information, including:

[0014] The optimization engine is triggered to extract the metadata information corresponding to the slow query statement, and the metadata information is processed by adding or deleting indexes to obtain the optimized metadata information.

[0015] In one possible implementation, determining whether the type of the slow query statement is a high-risk type based on preset filtering conditions includes:

[0016] Obtain the number of rows scanned, execution time, and execution count corresponding to the slow query statement, and determine whether the number of rows scanned is equal to the total number of rows in the table threshold, whether the execution time is greater than the high-risk execution time threshold, and whether the execution count is greater than the high-risk execution count threshold;

[0017] If the number of rows scanned is equal to the total number of rows in the table threshold, the execution time is greater than the high-risk execution time threshold, and / or the number of executions is greater than the high-risk execution count threshold, then the slow query statement is determined to be of a high-risk type.

[0018] In one possible implementation, it also includes:

[0019] If the number of rows scanned is less than the total number of rows in the table threshold, the execution time is less than the high-risk execution time threshold, and the number of executions is less than the high-risk execution count threshold, then the slow query statement is determined to be of a non-high-risk type.

[0020] In one possible implementation, it also includes:

[0021] Obtain key information of slow query statements of non-high-risk types, and tag the slow query statements of non-high-risk types according to the key information to obtain the corresponding identifier of the slow query statements of non-high-risk types.

[0022] The slow query statements of the non-high-risk type are then sent to the analysis terminal corresponding to the identifier of the slow query statement of the non-high-risk type, so that each corresponding analysis terminal can analyze and process the slow query statements of the non-high-risk type.

[0023] In one possible implementation, it also includes:

[0024] If a statement template matching the slow query statement is determined, the optimization method corresponding to the matching statement template is obtained, and the slow query statement and / or the metadata information corresponding to the slow query statement are optimized based on the optimization method, so as to perform subsequent execution processing through the obtained optimized slow query statement and / or optimized metadata information.

[0025] Secondly, this application provides an optimization processing apparatus for slow query statements, comprising:

[0026] The acquisition module is used to collect slow query statements and determine whether the type of the slow query statement is a high-risk type according to preset filtering conditions.

[0027] The processing module is used to query the knowledge base to determine whether there is a statement template that matches the slow query statement if it is determined that the type of the slow query statement is a high-risk type.

[0028] The processing module is further configured to, if it is determined that there is no statement template matching the slow query statement, trigger the optimization engine to optimize the slow query statement and / or the metadata information corresponding to the slow query statement, so as to obtain the optimized slow query statement and / or metadata information, and perform pre-running based on the optimized slow query statement and / or the optimized metadata information to obtain performance results;

[0029] The processing module is further configured to, if the performance result is a performance improvement result, trigger the optimization engine to perform subsequent execution processing through the optimized slow query statement and / or optimized metadata information, and store the optimization method of the optimized slow query statement and / or optimized metadata information, as well as the statement template matched by the slow query statement, into the knowledge base.

[0030] Thirdly, this application provides an optimization system for slow query statements, comprising: a data collector, a filter, an automatic processing device, a knowledge base, a test library, and an optimization engine; wherein,

[0031] The collector is used to collect and acquire slow query statements;

[0032] The filter is used to determine whether the type of the slow query statement is a high-risk type based on preset filtering conditions;

[0033] The automatic processing device is used to query the knowledge base to determine whether there is a statement template that matches the slow query statement if it is determined that the type of the slow query statement is a high-risk type.

[0034] The automatic processing device is further configured to, if it is determined that there is no statement template matching the slow query statement, trigger the optimization engine to optimize the slow query statement and / or the metadata information corresponding to the slow query statement, so as to obtain the optimized slow query statement and / or metadata information.

[0035] The test library is used to perform pre-processing based on the optimized slow query statement and / or the optimized metadata information to obtain performance results.

[0036] The optimization engine is configured to perform subsequent execution processing using the optimized slow query statement and / or optimized metadata information if the performance result is a performance improvement result, and to store the optimization method of the optimized slow query statement and / or optimized metadata information, as well as the statement template matched by the slow query statement, in the knowledge base.

[0037] Fourthly, this application provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the method described in any of the first aspects.

[0038] This application provides a method, apparatus, system, and storage medium for optimizing slow query statements. The method acquires slow query statements and determines whether the type of the slow query statement is a high-risk type based on preset filtering conditions. If the slow query statement is determined to be a high-risk type, a knowledge base is queried to determine if a statement template matching the slow query statement exists. If no matching statement template exists, an optimization engine is triggered to optimize the slow query statement and / or its corresponding metadata information to obtain optimized slow query statements and / or metadata information. Pre-processing is then performed based on the optimized slow query statement and / or optimized metadata information to obtain performance results. If the performance results are improved, the optimization engine is triggered to perform subsequent execution processing using the optimized slow query statement and / or optimized metadata information. The optimization method of the optimized slow query statement and / or optimized metadata information, as well as the matching statement template, are stored in the knowledge base, achieving automatic and rapid processing of slow queries, thereby accelerating application response. Attached Figure Description

[0039] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0040] Figure 1 A flowchart illustrating an embodiment of a method for optimizing slow query statements provided in this application;

[0041] Figure 2 A flowchart illustrating a second embodiment of an optimization method for slow query statements provided in this application.

[0042] Figure 3 A flowchart illustrating a third embodiment of an optimization method for slow query statements provided in this application.

[0043] Figure 4 A flowchart illustrating Embodiment 4 of an optimization method for slow query statements provided in this application;

[0044] Figure 5 A schematic diagram of the structure of an embodiment of a slow query statement optimization system provided in this application;

[0045] Figure 6This is a schematic diagram of an embodiment of a slow query statement optimization processing device provided in this application.

[0046] The accompanying drawings illustrate specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0047] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application.

[0048] Slow SQL generally refers to SQL queries that take more than one second to execute. The execution time can be set by modifying the `long_query_time` parameter in the database. Slow SQL is an important indicator of database performance and stability. As businesses grow, the complexity of business systems increases, and the number of users grows. Therefore, a sudden surge in concurrent slow SQL queries, leading to an increase in timeout queries, can cause database performance degradation and even significant security risks if not handled promptly.

[0049] The existing methods and problems for handling slow queries are as follows:

[0050] (1) Pre-emptive measures: Before slow queries occur, the R&D team formulates SQL development specifications to regulate the coding behavior of R&D personnel. Although this can avoid the occurrence of slow SQL to a certain extent before going live, it may not be effectively implemented due to factors such as the technical level or management methods of the R&D team.

[0051] (2) In-process handling method: When a slow query occurs, the relevant maintenance personnel receive an alarm, query the slow SQL in real time, and perform removal. This method can temporarily reduce database resource consumption and restore normal business through timely human intervention, but it has high labor costs and requires high timeliness and personnel cooperation.

[0052] (3) Post-event handling method: After a slow query occurs, maintenance personnel retain the slow SQL during the troubleshooting process and then distribute it to the person in charge for review and optimization. This method can prevent the identified slow SQL from recurring, but it requires professional database administrators to provide SQL optimization guidance and dedicated personnel to track the optimization progress. It is time-consuming, labor-intensive, and has a certain technical threshold.

[0053] Based on this, in order to solve the above-mentioned technical problems, the technical concept of this application lies in providing a new method for optimizing slow query statements to achieve timely resolution of slow queries. The technical solution of this application will be described in detail below through specific embodiments. It should be noted that the following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments.

[0054] Figure 1 A flowchart illustrating an embodiment of an optimization method for slow query statements provided in this application is shown below. Figure 1 As shown, specifically, the method includes:

[0055] S101. Collect and obtain slow query statements, and determine whether the type of slow query statement is a high-risk type according to the preset filtering conditions.

[0056] In this embodiment, for example, after detecting an SQL query that takes more than 1 second to complete in the database, the SQL statement is collected and identified as a slow query. Then, based on preset filtering criteria, it is determined whether the type of the slow query is high-risk.

[0057] S102. If it is determined that the slow query statement is of a high-risk type, then query the knowledge base to determine whether there is a statement template that matches the slow query statement.

[0058] In this embodiment, for example, if the slow query statement is "select * from test where id = 1", then after removing the parameters, the statement template of the slow query statement is "select * from test where id = ?". Then, the knowledge base is queried to determine whether the template "select * from test where id = ?" exists.

[0059] S103. If it is determined that there is no statement template matching the slow query statement, the optimization engine is triggered to optimize the slow query statement and / or the metadata information corresponding to the slow query statement to obtain the optimized slow query statement and / or metadata information, and pre-processing is performed based on the optimized slow query statement and / or the optimized metadata information to obtain the performance results.

[0060] In this example, the slow query statement can be optionally rewritten. For example, if the slow query statement is "select * from test where id = 0", it can be rewritten as "select 'name' from test where id = 0".

[0061] S104. If the performance result is a performance improvement result, the optimization engine is triggered to perform subsequent execution processing through the optimized slow query statement and / or the optimized metadata information, and the optimization method of the optimized slow query statement and / or the optimized metadata information, as well as the statement template matched by the slow query statement, are stored in the knowledge base.

[0062] In this embodiment, for example, if the performance result is a reduction in execution time, the optimized slow query statement and / or the optimized metadata information are applied to the actual application environment. For example, the optimized slow query statement is used for execution, thereby improving the query speed compared to the query speed based on the slow query statement, thus effectively improving the user's query speed and user experience; or, the optimized metadata information is queried using the slow query statement, thereby improving the query speed compared to the query speed of the metadata information before optimization based on the SQL statement.

[0063] In addition, to facilitate subsequent automatic optimization, the optimization methods of the matching slow query statement template, the corresponding optimized slow query statement, and / or the optimized metadata information can be stored in the knowledge base.

[0064] In this embodiment, slow query statements are collected and, based on preset filtering conditions, it is determined whether the type of the slow query statement is a high-risk type. If the type of the slow query statement is determined to be high-risk, the knowledge base is queried to determine whether there is a statement template matching the slow query statement. If no statement template matching the slow query statement is determined, the optimization engine is triggered to optimize the slow query statement and / or the metadata information corresponding to the slow query statement to obtain the optimized slow query statement and / or metadata information. Based on the optimized slow query statement and / or the optimized metadata information, pre-running processing is performed to obtain performance results. If the performance result is a performance improvement, the optimization engine is triggered to perform subsequent execution processing using the optimized slow query statement and / or the optimized metadata information, and the optimization method of the optimized slow query statement and / or the optimized metadata information, as well as the statement template matching the slow query statement, are stored in the knowledge base. Compared to existing technologies that require manual processing of slow query statements, leading to low processing efficiency, this application, after determining that no matching statement template exists in the knowledge base, triggers an optimization engine to optimize the slow query statement and / or its corresponding metadata information. This optimizes the slow query statement and / or metadata information, and then performs pre-processing based on the optimized slow query statement and / or optimized metadata information to obtain performance results. If the performance result is an improvement, subsequent processing is performed using the optimized slow query statement and / or optimized metadata information. This achieves automatic optimization of slow query statements, thereby accelerating query processing speed.

[0065] Figure 2 This is a flowchart illustrating a second embodiment of an optimization method for slow query statements provided in this application. Based on the above embodiments, as follows... Figure 2 As shown, one specific implementation of step S103 is as follows:

[0066] S201. If it is determined that there is no statement template that matches the slow query statement, the optimization engine is triggered to rewrite the slow query statement to obtain the optimized slow query statement, and pre-processing is performed based on the optimized slow query statement to obtain the performance results.

[0067] Alternatively, another specific implementation of step 103 is as follows:

[0068] If it is determined that there is no statement template that matches the slow query statement, the optimization engine is triggered to extract the metadata information corresponding to the slow query statement, and perform index addition and deletion processing on the metadata information to obtain optimized metadata information. Based on the optimized metadata information, pre-processing is performed to obtain performance results.

[0069] In this embodiment, for example, the slow query statement is "select * from test where id = 0". Based on preset rules, the slow query statement is rewritten as "select 'name' from test where id = 0". And / or indexes are added or deleted from fields in the metadata information. Then, based on the rewritten slow query statement and / or the optimized metadata information, the query is executed in the test database to obtain the execution time.

[0070] In this embodiment, the optimization engine is triggered to rewrite slow query statements to obtain optimized slow query statements. And / or, the optimization engine is triggered to extract metadata information corresponding to the slow query statements and perform indexing on the metadata information to obtain optimized metadata information. Compared to the prior art where slow query statements are manually processed, leading to slow application response, this application improves the processing efficiency of slow query statements and thus improves the access speed of the application by triggering the optimization engine to rewrite slow query statements and / or extracting metadata information corresponding to the slow query statements and performing indexing on the metadata information.

[0071] Figure 3 A flowchart illustrating Embodiment 3 of an optimization method for slow query statements provided in this application is shown below. Figure 3 As shown, the method includes:

[0072] S301. Obtain the number of rows scanned, execution time, and execution count corresponding to the slow query statement, and determine whether the number of rows scanned is equal to the total number of rows in the table threshold, whether the execution time is greater than the high-risk execution time threshold, and whether the execution count is greater than the high-risk execution count threshold.

[0073] In this embodiment, the "explain slow query statement" is executed in the database to obtain the execution plan of the slow query statement. Then, the number of rows scanned is obtained from the execution plan. The execution time of the slow query statement is obtained from the slow query log in the database. The number of times the slow query statement is executed within a day is obtained from the database log.

[0074] S302. If the number of rows scanned is equal to the threshold for the total number of rows in the table, the execution time is greater than the threshold for high-risk execution time, and / or the number of executions is greater than the threshold for high-risk executions, then the slow query statement is determined to be of a high-risk type.

[0075] In this embodiment, for example, if the number of rows scanned is equal to the threshold of the total number of rows in the table, the execution time is greater than 5 seconds, and / or the number of executions is greater than 100, then the slow query statement is determined to be of a high-risk type.

[0076] S303. Query the knowledge base to determine if a statement template matching the slow query statement exists. If it is determined that no such template exists, proceed to step S304; if it is determined that a such template exists, proceed to step S306.

[0077] In this embodiment, for example, if the slow query statement is "select * from test where id = 1", then after removing the parameters, the statement template of the slow query statement is "select * from test where id = ?". Then, the knowledge base is queried to determine whether the template "select * from test where id = ?" exists.

[0078] S304. Trigger the optimization engine to optimize the slow query statement and / or the metadata information corresponding to the slow query statement to obtain the optimized slow query statement and / or metadata information, and perform pre-processing based on the optimized slow query statement and / or the optimized metadata information to obtain performance results.

[0079] In this example, the slow query statement can be optionally rewritten. For example, if the slow query statement is "select * from test where id = 0", it can be rewritten as "select 'name' from test where id = 0".

[0080] S305. If the performance result indicates improved performance, then subsequent execution processing will be performed using the optimized slow query statement and / or optimized metadata information. The optimization methods of the optimized slow query statement and / or optimized metadata information, as well as the statement template matched by the slow query statement, will be stored in the knowledge base. End.

[0081] In this embodiment, optionally, if the performance result is either a performance decrease or no performance, the slow query statement is sent to the database administrator. After the database administrator completes the optimization according to the optimization process, the statement template matching the slow query statement and the optimization process are stored in the knowledge base.

[0082] S306. Obtain the optimization method corresponding to the matched statement template, and optimize the slow query statement and / or the metadata information corresponding to the slow query statement based on the optimization method, so as to perform subsequent execution processing through the obtained optimized slow query statement and / or optimized metadata information.

[0083] In this example, the system retrieves the number of rows scanned, execution time, and execution count corresponding to the slow query statement. It then determines whether the number of scanned rows equals the total table row count threshold, whether the execution time exceeds the high-risk execution time threshold, and whether the execution count exceeds the high-risk execution count threshold. If the number of scanned rows equals the total table row count threshold, the execution time exceeds the high-risk execution time threshold, and / or the execution count exceeds the high-risk execution count threshold, the slow query statement is classified as high-risk. The system then queries the knowledge base to determine if a matching query template exists. If no template exists, the optimization engine is triggered to optimize the slow query statement and / or its corresponding metadata information to obtain the optimized slow query statement and / or metadata information. Based on the optimized slow query statements and / or optimized metadata information, a pre-processing procedure is performed to obtain performance results. If the performance result indicates improved performance, subsequent execution processing is performed using the optimized slow query statements and / or optimized metadata information. The optimization methods of the optimized slow query statements and / or optimized metadata information, as well as the statement templates matching the slow query statements, are stored in a knowledge base. If an optimization method corresponding to the matching statement template is found, the optimization method is retrieved, and the slow query statements and / or their corresponding metadata information are optimized based on this method. Subsequent execution processing is then performed using the retrieved optimized slow query statements and / or optimized metadata information. This approach enables the filtering and timely optimization of high-risk slow query statements, thereby achieving rapid application response.

[0084] Figure 4 This is a flowchart illustrating a fourth embodiment of an optimization method for slow query statements provided by this application. Based on the above example, after step S301, the method further includes:

[0085] S401. If the number of rows scanned is less than the total number of rows in the table, the execution time is less than the high-risk execution time threshold, and the number of executions is less than the high-risk execution count threshold, then the slow query statement is determined to be of a non-high-risk type.

[0086] In this embodiment, for example, if the number of rows scanned is less than the threshold for the total number of rows in the table, the execution time is less than 5 seconds, and the number of executions is less than 100, then the slow query statement is determined to be of a non-high-risk type.

[0087] S402. Obtain key information of slow query statements of non-high-risk types, and mark the slow query statements of non-high-risk types according to the key information to obtain the corresponding identifiers of slow query statements of non-high-risk types.

[0088] In this embodiment, key information of slow query statements of non-high-risk types is obtained, and after analyzing and statistically analyzing the key information, the slow query statements of non-high-risk types are tagged to obtain the corresponding identifiers of slow query statements of non-high-risk types.

[0089] In this embodiment, for example, the execution time, number of rows scanned, and IP source of a slow query statement that is not of a high-risk type are obtained, and the slow query statement is tagged as a new statement added this week, a full table scan, or an inefficient index based on the execution time, number of rows scanned, and IP source.

[0090] S403, and send the slow query statements of non-high-risk types to the analysis terminal corresponding to the identifier of the slow query statement of non-high-risk type, so that each corresponding analysis terminal can analyze and process the slow query statements of non-high-risk types.

[0091] In this embodiment, for example, slow query statements of non-high-risk types are sent to the database administrator's analysis terminal so that the database administrator can classify the slow query data of non-high-risk types into important SQL, ignored SQL, and scheduled optimization according to the identifier. Then, according to the analysis terminal corresponding to the identifier of the slow query statement of non-high-risk type, it is distributed to each R&D team for processing.

[0092] In this example, the daily number of slow queries, the number of slow queries processed, the IP sources of slow queries, and the maximum duration of slow queries for each R&D team are displayed on a large screen so that database administrators can monitor the optimization of slow queries in real time.

[0093] In this embodiment, if the number of rows scanned is less than the total number of rows in the table threshold, the execution time is less than the high-risk execution time threshold, and the number of executions is less than the high-risk execution count threshold, then the slow query statement is determined to be of a non-high-risk type. Key information from the non-high-risk slow query statement is obtained, and based on this key information, the non-high-risk slow query statement is tagged to obtain its corresponding identifier. The non-high-risk slow query statement is then sent to the analysis terminal corresponding to the identifier of the non-high-risk slow query statement, so that each corresponding analysis terminal can analyze and process the non-high-risk slow query statement, thereby improving the processing efficiency of the non-high-risk slow query statement.

[0094] Figure 5 A schematic diagram of an embodiment of a slow query statement optimization processing device provided in this application is shown below. Figure 5 As shown, the device includes an acquisition module 51 and a processing module 52.

[0095] Specifically, the acquisition module 51 is used to collect slow query statements and determine whether the type of the slow query statement is a high-risk type based on preset filtering conditions. The processing module 52, if the slow query statement is determined to be a high-risk type, queries the knowledge base to determine if a statement template matching the slow query statement exists. The processing module 52 is also used to, if it is determined that no statement template matching the slow query statement exists, trigger the optimization engine to optimize the slow query statement and / or its corresponding metadata information to obtain the optimized slow query statement and / or metadata information, and perform pre-processing based on the optimized slow query statement and / or optimized metadata information to obtain performance results. The processing module 52 is also used to, if the performance result is a performance improvement, trigger the optimization engine to perform subsequent execution processing using the optimized slow query statement and / or optimized metadata information, and store the optimization method of the optimized slow query statement and / or optimized metadata information, as well as the statement template matching the slow query statement, in the knowledge base.

[0096] The slow query statement optimization processing device provided in this embodiment can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar, and will not be described again here.

[0097] Figure 6 A schematic diagram of an embodiment of a slow query statement optimization system provided in this application is shown below. Figure 1 As shown, the system includes: a collector 61, a filter 62, an automatic processing device 63, a knowledge base 64, a test library 65, and an optimization engine 66.

[0098] In this process, after the collector 11 collects slow query statements, the filter 62 determines whether the type of the slow query statement is a high-risk type based on preset filtering conditions. If the automatic processing device 63 determines that the type of the slow query statement is a high-risk type, it queries the knowledge base 64 to see if there is a statement template that matches the slow query statement. If it is determined that there is no statement template that matches the slow query statement, the optimization engine 66 is triggered to optimize the slow query statement and / or the metadata information corresponding to the slow query statement to obtain the optimized slow query statement and / or metadata information. The test library 65 performs pre-testing based on the optimized slow query statement and / or the optimized metadata information to obtain performance results. If the performance result is a performance improvement result, the optimization engine 66 performs subsequent execution processing through the optimized slow query statement and / or the optimized metadata information, and stores the optimization method of the optimized slow query statement and / or the optimized metadata information, as well as the statement template that matches the slow query statement, in the knowledge base 64.

[0099] The slow query statement optimization system provided in this embodiment can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar, and will not be described again here.

[0100] This embodiment also provides a computer-readable storage medium storing computer-executable instructions. When the processor executes the computer-executable instructions, it implements the embodiment of the method described above, which will not be repeated here.

[0101] Those skilled in the art will understand that all or part of the steps of the above-described method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When executed, the program performs the steps of the above-described method embodiments; and the aforementioned storage medium includes various media capable of storing program code, such as ROM, RAM, magnetic disks, or optical disks.

[0102] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for optimizing slow query statements, characterized in that, include: Collect slow query statements and determine whether the type of the slow query statement is a high-risk type based on preset filtering conditions; If it is determined that the slow query statement is of a high-risk type, then the knowledge base is queried to determine whether there is a statement template that matches the slow query statement. If it is determined that there is no statement template matching the slow query statement, the optimization engine is triggered to optimize the slow query statement and / or the metadata information corresponding to the slow query statement to obtain the optimized slow query statement and / or metadata information, and a pre-running process is performed based on the optimized slow query statement and / or the optimized metadata information to obtain performance results. If the performance result is a performance improvement, the optimization engine is triggered to perform subsequent execution processing through the optimized slow query statement and / or the optimized metadata information, and stores the optimization method of the optimized slow query statement and / or the optimized metadata information, as well as the statement template matched by the slow query statement, in the knowledge base; The step of determining whether the slow query statement is a high-risk type based on preset filtering conditions includes: Obtain the number of rows scanned, execution time, and execution count corresponding to the slow query statement, and determine whether the number of rows scanned is equal to the total number of rows in the table threshold, whether the execution time is greater than the high-risk execution time threshold, and whether the execution count is greater than the high-risk execution count threshold; If the number of rows scanned is equal to the total number of rows in the table threshold, the execution time is greater than the high-risk execution time threshold, and / or the number of executions is greater than the high-risk execution count threshold, then the slow query statement is determined to be of a high-risk type.

2. The method according to claim 1, characterized in that, The trigger optimization engine optimizes the slow query statement and / or the metadata information corresponding to the slow query statement to obtain the optimized slow query statement and / or metadata information, including: The optimization engine is triggered to rewrite the slow query statement to obtain an optimized slow query statement.

3. The method according to claim 1, characterized in that, The trigger optimization engine optimizes the slow query statement and / or the metadata information corresponding to the slow query statement to obtain the optimized slow query statement and / or metadata information, including: The optimization engine is triggered to extract the metadata information corresponding to the slow query statement, and the metadata information is processed by adding or deleting indexes to obtain the optimized metadata information.

4. The method according to claim 1, characterized in that, Also includes: If the number of rows scanned is less than the total number of rows in the table threshold, the execution time is less than the high-risk execution time threshold, and the number of executions is less than the high-risk execution count threshold, then the slow query statement is determined to be of a non-high-risk type.

5. The method according to claim 4, characterized in that, Also includes: Obtain key information of slow query statements of non-high-risk types, and tag the slow query statements of non-high-risk types according to the key information to obtain the corresponding identifier of the slow query statements of non-high-risk types. The slow query statements of the non-high-risk type are then sent to the analysis terminal corresponding to the identifier of the slow query statement of the non-high-risk type, so that each corresponding analysis terminal can analyze and process the slow query statements of the non-high-risk type.

6. The method according to claim 1, characterized in that, Also includes: If a statement template matching the slow query statement is determined, the optimization method corresponding to the matching statement template is obtained, and the slow query statement and / or the metadata information corresponding to the slow query statement are optimized based on the optimization method, so as to perform subsequent execution processing through the obtained optimized slow query statement and / or optimized metadata information.

7. An optimization processing device for slow query statements, characterized in that, include: The acquisition module is used to collect slow query statements and determine whether the type of the slow query statement is a high-risk type according to preset filtering conditions. The processing module is used to query the knowledge base to determine whether there is a statement template that matches the slow query statement if it is determined that the type of the slow query statement is a high-risk type. The processing module is further configured to, if it is determined that there is no statement template matching the slow query statement, trigger the optimization engine to optimize the slow query statement and / or the metadata information corresponding to the slow query statement, so as to obtain the optimized slow query statement and / or metadata information, and perform pre-running based on the optimized slow query statement and / or the optimized metadata information to obtain performance results; The processing module is further configured to, if the performance result is a performance improvement result, trigger the optimization engine to perform subsequent execution processing through the optimized slow query statement and / or optimized metadata information, and store the optimization method of the optimized slow query statement and / or optimized metadata information, as well as the statement template matched by the slow query statement, into the knowledge base; The acquisition module is specifically used for: Obtain the number of rows scanned, execution time, and execution count corresponding to the slow query statement, and determine whether the number of rows scanned is equal to the total number of rows in the table threshold, whether the execution time is greater than the high-risk execution time threshold, and whether the execution count is greater than the high-risk execution count threshold; If the number of rows scanned is equal to the total number of rows in the table threshold, the execution time is greater than the high-risk execution time threshold, and / or the number of executions is greater than the high-risk execution count threshold, then the slow query statement is determined to be of a high-risk type.

8. A system for optimizing slow query statements, comprising: Collectors, filters, automated processing devices, knowledge bases, testing libraries, and optimization engines; among them, The collector is used to collect and acquire slow query statements; The filter is used to determine whether the type of the slow query statement is a high-risk type based on preset filtering conditions; The automatic processing device is used to query the knowledge base to determine whether there is a statement template that matches the slow query statement if it is determined that the type of the slow query statement is a high-risk type. The automatic processing device is further configured to, if it is determined that there is no statement template matching the slow query statement, trigger the optimization engine to optimize the slow query statement and / or the metadata information corresponding to the slow query statement, so as to obtain the optimized slow query statement and / or metadata information. The test library is used to perform pre-performance processing based on the optimized slow query statement and / or the optimized metadata information to obtain performance results. The optimization engine is used to perform subsequent execution processing through the optimized slow query statement and / or optimized metadata information if the performance result is a performance improvement result, and to store the optimization method of the optimized slow query statement and / or optimized metadata information, as well as the statement template matched by the slow query statement, in the knowledge base. The filter is specifically used for: Obtain the number of rows scanned, execution time, and execution count corresponding to the slow query statement, and determine whether the number of rows scanned is equal to the total number of rows in the table threshold, whether the execution time is greater than the high-risk execution time threshold, and whether the execution count is greater than the high-risk execution count threshold; If the number of rows scanned is equal to the total number of rows in the table threshold, the execution time is greater than the high-risk execution time threshold, and / or the number of executions is greater than the high-risk execution count threshold, then the slow query statement is determined to be of a high-risk type.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, are used to implement the method as described in any one of claims 1 to 6.

Images