SQL statement optimization method and system, electronic equipment and storage equipment

Abstract

The invention relates to the technical field of data processing, and discloses an SQL statement optimization method and system.The method comprises the steps that when a system is configured with a data source, SQL statements needing to be optimized are imported; performing grammar verification on the imported SQL statement; and when the verification is passed, based on the table structure information of the data source, adopting a first optimizer to optimize the SQL statement passing the verification. By means of the mode, the purpose of optimization in a complex environment is achieved, and the practical effect is improved.

Description

Technical Field

[0001] The embodiments of the present application relate to the field of data processing technology, and specifically to a method and system for optimizing SQL statements, an electronic device, and a storage device. Background Art

[0002] Structured Query Language (SQL) is a commonly used relational database operation language. When the execution efficiency of SQL query statements is low, it will lead to waste of database system resources, slow response speed, and even affect the normal operation of the business. By optimizing SQL statements, you can improve query efficiency, reduce system resource consumption, and improve system performance.

[0003] The existing optimization methods only provide basic optimization and are only suitable for simple scenarios. Therefore, there is an urgent need for an optimization method that can meet the needs of complex scenarios. Summary of the invention

[0004] In view of the above problems, the embodiments of the present application provide a method and system for optimizing SQL statements, an electronic device, and a storage device, which are used to solve the problem that the optimization methods in the prior art cannot meet the needs of complex scenarios.

[0005] According to one aspect of an embodiment of the present application, a method for optimizing a SQL statement is provided, the method comprising:

[0006] When the system is configured with a data source, import the SQL statements that need to be optimized;

[0007] Perform syntax check on the imported SQL statements;

[0008] When the verification passes, the first optimizer is used to optimize the SQL statement that passes the verification based on the table structure information of the data source.

[0009] In an optional manner, when the system is configured with a data source, before importing the SQL statement that needs to be optimized, the method further includes:

[0010] Determining whether the system is configured with a data source;

[0011] When the judgment is yes, go to the step of importing the SQL statement that needs to be optimized;

[0012] When the judgment is no, import the table structure information, import the SQL statement that needs to be optimized, and go to the step of verifying the imported SQL statement; and / or

[0013] The step of optimizing the SQL statement that has passed the verification by using the first optimizer includes:

[0014] Preliminarily optimizing the SQL statement based on the table structure information of the data source;

[0015] Use the NORMAL optimization chain to execute SQL statements that have been preliminarily optimized.

[0016] In an optional manner, after optimizing the SQL statement that has passed the verification by using the first optimizer, the method further includes:

[0017] Determining whether the system needs to be optimized again;

[0018] When the judgment is yes, using a second optimizer to optimize the SQL statement optimized by the first optimizer;

[0019] When the judgment is no, the SQL statement optimized by the first optimizer is output.

[0020] In an optional manner, the determining whether the system needs to be optimized again is specifically: determining whether the system is configured with a data source, and when the determination is yes, it indicates that the system needs to be optimized again; and

[0021] The using the second optimizer to optimize the SQL statement optimized by the first optimizer, and outputting the currently optimized SQL statement includes:

[0022] Preliminarily optimizing the SQL statement optimized by the first optimizer based on the index;

[0023] Use the EXPLAIN optimization chain to execute SQL statements that have been preliminarily optimized.

[0024] In an optional manner, when the judgment is yes, after optimizing the SQL statement optimized by the first optimizer using the second optimizer, the method further includes:

[0025] Determining whether the SQL statement optimized by the second optimizer meets the optimization standard;

[0026] When the judgment is yes, the currently optimized SQL statement is output;

[0027] When the judgment is no, it is determined whether to stop the optimization according to the number of times the SQL statement is optimized by the second optimizer.

[0028] In an optional manner, when the judgment is no, determining whether to stop the optimization according to the number of times the SQL statement is optimized by the second optimizer includes:

[0029] Determine whether the number of optimizations of the SQL statement by the second optimizer exceeds a preset value;

[0030] When the SQL statement does not meet the optimization standard and the number of optimizations of the SQL statement by the second optimizer does not exceed the preset value, go to the step of performing preliminary optimization on the SQL statement optimized by the first optimizer based on the index and continue to optimize until the number of optimizations reaches the preset value, and then output the currently optimized SQL statement;

[0031] When the SQL statement does not meet the optimization standard, and the number of optimization times of the SQL statement by the second optimizer reaches the preset value, the current optimization result is output.

[0032] In an optional manner, before determining whether the SQL statement optimized by the second optimizer meets the optimization standard, the method further includes:

[0033] Perform EXPLAIN analysis on optimized SQL statements; and / or

[0034] The determining whether the SQL statement optimized by the second optimizer meets the optimization standard includes:

[0035] Obtaining the execution result of the EXPLAIN optimization chain executing the SQL statement that has been preliminarily optimized;

[0036] Analyze the obtained execution result, which includes the following fields: type field, key field, and extra field;

[0037] If the fields satisfy the corresponding preset conditions respectively, the optimization criteria are met; and / or

[0038] The preliminarily optimizing the SQL statement optimized by the first optimizer based on the index includes:

[0039] Obtain the execution plan result set of the SQL statement based on the EXPLAIN keyword;

[0040] A preset number of optimizations are performed based on the execution plan result set.

[0041] According to another aspect of an embodiment of the present application, a SQL statement optimization system is provided, including:

[0042] An import module, a verification module connected to the import module, and a first optimization module connected to the verification module, wherein:

[0043] The import module is used to import SQL statements that need to be optimized when the system is configured with a data source;

