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 a database operation statement meeting a preset condition; a composite predicate contained in a conditional clause of the target sub-query is extracted, the conditional clause is a clause corresponding to the WHERE keyword, and the composite predicate is formed by connecting at least two atomic conditions through logic or operators; converting the target sub-query based on the composite predicate to obtain a combined query statement, wherein the combined query statement is formed by combining a plurality of independent query statements; and replacing the target sub-query in the database operation statement with the combined query statement to obtain an optimized database operation statement. Through the method, the condition of each independent query can be purer, the limitation of original mixed conditions is eliminated, and the index hit probability and the partitioning / fragmenting cutting effect are greatly improved, so that the execution efficiency of database query and the decision credibility of an optimizer are stably improved on the basis of reducing the implementation cost.

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 field of database query processing, existence and membership checks are frequently used scenarios. These requirements are often achieved through subqueries corresponding to operators such as IN, EXISTS, ANY, and ALL. Their core purpose is to determine whether the target data exists, rather than focusing on data duplication. The WHERE clause of such subqueries often contains a compound OR predicate composed of equi-join conditions and range filtering conditions, which is an important part of the database query logic.

[0003] However, traditional database optimizers have significant technical limitations when handling subqueries containing OR compound predicates. On the one hand, optimizers struggle to accurately estimate the selectivity of mixed conditions resulting from OR combinations, leading to low reliability of cost model calculations and failing to provide a reliable basis for execution plan selection. On the other hand, OR conditions can cause equality and range conditions to become intertwined, preventing optimizers from effectively pushing filtering logic down to the data index level. Consequently, it becomes difficult to invoke efficient optimization schemes such as hashjoin and indexscan, forcing them to adopt general but inefficient execution strategies. Furthermore, during the join optimization phase, the mixed condition patterns prevent the planner from matching the optimal join strategy, further limiting query performance.

[0004] These defects directly lead to an increase in the amount of invalid data scanned and insufficient index utilization during query execution, ultimately resulting in increased execution overhead. This performance bottleneck is particularly prominent in large-scale data storage or complex query scenarios, severely impacting the overall query efficiency of the database. 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 improve the optimizability of database operation statements.

[0007] Specifically, this invention provides a method for optimizing database operation statements, comprising: Get database operation statements that meet preset conditions. The preset conditions are that the database operation statement contains a target subquery, the query result of the target subquery is only used for existence or membership judgment, the target subquery only performs projection operation and predicate filtering operation, and there is no upper-level semantics in the target subquery that affects the result set. Extract the compound predicates contained in the conditional clauses of the target subquery. The conditional clauses are the clauses corresponding to the WHERE keyword. The compound predicates are composed of at least two atomic conditions connected by a logical OR operator. The target subquery is transformed based on the compound predicate to obtain a combined query statement, which is formed by merging multiple independent query statements. By replacing the target subquery in the database operation statement with a combined query statement, an optimized database operation statement can be obtained.

[0008] Optionally, the steps of transforming the target subquery based on the compound predicate to obtain the combined query statement include: Determine the atomic conditions in a compound predicate; Each atomic condition is converted into an independent query statement. The independent query statement is consistent with the projection operation of the target subquery and only contains its own corresponding atomic condition as the filtering condition. By merging non-duplicate keywords, independent query statements are combined to obtain combined query statements. Merging non-duplicate keywords is used to combine the result sets of each independent query statement without deduplication.

[0009] Optionally, the keyword to be merged without deduplication includes: UNION ALL.

[0010] Optionally, the target subquery includes: IN subquery, NOT IN subquery, EXISTS subquery, NOT EXISTS subquery, ANY type subquery, NOT ANY type subquery, NOT ALL type subquery, and the subqueries corresponding to equivalent semi-joins and anti-semi-joins.

[0011] Optionally, the operators involved in ANY type subqueries include EXISTS, IN, <ANY、<=ANY、=ANY、> ANY, >=ANY, !=ANY, <>ANY; The operators involved in NOT ANY type subqueries include NOT EXISTS, NOT IN, and NOT EXISTS. <ANY、NOT<=ANY、NOT=ANY、NOT> ANY, NOT>=ANY, NOT!=ANY, NOT<>ANY; The NOT operator is used in NOT ALL type subqueries. <ALL、NOT<=ALL、NOT=ALL、NOT> ALL, NOT>=ALL, NOT!=ALL, NOT<>ALL.

