A hash join parallel query method and system based on the openGauss database

Abstract

This invention relates to the field of database query technology, providing a method and system for parallel hash join queries based on the OpenGauss database. The method includes: determining whether a shared hash table can store all inner table data; creating initialization conditions for the construction of the shared hash table and the joining of inner and outer tuples; when the shared hash table can store all inner table data, multi-threaded parallel scanning of the inner tables is performed, with a barrier structure controlling the construction of the shared hash table; after the shared hash table construction is completed, parallel scanning of the outer table is performed, and tuples from the inner and outer tables are joined; when the shared hash table cannot store all inner table data, the shared hash table is constructed in batches; after the current batch of shared hash table construction is completed, parallel scanning of the outer table is performed; after joining the current batch of tuples from the inner and outer tables, the next batch of shared hash tables is constructed, and the next batch of tuple joins from the inner and outer tables are executed. This method and system can improve CPU utilization and increase the efficiency of hash join queries.

Description

Technical Field

[0001] This invention relates to the field of database query technology, and in particular to a hashjoin parallel query method and system based on the openGauss database. Background Technology

[0002] Hash join is an algorithm in databases that uses hash tables to join data from two or more tables. The openGauss database implements the hash join algorithm by first scanning the inner table and building a hash table in memory. If the input for the entire construction is less than the available memory, all tuples can be inserted into the hash table. If the input for the entire construction is greater than the available memory, tuples are written to a temporary file in batches. Then, the outer table is scanned, and the inner and outer table tuples are joined, returning tuples that meet the conditions. Because hash tables have high lookup efficiency, hash join is relatively efficient. However, hash tables need to be built in memory, which places relatively high demands on memory size. This join algorithm is suitable for joining small and large tables where the result set has a large number of records.

[0003] In the OpenGauss database, hash joins involve serial scanning of inner and outer tables, resulting in low CPU utilization and slow efficiency. In multi-table join queries, if the tables have large amounts of data, the serial scanning of inner and outer tables, hash table construction, and joining of inner and outer table tuples prevents efficient use of CPU resources, leading to performance bottlenecks. If each thread builds its own hash table, the data in the hash table will be duplicated, wasting memory. If each thread shares a hash table, managing the construction of this shared hash table becomes a significant challenge.

[0004] Therefore, how to provide a more efficient hash join parallel query method has become an urgent technical problem to be solved. Summary of the Invention

[0005] In view of this, in order to overcome the shortcomings of the prior art, the present invention aims to provide a hash join parallel query method and system based on the openGauss database.

[0006] According to a first aspect of the present invention, a parallel query method for hash join based on the openGauss database is provided, comprising:

[0007] Obtain the memory parameters and hash bucket parameters of the openGauss database, and determine whether the shared hash table can store all internal table data based on the obtained openGauss database memory parameters and hash bucket parameters;

[0008] By constructing a barrier structure, allocating an empty hash table, and obtaining the hash calculation functions corresponding to the outer table join field and the inner table join field in the join condition, initialization conditions are created for the construction of the shared hash table and the connection of inner and outer tuples.

[0009] When the shared hash table can store all the data in the inner table, multiple threads scan the inner table in parallel. The construction of the shared hash table is controlled by a barrier structure. After the construction of the shared hash table is completed, the outer table is scanned in parallel, and the tuples of the inner table and the outer table are joined.

[0010] When the shared hash table cannot store all the data in the inner table, the shared hash table is built in batches. After the construction of the current batch of the shared hash table is completed, the outer table is scanned in parallel. After joining the tuples of the current batch in the inner table and the outer table, the next batch of the shared hash table is built and the next batch of tuple joins in the inner table and the outer table are performed.

[0011] Preferably, in the parallel query method for hash join based on the openGauss database of the present invention, the openGauss database memory parameters and hash bucket parameters are obtained, and the shared hash table is determined to be able to store all internal table data based on the obtained openGauss database memory parameters and hash bucket parameters, including:

[0012] Get the size of the internal table, the maximum memory usage for the query, the number of tuples in the internal table, and the size of the hash bucket header structure.

[0013] The number of hash buckets is determined based on the maximum memory usage of the query job, the number of internal table tuples, and the size of the hash bucket header structure.

[0014] Multiply the number of hash buckets by the size of the hash bucket header structure, sum the product with the size of the internal table, and compare the sum with the maximum memory required for the query.

[0015] If the summation value is not greater than the maximum memory value of the query operation, it is determined that the shared hash table can store all internal table data.

[0016] If the summation value is greater than the maximum memory limit for the query operation, it is determined that the shared hash table cannot store all internal table data.

[0017] Preferably, in the hash join parallel query method based on the openGauss database of the present invention, by constructing a barrier structure, allocating an empty hash table, and obtaining the hash calculation function corresponding to the outer table join field and the hash calculation function corresponding to the inner table join field in the join condition, initialization conditions are created for the construction of the shared hash table and the connection of inner and outer tuples. This includes: constructing a barrier structure for synchronizing the shared hash table process, wherein the barrier structure includes a phase counter and a lock for protecting the members of the barrier structure, and the phase counter uses different count values ​​to indicate different construction stages of the shared hash table.

