Database operation statement optimization method, medium, product and equipment

Abstract

The invention provides a database operation statement optimization method, a medium, a product and equipment. The method comprises the following steps: acquiring and analyzing a database operation statement; judging whether the database operation statement meets a preset redundant connection elimination condition or not according to the analysis result of the database operation statement, wherein the preset redundant connection elimination condition is used for determining whether redundant connection exists in the database operation statement or not; under the condition that the database operation statement meets a preset redundant connection elimination condition, determining a redundancy type met by the database operation statement; and executing corresponding redundancy elimination operation on the database operation statement according to the redundancy type. Through the method, scanning of redundant tables can be effectively reduced, connection operation is reduced, a query execution plan of database operation statements is optimized, and 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 operation statements. Background Technology

[0002] In the current database technology field, query operations are the core of data processing, and join operations, as a key technology for realizing data association and supporting complex query logic, directly affect the overall performance of the database. As the scale of business data continues to expand and query logic becomes increasingly complex, redundant joins have gradually become a prominent bottleneck restricting the improvement of query performance.

[0003] While existing database query optimization techniques possess basic optimization capabilities, they have significant limitations in identifying and eliminating redundant joins, making it difficult to accurately handle invalid joins in various complex scenarios. Some join operations in queries do not provide valid data supplementation to the query results, nor do they affect the correctness or uniqueness of the results, yet they still trigger additional table, subquery, or view scans, as well as unnecessary join calculations.

[0004] These redundant connections not only consume significant database computing resources and storage bandwidth, but also substantially increase query response time. This problem is particularly pronounced in scenarios involving large-volume queries and high-frequency business access, severely impacting business processing efficiency and user experience. Therefore, there is an urgent need to develop an efficient database operation statement optimization technique to address the shortcomings of existing technologies and improve database query performance. Summary of the Invention

[0005] In view of the above problems, the present invention proposes an optimization method, medium, product and device for database operation statements that overcomes or at least partially solves the above problems.

[0006] One objective of this invention is to comprehensively identify and effectively eliminate various types of redundant connections that have no practical significance.

[0007] A further objective of this invention is to optimize the execution performance of database operation statements; Specifically, this invention provides a method for optimizing database operation statements, comprising: Retrieve and parse database operation statements; The database operation statement is determined based on the parsing results to determine whether the database operation statement meets the preset redundant connection elimination condition. The preset redundant connection elimination condition is used to determine whether there are redundant connections in the database operation statement. If the database operation statement meets the preset redundant connection elimination conditions, determine the redundancy type that the database operation statement meets; Perform corresponding redundancy elimination operations on the database operation statements based on the redundancy type.

[0008] Optionally, the step of determining whether a database operation statement meets the preset redundant connection elimination conditions based on the parsing result of the database operation statement includes: Determine if a database operation statement contains an equi-join of the same column in the same table, and that the join key is unique; When database operation statements contain equi-joins in the same table and column, and the join key is unique, identify redundant data tables in the database operation statements. Determine whether the target column of a database operation statement contains data columns from redundant data tables; If the target column does not contain data columns from redundant data tables, the database operation statement is determined to meet the preset redundant connection elimination conditions.

[0009] Optionally, the steps for determining the redundancy type satisfied by the database operation statement include: when the database operation statement has an equi-value join in the same table and column and the join key is unique, and the target column does not contain data columns from the redundant data table, the redundancy type satisfied by the database operation statement is determined to be an equi-value redundant join with a unique key in the same table and column. When the redundancy type is an equal-value redundant join with a unique key in the same table and column, the steps for performing the corresponding redundancy elimination operation on the database operation statement according to the redundancy type include: eliminating redundant data tables and adding constraints corresponding to the join key.

[0010] Optionally, the steps for adding constraints corresponding to the connection key include: Add a constraint that the connection key cannot be empty.

[0011] Optionally, the step of determining whether a database operation statement meets the preset redundant connection elimination conditions based on the parsing result of the database operation statement includes: Determine if the database operation statement contains a target connection operation, where the connection condition for the target connection operation is either a filter condition or no connection condition. In cases where a database operation statement contains a target join operation, identify redundant data tables in the database operation statement. Determine whether the result set of the parent query of the database operation statement has the property of deduplication and whether the target column of the parent query contains data columns of redundant data tables; If the result set of the parent query of the database operation statement has deduplication properties and the target column of the parent query does not contain data columns from redundant data tables, the database operation statement is determined to meet the preset redundant join elimination conditions.

