A query optimization method of a database and related product

Abstract

This invention provides a database query optimization method and related products. The method includes: obtaining an SQL query statement containing a result set constraint clause; identifying whether the initial query execution plan of the SQL query statement contains a pushdown node; if the pushdown node is present, generating candidate query paths that push down the result set constraint clause to the pushdown node; and selecting a target query execution plan from the initial query execution plan of the SQL query statement and multiple query execution plans of the candidate query paths based on cost evaluation of each candidate query path, to execute the SQL query statement. This invention achieves dynamic pushdown of row count constraint operations in the execution plan, overcoming the limitations of traditional static execution plans in optimizers. By identifying scenarios such as outer joins, set operations, parallel execution, and Cartesian products, it can pre-prune data volume in the early stages of data processing, significantly reducing the burden of query operations.

Description

Technical Field

[0001] This invention relates to database technology, and in particular to a database query optimization method and related products. Background Technology

[0002] With the diversification of database application scenarios, complex queries involving row count limitations are becoming increasingly common. However, once the execution plan structure of existing database optimizers is determined, the execution order and processing scope of each operation node are also fixed, making dynamic adjustment during execution difficult. This static execution plan mechanism leads to row count limitation operations typically being placed at the top level of the plan, requiring lower-level nodes to process all data before pruning, resulting in a significant waste of computational resources. When the data volume is large or the query structure is complex, this can restrict query response speed and the database system's throughput. Summary of the Invention

[0003] One objective of this invention is to improve the execution efficiency of complex queries that include row count limitations.

[0004] A further objective of this invention is to reduce the amount of data processing in lower-level nodes during query execution while ensuring semantic equivalence of queries.

[0005] Specifically, the present invention provides a database query optimization method, comprising: Retrieve an SQL query statement that includes a result set restriction clause, which limits the number of rows returned by the query; Identify whether the initial query execution plan of the SQL query statement contains pushdown nodes. The pushdown nodes include at least one of the following: non-nullable ends of outer joins, branches of set operations, sub-plans under the convergence node in parallel execution, and data source nodes of Cartesian product joins. If the pushdownable node is included, generate a candidate query path that pushes down the result set constraint clause to the pushdownable node; Based on the cost evaluation of each candidate query path, a target query execution plan is selected from the initial query execution plan of the SQL query statement and the multiple query execution plans of the candidate query paths to execute the SQL query statement.

[0006] Optionally, the step of identifying whether the initial query execution plan of the SQL query statement contains pushdown nodes includes: Traverse the node tree of the query execution plan and identify nodes of a preset type, wherein the preset type of node includes at least one of the following: outer join node, set operation node, convergence node in parallel execution, and Cartesian product join node; Nodes of the preset type that meet the pushdown conditions are identified as pushable nodes, and the pushdown conditions are configured according to the node type.

[0007] Optionally, determining nodes of the preset type that meet the pushdown conditions as pushable nodes includes: If the node of the preset type is the external connection node, the non-empty end of the external connection node is determined as the pushdown node; If the node of the preset type is the set operation node, then each branch of the set operation node is determined as the pushdown node; If the node of the preset type is the convergence node in the parallel execution, then each sub-plan under the convergence node in the parallel execution is determined as the pushdown node; If the node of the preset type is the Cartesian product connection node, then each data source node that provides input data to the Cartesian product connection node will be determined as the pushdown node.

[0008] Optionally, the step of generating candidate query paths that push down the result set constraint clause to the pushdownable node includes: Based on the type of the pushdown node, a row count limit node is inserted between the pushdown node and the upper-level node in the initial query execution plan of the SQL query statement to obtain the candidate query path, which is semantically equivalent to the initial query execution plan. The row count threshold for the inserted row count limit node is determined based on the row count limit of the result set limit clause.

[0009] Optionally, inserting a row count limit node between the pushdown node and the upper-level node includes: If the pushdown node is a non-nullable end of an outer join, insert the row count limit node on the table scan or subquery corresponding to the non-nullable end; If the pushdown nodes are branches of a set operation, insert the row count limit node between the query node corresponding to each branch and the set operation node to which the branch belongs; If the pushdown node is a sub-plan under the convergence node in parallel execution, the row count limit node is inserted between the execution node corresponding to each sub-plan and the convergence node to which the sub-plan belongs; If the pushdown nodes are data source nodes connected by a Cartesian product, the row count limit node is inserted between each data source node and the upper-level connection node to which the data source node belongs.

