Query optimization method, storage medium and equipment

Abstract

The invention relates to a database technology, in particular to a query optimization method, a storage medium and equipment. The query optimization method comprises the following steps: obtaining a to-be-processed query statement; judging whether the to-be-processed query statement contains a window function or not; and if yes, converting the window function into transverse connection, and generating an optimized query statement based on the transverse connection. According to the query optimization method, the window function in the query statement is converted into the transverse connection, so that the window function in the query statement is eliminated, the corresponding grouping and sorting operation is removed, the problem of high performance loss caused by excessive unnecessary grouping and sorting operation due to execution of the window function is avoided, and the query efficiency is improved. And the execution efficiency of the query statement is improved, so that the performance of the database is improved.

Description

Technical Field

[0001] The present invention relates to database technology, and particularly to a query optimization method, a storage medium, and a device. Background Art

[0002] With the development of database technology, database query optimization has become a key link in improving data processing efficiency. In database queries, window functions are a powerful analysis tool that allows users to perform calculations without changing the number of rows in the result set.

[0003] However, when executing window functions, grouping and sorting operations are usually required. In the case of a large amount of data, if the window function is directly executed, the grouping and sorting operations will take a lot of time, which may lead to performance bottlenecks. Even if only a very small part of the result set is ultimately needed, all data must be processed, which means that the previous grouping and sorting operations are meaningless and consume a large amount of unnecessary computing resources. The execution efficiency of the query statement is low, thus affecting the performance of the database. Summary of the Invention

[0004] In view of the above problems, a query optimization method, a storage medium, and a device are proposed to overcome or at least partially solve the above problems.

[0005] An object of the present invention is to provide a query optimization method to improve query efficiency.

[0006] A further object of the present invention is to improve the accuracy of query optimization.

[0007] Another further object of the present invention is to reduce the amount of data in the result set to further improve query efficiency.

[0008] Specifically, the present invention provides a query optimization method, including:

[0009] Obtain a query statement to be processed;

[0010] Determine whether the query statement to be processed contains a window function;

[0011] If so, convert the window function into a cross join and generate an optimized query statement based on the cross join.

[0012] Optionally, converting the window function into a cross join and generating an optimized query statement based on the cross join includes:

[0013] Record the grouping clause and / or sorting clause in the window function;

[0014] Generate a new subquery based on a grouping clause and / or a sorting clause, where the new subquery includes all table structures in the old subquery corresponding to the query statement to be processed and the window function;

[0015] Perform a horizontal join on the new subquery and the old subquery to generate an optimized query statement.

[0016] Optionally, generating a new subquery based on a grouping clause and / or a sorting clause includes:

[0017] Construct a new subquery;

[0018] In the case where the window function contains a grouping clause, convert the grouping clause into a join condition for the new subquery; and / or

[0019] In the case where the window function contains a sorting clause, add the sorting clause to the new subquery.

[0020] Optionally, after the step of constructing the new subquery, the query optimization method further includes:

[0021] In the case where the window function contains a filtering operation on the result set, convert the filtering operation into a restriction clause and add the restriction clause to the new subquery.

[0022] Optionally, the join type of the horizontal join is an inner join.

[0023] Optionally, the optimized query statement outputs columns from the old subquery.

[0024] Optionally, determining whether the query statement to be processed contains a window function includes:

[0025] Obtain the query selection clause in the query statement to be processed;

[0026] Determine whether the query selection clause to be processed contains a window function;

[0027] If so, determine that the query statement to be processed contains a window function;

[0028] If not, determine that the query statement to be processed does not contain a window function.

[0029] Optionally, after the step of determining whether the query statement contains a window function, the query optimization method further includes:

[0030] In the case where the query statement does not contain a window function, skip the optimization operation on the query statement.

[0031] According to another aspect of the present invention, there is also provided a machine-readable storage medium, on which a machine-executable program is stored, and when the machine-executable program is executed by a processor, the above-mentioned query optimization method is implemented.

[0032] According to another aspect of the present invention, there is also provided a computer device, including a memory, a processor, and a machine-executable program stored on the memory and running on the processor, and when the processor executes the machine-executable program, the query optimization method of any of the above is implemented.

