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

Abstract

The invention discloses a Hash connection implementation method, electronic equipment, a computer readable storage medium and a computer program product, and the method comprises the steps: creating a reference table according to an implementation method for foreign key reference and connection query based on an array index, automatically creating a reference array index for the reference table through a specified option when the reference table is created, and automatically creating a connection query result according to the reference array index. Meanwhile, a referenced array index is automatically created for the referenced table; a column of reference is added to the created reference array index, and a new reference array index is obtained; constructing a byte array according to [the maximum subscript used in the quoted array index corresponding to the quoted table / 8] + 1, and setting the content of the byte array as 0; for all records meeting conditions in the referenced table, setting a bit corresponding to the byte array as 1; all units in the reference array index are sequentially scanned, and for the matching condition, the required referenced table and the corresponding record information of the reference table are directly output to an upper-layer operator.

Description

TECHNICAL FIELD

[0001] The present application belongs to the technical field of database, and particularly relates to a hash connection implementation method, an electronic device, a computer readable storage medium and a computer program product. BACKGROUND

[0002] In a database system, the connection between most tables is the connection between primary keys and foreign keys, such as the multi-table connection in benchmark test scenarios such as TPC-H, TPC-DS, and because it is an analytical scenario, almost all databases use hash connection to execute complex analytical SQL statements.

[0003] The basic steps of executing hash connection based on primary keys and foreign keys in the existing database are as follows:

[0004] (1) First, a hash table (HASHTABLE) is constructed based on the primary keys (KEY) in the referenced table that meet the query conditions. In the process of building the hash table, a specific hash algorithm is used to calculate the hash value for each KEY that meets the condition, and the KEY information (such as KEY itself, pointer to KEY, pointer to conflict KEY, etc.) is stored in the hash table. If the referenced table has 1 billion records for analytical scenarios of hash connection, the hash table constructed based on KEY will occupy at least 1 billion * (4+8+8) bytes, about 20 GB of memory, of which two pointers occupy 16 bytes and KEY occupies at least 4 bytes. If KEY itself occupies 20 bytes, a single hash table will occupy about 36 GB of memory.

[0005] (2) Scan the foreign key index of the referenced table (if any) or perform full table scan to obtain the value VALUE of the reference column. For each VALUE, the same hash algorithm is used to calculate the hash value, and the corresponding unit of the HASH TABLE is checked for content. If there is no content, it means that there is no match. If there is, compare the KEY in the unit with the VALUE of the reference column. If they are not the same, continue to find along the conflict chain. If the tail of the conflict chain is still not matched, it means that the VALUE fails to match. If the same KEY as the VALUE is found, it means that the match is successful. The record corresponding to the VALUE and the record corresponding to the KEY of the reference table are matched, and the required information can be output to the upper operation operator.

[0006] In the prior database, when performing hash connection based on primary keys and foreign keys, a hash table is first constructed based on the primary keys, and when the number of records of the reference table is large, the hash table constructed by the prior database based on the primary keys needs to consume a large amount of memory, so that many analytical scenarios can only support a small number of concurrent requests. In addition, a large number of KEY hash value calculation actions need to be performed when the hash table is constructed, and the reference table also needs to perform a large number of reference column hash value calculation actions when performing matching operations, and also needs to perform comparison operations with the primary keys, which all consume a large amount of CPU resources, resulting in poor performance. SUMMARY

[0007] The present application aims to solve the problem that the prior hash connection operation needs to consume a large amount of CPU resources, resulting in poor performance. The present application provides a hash connection implementation method, an electronic device, a computer readable storage medium and a computer program product, which are suitable for hash connection between primary and foreign keys, greatly improve the performance of hash connection, and greatly reduce the memory consumption.

[0008] Technical scheme: In a first aspect, the present application provides a hash connection implementation method, comprising the following steps:

[0009] According to an implementation method of foreign key reference and connection query based on array index, a reference table is created, and when the reference table is created, a reference 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] A column refed_arr_id is added to the created reference array index to obtain a new reference array index, and the refed_arr_id corresponds to the subscript of the referenced KEY in the referenced array index;