[0012] Optionally, the step of determining the redundancy type satisfied by the database operation statement includes: if the result set of the parent query of the database operation statement has deduplication properties and the target column of the parent query does not contain data columns of the redundant data table, then the redundancy type satisfied by the database operation statement is deduplication irrelevant column redundant join. When the redundancy type is deduplicating irrelevant column redundant join, the steps to perform the corresponding redundancy elimination operation on the database operation statement according to the redundancy type include: eliminating redundant data tables in the database operation statement.

[0013] Optionally, the steps to determine whether the result set of the parent query of the database operation statement has deduplication properties include: Determine if the parent query contains keywords that are used for deduplication. If the parent query contains keywords that are deduplicated, the result set is determined to have deduplicated properties.

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

[0015] 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 database operation statements described above.

[0016] 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 database operation statements of any of the above.

[0017] The database operation statement optimization method of the present invention first acquires and parses the database operation statement; then, based on the parsing result, it determines whether the database operation statement meets a preset redundant join elimination condition, which is used to determine whether redundant joins exist in the database operation statement; if the database operation statement meets the preset redundant join elimination condition, the redundancy type of the database operation statement is determined; finally, based on the redundancy type, the corresponding redundancy elimination operation is performed on the database operation statement. This method can effectively reduce the scanning of redundant tables, reduce join operations, optimize the query execution plan of the database operation statement, and improve query execution performance.

[0018] 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

[0019] 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 operation statements according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating a method for optimizing database operation statements according to another embodiment of the present invention; Figure 3 This is a flowchart illustrating a method for optimizing database operation statements according to yet another embodiment of the present invention; Figure 4 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 5 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 6 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0020] 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.

[0021] 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).

[0022] In the current database technology field, SQL (Structured Query Language) is a commonly used example of database manipulation statements. 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. However, unreasonable join design can easily lead to redundant joins, resulting in a significant reduction in query efficiency. While existing database query optimizers can handle some simple redundant scenarios, they have shortcomings in optimizing two specific join scenarios: One type of scenario involves equi-joins using unique indexes on the same column of the same table. When a parent query and a subquery perform an equi-join on the same column (which has a unique index) of the same table, current technology still scans the table in the subquery first to form a result set, and then repeatedly scans the same table in the parent query to perform the join operation. Since the uniqueness of the join key ensures that the result set is not duplicated and does not depend on additional join data, the repeated table scans are ineffective overhead, resulting in wasted database resources and query delays.

[0023] An alternative example is shown in equation (1): selectssh.* from (select a ,d from s1 where b =1 group by a ,d ) ssh,s1 s where ssh.a =sa and s. b=1 Formula (1) The SQL statement shown in equation (1) has unique column a of table s1, and its execution logic is as follows: 1. Execute the subquery within the parentheses first: Scan table s1 and filter out all records that satisfy b=1; Group by columns a and d (essentially to remove duplicates, ensuring that only one record is retained for the same combination of a and d). Output the subquery result set ssh, which contains only columns a and d.

[0024] 2. Then perform multi-table joins and filtering: Scan table s1 (alias s) again and filter out records where b=1; Perform an equi-join between the subquery result set ssh and the filtered results of table s, with ssh.a=sa; Final output: Only all columns (a, d) in the subquery ssh are retained; all data from table s is not output.

[0025] The SQL statement shown in equation (1) belongs to the scenario of equi-join of unique indexes in the same table and column. During the execution process, table s1 was scanned, but the data of table s1 was not output in the end, resulting in a waste of database resources and query delay.

[0026] Another type of join scenario involves deduplication. When a query contains deduplication operations such as DISTINCT, GROUP BY, MIN / MAX aggregate functions, or UNION without the ALL keyword, if the joined table, subquery, or view does not provide data to the target column and there are no valid join conditions (only filtering conditions or no related conditions), current technology will still scan all associated objects and perform the join. This type of join does not change the deduplicated result set, but it adds unnecessary scanning and calculation steps, significantly reducing the execution efficiency of complex queries.

[0027] An alternative example is shown in equation (2): select distinct ssh.* from (select a ,d from s1 where b =1 group by a,d ) ssh ,s2 Equation (2) The SQL statement shown in equation (2) has the following execution logic; 1. Execute the subquery within the parentheses first: Scan table s1 and filter out all records that satisfy b=1; Group by columns a and d (essentially to remove duplicates, ensuring that only one record is retained for the same combination of a and d). Output the subquery result set ssh, which contains only columns a and d, and has no duplicate data.