[0044] The verification module is used to perform syntax verification on the imported SQL statements;

[0045] The first optimization module is used to optimize the SQL statement that has passed the verification based on the table structure information of the data source using the first optimizer when the verification passes.

[0046] According to another aspect of an embodiment of the present application, an electronic device is provided, comprising: a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the above-mentioned SQL statement optimization method.

[0047] According to another aspect of the embodiments of the present application, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the above-mentioned SQL statement optimization method is implemented.

[0048] The embodiment of the present application first performs a syntax check. After the check is passed, SQL optimization can be performed based on the table structure information of the data source. There is no need to connect to a specific database, so that SQL statements can be used in complex environments, improving practicality.

[0049] The above description is only an overview of the technical solution of the embodiment of the present application. In order to more clearly understand the technical means of the embodiment of the present application, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the embodiment of the present application more obvious and easy to understand, the specific implementation methods of the present application are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0050] The accompanying drawings are only used to illustrate the embodiments and are not to be considered as limiting the present application. In addition, the same reference symbols are used to represent the same components throughout the accompanying drawings. In the accompanying drawings:

[0051] Figure 1 A schematic diagram of a process flow of a method for optimizing SQL statements provided in an embodiment of the present application is shown;

[0052] Figure 2 A schematic diagram of a flow chart of a method for optimizing SQL statements provided in another embodiment of the present application is shown;

[0053] Figure 3 A specific flow chart of step S220 of a method for optimizing SQL statements provided in another embodiment of the present application is shown;

[0054] Figure 4 A specific flow chart of step S230 of a method for optimizing SQL statements provided in another embodiment of the present application is shown;

[0055] Figure 5 A specific flow chart of step S250 of a method for optimizing SQL statements provided in another embodiment of the present application is shown;

[0056] Figure 6 A schematic diagram of the structure of an optimization system for SQL statements provided in an embodiment of the present application is shown;

[0057] Figure 7 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application is shown. DETAILED DESCRIPTION

[0058] The exemplary embodiments of the present application will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present application are shown in the drawings, it should be understood that the present application can be implemented in various forms and should not be limited by the embodiments set forth herein.

[0059] The system of the present invention is any target system that uses a database and has a query function. The target system can use the optimization system of the present invention. The optimization system of the present invention can be deployed independently or embedded in the aforementioned target system for use.

[0060] Figure 1 The flowchart of the SQL statement optimization method provided by the embodiment of the present application is shown, and the method is executed by an electronic device. The electronic device may be a server, an embedded system, etc. Figure 1 As shown, the method comprises the following steps:

[0061] Step 110: When the system is configured with a data source, the SQL statement that needs to be optimized is imported.

[0062] The system can be any system that uses a database. As long as a database is used and there is a query operation, the optimization device of the present invention can be used. The optimization device can be deployed independently or packaged into a jar package and referenced by other systems. When the system is configured with a data source, it means that the optimization method of the present invention can be used, and the SQL statement that needs to be optimized is imported into the system. The data source carries the database table address that provides the SQL statement, and the system can connect to the database through this address. The data source also carries table structure information.

[0063] Step 120: Perform syntax check on the imported SQL statement.

[0064] Among them, the SQL statement syntax parser is called to check whether the SQL statement syntax is correct.

[0065] Step S130: When the verification is passed, the first optimizer is used to optimize the SQL statement that has passed the verification based on the table structure information of the data source.

[0066] When the check is passed, it means that the syntax is fine and the next step of optimization can be performed. Then, according to the table structure information of the data source, the first optimizer is used to optimize the SQL statement that has passed the check. Preferably, the first optimizer can be a NORMAL optimizer.

[0067] In the embodiment of the present invention, since the NORMAL optimizer is used to optimize the SQL statement based on the table structure information of the data source, there is no need to connect to a specific database. It only needs to analyze the data structure of the table involved in the SQL statement. This can simplify the optimization process, be applied to complex scenarios, and improve practicality.

[0068] Figure 2 A flowchart of a method for optimizing SQL statements provided by another embodiment of the present application is shown, and the method is executed by an electronic device. The electronic device may be a server, an embedded system, etc. Figure 2 As shown, the method comprises the following steps:

[0069] Step 210: When the system is configured with a data source, the SQL statement that needs to be optimized is imported.

[0070] The system can be any system that uses a database. As long as a database is used and there is a query operation, the optimization device of the present invention can be used. The optimization device can be deployed independently or packaged into a jar package and referenced by other systems. When the system is configured with a data source, it means that the optimization method of the present invention can be used, and the SQL statement that needs to be optimized is imported into the system. The data source carries the database table address that provides the SQL statement, and the system can connect to the database through this address. The data source also carries table structure information.

[0071] Step 220: Perform syntax check on the imported SQL statement.

[0072] Among them, the SQL statement syntax parser is called to check whether the SQL statement syntax is correct.

[0073] In a preferred solution of this embodiment, the specific process of verification is as follows (see Figure 3 ):

[0074] Step A1: perform lexical verification on the imported SQL statement.

[0075] Among them, based on the SQL statement lexical rules, the SQL statement lexical verification library is generated. In the SQL statement lexical verification library, the lexical analysis of SQL statements is the process of converting source code into a token sequence. A token is composed of a morpheme and a token type. A morpheme is the smallest semantic unit in the source code, and the token type indicates the category of the morpheme. In SQL, common token types include keywords, identifiers, operators, separators, etc.

[0076] Furthermore, the lexical check library of the SQL statement is composed of common morphemes, and a complete SQL statement is composed of different morphemes. The specific checking process can adopt existing technology, which is not limited here.

[0077] Step A2: determine whether the verification result complies with the lexical rules. If yes, go to step A3; otherwise, go to step A4.

[0078] Step A3: Perform syntax check on the SQL statement;

[0079] Among them, based on the grammatical rules of SQL statements, an SQL statement grammar verification library is generated. In the SQL statement grammar verification library, the syntax of the SQL statements that pass the lexical rule verification is matched and checked, and the inspection results are output. Only on the premise that the lexical check passes, the grammar rule verification judgment can be performed. SQL grammar checking and verification refers to the analysis and verification of the written SQL statements to ensure that they comply with the specifications and grammar rules of the SQL language. By performing grammar checking, errors can be discovered early and help can be provided, thereby improving the quality and readability of the code. The SQL statement grammar verification library is a code program generated according to the grammar rules of SQL statements. The verification process includes checking whether the keyword position in the SQL statement is wrong, whether the operator is wrong, whether the logic is wrong, whether the type of the field and the field assignment is consistent, etc. If an error occurs, a corresponding error or warning message is given to help developers quickly repair and debug the code.

[0080] Step A4: End the SQL statement optimization process.

[0081] Step A5: Determine whether the grammar check result complies with the grammar rules. If so, go to step S220; if not, go to step A4.

[0082] Among them, the verification process of the syntax analyzer is to decompose a complete SQL statement into more than one sub-statements based on lexical analysis according to different keywords, and each sentence has a specific grammatical structure. The analyzer will verify whether each sentence exists according to the grammatical structure, and mark the sequence of existing sentences according to the correct grammatical structure. If the marking sequence does not conform to the grammatical rules, it does not meet the grammatical verification. The grammatical structure is pre-set. If the verification of all sentences passes, it is necessary to further check the position of each sentence. If the position verification passes, it means that the syntax verification is met.

[0083] Step S230: When the verification is passed, the first optimizer is used to optimize the SQL statement that has passed the verification based on the table structure information of the data source.

[0084] Among them, when the verification is passed, it means that there is no problem with the syntax and the next step of optimization can be carried out. Then, according to the table structure information of the data source, the first optimizer is used to optimize the SQL statement that has passed the verification. Preferably, the first optimizer can be a NORMAL optimizer. The NORMAL optimizer optimizes the database table structure and does not need to be connected to a specific database. It only needs to analyze the data structure of the table involved in the SQL statement. The analysis method used basically includes different SQL query scenarios, such as single table query, multi-table joint query, group query, sorting, etc. The analysis method constitutes the NORMAL optimization chain in the NORMAL optimizer.

[0085] In the embodiment of the present invention, before step S210, the following steps are also included:

[0086] Step S200, determining whether the system is configured with a data source;

[0087] Among them, it is first necessary to determine whether the system is currently equipped with a data source. If the determination result is yes, go to step S210, otherwise go to step S240;

[0088] Step S240: import the table structure information and the SQL statement to be optimized, and then go to the step of verifying the imported SQL statement, that is, go to step S210.

[0089] If the data source is not configured, you need to import the table structure information before entering the optimization process. For example, you can verify the lexical and grammatical aspects of SQL statements based on the table structure information, and you can also optimize the NORMAL optimizer based on the table structure data.

[0090] Preferably, if Figure 4 As shown, step S230 specifically includes:

[0091] Step B1, preliminarily optimizing the SQL statement based on the table structure information of the data source;

[0092] Step B2: Use the NORMAL optimization chain to execute the SQL statement that has been preliminarily optimized.

[0093] In a preferred solution of this embodiment, the step B1 specifically includes:

[0094] Disassemble and store SQL statements;

[0095] The SQL statements are disassembled according to their execution order, and the disassembly results are stored for subsequent optimization.

[0096] For example: when the optimized SQL statement is "select * from user where name like '%张三%' group by age having age > 1 order by age desc limit 10", during decomposition, the SQL statement will be decomposed according to keywords such as where, like, group by, having, order by, limit, etc., and the values corresponding to the keywords will be stored. The stored data will be used in all subsequent NORMAL optimization methods.

[0097] Perform NORMAL optimization on the decomposed SQL statement multiple times;

[0098] Among them, for the first, second,..., (n - 1)th, and nth NORMAL optimizations of the decomposed SQL statement, the NORMAL optimizer has n optimization methods, and n optimization methods are called to optimize the SQL statement. Here, n is a natural number greater than 1 (preferably greater than 4), and the specific value can be set according to the actual situation, and no restrictions are imposed here.

[0099] For example: when performing the first optimization, optimize the keywords group by and having. For the SQL statement "select job, avg(sal) from user group by job having job = 'president' or job ='manager'", there is the keyword having in this statement. Since filtering needs to be performed on the sorted SQL statement, but if the amount of sorted data is too large, it will affect the sorting performance. From the execution order of the SQL statement, it can be known that the filtering of the where condition is executed before the group by statement. Therefore, this statement is optimized to "select job, avg(sal) from user where job IN ('president','manager') group by job".

[0100] Another example: when performing the second optimization, the join keyword is optimized. There is an existing data table user with a data volume of 10,000,000; a data table order with a data volume of 2 million. There is a query statement select a.user_id,b.id from order aleft join user b on a.user_id=b.id, which follows the rule that the small table drives the large table. At this time, it is modified to selecta.user_id,b.id from user bleft join order a on b.id=a.user_id. Further, the small table drives the large table, that is, the small data set drives the large data set. This rule mainly occurs in the scenario of multi-table joint query. For example, there is an existing data table user with a data volume of 10,000; a data table order with a data volume of 2 million. Then the small table is user and the large table is order. If the user table and the order table are to be associated to query the order data placed by the user, when the user table is used to drive the order table, the data of the small table user is read first, and then the large table order is accessed based on the user data, thereby reducing the amount of data to be queried. On the contrary, if the large table order is accessed first, more data needs to be queried.

