Database operation statement optimization method and related product

Abstract

The invention provides a database operation statement optimization method and a related product. The method comprises the following steps: acquiring a database operation statement; judging whether the database operation statement contains a deduplication property and an aggregation function or not; if yes, obtaining an aggregation function, and determining a calculation type of the aggregation function; and under the condition that the reference content in the aggregation function is a constant, performing corresponding replacement operation on the database operation statement according to the calculation type so as to delete the aggregation function. Through the method, the occurrence of Aggregate execution nodes can be reduced by deleting the aggregated functions meeting the conditions, so that the aggregated functions in the database operation statements meeting the conditions are optimized, and the query efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method for optimizing database operation statements and related products. Background Art

[0002] In the current common database technology, the Aggregate execution node needs to be called when processing an aggregate function. However, in certain specific cases, the database operation statement does not need to call the Aggregate execution node to process the aggregate function and can also obtain the same result. If the current common database technology is used, it will affect the execution efficiency of the database for the database operation statement. Summary of the invention

[0003] In view of the above problems, the present invention proposes a method for optimizing database operation statements and related products that overcome the above problems or at least partially solve the above problems.

[0004] An object of the present invention is to optimize aggregate functions in qualified database operation statements.

[0005] A further object of the present invention is to reduce the occurrence of Aggregate execution nodes by deleting eligible aggregation functions.

[0006] In particular, the present invention provides a method for optimizing database operation statements, comprising:

[0007] Get database operation statements;

[0008] Determine whether the database operation statement contains deduplication properties and aggregation functions;

[0009] If so, obtain the aggregate function and determine the calculation type of the aggregate function;

[0010] When the reference content in the aggregate function is a constant, the database operation statement is replaced accordingly according to the calculation type, thereby deleting the aggregate function.

[0011] Optionally, the calculation type includes count and sum;

[0012] The steps of performing corresponding replacement operations on database operation statements according to the calculation type include:

[0013] When the calculation type shows that the aggregation function of the database operation statement is a counting or summing-related function, determine whether the aggregation function is deduplicated using the target deduplication keywords, which include distinct and unique.

[0014] If so, perform corresponding replacement operations on the aggregate function in the database operation statement.

[0015] Optionally, the step of performing corresponding replacement operations on the aggregate function in the database operation statement includes:

[0016] Determine whether the reference content in the aggregate function is a constant directly or indirectly;

[0017] When the reference content is directly a constant, convert the reference content into a constant and replace the aggregation function;

[0018] When the reference content is indirectly a constant, the reference content is converted into a constant according to the conditions in the data source of the database operation statement, and the aggregate function is replaced.

[0019] Optionally, the step of performing corresponding replacement operations on the database operation statement according to the calculation type further includes:

[0020] When the calculation type shows that the aggregate function of the database operation statement is not count or sum, determine whether the reference content in the aggregate function is a constant directly or indirectly;

[0021] When the reference content is directly a constant, convert the reference content into a constant and replace the aggregation function;

[0022] When the reference content is indirectly a constant, the reference content is converted into a constant according to the conditions in the data source of the database operation statement, and the aggregate function is replaced.

[0023] Optionally, the step of determining whether the database operation statement contains an aggregate function includes:

[0024] Parse the database operation statement and obtain the parsing result;

[0025] Determine whether the flag of the aggregation function in the parsing result is true;

[0026] When the flag of the aggregate function is true, it is determined that the database operation statement contains the aggregate function.

[0027] Optionally, after the step of performing corresponding replacement operation on the database operation statement according to the calculation type, the following step is further included:

[0028] Determine whether there are other aggregate functions in the database operation statement;

[0029] If not, the flag bit is reassigned based on whether there is still an aggregate function in the database operation statement.

[0030] Optionally, the step of determining whether the database operation statement contains a deduplication property includes:

[0031] Parse the database operation statement and obtain the parsing result;

[0032] Determine whether the database operation statement contains a deduplication keyword or whether a target column of the database operation statement has a deduplication constraint based on the analysis result;

[0033] When the database operation statement includes a deduplication keyword or a target column of the database operation statement has a deduplication constraint, it is determined that the database operation statement includes a deduplication property.

[0034] According to another aspect of the present invention, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned methods for optimizing database operation statements are implemented.

[0035] According to another aspect of the present invention, a computer program product is provided, which includes a computer program, and when the computer program is executed by a processor, the steps of any one of the above-mentioned methods for optimizing database operation statements are implemented.

[0036] According to another aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements the steps of any of the above-mentioned database operation statement optimization methods when executing the machine executable program.

[0037] The optimization method of database operation statements of the present invention first obtains database operation statements; then determines whether the database operation statements contain deduplication properties and aggregation functions; if so, obtains the aggregation function and determines the calculation type of the aggregation function; then, when the reference content in the aggregation function is a constant, performs corresponding replacement operations on the database operation statement according to the calculation type, thereby deleting the aggregation function. Through this method, the appearance of Aggregate execution nodes can be reduced by deleting the aggregation functions that meet the conditions, thereby optimizing the aggregation functions in the database operation statements that meet the conditions and improving the query efficiency.

[0038] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0040] Figure 1 is a flowchart of a method for optimizing database operation statements according to an embodiment of the present invention;

[0041] Figure 2 is a flow chart of a method for optimizing database operation statements according to another embodiment of the present invention;

[0042] Figure 3 is a schematic diagram of a computer program product according to an embodiment of the present invention;

[0043] Figure 4 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0044] Figure 5 is a schematic diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0045] It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, rather than all embodiments of the present invention, and these embodiments are intended to explain the technical principles of the present invention, rather than to limit the protection scope of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should still fall within the protection scope of the present invention.

[0046] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0047] The present invention provides a method for optimizing database operation statements. Figure 1 FIG. 1 is a flow chart of a method for optimizing database operation statements according to an embodiment of the present invention. Figure 1 As shown, the method for optimizing database operation statements at least includes the following steps S101 to S104.

[0048] Step S101, obtaining database operation statements. An optional example is SQL (Structured Query Language). SQL statements have multiple functions such as data manipulation and data definition. This language has interactive characteristics and can provide great convenience for users. In this step, the way to receive database operation statements can generally be active input by the user, or automatically generated by the database program during operation due to business needs. Those skilled in the art can decide the way to receive database operation statements according to actual conditions.

[0049] Step S102, determine whether the database operation statement contains deduplication properties and aggregate functions. In SQL, deduplication properties generally refer to removing duplicate rows from query results. There are several common ways to achieve this, such as using DISTINCT, GROUP BY, UNION, and UNIQUE constraints. Aggregate functions in SQL are a type of function used to calculate a set of values ​​and return a single result. These functions are usually used with SELECT statements. Common aggregate functions generally include: COUNT(): calculate the number of rows, SUM(): calculate the sum of numeric columns, AVG(): calculate the average value of numeric columns, MAX(): return the maximum value in a column, MIN(): return the minimum value in a column, etc.

[0050] In some optional embodiments, the step of judging whether a database operation statement contains a deduplication property may generally include: parsing the database operation statement to obtain a parsing result; judging whether the database operation statement contains a deduplication keyword or whether a target column of the database operation statement has a deduplication constraint based on the parsing result; and judging whether the database operation statement contains a deduplication keyword or a target column of the database operation statement has a deduplication constraint when the database operation statement contains a deduplication keyword or a target column of the database operation statement has a deduplication constraint. Deduplication keywords generally include keywords such as DISTINCT, GROUP BY, and UNION. Such deduplication keywords are usually used directly in SQL statements, so it can be determined by parsing whether such deduplication keywords exist, thereby determining whether the database operation statement contains a deduplication property. Keywords such as UNIQUE are usually used to operate on data tables and are generally not used in SELECT statements. Therefore, when the database operation statement in this embodiment is a SELECT statement, it is necessary to judge whether its target column has a deduplication constraint to determine whether there is a deduplication property.