[0018] Preferably, in the parallel query method for hash join based on the openGauss database of the present invention, by constructing a barrier structure, allocating an empty hash table, and obtaining the hash calculation functions corresponding to the outer table join field and the inner table join field in the join conditions, initialization conditions are created for the construction of the shared hash table and the inner and outer tuple join, including:

[0019] Start multiple worker threads, and use the first thread to enter the execution logic to lock the barrier structure members, update the count value of the phase counter in the barrier structure to 1, and release the lock protecting the barrier structure members.

[0020] The first thread to enter the execution logic requests memory for a shared hash table from shared memory, sets each hash bucket to empty, and obtains an empty shared hash table;

[0021] The first thread to enter the execution logic locks the barrier structure members, updates the phase counter in the barrier structure to 2, releases the locks protecting the barrier structure members, and wakes up other threads among the multiple worker threads.

[0022] Preferably, in the parallel query method for hash join based on the openGauss database of the present invention, by constructing a barrier structure, allocating an empty hash table, and obtaining the hash calculation functions corresponding to the outer table join field and the inner table join field in the join conditions, initialization conditions are created for the construction of the shared hash table and the inner and outer tuple join, including:

[0023] Based on the operator of the join condition, the data type of the left parameter of the join condition, and the data type of the right parameter of the join condition, query the unique identifier of the operator of the join condition in the database from pg_operator;

[0024] Based on the unique identifier of the operator in the database for the queried join condition, retrieve the corresponding index family, index access method and operator strategy from pg_amop;

[0025] If the index access method found is a hash access method, and the operator strategy is an equal hash index strategy, the hash calculation function corresponding to the outer table join field is obtained from pg_amproc based on the index family and the data type of the left parameter of the join condition; the hash calculation function corresponding to the inner table join field is obtained from pg_amproc based on the index family and the data type of the right parameter of the join condition.

[0026] If the corresponding hash access method is not registered in pg_amop, an error will occur and the application will exit.

[0027] Preferably, in the hash join parallel query method based on the openGauss database of the present invention, when the shared hash table can store all internal table data, multiple threads scan the internal tables in parallel, and the construction of the shared hash table is controlled by a barrier structure, including:

[0028] When the shared hash table is able to store all internal table data, the construction phase of the shared hash table is obtained through the barrier structure;

[0029] When the phase counter in the barrier structure has a count value of 2, the inner table is scanned in parallel using multiple threads. The value of the join field is obtained from the tuple of the inner table. The hash calculation function corresponding to the join field of the inner table is used to calculate the hash value of the join field based on the value of the join field. The corresponding hash bucket number is calculated based on the hash value of the join field. The hash value of the join field and the corresponding tuple are stored in the hash bucket of the corresponding hash bucket number.

[0030] After all threads have finished scanning the inner table, the last thread to finish scanning the inner table acquires a lock on the barrier structure to protect the barrier structure members, updates the phase counter value in the barrier structure to 3, releases the lock protecting the barrier structure members, and completes the construction of the shared hash table.

[0031] Preferably, in the hash join parallel query method based on the openGauss database of the present invention, after the shared hash table is constructed, the outer table is scanned in parallel, and the tuples of the inner table and the outer table are joined, including:

[0032] Multi-threaded parallel scanning of the external table is used to obtain the value of the join field from the tuple of the external table. The hash value of the join field is calculated based on the value of the join field using the hash calculation function corresponding to the join field of the external table. The corresponding hash bucket number is then calculated based on the hash value of the join field.

[0033] Scan the tuples in the corresponding hash bucket of the shared hash table, and find the inner table tuple whose hash value is equal to the value of the current outer table join field. If a tuple with the same hash value is found and the inner and outer table join fields meet the join conditions, the tuple is returned. Otherwise, continue to get the next tuple in the shared hash bucket for comparison. If none of the tuples in the shared hash bucket meet the conditions, continue scanning the outer table. After the outer table scan is completed, stop all threads.

[0034] Preferably, in the parallel hash join query method based on the openGauss database of the present invention, when the shared hash table cannot store all the data of the inner table, the shared hash table is constructed in batches. After the construction of the current batch of the shared hash table is completed, the outer table is scanned in parallel. After joining the tuples of the current batch of the inner table and the outer table, the next batch of the shared hash table is constructed and the next batch of tuple joins of the inner table and the outer table are performed, including:

[0035] The internal table is scanned in parallel using multiple threads. The values ​​of the join fields are obtained from the tuples in the internal table. The hash calculation function corresponding to the join field of the internal table is used to calculate the hash value of the join field based on the value of the join field. The corresponding hash bucket number and hash value batch number are calculated based on the hash value of the join field.

[0036] A shared hash table is built based on the batch number of the hash value. When building a shared hash table for the tuple of the current batch number, the hash value of the corresponding join field and the corresponding tuple are stored in the hash bucket of the corresponding hash bucket number. Tuples of other batches that are scanned are written to the internal table tuple file of the corresponding batch number.

[0037] Multi-threaded parallel scanning of the external table is used to obtain the value of the join field from the tuple of the external table. The hash calculation function corresponding to the join field of the external table is used to calculate the hash value of the join field based on the value of the join field. The corresponding hash bucket number and hash value batch number are calculated based on the hash value of the join field. The tuple is written to the external table tuple file with the corresponding batch number.

[0038] Scan the tuples in the corresponding hash bucket of the shared hash table, and find the inner table tuple whose hash value is equal to the value of the current outer table join field. If a tuple with an equal hash value is found and the inner and outer table join fields meet the join conditions, the tuple is returned. Otherwise, continue to get the next tuple in the shared hash bucket for comparison. If none of the tuples in the shared hash bucket meet the conditions, continue scanning the outer table until the current batch of tuple joins between the inner and outer tables is completed. Then, build the next batch of shared hash tables and perform the next batch of tuple joins between the inner and outer tables.

[0039] Preferably, in the hash join parallel query method based on the openGauss database of the present invention, after joining the current batch of tuples of the inner table and the outer table, constructing the next batch of shared hash table and performing the next batch of tuple join of the inner table and the outer table includes: after joining all batches of tuples, stopping all threads and cleaning up the inner table tuple file and the outer table tuple file.

[0040] According to a second aspect of the present invention, a hash based on the openGauss database is provided. The join parallel query system includes a parallel query server. This server is used to: obtain the memory parameters and hash bucket parameters of the OpenGauss database; determine whether the shared hash table can store all inner table data based on these parameters; create initialization conditions for the shared hash table construction and inner / outer table tuple joins by constructing a barrier structure, allocating an empty hash table, and obtaining the hash calculation functions corresponding to the outer and inner table join fields in the join conditions; when the shared hash table can store all inner table data, multiple threads scan the inner tables in parallel, controlling the construction of the shared hash table through the barrier structure; after the shared hash table construction is complete, the outer table is scanned in parallel, and tuples from the inner and outer tables are joined; when the shared hash table cannot store all inner table data, the shared hash table is constructed in batches; after the current batch of shared hash table construction is completed, the outer table is scanned in parallel; after the current batch of tuples from the inner and outer tables is joined, the next batch of shared hash tables is constructed, and the next batch of tuple joins from the inner and outer tables are executed.

[0041] According to a third aspect of the present invention, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method described in the first aspect of the present invention.

[0042] This invention presents a parallel hash join query method and system based on the OpenGauss database. It employs multi-threaded parallel scanning of the inner table and uses a barrier structure to track and manage the construction of the shared hash table. All threads share the barrier's state; when all threads reach a barrier stage, all waiting threads are awakened to continue to the next stage until the shared hash table is fully constructed. Then, the outer table is scanned in parallel, the join is performed, and tuples matching the join conditions are returned. This method and system utilize multi-threaded collaborative execution compared to serial execution, fully utilizing CPU resources. By sharing a hash table among multiple threads and using a barrier structure to track and manage the construction of the shared hash table, memory usage is reduced, and hash join query efficiency is improved. Attached Figure Description

[0043] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0044] Figure 1 This is a schematic diagram of a system for a hash join parallel query method based on the openGauss database applicable to embodiments of the present invention;

[0045] Figure 2 This is a flowchart illustrating the steps of a hash join parallel query method based on the openGauss database according to an embodiment of the present invention.

[0046] Figure 3 This is a flowchart illustrating a process for constructing a shared hash table and creating initialization conditions for joining inner and outer bytes according to the method of this embodiment.

[0047] Figure 4 This is a flowchart illustrating a method for constructing a shared hash table according to this embodiment.

[0048] Figure 5 This is a flowchart illustrating a method for parallel scanning of external surfaces and connection tuples according to an embodiment of the present invention.

[0049] Figure 6 This is a schematic diagram of the structure of the device provided by the present invention. Detailed Implementation

[0050] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0051] It should be noted that, in the absence of conflict, the following embodiments and features can be combined with each other; and, based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0052] It should be noted that various aspects of embodiments within the scope of the appended claims are described below. It will be apparent that the aspects described herein can be embodied in a wide variety of forms, and any particular structure and / or function described herein is merely illustrative. Based on this disclosure, those skilled in the art will understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects set forth herein can be used to implement the device and / or practice the method. Additionally, this device and / or method can be implemented using structures and / or functionalities other than one or more of the aspects set forth herein.

[0053] Figure 1 An exemplary system for a hash join parallel query method based on the openGauss database, applicable to embodiments of the present invention, is shown. For example... Figure 1 As shown, the system may include a parallel query server 101, a communication network 102, and / or one or more parallel query clients 103. Figure 1 The example in the text is 103, which represents multiple parallel query clients.

[0054] The parallel query server 101 can be any suitable server used to store information, data, programs, and / or any other suitable type of content. In some embodiments, the parallel query server 101 can perform appropriate functions. For example, in some embodiments, the parallel query server 101 can be used to perform hash join parallel queries in the openGauss database. As an optional example, in some embodiments, the parallel query server 101 can be used to obtain the openGauss database memory parameters and hash bucket parameters, and determine whether the shared hash table can store all internal table data based on the obtained openGauss database memory parameters and hash bucket parameters; by constructing a barrier structure, allocating an empty hash table, and obtaining the hash calculation function corresponding to the outer table join field in the join condition and the hash calculation function corresponding to the inner table join field in the join condition, initialization conditions are created for the construction of the shared hash table and the connection of inner and outer table tuples; when the shared hash table can store all internal table data, multiple threads scan the internal tables in parallel, and the construction of the shared hash table is controlled by the barrier structure. After the construction of the shared hash table is completed, the outer table is scanned in parallel, and the tuples of the inner table and the outer table are joined; when the shared hash table cannot store all internal table data, the shared hash table is constructed in batches. After the construction of the current batch of the shared hash table is completed, the outer table is scanned in parallel. After the current batch of tuples of the inner table and the outer table is joined, the next batch of the shared hash table is constructed and the next batch of tuples of the inner table and the outer table is joined.

[0055] As another example, in some embodiments, the parallel query server 101 may send the hash join parallel query method based on the openGauss database to the parallel query client 103 for user use, according to the request of the parallel query client 103.

[0056] As an optional example, in some embodiments, the parallel query client 103 is used to provide a visual query interface for receiving a user's selection input operation for performing a hash join parallel query in the openGauss database, and for retrieving and displaying the query interface corresponding to the option selected by the selection input operation from the parallel query server 101 in response to the selection input operation. The query interface displays at least information about performing a hash join parallel query in the openGauss database and operation options for the information about performing a hash join parallel query in the openGauss database.

[0057] In some embodiments, communication network 102 can be any suitable combination of one or more wired and / or wireless networks. For example, communication network 102 can include any one or more of the following: the Internet, intranet, wide area network (WAN), local area network (LAN), wireless network, digital subscriber line (DSL) network, frame relay network, asynchronous transfer mode (ATM) network, virtual private network (VPN), and / or any other suitable communication network. Parallel query client 103 can connect to communication network 102 via one or more communication links (e.g., communication link 104), which can be linked to parallel query server 101 via one or more communication links (e.g., communication link 105). Communication links can be any communication link suitable for transmitting data between parallel query client 103 and parallel query server 101, such as network links, dial-up links, wireless links, hardwired links, any other suitable communication links, or any suitable combination of such links.

[0058] The parallel query client 103 may include any one or more clients that present an interface related to high-speed access to ORC external tables in a suitable manner for user use and operation. In some embodiments, the parallel query client 103 may include any suitable type of device. For example, in some embodiments, the parallel query client 103 may include a mobile device, tablet computer, laptop computer, desktop computer, and / or any other suitable type of client device.

[0059] Although the parallel query server 101 is illustrated as a single device, in some embodiments, any suitable number of devices can be used to perform the functions performed by the parallel query server 101. For example, in some embodiments, multiple devices can be used to implement the functions performed by the parallel query server 101. Alternatively, cloud services can be used to implement the functions of the parallel query server 101.

[0060] Based on the above system, this invention provides a hash join parallel query method based on the openGauss database. Hash join is an algorithm that uses a hash table to associate data from two or more tables in a database. The following examples illustrate this method.

[0061] Figure 2 This is a flowchart illustrating the steps of a hash join parallel query method based on the openGauss database according to an embodiment of the present invention. This hash join parallel query method based on the openGauss database can be executed on a parallel query server. Figure 2 As shown, this hash join parallel query method based on the openGauss database includes the following steps:

[0062] In the method of this invention embodiment, it is first necessary to obtain the openGauss database memory parameters and hash bucket parameters, and then determine whether the shared hash table can store all internal table data based on the obtained openGauss database memory parameters and hash bucket parameters.

[0063] As an optional example, this embodiment's method requires obtaining the internal table size, the maximum memory size for the query operation, the number of tuples in the internal table, and the hash bucket header structure size. In this embodiment's method, the maximum memory size for the query operation is a parameter of the openGauss database, which sets the memory size used by the internal sorting operation and the hash table before it begins writing to the temporary disk file; the hash bucket is used to store multiple tuples, and the hash bucket header is used to store a pointer to the next tuple and the hash value of the tuple.

[0064] This embodiment determines the number of hash buckets based on the maximum memory size of the query job, the number of internal table tuples, and the size of the hash bucket header structure. For example, this embodiment divides the maximum memory size of the query job by the size of the hash bucket header structure, using the result as the first hash bucket count and the number of internal table tuples as the second hash bucket count; the minimum of the first and second hash bucket counts is then taken as the total hash bucket count. Those skilled in the art can choose other methods to obtain the number of hash buckets depending on the application scenario when implementing this embodiment, and this embodiment does not impose any limitations on this.

[0065] After determining the number of hash buckets, the method in this embodiment multiplies the number of hash buckets by the size of the hash bucket header structure, sums the product with the size of the internal table, and compares the sum with the maximum memory value of the query operation. If the sum is not greater than the maximum memory value of the query operation, it is determined that the shared hash table can store all internal table data; if the sum is greater than the maximum memory value of the query operation, it is determined that the shared hash table cannot store all internal table data.