[0033] The query optimization method of the present invention realizes the optimization of the query statement by obtaining the query statement to be processed, determining whether the query statement to be processed contains a window function, and when the query statement contains a window function, converting the window function into a cross join, and generating an optimized query statement based on the cross join. Thus, the query optimization method of the present invention realizes the early filtering of data by converting the window function in the query statement into a cross join, eliminates the window function in the query statement, removes the corresponding grouping and sorting operations, and avoids the problem of large performance loss caused by excessive unnecessary grouping and sorting operations due to the execution of the window function, improves the execution efficiency of the query statement, thereby improving the query efficiency, and further improving the performance of the database.

[0034] Furthermore, the query optimization method of the present invention records the grouping clause and / or sorting clause in the window function, generates a new subquery based on the grouping clause and / or sorting clause, and performs a cross join on the new subquery and the old subquery to generate an optimized query statement, realizing the accurate acquisition of the calculation range and order based on the grouping clause and sorting clause in the window function to accurately rewrite the query statement, ensuring that the new subquery can correctly simulate the behavior of the window function, improving the query accuracy of the optimized query statement, and thus improving the query accuracy.

[0035] Even further, the query optimization method of the present invention, when the window function contains a filtering operation on the result set, converts the filtering operation into a limit clause and adds the limit clause to the new subquery, realizing that the sorting can be terminated in advance when the new subquery is executed, reducing the data volume of the result set. For the scenario where the data volume is large but only a very small part of the result set is actually needed, the overall calculation amount is effectively reduced, thereby significantly reducing the query execution time and resource consumption, and further improving the query efficiency.

[0036] Those skilled in the art will become more apparent about the above and other objects, advantages and features of the present invention according to the following detailed description of the specific embodiments of the present invention in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Some specific embodiments of the present invention will be described in detail hereinafter with reference to the accompanying drawings in an exemplary but non-limiting manner. The same reference numerals in the drawings denote the same or similar components or parts. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0038] Figure 1 is a schematic diagram of a query optimization method according to an embodiment of the present invention;

[0039] Figure 2 is a flowchart of a query optimization method according to an embodiment of the present invention;

[0040] Figure 3 is a schematic structural diagram of a machine-readable storage medium according to an embodiment of the present invention; and

[0041] Figure 4 is a schematic structural diagram of a computer device according to an embodiment of the present invention. Detailed Embodiments

[0042] Exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present invention are shown in the drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided so that the present disclosure can be more thoroughly understood and the scope of the present invention can be fully conveyed to those skilled in the art.

[0043] To solve the above technical problems, an embodiment of the present invention proposes a query optimization method. Figure 1 is a schematic diagram of a query optimization method according to an embodiment of the present invention. As Figure 1 shown, the query optimization method of the embodiment of the present invention generally may include:

[0044] Step S102: Obtain a query statement to be processed. It should be noted that the query statement may include a query selection clause (or called a SELECT clause), and the SELECT clause may include aggregate functions, scalar functions, window functions, etc.

[0045] Step S104: Determine whether the query statement to be processed contains a window function. If so, execute step S106. Among them, the window function is used to perform complex calculations in the query result, allowing data to be grouped, sorted, and calculated without changing the original data. Common window functions may include ROW_NUMBER(), RANK(), SUM(), etc., and at the same time, the OVER clause is required to define the window range.

[0046] Step S106: Convert the window function into a lateral join and generate an optimized query statement based on the lateral join. It should be noted that the lateral join (or called Lateral Join) can filter data in advance through the join condition and subquery to eliminate the full-table grouping and sorting operations required by the window function, thereby avoiding inefficient multiple queries or redundant operations.

[0047] Based on the above steps, the query optimization method of this embodiment can effectively optimize query statements containing window functions, and realizes rewriting query statements containing window functions into query statements based on horizontal joins.

[0048] Therefore, the query optimization method of the embodiment of the present invention obtains the query statement to be processed, determines whether the query statement to be processed contains a window function, and when the query statement contains a window function, converts the window function into a horizontal join and generates an optimized query statement based on the horizontal join, thus realizing the optimization of the query statement. Therefore, the query optimization method of the present invention eliminates the window function in the query statement by converting the window function in the query statement into a horizontal join, removes the corresponding grouping and sorting operations, avoids the performance loss caused by excessive unnecessary grouping and sorting operations due to the execution of the window function, improves the execution efficiency of the query statement, and thus improves the performance of the database.

