Sentence warning method, device, equipment and computer-readable storage medium

Abstract

The present invention discloses a statement early warning method, which includes: obtaining a structured query statement for an application to access a preset production database; obtaining an execution plan for the structured query statement, and extracting preset parameters related to slow queries from the execution plan; judging whether the extracted preset parameters meet preset slow query early warning conditions; when the preset parameters meet the preset slow query early warning conditions, adding the corresponding structured query statement to a preset early warning list. The present invention also discloses a statement early warning device, equipment, and a computer-readable storage medium. The present invention can reduce the optimization cost of slow queries without affecting the performance of the production database.

Description

Technical Field

[0001] The present invention relates to the technical field of financial technology (Fintech), and in particular to a statement warning method, device, equipment and computer-readable storage medium. Background Art

[0002] With the continuous development of Fintech, especially Internet technology finance, more and more technologies are being applied in the financial field. MySQL is one of the most popular relational database management systems. The SQL (Structured Query Language) used by MySQL is the most commonly used standardized language for accessing databases. In the process of using SQL language to query the database, the phenomenon that the query response time exceeds the specified time is called slow query. Slow query will affect the performance of the database and application system, and in serious cases, it will affect the availability of the application system.

[0003] At present, in order to eliminate or reduce slow queries, monitoring logic is generally deployed in the production database to monitor whether slow queries occur. After slow queries are found, slow query statements are extracted and optimized. The defects of this method are: 1) When an exception is monitored, the slow query in the production database has already been formed. The formed slow query often means that the data scale of the target table of the query has grown relatively large, forming a large table. When the optimization plan involves adding indexes, adding fields, partitioning, etc. to the large table, it will be accompanied by a long production database table lock process. During this period, the business that has write operations on the large table cannot be carried out normally, which leads to a high optimization cost for slow queries. 2) The monitoring logic has query operations on the production database, which objectively increases the load on the production database. In addition, the monitoring logic and the production database are deployed on the same physical resources, which will also cause certain resource contention and affect the performance of the production database. Summary of the invention

[0004] The main purpose of the present invention is to propose a statement warning method, device, equipment and computer-readable storage medium, aiming to reduce the optimization cost of slow queries without affecting the performance of production databases.

[0005] To achieve the above object, the present invention provides a sentence early warning method, which comprises the following steps:

[0006] Obtain the structured query statements used by the application to access the preset production database;

[0007] Obtaining an execution plan of the structured query statement, and extracting preset parameters related to slow queries from the execution plan;

[0008] Determine whether the extracted preset parameters meet the preset slow query warning conditions;

[0009] When the preset parameters meet the preset slow query warning conditions, the corresponding structured query statement is added to the preset warning list.

[0010] Preferably, the step of obtaining the execution plan of the structured query statement includes:

[0011] Clustering the structured query statements, wherein the execution plans of structured query statements of the same type are the same;

[0012] Select a target structured query statement from the structured query statements of each type;

[0013] The disaster recovery database of the production database is accessed according to the target structured query statement to obtain an execution plan of the structured query statement of the corresponding type.

[0014] Preferably, the preset parameters include a query type and a number of candidate results, and the step of determining whether the extracted preset parameters meet a preset slow query warning condition includes:

[0015] Determining whether the query type is a preset query type;

[0016] 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;

[0017] If the number of candidate results is greater than or equal to a first preset threshold, it is determined that the preset parameter meets a preset slow query warning condition.

[0018] Preferably, after the step of determining whether the number of candidate results is greater than or equal to a first preset threshold, the method further comprises:

[0019] 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;

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

[0021] Determine whether the number of candidate results currently extracted from the execution plan falls within a first preset interval corresponding to the first historical change trend, and if so, determine that the preset parameter meets a preset slow query warning condition;

[0022] Alternatively, a first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical duration is calculated;

[0023] It is determined whether the first increment value is greater than or equal to a first preset increment value. If so, it is determined that the preset parameter meets the preset slow query warning condition.

[0024] Preferably, after the step of clustering the structured query statements, the method further includes:

[0025] Counting the number of executions of structured query statements of each type respectively, and analyzing the second historical change trend of the number of executions of structured query statements of each type;

[0026] Determine whether the statistical execution times fall within a second preset interval corresponding to the second historical change trend, and if so, add the corresponding structured query statement to the warning list;

[0027] Alternatively, a second incremental value of the statistical execution times within a second preset historical time period is calculated;

[0028] Determine whether the second increment value is greater than or equal to a second preset increment value, and if so, add the corresponding structured query statement to the warning list.

[0029] Preferably, the preset parameters include a sorting mode, and after the step of adding the corresponding structured query statement to the preset warning list, the method further includes:

[0030] Obtaining a sorting mode of the structured query statements in the warning list;

[0031] Determine whether the acquired sorting mode is a preset sorting mode;

[0032] If the acquired sorting mode is a preset sorting mode, prompt information indicating that the sorting mode needs to be reviewed is added to the warning list.

[0033] Preferably, after the step of adding the corresponding structured query statement to the preset warning list, the method further includes:

[0034] The warning list is sent to the front-end page for display.

[0035] In addition, to achieve the above-mentioned purpose, the present invention also provides a sentence warning device, the sentence warning device comprising:

[0036] A first acquisition module is used to acquire a structured query statement for an application to access a preset production database;

[0037] An extraction module, used to obtain an execution plan of the structured query statement, and extract preset parameters related to the slow query from the execution plan;

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

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

[0040] Preferably, the extraction module further includes:

[0041] A clustering unit, used for clustering the structured query statements, wherein the execution plans corresponding to the structured query statements of the same type are the same;

[0042] A selection unit, used for selecting a target structured query statement from each type of structured query statement;

[0043] The access unit is used to access the disaster recovery database of the production database according to the target structured query statement to obtain an execution plan corresponding to each type of structured query statement.

[0044] Preferably, the preset parameters include the query type and the number of candidate results, and the first judgment module is further used for:

[0045] Determining whether the query type is a preset query type;

[0046] 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;

[0047] If the number of candidate results is greater than or equal to a first preset threshold, it is determined that the preset parameter meets a preset slow query warning condition.

[0048] Preferably, the first determination module is further used for:

[0049] 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;

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

[0051] Determine whether the number of candidate results currently extracted from the execution plan falls within a first preset interval corresponding to the first historical change trend, and if so, determine that the preset parameter meets a preset slow query warning condition;

[0052] Alternatively, a first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical duration is calculated;

[0053] It is determined whether the first increment value is greater than or equal to a first preset increment value. If so, it is determined that the preset parameter meets the preset slow query warning condition.

[0054] Preferably, the sentence warning device further comprises:

[0055] A statistical analysis module, used to count the number of executions of structured query statements of each type, and analyze the second historical change trend of the number of executions of structured query statements of each type;

[0056] The first judgment module is further configured to judge whether the statistical execution times fall within a second preset interval corresponding to the second historical change trend, and if so, add the corresponding structured query statement to the warning list;

[0057] Alternatively, a second incremental value of the statistical execution times within a second preset historical time period is calculated; whether the second incremental value is greater than or equal to a second preset incremental value is determined, and if so, the corresponding structured query statement is added to the warning list.

[0058] Preferably, the preset parameters include a sorting mode, and the sentence early warning device further includes:

[0059] A second acquisition module is used to acquire a sorting mode corresponding to the structured query statement in the warning list;

[0060] The second judgment module is used to judge whether the acquired sorting mode is a preset sorting mode; the adding module is used to add prompt information of the sorting mode to be reviewed in the early warning list if the acquired sorting mode is the preset sorting mode.

[0061] Preferably, the sentence warning device further comprises:

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

[0063] In addition, to achieve the above-mentioned purpose, the present invention also provides a sentence warning device, which includes: a memory, a processor, and a statement warning program stored in the memory and executable on the processor, and when the statement warning program is executed by the processor, the steps of the statement warning method described above are implemented.

[0064] In addition, to achieve the above-mentioned purpose, the present invention also provides a computer-readable storage medium, on which a statement warning program is stored, and when the statement warning program is executed by a processor, the steps of the statement warning method as described above are implemented.

[0065] The present invention obtains a structured query statement for an application to access a preset production database; obtains an execution plan for the structured query statement, extracts preset parameters related to slow queries from the execution plan; determines whether the extracted preset parameters meet preset slow query warning conditions; when the preset parameters meet the preset slow query warning conditions, adds the corresponding structured query statement to a preset warning list. Compared with the prior art method of optimizing slow queries after the slow queries are formed, the present invention realizes warning of statements that may form slow queries before the slow queries are formed. At this time, the target table generally has a small data scale and has not yet developed into a large table, so the optimization can be achieved at a lower cost; in addition, the present invention only needs to analyze the execution plan of the statement, and will not perform any parameter collection in the production database, and will not affect the performance of the production database. Therefore, the present invention reduces the optimization cost of slow queries without affecting the performance of the production database. BRIEF DESCRIPTION OF THE DRAWINGS

[0066] Figure 1 It is a schematic diagram of the device structure of the hardware operating environment involved in the embodiment of the present invention;

[0067] Figure 2 It is a flowchart diagram of the first embodiment of the sentence warning method of the present invention.

[0068] The realization of the purpose, functional features and advantages of the present invention will be further explained in conjunction with embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

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

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

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

[0072] like Figure 1As shown, the statement 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 realize the connection and communication between these components. The user interface 1003 may include a display screen (Display), 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 it may be a stable memory (non-volatile memory), such as a disk memory. The memory 1005 may also be a storage device independent of the aforementioned processor 1001.

[0073] Those skilled in the art will understand that Figure 1 The device structure shown in the figure does not constitute a limitation of the device, and may include more or less components than shown in the figure, or combine certain components, or arrange the components differently.

[0074] like 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 statement warning program.

[0075] exist Figure 1 In the terminal shown, the network interface 1004 is mainly used to connect to the background server and communicate data with the background server; the user interface 1003 is mainly used to connect to the client (user end) and communicate data with the client; and the processor 1001 can be used to call the statement warning program stored in the memory 1005, and execute the operations in each embodiment of the following statement warning method.