[0066] After determining whether the shared hash table can store all internal table data, the method in this embodiment needs to construct a barrier structure, allocate an empty hash table, and obtain the hash calculation function corresponding to the outer table join field in the join condition and the hash calculation function corresponding to the inner table join field in the join condition to create initialization conditions for the construction of the shared hash table and the connection of inner and outer tuples.

[0067] As an optional example, the method in this embodiment requires constructing a barrier structure for synchronizing the shared hash table process. This barrier structure includes a phase counter and locks for protecting the barrier structure members. The phase counter uses different count values ​​to indicate different construction stages of the shared hash table. For example, the phase counter count values ​​are set to indicate different construction stages of the shared hash table in the following manner:

[0068] 0: Initial state;

[0069] 1: Allocate an empty shared hash table from shared memory;

[0070] 2: Scan the internal table to build a shared hash table;

[0071] 3: Once the shared hash table is built, perform a tuple data join between the inner and outer tables.

[0072] It should be noted that when implementing the method of the present invention, those skilled in the art can also use other settings to indicate the construction stage of the shared hash table according to the actual application scenario, and this embodiment does not limit this.

[0073] After constructing the barrier structure, the method in this embodiment needs to allocate an empty hash table from shared memory. As an optional example, in this embodiment, multiple worker threads are started. The first thread to enter the execution logic acquires a lock to protect the barrier structure members, updates the phase counter value in the barrier structure to 1, and releases the lock to protect the barrier structure members. The first thread to enter the execution logic requests memory for the shared hash table from shared memory, sets each hash bucket to empty, and obtains an empty shared hash table. The first thread to enter the execution logic acquires a lock to protect the barrier structure members, updates the phase counter value in the barrier structure to 2, releases the lock to protect the barrier structure members, and wakes up the other threads among the multiple worker threads.

[0074] After completing the allocation of an empty hash table, the method in this embodiment needs to obtain the hash calculation function corresponding to the outer table join field in the join condition and the hash calculation function corresponding to the inner table join field in the join condition, and further create initialization conditions for the construction of the shared hash table and the connection of inner and outer tuples.

[0075] Figure 3 This is a flowchart illustrating a process for constructing a shared hash table and creating initialization conditions for joining inner and outer bytes according to the method of this embodiment. As an optional example, such as... Figure 3 As shown, the method in this embodiment queries the `pg_operator` for the unique identifier of the operator in the database based on the operator of the join condition, the data type of the left parameter of the join condition, and the data type of the right parameter of the join condition. Based on the unique identifier of the operator in the database, it queries the corresponding index family, index access method, and operator strategy from `pg_amop`. If the queried index access method is a hash access method and the operator strategy is an equal hash index strategy, it retrieves the hash calculation function corresponding to the outer table join field from `pg_amproc` based on the index family and the data type of the left parameter of the join condition. It also retrieves the hash calculation function corresponding to the inner table join field from `pg_amproc` based on the index family and the data type of the right parameter of the join condition. If no corresponding hash access method is registered in `pg_amop`, the method exits with an error. In this embodiment, pg_operator is an operator data dictionary that stores the operator's unique identifier, name, left parameter data type, right parameter data type, etc.; pg_amop is an operator access method data dictionary that stores the access methods for database operators; and pg_amproc is an operator access method procedure data dictionary that stores the access method procedures for database operators.

[0076] In this embodiment, when the shared hash table can store all the data in the inner table, multiple threads scan the inner table in parallel. The construction of the shared hash table is controlled by a barrier structure. After the construction of the shared hash table is completed, the outer table is scanned in parallel, and the tuples of the inner table and the outer table are joined.

[0077] Figure 4 This is a flowchart illustrating a method for constructing a shared hash table according to this embodiment. As an optional example, such as... Figure 4 As shown, when the shared hash table can store all internal table data, the construction phase of the shared hash table is obtained through a barrier structure. When the phase counter in the barrier structure is 2, multi-threaded parallel scanning of the internal table is used to obtain the value of the join field from the tuples of the internal table. The hash calculation function corresponding to the join field of the internal table is used to calculate the hash value of the join field based on the value of the join field. The corresponding hash bucket number is calculated based on the hash value of the join field. The hash value of the join field and the corresponding tuple are stored in the hash bucket of the corresponding hash bucket number. In this embodiment, the hash bucket number is equal to the remainder when the hash value is divided by the number of hash buckets.

[0078] After all threads have finished scanning the inner table, the last thread to finish scanning the inner table acquires a lock on the barrier structure to protect the barrier structure members, updates the phase counter value in the barrier structure to 3, releases the lock protecting the barrier structure members, and completes the construction of the shared hash table.

[0079] Figure 5 This is a flowchart illustrating a method for parallel scanning of external surfaces and connection tuples according to an embodiment of the present invention, such as... Figure 5 As shown, after the shared hash table is constructed, this embodiment uses multi-threaded parallel scanning of the outer table. It retrieves the value of the join field from the tuples of the outer table, calculates the hash value of the join field based on its value using the hash calculation function corresponding to the join field, and calculates the corresponding hash bucket number based on the hash value of the join field. It then scans the tuples of the corresponding hash buckets in the shared hash table, searching for an inner table tuple whose hash value is equal to the current outer table join field value. If a tuple with an equal hash value is found and the inner and outer table join fields meet the join condition, the tuple is returned. Otherwise, it continues to retrieve the next tuple in the shared hash bucket for comparison. If none of the tuples in the shared hash bucket meet the condition, it continues scanning the outer table. After the outer table scanning is complete, all threads are stopped.

[0080] In this embodiment, when the shared hash table cannot store all the data in the inner table, the shared hash table is constructed in batches. After the construction of the current batch of the shared hash table is completed, the outer table is scanned in parallel. After joining the tuples of the current batch in the inner table and the outer table, the next batch of the shared hash table is constructed and the next batch of tuple joins in the inner table and the outer table are performed.

[0081] As an optional example, the method of this embodiment employs multi-threaded parallel scanning of the internal table, retrieves the value of the join field from the tuples of the internal table, calculates the hash value of the join field using the hash calculation function corresponding to the join field value, and calculates the corresponding hash bucket number and hash value batch number based on the hash value of the join field. It should be noted that when the shared hash table cannot store all the data in the internal table, this embodiment's method needs to calculate the number of hash buckets based on the maximum memory value of the query operation, the size of the hash bucket header structure, and the size of the tuples in the internal table. Specifically, the size of the hash bucket header structure and the size of the tuples in the internal table are summed, and the ratio of the maximum memory value of the query operation to the obtained sum is taken as the number of hash buckets. In this embodiment's method, the number of batches is determined based on the size of the internal table, the maximum memory value of the query operation, the number of hash buckets, and the size of the hash bucket header structure. Specifically, the number of hash buckets is multiplied by the size of the hash bucket header structure, and the difference between the maximum memory value of the query operation and the product obtained from the multiplication is processed. The ratio of the size of the internal table to the obtained difference is taken as the number of batches. In this embodiment, the hash bucket number is equal to the remainder when the hash value is divided by the number of hash buckets. The batch number of the hash value = ROR(hash value, ceil(log2(number of hash buckets)))%number of hash buckets, where ceil(log2(number of hash buckets)) refers to calculating the logarithm of the number of hash buckets to the base 2 and then rounding the logarithm up; ROR(hash value, ceil(log2(number of hash buckets))), where h represents the hash value and n represents ceil(log2(number of hash buckets)), means: shift h to the right by n bits, shift h to the left by (32-n) bits, and then perform a OR operation on the two results.

[0082] After obtaining the hash value batch number, the method in this embodiment constructs a shared hash table according to the hash value batch number. When constructing a shared hash table for the tuple of the current batch number, the hash value of the corresponding join field and the corresponding tuple are stored in the hash bucket of the corresponding hash bucket number, and the tuples of other scanned batches are written into the internal table tuple file of the corresponding batch number.

[0083] After completing the construction of the shared hash table for the current batch number, the method in this embodiment uses multi-threaded parallel scanning of the external table, obtains the value of the join field from the tuple of the external table, calculates the hash value of the join field based on the value of the join field using the hash calculation function corresponding to the join field of the external table, calculates the corresponding hash bucket number and hash value batch number based on the hash value of the join field, and writes the tuple to the external table tuple file of the corresponding batch number.

[0084] Then, the method in this embodiment scans the tuples in the corresponding hash buckets of the shared hash table, searching for inner table tuples whose hash values ​​are equal to the values ​​of the current outer table join fields. If a tuple with an equal hash value is found and the inner and outer table join fields meet the join conditions, the tuple is returned; otherwise, the next tuple in the shared hash bucket is retrieved for comparison. If none of the tuples in the shared hash bucket meet the conditions, the outer table is scanned again until the current batch of tuple joins between the inner and outer tables is completed. Then, the next batch of shared hash tables is constructed, and the next batch of tuple joins between the inner and outer tables is performed. After joining all batches of tuples, all threads are stopped, and the inner table tuple file and the outer table tuple file are cleaned up.

[0085] The hash join parallel query method and system based on the OpenGauss database according to embodiments of the present invention employs multi-threaded parallel scanning of the inner table and uses a barrier structure to track and manage the construction of the shared hash table. All threads share the state of the barrier. When all threads reach a barrier stage, all waiting threads are awakened to continue to the next stage until the shared hash table is completed. Then, the outer table is scanned in parallel, the join is performed, and tuples that meet the join conditions are returned. The method and system of the present invention use multi-threaded collaborative comparison with serial execution, making full use of CPU resources. By sharing a hash table among multiple threads and using a barrier structure to track and manage the construction of the shared hash table, memory usage is reduced and hash join query efficiency is improved.

[0086] like Figure 6 As shown, the present invention also provides a device including a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 implements the aforementioned hash join parallel query method based on the openGauss database by running the executable computer program.

