Database operation statement optimization method and device, electronic equipment, medium and product

Abstract

The invention provides a database operation statement optimization method and device, electronic equipment, a medium and a product, and relates to the technical field of databases. The method comprises the steps that database operation statements are obtained, and whether the statements contain a first statement or not is judged; the first statement comprises a keyword statement and / or a grouping statement used for deduplication; if yes, after it is determined that the statement meets the preset condition, the first statement is replaced with a second statement, and then a target operation statement is generated through conversion; the preset conditions include that a target column is fixed by a constant value, an aggregation function and a window function are not included, and screening conditions after grouping do not exist; the second statement is used for returning the duplicate results to realize duplicate removal when the number of the duplicate results in the list is determined to be equal to 2; therefore, when the duplicate removal is realized by utilizing the converted target operation statement, the result with the number of duplicate results equal to 2 is directly obtained from the table according to the condition, namely, the duplicate results are returned, and the whole table is not continuously scanned, so that the duplicate removal efficiency is improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device, electronic equipment, medium and product for optimizing database operation statements. Background Art

[0002] Deduplication is a common requirement in database operations, especially when dealing with large data sets containing duplicate data. Different methods can be used to deduplicate the target column based on database operation statements.

[0003] In the prior art, when it is necessary to deduplicate the entire result set, the "distinct" statement can be used to select the target column from the table and remove duplicate rows; when it is necessary to group and deduplicate specific columns, the "group by" statement can be used to scan the entire table and group according to the target column, and then deduplicate the grouped result set.

[0004] However, the above two deduplication methods require full table scanning and selecting all satisfying result sets according to the screening conditions or removing duplicate result sets after all key values ​​are grouped, so the deduplication efficiency is relatively slow. Summary of the invention

[0005] The present application provides a database operation statement optimization method, device, electronic device, medium and product, which are used to solve the problem that the existing deduplication method has a relatively slow deduplication efficiency.

[0006] In a first aspect, the present application provides a method for optimizing database operation statements, the method comprising:

[0007] Obtaining a database operation statement, and determining whether the database operation statement includes a first statement; the first statement includes a keyword statement and / or a grouping statement for deduplication;

[0008] If so, after determining that the database operation statement meets the preset conditions, the first statement is replaced by the second statement; the preset conditions include that the corresponding target column is fixed by a constant value, the database operation statement does not contain an aggregate function and a window function, and there is no filtering condition after grouping; the second statement is used to return the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2;

[0009] A target operation statement is generated based on the second statement conversion.

[0010] Optionally, the keyword statement is used to remove duplicate rows in the target column; the group statement is used to group the data in the table according to the specified column, and each group contains rows with the same value; after determining that the database operation statement meets the preset condition, replacing the first statement with the second statement includes:

[0011] When the first statement is a keyword statement, determining whether the target column corresponding to the database operation statement is a constant value, and whether the database operation statement does not contain an aggregate function and a window function;

[0012] If so, replace the first statement with the second statement;

[0013] If not, then collect the constant conditions connected in the database operation statement, and after determining that the constant conditions are used to perform indirect constant fixation on the target column and the database operation statement does not contain aggregate functions and window functions, replace the first statement with the second statement.

[0014] Optionally, after determining that the database operation statement satisfies a preset condition, replacing the first statement with a second statement includes:

[0015] When the first statement is a grouping statement, determine whether the target column corresponding to the database operation statement is a constant value, and whether the database operation statement does not contain an aggregate function and a window function and does not have a screening condition after grouping;

[0016] If so, replace the first statement with the second statement;

[0017] If not, collect the constant conditions connected in the database operation statement, and after determining that the constant conditions are used to indirectly fix the target column, and the database operation statement does not contain aggregate functions and window functions and does not exist after grouping, replace the first statement with the second statement.

[0018] Optionally, the second statement is a limit 1 statement; the filtering condition after the non-existence of grouping is a filtering condition without having clause restrictions.

[0019] Optionally, the database operation statements include query statements, add statements, delete statements, and update statements.

[0020] Optionally, the method further includes:

[0021] Executing the target operation statement and the database operation statement respectively, and obtaining a first result set and a second result set after the execution;

[0022] Comparing the first result set with the second result set to verify whether the target operation statement is correct;

[0023] When the target operation statement is correct, obtaining a first processing rate for executing the first result set and a second processing rate for executing the second result set;

[0024] When the first processing rate is less than or equal to the second processing rate, prompt information is generated to prompt the developer to modify the target operation statement.

[0025] In a second aspect, the present application provides a database operation statement optimization device, the device comprising:

[0026] A judgment module, used for obtaining a database operation statement, and judging whether the database operation statement includes a first statement; the first statement includes a keyword statement and / or a grouping statement for deduplication;

[0027] A replacement module, used for, when the database operation statement includes a first statement, replacing the first statement with a second statement after determining that the database operation statement satisfies a preset condition; the preset condition includes that the corresponding target column is fixed by a constant value, the database operation statement does not include an aggregate function and a window function, and there is no post-grouping screening condition; the second statement is used for returning the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2;

[0028] A generating module is used to generate a target operation statement based on the second statement conversion.

[0029] In a third aspect, the present application provides an electronic device, comprising: a processor, and a memory communicatively connected to the processor;

[0030] The memory stores computer-executable instructions;

[0031] The processor executes the computer-executable instructions stored in the memory to implement the method as described in any one of the first aspects.

[0032] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the method as described in any one of the first aspects.

[0033] In a fifth aspect, the present application provides a computer program product, comprising a computer program, which, when executed by a processor, implements the method as described in any one of the first aspects.

[0034] In summary, the present application provides a database operation statement optimization method, device, electronic device, medium and product. For the case where all target columns can be fixed by constant values, a specific statement, such as a limit 1 statement, is used to replace the original deduplication method. Specifically, for database operation statements with keyword statements and / or grouping statements for deduplication, after determining that the target column is fixed by a constant value, the database operation statement does not contain an aggregate function and a window function, and there is no screening condition after grouping, a specific statement can be used to replace the original keyword statement and / or grouping statement, thereby generating a target operation statement that meets the conditions. The specific statement is used to return the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2. For example, after scanning a table and finding that there are 2 duplicates of a certain result 1, the table is no longer scanned, but deduplication is performed directly, and result 1 is returned. In this way, when deduplication is achieved using the converted target operation statement, it is not necessary to first scan the entire table according to the conditions to obtain a result set and then remove the duplicate results. Instead, the results that meet the conditions can be obtained from the table according to the conditions and then returned without further scanning the table, thereby improving the deduplication efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0036] Figure 1 A schematic diagram of an application scenario provided for an embodiment of the present application;

[0037] Figure 2 A flowchart of a method for optimizing database operation statements provided in an embodiment of the present application;

[0038] Figure 3 A flowchart of an optional DISTINCT statement optimization method provided in an embodiment of the present application;

[0039] Figure 4 A flowchart of an optional group by statement optimization method provided in an embodiment of the present application;

[0040] Figure 5 A schematic diagram of the structure of a database operation statement optimization device provided in an embodiment of the present application;

[0041] Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.

[0042] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0043] In order to facilitate the clear description of the technical solutions of the embodiments of the present application, in the embodiments of the present application, the words "first", "second" and the like are used to distinguish the same items or similar items with substantially the same functions and effects. For example, the first device and the second device are only used to distinguish different devices, and the order of precedence is not limited. Those skilled in the art can understand that the words "first", "second" and the like do not limit the quantity and execution order, and the words "first", "second" and the like do not necessarily limit the difference.

[0044] It should be noted that, in this application, words such as "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in this application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.

[0045] In this application, "at least one" means one or more, and "plurality" means two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone, where A and B can be singular or plural. The character " / " generally indicates that the previous and next associated objects are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b, or c can mean: a, b, c, ab, ac, bc, or abc, where a, b, c can be single or multiple.

[0046] For the result set where the target column can be fixed, one possible implementation method is to sequentially scan the entire table according to the conditions. After scanning all the results, use the "distinct" statement to select the target column from the table and remove duplicate rows. Another possible implementation method is to use the "group by" statement to scan the entire table and group according to the target column, and then deduplicate the grouped result set.

[0047] Although both of the above two deduplication methods can reduce the result set, they require a full table scan and select all satisfying result sets according to the filter conditions or remove duplicate result sets after all key values ​​are grouped, so the deduplication efficiency is relatively slow.

[0048] In response to the above problems, the present application provides a database operation statement optimization method. For the case where all target columns can be fixed by constant values, a specific statement, such as a limit 1 statement, is used to replace the original deduplication method. Specifically, for database operation statements that have keyword statements and / or grouping statements for deduplication, after determining that the target column is fixed by a constant value, the database operation statement does not contain an aggregate function and a window function, and there is no screening condition after grouping, a specific statement can be used to replace the original keyword statement and / or grouping statement, thereby generating a target operation statement that meets the conditions. The specific statement is used to return the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2. For example, after scanning a table and finding that there are 2 duplicates of a certain result 1, the table is no longer scanned, but deduplication is performed directly, and result 1 is returned. In this way, when deduplication is achieved using the converted target operation statement, there is no need to first scan the entire table according to the conditions to obtain a result set and then remove the duplicate results. Instead, the results that meet the conditions can be obtained from the table according to the conditions and then returned without further scanning the table, thereby improving the deduplication efficiency.