[0010] Optionally, the threshold for limiting the number of rows inserted is determined based on the row limit of the result set constraint clause, including: If the pushdown node is any one of the following: a non-nullable end of an outer join, each branch of a set operation, or each data source node connected by a Cartesian product, the row number threshold of the row number limit node is the same as the row number threshold of the result set limit clause. If the pushdown node is a sub-plan under the convergence node in parallel execution, the row count threshold of the row count limit node is the row count threshold of the result set limit clause divided by the degree of parallelism and then rounded up.

[0011] Optionally, the step of selecting a target query execution plan from the initial query execution plan of the SQL query statement and multiple query execution plans of the candidate query paths based on cost evaluation of each of the candidate query paths includes: Determine the first execution cost of the initial query execution plan and the second execution cost of each of the candidate query paths; The first execution cost is compared with each of the second execution costs, and the query execution plan with the lowest execution cost is selected as the target query execution plan.

[0012] 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 any of the above-described database query optimization methods.

[0013] 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 any of the above-described database query optimization methods.

[0014] According to another aspect of the present invention, a computer device is also provided, comprising a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of any database query optimization method.

[0015] The database query optimization method of this invention, by pushing the result set constraint clause down to the non-nullable end of the outer join, achieves data pruning before the join operation while ensuring the semantic correctness of the outer join, significantly reducing the amount of input data for the join operation and improving the execution efficiency of join queries. By pushing the result set constraint clause down to each branch of the set operation, each branch independently performs row number constraints before merging, avoiding invalid pruning after merging all data, and reducing the memory consumption and processing overhead of the set operation. By pushing the result set constraint clause down to each sub-plan under the aggregation node in parallel execution, each parallel subprocess returns only a small amount of constrained data, significantly reducing the amount of data transfer between parallel processes and improving the overall performance of parallel queries. By pushing the result set constraint clause down to each data source node of the Cartesian product join, row number constraints are applied in advance at each data source node, reducing the size of the intermediate result of the Cartesian product from the product of the number of rows in each table to the product of the constrained number of rows, fundamentally avoiding the explosive growth of the intermediate result set. By selecting the target execution plan from the initial execution plan and each candidate path based on cost evaluation, the optimal execution strategy can be adaptively selected for different queries while ensuring semantic equivalence of queries. This avoids performance degradation caused by forced pushdown and achieves intelligent and dynamic query optimization.

[0016] Furthermore, the database query optimization method of the present invention identifies preset type nodes by traversing the node tree of the initial query execution plan and configures corresponding pushdown conditions for different type nodes, thereby realizing a unified processing framework for various complex query scenarios and significantly improving the universality and practical value of optimization. By configuring differentiated pushdown conditions according to node type, accurate identification and differentiated processing of different type nodes are achieved, ensuring that row count limit operations can be pushed down to the correct position, thus guaranteeing both the correctness of query semantics and maximizing performance optimization. By separating the identification logic from the pushdown condition configuration, a highly scalable optimization framework is formed. When new node types need to be supported, only the node type needs to be added and the corresponding pushdown conditions configured, without modifying the identification logic, reducing the maintenance cost and expansion difficulty of the database system.

[0017] Furthermore, the database query optimization method of this invention dynamically selects the optimal execution plan by calculating and comparing the execution costs of the initial execution plan with those of each candidate execution path, thus avoiding performance degradation that may be caused by rule-based optimization. Based on the characteristics of different queries and the current state of the system, it adaptively selects the most suitable execution strategy, ensuring the most efficient utilization of system resources such as I / O, memory, and CPU, thereby improving the overall throughput and response speed of the database system.

[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 schematic flowchart of a database query optimization method according to an embodiment of the present invention; Figure 2 This is a schematic flowchart illustrating the identification of pushdownable nodes in a database query optimization method according to an embodiment of the present invention; Figure 3 This is a schematic flowchart of cost evaluation in a database query optimization method according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 5 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; Figure 6 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0020] With the diversification of database application scenarios, complex queries involving row count limitations are becoming increasingly common. In actual business operations, users frequently use syntaxes such as LIMIT and TOP for paginated queries, data sampling, and Top-N analysis (queries that retrieve the top N records from a dataset). These queries often involve complex structures such as multi-table joins, set operations, and parallel execution. However, existing database optimizers have inherent limitations in handling such queries.

[0021] Current mainstream database products use optimizers to generate execution plans. Once the execution plan structure is determined, the execution order and processing scope of each operation node are also fixed, making it difficult to dynamically adjust during execution. This static execution plan mechanism means that row count limit operations are usually placed at the top level of the plan as the last step before the final output. Lower-level nodes must process all data according to the predetermined plan, including complete table scans, join operations, sorting calculations, etc., and the top-level nodes then perform row count pruning after the complete result set is generated.

[0022] The above processing method results in a significant waste of computing resources. A considerable portion of the data processed by lower-level nodes is ultimately discarded due to row count limitations. Scanning, calculating, and transmitting this invalid data consumes substantial I / O, memory, and CPU resources. This problem is particularly pronounced when the data volume is large or the query structure is complex; the size of the intermediate result set can expand dramatically, severely restricting query response speed and database system throughput, thereby impacting user experience and business efficiency.

[0023] Therefore, how to reduce the amount of data processing in lower-level nodes of queries with row count restrictions while ensuring the correctness of query semantics has become a pressing technical problem in the field of database query optimization.

[0024] This embodiment provides a dynamic pushdown scheme for row-count-limited operations based on cost evaluation. While ensuring semantic equivalence of queries, it achieves optimal pushdown position selection for row-count-limited operations by identifying pushable node types, generating candidate execution paths, and evaluating and comparing execution costs.

[0025] Figure 1 This is a schematic flowchart of a database query optimization method according to an embodiment of the present invention, such as... Figure 1 As shown, the query optimization methods for this database can generally include: Step S101: Obtain the SQL query statement containing the result set restriction clause. The result set restriction clause is used to limit the number of rows returned by the query. Step S102: Identify whether the initial query execution plan of the SQL query statement contains pushdown nodes. Pushdown nodes include at least one of the following: non-nullable ends of outer joins, branches of set operations, sub-plans under the convergence node in parallel execution, and data source nodes of Cartesian product joins. Step S103: If the pushdownable node is included, generate a candidate query path that pushes down the result set constraint clause to the pushdownable node; Step S104: Based on the cost evaluation of each candidate query path, select the target query execution plan from the initial query execution plan of the SQL query statement and the multiple query execution plans of the candidate query paths to execute the SQL query statement.

[0026] In this embodiment, the parser receives the SQL query statement submitted by the client and parses the result set restriction clause contained therein. This result set restriction clause limits the number of rows of data ultimately returned to the user, and in specific implementations, it is represented by syntax forms such as LIMIT, TOP, FETCH FIRST, or ROWNUM. The parser generates an abstract syntax tree by performing lexical analysis (splitting the input SQL string into a token sequence) and syntax analysis (organizing the token sequence generated by lexical analysis into a structured representation according to SQL syntax rules), and identifies the position of the row number restriction clause and its restriction value.

[0027] In this embodiment, the query execution plan corresponding to the SQL query statement is traversed and analyzed. A depth-first or breadth-first algorithm can be used to scan the node tree of the execution plan (a tree-like data structure generated by the database system's query optimizer to describe the SQL query execution steps; each node in the tree represents a specific execution operation, and the parent-child relationship between nodes indicates the data flow and execution order). This identifies whether there are pre-defined types of pushdownable nodes, including: detecting outer join nodes and locating their non-nullable ends, i.e., the left table in a left join or the right table in a right join; detecting set operation nodes such as UNION and UNION ALL and enumerating all their branch sub-plans; detecting parallel convergence nodes such as Gather and Merge and obtaining the execution plans of their subordinate parallel subprocesses; detecting Cartesian product join nodes and tracing the base table scan nodes or subquery nodes that provide input data. The identified nodes are then marked as a candidate set of pushdownable nodes.

[0028] In this embodiment, based on the identified pushdown nodes, the database system's query optimizer performs an equivalent transformation on the initial query execution plan. This transformation involves inserting a row count limit node between the pushdown node and its parent node. The row count limit for this node is determined based on the row count of the result set's limit clause and the node type. The specific location and method of the insertion operation differ for different types of pushdown nodes, but all follow the principle of "applying row count limits as early as possible on the upward data flow path." After completing the transformation of all pushdown nodes, the optimizer generates a set of candidate query paths semantically equivalent to the original query. These candidate query paths, together with the initial execution plan, constitute a candidate path set for subsequent cost evaluation and selection.

[0029] In this embodiment, the database system's query optimizer invokes the cost evaluation engine to perform quantitative analysis on the initial execution plan and all candidate execution paths. During this analysis, the cost evaluation engine, based on its internal cost model and combined with statistical information maintained by the database system, comprehensively evaluates the estimated cost of each execution plan. The statistical information includes table data distribution, index selectivity, and physical storage characteristics. The cost model comprehensively considers multiple factors such as input / output data volume, CPU processing overhead, I / O access counts, network transmission overhead, and memory usage to generate a quantitative cost estimate for each execution plan.

[0030] In this embodiment, after cost calculation, the database system's query optimizer performs a comprehensive comparison based on the cost estimates of each execution plan, and determines a target query execution plan from the initial execution plan and all candidate execution paths. The selected target execution plan is passed to the execution engine, which executes the SQL query statement according to the operation order and algorithm described in the target execution plan, and returns the generated result set to the client.

[0031] Based on the above steps, by pushing the result set constraint clause down to the non-nullable end of the outer join, data pruning is achieved before the join operation while ensuring the semantic correctness of the outer join. This significantly reduces the amount of input data for the join operation and improves the execution efficiency of join queries. By pushing the result set constraint clause down to each branch of the set operation, each branch independently performs row count constraint before merging, avoiding invalid pruning after merging all data and reducing the memory consumption and processing overhead of the set operation. By pushing the result set constraint clause down to each sub-plan under the aggregation node in parallel execution, each parallel subprocess returns only a small amount of constrained data, greatly reducing the amount of data transfer between parallel processes and improving the overall performance of parallel queries. By pushing the result set constraint clause down to each data source node of the Cartesian product join, row count constraint is performed in advance at each data source node, reducing the size of the intermediate result of the Cartesian product from the product of the number of rows in each table to the product of the constrained number of rows, fundamentally preventing the explosive growth of the intermediate result set. By selecting the target execution plan from the initial execution plan and each candidate path based on cost evaluation, the optimal execution strategy can be adaptively selected for different queries while ensuring semantic equivalence of queries. This avoids performance degradation caused by forced pushdown and achieves intelligent and dynamic query optimization.

[0032] Figure 2 This is a schematic flowchart illustrating the identification of pushdownable nodes in a database query optimization method according to an embodiment of the present invention, such as... Figure 2 As shown, the steps to identify whether the initial query execution plan of an SQL query statement contains pushdown nodes include: Step S201: Traverse the node tree of the initial query execution plan and identify nodes of a preset type. The preset type of node includes at least one of the following: outer join node, set operation node, convergence node in parallel execution, and Cartesian product join node. Step S202: Nodes of a preset type that meet the pushdown conditions are identified as pushdown nodes. The pushdown conditions are configured according to the node type.

[0033] In this embodiment, the database system's query optimizer traverses the node tree of the initial query execution plan, identifying nodes belonging to preset types. The traversal can employ a depth-first strategy, recursively visiting each child node starting from the root node; or a breadth-first strategy, scanning the node tree layer by layer. During the traversal, the type identifier of each node is checked to identify whether it belongs to a preset candidate type of pushdownable node. Preset types of nodes include: outer join nodes, such as Left Join, Right Join, etc.; set operation nodes, such as Union All, Union, Intersect, Except, etc.; convergence nodes in parallel execution, such as Gather, Merge, Collect, etc.; and Cartesian product join nodes, such as NestedLoop Join without join conditions, etc. For each identified preset type of node, its position, type, and related attribute information are recorded.

[0034] In this embodiment, pushdown nodes are determined from the identified preset type nodes according to the pushdown rules corresponding to the node type. The configuration of the pushdown rules specifies the exact location or substructure suitable for inserting nodes with row count limitations within different node types.

[0035] In some embodiments, determining nodes of a preset type that meet the pushdown conditions as pushdown-capable nodes includes: if the preset type node is an outer join node, determining the non-nullable ends of the outer join node as pushdown-capable nodes; if the preset type node is a set operation node, determining each branch of the set operation node as pushdown-capable nodes; if the preset type node is a convergence node in parallel execution, determining each sub-plan under the convergence node in parallel execution as pushdown-capable nodes; if the preset type node is a Cartesian product join node, determining each data source node that provides input data to the Cartesian product join node as pushdown-capable nodes.

[0036] Based on the above steps, by traversing the node tree of the initial query execution plan, preset type nodes are identified, and corresponding pushdown conditions are configured for different type nodes. This achieves a unified processing framework for various complex query scenarios, significantly improving the universality and practical value of the optimization. By configuring differentiated pushdown conditions according to node type, accurate identification and differentiated processing of different type nodes are achieved, ensuring that row count limit operations are pushed down to the correct position. This guarantees both the correctness of query semantics and maximizes performance optimization. By separating the identification logic from the pushdown condition configuration, a highly scalable optimization framework is formed. When new node types need to be supported, only the node type needs to be added and the corresponding pushdown conditions configured, without modifying the identification logic, reducing the maintenance cost and expansion difficulty of the database system.

[0037] In one feasible implementation, generating candidate query paths that push down the result set constraint clause to pushdownable nodes includes: Based on the type of pushdown node, a row count limit node is inserted between the pushdown node and the upper-level node in the initial query execution plan of the SQL query statement to obtain a candidate query path. The candidate query path is semantically equivalent to the initial query execution plan. The row count threshold of the inserted row count limit node is determined based on the row count limit of the result set limit clause.

[0038] In the above implementation, the insertion operation follows the principle of "applying row count limits as early as possible in the data flow." While maintaining the initial query execution plan structure, an additional row count limit operation is added. This row count limit operation receives the data flow from the pushdown node, performs row count truncation, and then passes the result to the parent node, thereby completing the initial pruning of the data volume before subsequent operations are executed.

[0039] In some embodiments, inserting a row-count-limited node between a pushdown node and its parent node includes: If the pushdown node is a non-nullable end of an outer join, insert a row count limit node on the table scan or subquery corresponding to the non-nullable end; if the pushdown node is a branch of a set operation, insert a row count limit node between the query node corresponding to each branch and the set operation node to which the branch belongs; if the pushdown node is a sub-plan under the aggregation node in parallel execution, insert a row count limit node between the execution node corresponding to each sub-plan and the aggregation node to which the sub-plan belongs; if the pushdown node is a data source node of a Cartesian product join, insert a row count limit node between each data source node and the upper-level join node to which the data source node belongs.

[0040] In the above embodiments, row count limitation nodes are inserted between different types of pushdownable nodes and their parent nodes to achieve precise pushdown of row count limitation operations. For outer join nodes, the row count limitation is pushed down to the non-nullable end, pre-pruning data while ensuring the semantic correctness of the outer join, significantly reducing the amount of input data for the join operation; for set operation nodes, row count limitation is applied independently to each branch before merging, avoiding invalid pruning after merging all data; for parallel execution nodes, row count limitation is applied to each sub-plan, greatly reducing the amount of data transfer between parallel processes; for Cartesian product nodes, row count limitation is applied in advance to each data source node, fundamentally avoiding the explosive growth of intermediate result sets. Through a unified row count limitation node insertion mechanism, an optimization framework covering various complex query scenarios is formed. Regardless of whether the query involves outer joins, set operations, parallel execution, or Cartesian products, optimization can be achieved by inserting row count limitation nodes between pushdownable nodes and their parent nodes, eliminating the need to develop independent optimization logic for different scenarios, reducing the implementation complexity of the database system, and enhancing the maintainability and scalability of the code. By maintaining the semantic equivalence between candidate query paths after insert operations and the initial execution plan, the correctness of the optimization results is ensured. By imposing row count limits as early as possible in the data flow, efficient utilization of database system resources is achieved. Preemptive pruning of invalid data reduces the processing burden on subsequent nodes, lowering I / O overhead, memory usage, and CPU consumption. This enables the database system to handle the same query load with fewer resources, improving overall throughput and response speed.

[0041] In some embodiments, the threshold for the number of rows to be inserted is determined based on the number of rows in the result set constraint clause, including: If the pushdown node is any of the following: a non-nullable end of an outer join, a branch of a set operation, or a data source node connected by a Cartesian product, the row number threshold of the row number limit node is the same as the row number threshold of the result set limit clause. If the pushdown nodes are the sub-plans under the convergence node in parallel execution, the row count threshold of the row count limit node is the row count threshold of the result set limit clause divided by the degree of parallelism and then rounded up.

[0042] In the above embodiments, precise adaptation of the row count limit pushdown is achieved by setting differentiated row count thresholds for different types of nodes. For non-nullable outer join nodes, set operation branches, and Cartesian product data source nodes, the same row count threshold as the original limit clause is used to ensure the sufficiency and effectiveness of data pruning. For parallel sub-plans, a threshold that is divided by the degree of parallelism and then rounded up is used to ensure balanced load across parallel tasks and avoid missing final results due to insufficient data, achieving optimal resource utilization in parallel scenarios. For each sub-plan under the aggregation node in parallel execution, the integrity of the parallel execution results is ensured by using a threshold calculation method that rounds up. When the original limit row count cannot be divided by the degree of parallelism, rounding up ensures that the total amount of data returned by all parallel sub-plans is not less than the original limit row count, leveraging the advantages of parallel execution while ensuring the correctness of query semantics.

[0043] Figure 3 This is a schematic flowchart illustrating cost evaluation in a database query optimization method according to an embodiment of the present invention, as shown below. Figure 3 As shown, based on the cost evaluation of each candidate query path, the steps for selecting the target query execution plan from the initial query execution plan of the SQL query statement and multiple query execution plans of candidate query paths include: Step S301: Determine the first execution cost of the initial query execution plan and the second execution cost of each candidate query path; Step S302: Compare the first execution cost with each of the second execution costs, and select the query execution plan with the lowest execution cost as the target query execution plan.

[0044] In this embodiment, the database system's query optimizer, by invoking the cost evaluation engine, calculates the estimated execution cost of the initial query execution plan and each candidate query path based on the statistical information maintained by the database system. The initial query execution plan corresponds to the first execution cost, and each candidate query path corresponds to the second execution cost. The cost calculation comprehensively considers multiple factors such as input / output data volume, CPU processing overhead, I / O access counts, network transmission overhead, and memory usage to generate a quantified cost estimate.

[0045] In this embodiment, the first execution cost is comprehensively compared with each of the second execution costs, and the execution plan with the lowest execution cost is selected as the target query execution plan. This target query execution plan is passed to the execution engine to execute the SQL query statement, while the remaining query execution plans are discarded. Through this cost-based comparison and selection mechanism, it is ensured that the final executed query execution plan has the optimal predicted performance under the current database system state.

[0046] Based on the above steps, the optimal execution plan is dynamically selected by calculating and comparing the execution costs of the initial execution plan and each candidate execution path, thus avoiding performance degradation that may be caused by rule-based optimization. According to the characteristics of different queries and the current state of the system, the most suitable execution strategy is adaptively selected to make the most efficient use of system resources such as I / O, memory, and CPU, thereby improving the overall throughput and response speed of the database system.

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

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

[0049] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 4 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 5 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 6 This is a schematic diagram of a computer device 30 according to an embodiment of the present invention. Figure 4 As shown, the computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of any of the database query optimization methods described above. Figure 5 As shown, a computer-readable storage medium 20 stores the aforementioned computer program 11, which, when executed by the processor 32, implements the steps of the database query optimization method of any of the above embodiments. Figure 6 As shown, the computer device 30 may include a memory 31, a processor 32, and a computer program 11 stored on the memory 31 and running on the processor 32.

[0050] 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, status 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 a Wide Area Network (WAN), or may be connected to an external computer. 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 status information of the computer-readable program instructions.

[0051] For the purposes of this embodiment, computer program product 10 is a related product that includes computer program 11.

[0052] For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing a computer program 11. It can be any device capable of containing, storing, communicating, propagating, or transmitting the 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.

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

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

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

[0056] 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 database query optimization method, characterized in that, include: Retrieve an SQL query statement that includes a result set restriction clause, which limits the number of rows returned by the query; Identify whether the initial query execution plan of the SQL query statement contains pushdown nodes. The pushdown nodes include at least one of the following: non-nullable ends of outer joins, branches of set operations, sub-plans under the convergence node in parallel execution, and data source nodes of Cartesian product joins. If the pushdownable node is included, generate a candidate query path that pushes down the result set constraint clause to the pushdownable node; Based on the cost evaluation of each candidate query path, a target query execution plan is selected from the initial query execution plan of the SQL query statement and the multiple query execution plans of the candidate query paths to execute the SQL query statement.

2. The database query optimization method according to claim 1, characterized in that, The steps for identifying whether the initial query execution plan of the SQL query statement contains pushdown nodes include: Traverse the node tree of the query execution plan and identify nodes of a preset type, wherein the preset type of node includes at least one of the following: outer join node, set operation node, convergence node in parallel execution, and Cartesian product join node; Nodes of the preset type that meet the pushdown conditions are identified as pushable nodes, and the pushdown conditions are configured according to the node type.

3. The database query optimization method according to claim 2, characterized in that, Determining nodes of the preset type that meet the pushdown conditions as pushable nodes includes: If the node of the preset type is the external connection node, the non-empty end of the external connection node is determined as the pushdown node; If the node of the preset type is the set operation node, then each branch of the set operation node is determined as the pushdown node; If the node of the preset type is the convergence node in the parallel execution, then each sub-plan under the convergence node in the parallel execution is determined as the pushdown node; If the node of the preset type is the Cartesian product connection node, then each data source node that provides input data to the Cartesian product connection node will be determined as the pushdown node.

4. The database query optimization method according to claim 1, characterized in that, Generating candidate query paths that push down the result set constraint clause to the pushdownable node includes: Based on the type of the pushdown node, a row count limit node is inserted between the pushdown node and the upper-level node in the initial query execution plan of the SQL query statement to obtain the candidate query path, which is semantically equivalent to the initial query execution plan. The row count threshold of the inserted row count limit node is determined based on the row count limit of the result set limit clause.

5. The database query optimization method according to claim 4, characterized in that, Inserting a row-count-limited node between the pushdown node and the upper-level node includes: If the pushdown node is a non-nullable end of an outer join, insert the row count limit node on the table scan or subquery corresponding to the non-nullable end; If the pushdown nodes are branches of a set operation, insert the row count limit node between the query node corresponding to each branch and the set operation node to which the branch belongs; If the pushdown node is a sub-plan under the convergence node in parallel execution, the row count limit node is inserted between the execution node corresponding to each sub-plan and the convergence node to which the sub-plan belongs; If the pushdown nodes are data source nodes connected by a Cartesian product, the row count limit node is inserted between each data source node and the upper-level connection node to which the data source node belongs.

6. The database query optimization method according to claim 4, characterized in that, The threshold for limiting the number of rows inserted is determined based on the row limit of the result set constraint clause, including: If the pushdown node is any one of the following: a non-nullable end of an outer join, each branch of a set operation, or each data source node connected by a Cartesian product, the row number threshold of the row number limit node is the same as the row number threshold of the result set limit clause. If the pushdown node is a sub-plan under the convergence node in parallel execution, the row count threshold of the row count limit node is the row count threshold of the result set limit clause divided by the degree of parallelism and then rounded up.

7. The database query optimization method according to claim 1, characterized in that, The step of selecting a target query execution plan from the initial query execution plan of the SQL query statement and the multiple query execution plans of the candidate query paths, based on cost evaluation of each candidate query path, includes: Determine the first execution cost of the initial query execution plan and the second execution cost of each of the candidate query paths; The first execution cost is compared with each of the second execution costs, and the query execution plan with the lowest execution cost is selected as the target query execution plan.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the database query optimization method according to any one of claims 1 to 7.

9. A computer program product, comprising a computer program, characterized in that, When executed by a processor, the computer program implements the steps of the database query optimization method according to any one of claims 1 to 7.

10. A computer device, characterized in that, The system includes a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of the database query optimization method according to any one of claims 1 to 7.

Images