[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 This is a flow chart of a first embodiment of a sentence warning method of the present invention, the method comprising:

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

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

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

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

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

[0083] In this step, the early warning server obtains the execution plan of the above-mentioned structured query statement, and then extracts the preset parameters related to the slow query from the execution plan. Among them, the execution plan refers to the plan for the physical execution of the structured query statement in the database, which is composed of several operations, including full table scan, use of indexes, nested loops, table joins, etc. 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 response speed of the statement can be improved by optimizing the execution plan to eliminate slow queries. The preset parameters related to slow queries are parameters that may affect the query speed and query efficiency in practice, including but not limited to query type, number of candidate results, sorting mode, etc., which can be flexibly set according to the slow query failure experience in actual production during specific implementation.

[0084] In one embodiment, the step of obtaining the execution plan of the structured query statement may include: clustering the structured query statements, wherein the execution plans of structured query statements of the same type are the same; selecting a target structured query statement from the structured query statements of each type; and accessing the disaster recovery database of the production database according to the target structured query statement to obtain the execution plan of the structured query statement of the corresponding type.

[0085] Specifically, the acquired structured query statements can be first clustered, wherein the execution plans of structured query statements of the same type are the same, and specific clustering rules can be flexibly set according to actual business conditions, including but not limited to classifying statements containing name query fields into one category, and classifying statements containing age query fields into another category, etc.

[0086] Then, a target structured query statement is selected from the structured query statements under each type, wherein the selection is random selection, or the statement that is ranked first is selected; thereafter, the disaster recovery database of the production data is accessed according to the selected target structured query statement, wherein the disaster recovery database is a database deployed in a different location and is exactly the same as the production database. When a natural disaster occurs in the geographical location where the production database is located, causing a disaster in the production database, the disaster recovery database will immediately take over the work. When the disaster recovery 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. Since the execution plans of structured query statements under the same type are the same, the early warning device can obtain the execution plans of all structured query statements under each type at this time.

[0087] The above method of obtaining the execution plan after clustering the structured query statements realizes batch acquisition of the execution plan and improves the efficiency of obtaining the execution plan. Obtaining the execution plan from the disaster recovery database of the production database will not occupy the resources of the production database, and thus 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 statements may not be clustered, but the execution plan corresponding to each structured query statement may be obtained separately, and then the preset parameters related to the slow query may be extracted from the obtained execution plan; in addition, if a disaster recovery database is not deployed in practice, the execution plan of the structured query statement may be directly obtained from the production database, which will occupy some resources of the production database. The specific implementation plan can be flexibly selected according to the actual situation.

[0089] Step S30, determining whether the extracted preset parameters meet the preset slow query 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 warning conditions.

[0091] Among them, the preset parameters may include a 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 warning conditions includes: determining whether the query type is a preset query type; 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; if the number of candidate results is greater than or equal to the first preset threshold, determining that the preset parameters meet the preset slow query warning conditions.

[0092] In specific implementation, the query type corresponding to the statement with a high probability of generating slow queries in actual experience can be used as the preset query type. For example, according to actual production experience, the statements corresponding to these query types, such as "ALL" (full table scan), "range" (scan in the form of range), and "index" (scan in index order, read the index first, and then read the actual row), have a high probability of generating slow queries, so these query types can be used as preset query types. When the extracted query type is "ALL", "range" or "index", it is further determined whether the number of extracted candidate results is greater than or equal to the first preset threshold, wherein the number of candidate results reflects the data scale of the target table queried by the structured query language, and the first preset threshold is a dangerous threshold, which can be flexibly set in 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 generate a slow query. At this time, it can be determined that the preset parameters, i.e., the query type and the number of candidate results meet the preset slow query warning conditions, thereby achieving accurate warning of slow queries.

[0093] Of course, other preset parameters and their corresponding slow query warning conditions can also be set during the specific implementation. For example, when the preset parameter is a sorting mode, if the sorting mode in the execution plan is the preset sorting mode, the preset slow query warning condition is met and a slow query warning can also be issued.

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

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

[0096] Furthermore, after step S40, the method may further include: sending the warning list to a front-end page for display, so that operation and maintenance personnel can make corresponding slow query optimization processing in a timely manner.

[0097] Compared with the prior art method of optimizing slow queries after they are formed, this embodiment implements early warning of statements that may form slow queries before they are formed. At this time, the target table generally has a small data scale and has not yet developed into a large table, so optimization can be achieved at a lower cost. In addition, this embodiment only needs to analyze the execution plan of the statement, and will not collect any parameters in the production database, which does not affect the performance of the production database. Therefore, this embodiment reduces the optimization cost of slow queries without affecting the performance of the production database.

[0098] Furthermore, 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 determining whether the number of candidate results is greater than or equal to the first preset threshold, the following steps may also be included:

[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 a second preset threshold, analyzing a first historical change trend of the number of candidate results corresponding to the structured query statement; determining whether the number of candidate results currently extracted from the execution plan falls within a first preset interval corresponding to the first historical change trend; and if so, determining that the preset parameter meets a preset slow query warning condition;

[0102] Alternatively, calculate a first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical time length; determine whether the first incremental value is greater than or equal to a first preset incremental value, and if so, determine that the preset parameters meet the preset slow query warning conditions.

[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 so, it is considered that there is a certain probability of slow query. At this time, the following two methods can be used to further determine whether an early warning is needed:

[0104] (1) Analyze the first historical change trend of the number of candidate results corresponding to the structured query statement. In specific implementation, the number of candidate results corresponding to the current structured query statement pre-saved in the historical record can be extracted, and then the trend analysis is performed using the mathematical Chebyshev theorem to determine whether the number of candidate results currently extracted from the execution plan falls within a first preset interval corresponding to the first historical change trend. If so, it means that the corresponding statement has a greater probability of generating a slow query. At this time, it is determined that the preset parameters meet the preset slow query warning conditions.

[0105] The Chebyshev theorem states that the proportion (or portion) of any data set that is 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 following results are obtained: At least 3 / 4 (or 75%) of all data are within 2 standard deviations of the mean. At least 8 / 9 (or 88.9%) of all data are within 3 standard deviations of the mean. At least 24 / 25 (or 96%) of all data are within 5 standard deviations of the mean.

[0106] In this embodiment, the above-mentioned first preset interval can be set flexibly. 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 two standard deviations, it can be determined that the preset parameters meet the preset slow query warning conditions.

[0107] (2) Calculate a first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical time length; determine whether the first incremental value is greater than or equal to the first preset incremental value. If so, it means that the corresponding statement has a greater probability of generating a slow query. At this time, it is determined that the preset parameters meet the preset slow query warning conditions.

[0108] Among them, the first preset historical duration and the first incremental value can be flexibly set. For example, if the incremental value of the number of candidate results currently extracted from the execution plan relative to the average number of candidate results last month is greater than 10,000, it can be determined that the preset parameters meet the preset slow query 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 warning conditions are met, thereby achieving further warning analysis of statements that may generate slow queries and improving the comprehensiveness of slow query warnings.

[0110] Furthermore, 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 statements, the following steps may also be included:

[0112] Counting the number of executions of structured query statements of each type respectively, and analyzing the second historical change trend of the number of executions of structured query statements of each type; judging whether the statistical number of executions falls within a second preset interval corresponding to the second historical change trend, and if so, adding the corresponding structured query statement to the warning list;

[0113] Alternatively, a second incremental value of the statistical execution times within a second preset historical time period is calculated; whether the second incremental value is greater than or equal to a second preset incremental value is determined, and if so, the corresponding structured query statement is added to the warning list.

[0114] Specifically, after clustering the obtained structured query statements, the statements that require warnings can be screened out in the following two ways:

[0115] (1) Count the execution times of structured query statements of each type respectively, for example, the execution times of structured query statements of name type is 1000 times, and the execution times of structured query statements of age type is 500 times; then analyze the second historical change trend of the execution times of structured query statements of each type. In specific implementation, the execution times of structured query statements of each type pre-saved in the historical records can be extracted, and then the trend analysis can be performed using the mathematical Chebyshev theorem 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 statement has a greater probability of generating a slow query, and the corresponding structured query statement is added to the warning list.

[0116] In this embodiment, the second preset interval can be flexibly set. For example, when the current statistical execution times are greater than the mean of the historical execution times plus two standard deviations, the corresponding structured query statement can be added to the warning list.

[0117] (2) Calculate a second incremental value of the statistical execution count within a second preset historical time period; determine whether the second incremental value is greater than or equal to the second preset incremental value. If so, it means that the corresponding statement has a greater probability of generating a slow query. At this time, the corresponding structured query statement is added to the warning list.

[0118] Among them, the second preset historical duration and the second incremental value can be flexibly set. For example, if the incremental value of the current statistical execution times relative to the execution times last month is greater than 10,000, the corresponding structured query statement can be 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, thereby achieving further warning analysis of statements that may generate slow queries and improving the comprehensiveness of slow query warnings.

[0120] Furthermore, 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 slow queries include a sorting mode, which may further include: obtaining the sorting mode of the structured query statement in the warning list; determining whether the obtained sorting mode is a preset sorting mode; if the obtained sorting mode is a preset sorting mode, adding prompt information that the sorting mode needs to be reviewed in the warning list after corresponding to the above step S40.

[0122] In this embodiment, considering that some sorting modes have low sorting efficiency and a high probability of generating slow queries, the sorting mode of the structured query statements that have been added to the warning list can be further obtained. If the sorting mode is a preset sorting mode, an additional description is added to the warning list to indicate that the sorting mode needs to be reviewed to prompt the operation and maintenance personnel to optimize the sorting mode. Among them, the preset sorting modes include but are not limited to "Using filesort" (file sorting), "Usingtemporary" (temporary table), etc. In this way, the sorting efficiency can be improved.

[0123] The present invention also provides a sentence warning device. The sentence warning device comprises:

[0124] A first acquisition module is used to acquire a structured query statement for an application to access a preset production database;

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

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

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

[0128] Furthermore, the extraction module also includes:

[0129] A clustering unit, used for clustering the structured query statements, wherein the execution plans corresponding to the structured query statements of the same type are the same;

[0130] A selection unit, used for selecting a target structured query statement from each type of structured query statement;

[0131] The access unit is used to access the disaster recovery database of the production database according to the target structured query statement to obtain an execution plan corresponding to each type of structured query statement.

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

[0133] Determining 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 a first preset threshold, it is determined that the preset parameter meets a preset slow query warning condition.

[0136] Furthermore, the first determination 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 a first historical change trend of the number of candidate results corresponding to the structured query statement;

[0139] Determine whether the number of candidate results currently extracted from the execution plan falls within a first preset interval corresponding to the first historical change trend, and if so, determine that the preset parameter meets a preset slow query warning condition;

[0140] Alternatively, a first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical duration is calculated;

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

[0142] Furthermore, the sentence warning device also includes:

[0143] A statistical analysis module, used to count the number of executions of structured query statements of each type, and analyze the second historical change trend of the number of executions of structured query statements of each type;

[0144] The first judgment module is further configured to judge whether the statistical execution times fall within a second preset interval corresponding to the second historical change trend, and if so, add the corresponding structured query statement to the warning list;

[0145] Alternatively, a second incremental value of the statistical execution times within a second preset historical time period is calculated; whether the second incremental value is greater than or equal to a second preset incremental value is determined, and if so, the corresponding structured query statement is added to the warning list.

[0146] Furthermore, the preset parameters include a sorting mode, and the statement early warning device further includes:

[0147] A second acquisition module is used to acquire a sorting mode corresponding to the structured query statement in the warning list;

[0148] The second judgment module is used to judge whether the acquired sorting mode is a preset sorting mode; the adding module is used to add prompt information of the sorting mode to be reviewed in the early warning list if the acquired sorting mode is the preset sorting mode.

[0149] Furthermore, the sentence warning device also 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] The computer-readable storage medium of the present invention stores a statement warning program, and when the statement warning program is executed by a processor, the steps of the statement warning method described above are implemented.

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

[0154] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or system including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or system. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or system including the element.

[0155] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0156] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present invention.

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

Claims

1. A sentence warning method, characterized in that: The sentence early warning method comprises the following steps: Obtain structured query statements used by applications to access production databases; Clustering the structured query statements, wherein the execution plans of structured query statements of the same type are the same; Select a target structured query statement from each type of structured query statement; access the disaster recovery database of the production database according to the target structured query statement to obtain an execution plan of the structured query statement of the corresponding type, and extract preset parameters related to slow queries from the execution plan; determine whether the extracted preset parameters meet the preset slow query warning conditions; When the preset parameters meet the preset slow query warning conditions, the corresponding structured query statement is added to the preset warning list; The execution times of structured query statements under each type are counted respectively, and the second historical change trend of the execution times of structured query statements under each type is analyzed; it is determined whether the counted execution times fall within a second preset interval corresponding to the second historical change trend, and if so, the corresponding structured query statement is added to the warning list.

2. The sentence early warning method according to claim 1, characterized in that: The preset parameters include a query type and a number of candidate results, and the step of determining whether the extracted preset parameters meet a preset slow query warning condition includes: Determining whether the query type is a preset query type; 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; If the number of candidate results is greater than or equal to a first preset threshold, it is determined that the preset parameter meets a preset slow query warning condition.

3. The sentence early warning method according to claim 2, characterized in that: After the step of determining whether the number of candidate results is greater than or equal to the first preset threshold, the method further includes: 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; If the number of candidate results is greater than a second preset threshold, analyzing a first historical change trend of the number of candidate results corresponding to the structured query statement; Determine whether the number of candidate results currently extracted from the execution plan falls within a first preset interval corresponding to the first historical change trend, and if so, determine that the preset parameter meets a preset slow query warning condition; Alternatively, a first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical duration is calculated; It is determined whether the first increment value is greater than or equal to a first preset increment value. If so, it is determined that the preset parameter meets the preset slow query warning condition.

4. The sentence warning method according to claim 1, characterized in that: After the step of analyzing the second historical change trend of the number of executions of structured query statements of each type, the method further includes: Calculate a second incremental value of the statistical execution times within a second preset historical time period; It is determined whether the second increment value is greater than or equal to a second preset increment value. If so, the corresponding structured query statement is added to the warning list.

5. The sentence warning method according to any one of claims 1 to 4, characterized in that: The preset parameters include a sorting mode, and after the step of adding the corresponding structured query statement to the preset warning list, the method further includes: Obtaining a sorting mode of the structured query statements in the warning list; Determine whether the acquired sorting mode is a preset sorting mode; If the acquired sorting mode is a preset sorting mode, prompt information indicating that the sorting mode needs to be reviewed is added to the warning list.

6. The sentence warning method according to any one of claims 1 to 4, characterized in that: After the step of adding the corresponding structured query statement to the preset warning list, the method further includes: The warning list is sent to the front-end page for display.

7. A sentence warning device, characterized in that: The sentence early warning device comprises: A first acquisition module is used to acquire a structured query statement for an application to access a production database; A clustering unit, used for clustering the structured query statements, wherein the execution plans corresponding to the structured query statements of the same type are the same; A selection unit, used for selecting a target structured query statement from each type of structured query statement; An access unit, configured to access the disaster recovery database of the production database according to the target structured query statement to obtain an execution plan of the structured query statement of the corresponding type, and extract preset parameters related to the slow query from the execution plan; A first judgment module is used to judge whether the extracted preset parameters meet the preset slow query warning conditions; An adding module, used for adding the corresponding structured query statement to a preset warning list when the preset parameters meet the preset slow query warning conditions; A statistical analysis module, used to count the number of executions of structured query statements of each type, and analyze the second historical change trend of the number of executions of structured query statements of each type; The first judgment module is further used to judge whether the statistical execution times fall within a second preset interval corresponding to the second historical change trend. If so, the corresponding structured query statement is added to the warning list.

8. The sentence warning device according to claim 7, characterized in that: The preset parameters include the query type and the number of candidate results, and the first judgment module is further used to: Determining whether the query type is a preset query type; 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; If the number of candidate results is greater than or equal to a first preset threshold, it is determined that the preset parameter meets a preset slow query warning condition.

9. The sentence warning device according to claim 8, characterized in that: The first determination module is also used for: 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; If the number of candidate results is greater than a second preset threshold, analyzing a first historical change trend of the number of candidate results corresponding to the structured query statement; Determine whether the number of candidate results currently extracted from the execution plan falls within a first preset interval corresponding to the first historical change trend, and if so, determine that the preset parameter meets a preset slow query warning condition; Alternatively, a first incremental value of the number of candidate results currently extracted from the execution plan within a first preset historical duration is calculated; It is determined whether the first increment value is greater than or equal to a first preset increment value. If so, it is determined that the preset parameter meets the preset slow query warning condition.

10. The sentence warning device according to claim 7, characterized in that: The first judgment module is further used for: Calculate a second incremental value of the statistical execution times within a second preset historical time period; It is determined whether the second increment value is greater than or equal to a second preset increment value. If so, the corresponding structured query statement is added to the warning list.

11. The sentence warning device according to any one of claims 7 to 10, characterized in that: The preset parameters include a sorting mode, and the sentence early warning device also includes: A second acquisition module is used to acquire a sorting mode corresponding to the structured query statement in the warning list; A second judgment module is used to judge whether the acquired sorting mode is a preset sorting mode; An adding module is used to add prompt information of the sorting mode to be reviewed in the warning list if the acquired sorting mode is a preset sorting mode.

12. A sentence warning device, characterized in that: The device includes: a memory, a processor, and a statement warning program stored in the memory and executable on the processor. When the statement warning program is executed by the processor, the steps of the statement warning method as described in any one of claims 1 to 6 are implemented.

13. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a sentence warning program, and when the sentence warning program is executed by the processor, the steps of the sentence warning method according to any one of claims 1 to 6 are implemented.

Images