[0049] For example, Figure 1 A schematic diagram of an application scenario provided in an embodiment of the present application, such as Figure 1 As shown, the application scenario can be applied to the scenario where deduplication is performed when all target columns are fixed. The application scenario includes: a server 102 and a display device 101 corresponding to the server 102. The display device 101 is used for users to view data in the table and perform a series of table operations, such as adding, deleting, modifying, and querying the table.

[0050] Taking the deduplication operation on the data in a certain table as an example, the server 102 can obtain relevant database operation statements from the database in advance, such as relevant statements including the "distinct" statement. Furthermore, after the server 102 recognizes that the database operation statement includes the "distinct" statement, it can further determine whether the database operation statement meets the preset conditions. After determining that the database operation statement meets the preset conditions, the "distinct" statement can be replaced with a specific statement, such as a limit 1 statement, and then a target operation statement is generated based on the limit 1 statement conversion, so that the server 102 can perform deduplication operations on the data in the table based on the target operation statement.

[0051] Among them, the preset conditions include that the corresponding target columns are fixed by constant values, the database operation statement does not contain aggregate functions and window functions, and there are no filtering conditions after grouping. Taking the database operation statement as a query statement and including a "distinct" statement as an example, the preset conditions can be: the target columns can all be fixed by constant values, and the query statement does not contain aggregate functions and window functions.

[0052] Furthermore, after the server 102 performs a deduplication operation on the data in the table based on the target operation statement, the deduplication result set may be displayed on the display device 101 for the user to view.

[0053] In this way, when using the target operation statement to deduplicate data in the table, the results that meet the conditions can be determined in the table. That is, when the number of duplicate results is equal to 2, the results are returned directly without continuing to scan the table, which greatly improves the deduplication efficiency.

[0054] It should be noted that the embodiment of the present application does not specifically limit the form corresponding to a specific statement. It can be used to directly return the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2.

[0055] Optionally, when it is determined that the database operation statement is a related statement including a "group by" statement, the above method can also be used to optimize the statement to obtain a target operation statement to perform a deduplication operation on the table.

[0056] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0057] Figure 2 A flowchart of a method for optimizing database operation statements provided in an embodiment of the present application is shown, taking the execution subject as a server as an example. Figure 2 As shown, the database operation statement optimization method includes the following steps:

[0058] S201. Obtain a database operation statement, and determine whether the database operation statement includes a first statement; the first statement includes a keyword statement and / or a grouping statement for deduplication.

[0059] In an embodiment of the present application, a database operation statement may refer to a command or instruction used to interact with a database, which is usually written in a structured query language such as SQL. The database operation statement can be used for various operations, such as querying data, inserting data, updating data, and deleting data, etc., and may include query statements, add statements, delete statements, and update statements.

[0060] Among them, query statements are used to retrieve data from the database, add statements are used to insert new records into the database table, delete statements are used to delete records from the database table, and update statements are used to modify existing records in the database table.

[0061] Since database operation statements may include query statements, add statements, delete statements, and update statements, the present application can optimize query statements, add statements, delete statements, and update statements. Optimizing query statements can significantly reduce query time. More efficient query statements can reduce the central processing unit (Central Processing Unit) of the database server. Unit, CPU) and memory usage, thereby improving query performance, and through deduplication and other optimization operations, the accuracy of query results can be ensured to avoid the occurrence of duplicate or erroneous data; optimizing add statements can speed up data insertion, and more efficient add operations can reduce the lock time of database tables, thereby improving concurrency performance and reducing the impact on other operations, and through preset conditions and optimization, it can be ensured that the inserted data meets data integrity constraints; optimizing delete statements can speed up data deletion, and more efficient delete operations can reduce the lock time of database tables, thereby improving concurrency performance and reducing the impact on other operations, and through preset conditions and optimization, it can be ensured that delete operations will not destroy data consistency and integrity; optimizing update operations can increase the update rate, and more efficient update operations can reduce the lock time of database tables, thereby improving concurrency performance and reducing the impact on other operations, and through preset conditions and optimization, it can be ensured that update operations will not destroy data consistency and accuracy and avoid data errors.

[0062] It should be noted that optimized query statements can be used to process large data sets, which is crucial for real-time data analysis and fast response; optimized add statements can be used to insert data in batches, which is very important for data import and initialization; optimized delete statements can be used to delete large amounts of data, which is very important for data cleaning and maintenance; optimized update statements can be used to update data in batches, which is very important for data synchronization and maintenance.