[0051] Optionally, the step of determining whether a database operation statement contains an aggregate function may generally include: parsing the database operation statement to obtain a parsing result; determining whether a flag of the aggregate function in the parsing result is true; and determining that the database operation statement contains an aggregate function when the flag of the aggregate function is true. Typically in SQL queries, aggregate functions are typically used to calculate a set of values ​​and return a single result. The "flag" of an aggregate function typically refers to whether these aggregate functions are used during query execution. This flag can help understand whether the query contains an aggregate operation that requires special processing, thereby determining whether the database operation statement contains an aggregate function.

[0052] Step S103, if the judgment in step S102 is yes, obtain the aggregation function and determine the calculation type of the aggregation function. In some optional embodiments, the aggregation function may generally include SUM, COUNT, AVG, MAX, MIN, etc., and their respective functions or calculation types are different. For example, SUM is generally used to calculate the sum of the numerical column, and its calculation type is summation; COUNT is generally used to calculate the number of rows, and its calculation type is count; AVG is generally used to calculate the average value of the numerical column, and its calculation type is average value. Different aggregation functions correspond to different calculation types.

[0053] Step S104, when the reference content in the aggregate function is a constant, the database operation statement is replaced accordingly according to the calculation type, thereby deleting the aggregate function. In some optional embodiments, when executing SQL, if there is a function, the function has no input parameters, and the function is stable, the optimizer will generally optimize it to a constant, and similar situations will occur in the aggregate function of the SQL statement, and each execution of the aggregate function requires calling the Aggregate execution node for processing, so when the reference content of the aggregate function is a constant, consider optimizing it, thereby replacing the aggregate function, thereby reducing the appearance of the Aggregate execution node.

[0054] Through this method, the appearance of Aggregate execution nodes can be reduced by deleting qualified aggregate functions, thereby optimizing the aggregate functions in qualified database operation statements and improving query efficiency.

[0055] In some optional embodiments, after performing the corresponding replacement operation on the database operation statement according to the calculation type, the following steps may be generally included: determining whether there are other aggregate functions in the database operation statement; if not, reassigning the flag bit according to whether there are still aggregate functions in the database operation statement. When all aggregate functions in the SQL statement are replaced with constants, the flag bit corresponding to the aggregate function can be reassigned to false, so that it is clear during the database execution process that the modified SQL statement has no aggregate function, thereby avoiding the appearance of the Aggregate execution node; if there is an aggregate function that cannot be replaced, the flag bit still needs to be kept true.

[0056] Optionally, the calculation type may generally include counting and summing. The step of performing corresponding replacement operations on the database operation statement according to the calculation type may generally include: when the calculation type shows that the aggregation function of the database operation statement is a counting or summing-related function, judging whether the aggregation function is deduplicated through the target deduplication keyword, the target deduplication keywords include: distinct, unique; if so, performing corresponding replacement operations on the aggregation function in the database operation statement. It should be noted that the reason why it is necessary to judge whether the aggregation function is deduplicated through the target deduplication keyword when the calculation type shows that the aggregation function of the database operation statement is a counting or summing-related function is that in the face of other types of deduplication keywords, even if the reference content of the aggregation function is a constant, its result will change. For example, "selectsum(id)from t1 where id=1group by age", in this SQL statement, the aggregation function is sum(id), and id=1, but when the deduplication keyword is group by, group by age groups the results by the age column, and the final result will change according to different data. Assume that the data of the t1 table is as shown in Table 1:

[0057] Table 1

[0058] id age 1 20 1 20 1 30 1 40

[0059] The results after executing the above query are shown in Table 2:

[0060] Table 2

[0061] sum(id) age 2 20 1 30 1 40

[0062] For age=20, there are two records, and the id of each record is 1, so sum(id)=1+1=2.

[0063] For age=25 and age=30, there is one record each, and the id of each record is 1, so SUM(id)=1.

