A hash connection implementation method, electronic equipment, computer readable storage medium and computer program product

Abstract

This invention discloses a hash join implementation method, an electronic device, a computer-readable storage medium, and a computer program product. The method includes creating a reference table according to a foreign key reference and join query based on an array index. When creating the reference table, an array index is automatically created for the reference table by specifying options, and a referenced array index is also automatically created for the referenced table. A column `reffed_arr_id` is added to the created array index to obtain a new array index. A byte array is constructed based on [the largest used index in the referenced array index of the referenced table / 8] + 1, and the content of the byte array is set to 0. For all records in the referenced table that meet the conditions, the corresponding bit in the byte array is set to 1. All cells in the array index are scanned sequentially, and for matching cases, the required referenced table and corresponding record information are directly output to the upper-level operation operator.

Description

Technical Field

[0001] This invention belongs to the field of database technology, specifically relating to a hash connection implementation method, electronic device, computer-readable storage medium, and computer program product. Background Technology

[0002] In database systems, most table joins are between primary keys and foreign keys, such as multi-table joins in benchmark scenarios like TPC-H and TPC-DS. Moreover, due to the analytical nature of these scenarios, almost all databases use hash joins to execute complex analytical SQL statements.

[0003] The basic steps for performing a hash join based on primary keys and foreign keys in an existing database are as follows:

[0004] (1) First, a hash table is constructed based on the primary key (KEY) in the referenced table that meets the query conditions. During the construction of the hash table, a specific hash algorithm is used to calculate the hash value of each KEY that meets the conditions, and the KEY information (such as the KEY itself, pointers to the KEY, pointers to conflicting KEYs, etc.) is stored in the hash table. If the referenced table has 1 billion records for hash connection in analytical scenarios, the hash table built based on the KEY will occupy at least 1 billion * (4 + 8 + 8) bytes, approximately 20GB of memory. Among them, the two pointers occupy 16 bytes, the KEY occupies at least 4 bytes, and if the KEY itself occupies 20 bytes, then a single hash table will occupy approximately 36GB of memory.

[0005] (2) Scan the foreign key index of the reference table (if any) or perform a full table scan to obtain the value VALUE of the reference column. Calculate the hash value for each VALUE using the same hash algorithm and check if there is any content in the corresponding cell of the HASH TABLE. If not, it means there is no match. If there is, compare whether the KEY of the cell is consistent with the VALUE value of the reference column. If they are inconsistent, continue searching forward along the conflict chain. If the end of the conflict chain is found and there is still no match, it means that the VALUE has failed to match. If the same KEY as the VALUE is found, it means that the match is successful. The record corresponding to the VALUE matches the record corresponding to the KEY of the reference table. The required information can be output to the upper-level operation operator.

[0006] When performing hash joins based on primary keys and foreign keys in existing databases, a hash table needs to be built first based on the primary key. When the number of records in the reference table is large, the hash table built based on the primary key in existing databases consumes a lot of memory, making it possible to support only a small number of concurrent requests in many analytical scenarios. In addition, building the hash table requires a large number of hash value calculations for the keys, and when the reference table performs matching operations, it also requires a large number of hash value calculations for the reference columns, as well as comparison operations with the primary key. All of these consume a lot of CPU resources, resulting in poor performance. Summary of the Invention

[0007] Purpose of the invention: To address the problem that existing hash join operations consume a large amount of CPU resources, resulting in poor performance, this invention proposes a hash join implementation method, electronic device, computer-readable storage medium, and computer program product, applicable to hash joins between primary and foreign keys, which greatly improves hash join performance while significantly reducing memory consumption.

[0008] Technical solution: In the first aspect, the present invention proposes a hash connection implementation method, including the following steps:

[0009] Following an implementation method based on array index-based foreign key references and join queries, a reference table is created. When creating the reference table, an array index is automatically created for the reference table by specifying options, and a referenced array index is also automatically created for the referenced table.