[0063] In an embodiment of the present application, the keyword statement may be a distinct statement, which is used to remove duplicate rows in a target column; the grouping statement may be a group by statement, which is used to group the data in a table according to a specified column, and each group contains rows with the same value. For example, the database operation statement may be select 5,5 from test group by 5,5, which means grouping according to the constant value '5' from the test table, where '5' is a constant value (constant value), and 'select 5,5' means that each row in the query result will contain two columns, and the values ​​of these two columns are the constant '5'; it may also be select distinct 5,5 from test, which means removing duplicate rows with the constant value '5' from the test table. The embodiment of the present application does not limit the specific content corresponding to the database operation statement, and the above is merely an example.

[0064] S202. If yes, then after determining that the database operation statement meets the preset conditions, replace the first statement with the second statement; the preset conditions include that the corresponding target column is fixed by a constant value, the database operation statement does not contain aggregate functions and window functions, and there is no filtering condition after grouping; the second statement is used to return the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2.

[0065] Among them, the aggregation function is used to calculate a group of values ​​and return a single value, such as "SUM", "COUNT", "AVG", "MAX", "MIN" and other functions; the window function is used to perform calculations within a certain "window" of the query results without grouping the data, such as "RANK", "LAG", "LEAD" and other functions. The embodiments of the present application do not limit the specific function types corresponding to the aggregation functions and window functions, which can be determined according to the specific circumstances.

[0066] In an embodiment of the present application, the second statement can be a limit 1 statement; the filtering condition after non-grouping can be a filtering condition without a having clause restriction. The embodiment of the present application does not specifically limit the content of the second statement and the content of the filtering condition after non-grouping. The second statement can return duplicate results to achieve the purpose of deduplication when the number of duplicate results in the determined list is equal to 2. The filtering condition after non-grouping can ensure that the content of the filtering condition restriction does not conflict with the condition that all target columns are fixed by constant values.

[0067] It should be noted that traditional deduplication operations may require scanning the entire data set, but using the limit 1 statement can significantly reduce the amount of data that the database needs to process and speed up the response time. Especially when processing large data sets, it can significantly improve the performance of deduplication. Ensuring that there are no filtering conditions restricted by the having clause can avoid complex grouping and filtering operations, thereby reducing unnecessary calculations and improving processing efficiency.

[0068] Optionally, there are two cases where the first statement is replaced by the second statement. The first case is to use the limit 1 statement instead of the distinct statement to perform fixed deduplication on all target columns. For example, S202 includes:

[0069] When the first statement is a distinct statement, determining whether the target column corresponding to the database operation statement is a constant value, and whether the database operation statement does not contain an aggregate function and a window function;

[0070] If so, replace the first statement with the second statement;

[0071] If not, then the constant conditions of the connection in the database operation statement are collected, and after determining that the constant conditions are used to perform indirect constant fixation on the target column, the first statement is replaced by the second statement.

[0072] In some embodiments, taking the database operation statement as a query statement as an example, after determining that the database operation statement contains a distinct statement, it is determined whether the database operation statement meets the preset conditions, that is, whether the target columns can all be fixed by constant values, and whether the query statement does not contain aggregate functions and window functions. If so, the limit 1 statement is used instead of the distinct statement, that is, the distinct statement is replaced with a limit 1 statement.

[0073] For example, Figure 3 A flowchart of an optional distinct statement optimization method provided in an embodiment of the present application is shown as follows: Figure 3 As shown, taking the database operation statement as a query block as an example, the distinct statement optimization method includes the following steps:

[0074] Step A: Determine whether the query block contains a keyword (distinct) statement for deduplication. If so, determine whether the target column corresponding to the query block is a constant. If so, directly replace the distinct statement with a limit 1 statement. Otherwise, execute step B.

[0075] Step B: Collect the constant conditions of the connection in the query block, that is, collect the constant conditions in where and connection. The constant conditions are that all the conditions connected by where and on can be fixed by constant values. Further, determine whether the query block containing the constant conditions meets the restriction conditions (preset conditions), that is, determine whether the constant conditions are used to indirectly fix the target column to a constant value, and whether the query block does not contain aggregate functions and window functions; if so, execute step C; if not, do not optimize the query block, but use the original query block for deduplication.

[0076] Step C: For the query block, replace the distinct statement with the limit 1 statement to obtain the second statement.

[0077] In this way, by converting the distinct statement into a limit 1 statement, and using the limit 1 statement to find the result set that meets the conditions and return it directly, unnecessary table scans can be reduced, and the amount of data that the database needs to process can be significantly reduced, thereby improving overall performance and reducing the load caused by complex queries, thereby enhancing stability and reliability. In addition, automated statement conversion can reduce the workload of developers to manually optimize query statements, thereby improving development efficiency.

[0078] Optionally, the second method is to use the limit 1 statement instead of the group by statement to fix all target columns and perform duplicate removal based on key value groups, such as S202 including:

[0079] When the first statement is a group by statement, determine whether the target column corresponding to the database operation statement is a constant value, and whether the database operation statement does not contain an aggregate function and a window function and does not have a screening condition after grouping;

