Database query optimization methods and related products

Abstract

This invention provides a database query optimization method and related products. The query optimization method includes: obtaining a query statement containing aggregation sorting instructions; determining whether the query statement contains multiple instructions with sorting operations; if so, obtaining the compatibility of the sorting operations among the multiple instructions, where compatibility indicates whether there is an inclusion relationship between any two sorting keys in the sorting direction; and determining the target sorting path for the query statement based on the compatibility. This query optimization method dynamically optimizes the query based on the compatibility of multiple sorting operations, thereby determining the optimal target sorting path, avoiding redundant sorting, and achieving the goal of improving database query efficiency and reducing resource consumption.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a database query optimization method, a computer-readable storage medium, a computer program product, and a computer device. Background Technology

[0002] In existing database implementations, for aggregate queries with sorting operations (ORDER BY / DISTINCT) (referred to as aggregate sorting instructions or aggregate sorting operations), when multiple sorting operations exist (e.g., containing GROUP BY grouping sorting operations, nested multiple aggregate sorting operations, etc.), the sorting process generally suffers from the following performance bottlenecks: 1. Additional computational waste: For example, in cases involving GROUP BY grouping and sorting operations, the executor must sort the same column again in the aggregate sorting operation after grouping and sorting the GROUP BY columns. As the number of groups increases, the number of operations on the sorting structure (generating, initializing, and destroying) also increases, leading to additional CPU overhead.

[0003] 2. Overhead of Redundant Sorting: For example, in the case of nested aggregation sorting operations, the same column may be fully sorted multiple times, triggering redundant sorting. In addition, during aggregation, the sorting operation needs to store all tuples in the tuple memory before sorting. At this time, due to the large amount of data to be stored, the external sorting process may be triggered multiple times, resulting in additional I / O overhead. Summary of the Invention

[0004] One object of the present invention is to provide a database query optimization method, a computer-readable storage medium, a computer program product, and a computer device to improve database query efficiency and reduce resource consumption.

[0005] Specifically, according to one aspect of the present invention, the present invention provides a database query optimization method, comprising: Retrieve the query statement containing the aggregate sort command; Determine whether the query statement contains multiple instructions with sorting operations; If so, then obtain the compatibility status of multiple sorting operations containing the sorting operation instructions, wherein the compatibility status indicates whether there is an inclusion relationship between any two sorting keys of the multiple sorting operations in the sorting direction; Based on the compatibility criteria, the target sorting path for the query statement is determined.

[0006] Optionally, obtaining the compatibility status of multiple sorting operations containing the sorting operation instructions includes: Determine whether a group sorting instruction is included among multiple instructions containing the sorting operation; If included, then obtain the first sorting key of the sorting operation of the grouping sorting instruction, and obtain the second sorting key of the sorting operation of the aggregation sorting instruction itself; Merge the first sort key and the second sort key; Determine whether there is an inclusion relationship between any two sort keys in the sorting direction after merging, and use the determination result as the result of the compatibility situation.

[0007] Optionally, after determining whether the plurality of instructions containing the sorting operation includes a grouping sorting instruction, the method further includes: If the grouping sorting instruction is not included, then the second sorting key of the sorting operation of each of the aggregation sorting instructions is obtained respectively; Determine whether there is an inclusion relationship between any two pairs of the multiple second sorting keys in the sorting direction, and use the determination result as the result of the compatibility situation.

[0008] Optionally, determining the target sorting path for the query statement based on the compatibility criteria includes: In the case where there is an inclusion relationship in the compatibility condition, the sort key that covers the most aggregation operators is determined from the sort keys of the multiple sorting operations and used as the target sorting path key for the target sorting path.

[0009] Optionally, after determining the sort key that covers the most aggregation operators among the sort keys of the plurality of sorting operations as the target sorting path key for the target sorting path, the method further includes: Set a pre-sorting flag for the aggregated operations that are covered; The target sorting path key is pushed down to the target sorting operation, which is the sorting operation that is below the aggregation operation that is set with the pre-sorting identifier.

[0010] Optionally, after determining the target sorting path of the query statement based on the compatibility situation, the method further includes: The executor is used to determine whether the aggregation operation to be executed has the pre-sorting flag set; If not set, the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs is performed; If set, the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs is skipped.