[0064] Therefore, to avoid such situations, when the calculation type shows that the aggregation function of the database operation statement is a counting or summing-related function, it is necessary to determine whether the aggregation function is deduplicated through target deduplication keywords such as distinct or unique. If so, you can continue with subsequent operations.

[0065] Optionally, the step of performing corresponding replacement operations on the aggregate function in the database operation statement may generally include: determining whether the reference content in the aggregate function is a constant directly or indirectly; in the case where the reference content is a constant directly, converting the reference content into a constant and replacing the aggregate function; in the case where the reference content is an indirect constant, converting the reference content into a constant according to the conditions in the database operation statement data source, and replacing the aggregate function. In order to clearly illustrate the processing flow of this method, an optional example is proposed as shown in Formula 1:

[0066] select distinct sum(distinct id) from t1 where id = 2 Formula 1

[0067] In the SQL statement shown in Formula 1:

[0068] The condition where id=2 filters out all records in table t1 whose id is 2. Since id has been restricted to 2, the id value in the records that meet the condition is 2;

[0069] sum(distinct id) is an aggregate function that sums the unique values ​​in the id column. Since id has been restricted to 2 by the WHERE clause, the unique value of id is 2;

[0070] distinct in a SELECT statement: The distinct keyword is used to remove duplicate rows from the result set. In this query, sum(distinct id) will only return a single value (i.e. 2), which means that no matter how many records in table t1 have id 2, the result of the query will always be 2.

[0071] When the SQL statement shown in Formula 1 is obtained, the optimization method of the present invention first determines whether it contains deduplication properties and aggregation functions. Since the deduplication keyword distinct and the aggregation function sum exist in its target column "distinct sum (distinct id)", it can be determined that the SQL statement described in Formula 1 contains deduplication properties and aggregation functions; then, since its aggregation function is only sum, sum is a function related to summation, distinct belongs to the target deduplication keyword, and the reference content of sum is "distinct id", and the condition in the data source of Formula 1 (that is, the content after the from keyword) includes "id=2", it can be determined that the reference content in the aggregation function is indirectly a constant, so the replacement operation of the aggregation function can be performed. In the case where the reference content is indirectly a constant, the reference content is converted into a constant according to the condition in the data source of the database operation statement, and the aggregation function is replaced, that is, 2 is used to replace the aggregation function. The final optimized SQL statement is shown in Formula 2:

[0072] Select distinct 2 from t1 where id = 2 Equation 2

[0073] As shown in Formula 2, there is no aggregation function in the SQL statement, and the appearance of the Aggregate execution node is avoided during the execution process, thereby greatly improving the query efficiency.

[0074] As shown in Formula 1, the aggregate function reference content of the SQL statement is indirectly a constant. In some other optional embodiments, an example in which the aggregate function reference content is directly a constant is shown in Formula 3:

[0075] select distinct count(distinct 2)from t1 formula 3

[0076] In the SQL statement shown in Formula 3:

[0077] count(distinct 2) is an aggregate function that counts the number of unique values ​​2. Since 2 is a constant value, no matter how many rows there are in the t1 table, distinct 2 always has only one unique value 2. Therefore, the result of count(distinct 2) is always 1.

[0078] In the SELECT statement: The distinct keyword is used to remove duplicate rows in the result set. Since the result of count(distinct 2) is always 1, distinct is actually redundant here.

[0079] When the SQL statement shown in Formula 3 is obtained, the optimization method of the present invention first determines whether it contains deduplication properties and aggregation functions. Since the deduplication keyword distinct and the aggregation function count exist in its target column "distinct count (distinct 2)", it can be determined that the SQL statement described in Formula 1 contains deduplication properties and aggregation functions; then, since its aggregation function only has count, count is a counting-related function, distinct belongs to the target deduplication keyword, and the reference content of count is "2", it can be determined that the reference content in the aggregation function is directly a constant, so the replacement operation of the aggregation function can be performed. In the case where the reference content is directly a constant, the reference content is converted into a constant and the aggregation function is replaced, and the result of count (distinct 2) is 1, that is, 1 is used to replace the aggregation function. The final optimized SQL statement is shown in Formula 4:

[0080] Select distinct 1from t1Formula 4

[0081] As shown in Formula 4, there is no aggregation function in the SQL statement, and the appearance of the Aggregate execution node is avoided during the execution process, thereby greatly improving the query efficiency.

[0082] In some other optional embodiments, the step of performing corresponding replacement operations on the database operation statement according to the calculation type also includes: when the calculation type shows that the aggregation function of the database operation statement is not counting or summing, judging whether the reference content in the aggregation function is a constant directly or indirectly; when the reference content is a constant directly, converting the reference content into a constant and replacing the aggregation function; when the reference content is a constant indirectly, converting the reference content into a constant according to the conditions in the database operation statement data source, and replacing the aggregation function. In order to clearly illustrate the processing flow of this method, an optional example is proposed as shown in Formula 5:

[0083] select distinct min(id) from t1 where id = 1 Equation 5

[0084] In the SQL statement shown in Formula 5:

[0085] where id=1: This condition filters out all records in table t1 whose id is 1. Since id has been restricted to 1, the id values ​​in the records that meet the condition are all 1.

[0086] min(id) is an aggregate function that returns the minimum value in the id column. Since id has been restricted to 1 by the where clause, the minimum value of id is 1.

[0087] In the select statement: The distinct keyword is used to remove duplicate rows from the result set. Since min(id) will only return a single value (i.e. 1), distinct is actually redundant here.

[0088] When the SQL statement shown in Formula 5 is obtained, the optimization method of the present invention first determines whether it contains deduplication properties and aggregation functions. Since the deduplication keyword distinct and the aggregation function min exist in its target column "distinct min(id)", it can be determined that the SQL statement described in Formula 5 contains deduplication properties and aggregation functions; then, since its aggregation function is only min, min is not a counting or summing-related function, and the reference content of min is "id", and the condition in the data source of Formula 5 (that is, the content after the from keyword) includes "id=1", it can be determined that the reference content in the aggregation function is indirectly a constant, so the replacement operation of the aggregation function can be performed. In the case where the reference content is indirectly a constant, the reference content is converted into a constant according to the condition in the data source of the database operation statement, and the aggregation function is replaced, that is, the aggregation function is replaced with 1. The final optimized SQL statement is shown in Formula 6 or Formula 7:

[0089] select distinct 1 from t1 where id = 1 Formula 6

[0090] select distinct id from t1 where id = 1 Equation 7

[0091] As shown in Formula 6 or Formula 7, there is no aggregation function in the SQL statement, and the appearance of the Aggregate execution node is avoided during the execution process, thereby greatly improving the query efficiency.

[0092] As shown in Formula 5, the aggregate function reference content of the SQL statement is indirectly a constant. In some other optional embodiments, an example in which the aggregate function reference content is directly a constant is shown in Formula 8:

[0093] select distinct avg(2) from t1 where id = 1 Equation 8

[0094] In the SQL statement shown in Formula 8:

[0095] The condition where id=1 filters out all records in table t1 whose id is 1. Since id has been restricted to 1, the id values ​​in the records that meet the condition are all 1.

[0096] avg(2) is an aggregate function that computes the average of the constant value 2. · No matter how many rows meet the criteria, the average of 2 is always 2 because 2 is a constant value.

[0097] In the SELECT statement: The distinct keyword is used to remove duplicate rows in the result set.

[0098] When the SQL statement shown in Formula 8 is obtained, the optimization method of the present invention first determines whether it contains deduplication properties and aggregation functions. Since the deduplication keyword distinct and the aggregation function avg exist in the target column "distinct avg(2)", it can be determined that the SQL statement described in Formula 8 contains deduplication properties and aggregation functions; then, since its aggregation function is only avg, avg is not a counting or summing related function, and the reference content of avg is "2", it can be determined that the reference content in the aggregation function is directly a constant, so the replacement operation of the aggregation function can be performed. In the case where the reference content is directly a constant, the reference content is converted into a constant and the aggregation function is replaced, that is, 2 is used to replace the aggregation function. The final optimized SQL statement is shown in Formula 9:

[0099] select distinct 2 from t1 where id = 1 Equation 9

[0100] As shown in Formula 9, there is no aggregation function in the SQL statement, and the appearance of the Aggregate execution node is avoided during the execution process, thereby greatly improving the query efficiency.

[0101] In addition, it should be noted that when the deduplication keyword of the SQL statement is group by, the replacement operation in this method can only be performed if the column that requires the aggregate function does not appear in the group by.

[0102] Figure 2 FIG. 1 is a flow chart of a method for optimizing database operation statements according to another embodiment of the present invention. Figure 2 As shown, the method for optimizing database operation statements at least includes the following steps S201 to S212.

[0103] Step S201, obtaining a database operation statement.

[0104] Step S202, determine whether the database operation statement contains a deduplication property and the flag of the aggregation function is true. If not, do not perform subsequent operations.

[0105] Step S203: if the answer in step S202 is yes, determine whether the aggregation function is a counting or summing related function.

[0106] Step S204, when the judgment in step S203 is yes, it is determined whether the aggregation function is used to remove duplicates through the target deduplication keyword.

[0107] Step S205: If the judgment in step S204 is yes, it is determined whether the reference content in the aggregate function is a constant directly or indirectly.

[0108] Step S206, when it is determined to be direct in step S205, the reference content is converted into a constant and the aggregate function is replaced.

[0109] Step S207, when it is determined to be indirect in step S205, the reference content is converted into a constant according to the conditions in the database operation statement data source, and the aggregation function is replaced.

[0110] Step S208: If the result of the determination in step S203 is no, it is determined whether the reference content in the aggregate function is a constant directly or indirectly.

[0111] Step S209, when it is determined to be direct in step S208, the reference content is converted into a constant and the aggregate function is replaced.

[0112] Step S210, when it is determined to be indirect in step S208, the reference content is converted into a constant according to the conditions in the database operation statement data source, and the aggregation function is replaced.

[0113] Step S211, if the judgment in step S204 is no or any of steps S206, S207, S209 and S210 is completed, it is determined whether there are other aggregation functions in the database operation statement. If so, the next aggregation function is determined by step 203.

[0114] Step S212, when the judgment in step S211 is no, reassign the flag bit according to whether there is still an aggregate function in the database operation statement.

[0115] Through this method, the appearance of Aggregate execution nodes can be reduced by deleting qualified aggregate functions, thereby optimizing the aggregate functions in qualified database operation statements and improving query efficiency.

[0116] In order to clearly illustrate the processing flow of this method, an optional example is proposed as shown in Formula 10:

[0117] select min(id) from t1 where id = 1 group by age Equation 10

[0118] In the SQL statement shown in Formula 10:

[0119] The condition where id=1 filters out all records in table t1 whose id is 1. Since id has been restricted to 1, the id values ​​in the records that meet the condition are all 1.

[0120] min(id) is an aggregate function that returns the minimum value in the id column. Since id has been restricted to 1 by the where clause, the minimum value of id is 1.

[0121] group by age groups the result set by the age column. Each distinct age value generates a separate group, and min(id) is calculated for each group.

[0122] When the SQL statement shown in Formula 10 is obtained, the optimization method of the present invention first determines whether it contains deduplication properties and aggregation functions. Since the deduplication keyword distinct and the aggregation function min exist in its target column "min(id)", it can be determined that the SQL statement shown in Formula 10 contains deduplication properties and aggregation functions; then, since its aggregation function is only min, min is not a counting or summing-related function, and the reference content of min is "id", and the condition in the data source of Formula 10 (that is, the content after the from keyword) includes "id=1", it can be determined that the reference content in the aggregation function is indirectly a constant, and there is a group by in the statement, and the column in the aggregation function does not appear in the group by, so the aggregation function can be replaced. In the case where the reference content is indirectly a constant, the reference content is converted into a constant according to the condition in the data source of the database operation statement, and the aggregation function is replaced, that is, the aggregation function is replaced with 1. The final optimized SQL statement is shown in Formula 11:

[0123] select 1 from t1 where id = 1 group by age Equation 11

[0124] As shown in Formula 11, there is no aggregation function in the SQL statement, and the appearance of the Aggregate execution node is avoided during the execution process, thereby greatly improving the query efficiency.

[0125] It should be noted that the constants in the present invention are not just numbers, but refer to values ​​that will not change during the query process. Those skilled in the art can set the range of the constants according to actual conditions. To illustrate the range of the constants, an optional example is proposed as shown in Formula 12:

[0126] select distinct max(t1.name)from t1 join t2 on t1.name=t2.name wheret2.name='abc' Equation 12

[0127] In the SQL statement shown in Formula 12:

[0128] The condition t1 join t2 on t1.name=t2.name joins the t1 table and the t2 table. The join condition is that t1.name is equal to t2.name.

[0129] The condition where t2.name='abc' further filters out the records in table t2 where name is 'abc'. Due to the existence of the join condition, this also means that name in table t1 must also be 'abc'.

[0130] max(t1.name) is an aggregate function that returns the maximum value in the t1.name column. Since the name field is usually a string type, the max function returns the largest string in lexicographic order. distinct, in a SELECT statement: The distinct keyword is used to remove duplicate rows in the result set.

[0131] When the SQL statement shown in Formula 12 is obtained, the optimization method of the present invention first determines whether it contains deduplication properties and aggregation functions. Since the deduplication keyword distinct and the aggregation function max exist in its target column "distinct max(t1.name)", it can be determined that the SQL statement shown in Formula 12 contains deduplication properties and aggregation functions; then, since its aggregation function is only max, max is not a counting or summing-related function, and the reference content of max is "t1.name", and the conditions in the data source of Formula 12 (that is, the content after the from keyword) include "t1 join t2 on t1.name=t2.name where t2.name='abc'", that is, t1.name='abc', it can be determined that the reference content in the aggregation function is indirectly a constant, so the replacement operation of the aggregation function can be performed. In the case where the reference content is indirectly a constant, the reference content is converted into a constant according to the conditions in the database operation statement data source, and the aggregation function is replaced, that is, 1 is used to replace the aggregation function. The final optimized SQL statement is shown in Formula 13 or Formula 14:

[0132] select distinct'abc'from t1 join t2 on t1.name=t2.name where t2.name=

[0133] 'abc' formula 13

[0134] In addition, the final optimized SQL statement can also be shown as formula 13:

[0135] select distinct t1.name from t1 join t2 on t1.name=t2.name wheret2.name='abc'Equation 14

[0136] As shown in Formula 13 or Formula 14, there is no aggregation function in the SQL statement, and the appearance of the Aggregate execution node is avoided during the execution process, thereby greatly improving the query efficiency.

[0137] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes may be made to the above method.

[0138] It should be understood that in some embodiments, each part can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0139] This embodiment also provides a computer program product 10 , a computer readable storage medium 20 , and a computer device 30 . Figure 3 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 4 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Figure 5 is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which implements the steps of any of the above-mentioned methods for optimizing database operation statements when executed by a processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11, which implements the steps of any of the above-mentioned methods for optimizing database operation statements when executed by a processor 32. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0140] The computer program 11 for performing the operation of the present invention may be an assembly instruction, an instruction set architecture (ISA) instruction, a machine instruction, a machine-related instruction, a microcode, a firmware instruction, a state setting data, a configuration data of an integrated circuit, or a source code or an object code written in any combination of one or more programming languages ​​and process programming languages. The computer program 11 may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network (including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., using an Internet service provider via the Internet). In some embodiments, in order to perform various aspects of the present invention, an electronic circuit including, for example, a programmable logic circuit, a field programmable gate array (FPGA) or a programmable logic array (PLA) may execute computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit.