[0080] If so, replace the first statement with the second statement;

[0081] If not, collect the constant conditions connected in the database operation statement, and after determining that the constant conditions are used to indirectly fix the target column, and the database operation statement does not contain aggregate functions and window functions and does not exist after grouping, replace the first statement with the second statement.

[0082] In some embodiments, taking the database operation statement as a query statement as an example, after determining that the database operation statement contains a group by statement, it is determined whether the database operation statement meets the preset conditions, that is, whether the target columns can all be fixed by constant values, and whether the query statement does not contain aggregation functions and window functions, and whether there is a having clause in the query statement that can be promoted as a screening condition. If so, the limit 1 statement is used instead of the group by statement, that is, the group by statement is replaced with a limit 1 statement.

[0083] For example, Figure 4 A flow chart of an optional group by statement optimization method provided in an embodiment of the present application is as follows: Figure 4 As shown, taking the database operation statement as a query block as an example, the group by statement optimization method includes the following steps:

[0084] Step a: Determine whether the query block contains a group by statement for deduplication. If so, determine whether the target column corresponding to the query block is a constant. If so, directly replace the group by statement with a limit 1 statement. Otherwise, execute step b.

[0085] Step b: Collect the constant conditions of the connection in the query block, that is, collect the constant conditions in where and connection. The constant conditions are that all the conditions connected by where and on can be fixed by constant values. Further, determine whether the query block containing the constant conditions meets the restriction conditions (preset conditions), that is, determine whether the constant conditions are used to indirectly fix the target column, whether the query block does not contain aggregate functions and window functions, and whether there are no filtering conditions after grouping; if so, execute step c; if not, do not optimize the query block, but use the original query block for deduplication.

[0086] Step c: For this query block, replace the group by statement with the limit 1 statement to obtain the second statement.

[0087] In this way, by converting the group by statement into a limit 1 statement, and using the limit 1 statement to find the result set that meets the conditions and return it directly, unnecessary table scans can be reduced, and the amount of data that the database needs to process can be significantly reduced. Through statement optimization, the database load can be reduced, especially when processing large data sets, which helps to improve the overall performance and response time of the database. In addition, automated statement conversion can reduce the workload of developers to manually optimize query statements, thereby improving development efficiency.

[0088] It should be noted that the processing process of adding statements, deleting statements, and updating statements is similar to the above-mentioned query statements. For details, please refer to the description of the above-mentioned embodiment, which will not be repeated here.

[0089] S203: Generate a target operation statement based on the second statement conversion.

[0090] For example, when optimizing the deduplication of a distinct statement, the target columns can be all fixed with constant values, which can be divided into three cases:

[0091] Case 1: When the target column is a constant value, such as the statement select distinct 5,5 from test, when the statement uses distinct to remove duplicates, the entire table is scanned first, and then duplicate values ​​are removed. Based on this application, when the target column is determined to be a constant value 5, 5, the limit 1 statement is used instead of the distinct statement, and then the statement is converted into a target operation statement: select 5,5 from test limit 1.

[0092] Case 2: When the target column is indirectly fixed according to the constant condition, such as the statement select distinct a, b from test where a=1 and b=1; after determining that the statement meets the preset conditions, the limit 1 statement can be used instead of the distinct statement, and then it can be converted into the target operation statement: select a, b from test where a=1and b=1 limit 1.

[0093] Case 3: When the target column is indirectly fixed according to the constant condition, such as the statement select distincts1.a, s1.b from test inner join s2 on s1.a=s1.b and s1.a=5; after determining that the statement meets the preset conditions, the limit 1 statement can be used instead of the distinct statement, and then it can be converted into the target operation statement: select s1.a, s1.b from test inner join s2 on s1.a=s1.b and s1.a=5 limit 1.

[0094] It should be noted that when using the distinct statement to perform deduplication operations, it is necessary to scan the entire table according to the conditions and filter all rows that meet the conditions. Since the target column has been fixed by a constant value, only one result set will be output in the end. Therefore, in the same situation, after using the limit 1 statement instead of the distinct statement, when the limit 1 statement is filtered according to the conditions, as long as one row that meets the conditions is found, the result set can be returned without continuing the filtering operation. Therefore, using limit 1 to implement deduplication operations can avoid unnecessary table scans and greatly improve deduplication efficiency.

[0095] For example, when optimizing the deduplication of the group by statement, the target columns can all be fixed with constant values, which can be divided into three cases:

[0096] Case 1: When the target column is a constant value, such as the statement select 5,5 from test group by 5,5, after determining that the statement meets the preset conditions, the limit 1 statement can be used to replace the group by statement, and then it can be converted into a target operation statement: select 5,5 from test limit 1, that is, the limit 1 statement is used to perform a deduplication operation based on the determined constant value.

