A SQL optimization processing method based on a relational database

Abstract

The application relates to a SQL optimization processing method based on a relational database. The method comprises the following steps: marking whether a state transition function of an aggregation function is a monotone function in a metadata table of a relational database system; pushing a filtering condition of the aggregation function into the state transition function when a query plan is generated; executing the state transition function of the aggregation function on data when the query plan is executed; substituting a result into the filtering condition; and directly processing next grouped data if the filtering condition is not satisfied. Compared with an existing processing mode, the filtering condition of the aggregation function is pushed into the state transition function of the aggregation function, records not satisfying output requirements can be found early, and the SQL execution process is accelerated. The execution time of some SQL of a database can be greatly shortened through the method, and application of the method can help to better exert the performance of the database and improve the working efficiency of the database.

Description

Technical Field

[0001] This invention belongs to the technical field of relational database optimization methods, and particularly relates to an SQL optimization method and system based on relational databases. Background Technology

[0002] In a typical relational database management system, processing a query usually involves the following three stages:

[0003] (1) Lexical and syntactic analysis mainly transforms the user's input text (SQL) into an internal data structure, generally called a syntax parse tree, and verifies the correctness of the syntax, ultimately obtaining a syntax parse tree representing SQL.

[0004] (2) Query optimization: The syntax parse tree obtained in the previous stage is optimized based on rules and physical cost to generate an optimal query plan.

[0005] (3) Execute the query plan, which is generated in the previous stage (usually using an iterator) to obtain the query results and return them to the user. Summary of the Invention

[0006] Currently, relational databases handle queries (SQL) with aggregate functions and filtered results, such as the following SQL: `SELECT sum(grade), name from user group by name having sum(grade) < 100`. The `user` table has two columns: `name` (varchar) and `grade` (int) which are integers greater than 0. The above SQL groups the data in the `user` table by `name`, calculates the sum of the `grade` values ​​corresponding to each `name` (the result of the `sum` aggregate function), and ultimately keeps the results where the total `grade` value is less than 100.

[0007] Generally, in a relational database, the above SQL will go through the three processing stages mentioned above before finally outputting the result. In the third stage, the query plan execution stage, the processing typically follows these steps:

[0008] 1) Scan data from the user table. If the table is empty, the operation is complete and the output result set is empty.

[0009] 2) Group the data scanned from the user table according to the name field. The grouping method can be hash or sorting, depending on the grouping method specified by the query plan generated in the second stage.

[0010] 3) For each group generated in the previous step, proceed to the next step.

[0011] 4) For each data entry in the current group, extract the grade field and store its value in the intermediate result. After processing all data in the group, proceed to the next step.

[0012] 5) Use the accumulated intermediate results as the final result of the sum aggregation function.

[0013] 6) If the result of the aggregate function is less than 100, output the name and the result of the aggregate function as a record in the final result set returned to the user; if the result of the aggregate function is greater than or equal to 100, it means that the requirements are not met and it needs to be filtered out; return to step 4).

[0014] 7) If there is another group, return to step 4); otherwise, it means that all groups have been traversed, the entire execution process is complete, and after returning the user data, exit directly.

[0015] The above seven steps are typical processing steps for SQL statements with aggregate functions and filtered results in relational databases. In abstract terms, they involve completing the following seven processing steps:

[0016] 1) Obtain the input to the aggregate function. The input can be records scanned from a table or an intermediate result set.

[0017] 2) Iterate through the inputs of the aggregate function and group the inputs according to the method specified in the query plan.

[0018] 3) For each group, proceed to the next step.

[0019] 4) For each data point in the current group, execute the state transition function of the aggregation function, and save the result of the state transition function as an intermediate result.

[0020] 5) The intermediate results are taken as input and passed to the final state function of the aggregation function. The result of the final state function is used as the final aggregation function result of the group.

[0021] 6) Substitute the aggregation function result into the aggregation function's filtering condition, and determine whether the filtering condition is met. If it is met, output the calculation result of the group; otherwise, discard the calculation result of the group.

[0022] 7) If a next group exists, jump back to step 4); otherwise, it means that all groups have been processed and the query plan execution ends.

