Database query statement optimization method, medium, product and equipment
By passing filtering conditions across levels in database query statements, the problems of full scan and invalid data loading in existing technologies are solved, achieving a high-efficiency improvement in query execution performance. It is suitable for multi-level queries and multi-table join scenarios.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- CETC JINCANG (BEIJING) TECH CO LTD
- Filing Date
- 2025-12-30
- Publication Date
- 2026-04-17
AI Technical Summary
Existing technologies lack the ability to perform deep correlation analysis on join relationships and filtering conditions in multi-level queries. This results in database query statements having to perform a full scan of the associated tables or load a large amount of invalid data when performing join operations, increasing storage I/O and computational overhead, and leading to low query efficiency. This is especially true in scenarios with large amounts of data and complex join relationships, where it is difficult to meet the needs of efficient data processing.
By obtaining the target parent query and target subquery in the database query statement, it is determined that they have an equi-join relationship in the same table and column and that the join key is unique. The target filtering conditions are passed across levels equally, and the query logic is optimized to reduce the intermediate result set and improve execution performance.
By passing filtering conditions across levels of equivalence, the amount of data and computational overhead of database query statements are reduced, improving query execution efficiency, especially in scenarios with large amounts of data and complex join relationships.
Smart Images
Figure CN121880384A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database technology, and in particular to a method, medium, product, and device for optimizing database query statements. Background Technology
[0002] In the current database technology field, databases, as the core carrier of data storage and management, directly impact the operational efficiency of various application systems through their query performance. With the explosive growth of business data and the increasing complexity of query scenarios, database query statements involving multi-level nested queries and multi-table joins have become the mainstream application. Therefore, improving data processing efficiency through optimizing query logic has become one of the core research directions in the database field.
[0003] Predicate push-up and push-down are key technologies for improving performance. By rationally allocating filtering conditions, data is pre-filtered, reducing the amount of data in subsequent complex operations. However, existing technologies lack the ability to deeply analyze the correlation between join relationships and filtering conditions in multi-level queries, and cannot effectively pass the filtering conditions for join keys in subqueries or parent queries to the corresponding parent or subqueries. This results in one level of query needing to perform a full scan of the joined tables or load a large amount of invalid data when performing join operations, significantly increasing storage I / O and computational overhead, leading to low query efficiency. This deficiency is particularly pronounced in scenarios with large amounts of data in joined tables and complex join relationships, making it difficult to meet the needs of efficient data processing. Therefore, there is an urgent need for an optimization scheme that can overcome the limitations of existing technologies and achieve efficient transmission of query filtering conditions at different levels in multi-level queries and multi-table join scenarios, in order to improve the execution performance of complex database query statements and meet the ever-increasing data processing demands. Summary of the Invention
[0004] In view of the above problems, the present invention proposes a method, medium, product and device for optimizing database query statements to overcome the above problems or at least partially solve the above problems.
[0005] One objective of this invention is to reduce the amount of data involved in database query join operations.
[0006] A further objective of this invention is to improve the execution efficiency of database query statements.
[0007] Specifically, the present invention provides a method for optimizing database query statements, comprising: Retrieve the database query statement; Determine the target parent query and target subquery in the database query statement. The target parent query and target subquery have an equi-join relationship in the same table and column, and the join key is unique. Determine whether the target filter condition exists in the target parent query or target subquery. The data column corresponding to the target filter condition is a data column other than the join key in the data table. If target filtering conditions exist in the target parent query or target subquery, the target filtering conditions are passed across levels with equivalent pass-through.
[0008] Optionally, the steps for determining the target parent query and target subquery in a database query statement include: Identify parent and child queries with join relationships in database query statements; Retrieve the join conditions for records in the parent query; Determine if the join condition is an equi-join condition that uses the same data column from the same data table; If so, then determine whether the data columns to be equi-joined are unique; When the data columns being joined by equi-joins are unique, the parent query and the subquery are determined as the target parent query and the target subquery, respectively.
[0009] Optionally, the steps of equivalent transitivity for target filtering criteria include: If the target parent query contains target filtering conditions, the target filtering conditions are equivalently passed to the target subquery.
[0010] Optionally, the steps of equivalent transitivity for target filtering criteria include: If a target filter condition exists in the target subquery, the target filter condition is equivalently passed to the target parent query.
[0011] Optionally, the step of equivalent transitivity of target filtering criteria may be included before: Determine whether there are filtering conditions in the target parent query and target subquery that are logically equivalent to the target filtering conditions; If so, then the equivalent transit operation ends.
[0012] Optionally, after the step of equivalent transitivity of the target filtering criteria, the method may further include: The query logic of database query statements and its equivalent passing results are cached and recorded.
[0013] Optionally, the step of obtaining the database query statement may further include: Parse the database query statement to obtain the query logic; Determine if the same query logic exists in the cache; If it exists, the equivalent pass-through result corresponding to the same query logic in the cache is applied directly.
[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 any of the above-described database query statements.
[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 any of the above-described database query statements.
[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 any of the above-described database query statements.
[0017] The database query statement optimization method of this invention first obtains the database query statement; then, it determines the target parent query and target subquery in the database query statement, wherein the target parent query and target subquery have an equi-join relationship in the same table and the join key is unique; next, it determines whether there is a target filtering condition in the target parent query or target subquery, and the data column corresponding to the target filtering condition is a data column other than the join key in the data table; if there is a target filtering condition in the target parent query or target subquery, the target filtering condition is passed across levels of equivalence. This method can push the filtering conditions up or down to the table, effectively pre-filtering the table data, reducing intermediate result sets, and improving 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 query statements according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating a method for optimizing database query statements according to another embodiment of the present invention; Figure 3 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 5 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation
[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 a database query statement. SQL statements offer various functions such as data manipulation and data definition. This language is interactive and provides great convenience to users. In specific query processing, SQL join operations are the core means of achieving multi-table data association and complex logical queries, but some SQL statements have room for optimization.
[0023] An optional SQL example is shown in equation (1): select ssh.* from (select a ,c,b from s1 group by a ,c,b ) ssh ,s1 swhere ssh.b =sb and s. a=1 Formula (1) Column b of table s1 is unique.
[0024] The SQL statement shown in equation (1) has a subquery of (select a,c,b from s1 group by a,c,b)ssh and a parent query of select ssh.* from (…) ssh,s1 s where ssh.b =sb and s.a=1. Its execution logic is as follows: 1. Execute subquery: First, group the entire s1 table by a, c, and b, and generate a temporary result set ssh containing unique combinations of a, c, and b (without pre-filtering, the entire s1 data needs to be scanned). 2. Main table data filtering: Filter the original data table s1 (alias s) using the WHERE clause with sa=1 to obtain the subset of tables s where a=1; 3. Inner join matching: The result set ssh of the subquery is joined with the filtered subset of table s by ssh.b=sb, and records with the same b field value are retained. 4. Returned Results: Extract all fields from the SSH connection; this is the final query result.
[0025] Analysis reveals that the parent query of the SQL statement shown in equation (1) contains the filtering condition a=1, and performs an implicit inner join with the join condition ssh.b=sb. During the execution of the subquery, a full data scan of table s1 is required. Many records with a≠1 in the generated ssh table will ultimately be filtered out due to the main table's filtering condition. However, these records still need to participate in the two core steps of subquery grouping and main table join matching, which constitutes completely meaningless redundant calculation. This approach not only wastes storage I / O resources but also significantly increases the computational load of subsequent join operations. Especially in scenarios with a large amount of data in table s1, it significantly prolongs the query response time, resulting in a clear bottleneck in query performance optimization.
[0026] Therefore, this invention provides a method for optimizing database query statements. Figure 1 This is a flowchart illustrating a method for optimizing database query statements according to an embodiment of the present invention, as shown below. Figure 1 As shown, the optimization method for this database query statement includes at least the following steps S101 to S104.
[0027] Step S101: Obtain the database query statement. In this step, the database query statement can generally be obtained by user input or automatically generated by the database program during operation due to business requirements. Those skilled in the art can decide on the method of obtaining the database query statement based on the actual situation.
[0028] Step S102: Determine the target parent query and target subquery in the database query statement. The target parent query and target subquery must have an equi-join relationship in the same table and column, and the join key must be unique.
[0029] In some optional embodiments, the steps of determining the target parent query and target subquery in a database query statement generally include: identifying the parent query and subquery in the database query statement that have a join relationship; obtaining the join conditions recorded in the parent query; determining whether the join conditions are conditions for equi-joining the same data column of the same data table; if so, determining whether the data column being equi-joined is unique; if the data column being equi-joined is unique, determining the parent query and subquery as the target parent query and target subquery.
[0030] Taking the SQL statement shown in equation (1) as an example, firstly, the parent query (i.e., select ssh.* from (...) ssh ,s1 s where ssh.b =sb and s. a=1) and the subquery (i.e., select a ,c,b from s1 group by a ,c,b) with a join relationship are identified. Then, the join condition in the parent query (such as ssh.b=sb) is extracted. Since ssh is the result set of the subquery in the s1 table, and s is an alias of the s1 table, ssh.b and sb belong to the same data column in the same data table and are still equi-joined. In addition, column b is also unique, so it fully meets the judgment condition of this step. The parent query and the subquery are determined to be the target parent query and target subquery that need to be optimized.
[0031] This judgment operation can accurately filter out the applicable scenarios of the present invention, avoid performing invalid optimizations in queries that do not meet the conditions, and improve the targeting of the optimization process. Secondly, by verifying the uniqueness of the join column, a logical basis is provided for the equivalent passing of subsequent filtering conditions. Because the join key is unique, the associated data of the parent query and the child query have a one-to-one correspondence, and the semantics of the query can be guaranteed to remain unchanged after the filtering conditions are passed.
[0032] Step S103: Determine whether the target filter condition exists in the target parent query or target subquery. The data column corresponding to the target filter condition is a data column other than the join key in the data table. In the SQL statement shown in equation (1), it can be determined that there is another filter condition (sa=1) in the parent query, which belongs to another data column in the s1 table, and therefore can be determined as the target filter condition.
[0033] Step S104: If there are target filtering conditions in the target parent query or target subquery, perform cross-level equivalent transfer of the target filtering conditions.
[0034] Specifically, the steps for equivalent passing of target filtering conditions generally include: if target filtering conditions exist in the target parent query, passing the target filtering conditions equivalently to the target subquery.
[0035] If a target filter condition exists in the target subquery, the target filter condition is equivalently passed to the target parent query.
[0036] In the SQL statement shown in equation (1), the target filter condition belongs to the target parent query, so the target filter condition needs to be equivalently passed down to the target subquery.
[0037] The optimized result of the SQL statement shown in equation (1) is shown in equation (2): select ssh.* from (select a ,c,b from s1 where a =1 group by a ,c,b )ssh ,s1 s where ssh.b =sb and s. a=1 Formula (2) In the SQL statement shown in equation (2), the target filter condition (sa=1) is pushed down to the target subquery, thus adding a filter condition (where a=1) to the target subquery. Its execution logic is as follows: 1. Subquery-based filtering + grouping: First, scan table s1 and filter out only the records where a=1 (the valid data subset). Then, group the subset by a, c, and b to generate a concise temporary result set ssh (without invalid data where a≠1). 2. Main table filtering: Execute `where sa=1` on the original data table s1 (alias s) to obtain the valid subset of table s; 3. Efficient join matching: Perform an inner join between the simplified ssh and the effective subset of s table using ssh.b=sb, matching only records where the b field of both tables is consistent; 4. Return results: Extract all fields from the SSH connection to obtain the final result.
[0038] The comparison shows that the SQL statement shown in Equation (2) filters invalid data in advance by first applying the newly added filtering conditions during the subquery execution stage, which greatly reduces the amount of data in subsequent join operations. By pushing the non-join key filtering conditions of the parent query to the subquery, invalid data is removed from the upstream of the data flow, avoiding its participation in subsequent complex operations such as grouping and joining. Without affecting the correctness of the query results, a significant improvement in query performance is achieved, which is especially suitable for query scenarios with large data tables.
[0039] Ultimately, this method allows filtering conditions to be pushed up or down onto the table, effectively filtering table data in advance, reducing intermediate result sets, and improving query execution performance.
[0040] To further ensure the security of the push-up, a verification step can be added before adding the filtering conditions. In some alternative embodiments, the step of equivalent passing of the target filtering conditions can generally include: determining whether there are filtering conditions in the target parent query and target subquery that are logically equivalent to the target filtering conditions; if so, the equivalent passing operation ends.
[0041] For example, if the target filter condition is a=1, and the subquery already contains a=1, or contains logically equivalent conditions such as a>0 and a<2, then the equivalence propagation operation should be terminated directly without adding the filter condition again. This is because repeatedly propagating logically equivalent filter conditions at the target query level leads to redundant filter conditions, which not only fails to improve performance but may also increase the overhead of query parsing and execution, and even cause execution plan optimization to fail due to duplicate conditions. Therefore, this validation step is necessary to avoid redundant operations.
[0042] In addition, in practical applications, the same business requirement may involve repeatedly executing queries with the same logic (with differences only in non-core elements such as table aliases, spaces, and field order). Repeating the entire optimization process for each execution would result in unnecessary computational waste. Caching mechanisms allow for the reuse of optimized results, improving the efficiency of handling repeated queries. Therefore, to further increase execution efficiency, after the step of equivalent passing of target filtering conditions, it is generally also possible to cache the query logic of the database query statement and its equivalent passing results.
[0043] After obtaining the database query statement, the process typically includes: parsing the query logic of the database query statement; checking if the same query logic exists in the cache; and if so, directly applying the equivalent pass-through result corresponding to the same query logic in the cache. This can significantly improve the response speed of repeated queries, reduce the time overhead of query parsing and optimization, reduce the load on the database server, reduce the CPU and memory consumption caused by repeated optimization operations, and improve the server's concurrent processing capabilities. Figure 2 This is a flowchart illustrating a method for optimizing database query statements according to another embodiment of the present invention, as shown below. Figure 2 As shown, the optimization method for this database query statement includes at least the following steps S201 to S209.
[0044] Step S201: Obtain the database query statement.
[0045] Step S202: Identify the parent and child queries that have a join relationship in the database query statement.
[0046] An optional SQL example is shown in equation (3): select ssh.* from (select a ,b from s1 where a=1 group by a ,b ) ssh,s1 s where ssh.b =sb Formula (3) Column b of table s1 is unique.
[0047] The execution logic of the SQL statement shown in equation (3) is as follows; 1. Subquery execution: Scan table s1 → Filter records where a=1 → Group by a and b → Generate temporary result set ssh; 2. Main table scan: Full table scan of the original data table s1 (alias s); 3. Join matching: Perform an inner join between ssh and the full data of table s using ssh.b=sb; 4. Return results: Extract the 'a' and 'b' fields from the SSH connection to obtain the final result.
[0048] Step S202 identifies the subquery with a join relationship as (select a ,b from s1where a=1 group by a ,b ) ssh, and the parent query is select ssh.* from (…) ssh ,s1 swhere ssh.b =sb.
[0049] Step S203: Obtain the join condition of the records in the parent query. In the SQL statement shown in equation (3), the join condition of the records in the parent query can be determined to be ssh.b=sb.
[0050] Step S204: Determine whether the join condition is an equi-join condition based on the same data column in the same data table. If the determination is no, end the current optimization operation.
[0051] Specifically, for the SQL statement shown in equation (3), first determine whether the two related parties operate on the same data table (e.g., the parent query is related to the ssh table as the result set of the subquery, and the result set of the subquery originates from the s1 table, so the same table requirement is met). Then determine if the join is based on the same data column of the data table, and if the join operator is the equality operator (=): In the SQL statement shown in equation (3), all are connected through the b field and the operator is =, which satisfies the requirement of equal value connection in the same column.
[0052] If any of the above criteria are not met (such as not being in the same table, not being in the same column, or not being an equi-join), then the current query is deemed not to meet the optimization conditions, and the current optimization process is terminated directly.
[0053] Step S205: If the determination in step S204 is yes, determine whether the data columns to be joined are unique. If the determination is no, end the current optimization operation.
[0054] After determining in step S204 that the join is an equi-join of the same column in the same table, the uniqueness of the join column needs to be further verified: by querying the database's data dictionary, index information, or constraint definitions, it is verified whether the join column has a unique index (including a single-column unique index or a column in a composite unique index that plays a unique role) or a primary key constraint, ensuring that each value in the column appears only once in the data table. If the join column does not have uniqueness, the current optimization process is terminated.
[0055] This step provides a logical guarantee for the equivalent passing of filtering conditions: because the join column is unique, the associated data of the parent query and the child query have a one-to-one correspondence, so passing non-join key filtering conditions across levels will not lead to result deviation.
[0056] Step S206: If the determination in step S205 is yes, then determine the parent query and the subquery as the target parent query and the target subquery.
[0057] When both steps S204 (equi-join of the same column in the same table) and S205 (unique join column) are determined to be yes, it indicates that the current query fully meets the optimization conditions of the present invention, and the parent query and subquery are officially marked as the target parent query and target subquery, providing clear operation objects for subsequent filtering condition determination and equivalence transfer.
[0058] Step S207: Determine if a target filter condition exists in the target parent query or target subquery. Parse the syntax of the target parent query and target subquery (such as the conditional expressions in the WHERE clause and HAVING clause), extract all filter conditions, and determine if a target filter condition exists, that is, if the field corresponding to the filter condition is a data column other than the join column. Specifically, in the SQL statement shown in equation (3), the target subquery contains the filter condition WHERE a=1, and column a is also a data column in table s1, so a=1 is determined to be the target filter condition.
[0059] In step S208, if the target filtering condition exists in the target parent query, the target filtering condition is equivalently passed to the target subquery. When step S207 determines that the target parent query contains a target filtering condition (e.g., a=1 in the parent query, with column b as the join), the filtering condition is passed completely and equivalently to the target subquery (e.g., the subquery adds WHERE a=1). During the passing process, the original logic of the filtering condition (including field type, comparison operator, function operation, etc.) is preserved, and its semantics are not changed. Specific examples are shown in the SQL statements in equations (1) and (2).
[0060] This step enables predicate pushdown optimization, allowing the subquery to first execute filtering conditions to remove invalid data before performing complex operations such as grouping and aggregation. This reduces the amount of data processed by the subquery from the source, lowering I / O and CPU overhead. Simultaneously, it reduces the size of the intermediate result set, as the temporary result set generated by the subquery only contains valid data, reducing the number of matches when joining with the parent query and improving join efficiency. Furthermore, the method of this invention is based on the uniqueness check of the preceding join column, ensuring that the query semantics remain unchanged after the filtering conditions are pushed down, and the final result is completely consistent with the result before optimization, thus guaranteeing result consistency.
[0061] Step S209: If a target filter condition exists in the target subquery, the target filter condition is equivalently passed to the target parent query. When step S207 determines that a target filter condition exists in the target subquery, the filter condition is completely and equivalently passed to the target parent query to ensure that the logic of the filter condition remains unchanged.
[0062] This step optimizes predicate push-up, allowing the parent query to first execute filtering conditions to filter data before performing the join operation with the subquery. This reduces the amount of data in the parent query involved in the join and avoids join calculations for invalid data. Furthermore, the optimization method of this invention is based on the premise of the uniqueness of the join columns, ensuring that the push-up of filtering conditions does not change the final query result, thus balancing performance and correctness. Existing technologies completely do not support the passing of non-join column filtering conditions from the subquery to the parent query. This step is the first to achieve optimization in this scenario, significantly improving the execution efficiency of such queries.
[0063] Taking the SQL statement shown in equation (3) as an example, since the target subquery contains the target filtering condition a=1, it can be pushed up to the target parent query. The final optimized result is shown in equation (4): select ssh.* from (select a ,b from s1 where a=1 group by a ,b ) ssh,s1 s where ssh.b =sb and sa=1 Formula (4) Its execution logic is as follows: 1. Subquery execution: Scan table s1 → Filter valid records where a=1 → Group by a and b to remove duplicates → Generate a simplified temporary set ssh (containing only unique a and b combinations where a=1); 2. Main table filtering: When scanning the original data table s1 (alias s), first execute the WHERE sa=1 filter to keep only the valid subset of data where a=1 (no need to scan the entire table). 3. Join matching: Perform an inner join between the simplified ssh and the valid subset of the main table s, with ssh.b=sb, and only match records where the b field value is the same on both sides; 4. Result Return: Extract the 'a' and 'b' fields from the SSH connection and output the final result.
[0064] The comparison shows that the optimized SQL statement only scans the valid subset of a=1 in the main table s, instead of the entire table, which greatly reduces I / O overhead; both joins have valid data with a=1, which greatly reduces the number of matches and significantly reduces CPU computation overhead; because the join key b is unique, the a=1 in the subquery is pushed up to the parent query, and the filtering logic of the main table and the subquery is equivalent. The final result is completely consistent with the unoptimized version, with no risk of data deviation.
[0065] The flowcharts provided in this embodiment are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.
[0066] 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.
[0067] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 3 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 4 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 5 This is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of the database query statement optimization method described above. A computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by the processor 32, implements the steps of the database query statement optimization method described above. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored on the memory 31 and running on the processor 32.
[0068] 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.
[0069] 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.
[0070] 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.
[0071] 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.
[0072] 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.
[0073] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.
Claims
1. A method for optimizing database query statements, comprising: Obtain the database query statement; Determine the target parent query and target subquery in the database query statement, wherein the target parent query and the target subquery have an equi-value join relationship in the same table and column and the join key is unique; Determine whether a target filter condition exists in the target parent query or the target subquery, wherein the data column corresponding to the target filter condition is a data column other than the join key in the data table; If the target filtering condition exists in the target parent query or the target subquery, the target filtering condition is passed across levels with equivalent transfer.
2. The method for optimizing database query statements according to claim 1, wherein, The steps for determining the target parent query and target subquery in the database query statement include: Identify parent and child queries with join relationships in the database query statement; Retrieve the join conditions of the records in the parent query; Determine whether the join condition is an equi-join condition that uses the same data column from the same data table; If so, determine whether the data column to be joined is unique; If the data columns being joined by equi-joins are unique, then the parent query and the subquery are determined as the target parent query and the target subquery.
3. The method for optimizing database query statements according to claim 1, wherein, The step of equivalently passing the target screening criteria includes: If the target filtering condition exists in the target parent query, the target filtering condition is equivalently passed to the target subquery.
4. The method for optimizing database query statements according to claim 1, wherein, The step of equivalently passing the target screening criteria includes: If the target filtering condition exists in the target subquery, the target filtering condition is equivalently passed to the target parent query.
5. The method for optimizing database query statements according to claim 1, wherein, Before the step of equivalently passing the target screening criteria, the method further includes: Determine whether there are any filtering conditions in the target parent query and the target subquery that are logically equivalent to the target filtering conditions; If so, then the equivalent transfer operation ends.
6. The method for optimizing database query statements according to claim 1, wherein, Following the step of equivalent transiting the target screening criteria, the method further includes: The query logic of the database query statement and its equivalent pass results are cached and recorded.
7. The method for optimizing database query statements according to claim 6, wherein, Following the step of obtaining the database query statement, the method further includes: The query logic of the database query statement is obtained through parsing. Determine if the same query logic exists in the cache; If it exists, the equivalent pass-through result corresponding to the same query logic in the cache is directly applied.
8. A computer-readable storage medium having a computer program stored thereon, the computer program being executed by a processor to implement the steps of the method for optimizing a database query statement according to any one of claims 1 to 7.
9. A computer program product comprising a computer program that, when executed by a processor, implements the steps of the method for optimizing a database query statement according to any one of claims 1 to 7.
10. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements the steps of the method for optimizing a database query statement according to any one of claims 1 to 7.