[0097] Case 2: When the target column is indirectly fixed according to the constant condition, such as the statement select a, b from test where a=1 and b=1 group by a, b; after determining that the statement meets the preset conditions, the limit1 statement can be used to replace the group by statement, and then it can be converted into the target operation statement: select a, b from test where a=1 and b=1 limit 1.

[0098] Case 3: When the target column is indirectly fixed according to the constant condition, such as the statement is select s1.a, s1.b from test inner join s2 on s1.a=s1.b and s1.a=5 group by s1.b,s1.a; after determining that the statement meets the preset conditions, the limit 1 statement can be used to replace the group by statement, and then it can be converted into the target operation statement: select s1.a, s1.b from test inner join s2 on s1.a=s1.b and s1.a=5 limit 1.

[0099] It should be noted that the use of the group by statement for deduplication requires scanning the entire table and then grouping according to the key value, and then deduplication. Since the target column has been fixed by a constant value, only one result set will be output in the end. Therefore, in the same situation, after using the limit 1 statement instead of the group by statement, when the limit 1 statement is filtered according to the condition, as long as one that meets the condition is found, the result set can be returned without continuing the filtering operation. Therefore, using limit 1 to implement deduplication operations can avoid unnecessary table scanning and key value grouping, greatly improving the efficiency of deduplication.

[0100] Therefore, the embodiments of the present application can significantly improve query performance by judging and replacing database operation statements that contain deduplication keyword statements and / or grouping statements. Using a second statement to replace keyword statements and / or grouping statements can avoid unnecessary grouping and deduplication operations, simplify the query logic, and thus improve deduplication efficiency. That is, when the number of duplicate results in the determined list is equal to 2, duplicate results are returned, which can achieve accurate deduplication and reduce the load on the database, especially when processing large data sets, which helps to improve the overall performance and response time of the database.

[0101] Optionally, the method further includes:

[0102] Executing the target operation statement and the database operation statement respectively, and obtaining a first result set and a second result set after the execution;

[0103] Comparing the first result set with the second result set to verify whether the target operation statement is correct;

[0104] When the target operation statement is correct, obtaining a first processing rate for executing the first result set and a second processing rate for executing the second result set;

[0105] When the first processing rate is less than or equal to the second processing rate, prompt information is generated to prompt the developer to modify the target operation statement.

[0106] Optionally, when the first processing rate is greater than the second processing rate, the target operation statement may be directly used for deduplication.

[0107] Optionally, when the first processing rate is less than or equal to the second processing rate, a prompt message is generated and sent to the developer's terminal device or the display device corresponding to the server to prompt the developer to choose whether to execute the target operation statement or the original database operation statement, or to prompt the developer to modify the target operation statement to obtain an optimized target operation statement so that the processing rate of the optimized target operation statement is greater than the second processing rate.

[0108] In the embodiment of the present application, since the target operation statement may not be executed normally or the execution rate may be abnormal due to grammatical problems or statement omissions, the target operation statement and the database operation statement may be executed separately to verify whether the execution results of the two are consistent, and to compare the processing rates of the two to determine whether the optimized database operation statement is correct and reasonable.

[0109] When it is determined that the execution results of the two are inconsistent, the target operation statement can be corrected in response to the correction operation of the R&D personnel.

[0110] Therefore, the embodiment of the present application can verify the correctness of the target operation statement by comparing the first result set and the second result set. On the premise of verifying that the target operation statement is correct, the performance of the target operation statement can be evaluated by comparing the first processing rate and the second processing rate. If the processing rate of the target operation statement is lower than or equal to the processing rate of the database operation statement, a prompt message will be generated to prompt the R&D personnel to make corrections, thereby helping to optimize the operation statement and improve overall performance. In this way, by verifying and optimizing the operation statement, faults caused by operation statement errors or poor performance can be reduced, thereby improving stability and reliability.

[0111] In the above embodiments, the database operation statement optimization method provided by the embodiment of the present application is introduced, and in order to realize the various functions in the method provided by the embodiment of the present application, the electronic device as the execution subject may include a hardware structure and / or a software module, and the above functions are realized in the form of a hardware structure, a software module, or a hardware structure plus a software module. Whether a certain function of the above functions is executed in the form of a hardware structure, a software module, or a hardware structure plus a software module depends on the specific application and design constraints of the technical solution.

[0112] For example, Figure 5 A schematic diagram of the structure of a database operation statement optimization device provided in an embodiment of the present application is shown in FIG. Figure 5 As shown, the device 500 includes: a judgment module 501, which is used to obtain a database operation statement and judge whether the database operation statement includes a first statement; the first statement includes a keyword statement and / or a grouping statement for deduplication;