[0011] Optionally, after the step of skipping the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs, the following steps are included: Determine whether the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs includes a deduplication operation; If so, the transformation function of the aggregation operation to be executed is called to remove duplicate data.

[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 the database query optimization method described above.

[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, including 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 described above.

[0015] The database query optimization method of the present invention dynamically optimizes the query based on the compatibility of multiple sorting operations, thereby determining the optimal target sorting path, avoiding redundant sorting, and achieving the goal of improving database query efficiency and reducing resource consumption.

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

[0017] 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 query optimization method according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating the process of obtaining the target sorting path key in a query optimization method according to an embodiment of the present invention. Figure 3 This is a flowchart illustrating the process of setting a pre-sorting identifier for an aggregation operation in a query optimization method according to an embodiment of the present invention. Figure 4 This is a flowchart illustrating the executor optimization of an aggregation operation using a query optimization method according to an embodiment of the present invention. Figure 5This is a flowchart illustrating the query optimization method according to another embodiment of the present invention during the planner optimization phase. Figure 6 This is a flowchart illustrating the query optimization method according to another embodiment of the present invention during the executor optimization phase. Figure 7 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 8 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 9 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0018] In existing database implementations, aggregate queries with sorting operations (ORDER BY / DISTINCT) are called aggregate sorting instructions or aggregate sorting operations, for example: Example 1: select string_agg (c1, '' order by c1) from t1 group by c2; Example 2: select string_agg (distinct c1, '') from t1 group by c2; Example 3: select string_agg (c1, '' order by c2), string_agg (c2, ''order by c2), string_agg (distinct c3, '') from t1; The sorting process involved is as follows: For Example 1: First, group and sort column c2 (group by c2), then sort the data in column c1 of each group in the string_agg aggregation (order by c1).

[0019] For Example 2: First, group and sort column c2 (group by c2), then in the string_agg aggregation, sort the data in column c1 of each group to remove duplicates (distinct c1).

[0020] For Example 3: 3 string_agg aggregation operations, the first two string_agg aggregations each perform a full sort on column c2 (order by c2), and the third string_agg aggregation performs a deduplication sort on column c3 (distinct c3).

[0021] As can be seen from the above sorting process, the following performance bottlenecks generally exist: 1. Additional computational waste: As in Examples 1 and 2, after the executor sorts the data in column c2, it also needs to sort or remove duplicates from the data in c1 within the group during aggregation. As the number of groups increases, the number of operations on the sorting structure (generating, initializing, and destroying) also increases, leading to additional CPU overhead.

[0022] 2. Overhead of Redundant Sorting: As shown in Example 3, the first two string_agg aggregations require a full sort on column c2 each time, triggering redundant sorting. Additionally, during aggregation, the sorting operation requires storing all tuples in a tuple store before sorting. This may trigger multiple external sorting processes due to the large amount of data stored, resulting in extra I / O overhead.

[0023] One object of the present invention is to provide a database query optimization method, a computer-readable storage medium, a computer program product, and a computer device to improve database query efficiency and reduce resource consumption.

[0024] Figure 1 This is a flowchart illustrating a database query optimization method according to an embodiment of the present invention. The method generally includes: S100, retrieve the query statement containing the aggregation sorting instruction; S200, Determine if the query statement contains multiple instructions with sorting operations; S300, if so, then obtain the compatibility of sorting operations of multiple instructions containing sorting operations. The compatibility indicates whether there is an inclusion relationship between each pair of sorting keys in the sorting direction. S400 determines the target sorting path for the query statement based on compatibility considerations.

[0025] Aggregate sorting instructions themselves contain sorting operations, such as the ORDER BY clause (for sorting operations) in Examples 1 and 3 above, and the DISTINCT clause (for sorting and deduplication operations) in Examples 2 and 3 above.

[0026] There are two main scenarios when a query contains multiple sorting instructions: one is that it includes other sorting instructions in addition to the aggregate sorting instruction, such as the group by instruction in Examples 1 and 2; the other is that it includes multiple aggregate sorting instructions, such as the multiple string_agg instructions in Example 3.

[0027] In this embodiment, query optimization is dynamically performed based on whether a query containing an aggregation sorting instruction has multiple instructions containing sorting operations. If it is determined that the query containing an aggregation sorting instruction does not have multiple instructions containing sorting operations, then there is no need to consider redundant sorting optimizations, and existing optimization methods can be used to optimize the query. If it is determined that the query containing an aggregation sorting instruction has multiple instructions containing sorting operations, then redundant sorting optimizations need to be considered, and further optimization is performed based on the compatibility of the multiple sorting operations.

[0028] The compatibility of sorting operations refers to whether two sorting operations are contained within each other in terms of sorting direction. For example, sorting operation 1 has a sort key of {c1} and a sorting direction of c1; sorting operation 2 has a sort key of {c1,c2} and a sorting direction of c1->c2. Since sorting operation 2 contains sorting operation 1 in the sorting direction, then sorting operation 1 and sorting operation 2 are compatible. However, if sorting operation 2 has a sort key of {c2,c1} and a sorting direction of c2->c1, then sorting operation 1 and sorting operation 2 are not compatible.

[0029] If any two sorting operations in a given sorting operation do not have an inclusion relationship in the sorting direction, it indicates that the query statement does not have redundant sorting issues, and existing optimization methods can be used to optimize the query statement. If two or more sorting operations in a given sorting operation have an inclusion relationship in the sorting direction, it indicates that the sorting operations are redundant. In this case, each redundant sort needs to be optimized based on specific compatibility considerations to determine the optimal target sorting path. This avoids redundant sorting, thereby improving database query efficiency and reducing resource consumption.

[0030] In some embodiments of the query optimization method of the present invention, such as Figure 2 As shown, the compatibility of sorting operations for obtaining multiple instructions containing sorting operations is as follows: S311, Determine whether a group sort instruction (group by) is included among multiple instructions containing sorting operations; S313, if included, then obtain the first sorting key of the sorting operation of the group sorting instruction, and obtain the second sorting key of the sorting operation of the aggregation sorting instruction itself. S315, merge the first sorting key and the second sorting key; S317, determine whether there is an inclusion relationship between any two sort keys in the sorting direction after merging, and use the judgment result as the result of the compatibility situation.

[0031] The following explanation uses Example 1 as an example. After determining that there are multiple sorting operations in Example 1, including grouping sorting instructions, we first obtain the sorting operation of the aggregation sorting instruction itself (order by c1) and the sorting operation of the grouping sorting instruction (group by c2). Next, we generate the grouping sorting key (i.e., the key {c2} associated with group by) and the sorting key of each aggregation sorting operation itself (i.e., the key {c1} associated with order by).

[0032] Next, the sort keys of each aggregation sort operation are logically merged with the group sort keys. In Example 1, the sort key of the merged group sort key is {c2}, and the sort key of the merged aggregation sort operation itself is {c2,c1}. The two have an inclusion relationship in the sorting direction. Therefore, the compatibility of the query statement in Example 1 is determined to be either an inclusion relationship or compatibility.

[0033] This embodiment determines whether the sorting operation instruction includes a grouping sorting instruction. If a grouping sorting instruction is included, the first sorting key and the second sorting key are merged. This allows for a convenient and accurate determination of whether there is an inclusion relationship between any two sorting keys in the sorting direction after merging, thus obtaining the compatibility result.

[0034] In some embodiments of the query optimization method of the present invention, such as Figure 2 As shown, after determining whether a group sort instruction is included among multiple instructions containing sorting operations, the process also includes: S321, if grouping sorting instructions are not included, then obtain the second sorting key of the sorting operation of each aggregation sorting instruction itself; S323, determine whether there is an inclusion relationship between any two pairs of the multiple second sort keys in the sorting direction, and use the judgment result as the result of the compatibility situation.

[0035] The following explanation uses Example 3 as an example. After determining that Example 3 contains multiple sorting operations, excluding grouping sorting instructions, it indicates that the query statement includes two or more aggregate sorting instructions. Therefore, we first obtain the sorting operations for each aggregate sorting instruction itself (order by c2, order by c2, istinct c3, respectively). Next, we generate the sort keys for each aggregate sorting operation itself ({c2}, {c2}, {c3}, respectively).

[0036] Next, it is determined whether the sort keys of each aggregation sort operation are contained in each other in the sorting direction. Since the sort keys of the first two aggregation sort operations are the same, the compatibility of the query statement in Example 3 is determined to be either contained or compatible.

[0037] This embodiment determines whether the sorting operation instruction includes a grouping sorting instruction. If the grouping sorting instruction is not included, it determines whether there is an inclusion relationship between each pair of the second sorting keys in the sorting direction, thus conveniently and accurately obtaining the compatibility result.

[0038] In some embodiments of the query optimization method of the present invention, such as Figure 2 As shown, based on compatibility considerations, the target sorting path for the query statement is determined, including: S411, in the case where there is an inclusion relationship, determine the sort key that covers the most aggregation operator among the sort keys of multiple sorting operations, and use it as the target sorting path key for the target sorting path.

[0039] Let's continue with Example 1 above. If the planner determines that there is an inclusion relationship in Example 1, it can identify the sort key {c2, c1} that covers the most aggregation operations, use that sort key as the target sort path key, and plan the target sort path.

[0040] In this embodiment, by determining the sorting key that covers the most aggregation operations as the target sorting path key, multiple aggregation operations can be sorted through a single explicit sorting operation, thereby avoiding redundant sorting.

[0041] In some embodiments of the query optimization method of the present invention, such as Figure 3 As shown, after determining the target sort path key that covers the most aggregation operators among the sort keys of multiple sorting operations, the process further includes: S413, Set a pre-sort flag for the covered aggregation operations; S415, push the target sorting path key down to the target sorting operation, the target sorting operation being the sorting operation below the corresponding aggregation operation with a pre-sorting flag set.

[0042] In this embodiment, a pre-sorting flag is set to distinguish whether each aggregation operation in the query statement is pre-sorted and optimized.

[0043] For aggregation operations that satisfy the target sorting path key, their pre-sort flag can be set to true. Then, the target sorting path key is pushed down to the lower-level sorting operator of the aggregation operation. This means that sorting operations that should have been performed during the aggregation process will be merged into the lower-level sorting operator, eliminating redundant sorting during aggregation. When a query statement contains multiple aggregation operations, for aggregation operations where the pre-sort flag is not true, the original sorting logic is retained during aggregation.

[0044] In some embodiments of the query optimization method of the present invention, such as Figure 4As shown, after determining the target sorting path of the query statement based on compatibility, the process also includes: S511, Use the executor to determine whether the aggregation operation to be executed has a pre-sorting flag set; S513, if set, skips the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs; S519, if not set, then the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs is performed.

[0045] This embodiment is used for executor optimization. Once the executor determines that the pre-sorting flag of the current aggregation operation is true, the current aggregation process can directly use the sorted data of the lower-level sorting operator. This process is supported by the database executor itself, so there is no need to modify the relevant code. It is only necessary to skip (disable) the sorting process when initializing the aggregation and processing the final aggregation result.

[0046] In this embodiment, during the planner optimization phase, by using dynamic optimal path selection and setting pre-sorting flags for aggregation operations that satisfy the target sorting path key, conditions can be provided for skipping redundant sorting during the executor optimization phase. During the executor optimization phase, by determining whether to perform explicit sorting based on the pre-sorting flag during aggregation, aggregation operations with pre-sorting flags can directly utilize the ordered data provided by upstream operator nodes, eliminating the need for further sorting and thus improving query execution efficiency.

[0047] In practical use, by optimizing the queries in Examples 1-3 above, redundant sorting is avoided during the aggregation sorting process, which improves query performance by 15% and effectively reduces memory and I / O overhead, avoiding the process of storing a large number of intermediate tuples in the tuple memory during sorting operations.

[0048] In some embodiments of the query optimization method of the present invention, such as Figure 4 As shown, after skipping the sorting operation step of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs, the process includes: S515, determine whether the sorting operation of the aggregation sorting instruction to which the aggregation operation to be executed belongs includes a deduplication operation; S517, if so, then call the transformation function of the aggregation operation to be executed to remove duplicate data.

[0049] This embodiment describes a method for handling aggregation operations with pre-sorting flags that inherently involve deduplication. When an aggregation operation with pre-sorting flags inherently involves deduplication (e.g., if a DISTINCT clause exists, the aggregation operation is also called a DISTINCT aggregation), since the aggregation process skips the sorting operation, corresponding deduplication logic needs to be added.

[0050] For example, for a single-column DISTINCT aggregation, the current value after deduplication sorting can be compared with the previous value, and the aggregation operation's transformation function can be called if the current value differs from the previous value. For a multi-column DISTINCT aggregation, the current tuple after deduplication sorting can be compared with the previous tuple, and the aggregation operation's transformation function can be called if the current tuple differs from the previous tuple. In this way, deduplication is achieved for aggregation operations that skip the sorting operation.

[0051] In some embodiments of the query optimization method of the present invention, such as Figure 5-6 As shown, query optimization methods include: During the planner optimization phase: S611, retrieve aggregate sort query statements containing multiple sorting operations; S612, determine whether the query statement contains a GROUP BY clause; if it does, execute S613; if it does not, execute S615. S613, retrieve the grouping and sorting key of the GROUP BY clause; S614, merge the sort key of each aggregation sort operation with the group sort key; execute S616; S615, obtain the sort key for each aggregation sort operation itself; execute S616; S616, perform compatibility comparison of each sorting key, and generate the optimal target sorting path key; S617, Determine whether the current aggregation operation is overwritten by the target sorting path key; if so, execute S618. S618, Set the pre-sort flag for the current aggregation operation to true; S619, push down the target sorting path key.

[0052] During the actuator optimization phase: S621, Determine if the pre-sorting flag of the current aggregation operation is true; if so, execute S622. S622, Determine if the current aggregation operation is a DISTINCT aggregation; if so, execute S623; S623, perform deduplication processing according to whether the DISTINCT aggregated single column or multiple columns.

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

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

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

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

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

[0058] For the purposes of this embodiment, the 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 computer program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the 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.

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

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

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

[0062] 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 the query statement containing the aggregate sort command; Determine whether the query statement contains multiple instructions with sorting operations; If so, then obtain the compatibility status of multiple sorting operations containing the sorting operation instructions, wherein the compatibility status indicates whether there is an inclusion relationship between any two sorting keys of the multiple sorting operations in the sorting direction; Based on the compatibility criteria, the target sorting path for the query statement is determined.

2. The query optimization method according to claim 1, characterized in that, The process of obtaining the compatibility status of multiple sorting operations containing the sorting operation includes: Determine whether a group sorting instruction is included among multiple instructions containing the sorting operation; If included, then obtain the first sorting key of the sorting operation of the grouping sorting instruction, and obtain the second sorting key of the sorting operation of the aggregation sorting instruction itself; Merge the first sort key and the second sort key; Determine whether there is an inclusion relationship between any two sort keys in the sorting direction after merging, and use the determination result as the result of the compatibility situation.

3. The query optimization method according to claim 2, characterized in that, After determining whether the multiple instructions containing the sorting operation include a group sorting instruction, the method further includes: If the grouping sorting instruction is not included, then the second sorting key of the sorting operation of each of the aggregation sorting instructions is obtained respectively; Determine whether there is an inclusion relationship between any two pairs of the multiple second sorting keys in the sorting direction, and use the determination result as the result of the compatibility situation.

4. The query optimization method according to claim 1, characterized in that, Determining the target sorting path for the query statement based on the compatibility criteria includes: In the case where there is an inclusion relationship in the compatibility condition, the sort key that covers the most aggregation operators is determined from the sort keys of the multiple sorting operations and used as the target sorting path key for the target sorting path.

5. The query optimization method according to claim 4, characterized in that, After determining the sort key that covers the most aggregation operators among the sort keys of the plurality of sorting operations, and using it as the target sorting path key for the target sorting path, the method further includes: Set a pre-sorting flag for the aggregated operations that are covered; The target sorting path key is pushed down to the target sorting operation, which is the sorting operation that is below the aggregation operation that is set with the pre-sorting identifier.

6. The query optimization method according to claim 5, characterized in that, After determining the target sorting path of the query statement based on the compatibility situation, the method further includes: The executor is used to determine whether the aggregation operation to be executed has the pre-sorting flag set; If not set, the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs is performed; If set, the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs is skipped.

7. The query optimization method according to claim 6, characterized in that, After the step of skipping the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs, the following is included: Determine whether the sorting operation of the aggregation sorting instruction itself to which the aggregation operation to be executed belongs includes a deduplication operation; If so, the transformation function of the aggregation operation to be executed is called to remove duplicate data.

8. A computer-readable storage medium, characterized in that, It stores a computer program that, when executed by a processor, implements the steps of the database query optimization method as described in 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 as described in 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