Database query statement optimization method, medium, product and equipment

Abstract

The invention provides a database query statement optimization method, a medium, a product and equipment. The method comprises the following steps: acquiring a database query statement; extracting a parent query statement and a child query statement in the database query statement; judging whether a useless target column exists in the sub-query statement or not, wherein the useless target column is a data column which does not need to exist in the sub-query statement and is not used by the parent query statement; and under the condition that the useless target column exists in the sub-query statement, replacing the useless target column with a constant value in the database query statement. Through the method, meaningless complex operations can be reduced, so that the query execution performance is improved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, medium, product, and device for optimizing database query statements. Background Technology

[0002] In the current database technology field, the execution efficiency of complex query statements (especially those containing subqueries and table joins) directly affects the performance of database systems. Currently, query statements in scenarios such as online analytical processing often contain multiple levels of nested parent and subqueries, and left join operations are common in subqueries.

[0003] While current database optimizers can perform basic optimizations on query statements, they have significant drawbacks: First, subqueries often contain useless target columns, and redundant calculations of these columns consume a lot of system resources, reducing query efficiency. Second, for single-sided retainable joins containing useless target columns, existing optimizers struggle to identify their redundancy. Even if the internal table columns in the subquery's target column no longer contribute anything, they will still perform the complete join operation, resulting in redundant I / O and computational overhead, further exacerbating query performance bottlenecks.

[0004] Therefore, how to accurately identify and optimize useless target columns in subqueries, while efficiently identifying and eliminating redundant one-sided retainable joins to reduce query overhead and improve database execution efficiency, has become a pressing technical problem in the field of database query optimization. Summary of the Invention

[0005] In view of the above problems, the present invention proposes a method, medium, product and device for optimizing database query statements to overcome the above problems or at least partially solve the above problems.

[0006] One objective of this invention is to reduce redundant operations in database query statements.

[0007] A further objective of this invention is to improve the query performance of database query statements.

[0008] Specifically, the present invention provides a method for optimizing database query statements, comprising: Retrieve the database query statement; Extract the parent and child query statements from a database query statement; Determine if there are any useless target columns in the subquery. Useless target columns are data columns that are not required in the subquery and are not used by the parent query. If a useless target column exists in a subquery, replace the useless target column with a constant value in the database query statement.

[0009] Optionally, the steps to determine whether there is a useless target column in the subquery include: Retrieve the target column from the subquery statement; Determine whether there are any unreferenced target columns based on the non-filtering dependency conditions of the subquery statement; If there are unreferenced target columns, treat them as target columns to be confirmed. Determine whether the parent query uses the target column to be confirmed; If the parent query does not use the target column to be confirmed, then the target column to be confirmed is determined to be a useless target column.

[0010] Optionally, the step of replacing the useless target column with a constant value in the database query statement may include: Determine if a one-sided reserved join exists in the target subquery containing the useless target column; When a one-sided reserved join exists in the target subquery, determine the inner table involved in the one-sided reserved join and its inner table join columns. The inner table is the non-reserved table in the one-sided reserved join, and the inner table join columns are the data columns that participate in the one-sided reserved join. Determine whether a single-sided reserved join is a redundant join based on the inner table and the inner table join column; If a one-sided reserved join is a redundant join, eliminate the one-sided reserved join in the target subquery.

[0011] Optionally, the steps for determining whether a single-sided reserved connection is a redundant connection include: Determine whether the data columns of the inner table appear only in the join conditions of a one-sided reserved join after replacing the useless target column; Determine whether the join column of the inner table has a unique constraint when the data column of the inner table only appears in the join condition of a one-sided reserved join. If the join column in the inner table has a unique constraint, a one-sided reserved join is determined to be a redundant join.

[0012] Optionally, the unilateral retention type connection includes: a left connection and a right connection; When a one-sided reserved join is a left join, the inner table is the non-reserved table to the right of the left join operator; In the case of a one-sided reserved join being a right join, the inner table is the non-reserved table to the left of the right join operator.

[0013] Optionally, after the step of eliminating one-sided reserved joins in the target subquery, the following may also be included: Verify whether the result set returned by the subquery after removing the one-sided preserved join is consistent with the result set returned by the subquery before removal; If the result set is consistent, retain the eliminated subquery statement; In the event of inconsistent result sets, restore the one-sided preserved connection and output an optimization failure message.

