Method and apparatus for optimizing nested loop query

Abstract

The present specification provides an optimization method and device for a nested loop query, an execution structure of the nested loop query includes at least two levels of nested loop queries, the method can include: a target nested loop operator acquires a first query result output by a query driver branch in a previous level of nested loop query in a case where it is determined that the previous level of nested loop query exists, the first query result carries a level mark corresponding to the previous level of nested loop query; the target nested loop operator is connected with a target query driver branch and a target query driven branch; the target query driver branch counts a quantity of the first query result according to the level mark; in a case where a statistical result indicates that there are multiple first query results and any first query result carries an end identifier, the target query driver branch performs batch query according to the multiple first query results, and sends a second query result set obtained by the batch query to the target query driven branch.

Description

TECHNICAL FIELD

[0001] The present specification relates to the technical field of data query, and in particular, to a method and device for optimizing a nested loop query. BACKGROUND

[0002] A nested loop query (NLJ) is to connect an external data table (also referred to as a driving table) to an internal data table (also referred to as a driven table), form two data tables nested inside and outside, and then read row data from the driving table one row at a time, take all rows that meet the filtering conditions from the driven table by performing a full table scan on the driven table, with the row data read as the filtering conditions of the driven table. The core idea is to obtain the query result by comparing data through double-loop comparison. Since each full table scan on the driven table needs to read data from the hard disk and load it into the memory, the IO overhead is very high.

[0003] In related technologies, a block nested loop query (BNL) is used to optimize the execution overhead of a query, that is, all row data of the driving table is read into a cache block (join_buffer), and then each row in the driven table is taken out to match all row data in the cache block, and data that meets the conditions is returned. In this process, only one full table scan needs to be performed on the driven table, greatly reducing the number of IOs and reducing the IO overhead. However, this method can only optimize the execution overhead in the single-level nested loop query scenario, and cannot optimize the execution overhead in the multi-level nested loop query scenario. SUMMARY

[0004] Therefore, the present specification provides a method and device for optimizing a nested loop query to solve the problems in related technologies.

[0005] Specifically, the present specification is implemented through the following technical solutions:

[0006] According to a first aspect of an embodiment of the present specification, a method for optimizing a nested loop query is provided. The execution structure of the nested loop query includes at least two levels of nested loop queries. Each level of nested loop query includes a nested loop operator and a query driving branch and a query driven branch connected to the nested loop operator. The nested loop operator of each level of nested loop query is the query driven branch of the upper level of nested loop query. The method includes:

[0007] The target nested loop operator obtains a first query result output by a query driving branch in a previous level nested loop query in a case where it is determined that the previous level nested loop query exists, the first query result carrying a level mark corresponding to the previous level nested loop query; and the target nested loop operator is connected with a target query driving branch and a target query driven branch.

[0008] The target query driving branch counts a quantity of the first query results according to the level mark.

[0009] In a case where the counting result indicates that there are a plurality of the first query results and any of the first query results carries an end identifier, the target query driving branch performs batch query according to the plurality of the first query results, and sends a second query result set obtained by the batch query to the target query driven branch.

[0010] According to a second aspect of an embodiment of the present specification, an optimization device of a nested loop query is provided, an execution structure of the nested loop query comprising at least two levels of nested loop queries, each level of the nested loop queries comprising a nested loop operator and a query driving branch and a query driven branch connected with the nested loop operator, and the nested loop operator of each level of the nested loop queries being the query driven branch of a previous level of the nested loop queries, the device comprising:

[0011] An obtaining module, configured to obtain, by a target nested loop operator, a first query result output by a query driving branch in a previous level nested loop query in a case where it is determined that the previous level nested loop query exists, the first query result carrying a level mark corresponding to the previous level nested loop query; and the target nested loop operator being connected with a target query driving branch and a target query driven branch.

[0012] A counting module, configured to count, by the target query driving branch, a quantity of the first query results according to the level mark.

[0013] A batch query module, configured to perform batch query, by the target query driving branch, according to a plurality of the first query results in a case where a counting result indicates that there are the plurality of the first query results and any of the first query results carries an end identifier, and send a second query result set obtained by the batch query to the target query driven branch.

[0014] According to a third aspect of an embodiment of the present specification, a database node is provided, comprising:

[0015] A processor;

[0016] A memory for storing processor-executable instructions;

[0017] The processor implements the optimization method of the nested loop query according to the first aspect by running the executable instructions.

[0018] According to a fourth aspect of the embodiments of the present specification, a distributed database is provided, which comprises the database node according to the third aspect.

[0019] According to a fifth aspect of the embodiments of the present specification, a computer readable storage medium is provided, which stores computer instructions, and the instructions are executed by a processor to implement the steps of the optimization method of the nested loop query according to the first aspect.

[0020] In the technical solutions provided in the present specification, for a target nested loop operator existing in a higher-level nested loop query, the first query result output by the query driver branch of the higher-level nested loop query is marked by level, so that the target nested loop operator can calculate the number of the first query result according to the level mark, so that when it is calculated that there are multiple first query results, the target query driver branch can perform batch query on the multiple first query results as a whole filtering condition, that is, the target query driver branch only needs to perform full table scanning once. Then the second result set obtained by batch query is uniformly sent to the target query driven branch, so that the target query driven branch can match each row taken out from the driven table with all the second results in the second result set, that is, the target query driven branch also only needs to perform full table scanning once. Through the above-mentioned manner, the number of full table scanning of the target query driver branch and the target query driven branch is effectively reduced, that is, the number of IO is effectively reduced, thereby reducing the execution overhead of the nested loop query at this level to which the target nested loop operator belongs. For the first-level nested loop query, BNL can be used to reduce the execution overhead of the first-level nested loop query, thereby realizing the reduction of the execution overhead of each level of nested loop query in the multi-level nested loop query, and achieving the optimization effect.

