CN116521707A - Analysis method and system for index performance of pre-execution SQL statement - Google Patents

Abstract

The invention discloses an analysis method for index performance of a pre-execution SQL sentence, which aims to analyze the rationality of index creation and judge whether an index fails, so as to analyze whether the index performance of the pre-execution SQL sentence can reach expectations; the analysis system for the index performance of the pre-execution SQL statement is also provided; the technical scheme is as follows: a method for analyzing index performance of a pre-execution SQL statement comprises the following steps: step 1: the configuration database is connected with the target database to acquire database information; step 2: acquiring a pre-execution SQL sentence, and analyzing the pre-execution SQL sentence to obtain first data; step 3: positioning a target database table in a database based on the first data, and acquiring a data structure and an index list of the target database table; determining the index length limit of each field according to the data structure, and judging whether the index field in the index list exceeds the index length limit; belongs to the technical field of databases.

Description

Analysis method and system for index performance of pre-execution SQL statement

Technical Field

The invention belongs to the technical field of databases, and particularly relates to an analysis method and an analysis system for index performance of a pre-execution SQL sentence.

Background

The system development is not separated from data storage, namely, the system development means that the system needs to make a way with a database, and writing of SQL sentences is indestructible; the execution performance of the SQL statement is determined by various factors, such as server performance and broadband, but how to greatly improve the execution performance of the SQL statement is not just to create a proper index in a database table, the function of the index in the database is similar to that of a directory in a book, and the index is used for improving the speed of searching data, and is a list of values contained in a table, wherein the storage position of a row containing each value in the table is specified, when the index is used for searching data, the storage position of a relevant column is obtained from an index object, and then the storage position is directly used for searching the required information, so that the table is not required to be scanned, and the required data can be quickly found;

the initially built SQL statement is a pre-execution SQL statement, in practice, the pre-execution SQL statement often has index failure or index execution performance failing to reach the expected condition because of improper statement writing or created index fields, and in the prior art, no method capable of judging the index performance of the pre-execution SQL statement is available, and whether the index of the pre-execution SQL statement fails or not cannot be known.

Disclosure of Invention

The invention mainly aims to provide an analysis method for index performance of a pre-execution SQL statement, which aims to analyze the rationality of index creation and judge whether the index is invalid so as to analyze whether the index performance of the pre-execution SQL statement can reach expectations; an analysis system for pre-executing SQL statement index performance is also provided.

According to a first aspect of the present invention, there is provided a method for analyzing index performance of a pre-executed SQL statement, comprising the steps of:

step 1: the configuration database is connected with the target database to acquire database information;

step 2: acquiring a pre-execution SQL sentence, and analyzing the pre-execution SQL sentence to obtain first data;

step 3: positioning a target database table in a database based on the first data, and acquiring a data structure and an index list of the target database table; and determining the index length limit of each field according to the data structure, and judging whether the index field in the index list exceeds the index length limit.

In the above analysis method for index performance of the pre-executed SQL statement, the first data includes a target database table for query of the pre-executed SQL statement and an index column in the target database table.

In the above analysis method for index performance of the pre-execution SQL statement, the data structure includes field information of the target database table.

The method for analyzing the index performance of the pre-execution SQL statement further comprises the following step 4: a plurality of judgment strategies are predefined, and key word groups corresponding to the judgment strategies one by one are set;

and detecting whether a keyword group appears in the pre-execution SQL sentence, and if so, analyzing whether an index in the pre-execution SQL sentence is valid or not based on a judging strategy corresponding to keyword matching.

The method for analyzing the index performance of the pre-execution SQL statement further comprises the following step 5: and simulating the execution of the pre-execution SQL statement by using the displain command in MYSQL to obtain the execution plan of the pre-execution SQL statement.

According to a second aspect of the present invention, there is provided an analysis system for performing the indexing performance of the pre-executed SQL statement of the method of the first aspect, comprising:

configuration unit: the method comprises the steps of establishing connection with a target database to obtain database information;

a first acquisition unit: the method comprises the steps of inputting a pre-execution SQL sentence, and analyzing the pre-execution SQL sentence to obtain first data;

a second acquisition unit: positioning a target database table in a database based on the first data, and acquiring a data structure and an index list of the target database table;

a first analysis unit: and determining the index length limit of each field according to the data structure, and judging whether the index field in the index list exceeds the index length limit.

