Statement early warning method and device, equipment and computer readable storage medium

[0069] It should be understood that the specific embodiments described here are only used to explain the present invention, but not to limit the present invention.

[0070] Such as figure 1 As shown, figure 1 It is a schematic diagram of the device structure of the hardware operating environment involved in the solution of the embodiment of the present invention.

[0071] The sentence warning device in the embodiment of the present invention may be a PC or a server device.

[0072] Such as figure 1 As shown, the sentence warning device may include: a processor 1001, such as a CPU, a network interface 1004, a user interface 1003, a memory 1005, and a communication bus 1002. Among them, the communication bus 1002 is used to implement connection and communication between these components. The user interface 1003 may include a display screen (Display) and an input unit such as a keyboard (Keyboard), and the optional user interface 1003 may also include a standard wired interface and a wireless interface. The network interface 1004 may optionally include a standard wired interface and a wireless interface (such as a WI-FI interface). The memory 1005 may be a high-speed RAM memory, or a stable memory (non-volatile memory), such as a magnetic disk memory. Optionally, the memory 1005 may also be a storage device independent of the foregoing processor 1001.

[0073] Those skilled in the art can understand, figure 1 The device structure shown in does not constitute a limitation on the device, and may include more or less components than shown in the figure, or a combination of some components, or a different component arrangement.

[0074] Such as figure 1 As shown, the memory 1005 as a computer storage medium may include an operating system, a network communication module, a user interface module, and a sentence warning program.

[0075] in figure 1 In the terminal shown, the network interface 1004 is mainly used to connect to a background server and perform data communication with the background server; the user interface 1003 is mainly used to connect to a client (user side) and perform data communication with the client; and the processor 1001 can be used The sentence warning program stored in the memory 1005 is called, and the operations in each embodiment of the following sentence warning method are executed.

[0076] Based on the above hardware structure, an embodiment of the sentence warning method of the present invention is proposed.

[0077] Reference figure 2 , figure 2 It is a schematic flowchart of the first embodiment of the sentence warning method of the present invention, and the method includes:

[0078] Step S10, obtaining a structured query statement for the application program to access the preset production database;

[0079] In this embodiment, the statement specifically refers to a database operation statement composed of a structured query language SQL, which is used to access data and query, update, and manage a relational database system. The sentence warning method of this embodiment is applied to a sentence warning device. The sentence warning device may be a background server of the application program, that is, an application server. Of course, in order not to occupy application server resources, the sentence warning device may also be independent of the application server Warning server. In this embodiment, the sentence warning device is an warning server independent of the application server as an example for description.

[0080] In this embodiment, the application program accesses the production database through a structured query statement. The production database is the database actually used by the business. During specific implementation, the alert server can send a structured query based on the application program's access to the preset production database to the application server. Statement acquisition request, so that after receiving the request, the application server captures the corresponding structured query statement in the application and returns it to the warning server.

[0081] It should be noted that when the sentence warning device is an application server, the application server directly captures the corresponding structured query sentence in the application program.

[0082] Step S20, acquiring the execution plan of the structured query statement, and extracting preset parameters related to slow query from the execution plan;

[0083] In this step, the early warning server obtains the execution plan of the structured query statement, and then extracts preset parameters related to the slow query from the execution plan. Among them, the execution plan refers to the plan for the structured query statement to be physically executed in the database, which consists of several operations, including full table scans, use of indexes, nested loops, and table joins. Slow query refers to the phenomenon that the query response time exceeds the specified time in the process of querying the database using the SQL language. The execution plan can be optimized to improve the response speed of the statement and eliminate slow queries. The preset parameters related to slow query are preset parameters that may affect query speed and query efficiency in practice, including but not limited to query type, number of candidate results, sorting mode, etc. The specific implementation can be based on actual production The slow query fault experience and flexible settings.

[0084] In an embodiment, the step of obtaining the execution plan of the structured query statement may include: clustering the structured query statement, wherein the execution plans of the structured query statements of the same type are the same; respectively; Select a target structured query sentence from the structured query sentences of each type; access the disaster recovery database of the production database according to the target structured query sentence to obtain the execution plan of the structured query sentence of the corresponding type.

