Optimization method for two-hop chain type connection

Abstract

The invention provides an optimization method for two-hop chain type connection. The optimization point of the method is that the scale of a search space is reduced, and the finally needed information amount is small. The specific optimization process is as follows: firstly, finding a part containing the chain connection according to a generated query graph, if the number of nodes in the chain connection is greater than or equal to three, checking two continuous nodes at the tail end in the chain connection, and if the two continuous nodes are not in a selection result finally, adjusting the sequence, so that the two continuous nodes at the tail end are firstly connected, and then the two continuous nodes at the tail end are not in the selection result. And an optimization operator h-join is introduced. The method is mainly used for database query optimization, especially for query of chain connection. By adjusting the connection sequence and preferentially processing the nodes of which the tail ends are not in the selection result, the scale of the search space can be effectively reduced, and the query execution efficiency is improved. The method is suitable for various relational database management systems, and the performance of complex query can be remarkably improved.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and particularly to an optimization method for two-hop chain connections. Background Art

[0002] Database technology is a core component of modern information systems and is widely used in various industries. Database technology provides an efficient data storage and management mechanism, capable of handling large amounts of structured and unstructured data, ensuring data integrity and consistency. At the same time, database technology supports multi-user concurrent access, allowing multiple users to operate and share data simultaneously, promoting collaboration and information sharing. In addition, database systems support complex query operations, allowing users to quickly retrieve and analyze data, thereby supporting decision-making and business operations. In database queries, the join operation is one of the most common and complex operations.

[0003] The join operation usually involves data matching and merging of multiple tables, with a large amount of calculation. Optimizing the join operation can significantly reduce query execution time and improve system performance. Optimizing the join operation can reduce memory and CPU usage, lower system resource consumption, and thus improve the overall efficiency and scalability of the system. For real-time applications and online services, fast response is crucial. Optimizing the join operation can shorten query response time and enhance the user experience. In complex queries, the join operation may involve multiple tables and complex join conditions. Optimizing the join operation can simplify the query plan, reduce the size of intermediate results, and improve query execution efficiency. Data analysis and business intelligence applications usually rely on complex join operations. Optimizing the join operation can accelerate the data analysis process and provide more timely and accurate decision support. Therefore, optimizing the join operation is an important part of database system optimization and directly affects the overall efficiency and user experience of the database system.

[0004] A two-hop chain connection refers to a query pattern in database queries where a target node is reached from a starting node through two join operations. This query pattern is very common in many practical applications. For example, in social networks, two-hop chain connections can be used to find a user's friends' friends, which can help discover potential social connections or recommend friends; in recommendation systems, two-hop chain connections can be used to make recommendations based on user behavior, query related products of the products a user has purchased, or related movies of the movies a user has watched; in graph databases or network analysis, two-hop chain connections can be used to find paths from one node to another, query transfer routes from one city to another. Two-hop chain connections are very common in many practical applications, especially in scenarios involving complex relationships and indirect connections, and can effectively discover and analyze indirect relationships in data, providing deeper insights and analysis capabilities.

[0005] Hash join is an efficient join algorithm, especially suitable for equi-joins. It constructs a hash table from the join columns of one table and then scans the other table and uses the hash table for matching. Hash join usually consists of two phases: the build phase and the probe phase. In the build phase, hash join constructs a hash table from the join columns of the smaller table (usually called the inner table, InnerTable). Each key-value pair is inserted into the hash table, where the key is the value of the join column and the value is the corresponding row. In the probe phase, hash join scans the larger table (usually called the outer table, OuterTable) and uses the hash table to find matching rows. For each row, hash join calculates the hash value of the join column and looks up the matching key-value pair in the hash table.

[0006] Hash join is very efficient in processing equi-joins because the lookup operation in the hash table has a constant time complexity, and it does not require sorting the data, which makes it perform well when dealing with unsorted data. Hash join can handle large-scale data sets. Especially when there is enough memory, the entire hash table can be loaded into memory for fast lookup, and it can be easily parallelized because the construction and lookup operations of the hash table can be distributed across multiple processors. This also makes hash join perform well in distributed database systems because hash partitioning can effectively disperse data and reduce the amount of data transmission.

[0007] However, hash join requires additional memory to store the hash table. If the data set is very large, it may cause out-of-memory problems. When out of memory, it may be necessary to partition the hash table, which will increase additional I / O overhead (Graefe, G. (1993). Query evaluation techniques for large databases. ACM Computing Surveys (CSUR), 25(2), 73 - 169.). And hash join may have hash conflicts when using the hash table, and additional mechanisms are needed to handle conflicts, which may affect performance. The handling of hash conflicts may lead to a decrease in the lookup efficiency of the hash table (Shatdal, A., & Naughton, J. F. (1995). Adaptive parallel aggregation algorithms. ACM SIGMOD Record, 24(2), 104 - 114.). In addition, the initial overhead of constructing the hash table is relatively large, especially when the inner table is very large, the time and space overhead of constructing the hash table are both high. Summary of the Invention

[0008] The object of the present invention is to provide an optimization method for two-hop chain-type joins in view of the deficiencies of the prior art.

[0009] The object of the present invention is achieved by the following technical solutions:

[0010] An optimization method for two-hop chain connections, comprising the following steps:

[0011] Step 1: According to the generated query graph, find the part containing the chain connection; if the number of nodes in the chain connection is greater than or equal to three, then proceed to Step 2;

[0012] Step 2: Check the two consecutive nodes at the end of the chain connection; if these two nodes are not finally in the selection result, that is, their connection is only used to reduce the search space and does not retain relevant data, it means that this chain connection meets the optimization conditions and proceed to Step 3;

[0013] Step 3: Adjust the optimization order of the eligible chain connection: Place the two consecutive nodes at the end in the front for connection operations, so as to reduce the search space in the early stage, thereby improving the query efficiency, and then proceed to Step 4;

[0014] Step 4: Use the optimization operator h-join for the chain connection with the adjusted order. The optimization operator h-join is used to optimize the chain connection and further improve the query performance through an efficient connection algorithm.

[0015] Furthermore, the chain connection refers to a chain-like structure formed by sequentially connecting multiple nodes through connection operations.

[0016] Furthermore, the optimization operator h-join reduces the number of mapping tables required during the two-hop connection process, thereby reducing the resource consumption of the connection.

[0017] Furthermore, Step 4 is implemented through the following sub-steps:

[0018] (4.1) For the three end nodes in the chain connection, according to the connection order, the three tables involved in the original query are sequentially transformed into three input tuple streams, namely left, mid, and right, starting from the node at the very end of the chain connection: left is the node at the very end of the chain connection, mid is the node in the middle of the chain connection, and right is the third node from the end of the chain connection; through this transformation, the complex chain connection is simplified into three input tuple streams, facilitating subsequent optimization operations;

[0019] (4.2) Replace the two consecutive connection operations that connect these three nodes in the original query with one optimization operator h-join operation to reduce the size and computational amount of the intermediate result;

[0020] (4.3) Create an empty multimap and iterate through each tuple in the mid tuple stream; according to the join condition, insert the key-value pairs of each tuple into the multimap; use a hash index to accelerate the insertion operation to ensure that the key-value pairs in the multimap can be quickly searched; where the multimap is a mapping table that allows duplicate keys.

[0021] (4.4) Iterate through each tuple in the left tuple stream and use the hash index to search for matching key-value pairs in the multimap; delete the non-matching key-value pairs and retain the matching ones to reduce the number of indexes in the multimap, thereby shrinking the size of the entire search space.

[0022] (4.5) Iterate through each tuple in the right tuple stream and use the hash index to search for matching key-value pairs in the multimap, and output the matching results as the final query results.

[0023] The present invention also provides an optimization device for two-hop chain connections, including one or more processors for implementing the above-mentioned optimization method for two-hop chain connections.

[0024] The present invention also provides an electronic device, including a memory and a processor, the memory being coupled to the processor; wherein, the memory is used to store program data, and the processor is used to execute the program data to implement the above-mentioned optimization method for two-hop chain connections.

[0025] The present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by the processor, it implements the above-mentioned optimization method for two-hop chain connections.

[0026] Compared with the prior art, the beneficial effects of the present invention at least include:

[0027] The optimization of two-hop chain-type connections in the present invention can significantly improve query performance by adjusting the connection order and using the optimization operator h-join. By preferentially processing the two consecutive nodes at the end, the size of intermediate results can be reduced at an early stage. This means that less data needs to be processed in subsequent join operations, thereby improving the overall query efficiency. The optimized connection order and the use of the h-join operator can reduce the computational complexity of join operations. Especially for large-scale data sets, reducing the computational complexity can significantly shorten the query execution time. By reducing the size of intermediate results, memory consumption can be reduced. This is particularly important for systems with limited memory, as it can avoid memory shortages and improve the stability and performance of the system. The optimized connection order can reduce the number of disk I / O operations. Since disk I / O is usually a bottleneck in database operations, reducing I / O operations can significantly improve the query response time. Through the above optimization effects, the overall query response time can be significantly shortened. This is particularly important for real-time queries and application scenarios that require quick responses. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Figure 1 is a flowchart for determining whether a query can be optimized in the method of the present invention;

[0029] Figure 2 is a structural diagram of the h-join operator in the present invention;

[0030] Figure 3 is a schematic structural diagram of the device of the present invention;

[0031] Figure 4 is a schematic diagram of an electronic device of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0032] Here, exemplary embodiments will be described in detail, and their examples are shown in the drawings. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present invention. On the contrary, they are merely examples of devices and methods consistent with some aspects of the present invention as detailed in the appended claims.

[0033] The terms used in the present invention are for the purpose of describing specific embodiments only and are not intended to limit the present invention. The singular forms "a", "the", and "said" used in the present invention and the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more of the associated listed items.

[0034] It should be understood that although the terms first, second, third, etc. may be used in the present invention to describe various information, such 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 the present invention, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein may be interpreted as "when" or "while" or "in response to a determination".

[0035] The present invention will be described in detail below with reference to the accompanying drawings.

[0036] See Figure 1 , the present invention provides an optimization method for two-hop chain connections, including the following steps:

[0037] Step 1: During the query processing or optimization process, the graphical representation constructed by the database management system (DBMS) for a specific query statement is a query graph, which is used to describe the relationships between the tables, conditions, joins, projections, and other operations involved in the query. According to the generated query graph, find the part containing the chain connection. A chain connection refers to a chain-like structure formed by sequentially connecting multiple nodes through join operations. If the number of nodes in the chain connection is greater than or equal to three, then proceed to Step 2;

[0038] Step 2: Check the two consecutive nodes at the end of the chain connection. If these two nodes are not finally in the selection result, that is, their connection is only used to reduce the search space and does not retain relevant data, it means that this chain connection meets the optimization conditions, and proceed to Step 3;

[0039] Step 3: Adjust the optimization order of the eligible chain connection. Specifically, place the two consecutive nodes at the end in the front for the join operation. This can reduce the search space at an early stage, thereby improving the query efficiency, and then proceed to Step 4;

[0040] Step 4: Design a specific optimization operator h-join for the chain connection with the adjusted order. This operator reduces the number of mapping tables required during the two-hop connection process, thereby reducing the resource consumption of this connection. See Figure 2, first use the insert operation to construct a multimap for connection purposes. The two nestedMap boxes represent nested tables introduced by successive lookup operations. In this nested table, obtain the list args (lookup key mapping linked list), then perform a scanList operation on this linked list to obtain the key-value relationships required for connection and store them (gather operation). After the gather operation on the right table is completed, output the final result. This is an operator specifically designed to optimize chain joins, which further improves query performance through an efficient join algorithm.

[0041] This step is the core of the present invention and is divided into the following sub-steps.

[0042] (4.1) For the last three nodes in the chain join, according to the connection order, the three tables involved in the original query are successively transformed into three input tuple streams: left, mid, and right, starting from the last node in the chain join. Left is the last node in the chain join, mid is the middle node in the chain join, and right is the third-to-last node in the chain join. Through this transformation, the complex chain join can be simplified into three input tuple streams, facilitating subsequent optimization operations;

[0043] (4.2) Replace the two consecutive operations that connect these three nodes in the original query with the optimized operator h-join to further improve query performance through an efficient join algorithm. Replace the two consecutive join operations with one h-join operation to reduce the size of intermediate results and the amount of computation;

[0044] (4.3) Create an empty multimap (a mapping table that allows duplicate keys), and traverse each tuple in the mid tuple stream. According to the join condition, insert the key-value pair of each tuple into the multimap. Use a hash index to accelerate the insertion operation to ensure that the key-value pairs in the multimap can be quickly searched;

[0045] (4.4) Traverse each tuple in the left tuple stream, and use the hash index to search for matching key-value pairs in the multimap. Delete the non-matching key-value pairs and retain the matching key-value pairs, thereby reducing the number of indexes in the multimap and greatly shrinking the size of the entire search space;

[0046] (4.5) Traverse each tuple in the right tuple stream, use the hash index to search for matching key-value pairs in the multimap, and output the matching results as the final query result.

[0047] Example 1

[0048]

[0049]

[0050] In this SQL query scenario, what needs to be found are the friends of friends of friends of a person with ID 15393162790150. The first JOIN operation finds the friends of the person with ID 15393162790150. Since there is only one ID as the key value, the search speed is very fast and there is no place that needs optimization.

[0051] However, when it comes to finding the friends of friends, that is, in the second and third JOIN operations, it becomes two many-to-many mapping relationships, making the query scenario very complex and the query speed very slow. Generally, when encountering two consecutive JOIN operations, a many-to-many mapping table of the knowsB and knowsC tables will be established for the first JOIN operation to find the key value of knowsC required for the second query. Then, a many-to-many mapping table of knowsC and knowsD will be established for the second JOIN operation, and finally the data of knowsD that needs to be found will be obtained.

[0052] The specific operator h-join designed by the present invention directly establishes a many-to-many mapping table for the knowsC table in the middle position between the two JOINs, filters out the key values that can be paired with knowsB, and then uses the filtered key values to find the data required in knowsD.

[0053] Corresponding to the embodiment of the optimization method for two-hop chain connection described above, the present invention also provides an embodiment of an optimization device for two-hop chain connection.

[0054] See Figure 3 , the optimization device for two-hop chain connection provided by the embodiment of the present invention includes one or more processors for implementing the optimization method for two-hop chain connection in the above embodiment.

[0055] The embodiment of the optimization device for two-hop chain connection of the present invention can be applied to any device with data processing capabilities, and the any device with data processing capabilities can be a device or apparatus such as a computer. The device embodiment can be implemented by software, or by hardware or a combination of software and hardware. Taking software implementation as an example, as a logically meaningful device, it is formed by the processor of any device with data processing capabilities reading the corresponding computer program instructions in the non-volatile memory into the memory and running. From the hardware level, such as Figure 3As shown in the figure, it is a hardware structure diagram of any device with data processing capabilities where the optimization device for two-hop chain connections according to the present invention is located. In addition to Figure 3 the shown processor, memory, network interface, and non-volatile memory, any device with data processing capabilities where the device in the embodiment is located usually may further include other hardware according to the actual functions of the any device with data processing capabilities, which will not be elaborated herein.

[0056] For the implementation processes of the functions and roles of each unit in the above device, please refer to the implementation processes of the corresponding steps in the above method for details, which will not be elaborated herein.

[0057] For the device embodiment, since it basically corresponds to the method embodiment, the relevant parts can be referred to the partial description of the method embodiment. The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed to multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of the present invention. Those of ordinary skill in the art can understand and implement it without creative efforts.

[0058] Corresponding to the embodiment of the optimization method for two-hop chain connections described above, an embodiment of the present application further provides an electronic device, including: one or more processors; a memory for storing one or more programs; when the one or more programs are executed by the one or more processors, the one or more processors implement the optimization method for two-hop chain connections as described above. As Figure 4 shown in the figure, it is a hardware structure diagram of any device with data processing capabilities where the optimization method for two-hop chain connections provided by the embodiment of the present application is located. In addition to Figure 4 the shown processor, memory, DMA controller, disk, and non-volatile memory, any device with data processing capabilities where the device in the embodiment is located usually may further include other hardware according to the actual functions of the any device with data processing capabilities, which will not be elaborated herein.

[0059] Corresponding to the embodiment of the optimization method for two-hop chain connections described above, an embodiment of the present invention further provides a computer-readable storage medium, on which a program is stored, and when the program is executed by a processor, the optimization method for two-hop chain connections in the above embodiment is implemented.

[0060] The computer-readable storage medium may be an internal storage unit of any data processing-capable device described in any of the foregoing embodiments, such as a hard disk or a memory. The computer-readable storage medium may also be any data processing-capable device, such as a plug-in hard disk, a Smart Media Card (SMC), an SD card, a Flash Card, etc. equipped on the device. Further, the computer-readable storage medium may also include both an internal storage unit of any data processing-capable device and an external storage device. The computer-readable storage medium is used to store the computer program and other programs and data required by any data processing-capable device, and may also be used to temporarily store the data that has been output or is to be output.

[0061] The above are only the preferred embodiments of the present invention and are not intended to limit the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

[0062] The above embodiments are only used to illustrate the design concept and features of the present invention, and their purpose is to enable those skilled in the art to understand the content of the present invention and implement it accordingly. The protection scope of the present invention is not limited to the above embodiments. Therefore, any equivalent changes or modifications made based on the principles and design concepts disclosed by the present invention are within the protection scope of the present invention.

Claims

1. An optimization method for two-hop chain connections, characterized in that, It includes the following steps: Step 1: According to the generated query graph, find the part containing chain - type connections; if the number of nodes in the chain - type connection is greater than or equal to three, go to Step 2; Step 2: Check the two consecutive nodes at the end of the chain - type connection; if these two nodes are not finally in the selection result, that is, their connection is only used to reduce the search space without retaining relevant data, it means that this chain - type connection meets the optimization conditions and go to Step 3; Step 3: Adjust the optimization order of the eligible chain - type connection: place the two consecutive nodes at the end at the front for connection operations, so as to reduce the search space at an early stage, thereby improving the query efficiency, and then go to Step 4; Step 4: Use the optimization operator h - join for the chain - type connection with adjusted order. The optimization operator h - join is used to optimize the chain - type connection and further improve the query performance through an efficient connection algorithm.

2. The optimization method for two-hop chain connection according to claim 1, characterized in that, The chain - type connection refers to a chain - like structure formed by sequentially connecting multiple nodes through connection operations.

3. The optimization method for two-hop chain connection according to claim 1, characterized in that The optimization operator h - join reduces the number of mapping tables required in the two - hop connection process, thereby reducing the resource consumption of the connection.

4. The optimization method for two-hop chain connection according to claim 1, characterized in that Step 4 is implemented through the following sub - steps: (4.1) For the three end nodes in the chain - type connection, convert the three tables involved in the original query into three input tuple streams of left, mid, and right according to the connection order, starting from the node at the very end of the chain - type connection. Left is the node at the very end of the chain - type connection, mid is the middle node of the chain - type connection, and right is the third - last node of the chain - type connection. Through this conversion, the complex chain - type connection is simplified into three input tuple streams, facilitating subsequent optimization operations; (4.2) Replace the two consecutive connection operations that connect these three nodes in the original query with one optimization operator h - join operation to reduce the size and computational amount of the intermediate result; (4.3) Create an empty multimap, and traverse each tuple in the mid tuple stream; according to the connection condition, insert the key - value pair of each tuple into the multimap; use a hash index to accelerate the insertion operation to ensure that the key - value pairs in the multimap can be quickly searched; where multimap is a mapping table that allows duplicate keys; (4.4) Traverse each tuple in the left tuple stream, and use the hash index to search for matching key - value pairs in the multimap; delete the non - matching key - value pairs and retain the matching key - value pairs to reduce the number of indexes in the multimap, thereby shrinking the size of the entire search space; (4.5) Traverse each tuple in the right tuple stream, use the hash index to search for matching key - value pairs in the multimap, and output the matching results as the final query result.

5. An optimization device for two-hop chain connection, characterized in that, It includes one or more processors for implementing the optimization method for two - hop chain - type connections described in any one of claims 1 - 4.

6. An electronic device, comprising a memory and a processor, characterized in that, The memory is coupled to the processor; wherein, the memory is used for storing program data, and the processor is used for executing the program data to implement the optimization method for two-hop chain connection according to any one of claims 1-4 above.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the optimization method for two-hop chain connection according to any one of claims 1-4.

Images