[0014] Optionally, constant values ​​include: null values ​​and fixed values ​​with no business meaning that match the data type of the useless target column.

[0015] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of the optimization method for any of the above-described database query statements.

[0016] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of the optimization method for any of the above-described database query statements.

[0017] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the steps of the optimization method for any of the above-described database query statements.

[0018] The database query statement optimization method of the present invention first obtains the database query statement; extracts the parent query statement and subquery statement from the database query statement; determines whether there are any useless target columns in the subquery statement, where useless target columns are data columns that are not required in the subquery statement and are not used by the parent query statement; if there are useless target columns in the subquery statement, replaces the useless target columns with constant values ​​in the database query statement. This method can reduce meaningless complex operations, thereby improving query execution performance.

[0019] Furthermore, the database query statement optimization method of the present invention, after the step of replacing useless target columns with constant values ​​in the database query statement, further includes: determining whether a one-sided reserved join exists in the target subquery statement containing the useless target column; if a one-sided reserved join exists in the target subquery statement, determining the inner table involved in the one-sided reserved join and its inner table join columns, wherein the inner table is the non-reserved table in the one-sided reserved join, and the inner table join columns are the data columns participating in the one-sided reserved join; determining whether the one-sided reserved join is a redundant join based on the inner table and the inner table join columns; if the one-sided reserved join is a redundant join, eliminating the one-sided reserved join in the target subquery statement. This method can further reduce meaningless join operations in the database query statement, thereby improving query execution performance.

[0020] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0021] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a flowchart illustrating a method for optimizing database query statements according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating a method for optimizing database query statements according to another embodiment of the present invention; Figure 3 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 5 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0022] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0023] It should be noted that the logic and / or steps represented in the flowchart or otherwise described 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, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0024] In the current database technology field, SQL (Structured Query Language) is a commonly used example of a database query statement. SQL statements offer various functions such as data manipulation and data definition. This language is interactive and provides great convenience to users. In specific query processing, SQL join operations are the core means of achieving multi-table data association and complex logical queries, but some SQL statements have room for optimization.

[0025] An optional SQL example is shown in equation (1): select s1.* from s1 ,(select s2a ,1 c , count(s2d) from s2 where s2d=3 group by s2a )v where a =vc Formula (1) The execution logic of the SQL statement shown in equation (1) is as follows; Step 1: Execute the subquery (select s2a ,1 c , count(s2d) from s2 where s2d =3 group by s2a )v, and name the result a temporary table v. The specific operations are as follows: First, filter all rows in table s2 that satisfy s2d=3; then group the filtered rows by the s2a field; keep the s2a value in each group, generate a column c with a fixed value of 1, and count the number of non-empty rows in each group s2d (i.e., the total number of rows in each group); finally, the subquery outputs a temporary table v containing three columns: s2a, c, and count(s2d).

[0026] Step 2: Execute the main query select s1.* from s1, v where a = vc, which performs a Cartesian product join between the main table s1 and the temporary table v, and filters out rows that satisfy the join condition s1.a=vc.

[0027] Step 3: Filter the final results: After filtering by where a=vc, only keep the related rows that meet the conditions.

[0028] Step 4: Output target fields: Finally, extract all fields (s1.*) from the main table s1 as the query results.

[0029] Analysis reveals that the SQL statement shown in equation (1) has significant flaws in execution logic and resource utilization, resulting in a clear bottleneck in query performance optimization. Specifically, although the subquery completes the filtering and grouping statistics of table s2, the generated count(s2d) field does not participate in the association filtering or result output of the main query. The statistical operation of count(s2d) in the subquery requires traversing all rows in table s2 where s2d=3, but the calculation result is not utilized, which is a completely redundant calculation and storage overhead, resulting in meaningless performance loss.

[0030] Therefore, this invention provides a method for optimizing database query statements. Figure 1 This is a flowchart illustrating a method for optimizing database query statements according to an embodiment of the present invention, as shown below. Figure 1 As shown, the optimization method for this database query statement includes at least the following steps S101 to S104.

[0031] Step S101: Obtain the database query statement. In this step, the database query statement can generally be obtained by user input or automatically generated by the database program during operation due to business requirements. Those skilled in the art can decide on the method of obtaining the database query statement based on the actual situation.

[0032] Step S102: Extract the parent query statement and subquery statement from the database query statement.