[0049] In some embodiments, after the above step S104, the query optimization method of this embodiment may further include the following steps: when the query statement does not contain a window function, skip the optimization operation on the query statement.

[0050] Therefore, the query optimization method of the embodiment of the present invention avoids redundant analysis of query statements that do not contain window functions, saves CPU and memory resources, thereby reducing the database system overhead. At the same time, it reduces the potential errors that may be introduced by complex optimizers, resulting in query optimization failures, and ensures the stability of the database system.

[0051] In some embodiments, the above step S104 may include the following steps: obtain the query selection clause in the query statement to be processed; determine whether the query selection clause to be processed contains a window function; if so, determine that the query statement to be processed contains a window function; if not, determine that the query statement to be processed does not contain a window function.

[0052] Based on the above steps, when obtaining the query statement to be processed, first extract the SELECT clause in the query statement to be processed, and then check whether the SELECT clause contains a window function. Specifically, by identifying the function calls in the SELECT clause and matching the window function features through regular expressions or grammar rules (for example, OVER clause, PARTITION BY clause, ORDER BY clause, etc.), if the match is successful, it is confirmed that the window function is detected.

[0053] Thus, after receiving a query statement to be processed, the query optimization method according to the embodiments of the present invention can directly check whether a window function is included in the SELECT clause, which improves the accuracy and convenience of determining whether the query statement to be processed contains a window function, thereby further improving the query optimization efficiency.

[0054] In some embodiments, the above step S106 may include the following steps: recording the grouping clause and / or sorting clause in the window function; generating a new subquery based on the grouping clause and / or sorting clause, where the new subquery includes all table structures in the old subquery corresponding to the query statement to be processed and the window function; and horizontally joining the new subquery and the old subquery to generate an optimized query statement.

[0055] Specifically, the step of recording the grouping clause and / or sorting clause in the window function may include extracting the grouping clause (or called PARTITION BY clause) and sorting clause (or called ORDER BY clause) in the window function, so as to record the window function parameters and facilitate using the grouping clause and sorting clause as key conditions for generating subqueries later.

[0056] In addition, the new subquery includes all table structures of the old subquery, such as the FROM clause, etc., and through the precise mapping of grouping and sorting conditions, it ensures that the optimized query result is consistent with the window function result.

[0057] Thus, the query optimization method according to the embodiments of the present invention, by recording the grouping clause and / or sorting clause in the window function, generating a new subquery based on the grouping clause and / or sorting clause, and horizontally joining the new subquery and the old subquery to generate an optimized query statement, realizes accurately obtaining the calculation range and order based on the grouping clause and sorting clause in the window function to accurately rewrite the query statement, ensures that the new subquery can correctly simulate the behavior of the window function, improves the query accuracy of the optimized query statement, and thus improves the query accuracy.

[0058] In some embodiments, the step of generating a new subquery based on the grouping clause and / or sorting clause may further include the following steps: constructing a new subquery; in the case where the window function contains a grouping clause, converting the grouping clause into a join condition of the new subquery; and / or in the case where the window function contains a sorting clause, adding the sorting clause to the new subquery.

[0059] Specifically, the step of generating a new subquery based on the grouping clause and / or the sorting clause can be specifically executed as follows: construct a new subquery, where the new subquery includes all the table structures of the old subquery; convert the partition by clause in the window function into a join condition; if there is an order by clause in the window function, add the order by clause to the new subquery.

[0060] Further, after the step of constructing the new subquery, the query optimization method of the embodiment of the present invention may further include the following steps: in the case where the window function contains a filtering operation on the result set, convert the filtering operation into a limit clause, and add the limit clause to the new subquery.

[0061] That is to say, if the semantics of the window function is to only obtain the first few rows of the result set, a limit... offset... clause can be added to reduce the amount of data in the result set. It should be noted that the limit clause may include a limit clause and an offset clause, etc.

[0062] In a specific embodiment, taking the window function as "select *, row_number() over(partition by a order by b) rn from t1" as an example, the generation process of the new subquery may include a grouping clause conversion operation, a sorting clause integration operation, and a filtering operation push-down operation. The grouping clause conversion operation may include: if the window function contains PARTITION BY a, generate a join condition t1.a = v1.a in the new subquery, and ensure that the left table (such as v1) contains the unique values of the grouping columns (implemented by SELECT DISTINCT a). The sorting clause integration operation includes: adding ORDER BY b to the new subquery. The filtering operation push-down operation may include: if the original query contains a filtering on the result of the window function (such as rn = 1), convert it to LIMIT 1, and add the LIMIT clause to the new subquery to terminate the sorting process in advance to reduce the intermediate result set.

[0063] Thus, the query optimization method of the embodiment of the invention realizes the precise mapping of the grouping and sorting conditions by converting the grouping clause into a join condition and adding the sorting clause to the new subquery, further ensuring the consistency between the optimized query result and the window function result.

[0064] In addition, in the query optimization method of the embodiments of the present invention, when the window function contains a filtering operation on the result set, the filtering operation is converted into a restriction clause, and the restriction clause is added to a new subquery, so as to achieve early termination of sorting when executing the new subquery, reduce the data volume of the result set, and for scenarios with a large amount of data but actually only a very small part of the result set, effectively reduce the overall computation amount, thereby significantly reducing the query execution time and resource consumption, and further improving the query efficiency.

[0065] In some embodiments, the join type of the lateral join (or called Lateral Join) in the above step S106 may be an inner join (or called Inner Join) to combine the characteristics of the lateral join and the inner join, so as to achieve an innerlateral join. It should be noted that the lateral keyword is used to allow the subquery on the right side to reference the columns of the left table to form a lateral join. Inner Join is used to only return the rows that meet the join conditions.

[0066] Thus, the lateral join of the present invention can achieve only returning the rows that match between the left table and the right subquery, and the inner join strictly matches the join conditions, avoiding the introduction of NULL values or invalid data, improving the data accuracy. In addition, when the result of the right subquery is sparse, the query efficiency is greatly improved.

[0067] In some embodiments, the optimized query statement in the above step S106 outputs the columns from the old subquery. Specifically, in the query optimization method of the embodiments of the present invention, while performing an inner lateral join between the old subquery and the new subquery, only the columns from the old subquery are output externally. That is, the specified output columns of the optimized query statement only come from the old subquery query, ignoring the additional columns of the new subquery.

[0068] Thus, in the query optimization method of the embodiments of the present invention, by ensuring that the output modes (column names, data types) of the queries before and after optimization are exactly the same, it is convenient to compare the query results before and after optimization to verify the correctness of the query optimization, and at the same time avoid the accidental exposure of the intermediate values of the temporary calculation, improving the data security.

[0069] In a specific embodiment, the query statement to be processed is:

[0070]

[0071] Performing an explain analyze on the above query statement to be processed, the original planned execution time is about 30 seconds.

[0072] In this embodiment, the query optimization method of the present invention may include the following steps:

[0073] 1. Check whether the SELECT clause in the query statement to be processed contains a window function. If it contains a window function, then check and record the PARTITION BY and ORDER BY clauses of the window function.

[0074] 2. Construct a new subquery. The new subquery contains all the table structures of the old subquery. At the same time, convert the PARTITION BY clause in the window function into a join condition. If there is an ORDER BY clause in the window function, add the ORDER BY clause to the new subquery. At the same time, if the semantics of the window function is to only obtain the first few rows of the result set, add a LIMIT... OFFSET... clause.

[0075] 3. Perform an inner lateral join between the old subquery and the new subquery, and only output the columns from the old subquery externally.

[0076] Based on the above steps, the optimized query statement is:

[0077]

[0078]

[0079] Perform EXPLAIN ANALYZE on the above optimized query statement. The execution time of the optimized plan is about 17 seconds, that is, the execution time is reduced from 30 seconds to 17 seconds before and after optimization.

[0080] Therefore, the query optimization method of the present invention mainly aims at rewriting query statements containing window functions, generates a new subquery with the same structure according to the PARTITION BY and ORDER BY clauses in the window function, and at the same time performs a lateral join between the old subquery and the new subquery, eliminating the window function, removing the corresponding grouping and sorting operations, avoiding performance losses caused by excessive unnecessary grouping and sorting operations due to the execution of window functions, greatly improving the execution efficiency of the query statement, and thus improving the performance of the database.

[0081] Figure 2 It is a flowchart of the query optimization method according to an embodiment of the present invention. The following combines Figure 2 , and specifically describes the flow steps of the query optimization method of this embodiment.

[0082] Step S202, obtain the query statement to be processed.

[0083] Step S204, determine whether the query statement to be processed contains a window function. If so, execute step S206. If not, execute step S214.

[0084] Step S206, record the PARTITION BY clause and the ORDER BY clause in the window function.

[0085] Step S208, construct a new subquery, convert the PARTITION BY clause into a join condition of the new subquery, and add the ORDER BY clause to the new subquery.

[0086] Step S210, convert the filtering operation on the result set contained in the window function into a WHERE clause, and add the WHERE clause to the new subquery.

[0087] Step S212, perform a cross join between the new subquery and the old subquery, and output the columns of the old subquery externally. Thus, this process ends.

[0088] Step S214, skip the execution of the optimization operation on the query statement. Thus, this process ends.

[0089] The query optimization method according to the embodiment of the present invention realizes the optimization of the query statement by obtaining the query statement to be processed, determining whether the query statement to be processed contains a window function, and converting the window function into a cross join when the query statement contains a window function, and generating an optimized query statement based on the cross join. Thus, the query optimization method of the present invention eliminates the window function in the query statement by converting the window function in the query statement into a cross join, removes the corresponding grouping and sorting operations, avoids the performance loss caused by excessive unnecessary grouping and sorting operations due to the execution of the window function, improves the execution efficiency of the query statement, and thus improves the performance of the database.

[0090] Furthermore, the query optimization method according to the embodiment of the present invention accurately obtains the calculation range and order based on the grouping clause and / or sorting clause in the window function by recording the grouping clause and / or sorting clause in the window function, generates a new subquery based on the grouping clause and / or sorting clause, and performs a cross join between the new subquery and the old subquery to generate an optimized query statement, realizing the accurate rewriting of the query statement, ensuring that the new subquery can correctly simulate the behavior of the window function, improving the query accuracy of the optimized query statement, and thus improving the query accuracy.

[0091] Furthermore, in the query optimization method according to the embodiments of the present invention, when a window function contains a filtering operation on the result set, the filtering operation is converted into a restrictive clause, and the restrictive clause is added to a new subquery, so as to achieve early termination of sorting when executing the new subquery, reduce the data volume of the result set, and effectively reduce the overall computation amount for scenarios where the data volume is large but only a very small part of the result set is actually required, thereby significantly reducing the query execution time and resource consumption, and further improving the query efficiency.

[0092] This embodiment also provides a machine-readable storage medium and a computer device. Figure 3 FIG. is a schematic structural diagram of a machine-readable storage medium according to an embodiment of the present invention. Figure 4 FIG. is a schematic structural diagram of a computer device according to an embodiment of the present invention.

[0093] As Figure 3 and Figure 4 shown, the machine-readable storage medium 10 stores a machine-executable program 11 thereon, and when the machine-executable program 11 is executed by a processor, it implements the method for calculating the selected number of rows of a table in the structured query language of a database in any of the above embodiments.

[0094] The computer device 20 may include a memory 220, a processor 210, and a machine-executable program 11 stored on the memory 220 and running on the processor 210, and when the processor 210 executes the machine-executable program 11, it implements the method for calculating the selected number of rows of a table in the structured query language of a database in any of the above embodiments.

[0095] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing a logical function, and can be specifically implemented in any machine-readable storage medium for use by an instruction execution system, apparatus, or device (such as a computer-based system, a system including a processor, or other systems that can fetch and execute instructions from the instruction execution system, apparatus, or device), or used in combination with these instruction execution systems, apparatus, or devices.

[0096] For the description of this embodiment, the machine-readable storage medium 10 can be any device that can contain, store, communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable media include the following: an electrical connection (electronic device) having one or more wirings, a portable computer diskette (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). Additionally, the computer-readable medium 10 can even be paper or other suitable media on which the program can be printed, as the program can be obtained electronically, for example, by optically scanning the paper or other media, followed by editing, interpretation, or other suitable processing as necessary, and then stored in a computer memory.

[0097] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above-described embodiments, multiple steps or methods can be implemented using software or firmware stored in a memory and executed by a suitable instruction execution system.

[0098] The computer device 20 can be, for example, a server, a desktop computer, a laptop computer, a tablet computer, or a smartphone. In some examples, the computer device 20 can be a cloud computing node. The computer device 20 can be described in the general context of computer system-executable instructions, such as program modules, executed by a computer system. Generally, program modules can include routines, programs, object programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 20 can be implemented in a distributed cloud computing environment where tasks are executed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can be located on local or remote computing system storage media including storage devices.

[0099] The computer device 20 can include a processor 210 adapted to execute stored instructions and a memory 220 that provides temporary storage space for the operation of the instructions during operation. The processor 210 can be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 220 can include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0100] The processor 210 may be connected via a system interconnect (such as PCI, PCI-Express, etc.) to an I / O interface (input / output interface) adapted to connect the computer device 20 to one or more I / O devices (input / output devices). The I / O devices may include, for example, a keyboard and a pointing device, where the pointing device may include a touchpad or a touch screen, etc. The I / O devices may be built-in components of the computer device 20, or may be devices externally connected to the computing device.

[0101] The processor 210 may also be linked via a system interconnect to a display interface adapted to connect the computer device 20 to a display device. The display device may include a display screen as a built-in component of the computer device 20. The display device may also include a computer monitor, a television set, a projector, etc. externally connected to the computer device 20. In addition, a network interface controller (NIC) may be adapted to connect the computer device 20 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as Internet Small Computer System Interface, etc.) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices may be connected to the computing device via the network.

[0102] The flowcharts provided in this embodiment are not intended to indicate that the operations of the method will be performed in any specific order, or that all operations of the method are included in all cases. In addition, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional changes may be made to the above method.

[0103] At this point, those skilled in the art should recognize that although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived from the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and determined to cover all these other variations or modifications.

Claims

1. A query optimization method, comprising: Obtaining a query statement to be processed; Determining whether the query statement to be processed contains a window function; If so, converting the window function into a cross join and generating an optimized query statement based on the cross join.

2. The query optimization method according to claim 1, wherein Converting the window function into a cross join and generating an optimized query statement based on the cross join includes: Recording the grouping clause and / or sorting clause in the window function; Generating a new subquery based on the grouping clause and / or the sorting clause, wherein the new subquery includes all table structures in the old subquery corresponding to the window function in the query statement to be processed; Performing a cross join on the new subquery and the old subquery to generate the optimized query statement.

3. The query optimization method according to claim 2, wherein Generating a new subquery based on the grouping clause and / or the sorting clause includes: Constructing a new subquery; When the window function contains the grouping clause, converting the grouping clause into a join condition of the new subquery; and / or When the window function contains a sorting clause, adding the sorting clause to the new subquery.

4. The query optimization method according to claim 3, wherein After the step of constructing the new subquery, the query optimization method further includes: When the window function contains a filtering operation on the result set, converting the filtering operation into a limit clause and adding the limit clause to the new subquery.

5. The query optimization method according to claim 2, wherein The join type of the cross join is an inner join.

6. The query optimization method according to claim 5, wherein The optimized query statement outputs columns from the old subquery.

7. The query optimization method according to claim 1, wherein Determining whether the query statement to be processed contains a window function includes: Obtaining a query selection clause in the query statement to be processed; Determining whether the query selection clause to be processed contains a window function; If so, determining that the query statement to be processed contains a window function; If not, determining that the query statement to be processed does not contain a window function.

8. The query optimization method according to claim 1, wherein After the step of determining whether the query statement contains a window function, the query optimization method further includes: When the query statement does not contain a window function, skipping the optimization operation on the query statement.

9. A machine-readable storage medium having a machine-executable program stored thereon, and when the machine-executable program is executed by a processor, the query optimization method according to any one of claims 1 to 8 is implemented.

10. A computer device, comprising a memory, a processor, and a machine-executable program stored on the memory and running on the processor, and when the processor executes the machine-executable program, the query optimization method according to any one of claims 1 to 8 is implemented.

Images