CN114185919B - Slow query alarm method, electronic equipment and storage medium - Google Patents
Slow query alarm method, electronic equipment and storage medium Download PDFInfo
- Publication number
- CN114185919B CN114185919B CN202111304394.6A CN202111304394A CN114185919B CN 114185919 B CN114185919 B CN 114185919B CN 202111304394 A CN202111304394 A CN 202111304394A CN 114185919 B CN114185919 B CN 114185919B
- Authority
- CN
- China
- Prior art keywords
- average
- date
- sql
- time length
- value
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 63
- 230000003321 amplification Effects 0.000 claims abstract description 74
- 238000003199 nucleic acid amplification method Methods 0.000 claims abstract description 74
- 238000005457 optimization Methods 0.000 claims description 61
- 238000004590 computer program Methods 0.000 claims description 10
- 238000000926 separation method Methods 0.000 claims description 10
- 238000012545 processing Methods 0.000 description 11
- 230000001360 synchronised effect Effects 0.000 description 9
- 238000010586 diagram Methods 0.000 description 8
- 238000005516 engineering process Methods 0.000 description 7
- 230000003203 everyday effect Effects 0.000 description 7
- 230000005291 magnetic effect Effects 0.000 description 5
- 230000003068 static effect Effects 0.000 description 4
- 238000004891 communication Methods 0.000 description 3
- 230000003287 optical effect Effects 0.000 description 3
- 230000008569 process Effects 0.000 description 3
- 238000004458 analytical method Methods 0.000 description 2
- 230000008859 change Effects 0.000 description 2
- 230000003993 interaction Effects 0.000 description 2
- 238000012512 characterization method Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000005294 ferromagnetic effect Effects 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 238000012544 monitoring process Methods 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- XLYOFNOQVPJJNP-UHFFFAOYSA-N water Substances O XLYOFNOQVPJJNP-UHFFFAOYSA-N 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application discloses a slow query alarm method, electronic equipment and a storage medium. The slow query alarm method comprises the following steps: executing a first average time length consumed by the first SQL sentence based on a unit time length in a first set statistical period, and determining a first amplification corresponding to the first average time length every two adjacent days; determining a first average value for the first amplification based on the determined first amplification; based on the first average value and a first average duration corresponding to the first date, and based on a first threshold value, predicting a second date corresponding to the first SQL sentence when the first SQL sentence is slow query; wherein the first threshold characterizes a critical time consuming of a slow query; the first date represents the last day of a first set statistical period; the second date is subsequent to the first date; and outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date.
Description
Technical Field
The present application relates to the field of computer technologies, and in particular, to a slow query alarm method, an electronic device, and a storage medium.
Background
With the development of computer technology, more and more technologies such as blockchain (Blockchain), big data, distributed technology, etc. are applied in the financial field, and the traditional financial industry is gradually changing to the financial technology, however, the financial technology also puts higher demands on the technology due to the requirements of security and real-time property of the financial industry. In the financial and technological field, in the running process of a relational database management system MySQL, monitoring and executing a structured query language (SQL, structured Query Language) statement, when the execution time of any SQL statement is greater than a set threshold, slowly querying the SQL statement, and outputting an alarm log or mail corresponding to the SQL statement. However, when a slow query is detected, an alarm log or mail is output, so that the service performance of the database or the processing efficiency of the business is easily affected when the slow query of the alarm is more.
Disclosure of Invention
In view of the above, the embodiments of the present application provide a method, an electronic device, and a storage medium for alarming slow query, so as to solve the technical problem that in the related art, when the number of slow queries for alarming is large, the service performance of a database is easily affected or the processing efficiency of a service is easily affected.
In order to achieve the above purpose, the technical scheme of the application is realized as follows:
The embodiment of the application provides a slow query alarm method, which comprises the following steps:
executing a first average time length consumed by the first SQL sentence based on a unit time length in a first set statistical period, and determining a first amplification corresponding to the first average time length every two adjacent days;
determining a first average value for the first amplification based on the determined first amplification;
Based on the first average value and a first average duration corresponding to the first date, and based on a first threshold value, predicting a second date corresponding to the first SQL sentence when the first SQL sentence is slow query; wherein the first threshold characterizes a critical time consuming of a slow query; the first date represents the last day of a first set statistical period; the second date is subsequent to the first date;
And outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date.
In the above scheme, the method further comprises:
determining a second average time length consumed by executing the first SQL statement in a unit time length in a second set statistical period;
determining a second average value of the second average time length in the second statistical period based on the determined second average time length;
determining the maximum value of the first value and the second value as the first threshold value; wherein,
The first numerical value represents a set multiple of the second mean value; the second value characterizes the sum of a third mean value and the second mean value; the third average value represents an average value between a second average duration corresponding to a third date and the second average value; the third date characterizes a last day of the second set statistical period.
In the above aspect, when determining the first average value regarding the first amplification or the second average value regarding the second average duration, the method includes:
Calculating the product between the set proportion and the total number of all the determined first parameters to obtain a first number; the first parameter comprises a first amplification or a second average duration; the set proportion is smaller than 0.5;
Deleting a first number of first parameters from the determined first parameters in order of magnitude; deleting a first number of first parameters from the determined first parameters according to the sequence from small to large to obtain the rest first parameters;
the average of all the first parameters remaining is calculated, resulting in a first average for the first amplification or a second average for the second average duration.
In the above solution, when determining the first average duration consumed by executing the first SQL statement in the unit duration, the method further includes:
determining a first SQL statement with the execution time length being greater than or equal to a second threshold value from SQL statements executed in unit time length in a first set statistical period; the second threshold is less than the first threshold;
and calculating the average value of the execution time lengths corresponding to all the first SQL sentences executed in the unit time length to obtain the first average time length consumed by executing the first SQL sentences in the unit time length.
In the above solution, the predicting the second date corresponding to the first SQL statement when the first SQL statement is a slow query includes:
Determining a first day from the first date when a first SQL sentence is slowly queried based on the first average time length corresponding to the first average value and the first date and a first threshold value;
And predicting a second date corresponding to the first SQL sentence when the first SQL sentence is a slow query sentence based on the first date and the first day.
In the above solution, after the predicting the second date corresponding to the first SQL statement when the first SQL statement is a slow query, the method further includes:
and outputting optimization suggestions corresponding to the early-warning first SQL statement.
In the above scheme, the optimization suggestion corresponding to the first SQL statement of the output early warning includes one of the following:
Outputting a first optimization suggestion or a second optimization suggestion under the condition that index information is not used by the first SQL statement of the early warning; the first optimization suggestions are characterized by corresponding first SQL statement newly added index information; the second optimization suggestion represents the arrangement sequence of the first index information optimization query condition contained in the table structure information based on the related data table;
Under the condition that a query condition corresponding to the first SQL statement of the early warning comprises limit and two limiting parameters exist after the limit, outputting optimization suggestions for representing offset control by adding a primary key field;
Under the condition that the first SQL statement of the early warning contains sub-queries and the scanning line number corresponding to the sub-queries is larger than a first set threshold value, outputting optimization suggestions representing modifying the sub-queries into connection queries;
Outputting optimization suggestions representing backup and clearing old data under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and at least one set condition is met;
And outputting optimization suggestions representing database separation or table separation of the data table under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and all set conditions are not met.
The embodiment of the application also provides electronic equipment, which comprises:
the first determining unit is used for executing a first average duration consumed by the SQL statement of the first structured query language based on the unit duration in the first set statistical period, and determining a first amplification corresponding to the first average duration of every two adjacent days;
A second determining unit configured to determine a first average value regarding the first amplification based on the determined first amplification;
The prediction unit is used for predicting a second date corresponding to the slow query of the first SQL sentence based on the first average duration corresponding to the first average value and the first date and based on a first threshold value; wherein the first threshold characterizes a critical time consuming of a slow query; the first date represents the last day of a first set statistical period; the second date is subsequent to the first date;
and the prompting unit is used for outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date.
The embodiment of the application also provides electronic equipment, which comprises: a processor and a memory for storing a computer program capable of running on the processor,
The processor is used for executing the steps of the slow query alarm method when running the computer program.
The embodiment of the application also provides a storage medium, on which a computer program is stored, which when being executed by a processor, realizes the steps of the method for slow query alarming.
In the embodiment of the application, based on a first average time length consumed by executing a first Structured Query Language (SQL) statement in a unit time length in a first set statistical period, determining a first amplification corresponding to the first average time length of every two adjacent days; determining a first average value for the first amplification based on the determined first amplification; based on the first average value and a first average duration corresponding to the first date, and based on a first threshold value, predicting a second date corresponding to the first SQL sentence when the first SQL sentence is slow query; and outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date. Compared with the prior art, the method and the device have the advantages that the warning is given when the slow query occurs, the change trend of the execution time of the first SQL statement in a period of time in the future can be predicted based on the first average value of the first amplification corresponding to the first SQL statement, so that the second date corresponding to the slow query when the first SQL statement is the slow query can be predicted in advance, and the slow query is early warned in advance, so that relevant personnel can determine corresponding solutions aiming at the slow query which may occur, and the influence on the service performance of a database or the processing efficiency of the service is avoided.
Drawings
FIG. 1 is a schematic diagram of an implementation flow of a slow query alarm method according to an embodiment of the present application;
FIG. 2 is a schematic diagram of an implementation flow for determining a second date in a slow query alarm method according to an embodiment of the present application;
FIG. 3 is a schematic diagram of an implementation flow for determining a first threshold in a slow query alarm method according to an embodiment of the present application;
FIG. 4 is a schematic diagram of an implementation flow for determining a first threshold in a slow query alarm method according to an embodiment of the present application;
fig. 5 is a schematic structural diagram of an electronic device according to an embodiment of the present application;
fig. 6 is a schematic diagram of a hardware composition structure of an electronic device according to an embodiment of the present application.
Detailed Description
The present application will be described in further detail with reference to the drawings and examples, in order to make the objects, technical solutions and advantages of the present application more apparent. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the application.
Fig. 1 is a schematic diagram of an implementation flow of a slow query alarm method according to an embodiment of the present application, where an execution body of the flow is an electronic device such as a terminal or a server. As shown in fig. 1, the slow query alert method includes:
Step 101: and based on the first average time length consumed by executing the first SQL sentence in the unit time length in the first set statistical period, determining a first amplification corresponding to the first average time length of every two adjacent days.
Here, each time the electronic device completes execution of one SQL statement, recording a first time consumed by executing the SQL statement, and counting the first times of executing the same SQL statement within a unit time; and calculating the average value of the first time length based on the corresponding first times in the unit time length and the first time length consumed by executing the SQL sentence every time in the unit time length, and obtaining the first average time length consumed by executing the SQL sentence in the unit time length. According to the method, a first average time length for executing the first SQL sentence in each unit time length in a first set statistical period is counted, and the first average time length for executing the first SQL sentence in each unit time length in the first set statistical period is determined based on the first average time length for executing the first SQL sentence in each unit time length in the first set statistical period; and determining a first amplification corresponding to the first average duration of every two adjacent days based on the first average duration consumed by executing the first SQL sentence every day in the first set statistical period.
Wherein the first set statistical period corresponds to a period of time preceding the current date, e.g. a month preceding the current date, i.e. the last day of the first set statistical period is preceding the current date. The same SQL statement refers to the same SQL statement after the variable parameters are culled. For example, SQL statement is select x from table where column =x and column 2=y, the variable parameters in this SQL statement are x and y, and the SQL statement after rejecting the variable parameters x and y is select x from table where column 1=and column 2=; if any two SQL statements are identical after the variable parameters are culled, then the two SQL statements are identical SQL statements.
The first average length of time consumed to execute the first SQL statement per unit length of time may be calculated as follows:
Summing the first time length consumed by executing the same SQL sentence every time in the first unit time length to obtain the sum of the first time length corresponding to the SQL sentence; counting the first times of executing the SQL sentence in a first unit time length; and determining the sum of the first time periods and the counted quotient of the first times as the average value of the first time periods corresponding to the SQL sentences in the first unit time periods to obtain the first average time periods corresponding to the SQL sentences in the first unit time periods. Wherein the first unit time length is any one unit time length. The unit time length can be hours or days, and is specifically set according to actual conditions. In practical application, the unit time length is days.
It should be noted that, under the condition that the unit time length is hour, based on the first average time length corresponding to the same SQL sentence in each unit time length every day, average value operation is performed to obtain an average value of the first average time length corresponding to the SQL sentence executed every day; and calculating a first amplification corresponding to the average value of the first average time length every two adjacent days based on the average value of the first average time length corresponding to the SQL sentence executed every day.
In practical application, the first set statistical period is one month, and the unit time length is 1 day.
It should be noted that, in practical application, the execution duration of some SQL statements is longer, and the execution duration of some SQL statements is shorter, so as to reduce errors, and accurately predict the trend of variation of the first duration consumed by executing the first SQL statement, so as to accurately early warn the slow query, in some embodiments, when determining the first average duration consumed by executing the first SQL statement in unit duration, the method further includes:
determining a first SQL statement with the execution time length being greater than or equal to a second threshold value from SQL statements executed in unit time length in a first set statistical period; the second threshold is less than the first threshold;
And calculating the average value of the execution time lengths corresponding to all the first SQL sentences executed in the unit time length to obtain the first average time length consumed by executing the first SQL sentences every day.
The electronic device determines, based on the execution duration corresponding to each SQL statement, a first SQL statement with the execution duration greater than or equal to a second threshold value from the SQL statements executed by the unit duration in the first set statistical period, and at this time, the determined execution durations of all the first SQL statements are greater than or equal to the second threshold value; and calculating the average value of the execution time lengths corresponding to all the first SQL sentences executed in the unit time length based on the determined execution time lengths corresponding to all the first SQL sentences, and obtaining a first average time length consumed by executing the first SQL sentences in the unit time length in a first set statistical period. Wherein all first SQL statements characterize the same SQL statement, i.e. the same SQL statement after the variable parameters are culled.
Wherein the second threshold characterizes critical time consuming that requires attention to the time consuming trend of the SQL statement; the second threshold is less than the first threshold and greater than half of the first threshold. The first threshold characterizes the critical time consumption of a slow query. In practical application, the second threshold may be 2M/3, M characterizing the first threshold.
In some embodiments, the SQL statement that the execution times in the unit time length is greater than the set times and the execution time length is greater than the second threshold may be further determined as a first SQL statement, and according to the above method, a first average time length consumed by executing the first SQL statement in the unit time length in the first set statistical period is calculated, and a first amplification corresponding to the first average time length of every two adjacent days is determined.
For example, when the number of times of executing any SQL statement in a unit time length is greater than a set number of times and the first time length consumed for executing the SQL statement is greater than a second threshold, the SQL statement is determined to be the first SQL statement, and at this time, a time-consuming trend of the SQL statement needs to be predicted. The electronic device can count the first SQL statement with the execution time length being greater than or equal to a second threshold value in the SQL statement executed on the day before every early morning; the first duration consumed by executing the first SQL statement each time in the unit duration can be recorded as t 1、t2、t3、……、tn; first average duration t= (T 1+t2+t3+……+tn)/n corresponding to the first SQL statement within the unit duration. And under the condition that the unit time length is a day, calculating a first amplification corresponding to the first average time consumption of every two adjacent days of the first SQL sentence based on the formula P d＝(Td-Td-1)/Td-1. P d characterizes the first amplification on day d corresponding to day d-1; t d characterizes the first average time-consuming on day d, and T d-1 characterizes the first average time-consuming on day d-1.
Step 102: based on the determined first amplification, a first average value for the first amplification is determined.
Here, the electronic device may calculate a first average value for the first amplification based on all or part of the determined first amplification. Wherein,
Calculating the first average of the first amplifications based on the determined all first amplifications means that the average of all first amplifications is calculated, and the first average of the first amplifications is obtained.
Based on the determined partial first amplifications, calculating a first average value of the first amplifications means that, from all the determined first amplifications, after deleting the lower and higher first amplifications, remaining first amplifications are obtained, and calculating the average value of all the remaining first amplifications, thereby obtaining the first average value of the first amplifications. Thereby, errors can be reduced and the accuracy of the determined first average of the first amplification can be improved.
To reduce errors in the first mean value for the first amplification, to improve accuracy of the first mean value for the first amplification, in some embodiments, in determining the first mean value for the first amplification, the method comprises:
calculating the product between the set proportion and the total number of all the determined first parameters to obtain a first number; the first parameter includes a first amplification; the set proportion is smaller than 0.5;
Deleting a first number of first parameters from the determined first parameters in order of magnitude; deleting a first number of first parameters from the determined first parameters according to the sequence from small to large to obtain the rest first parameters;
The average of all the remaining first parameters is calculated, resulting in a first average for the first amplification.
Here, the electronic device calculates a product between the set proportion and the total number of all the determined first amplifications to obtain a first number; deleting a first number of first amplifications from the determined first amplifications in order from large to small; and deleting a first number of first amplifications from the determined first amplifications in order from small to large to obtain remaining first amplifications; the average of all the first amplifications remaining is calculated, resulting in a first average for the first amplifications. In practical application, the set proportion is 5%.
Step 103: based on the first average value and a first average duration corresponding to the first date, and based on a first threshold value, predicting a second date corresponding to the first SQL sentence when the first SQL sentence is slow query; wherein the first threshold characterizes a critical time consuming of a slow query; the first date represents the last day of a first set statistical period; the second date is subsequent to the first date.
Here, the electronic device predicts a trend of change of the first average duration corresponding to the first SQL statement within a period of time after the first date based on the first average value of the first amplification corresponding to the first SQL statement and the first average duration corresponding to the first date; judging whether a first average duration greater than or equal to a first threshold exists or not based on a variation trend of the first average duration corresponding to the first SQL statement; and under the condition that the first average duration which is greater than or equal to the first threshold exists, determining a second date corresponding to the first average duration which is greater than or equal to the first threshold, and obtaining the second date corresponding to the first SQL sentence which is the slow query.
To improve accuracy of the predicted second date, as shown in fig. 2, in some embodiments, the predicting the second date corresponding to the first SQL statement when the first SQL statement is a slow query includes:
step 201: determining a first number of days from a first date when slow query occurs in a first SQL sentence based on the first average value and a first average duration corresponding to the first date and a first threshold;
Step 202: and predicting a second date corresponding to the first SQL sentence when the first SQL sentence is a slow query sentence based on the first date and the first day.
The electronic device substitutes a first average value about the first amplification, a first average duration corresponding to the first date and a first threshold value into a set formula, and calculates a first number of days away from the first date when the first SQL sentence is slowly queried; and adding the calculated first days on the basis of the first date to obtain a second date corresponding to the first SQL sentence which is the slow query sentence.
Wherein, the setting formula is T× (1+P) x is more than or equal to M, and the setting formula can be obtained by transformingP characterizes a first mean value with respect to the first amplification; t represents a first average duration corresponding to the first date; m represents a first threshold, and the first day number can be obtained by rounding x, namely, slow query occurs in the first SQL on the x th day after the first day.
It should be noted that the first threshold may be a set threshold, or may be determined according to a time period consumed for executing the first SQL statement before the first set statistics period. The threshold may be set according to the real-time requirement of the service system, for example, the first threshold is relatively small for a service system with high real-time requirement and low delay, such as 300 milliseconds (ms) or 500ms, and the first threshold is relatively large for a service system with high real-time tolerance, such as 1000ms or more.
As shown in fig. 3, in some embodiments, when determining the first threshold based on the length of time it takes to execute the first SQL statement before the first set statistics period, the method includes:
Step 301: determining a second average time length consumed by executing the first SQL statement in a unit time length in a second set statistical period;
step 302: determining a second average value of the second average time length in the second statistical period based on the determined second average time length;
step 303: determining the maximum value of the first value and the second value as the first threshold value; wherein the first value characterizes a set multiple of the second mean; the second value characterizes the sum of a third mean value and the second mean value; the third average value represents an average value between a second average duration corresponding to a third date and the second average value; the third date characterizes a last day of the second set statistical period.
Here, the period corresponding to the second set statistical period is located before the period corresponding to the first set statistical period. The second setting statistical period can be one week or one month, and can be set according to actual needs.
The electronic equipment calculates a corresponding average value based on the execution time length corresponding to the first SQL statement executed each time in the unit time length in the second set statistical period, and obtains a second average time length consumed by executing the first SQL statement in the unit time length in the second set statistical period; determining a second average value of the second average duration in the second statistical period based on all or part of the determined second average duration; determining the product between the determined second average value and the set multiple as a first numerical value; determining an average value between a second average time length corresponding to the last day of the second set statistical period and the determined second average value as a third average value; determining the sum of the third mean value and the second mean value as a second numerical value; and determining the maximum value of the determined first value and the determined second value as a first threshold value. In practical application, the second set statistical period is one week, and the unit duration in the second set statistical period is 1 day.
And determining a second average value of the second average duration in the second statistical period based on the determined all second average durations, wherein the determining of the second average value of the second average duration in the second statistical period means that the determined all second average durations are averaged to obtain the second average value of the second average duration in the second statistical period. For example, the second average duration consumed by executing the first SQL statement per unit duration in the second statistical period is denoted as a1, a2, a3 … … an; a second average value an= (a1+a2+a3+ … … An)/n for a second average time period in the second statistical period. At this time, the first threshold value m=max (2 An, (An-An)/2+an). 2An characterizes a first value and (An-An)/2 + An characterizes a second value.
And determining a second average value of the second average duration in the second statistical period based on the determined partial second average duration, namely, deleting the lower and higher second average durations from all the determined second average durations to obtain the remaining second average duration, and calculating the average value of all the remaining second average durations to obtain the second average value of the second average duration in the second statistical period. Therefore, the error can be reduced, and the accuracy of the second average value of the determined second average duration can be improved. For example, the second average duration consumed by executing the first SQL statement per unit duration in the second statistical period is denoted as a1, a2, a3 … … an; after m second average durations are deleted from the determined second average durations, the remaining second average durations are a1, a2, a3 … … Am, and a second average value am= (a1+a2+a3+ … … Am)/m for the second average duration in the second statistical period. At this time, the first threshold value m=max (2 Am, (an-Am)/2+am). 2Am characterizes a first value and (an-Am)/2+am characterizes a second value.
It should be noted that the corresponding first threshold value may be different in the second different setting statistical period, that is, the first threshold value determined according to the methods of steps 301 to 303 is dynamically changed. In some embodiments, a default first threshold may be set first, and the maximum value of the default first threshold and the first threshold determined by the method is determined as a final first threshold.
To reduce errors with respect to the second average value of the second average duration, and improve accuracy of the second average value with respect to the second average duration, in some embodiments, in determining the second average value with respect to the second average duration, the method includes:
Calculating the product between the set proportion and the total number of all the determined first parameters to obtain a first number; the first parameter includes a second average duration; the set proportion is smaller than 0.5;
Deleting a first number of first parameters from the determined first parameters in order of magnitude; deleting a first number of first parameters from the determined first parameters according to the sequence from small to large to obtain the rest first parameters;
And calculating the average value of all the remaining first parameters, and obtaining a second average value about the second average duration.
Here, the electronic device calculates a product between the set proportion and the total number of all the determined second average durations to obtain a first number; deleting a first number of second average durations from the determined second average durations in order from large to small; deleting the first number of second average time lengths from the determined second average time lengths according to the sequence from small to large to obtain the remaining second average time lengths; and calculating the average value of all the remaining second average time lengths, and obtaining a first average value about the second average time lengths. In practical application, the set proportion is 5%.
Step 104: and outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date.
Under the condition that the electronic device predicts that the first SQL sentence is the corresponding second date when the slow query is performed, a slow query early warning prompt related to the corresponding first SQL sentence is output before the second date is reached, so that the slow query can be early warned in advance, relevant personnel can determine a corresponding solution for the slow query which possibly occurs, and the influence on the service performance of a database or the processing efficiency of the service is avoided.
In actual application, the electronic device determines a third date from the dates before the predicted second date, adds the corresponding first SQL sentence to the early warning list, and outputs a slow query early warning prompt related to the corresponding first SQL sentence every day from the third date. The third date represents the date on which slow query and early warning are required to be performed on the corresponding first SQL statement at the earliest time.
In the embodiment of the application, based on a first average time length consumed by executing a first Structured Query Language (SQL) statement in a unit time length in a first set statistical period, determining a first amplification corresponding to the first average time length of every two adjacent days; determining a first average value for the first amplification based on the determined first amplification; based on the first average value and a first average duration corresponding to the first date, and based on a first threshold value, predicting a second date corresponding to the first SQL sentence when the first SQL sentence is slow query; and outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date. Therefore, the slow inquiry can be predicted in advance, and early warning can be carried out on the slow inquiry in advance, so that relevant personnel can determine corresponding solutions aiming at the slow inquiry which possibly occurs, and the influence on the service performance of the database or the processing efficiency of the service is avoided.
To facilitate the ability of relevant personnel to take effective measures prior to the occurrence of a slow query, thereby minimizing or avoiding the occurrence of a slow query, in some embodiments, after the corresponding second date when the first SQL statement is predicted to be a slow query, the method further comprises:
and outputting optimization suggestions corresponding to the early-warning first SQL statement.
Here, the electronic device searches the optimization suggestion corresponding to the early-warning first SQL sentence from the database; and under the condition that the optimization suggestion corresponding to the first SQL statement of the early warning is found, outputting the corresponding found optimization suggestion. Under the condition that the optimization suggestion corresponding to the early-warning first SQL sentence is not found, analyzing the early-warning first SQL sentence and a related data table to obtain an analysis result; and outputting a corresponding optimization suggestion based on the analysis result. Therefore, related personnel can refer to the output optimization suggestion to optimize the corresponding first SQL sentence in advance, so that the influence on a service system when the early-warning SQL sentence generates slow query is effectively avoided, the related personnel are not required to analyze manually, and the optimization efficiency of the SQL sentence can be improved. The database may be a local database or a cloud database.
It should be noted that, the electronic device may associate and output the early-warning SQL statement and the corresponding optimization suggestion, for example, send the early-warning SQL statement and the corresponding optimization suggestion in the form of a mail.
In order to facilitate relevant personnel to adopt effective optimization strategies for different SQL sentences, in some embodiments, the optimization suggestion corresponding to the first SQL sentence of the output early warning comprises one of the following:
Outputting a first optimization suggestion or a second optimization suggestion under the condition that index information is not used by the first SQL statement of the early warning; the first optimization suggestions are characterized by corresponding first SQL statement newly added index information; the second optimization suggestion characterizes an order of optimizing query conditions based on first index information contained in table structure information of the related data table;
Under the condition that a query condition corresponding to the first SQL statement of the early warning comprises limit and two limiting parameters exist after the limit, outputting optimization suggestions for representing offset control by adding a primary key field;
Under the condition that the first SQL statement of the early warning contains sub-queries and the scanning line number corresponding to the sub-queries is larger than a first set threshold value, outputting optimization suggestions representing modifying the sub-queries into connection queries;
Outputting optimization suggestions representing backup and clearing old data under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and at least one set condition is met;
And outputting optimization suggestions representing database separation or table separation of the data table under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and all set conditions are not met.
The electronic equipment analyzes the data table related to the early-warning first SQL statement to obtain table structure information of the data table, and determines first index information corresponding to each data record in the data table from the table structure information; and simulating the first SQL statement for executing the early warning through explain commands to obtain related information when the first SQL statement for executing the early warning is executed, wherein the related information comprises the scanning line number, the second index information used, the query condition, the connection mode and the like. Wherein, the first index information and the second index information each comprise at least one field. The number of scanning lines refers to the number of lines of the data table related to scanning. The connection mode comprises at least one of left connection, right connection, sub-query and class connection.
Based on the first index information corresponding to each data record in the determined data table and the related information when the early warning first SQL statement is executed, outputting the optimization suggestion corresponding to the early warning first SQL statement.
The electronic device judges whether index information is used in a first SQL statement of the early warning to obtain a first judging result. Under the condition that the first judging result characterizes the first SQL statement of the early warning and index information is not used, comparing all the read query conditions from the first SQL statement of the early warning with all the first index information corresponding to the determined data table; and under the condition that all the first index information corresponding to the determined data table does not comprise the fields in the read query conditions, outputting a first optimization suggestion corresponding to the early-warning first SQL sentence, so that related personnel can add index information for the corresponding first SQL sentence. Under the condition that all the first index information corresponding to the determined data table comprises the fields in all or part of the read query conditions, outputting a second optimization suggestion corresponding to the early-warning first SQL sentence based on the arrangement sequence of the fields included in the corresponding first index information, wherein the second optimization suggestion represents the arrangement sequence of the query conditions in the early-warning first SQL sentence based on the arrangement sequence of the fields included in the corresponding first index information, so that the arrangement sequence of the query fields corresponding to the optimized query conditions is matched with the arrangement sequence of the fields included in the corresponding first index information.
And the electronic equipment judges whether the query condition corresponding to the early-warning SQL statement comprises a limit keyword or not, and a second judgment result is obtained. And outputting optimization suggestions for representing offset control by adding a main key field under the condition that the second judgment result representation comprises limit in the query condition corresponding to the first SQL statement of the early warning and two limiting parameters exist after limit. The query condition comprises limit, and the value of the main key field represents the value queried by the previous page, wherein the limit represents that the query involves page turning operation.
And the electronic equipment judges whether the pre-warning SQL statement contains sub-queries or not to obtain a third judging result. Under the condition that the SQL statement of the third judging result characterization early warning contains the sub-query, simulating and executing the sub-query through explain command, and determining the scanning line number corresponding to the sub-query; and outputting optimization suggestions corresponding to the first SQL statement representing the early warning and modifying the sub-queries into the connection queries under the condition that the determined scanning line number is larger than a first set threshold. The sub-queries are identified by: analyzing whether a set keyword exists in the query condition of the first SQL statement of the early warning; under the condition that a set keyword exists and other query SQL sentences exist behind the set keyword, the sub-query exists in the first SQL sentence of the early warning. The setting key includes in, not in, exist, or not exist, etc. The first set threshold may be 1000 lines or 5000 lines, etc., or may be set according to actual situations.
The method comprises the steps that electronic equipment counts the record number of a data table related to a first SQL statement of early warning, judges whether the data table meets at least one set condition or not under the condition that the record number of the data table is larger than a second set threshold value, and outputs optimization suggestions representing backup and removing old data under the condition that the record number of the data table related to the first SQL statement of early warning is larger than the second set threshold value and the data table meets at least one set condition; and outputting optimization suggestions representing database separation or table separation of the data table under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and the data table does not meet all set conditions.
In practical application, the second set threshold may be 1000 ten thousand lines; the setting condition includes one of the following:
The table names of the data table are ended by key words such as log, flow and the like;
The table names of the data table comprise keywords such as running water, records or logs;
The queried frequency of the data table is larger than the set frequency, and the control date included in the query condition corresponding to each query is close to the current date.
Fig. 4 is a schematic implementation flow chart of determining a first threshold in a slow query alarm method according to an embodiment of the present application, where, as shown in fig. 4, the slow query alarm method includes:
step 401: and based on the first average time length consumed by executing the first SQL sentence in the unit time length in the first set statistical period, determining a first amplification corresponding to the first average time length of every two adjacent days.
Step 402: based on the determined first amplification, a first average value for the first amplification is determined.
Step 403: and determining a first number of days from the first date when the first SQL sentence is slowly queried based on the first average time length corresponding to the first average value and the first date and based on a first threshold value.
Step 404: and predicting a second date corresponding to the first SQL sentence when the first SQL sentence is a slow query sentence based on the first date and the first day.
Step 405: from the date preceding the predicted second date, a third date is determined.
Step 406: and outputting slow query early warning prompts related to the corresponding first SQL sentences every day from the third date, and outputting optimization suggestions corresponding to the early-warning first SQL sentences.
It should be noted that, for the implementation process of step 401 to step 403, please refer to the related descriptions of step 101 to step 103, which are not repeated here.
In order to implement the slow query alarm method according to the embodiment of the present application, the embodiment of the present application further provides an electronic device, as shown in fig. 5, where the electronic device includes:
A first determining unit 41, configured to execute a first average duration consumed by the SQL statement of the first structured query language based on a unit duration in a first set statistical period, and determine a first amplification corresponding to the first average duration every two adjacent days;
a second determining unit 42 for determining a first average value regarding the first amplification based on the determined first amplification;
A prediction unit 43, configured to predict, based on the first average value and a first average duration corresponding to the first date, a second date corresponding to when the first SQL statement is a slow query based on a first threshold; wherein the first threshold characterizes a critical time consuming of a slow query; the first date represents the last day of a first set statistical period; the second date is subsequent to the first date;
and the prompting unit 44 is used for outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date.
In some embodiments, the electronic device further comprises:
The third determining unit is used for determining a second average time length consumed by executing the first SQL sentence in the unit time length in the second set statistical period;
A fourth determining unit, configured to determine a second average value about a second average duration in the second statistical period based on the determined second average duration;
a fifth determining unit configured to determine a maximum value of the first value and the second value as the first threshold; wherein the first value characterizes a set multiple of the second mean; the second value characterizes the sum of a third mean value and the second mean value; the third average value represents an average value between a second average duration corresponding to a third date and the second average value; the third date characterizes a last day of the second set statistical period.
In some embodiments, the second determining unit 42 or the fourth determining unit is specifically configured to:
Calculating the product between the set proportion and the total number of all the determined first parameters to obtain a first number; the first parameter comprises a first amplification or a second average duration; the set proportion is smaller than 0.5;
Deleting a first number of first parameters from the determined first parameters in order of magnitude; deleting a first number of first parameters from the determined first parameters according to the sequence from small to large to obtain the rest first parameters;
the average of all the first parameters remaining is calculated, resulting in a first average for the first amplification or a second average for the second average duration.
In some embodiments, the first determining unit 41 is further configured to:
determining a first SQL statement with the execution time length being greater than or equal to a second threshold value from SQL statements executed in unit time length in a first set statistical period; the second threshold is less than the first threshold;
and calculating the average value of the execution time lengths corresponding to all the first SQL sentences executed in the unit time length to obtain the first average time length consumed by executing the first SQL sentences in the unit time length.
In some embodiments, the prediction unit 43 is specifically configured to:
Determining a first day from the first date when a first SQL sentence is slowly queried based on the first average time length corresponding to the first average value and the first date and a first threshold value;
And predicting a second date corresponding to the first SQL sentence when the first SQL sentence is a slow query sentence based on the first date and the first day.
In some embodiments, the prompting unit 44 is further configured to:
and outputting optimization suggestions corresponding to the early-warning first SQL statement.
In some embodiments, prompt unit 44 is specifically configured to perform one of:
Outputting a first optimization suggestion or a second optimization suggestion under the condition that index information is not used by the first SQL statement of the early warning; the first optimization suggestions are characterized by corresponding first SQL statement newly added index information; the second optimization suggestion represents the arrangement sequence of the first index information optimization query condition contained in the table structure information based on the related data table;
Under the condition that a query condition corresponding to the first SQL statement of the early warning comprises limit and two limiting parameters exist after the limit, outputting optimization suggestions for representing offset control by adding a primary key field;
Under the condition that the first SQL statement of the early warning contains sub-queries and the scanning line number corresponding to the sub-queries is larger than a first set threshold value, outputting optimization suggestions representing modifying the sub-queries into connection queries;
Outputting optimization suggestions representing backup and clearing old data under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and at least one set condition is met;
And outputting optimization suggestions representing database separation or table separation of the data table under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and all set conditions are not met.
In practice, the units included in the electronic device may be implemented by a Processor in the electronic device, such as a central processing unit (CPU, central Processing Unit), a digital signal Processor (DSP, digital Signal Processor), a micro control unit (MCU, microcontroller Unit), or a Programmable gate array (FPGA, field-Programmable GATE ARRAY), etc.
It should be noted that: in the electronic device provided in the foregoing embodiment, only the division of the program modules is used for illustration when the slow query alarm is performed, and in practical application, the processing allocation may be performed by different program modules according to needs, that is, the internal structure of the device is divided into different program modules, so as to complete all or part of the processing described above. In addition, the electronic device provided in the above embodiment and the embodiment of the slow query alarm method belong to the same concept, and the specific implementation process of the electronic device is detailed in the method embodiment, which is not repeated herein.
Based on the hardware implementation of the program modules, and in order to implement the method of the embodiment of the present application, the embodiment of the present application further provides an electronic device. Fig. 6 is a schematic diagram of a hardware composition structure of an electronic device according to an embodiment of the present application, and as shown in fig. 6, the electronic device 6 includes:
a communication interface 61 capable of information interaction with other devices such as a network device and the like;
And the processor 62 is connected with the communication interface 61 to realize information interaction with other devices, and is used for executing the slow query alarm method provided by one or more technical schemes when running the computer program. And the computer program is stored on the memory 63.
Of course, in practice, the various components in the electronic device 6 are coupled together by a bus system 64. It is understood that the bus system 64 is used to enable connected communications between these components. The bus system 64 includes a power bus, a control bus, and a status signal bus in addition to the data bus. But for clarity of illustration the various buses are labeled as bus system 64 in fig. 6.
The memory 63 in an embodiment of the present application is used to store various types of data to support the operation of the electronic device 6. Examples of such data include: any computer program for operation on the electronic device 6.
It will be appreciated that memory 63 may be either volatile memory or nonvolatile memory, and may include both volatile and nonvolatile memory. The non-volatile Memory may be, among other things, a Read Only Memory (ROM), a programmable Read Only Memory (PROM, programmable Read-Only Memory), erasable programmable Read-Only Memory (EPROM, erasable Programmable Read-Only Memory), electrically erasable programmable Read-Only Memory (EEPROM, ELECTRICALLY ERASABLE PROGRAMMABLE READ-Only Memory), Magnetic random access Memory (FRAM, ferromagnetic random access Memory), flash Memory (Flash Memory), magnetic surface Memory, optical disk, or compact disk-Only (CD-ROM, compact Disc Read-Only Memory); the magnetic surface memory may be a disk memory or a tape memory. The volatile memory may be random access memory (RAM, random Access Memory) which acts as external cache memory. By way of example and not limitation, many forms of RAM are available, such as static random access memory (SRAM, static Random Access Memory), synchronous static random access memory (SSRAM, synchronous Static Random Access Memory), dynamic random access memory (DRAM, dynamic Random Access Memory), synchronous dynamic random access memory (SDRAM, synchronous Dynamic Random Access Memory), and, Double data rate synchronous dynamic random access memory (DDRSDRAM, double Data Rate Synchronous Dynamic Random Access Memory), enhanced synchronous dynamic random access memory (ESDRAM, enhanced Synchronous Dynamic Random Access Memory), synchronous link dynamic random access memory (SLDRAM, syncLink Dynamic Random Access Memory), Direct memory bus random access memory (DRRAM, direct Rambus Random Access Memory). The memory 63 described in embodiments of the present application is intended to comprise, without being limited to, these and any other suitable types of memory.
The method disclosed in the above embodiment of the present application may be applied to the processor 62 or implemented by the processor 62. The processor 62 may be an integrated circuit chip having signal processing capabilities. In implementation, the steps of the above method may be performed by integrated logic circuitry in hardware in the processor 62 or by instructions in the form of software. The processor 62 may be a general purpose processor, DSP, or other programmable logic device, discrete gate or transistor logic device, discrete hardware components, or the like. Processor 62 may implement or perform the methods, steps, and logic blocks disclosed in embodiments of the present application. The general purpose processor may be a microprocessor or any conventional processor or the like. The steps of the method disclosed in the embodiment of the application can be directly embodied in the hardware of the decoding processor or can be implemented by combining hardware and software modules in the decoding processor. The software modules may be located in a storage medium in a memory 63 and the processor 62 reads the program in the memory 63 to perform the steps of the method described above in connection with its hardware.
Optionally, when the processor 62 executes the program, a corresponding flow implemented by the terminal in each method of the embodiment of the present application is implemented, and for brevity, will not be described herein.
In an exemplary embodiment, the present application also provides a storage medium, i.e. a computer storage medium, in particular a computer readable storage medium, for example comprising a first memory 63 storing a computer program executable by the processor 62 of the terminal for performing the steps of the method described above. The computer readable storage medium may be FRAM, ROM, PROM, EPROM, EEPROM, flash Memory, magnetic surface Memory, optical disk, or CD-ROM.
In the several embodiments provided by the present application, it should be understood that the disclosed apparatus and method may be implemented in other ways. The above described device embodiments are only illustrative, e.g. the division of the units is only one logical function division, and there may be other divisions in practice, such as: multiple units or components may be combined or may be integrated into another system, or some features may be omitted, or not performed. In addition, the various components shown or discussed may be coupled or directly coupled or communicatively coupled to each other via some interface, whether indirectly coupled or communicatively coupled to devices or units, whether electrically, mechanically, or otherwise.
The units described as separate units may or may not be physically separate, and units displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units; some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
In addition, each functional unit in each embodiment of the present invention may be integrated in one processing module, or each unit may be separately used as one unit, or two or more units may be integrated in one unit; the integrated units may be implemented in hardware or in hardware plus software functional units.
Those of ordinary skill in the art will appreciate that: all or part of the steps for implementing the above method embodiments may be implemented by hardware associated with program instructions, where the foregoing program may be stored in a computer readable storage medium, and when executed, the program performs steps including the above method embodiments; and the aforementioned storage medium includes: a mobile storage device, a Read-Only Memory (ROM), a random access Memory (RAM, random Access Memory), a magnetic disk or optical disk, or the like, which can store program codes.
The technical schemes described in the embodiments of the present application may be arbitrarily combined without any collision.
It should be noted that, the term "and/or" in the embodiment of the present application is merely an association relationship describing the association object, and indicates that three relationships may exist, for example, a and/or B may indicate: a exists alone, A and B exist together, and B exists alone. In addition, the term "at least one" herein means any combination of any one or more of at least two of the plurality, for example, including at least one of A, B, C, may mean including any one or more elements selected from the group consisting of A, B and C.
The foregoing is merely illustrative of the present application, and the present application is not limited thereto, and any person skilled in the art will readily recognize that variations or substitutions are within the scope of the present application. Therefore, the protection scope of the present application shall be subject to the protection scope of the claims.
Claims (10)
1. A slow query alert method comprising:
executing a first average time length consumed by the SQL statement of the first structured query language based on the unit time length in the first set statistical period, and determining a first amplification corresponding to the first average time length of every two adjacent days;
determining a first average value for the first amplification based on the determined first amplification;
Based on the first average value and a first average duration corresponding to the first date, and based on a first threshold value, predicting a second date corresponding to the first SQL sentence when the first SQL sentence is slow query; wherein the first threshold characterizes a critical time consuming of a slow query; the first date represents the last day of a first set statistical period; the second date is subsequent to the first date;
And outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date.
2. The method according to claim 1, wherein the method further comprises:
determining a second average time length consumed by executing the first SQL statement in a unit time length in a second set statistical period;
determining a second average value of the second average time length in the second set statistical period based on the determined second average time length;
determining the maximum value of the first value and the second value as the first threshold value; wherein,
The first numerical value represents a set multiple of the second mean value; the second value characterizes the sum of a third mean value and the second mean value; the third average value represents an average value between a second average duration corresponding to a third date and the second average value; the third date characterizes a last day of the second set statistical period.
3. The method of claim 2, wherein in determining the first average value for the first amplification or the second average value for the second average duration, the method comprises:
Calculating the product between the set proportion and the total number of all the determined first parameters to obtain a first number; the first parameter comprises a first amplification or a second average duration; the set proportion is smaller than 0.5;
Deleting a first number of first parameters from the determined first parameters in order of magnitude; deleting a first number of first parameters from the determined first parameters according to the sequence from small to large to obtain the rest first parameters;
the average of all the first parameters remaining is calculated, resulting in a first average for the first amplification or a second average for the second average duration.
4. The method of claim 1, wherein in determining a first average length of time that the first SQL statement consumes per unit length of time, the method further comprises:
determining a first SQL statement with the execution time length being greater than or equal to a second threshold value from SQL statements executed in unit time length in a first set statistical period; the second threshold is less than the first threshold;
and calculating the average value of the execution time lengths corresponding to all the first SQL sentences executed in the unit time length to obtain the first average time length consumed by executing the first SQL sentences in the unit time length.
5. The method of claim 1, wherein predicting a corresponding second date when the first SQL statement is a slow query comprises:
Determining a first day from the first date when a first SQL sentence is slowly queried based on the first average time length corresponding to the first average value and the first date and a first threshold value;
And predicting a second date corresponding to the first SQL sentence when the first SQL sentence is a slow query sentence based on the first date and the first day.
6. The method of any one of claims 1 to 5, wherein after the predicting a corresponding second date when the first SQL statement is a slow query, the method further comprises:
and outputting optimization suggestions corresponding to the early-warning first SQL statement.
7. The method of claim 6, wherein the optimization suggestion corresponding to the first SQL statement of the output pre-warning comprises one of:
Outputting a first optimization suggestion or a second optimization suggestion under the condition that index information is not used by the first SQL statement of the early warning; the first optimization suggestions are characterized by corresponding first SQL statement newly added index information; the second optimization suggestion represents the arrangement sequence of the first index information optimization query condition contained in the table structure information based on the related data table;
Under the condition that a query condition corresponding to the first SQL statement of the early warning comprises limit and two limiting parameters exist after the limit, outputting optimization suggestions for representing offset control by adding a primary key field;
Under the condition that the first SQL statement of the early warning contains sub-queries and the scanning line number corresponding to the sub-queries is larger than a first set threshold value, outputting optimization suggestions representing modifying the sub-queries into connection queries;
Outputting optimization suggestions representing backup and clearing old data under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and at least one set condition is met;
And outputting optimization suggestions representing database separation or table separation of the data table under the condition that the record number of the data table related to the first SQL statement of the early warning is larger than a second set threshold value and all set conditions are not met.
8. An electronic device, comprising:
the first determining unit is used for executing a first average duration consumed by the SQL statement of the first structured query language based on the unit duration in the first set statistical period, and determining a first amplification corresponding to the first average duration of every two adjacent days;
A second determining unit configured to determine a first average value regarding the first amplification based on the determined first amplification;
The prediction unit is used for predicting a second date corresponding to the slow query of the first SQL sentence based on the first average duration corresponding to the first average value and the first date and based on a first threshold value; wherein the first threshold characterizes a critical time consuming of a slow query; the first date represents the last day of a first set statistical period; the second date is subsequent to the first date;
and the prompting unit is used for outputting a slow query early warning prompt related to the corresponding first SQL sentence before the second date.
9. An electronic device, comprising: a processor and a memory for storing a computer program capable of running on the processor,
Wherein the processor is adapted to perform the steps of the method of any of claims 1 to 7 when the computer program is run.
10. A storage medium having stored thereon a computer program, which when executed by a processor performs the steps of the method according to any of claims 1 to 7.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111304394.6A CN114185919B (en) | 2021-11-05 | 2021-11-05 | Slow query alarm method, electronic equipment and storage medium |
| PCT/CN2022/099854 WO2023077823A1 (en) | 2021-11-05 | 2022-06-20 | Slow query alarm method, electronic device, and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111304394.6A CN114185919B (en) | 2021-11-05 | 2021-11-05 | Slow query alarm method, electronic equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN114185919A CN114185919A (en) | 2022-03-15 |
| CN114185919B true CN114185919B (en) | 2024-05-28 |
Family
ID=80540732
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111304394.6A Active CN114185919B (en) | 2021-11-05 | 2021-11-05 | Slow query alarm method, electronic equipment and storage medium |
Country Status (2)
| Country | Link |
|---|---|
| CN (1) | CN114185919B (en) |
| WO (1) | WO2023077823A1 (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114185919B (en) * | 2021-11-05 | 2024-05-28 | 深圳前海微众银行股份有限公司 | Slow query alarm method, electronic equipment and storage medium |
| CN114911817A (en) * | 2022-04-19 | 2022-08-16 | 北京百度网讯科技有限公司 | Data processing method and device, electronic equipment and storage medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110019349A (en) * | 2019-04-02 | 2019-07-16 | 深圳前海微众银行股份有限公司 | Sentence method for early warning, device, equipment and computer readable storage medium |
| CN112084211A (en) * | 2020-10-12 | 2020-12-15 | 北京高因科技有限公司 | Slow SQL statement processing system |
| CN113297249A (en) * | 2021-01-25 | 2021-08-24 | 阿里云计算有限公司 | Slow query statement identification and analysis method and device and query statement statistical method and device |
Family Cites Families (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US10152429B2 (en) * | 2015-10-27 | 2018-12-11 | Medallia, Inc. | Predictive memory management |
| US10915515B2 (en) * | 2017-05-05 | 2021-02-09 | Servicenow, Inc. | Database performance tuning framework |
| CN107315790B (en) * | 2017-06-14 | 2021-07-06 | 腾讯科技（深圳）有限公司 | Optimization method and device for non-relevant sub-queries |
| CN110109953B (en) * | 2018-01-19 | 2023-12-19 | 阿里巴巴集团控股有限公司 | Data query method, device and equipment |
| CN113220705A (en) * | 2020-02-06 | 2021-08-06 | 北京沃东天骏信息技术有限公司 | Slow query identification method and device |
| US11526486B2 (en) * | 2020-04-08 | 2022-12-13 | Paypal, Inc. | Time-based data retrieval prediction |
| CN112380237B (en) * | 2020-11-13 | 2024-03-01 | 深圳市兴海物联科技有限公司 | Method, device, terminal and storage medium for predicting database hidden danger SQL |
| CN114185919B (en) * | 2021-11-05 | 2024-05-28 | 深圳前海微众银行股份有限公司 | Slow query alarm method, electronic equipment and storage medium |
-
2021
- 2021-11-05 CN CN202111304394.6A patent/CN114185919B/en active Active
-
2022
- 2022-06-20 WO PCT/CN2022/099854 patent/WO2023077823A1/en unknown
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110019349A (en) * | 2019-04-02 | 2019-07-16 | 深圳前海微众银行股份有限公司 | Sentence method for early warning, device, equipment and computer readable storage medium |
| CN112084211A (en) * | 2020-10-12 | 2020-12-15 | 北京高因科技有限公司 | Slow SQL statement processing system |
| CN113297249A (en) * | 2021-01-25 | 2021-08-24 | 阿里云计算有限公司 | Slow query statement identification and analysis method and device and query statement statistical method and device |
Non-Patent Citations (2)
| Title |
|---|
| Towards Predicting Query Execution Time for Concurrent and Dynamic Database Workloads;Wentao Wu et al.;Proceedings of the VLDB Endowment(PVLDB);20130831;第925-936页 * |
| 基于统计方法的Hive数据仓库查询优化实现;王有为等;计算机研究与发展;20150615;第1452-1462页 * |
Also Published As
| Publication number | Publication date |
|---|---|
| WO2023077823A1 (en) | 2023-05-11 |
| CN114185919A (en) | 2022-03-15 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN114185919B (en) | Slow query alarm method, electronic equipment and storage medium | |
| CN109254966B (en) | Data table query method, device, computer equipment and storage medium | |
| WO2017162086A1 (en) | Task scheduling method and device | |
| CN110674014A (en) | Method and device for determining abnormal query request | |
| CN102073712B (en) | Holographic process data archiving and inverting method based on dynamic change frame | |
| CN111104426B (en) | Data query method and system | |
| US10191668B1 (en) | Method for dynamically modeling medium error evolution to predict disk failure | |
| CN111445597A (en) | Data stitching and integration for machine learning | |
| US10783113B2 (en) | Data retention framework | |
| CN117033424A (en) | Query optimization method and device for slow SQL (structured query language) statement and computer equipment | |
| CN114662772A (en) | Traffic noise early warning method, model training method, device, equipment and medium | |
| CN114238389A (en) | Database query optimization method, apparatus, electronic device, medium, and program product | |
| CN116756176A (en) | Structured query language problem prediction method, device, equipment and storage medium | |
| CN110855484A (en) | Method, system, electronic device and storage medium for automatically detecting traffic change | |
| CN115422293A (en) | Distributed database and data retrieval method thereof | |
| CN114266242A (en) | Work order data processing method and device, server and readable storage medium | |
| CN111131393A (en) | User activity data statistical method, electronic device and storage medium | |
| CN110909112A (en) | Data extraction method, device, terminal equipment and medium | |
| CN111858902A (en) | Regular matching recommendation method and related device | |
| CN112306459B (en) | Parameter determination method and device and electronic equipment | |
| CN115099922B (en) | Financial data query method, system, readable storage medium and computer equipment | |
| CN114238258B (en) | Database data processing method, device, computer equipment and storage medium | |
| CN115840539B (en) | Data processing method, device, electronic equipment and storage medium | |
| CN113568822B (en) | Service resource monitoring method, device, computing equipment and storage medium | |
| CN111737281B (en) | Database query method, device, electronic equipment and readable storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant |