CN110895529B - Processing method of structured query language and related device - Google Patents
Processing method of structured query language and related device Download PDFInfo
- Publication number
- CN110895529B CN110895529B CN201810966443.4A CN201810966443A CN110895529B CN 110895529 B CN110895529 B CN 110895529B CN 201810966443 A CN201810966443 A CN 201810966443A CN 110895529 B CN110895529 B CN 110895529B
- Authority
- CN
- China
- Prior art keywords
- sql
- statement
- constructed
- word set
- index list
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a processing method and a related device of a structured query language, which comprises the steps of firstly obtaining a statement constructed by using the Structured Query Language (SQL); performing lexical analysis on the statements constructed by utilizing SQL to obtain a word set of the statements constructed by utilizing SQL; processing the word set of the statement constructed by using the SQL by using a preset index setting rule to obtain an updated index list corresponding to the statement constructed by using the SQL; determining an original index list corresponding to the statement constructed by using SQL; and generating an index optimization suggestion of the statement constructed by using the SQL according to the comparison result of the updated index list and the original index list. According to the invention, the index optimization of the statement constructed by using SQL can be completed through the index setting rule, manual operation is not required, the optimization efficiency is improved, and the optimized program performance is ensured.
Description
Technical Field
The invention relates to the field of data processing, in particular to a method and a device for processing a structured query language.
Background
Structured Query Language (SQL) is a database Query and programming Language for accessing data and querying, updating, and managing relational database systems. In the project development and database operation and maintenance processes, in order to improve program performance and reduce online failure rate, SQL needs to be optimized frequently.
The currently common SQL processing method is to execute an SQL statement by using a previously established index, and then manually analyze an execution result of the SQL statement. When the programmer finds that the previously established index does not meet the requirements, the programmer modifies the previously established index in a mode of manually adjusting the index or adding a new index, executes SQL statements by using the modified index and analyzes the execution result, and the steps are repeated until a satisfactory execution result is obtained. The whole optimization process is huge in workload, time-consuming and low in optimization efficiency, the optimization quality is very dependent on the working experience of programmers, and the optimized program performance cannot be guaranteed.
Disclosure of Invention
The invention provides a processing method and a processing device for a structured query language, which can efficiently complete the optimization work of SQL, find the problems in the SQL in time and improve the program performance.
In order to achieve the purpose, the invention provides the following technical scheme:
a processing method of a structured query language is applied to an SQL analysis plug-in, and comprises the following steps: in the process of carrying out unit test or integrated test, calling the SQL analysis plug-in to analyze the SQL sentences executed in the test process; the SQL analysis plug-in analyzes the SQL statements executed in the test process, and the SQL analysis plug-in comprises the following steps:
obtaining a statement constructed by using a Structured Query Language (SQL);
performing lexical analysis on the statements constructed by utilizing SQL to obtain a word set of the statements constructed by utilizing SQL;
extracting the where condition field in the word set of the statement constructed by using SQL, and calculating the discrimination of the extracted where condition field;
under the condition that the discrimination of the where condition fields in the word set of the statement constructed by utilizing SQL is greater than a preset threshold, writing group by fields in the word set of the statement constructed by utilizing SQL and/or order by fields in the word set of the statement constructed by utilizing SQL into an index list to obtain an updated index list corresponding to the statement constructed by utilizing SQL;
analyzing the execution plan of the statement constructed by utilizing the SQL by utilizing an analyzer to obtain an original index list corresponding to the statement constructed by utilizing the SQL;
and generating an index optimization suggestion of the statement constructed by using the SQL according to the comparison result of the updated index list and the original index list.
Optionally, when the discrimination of the where condition field in the word set of the statement constructed by using SQL is greater than a predetermined threshold, writing a groupby field in the word set of the statement constructed by using SQL into an index list to obtain an updated index list corresponding to the statement constructed by using SQL, where the updating index list includes:
under the condition that the discrimination of the where condition fields in the word set of the statement constructed by utilizing SQL is greater than a preset threshold, judging whether the group by fields in the word set of the statement constructed by utilizing SQL are effective or not;
if the group by field in the word set of the statement constructed by utilizing SQL is judged to be valid, writing the group by field in the word set of the statement constructed by utilizing SQL into an index list, and obtaining an updated index list corresponding to the statement constructed by utilizing SQL.
Optionally, after writing the group by field in the word set of the statement constructed by using SQL into the index list, the method further includes:
judging whether the orderby field in the word set of the statement constructed by utilizing SQL is effective or not;
if the orderby field in the word set of the statement constructed by utilizing SQL is judged to be effective, writing the orderby field in the word set of the statement constructed by utilizing SQL into the index list;
and writing an index list of orderby fields in the word set of the statement constructed by using SQL as the updated index list.
Optionally, the method further comprises:
executing repeated index filtering operation on the index list written into the orderby field in the word set of the statement constructed by using SQL to obtain a filtered index list;
wherein the filtered index list is used as the updated index list.
Optionally, when the discrimination of the where condition field in the word set of the statement constructed by using SQL is greater than a predetermined threshold, writing the orderby field in the word set of the statement constructed by using SQL into an index list to obtain an updated index list corresponding to the statement constructed by using SQL, where the updating index list includes:
under the condition that the discrimination of the where condition fields in the word set of the statement constructed by utilizing SQL is greater than a preset threshold, judging whether the orderby fields in the word set of the statement constructed by utilizing SQL are effective or not;
and if the orderby field in the word set of the statement constructed by utilizing the SQL is judged to be effective, writing the orderby field in the word set of the statement constructed by utilizing the SQL into an index list to obtain an updated index list corresponding to the statement constructed by utilizing the SQL.
A structured query language processing apparatus comprising an SQL analysis plug-in, the SQL analysis plug-in comprising:
the system comprises an acquisition unit, a query unit and a query unit, wherein the acquisition unit is used for acquiring statements constructed by using a Structured Query Language (SQL);
the parsing unit is used for performing lexical parsing on the statements constructed by using SQL to obtain a word set of the statements constructed by using SQL;
the processing unit is used for extracting the where condition fields in the word set of the statement constructed by using SQL and calculating the discrimination of the extracted where condition fields; under the condition that the discrimination of the where condition fields in the word set of the statement constructed by utilizing SQL is greater than a preset threshold, writing group by fields in the word set of the statement constructed by utilizing SQL and/or order by fields in the word set of the statement constructed by utilizing SQL into an index list to obtain an updated index list corresponding to the statement constructed by utilizing SQL;
the control unit is used for analyzing the execution plan of the statement constructed by utilizing the SQL by utilizing an analyzer to obtain an original index list corresponding to the statement constructed by utilizing the SQL;
and the generating unit is used for generating the index optimization suggestion of the statement constructed by using the SQL according to the comparison result of the updated index list and the original index list.
Optionally, the writing, by the processing unit, a groupby field in the word set of the statement constructed by using SQL into an index list when the degree of distinction of the where condition field in the word set of the statement constructed by using SQL is greater than a predetermined threshold is executed, and obtaining an updated index list corresponding to the statement constructed by using SQL includes:
a first judging unit, configured to judge whether a group by field in the word set of the statement constructed using SQL is valid or not when the degree of distinction of the where condition field in the word set of the statement constructed using SQL is greater than a predetermined threshold;
and the first writing unit is used for writing the group by field in the word set of the statement constructed by using SQL into an index list if the first judging unit judges that the group by field in the word set of the statement constructed by using SQL is valid, so as to obtain an updated index list corresponding to the statement constructed by using SQL.
Optionally, the method further comprises:
a second judging unit, configured to, after writing the groupby field in the word set of the SQL-based statement into an index list, judge whether the orderby field in the word set of the SQL-based statement is valid;
the second writing unit is used for writing the orderby field in the word set of the statement constructed by using SQL into the index list if the second judging unit judges that the orderby field in the word set of the statement constructed by using SQL is effective;
and writing an index list of orderby fields in the word set of the statement constructed by using SQL as the updated index list.
Optionally, the method further comprises:
the filtering unit is used for executing repeated index filtering operation on the index list written into the order by field in the word set of the statement constructed by using SQL to obtain a filtered index list;
wherein the filtered index list is used as the updated index list.
Optionally, the writing, by the processing unit, the orderby field in the word set of the SQL-built statement into an index list to obtain an updated index list corresponding to the SQL-built statement when the degree of distinction of the where condition field in the word set of the SQL-built statement is greater than a predetermined threshold includes:
a third judging unit, configured to judge whether an orderby field in the word set of the statement constructed by using SQL is valid or not when the degree of distinction of the where condition field in the word set of the statement constructed by using SQL is greater than a predetermined threshold;
and the third writing unit is used for writing the orderby field in the word set of the statement constructed by using the SQL into an index list if the third judging unit judges that the orderby field in the word set of the statement constructed by using the SQL is effective, so as to obtain an updated index list corresponding to the statement constructed by using the SQL.
A structured query language processing apparatus, comprising: a memory and a processor, wherein:
the memory is to store computer instructions;
the processor is configured to execute the computer instructions stored by the memory, and in particular, to perform the method as described in any one of the above.
A computer readable storage medium having stored therein instructions for performing a method as in any above.
According to the technical scheme, the invention discloses a processing method and a related device of a structured query language, wherein a word set of a statement constructed by using SQL is obtained through lexical analysis, the word set is processed by using a preset index setting rule to obtain an updated index list corresponding to the statement constructed by using SQL, and an index optimization suggestion of the statement constructed by using SQL is generated according to a comparison result of the updated index list and an original index list. According to the invention, the index optimization of the statement constructed by using SQL can be completed through the index setting rule, manual operation is not required, the optimization efficiency is improved, and the optimized program performance is ensured.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the provided drawings without creative efforts.
FIG. 1 is a flowchart of a method for processing a structured query language according to an embodiment of the present invention;
FIG. 2 is a flow chart of another method for processing a structured query language according to an embodiment of the present disclosure;
FIG. 3 is a schematic diagram of a processing apparatus for a structured query language according to an embodiment of the present invention;
FIG. 4 is a diagram of another apparatus for processing a structured query language according to an embodiment of the disclosure.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
The invention provides a processing method and a related device of a structured query language, which can efficiently complete the optimization work of SQL, find the problems in the SQL in time and improve the program performance.
It should be noted that, in the development process of a project or an application, when a developer completes a functional module or a system method, a unit test or an integration test is generally performed, and an SQL analysis plug-in is enabled in the test process, and the SQL analysis plug-in acquires and analyzes an SQL statement executed in the test to obtain an analysis result.
The processing method of the structured query language disclosed by the invention can automatically complete the test process, namely automatically acquire the SQL sentences executed in the test and generate the index optimization suggestion after automatically completing the analysis of the SQL sentences.
As shown in fig. 1, an embodiment of the present invention discloses a processing method for a structured query language, which is applied to an SQL analysis plug-in, and the processing method for the structured query language includes: in the process of carrying out unit test or integrated test, calling the SQL analysis plug-in to analyze the SQL sentences executed in the test process; the SQL analysis plug-in analyzes the SQL sentences executed in the test process, and comprises the following steps:
s101, obtaining a statement constructed by using a Structured Query Language (SQL).
The plug-in interception method based on mybatis is called to obtain a statement constructed by using Structured Query Language (SQL).
S102, performing lexical analysis on the statements constructed by utilizing the SQL to obtain a word set of the statements constructed by utilizing the SQL.
In step S102, the word set of the statement constructed by using SQL includes: the statement constructed by using SQL comprises a plurality of keywords and non-keywords corresponding to each keyword.
It should be noted that lexical analysis is to convert the input into individual tokens (Token). Wherein Token includes keywords (Keyword is also called symbol) and non-keywords. For example, the SQL statement select username from userinfo, after lexical parsing, would result in 4 tokens, of which there are 2 keywords, select and from, respectively, and two non-keywords, username and userinfo, respectively.
S103, processing the word set of the statement constructed by the SQL according to a preset index setting rule to obtain an updated index list corresponding to the statement constructed by the SQL.
And S104, determining an original index list corresponding to the statement constructed by utilizing the SQL.
Optionally, a parser is used to parse the execution plan of the statement constructed by using SQL to obtain an original index list corresponding to the statement constructed by using SQL.
Specifically, the execution plan of the statement constructed by using SQL is a process for executing the statement constructed by using SQL, and includes each execution step included in the execution of the statement constructed by using SQL. Since a preset index is used in the process of executing the statement constructed by SQL, the original index list corresponding to the statement constructed by SQL can be obtained by analyzing the execution plan.
S105, generating an index optimization suggestion of the statement constructed by using the SQL according to the comparison result of the updated index list and the original index list.
Specifically, the difference between the updated index list and the original index list is searched in a comparison manner, and if the difference between the updated index list and the original index list is found, a modification suggestion for the original index list is proposed in the index optimization suggestion according to the difference.
It should be noted that after the index optimization suggestion is obtained, the index optimization suggestion needs to be output. Specifically, the index optimization suggestion can be directly output to a console or a designated file, so that a user can directly check the console or the designated file to obtain the index optimization suggestion and automatically determine whether statement optimization is needed.
The embodiment discloses a processing method of a structured query language, which includes obtaining a word set of a statement constructed by using SQL through lexical analysis, processing the word set by using a preset index setting rule to obtain an updated index list corresponding to the statement constructed by using SQL, and generating an index optimization suggestion of the statement constructed by using SQL according to a comparison result of the updated index list and an original index list. According to the invention, the index optimization of the statement constructed by using SQL can be completed through the index setting rule, manual operation is not required, the optimization efficiency is improved, and the optimized program performance is ensured.
As shown in fig. 2, the embodiment of the present invention discloses another processing method for a structured query language, which is also applied to an SQL analysis plug-in, and the SQL analysis plug-in performs an analysis method on an SQL statement in the project instance, including the following steps:
s201, obtaining a statement constructed by using a Structured Query Language (SQL).
S202, performing lexical analysis on the statements constructed by utilizing the SQL to obtain a word set of the statements constructed by utilizing the SQL.
In step S202, the word set of the statement constructed by using SQL includes: the statement constructed by using SQL comprises a plurality of keywords and non-keywords corresponding to each keyword.
S203, extracting the where condition fields in the word set of the statement constructed by using the SQL, and calculating the discrimination of the extracted where condition fields.
Specifically, the distinction degree of the extracted where condition field can be calculated by a distinction degree calculation formula distinction ═ count (distinct col)/count (, where the distinction degree indicates a ratio of non-repeated fields. The discrimination is the total number of records after deduplication/the total number of records, count (×) is the total number of statistical records, distict col is the deduplication of the row pair, and count (distict col) is the total number of records after statistical deduplication.
In the case of the where condition, connection is generally performed using and or connectors, and since the where condition for connection using an or connector is difficult to handle, only the where condition field for connection using an and connector is extracted, ignoring the condition field.
Furthermore, if the operator in a certain where condition field is like, the where condition field is discarded when like is not a prefix match.
Due to the leftmost matching characteristic of the index, the like cannot be indexed without prefix matching, and is discarded without the need of optimization.
Optionally, the where condition fields smaller than the preset distinction degree are discarded, and the where condition fields excluding the where condition fields smaller than the preset distinction degree are sorted according to the sequence from high to low of the distinction degree.
The preset discrimination can be set according to specific needs, generally, the preset discrimination is set to be 30, and since the discrimination indicates a non-repeated proportion of fields, when the discrimination is less than 30, an index or an optimized index is not necessary to be established.
Optionally, if the statement constructed by using SQL relates to only one list in the execution process, the list is used as a unique list, and if the statement constructed by using SQL relates to multiple lists in the execution process, after sorting the extracted where condition fields according to the order of the degree of distinction from high to low, the method further includes:
and judging whether a join field exists in the where condition field, if so, analyzing the join field to obtain a plurality of result sets. The result set refers to a plurality of lists in the execution process of the statements constructed by utilizing SQL.
And taking the result set with few rows as a driving table.
S204, under the condition that the discrimination of the where condition field in the word set of the statement constructed by utilizing the SQL is greater than the preset discrimination, judging whether the group by field in the word set of the statement constructed by utilizing the SQL is effective.
Specifically, if all the group by fields are from a unique list or the same driving table, it is determined that the group by fields in the word set of the statement constructed by using SQL are valid.
And S205, if the group by field in the word set of the statement constructed by the SQL is judged to be valid, writing the group by field in the word set of the statement constructed by the SQL into an index list.
S206, judging whether the orderby field in the word set of the statement constructed by utilizing the SQL is effective or not.
Specifically, if all the orderby fields come from a unique list or the same driving table, the orderby fields in the word set of the statement constructed by using the SQL are judged to be valid.
S207, if the orderby field in the word set of the statement constructed by the SQL is judged to be valid, writing the orderby field in the word set of the statement constructed by the SQL into the index list.
And taking the index lists written in the group by fields and the order by fields in the word set of the statement constructed by using the SQL as the updated index list.
Optionally, the repeated index filtering operation is performed on the index lists written in the group by fields and the orderby fields in the word set of the statement constructed by using SQL, so as to obtain the filtered index lists.
And taking the filtered index list as the updated index list.
And S208, determining an original index list corresponding to the statement constructed by using the SQL.
S209, generating an index optimization suggestion of the statement constructed by using the SQL according to the comparison result of the updated index list and the original index list.
It should be noted that steps S203 to S207 are a specific implementation manner for implementing step S103 in the first embodiment.
The technical solutions formed in steps S203 to S205 and steps S203 and S206 to S207 are two other specific implementations that can also implement step S103 in the first embodiment, and the execution process and principle thereof are the same as those of steps S03 to S207, and are not described herein again
The embodiment discloses another processing method of the structured query language, which comprises the steps of obtaining a word set of the statement constructed by utilizing the SQL through lexical analysis, extracting a where condition field in the word set, calculating the distinguishing degree, writing a group by field and an order by field into an index list serving as an updated index list when the distinguishing degree is larger than a preset threshold value, and generating an index optimization suggestion of the statement constructed by utilizing the SQL according to a comparison result of the updated index list and an original index list. According to the invention, the index optimization of the statement constructed by using SQL can be completed through the index setting rule, manual operation is not required, the optimization efficiency is improved, and the optimized program performance is ensured.
Based on the processing method of the structured query language disclosed in the above embodiment of the present invention, fig. 3 specifically discloses a processing apparatus of the structured query language applying the processing method of the structured query language.
As shown in fig. 3, another embodiment of the present invention discloses a processing apparatus for structured query language, which can be understood as SQL analysis plug-in, including:
an obtaining unit 301, configured to obtain a statement constructed by using a structured query language SQL;
an analyzing unit 302, configured to perform lexical analysis on the statements constructed by using SQL to obtain a word set of the statements constructed by using SQL;
a processing unit 303, configured to process the word set of the statement constructed by using SQL according to a predetermined index setting rule, to obtain an updated index list corresponding to the statement constructed by using SQL;
a determining unit 304, configured to determine an original index list corresponding to the statement constructed by using SQL;
a generating unit 305, configured to generate an index optimization suggestion of the statement constructed by using SQL according to a comparison result between the updated index list and the original index list.
Optionally, the processing unit 303 includes:
a first judging unit, configured to judge whether a group by field in the word set of the statement constructed using SQL is valid or not when the degree of distinction of the where condition field in the word set of the statement constructed using SQL is greater than a predetermined threshold;
and the first writing unit is used for writing the group by field in the word set of the statement constructed by using SQL into an index list if the first judging unit judges that the group by field in the word set of the statement constructed by using SQL is valid, so as to obtain an updated index list corresponding to the statement constructed by using SQL.
Optionally, the processing apparatus further includes:
a second judging unit, configured to, after writing the groupby field in the word set of the SQL-based statement into an index list, judge whether the orderby field in the word set of the SQL-based statement is valid;
the second writing unit is used for writing the orderby field in the word set of the statement constructed by using SQL into the index list if the second judging unit judges that the orderby field in the word set of the statement constructed by using SQL is effective;
and writing an index list of orderby fields in the word set of the statement constructed by using SQL as the updated index list.
Optionally, the processing apparatus further includes:
the filtering unit is used for executing repeated index filtering operation on the index list written into the order by field in the word set of the statement constructed by using SQL to obtain a filtered index list;
wherein the filtered index list is used as the updated index list.
Optionally, the processing unit 303 includes:
a third judging unit, configured to judge whether an orderby field in the word set of the statement constructed by using SQL is valid or not when the degree of distinction of the where condition field in the word set of the statement constructed by using SQL is greater than a predetermined threshold;
and the third writing unit is used for writing the orderby field in the word set of the statement constructed by using the SQL into an index list if the third judging unit judges that the orderby field in the word set of the statement constructed by using the SQL is effective, so as to obtain an updated index list corresponding to the statement constructed by using the SQL.
Optionally, the determining unit 304 includes:
and the control unit is used for analyzing the execution plan of the statement constructed by utilizing the SQL by utilizing an analyzer to obtain an original index list corresponding to the statement constructed by utilizing the SQL.
For specific execution processes and execution principles of the obtaining unit 301, the parsing unit 302, the processing unit 303, the determining unit 304, and the generating unit 305 in the processing apparatus of a structured query language disclosed in the above embodiment of the present invention, reference may be made to corresponding contents in the processing method of a structured query language disclosed in the above embodiment of the present invention, and details are not described here again.
The embodiment discloses a processing device of a structured query language, wherein an analysis unit obtains a word set of a statement constructed by SQL acquired by an acquisition unit through lexical analysis, a processing unit processes the word set by using a preset index setting rule to obtain an updated index list corresponding to the statement constructed by SQL, and a generation unit generates an index optimization suggestion of the statement constructed by SQL according to a comparison result of the updated index list and an original index list determined by a determination unit. According to the invention, the index optimization of the statement constructed by using SQL can be completed through the index setting rule, manual operation is not required, the optimization efficiency is improved, and the optimized program performance is ensured.
As shown in fig. 4, the present invention discloses another processing apparatus for structured query language, where the hardware device can implement the function of an SQL analysis plug-in, and the processing apparatus includes:
a memory 401 and a processor 402.
Wherein the memory 401 is used for storing computer instructions.
The processor 402 is configured to execute the computer instructions stored in the memory, and in particular, to execute the method for processing the structured query language disclosed in the above embodiments.
The invention also discloses a computer readable storage medium, wherein the computer readable storage medium is stored with instructions for executing the processing method of the structured query language disclosed by the embodiment.
It is noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.
The foregoing is directed to embodiments of the present application and it is noted that numerous modifications and adaptations may be made by those skilled in the art without departing from the principles of the present application and are intended to be within the scope of the present application.
Claims (9)
1. A processing method of a structured query language is applied to an SQL analysis plug-in, and comprises the following steps: in the process of carrying out unit test or integrated test, calling the SQL analysis plug-in to analyze the SQL sentences executed in the test process; the SQL analysis plug-in analyzes the SQL statements executed in the test process, and the SQL analysis plug-in comprises the following steps:
obtaining a statement constructed by using a Structured Query Language (SQL);
performing lexical analysis on the statements constructed by utilizing SQL to obtain a word set of the statements constructed by utilizing SQL;
extracting the where condition field in the word set of the statement constructed by using SQL, and calculating the discrimination of the extracted where condition field;
under the condition that the discrimination of the where condition fields in the word set of the statement constructed by utilizing SQL is greater than a preset threshold, writing group by fields in the word set of the statement constructed by utilizing SQL and/or order by fields in the word set of the statement constructed by utilizing SQL into an index list to obtain an updated index list corresponding to the statement constructed by utilizing SQL;
analyzing the execution plan of the statement constructed by utilizing the SQL by utilizing an analyzer to obtain an original index list corresponding to the statement constructed by utilizing the SQL;
and generating an index optimization suggestion of the statement constructed by using the SQL according to the comparison result of the updated index list and the original index list.
2. The method according to claim 1, wherein, in a case that the degree of distinction of the where condition fields in the word set of the SQL-based statement is greater than a predetermined threshold, writing the group by fields in the word set of the SQL-based statement into an index list to obtain an updated index list corresponding to the SQL-based statement, the method includes:
under the condition that the discrimination of the where condition fields in the word set of the statement constructed by utilizing SQL is greater than a preset threshold, judging whether the group by fields in the word set of the statement constructed by utilizing SQL are effective or not;
if the group by field in the word set of the statement constructed by utilizing SQL is judged to be valid, writing the group by field in the word set of the statement constructed by utilizing SQL into an index list, and obtaining an updated index list corresponding to the statement constructed by utilizing SQL.
3. The method according to claim 2, wherein after writing the group by field in the word set of the SQL-built statement into an index list, the method further comprises:
judging whether the order by field in the word set of the statement constructed by using SQL is valid or not;
if the order by field in the word set of the statement constructed by utilizing SQL is judged to be valid, writing the order by field in the word set of the statement constructed by utilizing SQL into the index list;
and writing the index list of the order by fields in the word set of the statement constructed by using SQL as the updated index list.
4. The method of claim 3, further comprising:
executing repeated index filtering operation on the index list written in the order by field in the word set of the statement constructed by using SQL to obtain a filtered index list;
wherein the filtered index list is used as the updated index list.
5. The method according to claim 1, wherein, in a case that the degree of distinction of the where condition fields in the word set of the SQL-built statement is greater than a predetermined threshold, writing order by fields in the word set of the SQL-built statement into an index list to obtain an updated index list corresponding to the SQL-built statement, includes:
under the condition that the discrimination of the where condition fields in the word set of the statement constructed by utilizing SQL is greater than a preset threshold, judging whether the order by fields in the word set of the statement constructed by utilizing SQL are effective or not;
and if the order by field in the word set of the statement constructed by utilizing the SQL is judged to be effective, writing the order by field in the word set of the statement constructed by utilizing the SQL into an index list to obtain an updated index list corresponding to the statement constructed by utilizing the SQL.
6. A structured query language processing apparatus, comprising an SQL analysis plug-in, comprising:
the system comprises an acquisition unit, a query unit and a query unit, wherein the acquisition unit is used for acquiring statements constructed by using a Structured Query Language (SQL);
the parsing unit is used for performing lexical parsing on the statements constructed by using SQL to obtain a word set of the statements constructed by using SQL;
the processing unit is used for extracting the where condition fields in the word set of the statement constructed by using SQL and calculating the discrimination of the extracted where condition fields; under the condition that the discrimination of the where condition fields in the word set of the statement constructed by utilizing SQL is greater than a preset threshold, writing group by fields in the word set of the statement constructed by utilizing SQL and/or order by fields in the word set of the statement constructed by utilizing SQL into an index list to obtain an updated index list corresponding to the statement constructed by utilizing SQL;
the control unit is used for analyzing the execution plan of the statement constructed by utilizing the SQL by utilizing an analyzer to obtain an original index list corresponding to the statement constructed by utilizing the SQL;
and the generating unit is used for generating the index optimization suggestion of the statement constructed by using the SQL according to the comparison result of the updated index list and the original index list.
7. The apparatus according to claim 6, wherein the processing unit, when writing a group by field in the word set of the SQL-based statement into an index list and obtaining an updated index list corresponding to the SQL-based statement, executes, if a degree of distinction of the where condition field in the word set of the SQL-based statement is greater than a predetermined threshold, the processing unit includes:
a first judging unit, configured to judge whether a group by field in the word set of the statement constructed using SQL is valid or not when the degree of distinction of the where condition field in the word set of the statement constructed using SQL is greater than a predetermined threshold;
and the first writing unit is used for writing the group by fields in the word set of the statement constructed by using the SQL into an index list if the first judging unit judges that the group by fields in the word set of the statement constructed by using the SQL are valid, so as to obtain an updated index list corresponding to the statement constructed by using the SQL.
8. A structured query language processing apparatus, comprising: a memory and a processor, wherein:
the memory is to store computer instructions;
the processor is configured to execute the computer instructions stored by the memory, in particular to perform the method according to any one of claims 1 to 5.
9. A computer-readable storage medium having stored thereon instructions for performing the method of any one of claims 1-5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810966443.4A CN110895529B (en) | 2018-08-23 | 2018-08-23 | Processing method of structured query language and related device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810966443.4A CN110895529B (en) | 2018-08-23 | 2018-08-23 | Processing method of structured query language and related device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110895529A CN110895529A (en) | 2020-03-20 |
| CN110895529B true CN110895529B (en) | 2021-03-30 |
Family
ID=69784754
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810966443.4A Active CN110895529B (en) | 2018-08-23 | 2018-08-23 | Processing method of structured query language and related device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110895529B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117290352A (en) * | 2022-06-16 | 2023-12-26 | 中兴通讯股份有限公司 | Index establishing method, electronic equipment and computer storage equipment |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105989137A (en) * | 2015-02-27 | 2016-10-05 | 中国移动通信集团河北有限公司 | Structured query language performance optimization method and system |
| CN106611044A (en) * | 2016-12-02 | 2017-05-03 | 星环信息科技（上海）有限公司 | SQL optimization method and device |
| CN108170775A (en) * | 2017-12-26 | 2018-06-15 | 上海新炬网络技术有限公司 | A kind of database SQL indexes dynamic optimization method |
| CN108255477A (en) * | 2018-01-15 | 2018-07-06 | 贵州易鲸捷信息技术有限公司 | A kind of method and system by SQL compiler simulative optimization database performances |
Family Cites Families (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8335767B2 (en) * | 2007-10-17 | 2012-12-18 | Oracle International Corporation | Maintaining and utilizing SQL execution plan histories |
| US9846724B2 (en) * | 2014-11-13 | 2017-12-19 | Sap Se | Integration of calculation models into SQL layer |
-
2018
- 2018-08-23 CN CN201810966443.4A patent/CN110895529B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105989137A (en) * | 2015-02-27 | 2016-10-05 | 中国移动通信集团河北有限公司 | Structured query language performance optimization method and system |
| CN106611044A (en) * | 2016-12-02 | 2017-05-03 | 星环信息科技（上海）有限公司 | SQL optimization method and device |
| CN108170775A (en) * | 2017-12-26 | 2018-06-15 | 上海新炬网络技术有限公司 | A kind of database SQL indexes dynamic optimization method |
| CN108255477A (en) * | 2018-01-15 | 2018-07-06 | 贵州易鲸捷信息技术有限公司 | A kind of method and system by SQL compiler simulative optimization database performances |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110895529A (en) | 2020-03-20 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US20200311064A1 (en) | Systems and methods for rapid data analysis | |
| CN110795455B (en) | Dependency analysis method, electronic device, computer apparatus, and readable storage medium | |
| CN108052394B (en) | Resource allocation method based on SQL statement running time and computer equipment | |
| CN107016019B (en) | Database index creation method and device | |
| CN112988782B (en) | Hive-supported interactive query method and device and storage medium | |
| CN107016018B (en) | Database index creation method and device | |
| CN110222071B (en) | Data query method, device, server and storage medium | |
| CN109947804B (en) | Data set query optimization method and device, server and storage medium | |
| CN109145003B (en) | Method and device for constructing knowledge graph | |
| CN110688544A (en) | Method, device and storage medium for querying database | |
| CN112100198B (en) | Database SQL statement optimization method, device, equipment and storage medium | |
| CN112162983A (en) | Database index suggestion processing method, device, medium and electronic equipment | |
| US11288266B2 (en) | Candidate projection enumeration based query response generation | |
| CN110704472A (en) | Data query statistical method and device | |
| CN110888876A (en) | Method and device for generating database script, storage medium and computer equipment | |
| CN110895529B (en) | Processing method of structured query language and related device | |
| CN110580170B (en) | Method and device for identifying software performance risk | |
| CN112434003A (en) | SQL optimization method and device, computer equipment and storage medium | |
| CN111427784A (en) | Data acquisition method, device, equipment and storage medium | |
| CN112765118B (en) | Log query method, device, equipment and storage medium | |
| CN110633430B (en) | Event discovery method, apparatus, device, and computer-readable storage medium | |
| CN112948419A (en) | Query statement processing method and device | |
| CN113901094B (en) | Data processing method, device, equipment and storage medium | |
| CN116225965B (en) | IO size-oriented database performance problem detection method | |
| CN109558303B (en) | Application behavior analysis method and device and electronic equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |