Method, medium and device for optimizing nested loop join with materialized database

Abstract

The application provides a database with a materialized nested loop join optimization method, a storage medium and equipment. The method comprises the following steps: obtaining a nested loop join; judging whether the nested loop join meets an optimization condition; if yes, calling an optimizer to create a first path containing a materialized index node and a second path containing a materialized node, judging whether the cost of executing the first path is less than the cost of executing the second path, and in the case that the cost of executing the first path is less than the cost of executing the second path, calling the optimizer to generate a first execution plan according to the first path; and calling an executor to execute the first execution plan. Through the method, the nested loop join meeting the optimization condition can be screened out, and a materialized index node is created. The node expands the capacity of the original materialized node, so that the node has the capacity of fast indexing, thereby pushing down the join condition to the materialized index node, accelerating the scanning speed of the materialized view, and improving the performance of the database.

Description

TECHNICAL FIELD

[0001] The present application relates to database technology, and in particular, to a method, medium and device for optimizing a nested loop join with materialized database. BACKGROUND

[0002] A nested loop join is a basic join method. The nested loop join is performed by two loops, with a first table T1 as a driving node and a second table T2 as a driven node. Each record of T1 is compared with each record of T2, and the records meeting the condition are outputted.

[0003] For a nested loop join with a materialized node, the join condition in the nested loop join cannot be pushed down to the materialized node, which results in that each record in T1 needs to be compared with each record of the materialized T2, and the efficiency is low. SUMMARY

[0004] An object of the present application is to improve the efficiency of the nested loop join.

[0005] A further object of the present application is to reduce the resource consumption of the nested loop join.

[0006] A further object of the present application is to improve the performance of the database.

[0007] In particular, the present application provides a method for optimizing a nested loop join with a materialized database, which comprises:

[0008] obtaining the nested loop join;

[0009] determining whether the nested loop join meets an optimization condition;

[0010] if yes, calling an optimizer to create a first path comprising a materialized index node and a second path comprising a materialized node, the materialized index node being capable of generating an index for the materialized data on the basis of the materialized node;

[0011] determining whether a cost of executing the first path is less than a cost of executing the second path,

[0012] in the case that the cost of executing the first path is less than the cost of executing the second path, calling the optimizer to generate a first execution plan according to the first path;

[0013] calling an executor to execute the first execution plan.

[0014] Optionally, the step of determining whether the nested loop join meets the optimization condition comprises:

[0015] determining whether a join condition of the nested loop join meets a lookup condition of a preset type index;

[0016] If yes, it is determined that the optimization condition is met.

[0017] The preset type index includes a Hash index and a Btree index.

[0018] Optionally, after the step of determining whether the nested loop join meets the optimization condition, the method further includes:

[0019] In the case that the nested loop join does not meet the optimization condition, the optimizer is invoked to create a second path containing a materialized node, and a second execution plan is generated according to the second path containing the materialized node;

[0020] The executor is invoked to execute the second execution plan.

[0021] Optionally, the step of invoking the executor to execute the first execution plan includes:

[0022] The driving data of the driving node in the nested loop join is scanned;

[0023] It is determined whether it is the first time to scan the driven node in the nested loop join in the materialized index mode;

[0024] If yes, the driven data of the driven node is scanned and obtained;

[0025] The driven data is materialized in the memory;

[0026] The memory index is constructed according to the materialized driven data;

[0027] The driven data satisfying the connection condition in the nested loop join is matched one by one through the memory index according to the driving data;

[0028] The matching result is returned to the client.

[0029] Optionally, after the step of determining whether it is the first time to scan the driven node in the nested loop join in the materialized index mode, the method further includes:

[0030] If no, the memory index of the driven node in the memory is queried;

[0031] The driven data satisfying the connection condition in the nested loop join is matched one by one through the memory index according to the driving data;

[0032] The matching result is returned to the client.

[0033] Optionally, after the step of determining whether the cost of executing the first path is less than the cost of executing the second path, the method further includes:

[0034] In the case that the cost of executing the first path is greater than the cost of executing the second path, the optimizer is invoked to generate a second execution plan according to the second path;

[0035] The executor is invoked to execute the second execution plan.

[0036] Optionally, the step of judging whether the cost of executing the first path is less than the cost of executing the second path comprises: estimating the cost of executing the first path and the cost of executing the second path.

[0037] The step of estimating the cost of executing the first path comprises: estimating the cost consumed by executing the materialized index node.

[0038] Optionally, the step of estimating the cost consumed by executing the materialized index node comprises:

[0039] The cost consumed by constructing the index and the cost consumed by searching the index are estimated.

[0040] According to another aspect of the present application, there is also provided a machine readable storage medium having stored thereon a machine executable program which, when executed by a processor, implements any of the above-mentioned database optimization methods for nested loop join with materialization.

[0041] According to yet another aspect of the present application, there is also provided a computer device comprising a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implements any of the above-mentioned database optimization methods for nested loop join with materialization when executing the machine executable program.

[0042] The database optimization method for nested loop join with materialization can push down the join condition to the target node, so that the target node has the ability of fast indexing, thereby accelerating the execution speed of such nested loop join, reducing resource consumption, and improving the performance of the database.

[0043] Further, the optimization method of the database with materialized nested loop join in the application judges whether the join condition in the nested loop join meets the search condition of the preset type index after the nested loop join is obtained, wherein the preset type index includes Hash index and Btree index, and when the join condition meets the search condition of any preset type index, the optimizer is called to create the first path containing the materialized index node and the second path containing the materialized node; the cost of executing the first path is estimated and judged whether it is less than the cost of executing the second path, if yes, the optimizer is called to generate the first execution plan according to the first path, and then the executor is called to execute the first execution plan. Through the method, the nested loop join meeting the condition can be screened, and the execution path with the minimum cost is found to generate the optimal execution plan, so as to improve the efficiency of the database in executing the nested loop join and improve the performance of the database.

[0044] The above and other objects, advantages and features of the application will become more apparent from the following detailed description of specific embodiments thereof, taken in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS

[0045] Some specific embodiments of the application will now be described in detail by way of example with reference to the drawings. The same reference numbers in different drawings identify the same or similar components or parts. It should be understood that these drawings are not necessarily to scale. In the drawings:

[0046] Figure 1 is a flowchart of the optimization method of the database with materialized nested loop join according to an embodiment of the application;

[0047] Figure 2 is an optimizer execution flowchart of the optimization method of the database with materialized nested loop join according to an embodiment of the application;

[0048] Figure 3 is a schematic diagram of the machine readable storage medium in the optimization method of the database with materialized nested loop join according to an embodiment of the application; and

[0049] Figure 4 is a schematic diagram of the computer device in the optimization method of the database with materialized nested loop join according to an embodiment of the application. DETAILED DESCRIPTION

[0050] Those skilled in the art should understand that the embodiments described below are only a part of the embodiments of the present application, and are intended to explain the technical principles of the present application, but not to limit the protection scope of the present application. Based on the embodiments provided by the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort shall fall within the protection scope of the present application.

[0051] Figure 1 is a flowchart of an optimization method of a database nested loop join materialized according to an embodiment of the present application. The flowchart includes:

[0052] In step S101, a nested loop join is acquired.

[0053] In step S102, it is judged whether the nested loop join meets an optimization condition. This step includes: judging whether the join condition of the nested loop join meets a preset type index search condition, wherein the preset type index includes: a Hash index and a Btree index; and in the case that the join condition of the nested loop join meets the preset type index search condition, it is determined that the optimization condition is met.

[0054] For example, in the case that the join condition of the nested loop join only contains an equal operator, it can be determined that the join condition of the nested loop join can use a Hash index; in the case that the join condition of the nested loop join contains =, >, >=, <, <=, etc., it can be determined that the join condition of the nested loop join can use a Btree index; and those skilled in the art can select the available index type according to the actual situation.

[0055] In addition, in the case that it is determined that the nested loop join can use multiple index modes, an optional mode is to establish a priority mechanism in the preset index type, for example, when a Hash index and a Btree index can be used, the Hash index is used preferentially, and those skilled in the art can determine the priority of different indexes according to the actual situation.

[0056] When the determination result is no, it indicates that this nested loop join cannot use such index, so a normal materialized node is created, then an optimizer is called to generate an execution plan, and an executor is called to execute the execution plan.

[0057] Step S103, in the case of YES in step S102, the optimizer creates a first path containing a materialized index node and a second path containing a materialized node. In this step, the materialized node can convert data in the data table into the format required in the memory, and the materialized index node can generate a corresponding index according to the materialized data on the basis of the materialized node, so that the join condition of the nested loop join can be pushed down to the node to perform a query operation according to the index.

[0058] Step S104, it is judged whether the cost of executing the first path is less than the cost of the second path. Before this step, the cost of executing the first path and the cost of executing the second path are estimated. The step of estimating the cost of executing the first path includes estimating the cost consumed by the materialized index node. The step of estimating the cost consumed by the materialized index node includes estimating the cost consumed by constructing the index and the cost consumed by searching the index.

[0059] An optional algorithm for estimating the cost of constructing the index is, for example, the number of tuples returned by the lower layer * the cost of inserting 1 index.

[0060] The number of tuples returned by the lower layer and the cost of inserting 1 index can be adjusted by adding a coefficient according to the actual situation.

[0061] An optional algorithm for estimating the cost of searching the index is, for example,

[0062] It should be noted that, because the preset type index in this example includes Hash index and Btree index, an optional algorithm is provided for both index modes.

[0063] The cost of searching the Hash index = the number of tuples returned by the lower layer * the selection rate * the comparison operation cost;

[0064] The cost of searching the Btree index = the number of tuples returned by the lower layer * the selection rate * the comparison operation cost * the number of tuples returned by the lower layer * log(n) * the comparison operation cost.

[0065] The selection rate is the percentage of the estimated hit data in the total data amount;

[0066] The comparison operation cost is the resource cost consumed by comparing the join condition with the index;

[0067] And each parameter in the above formula can be adjusted by adding a coefficient according to the actual situation.

[0068] The person skilled in the art can select the available index type and the algorithm for estimating the cost according to the actual situation.

[0069] Step S105, in the case of yes in step S104, calling the optimizer to generate a first execution plan according to the first path.

[0070] Step S106, calling the executor to execute the first execution plan. This step includes: scanning the driving data of the driving node in the nested loop join connection; judging whether it is the first time to scan the driven node in the nested loop join connection in the form of materialized index; if yes, scanning to obtain the driven data of the driven node; materializing the driven data into the memory; constructing the memory index according to the materialized driven data; matching the driven data satisfying the connection condition in the nested loop join connection one by one according to the driving data through the memory index; and returning the matching result to the client.

[0071] In the case that the executor is not the first time to scan the driven node in the nested loop join connection in the form of materialized index, that is, the memory index corresponding to the driven data already exists in the database memory, the memory index of the driven node in the memory is queried; the driven data satisfying the connection condition in the nested loop join connection is matched one by one according to the driving data through the memory index; and the matching result is returned to the client.

[0072] Through this method, the nested loop connection meeting the condition can be screened out, and the execution path with the minimum cost is found to generate the optimal execution plan and execute, thereby improving the efficiency of the database in executing the nested loop connection and improving the performance of the database.

[0073] Figure 2 is a flowchart of the optimization method of the database with nested loop connection with materialization according to another embodiment of the application. The schematic diagram includes:

[0074] Step S201, judging whether the nested loop connection meets the optimization condition. This step includes: judging whether the connection condition of the nested loop connection meets the lookup condition of the preset type index, wherein the preset type index includes: Hash (Hash) index and Btree (Balanced Tree, multi-way balanced lookup tree) index; in the case that the connection condition of the nested loop connection meets the lookup condition of the preset type index, it is determined that the optimization condition is met.

[0075] Step S202, in the case of yes in step S201, creating a first path containing a materialized index node and a second path containing a materialized node. In this step, the materialized node can convert the data in the data table to the memory according to the required format, and the materialized index node can generate the corresponding index according to the materialized data on the basis of the materialized node, so that the connection condition of the nested loop connection can be pushed down to this node for query operation according to the index.

[0076] Step S203, calculating the cost of executing each path.

[0077] Step S204, it is judged whether the cost of the first path is less than the cost of the second path.

[0078] Step S205, in the case that the determination of step S204 is yes, the first path containing the materialized index node is selected.

[0079] Step S206, in the case that the determination of step S201 is no, the second path containing the materialized node is created.

[0080] Step S207, in the case that the determination of step S204 is no or step S206 is executed, the second path containing the materialized node is selected. In this step, when the determination of step S204 is no, it means that the cost of the first path is greater than the cost of the second path, so the second path with smaller cost is selected.

[0081] Step S208, in the case that step S205 is executed or step S207 is executed, other optimization steps are executed.

[0082] Step S209, an execution plan is generated.

[0083] Through the method, the optimizer can screen the nested loop connection meeting the condition, and find the execution path with the smallest cost to generate the optimal execution plan, thereby improving the efficiency of the database in executing the nested loop connection and improving the performance of the database.

[0084] In order to more clearly illustrate the method, the following examples are provided:

[0085] Nested Loop

[0086] Join Filter: (t1.id = t2.id)

[0087] -> Seq Scan on t1

[0088] -> Materialize

[0089] -> Seq Scan on t2

[0090] Wherein, the t1 table is the driving node, and the t2 is the driven node.

[0091] This paragraph is the execution plan of the nested loop connection before applying the method, which means that each record in t1 needs to be compared with each record of the materialized t2 to filter out the data meeting the condition, but this way is relatively inefficient.

[0092] After applying the method, the execution plan of this nested loop connection becomes:

[0093] Nested Loop

[0094] -> Seq Scan on t1

[0095] -> MaterializeIndex

[0096] MFilter((t1.id=t2.id))

[0097] -> Seq Scan on t2

[0098] The final execution flow is as follows by pushing the join condition into the target node:

[0099] Step 1: execute the SeqScan (full table scan) node to sequentially scan each piece of data of the t2 table.

[0100] Step 2: execute the MaterializeIndex node to organize each piece of data of the SeqScan in the memory in an index manner according to the join condition.

[0101] Step 3: execute the SeqScan node to scan each record of the t1 table.

[0102] Step 4: for each record of the t1 table, execute the MaterializeIndex node to find whether there is a record satisfying t1.id=t2.id through the index structure, and if so, return the record to the user as a result; if not, continue to return to Step 3.

[0103] Through the method, the driven node t2 is stored in the memory in an index manner according to the join condition, which can accelerate the query efficiency on one hand, and the index existing in the memory can also improve the execution speed, thereby finally improving the efficiency of the database in executing the nested loop connection.

[0104] The embodiment also provides a machine readable storage medium and a computer device. Figure 3 is a schematic diagram of a machine readable storage medium 301 according to an embodiment of the present application, Figure 4 is a schematic diagram of a computer device 403 according to an embodiment of the present application.

[0105] The machine readable storage medium 301 has a machine executable program 302 stored thereon, and the machine executable program 302 is executed by a processor to implement the optimization method of the database nested loop connection with materialization according to any one of the above embodiments.

[0106] The computer device 403 can include the memory 401, the processor 402, and the machine executable program 302 stored on the memory 401 and running on the processor 402, and the processor 402 implements the optimization method of the database materialized nested loop join of the present application when executing the machine executable program 302.

[0107] It should be noted that the logic and / or the steps represented in the flowcharts, or otherwise described herein, such as generating an execution plan, can be embodied in any machine-readable storage medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor- containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions, or a combination thereof.

[0108] For the description of the present embodiment, the machine readable storage medium 301 can be any device that can contain, store, communicate, propagate or transport the program for use by or in connection with the instruction execution system, apparatus, or device, or in conjunction with the instruction execution system, apparatus, or device. More specific examples (non-exhaustive list) of the machine readable storage medium 301 include the following: an electrical connection having one or more wires (electrical device), 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). In addition, the machine readable storage medium 301 can even be paper or another suitable medium upon which the program can be printed, as the program can be electronically captured, for example, by optically scanning the paper or other suitable medium, then electronically converted into a form that can be further processed by the computer.

[0109] It should be understood that parts of the present application can be realized by hardware, software, firmware, or a combination thereof. In the above-described embodiments, a plurality of steps or methods can be realized by software or firmware stored in the memory and executed by a suitable instruction execution system.

[0110] The computer device 403 can be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, the computer device 403 can be a cloud computing node. The computer device 403 can be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules can include routines, programs, objects, components, logic, data structures, and so on that perform particular tasks or implement particular abstract data types. The computer device 403 can be practiced in distributed cloud computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules can be located in local or remote computer system storage media including memory storage devices.

[0111] The computer device 403 can include a processor 402 adapted to execute instructions stored in memory 401, which in operation provides temporary storage for the instructions during execution. The processor 402 can be a single core processor, multi-core processor, computing cluster, or any number of other configurations. The memory 401 can include random access memory (RAM), read only memory (ROM), flash memory, or any other suitable memory systems.

[0112] The processor 402 can be connected through a system interconnect (e.g., a PCI, PCI-Express, or other interconnect) to an I / O interface adapted to connect the computer device 403 to one or more I / O devices (input / output devices). The I / O devices can include, for example, a keyboard and a pointing device, where the pointing device can include a touchpad or touchscreen, among others. The I / O devices can be built-in components of the computer device 403, or can be devices externally connected to the computer device.

[0113] The processor 402 can also be linked through the system interconnect to a display interface adapted to connect the computer device 403 to a display device. The display device can include a display screen that is a built-in component of the computer device 403. The display device can also include a computer monitor, television, or projector, among others, that is externally connected to the computer device 403. Further, a network interface controller (NIC) can be adapted to connect the computer device 403 to a network through the system interconnect. In some embodiments, the NIC can use any suitable interface or protocol, such as an Internet Small Computer Systems Interface, among others, to transfer data. The network can be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, among others. Remote devices can be connected to the computer device through the network.

[0114] The flowcharts provided by the embodiments are not intended to indicate that the operations of the methods will be executed in any particular order, or that all of the operations of the methods are included in every case. In addition, the methods can include additional operations. Additional changes can be made to the above-described methods within the scope of the technical ideas provided by the embodiments.

[0115] Thus, those skilled in the art will recognize that the present application has been well- described and clearly demonstrated by way of example, and that numerous modifications, substitutions and changes can be made thereto without departing from the spirit and scope of the present application as set forth above. It is intended that the scope of the present application be limited solely by the scope of the appended claims.

Claims

1. A method for optimizing a nested loop join with materialization of a database, comprising: obtaining the nested loop join; determining whether the nested loop join meets an optimization condition; if yes, invoking an optimizer to create a first path including a materialized index node and a second path including a materialized node, the materialized index node being capable of indexing data to be materialized based on the materialized node; determining whether a cost of executing the first path is less than a cost of executing the second path; in the case that the cost of executing the first path is less than the cost of executing the second path, invoking the optimizer to generate a first execution plan according to the first path; and invoking an executor to execute the first execution plan. The step of determining whether the nested loop join meets the optimization condition comprises: determining whether a join condition of the nested loop join meets a lookup condition of a preset type index; if yes, determining that the optimization condition is met. The preset type index comprises a Hash index and a Btree index. The method further comprises, after the step of determining whether the nested loop join meets the optimization condition: in the case that the nested loop join does not meet the optimization condition, invoking the optimizer to create the second path including the materialized node and to generate a second execution plan according to the second path including the materialized node; and invoking the executor to execute the second execution plan. The step of invoking the executor to execute the first execution plan comprises: scanning driving data of a driving node in the nested loop join; determining whether it is the first time to scan a driven node in the nested loop join in a materialized index manner; if yes, scanning to obtain driven data of the driven node; materializing the driven data into memory; constructing an in-memory index according to the materialized driven data; matching the driven data meeting a join condition in the nested loop join one by one according to the driving data through the in-memory index; and returning a matching result to a client. The step of determining whether it is the first time to scan the driven node in the nested loop join in the materialized index manner further comprises: if no, querying an in-memory index of the driven node in the memory; matching the driven data meeting the join condition in the nested loop join one by one according to the driving data through the in-memory index; and returning a matching result to the client. The method further comprises, after the step of determining whether the cost of executing the first path is less than the cost of executing the second path: in the case that the cost of executing the first path is greater than the cost of executing the second path, invoking the optimizer to generate a second execution plan according to the second path; and invoking the executor to execute the second execution plan.

2. The method of claim 1, wherein, The step of determining whether the cost of executing the first path is less than the cost of executing the second path comprises: estimating the cost of executing the first path and the cost of executing the second path. The step of estimating the cost of executing the first path comprises: estimating a cost consumed by executing the materialized index node. The step of estimating the cost consumed by executing the materialized index node comprises: estimating a cost consumed by constructing an index and a cost consumed by looking up the index. ​ 3. The method of claim 1, wherein, ​ ​ ​ 4. The method of claim 1, wherein, ​ ​ ​ ​ ​ ​ ​ ​ 5. The method of claim 4, wherein, ​ ​ ​ ​ 6. The method of claim 1, wherein, ​ ​ ​ 7. The method of claim 1, wherein, ​ ​ 8. The method of claim 7, wherein, ​ ​ 9. A machine readable storage medium having stored thereon a machine executable program which, when executed by a processor, implements the method of optimizing a nested loop join of database with materialized according to any of claims 1 to 8.

10. A computer device comprising a memory, a processor, and a machine executable program stored on the memory for execution on the processor, and the processor implements the method of optimizing a nested loop join of database with materialized according to any of claims 1 to 8 when executing the machine executable program.

Images