CN107748782A - Query statement processing method and processing device - Google Patents
Query statement processing method and processing device Download PDFInfo
- Publication number
- CN107748782A CN107748782A CN201710985953.1A CN201710985953A CN107748782A CN 107748782 A CN107748782 A CN 107748782A CN 201710985953 A CN201710985953 A CN 201710985953A CN 107748782 A CN107748782 A CN 107748782A
- Authority
- CN
- China
- Prior art keywords
- query statement
- risk
- analysis result
- processing
- statement
- 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/21—Design, administration or maintenance of databases
- G06F16/217—Database tuning
-
- 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
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the present invention provides a kind of query statement processing method and processing device, is related to database field.Wherein, methods described includes：Parsing performs the time-consuming query statement not less than given threshold and obtains analysis result；Judge whether the analysis result meets risk judgment condition based on the analysis result；If the analysis result meets the risk judgment condition, the query statement is defined as risk query statement.Technical scheme provided in an embodiment of the present invention is advantageous to provide effective, comprehensive data support for data base optimization.
Description
Technical field
The present embodiments relate to database field, more particularly to a kind of query statement processing method and processing device.
Background technology
Database technology is widely used, in face of big data quantity, SQL (Structured Query
Language, SQL) sentence is unreasonable, can cause to perform overlong time and cause to inquire about slowly.Slowly inquire about because of mistake
Consuming CPU or I/O (input/output) more can cause whole database response slack-off, be also possible to cause under short connection scene
Exhaust connection number.Sybase such as MySql has supported slow inquiry log function, can detect all database behaviour
Note down.When being more than given threshold between when implemented, it will will perform the information records such as SQL statement and be saved in file.
The purpose for analyzing slow inquiry log is that the SQL statement found out and cause to inquire about slowly, repairs them to avoid into one
Database performance caused by step loses.It is typically all artificially to set to perform time-consuming threshold value first to analyze the method inquired about slowly, high
Just it is recorded in the SQL statement execution journal for performing time-consuming threshold value, then by parsing journal file, is therefrom needed
Information is carried out to be judged again.
The content of the invention
Inventor has found in the research to prior art, and in terms of the processing of query statement, prior art will not place
Managed that the daily record data amount that process automation causes technical staff to face is still excessively huge, and technical staff is to database operation maintenance
Complexity still exist；Furthermore prior art is only focused on more than the slow inquiry for performing time-consuming threshold value, and data are comprehensive to leave a question open,
So hidden danger be present；In addition, early warning only is done to the slow inquiry that system is generated on line, but the slow inquiry on line is when analysis is found
The infringement of performance also has resulted in, and results in a finding that the cost of problem is excessive.
For above-mentioned part or all of defect, the embodiment of the present invention provides a kind of query statement processing method and processing device.Under
Face illustrates respectively.
In a first aspect, providing a kind of query statement processing method in the embodiment of the present invention, methods described includes：
Parsing performs the time-consuming query statement not less than given threshold and obtains analysis result；
Judge whether the analysis result meets risk judgment condition based on the analysis result；
If the analysis result meets the risk judgment condition, the query statement is defined as risk inquiry language
Sentence.
With reference in a first aspect, of the invention in the first implementation of first aspect, methods described also includes：
Runtime database is descended based on test environment under production environment data on line and line online, obtains inquiry day to be analyzed
Will；
Parse the inquiry log to be analyzed and obtain the query statement and/or slow query statement.
With reference in a first aspect, of the invention in second of implementation of first aspect, the risk judgment condition is：
The analysis result includes following any one or more persons：
Represent the query statement cause the first element of full table scan, represent the query statement should use index and
The second key element of index is not used, represents that the wall scroll sentence has used the three elements of interim table, represented the query statement
Retrieval line number exceed given threshold the 4th key element.
With reference to the first implementation of first aspect, first aspect or second of implementation of first aspect, this hair
It is bright in the third implementation of first aspect, in addition to：Abstract processing is carried out to the risk query statement；
Classification processing is carried out to the risk query statement according to the result of the abstract processing.
With reference to the first implementation of first aspect, the present invention also wraps in the 4th kind of implementation of first aspect
Include：
Abstract processing is carried out to the slow query statement and/or the risk query statement；
The slow query statement and/or the risk query statement are carried out at classification according to the result of the abstract processing
Reason.
Second aspect, the embodiments of the invention provide a kind of query statement processing unit, including：
Statement parsing module, analysis result is obtained for parsing the time-consuming query statement not less than given threshold of execution；
Risk judgment module, for judging whether the analysis result meets that risk adjudicates bar based on the analysis result
Part；
Risk determining module, for when the analysis result meets the risk judgment condition, by the query statement
It is defined as risk query statement.
With reference to second aspect, in the first implementation of second aspect, described device also includes the present invention：
Log acquisition module, for descending runtime database online based on test environment under production environment data on line and line,
Obtain inquiry log to be analyzed.
Log analyzing module, the query statement and/or slow inquiry are obtained for parsing the inquiry log to be analyzed
Sentence.
With reference to second aspect, in second of implementation of second aspect, the risk judgment condition is the present invention：
The analysis result includes following any one or more persons：
Represent the query statement cause the first element of full table scan, represent the query statement should use index and
The second key element of index is not used, represents that the wall scroll sentence has used the three elements of interim table, represented the query statement
Retrieval line number exceed given threshold the 4th key element.
With reference to the first implementation of second aspect, second aspect or second of implementation of second aspect, this hair
It is bright in the third implementation of second aspect, in addition to：
First abstract module, for carrying out abstract processing to the risk query statement；
First sort module, the risk query statement is carried out at classification for the result according to the abstract processing
Reason.
With reference to the first implementation of second aspect, the present invention also wraps in the 4th kind of implementation of second aspect
Include：
Second abstract module, for carrying out abstract processing to the slow query statement and/or the risk query statement；
Second sort module, for the result according to the abstract processing to the slow query statement and/or the risk
Query statement carries out classification processing.
The third aspect, the embodiment of the present invention provide a kind of electronic equipment, including memory and processor；
Wherein, the memory is used to store one or more computer instruction, one or more computer instruction
Call and perform for the processor；
The processor be used to call perform one or more computer instruction to realize such as aforementioned first aspect or
Method described in its various implementation.
Fourth aspect, the embodiment of the present invention provide a kind of computer-readable storage medium, store one or more computer and refer to
Order, the side as described in aforementioned first aspect or its various implementation is realized when one or more computer instruction is performed
Method.
The embodiment of the present invention is advantageous to provide effective, comprehensive data support for data base optimization.
The aspects of the invention or other aspects can more straightforwards in the following description.
Brief description of the drawings
In order to illustrate more clearly about the embodiment of the present invention or technical scheme of the prior art, below will be to embodiment or existing
There is the required accompanying drawing used in technology description to be briefly described, it should be apparent that, drawings in the following description are this hairs
Some bright embodiments, for those of ordinary skill in the art, on the premise of not paying creative work, can be with root
Other accompanying drawings are obtained according to these accompanying drawings.
Fig. 1 shows the schematic flow sheet of query statement processing method according to an embodiment of the invention；
Fig. 2 shows the schematic flow sheet of query statement processing method in accordance with another embodiment of the present invention；
Fig. 3 shows the schematic flow sheet of the query statement processing method according to further embodiment of the present invention；
Fig. 4 shows the block diagram of query statement processing unit according to an embodiment of the invention；
Fig. 5 shows the block diagram of query statement processing unit in accordance with another embodiment of the present invention；
Fig. 6 shows the block diagram of the query statement processing unit according to further embodiment of the present invention；
Fig. 7 shows the block diagram of electronic equipment according to an embodiment of the invention.
Embodiment
In order that those skilled in the art more fully understand the present invention program, below in conjunction with the embodiment of the present invention
Accompanying drawing, the technical scheme in the embodiment of the present invention is clearly and completely described.
In some flows of description in description and claims of this specification and above-mentioned accompanying drawing, contain according to
Particular order occur multiple operations, but it should be clearly understood that these operation can not occur herein according to it is suitable
Sequence is performed or performed parallel, the sequence number such as 101,102 etc. of operation, is only used for distinguishing each different operation, sequence number
Any execution sequence is not represented for itself.In addition, these flows can include more or less operations, and these operations can
To perform or perform parallel in order.It should be noted that the description such as " first " herein, " second ", is to be used to distinguish not
Message together, equipment, module etc., do not represent sequencing, it is different types also not limit " first " and " second ".
Below in conjunction with the accompanying drawing in the embodiment of the present invention, the technical scheme in the embodiment of the present invention is carried out clear, complete
Site preparation describes, it is clear that described embodiment is only part of the embodiment of the present invention, rather than whole embodiments.It is based on
The every other embodiment that embodiment in the present invention is obtained, belongs to the scope of protection of the invention.
First, to the present invention relates to or the title that may relate to explain, these explanations are intended to make people in the art
Member is easier to understand the present invention, rather than limits the scope of the invention.
Slow inquiry：SQL statement more than setting time is inquired about.
Production environment data on line：User uses the service carried on the server and server of the actual motion of product
Production environment referred to as on line, user are recorded in data in database in using product process and are referred to as " production environment number on line
According to ".
Test environment under line：Server and clothes of the exploitation and tester of product used in research and development and test process
The service carried on business device.
Fig. 1 is a kind of query statement processing method according to embodiments of the present invention, and reference picture 1, methods described includes：
104：Parsing performs the time-consuming query statement not less than given threshold and obtains analysis result.
106：Judge whether the analysis result meets risk judgment condition based on the analysis result.The risk judgement
Condition is used to judge whether the query statement is risk query statement.Wherein, " risk query statement " refers to：It is not belonging to look into slowly
Ask sentence but the query statement of slow inquiry risk be present.
108：If the analysis result meets the risk judgment condition, the query statement is defined as risk and looked into
Ask sentence.
The method provided using the present embodiment, for prior art only determines slow query statement, by determining
Risk query statement, more fully data support can be provided for data base optimization, be advantageous to what forecast database may face
Performance issue, improve the effect of data base optimization.
Alternatively, in a kind of implementation of the present embodiment, as described in dotted line frame in Fig. 1, methods described also includes：
100：Obtain inquiry log to be analyzed.
For example, descending runtime database online based on test environment under production environment data on line and line, obtain to be analyzed
Inquiry log.In a kind of example of this implementation, inquiry log to be analyzed records the relevant information of all query statements.
Performed in another example of this implementation, in inquiry log database of record running to be analyzed it is time-consuming not less than
The relevant information of the query statement of given threshold.
102：Parse the inquiry log to be analyzed, obtain performing the time-consuming query statement not less than given threshold and/
Or slow query statement.
In this implementation, by determining slow query statement, be advantageous to data base optimization, be also beneficial to follow-up independent
It is abstracted, classified based on slow query statement or comprehensive slow query statement and risk query statement, alarm etc. and is handled.
In this implementation, by by the analysis of query statement, positioning (even follow-up solution process) from line
Production environment is preposition to test environment under line, is lost caused by can avoiding slow inquiry on line, reduces loss caused by slow inquiry.
Alternatively, in a kind of implementation of the present embodiment, in processing 106, the risk judgment condition is described
Analysis result includes following any one or more persons：Represent that the query statement causes the first element of full table scan, represents institute
Index should be used and the second key element of index is not used, represent that the wall scroll sentence has used the of interim table by stating query statement
Three elements, represent that the retrieval line number of the query statement exceedes the 4th key element of given threshold.
Fig. 2 is a kind of schematic flow sheet of query statement processing method according to embodiments of the present invention.Reference picture 2, it is described
Method includes：
200：Parsing performs the time-consuming query statement not less than given threshold and obtains analysis result.
202：Judge whether the analysis result meets risk judgment condition based on the analysis result.The risk judgement
Condition is used to judge whether the query statement is risk query statement.Wherein, " risk query statement " refers to：It is not belonging to look into slowly
Ask sentence but the query statement of slow inquiry risk be present.
204：If the analysis result meets the risk judgment condition, the query statement is defined as risk and looked into
Ask sentence.
206：Abstract processing is carried out to the risk query statement.
For example, numeral, character string to the search in former SQL statement etc. carries out symbol replacement (for example, all being replaced digital
Change N into, character be all substituted for S), so as to eliminate the influence of numerical value in specific SQL statement, find similar SQL.
208：Classification processing is carried out to the risk query statement according to the result of the abstract processing.
In the present embodiment, classify processing result can be used as storage object and alarm (that is, export, send or displaying
To related personnel or associated processing device) object.In addition, the SQL statement of same type only stores once, the content that will be stored
As the basis of alarm, so as to avoid the repetition of alarms of the SQL statement for same type.
It will be appreciated by those skilled in the art that the embodiment that Fig. 2 is provided can be with embodiment illustrated in fig. 1 or its realization side
Formula combines, and this also belongs to protection scope of the present invention.
In a kind of embodiment similar with embodiment illustrated in fig. 2, while it is based on slow query statement and risk query statement
Abstract processing is carried out, and classification processing is carried out to slow query statement and risk query statement according to abstract result.Using such
Embodiment, comprehensive slow query statement and risk query statement are classified, and can not only provide more fully data support, moreover it is possible to
The data organizational form for being easy to subsequent treatment is provided.
Fig. 3 is a kind of schematic flow sheet of query statement processing method according to embodiments of the present invention.Reference picture 3, it is described
Method includes：
300：By test environment database under the data syn-chronization of production environment data-base cluster on line to line.
Alternatively, in a kind of implementation of the present embodiment, in order to ensure synchronous efficiency, according to research and development or tester
Demand, the data synchronously needed to use.
Alternatively, in a kind of implementation of the present embodiment, online in lower database running, full dose day is recorded
Will, i.e., the SQL statement that each online lower database performs all are recorded in daily record.
302：Inquiry log is collected into daily record parsing machine, parses inquiry log.The daily record parsing machine is used for real
When parse every inquiry log, the SQL statement that is performed every time, perform database, user name, perform time-consuming, timestamp etc.
Information.
Alternatively, in a kind of implementation of the present embodiment, daily record parses machine while daily record is parsed, ceaselessly
Collector journal.After current log is parsed, parsing machine continues to parse new daily record.
304：Judge that the execution of SQL statement is time-consuming and whether exceed given threshold, if it is, performing 306, otherwise perform
310。
Alternatively, in a kind of implementation of the present embodiment, if the execution of SQL statement is time-consuming not less than setting threshold
Value, it is determined that the SQL statement is slow query statement, and the SQL statement can subsequently be stored directly as warning message.
306：SQL statement is parsed by database on line and obtains analysis result.For example, under mysql database scenes, lead to
Cross execution expain sentences and obtain the analysis result.
308：Judge SQL statement with the presence or absence of slow inquiry risk according to analysis result.If there is slow inquiry risk, then hold
Row 310.
Alternatively, in one kind of the present embodiment, in processing 308, for the SQL that the type in analysis result is all
Sentence, it is judged as being related to full table scan, determines that it has slow inquiry risk；It is empty and search row for key in analysis result
Number is judged as using index to be still not used, determines it more than the SQL statement of 1000 (concrete numerical value can customize)
There is slow inquiry risk；For the SQL statement that Extra is Using temporary, it is judged as having used interim table, it is determined that
It has slow inquiry risk；If all being calculated without problem above the line number of retrieval, if it exceeds retrieval line number threshold value, then
It is same to judge there is slow inquiry risk.
310：SQL statement is classified.
In the present embodiment, for convenience of further artificial treatment, the slow inquiry of the SQL statement of same type is avoided to repeat
Alarm, polymerize after being abstracted to problematic SQL statement, similar SQL statement is sorted out.SQL statement after abstract
Can be as the foundation of the storage of next step.
312：Store warning information.For example, the similar SQL statement after polymerization is carried out with the dimension of database and user
Storage.
314：Alarmed according to the warning information.For example, the SQL statement after will be abstract is sent to technical staff.
The method provided using the present embodiment, the process of the positioning to query statement, analysis and solution is produced from line
Environment is preposition to test environment under line, avoids slow inquiry and causes to lose on line, the loss that reparation is inquired about slowly reduces；Do not remembering
Threshold value is set when recording daily record, but accesses full dose execution journal, analysis is more comprehensively；By to performing the time not less than threshold value
SQL statement database explains on line, analyse whether slow inquiry risk point be present, foundation provided so as to be changed for SQL；
Synchronization, log collection, analysis SQL statement automate to final alarm whole process under database line, manually no longer need to consume
Take on the journal file of processing magnanimity, foundation modification SQL can be gone problem to be present more absorbedly.
Fig. 4 is a kind of block diagram of query statement processing unit according to embodiments of the present invention.Reference picture 4, the inquiry language
Sentence processing unit includes statement parsing module 40, risk judgment module 42 and risk determining module 44.It is specifically described below.
In the present embodiment, statement parsing module 40 obtains for parsing the time-consuming query statement not less than given threshold of execution
To analysis result.
In the present embodiment, risk judgment module 42 is used to judge whether the analysis result is full based on the analysis result
Sufficient risk judgment condition.The risk judgment condition is used to judge whether the query statement is risk query statement.Wherein,
" risk query statement " refers to：It is not belonging to slow query statement but the query statement of slow inquiry risk is present.
Alternatively, in a kind of implementation of the present embodiment, the risk judgment condition is that the analysis result includes
Any one or more persons below：Represent that the query statement causes the first element of full table scan, represents that the query statement should
The second key element of index is not used using index, represents that the wall scroll sentence has used the three elements of interim table, represented for this
The retrieval line number of the query statement exceedes the 4th key element of given threshold.
In the present embodiment, risk determining module 44 is used for when the analysis result meets the risk judgment condition,
The query statement is defined as risk query statement.
The device provided using the present embodiment, for prior art only determines slow query statement, by determining
Risk query statement, more fully data support can be provided for data base optimization, be advantageous to what forecast database may face
Performance issue, improve the effect of data base optimization.
Fig. 5 is a kind of block diagram of query statement processing unit according to embodiments of the present invention.Reference picture 5, described device bag
Include log acquisition module 50, log analyzing module 52, statement parsing module 40, risk judgment module 42 and risk determining module
44.It is specifically described below.
In the present embodiment, log acquisition module 50 is used to obtain inquiry log to be analyzed.For example, it is based on producing on line
Test environment descends runtime database online under environmental data and line, obtains inquiry log to be analyzed.In a kind of example, treat point
The inquiry log of analysis records the relevant information of all query statements.In another example, inquiry log to be analyzed record number
According to the relevant information that the time-consuming query statement not less than given threshold is performed in the running of storehouse.
In the present embodiment, log analyzing module 52 obtains the inquiry language for parsing the inquiry log to be analyzed
Sentence and/or slow query statement.
In the present embodiment, statement parsing module 40 obtains for parsing the time-consuming query statement not less than given threshold of execution
To analysis result；Risk judgment module 42 is used to judge whether the analysis result meets that risk is adjudicated based on the analysis result
Condition；Risk determining module 44 is used for when the analysis result meets the risk judgment condition, and the query statement is true
It is set to risk query statement.
The device provided using the present embodiment, by determining slow query statement, is advantageous to data base optimization, is also beneficial to
Subsequently it is based solely on slow query statement or comprehensive slow query statement and risk query statement is abstracted, classified, alarmed etc.
Reason.
Using the present embodiment provide device, by by the analysis of query statement, positioning (even follow-up solution
Journey) production environment is preposition to test environment under line from line, and lost caused by can avoiding slow inquiry on line, reduce slow inquiry and make
Into loss.
Fig. 6 is a kind of block diagram of query statement processing unit according to embodiments of the present invention.Reference picture 6, the inquiry language
Sentence processing unit includes statement parsing module 40, risk judgment module 42, risk determining module 44, abstract module 60 and classification mould
Block 62.It is specifically described below.
In the present embodiment, statement parsing module 40 obtains for parsing the time-consuming query statement not less than given threshold of execution
To analysis result；Risk judgment module 42 is used to judge whether the analysis result meets that risk is adjudicated based on the analysis result
Condition；Risk determining module 44 is used for when the analysis result meets the risk judgment condition, and the query statement is true
It is set to risk query statement.
In the present embodiment, abstract module 60 is used to carry out the risk query statement abstract processing.For example, to original
It is (for example, be all substituted for N by digital, character is whole that the numeral of search in SQL statement, character string etc. carry out symbol replacement
It is substituted for S), so as to eliminate the influence of numerical value in specific SQL statement, find similar SQL.
In the present embodiment, sort module 62 is used to enter the risk query statement according to the result of the abstract processing
Row classification is handled.The result handled of classifying can be used as storage object and alarm (that is, export, send or show related personnel
Or associated processing device) object.In addition, the SQL statement of same type only stores once, the content that will be stored is as alarm
Basis, so as to avoid the repetition of alarms of the SQL statement for same type.
It will be appreciated by those skilled in the art that the embodiment that Fig. 6 is provided can be with embodiment illustrated in fig. 5 or its realization side
Formula combines, and this also belongs to protection scope of the present invention.
In the other embodiments similar with the present embodiment, abstract module 60 can be used for slow query statement and/or wind
Dangerous query statement carries out abstract processing；Sort module 62 is then used for the result according to the abstract processing to the slow query statement
And/or the risk query statement carries out classification processing.
Fig. 7 is the block diagram of a kind of electronic equipment according to embodiments of the present invention.The electronic equipment is used to enter query statement
Row processing, supported to provide data for data base optimization.Reference picture 7, the electronic equipment include memory 70 and processor
72.Wherein, the memory 70 is used to store one or more computer instruction, and one or more computer instruction supplies institute
State processor 72 and call execution；The processor 72, which is used to call, performs one or more computer instruction to realize such as figure
The query statement processing method that any embodiment or its implementation shown in 1- Fig. 3 are provided.
Optionally, in addition, as shown in dotted line frame in Fig. 7, the electronic equipment can also include input/output interface, be used for
Enter row data communication.
A kind of embodiment of the present invention also provides a kind of computer-readable storage medium, and it is stored with one or more computer and referred to
Order, any embodiment or its implementation as shown in Figure 1-Figure 3 are realized when one or more computer instruction is performed
The query statement processing method provided.
It is apparent to those skilled in the art that for convenience and simplicity of description, the system of foregoing description,
The specific work process of device and unit, the corresponding process in preceding method embodiment is may be referred to, will not be repeated here.
Device embodiment described above is only schematical, wherein the unit illustrated as separating component can
To be or may not be physically separate, it can be as the part that unit is shown or may not be physics list
Member, you can with positioned at a place, or can also be distributed on multiple NEs.It can be selected according to the actual needs
In some or all of module realize the purpose of this embodiment scheme.Those of ordinary skill in the art are not paying creativeness
Work in the case of, you can to understand and implement.
Through the above description of the embodiments, those skilled in the art can be understood that each embodiment can
Realized by the mode of software plus required general hardware platform, naturally it is also possible to pass through hardware.Based on such understanding, on
The part that technical scheme substantially in other words contributes to prior art is stated to embody in the form of software product, should
Computer software product can store in a computer-readable storage medium, such as ROM/RAM, magnetic disc, CD, including some fingers
Make to cause a computer equipment (can be personal computer, server, or network equipment etc.) to perform each implementation
Method described in some parts of example or embodiment.
Finally it should be noted that：The above embodiments are merely illustrative of the technical solutions of the present invention, rather than its limitations；Although
The present invention is described in detail with reference to the foregoing embodiments, it will be understood by those within the art that：It still may be used
To be modified to the technical scheme described in foregoing embodiments, or equivalent substitution is carried out to which part technical characteristic；
And these modification or replace, do not make appropriate technical solution essence depart from various embodiments of the present invention technical scheme spirit and
Scope.
The present invention discloses A1, a kind of query statement processing method, and methods described includes：
Parsing performs the time-consuming query statement not less than given threshold and obtains analysis result；
Judge whether the analysis result meets risk judgment condition based on the analysis result；
If the analysis result meets the risk judgment condition, the query statement is defined as risk inquiry language
Sentence.
A2, the method as described in A1, methods described also include：
Runtime database is descended based on test environment under production environment data on line and line online, obtains inquiry day to be analyzed
Will；
Parse the inquiry log to be analyzed and obtain the query statement and/or slow query statement.
A3, the method as described in A1, the risk judgment condition are：
The analysis result includes following any one or more persons：
Represent the query statement cause the first element of full table scan, represent the query statement should use index and
The second key element of index is not used, represents that the wall scroll sentence has used the three elements of interim table, represented the query statement
Retrieval line number exceed given threshold the 4th key element.
A4, the method as any one of A1-A3, methods described also include：
Abstract processing is carried out to the risk query statement；
Classification processing is carried out to the risk query statement according to the result of the abstract processing.
A5, the method as described in A2, methods described also include：
Abstract processing is carried out to the slow query statement and/or the risk query statement；
The slow query statement and/or the risk query statement are carried out at classification according to the result of the abstract processing
Reason.
Invention additionally discloses：B6, a kind of query statement processing unit, described device include：
Statement parsing module, analysis result is obtained for parsing the time-consuming query statement not less than given threshold of execution；
Risk judgment module, for judging whether the analysis result meets that risk adjudicates bar based on the analysis result
Part；
Risk determining module, for when the analysis result meets the risk judgment condition, by the query statement
It is defined as risk query statement.
B7, the device as described in B6, described device also include：
Log acquisition module, for descending runtime database online based on test environment under production environment data on line and line,
Obtain inquiry log to be analyzed.
Log analyzing module, the query statement and/or slow inquiry are obtained for parsing the inquiry log to be analyzed
Sentence.
B8, the device as described in B6, the risk judgment condition are：
The analysis result includes following any one or more persons：
Represent the query statement cause the first element of full table scan, represent the query statement should use index and
The second key element of index is not used, represents that the wall scroll sentence has used the three elements of interim table, represented the query statement
Retrieval line number exceed given threshold the 4th key element.
B9, the device as any one of B6-B8, described device also include：
First abstract module, for carrying out abstract processing to the risk query statement；
First sort module, the risk query statement is carried out at classification for the result according to the abstract processing
Reason.
B10, the device as described in B7, described device also include：
Second abstract module, for carrying out abstract processing to the slow query statement and/or the risk query statement；
Second sort module, for the result according to the abstract processing to the slow query statement and/or the risk
Query statement carries out classification processing.
Invention additionally discloses C11, a kind of electronic equipment, including memory and processor；
Wherein, the memory is used to store one or more computer instruction, one or more computer instruction
Call and perform for the processor；
The processor, which is used to call, performs one or more computer instruction to realize following processing：
Parsing performs the time-consuming query statement not less than given threshold and obtains analysis result；
Judge whether the analysis result meets risk judgment condition based on the analysis result；
If the analysis result meets the risk judgment condition, the query statement is defined as risk inquiry language
Sentence.
C12, the electronic equipment as described in C11, the processor, which is additionally operable to call, performs one or more computer
Instruct to realize following processing：
Runtime database is descended based on test environment under production environment data on line and line online, obtains inquiry day to be analyzed
Will；
Parse the inquiry log to be analyzed and obtain the query statement and/or slow query statement.
C13, the electronic equipment as described in C11, the risk judgment condition are：
The analysis result includes following any one or more persons：
Represent the query statement cause the first element of full table scan, represent the query statement should use index and
The second key element of index is not used, represents that the wall scroll sentence has used the three elements of interim table, represented the query statement
Retrieval line number exceed given threshold the 4th key element.
C14, the electronic equipment as any one of C11-C13, the processor, which is additionally operable to call, performs described one
Or a plurality of computer instruction is to realize following processing：
Abstract processing is carried out to the risk query statement；
Classification processing is carried out to the risk query statement according to the result of the abstract processing.
C15, the electronic equipment as described in C12, the processor, which is additionally operable to call, performs one or more computer
Instruct to realize following processing：
Abstract processing is carried out to the slow query statement and/or the risk query statement；
The slow query statement and/or the risk query statement are carried out at classification according to the result of the abstract processing
Reason.
Invention additionally discloses D16, a kind of computer-readable storage medium, one or more computer instruction is stored, described one
Following handle is realized when bar or a plurality of computer instruction are performed：
Parsing performs the time-consuming query statement not less than given threshold and obtains analysis result；
Judge whether the analysis result meets risk judgment condition based on the analysis result；
If the analysis result meets the risk judgment condition, the query statement is defined as risk inquiry language
Sentence.
D17, the computer-readable storage medium as described in D16, when one or more computer instruction is performed realize with
Lower processing：
Runtime database is descended based on test environment under production environment data on line and line online, obtains inquiry day to be analyzed
Will；
Parse the inquiry log to be analyzed and obtain the query statement and/or slow query statement.
D18, the computer-readable storage medium as described in D16, the risk judgment condition are：
The analysis result includes following any one or more persons：
Represent the query statement cause the first element of full table scan, represent the query statement should use index and
The second key element of index is not used, represents that the wall scroll sentence has used the three elements of interim table, represented the query statement
Retrieval line number exceed given threshold the 4th key element.
D19, the computer-readable storage medium as any one of D16-D18, the one or more computer instruction quilt
Following handle is realized during execution：
Abstract processing is carried out to the risk query statement；
Classification processing is carried out to the risk query statement according to the result of the abstract processing.
D20, the computer-readable storage medium as described in D17, when one or more computer instruction is performed realize with
Lower processing：
Abstract processing is carried out to the slow query statement and/or the risk query statement；
The slow query statement and/or the risk query statement are carried out at classification according to the result of the abstract processing
Reason.
Claims (10)
1. a kind of query statement processing method, it is characterised in that methods described includes：
Parsing performs the time-consuming query statement not less than given threshold and obtains analysis result；
Judge whether the analysis result meets risk judgment condition based on the analysis result；
If the analysis result meets the risk judgment condition, the query statement is defined as risk query statement.
2. the method as described in claim 1, it is characterised in that methods described also includes：
Runtime database is descended based on test environment under production environment data on line and line online, obtains inquiry log to be analyzed；
Parse the inquiry log to be analyzed and obtain the query statement and/or slow query statement.
3. the method as described in claim 1, it is characterised in that the risk judgment condition is：
The analysis result includes following any one or more persons：
Represent that the query statement causes the first element of full table scan, represents that the query statement should use index and not make
Second key element of index of reference, represent that the wall scroll sentence has used the three elements of interim table, represented the inspection of the query statement
Rope line number exceedes the 4th key element of given threshold.
4. such as the method any one of claim 1-3, it is characterised in that methods described also includes：
Abstract processing is carried out to the risk query statement；
Classification processing is carried out to the risk query statement according to the result of the abstract processing.
5. method as claimed in claim 2, it is characterised in that methods described also includes：
Abstract processing is carried out to the slow query statement and/or the risk query statement；
Classification processing is carried out to the slow query statement and/or the risk query statement according to the result of the abstract processing.
6. a kind of query statement processing unit, it is characterised in that described device includes：
Statement parsing module, analysis result is obtained for parsing the time-consuming query statement not less than given threshold of execution；
Risk judgment module, for judging whether the analysis result meets risk judgment condition based on the analysis result；
Risk determining module, for when the analysis result meets the risk judgment condition, the query statement to be determined
For risk query statement.
7. device as claimed in claim 6, it is characterised in that described device also includes：
Log acquisition module, for descending runtime database online based on test environment under production environment data on line and line, obtain
Inquiry log to be analyzed.
Log analyzing module, the query statement and/or slow query statement are obtained for parsing the inquiry log to be analyzed.
8. device as claimed in claim 6, it is characterised in that the risk judgment condition is：
The analysis result includes following any one or more persons：
Represent that the query statement causes the first element of full table scan, represents that the query statement should use index and not make
Second key element of index of reference, represent that the wall scroll sentence has used the three elements of interim table, represented the inspection of the query statement
Rope line number exceedes the 4th key element of given threshold.
9. such as the device any one of claim 6-8, it is characterised in that described device also includes：
First abstract module, for carrying out abstract processing to the risk query statement；
First sort module, classification processing is carried out to the risk query statement for the result according to the abstract processing.
10. device as claimed in claim 7, it is characterised in that described device also includes：
Second abstract module, for carrying out abstract processing to the slow query statement and/or the risk query statement；
Second sort module, the slow query statement and/or the risk are inquired about for the result according to the abstract processing
Sentence carries out classification processing.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710985953.1A CN107748782A (en) | 2017-10-20 | 2017-10-20 | Query statement processing method and processing device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710985953.1A CN107748782A (en) | 2017-10-20 | 2017-10-20 | Query statement processing method and processing device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN107748782A true CN107748782A (en) | 2018-03-02 |
Family
ID=61253944
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710985953.1A Pending CN107748782A (en) | 2017-10-20 | 2017-10-20 | Query statement processing method and processing device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107748782A (en) |
Cited By (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108509530A (en) * | 2018-03-14 | 2018-09-07 | 武汉斗鱼网络科技有限公司 | A kind of slow query statement automatic optimization method of MySQL, computer equipment and storage medium |
| CN109325043A (en) * | 2018-08-22 | 2019-02-12 | 北京小度信息科技有限公司 | A kind of method, apparatus and electronic equipment marking SQL statement |
| CN111913937A (en) * | 2020-07-31 | 2020-11-10 | 中国工商银行股份有限公司 | Database operation and maintenance method and device |
| CN112084211A (en) * | 2020-10-12 | 2020-12-15 | 北京高因科技有限公司 | Slow SQL statement processing system |
| CN112506951A (en) * | 2020-12-07 | 2021-03-16 | 海南车智易通信息技术有限公司 | Processing method, server, computing device and system for database slow query log |
| CN112613176A (en) * | 2020-12-23 | 2021-04-06 | 贝壳技术有限公司 | Slow SQL statement prediction method and system |
| CN112783914A (en) * | 2019-11-01 | 2021-05-11 | 北京京东振世信息技术有限公司 | Statement optimization method and device |
| CN113220705A (en) * | 2020-02-06 | 2021-08-06 | 北京沃东天骏信息技术有限公司 | Slow query identification method and device |
| CN113255007A (en) * | 2021-06-25 | 2021-08-13 | 蓝象智联(杭州)科技有限公司 | Safe hidden three-factor query method |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103390066A (en) * | 2013-08-08 | 2013-11-13 | 上海新炬网络技术有限公司 | Database overall automation optimizing early warning device and processing method thereof |
| CN104866580A (en) * | 2015-05-26 | 2015-08-26 | 梁猛 | Method for quickly detecting impact caused by database modification to current service |
| CN105243147A (en) * | 2015-10-22 | 2016-01-13 | 浪潮(北京)电子信息产业有限公司 | Slow query log management method and system of MySQL database |
| CN105373607A (en) * | 2015-11-13 | 2016-03-02 | 国网智能电网研究院 | Method for compressing SQL access log of power business system |
| CN105653607A (en) * | 2015-12-23 | 2016-06-08 | 北京奇虎科技有限公司 | SQL (Structured Query Language) log collection and analysis method and device |
| CN105989137A (en) * | 2015-02-27 | 2016-10-05 | ***通信集团河北有限公司 | Structured query language performance optimization method and system |
-
2017
- 2017-10-20 CN CN201710985953.1A patent/CN107748782A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103390066A (en) * | 2013-08-08 | 2013-11-13 | 上海新炬网络技术有限公司 | Database overall automation optimizing early warning device and processing method thereof |
| CN105989137A (en) * | 2015-02-27 | 2016-10-05 | ***通信集团河北有限公司 | Structured query language performance optimization method and system |
| CN104866580A (en) * | 2015-05-26 | 2015-08-26 | 梁猛 | Method for quickly detecting impact caused by database modification to current service |
| CN105243147A (en) * | 2015-10-22 | 2016-01-13 | 浪潮(北京)电子信息产业有限公司 | Slow query log management method and system of MySQL database |
| CN105373607A (en) * | 2015-11-13 | 2016-03-02 | 国网智能电网研究院 | Method for compressing SQL access log of power business system |
| CN105653607A (en) * | 2015-12-23 | 2016-06-08 | 北京奇虎科技有限公司 | SQL (Structured Query Language) log collection and analysis method and device |
Non-Patent Citations (1)
| Title |
|---|
| COBRA: "详解慢查询", 《HTTPS://WWW.W3CSCHOOL.CN/GDFJU6/AXR1QOZT.HTML》 * |
Cited By (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108509530A (en) * | 2018-03-14 | 2018-09-07 | 武汉斗鱼网络科技有限公司 | A kind of slow query statement automatic optimization method of MySQL, computer equipment and storage medium |
| CN109325043A (en) * | 2018-08-22 | 2019-02-12 | 北京小度信息科技有限公司 | A kind of method, apparatus and electronic equipment marking SQL statement |
| CN112783914A (en) * | 2019-11-01 | 2021-05-11 | 北京京东振世信息技术有限公司 | Statement optimization method and device |
| CN112783914B (en) * | 2019-11-01 | 2023-09-01 | 北京京东振世信息技术有限公司 | Method and device for optimizing sentences |
| CN113220705A (en) * | 2020-02-06 | 2021-08-06 | 北京沃东天骏信息技术有限公司 | Slow query identification method and device |
| CN111913937A (en) * | 2020-07-31 | 2020-11-10 | 中国工商银行股份有限公司 | Database operation and maintenance method and device |
| CN111913937B (en) * | 2020-07-31 | 2024-03-08 | 中国工商银行股份有限公司 | Database operation and maintenance method and device |
| CN112084211A (en) * | 2020-10-12 | 2020-12-15 | 北京高因科技有限公司 | Slow SQL statement processing system |
| CN112506951A (en) * | 2020-12-07 | 2021-03-16 | 海南车智易通信息技术有限公司 | Processing method, server, computing device and system for database slow query log |
| CN112613176A (en) * | 2020-12-23 | 2021-04-06 | 贝壳技术有限公司 | Slow SQL statement prediction method and system |
| CN113255007A (en) * | 2021-06-25 | 2021-08-13 | 蓝象智联(杭州)科技有限公司 | Safe hidden three-factor query method |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107748782A (en) | Query statement processing method and processing device | |
| CN107147639A (en) | A kind of actual time safety method for early warning based on Complex event processing | |
| CN105677615B (en) | A kind of distributed machines learning method based on weka interface | |
| CN111459799A (en) | Software defect detection model establishing and detecting method and system based on Github | |
| CN110765639A (en) | Electrical simulation modeling method and device and readable storage medium | |
| CN107577724A (en) | A kind of big data processing method | |
| CN112506750A (en) | Big data processing system for mass log analysis and early warning | |
| CN113505048A (en) | Unified monitoring platform based on application system portrait and implementation method | |
| CN114201328A (en) | Fault processing method and device based on artificial intelligence, electronic equipment and medium | |
| Baembitov et al. | Fast extraction and characterization of fundamental frequency events from a large PMU dataset using big data analytics | |
| CN113157795A (en) | Power grid regulation and control operation multi-source data modeling and management system suitable for mobile application | |
| CN112825165A (en) | Project quality management method and device | |
| CN108073582A (en) | A kind of Computational frame selection method and device | |
| CN112598142B (en) | Wind turbine maintenance working quality inspection auxiliary method and system | |
| CN112115234A (en) | Question bank analysis method and device | |
| CN111352820A (en) | Method, equipment and device for predicting and monitoring running state of high-performance application | |
| CN110515926A (en) | Heterogeneous data source mass data carding method based on participle and semantic dependency analysis | |
| CN111241145A (en) | Self-healing rule mining method and device based on big data | |
| CN115765153A (en) | Method and system for fusion monitoring of Internet of things and online monitoring data of primary electric power equipment | |
| CN115186935A (en) | Electromechanical device nonlinear fault prediction method and system | |
| CN114465875A (en) | Fault processing method and device | |
| KR102268549B1 (en) | Big data platform managing method and device | |
| CN114626433A (en) | Fault prediction and classification method, device and system for intelligent electric energy meter | |
| CN116049700B (en) | Multi-mode-based operation and inspection team portrait generation method and device | |
| CN116681400A (en) | Intelligent management system based on big data |
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 | ||
| CB02 | Change of applicant information | ||
| CB02 | Change of applicant information |
Address after: Building N3, building 12, No. 27, Jiancai Chengzhong Road, Haidian District, Beijing 100096 Applicant after: Beijing Xingxuan Technology Co.,Ltd. Address before: 100085 Beijing, Haidian District on the road to the information on the ground floor of the 1 to the 3 floor of the 2 floor, room 11, 202 Applicant before: Beijing Xiaodu Information Technology Co.,Ltd. |
|
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20180302 |