[0085] Specifically, the obtained structured query statements can be clustered first, where the execution plans of the structured query statements under the same type are the same, and specific clustering rules can be flexibly set according to actual business conditions, including but not It is limited to classify sentences that contain name query fields into one category, and classify sentences that contain age queries into another category.

[0086] Then, select a target structured query statement from the structured query statements under each type, where the selection is randomly selected or the sentence that is ranked at the top; then, according to the selected target structured query statement to access the production data Disaster tolerant database. The disaster tolerant database is a database that is deployed in a different place and is exactly the same as the production database. When a natural disaster occurs in the geographic location of the production database, the disaster tolerant database will immediately take over. When the disaster database receives the target structured query statement, it matches the corresponding execution plan for the target structured query statement and returns it to the early warning device. Because the execution plan of the structured query statement of the same type is the same, the early warning device When you can get the execution plan of all structured query statements under each type.

[0087] The above method of obtaining the execution plan after clustering the structured query statement realizes the batch acquisition of the execution plan and improves the efficiency of obtaining the execution plan. The execution plan is obtained from the disaster recovery database of the production database without occupying the production database. Therefore, it will not have any impact on the performance of the production database.

[0088] It should be noted that in the specific implementation, the structured query statement may not be clustered, but the execution plan corresponding to each structured query statement is obtained separately, and then the slow query related to the slow query is extracted from the obtained execution plan In addition, if the disaster recovery database is not deployed in practice, the execution plan of the structured query statement can also be obtained directly from the production database. At this time, some production database resources will be occupied. The specific implementation plan can be based on actual The situation is flexible.

[0089] Step S30, judging whether the extracted preset parameters meet preset slow query early warning conditions;

[0090] After extracting the preset parameters related to the slow query from the execution plan, it is determined whether the extracted preset parameters meet the preset slow query early warning conditions.

[0091] Wherein, the preset parameters may include the query type and the number of candidate results. At this time, the step of determining whether the extracted preset parameters meet the preset slow query early warning condition includes: determining whether the query type is a preset query type; If the query type is a preset query type, determine whether the number of candidate results is greater than or equal to a first preset threshold; if the number of candidate results is greater than or equal to a first preset threshold, then determine the preset parameter Meet the preset slow query early warning conditions.

[0092] In specific implementation, the query type corresponding to the sentence with a higher probability of slow query in actual experience can be used as the preset query type. For example, according to actual production experience, "ALL" (full table scan), "range" (in range The form of scan), "index" (scan in index order, read the index first, then read the actual row) these query types have a higher probability of slow query, so these query types can be used as the default query types. When the extracted query type is "ALL", "range" or "index", it is further judged whether the number of extracted candidate results is greater than or equal to the first preset threshold, where the number of candidate results reflects the structured query language query For the data size of the target table, the first preset threshold is a dangerous threshold, which can be flexibly set during specific implementation; if the number of candidate results is greater than or equal to the first preset threshold, it means that the current structured query statement is very likely to produce slow queries. It can be determined that the preset parameters, that is, the query type and the number of candidate results meet the preset slow query early warning conditions, thereby realizing accurate early warning of slow queries.

[0093] Of course, other preset parameters and their corresponding slow query pre-warning conditions can also be set during specific implementation. For example, when the preset parameter is the sorting mode, if the sorting mode in the execution plan is the preset sorting mode, the preset sorting mode is satisfied. Slow query early warning conditions can also be used for slow query early warning.

[0094] When the preset parameters meet the preset slow query early warning conditions, step S40 is executed to add the corresponding structured query sentence to the preset early warning list.

[0095] In this step, when the preset parameters meet the preset slow query early warning conditions, the corresponding structured query sentence is added to the preset early warning list.

[0096] Further, after step S40, it may further include: sending the warning list to the front-end page for display. This is convenient for operation and maintenance personnel to make timely corresponding slow query optimization processing.