[0141] For the purposes of the description of the present embodiment, the computer program product 10 is a related product that includes the computer program 11. For the purposes of the description of the present embodiment, the computer readable storage medium 20 is a tangible device capable of retaining and storing the computer program 11, which may be any device that can contain, store, communicate, propagate or transmit the program 11 for use with or in conjunction with an instruction execution system, device or apparatus. More specific examples (a non-exhaustive list) of the computer readable storage medium 20 include the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, and any suitable combination of the foregoing.

[0142] The computer device 30 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 30 may be a cloud computing node. The computer device 30 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, target programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 30 may be implemented in a distributed cloud computing environment where remote processing devices linked via a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0143] The computer device 30 may include a processor 32 adapted to execute stored instructions, and a memory 31 providing temporary storage space for the operation of the instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 31 may include a random access memory (RAM), a read-only memory, a flash memory, or any other suitable storage system.

[0144] The computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows data to be input and output with external devices that may be connected to the computer device. The network adapter / interface may provide communication between the computer device and a network, which is typically shown as a communication network.

[0145] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A method for optimizing database operation statements, comprising: Obtaining the database operation statement; Determine whether the database operation statement contains deduplication properties and aggregation functions; If yes, then obtaining the aggregation function and determining the calculation type of the aggregation function; In the case where the reference content in the aggregate function is a constant, a corresponding replacement operation is performed on the database operation statement according to the calculation type, thereby deleting the aggregate function.

2. The method for optimizing database operation statements according to claim 1, wherein: The calculation types include counting and summing; The step of performing a corresponding replacement operation on the database operation statement according to the calculation type comprises: When the calculation type shows that the aggregation function of the database operation statement is a counting or summing-related function, determining whether the aggregation function is deduplicated using a target deduplication keyword, wherein the target deduplication keyword includes distinct and unique. If so, a corresponding replacement operation is performed on the aggregate function in the database operation statement.

3. The method for optimizing database operation statements according to claim 2, wherein: The step of performing corresponding replacement operation on the aggregate function in the database operation statement comprises: Determine whether the reference content in the aggregate function is directly or indirectly the constant; In the case where the reference content is directly the constant, converting the reference content into the constant and replacing the aggregation function; In the case where the reference content is indirectly the constant, the reference content is converted into the constant according to the condition in the database operation statement data source, and the aggregate function is replaced.

4. The method for optimizing database operation statements according to claim 2, wherein: The step of performing a corresponding replacement operation on the database operation statement according to the calculation type further includes: When the calculation type shows that the aggregate function of the database operation statement is not counting or summing, determining whether the reference content in the aggregate function is directly or indirectly the constant; In the case where the reference content is directly the constant, converting the reference content into the constant and replacing the aggregation function; In the case where the reference content is indirectly the constant, the reference content is converted into the constant according to the condition in the database operation statement data source, and the aggregate function is replaced.

5. The method for optimizing database operation statements according to claim 1, wherein: The step of determining whether the database operation statement contains an aggregate function comprises: Parsing the database operation statement to obtain a parsing result; Determine whether the flag bit of the aggregation function in the parsing result is true; When the flag bit of the aggregate function is true, it is determined that the database operation statement includes the aggregate function.

6. The method for optimizing database operation statements according to claim 5, wherein: After the step of performing a corresponding replacement operation on the database operation statement according to the calculation type, the following step further includes: Determine whether there are other aggregate functions in the database operation statement; If not, the flag bit is reassigned according to whether the aggregate function still exists in the database operation statement.

7. The method for optimizing database operation statements according to claim 1, wherein: The step of determining whether the database operation statement contains a deduplication property comprises: Parsing the database operation statement to obtain a parsing result; Determine, according to the analysis result, whether the database operation statement contains a deduplication keyword or whether a target column of the database operation statement has a deduplication constraint; In the case that the database operation statement includes the deduplication keyword or the target column of the database operation statement has the deduplication constraint, it is determined that the database operation statement includes the deduplication property.

8. A computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the computer program implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 7.

9. A computer program product, comprising a computer program, which, when executed by a processor, implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 7.

10. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 7 when executing the machine executable program.

Images