[0101] For another example: when performing the third optimization, the optimization method optimizes the on and where conditions. It is necessary to first read the table creation statement in the SQL statement and record the data types of the index and condition column of the table. For example, select a.id, b.user_id from user a join order b on a.id = b.user_id. The id field in the user table is of bigint type, and the user_id in the order table is of varchar type. When querying this SQL statement, an implicit conversion problem will occur, resulting in index failure and low query efficiency. At this time, it can be optimized to select a.id, b.user_id from user a join order bon a.id = cast(b.user_id as signed), keeping the data types on both sides of the equation the same. By comparing the data type of the condition column with the numerical type of the condition column, the problem of implicit data type conversion due to inconsistent data types is avoided, which leads to index failure and reduced query speed. Furthermore, implicit conversion refers to the data type conversion automatically performed by the database management system during the execution of the SQL statement. This conversion usually occurs when the data types do not match but operations such as comparison, calculation or assignment are required. If it is a table query, the system will determine whether the data type of the table field is consistent with the data type assigned to the query condition field. If they are inconsistent, implicit conversion may occur, or an error may be directly reported. If it is a multi-table joint query, the system will count the data types of the connected fields in each table to determine whether the data types are consistent. If they are inconsistent, implicit conversion may occur, or an error may be directly reported.

[0102] Another example: when performing the n-4th optimization (when n>4), the SQL statement is optimized according to its rules. The optimization method used at this time can be customized according to the needs of the system user, but it needs to comply with the optimization link specification and follow the responsibility chain design pattern.

[0103] For another example: when performing the nth optimization, this is the last optimization method in the optimization chain. After execution, it marks the end of the optimization logic of the NORMAL optimizer and outputs the optimized SQL statement.

[0104] In this embodiment, the EXPLAIN optimizer executes SQL statements in the database through the EXPLAIN keyword to obtain the execution plan of the SQL statement. The execution plan is a fixed field result set, but the value of each field is dynamic, and the value of the execution plan of different SQL statements may be different. The value is an analysis result of the execution of this SQL statement in the database. The optimizer analyzes the fields in the result set of this execution plan. The analysis process of each value is equivalent to an optimization method. The optimization methods are connected together to form an EXPLAIN optimization chain.

[0105] In this embodiment, step B2 is specifically: using the NORMAL optimization chain to execute SQL statements. The NORMAL optimization chain is set according to the SQL statement execution order. The optimization chain follows the responsibility chain design pattern to ensure that all optimization chains are fully executed. The optimization chain also supports system developer customization. Different optimization methods can be created according to different business scenarios, and finally these optimization methods are added to the optimization chain through the responsibility chain pattern. Therefore, using the NORMAL optimizer can enhance the practicality of the optimization system so that it can meet different / complex scenarios.

[0106] In this embodiment, the NORMAL optimizer optimizes the database table structure, and does not need to connect to a specific database. It only needs to analyze the data structure of the table involved in the SQL statement. The analysis methods used basically include different SQL query scenarios, such as single table query, multi-table joint query, group query, sorting, etc. These scenarios are embedded in the optimization system of this embodiment, so that the system can be applied to different scenarios and improve practicality.

[0107] In a preferred solution of this embodiment, after step S230, the following steps are further included:

[0108] Step S250: determining whether the system needs to be optimized again;

[0109] Wherein, whether the system needs further optimization can be analyzed by determining whether the system is configured with a data source. If the determination is yes, proceed to step S260; if the determination is no, proceed to step S270;

[0110] Step S260, using the second optimizer to optimize the SQL statement optimized by the first optimizer, and outputting the currently optimized SQL statement;

[0111] The second optimizer is the EXPLAIN optimizer, the first optimizer is the NORMAL optimizer, and the EXPLAIN optimizer is used to optimize the SQL statement optimized by the NORMAL optimizer again, and the currently optimized SQL statement is output.

[0112] Step S270: outputting the SQL statement optimized by the first optimizer;

[0113] If the current system is not configured with table structure information, it means that the EXPLAIN optimizer cannot be used for further optimization.

[0114] In this embodiment, when the data source is not configured, it is required to import the table structure information. The table structure information can be used to verify the lexical and grammatical functions of the SQL statement. The table structure information can be used to optimize the SQL statement using the NORMAL optimizer. The NORMAL optimizer does not need to execute the SQL statement and can be verified through the rule verification library.

[0115] Without configuring a data source, the system cannot execute the explain execution plan of the SQL statement, so it is necessary to end the optimization process after executing the NORMAL optimization chain.

[0116] In a preferred embodiment of this embodiment, Figure 5 As shown, step S260 specifically includes:

[0117] Step C1, preliminarily optimizing the SQL statement optimized by the first optimizer based on the index;

[0118] The execution plan of the SQL statement is obtained through the keyword EXPLAIN. The EXPLAIN optimizer includes all optimization methods for optimizing the execution plan, and all the optimization methods are combined into an EXPLAIN optimization chain.

[0119] Step C2: Use the EXPLAIN optimization chain to execute the SQL statement that has been preliminarily optimized;