[0097] Compared with the prior art method of optimizing the slow query after the slow query is formed, this embodiment realizes that before the slow query is formed, an early warning is given to the sentence that may form a slow query. At this time, the general data size of the target table is It is small and has not yet been developed into a large table, so it can be optimized at a lower cost; in addition, this embodiment only needs to analyze the execution plan of the statement, and does not collect any parameters in the production database, and does not affect the performance of the production database. Therefore, this embodiment realizes the reduction of the optimization cost of slow queries without affecting the performance of the production database.

[0098] Further, based on the first embodiment of the sentence warning method of the present invention, a second embodiment of the sentence warning method of the present invention is proposed.

[0099] In this embodiment, after the step of judging whether the number of candidate results is greater than or equal to a first preset threshold, it may further include:

[0100] If the number of candidate results is less than the first preset threshold, determining whether the number of candidate results is greater than a second preset threshold;

[0101] If the number of candidate results is greater than the second preset threshold, analyze the first historical change trend of the number of candidate results corresponding to the structured query sentence; determine the number of candidate results currently extracted from the execution plan Whether it falls within the first preset interval corresponding to the first historical change trend, and if so, determining that the preset parameter satisfies a preset slow query early warning condition;

[0102] Alternatively, calculate the first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical time period; determine whether the first incremental value is greater than or equal to the first preset increase If yes, it is determined that the preset parameter meets the preset slow query early warning condition.

[0103] Specifically, if the number of candidate results is less than the first preset threshold, it is determined whether it is greater than the second preset threshold. If it is, it is considered that there is a certain probability that a slow query will be generated. At this time, the following two methods can be used to further determine whether it needs to be performed Early warning:

[0104] (1) Analyze the first historical change trend of the number of candidate results corresponding to the structured query sentence. In specific implementation, the number of candidate results corresponding to the current structured query sentence stored in the historical record can be extracted, and then used mathematically The Chebyshev theorem of, to perform trend analysis to determine whether the number of candidate results currently extracted from the execution plan falls within the first preset interval corresponding to the first historical change trend, and if so, the corresponding The sentence has a higher probability of causing a slow query, and at this time, it is determined that the preset parameter meets the preset slow query early warning condition.

[0105] Among them, the general idea of ​​Chebyshev's theorem is: in any data set, the proportion (or part) within m standard deviations of its mean is always at least 1-1/m 2 , Where m is any positive number greater than 1. For m=2, m=3 and m=5, the results are as follows: in all the data, at least 3/4 (or 75%) of the data are within 2 standard deviations of the average. Of all the data, at least 8/9 (or 88.9%) of the data are within 3 standard deviations of the mean. Of all the data, at least 24/25 (or 96%) of the data are within 5 standard deviations of the mean.

[0106] In this embodiment, the above-mentioned first preset interval can be flexibly set. For example, when the number of candidate results currently extracted from the execution plan is greater than the mean of the number of historical candidate results plus twice the standard deviation, it can be determined The preset parameters satisfy preset slow query early warning conditions.

[0107] (2) Calculate the first incremental value of the number of candidate results currently extracted from the execution plan within the first preset historical duration; determine whether the first incremental value is greater than or equal to the first preset The increment value, if it is, indicates that the corresponding sentence has a greater probability of causing a slow query. At this time, it is determined that the preset parameter meets the preset slow query early warning condition.

[0108] Wherein, the first preset history duration and the first increment value can be flexibly set, for example, if the number of candidate results extracted from the execution plan is currently the incremental value of the number of candidate results in the previous month If it is greater than 10,000, it can be determined that the preset parameters meet the preset slow query early warning conditions.

[0109] This embodiment analyzes the current number of candidate results in combination with the number of historical candidate results, and then determines whether the preset slow query early warning conditions are met, and realizes further early warning analysis of sentences that may generate slow queries, and improves the comprehensiveness of slow query early warning. Sex.

[0110] Further, based on the first embodiment of the sentence warning method of the present invention, a third embodiment of the sentence warning method of the present invention is proposed.

[0111] In this embodiment, after the step of clustering the structured query sentence, it may further include:

[0112] Count the execution times of the structured query statements under each type, and analyze the second historical change trend of the execution times of the structured query statements under each type; determine whether the counted execution times fall with the second historical change In the second preset interval corresponding to the trend, if yes, add the corresponding structured query sentence to the warning list;

[0113] Or, calculate the second increment value of the counted execution times within the second preset historical duration; determine whether the second increment value is greater than or equal to the second preset increment value, and if so, the corresponding The structured query sentence is added to the warning list.

[0114] Specifically, after clustering the obtained structured query statements, the following two methods can be used to filter out the statements that require warning:

[0115] (1) Count the number of executions of structured query statements under each type. For example, the number of executions of structured query statements under the query name type statement is 1000 times, and the number of executions of structured query statements under query age type statement. It is 500 times; then analyze the second historical change trend of the execution times of structured query statements under each type. In specific implementation, you can extract the execution times of structured query statements under each type stored in the historical record, and then use The mathematical Chebyshev theorem performs trend analysis to determine whether the currently counted execution times fall within the second preset interval corresponding to the second historical change trend. If so, it means that the corresponding sentence has a larger A slow query is likely to occur, and at this time, the corresponding structured query sentence is added to the warning list.

[0116] In this embodiment, the aforementioned second preset interval can be flexibly set. For example, when the current count of execution times is greater than the average of the historical execution times plus twice the standard deviation, the corresponding structured query sentence may be added to the warning List.

[0117] (2) Calculate the statistical second increment value of the number of executions within the second preset historical duration; determine whether the second increment value is greater than or equal to the second preset increment value, and if so, indicate the corresponding There is a greater probability of slow query, and the corresponding structured query sentence is added to the warning list.

[0118] Among them, the second preset historical duration and the second increment value can be flexibly set. For example, if the increment value of the current count of execution times relative to the execution times of the previous month is greater than 10,000, the corresponding structured query The sentence is added to the warning list.

[0119] This embodiment analyzes the current statement execution times in combination with the historical statement execution times, and then determines whether the preset slow query warning conditions are met, realizes further warning analysis of the sentences that may generate slow queries, and improves the comprehensiveness of the slow query warning. Sex.

[0120] Further, based on the first, second, and third embodiments of the sentence warning method of the present invention, a fourth embodiment of the sentence warning method of the present invention is proposed.

[0121] In this embodiment, the preset parameters related to the slow query include a sorting mode. After corresponding to the above step S40, it may further include: obtaining the sorting mode of the structured query sentences in the alert list; judging the obtained sorting mode Whether the mode is a preset sorting mode; if the obtained sorting mode is a preset sorting mode, the prompt information for checking the sorting mode is added to the alert list.

[0122] In this embodiment, considering that some sorting modes have low sorting efficiency, there is a greater probability of slow queries. Therefore, for structured query sentences that have been added to the alert list, the sorting mode can be further obtained if the sorting mode If it is the preset sorting mode, add a description in the alert list that the sorting mode needs to be viewed to prompt the operation and maintenance personnel to optimize the sorting mode. Among them, the preset sorting mode includes but not limited to "Using filesort" (file sorting), "Usingtemprorary" (temporary table), etc. In this way, the sorting efficiency can be improved.

[0123] The invention also provides a sentence early warning device. The sentence warning device includes:

[0124] The first obtaining module is used to obtain the structured query statement for the application program to access the preset production database;

[0125] The extraction module is used to obtain the execution plan of the structured query statement, and extract the preset parameters related to the slow query from the execution plan;

[0126] The first judgment module is used to judge whether the extracted preset parameters meet the preset slow query early warning conditions;

[0127] The adding module is used to add the corresponding structured query sentence to the preset early warning list when the preset parameters meet the preset slow query early warning conditions.

[0128] Further, the extraction module further includes:

[0129] The clustering unit is configured to perform clustering processing on the structured query statements, wherein the execution plans corresponding to the structured query statements of the same type are the same;

[0130] The selection unit is used to select a target structured query statement from various types of structured query statements;