[0028] 2. Then perform an implicit cross-table join: Scan all records in table s2 (without any filtering conditions); Since there is no explicit join condition (such as ssh.xx=s2.xx), the database will perform a Cartesian product join—combining each record of the subquery ssh with each record of table s2 indiscriminately to generate a temporary result set (number of records = number of ssh records × number of s2 records).

[0029] 3. Final deduplication and output: Perform distinct deduplication on the temporary result set after the Cartesian product concatenation (since ssh has already grouped and deduplicated, the duplicates in the combined result set only come from the multi-record matching of s2). Only columns a and d from the subquery ssh are retained; all fields from table s2 are not output.

[0030] The SQL statement shown in equation (2) belongs to the join scenario with deduplication properties. During the execution process, table s2 was scanned, but the data of table s2 was not output in the end, resulting in a waste of database resources and query delay.

[0031] The aforementioned redundant connection problem is particularly prominent in scenarios involving large-scale data queries and complex business logic, and has become a key bottleneck restricting the improvement of database query performance. There is an urgent need for an optimization scheme that can accurately identify and eliminate such redundant connections.

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

[0033] Step S101: Obtain and parse the database operation statement. In this step, the database operation statement can generally be obtained through user input or automatically generated by the database program during operation due to business needs. Those skilled in the art can decide on the method of obtaining the database operation statement based on the actual situation. Parsing generally involves using techniques such as syntax analysis and semantic analysis to extract key information from the statement, specifically including: tables, subqueries, views, and their interrelationships; the join key (i.e., the field used for association) corresponding to the join operation; the target column of the query (i.e., the data column to be output); the conditions contained in the statement (such as filtering conditions and join conditions); and whether the query has deduplication properties. The purpose of parsing is to transform the structured database operation statement into key feature data that can be used for redundancy detection, laying the foundation for the conditional judgment in step S102.

[0034] Step S102: Determine whether the database operation statement meets the preset redundant connection elimination condition based on the parsing result of the database operation statement. The preset redundant connection elimination condition is used to determine whether there are redundant connections in the database operation statement.

[0035] This step is the core judgment step of the optimization method. It accurately identifies redundant connections through two types of preset conditions. The design logic of the preset redundant connection elimination conditions is: only for connection operations that "do not provide valid data to the query results and do not affect the correctness and uniqueness of the results", to ensure the accuracy of redundancy identification and avoid mistakenly deleting valid connections. Specifically, it can include two optional implementation methods.

[0036] In some optional embodiments, step S102 may generally include: determining whether the database operation statement has an equi-join in the same table and column and the join key is unique; if the database operation statement has an equi-join in the same table and column and the join key is unique, identifying the redundant data table in the database operation statement; determining whether the target column of the database operation statement contains the data column of the redundant data table; if the target column does not contain the data column of the redundant data table, determining that the database operation statement meets the preset redundant join elimination condition.

[0037] This type of judgment condition applies to SQL statements of the type (1), and the specific judgment process is as follows: The first step is to determine whether there is an equi-value join in the same table and column and whether the join key is unique: an equi-value join in the same table and column means that the same field in the same data table (or the data table corresponding to the subquery) is used as the join key in the statement and the join method is equi-value matching (such as ssh.a=sa in formula (1), where the ssh subquery and the s table both correspond to the s1 table and a is the same join key); the join key is unique means that the database field corresponding to the join key has been set with a unique index (such as primary key, unique constraint) to ensure that the value of the field is not repeated, and to ensure from the data characteristics that the join operation will not add new valid data.

[0038] The second step is to identify redundant data tables: Redundant data tables refer to tables (or subqueries, views) that do not provide any data to the target column in the above equi-join of the same table and column, and whose existence does not affect the correctness and uniqueness of the query results. For example, if only the fields of one table are output after the join, and the other table does not contribute any output data, then the table is a redundant data table (such as table s1 in equation (1)).

[0039] The third step is to determine whether the target column contains data columns from the redundant data table: If the target column (the column in the final output) does not contain any data columns from the aforementioned redundant data table (for example, the target column in equation (1) is only ssh.*, excluding the data columns in the s1 table), it indicates that the connection of the redundant data table has not made a substantial contribution to the output result, further verifying its redundancy attribute.