[0120] Among them, the optimization chain is set according to the results of the SQL statement execution plan. The optimization chain follows the responsibility chain design pattern to ensure that all optimization links are fully executed. The optimization chain also supports system developers to customize, and can create different optimization methods according to different execution plan results. Finally, these customized optimization methods are added to the optimization chain through the responsibility chain pattern. Therefore, using this optimizer can improve practicality and meet complex application scenarios.

[0121] In a preferred solution of this embodiment, step C2 further includes:

[0122] Step C3: determine whether the optimized SQL statement meets the optimization standard;

[0123] Wherein, it is determined whether the SQL statement optimized by the EXPLAIN optimizer meets the optimization standard. If it is determined to be yes, it goes to step C0, otherwise it goes to step C4;

[0124] Preferably, after the explain optimization chain execution plan is completed, an execution result data is generated, and the execution result data is analyzed to determine whether the optimization standard has been met.

[0125] Step C0, output the currently optimized SQL statement;

[0126] Step C4: Determine whether the number of optimization times of the SQL statement by the second optimizer exceeds a preset value.

[0127] Among them, the number of times the SQL statement is optimized by the second optimizer is counted. If it reaches a preset value, it is determined to stop the optimization and go to step C0. If the number of optimizations does not reach the preset value, go to step C1 to continue the optimization. When the number of optimizations reaches the preset value, go to step C0 and output the currently optimized SQL statement.

[0128] In this embodiment, when the EXPLAIN optimizer is used for optimization, the system automatically increases the number of optimizations by 1 each time the optimization is performed. When the number reaches the preset value, the system directly outputs the currently optimized SQL statement. When the preset value is not reached, the system goes to step C1 to continue the optimization. At this time, the preset value can be set according to the actual scenario, and there is no restriction on this here. Preferably, the preset value can be 3. When the optimization standard is not reached and the number of optimizations does not reach the preset value, the optimization needs to be continued. If the number of optimizations has reached the preset value, there is no need to optimize any more, and the currently optimized SQL statement is directly output; when the optimization standard is reached, it means that the requirements are met, and the currently optimized SQL statement is directly output.

[0129] In a further preferred embodiment of the present invention, after step C2 and before step C3, the following steps are further included:

[0130] Step C5: Perform EXPLAIN analysis on the optimized SQL statement;

[0131] Among them, the analysis of the SQL statement process EXPLAIN execution plan optimized by the EXPLAIN optimizer is performed to obtain an analysis result; further, step C3 determines whether the optimization standard is met based on the analysis result to improve the judgment efficiency and accuracy.

[0132] In this embodiment, the specific process of step C1 is as follows:

[0133] Obtain the execution plan result set of the SQL statement based on the EXPLAIN keyword;

[0134] The EXPLAIN keyword is used to obtain the execution plan result set of the SQL statement, disassemble the execution plan result set, and store the disassembled data inside the optimizer for use by all its internal optimization methods. For example, the execution plan result set of the SQL statement is disassembled according to the field name, such as the result set contains the fields id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, and extra.

[0135] A preset number of optimizations are performed based on the execution plan result set.

[0136] Among them, a preset number of optimizations are performed according to the disassembly results. The preset number may be M times. The specific value is set according to the needs and is not limited here. The specific process of the optimization is as follows:

[0137] For example: when the first optimization is performed, the first optimization method is executed to optimize the SQL statement, and the type field data set in the result is analyzed and optimized. Type indicates what type is used in the query. Type is a very important indicator in SQL optimization. The following performances are from good to bad: system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all. This optimization method monitors rang and the types arranged after it, and according to the index information of the query table, the query fields and condition fields are all index fields. The optimization direction is to use the covering index, thereby improving the efficiency of the query.

[0138] For another example: when the second optimization is performed, the second optimization method is executed to analyze and optimize the possible_keys and key field data sets in the result. possible_keys: the index involved in the query condition field may be used; key: indicates the index actually used by the query. If key is NULL, it means that the index is not used. The optimization method at this time is to combine the output results of key and possible_keys, and according to the query table index information, if it is a joint index, it is necessary to follow the leftmost prefix principle. The direction of this optimization is to actually use the index in the SQL query, thereby improving the query efficiency. Among them, the leftmost prefix principle is a rule that only appears on the joint index. The joint index is a classification of database indexes, which is composed of multiple fields of the database table structure. For example, a joint index idx_name_age(name,age) is created in the user table, indicating that the name and age fields in the user table together constitute an index. When querying, the filtering of the where condition must be in the order in which the index fields are created, such as where name = 'tobin' and age = 18 or where name = 'tobin', to hit the index. On the contrary, if where age = 18 and name = 'tobin' or where age = 18, it means that the leftmost prefix principle is not followed and the index is invalid.

[0139] Another example: when performing the second optimization, the second optimization method is executed to analyze and optimize the extra field data set in the result. Extra indicates some additional information of the execution plan. This optimization method is to parse the output of extra, and focus on using filesort (sorting in the file. If the data is large, it needs to be sorted in the disk) and using temporary (generating a temporary table). According to the query table index information, the sorting and grouping fields are index fields. The direction of this optimization is to avoid sorting in the file and generating temporary tables, thereby improving the efficiency of the query.