[0033] Taking Equation (1) as an example, in the SQL statement shown in Equation (1), the parent query is select s1.* from s1 , v where a = vc, where v is an alias of the subquery, which is essentially the parent query referencing the result of the subquery; the subquery is select s2a ,1 c , count(s2d) from s2 where s2d =3 group by s2a, and this subquery is named a temporary table v for use by the parent query.

[0034] Step S103: Determine if there are any useless target columns in the subquery. Useless target columns are data columns in the subquery that are not essential and are not used by the parent query. Even if such columns are replaced, it will not affect the execution logic or result structure of either the subquery or the parent query. "Not essential" generally means that the data column does not appear in non-filtering dependency conditions such as `GROUP BY` or `HAVING` in the subquery. "Not used by the parent query" means that the data column does not appear in clauses such as `SELECT`, `WHERE`, or `JOIN` in the parent query, indicating that the parent query does not need the data in that column.

[0035] In some optional embodiments, the step of determining whether there is a useless target column in the subquery statement generally includes: obtaining the target column in the subquery statement; determining whether there is an unreferenced target column based on the non-filtering dependency condition of the subquery statement; if there is an unreferenced target column, taking the unreferenced target column as the target column to be confirmed; determining whether the parent query statement uses the target column to be confirmed; if the parent query statement does not use the target column to be confirmed, determining that the target column to be confirmed is a useless target column. The non-filtering dependency condition refers to conditions other than the filtering condition in the subquery. For example, in equation (1), the filtering condition of the subquery is where s2d=3, while group by s2a is a non-filtering dependency condition. If such a non-filtering dependency condition references a data column, that column must generally appear in the target column.

[0036] Let's take equation (1) as an example for explanation: Step 1: Obtain the target columns of the subquery: In the SQL statement shown in equation (1), the target columns of the subquery are s2a, c (fixed value 1), and count(s2d); Step 2: Determine the unreferenced target column based on the non-filtering dependency condition: The non-filtering dependency condition in the subquery of formula (1) is group by s2a. s2a appears in group by and is a required column in the subquery; c is a fixed value of 1 and has no dependency condition; count(s2d) is only a statistical result and does not appear in group by.

[0037] Step 3: Select unreferenced columns as target columns to be confirmed: At this point, we can determine that the target columns to be confirmed are 1 c and count(s2d); Step 4: Determine whether the parent query uses the target column to be confirmed: Equation (1) The parent query only references column c of the subquery through a=vc, and does not reference count(s2d).

[0038] Step 5: Identify useless target columns: Based on the previous steps, count(s2d) can be identified as a useless target column.

[0039] Step S104: If the determination in step S103 is yes, replace the useless target column with a constant value in the database query statement. The constant value generally includes: null values ​​and fixed values ​​without business meaning that match the data type of the useless target column. After confirming that a useless target column exists in the subquery, replace that column with a constant value. The selection of the constant value must meet two conditions: 1. No change to query semantics: The result structure (number of columns, column types) of the subquery after replacement is consistent with the original query and does not affect the execution of the parent query; 2. No business meaning: To avoid interfering with query logic due to replacement values.

[0040] Optional constant values ​​can generally include: null values ​​(NULL) or meaningless fixed values ​​that match the column type: such as replacing numeric columns with 0, and replacing string columns with an empty string ('').

[0041] The specific replacement operation for equation (1) can be to replace count(s2d) in the subquery with NULL. The final result after replacement is shown in equation (2): select s1.* from s1 ,(select s2a ,1 c , null from s2 where s2d =3group by s2a )v where a =vc Formula (2) The execution logic of this SQL is as follows: 1. Execute a subquery to generate a temporary table v: Filter the rows where s2d=3 from table s2, group them by s2a, and generate three columns: grouping field s2a, column c with a fixed value of 1, and column null with a fixed null value. Finally, you get a temporary table v containing only the three columns s2a, c, and null.

[0042] 2. Filtering by joining the main table and the temporary table: The main table s1 and the temporary table v are implicitly joined (Cartesian product). The filtering is done by WHERE a=vc. Since vc is fixed at 1, it actually filters the rows in table s1 where a=1.

[0043] 3. Output results: Extract only all fields (s1.*) of the filtered s1 table as the final result.

