CN113779070A - SQL optimization method and system - Google Patents
SQL optimization method and system Download PDFInfo
- Publication number
- CN113779070A CN113779070A CN202111086695.6A CN202111086695A CN113779070A CN 113779070 A CN113779070 A CN 113779070A CN 202111086695 A CN202111086695 A CN 202111086695A CN 113779070 A CN113779070 A CN 113779070A
- Authority
- CN
- China
- Prior art keywords
- sql
- sql statement
- statement
- file
- database
- 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
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/2445—Data retrieval commands; View definitions
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2452—Query translation
- G06F16/24528—Standardisation; Simplification
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24542—Plan optimisation
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Operations Research (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a SQL optimization method and a system, which are used for obtaining SQL sentences in XML by acquiring and scanning XML files of an extensible markup language; calling a SQL database basic specification file stored in an update library in advance, and carrying out specification check on SQL statements based on specification information in the SQL database basic specification file to obtain the SQL statements meeting the specification; searching whether an optimized SQL statement matched with the SQL statement meeting the specification exists in the updating library; and if so, calling the optimized SQL statement and displaying. According to the technical scheme, the standard information in the SQL database basic standard file stored in the updating library in advance is used for carrying out standard check on the scanned SQL sentences, and SQL optimization is carried out according to the optimized SQL sentences matched with the SQL sentences meeting the standard in the searched updating library, so that the applicability of the SQL optimization technology is improved, and dynamic updating is realized.
Description
Technical Field
The present invention relates to the field of Language optimization technologies, and in particular, to a method and a system for optimizing Structured Query Language (SQL).
Background
With the development of big data, the data volume borne by a database is larger and larger, the logical relationship among data is more and more complex, and enterprise-level applications often contain thousands of SQL operations, and the execution efficiency of the SQL operations greatly affects the throughput of the whole system.
In the prior art, there are two SQL optimization techniques, one technique needs to extract at least two SQL for analysis, and all SQL is directly optimized according to the setting, without agreement of developers, and it is likely to delete some necessary information, and the optimization method cannot be updated. The other optimization method for the SQL statement needs to be connected with a database, risks exist for enterprise application, safety is low, the database in the optimization method needs an index module, the optimization method cannot be generally applied to all types of databases, and meanwhile the optimization method cannot be updated.
Therefore, the existing SQL optimization technology cannot be generally applied to the optimization method under all conditions, the applicability is poor, and dynamic updating cannot be performed.
Disclosure of Invention
In view of this, embodiments of the present invention provide an SQL optimization method and system to solve the problems in the prior art that the applicability of the SQL optimization technology is poor and dynamic update cannot be performed.
In order to achieve the above purpose, the embodiments of the present invention provide the following technical solutions:
the first aspect of the embodiment of the invention discloses a Structured Query Language (SQL) optimization method, which comprises the following steps:
acquiring and scanning an extensible markup language (XML) file to obtain an SQL statement in the XML;
calling an SQL database basic specification file stored in an update library in advance, and carrying out specification check on the SQL statement based on specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification;
searching whether an optimized SQL statement matched with the SQL statement conforming to the specification exists in the updating library;
and if so, calling the optimized SQL statement and displaying.
Optionally, in the process of performing specification check on the SQL statement based on the specification information in the basic specification file of the SQL, the method further includes:
obtaining SQL sentences which do not accord with the standard;
and modifying and marking the SQL sentences which do not conform to the specification.
Optionally, the generating process of the SQL database basic specification file includes:
establishing an initial file in the updating library;
acquiring basic database specification information of the SQL statement, wherein the basic database specification information at least comprises whether a character set meets a standard, whether a field name is repeated with a keyword, and whether an end character is lacked;
and writing the basic database specification information of the SQL statement into the initial file to generate a basic database specification file of the SQL.
Optionally, the method further includes:
when a request for updating the specification information is acquired, confirming a SQL database basic specification file to be updated in the SQL database basic specification file pre-stored in an update library based on the request for updating the specification information, wherein the request for updating the specification information comprises supplement or deletion of specification information;
and updating the basic database specification file of the SQL to be updated based on the supplement or deletion specification information to obtain the updated basic database specification file of the SQL.
Optionally, the process of storing the optimized SQL statement in the update library in advance includes:
optimizing the sample SQL statement based on a preset optimization strategy to obtain a corresponding optimized SQL statement;
and establishing a corresponding relation between the sample SQL statement and the optimized SQL statement, and storing the corresponding relation in the updating library.
Optionally, the method further includes:
when an SQL updating statement request is obtained, confirming the corresponding relation between a sample SQL statement to be updated and an optimized SQL statement in the corresponding relation between the sample SQL statement and the optimized SQL statement stored in an updating library in advance based on the SQL updating statement request, wherein the SQL updating statement request comprises adding or deleting the SQL statement or optimizing the SQL statement;
and updating the corresponding relation between the sample SQL statement to be updated and the optimized SQL statement based on the supplement or deletion of the SQL statement or the optimized SQL statement to obtain the corresponding relation between the updated sample SQL statement and the optimized SQL statement.
Optionally, the invoking the SQL database basic specification file pre-stored in the update library, and performing specification check on the SQL statement based on the specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification includes:
acquiring key characters of the SQL statement, wherein the key characters at least comprise a character set, a field name, key words and end characters;
calling a SQL database basic specification file stored in an update library in advance, and matching the key characters with specification information in the SQL database basic specification file;
and if the key characters are matched with the standard information in the basic standard file of the SQL database, determining that the SQL statement conforms to the standard, and obtaining the SQL statement conforming to the standard.
Optionally, the method further includes:
and displaying the optimized SQL statement in a comment form.
The second aspect of the embodiment of the invention discloses a Structured Query Language (SQL) optimization system, which comprises:
the scanning module is used for acquiring and scanning an extensible markup language (XML) file to obtain an SQL statement in the XML;
the checking module is used for calling the SQL database basic specification file stored in the updating library in advance and carrying out standard checking on the SQL statement based on the specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification;
the searching module is used for searching whether the optimized SQL statement matched with the SQL statement conforming to the specification exists in the updating library; and if so, calling the optimized SQL statement and displaying.
Optionally, the checking module is further specifically configured to:
in the process of carrying out standard inspection on the SQL statement based on the standard information in the SQL database basic standard file, obtaining the SQL statement which does not conform to the standard; and modifying and marking the SQL sentences which do not conform to the specification.
Based on the SQL optimization method and the SQL optimization system provided by the embodiment of the invention, the method comprises the following steps: acquiring and scanning an extensible markup language (XML) file to obtain an SQL statement in the XML; calling an SQL database basic specification file stored in an update library in advance, and carrying out specification check on the SQL statement based on specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification; searching whether an optimized SQL statement matched with the SQL statement conforming to the specification exists in the updating library; and if so, calling the optimized SQL statement and displaying. According to the technical scheme, the standard information in the SQL database basic standard file stored in the updating library in advance is used for carrying out standard check on the scanned SQL sentences, and SQL optimization is carried out according to the optimized SQL sentences matched with the SQL sentences meeting the standard in the searched updating library, so that the applicability of the SQL optimization technology is improved, and dynamic updating is realized.
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 schematic flow chart of an SQL optimization method according to an embodiment of the present invention;
fig. 2 is a schematic flowchart of a basic specification file of a database for generating SQL according to an embodiment of the present invention;
fig. 3 is a schematic flowchart of another basic specification file of a database for generating SQL according to an embodiment of the present invention;
FIG. 4 is a schematic flow chart illustrating storing an optimized SQL statement in an update repository according to an embodiment of the present invention;
FIG. 5 is a schematic flow chart illustrating storing optimized SQL statements in an update library according to an embodiment of the present invention;
fig. 6 is a schematic flowchart of performing a specification check on an SQL statement according to an embodiment of the present invention;
fig. 7 is a schematic structural diagram of an SQL optimizing system according to an embodiment of the present invention;
fig. 8 is a schematic structural diagram of another SQL optimizing system according to an embodiment of the present invention;
fig. 9 is a schematic structural diagram of another SQL optimizing system according to an embodiment of the present invention.
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.
In this application, 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 terms "first," "second," "third," "fourth," and the like in the description and in the claims of the present application and in the drawings described above, if any, are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It will be appreciated that the data so used may be interchanged under appropriate circumstances such that the embodiments described herein may be practiced otherwise than as specifically illustrated or described herein.
As known from the background art, the existing SQL optimization technology cannot be generally applied to the optimization method under all situations, is poor in applicability, and cannot be dynamically updated.
Therefore, in the present invention, the specification information in the basic specification file of the SQL database stored in the update library in advance is used to perform specification check on the scanned SQL statement, and the SQL is optimized according to the found optimized SQL statement in the update library that matches the SQL statement that meets the specification, so that the applicability of the SQL optimization technology is improved, and dynamic update is implemented.
Fig. 1 is a schematic flow chart of an SQL optimization method according to an embodiment of the present invention.
Optionally, the SQL optimization method may be a GBASE database-based SQL optimization method.
The method mainly comprises the following steps:
step S101: and acquiring and scanning an extensible markup language (XML) file to obtain an SQL statement in the XML.
In step S101, XML (Extensible Markup Language) is a Markup Language for Markup of an electronic document to be structured.
In the process of implementing step S101 specifically, an XML file of the XML is obtained, and the XML file of the XML is scanned to obtain an SQL statement in the XML.
Step S102: and calling the SQL database basic specification file stored in the update library in advance, and carrying out specification check on the SQL statement based on the specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification.
In step S102, the SQL database base specification file contains at least specification information.
In the process of implementing step S102 specifically, the created SQL database basic specification file is stored in the update library in advance, and when performing specification check on the scanned SQL statement, the SQL database basic specification file stored in the update library in advance is called, and the scanned SQL statement is subjected to specification check according to the specification information included in the SQL database basic specification file, so as to obtain the SQL statement meeting the specification.
Optionally, in the process of performing specification check on the SQL statement by the specification information in the SQL-based database basic specification file, the method further includes:
and obtaining SQL sentences which do not accord with the specification, modifying the SQL sentences which do not accord with the specification, and marking.
Step S103: and searching whether the optimized SQL statement matched with the SQL statement meeting the specification exists in the updating library, if so, executing the step S104, and if not, finishing the searching.
In the process of implementing step S103 specifically, it is searched whether there is an optimized SQL statement matching the SQL statement conforming to the specification in the update library, if yes, it is indicated that there is an optimized SQL statement matching the SQL statement conforming to the specification in the update library, step S104 is executed, if no, it is indicated that there is no optimized SQL statement matching the SQL statement conforming to the specification in the update library, and the SQL statement conforming to the specification does not need to be optimized, and the search is ended.
Step S104: and calling the optimized SQL statement and displaying.
In the specific implementation process of step S104, it is determined that there is an optimized SQL statement matching the SQL statement that meets the specification in the update library, the optimized SQL statement matching the SQL statement that meets the specification is called, and the optimized SQL statement matching the SQL statement that meets the specification is displayed.
Optionally, the procedure of invoking the optimized SQL statement and displaying in step S104 is executed, and includes:
the optimized SQL statement is presented in annotated form.
For example, the step S103 and the step S104 are illustrated, for example, the SQL statements conforming to the specification are as follows:
SELECT col_1FORM tb_1WHERE col_1！＝5；
it is known to update optimized SQL statements stored in the library to avoid using NOT or! Then, based on the known conditions, finding out that there exists an optimized SQL statement in the update library that matches the SQL statement that meets the specification, as follows:
optimizing SQL statement 1:
SELECT col_1FORM tb_1WHERE col_1>5or col_1<5；
optimizing SQL statement 2:
SELECT col_1FORM tb_1WHERE col_1<>5；
and calling the searched optimized SQL sentence, and displaying the searched optimized SQL sentence.
Based on the SQL optimization method provided by the embodiment of the invention, SQL sentences in XML are obtained by acquiring and scanning XML files of XML; calling a SQL database basic specification file stored in an update library in advance, and carrying out specification check on the SQL statement based on specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification; searching whether an optimized SQL statement matched with the SQL statement meeting the specification exists in the updating library; and if so, calling the optimized SQL statement and displaying. According to the technical scheme, the standard information in the SQL database basic standard file stored in the updating library in advance is used for carrying out standard check on the scanned SQL sentences, and SQL optimization is carried out according to the optimized SQL sentences matched with the SQL sentences meeting the standard in the searched updating library, so that the applicability of the SQL optimization technology is improved, and dynamic updating is realized.
Based on the SQL optimizing method provided in the embodiment of the present invention, a process of invoking the SQL database basic specification file pre-stored in the update library in step S102 is executed, as shown in fig. 2, which is a schematic flow diagram of generating the SQL database basic specification file provided in the embodiment of the present invention, and mainly includes the following steps:
step S201: an initial file is created in an update library.
In step S201, the initial file is a blank initial file.
In the process of implementing step S201 specifically, when generating a basic specification file of the SQL database, the file needs to be generated on the basis of creating a blank file, and a blank initial file is first created in the update library.
Step S202: and acquiring the basic database specification information of the SQL statement.
In step S202, the database basic specification information includes at least whether the character set meets the criterion, whether the field name is duplicated with the keyword, and whether the end character is absent.
In the process of specifically implementing step S202, after the blank initial file is created, the database basic specification information of the SQL statement necessary for generating the SQL database basic specification file needs to be acquired, and then, the database basic specification information such as whether the character set meets the standard, whether the field name is repeated with the keyword, and whether the ending character is lacked is specifically acquired.
Step S203: and writing the basic database specification information of the SQL statement into the initial file to generate a basic database specification file of the SQL.
In the process of specifically implementing step S203, writing the acquired database basic specification information of the SQL statement into the newly created blank initial file, and generating a database basic specification file of the SQL.
Based on the SQL optimization method provided by the embodiment of the invention, the SQL database basic specification file is generated through the SQL statement-based database basic specification information, and a data source is provided for subsequently calling the SQL database basic specification file, so that the applicability of the SQL optimization technology is improved, and dynamic update is realized.
Based on the SQL optimizing method provided in the embodiment of the present invention, a process of invoking the SQL database basic specification file pre-stored in the update library in step S102 is executed, as shown in fig. 3, which is another schematic flow diagram for generating the SQL database basic specification file provided in the embodiment of the present invention, and mainly includes the following steps:
step S301: an initial file is created in an update library.
Step S302: and acquiring the basic database specification information of the SQL statement.
Step S303: and writing the basic database specification information of the SQL statement into the initial file to generate a basic database specification file of the SQL.
It should be noted that the execution principle and procedure of the above steps S301 to S303 are the same as the execution principle and procedure of the steps S201 to S203 disclosed in fig. 2, and reference may be made to these steps, which are not described herein again.
Step S304: it is determined whether a request for updating the specification information is acquired, if so, step S305 is performed, and if not, step S304 is continuously performed.
In step S304, the request for updating the specification information includes supplementing or deleting the specification information.
Step S305: and confirming the SQL database basic specification files to be updated in the SQL database basic specification files pre-stored in the update library based on the update specification information request.
In the specific implementation process of step S305, when it is determined that the request for updating specification information is obtained, the database base specification file of SQL to be updated, which is pre-stored in the database base specification files of SQL of the update library, is confirmed based on the obtained request for updating specification information.
Step S306: and updating the basic specification file of the SQL database to be updated based on the supplement or deletion of the specification information to obtain the updated basic specification file of the SQL database.
In the process of implementing step S306 specifically, the confirmed basic specification file of the SQL database to be updated is updated based on the supplement or deletion of the specification information in the request for updating specification information, so as to obtain the updated basic specification file of the SQL database.
Based on the SQL optimization method provided by the embodiment of the invention, when the request for updating the specification information is obtained, the SQL database basic specification file is updated according to the request for updating the specification information to obtain the updated SQL database basic specification file, and a data source is provided for subsequently calling the SQL database basic specification file, so that the applicability of the SQL optimization technology is improved, and dynamic updating is realized.
Based on the SQL optimizing method provided in the embodiment of the present invention, step S103 is executed to find whether there is an optimized SQL statement matching the SQL statement that meets the specification in the update library, as shown in fig. 4, which is a schematic flow diagram for storing the optimized SQL statement in the update library provided in the embodiment of the present invention, and the method mainly includes the following steps:
step S401: and optimizing the sample SQL statement based on a preset optimization strategy to obtain a corresponding optimized SQL statement.
It should be noted that each optimization strategy corresponds to a corresponding relationship, and a plurality of optimization strategies correspond to a plurality of corresponding relationships.
In the specific implementation process of step S401, an optimization policy is preset, and the sample SQL statement is optimized according to the preset optimization policy, so as to obtain an optimized SQL statement corresponding to the sample SQL statement.
Step S402: and establishing a corresponding relation between the sample SQL statement and the optimized SQL statement, and storing the corresponding relation in an update library.
In the process of implementing the step S402, according to the corresponding relationship of the optimization policy, a corresponding relationship between the sample SQL statement and the optimization SQL statement is established, and the corresponding relationship between the sample SQL statement and the optimization SQL statement is stored in the update library.
Based on the SQL optimization method provided by the embodiment of the invention, the corresponding relation between the sample SQL statement and the optimized SQL statement is established, and the corresponding relation is stored in the update library, so that the matched optimized SQL statement can be conveniently searched based on the corresponding relation between the sample SQL statement and the optimized SQL statement, the applicability of the SQL optimization technology is improved, and dynamic update is realized.
Based on the SQL optimizing method provided in the embodiment of the present invention, step S103 is executed to find whether there is an optimized SQL statement matching the SQL statement that meets the specification in the update library, as shown in fig. 5, another flow diagram for storing the optimized SQL statement in the update library provided in the embodiment of the present invention mainly includes the following steps:
step S501: and optimizing the sample SQL statement based on a preset optimization strategy to obtain a corresponding optimized SQL statement.
Step S502: and establishing a corresponding relation between the sample SQL statement and the optimized SQL statement, and storing the corresponding relation in an update library.
It should be noted that the execution principle and procedure of the above steps S501 to S502 are the same as the execution principle and procedure of the steps S401 to S402 disclosed in fig. 4, and reference may be made to these steps, which are not described herein again.
Step S503: and judging whether the SQL statement updating request is acquired, if so, executing step S504, and if not, continuing to execute step S503.
In step S503, the request for updating the SQL statement includes supplementing or deleting the SQL statement or optimizing the SQL statement.
Step S504: and confirming the corresponding relation between the sample SQL statement to be updated and the optimized SQL statement in the corresponding relation between the sample SQL statement and the optimized SQL statement which are stored in the updating library in advance based on the SQL updating statement request.
In the process of implementing step S504 specifically, when it is confirmed that the request for updating the SQL statement is obtained, the corresponding relationship between the sample SQL statement to be updated and the optimized SQL statement in the corresponding relationship between the sample SQL statement and the optimized SQL statement stored in the update library in advance is confirmed based on the obtained request for updating the SQL statement.
Step S505: and updating the corresponding relation between the sample SQL statement to be updated and the optimized SQL statement based on adding or deleting the SQL statement or the optimized SQL statement to obtain the corresponding relation between the updated sample SQL statement and the optimized SQL statement.
In the process of implementing step S505 specifically, based on adding or deleting the SQL statement or optimizing the SQL statement in the request for updating the SQL statement, the corresponding relationship between the confirmed sample SQL statement to be updated and the optimized SQL statement is updated, so as to obtain the corresponding relationship between the updated sample SQL statement and the optimized SQL statement.
Based on the SQL optimization method provided by the embodiment of the invention, when the request for updating the SQL statement is obtained, the corresponding relation between the sample SQL statement and the optimization SQL statement is updated according to the request for updating the SQL statement, so that the corresponding relation between the updated sample SQL statement and the optimization SQL statement is obtained, and the matched optimization SQL statement can be conveniently searched based on the corresponding relation between the sample SQL statement and the optimization SQL statement, thereby improving the applicability of the SQL optimization technology and realizing dynamic updating.
Based on the SQL optimization method provided in the embodiment of the present invention, step S102 is executed to invoke the SQL database basic specification file pre-stored in the update library, and perform specification check on the SQL statement based on the specification information in the SQL database basic specification file to obtain a procedure of the SQL statement conforming to the specification, as shown in fig. 6, which is a flow diagram for performing specification check on the SQL statement provided in the embodiment of the present invention, and mainly includes the following steps:
step S601: and acquiring key characters of the SQL statement.
In step S601, the key characters include at least a character set, a field name, a key word, and an end character.
In the process of implementing step S601, the key characters of the SQL statement are obtained, and the character set, the field name, the key words, and the end characters of the SQL statement are specifically obtained.
Step S602: and calling the SQL database basic specification file stored in the update library in advance, and matching the key characters with the specification information in the SQL database basic specification file.
In the process of implementing step S602 specifically, the created SQL database basic specification file is stored in the update library in advance, and when the scanned SQL statement is subjected to specification check, the SQL database basic specification file stored in the update library in advance is called, and the obtained key characters of the SQL statement are matched with the specification information in the SQL database basic specification file.
Step S603: and judging whether the key characters are matched with the specification information in the SQL database basic specification file, if so, executing the step S604, and if not, executing the step S605.
Step S604: and determining that the SQL statement conforms to the specification to obtain the SQL statement conforming to the specification.
In the specific implementation process of step S604, it is determined that the key characters match the specification information in the basic specification file of the SQL database, and then it is determined that the SQL statement conforms to the specification, so as to obtain the SQL statement conforming to the specification.
Step S605: and determining that the SQL statement does not meet the specification to obtain the SQL statement which does not meet the specification.
In the specific implementation process of step S605, it is determined that the key characters do not match the specification information in the basic specification file of the SQL database, and it is further determined that the SQL statement does not meet the specification, so as to obtain the SQL statement that does not meet the specification.
Based on the SQL optimization method provided by the embodiment of the invention, the key characters of the SQL sentences are matched with the standard information in the SQL database basic standard file stored in the update library in advance, so that the scanned SQL sentences are subjected to standard inspection, the applicability of the SQL optimization technology is improved, and dynamic update is realized.
Corresponding to the SQL optimizing method shown in the foregoing embodiment of the present invention, the embodiment of the present invention further provides an SQL optimizing system, as shown in fig. 7, where the SQL optimizing system includes: a scanning module 71, an inspection module 72 and a lookup module 73.
The scanning module 71 is configured to acquire and scan an XML file of the XML, so as to obtain an SQL statement in the XML.
The checking module 72 is configured to call a basic specification file of the SQL database stored in the update library in advance, and perform specification checking on the SQL statement based on the specification information in the basic specification file of the SQL database to obtain the SQL statement meeting the specification.
Optionally, the checking module 72 is further specifically configured to:
in the process of carrying out standard inspection on SQL statements by the standard information in the SQL-based basic standard file, obtaining SQL statements which do not conform to the standard; and modifying and marking SQL statements which do not conform to the specification.
The searching module 73 is used for searching whether an optimized SQL statement matched with the SQL statement meeting the specification exists in the update library; and if so, calling the optimized SQL statement and displaying.
Optionally, the search module 73 presents the optimized SQL statement in the form of a comment.
It should be noted that, the specific principle and the execution process of each module or each unit in the SQL optimization system disclosed in the embodiment of the present invention are the same as the SQL optimization method implemented in the present invention, and reference may be made to corresponding parts in the SQL optimization method disclosed in the embodiment of the present invention, which are not described herein again.
Based on the SQL optimization system provided by the embodiment of the invention, SQL sentences in XML are obtained by acquiring and scanning XML files of XML; calling a SQL database basic specification file stored in an update library in advance, and carrying out specification check on the SQL statement based on specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification; searching whether an optimized SQL statement matched with the SQL statement meeting the specification exists in the updating library; and if so, calling the optimized SQL statement and displaying. According to the technical scheme, the standard information in the SQL database basic standard file stored in the updating library in advance is used for carrying out standard check on the scanned SQL sentences, and SQL optimization is carried out according to the optimized SQL sentences matched with the SQL sentences meeting the standard in the searched updating library, so that the applicability of the SQL optimization technology is improved, and dynamic updating is realized.
Optionally, based on the SQL optimization system shown in fig. 7, in combination with fig. 7, as shown in fig. 8, the SQL optimization system is further provided with a generation module 74.
A generating module 74, configured to create an initial file in the update repository; acquiring basic database specification information of the SQL statement, wherein the basic database specification information at least comprises whether a character set meets the standard, whether a field name is repeated with a keyword, and whether an end character is lacked; and writing the basic database specification information of the SQL statement into the initial file to generate a basic database specification file of the SQL.
Based on the SQL optimization system provided by the embodiment of the invention, the SQL database basic specification file is generated through the SQL statement-based database basic specification information, and a data source is provided for subsequently calling the SQL database basic specification file, so that the applicability of the SQL optimization technology is improved, and dynamic update is realized.
Optionally, based on the generating module 74 shown in fig. 8, the generating module 74 is further specifically configured to:
when a request for updating the specification information is acquired, confirming the SQL database basic specification file to be updated in the SQL database basic specification file pre-stored in the update library based on the request for updating the specification information, wherein the request for updating the specification information comprises supplement or deletion of the specification information; and updating the basic specification file of the SQL database to be updated based on the supplement or deletion of the specification information to obtain the updated basic specification file of the SQL database.
Based on the SQL optimization system provided by the embodiment of the invention, when the request for updating the specification information is obtained, the SQL database basic specification file is updated according to the request for updating the specification information to obtain the updated SQL database basic specification file, and a data source is provided for subsequently calling the SQL database basic specification file, so that the applicability of the SQL optimization technology is improved, and dynamic updating is realized.
Optionally, based on the SQL optimization system shown in fig. 7, in combination with fig. 7 and 8, as shown in fig. 9, the SQL optimization system is further provided with a storage module 75.
The storage module is used for optimizing the sample SQL statement based on a preset optimization strategy to obtain a corresponding optimized SQL statement; and establishing a corresponding relation between the sample SQL statement and the optimized SQL statement, and storing the corresponding relation in the updating library.
Based on the SQL optimization system provided by the embodiment of the invention, the corresponding relation between the sample SQL statement and the optimized SQL statement is established, and the corresponding relation is stored in the update library, so that the matched optimized SQL statement can be conveniently searched based on the corresponding relation between the sample SQL statement and the optimized SQL statement, the applicability of the SQL optimization technology is improved, and dynamic update is realized.
Optionally, based on the storage module 75 shown in fig. 9, the storage module 75 is further specifically configured to:
when an SQL updating statement request is obtained, confirming the corresponding relation between a sample SQL statement to be updated and an optimized SQL statement in the corresponding relation between the sample SQL statement and the optimized SQL statement stored in an updating library in advance based on the SQL updating statement request, wherein the SQL updating statement request comprises adding or deleting the SQL statement or optimizing the SQL statement; and updating the corresponding relation between the sample SQL statement to be updated and the optimized SQL statement based on adding or deleting the SQL statement or the optimized SQL statement to obtain the corresponding relation between the updated sample SQL statement and the optimized SQL statement.
Based on the SQL optimization system provided by the embodiment of the invention, when the request for updating the SQL statement is obtained, the corresponding relation between the sample SQL statement and the optimization SQL statement is updated according to the request for updating the SQL statement, so that the corresponding relation between the updated sample SQL statement and the optimization SQL statement is obtained, and the matched optimization SQL statement can be conveniently searched based on the corresponding relation between the sample SQL statement and the optimization SQL statement, thereby improving the applicability of the SQL optimization technology and realizing dynamic updating.
Optionally, based on the inspection module 72 shown in fig. 7, the inspection module 72 is specifically configured to:
acquiring key characters of the SQL statement, wherein the key characters at least comprise a character set, a field name, key words and end characters; calling a SQL database basic specification file stored in an update library in advance, and matching the key characters with specification information in the SQL database basic specification file; and if the key characters are matched with the standard information in the basic standard file of the SQL database, determining that the SQL statement conforms to the standard, and obtaining the SQL statement conforming to the standard.
Based on the SQL optimization system provided by the embodiment of the invention, the key characters of the SQL sentences are matched with the standard information in the SQL database basic standard file stored in the update library in advance, so that the scanned SQL sentences are subjected to standard inspection, the applicability of the SQL optimization technology is improved, and dynamic update is realized.
The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, the system or system embodiments are substantially similar to the method embodiments and therefore are described in a relatively simple manner, and reference may be made to some of the descriptions of the method embodiments for related points. The above-described system and system embodiments are only illustrative, wherein the units described as separate parts may or may not be physically separate, and the parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of the present embodiment. One of ordinary skill in the art can understand and implement it without inventive effort.
Those of skill would further appreciate that the various illustrative elements and algorithm steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware, computer software, or combinations of both, and that the various illustrative components and steps have been described above generally in terms of their functionality in order to clearly illustrate this interchangeability of hardware and software. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the implementation. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present invention.
The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the invention. Thus, the present invention is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
Claims (10)
1. A method for optimizing Structured Query Language (SQL), comprising:
acquiring and scanning an extensible markup language (XML) file to obtain an SQL statement in the XML;
calling an SQL database basic specification file stored in an update library in advance, and carrying out specification check on the SQL statement based on specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification;
searching whether an optimized SQL statement matched with the SQL statement conforming to the specification exists in the updating library;
and if so, calling the optimized SQL statement and displaying.
2. The method according to claim 1, wherein in the process of performing specification check on the SQL statement based on the specification information in the basic specification file of the SQL, the method further comprises:
obtaining SQL sentences which do not accord with the standard;
and modifying and marking the SQL sentences which do not conform to the specification.
3. The method according to claim 1, wherein the generating process of the SQL database base specification file comprises:
establishing an initial file in the updating library;
acquiring basic database specification information of the SQL statement, wherein the basic database specification information at least comprises whether a character set meets a standard, whether a field name is repeated with a keyword, and whether an end character is lacked;
and writing the basic database specification information of the SQL statement into the initial file to generate a basic database specification file of the SQL.
4. The method of claim 3, further comprising:
when a request for updating the specification information is acquired, confirming a SQL database basic specification file to be updated in the SQL database basic specification file pre-stored in an update library based on the request for updating the specification information, wherein the request for updating the specification information comprises supplement or deletion of specification information;
and updating the basic database specification file of the SQL to be updated based on the supplement or deletion specification information to obtain the updated basic database specification file of the SQL.
5. The method according to claim 1, wherein the process of storing the optimized SQL statement in an update library in advance comprises:
optimizing the sample SQL statement based on a preset optimization strategy to obtain a corresponding optimized SQL statement;
and establishing a corresponding relation between the sample SQL statement and the optimized SQL statement, and storing the corresponding relation in the updating library.
6. The method of claim 5, further comprising:
when an SQL updating statement request is obtained, confirming the corresponding relation between a sample SQL statement to be updated and an optimized SQL statement in the corresponding relation between the sample SQL statement and the optimized SQL statement stored in an updating library in advance based on the SQL updating statement request, wherein the SQL updating statement request comprises adding or deleting the SQL statement or optimizing the SQL statement;
and updating the corresponding relation between the sample SQL statement to be updated and the optimized SQL statement based on the supplement or deletion of the SQL statement or the optimized SQL statement to obtain the corresponding relation between the updated sample SQL statement and the optimized SQL statement.
7. The method according to claim 1 or 3, wherein the calling the SQL database basic specification file pre-stored in the update library, and performing specification check on the SQL statement based on the specification information in the SQL database basic specification file to obtain the SQL statement conforming to the specification comprises:
acquiring key characters of the SQL statement, wherein the key characters at least comprise a character set, a field name, key words and end characters;
calling a SQL database basic specification file stored in an update library in advance, and matching the key characters with specification information in the SQL database basic specification file;
and if the key characters are matched with the standard information in the basic standard file of the SQL database, determining that the SQL statement conforms to the standard, and obtaining the SQL statement conforming to the standard.
8. The method of claim 1, further comprising:
and displaying the optimized SQL statement in a comment form.
9. A structured query language, SQL, optimization system, the system comprising:
the scanning module is used for acquiring and scanning an extensible markup language (XML) file to obtain an SQL statement in the XML;
the checking module is used for calling the SQL database basic specification file stored in the updating library in advance and carrying out standard checking on the SQL statement based on the specification information in the SQL database basic specification file to obtain the SQL statement meeting the specification;
the searching module is used for searching whether the optimized SQL statement matched with the SQL statement conforming to the specification exists in the updating library; and if so, calling the optimized SQL statement and displaying.
10. The system of claim 9, wherein the inspection module is further specifically configured to:
in the process of carrying out standard inspection on the SQL statement based on the standard information in the SQL database basic standard file, obtaining the SQL statement which does not conform to the standard; and modifying and marking the SQL sentences which do not conform to the specification.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111086695.6A CN113779070A (en) | 2021-09-16 | 2021-09-16 | SQL optimization method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111086695.6A CN113779070A (en) | 2021-09-16 | 2021-09-16 | SQL optimization method and system |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113779070A true CN113779070A (en) | 2021-12-10 |
Family
ID=78851530
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111086695.6A Pending CN113779070A (en) | 2021-09-16 | 2021-09-16 | SQL optimization method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113779070A (en) |
-
2021
- 2021-09-16 CN CN202111086695.6A patent/CN113779070A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9858270B2 (en) | Converting data into natural language form | |
| US9390179B2 (en) | Federated search | |
| US10599695B2 (en) | Key word searching where a key word is a homonym | |
| US5706509A (en) | Application independent record level synchronization | |
| Lin et al. | Poirot: A distributed tool supporting enterprise-wide automated traceability | |
| US8255432B2 (en) | Mechanism for supporting indexed tagged content in a general purpose data store | |
| US8849653B2 (en) | Updating dictionary during application installation | |
| Luković et al. | An approach to developing complex database schemas using form types | |
| JP2000148461A (en) | Software model and existing source code synchronizing method and device | |
| US20150199346A1 (en) | Hierarchical database report generation with automated query generation for placeholders | |
| LU503512B1 (en) | Operating method for construction of knowledge graph based on naming rule and caching mechanism | |
| US9053207B2 (en) | Adaptive query expression builder for an on-demand data service | |
| CN113032548A (en) | Information processing apparatus, storage medium, and information processing method | |
| CN113779070A (en) | SQL optimization method and system | |
| CN111460000A (en) | Backtracking data query method and system based on relational database | |
| Konstantinou et al. | An approach for the incremental export of relational databases into RDF graphs | |
| CN112765180B (en) | Method and device for analyzing column names of table building logs of DB2 database | |
| CN114819631A (en) | Multitask visualization method and device, computer equipment and storage medium | |
| CN112817931B (en) | Incremental version file generation method and device | |
| US11269836B2 (en) | System and method for generating multi-category searchable ternary tree data structure | |
| US8037113B2 (en) | Techniques for file system searching | |
| CN112148710B (en) | Micro-service library separation method, system and medium | |
| US10997056B1 (en) | Generation of explanatory and executable repair examples | |
| CN113010230B (en) | Configuration information processing method, device, equipment and storage medium | |
| US11250010B2 (en) | Data access generation providing enhanced search models |
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 |