[0140] Another example: when the third optimization is performed, the third optimization method is executed to analyze and optimize the extra field data set in the optimization result. Extra indicates some additional information of the execution plan. The focus of this optimization method is to parse the output result of extra. The focus is on using filesort (sorting in the file. If the data is large, it is necessary to complete the sorting in the disk) and using temporary (generating a temporary table). According to the query table index information, the sorting and grouping fields are index fields. The optimization direction is to avoid sorting in the file and generating temporary tables, thereby improving the efficiency of the query. Among them, the use of filesort field indicates that the sorting function is used in the query statement. However, if the sorting field is not an index, the result set of the query needs to be returned to the table again to get the sorting field and sort it in the sorted buffer. If the size of the result set is greater than the capacity of the sorted buffer, a temporary file will be generated on the disk for sorting, which seriously affects the efficiency of the query. When the usingtemporary field appears, it indicates that a temporary table is used in the query statement. The reason for the generation of the temporary table may be the sorting function. Temporary tables are used by the database to assist in complex SQL statement aggregation calculations. They will take up memory first. If the memory is insufficient, the temporary table in memory will be converted to a temporary table on disk. The generation of temporary tables consumes a lot of performance and reduces query efficiency. The direction of this optimization is to give priority to the use of index fields to avoid the generation of temporary tables.

[0141] Another example: the n-4th optimization (n is greater than 4) is performed, and the (n-4)th optimization method is used for optimization. The (n-4)th optimization method generally refers to the n-4 EXPLAIN optimization methods (n>4) on the optimization chain link. According to its rules, the SQL statement is optimized. This optimization method can be customized according to different execution plan results, provided that it meets the optimization chain specifications and follows the responsibility chain design pattern.

[0142] Another example: perform the nth optimization, use the nth optimization method for optimization, execute EXPLAIN optimization method n, and optimize the SQL statement according to its rules. Optimization method n refers to the last optimization method in the optimization chain. After execution, it marks the end of the optimization logic of the EXPLAIN optimizer, and an optimized SQL statement will be output.

[0143] In this embodiment, the specific process of step C3 is as follows:

[0144] Obtaining the execution result of the EXPLAIN optimization chain executing the SQL statement that has been preliminarily optimized;

[0145] Analyze the obtained execution result, which includes the following fields: type field, key field, and extra field;

[0146] After the explain optimization chain completes the optimization plan, it will generate an execution result data. By analyzing the result data, it is determined whether the optimization standard has been met. The optimization standard can be set according to the execution status of the target type field. For example, the judgment standard is whether the comparison result of the target field value preset value and the target value meets the expectation. If the fields meet the corresponding preset conditions respectively, the optimization standard is met;

[0147] If the corresponding fields meet the corresponding preset conditions respectively, the optimization standard is achieved.

[0148] For example, the value of the result field type is judged, and the fields are sorted in the order of optimization effect from left to right. Specifically, they are system / const / eq_ref / ref / ref_or_null / index_merge / unique_subquery / index_subquery / range / index / all, where system is the best, range is the standard, and all is the worst. The optimization standard at this time can be: focus on monitoring the range field and the field that follows it. If it is all, the optimization standard is not met. If it is the type value before range, it means that the optimization standard is met.

[0149] For another example: if the value of the result field key is NULL, you can know whether the SQL statement uses the index. If it is NULL, it does not meet the optimization criteria.

[0150] For another example, if the value of the result field extra is Using index, it means that a covering index is used, and the efficiency is very objective; if it is Using filesort or Using temporary, the optimization standard is not met.

[0151] In the embodiment of the present invention, since the NORMAL optimizer is used to optimize the SQL statement based on the table structure information of the data source, there is no need to connect to a specific database. It only needs to analyze the data structure of the table involved in the SQL statement. This can simplify the optimization process, be applied to complex scenarios, and improve practicality.

[0152] Secondly, use the EXPLAIN optimizer to further optimize, enhance the optimization effect, reduce losses, and improve efficiency.

[0153] Based on the above method embodiment, the present invention also proposes an optimization system for SQL statements. Figure 6 The schematic diagram of the structure of the optimization system of SQL statements provided in the embodiment of the present application is shown. Figure 6 As shown, the system 600 includes: an import module 610, a verification module 620 and a first optimization module 630, wherein the import module 610 is connected to the verification module 620, and the verification module 620 is connected to the first optimization module 630, wherein:

[0154] The import module 610 is used to import the SQL statement that needs to be optimized when the system is configured with a data source;

[0155] Verification module 620, used for performing syntax verification on the imported SQL statements;

[0156] The first optimization module 630 is used to optimize the SQL statement that has passed the verification based on the table structure information of the data source when the verification passes.

[0157] If the verification fails, the import module 610 is notified to import the SQL statement that needs to be optimized.

[0158] In this embodiment, the first optimization module 630 can be a NORMAL optimizer. Since the NORMAL optimizer is used to optimize the SQL statement based on the table structure information of the data source, there is no need to connect to a specific database. It only needs to analyze the data structure of the table involved in the SQL statement. This can simplify the optimization process, be applied to complex scenarios, and improve practicality.

[0159] In an optional manner, the system 600 further includes:

[0160] A determination module 640 is used to determine whether the system is configured with a data source;

[0161] When the judgment is yes, feedback is given to the import module 610, and when the judgment is no, an instruction to import the table structure information is sent to the import module 610, so that the import module 610 imports the table structure information according to the instruction.

[0162] In an optional manner, the first optimization module 630 is specifically configured to:

[0163] Preliminary optimization of SQL statements based on the table structure information of the data source;

[0164] Use the NORMAL optimization chain to execute SQL statements that have been preliminarily optimized.

[0165] In an optional manner, the system 600 further includes:

[0166] A second optimization module 650, configured to optimize the SQL statement optimized by the first optimizer;

[0167] The second optimization module 650 is preferably a second optimizer, and further preferably, the second optimizer is an EXPLAIN optimizer.

[0168] The output module 660 is used to output the currently optimized SQL statement.