[0044] In the SQL shown in Equation (1), the calculation of count(s2d) does not contribute to the final result, but consumes database resources. After replacing it with null, the subquery only needs to complete the basic filtering and grouping, without additional statistics, which greatly reduces the execution time and resource consumption, and the query semantics (the final output of the row with s1.a=1) are completely consistent.

[0045] Since the target column in the subquery is not a required column and is not referenced by the parent query, it will not be output and is not referenced by the parent query. Therefore, the column is a useless column and can be replaced by a constant value, thereby eliminating some unnecessary operations and improving query performance.

[0046] This method can eliminate meaningless operations in subqueries, thereby reducing database I / O and CPU consumption, maintaining complete consistency in query semantics, and ultimately improving the query efficiency of database query statements.

[0047] Figure 2 This is a flowchart illustrating a method for optimizing database query statements according to another embodiment of the present invention, as shown below. Figure 2As shown, the optimization method for this database query statement includes at least the following steps S201 to S208.

[0048] Step S201: Obtain the database query statement.

[0049] Step S202: Identify the parent and child queries that have a join relationship in the database query statement.

[0050] An optional SQL example is shown in equation (3): select s1.* from s1 ,(select s2a ,1 c ,min(s0.id) from s2 left joins0 on s2.s2a =s0.age where s2d =3 group by s2a)v where a =vc Formula (3) Furthermore, the age column in table s0 has a uniqueness constraint.

[0051] The execution logic of the SQL statement shown in equation (3) is as follows; Step 1: Execute the left join and data filtering within the subquery: First, execute s2 left join s0 on s2.s2a = s0.age (left join) in the subquery. Then, with s2 as the retained table (left table) and s0 as the non-retained table (right table / inner table), join the two rows of data by s2.s2a=s0.age. Next, filter out all rows in the join result that satisfy s2d=3 (only keep the rows in s2 where s2d=3, and the rows in s0 that match age=s2.s2a; if there is no match, the value of column s0 is NULL).

[0052] Step 2: Subquery Grouping and Field Generation: Group the filtered join results by s2a to generate three columns (temporary table v): s2a: Grouping by field, retaining the s2a value for each group; 1. c: The column with a fixed value of 1, aliased as c; min(s0.id): Calculates the minimum value of s0.id in each group.

[0053] Step 3: Implicit inner join between the main table and the temporary table: In the main query, s1 and v are implicitly joined (Cartesian product), combining all rows of table s1 with all rows of table v in pairs.

[0054] Step 4: Conditional Filtering and Result Output: Filter by where a=vc (vc is fixed at 1, actually filtering rows where s1.a=1), and finally output only all fields of table s1 (s1.*).

[0055] Analysis shows that the ultimate goal of this SQL query is only to filter rows in table s1 where a=1. However, the subquery performs additional operations such as a full left join between s2 and s0 and a min(s0.id) aggregation calculation after grouping. These operations consume a lot of database resources but have no impact on the final result, resulting in extremely low query efficiency.

[0056] Step S203: Determine if there are any useless target columns in the subquery statement. Using the SQL statement shown in equation (3) as an example, the target columns of the subquery in the SQL statement shown in equation (3) are s2a, c, and min(s0.id). Then, a non-necessary column judgment is performed: s2a appears in group by s2a, and is a necessary column in the subquery; although c is a fixed value of 1, it is not necessary in the subquery but is referenced by the parent query, so it is also a necessary column; min(s0.id) does not appear in non-filtering dependency conditions such as group by, and is not referenced by the parent query, so it is determined to be a useless target column.

[0057] Step S204: If the determination in step S203 is yes, replace the useless target column with a constant value in the database query statement. Replace min(s0.id) in the subquery of equation (3) with a constant value (preferably NULL, which is suitable for all data types). In the original SQL, min(s0.id) needs to calculate the minimum value of s0.id for each s2a group. After replacing it with NULL, the database does not need to perform this statistical operation and directly returns a fixed value. The result structure of the subquery remains unchanged after the replacement, and it does not affect the association logic of the parent query. At the same time, it lays the foundation for subsequent steps.

[0058] Step S205: If step S204 has been completed or step S203 has failed, determine whether a one-sided reserved join exists in the target subquery containing the useless target column. If the subquery contains only inner joins or no join operations, the result is negative, and the optimization ends.

[0059] One-sided retainable joins generally refer to left joins or right joins (this type of join differs from inner joins in that it retains all rows from one of the tables). Taking the SQL statement shown in equation (3) as an example, the subquery contains s2 leftjoin s0, which is a left join in one-sided retainable joins, so the result is yes.