[0010] Add a column `reffed_arr_id` to the created reference array index to obtain a new reference array index; the `reffed_arr_id` is the index of the referenced KEY recorded in the referenced array index;

[0011] A byte array is constructed based on [the maximum used index in the referenced array corresponding to the referenced table / 8] + 1, and the contents of the byte array are set to 0; the byte array is equivalent to a hash table in a traditional hash join algorithm;

[0012] For all records in the referenced table that meet the conditions, set the corresponding bit in the byte array to 1;

[0013] Scan all cells in the reference array index sequentially and complete the following: take the rejected_arr_id from the reference array index and use it directly as the hash value of the referenced record. Then, check whether the corresponding bit of the byte array is 1 to determine whether the referenced record matches the record in the referenced table. If the corresponding bit of the byte array is 1, it means that the record matches; if it is 0, it means that it does not match.

[0014] In the case of a match, the required referenced table and the corresponding record information of the referenced table are directly output to the upper-level operation operator.

[0015] Furthermore, the new reference array index is represented as:

[0016] [0]|address|prev_ref_fk_id|reffed_arr_id|

[0017] [1]|address|prev_ref_fk_id|reffed_arr_id| ......

[0019] [N]|address|prev_ref_fk_id|reffed_arr_id|

[0020] In this context, the numbers enclosed in square brackets represent array indices, address represents the address of the physical record in the reference table in the data file, and prev_ref_fk_id represents the index ID of the reference array corresponding to the last referenced physical record in the reference table.

[0021] Furthermore, the specific operation of setting the corresponding bit of the byte array to 1 for all records in the referenced table that meet the conditions includes:

[0022] If the key of the nth record meets the condition, set the n%8th bit of the n / 8th byte of the byte array to 1.

[0023] Furthermore, in the case of matching, the required referenced table and corresponding record information of the referenced table are directly output to the upper-level operation operator, specifically including:

[0024] In the case of a match, the physical record is found based on the address and output to the upper-level operation operator.

[0025] Secondly, the present invention provides an electronic device, the electronic device comprising:

[0026] At least one processor;

[0027] and a memory communicatively connected to the at least one processor;

[0028] The memory stores a computer program that can be executed by the at least one processor, which enables the at least one processor to execute a hash link implementation method.

[0029] Thirdly, the present invention provides a computer-readable storage medium storing computer instructions for causing a processor to implement a hash linking method when executed.

[0030] In a fourth aspect, the present invention provides a computer program product, wherein the computer program product comprises a computer program, which implements a hash join implementation method when executed by a processor.

[0031] Beneficial effects: the present invention provides a new algorithm suitable for hash join between primary and foreign keys, which improves hash join performance and greatly reduces memory consumption. Description of Drawings

[0032] Figure 1 is a flow chart of a hash join implementation method of the present invention. Detailed Description of Embodiments

[0033] First Embodiment:

[0034] An embodiment of the present invention provides a hash join implementation algorithm for implementing hash join between primary and foreign keys of a database. The algorithm depends on an implementation mode of foreign key reference based on an array structure (Application No.: CN202210560573.4). Based on this, a novel hash join implementation method is provided, which significantly improves hash join performance and greatly reduces memory consumption, as Figure 1 shown, specifically comprises the following steps:

[0035] Step 1: Initialize data with reference to the invention patent (Application No.: CN202210560573.4):

[0036] A main table students and a secondary table classes, table definitions and initial data are as follows:

[0037] Create table students(sno varchar(20), sname varchar(10), age int,primary key(sno));

[0038] Insert into students values(‘S001’, ‘Zhang Fei’, 15); / / Assume the address is 100

[0039] Insert into students values(‘S002’, ‘Li Kui’, 16); / / Assume the address is 200

[0040] Create table classes(cno varchar(20), cname varchar(10), stu_novarchar(20), constraint c1 foreign key(stu_no) references students(sno));

[0041] Insert into classes values('C001', 'Chinese Language', 'S001'); / / Assume the address is 300