[0087] The computer program in memory 330, when implemented as a software functional unit and sold or used as an independent product, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, a portable hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0088] The system embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected based on actual needs to achieve the purpose of this embodiment. Those skilled in the art can understand and implement this without any creative effort.

[0089] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods of various embodiments or some parts of embodiments.

[0090] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. An openGauss database-based hash join parallel query method, characterized in that, The method comprises: Obtaining openGauss database memory parameters and hash bucket parameters, and judging whether the shared hash table can store all internal table data according to the obtained openGauss database memory parameters and hash bucket parameters; By constructing a barrier structure, allocating an empty hash table, and obtaining a hash calculation function corresponding to the outer table connection field in the connection condition and a hash calculation function corresponding to the internal table connection field in the connection condition, initialization conditions are created for the shared hash table construction and internal and external table tuple connection. When the shared hash table can store all internal table data, the internal table is scanned in parallel by multiple threads, the construction of the shared hash table is controlled through the barrier structure, and after the construction of the shared hash table is completed, the external table is scanned in parallel, and the tuples of the internal and external tables are connected. When the shared hash table cannot store all internal table data, the shared hash table is constructed in batches, after the construction of the current batch of shared hash table is completed, the external table is scanned in parallel, and after the current batch of tuples of the internal and external tables are connected, the next batch of shared hash table is constructed and the next batch of tuple connection of the internal and external tables is performed.

2. The hash join parallel query method based on the openGauss database according to claim 1, characterized in that, Obtaining openGauss database memory parameters and hash bucket parameters, and judging whether the shared hash table can store all internal table data according to the obtained openGauss database memory parameters and hash bucket parameters, comprising: Obtaining the internal table size value, the maximum memory value of the query work, the internal table tuple quantity, and the hash bucket header structure size value; Determining the number of hash buckets according to the maximum memory value of the query work, the internal table tuple quantity, and the hash bucket header structure size value; Multiplying the number of hash buckets and the hash bucket header structure size value, summing the product obtained by multiplication and the internal table size value, and comparing the value obtained by summation with the maximum memory value of the query work; When the value obtained by summation is not greater than the maximum memory value of the query work, it is determined that the shared hash table can store all internal table data; When the value obtained by summation is greater than the maximum memory value of the query work, it is determined that the shared hash table cannot store all internal table data.

3. The hash join parallel query method based on an openGauss database according to claim 1, characterized in that, By constructing a barrier structure, allocating an empty hash table, and obtaining a hash calculation function corresponding to the outer table connection field in the connection condition and a hash calculation function corresponding to the internal table connection field in the connection condition, initialization conditions are created for the shared hash table construction and internal and external table tuple connection, comprising: constructing a barrier structure for synchronizing the shared hash table process, the barrier structure comprising a phase counter and a lock for protecting the barrier structure members, and the phase counter indicating different construction stages of the shared hash table with different count values.

4. The hash join parallel query method based on an openGauss database according to claim 1, characterized in that, By constructing a barrier structure, allocating an empty hash table, and obtaining a hash calculation function corresponding to the outer table connection field in the connection condition and a hash calculation function corresponding to the internal table connection field in the connection condition, initialization conditions are created for the shared hash table construction and internal and external table tuple connection, comprising: A plurality of worker threads are started, a lock of the barrier structure is protected by a thread which first enters the execution logic, a count value of a phase counter in the barrier structure is updated to 1, and the lock of the barrier structure is released; A memory of the shared hash table is applied from the shared memory by the thread which first enters the execution logic, each hash bucket is emptied, and an empty shared hash table is obtained; The lock of the barrier structure is protected by the thread which first enters the execution logic, the count value of the phase counter in the barrier structure is updated to 2, the lock of the barrier structure is released, and other threads in the plurality of worker threads are woken up.

5. The openGauss database-based hash join parallel query method according to claim 1, characterized in that, By constructing the barrier structure, allocating the empty hash table, and obtaining a hash calculation function corresponding to an outer table connection field in the connection condition and a hash calculation function corresponding to an inner table connection field in the connection condition, an initialization condition is created for the shared hash table construction and the inner-outer table tuple connection, including: According to an operator of the connection condition, a left parameter data type of the connection condition, and a right parameter data type of the connection condition, a unique identifier of the operator of the connection condition in the database is queried from pg_operator; According to the unique identifier of the operator of the connection condition in the database, an index family, an index access method, and an operator strategy corresponding to the operator are queried from pg_amop; If the index access method is a hash access method, and the operator strategy is an equal hash index strategy, a hash calculation function corresponding to the outer table connection field is obtained from pg_amproc according to the index family and the left parameter data type of the connection condition; a hash calculation function corresponding to the inner table connection field is obtained from pg_amproc according to the index family and the right parameter data type of the connection condition; If the corresponding hash access method is not registered in pg_amop, an error is reported and the process is exited.