[0040] When all three conditions above are met, the database operation statement is determined to meet the preset redundant connection elimination conditions, and there are redundant connections that can be eliminated.

[0041] In some alternative embodiments, step S102 may further include: determining whether the database operation statement has a target join operation, wherein the join condition of the target join operation is only a filtering condition or no join condition; if the database operation statement has a target join operation, identifying redundant data tables in the database operation statement; determining whether the result set of the parent query of the database operation statement has deduplication properties and whether the target column of the parent query contains data columns of redundant data tables; if the result set of the parent query of the database operation statement has deduplication properties and the target column of the parent query does not contain data columns of redundant data tables, it is also determined that the database operation statement meets the preset redundant join elimination conditions.

[0042] This type of judgment condition applies to SQL statements of the type (2), and the specific judgment process is as follows: This type of condition is used to identify invalid joins that are not related in deduplication queries, and it revolves around two key characteristics: deduplication properties and the absence of valid associations. The first step is to determine if a target join operation exists: A target join operation refers to a join where the join condition is either only a filtering condition or has no join condition. "Only a filtering condition" means that the condition only filters the data of the joined objects themselves (e.g., s2.b=1 only filters data from the s2 table, without involving any association with other tables / subqueries); "No join condition" means that the tables, subqueries, or views are joined only by commas, without setting any equality matching, range matching, or other association conditions (i.e., Cartesian product join).

[0043] The second step is to identify redundant data tables: Redundant data tables here also refer to tables, subqueries, or views that do not provide data to the target columns of the parent query and do not affect the correctness and uniqueness of the query results. The judgment criteria are the same as those for redundant data tables in the first category of conditions.

