Query optimization method and device for database query statements, medium and product

Abstract

The invention provides a query optimization method and device for database query statements, a medium and a product. The query optimization method for the database query statement comprises the following steps: obtaining the query statement, wherein the query statement comprises two target data tables for performing internal connection operation; whether a connection condition of the query statement meets a preset requirement or not is judged, the preset requirement is that the connection condition comprises a plurality of sub-conditions, all the sub-conditions of the connection condition are connected through or logical characters, and each sub-condition comprises a query condition utilizing two target data tables connected with the logical characters; if yes, respectively constructing filtering conditions of the two target data tables according to the connection conditions; and pushing the filtering condition to be executed before the internal connection operation of the query statement. The situation that a large amount of data which does not meet the final connection condition is read into a memory and participates in expensive connection calculation is avoided, the query performance of the query statement meeting the preset requirement is improved, and the overall resource utilization rate of the system is increased.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a query optimization method, device, medium, and product for database query statements. Background Technology

[0002] In modern database systems, the query optimizer is a core component for improving data retrieval performance. Its role is to analyze user-submitted SQL (Structured Query Language) statements and generate efficient execution plans. Among these strategies, "predicate pushdown" is a widely used and highly effective optimization technique. Its core idea is to perform data filtering operations as early as possible, pushing selection conditions down to the data scanning layer. This reduces the amount of data involved in subsequent expensive operations such as JOINs and aggregations, thereby improving overall query performance.

[0003] Therefore, query optimization plays a crucial role in database systems and is a key factor affecting database performance. Although existing query optimization methods are highly diverse, due to the complexity of query statement structures, some specially structured queries still fail to be effectively identified and optimized, resulting in high execution costs. Summary of the Invention

[0004] One object of the present invention is to provide a query optimization method, device, medium and product for database query statements with special inner join structures.

[0005] Specifically, the present invention provides a query optimization method for database query statements, including: Obtain the query statement, which contains two target data tables to be joined. Determine whether the join condition of the query statement meets the preset requirements. The preset requirements are that the join condition contains multiple sub-conditions, and all sub-conditions of the join condition are connected by the OR logical operator, and each sub-condition includes the query conditions of the two target data tables connected by the AND logical operator. If so, construct the filtering conditions for the two target data tables respectively according to the connection conditions; The filtering conditions are pushed forward to be executed before the inner join operation of the query statement.

[0006] Optionally, the step of constructing the filtering conditions for the two target data tables based on the join conditions includes: For each target data table, extract the query conditions for the target data table from all sub-conditions of the join condition; The extracted query conditions for the target data table are connected using the OR logical operator.

[0007] Optionally, the step of connecting the extracted query conditions for the target data table using an OR logical operator includes: Check if there are any query conditions with overlapping ranges among the query conditions for the target data table. If so, take the union of the query conditions with overlapping ranges.

[0008] Optionally, the step of pushing the filtering conditions forward to be performed before the inner join operation of the query statement includes: Filter data that meets the filtering conditions from the two target data tables respectively; The data that meets the filtering conditions are then joined according to the connection conditions.

[0009] Optionally, the step of filtering data that meets the filtering conditions from the two target data tables includes: The data filtered for the same column in each target data table are sorted and recorded according to the column value order.

[0010] Optionally, the step of filtering data that meets the filtering conditions from the two target data tables includes: Check if each of the target data tables has an index on a column based on the filtering conditions; if so, use the index to filter the target data table.

[0011] Optionally, the preset requirement also includes that the query conditions of the two target data tables in each of the sub-conditions do not reference other tables for related queries.

[0012] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer executable program stored in the memory and running on the processor, wherein the processor, when executing the computer executable program, implements a query optimization method for a database query statement according to any of the preceding claims.

[0013] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer-executable program is stored, which, when executed by a processor, implements a query optimization method for a database query statement according to any one of the preceding claims.

[0014] According to another aspect of the present invention, a computer program product is also provided, comprising a computer executable program that, when executed by a processor, implements a query optimization method for a database query statement according to any of the preceding claims.

[0015] The query optimization method for database queries of this invention, after obtaining a query statement containing an inner join operation on two target data tables, determines whether the join condition of the query statement meets preset requirements. The preset requirements are that the join condition contains multiple sub-conditions, all sub-conditions of the join condition are connected using an OR logical operator, and each sub-condition includes query conditions for the two target data tables connected using an AND logical operator. If the preset requirements are met, filter conditions for the two target data tables are constructed separately based on the join condition, and the filter conditions are pushed forward for execution before the inner join operation of the query statement. By identifying and extracting query conditions for the same target data table in different OR clauses of the join condition, and reconstructing them into independent filter conditions, the reconstructed filter conditions can be pushed forward for execution before the inner join operation. This avoids reading a large amount of data that does not meet the final join conditions into memory and participating in expensive join calculations, greatly reducing the data size of the join result set, helping to improve the query performance of queries that meet the preset requirements, and improving the overall resource utilization of the system.

[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 schematic flowchart of a database query statement optimization method according to an embodiment of the present invention; Figure 2 This is a schematic flowchart illustrating the process of constructing filtering conditions in a query optimization method for database query statements according to an embodiment of the present invention; Figure 3 This is a schematic flowchart of the process of constructing filtering conditions in a query optimization method for database query statements according to another embodiment of the present invention; Figure 4 This is a schematic diagram of a computer device 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 program product according to an embodiment of the present invention. Detailed Implementation

[0018] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0019] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0020] The flowcharts provided in this invention are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0021] like Figure 1 As shown, in one embodiment, the query optimization method for database query statements generally includes: Step S101: Obtain the query statement. The query statement contains the two target data tables to be joined. Specifically, it obtains the SQL (Structured Query Language) statement that performs an inner join query on the two target data tables in the database.

[0022] Step S102: Determine whether the join condition of the query statement meets the preset requirements. If yes, proceed to step S104; otherwise, proceed to step S103.

[0023] Specifically, the default requirement is that the join condition contains multiple sub-conditions, and all sub-conditions of the join condition are connected using the OR logical operator, and each sub-condition includes query conditions for two target data tables connected using the logical operator.

[0024] For example, consider a specific query statement that meets preset requirements: SELECT * FROM s1 JOIN s2 ON (s1.a<10 AND s2.s2a<20) OR (s1.a>20 ANDs2.s2a>10); This query statement contains an inner join operation on two target data tables, s1 and s2. The join condition includes two subconditions: (s1.a<10 AND s2.s2a<20) and (s1.a>20 AND s2.s2a>10), connected by the OR operator. Each subcondition includes the query conditions for the two target data tables s1 and s2, connected by the AND operator.

[0025] In addition, the default requirement also includes that the query conditions for the two target data tables in each sub-condition do not reference other tables for join queries. Specifically, this means that the query conditions for each target data table can be queried independently, without depending on the query results of other tables. In other words, the query conditions for each target data table are direct range limits on the values ​​of its own columns.

[0026] Step S103: Revert to the normal query execution operation. Specifically, if the query statement does not meet the preset requirements, generate and execute the query plan according to the conventional optimization logic in the prior art, without adopting the specific optimization process of this embodiment.

[0027] Step S104: Construct filtering conditions for the two target data tables based on the join conditions. Specifically, if the query statement meets the preset requirements, construct filtering conditions for the two target data tables based on the join conditions.

[0028] Reference Figure 2 As shown, in one embodiment, this step includes: For each target data table, execute: Step S201: Extract the query conditions for the target data table from all the sub-conditions of the join condition.

[0029] Referring to the example of the specific query statement above, taking the target data table s1 as an example, we extract the query conditions for the target data table s1 from all the sub-conditions of the join condition, s1.a<10 and s1.a>20.

[0030] Step S202: Connect the extracted query conditions for the target data table using the OR logical operator.

[0031] Specifically, referring to the previous example, for the query conditions s1.a<10 and s1.a>20 of the target data table s1, we use the OR operator to connect them, resulting in "s1.a<10 OR s1.a>20". This is then used as the filtering condition for the target data table s1.

[0032] Similarly, the filtering condition for the target data table s2 can also be obtained as "s2.s2a<20 OR s2.s2a>10".

[0033] Step S105: Push the filter conditions forward to be executed before the inner join operation of the query statement.

[0034] Specifically, the filtering conditions of each target data table are first pushed forward to the corresponding base table for execution, filtering out data that meets the filtering conditions from both target data tables. Then, the filtered data that meets the filtering conditions is joined according to the join conditions.

[0035] In addition, the steps for filtering data that meets the filtering criteria from the two target data tables include: checking whether each target data table has an index on the column based on the filtering criteria; if so, using the index to filter the target data table. Specifically, when filtering data in the target data tables, indexes are used preferentially to improve filtering efficiency.

[0036] The step of filtering data that meets the filtering criteria from the two target data tables may further include: sorting and recording the data filtered for the same column in each target data table according to the column value order. For example, the data filtered for column a of s1 according to the filtering criteria are sorted according to the column value order of column a, so as to facilitate matching with the join criteria when performing join operations later.

[0037] In this embodiment, after obtaining a query statement containing an inner join operation on two target data tables, it is determined whether the join condition of the query statement meets preset requirements. The preset requirements are that the join condition contains multiple sub-conditions, all sub-conditions are connected using an OR logical operator, and each sub-condition includes query conditions for the two target data tables connected using an OR logical operator. If the preset requirements are met, filter conditions for the two target data tables are constructed based on the join condition, and these filter conditions are pushed forward to execution before the inner join operation of the query statement. By identifying and extracting query conditions for the same target data table from different OR clauses in the join condition, and reconstructing them into independent filter conditions, the reconstructed filter conditions can be pushed forward to execution before the inner join operation. This avoids reading a large amount of data that does not meet the final join condition into memory and participating in expensive join calculations, greatly reducing the data size of the join result set, improving the query performance of queries that meet the preset requirements, and enhancing the overall resource utilization of the system.

[0038] like Figure 3 As shown, in one embodiment, after the step of connecting the extracted query conditions for the target data table using an OR logical operator, the method includes: checking whether there are query conditions with overlapping ranges among the query conditions for the target data table; if so, taking the union of the query conditions with overlapping ranges.

[0039] In this embodiment, the step of constructing filtering conditions for the two target data tables based on the join conditions includes: For each target data table, execute step S301 to extract the query conditions for the target data table from all sub-conditions of the join condition.

[0040] Step S302: Connect the extracted query conditions for the target data table using the OR logical operator.

[0041] Step S303: Check if there are any query conditions with overlapping ranges in the query conditions for the target data table. If yes, proceed to step S304. If no, end the process, i.e., do not perform any processing.

[0042] Step S304: Take the union of query conditions with overlapping ranges. That is, merge query conditions with overlapping ranges into a single set to improve the efficiency of subsequent filtering.

[0043] For example, for the target data table s1, if there are query conditions s1.a<10 and s1.a<20, then s1.a<10 overlaps with s1.a<20, so we take the union and merge them into a single condition s1.a<20.

[0044] This embodiment also provides a computer device and a computer-readable storage medium. Figure 4 This is a schematic diagram of a computer device 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.

[0045] The computer device 10 may include a memory 110, a processor 120, and a computer-executable program 11 stored on the memory 110 and running on the processor 120. When the processor 120 executes the computer-executable program 11, it implements the query optimization method for the database query statement of any of the above embodiments.

[0046] The computer-readable storage medium 20 stores a computer-executable program 11 thereon, which, when executed by a processor, implements the query optimization method for the database query statement of any of the above embodiments.

[0047] This embodiment also provides a computer program product. Figure 6 This is a schematic diagram of a computer program product 30 according to an embodiment of the present invention. The computer program product 30 includes a computer executable program 11, which, when executed by a processor 120, implements the query optimization method for any of the database query statements described above.

[0048] Specifically, the computer executable program 11 used to perform the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, computer instructions, computer-related instructions, microcode, firmware instructions, status setting data, or source code or object code written in any combination of one or more programming languages.

[0049] For the purposes of this embodiment, the computer-readable storage medium 20 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable media include: an electrical connection having one or more wires (electronic device), a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the computer-readable storage medium 20 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.

[0050] 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 embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0051] Computer device 10 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 10 can be a cloud acquisition node. Computer device 10 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 10 can be implemented in a distributed cloud acquisition environment where tasks are performed by remote processing devices linked via a communication network. In a distributed cloud acquisition environment, program modules can reside on local or remote acquisition system storage media, including storage devices.

[0052] Computer device 10 may include a processor 120 adapted to execute stored instructions and a memory 110 that provides temporary storage space for the operation of said instructions during operation. Processor 120 may be a single-core processor, a multi-core processor, an acquisition cluster, or any other configuration. Memory 110 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0053] The processor 120 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 10 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 10 or may be external devices connected to the acquisition device.

[0054] The processor 120 may also be linked via a system interconnect to a display interface suitable for connecting the computer device 10 to a display device. The display device may include a display screen that is a built-in component of the computer device 10. The display device may also include an external computer monitor, television, or projector connected to the computer device 10. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 10 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) 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 connect to the computer device via the network.

[0055] 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 query optimization method for database query statements, comprising: Obtain the query statement, which contains two target data tables to be joined. Determine whether the join condition of the query statement meets the preset requirements. The preset requirements are that the join condition contains multiple sub-conditions, and all sub-conditions of the join condition are connected by the OR logical operator, and each sub-condition includes the query conditions of the two target data tables connected by the AND logical operator. If so, construct the filtering conditions for the two target data tables respectively according to the connection conditions; The filtering conditions are pushed forward to be executed before the inner join operation of the query statement.

2. The query optimization method for database query statements according to claim 1, wherein... The step of constructing the filtering conditions for the two target data tables based on the join conditions includes: For each target data table, extract the query conditions for the target data table from all sub-conditions of the join condition; The extracted query conditions for the target data table are connected using the OR logical operator.

3. The query optimization method for database query statements according to claim 2, wherein... The step of connecting the extracted query conditions for the target data table using an OR logical operator includes: Check if there are any query conditions with overlapping ranges among the query conditions for the target data table. If so, take the union of the query conditions with overlapping ranges.

4. The query optimization method for database query statements according to claim 1, wherein... The step of pushing the filter condition forward to be performed before the inner join operation of the query statement includes: Filter data that meets the filtering conditions from the two target data tables respectively; The data that meets the filtering conditions are then joined according to the connection conditions.

5. The query optimization method for database query statements according to claim 4, wherein... The step of filtering data that meets the filtering conditions from the two target data tables includes: The data filtered for the same column in each target data table are sorted and recorded according to the column value order.

6. The query optimization method for database query statements according to claim 4, wherein... The step of filtering data that meets the filtering conditions from the two target data tables includes: Check if each of the target data tables has an index on a column based on the filtering conditions; if so, use the index to filter the target data table.

7. The query optimization method for database query statements according to claim 1, wherein... The preset requirement also includes that the query conditions of the two target data tables in each of the sub-conditions do not reference other tables for related queries.

8. A computer device comprising a memory, a processor, and a computer-executable program stored in the memory and running on the processor, wherein the processor, when executing the computer-executable program, implements a query optimization method for a database query statement according to any one of claims 1 to 7.

9. A computer-readable storage medium having a computer-executable program stored thereon, wherein the computer-executable program, when executed by a processor, implements a query optimization method for a database query statement according to any one of claims 1 to 7.

10. A computer program product comprising a computer executable program, wherein the computer executable program, when executed by a processor, implements a query optimization method for a database query statement according to any one of claims 1 to 7.

Images