[0021] It should be understood that the above general description and the following detailed description are only exemplary and explanatory, and cannot limit the present specification. BRIEF DESCRIPTION OF DRAWINGS

[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present specification or the prior art, the drawings needed in the embodiment or prior art description will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments described in the present specification, and other drawings can also be obtained by those skilled in the art according to these drawings.

[0023] Figure 1 is a schematic diagram of an execution structure of a multi-level nested loop query according to an exemplary embodiment of the present specification;

[0024] Figure 2 is a schematic diagram of a distributed database according to an example embodiment of the present description;

[0025] Figure 3 is a flowchart of a method for optimizing a nested loop query according to an example embodiment of the present description;

[0026] Figure 4 is a schematic diagram of storage locations in a PS according to an example embodiment of the present description;

[0027] Figure 5 is a schematic diagram of an execution plan corresponding to a three-level nested loop query according to an example embodiment of the present description;

[0028] Figure 6a is a schematic diagram of adding level markers according to an example embodiment of the present description;

[0029] Figure 6b is another schematic diagram of adding level markers according to an example embodiment of the present description;

[0030] Figure 7 is a schematic diagram of a method for optimizing a multi-level nested loop query according to an example embodiment of the present description;

[0031] Figure 8 is a schematic diagram of a database node according to an example embodiment of the present description;

[0032] Figure 9 is a schematic diagram of an optimization device for a nested loop query according to an example embodiment of the present description. DETAILED DESCRIPTION

[0033] The example embodiments will be described in detail in this document with reference to the drawings. Unless otherwise indicated, the same numbers on different drawings represent the same or similar elements. The following detailed description does not represent all implementations consistent with the present description. Instead, they merely represent example devices and methods consistent with some aspects of the present description.

[0034] It should be noted that in other embodiments, the steps of the corresponding methods are not necessarily performed in the order shown and described in this specification. In some other embodiments, the methods may include more or fewer steps than those described in this specification. Furthermore, a single step described in this specification may be broken down into multiple steps in other embodiments; and multiple steps described in this specification may be combined into a single step in other embodiments. It should be understood that although the terms first, second, third, etc., may be used in this specification to describe various information, this information should not be limited to these terms. These terms are only used to distinguish information of the same type from each other. For example, without departing from the scope of this specification, first information may also be referred to as second information, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "when," or "in response to a determination."

[0035] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of related data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0036] Figure 1 This is a schematic diagram illustrating the execution structure of a multi-level nested loop query as an exemplary embodiment. For example... Figure 1 As shown, the execution structure of this nested loop query includes two levels of nested loop queries. Each level of nested loop query includes a nested loop operator (NLJ) and a query driver branch and a query driven branch connected to the nested loop operator. The nested loop operator of the second-level nested loop query is the query driven branch of the first-level nested loop query. The query driver branch is used to perform a loop scan on its corresponding driving table, and the query driven branch is used to perform a loop scan on its corresponding driven table. In the diagram, the first-level nested loop query consists of NLJ1, TSC (Table Scan, full table scan operator) 1, and NLJ2, while the second-level nested loop query consists of NLJ2, TSC2, and TSC3. That is, the query driver branch and the query driven branch of NLJ1 are TSC1 and NLJ2, respectively, and the query driver branch and the query driven branch of NLJ2 are TSC2 and TSC3, respectively. TSC1 is used to scan table t1, TSC2 is used to scan table t2, and TSC3 is used to scan table t3.

[0037] In the multi-level nested loop query, when the nested loop operator of a certain level of the nested loop query is the query driven branch of another level of the nested loop query, the certain level of the nested loop query is the next level of the another level of the nested loop query, or in other words, the another level of the nested loop query is the previous level of the certain level of the nested loop query. By Figure 1 It can be seen that the first level of the nested loop query is the previous level of the second level of the nested loop query. The basic execution logic of the two levels of the nested loop query is shown by the dashed arrow in the figure: the second level of the nested loop query is abstracted as a whole, and is taken as the query driven branch of the NLJ1. When the TSC1 spits out one row of data to the NLJ1, the NLJ1 performs one scan of the second level of the nested loop query with the row of data as the filter condition. It is assumed that the second level of the nested loop query is scanned once, and the TSC2 generates N rows of data, each of which is taken as a separate filter condition of the TSC3, so that the TSC3 performs one full table scan. That is, the TSC3 needs to perform N full table scans for each scan of the second level of the nested loop query.

[0038] For the distributed database, when the multi-level nested loop query is performed in the distributed database, each full table scan operator corresponds to a scanned data table which can be distributed on different nodes / devices. The nodes / devices in the distributed database need to communicate with each other by exchanging messages. Figure 2 is a schematic diagram of a distributed database provided by an example embodiment. As shown in Figure 2 The data table t1 scanned by the TSC1 is stored on the node 1, the data table t2 scanned by the TSC2 is stored on the node 2, and the data table t3 scanned by the TSC3 is stored on the node 3. The node 1, the node 2, the node 3 and the client communicate with each other through the network. That is, when the user initiates a nested loop query request on the client, the node 1 receives the nested loop query request, and calls the data table t1 to the TSC1 for scanning. Then the node 1 broadcasts the nested loop query request to the node 2 and the node 3, and the node 2 calls the data table t2 to the TSC2 for scanning, and the node 3 calls the data table t3 to the TSC3 for scanning. Therefore, for the TSC3, the TSC3 needs to communicate with the node 3 N times for each multi-level nested loop query, which greatly increases the network communication overhead.

[0039] If the BNL is used to implement the multi-level nested loop query, the TSC3 needs to communicate with the node 3 N times for each multi-level nested loop query, which greatly increases the network communication overhead. Figure 1The two-level nested loop query is optimized. During the query process, the query driving branch TSC1 can cache all data obtained by the query in the join_buffer, and then send all data in the join_buffer to the second-level nested loop query for scanning, thereby effectively reducing the scanning times of the second-level nested loop query. Therefore, the execution overhead of the first-level nested loop query can be optimized by the join_buffer. However, for the second-level nested loop query, the query driving branch TSC2 of the second-level nested loop query is driven by the NLJ1 in the first-level nested loop query. The NLJ2 cannot predict the number of data sent by TSC1 in advance, and therefore the NLJ2 usually scans each data sent by TSC1 as a filter condition of TSC2. Each scanning of TSC2 sends the query result obtained by scanning to TSC3, so that TSC3 performs a full table scan as a filter condition.

[0040] For example, TSC1 outputs 1000 rows of data according to the query condition t1.a>10 (that is, data greater than 10 in the a column of table t1), and stores the 1000 rows of data in the join_buffer in sequence, and then sends the 1000 rows of data to NLJ2 as a whole to scan NLJ2 as a filter condition. However, NLJ2 does not know that TSC1 sends 1000 rows of data, and therefore NLJ2 receives the data and scans the data as a filter condition of TSC2, so that TSC2 performs 1000 times of scanning. Each scanning of TSC2 produces N rows of data. Each row of data in the N rows is a separate filter condition, so that TSC3 performs a full table scan. Therefore, for the 1000 rows of data output by TSC1, TSC2 needs to perform 1000 times of scanning, and TSC3 needs to perform 1000*N times of scanning. It can be seen that the scanning times of TSC2 and TSC3 are not reduced, and are consistent with the scanning times before the BNL is taken. That is, because TSC2 cannot perform a full table scan on all data sent by TSC1 as a whole, and further cannot send all query results obtained by TSC2 to TSC3 as a whole in batches, the scanning times of TSC2 and TSC3 cannot be reduced, and the execution overhead of the second-level nested loop query and the network communication overhead of TSC3 cannot be optimized.

[0041] The present specification proposes an optimization method for a nested loop query, which can optimize the execution overhead of each level of nested loop query in a multi-level nested loop query.

[0042] Next, the embodiments of the present specification are described in detail.

[0043] Figure 3 This is a flowchart illustrating an optimization method for nested loop queries according to an exemplary embodiment of this specification. The execution structure of the nested loop query includes at least two levels of nested loop queries. Each level of nested loop query includes a nested loop operator and a query driving branch and a query driven branch connected to the nested loop operator. The nested loop operator of each level of nested loop query is the query driven branch of its parent level of nested loop query. For example... Figure 3 As shown, the optimization method may include the following steps:

[0044] S302, when the target nested loop operator determines that there is a previous nested loop query, it obtains the first query result output by the query driving branch in the previous nested loop query. The first query result carries a level marker corresponding to the previous nested loop query. The target nested loop operator is connected to the target query driving branch and the target query driven branch.

[0045] For multi-level nested loop queries, the nested loop operator of each level of nested loop query can serve as the target nested loop operator. The query driver branch and the query driven branch connected to the target nested loop operator are the target query driver branch and the target query driven branch, respectively. When the target nested loop operator determines that there is a parent nested loop query, the target nested loop operator becomes the query driven branch of its parent nested loop query. In this case, according to the execution logic of the nested loop query, the target nested loop operator needs to obtain the first query result output by the query driver branch of its parent nested loop query, and use this first query result as a filtering condition to scan the target query driver branch.

[0046] by Figure 1 Taking the two-level nested loop query as an example, when NLJ1 of the first-level nested loop query is the target nested loop operator, since the first-level nested loop query does not have a parent nested loop query, NLJ1 is not applicable to this solution. When NLJ2 of the second-level nested loop query is the target nested loop operator, since the first-level nested loop query is the parent nested loop query of the second-level nested loop query, NLJ2 can be certain that a parent nested loop query exists, and NLJ2 is the query driven branch of its parent (first-level) nested loop query. Therefore, NLJ2 needs to obtain the first query result output by the query driven branch TSC1 of the first-level nested loop query and use the first query result as a filtering condition for TSC2, causing TSC2 to perform a full table scan. The first query result carries a level marker corresponding to the parent nested loop query. This level marker indicates that the first query result is output by the query driven branch of the parent nested loop query of the target nested loop operator. The query results output by the query driven branches of different levels of nested loop queries carry different level markers.

[0047] In an embodiment, in the process that the target nested loop operator obtains the first query result output by the query driving branch in the upper level nested loop query, the first query result is obtained according to the storage location identifier. Since the nested loop operator does not have the ability to save the query result output by the query driving branch, the query result output by each query driving branch can be saved in the memory, so that when the subsequent nested loop operator needs to use the query result as a filtering condition for scanning, the query result can be directly obtained from the memory without the query driving branch repeatedly sending the query result to the nested loop operator. In this embodiment, the memory can be referred to as a parameter store (PS).

[0048] Figure 4 is a schematic diagram of each storage location in a PS provided by an exemplary embodiment. As shown in Figure 4 , there are multiple storage locations in the PS, and each storage location is provided with a corresponding storage location identifier. Among them, the storage location identifier of the storage location 1 is idx_0, the storage location identifier of the storage location 2 is idx_1, and the storage location identifier of the storage location 3 is idx_2. In combination with Figure 1 , the query result output by the query driving branch of each level of the multi-level nested loop query can be stored in each different storage location. That is, the query result output by TSC1 in the first level of the nested loop query is stored in the storage location 1, and the query result output by TSC2 in the second level of the nested loop query is stored in the storage location 2. Thus, the specific storage location of the query result output by each query driving branch can be locked according to the storage location identifier corresponding to each storage location, and then the corresponding query result can be obtained from the locked storage location.

[0049] Before executing the multi-level nested loop query, the execution plan corresponding to the multi-level nested loop query needs to be generated to execute the multi-level nested loop query according to the execution plan. The storage location identifier associated with each query driving branch will be recorded in the execution plan. The storage location identifier is used to indicate the storage location of the query result output by each query driving branch in the PS. The storage location identifier associated with any query driving branch is used to indicate the storage location of the query result output by the query driving branch in the upper level nested loop query of the query driving branch. Therefore, in the query process, each nested loop operator can obtain the query result output by the query driving branch associated with itself from the memory according to the storage location identifier associated with the query driving branch to perform scanning as a filtering condition. In this embodiment, the target nested loop operator can obtain the target storage location identifier associated with the query driving branch connected to itself from the execution plan, and then obtain the first query result from the PS according to the storage location represented by the target storage location identifier.

[0050] Figure 5 This is a schematic diagram of the execution plan corresponding to a three-level nested loop query, provided in an exemplary embodiment. For example... Figure 5 As shown, the execution plan records a storage location identifier idx_0 associated with TSC2; and storage locations idx_1 and idx_0 associated with TSC3. Combined with... Figure 4 According to the execution plan of this three-level nested loop query, the query result output by TSC1 will be stored in storage location 1, the query result output by TSC2 will be stored in storage location 2, and the query result output by TSC3 will be stored in storage location 3. During the execution of this three-level nested loop query, NLJ2, as the nested loop operator of the second-level nested loop query, can retrieve the query result output by TSC1 from storage location 1 corresponding to the storage location identifier idx_0 associated with its connected query driver branch TSC2, and use this query result as a filter condition for NLJ2. Similarly, abstracting the third-level nested loop query as a whole, NLJ3, as the nested loop operator of the third-level nested loop query, can obtain the storage location identifiers idx_0 and idx_1 associated with its connected query driver branch TSC3 from the execution plan. Then, based on these two storage location identifiers, it retrieves the query results output by TSC1 and TSC2 from the corresponding storage locations 1 and 2 respectively, and uses these two query results as filter conditions for the entire third-level nested loop query. The specific execution logic is as follows: TSC3 first scans table t3 based on the query results output by TSC1 and TSC2, and then sends the query results to NLJ3. Then, TSC4 uses the query results obtained by TSC3, as well as the query results output by TSC1 and TSC2, as filtering conditions to query table t4.

[0051] It's important to note that different execution plans are generated for different query requirements. Therefore, for any level of target query driven branch, its filtering conditions may not necessarily include the query results output by all query driven branches above it, in addition to the query results output by the same level of target query driven branch. Figure 5For example, the target query driven branch TSC4 can query the query result output by TSC3 and the query result output by TSC2 as filter conditions, or the target query driven branch TSC4 can query the query result output by TSC3 and the query result output by TSC1 as filter conditions. The target query driven branch TSC4 can also query the query result output by TSC3, and the query result output by TSC1 and TSC2 as filter conditions. In other words, the filter conditions of the target query driven branch are determined by the specific query requirement of the user. Therefore, when the execution plan is generated, the storage location identifier associated with the target query driven branch can be recorded in the execution plan, which is used to indicate the storage location of the query result output by the query driven branch in the above level nested loop query of the target query driven branch. For example, the execution plan includes "ObFixedArray <int>TSC4_param_idx_1", which indicates that the storage location associated with the target query driven branch TSC4 is identified as idx_1. Then the output result of TSC2 can be obtained from the storage location 2 corresponding to idx_1, without obtaining the output result of TSC1. The query result output by TSC2 and the query result output by TSC3 are used as the filter condition of TSC4 to scan the table t4.

[0052] In this embodiment, the storage location of the query result output by each query driven branch is defined when the execution plan is generated, so that the query result output by each query driven branch can be stored in the specified location. The storage location identifier associated with each query driven branch is recorded in the execution plan, so that each nested loop operator in the query process obtains the corresponding query result from the storage location represented by the storage location identifier associated with the query driven branch connected to itself, and scans the obtained query result as the filter condition. This way, the query driven branch that outputs the query result does not need to repeatedly send the query result to the nested loop operator, which reduces the waiting time of the nested loop operator to obtain the query result and further reduces the overhead of the multi-level nested loop query.

[0053] S304, the target query driven branch counts the number of the first query results according to the level mark.

[0054] In an embodiment, the level mark can include a start level mark and an end level mark. Since the first query result output by the upper query driven branch is sequentially stored in the join_buffer, there is a certain order between the first query results. By adding the start level mark to the start query result in the first query results and adding the end level mark to the last query result in the first query results, the target query driven branch can determine the start query result according to the start level mark and determine the last query result according to the end level mark. Then the number of all query results from the start query result to the last query result is calculated, which is the number of the first query results.

[0055] Figure 6a is a schematic diagram of adding a level mark provided by an exemplary embodiment. It is assumed that TSC1 outputs 1000 rows of data, and these 1000 rows of data are all stored in the storage location 1 of PS. Among them, the first row of data output carries a start level mark, and the last row of data carries an end level mark. As shown in Figure 6a As shown, the starting level mark is s-NLJ1, and the ending level mark is e-NLJ1. Here, s represents the starting, e represents the ending, and NLJ1 represents that the two level marks correspond to the first level nested loop join query to which NLJ1 belongs. Similarly, the starting query result in the query result output by TSC2 will carry the starting level mark s-NLJ2, and the ending query result will carry the ending level mark e-NLJ2. The target query driving branch can determine the starting query result and the ending query result according to the starting level mark and the ending level mark, respectively, and then count the number of first query results from the starting query result to the ending query result.

[0056] In this embodiment, only the starting query result in the first query result output by the query driving branch of the upper level needs to be marked with the starting level mark, and the ending query result needs to be marked with the ending level mark, without marking all the first query results, thereby helping to avoid wasting marking resources and improving the efficiency of marking the first query results.

[0057] In another embodiment, the same level mark can be added to each first query result, so that the target query driving branch can determine the number of first query results by counting the number of the same level marks. Figure 6b As shown, the level mark NLJ1 is added to each row of data in the 1000 rows of data output by TSC1, and NLJ1 can indicate that the level mark corresponds to the first level nested loop join query. Similarly, the level mark NLJ2 can be added to each query result output by TSC2, so as to determine the query result output by the query driving branch in the second level nested loop join query according to NLJ2. For example, * can represent the first level nested loop join query, and ﹟ can represent the second level nested loop join query. Since the level marks corresponding to nested loop queries of different levels are not the same, adding the same level mark to the query result output by the query driving branch in the nested loop query of any level can clearly determine which query results are batch output by the same query driving branch. Moreover, the number of the same level marks can be counted to obtain the number of first query results, which helps to improve the accuracy of counting the number of first query results and avoid omission in the counting process. The specific format and specific content of the level mark can be set by those skilled in the art, and the present specification does not limit it.

[0058] In an embodiment, the query driving branch output by the nested loop join operator of any level can be added with the level mark corresponding to the any level. In combination with the above embodiments, the query driving branch of the target query can determine the number of first query results by counting the number of the same level marks. Figure 1 The NLJ2 of the second level nested loop query is the query driven branch of the NLJ1. Therefore, the NLJ1 can add a level mark S1 to the query result output by the TSC1, so that when the TSC1 sends the query result to the NLJ2 in batches, the TSC2 can count the number of query results output by the TSC1 according to the level mark S1, and then determine whether the TSC2 needs to perform batch scanning according to the counting result.

[0059] For the target nested loop operator, the target nested loop operator needs to add a level mark corresponding to the current level to the second query result output by the connected target query driven branch. The current level is the level of the nested loop query to which the target nested loop operator belongs.

[0060] In this embodiment, by adding the level mark, the query driven branch of the next level nested loop query can determine the number of query results output by the query driven branch connected to the any level nested loop query operator according to the level mark, and then determine whether the query driven branch of the next level nested loop query needs to perform batch scanning according to the number of query results.

[0061] In another embodiment, the any level nested loop query operator can add the corresponding level mark to the query result output by the connected query driven branch only when it is determined that there is a next level nested loop query. In this case, the nested loop operator of the next level nested loop query is the query driven branch of the any level nested loop query operator. In combination with the above description, the any level nested loop query operator can add the level mark to the query result output by the query driven branch only when it is determined that there is a next level nested loop query. Figure 1 When the NLJ1 determines that there is a second level nested loop query, the NLJ2 of the second level nested loop query is the query driven branch of the NLJ1. At this time, the NLJ1 can add a level mark S1 to the query result output by the TSC1, so that when the TSC1 sends the query result to the NLJ2 in batches, the TSC2 can count the number of query results output by the TSC1 according to the level mark S1, and then determine whether the TSC2 needs to perform batch scanning according to the counting result. The NLJ2 determines that there is no next level nested loop query, and therefore the NLJ2 can not add a level mark to the query result output by the TSC2, thereby reducing the time for the NLJ2 to add the level mark, which helps to save storage space.

[0062] S306, in the case that the counting result shows that there are multiple first query results, and any of the first query results carries an end identifier, the target query driven branch performs batch query according to the multiple first query results, and sends a second query result set obtained by the batch query to the target query driven branch.

[0063] When the statistical result shows that there are multiple first query results, and any first query result carries an end identifier, it indicates that the multiple first query results are the same batch of query results obtained by the query driven sub-query in the upper level nested loop query. Therefore, the target query driven sub-query can perform batch query on the same batch of query results as a whole, so as to realize only one full table scan, without multiple full table scans, thereby saving the execution overhead.

[0064] In combination Figure 1 A detailed description is as follows: it is assumed that TSC1 sends 1000 data to NLJ2 in batches. Figure 1 In the embodiment shown, TSC2 needs to perform 1000 scan operations. Each scan operation of TSC2 includes 5 steps: (1) obtaining one data as a filtering condition; (2) performing full table scan according to the data; (3) outputting the scan result corresponding to the data to NLJ2; (4) outputting an end iterator, which is used to represent the end of this scan; and (5) calling a scan interface. The 5 steps need to be repeated 1000 times to end the scan operation of TSC2.

[0065] Figure 7 A schematic diagram of an optimization method of a multi-level nested loop query provided for an exemplary embodiment of the present specification is shown. As shown in Figure 7 As shown, TSC2 performs full table scan operation on 1000 data as a whole only once, which includes: (1) obtaining 1000 data; (2) performing full table scan according to 1000 data; and (3) uniformly outputting the scan result corresponding to 1000 data to NLJ2, instead of outputting the scan result corresponding to each data to NLJ2 in sequence. Obviously, the method shown in the embodiment of the present specification does not need to repeatedly perform full table scan on the data in the storage layer by calling the scan interface multiple times, effectively reduces the number of full table scans of TSC2 (i.e. reduces the number of IOs), and reduces the execution overhead of the second level nested loop query.

[0066] Then, the target query driven sub-query sends the second query result set obtained by batch query to the target query driven sub-query, so that the target query driven sub-query can perform batch scan based on the second query result set. Figure 1 In the related art, the target query driven sub-query (TSC3) needs to perform N full table scans for each scan operation of the target query driven sub-query (TSC2). That is, the TSC3 finally needs to perform 1000*N times of scans in the related art. However, in the embodiment of the present specification, Figure 7 In the method, the TSC 2 sends the second query result set to the TSC 3 as a whole, so that the TSC 3 performs full table scanning only once and outputs the final query result as a filtering condition, thereby greatly reducing the scanning times of the TSC 3 and the execution overhead of the second-level nested loop query. That is, the node 3 only needs to call the data table t3 of the TSC 3 once for each nested loop query, thereby greatly reducing the communication times between the TSC 3 and the node 3 and the network communication overhead.

[0067] It can be seen that, in the scheme, the query driven branch and the query driven branch of each level of the nested loop query except the first level of the nested loop query can be greatly reduced, thereby effectively reducing the execution overhead of each level of the nested loop query. For the first level of the nested loop query, the BNL can be used to reduce the execution overhead of the first level of the nested loop query, thereby reducing the execution overhead of each level of the nested loop query in the multi-level nested loop query. Meanwhile, the communication times between the nodes are reduced, thereby effectively reducing the network communication overhead.

[0068] In an embodiment, since the nested loop operator of each level of the nested loop query is the query driven branch of the upper level of the nested loop query, the target query driven branch is subject to the driving of the nested loop operator in the upper level of the nested loop query, like the target query driven branch. That is, the target query driven branch also needs to query the first query result as a filtering condition, and then output the final result satisfying the first query result and the second query result set.

[0069] Specifically, the target nested loop operator sends a plurality of first query results to the target query driven branch. Since the second query result set is scanned by the target query driven branch based on the plurality of first query results, each second query result in the second query result set has a corresponding relationship with the plurality of first query results. For example, it is assumed that the first query result is 5, which are t1.1, t1.2, t2.1, t2.2 and t2.3. Then, the target query driven branch performs batch scanning based on the 5 first query results, and the obtained second query result set includes t1.1.1, t1.1.2, t1.2.1, t1.2.2, t1.2.3, t2.1.1, t2.3.1, t2.3.2 and t2.3.3. Thus, it can be determined that each second query result in the second query result set has a corresponding relationship with the plurality of first query results, as shown in Table 1.

[0070]

[0071] Table 1

[0072] From Table 1, t1.1 corresponds to two second query results, t1.2 corresponds to three second query results, t2.1 corresponds to one second query result, and t2.3 corresponds to three second query results. t2.2 has no corresponding second query result, which means that the target query driving branch does not query the data matching t2.2 in the full table, and the output is empty.

[0073] In the specific execution process of the multi-level nested loop query, the target nested loop operator can record the correspondence between each second query result and the plurality of first query results. In the multi-level nested loop query, the query driving branch of each level of nested loop query outputs data in the form of multiple groups of iterators, and each group of iterators corresponds to a first query result. Therefore, the target nested loop operator can record the number of rows contained in each group of iterators used in the target query driving branch, thereby determining the number of second query results corresponding to each first query result.

[0074] After determining the correspondence between each second query result and the plurality of first query results, the target nested loop operator generates a plurality of query conditions according to the correspondence, each query condition including each first query result and the second query result corresponding to the first query result. According to Table 1, five query conditions can be generated. Then, the target query driving branch is caused to perform batch query according to the plurality of query conditions.

[0075] In this embodiment, based on the correspondence between the first query result and each second query result in the second query result set, a plurality of association query conditions can be generated, and then batch query can be performed according to the plurality of association query conditions, thereby achieving batch output of the final result that meets the two types of filtering conditions (the two types of filtering conditions are the first query result and the second query result).

[0076] In an embodiment, since the number of first query results and the number of second query results corresponding to the first query results are different, that is, the lengths of the two types of filtering conditions in the generated query conditions are inconsistent, it is easy to cause query errors. In order to ensure the correctness of the nested loop query, the target nested loop operator can first determine the number of second query results corresponding to each first query result when generating the plurality of query conditions based on the correspondence. According to the number of second query results corresponding to each first query result, the number of each first query result is modified so that the modified number of each first query result is equal to the number of second query results corresponding to the first query result.

[0077] Taking Table 1 as an example, the number of t1.1 is increased to 2, the numbers of t1.2 and t2.3 are increased to 3 respectively, and the number of t2.2 is reduced to 0. The number of t2.1 is equal to the number of second query results corresponding to the first query result, and therefore no modification is needed. After modifying the number of each first query result, the correspondence is shown in Table 2:

[0078] First query result Second query result t1.1 t1.1.1 t1.1 t1.1.2 t1.2 t1.2.1 t1.2 t1.2.2 t1.2 t1.2.3 t2.1 t2.1.1 t2.3 t2.3.1 t2.3 t2.3.2 t2.3 t2.3.2

[0079] Table 2

[0080] Then, based on the correspondence between the first query result after modification of the number and each second query result, a plurality of query conditions are generated. According to Table 2, nine query conditions can be generated, and in each query condition, the first query result and the second query result correspond one by one. In this embodiment, by modifying the number of the first query result, the number of each first query result is kept consistent with the number of the corresponding second query result, so that the length of the two types of filtering conditions in each generated query condition can be guaranteed to be consistent, ensuring the correctness of the query result.

[0081] Through the method of this embodiment, each level of nested loop query except the first level of nested loop query can ensure the length of the filtering conditions of the query driven branch (i.e., the first query result and the second query result), and further realize the consistency of the query pace between the multi-level nested loop queries, and ensure the correctness of the final query result. In combination with Figure 5 For detailed description, assuming that in the generated execution plan, the filtering conditions of the query driven branch NLJ3 are the query result output by TSC1 and the query result output by TSC2. Then, in the second level of nested loop query, the nested loop operator NLJ2 can guarantee the length of the two types of filtering conditions of the query driven branch NLJ3 of the second level of nested loop query by modifying the number of the query result output by TSC1, that is, the consistency of the query pace of the first level and the second level of nested loop query is realized.

[0082] If in the execution plan, the filtering conditions of the query driven branch TSC4 are the query result output by TSC2 and the query result output by TSC3. Then, in the third level of nested loop query, the nested loop operator NLJ3 can guarantee the length of the two types of filtering conditions of the query driven branch TSC4 of the third level of nested loop query by modifying the number of the query result output by TSC2, that is, the consistency of the query pace of the second level and the third level of nested loop query is realized. Further, the consistency of the query pace between the first level, the second level, and the third level of nested loop query is realized.

[0083] If the query driven by the TSC4 is executed in the plan, the query result outputted by the TSC1 and the query result outputted by the TSC3 are the filter conditions of the TSC4. Then, in the third level of the nested loop query, the nested loop operator NLJ3 can ensure the length of the two types of filter conditions of the query driven by the TSC4 to be consistent by modifying the number of the query result outputted by the TSC1. Since one of the two types of filter conditions, i.e. the query result outputted by the TSC3, is scanned by taking the query result outputted by the TSC1 and the TSC2 as the filter conditions, and the first level and the second level of the nested loop query have kept the consistency, the consistency of the first level, the second level and the third level of the nested loop query can be ensured by ensuring the length of the two types of filter conditions of the query driven by the TSC4, i.e. the query result outputted by the TSC3 and the TSC1.

[0084] Figure 8 Fig. 1 is a schematic structural diagram of a database node according to an example embodiment. Please refer to Figure 8 At the hardware level, the database node comprises a processor 802, an internal bus 804, a network interface 806, a memory 808 and a non-volatile memory 810, and can further comprise other required hardware. One or more embodiments of the present specification can be implemented in a software manner, such as reading a corresponding computer program from the non-volatile memory 810 to the memory 808 by the processor 802 and then running. Of course, in addition to the software implementation, one or more embodiments of the present specification do not exclude other implementation manners, such as a logic device or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or a logic device.

[0085] Corresponding to the embodiment of the optimization method of the nested loop query, the present specification also provides an embodiment of the optimization device of the nested loop query.

[0086] Please refer to Figure 9 , Figure 9 Fig. 2 is a structural schematic diagram of the optimization device of the nested loop query according to an example embodiment. As shown in Figure 9 In the software implementation, the execution structure of the nested loop query comprises at least two levels of nested loop queries, each level of the nested loop query comprises a nested loop operator and a query driven branch and a query driven branch connected with the nested loop operator, and the nested loop operator of each level of the nested loop query is the query driven branch of the upper level of the nested loop query. The device can comprise an acquisition module 902, a statistics module 904 and a batch query module 906, wherein:

[0087] The acquisition module 902 is configured to, in a case where it is determined that there is a previous nested loop query, acquire a first query result output by a query driving branch in the previous nested loop query, the first query result carrying a level mark corresponding to the previous nested loop query; and the target nested loop operator is connected with a target query driving branch and a target query driven branch.

[0088] The statistics module 904 is configured to count the number of the first query results according to the level mark by the target query driving branch.

[0089] The batch query module 906 is configured to, in a case where the counting result indicates that there are a plurality of the first query results and any of the first query results carries an end mark, perform batch query according to the plurality of the first query results by the target query driving branch, and send a second query result set obtained by the batch query to the target query driven branch.

[0090] Optionally, the execution plan record corresponding to the nested loop query has a storage location mark associated with the query driving branch of each level of the nested loop query, and the storage location mark is used to indicate a storage location of a query result output by a query driving branch in each level of the nested loop query above the level; and the acquisition module 902 is specifically configured to:

[0091] The target nested loop operator acquires a target storage location mark associated with the target query driving branch from the execution plan; and the target nested loop operator acquires the first query result from a storage location corresponding to the target storage location mark.

[0092] Optionally, the level mark includes a start level mark and an end level mark; and the statistics module 904 is specifically configured to:

[0093] The target query driving branch determines a start query result according to the start level mark, and determines an end query result according to the end level mark; counts the number of all query results from the start query result to the end query result, and takes the counting result as the number of the first query results.

[0094] Optionally, each of the first query results carries the same level mark; and the statistics module 904 is specifically configured to:

[0095] The target query driving branch counts the number of the same level marks, and takes the counting result as the number of the first query results.

[0096] Optionally, the apparatus further includes:

[0097] The label adding module 908 is configured to add, by the target nested loop operator, a level label corresponding to a nested loop query to which the target nested loop operator belongs to the second query result set.

[0098] Optionally, the apparatus further comprises:

[0099] The generating module 910 is configured to send, by the target nested loop operator, the plurality of first query results to the target query driven branch; determine, by the target nested loop operator, a correspondence between each second query result contained in the second query result set and the plurality of first query results, and generate a plurality of query conditions based on the correspondence, wherein each query condition comprises each first query result and its corresponding second query result; and perform batch query according to the plurality of query conditions by the target query driven branch.

[0100] Optionally, the target nested loop operator generates a plurality of query conditions based on the correspondence, comprising: determining, by the target nested loop operator, a number of second query results corresponding to each first query result based on the correspondence; modifying, by the target nested loop operator, the number of each first query result based on the number of second query results, wherein the modified number of each first query result is equal to the number of its corresponding second query result; and generating the plurality of query conditions based on the correspondence between the modified number of first query results and the second query results.

[0101] The implementation process of the functions and roles of each unit in the above apparatus is specifically described in the implementation process of the corresponding steps in the above method, which will not be repeated here.

[0102] For the device embodiment, since it basically corresponds to the method embodiment, the related parts can be referred to the part of the method embodiment. The device embodiments described above are only illustrative, and the units described as separate components can or can not be physically separated, and the components displayed as units can or can not be physical units, that is, they can be located in one place, or they can be distributed on multiple network units. According to actual needs, some or all of the modules can be selected to achieve the purpose of the scheme of the present application. Those skilled in the art can understand and implement it without creative labor.

[0103] The apparatuses or modules illustrated in the above embodiments can be specifically implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, and the computer can be specifically a personal computer, a laptop computer, a cellular phone, a camera phone, a smartphone, a personal digital assistant, a media player, a navigation device, an e-mail device, a game console, a tablet computer, a wearable device, or a combination of any of these devices.

[0104] In a typical configuration, a computer includes one or more processors, including a processor (CPU) and a graphics processor (GPU), an input / output interface, a network interface, and a memory. Among them, the processor is used for computing simulation, and the graphics processor is used for outputting high-quality three-dimensional images.

[0105] The memory can include non-permanent memory in a computer readable medium, random access memory (RAM), and / or non-volatile memory such as read-only memory (ROM) or flash memory (flash RAM). The memory is an example of a computer readable medium.

[0106] The computer readable medium includes permanent and non-permanent, removable and non-removable media, which can be implemented by any method or technology to store information. The information can be computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassette, disk storage, quantum memory, graphene-based storage medium, or other magnetic storage device, or any other non-transmission medium that can be used to store information accessible by a computing device. According to the definition in this paper, computer readable medium does not include transitory computer readable medium, such as modulated data signals and carriers.

[0107] Similarly, although the operations are depicted in a particular order in the accompanying drawings, this should not be understood as requiring the operations to be performed in the particular order shown or sequentially, or requiring all of the illustrated operations to be performed to achieve the desired result. In some cases, multitasking and parallel processing can be advantageous. In addition, the separation of various system modules and components in the above embodiments should not be understood as requiring such separation in all embodiments, and it should be understood that the described program components and systems can generally be integrated together in a single software product, or packaged into multiple software products.

[0108] Therefore, specific embodiments of the subject matter have been described. Furthermore, the processes depicted in the figures are not necessarily shown in a specific order or sequence to achieve the desired result. In some implementations, multitasking and parallel processing may be advantageous.

[0109] The above description is merely a preferred embodiment of this specification and is not intended to limit this specification. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this specification should be included within the scope of protection of this specification.< / int>

Claims

1. A method for optimizing a nested loop query, an execution structure of the nested loop query comprising at least two levels of nested loop queries, each level of the nested loop queries comprising a nested loop operator and a query driven branch and a query driven branch connected to the nested loop operator, and the nested loop operator of each level of the nested loop queries being the query driven branch of the nested loop query at a level above, the method comprising: a target nested loop operator, in a case where it is determined that there is a nested loop query at a level above, obtaining a first query result output by a query driven branch in the nested loop query at the level above, the first query result carrying a level mark corresponding to the nested loop query at the level above; the target nested loop operator being connected to a target query driven branch and a target query driven branch; the target query driven branch counting a number of the first query results according to the level mark; in a case where the counting result indicates that there are a plurality of the first query results and any of the first query results carries an end mark, the target query driven branch performing batch query according to the plurality of the first query results and sending a second query result set obtained by the batch query to the target query driven branch. 2.The method of claim 1, a storage location mark associated with the query driven branch of each level of the nested loop queries being recorded in an execution plan corresponding to the nested loop query, the storage location mark being used to indicate a storage location of a query result output by the query driven branch in each level of the nested loop queries above; the target nested loop operator obtaining the first query result output by the query driven branch in the nested loop query at the level above comprising: the target nested loop operator obtaining a target storage location mark associated with the target query driven branch from the execution plan; and the target nested loop operator obtaining the first query result from a storage location corresponding to the target storage location mark. The target query driven branch counting the number of the first query results according to the level mark comprising: the target query driven branch determining a starting query result according to the starting level mark and a final query result according to the end level mark; and counting a number of all query results from the starting query result to the final query result and taking the counting result as the number of the first query results. 4.The method of claim 1, each of the first query results carrying a same level mark; the target query driven branch counting the number of the first query results according to the level mark comprising: the target query driven branch counting a number of the same level marks and taking the counting result as the number of the first query results. 5.The method of claim 1, further comprising: the target nested loop operator adding a level mark corresponding to the nested loop query to which the target nested loop operator belongs to the second query result set. 6.The method of claim 1, further comprising: the target nested loop operator sending the plurality of the first query results to the target query driven branch. ​ ​ ​ 3. The method of claim 1, the level markers comprising a start level marker and an end level marker. ​ ​ ​ ​ ​ ​ ​ ​ ​ The target nested loop operator determines a correspondence between each second query result contained in the second query result set and the plurality of first query results, and generates a plurality of query conditions based on the correspondence, wherein each query condition includes each first query result and its corresponding second query result; The target query driven branch performs batch query according to the plurality of query conditions.

7. The method of claim 6, wherein the target nested loop operator generates a plurality of query conditions based on the correspondence, comprising: The target nested loop operator determines the number of second query results corresponding to each first query result based on the correspondence; Based on the number of second query results, the target nested loop operator modifies the number of each first query result, wherein the modified number of each first query result is equal to the number of its corresponding second query result; Based on the modified number of first query results and the correspondence between each first query result and each second query result, the plurality of query conditions are generated.

8. An optimization device for nested loop query, wherein the execution structure of the nested loop query comprises at least two levels of nested loop queries, each level of nested loop query comprises a nested loop operator and a query driven branch and a query driven branch connected to the nested loop operator, and the nested loop operator of each level of nested loop query is the query driven branch of the upper level of nested loop query, the device comprising: An acquisition module, configured to acquire, by a target nested loop operator, first query results output by a query driven branch of an upper level of nested loop query when it is determined that the upper level of nested loop query exists, wherein the first query results carry a level mark corresponding to the upper level of nested loop query; The target nested loop operator is connected with a target query driven branch and a target query driven branch; A statistics module, configured to count the number of first query results according to the level mark by the target query driven branch; A batch query module, configured to perform batch query according to the plurality of first query results by the target query driven branch when the statistics result shows that there are a plurality of first query results and any first query result carries an end identifier, and send a second query result set obtained by batch query to the target query driven branch.

9. A database node, comprising: a processor; a memory for storing processor-executable instructions; wherein the processor implements the optimization method for nested loop query according to any one of claims 1-7 by running the executable instructions.

10. A distributed database comprising the database node of claim 9.

11. A computer-readable storage medium having computer instructions stored thereon, wherein the instructions are executed by a processor to implement the steps of the optimization method for nested loop query according to any one of claims 1-7.

Images