CN110019349A - Sentence method for early warning, device, equipment and computer readable storage medium - Google Patents
Sentence method for early warning, device, equipment and computer readable storage medium Download PDFInfo
- Publication number
- CN110019349A CN110019349A CN201910263955.9A CN201910263955A CN110019349A CN 110019349 A CN110019349 A CN 110019349A CN 201910263955 A CN201910263955 A CN 201910263955A CN 110019349 A CN110019349 A CN 110019349A
- Authority
- CN
- China
- Prior art keywords
- sentence
- preset
- early warning
- structured query
- warning
- 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.)
- Pending
Links
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/245—Query processing
- G06F16/2453—Query optimisation
Abstract
The invention discloses a kind of sentence method for early warning, this method comprises: obtaining the structured query sentence of application program access preset Production database；The executive plan for obtaining the structured query sentence extracts parameter preset relevant to inquiring slowly from the executive plan；Judge whether the parameter preset extracted meets preset slow inquiry early-warning conditions；When the parameter preset meets preset slow inquiry early-warning conditions, corresponding structured query sentence is added into preset early warning inventory.The invention also discloses a kind of sentence prior-warning device, equipment and a kind of computer readable storage mediums.The present invention can reduce the optimization cost inquired slowly under the premise of not influencing Production database performance.
Description
Technical field
The present invention relates to financial technology (Fintech) technical field more particularly to sentence method for early warning, device, equipment and
Computer readable storage medium.
Background technique
With financial technology (Fintech), the especially continuous development of internet techno-financial, more and more technologies are answered
Used in financial field.MySQL is one of current most popular Relational DBMS, SQL used in MySQL
(Structured Query Language, structured query language) is the most frequently used standardized language for accessing database.
During using sql like language inquiry database, the phenomenon that query responding time is more than specified time, is known as slow inquiry, looks into slowly
The performance that will affect database and application system is ask, will affect the availability of application system when serious.
Currently in order to eliminating or reducing slow inquiry, monitoring logic is usually disposed in Production database, is monitored whether out
Now slow inquiry is extracted slow query statement after finding slow inquiry, is optimized.The defect of this mode is: 1) different when monitoring
Chang Shi, the slow inquiry in storage facility located at processing plant have been formed, molding slow to inquire the data rule for often meaning that the object table of inquiry
Mould has developed bigger, forms big table, and when prioritization scheme be related to big table index plus the behaviour such as field, subregion
When making, can there are the business of write operation, equal nothing to the big table during this period with the Production database lock table process of long period
Method is normally carried out, and the optimization higher cost inquired slowly is so caused.2) monitoring logic has inquiry operation to Production database, objective
On increase the load of Production database, in addition monitoring logic and Production database are deployed on same physical resource, can also be produced
Raw certain contention for resources, influences the performance of Production database.
Summary of the invention
It is a primary object of the present invention to propose a kind of sentence method for early warning, device, equipment and computer-readable storage medium
Matter, it is intended under the premise of not influencing Production database performance, reduce the optimization cost inquired slowly.
To achieve the above object, the present invention provides a kind of sentence method for early warning, and the sentence method for early warning includes following step
It is rapid:
Obtain the structured query sentence of application program access preset Production database；
The executive plan for obtaining the structured query sentence extracts relevant to inquiring slowly from the executive plan
Parameter preset；
Judge whether the parameter preset extracted meets preset slow inquiry early-warning conditions；
When the parameter preset meets preset slow inquiry early-warning conditions, by corresponding structured query sentence be added to
In preset early warning inventory.
Preferably, the step of executive plan for obtaining the structured query sentence includes:
Clustering processing, the wherein execution of the structured query sentence under same type are carried out to the structured query sentence
Plan identical；
A target structured query sentence is chosen in the structured query sentence under all types of respectively；
The disaster recovery database of the Production database is accessed, according to the target structural query statement to obtain corresponding class
The executive plan of structured query sentence under type.
Preferably, the parameter preset includes query type and candidate result number, and the judgement extracts described default
Whether parameter, which meets the step of preset slow inquiry early-warning conditions, includes:
Judge whether the query type is default query type；
If the query type is default query type, it is pre- to judge whether the candidate result number is greater than or equal to first
If threshold value；
If the candidate result number is greater than or equal to the first preset threshold, it is preset slow to determine that the parameter preset meets
Inquire early-warning conditions.
Preferably, it is described judge the step of whether the candidate result number is greater than or equal to the first preset threshold after, also
Include:
If the candidate result number is less than first preset threshold, judge whether the candidate result number is greater than second
Preset threshold；
If the candidate result number is greater than the second preset threshold, candidate corresponding with the structured query sentence is analyzed
First historical variations trend of number of results；
Judge whether the candidate result number currently extracted from the executive plan falls in and first history
In corresponding first pre-set interval of variation tendency, if so, determining that the parameter preset meets preset slow inquiry early-warning conditions；
Alternatively, calculating the candidate result number currently extracted from the executive plan in the first default history duration
The first interior incremental value；
Judge whether first incremental value is greater than or equal to the first preset increments value, if so, determining the default ginseng
Number meets preset slow inquiry early-warning conditions.
Preferably, after described the step of carrying out clustering processing to the structured query sentence, further includes:
The execution number of structured query sentence under counting all types of respectively, and analyze all types of flowering structureization inquiry languages
Second historical variations trend of the execution number of sentence；
Judge whether the execution number of statistics falls in the second preset areas corresponding with the second historical variations trend
In, if so, being added corresponding structured query sentence into the early warning inventory；
Alternatively, second incremental value of the execution number of counting statistics in the second default history duration；
Judge whether second incremental value is greater than or equal to the second preset increments value, if so, by corresponding structuring
Query statement is added into the early warning inventory.
Preferably, the parameter preset includes sequencing model, described that corresponding structured query sentence is added to default
Early warning inventory in step after, further includes:
Obtain the sequencing model of the structured query sentence in the early warning inventory；
Judge whether the sequencing model got is preset sequencing model；
If the sequencing model got is preset sequencing model, addition need to the row of inspecting in the early warning inventory
The prompt information of sequence pattern.
Preferably, described that corresponding structured query sentence is added after the step into preset early warning inventory, also
Include:
The early warning inventory is sent to front end page to be shown.
In addition, to achieve the above object, the present invention also provides a kind of sentence prior-warning device, the sentence prior-warning device packet
It includes:
First obtains module, for obtaining the structured query sentence of application program access preset Production database；
Extraction module is extracted from the executive plan for obtaining the executive plan of the structured query sentence
Parameter preset relevant to inquiring slowly；
First judgment module, for judging whether the parameter preset extracted meets preset slow inquiry early warning item
Part；
Module is added, for when the parameter preset meets preset slow inquiry early-warning conditions, by corresponding structuring
Query statement is added into preset early warning inventory.
Preferably, the extraction module further include:
Cluster cell, for carrying out clustering processing, the wherein structuring under same type to the structured query sentence
The corresponding executive plan of query statement is identical；
Selection unit, for choosing a target structuralized query language from all types of structured query sentences respectively
Sentence；
Access unit, for accessing the disaster tolerance data of the Production database according to the target structural query statement
Library, to obtain executive plan corresponding with all types of structured query sentences.
Preferably, the parameter preset includes query type and candidate result number, and the first judgment module is also used to:
Judge whether the query type is default query type；
If the query type is default query type, it is pre- to judge whether the candidate result number is greater than or equal to first
If threshold value；
If the candidate result number is greater than or equal to the first preset threshold, it is preset slow to determine that the parameter preset meets
Inquire early-warning conditions.
Preferably, the first judgment module is also used to:
If the candidate result number is less than first preset threshold, judge whether the candidate result number is greater than second
Preset threshold；
If the candidate result number is greater than the second preset threshold, candidate corresponding with the structured query sentence is analyzed
First historical variations trend of number of results；
Judge whether the candidate result number currently extracted from the executive plan falls in and first history
In corresponding first pre-set interval of variation tendency, if so, determining that the parameter preset meets preset slow inquiry early-warning conditions；
Alternatively, calculating the candidate result number currently extracted from the executive plan in the first default history duration
The first interior incremental value；
Judge whether first incremental value is greater than or equal to the first preset increments value, if so, determining the default ginseng
Number meets preset slow inquiry early-warning conditions.
Preferably, the sentence prior-warning device further include:
Statistical analysis module for counting the execution number of all types of flowering structure query statements respectively, and is analyzed all kinds of
Second historical variations trend of the execution number of type flowering structure query statement；
The first judgment module is also used to judge whether the execution number of statistics falls in and becomes with second history
In corresponding second pre-set interval of change trend, if so, being added corresponding structured query sentence into the early warning inventory；
Alternatively, second incremental value of the execution number of counting statistics in the second default history duration；Described in judgement
Whether the second incremental value is greater than or equal to the second preset increments value, if so, being added corresponding structured query sentence to institute
It states in early warning inventory.
Preferably, the parameter preset includes sequencing model, the sentence prior-warning device further include:
Second obtains module, for obtaining the corresponding sequencing model of structured query sentence in the early warning inventory；
Second judgment module, for judging whether the sequencing model got is preset sequencing model；Add mould
Block, if the sequencing model for getting is preset sequencing model, day addition need to be inspected in the early warning inventory
The prompt information of sequencing model.
Preferably, the sentence prior-warning device further include:
Display module is shown for the early warning inventory to be sent to front end page.
In addition, to achieve the above object, the present invention also provides a kind of sentence source of early warning, the sentence source of early warning packet
It includes: memory, processor and the sentence early warning program that is stored on the memory and can run on the processor, it is described
Sentence early warning program realizes the step of sentence method for early warning as described above when being executed by the processor.
In addition, to achieve the above object, it is described computer-readable the present invention also provides a kind of computer readable storage medium
Sentence early warning program is stored on storage medium, the sentence early warning program realizes sentence as described above when being executed by processor
The step of method for early warning.
The structured query sentence of present invention acquisition application program access preset Production database；The structuring is obtained to look into
The executive plan for asking sentence, extracts parameter preset relevant to inquiring slowly from the executive plan；Judge the institute extracted
State whether parameter preset meets preset slow inquiry early-warning conditions；When the parameter preset meets preset slow inquiry early-warning conditions
When, corresponding structured query sentence is added into preset early warning inventory.The present invention compared with the prior art in looking into slowly
It askes after being formed again to the mode optimized inquire slowly, realizes before inquiring formation slowly, to being likely to form the language inquired slowly
Sentence carries out early warning, and object table general data scale is smaller at this time, not yet develops into big table, thus can be realized with lower cost
Optimization；In addition, the present invention only needs the executive plan of anolytic sentence, any parameter acquisition will not be carried out in Production database, no
Influence the performance of Production database.To which the present invention realizes under the premise of not influencing Production database performance, reduction is looked into slowly
The optimization cost of inquiry.
Detailed description of the invention
Fig. 1 is the device structure schematic diagram for the hardware running environment that the embodiment of the present invention is related to；
Fig. 2 is the flow diagram of sentence method for early warning first embodiment of the present invention.
The embodiments will be further described with reference to the accompanying drawings for the realization, the function and the advantages of the object of the present invention.
Specific embodiment
It should be appreciated that the specific embodiments described herein are merely illustrative of the present invention, it is not intended to limit the present invention.
As shown in Figure 1, Fig. 1 is the device structure schematic diagram for the hardware running environment that the embodiment of the present invention is related to.
Sentence source of early warning of the embodiment of the present invention can be PC machine or server apparatus.
As shown in Figure 1, the sentence source of early warning may include: processor 1001, such as CPU, network interface 1004, user
Interface 1003, memory 1005, communication bus 1002.Wherein, communication bus 1002 is for realizing the connection between these components
Communication.User interface 1003 may include display screen (Display), input unit such as keyboard (Keyboard), optional user
Interface 1003 can also include standard wireline interface and wireless interface.Network interface 1004 optionally may include having for standard
Line interface, wireless interface (such as WI-FI interface).Memory 1005 can be high speed RAM memory, be also possible to stable storage
Device (non-volatile memory), such as magnetic disk storage.Memory 1005 optionally can also be independently of aforementioned processing
The storage device of device 1001.
It will be understood by those skilled in the art that device structure shown in Fig. 1 does not constitute the restriction to equipment, can wrap
It includes than illustrating more or fewer components, perhaps combines certain components or different component layouts.
As shown in Figure 1, as may include that operating system, network are logical in a kind of memory 1005 of computer storage medium
Believe module, Subscriber Interface Module SIM and sentence early warning program.
In terminal shown in Fig. 1, network interface 1004 is mainly used for connecting background server, carries out with background server
Data communication；User interface 1003 is mainly used for connecting client (user terminal), carries out data communication with client；And processor
1001 can be used for calling the sentence early warning program stored in memory 1005, and execute following each implementations of sentence method for early warning
Operation in example.
Based on above-mentioned hardware configuration, sentence method for early warning embodiment of the present invention is proposed.
It is the flow diagram of sentence method for early warning first embodiment of the present invention referring to Fig. 2, Fig. 2, which comprises
Step S10 obtains the structured query sentence of application program access preset Production database；
In the present embodiment, sentence refers in particular to the data base manipulation statement constituted with structured query language SQL, for accessing
Data and query, update, and manage relational database system.The present embodiment sentence method for early warning is applied to sentence source of early warning,
The sentence source of early warning can be the background server of application program, i.e. application server, certainly, to be not take up application server
Resource, the sentence source of early warning are also possible to independently of the Warning Service device except application server.The present embodiment is pre- with sentence
Alert equipment is independently of being illustrated for the Warning Service device except application server.
In the present embodiment, application program accesses Production database by structured query sentence, which is
The database of business actual use is visited when it is implemented, Warning Service device can be sent to application server based on application program
The acquisition request of the structured query sentence of default Production database is asked, so as to apply after application server receives request
Corresponding structured query sentence is captured in program and returns to Warning Service device.
It should be noted that application server is directly in the application when while statement source of early warning is application server
Capture corresponding structured query sentence.
Step S20 obtains the executive plan of the structured query sentence, extracts from the executive plan and looks into slowly
Ask relevant parameter preset；
In the step, Warning Service device obtains the executive plan of above structure query statement, then from the executive plan
In extract parameter preset relevant to inquiring slowly.Wherein, executive plan refer to structured query sentence in the database by
The plan of physical execution is made of several operations, including full table scan, uses index, nested circulation, table connection etc..Slowly
The phenomenon that inquiry refers to during using sql like language inquiry database, and query responding time is more than specified time, can be with
The response speed of sentence is promoted by optimization executive plan, eliminates slow inquiry.Parameter preset relevant to inquiring slowly is default
The parameter that inquiry velocity, search efficiency may be had an impact in practice comprising but be not limited to query type, candidate result
Number, sequencing model etc., when specific implementation, can be according to the slow inquiry fault experience flexible settings in actual production.
In one embodiment, the step of executive plan for obtaining the structured query sentence may include: pair
The structured query sentence carries out clustering processing, and wherein the executive plan of the structured query sentence under same type is identical；
A target structured query sentence is chosen in the structured query sentence under all types of respectively；According to the target structural
Query statement accesses the disaster recovery database of the Production database, to obtain the execution of the structured query sentence under corresponding types
Plan.
Specifically, clustering processing can be carried out to the structured query sentence got first, wherein under same type
The executive plan of structured query sentence be it is identical, specific clustering rule can according to practical business situation flexible setting,
The sentence comprising name lookup field is including but not limited to divided into one kind, it is another kind of by being divided into comprising the sentence that the age inquires
Deng.
Then, a target structured query sentence is chosen in the structured query sentence under all types of respectively, wherein
It is chosen for randomly selecting, or chooses the sentence for coming foremost；Later, it is accessed according to the target structural query statement of selection
The disaster recovery database of creation data, wherein disaster recovery database is to be deployed in strange land, the data with Production database striking resemblances
Library, when the geographical location locating for the Production database occurs natural calamity and causes Production database that disaster occurs, disaster tolerance data
Library can immediately replace work, and when disaster recovery database receives target structural query statement, as the target structural is inquired
The corresponding executive plan of statement matching simultaneously returns to source of early warning, due to the execution meter of the structured query sentence under same type
Draw be it is identical, therefore source of early warning can obtain at this time it is all types of under all structured query sentences executive plan.
Above by the mode for obtaining executive plan after clustering to structured query sentence, executive plan is realized
Batch obtains, and improves the acquisition efficiency of executive plan, and obtains executive plan from the disaster recovery database of Production database, will not
The resource for occupying Production database, because generating any influence without the performance to Production database.
It should be noted that clustering processing can not also be carried out to structured query sentence when specific implementation, but respectively
Executive plan corresponding with every structured query sentence is obtained, then extracts from the executive plan got and inquires slowly
Relevant parameter preset；In addition, if can also directly be obtained from Production database without deployment disaster recovery database in practice
The executive plan of structured query sentence is taken, can occupy the resource of some Production databases at this time, specific embodiment can be with
Flexible choice according to the actual situation.
Step S30, judges whether the parameter preset extracted meets preset slow inquiry early-warning conditions；
After extracting parameter preset relevant to inquiring slowly in executive plan, whether the parameter preset extracted is judged
Meet preset slow inquiry early-warning conditions.
Wherein, parameter preset may include query type and candidate result number, judge the default ginseng extracted at this time
The step of whether number meets preset slow inquiry early-warning conditions includes: to judge whether the query type is default query type；
If the query type is default query type, judge whether the candidate result number is greater than or equal to the first preset threshold；
If the candidate result number is greater than or equal to the first preset threshold, determine that the parameter preset meets preset slow inquiry early warning
Condition.
Make when it is implemented, the corresponding query type of the higher sentence of probability inquired slowly can will be generated in practical experience
To preset query type, for example, " ALL " (full table scan), " range " (are swept in the form of range according to practical production experience
Retouch), " index " (by index order scanning, first reading index, then read actual row) corresponding sentence of these query types generate it is slow
The probability of inquiry is higher, therefore can be using these query types as default query type.When the query type extracted is
When " ALL ", " range " or " index ", further judge whether the candidate result number extracted is greater than or equal to the first default threshold
Value, wherein candidate result number reflects the data scale of the object table of structured query language inquiry, and the first preset threshold is danger
Dangerous threshold value, when specific implementation, can be with flexible settings；If candidate result number is greater than or equal to the first preset threshold, illustrate current knot
Structure query statement very likely generates slow inquiry, can be determined that parameter preset, i.e. query type and candidate result number are full at this time
The preset slow inquiry early-warning conditions of foot, it is thus achieved that the accurate early warning to inquiring slowly.
Certainly, other parameter presets and its corresponding slow inquiry early-warning conditions can also be set when specific implementation, such as when
When parameter preset is sequencing model, if the sequencing model in executive plan is preset sequencing model, meet preset slow
Early-warning conditions are inquired, can also carry out inquiring early warning slowly.
When the parameter preset meets preset slow inquiry early-warning conditions, step S40 is executed, corresponding structuring is looked into
Sentence is ask to be added into preset early warning inventory.
In the step, when parameter preset meets preset slow inquiry early-warning conditions, i.e., by corresponding structuralized query language
Sentence is added into preset early warning inventory.
It further, after the step s 40, can also include: that the early warning inventory is sent to front end page to open up
Show.So corresponding slow query optimization processing is made in time convenient for operation maintenance personnel.
The present embodiment compared with the prior art in after slow inquiry is formed again to the mode optimized is inquired slowly, realize
Before slow inquiry is formed, early warning is carried out to the sentence inquired slowly is likely to form, object table general data scale is smaller at this time, still
Big table is not developed into, thus can be realized and be optimized with lower cost；In addition, the present embodiment only needs the execution meter of anolytic sentence
It draws, any parameter acquisition will not be carried out in Production database, not influence the performance of Production database.To which the present embodiment realizes
Under the premise of not influencing Production database performance, the optimization cost inquired slowly is reduced.
Further, it is based on sentence method for early warning first embodiment of the present invention, proposes sentence method for early warning second of the present invention
Embodiment.
In the present embodiment, it is described judge the step of whether the candidate result number is greater than or equal to the first preset threshold it
Afterwards, can also include:
If the candidate result number is less than first preset threshold, judge whether the candidate result number is greater than second
Preset threshold；
If the candidate result number is greater than the second preset threshold, candidate corresponding with the structured query sentence is analyzed
First historical variations trend of number of results；Judge whether the candidate result number currently extracted from the executive plan falls
In the first pre-set interval corresponding with the first historical variations trend, if so, it is default to determine that the parameter preset meets
Slow inquiry early-warning conditions；
Alternatively, calculating the candidate result number currently extracted from the executive plan in the first default history duration
The first interior incremental value；Judge whether first incremental value is greater than or equal to the first preset increments value, if so, described in determining
Parameter preset meets preset slow inquiry early-warning conditions.
Specifically, if candidate result number is less than the first preset threshold, judge whether it is greater than the second preset threshold, if
Be, then it is assumed that there is certain probability to generate slow inquiry, can further determine whether to need at this time using the following two kinds mode into
Row early warning:
(1) the first historical variations trend of candidate result number corresponding with structured query sentence is analyzed, when it is implemented,
The candidate result number corresponding with current structure query statement being pre-stored in historical record can be extracted, number is then utilized
Chebyshev's theorem on carries out trend analysis, judges the candidate result number currently extracted from the executive plan
Whether fall in the first pre-set interval corresponding with the first historical variations trend, if so, it is larger to illustrate that corresponding sentence has
Probability generate slow inquiry, determine that the parameter preset meets preset slow inquiry early-warning conditions at this time.
Wherein, chebyshev's theorem is to the effect that: in any one data set, being located at m standard deviation range of its average
Interior ratio (or part) is always at least 1-1/m2, wherein m is any positive number greater than 1.For m=2, m=3 and m=5
Have following result: in all data, the data of at least 3/4 (or 75%) are located within the scope of 2 standard deviations of average.All numbers
In, the data of at least 8/9 (or 88.9%) are located within the scope of 3 standard deviations of average.In all data, at least 24/
The data of 25 (or 96%) are located within the scope of 5 standard deviations of average.
In the present embodiment, above-mentioned first pre-set interval can be with flexible setting, for example, ought be currently from the executive plan
When the mean value that the candidate result number extracted is greater than history candidate result number adds twice of standard deviation, it is possible to determine that described default
Parameter meets preset slow inquiry early-warning conditions.
(2) it calculates currently from the candidate result number extracted in the executive plan in the first default history duration
The first incremental value；Judge whether first incremental value is greater than or equal to the first preset increments value, if so, explanation is corresponding
Sentence has biggish probability to generate slow inquiry, determines that the parameter preset meets preset slow inquiry early-warning conditions at this time.
Wherein, the first default history duration and the first incremental value can flexible setting, for example, if currently from the execution
The candidate result number extracted in the works is greater than 10,000 relative to the incremental value of last month candidate result number mean value, then can be with
Determine that the parameter preset meets preset slow inquiry early-warning conditions.
The present embodiment judges whether to meet pre- by combining history candidate result number to analyze current candidate result number
If slow inquiry early-warning conditions, realize to there may be the sentences inquired slowly to carry out further early warning analysis, improve slow
Inquire the comprehensive of early warning.
Further, it is based on sentence method for early warning first embodiment of the present invention, proposes sentence method for early warning third of the present invention
Embodiment.
In the present embodiment, it after described the step of carrying out clustering processing to the structured query sentence, can also wrap
It includes:
The execution number of structured query sentence under counting all types of respectively, and analyze all types of flowering structureization inquiry languages
Second historical variations trend of the execution number of sentence；Judge whether the execution number of statistics falls in become with second history
In corresponding second pre-set interval of change trend, if so, being added corresponding structured query sentence into the early warning inventory；
Alternatively, second incremental value of the execution number of counting statistics in the second default history duration；Described in judgement
Whether the second incremental value is greater than or equal to the second preset increments value, if so, being added corresponding structured query sentence to institute
It states in early warning inventory.
Specifically, it after being clustered to the structured query sentence got, can be sieved by following two mode
Select the sentence for needing early warning:
(1) the execution number of the structured query sentence under counting all types of respectively, such as the sentence of query name type
Under the execution number of structured query sentence be 1000 times, the structured query sentence inquired under the sentence of age type is held
Row number is 500 times；Then the second historical variations trend of the execution number of all types of flowering structure query statements is analyzed, specifically
When implementation, the execution number for the structured query sentence being pre-stored under all types of in historical record can be extracted, then
Using mathematically chebyshev's theorem carry out trend analysis, judge the execution number of current statistic whether fall in it is described
In corresponding second pre-set interval of second historical variations trend, if so, it is slow to illustrate that corresponding sentence has biggish probability to generate
Inquiry, corresponding structured query sentence is added into the early warning inventory at this time.
In the present embodiment, above-mentioned second pre-set interval can be with flexible setting, for example, the execution number when current statistic is big
When the mean value that history executes number adds twice of standard deviation, corresponding structured query sentence can be added clear to the early warning
Dan Zhong.
(2) second incremental value of the execution number of counting statistics in the second default history duration；Judge described
Whether two incremental values are greater than or equal to the second preset increments value, if so, it is slow to illustrate that corresponding sentence has biggish probability to generate
Inquiry, corresponding structured query sentence is added into the early warning inventory at this time.
Wherein, the second default history duration and the second incremental value can flexible setting, for example, if the execution of current statistic
Number is greater than 10,000 relative to the incremental value that last month executes number, then corresponding structured query sentence can be added to institute
It states in early warning inventory.
The present embodiment analyzes current sentence execution number by combining history sentence to execute number, and then judges whether full
The preset slow inquiry early-warning conditions of foot are realized to there may be the sentences inquired slowly to carry out further early warning analysis, are improved
Slow inquiry early warning it is comprehensive.
Further, it is based on the first, second, third embodiment of sentence method for early warning of the present invention, proposes that sentence of the present invention is pre-
Alarm method fourth embodiment.
In the present embodiment, parameter preset relevant to inquiring slowly includes sequencing model, after corresponding above-mentioned steps S40,
It can also include: the sequencing model for obtaining the structured query sentence in the early warning inventory；Judge the sequence got
Whether mode is preset sequencing model；If the sequencing model got is preset sequencing model, in the early warning
Addition need to inspect the prompt information of sequencing model in inventory.
In the present embodiment, it is contemplated that certain sequencing model sequence efficiencies are lower, have biggish probability to generate slow inquiry, because
This can further obtain its sequencing model for the structured query sentence having been added in early warning inventory, if sequence mould
Formula is preset sequencing model, then additional explanation need to inspect sequencing model in early warning inventory, to prompt operation maintenance personnel to sequence
Mode optimizes.Wherein, preset sequencing model includes but is not limited to " Using filesort " (file ordering), " Using
Temprorary " (interim table) etc..In this way, sequence efficiency can be improved.
The present invention also provides a kind of sentence prior-warning devices.The sentence prior-warning device includes:
First obtains module, for obtaining the structured query sentence of application program access preset Production database；
Extraction module is extracted from the executive plan for obtaining the executive plan of the structured query sentence
Parameter preset relevant to inquiring slowly；
First judgment module, for judging whether the parameter preset extracted meets preset slow inquiry early warning item
Part；
Module is added, for when the parameter preset meets preset slow inquiry early-warning conditions, by corresponding structuring
Query statement is added into preset early warning inventory.
Further, the extraction module further include:
Cluster cell, for carrying out clustering processing, the wherein structuring under same type to the structured query sentence
The corresponding executive plan of query statement is identical；
Selection unit, for choosing a target structuralized query language from all types of structured query sentences respectively
Sentence；
Access unit, for accessing the disaster tolerance data of the Production database according to the target structural query statement
Library, to obtain executive plan corresponding with all types of structured query sentences.
Further, the parameter preset includes query type and candidate result number, and the first judgment module is also used to:
Judge whether the query type is default query type；
If the query type is default query type, it is pre- to judge whether the candidate result number is greater than or equal to first
If threshold value；
If the candidate result number is greater than or equal to the first preset threshold, it is preset slow to determine that the parameter preset meets
Inquire early-warning conditions.
Further, the first judgment module is also used to:
If the candidate result number is less than first preset threshold, judge whether the candidate result number is greater than second
Preset threshold；
If the candidate result number is greater than the second preset threshold, candidate corresponding with the structured query sentence is analyzed
First historical variations trend of number of results；
Judge whether the candidate result number currently extracted from the executive plan falls in and first history
In corresponding first pre-set interval of variation tendency, if so, determining that the parameter preset meets preset slow inquiry early-warning conditions；
Alternatively, calculating the candidate result number currently extracted from the executive plan in the first default history duration
The first interior incremental value；
Judge whether first incremental value is greater than or equal to the first preset increments value, if so, determining the default ginseng
Number meets preset slow inquiry early-warning conditions.
Further, the sentence prior-warning device further include:
Statistical analysis module for counting the execution number of all types of flowering structure query statements respectively, and is analyzed all kinds of
Second historical variations trend of the execution number of type flowering structure query statement；
The first judgment module is also used to judge whether the execution number of statistics falls in and becomes with second history
In corresponding second pre-set interval of change trend, if so, being added corresponding structured query sentence into the early warning inventory；
Alternatively, second incremental value of the execution number of counting statistics in the second default history duration；Described in judgement
Whether the second incremental value is greater than or equal to the second preset increments value, if so, being added corresponding structured query sentence to institute
It states in early warning inventory.
Further, the parameter preset includes sequencing model, the sentence prior-warning device further include:
Second obtains module, for obtaining the corresponding sequencing model of structured query sentence in the early warning inventory；
Second judgment module, for judging whether the sequencing model got is preset sequencing model；Add mould
Block, if the sequencing model for getting is preset sequencing model, day addition need to be inspected in the early warning inventory
The prompt information of sequencing model.
Further, the sentence prior-warning device further include:
Display module is shown for the early warning inventory to be sent to front end page.
The present invention also provides a kind of computer readable storage mediums.
Sentence early warning program is stored on computer readable storage medium of the present invention, the sentence early warning program is by processor
The step of sentence method for early warning as described above is realized when execution.
Wherein, the sentence early warning program run on the processor is performed realized method and can refer to the present invention
The each embodiment of sentence method for early warning, details are not described herein again.
It should be noted that, in this document, the terms "include", "comprise" or its any other variant are intended to non-row
His property includes, so that the process, method, article or the system that include a series of elements not only include those elements, and
And further include other elements that are not explicitly listed, or further include for this process, method, article or system institute it is intrinsic
Element.In the absence of more restrictions, the element limited by sentence "including a ...", it is not excluded that including being somebody's turn to do
There is also other identical elements in the process, method of element, article or system.
The serial number of the above embodiments of the invention is only for description, does not represent the advantages or disadvantages of the embodiments.
Through the above description of the embodiments, those skilled in the art can be understood that above-described embodiment side
Method can be realized by means of software and necessary general hardware platform, naturally it is also possible to by hardware, but in many cases
The former is more preferably embodiment.Based on this understanding, technical solution of the present invention substantially in other words does the prior art
The part contributed out can be embodied in the form of software products, which is stored in one as described above
In storage medium (such as ROM/RAM, magnetic disk, CD), including some instructions are used so that terminal device (it can be mobile phone,
Computer, server, air conditioner or network equipment etc.) execute method described in each embodiment of the present invention.
The above is only a preferred embodiment of the present invention, is not intended to limit the scope of the invention, all to utilize this hair
Equivalent structure or equivalent flow shift made by bright specification and accompanying drawing content is applied directly or indirectly in other relevant skills
Art field, is included within the scope of the present invention.
Claims (15)
1. a kind of sentence method for early warning, which is characterized in that the sentence method for early warning includes the following steps:
Obtain the structured query sentence of application program access preset Production database；
The executive plan for obtaining the structured query sentence extracts relevant to inquiring slowly default from the executive plan
Parameter；
Judge whether the parameter preset extracted meets preset slow inquiry early-warning conditions；
When the parameter preset meets preset slow inquiry early-warning conditions, corresponding structured query sentence is added to default
Early warning inventory in.
2. sentence method for early warning as described in claim 1, which is characterized in that described to obtain holding for the structured query sentence
Row plan the step of include:
Clustering processing, the wherein executive plan of the structured query sentence under same type are carried out to the structured query sentence
It is identical；
A target structured query sentence is chosen in the structured query sentence under all types of respectively；
The disaster recovery database of the Production database is accessed, according to the target structural query statement to obtain under corresponding types
Structured query sentence executive plan.
3. sentence method for early warning as described in claim 1, which is characterized in that the parameter preset includes query type and candidate
Number of results, it is described to judge that the step of whether parameter preset extracted meets preset slow inquiry early-warning conditions includes:
Judge whether the query type is default query type；
If the query type is default query type, judge whether the candidate result number is greater than or equal to the first default threshold
Value；
If the candidate result number is greater than or equal to the first preset threshold, determine that the parameter preset meets preset slow inquiry
Early-warning conditions.
4. sentence method for early warning as claimed in claim 3, which is characterized in that described to judge whether the candidate result number is greater than
Or after the step of being equal to the first preset threshold, further includes:
If the candidate result number is less than first preset threshold, it is default to judge whether the candidate result number is greater than second
Threshold value；
If the candidate result number is greater than the second preset threshold, candidate result corresponding with the structured query sentence is analyzed
The first several historical variations trend；
Judge whether the candidate result number currently extracted from the executive plan falls in and first historical variations
In corresponding first pre-set interval of trend, if so, determining that the parameter preset meets preset slow inquiry early-warning conditions；
Alternatively, calculating currently from the candidate result number extracted in the executive plan in the first default history duration
First incremental value；
Judge whether first incremental value is greater than or equal to the first preset increments value, if so, determining that the parameter preset is full
The preset slow inquiry early-warning conditions of foot.
5. sentence method for early warning as claimed in claim 2, which is characterized in that described to gather to the structured query sentence
After the step of class processing, further includes:
The execution number of structured query sentence under counting all types of respectively, and analyze all types of flowering structure query statements
Execute the second historical variations trend of number；
Judge whether the execution number of statistics falls in the second pre-set interval corresponding with the second historical variations trend,
If so, being added corresponding structured query sentence into the early warning inventory；
Alternatively, second incremental value of the execution number of counting statistics in the second default history duration；
Judge whether second incremental value is greater than or equal to the second preset increments value, if so, by corresponding structuralized query
Sentence is added into the early warning inventory.
6. the sentence method for early warning as described in any one of claims 1 to 5, which is characterized in that the parameter preset includes row
Sequence pattern, it is described that corresponding structured query sentence is added after the step into preset early warning inventory, further includes:
Obtain the sequencing model of the structured query sentence in the early warning inventory；
Judge whether the sequencing model got is preset sequencing model；
If the sequencing model got is preset sequencing model, addition need to inspect sequence mould in the early warning inventory
The prompt information of formula.
7. the sentence method for early warning as described in any one of claims 1 to 5, which is characterized in that described by corresponding structuring
Query statement is added after the step into preset early warning inventory, further includes:
The early warning inventory is sent to front end page to be shown.
8. a kind of sentence prior-warning device, which is characterized in that the sentence prior-warning device includes:
First obtains module, for obtaining the structured query sentence of application program access preset Production database；
Extraction module, for obtaining the executive plan of the structured query sentence, extracted from the executive plan with slowly
Inquire relevant parameter preset；
First judgment module, for judging whether the parameter preset extracted meets preset slow inquiry early-warning conditions；
Module is added, for when the parameter preset meets preset slow inquiry early-warning conditions, by corresponding structuralized query
Sentence is added into preset early warning inventory.
9. sentence prior-warning device as claimed in claim 8, which is characterized in that the extraction module further include:
Cluster cell is used to carry out clustering processing to the structured query sentence, wherein the structuralized query under same type
The corresponding executive plan of sentence is identical；
Selection unit, for choosing a target structured query sentence from all types of structured query sentences respectively；
Access unit, for accessing the disaster recovery database of the Production database according to the target structural query statement, with
Obtain executive plan corresponding with all types of structured query sentences.
10. sentence prior-warning device as claimed in claim 8, which is characterized in that the parameter preset includes query type and time
Number of results is selected, the first judgment module is also used to:
Judge whether the query type is default query type；
If the query type is default query type, judge whether the candidate result number is greater than or equal to the first default threshold
Value；
If the candidate result number is greater than or equal to the first preset threshold, determine that the parameter preset meets preset slow inquiry
Early-warning conditions.
11. sentence prior-warning device as claimed in claim 10, which is characterized in that the first judgment module is also used to:
If the candidate result number is less than first preset threshold, it is default to judge whether the candidate result number is greater than second
Threshold value；
If the candidate result number is greater than the second preset threshold, candidate result corresponding with the structured query sentence is analyzed
The first several historical variations trend；
Judge whether the candidate result number currently extracted from the executive plan falls in and first historical variations
In corresponding first pre-set interval of trend, if so, determining that the parameter preset meets preset slow inquiry early-warning conditions；
Alternatively, calculating currently from the candidate result number extracted in the executive plan in the first default history duration
First incremental value；
Judge whether first incremental value is greater than or equal to the first preset increments value, if so, determining that the parameter preset is full
The preset slow inquiry early-warning conditions of foot.
12. sentence prior-warning device as claimed in claim 9, which is characterized in that the sentence prior-warning device further include:
Statistical analysis module, for counting the execution number of all types of flowering structure query statements respectively, and analyze it is all types of under
Second historical variations trend of the execution number of structured query sentence；
The first judgment module, is also used to judge whether the execution number of statistics falls in become with second historical variations
In corresponding second pre-set interval of gesture, if so, being added corresponding structured query sentence into the early warning inventory；
Alternatively, second incremental value of the execution number of counting statistics in the second default history duration；Judge described second
Whether incremental value is greater than or equal to the second preset increments value, if so, being added corresponding structured query sentence to described pre-
In alert inventory.
13. the sentence prior-warning device as described in any one of claim 8 to 12, which is characterized in that the parameter preset includes
Sequencing model, the sentence prior-warning device further include:
Second obtains module, for obtaining the corresponding sequencing model of structured query sentence in the early warning inventory；
Second judgment module, for judging whether the sequencing model got is preset sequencing model；Adding module is used
If being preset sequencing model in the sequencing model got, day addition need to inspect sequence mould in the early warning inventory
The prompt information of formula.
14. a kind of sentence source of early warning, which is characterized in that the equipment includes: memory, processor and is stored in the storage
It is real when the sentence early warning program is executed by the processor on device and the sentence early warning program that can run on the processor
Now the step of sentence method for early warning as described in any one of claims 1 to 7.
15. a kind of computer readable storage medium, which is characterized in that it is pre- to be stored with sentence on the computer readable storage medium
Alert program realizes the sentence early warning as described in any one of claims 1 to 7 when the sentence early warning program is executed by processor
The step of method.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910263955.9A CN110019349A (en) | 2019-04-02 | 2019-04-02 | Sentence method for early warning, device, equipment and computer readable storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910263955.9A CN110019349A (en) | 2019-04-02 | 2019-04-02 | Sentence method for early warning, device, equipment and computer readable storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110019349A true CN110019349A (en) | 2019-07-16 |
Family
ID=67190496
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910263955.9A Pending CN110019349A (en) | 2019-04-02 | 2019-04-02 | Sentence method for early warning, device, equipment and computer readable storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110019349A (en) |
Cited By (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110674014A (en) * | 2019-09-16 | 2020-01-10 | 中国银联股份有限公司 | Method and device for determining abnormal query request |
| CN110895542A (en) * | 2019-11-28 | 2020-03-20 | 中国银行股份有限公司 | High-risk SQL statement screening method and device |
| CN111597203A (en) * | 2020-04-28 | 2020-08-28 | 中国银联股份有限公司 | Statement query method, device, equipment and medium |
| CN111797112A (en) * | 2020-06-05 | 2020-10-20 | 武汉大学 | PostgreSQL preparation statement execution optimization method |
| CN112613176A (en) * | 2020-12-23 | 2021-04-06 | 贝壳技术有限公司 | Slow SQL statement prediction method and system |
| CN112612810A (en) * | 2020-12-23 | 2021-04-06 | 贝壳技术有限公司 | Slow SQL statement identification method and system |
| CN113010521A (en) * | 2021-03-18 | 2021-06-22 | 中国邮政储蓄银行股份有限公司 | Method and device for processing remote transaction service and computer readable storage medium |
| CN113297249A (en) * | 2021-01-25 | 2021-08-24 | 阿里云计算有限公司 | Slow query statement identification and analysis method and device and query statement statistical method and device |
| CN113535773A (en) * | 2021-07-27 | 2021-10-22 | 工银科技有限公司 | Database optimization method, database optimization device, electronic device and storage medium |
| WO2023077823A1 (en) * | 2021-11-05 | 2023-05-11 | 深圳前海微众银行股份有限公司 | Slow query alarm method, electronic device, and storage medium |
-
2019
- 2019-04-02 CN CN201910263955.9A patent/CN110019349A/en active Pending
Cited By (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110674014A (en) * | 2019-09-16 | 2020-01-10 | 中国银联股份有限公司 | Method and device for determining abnormal query request |
| CN110895542A (en) * | 2019-11-28 | 2020-03-20 | 中国银行股份有限公司 | High-risk SQL statement screening method and device |
| CN110895542B (en) * | 2019-11-28 | 2022-09-27 | 中国银行股份有限公司 | High-risk SQL statement screening method and device |
| CN111597203A (en) * | 2020-04-28 | 2020-08-28 | 中国银联股份有限公司 | Statement query method, device, equipment and medium |
| CN111797112A (en) * | 2020-06-05 | 2020-10-20 | 武汉大学 | PostgreSQL preparation statement execution optimization method |
| CN112613176A (en) * | 2020-12-23 | 2021-04-06 | 贝壳技术有限公司 | Slow SQL statement prediction method and system |
| CN112612810A (en) * | 2020-12-23 | 2021-04-06 | 贝壳技术有限公司 | Slow SQL statement identification method and 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 |
| CN113010521A (en) * | 2021-03-18 | 2021-06-22 | 中国邮政储蓄银行股份有限公司 | Method and device for processing remote transaction service and computer readable storage medium |
| CN113535773A (en) * | 2021-07-27 | 2021-10-22 | 工银科技有限公司 | Database optimization method, database optimization device, electronic device and storage medium |
| CN113535773B (en) * | 2021-07-27 | 2022-08-12 | 工银科技有限公司 | Database optimization method, database optimization device, electronic device and storage medium |
| WO2023077823A1 (en) * | 2021-11-05 | 2023-05-11 | 深圳前海微众银行股份有限公司 | Slow query alarm method, electronic device, and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110019349A (en) | Sentence method for early warning, device, equipment and computer readable storage medium | |
| CN111294819B (en) | Network optimization method and device | |
| CN110471945B (en) | Active data processing method, system, computer equipment and storage medium | |
| CN114490375B (en) | Performance test method, device, equipment and storage medium of application program | |
| CN114237466B (en) | Inspection point configuration method and device | |
| CN113722134A (en) | Cluster fault processing method, device and equipment and readable storage medium | |
| CN112364014A (en) | Data query method, device, server and storage medium | |
| CN114116762A (en) | Offline data fuzzy search method, device, equipment and medium | |
| CN106484913A (en) | Method and server that a kind of Target Photo determines | |
| CN116074183B (en) | C3 timeout analysis method, device and equipment based on rule engine | |
| CN115310011A (en) | Page display method and system and readable storage medium | |
| CN112784025B (en) | Method and device for determining target event | |
| CN115619245A (en) | Portrait construction and classification method and system based on data dimension reduction method | |
| CN112416800B (en) | Intelligent contract testing method, device, equipment and storage medium | |
| CN111798237B (en) | Abnormal transaction diagnosis method and system based on application log | |
| KR102217092B1 (en) | Method and apparatus for providing quality information of application | |
| CN113610225A (en) | Quality evaluation model training method and device, electronic equipment and storage medium | |
| CN113360357A (en) | Data monitoring method, system, equipment and computer program product | |
| CN113326688A (en) | Ideological and political theory word duplication checking processing method and device | |
| CN111176985A (en) | Software interface performance testing method and device, computer equipment and storage medium | |
| CN115934980B (en) | Layout graph search processing method and device, equipment and storage medium | |
| CN113535594B (en) | Method, device, equipment and storage medium for generating service scene test case | |
| US11831521B1 (en) | Entity lifecycle management in service monitoring system | |
| US11641310B1 (en) | Entity lifecycle management in service monitoring system | |
| KR102555733B1 (en) | Object management for improving machine learning performance, control method thereof |
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 |