[0011] A byte array is constructed according to [the maximum subscript used in the referenced array index corresponding to the referenced table / 8]+1, and the content of the byte array is set to 0; the byte array is equivalent to the hash table in the traditional hash connection algorithm;

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

[0013] All cells in the reference array index are sequentially scanned to complete the following contents: the refed_arr_id in the reference array index is directly taken as the hash value of the reference record, and whether the reference record matches the record in the referenced table is judged by detecting whether the corresponding bit of the byte array is 1, if the bit of the byte array is 1, it means that the record matches, and if the bit of the byte array is 0, it means that the record does not match;

[0014] For the matching case, the required referenced table and the corresponding record information of the reference table are directly output to the upper operation operator.

[0015] Further, the new reference array index is expressed 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] Wherein, the number in the square bracket represents the array subscript, address represents the address of the physical record in the data file in the reference table, and prev_ref_fk_id represents the subscript ID of the corresponding reference array index of the last time the referenced physical record is referenced in the reference table.

[0021] Further, for all records in the referenced table that meet the conditions, the bit corresponding to the byte array is set to 1, and the specific operation comprises:

[0022] Assuming that the KEY of the nth record meets the condition, the nth%8 bit of the nth / 8 byte of the byte array is set to 1.

[0023] Further, for the matching case, the required referenced table and the corresponding record information of the reference table are directly output to the upper operation operator, and the specific operation comprises:

[0024] For the matching case, the physical record is found according to address, and is output to the upper operation operator.

[0025] Secondly, the present application provides an electronic device, which comprises:

[0026] At least one processor;

[0027] And a memory in communication connection with the at least one processor;

[0028] Wherein, the memory stores a computer program which can be executed by the at least one processor, and the computer program is executed by the at least one processor, so that the at least one processor can execute a hash connection implementation method.

[0029] Thirdly, the present application provides a computer readable storage medium, which stores computer instructions, and the computer instructions are used to make the processor execute a hash connection implementation method.

[0030] Fourthly, the present invention proposes a computer program product, which includes a computer program that implements a hash join implementation method when executed by a processor.

[0031] Advantages: The present invention proposes a new algorithm suitable for hash join between primary and foreign keys, which improves the hash join performance while significantly reducing the memory consumption. Brief Description of the Drawings

[0032] Figure 1 It is a flowchart of a hash join implementation method of the present invention. Detailed Embodiments

[0033] Embodiment 1:

[0034] The embodiment of the present invention proposes a hash join implementation algorithm for implementing the hash join between the primary and foreign keys of a database. This algorithm depends on the foreign key reference implementation method based on an array structure (Application No.: CN202210560573.4). On this basis, a brand-new hash join implementation method is provided, which significantly improves the hash join performance while greatly reducing the memory consumption. As Figure 1 shown, it specifically includes the following steps:

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

[0036] The main table students and the slave table classes, the 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_no varchar(20), constraint c1 foreign key(stu_no) references students(sno));

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

[0042] Insert into classes values('C002', 'Math', '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', 'S002'); / / Assume the address is 600

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

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

[0047] The content of the reference array index automatically generated after inserting data is as follows:<00001​​​​​​​​​​​​​array index | address | prev_ref_fk_id | reffed_arr_id | ref_arr_id |, which is expressed as:

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

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

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

[0057] Where reffed_arr_id is the index of the referenced KEY in the referenced array, when inserting the reference record, the array index of the referenced KEY can be found according to the value of the referenced KEY, and this value is filled into the new reference array.

[0058] Exemplary:

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

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

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

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

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

[0064] [5] | 800 | 4 | 1 | 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, which means querying the course information selected by the student whose age is greater than 10 years old, and assuming that the execution plan generated by the database system requires a hash join between the student table and the classes table to complete the execution of the connection condition s.sno = c.stu_no.

[0066] Step 3: Construct a byte array according to [the maximum subscript of the referenced table corresponding array / 8]+1, and set the byte array content to 0. For 1 billion records, about 128MB of memory is required. The maximum subscript of the referenced table corresponding array can be directly obtained according to the maximum subscript of the referenced array used in the granted patent (see application number: CN202210560573.4).