[0113] A replacement module 502 is used for replacing the first statement with a second statement when the database operation statement includes the first statement and after determining that the database operation statement satisfies a preset condition; the preset condition includes that the corresponding target column is fixed by a constant value, the database operation statement does not include an aggregate function and a window function, and there is no post-grouping screening condition; the second statement is used for returning the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2;

[0114] The generating module 503 is used to generate a target operation statement based on the second statement conversion.

[0115] Optionally, the keyword statement is used to remove duplicate rows in the target column; the group statement is used to group the data in the table according to the specified column, and each group contains rows with the same value; the replacement module 502 is specifically used to:

[0116] When the first statement is a keyword statement, determining whether the target column corresponding to the database operation statement is a constant value, and whether the database operation statement does not contain an aggregate function and a window function;

[0117] If so, replace the first statement with the second statement;

[0118] If not, then collect the constant conditions connected in the database operation statement, and after determining that the constant conditions are used to perform indirect constant fixation on the target column and the database operation statement does not contain aggregate functions and window functions, replace the first statement with the second statement.

[0119] Optionally, the replacement module 502 is specifically used for:

[0120] When the first statement is a grouping statement, determine whether the target column corresponding to the database operation statement is a constant value, and whether the database operation statement does not contain an aggregate function and a window function and does not have a screening condition after grouping;

[0121] If so, replace the first statement with the second statement;

[0122] If not, collect the constant conditions connected in the database operation statement, and after determining that the constant conditions are used to indirectly fix the target column, and the database operation statement does not contain aggregate functions and window functions and does not exist after grouping, replace the first statement with the second statement.

[0123] Optionally, the second statement is a limit 1 statement; the filtering condition after the non-existence of grouping is a filtering condition without having clause restrictions.

[0124] Optionally, the database operation statements include query statements, add statements, delete statements, and update statements.

[0125] Optionally, the device 500 further includes a correction module, wherein the correction module is configured to:

[0126] Executing the target operation statement and the database operation statement respectively, and obtaining a first result set and a second result set after the execution;

[0127] Comparing the first result set with the second result set to verify whether the target operation statement is correct;

[0128] When the target operation statement is correct, obtaining a first processing rate for executing the first result set and a second processing rate for executing the second result set;

[0129] When the first processing rate is less than or equal to the second processing rate, prompt information is generated to prompt the developer to modify the target operation statement.

[0130] It should be noted that the specific implementation principle and effects of the above-mentioned database operation statement optimization device can be found in the relevant descriptions and effects corresponding to the above-mentioned embodiments, and will not be elaborated here.

[0131] The present application also provides a schematic diagram of the structure of an electronic device. Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application is shown in FIG. Figure 6 As shown, the electronic device may include: a processor 601 and a memory 602 that is communicatively connected to the processor; the memory 602 stores a computer program; the processor 601 executes the computer program stored in the memory 602, so that the processor 601 executes the method described in any of the above embodiments.

[0132] The memory 602 and the processor 601 may be connected via a bus 603 .

[0133] An embodiment of the present application further provides a computer-readable storage medium, which stores computer-executable instructions. When the computer-executable instructions are executed by a processor, they are used to implement the method described in any of the aforementioned embodiments of the present application.

[0134] An embodiment of the present application further provides a chip for executing instructions, wherein the chip is used to execute the method described in any of the aforementioned embodiments as executed by an electronic device in any of the aforementioned embodiments of the present application.

[0135] An embodiment of the present application further provides a computer program product, which includes a computer program. When the computer program is executed by a processor, the method described in any of the aforementioned embodiments of the present application executed by an electronic device can be implemented.

[0136] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of modules is only a logical function division. There may be other division methods in actual implementation, such as multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or modules, which can be electrical, mechanical or other forms.

[0137] The modules described as separate components may or may not be physically separated, and the components shown as modules may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to implement the solution of this embodiment.

[0138] In addition, each functional module in each embodiment of the present application can be integrated into one processing unit, or each module can exist physically separately, or two or more modules can be integrated into one unit. The above-mentioned module-composed unit can be implemented in the form of hardware or in the form of hardware plus software functional units.

[0139] The above-mentioned integrated module implemented in the form of a software function module can be stored in a computer-readable storage medium. The above-mentioned software function module is stored in a storage medium, including a number of instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to perform some steps of the method described in each embodiment of the present application.

[0140] It should be understood that the above processor can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. The general-purpose processor can be a microprocessor or the processor can be any conventional processor, etc. The steps of the method disclosed in the application can be directly embodied as being executed by a hardware processor, or can be executed by a combination of hardware and software modules in the processor.

