Database multi-table connection processing method and related product

Abstract

The invention provides a multi-table connection processing method of a database and a related product. The multi-table connection processing method comprises the following steps: acquiring a plurality of tables to be connected; constructing a heap sorting structure; ranking the row data of the plurality of tables to be connected by using a heap ranking structure, and respectively forming candidate result subsets by using the row data with the same connection key value; and determining the candidate result subsets meeting a multi-table connection condition to obtain a multi-table connection result. According to the multi-table connection processing method, the memory and I / O consumption is reduced, the execution efficiency of large-scale data and complex query is remarkably improved, the optimization requirement of the connection sequence is eliminated, the query planning time is remarkably shortened, and the CPU and memory overhead is reduced.

Description

Technical Field

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

[0002] In relational databases, multi-table joins are one of the core operations in complex queries, widely used in data warehousing, online analytical processing (OLAP), and transaction processing. Merge join is a commonly used method for joining two tables equi-joined, suitable for tables sorted based on their join keys. Merge join compares the join keys of the two tables in a single scan, generating matching tuples.

[0003] In multi-table join scenarios, pairwise merge joins are typically used, combined with optimized paths to achieve complex queries. Specifically, this involves multiple calls to the pairwise merge join operator, processing two tables each time, merging based on sorted join keys, and generating intermediate results. These intermediate results serve as input for the next merge join, repeating this process until all tables are joined. To reduce join costs, a database optimizer is used, employing dynamic programming, genetic algorithms, greedy algorithms, etc., to calculate the optimal join order, minimizing the size of intermediate results and computational overhead. The optimizer typically calculates the cost by estimating the statistics of each table (such as the number of rows and join key distribution), and then selects the best path.

[0004] However, the above multi-table join processing method has the following drawbacks: 1. The optimizer has high computational overhead. The complexity of calculating the optimal join order increases exponentially with the number of tables n (i.e., O(n!)). When there are many tables, the optimizer takes a significant amount of time, which affects query performance.

[0005] 2. Multiple calls to the merge join operator are required. For a join of n tables, a multi-table join requires n-1 merge join operations. Each call involves operator initialization, execution, and intermediate result management, increasing CPU and memory overhead. Multiple operator calls may also lead to context switching, further reducing performance.

[0006] 3. Intermediate results incur significant overhead. The intermediate results generated by each merge and join need to be stored and transmitted, which may consume a large amount of memory or disk space. Especially when the data volume is large or the join order is not optimal, the intermediate results may expand rapidly, leading to performance bottlenecks. Summary of the Invention

[0007] One object of the present invention is to provide a method for processing multiple table joins in a database, a computer-readable storage medium, a computer program product, and a computer device, so as to improve the efficiency of multiple table joins and reduce the overhead of multiple table joins.

[0008] Specifically, according to one aspect of the present invention, the present invention provides a method for processing multiple table joins in a database, comprising: Obtain multiple tables to be joined, wherein the tables to be joined include multiple rows of data sorted by the size of the join key value of each row; Construct a heap sort structure, the initial capacity of which is equal to the number of tables to be joined, and the heap sort structure uses the join key value as the sorting element. The heap sort structure is used to sort the row data of the multiple tables to be joined, and the row data with the same join key value are respectively formed into a subset of candidate results. In each subset of candidate results, those that satisfy the multi-table join condition are identified, and the multi-table join result is obtained.

[0009] Optionally, the row data of the table to be joined is sorted in ascending order of the join key value, and the heap sort structure includes a min-heap.

[0010] Optionally, the row data of the table to be joined is sorted in descending order of the join key value, and the heap sort structure includes a max-heap.

[0011] Optionally, the step of sorting the row data of the multiple tables to be joined using the heap sort structure, and forming candidate result subsets from the row data with the same join key value, includes: Each row of data from each of the tables to be joined is processed into a heap tuple and inserted into the heap sort structure; and the heap sort structure inserts only one heap tuple at a time. Obtain the join key value of the top tuple of the heap sort structure as the current join value; Determine whether the join key value of the top tuple of the heap is equal to the current join value; If they are equal, then pop the top heap tuple, record the row data corresponding to the popped top heap tuple into the candidate result subset corresponding to the current join value, and return to execute the step of determining whether the join key value of the top heap tuple is equal to the current join value; If not equal, then based on the situation where the row data of each of the tables to be joined has not yet been inserted into the heap sort structure, return to the step of obtaining the join key value of the top tuple of the heap sort structure as the current join value.

[0012] Optionally, the step of processing each row of data from each of the tables to be joined into a heap tuple and inserting it into the heap sort structure includes: An iterator is constructed for each of the tables to be joined, and the iterator retrieves one row of data from the corresponding table at each time. The row data output by each iterator is constructed into a triple, which includes the join key value of the row data, the identifier of the table to be joined to which the row data belongs, and the data of the preset field of the row data; Each of the triples is inserted into the heap sort structure as a heap tuple, wherein at any given time, at most one of the triples in any of the lists to be joined is inserted into the heap sort structure.

[0013] Optionally, the step of popping the top tuple of the heap and recording the row data corresponding to the popped top tuple into the subset of candidate results corresponding to the current join value includes: Record the data of the preset field of the row data of the top tuple of the heap into the candidate result subset corresponding to the current join value; Remove the top tuple from the heap; Advance the iterator of the table to be joined corresponding to the top tuple of the heap, and determine whether the iterator has a next row of data; If it exists, the next row of data is constructed into the triplet and inserted into the heap sort structure as the heap tuple; If it does not exist, then decrease the current capacity by 1; The heap sort structure sorts each of the heap tuples.

[0014] Optionally, if the rows of data in each of the tables to be joined have not yet been inserted into the heap sort structure, the step of retrieving the join key value of the top tuple of the heap sort structure as the current join value is returned, including: Determine whether the current capacity is less than the initial capacity; If not, return to the step of obtaining the join key value of the top tuple of the heap sort structure as the current join value.

[0015] Optionally, determining the table join results that satisfy the multi-table join conditions from each subset of candidate results to obtain the multi-table join result includes: In each case where the join key value of the top tuple of the heap is not equal to the current join value, it is determined whether the row data of each table to be joined exists in the candidate result subset corresponding to the current join value; If all of them exist, then the subset of candidate results is determined to satisfy the multi-table join condition; Obtain the Cartesian product of the subset of candidate results and output it to the dataset that serves as the result of the multi-table join.

[0016] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of the multi-table join processing method for the database described above.

[0017] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of any of the above-described database multi-table join processing methods.

[0018] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of the multi-table join processing method for the database described above.

[0019] The multi-table join processing method of this invention constructs a heap sort structure matching n tables to be joined, and uses the heap sort structure to maintain the row data of each table to be joined in O(log n) time complexity. This achieves automatic aggregation and sorting of row data according to the join key value, obtaining a subset of candidate results corresponding to each join key value. By determining those that satisfy the multi-table join conditions from each subset of candidate results, the multi-table join result is obtained. Compared with the prior art, which uses pairwise merge join operators multiple times and uses an optimizer to perform path calculation in O(n!) time complexity, this method achieves efficient join in a single operation, reduces memory and I / O consumption, and significantly improves the execution efficiency of large-scale data and complex queries. Furthermore, it eliminates the need for join order optimization, significantly shortens query planning time, and reduces CPU and memory overhead. Especially in complex queries with a large number of tables to be joined, it greatly improves the response speed of complex queries and enhances the real-time performance of the database.

[0020] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0021] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a flowchart illustrating a multi-table join processing method according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating the sorting process using a heap sort structure in a multi-table join processing method according to an embodiment of the present invention. Figure 3 This is a flowchart illustrating the process of inserting row data as heap tuples into a heap sort structure in a multi-table join processing method according to an embodiment of the present invention. Figure 4 This is a flowchart illustrating the processing of the popped top element of a heap in a multi-table join processing method according to an embodiment of the present invention. Figure 5 This is a flowchart illustrating the processing of a subset of candidate results in a multi-table join processing method according to an embodiment of the present invention. Figure 6 This is a flowchart illustrating a multi-table join processing method according to another embodiment of the present invention; Figure 7 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 8 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 9 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0022] The purpose of the database multi-table join processing method in this embodiment is to improve the efficiency of multi-table joins and reduce the overhead of multi-table joins.

[0023] Figure 1 This is a flowchart illustrating a multi-table join processing method for a database according to an embodiment of the present invention. The method generally includes: S100: Obtain multiple tables to be joined, which include multiple rows of data sorted by the size of the join key value of each row; S200, construct a heap sort structure. The initial capacity of the heap sort structure is equal to the number of lists to be joined. The heap sort structure uses the join key value as the sorting element. S300 uses a heap sort structure to sort the row data of multiple tables to be joined, and forms candidate result subsets of the row data with the same join key value. S400: Identify the candidates that meet the multi-table join conditions from each subset of candidate results, and obtain the multi-table join result.

[0024] This embodiment is used to perform a multi-table equivalent merge join on multiple tables to be joined. There can be n tables to be joined, where n is an integer greater than or equal to 2. The n tables to be joined can be represented as T1, T2, ..., T... n .

[0025] A heap sort structure is a complete binary tree used for efficient heap sort. Heap sort structures can include min-heaps, max-heaps, etc. In a min-heap, the value of any node (called a heap tuple) is less than or equal to the value of its child nodes, while in a max-heap, the value of any node (called a heap tuple) is greater than or equal to the value of its child nodes.

[0026] The initial capacity of the heap sort structure can be the maximum number of nodes, i.e., the upper limit of the number of heap tuples it can hold. The initial capacity of the heap sort structure can be greater than or equal to the number of tables to be joined, thus ensuring that during the initial sorting, one row of data from each table to be joined can be inserted into the heap sort structure as a heap tuple. In this embodiment, to save memory overhead, the initial capacity of the heap sort structure is configured to be equal to the number of tables to be joined.

[0027] In this embodiment, a heap sort structure that matches the sorting method of the tables to be joined can be selected to improve the efficiency of the heap sort structure in sorting the row data of the tables to be joined. For example, if the row data of each table to be joined is sorted in ascending order of the join key value, a min-heap can be used as the heap sort structure. This ensures that the top tuple of the heap sort structure always contains the minimum value. For example, if the row data of each table to be joined is sorted in descending order of the join key value, a max-heap can be used. This ensures that the top tuple of the heap sort structure always contains the maximum value.

[0028] The following explanation uses ascending order as the sorting method for the rows of the tables to be joined, and a min-heap as the heap sort structure.

[0029] First, the first row of data from each table to be joined is inserted into the heap sort structure as a heap tuple. The heap sort structure sorts the tuples in the heap according to the size of the join key value, so that the join key value of any heap tuple is less than or equal to the join key value of its child heap tuples.

[0030] Next, the top tuple of the heap is popped sequentially. Each time a top tuple is popped, the next row of the corresponding table to be joined is inserted into the heap sort structure. The heap sort structure adjusts and sorts the tuples within the heap, forming new top tuples. For each popped top tuple, its join key is obtained, and the row data corresponding to top tuples with the same join key is recorded in a candidate result subset. Since the row data of each table to be joined is sorted in ascending order, and the join key of the top tuple is always the minimum value, each popped top tuple is the one with the smallest join key among all the rows that have not yet been recorded. In other words, the heap sort structure in this embodiment can dynamically aggregate and sort the row data of each table to be joined, and output the row data in ascending order of join key value by sequentially popping the top tuple.

[0031] For example, a subset of candidate results can be stored in a temporary matching buffer using multiple lists (e.g., match[i] = [ ], i = 1, 2, ..., n), where row data from T1 can be stored in list match[1], row data from T2 can be stored in list match[2], and row data from T... n The rows of data can be stored in the list match[n].

[0032] Next, each candidate result subset is checked to see if it satisfies the multi-table join condition. If it does, the candidate result subset is processed into a target result subset of the multi-table join result; otherwise, it is discarded. In practice, one or more target result subsets can be determined after obtaining all candidate result subsets corresponding to the join key values. Alternatively, after obtaining a candidate result subset corresponding to a join key value, it can be immediately checked whether the candidate result subset satisfies the multi-table join condition. If it does, the target result subset is generated; otherwise, it is discarded.

[0033] This embodiment of the multi-table join processing method constructs a heap sort structure matching the n tables to be joined, and uses the heap sort structure to maintain the row data of each table to be joined in O(log n) time complexity. This achieves automatic aggregation and sorting of row data according to the join key value, obtaining a subset of candidate results corresponding to each join key value. By identifying those subsets that satisfy the multi-table join conditions, the multi-table join result is obtained. Compared to existing technologies that use pairwise merge join operators multiple times and use an optimizer to perform path calculations in O(n!) time complexity, this solution achieves efficient joins in a single operation, reducing memory and I / O consumption and significantly improving the execution efficiency of large-scale data and complex queries (such as data warehouse OLAP analysis). Furthermore, this solution eliminates the need for join order optimization, significantly shortening query planning time and reducing CPU and memory overhead. Especially in complex queries with a large number of tables to be joined (e.g., more than 10), it greatly improves the response speed of complex queries and enhances the real-time performance of the database.

[0034] In some embodiments of the multi-table join processing method of the present invention, such as Figure 2 As shown, a heap sort structure is used to sort the row data of multiple tables to be joined, and rows with the same join key value are grouped into candidate result subsets, including: S311, process each row of data from each table to be joined into a heap tuple and insert it into the heap sort structure; the heap sort structure inserts only one heap tuple at a time. S313, obtain the join key value of the top tuple of the heap sort structure, and use it as the current join value; S315, determine if the join key value of the top tuple of the heap is equal to the current join value; if yes, execute S317; otherwise, execute S319. S317, perform the pop operation of the top heap tuple, record the row data corresponding to the popped top heap tuple into the candidate result subset corresponding to the current join value, and return to execute S315; S319, if each table to be joined contains rows of data that have not yet been inserted into the heap sort structure, return to execute S313.

[0035] The following explanation will continue to use the example of sorting the rows of the tables to be joined in ascending order and using a min-heap structure for heap sort.

[0036] In this embodiment, inner and outer loops are used to traverse the row data of all tables to be joined. Specifically, after inserting the first row of each table into the heap sort structure, the join key value of the top tuple of the heap sort structure is obtained first, serving as the current join value, and this current join value is used as the basis for one round of the outer loop. Each round of the outer loop is used to find all rows that are equal to the current join value and obtain a subset of candidate results. If the heap sort structure is empty, or if the join key value of any top tuple is not equal to the current join value, one round of the outer loop ends and the next round begins. The entire outer loop ends when all rows of any table to be joined have been inserted into the heap sort structure.

[0037] The inner loop is used to traverse each row of data using a heap sort structure. Each round of the inner loop iterates through one row of data, and the concatenation key of the iterated row is the smallest of all the rows that have not yet been traversed (i.e., the top tuple of the heap), until the entire outer loop ends.

[0038] In this embodiment, the dynamic sorting characteristic of the heap sort structure is utilized to maintain the join key values ​​of all tables to be joined in a heap sort structure, with the join key value of the top tuple always being the smallest key value. By extracting the join key value of the top tuple, matching rows with the same key value, and generating the join result, multi-table joins are completed within a single operator (hereinafter referred to as the multi-table merge join operator), which can quickly locate matching rows and reduce data scanning and comparison overhead.

[0039] In some embodiments of the multi-table join processing method of the present invention, such as Figure 3 As shown, each row of data from the tables to be joined is processed into a heap tuple and inserted into a heap sort structure, including: S321, construct an iterator for each table to be joined, and the iterator retrieves one row of data from the corresponding table to be joined each time; S323, construct triples from the row data output by each iterator. The triples include the join key of the row data, the identifier of the table to be joined to which the row data belongs, and the data of the preset field of the row data. S325, each triple is inserted into the heap sort structure as a heap tuple, wherein at any time any list to be joined can insert at most one triple into the heap sort structure.

[0040] In this embodiment, an iterator is constructed for each table to be joined, to traverse all rows of data in the corresponding table using pointers. Specifically, the iterator is used to obtain the row data of the corresponding table to be joined and output the join key value and the data of preset fields of the row data (e.g., including fields other than the join key). For example, the output tuple (key) is used. j row j), where j is the row number. Each time the iterator is advanced, it outputs the next tuple (key) in the sorted order of the row data. j+1 row j+1 (This continues) until all rows of the corresponding table to be joined have been traversed by the iterator.

[0041] In this embodiment, the tuples output by the iterator can be constructed into, for example, triples (key). j table_id, row j ), where table_id is the identifier of the table to be joined to which the row data belongs. For example, the third row of data in table T2 to be joined can be represented as (key3, 2, row3) using a triplet.

[0042] By using iterators and constructing triples from the iterator outputs, and then using these triples as heap tuples, the join key value of each heap tuple can be easily obtained. j The identifier of the table to be joined (table_id), and the data of the preset fields of the row data (row). j .

[0043] In some embodiments of the multi-table join processing method of the present invention, such as Figure 4 As shown, the operation of popping the top tuple of the heap is performed, and the row data corresponding to the popped top tuple is recorded in the candidate result subset corresponding to the current join value, including: S331, record the data of the preset field of the row data of the top tuple of the heap to the candidate result subset corresponding to the current join value; S332, pop the top tuple from the heap; S333, advance the iterator of the list to be joined corresponding to the top tuple of the heap, and determine whether the iterator has a next row of data; if yes, execute S334, otherwise execute S345; S334: Construct the next row of data into a triplet and insert it as a heap tuple into the heap sort structure; S335, reduce the current capacity by 1; S336, the heap sort structure sorts the heap tuples.

[0044] In this embodiment, the multi-table merge join operator traverses the row data of each row through pointers to construct a triplet insertion push sort structure, and traverses the popped top tuple through pointers, and records the data of the traversed top tuple into the candidate result subset (e.g., match[i]=[ ], i=1,2,...,n). For example, for the traversed triple (key3,2,row3), row3 is stored in the list match[2].

[0045] In this embodiment, when all rows of a table to be joined have been traversed by the iterator, the current capacity of the heap sort structure is reduced by 1. This facilitates continuing to use the heap sort structure to iterate over the rows of other tables until the current outer loop is complete. Furthermore, it allows for easy determination of whether all rows of a table to be joined have been traversed by the iterator by checking if the current capacity of the heap sort structure equals its initial capacity.

[0046] In some embodiments of the multi-table join processing method of the present invention, when each table to be joined contains rows of data that have not yet been inserted into a heap sort structure, the step of returning to obtain the join key value of the top tuple of the heap sort structure as the current join value includes: Determine if the current capacity is less than the initial capacity; if not, return to the step of retrieving the join key value of the top tuple of the heap sort structure as the current join value; if yes, end the sorting process using the heap sort structure.

[0047] In this embodiment, when the current capacity is less than the initial capacity, it can be determined that at least one table to be joined has had all its row data traversed by the iterator. For multi-table equivalent merge joins, when any table to be joined is empty, there is no need to continue with the outer and inner loops. That is, there is no need to obtain the untraversed row data of other tables to be joined, nor is there a need to sort them, thereby saving computing resources.

[0048] In some embodiments of the multi-table join processing method of the present invention, such as Figure 5 As shown, from each subset of candidate results, those that satisfy the multi-table join condition are identified, and the multi-table join result is obtained, including: S411, each time the join key value of the top tuple of the heap is not equal to the current join value, determine whether the row data of each table to be joined exists in the candidate result subset corresponding to the current join value; if yes, execute S415, otherwise execute S417. S413, if all exist, then the candidate result subset satisfies the multi-table join condition; S415, obtain the Cartesian product of the candidate result subset and output it to the dataset that is the result of the multi-table join; execute S417; S417, Delete the subset of candidate results corresponding to the current connection value.

[0049] In this embodiment, if all lists (match[i], i=1, 2, ..., n) corresponding to the current join value are not empty (i.e., each list contains at least one row of data), it means that the join key value of the current outer loop has a match in all tables. Then, the Cartesian product of all lists corresponding to the current join value can be generated and output to the output buffer.

[0050] In this embodiment, during each round of the outer loop, all initialized lists (match[i], i=1, 2, ..., n) can be constructed in a temporary matching buffer. At the end of each round of the outer loop, it is determined whether all lists in the matching buffer are not empty. If they are not empty, they are output to the output buffer, and all lists in the matching buffer are cleared. Otherwise, all lists in the matching buffer are directly cleared.

[0051] In some embodiments of the multi-table join processing method of the present invention, such as Figure 6 As shown, the methods for handling multi-table joins include: S511, Initialize the data of each table to be joined; S512, Initialize the heap sort structure; S513, Initialize the matching buffer, which is set with a list (match[i], i=1, 2, ..., n) to store all subsets of candidate results; S514, insert the first row of each table to be joined into the heap sort structure; S515 records the join key value of the top tuple of the heap, which is used as the current join value (min_key). S516, pop the top tuple of the heap; S517, determine if the heap sort structure is empty; if yes, execute S531; otherwise, execute S521. S521, determine whether the join key value of the popped top tuple is equal to the current join value; if yes, execute S522; if no, execute S531. S522, insert the row data of the popped top tuple into the corresponding list in the matching buffer; S523, determine if the table to be joined corresponding to the popped top tuple has a next row of data; if yes, execute S524; otherwise, execute S525. S524, insert the next row of data into the heap sort structure; execute S526; S525, decrease the current capacity of the heap sort structure by 1; S526, sort the heap tuples in the heap sort structure; return to execute S516; S531, check all lists in the matching buffer and determine if all lists are not empty; if yes, execute S532; otherwise, execute S534. S532, generate the Cartesian product of all lists matching the buffer; S533 outputs the Cartesian product to the result set; S534, Clear all lists in the match buffer; S535: Determine if the current capacity of the heap sort structure is less than the number of tables to be joined; if not, execute S515; if yes, end the multi-table merge and join procedure.

[0052] In this embodiment, the steps between starting and ending the multi-table merge join procedure constitute the usage steps of the multi-table merge join operator. This embodiment replaces the complex optimizer logic and intermediate result processing of existing technologies with a single operator and dynamic join key-value management. It only requires maintaining a heap sort structure and an iterator for the tables to be joined, achieving equivalent multi-table merge joins. This not only significantly reduces query planning and execution overhead, improves the performance of large-scale data processing, optimizes the efficiency and resource utilization of the database system, and enhances the applicability and scalability of complex query scenarios, but also simplifies algorithm logic and data flow management, improves the maintainability and robustness of the database system, and provides an innovative solution for high-performance data processing.

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

[0054] It should be understood that in some embodiments, the components may be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods may be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

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

[0056] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, Field-Programmable Gate Arrays (FPGAs), or Programmable Logic Arrays (PLAs), may execute computer-readable program instructions to personalize the electronic circuits by utilizing state information from computer-readable program instructions.

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

[0058] For the purposes of this embodiment, the computer-readable storage medium 20 is a tangible device capable of holding and storing a computer program 11. It can be any device capable of containing, storing, communicating, propagating, or transmitting the computer program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

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

[0060] Computer device 30 may include a processor 32 adapted to execute stored instructions and a memory 31 that provides temporary storage space for the operation of said instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 31 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0061] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

[0062] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for processing multi-table joins in a database, characterized in that, include: Obtain multiple tables to be joined, wherein the tables to be joined include multiple rows of data sorted by the size of the join key value of each row; Construct a heap sort structure, the initial capacity of which is equal to the number of tables to be joined, and the heap sort structure uses the join key value as the sorting element. The heap sort structure is used to sort the row data of the multiple tables to be joined, and the row data with the same join key value are respectively formed into a subset of candidate results. In each subset of candidate results, those that satisfy the multi-table join condition are identified, and the multi-table join result is obtained.

2. The multi-table join processing method according to claim 1, characterized in that, The rows of data in the tables to be joined are sorted in ascending order of the join key values, and the heap sort structure includes a min-heap; or The rows of data in the table to be joined are sorted in descending order of the join key value, and the heap sort structure includes a max-heap.

3. The multi-table join processing method of claim 1, wherein, The step of sorting the row data of the multiple tables to be joined using the heap sort structure, and forming candidate result subsets from the row data with the same join key value, includes: Each row of data from each of the tables to be joined is processed into a heap tuple and inserted into the heap sort structure; and the heap sort structure inserts only one heap tuple at a time. Obtain the join key value of the top tuple of the heap sort structure as the current join value; Determine whether the join key value of the top tuple of the heap is equal to the current join value; If they are equal, then pop the top heap tuple, record the row data corresponding to the popped top heap tuple into the candidate result subset corresponding to the current join value, and return to the step of determining whether the join key value of the top heap tuple is equal to the current join value. If not equal, then based on the situation where the row data of each of the tables to be joined has not yet been inserted into the heap sort structure, return to the step of obtaining the join key value of the top tuple of the heap sort structure as the current join value.

4. The multi-table join processing method of claim 3, wherein, The step of processing each row of data from each of the tables to be joined into a heap tuple and inserting it into the heap sort structure includes: An iterator is constructed for each of the tables to be joined, and the iterator retrieves one row of data from the corresponding table at each time. The row data output by each iterator is constructed into a triple, which includes the join key value of the row data, the identifier of the table to be joined to which the row data belongs, and the data of the preset field of the row data; Each of the triples is inserted into the heap sort structure as a heap tuple, wherein at any given time, at most one of the triples in any of the lists to be joined is inserted into the heap sort structure.

5. The multi-table join processing method of claim 4, wherein, The step of popping the top tuple of the heap and recording the row data corresponding to the popped top tuple into the subset of candidate results corresponding to the current join value includes: Record the data of the preset field of the row data of the top tuple of the heap into the candidate result subset corresponding to the current join value; Remove the top tuple from the heap; Advance the iterator of the table to be joined corresponding to the top tuple of the heap, and determine whether the iterator has a next row of data; If it exists, the next row of data is constructed into the triplet and inserted into the heap sort structure as the heap tuple; If it does not exist, then decrease the current capacity by 1; The heap sort structure sorts each of the heap tuples.

6. The multi-table join processing method of claim 5, wherein, The step of returning to the step of obtaining the join key value of the top tuple of the heap sort structure as the current join value based on the situation that the rows of data in each of the tables to be joined have not yet been inserted into the heap sort structure includes: Determine whether the current capacity is less than the initial capacity; If not, return to the step of obtaining the join key value of the top tuple of the heap sort structure as the current join value.

7. The multi-table join processing method of claim 3, wherein, The step of identifying those that satisfy the multi-table join conditions from each of the candidate result subsets to obtain the multi-table join result includes: In each case where the join key value of the top tuple of the heap is not equal to the current join value, it is determined whether the row data of each table to be joined exists in the candidate result subset corresponding to the current join value; If all of them exist, then the subset of candidate results is determined to satisfy the multi-table join condition; Obtain the Cartesian product of the subset of candidate results and output it to the dataset that serves as the result of the multi-table join.

8. A computer-readable storage medium, characterized in that, It stores a computer program that, when executed by a processor, implements the steps of the database multi-table join processing method as described in any one of claims 1 to 7.

9. A computer program product comprising a computer program, characterized in that, When executed by a processor, the computer program implements the steps of the database multi-table join processing method as described in any one of claims 1 to 7.

10. A computer device, comprising: The system includes a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of the multi-table join processing method for the database as described in any one of claims 1 to 7.

Images