The above analysis system for pre-executing SQL statement index performance further comprises a second analysis unit: a plurality of judgment strategies are predefined, and key word groups corresponding to the judgment strategies one by one are set; and detecting whether a keyword group appears in the pre-execution SQL sentence, and if so, analyzing whether an index in the pre-execution SQL sentence is valid or not based on a judging strategy corresponding to keyword matching.

In the above analysis system for pre-executing the index performance of the SQL statement, the second analysis unit includes a storage module, a read-write module, and a comparison module;

and a storage module: the method is used for storing judgment strategies and enabling the judgment strategies to correspond to the key word groups one by one;

and a read-write module: the method is used for writing, deleting or modifying the judging strategy and the keyword group, and is used for establishing, deleting or modifying the corresponding relation between the keyword group and the judging strategy;

and a comparison module: detecting whether a key phrase appears in the pre-execution SQL sentence; if yes, whether the index in the pre-execution SQL statement is valid or not is analyzed based on the judging strategy corresponding to the keyword matching.

The above analysis system for pre-executing SQL sentence index performance further comprises a simulation unit: and simulating the execution of the pre-execution SQL statement by using the displain command in MYSQL to obtain the execution plan of the pre-execution SQL statement.

One of the above technical solutions of the present invention has at least one of the following advantages or beneficial effects:

in the invention, the rationality of index creation is firstly judged, the field information of a target database table is utilized to determine the index length, and then whether the index in the pre-execution SQL statement is within the limit range of the index length is judged, so that the rationality of index creation is judged;

if the index creation is reasonable, further judging whether the index in the pre-execution SQL sentence fails, predefining a plurality of judging strategies, setting key word groups corresponding to the judging strategies one by one, detecting whether the key word groups appear in the pre-execution SQL sentence, and if so, analyzing whether the index in the pre-execution SQL sentence is effective based on the judging strategies corresponding to the key word matching;

under the condition that index creation is reasonable, the index in the pre-execution SQL statement is also effective, so that the index performance of the pre-execution SQL statement can be basically judged to reach expectations; once the index is unreasonable to be created, or the index in the pre-execution SQL statement fails, it is determined that the index performance of the pre-execution SQL statement is not as expected and further optimization is required.

Drawings

The invention is further described below with reference to the drawings and examples;

FIG. 1 is a flow chart of a method for analyzing the indexing performance of a pre-executed SQL statement according to an embodiment of the invention;

FIG. 2 is a schematic diagram of an analysis system for pre-execution SQL statement index performance according to one embodiment of the invention.

Detailed Description

Embodiments of the present invention are described in detail below, examples of which are illustrated in the accompanying drawings, wherein like or similar reference numerals refer to like or similar elements or elements having like or similar functions throughout. The embodiments described below by referring to the drawings are exemplary only for explaining the present invention and are not to be construed as limiting the present invention.

The initially built SQL statement is a pre-execution SQL statement, in practice, the pre-execution SQL statement often has index failure or index execution performance failing to reach the expected condition because of improper statement writing or created index fields, and in the prior art, no method capable of judging the index performance of the pre-execution SQL statement is available, and whether the index of the pre-execution SQL statement fails or not cannot be known.

Referring to fig. 1, in one embodiment of the present invention, an analysis method for index performance of a pre-executed SQL statement is provided, by analyzing a data structure of a database table, determining the rationality of index creation, and then using a predefined index failure policy to analyze whether an index used by the pre-executed SQL statement is valid, so as to determine whether the index performance of the pre-executed SQL statement can achieve an expected effect.

The analysis method for the index performance of the pre-execution SQL statement comprises the following steps:

step 1: the configuration database is connected with the target database to acquire database information;

step 2: acquiring a pre-execution SQL sentence, and analyzing the pre-execution SQL sentence to obtain first data; the first data comprises a target database table for pre-executing SQL statement query and an index column in the target database table;

in this embodiment, taking the pre-execution SQL statement "select × from student where Sname like% by tension"% as an example, the target database table of the query is "student database table", the index is "tension", and the index in the target database table is "name", so the first data includes "student database table" and "name".

Step 3: positioning a target database table in a database based on first data, and acquiring a data structure and an index list of the target database table, wherein the data structure comprises field information of the target database table, each field of the database table has an index length required to be stored when an index is created according to the data type of the field information, and the maximum length of an index field generally depends on a storage engine; the index list of the database table can be obtained by utilizing the corresponding query statement, whether the index field in the index list exceeds the index length is judged, if the index field exceeds the index length, the index creation is judged to be unreasonable, and if the index field does not exceed the index length, the index creation is judged to be reasonable;

it can be known that, based on the "student database table" of the first data, the target database table of the database can be located to be the "student database table", and the data structure of the "student database table" is obtained, so that the field information of the "student database table" is known, and the index length limitation of each field is determined; then, the index list of the "student database table" is obtained by using the query statement "show index form student", and the index of the pre-execution SQL statement is in the "name", so that it is judged whether the index field of the name column meets the index length limit thereof, if not, it is judged that the index creation is unreasonable, and if so, it is preliminarily judged that the index creation is reasonable.

Analyzing whether the index is invalid or not under the condition that the index creation is judged to be reasonable;

step 4: a plurality of judging strategies are defined in advance, key word groups corresponding to the judging strategies one by one are set, whether the key word groups appear in the pre-execution SQL statement is detected, and if yes, whether indexes in the pre-execution SQL statement are effective is analyzed based on the judging strategies corresponding to the key word matching;

for example, a first judgment policy: the following query field of where uses like word eyes, if like appears, judging index failure; the key word groups of the first judging strategy are "where" and "like", whether the pre-execution SQL statement has "where" and "like" are judged first, taking "select from student where Sname like% of the open%" as an example, the pre-execution SQL statement has "where" and "like" and then matches the first judging strategy to analyze the pre-execution SQL statement, when the pre-execution SQL statement is analyzed to have like%, the index of the pre-execution SQL statement can be judged to fail, because like% only has two cases, one is like% of the open, the other is like% of the open%, and no matter whether left fuzzy matching or left and right fuzzy matching is carried out, the index is invalid;

after judging that the index of the pre-execution SQL statement fails, recording corresponding failure reasons.

Under the condition that index creation is reasonable, the index in the pre-execution SQL statement is also effective, so that the index performance of the pre-execution SQL statement can be basically judged to reach expectations; once the index is unreasonable to be created, or the index in the pre-execution SQL statement fails, it is determined that the index performance of the pre-execution SQL statement is not as expected and further optimization is required.

In this embodiment, the method further includes step 5: simulating the execution of the pre-execution SQL statement by using the displain command in MYSQL to obtain an execution plan of the pre-execution SQL statement;

according to the execution plan, how the MYSQL processes the SQL statement of the user can be known, how the index performance actually used is analyzed, and what indexes actually used are; in addition, the search result of the SQL search statement executed by the displain command simulation executor can output a plurality of relatively professional terms, so that an analysis step is provided, the search result can be analyzed into a large white text, the paper surface translation is made by utilizing the ID of the search result and the value of the corresponding ID, the understanding and the checking are convenient, and the analysis one by one according to the search result is not needed;

meanwhile, summarizing the results of the step 3 and the step 4, and if the index creation is unreasonable, giving out a suggestion to create a proper index; if the index fails, giving out a failure reason, and giving out a solution for the failure reason; thereby forming an optimization suggestion.

Based on the method, the analysis process of the index performance of the pre-execution SQL statement can be summarized, an index performance report is output, and a developer can quickly position the index performance problem of the pre-execution SQL statement based on the report, so that the optimization is performed, and the response speed of the system is improved.

According to a second aspect of the present invention, referring to fig. 2, there is provided an analysis system for pre-executing index performance of SQL statements, comprising:

configuration unit 1: the method comprises the steps of establishing connection with a target database to obtain database information;

the first acquisition unit 2: the method comprises the steps of inputting a pre-execution SQL sentence, and analyzing the pre-execution SQL sentence to obtain first data; the first data comprises a target database table, an index and a column of the index in the target database table for pre-executing SQL statement query;

the second acquisition unit 3: positioning a target database table in a database based on the first data, and acquiring a data structure and an index list of the target database table; the data structure comprises field information of a target database table;

the first analysis unit 4: and determining the index length limit of each field according to the data structure, and judging whether the index field in the index list exceeds the index length limit.

Further comprising a second analysis unit 5: a plurality of judgment strategies are predefined, and key word groups corresponding to the judgment strategies one by one are set; and detecting whether a keyword group appears in the pre-execution SQL sentence, and if so, analyzing whether an index in the pre-execution SQL sentence is valid or not based on a judging strategy corresponding to keyword matching.

Specifically, the second analysis unit 5 includes a storage module 51, a read-write module 52, and a comparison module 53;

the storage module 51: the method is used for storing judgment strategies and enabling the judgment strategies to correspond to the key word groups one by one;

the read-write module 52: the method is used for writing, deleting or modifying the judging strategy and the keyword group, and is used for establishing, deleting or modifying the corresponding relation between the keyword group and the judging strategy;