6. The openGauss database-based hash join parallel query method according to claim 1, characterized in that, When the shared hash table can store all the inner table data, the inner table is scanned in parallel by multiple threads, and the construction of the shared hash table is controlled by the barrier structure, including: When the shared hash table can store all the inner table data, the construction phase of the shared hash table is obtained through the barrier structure; When the count value of the phase counter in the barrier structure is 2, the inner table is scanned in parallel by multiple threads, the value of the connection field is obtained from the tuple of the inner table, the hash value of the connection field is calculated according to the value of the connection field by using the hash calculation function corresponding to the inner table connection field, the hash bucket number corresponding to the hash value of the connection field is calculated according to the hash value of the connection field, and the hash value of the connection field and the corresponding tuple are stored in the hash bucket with the hash bucket number; When all the threads complete the scanning of the inner table, the last thread which completes the scanning of the inner table protects the lock of the barrier structure, updates the count value of the phase counter in the barrier structure to 3, releases the lock of the barrier structure, and completes the construction of the shared hash table.

7. The openGauss database-based hash join parallel query method according to claim 1, characterized in that, After the shared hash table construction is completed, the outer table is scanned in parallel, and the tuples of the inner table and the outer table are connected, including: The outer table is scanned in parallel by using multiple threads, the value of the connection field is obtained from the tuple of the outer table, the hash value of the connection field is calculated according to the value of the connection field by using the hash calculation function corresponding to the connection field of the outer table, and the hash bucket number corresponding to the hash value of the connection field is calculated; The tuples in the corresponding hash bucket of the shared hash table are scanned, and the inner table tuples corresponding to the hash value equal to the value of the current outer table connection field are searched, if the tuples with the equal hash value are found and the inner and outer table connection fields meet the connection condition, the tuple is returned, otherwise the next tuple in the shared hash bucket is obtained for comparison, if none of the tuples in the shared hash bucket meets the condition, the outer table is continuously scanned, and after the outer table scanning is completed, all threads are stopped.

8. The openGauss database-based hash join parallel query method according to claim 1, characterized in that, When the shared hash table cannot store all the inner table data, the shared hash table is constructed in batches, after the shared hash table construction of the current batch is completed, the outer table is scanned in parallel, and after the tuples of the current batch of the inner table and the outer table are connected, the next batch of shared hash tables is constructed and the next batch of tuples of the inner table and the outer table is connected, including: The inner table is scanned in parallel by using multiple threads, the value of the connection field is obtained from the tuple of the inner table, the hash value of the connection field is calculated according to the value of the connection field by using the hash calculation function corresponding to the connection field of the inner table, and the hash bucket number and the hash value batch number corresponding to the hash value of the connection field are calculated; The shared hash table is constructed according to the hash value batch number, when the shared hash table is constructed for the tuples of the current batch number, the hash value of the corresponding connection field and the corresponding tuple are stored in the hash bucket of the corresponding hash bucket number, and the tuples of other batches scanned are written into the inner table tuple file of the corresponding batch number; The outer table is scanned in parallel by using multiple threads, the value of the connection field is obtained from the tuple of the outer table, the hash value of the connection field is calculated according to the value of the connection field by using the hash calculation function corresponding to the connection field of the outer table, and the hash bucket number and the hash value batch number corresponding to the hash value of the connection field are calculated, and the tuple is written into the outer table tuple file of the corresponding batch number; The tuples in the corresponding hash bucket of the shared hash table are scanned, and the inner table tuples corresponding to the hash value equal to the value of the current outer table connection field are searched, if the tuples with the equal hash value are found and the inner and outer table connection fields meet the connection condition, the tuple is returned, otherwise the next tuple in the shared hash bucket is obtained for comparison, if none of the tuples in the shared hash bucket meets the condition, the outer table is continuously scanned, and after the outer table scanning is completed, all threads are stopped.

9. The openGauss database-based hash join parallel query method according to claim 1, characterized in that, After the tuples of the current batch of the inner table and the outer table are connected, the next batch of shared hash tables is constructed and the next batch of tuples of the inner table and the outer table is connected, including: after all the tuples of the batches are connected, all threads are stopped, and the inner table tuple file and the outer table tuple file are cleaned up.

10. An openGauss database-based hash join parallel query system, characterized in that, The system comprises a parallel query service end, which is used for: acquiring openGauss database memory parameters and hash bucket parameters, judging whether a shared hash table can store all internal table data according to the acquired openGauss database memory parameters and hash bucket parameters; constructing a barrier structure, allocating an empty hash table, and acquiring a hash calculation function corresponding to an external table connection field in a connection condition and a hash calculation function corresponding to an internal table connection field in the connection condition, to create an initialization condition for shared hash table construction and internal and external table tuple connection; when the shared hash table can store all internal table data, multi-threading is used for parallel scanning of the internal table, the barrier structure is used for controlling the construction of the shared hash table, after the construction of the shared hash table is completed, the external table is scanned in parallel, and the tuples of the internal table and the external table are connected; when the shared hash table cannot store all internal table data, the shared hash table is constructed in batches, after the construction of the shared hash table of the current batch is completed, the external table is scanned in parallel, after the tuples of the internal table and the external table of the current batch are connected, the shared hash table of the next batch is constructed and the tuple connection of the internal table and the external table of the next batch is performed.

Images