[0012] Optionally, after obtaining the optimized database operation statement, the steps may also include: Set a timeout threshold for each individual query in the optimized database operation statement; If any independent query exceeds its corresponding timeout threshold, the execution of the independent query will be terminated and an empty result set will be returned, while an error log will be generated. Continue executing other independent query statements.

[0013] Optionally, after obtaining the optimized database operation statement, the steps may also include: Determine the target optimization method supported by each independent query statement in the optimized database operation statement. The target optimization method is used to improve the query performance of each independent query statement individually. The query operation is performed according to the target optimization method supported by each independent query statement, resulting in multiple independent query results; The results of multiple independent queries are merged, and other operations are performed on the database operation statement.

[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 the computer program, when executed by a processor, implements the steps of the optimization method for database operation statements described above.

[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 optimization method for database operation statements of the present invention involves obtaining database operation statements that meet preset conditions. These preset conditions include the presence of a target subquery within the database operation statement, the subquery's query result being used solely for existence or membership checks, the subquery performing only projection and predicate filtering operations, and the absence of any upper-level semantics affecting the result set within the target subquery. The method further involves extracting compound predicates from the conditional clauses of the target subquery. The conditional clauses are clauses corresponding to the WHERE keyword, and the compound predicates consist of at least two atomic conditions connected by a logical OR operator. Based on the compound predicates, the target subquery is transformed to obtain a combined query statement, which is formed by merging multiple independent query statements. Finally, the combined query statement replaces the target subquery in the database operation statement, resulting in the optimized database operation statement. This method allows for purer conditions in each independent query, eliminating the limitations of the original mixed conditions and significantly improving the index hit probability and partition / shard pruning effect. Combined query statements support parallel execution of each branch, reducing the overall time of a single query. Furthermore, each branch performs cost estimation independently, resulting in more reliable results. This enables the optimizer to match the optimal execution strategy, ultimately improving the execution efficiency of database queries and the reliability of optimizer decisions while reducing implementation costs.

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

[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 an alternative example of database operation statements. SQL statements have multiple functions such as data manipulation and data definition. This language is interactive and can provide users with great convenience. In specific query processing, the join operation of SQL statements is the core means to realize multi-table data association and complex logical queries, but some SQL statements have room for optimization.

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

[0024] Step S101: Obtain database operation statements that meet preset conditions.

[0025] The core of this step is to accurately select database operation statements with optimization value. The acquisition method can be flexibly adapted to the actual scenario, including but not limited to user-inputted query statements and execution statements automatically generated by the database program based on business needs. Those skilled in the art can choose according to the application scenario.

[0026] Among these, the preset conditions are a key prerequisite for ensuring the effectiveness of optimization, and must simultaneously meet the following requirements: the database operation statement contains a target subquery; the query result of the target subquery is only used for existence or membership checks (e.g., judgment logic implemented through operators such as IN, EXISTS, ANY, ALL, etc.); the target subquery only performs projection and predicate filtering operations. Projection operations generally refer to operations that filter required fields or fixed constant values, such as SELECT 1 or SELECT a; predicate filtering operations refer to filtering data rows that meet the requirements based on conditions, such as t1.a=t2.a or t1.a<10; and it does not contain upper-level semantics that affect the result set. Upper-level semantics that affect the result set refer to operations that change the form of the subquery result set (number of rows, data content, data attributes), including but not limited to aggregate functions (COUNT / SUM / AVG / MIN / MAX), DISTINCT for deduplication, window functions (ROW_NUMBER / OVER, etc.), LIMIT / OFFSET for row number limits, GROUP BY for grouping, etc. Such operations will cause the semantics of the split result set to be inconsistent with the original result set, and therefore are excluded from the scope of application.

[0027] Specifically, target subqueries can generally include: IN subqueries, NOT IN subqueries, EXISTS subqueries, NOTEXISTS subqueries, ANY type subqueries, NOT ANY type subqueries, NOT ALL type subqueries, as well as the subqueries corresponding to equivalent semi-joins and anti-semi-joins.

[0028] The operators involved in ANY type subqueries are EXISTS, NOT EXISTS, IN, NOT IN, (NOT)(<, <=, =, >, >=, !=, <>=)ANY; the operators involved in ALL type subqueries are NOT(<, <=, =, >, >=, !=, <>=)ALL. The characteristic of these operators is that they do not care about duplicate data, but only focus on existence.

[0029] Step S102: Extract the compound predicates contained in the conditional clauses of the target subquery. The conditional clause is the clause corresponding to the WHERE keyword, and a compound predicate consists of at least two atomic conditions connected by a logical OR operator. Compound predicates generally need to satisfy the following characteristics: 1. It is formed by connecting at least two atomic conditions through the logical OR operator, for example, P1 OR P2 OR ... OR Pk (k≥2, k is a positive integer); 2. Each atomic condition can execute filtering logic independently, without relying on other atomic conditions to complete data filtering, such as equi-join conditions (t1.a=t2.a), range filtering conditions (t1.a<10, t2.b≥20), etc. In some optional embodiments, the abstract syntax tree of the target subquery can be traversed by a database syntax parser to locate the OR operator in the WHERE clause and break it down into individual atomic conditions, thereby ensuring the accuracy and completeness of the extracted results.

[0030] Step S103: The target subquery is transformed based on the compound predicate to obtain a combined query statement. The combined query statement is formed by merging multiple independent query statements.

[0031] Since the target subquery is only used for existence or membership checks, and its result set is used to determine whether there is data that meets the conditions, the result set corresponding to "P1 OR P2 OR ... OR Pk" is completely equivalent to "(result set corresponding to P1) UNION ALL (result set corresponding to P2) ... UNION ALL (result set corresponding to Pk)". The existence check naturally eliminates the impact of data duplication, and no additional deduplication logic is needed. Therefore, step S103 generally includes: determining the atomic conditions in the compound predicate, for example, splitting "t1.b=t2.b OR t1.a<10" into atomic conditions P1 (t1.b=t2.b) and P2 (t1.a<10); converting each atomic condition into an independent query statement, ensuring that the projection operation of the independent query statement is consistent with that of the target subquery, and only including its own corresponding atomic condition as the filtering condition; combining the independent query statements by merging the non-duplicate keywords to obtain the combined query statement. The core function of merging the non-duplicate keywords here is to completely merge the result sets of each independent query without removing duplicate data, which can ensure the equivalence of the results and avoid the additional execution overhead caused by deduplication. The non-duplicate keywords generally refer to the UNION ALL keyword. UNION ALL is used to combine the result sets of two or more SELECT statements. Unlike UNION, UNION ALL includes all matching rows, even duplicate rows.

[0032] An optional SQL example is shown in equation (1): Select * from t1 where exists(select 1 from t2 where t1.a=t2.a ort1.a<10) Formula (1) In the SQL statement shown in equation (1), the target subquery is `select 1 from t2 where t1.a=t2.a or t1.a<10`. The conditional clause of the target subquery is `where t1.a=t2.a or t1.a<10`, where the compound predicate is `t1.a=t2.a or t1.a<10`. By extraction, the atomic conditions are determined to include `t1.a=t2.a` and `t1.a<10`. Then, each atomic condition is converted into an independent query statement: Independent query statement 1: select 1 from t2 where t1.a=t2.a (corresponding to atomic condition P1); Independent query statement 2: select 1 from t2 where t1.a<10 (corresponding to atomic condition P2); The combined query statement is (select 1 from t2 where t1.a=t2.a) union all(select 1 from t2 where t1.a<10), which is semantically equivalent to the existence check of the original target subquery.

[0033] Step S104: Replace the target subquery in the database operation statement with a combined query statement to obtain the optimized database operation statement.

[0034] This step directly replaces the target subquery to be optimized in the original database operation statement with the combined query statement generated in step S103, completing the entire optimization process. After the replacement, the optimized database operation statement can fully leverage the optimization potential of each independent query statement (such as higher index hit rate and more accurate cost estimation), and because the existence check naturally eliminates data duplication, no additional deduplication filtering logic is needed.

[0035] Taking the SQL statement shown in equation (1) as an example, the target subquery in equation (1) is replaced with the combined query statement transformed in step S103. The final optimized statement after replacement is shown in equation (2): Select * from t1 where exists((select 1 from t2 where t1.a=t2.a)union all (select 1 from t2 where t1.a<10) Formula (2) After the replacement, the optimized database operation statements have multiple performance improvements: 1. Significantly improved index hit probability: Each independent query has purer conditions (containing only a single atomic condition), allowing the optimizer to effectively push filtered conditions to the index, avoiding index failure issues caused by mixed OR conditions; more accurate cost estimation: 2. Each independent query branch can be individually estimated (including scan data volume, I / O overhead, CPU overhead, etc.), and the total cost after aggregation is closer to the actual execution cost, solving the defect of inaccurate estimation of the selection rate of traditional OR conditions; 3. Supports parallel execution: The independent query branches in the combined query statement can be scheduled and executed in parallel through the database's multi-threaded or multi-process mechanism, which greatly reduces the overall query time; 4. Optimized partitioning / sharding effect: Independent atomic conditions can accurately trigger partitioning or sharding of the dataset, scanning only matching data partitions / shards and reducing the amount of invalid data scanned; 5. Avoid additional overhead: No need to add deduplication logic (existence judgment naturally eliminates duplication), avoiding the execution overhead and cost estimation errors caused by deduplication in traditional solutions, while reducing inefficient operations such as bitmap OR merging, predicate re-checking and a large number of table lookups.

[0036] In some optional embodiments, after the optimized database operation statement is obtained in step S104, an execution fault tolerance mechanism can be further added: a timeout threshold is set for each independent query statement in the optimized database operation statement. This threshold can be dynamically adjusted according to the actual application scenario (e.g., set to 1 second, 3 seconds, or 5 seconds based on data volume, network environment, hardware performance, etc.). Those skilled in the art can choose an appropriate threshold. When any independent query statement exceeds its corresponding timeout threshold, the execution of the independent query statement is terminated and an empty result set is returned, while error record information is generated. Other independent query statements continue to be executed. This optional embodiment addresses the scenario where a single branch execution timeout may occur in the database query, thereby ensuring query robustness: avoiding the entire combined query statement from being stuck in an infinite loop or having excessive execution time due to a certain independent query statement, which could cause it to stall or even fail. At the same time, the error record information provides developers with a clear basis for anomaly location, reducing operation and maintenance costs. Furthermore, while a single branch returns an empty result set after timeout, other branches can still execute normally and provide existence judgment basis, ensuring the validity of the overall query results.

[0037] The main technical effects that the solution of this invention can achieve include: 1. Improve query optimizability and index hit rate: Break down complex OR compound predicates into multiple logically pure independent query branches. Each branch contains only a single atomic condition (equi-join or range filtering condition), avoiding the "lack of direction" caused by the original mixed OR conditions. This allows the optimizer to effectively push filtering conditions to the index and accurately call efficient optimization schemes such as hashjoin and indexscan, greatly improving index utilization efficiency.

[0038] 2. More accurate and reliable cost estimation: Each independent query branch can be individually estimated for the selectivity of the filtering conditions (including the amount of data scanned, I / O overhead, CPU overhead, etc.), and then the total cost of the combined query is obtained by summarizing or merging. Compared with the traditional solution's one-time fuzzy estimation of mixed OR conditions, the result is closer to the actual execution cost, providing a reliable basis for the optimizer to select the optimal execution plan.

[0039] 3. Supports parallel execution and partition / shard pruning optimization: The independent branches of the combined query are combined by UNION ALL, which can be used to achieve parallel scheduling and execution with the help of the database's multi-threading or multi-process mechanism, shortening the overall query time; at the same time, independent atomic conditions can accurately trigger partition or shard pruning of the data set, scanning only the partition / shard data that matches the conditions, avoiding invalid data scanning, and further reducing execution overhead.

[0040] 4. Avoid redundant operations and additional overhead: Since the target subquery is only used for existence / membership judgment, duplicate data will be naturally eliminated. There is no need to add additional filtering conditions as in traditional splitting schemes, which completely avoids the execution overhead and cost estimation error caused by deduplication. At the same time, it reduces inefficient operations such as complex bitmap OR merging, predicate re-checking and a large number of table lookups, simplifying the execution process.

[0041] 5. Low implementation cost and strong generalization: This solution does not require complex system modifications, and has low development and deployment costs; it is also compatible with various database scenarios that support IN / EXISTS / ANY / ALL subqueries and semi-joins and anti-semi-joins, with strong cross-database compatibility.

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

[0043] Step S201: Obtain database operation statements that meet preset conditions. The core purpose of this step is to identify database operation statements with optimization value and avoid ineffective optimization.

[0044] The preset conditions must simultaneously meet four requirements: 1. A target subquery exists: The database operation statement contains a subquery module; 2. Subquery results are only used for existence or membership checks: The core purpose of a subquery is to determine whether data exists (e.g., EXISTS) or belongs to a set (e.g., IN), without considering duplicate data. 3. Subqueries only perform projection and predicate filtering operations: Projection operations generally refer to filtering a single field (such as SELECT a) or fixed constant values ​​(such as SELECT 1), while predicate filtering operations generally refer to filtering data through equi-joins (such as t1.a=t2.a) or range filtering (such as t1.a<10), without complex processing logic; 4. No upper-level semantics affecting the result set: Exclude aggregate functions (COUNT / SUM, etc.), DISTINCT for deduplication, window functions, GROUP BY for grouping, LIMIT for row count limits, table joins (JOIN), and other operations that would change the form of the result set (number of rows, data content, data attributes), ensuring the semantic equivalence of subsequent splits.

[0045] Step S202: Extract the compound predicates contained in the conditional clauses of the target subquery. The conditional clauses specifically refer to the logical clauses in the target subquery guided by the WHERE keyword; a compound predicate is formed by connecting at least two atomic conditions that can independently perform filtering through a logical OR operator (generally denoted as P1 OR P2 OR ... OR Pk, where k≥2 and is a positive integer). Each atomic condition can independently perform data filtering without depending on other conditions. For example, "t1.b=t2.bOR t1.a<10" is a compound predicate composed of two atomic conditions.

[0046] Step S203: Determine the atomic conditions in the compound predicate. The atomic conditions must meet the principle of independent execution, that is, they can complete the data filtering without relying on other conditions, and the filtering logic is simple, generally equi-join or range filtering.

[0047] The determination process typically involves semantically decomposing the compound predicate using parsing tools, removing the OR operator, and then verifying each remaining condition against the principle of independent execution: if a condition contains no nested OR, AND, or other logical operators (or if nested operators constitute independent filtering logic), it is determined to be an atomic condition. For example, in the compound predicate (t1.a=t2.aAND t1.b=t2.b) OR t1.a<10, although t1.a=t2.a AND t1.b=t2.b contains the AND operator, the whole condition constitutes independent filtering logic and is still determined to be an atomic condition, ensuring the logical purity of each branch after decomposition.

[0048] Step S204 converts each atomic condition into an independent query statement. This step is the core implementation of the decomposition optimization. By constructing an independent query for each atomic condition, the optimizer can accurately match the optimal execution strategy for a single logic.

[0049] An example of an optional transformation is shown in equation (3): Select * from t1 where a in(select a from t2 where t1.b=t2.b or t1.a<10) Formula (3) In the SQL statement shown in equation (3), the original target subquery is select a from t2 where t1.b=t2.b t1.a<10. After splitting, two atomic conditions P1 (t1.b=t2.b) and P2 (t1.a<10) are obtained, which generate two independent query statements after transformation: Independent query statement 1: select a from t2 where t1.b=t2.b (corresponding to P1); Independent query statement 2: select a from t2 where t1.a<10 (corresponding to P2).

[0050] Similarly, the transformation of ANY and ALL type subqueries follows the above logic. For example, the original ANY subquery select a from t2 where t1.b=t2.b or t1.a<10 will still maintain the independent query form with consistent projection and unique filtering conditions after transformation.

[0051] Step S205 involves combining independent query statements using a non-duplicate keyword to obtain a combined query statement. The core reason for choosing a non-duplicate keyword is that the target subquery is only used for existence checks, and duplicate data will be naturally eliminated without additional deduplication. In this embodiment, the keyword is preferably UNION ALL because UNION ALL can completely merge the result sets of each independent query without removing duplicate data, making it completely equivalent to the result set of the original OR condition; and compared to UNION (which has built-in deduplication logic), UNION ALL does not require deduplication calculations, avoiding additional overhead such as sorting and comparison.

[0052] All the independent query statements generated in step S204 are sequentially connected using the UNION ALL operator to form a combined query statement. For example, the two independent queries above are combined as follows: (select a from t2 where t1.b=t2.b)union all (select a from t2 where t1.a<10), which is completely consistent with the existence judgment semantics of the original target subquery.

[0053] Step S206: Replace the target subquery in the database operation statement with a combined query statement to obtain the optimized database operation statement.

[0054] Taking the SQL statement shown in equation (3) as an example, the target subquery to be optimized in the original database operation statement is directly replaced with the combined query statement generated in step S205, without modifying other logic of the original statement. The final result is shown in equation (4): Select * from t1 where a in ((select a from t2 where t1.b=t2.b) unionall (select a from t2 where t1.a<10)) Formula (4) After this replacement, the original mixed OR conditions were split into logically pure independent branches, which solved the core defect in the traditional solution that the OR conditions caused the optimizer to be unable to perform precise tuning.

[0055] Step S207: Determine the target optimization method supported by each independent query statement in the optimized database operation statement.

[0056] This step achieves precise branch-level tuning by matching a dedicated optimization method to each independent query, further amplifying the effect of split optimization. The target optimization method refers to a specific technical solution supported by the database optimizer that can individually improve the performance of a particular query statement, and its scope includes, but is not limited to: Index-related optimizations: indexscan, indexseek, etc., suitable for scenarios where the atomic condition is an indexed field; Join strategy optimization: hashjoin, nested loopjoin, etc., are suitable for scenarios where there is a join relationship between the subquery and the outer query; Data scanning optimization: partition pruning, fragment pruning, etc., suitable for scenarios where data is stored by partition / fragment; Other optimizations include predicate pushdown enhancement.

[0057] The core characteristics of independent query statements are then analyzed to determine the optimal optimization method. These include the type of filtering conditions (equality / range), whether the fields involved are indexed, the data storage format (partitioned / non-partitioned), and the join relationship with the outer query. Based on these characteristics, suitable target optimization methods are selected, for example: If the filtering condition of an independent query is an equi-join of indexed fields (such as t1.a=t2.a), then the index scan optimization will be matched. If the independent query involves data stored in partitions, and the atomic condition is a range filter related to the partition key (such as t2.date≥'2024-01-01'), then the matching partition pruning optimization + hashjoin optimization will be used.

[0058] Step S208: Perform query operations according to the target optimization method supported by each independent query statement to obtain multiple independent query results.

[0059] This step maximizes the execution efficiency of individual branches by applying a dedicated optimization method to each independent query. Each independent query statement can be executed in parallel using the database's multi-threading / multi-process mechanism (e.g., each independent query is assigned its own execution thread) without interference. During execution, each independent query strictly follows the target optimization method determined in step S207. For example, an independent query optimized using indexscan directly locates data through the index, avoiding a full table scan; an independent query optimized using partition pruning only scans partition data that matches the atomic conditions, reducing unnecessary scans.

[0060] After each independent query is executed, its result set (the dataset that satisfies the existence check) will be temporarily stored in the same format as the original query result to ensure compatibility for subsequent merging steps.

[0061] Step S209 merges the results of multiple independent queries and continues with other operations of the database operation statement. This step ensures the semantic integrity of the query by merging the results, ultimately improving overall performance.

[0062] Since the target subquery is only used for existence checks, the merging logic does not require complex calculations. It only needs to merge the temporary result sets of all independent queries (without deduplication). The merged result set is semantically identical to the result set of the original target subquery. After merging, the optimized database operation statement continues to execute the outer query logic (such as the outer SELECT, JOIN, etc.). For example, if the outer query is `Select * from t1 where exists(...)`, the merged result set is used to determine whether each record in table `t1` satisfies the existence of matching data, and then returns the final query result.

[0063] After splitting using this method, each branch has pure logic, and the optimizer can effectively push the filtering conditions to the index, avoiding index failure caused by the original mixed OR conditions, and accurately calling efficient optimization schemes such as hashjoin and indexscan; at the same time, each branch independently estimates the selectivity (scan volume, I / O overhead, etc.), and the total cost after aggregation is closer to the actual execution cost; and the transformed statement does not need to add deduplication logic, and also supports parallel execution of each branch operation, and independent atomic conditions can trigger partition / shard pruning.

[0064] In summary, this embodiment completely solves the shortcomings of traditional subqueries with OR condition existence judgments, such as poor optimization effect, high cost, and weak adaptability, by using the dual logic of split optimization and precise branch tuning. It can achieve the ultimate improvement in query performance without affecting semantic correctness, and it is simple to implement and has strong cross-database generalization.

[0065] To more clearly explain the method of the present invention, some optional examples are provided below: An example of a database operation statement with a target subquery of type ANY is shown in equation (5): Select * from t1 where a < any(select a from t2 where t1.b=t2.b ort1.a<10) Formula (5) Based on the optimization method of the present invention, the optimization process of equation (5) is as follows: Identify the target subquery: In equation (5), select a from t2 where t1.b=t2.b or t1.a<10 is the target subquery, which meets the preset conditions of being used only for membership judgment, only for projection + predicate filtering, and having no impact on the upper-level semantics of the result set; Extracting compound predicates: Extract the compound predicate t1.b=t2.b OR t1.a<10 from the WHERE clause of the target subquery. This predicate is composed of two atomic conditions (t1.b=t2.b, t1.a<10) connected by a logical OR operator. Generate independent query statements: Convert the two atomic conditions into independent query statements while maintaining consistency with the projection operation (SELECT a) of the original target subquery: Independent query statement 1 (corresponding to the atomic condition t1.b=t2.b): select a from t2 where t1.b=t2.b; Independent query statement 2 (corresponding to the atomic condition t1.a<10): select a from t2 where t1.a<10; Combined query statements: By using the non-duplicate keyword UNION ALL to combine two independent query statements, we get the combined query statement: (select a from t2 where t1.b=t2.b) union all (select a from t2 where t1.a<10); Replace the target subquery: Replace the original target subquery in equation (5) with the above combined query statement to obtain the optimized database operation statement as shown in equation (6): Select * from t1 where a < any((select a from t2 where t1.b=t2.b)union all (select a from t2 where t1.a<10) Formula (6) After optimization, the conditional logic of the two independent query statements is pure. Independent query 1 (equi-join condition) can trigger index scan optimization, and independent query 2 (range filtering condition) can trigger partition pruning optimization. It also supports parallel execution, which solves the problem that the optimizer cannot accurately tune due to the original mixed OR conditions.

[0066] An example of a database operation statement with a target subquery of type NOT ALL is shown in equation (7): Select * from t1 where not a > all(select a from t2 where t1.b=t2.bor t1.a<10) Formula (7) Based on the optimization method of the present invention, the optimization process of equation (7) is as follows: Identify the target subquery: In equation (7), select a from t2 where t1.b=t2.b or t1.a<10 is the target subquery, which satisfies the preset conditions that it is only used for existence judgment, only for projection + predicate filtering, and has no impact on the upper-level semantics of the result set; Extracting the compound predicate: Extract the compound predicate "t1.b=t2.b OR t1.a<10" from the WHERE clause of the target subquery. This predicate consists of two independent, executable atomic conditions. Generate independent query statements: To maintain consistency with the projection operation (SELECT a), generate independent query statements for each atomic condition: Independent query statement 1 (corresponding to the atomic condition t1.b=t2.b): select a from t2 where t1.b=t2.b; Independent query statement 2 (corresponding to the atomic condition t1.a<10): select a from t2 where t1.a<10; Combine two independent query statements to generate a combined query statement: (select a from t2 where t1.b=t2.b) union all (select a from t2 where t1.a<10); Replace the target subquery: Replace the original target subquery in equation (7) with a combined query statement to obtain the optimized database operation statement as shown in equation (8): Select * from t1 where not a > all((select a from t2 where t1.b=t2.b)union all (select a from t2 where t1.a<10) Formula (8) The optimized statement splits the original complex OR conditions, allowing for accurate cost estimation of the two independent queries. Both queries can call appropriate and efficient optimization solutions (such as index scans and hash joins). Furthermore, the existence check corresponding to NOT ALL naturally eliminates duplicate data, eliminating the need for additional deduplication operations and avoiding the extra execution overhead of traditional solutions.

[0067] The above examples all strictly follow the optimization method of this invention: the split independent query statements maintain consistency with the original projection operation, semantic equivalence is guaranteed through UNION ALL combination, and the existence / membership judgment logic of the original query is not changed after replacement. The optimized statements not only solve the defects of inaccurate estimation of selection rate of traditional OR conditions, ineffective index pushdown, and difficulty in selecting join strategy, but also give full play to the optimization potential of independent queries, improve the optimizability of statements and index hit, and the conditions do not interfere with each other, each taking the advantages. At the same time, the estimation of each branch and then the sum / union estimation is more reliable than the one-time estimation of OR, and the cost model is more credible. The branches of UNION ALL can be executed in parallel, the branch predicates are independent, and the partitioning / sharding pruning effect is better. The outer layer is a semi-join / existence test, which naturally eliminates repetition, resulting in low implementation cost and stable benefits. As a rule of "algebraic rewriting + cost judgment" of the database optimizer, the method of this invention is simple to implement and has strong cross-database generalization.

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

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

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

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

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

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

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

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

[0076] 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 database operation statements that meet preset conditions, wherein the preset conditions are that the database operation statement contains a target subquery, the query result of the target subquery is only used for existence or membership judgment, and the target subquery only performs projection operation and predicate filtering operation, and the target subquery does not contain any upper-level semantics that affect the result set; Extract the compound predicate contained in the conditional clause of the target subquery. The conditional clause is the clause corresponding to the WHERE keyword. The compound predicate is composed of at least two atomic conditions connected by a logical OR operator. Based on the compound predicate, the target subquery is transformed to obtain a combined query statement, which is formed by merging multiple independent query statements. The target subquery in the database operation statement is replaced by the combined query statement to obtain the optimized database operation statement.

2. The method for optimizing database operation statements according to claim 1, wherein, The step of transforming the target subquery based on the compound predicate to obtain the combined query statement includes: Determine the atomic conditions in the compound predicate; Each atomic condition is converted into an independent query statement, which is consistent with the projection operation of the target subquery and contains only its own corresponding atomic condition as a filtering condition. The independent query statements are combined by merging non-duplicate keywords to obtain the combined query statement. The non-duplicate keywords are used to combine the result sets of each independent query statement without deduplication.

3. The method for optimizing database operation statements according to claim 2, wherein, The keywords for merging without deduplication include: UNION ALL.

4. The method for optimizing database operation statements according to claim 1, wherein, The target subqueries include: IN subqueries, NOT IN subqueries, EXISTS subqueries, NOT EXISTS subqueries, ANY type subqueries, NOT ANY type subqueries, NOT ALL type subqueries, and equivalent semi-join and anti-semi-join subqueries.

5. The method for optimizing database operation statements according to claim 4, wherein, The operators involved in the ANY type subquery include EXISTS, IN, ... <ANY、<=ANY、=ANY、> ANY, >=ANY, != ANY, <> ANY; The operators involved in the NOT ANY type subquery include NOT EXISTS, NOT IN, and NOT. <ANY、NOT<=ANY、NOT=ANY、NOT> ANY, NOT>=ANY, NOT!=ANY, NOT<>ANY; The NOT ALL type subquery involves the NOT operator. <ALL、NOT<=ALL、NOT=ALL、NOT> ALL, NOT>=ALL, NOT!=ALL, NOT<>ALL.

6. The method for optimizing database operation statements according to claim 1, wherein, The step of obtaining the optimized database operation statement also includes: Set a timeout threshold for each of the independent query statements in the optimized database operation statements; If any of the independent query statements exceeds its corresponding timeout threshold, the execution of the independent query statement is terminated and an empty result set is returned, while an error record is generated. Continue executing other independent query statements.

7. The method for optimizing database operation statements according to claim 1, wherein, The step of obtaining the optimized database operation statement also includes: Determine the target optimization method supported by each independent query statement in the optimized database operation statement, wherein the target optimization method is used to improve the query performance of the independent query statement individually; Perform a query operation based on the target optimization method supported by each of the independent query statements to obtain multiple independent query results; The results of multiple independent queries are merged, and other operations of the database operation statement are continued.

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