[0044] The third step is to determine the deduplication property of the parent query and its correlation with the target column: the parent query has the deduplication property, which means that the parent query contains an operation that can deduplicate the result set (for example, the distinct keyword is added in equation (2); the target column of the parent query does not contain the data column of the redundant data table, which means that the redundant data table does not provide any data to the final output (for example, the target column in equation (2) is only ssh.*, excluding the data column in the s1 table).

[0045] When all three conditions above are met, the judgment statement satisfies the preset redundant connection elimination condition, and the connection is an invalid redundant connection.

[0046] Optionally, the steps to determine whether the result set of the parent query of a database operation statement has deduplication properties generally include: determining whether the parent query contains keywords that have deduplication properties; and if the parent query contains keywords that have deduplication properties, determining that the result set has deduplication properties. Examples of keywords that have deduplication properties include operations such as distinct, groupby, union, intersect, and except.

[0047] Step S103: If the determination in step S102 is yes, determine the redundancy type satisfied by the database operation statement.

[0048] Optionally, when a database operation statement contains an equi-join of the same table and column with a unique join key, and the target column does not contain any redundant data columns, the steps to determine the redundancy type satisfied by the database operation statement generally include: determining that the redundancy type satisfied by the database operation statement is an equi-join of the same table and column with a unique key. If the statement satisfies the first type of redundancy elimination condition (equi-join of the same table and column + unique join key + target column does not contain any redundant table columns), then the redundancy type is determined to be an equi-join of the same table and column with a unique key. For example, the SQL statement shown in equation (1) satisfies the first type of redundancy elimination condition, and its redundancy type is determined to be an equi-join of the same table and column with a unique key.

[0049] Optionally, when the result set of the parent query of the database operation statement has deduplication properties and the target column of the parent query does not contain data columns from redundant data tables, the steps to determine the redundancy type satisfied by the database operation statement generally include: determining that the redundancy type satisfied by the database operation statement is a deduplicated irrelevant column redundant join. For example, the SQL statement shown in equation (2) satisfies the above-mentioned second type of redundant join elimination condition, and its redundancy type is determined to be a deduplicated irrelevant column redundant join.

[0050] Step S104: Perform the corresponding redundancy elimination operation on the database operation statement according to the redundancy type.

[0051] When the redundancy type is an equal-value redundant join with a unique key in the same table and column, the steps to perform the corresponding redundancy elimination operation on the database operation statement according to the redundancy type generally include: eliminating redundant data tables and adding constraints corresponding to the join key.

[0052] Optionally, the steps for adding constraints corresponding to the join key generally include: adding a constraint that the join key is not null. The reason for adding a constraint that the join key is not null is that the original statement's equi-join implicitly includes the logic that the join key is not null (null values ​​cannot be matched equally). After removing redundant tables, this constraint needs to be added to ensure that the optimized statement is completely consistent with the query logic of the original statement, and to avoid result deviations due to null values.

[0053] Finally, the optimized SQL statement shown in equation (1) is shown in equation (3): select ssh.* from (select a ,d from s1 where b =1 and a is not nullgroup by a ,d ) ssh formula (3) The optimized SQL statement (3) removes the redundant secondary references and equi-join operations of the s1 table in the original query compared to (1), avoiding invalid join calculations and table scans. Especially when the s1 table has a large amount of data, it reduces one full table scan and join operation, significantly shortening the query response time.

[0054] In the case of redundant joins with irrelevant columns for redundancy removal, the steps for performing the corresponding redundancy removal operation on the database operation statement based on the redundancy type generally include: removing redundant data tables from the database operation statement. Since such redundant joins do not provide data to the target column, and the deduplication nature of the parent query ensures that the join will not change the result, no additional constraints are needed; direct removal is sufficient to guarantee semantic equivalence.

[0055] Finally, the optimized SQL statement shown in equation (2) is shown in equation (4): select distinct ssh.* from (select a ,d from s1 where b =1 group by a,d ) SSH (4) Equation (4) is the final execution statement after optimizing the SQL statement of the original Equation (2). The core is to eliminate meaningless redundant connections, retain the core query logic, and maximize the reduction of database query overhead while ensuring semantic equivalence with the original statement and consistent query results.

[0056] The comparison shows that: Equation (2) requires scanning the s1 table and the redundant table (such as s2) and performing a Cartesian product join, while Equation (4) only scans the s1 table once, without any additional join operations, thus significantly reducing IO and computational overhead. When the original redundant table (such as s2) has a large amount of data, the optimized statement can avoid massive invalid combination calculations, and the query response time is significantly shortened.

[0057] This method effectively reduces the scanning of redundant tables and join operations, optimizes the query execution plan of database operation statements, and ultimately greatly improves query execution performance.

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

[0059] Step S201: Obtain and parse the database operation statement. The parsing process requires techniques such as syntax analysis and semantic extraction to extract the following key information: tables, subqueries, and their inter-table joins (paying particular attention to multiple references to the same table); the join key (the field used for equality matching) and its field attributes (whether it has unique constraints, such as a unique index or primary key); the target column (the column to be output) and its source table; and the join type (whether it is an equi-join, such as an =-matched join condition). The core objective of the parsing is to extract three key features from the statement: "table association," "join key attributes," and "target column source," providing data support for subsequent steps.

[0060] Step S202 checks if the database operation statement contains an equi-join of the same table and column with a unique join key. If step S202 determines otherwise, the optimization operation ends. An equi-join of the same table and column means that the same data table (or the data table corresponding to a subquery) is referenced multiple times in the statement, and the join condition is an equality match of the same field. A unique join key means that the field used for the equi-join (join key) has a unique constraint set in the database, such as a primary key constraint or a unique index constraint, ensuring that all non-null values ​​of this field are unique.

[0061] An optional SQL example is shown in equation (5): select ssh.* from (select a ,d from s1 where b =1 group by a ,d ) ssh,s1 s where ssh.a =sa Formula (5) Column A of table S1 is pre-set to be unique.

[0062] The execution logic of the SQL statement shown in equation (5) is to first filter the s1 table (b=1) through a subquery and group it by a and d to remove duplicates, then connect the subquery result with the secondary reference of the s1 table through column a, and finally output only the data of columns a and d of the subquery.

[0063] Analysis reveals that the core redundancy in this statement lies in the fact that the double scan of the same table and the equi-join are meaningless, specifically manifested as follows: Redundant repeated scans: The subquery has already completed the scan, filtering and deduplication of table s1. Subsequent references to table s1 and full table scans are invalid and redundant I / O overhead. Redundancy in join operations: If the join key 'a' is unique (e.g., a unique index is set), then the join 'ssh.a=sa' will not add new valid data. In the subquery, group by a,d already ensures that the value of column a uniquely corresponds to the value of column d. The second join will only copy the original records and cannot add new data. No contribution to output: The secondary reference table s does not provide any column data to the final output. The join operation only increases the database connection calculation overhead and does not change the output result.

[0064] Step S203: If the determination in step S202 is yes, identify redundant data tables in the database operation statement. Redundant data tables here specifically refer to tables (or subqueries, views) that, in equi-joins of the same column within the same table, do not provide any valid data to the target column of the query, and whose existence does not affect the correctness and uniqueness of the query results.

[0065] The process of determining this generally involves: first, identifying which tables / subqueries the target columns (the columns in the final output) come from; and second, determining whether each table / subquery in the same table join provides data for the target columns. If a table / subquery does not contribute any target columns, and the join operation it participates in does not change the validity of the result set (because the join key is unique), then that table / subquery is a redundant data table.

[0066] Example: If the target column comes only from the subquery ssh, and the joined table s (with the same table alias) does not provide any output columns, then table s is a redundant data table.

[0067] Step S204: Does the target column of the database operation statement contain data columns from a redundant data table? If step S204 determines yes, the optimization operation ends. Each target column is checked to determine if any column comes from the redundant data table identified in step S203. If the target column contains columns from the redundant data table, it means that the table provides valid data for the output and is not truly redundant; the optimization operation ends directly. If the target column does not contain any columns from the redundant data table, it means that the table has no actual contribution and can be safely eliminated.

[0068] In step S205, if the determination in step S205 is negative, the redundancy type satisfied by the database operation statement is determined to be equi-redundant join with a unique key in the same table and column. This step can only be executed if step S202 (equi-redundant join with a unique key in the same table and column) is determined to be positive and step S204 (the target column does not contain redundant table columns) is determined to be negative.

[0069] Step S206: Eliminate redundant data tables and add constraints corresponding to the join keys.

[0070] Specific steps: Remove all references and related operations to redundant data tables in the statement, including aliases for redundant tables, join conditions related to the table, and filter conditions (if only filtering is applied to redundant tables and does not affect the core results).

[0071] Let's take the SQL statement shown in equation (5) as an example: In the original statement, the part "s1 s where ssh.a = sa" is deleted because s1 s is a redundant data table. Only the subquery ssh-related logic is retained. Then, the constraint condition corresponding to the join key is added: Since the equi-join of the original statement (such as ssh.a=sa) implicitly assumes that the join key is not empty (empty values ​​cannot be matched), the original join will automatically filter out records with empty join keys; after removing the redundant data table, this implicit logic disappears, and the constraint condition needs to be explicitly added to ensure that the optimized statement is consistent with the empty value handling logic of the original statement and avoid result deviation. Therefore, the constraint condition needs to be added to the where clause of the subquery.

[0072] When the query results satisfy uniqueness, performing an equi-join between the outer parent query and a subset of the tables in the subquery will not introduce any additional data changes or filtering effects, and is considered a redundant join operation. In this case, such joins can be safely eliminated to reduce unnecessary calculations and scans.

[0073] The final optimized statement is shown in equation (6): select ssh.* from (select a ,d from s1 where b =1 and a is not nullgroup by a ,d ) ssh formula (6) This example shows that the content of table s1 in the parent query will not appear in the result set, and it satisfies the condition that the column in the same table is unique and that the result set does not depend on the content generated by the join. Therefore, table s1 in the parent query can be eliminated, and the join can be eliminated. Since the parent query uses an equi-join, it means that the join key is not null. So, after eliminating redundant joins, in order to ensure semantic consistency, the constraint that the join key is not null should be applied in the subquery.

[0074] After optimization, the number of table scans in the SQL statement shown in Equation (6) is reduced from 2 (the original statement repeatedly scans the same table) to 1, which greatly reduces IO overhead; meaningless equi-join operations are removed, reducing the database connection computing resources occupied and shortening the query execution chain; and by supplementing the join key non-null constraint, it is ensured that the optimized statement is completely consistent with the query logic and output results of the original statement, without any data deviation.

[0075] Ultimately, this method can effectively reduce the scanning of redundant tables, reduce join operations, optimize SQL query execution plans, and improve query execution performance.

[0076] Figure 3 This is a flowchart illustrating a method for optimizing database operation statements according to yet another embodiment of the present invention, as shown below. Figure 3 As shown, the optimization method for the database operation statement includes at least the following steps S301 to S307.

[0077] Step S301: Obtain and parse the database operation statement. The parsing process requires techniques such as syntax analysis and semantic extraction to extract the following key information: tables, subqueries, and their inter-table joins (paying particular attention to multiple references to the same table); the join key (the field used for equality matching) and its field attributes (whether it has unique constraints, such as a unique index or primary key); the target column (the final output column) and its source table; and the join type (whether it's an equi-join, such as an =-matched join condition). The core objective of parsing is to extract three key features from the statement: "table association," "join key attributes," and "target column source," providing data support for subsequent steps.

[0078] An optional SQL example is shown in equation (7): select distinct s1.* from s1 , (select s2a ,s2d from s2 where s2b =1group by a ,d ) v where s1.a =3 Equation (7) The core logic of the SQL statement shown in equation (7) is as follows: First, perform filtering (s2b=1) and grouping deduplication (group by a,d) on table s2 to generate a subquery result set v. Then, perform an implicit join (Cartesian product) between table s1 and subquery v without any association. After filtering out the records with s1.a=3, remove duplicates and finally output all column data of table s1.

[0079] When a query contains any joins with meaningless join conditions, and the result set of the parent query does not depend on the data generated by the join and has deduplication properties, the join operation in the parent query is redundant. Currently, it first scans the s1 and v subqueries separately, and then joins them, leading to reduced database query efficiency. To address this, if the condition is met, the join operation in the parent query should be eliminated to reduce unnecessary table scans and thus improve query performance.

[0080] The subsequent steps are illustrated using the SQL statement shown in equation (7) as an example.

[0081] Step S302: Determine whether the database operation statement contains a target connection operation. The connection condition for the target connection operation is either a filter condition or a no-connection condition.

[0082] Where there is no join condition, it means that tables, subqueries, or views are implicitly joined only by commas, without any equality matching (such as =), range matching (such as >) or other association conditions (i.e., Cartesian product join); where there is only a filter condition, it means that the join condition only filters the data of a certain join object itself, without involving the association with other join objects, and the execution of the filter condition does not depend on the data of other tables / subqueries (such as s1.a=3 in equation (7), which only filters the data of table s1 and has no association with subquery v). If there is no such condition (such as all joins being valid equi-joins), it means that the statement has no such redundancy, and the optimization operation ends directly.

[0083] In equation (7), the s1 table and the subquery v are implicitly joined by a comma, and the join condition is only s1.a=3 (only the data in the s1 table is filtered, and it has no relation to the subquery v), which meets the definition of the target join operation. Therefore, step S302 is determined to be yes.

[0084] Step S303: If the determination in step S302 is yes, identify redundant data tables in the database operation statement. Redundant data tables refer to tables, subqueries, or views that do not provide any valid data to the target columns of the parent query in the target join operation, and whose existence does not affect the correctness and uniqueness of the query results.

[0085] The target column of equation (7) is s1.* (all columns of table s1). Subquery v does not provide any data to the target column, and the existence of v is not related to the filtering logic of s1.a=3. Therefore, subquery v or table s2 is determined to be a redundant data table.

[0086] Step S304: Determine whether the result set of the parent query has deduplication properties. A parent query result set having deduplication properties means that the parent query contains operations that can remove duplicates from the final result set. Specifically, these operations include, but are not limited to: distinct operations, groupby operations, min / max / agg (distinct) aggregate functions, and UNION / MINUS / INTERSECT operations without the ALL keyword.

[0087] If any of the above deduplication operations exist in the parent query, the result set is determined to have deduplication properties, and the process proceeds to the next step; if none exist, it indicates that redundant connections may affect the uniqueness of the result set (e.g., the duplicate records caused by Cartesian product connections cannot be canceled), and the optimization operation is terminated directly to avoid result deviation.

[0088] The parent query in equation (7) includes a distinct operation, which explicitly removes duplicates from the output results. Therefore, step S304 is determined to be true.

[0089] Step S305: If the determination in step S304 is yes, determine whether the target column of the parent query contains data columns from the redundant data table. Based on the parsing result of step S301, check each target column of the parent query to determine whether any column comes from the redundant data table determined in step S303: If the target column contains columns from the redundant data table, it means that the object provides valid data for the result output and is not truly redundant, so the optimization operation ends directly; if the target column does not contain any columns from the redundant data table, it means that the object has no actual data contribution and can be safely eliminated.

[0090] The target column of equation (7) is s1.*, which all come from table s1 and does not contain any columns (s2a, s2d) of the redundant subquery v. Therefore, step S305 is determined to be no.

[0091] Step S306: If the determination in step S305 is negative, determine that the redundancy type satisfied by the database operation statement is redundant join of irrelevant columns.

[0092] Step S307: Eliminate redundant data tables in the database operation statement. This step can only be executed if step S302 (a target join operation exists) and step S304 (the parent query has deduplication properties) are determined to be yes, and step S305 (the target column does not contain redundant table columns) is determined to be no.

[0093] Taking equation (7) as an example, the final optimized result of the SQL statement shown in equation (7) is shown in equation (8): select distinct s1.* from s1 where s1.a =3 Formula (8) The core logic of the SQL statement shown in Equation (8) is to perform a scan, condition filtering, and result deduplication only on table s1 to directly obtain the target data that meets the business requirements. There are no extra redundant tables, subqueries, or join operations. Compared with the SQL statement shown in Equation (7), the execution efficiency is significantly improved.

[0094] This method can significantly reduce resource overhead: by eliminating redundant data tables, the scanning of redundant tables / subqueries (such as the scanning of subquery v in equation (7)) and the calculation of invalid joins (such as Cartesian product combination) are avoided, and the IO overhead and computation overhead are significantly reduced.

[0095] The flowchart provided in this embodiment is 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.

[0096] 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.

[0097] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 4 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 5 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 6 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 operation 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 operation 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.

[0098] 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.

[0099] 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.

[0100] 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.

[0101] 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.

[0102] 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.

[0103] 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 operation statements, comprising: Obtain and parse the database operation statement; Based on the parsing result of the database operation statement, it is determined whether the database operation statement meets the preset redundant connection elimination condition. The preset redundant connection elimination condition is used to determine whether there are redundant connections in the database operation statement. If the database operation statement satisfies the preset redundant connection elimination condition, determine the redundancy type satisfied by the database operation statement. Perform the corresponding redundancy elimination operation on the database operation statement according to the redundancy type.

2. The method for optimizing database operation statements according to claim 1, wherein, The step of determining whether the database operation statement meets the preset redundant connection elimination condition based on the parsing result of the database operation statement includes: Determine whether the database operation statement contains an equi-join of the same column in the same table and the join key is unique; If the database operation statement contains equi-joins in the same table and column and the join key is unique, then the redundant data table in the database operation statement is identified. Determine whether the target column of the database operation statement contains data columns from the redundant data table; If the target column does not contain any data columns from the redundant data table, the database operation statement is determined to satisfy the preset redundant connection elimination condition.

3. The method for optimizing database operation statements according to claim 2, wherein, The step of determining the redundancy type satisfied by the database operation statement includes: when the database operation statement has an equi-value join in the same table and column and the join key is unique, and the target column does not contain the data column of the redundant data table, the redundancy type satisfied by the database operation statement is determined to be an equi-value redundant join with a unique key in the same table and column. When the redundancy type is the same-column unique key equivalent redundancy join, the step of performing the corresponding redundancy elimination operation on the database operation statement according to the redundancy type includes: eliminating the redundant data table and adding the constraint condition corresponding to the join key.

4. The method for optimizing database operation statements according to claim 3, wherein, The step of adding the constraint conditions corresponding to the connection key includes: Add a constraint that the connection key is not empty to the connection key.

5. The method for optimizing database operation statements according to claim 1, wherein, The step of determining whether the database operation statement meets the preset redundant connection elimination condition based on the parsing result of the database operation statement includes: Determine whether the database operation statement contains a target connection operation, wherein the connection condition of the target connection operation is only a filter condition or no connection condition; If the target connection operation exists in the database operation statement, identify the redundant data table in the database operation statement; Determine whether the result set of the parent query of the database operation statement has the property of deduplication and whether the target column of the parent query contains the data column of the redundant data table; If the result set of the parent query of the database operation statement has deduplication properties and the target column of the parent query does not contain the data columns of the redundant data table, then the database operation statement is determined to satisfy the preset redundant connection elimination condition.

6. The method for optimizing database operation statements according to claim 5, wherein, The step of determining the redundancy type satisfied by the database operation statement includes: when the result set of the parent query of the database operation statement has deduplication properties and the target column of the parent query does not contain the data column of the redundant data table, the redundancy type satisfied by the database operation statement is determined to be deduplication irrelevant column redundant join. When the redundancy type is the deduplication irrelevant column redundant join, the step of performing the corresponding redundancy elimination operation on the database operation statement according to the redundancy type includes: eliminating the redundant data table in the database operation statement.

7. The method for optimizing database operation statements according to claim 5, wherein, The step of determining whether the result set of the parent query of the database operation statement has the property of deduplication includes: Determine whether the parent query contains keywords with the deduplication property. If the parent query contains keywords that have the deduplication property, the result set is determined to have the deduplication property.

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 database operation statements 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 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, when executing the machine-executable program, implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 7.

Images