[0023] However, it's easy to see that the above execution flow doesn't consider the characteristics of aggregation functions and the characteristics of the input data for aggregation functions, resulting in low processing efficiency in some cases. We believe that for functions like `sum`, within each group, the ultimate goal is to sum the input parameters to obtain the final result. If the data itself is greater than 0, then the result of its state transition function will monotonically increase. Therefore, we can promptly determine whether the filtering conditions are met by checking the result of its state transition function. If not, we can skip the remaining records in that group and directly process the data in the next group. In this way, by promptly identifying groups that do not meet the filtering conditions, the query processing time can be significantly shortened. Based on the above considerations, this invention, based on the characteristics of SQL itself and the input data, proposes for the first time a method for optimizing the execution of certain SQL statements within a relational database. This method can greatly shorten the time for the database to execute certain SQL statements (SQL statements targeting aggregation results with certain filtering conditions), improving database performance and increasing work efficiency.

[0024] To achieve the aforementioned optimization goals, we first need to modify the relevant metadata tables in the existing relational database system. These metadata tables record information about aggregate functions. We need to add a field to these tables to indicate whether the state transition function of the aggregate function is a monotonic function. That is, if the input parameter is greater than 0, the function result increases; if the input parameter is less than 0, the function result continuously decreases. Whether it is a monotonic function needs to be determined based on the implementation logic of the aggregate function. Secondly, during the query optimization phase, we need to push the filtering conditions of the aggregate function into its state transition function when generating the query plan. Finally, when executing the query plan, after executing the state transition function of the aggregate function on the data, the result can be substituted into the filtering conditions. If the filtering conditions are not met, the next group of data is processed directly. Compared to the existing processing method, we have pushed the filtering conditions of the aggregate function into its state transition function. In certain scenarios, this allows us to identify records that do not meet the output requirements earlier, thereby accelerating the SQL execution process.

[0025] Specifically, in a first aspect, the present invention provides a SQL optimization processing method based on a relational database, comprising:

[0026] S1. Modify the metadata table in the relational database that records information about aggregate functions, and add a field to the metadata table to indicate whether the state transition function of the aggregate function recorded is a monotonic function;

[0027] S2. When generating the query plan, the filtering conditions of the aggregate function are pushed into its state transition function;

[0028] S3. Obtain the input data for the aggregation function;

[0029] S4. Iterate through the input data of the aggregate function and group the input data according to the method specified in the query plan;

[0030] S5. For each data point in the first group, execute the state transition function of the aggregation function. If the state transition function has an associated filtering condition, substitute the result of the state transition function into the filtering condition. If the filtering condition is not met, proceed to step S8. If the filtering condition is met, save it as an intermediate result.

[0031] S6. Take the intermediate result obtained in the previous step as input and pass it to the final state function of the aggregation function. The calculation result of the final state function is used as the final aggregation function result of the group. If the aggregation function has no filtering conditions, the result is returned to the user directly. If the aggregation function has filtering conditions, proceed to the next step.

[0032] S7. Substitute the aggregation function result obtained in the previous step into the aggregation function's filtering condition, and determine whether the filtering condition is met. If it is met, output the calculation result of the group; otherwise, discard the calculation result of the group.

[0033] S8. If a next group exists, jump back to step S5; otherwise, end the execution of the query plan.

[0034] Furthermore, the monotonic function mentioned in step S1 of the SQL optimization processing method based on relational databases of the present invention includes a state transition function with monotonically increasing results and a state transition function with monotonically decreasing results.

[0035] Furthermore, in step S2 of the SQL optimization processing method based on relational databases of the present invention, when generating the query plan, the filtering conditions of the aggregation function are pushed into its state transition function, including:

[0036] S20. When the filtering condition of the aggregate function is an expression connected by logical operators, check whether the logical operators of the expression are greater than, greater than or equal to, less than, or less than or equal to. If so, proceed to the next step; otherwise, exit.

[0037] S21. Check whether the left side of the expression is an aggregate function and whether the state transition function of the aggregate function is a monotonic function. If so, proceed to the next step; otherwise, exit.

[0038] S22. Check if the right side of the expression is a constant when executing each group. If it is, proceed to the next step; otherwise, exit.

[0039] S23. Normalize the expression, rewrite the expression into an equivalent expression, and ensure that the left side of the resulting expression is an aggregate function by swapping the left and right sub-expressions, then proceed to the next step;

[0040] S24. If the logical operator of the resulting expression is less than or less than or equal to, then determine whether the input parameter of the aggregate function is greater than 0;

[0041] Check the input parameters of the aggregate function; if they are columns, proceed to the next step.

[0042] If it is an arithmetic expression, check if the operator of the arithmetic expression is a monotonic operator. If not, exit. If it is, extract the columns involved by the operator and check if there is a constraint greater than 0 on the extracted columns. If there is no constraint on one or more columns, exit. If there is constraint on all columns, proceed to step S28.

[0043] S25. Check if there are any restrictions greater than 0 in the column. If not, exit; otherwise, proceed to step S28.

[0044] S26. If the logical operator of the resulting expression is greater than or greater than or equal to, then determine whether the input parameter of the aggregate function is less than 0;

[0045] Check the input parameters of the aggregate function; if they are columns, proceed to the next step.

[0046] If it is an arithmetic expression, check if the operator of the arithmetic expression is a monotonic operator. If not, exit. If it is, extract the columns involved by the operator and check if there is a constraint less than 0 on the extracted columns. If there is no constraint on one or more columns, exit. If there is a constraint on all columns, proceed to step S28.

[0047] S27. Check if there are any restrictions less than 0 in the column. If not, exit; otherwise, proceed to step S28.

[0048] S28. Associate the expression with the corresponding aggregation function so that the filtering condition can be executed when the state transition function of the aggregation function is executed;

[0049] S29. Remove aggregate function filtering conditions from SQL filtering conditions.

[0050] Furthermore, in step S3 of the SQL optimization processing method based on relational database of the present invention, the input data of the aggregation function is obtained as the scan records of the table or the intermediate result set.

[0051] Secondly, the present invention provides an SQL optimization processing system based on a relational database, comprising:

[0052] Monotonic function annotation module: Used to annotate whether the state transition function of the aggregate function in the metadata table of a relational database is a monotonic function;

[0053] Filter condition reset module: used to push down the filter conditions of the aggregation function to its state transition function;

[0054] Data grouping module: Used to group the input data of the aggregate function according to the method specified in the query plan;

[0055] Function execution module: State transition function used to execute aggregation functions on the data within each group, and final state function used to execute aggregation functions on intermediate results;

[0056] Logical judgment module: Used to determine whether the execution results of the state transition function and the final state function meet the corresponding filtering conditions.

[0057] Thirdly, the present invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the above-described SQL optimization processing method based on a relational database.

[0058] In summary, based on the characteristics of SQL itself and input data, this invention proposes for the first time a method to optimize the execution of certain SQL statements within a relational database. This method can significantly shorten the time required for the database to execute certain SQL statements (SQL statements targeting aggregated results with certain filtering conditions). Applying this method helps to better utilize database performance and significantly improve database efficiency. Attached Figure Description

[0059] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the embodiments of the present invention will be briefly introduced below. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0060] Figure 1 This is a schematic diagram of the overall implementation process of the SQL optimization processing method of the present invention.

[0061] Figure 2 This is a schematic diagram of the implementation process of step S2 in the SQL optimization processing method of the present invention.

[0062] Figure 3 This is a schematic diagram of the composition structure of the SQL optimization processing system of the present invention. Detailed Implementation

[0063] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this invention, and not all of them. This invention can also be implemented or applied through other different specific embodiments, and the details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of this invention.

[0064] At the same time, it should be understood that the scope of protection of the present invention is not limited to the specific embodiments described below; it should also be understood that the terminology used in the embodiments of the present invention is for describing specific embodiments and not for limiting the scope of protection of the present invention.

[0065] Example 1: A SQL optimization method based on relational databases.

[0066] This method includes the following steps (see...) Figure 1 ):

[0067] S1. Modify the metadata table in the relational database that records information about aggregate functions, and add a field to the metadata table to indicate whether the state transition function of the aggregate function recorded is a monotonic function.

[0068] S2. When generating the query plan, the filtering conditions of the aggregate function are pushed into its state transition function, including (see...). Figure 2 ):

[0069] S20. When the filtering condition of the aggregate function is an expression connected by logical operators, check whether the logical operators of the expression are greater than, greater than or equal to, less than, or less than or equal to. If so, proceed to the next step; otherwise, exit.

[0070] S21. Check whether the left side of the expression is an aggregate function and whether the state transition function of the aggregate function is a monotonic function. If so, proceed to the next step; otherwise, exit.

[0071] S22. Check if the right side of the expression is a constant when executing each group. If it is, proceed to the next step; otherwise, exit.

[0072] S23. Normalize the expression, rewrite the expression into an equivalent expression, and ensure that the left side of the resulting expression is an aggregate function by swapping the left and right sub-expressions, then proceed to the next step;

[0073] S24. If the logical operator of the resulting expression is less than or less than or equal to, then determine whether the input parameter of the aggregate function is greater than 0;

[0074] Check the input parameters of the aggregate function; if they are columns, proceed to the next step.

[0075] If it is an arithmetic expression, check if the operator of the arithmetic expression is a monotonic operator. If not, exit. If it is, extract the columns involved by the operator and check if there is a constraint greater than 0 on the extracted columns. If there is no constraint on one or more columns, exit. If there is constraint on all columns, proceed to step S28.

[0076] S25. Check if there are any restrictions greater than 0 in the column. If not, exit; otherwise, proceed to step S28.

[0077] S26. If the logical operator of the resulting expression is greater than or greater than or equal to, then determine whether the input parameter of the aggregate function is less than 0;

[0078] Check the input parameters of the aggregate function; if they are columns, proceed to the next step.

[0079] If it is an arithmetic expression, check if the operator of the arithmetic expression is a monotonic operator. If not, exit. If it is, extract the columns involved by the operator and check if there is a constraint less than 0 on the extracted columns. If there is no constraint on one or more columns, exit. If there is a constraint on all columns, proceed to step S28.

[0080] S27. Check if there are any restrictions less than 0 in the column. If not, exit; otherwise, proceed to step S28.

[0081] S28. Associate the expression with the corresponding aggregation function so that the filtering condition can be executed when the state transition function of the aggregation function is executed;

[0082] S29. Remove aggregate function filtering conditions from SQL filtering conditions.

[0083] S3. Obtain the input data for the aggregate function (the input data can be a scan record of a table or an intermediate result set).

[0084] S4. Iterate through the input data of the aggregate function and group the input data according to the method specified in the query plan.

[0085] S5. For each data point in the first group, execute the state transition function of the aggregation function. If the state transition function has an associated filtering condition, substitute the result of the state transition function into the filtering condition. If the filtering condition is not met, proceed to step S8. If the filtering condition is met, save it as an intermediate result.

[0086] S6. Take the intermediate result obtained in the previous step as input and pass it to the final state function of the aggregation function. The calculation result of the final state function is used as the final aggregation function result of the group. If the aggregation function has no filtering conditions, the result is returned to the user directly. If the aggregation function has filtering conditions, proceed to the next step.

[0087] S7. Substitute the aggregation function result obtained in the previous step into the aggregation function's filtering condition, and determine whether the filtering condition is met. If it is met, output the calculation result of the group; otherwise, discard the calculation result of the group.

[0088] S8. If a next group exists, jump back to step S5; otherwise, end the execution of the query plan.

[0089] Compared to the previous approach, we have pushed the filtering conditions of the aggregate function to the state transition function of the aggregate function. In this way, in certain scenarios, records that do not meet the output requirements can be detected earlier, thereby speeding up the SQL execution process.

[0090] Example 2: A SQL optimization processing system based on a relational database.

[0091] This system includes the following components (see Figure 3 ):

[0092] Monotonic function annotation module: Used to annotate whether the state transition function of the aggregate function in the metadata table of a relational database is a monotonic function;

[0093] Filter condition reset module: used to push down the filter conditions of the aggregation function to its state transition function;

[0094] Data grouping module: Used to group the input data of the aggregate function according to the method specified in the query plan;

[0095] Function execution module: State transition function used to execute aggregation functions on the data within each group, and final state function used to execute aggregation functions on intermediate results;

[0096] Logical judgment module: Used to determine whether the execution results of the state transition function and the final state function meet the corresponding filtering conditions.

[0097] Each module is implemented and run according to the SQL optimization processing method based on relational databases described above.

[0098] The above description is merely an embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, substitutions, etc., made within the spirit and principle of the present invention should be included within the scope of protection of the claims of the present invention.

Claims

1. A SQL optimization method based on a relational database, characterized in that, The method includes: S1. Modify the metadata table in the relational database that records information about aggregate functions, and add a field to the metadata table to indicate whether the state transition function of the aggregate function recorded is a monotonic function; S2. When generating the query plan, the filtering conditions of the aggregate function are pushed into its state transition function; S3. Obtain the input data for the aggregation function; S4. Iterate through the input data of the aggregate function and group the input data according to the method specified in the query plan; S5. For each data point in the first group, execute the state transition function of the aggregation function. If the state transition function has an associated filtering condition, substitute the result of the state transition function into the filtering condition. If the filtering condition is not met, proceed to step S8. If the filtering condition is met, save it as an intermediate result. S6. Take the intermediate result obtained in the previous step as input and pass it to the final state function of the aggregation function. The calculation result of the final state function is used as the final aggregation function result of the group. If the aggregation function has no filtering conditions, the result is returned to the user directly. If the aggregation function has filtering conditions, proceed to the next step. S7. Substitute the aggregation function result obtained in the previous step into the aggregation function's filtering condition, and determine whether the filtering condition is met. If it is met, output the calculation result of the group; otherwise, discard the calculation result of the group. S8. If a next group exists, jump back to step S5; otherwise, end the execution of the query plan.

2. The SQL optimization processing method based on relational databases according to claim 1, characterized in that, The monotonic functions mentioned in step S1 include state transition functions with monotonically increasing results and state transition functions with monotonically decreasing results.

3. The SQL optimization processing method based on relational databases according to claim 1, characterized in that, Step S2, which describes pushing the filtering conditions of the aggregation function into its state transition function when generating the query plan, includes: S20. When the filtering condition of the aggregate function is an expression connected by logical operators, check whether the logical operator of the expression is greater than, greater than or equal to, less than, or less than or equal to. If it is, proceed to the next step; otherwise, exit. S21. Check whether the left side of the expression is an aggregate function and whether the state transition function of the aggregate function is a monotonic function. If so, proceed to the next step; otherwise, exit. S22. Check if the right side of the expression is a constant when executing each group. If it is, proceed to the next step; otherwise, exit. S23. Normalize the expression, rewrite it into an equivalent expression, and ensure that the left side of the resulting expression is an aggregate function by swapping the left and right sub-expressions, then proceed to the next step; S24. If the logical operator of the resulting expression is less than or less than or equal to, then determine whether the input parameter of the aggregate function is greater than 0; Check the input parameters of the aggregate function; if they are columns, proceed to the next step. If it is an arithmetic expression, check if the operator of the arithmetic expression is a monotonic operator. If not, exit. If it is, extract the columns involved by the operator and check if there is a constraint greater than 0 on the extracted columns. If there is no constraint on one or more columns, exit. If there is constraint on all columns, proceed to step S28. S25. Check if there are any restrictions greater than 0 in the column. If not, exit; otherwise, proceed to step S28. S26. If the logical operator of the resulting expression is greater than or greater than or equal to, then determine whether the input parameter of the aggregate function is less than 0; Check the input parameters of the aggregate function; if they are columns, proceed to the next step. If it is an arithmetic expression, check if the operator of the arithmetic expression is a monotonic operator. If not, exit. If it is, extract the columns involved by the operator and check if there is a constraint less than 0 on the extracted columns. If there is no constraint on one or more columns, exit. If there is a constraint on all columns, proceed to step S28. S27. Check if there are any restrictions less than 0 in the column. If not, exit; otherwise, proceed to step S28. S28. Associate the expression with the corresponding aggregation function so that the filtering condition can be executed when the state transition function of the aggregation function is executed; S29. Remove aggregate function filtering conditions from SQL filtering conditions.

4. The SQL optimization processing method based on relational databases according to claim 1, characterized in that, In step S3, the input data for the aggregation function is obtained from the table scan records or intermediate result sets.

5. A SQL optimization processing system based on a relational database, characterized in that, The system implements the SQL optimization processing method based on a relational database as described in any one of claims 1-4 during operation, and the system includes: Monotonic function annotation module: Used to annotate whether the state transition function of the aggregate function in the metadata table of a relational database is a monotonic function; Filter condition reset module: used to push down the filter conditions of the aggregation function to its state transition function; Data grouping module: Used to group the input data of the aggregate function according to the method specified in the query plan; Function execution module: State transition function used to execute aggregation functions on the data within each group, and final state function used to execute aggregation functions on intermediate results; Logical judgment module: Used to determine whether the execution results of the state transition function and the final state function meet the corresponding filtering conditions.

6. A computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the SQL optimization processing method based on a relational database as described in any one of claims 1-4.

Images