[0131] The access unit is configured to access the disaster tolerance database of the production database according to the target structured query statement to obtain execution plans corresponding to various types of structured query statements.

[0132] Further, the preset parameters include the query type and the number of candidate results, and the first judgment module is further configured to:

[0133] Determine whether the query type is a preset query type;

[0134] If the query type is a preset query type, determining whether the number of candidate results is greater than or equal to a first preset threshold;

[0135] If the number of candidate results is greater than or equal to the first preset threshold, it is determined that the preset parameters meet a preset slow query early warning condition.

[0136] Further, the first judgment module is also used for:

[0137] If the number of candidate results is less than the first preset threshold, determining whether the number of candidate results is greater than a second preset threshold;

[0138] If the number of candidate results is greater than a second preset threshold, analyzing the first historical change trend of the number of candidate results corresponding to the structured query sentence;

[0139] Determine whether the number of candidate results currently extracted from the execution plan falls within the first preset interval corresponding to the first historical change trend, and if so, determine that the preset parameters meet the preset slowness Query early warning conditions;

[0140] Or, calculating the first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical time period;

[0141] It is determined whether the first increment value is greater than or equal to a first preset increment value, and if so, it is determined that the preset parameter satisfies a preset slow query early warning condition.

[0142] Further, the sentence warning device further includes:

[0143] The statistical analysis module is used to separately count the execution times of the structured query statements under each type, and analyze the second historical change trend of the execution times of the structured query statements under each type;

[0144] The first judgment module is also used to judge whether the counted execution times fall within a second preset interval corresponding to the second historical change trend, and if so, add the corresponding structured query sentence to all In the list of warnings;

[0145] Or, calculate the second increment value of the counted execution times within the second preset historical duration; determine whether the second increment value is greater than or equal to the second preset increment value, and if so, the corresponding The structured query sentence is added to the warning list.

[0146] Further, the preset parameters include a sorting mode, and the sentence warning device further includes:

[0147] The second obtaining module is used to obtain the sorting mode corresponding to the structured query sentence in the warning list;

[0148] The second judging module is used to judge whether the acquired sorting mode is a preset sorting mode; and the adding module is used to add in the early warning list if the acquired sorting mode is a preset sorting mode Add a reminder to view the sort mode every day.

[0149] Further, the sentence warning device further includes:

[0150] The display module is used to send the warning list to the front-end page for display.

[0151] The present invention also provides a computer-readable storage medium.

[0152] A sentence warning program is stored on the computer readable storage medium of the present invention, and when the sentence warning program is executed by a processor, the steps of the sentence warning method described above are realized.

[0153] The method implemented when the sentence warning program running on the processor is executed can refer to the various embodiments of the sentence warning method of the present invention, which will not be repeated here.

[0154] It should be noted that in this article, the terms "including", "including" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or system including a series of elements not only includes those elements, It also includes other elements that are not explicitly listed, or elements inherent to the process, method, article, or system. If there are no more restrictions, the element defined by the sentence "including a..." does not exclude the existence of other identical elements in the process, method, article or system that includes the element.

[0155] The sequence numbers of the foregoing embodiments of the present invention are only for description, and do not represent the superiority of the embodiments.

[0156] Through the description of the above embodiments, those skilled in the art can clearly understand that the method of the above embodiments can be implemented by means of software plus the necessary general hardware platform. Of course, it can also be implemented by hardware, but in many cases the former is better. 的实施方式。 Based on this understanding, the technical solution of the present invention essentially or the part that contributes to the existing technology can be embodied in the form of a software product, and the computer software product is stored in a storage medium (such as ROM/RAM) as described above. , Magnetic disk, optical disk), including several instructions to make a terminal device (can be a mobile phone, computer, server, air conditioner, or network device, etc.) execute the method described in each embodiment of the present invention.

[0157] The above are only the preferred embodiments of the present invention, and do not limit the scope of the present invention. Any equivalent structure or equivalent process transformation made using the content of the description and drawings of the present invention, or directly or indirectly applied to other related technical fields , The same reason is included in the scope of patent protection of the present invention.