[0042] Insert into classes values('C002', 'Mathematics', 'S001'); / / Assume the address is 400

[0043] Insert into classes values('C003', 'History', 'S001'); / / Assume the address is 500

[0044] Insert into classes values('C004', 'Chinese Language', 'S002'); / / Assume the address is 600

[0045] Insert into classes values('C005', 'Mathematics', 'S002'); / / Assume the address is 700

[0046] Insert into classes values('C006', 'Physics', 'S002'); / / Assume the address is 800

[0047] The automatically generated referenced array index content after data insertion is as follows:

[0048] Array subscript|Address|Array subscript of references referencing the current key|

[0049] [0]| 100| 2|

[0050] [1]| 200| 5|

[0051] The content of the automatically generated reference array index after data insertion is as follows. In order to support high-performance hash join, compared with the invention patent (Application No.: CN202210560573.4), a last column is added on the basis of the original reference array to store the subscript of the referenced record in the reference array (ref_arr_id):

[0052] Array index | address | the index of the array preceding the referenced key | the index of the referenced key in the referenced array (ref_arr_id) | is represented as:

[0053] [0]|address|prev_ref_fk_id|reffed_arr_id|

[0054] [1]|address|prev_ref_fk_id|reffed_arr_id| ......

[0056] [N]|address|prev_ref_fk_id|reffed_arr_id|

[0057] Where reduced_arr_id is the index of the record corresponding to the referenced KEY in the referenced array. When inserting a referenced record, the array index can be found based on the referenced KEY value, and this value can be filled into the new referenced array.

[0058] For example:

[0059] [0]| 300| -1| 0|

[0060] [1]| 400| 0 | 0|

[0061] [2]| 500| 1 | 0|

[0062] [3]| 600| -1| 1|

[0063] [4]| 700| 3 | 1|

[0064] [5]| 800| 4 | 1|

[0065] Step 2: The database receives a query processing request, such as `select classes.* from student s ,classes c where s.sno = c.stu_no and s.age>10`. This statement means querying the course information selected by students who are older than 10 years old. Assume that the execution plan generated by the database system requires the student table and the classes table to be joined by a hash to complete the execution of the join condition `s.sno=c.stu_no`.