[0060] Step S206: If the condition in step S205 is met, determine the inner table involved in the one-sided reserved join and its inner table join column. When the one-sided reserved join is a left join, the inner table is the non-reserved table to the right of the left join operator; when the one-sided reserved join is a right join, the inner table is the non-reserved table to the left of the right join operator.

[0061] In the SQL statement shown in equation (3), the join type is left join (s2 left join s0), so its inner table is the non-retained table of the left join, namely the s0 table; the join column of the inner table is the column in the s0 table that participates in the join condition, namely s0.age (the join condition is s2.s2a=s0.age).

[0062] Step S207: Determine whether a single-sided reserved join is a redundant join based on the inner table and the inner table join column.

[0063] In some optional embodiments, the step of determining whether a one-sided reserved join is a redundant join may generally include: determining whether the data columns of the inner table appear only in the join condition of the one-sided reserved join after replacing the useless target column; if the data columns of the inner table appear only in the join condition of the one-sided reserved join, determining whether the join columns of the inner table have uniqueness constraints; if the join columns of the inner table have uniqueness constraints, determining that the one-sided reserved join is a redundant join.

[0064] In the SQL statement shown in equation (3), after replacing the useless column, all columns of the inner table (s0) only appear in the join condition of the one-sided reserved join (s2.s2a=s0.age), and do not appear in other clauses such as SELECT, WHERE, and GROUP BY of the subquery; and the inner table join column (s0.age) has a unique constraint. At this time, the join result will result in each row in the s2 table matching at most 1 row in the s0 table, and even if there is no matching row (the s0 column is filled with NULL), it will not change the value of the s2a column, the number of rows, and the fixed value of column c after the subquery grouping. It will only make the value of the original min(s0.id) column (which has been replaced with NULL) remain unchanged. At this time, whether or not a left join operation is performed will not affect the final result set of the SQL. Therefore, it can be determined that s2left join s0 is a redundant join.

[0065] If the determination in step S207 is negative, the current optimization operation ends.

[0066] Step S208: If the determination in step S207 is yes, eliminate the one-sided reserved join in the target subquery statement.

[0067] Since the left join in equation (3) is a meaningless performance loss, it is chosen to eliminate it. After eliminating the one-sided retainable join, the subquery only needs to scan the s2 table (without scanning the s0 table), the overhead of join matching is completely eliminated, and since the join column in the inner table is unique, the number of rows in the result of the subquery after eliminating the join is consistent with the original result, and does not affect the final query result.

[0068] The final optimized result is shown in equation (4): select s1.* from s1 ,(select s2a ,1 c ,null from s2 where s2d =3group by s2a )v where a =vc Formula (4) Its execution logic is as follows: Step 1: Execute a subquery to generate a temporary table v: First, filter all rows in table s2 that satisfy s2d=3; then group the filtered rows according to the s2a field; finally, generate three columns to form a temporary table v (s2a, c, null).

[0069] Step 2: Implicit inner join between main table s1 and temporary table v: In the main query, s1 and v are implicitly joined. First, perform a Cartesian product between all rows in table s1 and all rows in temporary table v. Filter the join results using WHERE a=vc: Since vc is a fixed value of 1, only rows in table s1 where a=1 are actually filtered out.

[0070] Step 3: Output the final result: Extract only all fields (s1.*) from the s1 table in the filtered rows as the final query result.

[0071] Compared to the original formula (3), the subquery has removed the redundant s2 left join s0 connection and min(s0.id) aggregation calculation, and only retains the necessary s2d=3 filtering and s2a grouping. Without changing the final result, all meaningless calculations and connection overheads have been eliminated, and the execution efficiency has been greatly improved.

[0072] Optionally, after eliminating the one-sided reserved join in the target subquery, the following steps can generally be included: verifying whether the result set returned by the subquery after eliminating the one-sided reserved join is consistent with that returned by the subquery before elimination; if the result set is consistent, retain the subquery after elimination; if the result set is inconsistent, restore the one-sided reserved join and output an optimization failure message. This step ensures that the join elimination operation does not violate query semantics, guarantees that the optimized SQL returns a result set that is completely consistent with the original SQL, and avoids errors in the optimized result due to special cases such as deviations in the uniqueness constraint judgment of internal table join columns or hidden dependencies in business logic.