[0169] The judging module 640 is further used to judge whether the system needs to be optimized again, and when the judgment is yes, send an optimization instruction to the second optimizing module 650.

[0170] In an optional manner, the determination module 640 is specifically used to: determine whether the system is configured with a data source;

[0171] The first optimization module 620 is specifically used for:

[0172] Preliminarily optimizing the SQL statement optimized by the first optimizer based on the index;

[0173] Use the EXPLAIN optimization chain to execute SQL statements that have been preliminarily optimized.

[0174] In an optional manner, the determining module 640 is further configured to:

[0175] Determine whether the optimized SQL statement meets the optimization criteria;

[0176] When the judgment is yes, an output instruction is sent to the output module 660;

[0177] When the judgment is no, the judgment module 640 is further used to judge the number of times the SQL statement has been optimized by the second optimization module 650, and determine whether to issue an instruction to stop optimization to the second optimization module 650 according to the number of optimizations.

[0178] In an optional manner, the judgment module 640 is specifically used to: when it is determined that the SQL statement does not meet the optimization standard and the number of optimizations does not reach a preset value, issue an optimization instruction to the second optimization module 650, until the number of optimizations by the second optimization module 650 reaches a preset value, and then issue an output instruction to the output module 660.

[0179] It is also used to: when it is determined that the SQL statement does not meet the optimization standard and the number of optimizations by the second optimization module 650 has reached a preset value, send an output instruction to the output module.

[0180] In an optional manner, the system further includes:

[0181] Analysis module 670, used for performing EXPLAIN analysis on the optimized SQL statement;

[0182] The judgment module 640 is specifically used to: obtain the execution result of the SQL statement that has been preliminarily optimized by the EXPLAIN optimization chain, analyze the obtained execution result, and the execution result includes the following fields: type field, key field, and extra field; if the fields respectively meet the corresponding preset conditions, it is judged that the optimization standard is met;

[0183] The second optimization module 650 is specifically used for:

[0184] Obtain the execution plan result set of the SQL statement based on the EXPLAIN keyword;

[0185] A preset number of optimizations are performed based on the result set.

[0186] It should be noted that this system matches the optimization method described in the aforementioned embodiment. The system can implement any function of the optimization method of the aforementioned embodiment. The specific implementation functions / processes of the system are not described here. For details, please refer to the aforementioned method embodiment.

[0187] In the embodiment of the present invention, since the NORMAL optimizer is used to optimize the SQL statement based on the table structure information of the data source, there is no need to connect to a specific database. It only needs to analyze the data structure of the table involved in the SQL statement. This can simplify the optimization process, be applied to complex scenarios, and improve practicality.

[0188] Figure 7 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application is shown, and the specific embodiment of the present application does not limit the specific implementation of the electronic device.

[0189] like Figure 7 As shown, the electronic device 700 may include: a processor (processor) 702 and a memory (memory) 704.

[0190] The memory 704 is used to store a computer program 706. The memory 704 may include a high-speed RAM memory, or may also include a non-volatile memory, such as at least one disk memory. The computer program 706 may include computer executable instructions.

[0191] The processor 702 is used to execute the computer program 706 to implement the above-mentioned SQL statement optimization method embodiment.

[0192] The processor 702 may be a central processing unit (CPU), or an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of the present application. The one or more processors included in the electronic device 700 may be processors of the same type, such as one or more CPUs, or may be processors of different types, such as one or more CPUs and one or more ASICs.

[0193] In this embodiment, the processor 702 is used to execute:

[0194] When the system is configured with a data source, import the SQL statements that need to be optimized;

[0195] Perform syntax check on the imported SQL statements;

[0196] When the verification passes, the first optimizer is used to optimize the SQL statement that passes the verification based on the table structure information of the data source.

[0197] An embodiment of the present application provides a computer-readable storage medium, wherein the storage medium stores a computer program, and when the computer program is executed by a processor, the above-mentioned SQL statement optimization method embodiment is implemented.

[0198] The computer program performs the following functions: when the system is configured with a data source, importing the SQL statement that needs to be optimized;

[0199] Perform syntax check on the imported SQL statements;

[0200] When the verification passes, the first optimizer is used to optimize the SQL statement that passes the verification based on the table structure information of the data source.

[0201] An embodiment of the present application provides a computer program, which can be executed by a processor to implement the above-mentioned SQL statement optimization method embodiment.

[0202] The computer program is used to perform the following functions: when the system is configured with a data source, importing the SQL statement that needs to be optimized;

[0203] Perform syntax check on the imported SQL statements;

[0204] When the verification passes, the first optimizer is used to optimize the SQL statement that passes the verification based on the table structure information of the data source.

[0205] An embodiment of the present application provides a computer program product, which includes a computer program. When the computer program is executed by a processor, the above-mentioned SQL statement optimization method embodiment is implemented.

[0206] The computer program is used to perform the following functions: when the system is configured with a data source, importing the SQL statement that needs to be optimized;

[0207] Perform syntax check on the imported SQL statements;

[0208] When the verification passes, the first optimizer is used to optimize the SQL statement that passes the verification based on the table structure information of the data source.

[0209] In the present invention, since the NORMAL optimizer is used to optimize SQL statements based on the table structure information of the data source, there is no need to connect to a specific database. It only needs to analyze the data structure of the table involved in the SQL statement. This can simplify the optimization process, be applied to complex scenarios, and improve practicality.

[0210] Secondly, use the EXPLAIN optimizer to further optimize, enhance the optimization effect, reduce losses, and improve efficiency.