[0066] Step 3: Construct a byte array based on [maximum index of the referenced table's corresponding array / 8] + 1, and set the contents of the byte array to 0. For 1 billion records, this will require approximately 128MB of memory. The maximum index of the referenced table's corresponding array can be obtained directly from the maximum index already used in the referenced array in the authorized patent (see application number: CN202210560573.4).

[0067] In this embodiment of the invention, a byte array is constructed based on the total number of records in the student table: [2 / 8]+1=1, that is, only 1 byte of memory is needed to construct the byte array (corresponding to the hash table in the traditional hash join algorithm), and the content of this byte is cleared to 0;

[0068] Step 4: For all records in the referenced table that meet the conditions, set the corresponding bit in the byte array to 1. Assuming the KEY of record 35 meets the requirements, since 35 / 8 = 4 and 35%8 = 3, set the 3rd bit of the 4th byte to 1. This byte array is equivalent to the hash table in a traditional hash join algorithm.

[0069] In this embodiment of the invention, the database system filters the students table based on the condition s.age>10 and finds that two records meet the condition. Therefore, the 0th and 1st bits of the byte array are set to 1.

[0070] Step 5: Scan all cells in the reference array sequentially, performing the following operations: Retrieve the `reffed_arr_id` from the array and use it directly as the hash value of the referenced record. Check if the corresponding bit in the byte array from Step 1 is 1 to determine if the record matches a record in the referenced table. If the corresponding bit in the byte array is 1, the record matches; if it is 0, it does not match. For matches, directly output the required referenced table and corresponding record information to the upper-level operation operator.

[0071] In this embodiment of the invention, all cells in the reference array are scanned sequentially. Taking the first cell as an example, the following operations are performed: the value 0 corresponding to the rejected_arr_id in the first row of the reference array is extracted, and 0 is directly used as the hash value of the reference record. The 0 bit of the corresponding 1-byte array in step 4 is checked to be 1. This indicates that the first row in the reference array meets the conditions, and the physical record can be found according to the address and output to the upper layer operator.

[0072] Step 6: After completing the judgment of all units, output all records that meet the conditions in the hash join and complete the hash join operation.

[0073] Through the above operations, we can see that the memory consumption of hash tables can be significantly reduced. At the same time, the steps of calculating the hash values ​​of the keys of the referenced table and the referencing table are eliminated, and the comparison operation of whether the keys of the referenced table and the referencing table are equal is eliminated. Instead, it is transformed into bit setting and bit comparison operations, ultimately achieving a significant performance improvement.

Claims

1. A method for implementing hash join of a primary-foreign key join in a database system, the method comprising: Includes the following steps: ​ The database automatically records an incrementing sequence number, starting from 0, for all index records containing primary keys or unique constraints. The system specifies the creation of an array index structure when creating or modifying a referenced table. When creating a referenced table, it automatically creates a reference array index for the referenced table and a referenced array index for the referenced table by specifying options. Foreign key constraints are set within the referenced table using SQL statements, thereby automatically creating both the referenced and referenced array indexes. A new reference array index is obtained by adding a column `reffed_arr_id` to the created reference array index. The index of the referenced key corresponding to `reffed_arr_id` is recorded in the referenced array index. The new reference array index is represented as: [0]|address|prev_ref_fk_id|reffed_arr_id| [1]|address|prev_ref_fk_id|reffed_arr_id| ...... [N]|address|prev_ref_fk_id|reffed_arr_id| In this context, the numbers enclosed in square brackets represent array indices, address represents the address of the physical record in the reference table in the data file, and prev_ref_fk_id represents the index ID of the reference array corresponding to the last referenced physical record in the reference table; The database receives a query processing request, which includes: executing the set join conditions between tables through a hash join; A byte array is constructed based on [the maximum used index in the referenced array corresponding to the referenced table / 8] + 1, and the contents of the byte array are set to 0; the byte array is equivalent to a hash table in a traditional hash join algorithm; For all records in the referenced table that meet the conditions, set the corresponding bit in the byte array to 1; Scan all cells in the reference array index sequentially and complete the following: take the rejected_arr_id from the reference array index and use it directly as the hash value of the referenced record. Then, check whether the corresponding bit of the byte array is 1 to determine whether the referenced record matches the record in the referenced table. If the corresponding bit of the byte array is 1, it means that the record matches; if it is 0, it means that it does not match. In the case of a match, the required referenced table and the corresponding record information of the referenced table are directly output to the upper-level operation operator. After completing the judgment of all units, output all records that meet the conditions in the hash join, and complete the hash join operation.

2. The hash join implementation method for primary and foreign key joins in a database system according to claim 1, characterized in that: The specific operation of setting the corresponding bit of the byte array to 1 for all records in the referenced table that meet the conditions includes: If the key of the nth record meets the condition, set the n%8th bit of the n / 8th byte of the byte array to 1.

3. The method for implementing hash joins in a database system for primary and foreign key joins according to claim 1, characterized in that: In the case of matching, the required referenced table and corresponding record information of the referenced table are directly output to the upper-level operation operator, specifically including: In the case of a match, the physical record is found based on the address and output to the upper-level operation operator.

4. An electronic device, characterized in that, The electronic device includes: At least one processor; and a memory communicatively connected to the at least one processor; The memory stores a computer program that can be executed by the at least one processor, which is then executed by the at least one processor to enable the at least one processor to perform a hash join implementation method for primary and foreign key joins in a database system according to any one of claims 1-3.

5. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that, when executed by a processor, implement a hash connection implementation method for primary and foreign key connections in a database system according to any one of claims 1-3.

6. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements a hash connection implementation method for primary and foreign key connections in a database system according to any one of claims 1-3.

Images