[0067] In the embodiment of the application, a byte array is constructed according to the total records of the stuent table: [2 / 8]+1=1, that is, only 1 byte of memory is required to construct the byte array (corresponding to the hash table in the traditional hash join algorithm), and the byte content is 0;

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

[0069] In the embodiment of the application, the database system filters the students table according to the condition s.age>10, and finds that there are 2 records that meet the condition, so the 0th and 1st positions of the byte array are set to 1.

[0070] Step 5: Sequentially scan all cells in the reference array, and complete the following operations: take the reffed_arr_id in the array directly as the hash value of the reference record, and detect whether the corresponding bit of the byte array in step 1 is 1 to determine whether the record matches the record in the referenced table. If the corresponding bit of the byte array is 1, it means that the record matches, and if it is 0, it means that it does not match. For the matching case, the corresponding record information of the referenced table and the reference table required by the upper operator can be directly output.

[0071] In the embodiment of the application, all cells in the reference array are sequentially scanned, and the first cell is taken as an example to complete the following operations: the reffed_arr_id corresponding value 0 in the first row of the reference array is taken out, 0 is directly taken as the hash value of the reference record, and the 0th bit of the 1-byte array in step 4 is detected. 1, which indicates that the first row in the reference array meets the condition, and the physical record can be found according to the address and output to the upper operator;

[0072] Step 6: After judging all cells, output all records that meet the conditions in the hash join, and complete the hash join operation.

[0073] Through the above operation, it can be seen that the memory consumption of the hash table can be greatly reduced, and at the same time, the steps of calculating the hash values of the referenced table and the KEY of the reference table are eliminated, the comparison operation of whether the KEY of the referenced table and the reference table is equal is eliminated, and is transformed into bit setting and bit comparison operation, and finally the performance is greatly improved.

Claims

1. A method for implementing a hash join, the method comprising: The method comprises the following steps: ​ According to an implementation method of array index-based foreign key reference and join query, a reference table is created, and when the reference table is created, a reference array index is automatically created for the reference table by specifying an option, and a referenced array index is also automatically created for the referenced table; A column reffed_arr_id is added to the created reference array index to obtain a new reference array index; the reffed_arr_id is corresponding to the subscript of the record in the referenced array index; A byte array is constructed according to [the maximum subscript used in the corresponding referenced array index of the referenced table / 8]+1, and the content of the byte array is set to 0; the byte array is equivalent to the hash table in the traditional hash join algorithm; For all records in the referenced table that meet the condition, the corresponding bit of the byte array is set to 1; All cells in the reference array index are sequentially scanned to complete the following contents: the reffed_arr_id in the reference array index is directly taken as the hash value of the reference record, and whether the reference record matches the record in the referenced table is judged by detecting whether the corresponding bit of the byte array is 1; if the bit of the byte array is 1, it means that the record matches, and if the bit of the byte array is 0, it means that the record does not match; For the matching case, the corresponding record information of the referenced table and the reference table is directly output to the upper operation operator.

2. The hash join implementation method of claim 1, wherein: 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| Wherein, the number in the square brackets is the array subscript, address represents the address of the physical record in the data file in the reference table, and prev_ref_fk_id represents the subscript ID of the last reference array index corresponding to the referenced physical record in the reference table.

3. The hash join implementation method of claim 1, wherein: The specific operation of setting the corresponding bit of the byte array to 1 for all records in the referenced table that meet the condition comprises: Assuming that the KEY of the nth record meets the condition, the nth%8 bit of the nth / 8 byte of the byte array is set to 1.

4. The hash join implementation method of claim 1, wherein: The specific operation of directly outputting the corresponding record information of the referenced table and the reference table to the upper operation operator for the matching case comprises: For the matching case, the physical record is found according to the address, and is output to the upper operation operator.

5. An electronic device, comprising: The electronic device comprises: At least one processor; And a memory connected in communication with the at least one processor; Wherein, the memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to execute the hash join implementation method in any one of claims 1-4.

6. A computer-readable storage medium, characterized in that, The computer readable storage medium stores computer instructions for causing the processor to implement the hash connection implementation method in any one of claims 1-4 when executed.

7. A computer program product, characterised in that, The computer program product comprises a computer program which, when executed by the processor, implements the hash connection implementation method in any one of claims 1-4.

Images