[0211] In several embodiments provided in the present application, if any function is implemented in the form of a software function module / unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the technical solution of the present application can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions for a computer device (which can be a personal computer, server or other electronic device) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory, ROM), random access memory (RandomAccess Memory, RAM), disk or optical disk and other media that can store computer program codes.

[0212] The algorithm or display provided here are not inherently related to any specific computer, virtual system or other equipment. Various general systems can also be used together with the teaching based on this. According to the above description, it is obvious to construct the structure required for this type of system. In addition, the present application embodiment is not directed to any specific programming language yet. It should be understood that various programming languages ​​can be utilized to realize the content of the present application described here, and the above description of specific languages ​​is to disclose the best mode of implementation of the present application.

[0213] It should be noted that the above embodiments illustrate the present application rather than limit the present application, and that those skilled in the art may design alternative embodiments without departing from the scope of the appended claims. In the claims, any reference symbol between brackets shall not be constructed as a limitation on the claims. The word "comprising" does not exclude the presence of elements or steps not listed in the claims. The word "one" or "an" preceding an element does not exclude the presence of multiple such elements. The present application may be implemented by means of hardware including several different elements and by means of a suitably programmed computer. In the claims that list several devices, several units or modules in these devices may be embodied by the same hardware item. The use of the words first, second, and third, etc. does not indicate any order. These words may be interpreted as names. The steps in the above embodiments, unless otherwise specified, should not be understood as limitations on the order of execution.

[0214] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A method for optimizing SQL statements, characterized in that: The method comprises: When the system is configured with a data source, import the SQL statements that need to be optimized; Perform syntax check on the imported SQL statements; When the verification passes, the first optimizer is used to optimize the SQL statement that passes the verification based on the table structure information of the data source.

2. The method according to claim 1, characterized in that When the system is configured with a data source, before importing the SQL statement that needs to be optimized, the method further includes: Determining whether the system is configured with a data source; When the judgment is yes, go to the step of importing the SQL statement that needs to be optimized; When the judgment is no, import the table structure information, import the SQL statement that needs to be optimized, and go to the step of verifying the imported SQL statement; and / or The using the first optimizer to optimize the SQL statement that has passed the verification includes: Preliminarily optimizing the SQL statement based on the table structure information of the data source; Use the NORMAL optimization chain to execute SQL statements that have been preliminarily optimized.

3. The method according to claim 2, characterized in that After the first optimizer is used to optimize the SQL statement that has passed the verification, the method further includes: Determining whether the system needs to be optimized again; When the judgment is yes, the second optimizer is used to optimize the SQL statement optimized by the first optimizer, and the currently optimized SQL statement is output; When the judgment is no, the SQL statement optimized by the first optimizer is output.

4. The method according to claim 3, characterized in that The determining whether the system needs to be optimized again is specifically: determining whether the system is configured with a data source, and when the determination is yes, it indicates that the system needs to be optimized again; and The using the second optimizer to optimize the SQL statement optimized by the first optimizer includes: Preliminarily optimizing the SQL statement optimized by the first optimizer based on the index; Use the EXPLAIN optimization chain to execute SQL statements that have been preliminarily optimized.

5. The method according to claim 4, characterized in that When the judgment is yes, after optimizing the SQL statement optimized by the first optimizer using the second optimizer, the method further includes: Determining whether the SQL statement optimized by the second optimizer meets the optimization standard; When the judgment is yes, the currently optimized SQL statement is output; When the judgment is no, it is determined whether to stop the optimization according to the number of times the SQL statement is optimized by the second optimizer.

6. The method according to claim 5, characterized in that When the judgment is no, determining whether to stop the optimization according to the number of times the SQL statement is optimized by the second optimizer includes: Determine whether the number of optimizations of the SQL statement by the second optimizer exceeds a preset value; When the SQL statement does not meet the optimization standard and the number of optimizations of the SQL statement by the second optimizer does not exceed the preset value, go to the step of performing preliminary optimization on the SQL statement optimized by the first optimizer based on the index and continue to optimize until the number of optimizations reaches the preset value, and then output the currently optimized SQL statement; When the SQL statement does not meet the optimization standard, and the number of optimization times of the SQL statement by the second optimizer reaches the preset value, the current optimization result is output.

7. The method according to claim 5, characterized in that Before determining whether the SQL statement optimized by the second optimizer meets the optimization standard, the method further includes: Perform EXPLAIN analysis on optimized SQL statements; and / or The determining whether the SQL statement optimized by the second optimizer meets the optimization standard includes: Obtaining the execution result of the EXPLAIN optimization chain executing the SQL statement that has been preliminarily optimized; Analyze the obtained execution result, which includes the following fields: type field, key field, and extra field; If the fields all meet the corresponding preset conditions, the optimization criteria are met; and / or The preliminarily optimizing the SQL statement optimized by the first optimizer based on the index includes: Obtain the execution plan result set of the SQL statement based on the EXPLAIN keyword; A preset number of optimizations are performed based on the execution plan result set.

8. A SQL statement optimization system, characterized in that: The system comprises: an import module, a verification module connected to the import module, and a first optimization module connected to the verification module, wherein: The import module is used to import SQL statements that need to be optimized when the system is configured with a data source; The verification module is used to perform syntax verification on the imported SQL statements; The first optimization module is used to optimize the SQL statement that has passed the verification by using the first optimizer based on the table structure information of the data source when the verification passes.

9. An electronic device comprising a memory, a processor and a computer program stored in the memory, characterized in that: The processor executes the computer program to implement the SQL statement optimization method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method for optimizing the SQL statement according to any one of claims 1 to 7 is implemented.

Images