contrast module 53: detecting whether a key phrase appears in the pre-execution SQL sentence; if yes, whether the index in the pre-execution SQL statement is valid or not is analyzed based on the judging strategy corresponding to the keyword matching.

Also included is an analog unit 6: simulating the execution of the pre-execution SQL statement by using an explatin command of MYSQL to obtain an execution plan of the pre-execution SQL statement; according to the execution plan, how the MYSQL processes the SQL statement of the user can be known, how the index performance actually used is analyzed, and what indexes actually used are; in addition, the search result of the SQL search statement executed by the displain command simulation executor can output a plurality of relatively professional terms, so that the search result can be analyzed into a large white text by one analysis step, the understanding and the checking are convenient, and the analysis one by one according to the search result is not needed;

meanwhile, summarizing the results of the first analysis unit 4 and the second analysis unit 5, and if the index creation is unreasonable, giving a suggestion to create a proper index; if the index fails, giving out a failure reason, and giving out a solution for the failure reason; thereby forming an optimization suggestion.

While embodiments of the present invention have been shown and described, it will be understood by those of ordinary skill in the art that: many changes, modifications, substitutions and variations may be made to the embodiments without departing from the spirit and principles of the invention, the scope of which is defined by the claims and their equivalents.

Claims (9)

1. The analysis method for the index performance of the pre-execution SQL statement is characterized by comprising the following steps of:

step 1: the configuration database is connected with the target database to acquire database information;

step 2: acquiring a pre-execution SQL sentence, and analyzing the pre-execution SQL sentence to obtain first data;

step 3: positioning a target database table in a database based on the first data, and acquiring a data structure and an index list of the target database table; and determining the index length limit of each field according to the data structure, and judging whether the index field in the index list exceeds the index length limit.

2. The method of claim 1, wherein the first data comprises a target database table of the pre-executed SQL statement query and a column of the target database table in which the index is located.

3. The method of claim 1, wherein the data structure includes field information of a target database table.

4. The method for analyzing the indexing performance of the pre-executed SQL statement according to claim 1, further comprising step 4: a plurality of judgment strategies are predefined, and key word groups corresponding to the judgment strategies one by one are set;

and detecting whether a keyword group appears in the pre-execution SQL sentence, and if so, analyzing whether an index in the pre-execution SQL sentence is valid or not based on a judging strategy corresponding to keyword matching.

5. The method for analyzing the indexing performance of the pre-executed SQL statement according to claim 1, further comprising step 5: and simulating the execution of the pre-execution SQL statement by using the displain command in MYSQL to obtain the execution plan of the pre-execution SQL statement.

6. An analysis system for performing the indexing performance of the pre-executed SQL statement of the method of claims 1-5, comprising:

configuration unit: the method comprises the steps of establishing connection with a target database to obtain database information;

a first acquisition unit: the method comprises the steps of inputting a pre-execution SQL sentence, and analyzing the pre-execution SQL sentence to obtain first data;

a second acquisition unit: positioning a target database table in a database based on the first data, and acquiring a data structure and an index list of the target database table;

a first analysis unit: and determining the index length limit of each field according to the data structure, and judging whether the index field in the index list exceeds the index length limit.

7. The system for analyzing the indexing performance of the pre-executed SQL statement of claim 6, further comprising a second analyzing unit: a plurality of judgment strategies are predefined, and key word groups corresponding to the judgment strategies one by one are set; and detecting whether a keyword group appears in the pre-execution SQL sentence, and if so, analyzing whether an index in the pre-execution SQL sentence is valid or not based on a judging strategy corresponding to keyword matching.

8. The system for analyzing the index performance of the pre-executed SQL statement according to claim 7, wherein the second analyzing unit comprises a storage module, a read-write module and a comparison module;

and a storage module: the method is used for storing judgment strategies and enabling the judgment strategies to correspond to the key word groups one by one;

and a read-write module: the method is used for writing, deleting or modifying the judging strategy and the keyword group, and is used for establishing, deleting or modifying the corresponding relation between the keyword group and the judging strategy;

and a comparison module: detecting whether a key phrase appears in the pre-execution SQL sentence; if yes, whether the index in the pre-execution SQL statement is valid or not is analyzed based on the judging strategy corresponding to the keyword matching.

9. The system for analyzing the indexing performance of the pre-executed SQL statement of claim 6, further comprising a simulation unit: and simulating the execution of the pre-execution SQL statement by using the displain command in MYSQL to obtain the execution plan of the pre-execution SQL statement.