[0141] The memory may include high-speed random access memory (RAM), and may also include non-volatile memory (NVM), such as at least one disk storage, and may also be a USB flash drive, a mobile hard disk, a read-only memory, a disk or an optical disk, etc.

[0142] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, the bus in the drawings of this application is not limited to only one bus or one type of bus.

[0143] The above storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The storage medium can be any available medium that can be accessed by a general or special computer.

[0144] An exemplary storage medium is coupled to a processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in an application specific integrated circuit (ASIC). Of course, the processor and the storage medium can also exist as discrete components in an electronic device or a main control device.

[0145] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.

[0146] It should be further noted that, although the various steps in the flowchart are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless there is a clear description in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a portion of the steps in the flowchart may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these sub-steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.

[0147] In the above embodiments, the description of each embodiment has its own emphasis. For the part not described in detail in a certain embodiment, please refer to the relevant description of other embodiments. The technical features of the above embodiments can be combined arbitrarily. In order to make the description concise, all possible combinations of the technical features in the above embodiments are not described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0148] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the invention disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary techniques in the art that are not disclosed in the present application. The specification and examples are intended to be exemplary only, and the true scope and spirit of the present application are indicated by the claims.

[0149] The above is only a specific implementation of the embodiment of the present application, but the protection scope of the embodiment of the present application is not limited thereto, and any changes or replacements within the technical scope disclosed in the embodiment of the present application should be included in the protection scope of the embodiment of the present application. Therefore, the protection scope of the embodiment of the present application should be based on the protection scope of the claims.

Claims

1. A method for optimizing database operation statements, characterized in that: The method comprises: Obtaining a database operation statement, and determining whether the database operation statement includes a first statement; the first statement includes a keyword statement and / or a grouping statement for deduplication; If so, after determining that the database operation statement meets the preset conditions, the first statement is replaced by the second statement; the preset conditions include that the corresponding target column is fixed by a constant value, the database operation statement does not contain an aggregate function and a window function, and there is no filtering condition after grouping; the second statement is used to return the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2; A target operation statement is generated based on the second statement conversion.

2. The method according to claim 1, characterized in that The keyword statement is used to remove duplicate rows in the target column; the group statement is used to group the data in the table according to the specified column, and each group contains rows with the same value; After determining that the database operation statement meets a preset condition, replacing the first statement with a second statement includes: When the first statement is a keyword statement, determining whether the target column corresponding to the database operation statement is a constant value, and whether the database operation statement does not contain an aggregate function and a window function; If so, replace the first statement with the second statement; If not, then collect the constant conditions connected in the database operation statement, and after determining that the constant conditions are used to perform indirect constant fixation on the target column and the database operation statement does not contain aggregate functions and window functions, replace the first statement with the second statement.

3. The method according to claim 2, characterized in that After determining that the database operation statement meets a preset condition, replacing the first statement with a second statement includes: When the first statement is a grouping statement, determine whether the target column corresponding to the database operation statement is a constant value, and whether the database operation statement does not contain an aggregate function and a window function and does not have a screening condition after grouping; If so, replace the first statement with the second statement; If not, collect the constant conditions connected in the database operation statement, and after determining that the constant conditions are used to indirectly fix the target column, and the database operation statement does not contain aggregate functions and window functions and does not have post-grouping screening conditions, replace the first statement with the second statement.

4. The method according to claim 1, characterized in that: The second statement is a limit 1 statement; the screening condition after non-existence of grouping is a screening condition without having clause restrictions.

5. The method according to claim 1, characterized in that: The database operation statements include query statements, add statements, delete statements, and update statements.

6. The method according to any one of claims 1 to 5, characterized in that: The method further comprises: Executing the target operation statement and the database operation statement respectively, and obtaining a first result set and a second result set after the execution; Comparing the first result set with the second result set to verify whether the target operation statement is correct; When the target operation statement is correct, obtaining a first processing rate for executing the first result set and a second processing rate for executing the second result set; When the first processing rate is less than or equal to the second processing rate, prompt information is generated to prompt the developer to modify the target operation statement.

7. A database operation statement optimization device, characterized in that: The device comprises: A judgment module, used for obtaining a database operation statement, and judging whether the database operation statement includes a first statement; the first statement includes a keyword statement and / or a grouping statement for deduplication; A replacement module, used for, when the database operation statement includes a first statement, replacing the first statement with a second statement after determining that the database operation statement satisfies a preset condition; the preset condition includes that the corresponding target column is fixed by a constant value, the database operation statement does not include an aggregate function and a window function, and there is no post-grouping screening condition; the second statement is used for returning the duplicate results to achieve deduplication when the number of duplicate results in the determined list is equal to 2; A generating module is used to generate a target operation statement based on the second statement conversion.

8. An electronic device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the method according to any one of claims 1 to 6.

10. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 6 when being executed by a processor.

Images