[0073] This method can completely eliminate redundant calculations and connection overhead, significantly improving the execution efficiency of subqueries, reducing database load, and minimizing the memory usage of join results. It not only improves the query performance of database queries but also avoids business logic errors caused by optimization.

[0074] The flowcharts provided in this embodiment are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

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

[0076] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 3 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 4 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 5 This 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, when executed by the processor 32, implements the steps of the database query statement optimization method described above. A computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by the processor 32, implements the steps of the database query statement optimization method described above. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored on the memory 31 and running on the processor 32.

[0077] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone 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 Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, Field-Programmable Gate Arrays (FPGAs), or Programmable Logic Arrays (PLAs), may execute computer-readable program instructions to personalize the electronic circuits by utilizing state information from computer-readable program instructions.

[0078] For the purposes of this embodiment, computer program product 10 is a related product containing computer program 11. For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing computer program 11, and can be any device capable of containing, storing, communicating, propagating, or transmitting program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0079] Computer device 30 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 30 can be a cloud computing node. Computer device 30 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 30 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

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

[0081] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

[0082] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for optimizing database query statements, comprising: Obtain the database query statement; Extract the parent query statement and subquery statement from the database query statement; Determine whether there is a useless target column in the subquery statement. The useless target column is a data column that is not required in the subquery statement and is not used by the parent query statement. If the useless target column exists in the subquery statement, the useless target column is replaced with a constant value in the database query statement.

2. The method for optimizing database query statements according to claim 1, wherein, The step of determining whether there is a useless target column in the subquery statement includes: Retrieve the target column from the subquery statement; Determine whether there are any unreferenced target columns based on the non-filtering dependency conditions of the subquery statement; If there are unreferenced target columns, treat them as target columns to be confirmed. Determine whether the parent query statement uses the target column to be confirmed; If the parent query does not use the target column to be confirmed, the target column to be confirmed is determined to be a useless target column.

3. The method for optimizing database query statements according to claim 1, wherein, Following the step of replacing the useless target column with a constant value in the database query statement, the method further includes: Determine whether a one-sided reserved join exists in the target subquery containing the useless target column; If a one-sided reserved join exists in the target subquery statement, determine the inner table involved in the one-sided reserved join and its inner table join column. The inner table is the non-reserved table in the one-sided reserved join, and the inner table join column is the data column participating in the one-sided reserved join. Determine whether the single-sided reserved connection is a redundant connection based on the inner table and the inner table join column; If the one-sided reserved join is a redundant join, the one-sided reserved join is eliminated in the target subquery.

4. The method for optimizing database query statements according to claim 3, wherein, The step of determining whether the single-sided reserved connection is a redundant connection includes: Determine whether the data column of the inner table appears only in the join condition of the one-sided reserved join after replacing the useless target column; If the data column of the inner table only appears in the join condition of the one-sided reserved join, determine whether the join column of the inner table has a unique constraint; If the internal table join column has a unique constraint, the single-sided reserved join is determined to be the redundant join.

5. The method for optimizing database query statements according to claim 3, wherein, The single-sided reserved connection includes: a left connection and a right connection; When the single-sided reserved join is a left join, the inner table is the non-reserved table to the right of the left join operator; In the case where the single-sided reserved join is a right join, the inner table is the non-reserved table to the left of the right join operator.

6. The method for optimizing database query statements according to claim 3, wherein, The step of eliminating the one-sided reserved join in the target subquery statement further includes: Verify whether the result set returned by the subquery statement after eliminating the one-sided retained join is consistent with the result set returned by the subquery statement before elimination; If the result set is consistent, retain the eliminated subquery statement; If the result set is inconsistent, restore the one-sided preserved connection and output an optimization failure message.

7. The method for optimizing database query statements according to claim 1, wherein, The constant values ​​include: null values ​​and fixed values ​​with no business meaning that match the data type of the useless target column.

8. A computer-readable storage medium having a computer program stored thereon, the computer program being executed by a processor to implement the steps of the method for optimizing a database query statement according to any one of claims 1 to 7.

9. A computer program product comprising a computer program that, when executed by a processor, implements the steps of the method for optimizing a database query statement 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, when executing the machine-executable program, implements the steps of the method for optimizing a database query statement according to any one of claims 1 to 7.

Images