Database parallel equivalent connection method based on column storage format

Abstract

The invention discloses a database parallel equivalent connection method based on a column storage format. The database parallel equivalent connection method comprises the steps that a data table with the minimum data size is selected from a plurality of involved data tables to serve as a basic table; hash is carried out on the basic table according to a Hash function, data in the basic table is divided into a plurality of fragments according to a Hash result, each fragment is numbered and allocated to a plurality of sub-tasks, and a sub-Hash Table with a connection field value list as Key and a row position information list as Value is constructed in a concurrent mode; performing the same operation on the to-be-connected data table, and concurrently performing Hash detection; and according to a position information sequence pair obtained by Hash detection, splicing connection field values of corresponding position information of the basic table and the data table to be connected to obtain a spliced result, and taking a set of all the spliced results as a final connection result.

Description

Technical Field

[0001] The present invention belongs to the technical field of database storage, and in particular relates to a database parallel equivalent connection method based on a column storage format. Background Art

[0002] The equijoin operation in the database is an operation that combines multiple data tables into one data table by using the equality comparison between the data table fields as the filtering condition. You can specify a filtering condition that is not an equality comparison for an equijoin, but the condition must only involve a single data table. There must be at least one equality comparison condition in an equijoin, otherwise it cannot be called an equijoin. A data row will appear in the result data table of the equijoin only if and only if the data row in each data table meets all the given filtering conditions. There are usually three ways to implement an equijoin operation: Nested Loop, MergeJoin, and Hash Join.

[0003] In the traditional Hash-based multi-column equal join algorithm, the Hash value needs to be calculated for the fields involved in the join in each record, and the comparison function of multiple fields needs to be implemented through encapsulation or splicing. When a Hash conflict occurs, the comparison function is used to group them. This solution has low performance and accesses the HashTable more times. Since the comparison cost of objects in programming languages ​​is much higher than that of native data types, the comparison cost through splicing and encapsulation is high and the performance is low.

[0004] In the traditional Hash-based multi-column equi-join algorithm, data is still scanned in the form of rows. When the data in the database is organized in the form of columns, since the algorithm accesses the memory randomly and the architecture of modern CPUs has higher performance in sequential access to memory, the performance of the algorithm in column data is relatively low.

[0005] In the traditional Hash-based multi-column equi-join algorithm, data is grouped through a HashTable, resulting in the parallelism of the algorithm being limited by the low parallelism of the HashTable. Therefore, traditional databases usually use a single-threaded mode for equi-join calculations. Summary of the invention

[0006] Purpose of the invention: In order to solve the problems of high comparison cost, low performance in column data, low parallelism, etc. existing in the traditional multi-column equi-join algorithm, the present invention proposes a parallel data equi-join method for a single column or multiple columns of a database based on a column storage format.

[0007] Technical solution: A database parallel equivalent connection method based on column storage format, comprising the following steps:

[0008] Step 1: Determine whether all given equijoin conditions contain equijoin conditions involving more than or equal to 2 data tables. If not, directly filter the data of each data table in the order specified in the equijoin conditions, and then splice the data according to the filtering results and the equijoin conditions to obtain the connection result; if contained, execute step 2;

[0009] Step 2: Select the data table with the least amount of data from the multiple data tables involved as the base table;

[0010] Step 3: Hash the basic table according to the hash function, and divide the data in the basic table into several shards according to the hash result. Number each shard and assign it to several subtasks to concurrently build a sub-HashTable with the connection field value list as the key and the row location information list as the value.

[0011] Hash the data table to be connected according to the same hash function, and divide the data in the data table to be connected into several shards according to the hash result. Number each shard and assign it to several subtasks to concurrently build a sub-HashTable with the connection field value list as the key and the row position information list as the value, and perform hash detection concurrently; in each hash detection, according to the equal connection condition, use the sub-HashTable of the data table to be connected with the corresponding number to search the sub-HashTable of the basic table with the corresponding number to obtain the position information sequence pair;

[0012] Step 4: Based on the position information sequence pairs obtained by hash detection, concatenate the connection field values ​​of the corresponding position information of the base table and the data table to be connected to obtain the concatenated result. The set of all concatenated results is the final connection result.

[0013] Furthermore, in step 3, the sub-HashTable is constructed with the connection field value list as the key and the row position information list as the value, and the specific operations include:

[0014] S2.1: Take the first connection field to group and write the grouping result into the child HashTable;

[0015] S2.2: Based on the grouping results currently stored in the sub-HashTable, the second connection field is grouped and the grouping results are written into the sub-HashTable; and so on, until all the connection fields are grouped and the final sub-HashTable is obtained.

[0016] Furthermore, the first connection field is taken for grouping, and the grouping result is written into the sub-HashTable. The specific operations include:

[0017] S2.2.1: Determine whether there is an unused row position pos on the first connection field. If not, it means that the sub-HashTable already contains the grouping result of the first connection field; if so, execute S2.2.2;

[0018] S2.2.2: Take the unused row position pos from the first connection field, and determine whether the connection field value at the row position pos, recorded as f[pos], can be found in the sub-HashTable. If not, create a new record {f[pos], row position pos}, add this new record to the sub-HashTable, and then execute S2.2.1; if so, search the sub-HashTable for the row position information list corresponding to the connection field value f[pos], add the row position pos to the row position information list, and then execute S2.2.1.

[0019] Furthermore, the second connection field is grouped based on the grouping result currently stored in the sub-HashTable, and the grouping result is written into the sub-HashTable. The specific operations include:

[0020] S2.3.1: Determine whether there is an unused record rec in the grouping result currently stored in the sub-HashTable. If yes, execute S2.3.2; if not, it means that the sub-HashTable contains the grouping result of the second connection field based on the grouping result currently stored in the sub-HashTable;

[0021] S2.3.2: Take an unused record rec from the grouping results currently stored in the child HashTable, and determine whether the record rec contains only one row position information. If it contains only one row position information, record the row position information as POS, and append f[POS] to the Key of the record rec. f[POS] is the value of the second connection field at position POS, and then execute S2.3.1; if it contains multiple row position information, execute S2.3.3;

[0022] S2.3.3: Determine whether there is unused row position information in the record rec. If not, execute S2.3.1; if yes, obtain the unused row position information POS1 from the record rec, and determine whether the value f[POS1] of the second connection field corresponding to the row position information POS1 can be found in the sub-HashTable. If not, execute S2.3.4; if so, search the sub-HashTable for the row position information list corresponding to the value f[POS1] of the second connection field, and add the row position information POS1 to the row position information list;

[0023] S2.3.4: Create a new record {f[POS1], POS1} in the child HashTable and execute S2.3.3.

[0024] Furthermore, in each hash detection, according to the equal connection condition, the sub-HashTable of the data table to be connected with the corresponding number is used to search the sub-HashTable of the basic table with the corresponding number to obtain the position information sequence pair, and the specific operations include:

[0025] For each equi-join condition involving the base table and the data table to be joined, take the join field value of the sub-HashTable of the data table to be joined in the equi-join condition in turn, and search the position information sequence corresponding to the join field value from the sub-HashTable of the base table. The position information sequence and the position information sequence of the join field value in the sub-HashTable of the data table to be joined constitute a position information sequence pair.

[0026] The present invention also discloses a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps of a database parallel equivalent connection method based on a column storage format are implemented.

[0027] The present invention also discloses a storage medium, wherein the storage medium stores an equivalue connection program, and when the equivalue connection program is executed by at least one processor, the steps of a database parallel equivalue connection method based on a column storage format are implemented.

[0028] Beneficial effects: Compared with the prior art, the present invention has the following advantages:

[0029] (1) The method of the present invention realizes equi-join for column storage format, scans data in column form to perform equi-join calculation, and improves the performance of data scanning;

[0030] (2) According to the characteristics of data in column storage format, the method of the present invention splits the comparison of multiple columns into comparison calculations for each individual column, reduces the access to the HashTable and the comparison operation of the composite key, uses data access operations that conform to the modern CPU architecture, and improves the performance of accessing data; by pre-operating each column independently, random access to the memory is avoided, and the characteristics of the modern CPU architecture are fully utilized to improve the grouping performance;

[0031] (3) The method of the present invention avoids the splicing and encapsulation when comparing multiple grouping fields by comparing each column separately. When implementing the comparison algorithm of wide byte data types (for example, string types), SIMD instructions can be used to perform stream instruction comparison operations, thereby improving comparison performance;

[0032] (4) The method of the present invention solves the performance bottleneck caused by HashTable in the traditional Hash Join algorithm and improves the parallelism of the database aggregation algorithm through sharding technology;

[0033] (5) The method of the present invention reduces the read and write operations of the large data HashTable during the construction process by constructing the position sequence column by column, reduces the splicing operations of the comparison data, supports the use of sub-HashTables with different implementations according to the column type, and improves the performance of the HashTable construction process;

[0034] (6) The method of the present invention divides the equi-join operation into sub-tasks by splitting the grouping tasks and sharding the data. There is no dependency between the sub-tasks in the same stage, and the performance of the equi-join algorithm of the database is improved by parallel execution. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 A schematic diagram showing the difference between organizing the same set of data in rows and columns;

[0036] Figure 2 A schematic diagram of the flow of the column data parallel equivalent connection method proposed by the present invention;

[0037] Figure 3 A schematic diagram of the connection process shown for step 3, step 4 and step 5;

[0038] Figure 4 A flowchart for constructing a sub-hash table HashTable;

[0039] Figure 5 is a schematic diagram of the process of S2;

[0040] Figure 6 It is a schematic diagram of the process of S3;

[0041] Figure 7 This is a flowchart of the hash detection subtask;

[0042] Figure 8 Schematic diagram of the overall process of equivalue connection of the data in Table 1 and Table 2. DETAILED DESCRIPTION

[0043] The technical solution of the present invention is now further described in conjunction with the accompanying drawings and embodiments.

[0044] Embodiment 1:

[0045] The technical terms involved in the embodiments are explained as follows:

[0046] Column data refers to several pieces of data with the same data type, usually the data corresponding to a column in a data table, or the data calculated by an expression. Each piece of data in the column data has a logical, continuous position in the column, and the piece of data corresponding to the position in the column can be accessed according to the number of the position. Figure 1 The example illustrates the difference between organizing the same set of data in rows and columns.

[0047] The data equi-join operation of the database refers to the operation of combining the data of several data tables according to the equal conditions according to the specified connection conditions to obtain a new data table. Specifically, when two data tables are equi-joined, for each row of data in data table 1, each row of data in data table 2 is judged according to the equal conditions. If the equal conditions are met, the corresponding data rows of data table 1 and data table 2 are spliced ​​and added to the equi-join result; otherwise, the row is ignored and the next row of data in data table 2 is judged.

[0048] In addition, for ease of understanding, the equivalent join process is illustrated by using the data table Table1 shown in Table 1 and the data table Table2 shown in Table 2, and the join condition is agreed to be Table1.F1=Table2.F1 AND Table1.F2=Table2.F2.

[0049] Table 1 Data in Table 1

[0050]

[0051]

[0052] Table 2 Data in Table 2

[0053] Row number\column name F1 F2 0 1 2 1 3 4 2 1 4 3 4 5

[0054] Figure 2 : is a flow chart of the column data parallel equivalent connection method provided in this embodiment, which mainly includes the following steps:

[0055] Step 1: If the connection condition does not include conditions involving multiple data tables, HashTable is not used. The data of each table is directly filtered in the order specified in the connection statement, and then the data is spliced ​​according to the filtering results to obtain the result. The algorithm ends, otherwise proceed to the next step. Optionally, the filtering of different tables can be executed concurrently.

[0056] Taking the data tables Table1 and Table2 as examples, assuming that the connection condition is changed to Table1.F1=1ANDTable2.F1=3, the data in Table1 is first filtered according to the condition Table1.F1=1, and the retained position sequence is [0,2]; then the data in Table2 is filtered according to the condition Table2.F1=3, and the retained position sequence is [1]; finally, the data is concatenated, and the 0th row of Table1 is concatenated with the 1st row of Table2, and the 2nd row of Table1 is concatenated with the 1st row of Table2, and the final result is shown in Table 3.

[0057] Table 3. The splicing results of the example connection conditions in step 1

[0058] Row number\column name F1 F2 F1 F2 0 1 2 3 4 1 1 3 3 4

[0059] Step 2: Select the data table with the least amount of data as the data table for building the HashTable, which is called the basic table;

[0060] Step 3: Hash the base table according to the hash function (denoted as hash function A), and divide the data of the base table into several shards according to the hash result, the number of processor cores of the current machine, the amount of data to be connected, and other factors. Number each shard and assign it to several subtasks to concurrently build a sub-hash table HashTable with the list of all connection field values ​​as the key Key and the position information sequence as the value Value. In each subtask, for all the base table connection fields in all given equal connection conditions, scan the assigned data one by one (i.e., column by column) to build a sub-hash table HashTable with the list of all field values ​​as the key Key and the position information sequence as the value Value. Specifically, first scan and group the first field, and optionally, different data types use different implementations of Hash Table. Through the Hash grouping algorithm, a sub-HashTable with the field value of the current grouped column as Key and the position information sequence as Value can be obtained, where each record represents the position information of the same Key in the current column; then substitute the position information sequence into the next field column for further grouping, and the next column is further grouped only according to the valid position data in the substituted sequence, and the grouping algorithm is the same as above. If there is only one piece of data in the sequence, the subsequent fields do not need to be further grouped, and the sequence grouping ends. Whenever a new group is obtained, the grouping column field value corresponding to the position sequence entered during this grouping is concatenated with the current grouping column field value as a new Key, and a new sub-HashTable is established with the current grouping position information sequence as the Value. Repeat this process until all field columns have completed the grouping of the position sequence. At this time, the sub-hash table HashTable obtained is the sub-hash table HashTable used by the basic table in equal value connection.

[0061] Step 4: When all the grouping tasks are completed, the data table to be connected is hashed according to the hash function A, and according to the hash result, the data of the data table to be connected is divided into several shards, each shard is numbered, and assigned to several subtasks to build a sub-hash table HashTable with a list of all connection field values ​​as the key Key and a position information sequence as the value Value, and concurrent hash detection is performed. In the detection task, the sub-hash table HashTable of the data table to be connected with the corresponding number is used to search the sub-hash table HashTable of the basic table with the corresponding number. For each successful search, a position information sequence pair is obtained. According to the found position information sequence pair, the connection field value of the corresponding position of the basic table is obtained, and the Cartesian product is performed with the connection field value of the corresponding position of the current data table to be connected to obtain the connected data.

[0062] Step 5: According to the location information sequence, when all detection tasks are completed, the collection of all connected data result sets is the final connection result.

[0063] The entire connection process is as follows Figure 3 As shown, it can be seen that the grouping and detection operations are performed concurrently, thereby reducing the time consumption of the connection process.

[0064] Still taking the example of data table Table1 and data table Table2 and the agreed connection conditions, Table 4-1 and Table 4-2 give some possible sharding situations of data table Table1, and obtain shard 1 and shard 2, which are respectively handed over to subtask 1 and subtask 2 to construct the sub-hash table HashTable.

[0065] Table 4-1 Data Table 1 obtained by a possible sharding method shard 1

[0066] Row number\column name F1 F2 0 1 2 1 3 4 2 1 3 4 1 2

[0067] Table 4-2 Table 1 fragment 2 obtained by a possible fragmentation method

[0068] Row number\column name F1 F2 3 2 2

[0069] Table 5-1 and Table 5-2 show the sub-hash tables HashTable constructed by subtasks under the agreed connection conditions for all shards of Table1.

[0070] Table 5-1 Sub-hash table 1 generated by subtask 1

[0071] Key: List of aggregate field values Value: list of row positions 1，2 0，4 3，4 1 1，3 2

[0072] Table 5-2 Sub-hash table 2 generated by subtask 2

[0073] Key: List of aggregate field values Value: list of row positions 2，2 3

[0074] Table 6-1 and Table 6-2 show the sharding situation when Table2 uses the same hash function as Table1.

[0075] Table 6-1 Data table Table2 obtained by the same sharding method as Table1.

[0076] Row number\column name F1 F2 0 1 2 1 3 4 2 1 4

[0077] Table 6-2 Data table Table2 obtained by the same sharding method as Table1.

[0078] Row number\column name F1 F2 3 4 5

[0079] After the two shards of the data table Table2 pass through each hash detection subtask, they obtain the position sequence information pair sets {{[0,4],[0]},{[1],[1]}} and {} (empty set) respectively.

[0080] The data at corresponding positions in data table Table1 and data table Table2 are concatenated to obtain the final connection result Table 7.

[0081] Table 7 Final connection result of Table1 and Table2

[0082] Row number\column name Table1.F1 Table1.F2 Table2.F1 Table2.F2 0 1 2 1 2 1 1 2 1 2 2 3 4 3 4

[0083] For ease of description, a record of the sub-hash table HashTable with Key as the key and Value as the value is abbreviated as {Key, Value}.

[0084] Figure 4 This is a schematic diagram of the process of constructing the sub-hash table HashTable in this embodiment, which mainly includes the following steps:

[0085] S1: Create a sub-hash table HashTable with the connection field value list as the key and the row location information list as the value.

[0086] S2: Take the first connection field F1 for grouping and write the grouping result into the sub-hash table HashTable.

[0087] S3: Sequentially select a field f that has not been grouped in this task from all the specified connection fields, that is, the second field F2, and group the second field F2 based on the sub-hash table HashTable. Specifically, according to the intermediate grouping results stored in the sub-hash table HashTable, only the groups containing multiple rows are grouped according to the second field F2, and the groups containing only a single row do not need to be grouped again, and the value of the second field F2 can be directly appended to the group's connection field value list.

[0088] S4: If all connection fields have been grouped, the grouping within this task is completed; otherwise, continue to execute step S3.

[0089] Figure 5 is a flow chart of step S3, which mainly includes the following steps:

[0090] Sub-step 1: Take the unused position pos on the first connection field F1. If all positions on the first connection field F1 have been used, execute sub-step 3. Otherwise, execute sub-step 2.

[0091] Sub-step 2: If the value of the first connection field F1 at position pos is recorded as f[pos] and cannot be found in the sub-hash table HashTable, it means that the row where pos is located appears for the first time in the grouping of this task. Perform the following operations: Create a new field value list key and add f[pos]; create a new row position information list l and add the initial element pos; add the new record {key, l} to the sub-hash table HashTable; execute sub-step 1;

[0092] If it can be found, it means that the row where pos is located should be assigned to an existing group. Add pos to the value of the record found in the sub-hash table HashTable (that is, the row position information list), and then continue to execute sub-step 1.

[0093] Sub-step 3: The sub-hash table HashTable already contains the result of grouping the first connection field F1.

[0094] The following takes construction task 1 as an example for further explanation.

[0095] During the execution, the first connection field F1 is grouped first. After Task 1 establishes the sub-hash table HashTable, it first takes the position 0 of the first connection field F1, and the value of F1[0] is 1. At this time, the sub-hash table HashTable is empty, and the value 1 appears for the first time, so {connection field value list {1}, position information list {0}} is added to the sub-hash table HashTable; then take the position 1 of the first connection field F1, and the value of F1[1] is 3, which also appears for the first time in the sub-hash table HashTable, and add {field value list {3}, position information list {1}} to the sub-hash table Has hTable; then take the position 2 of the first connection field F1, the value of F1[2] is 1, which already exists in the sub-hash table HashTable, so the position information 2 is added to the existing record, and the record after adding is {field value list {1}, position information list {0,2}}; finally, take the position 4 of the first connection field F1, the value of F1[2] is 1, which already exists in the sub-hash table HashTable, so the position information 4 is added to the existing record, and the record after adding is {field value list {1}, position information list {0,2,4}}. The sub-hash table HashTable after the first connection field F1 is grouped is shown in Table 8.

[0096] Table 8 Hash table after task 1 groups the first connection field F1

[0097]

[0098]

[0099] Figure 6 This is a flow chart of S3, which mainly includes the following steps:

[0100] (a): Create a hash table H2 with the connection field list as the key and the row location information list as the value.

[0101] (b): Take the unused record rec in the sub-hash table HashTable. If all the records of the sub-hash table HashTable have been used, H2 already contains the result of grouping the second connection field F2 based on the sub-hash table HashTable. Exchanging H2 and the sub-hash table HashTable completes the grouping of the second connection field F2. (c): If the value of rec contains a unique value, record it as pos, append f[pos] to the key key of rec, and add a new record {key, value of rec} to H2, and then execute (b); otherwise, the value of rec contains multiple row positions, and a hash table h is established with the data of field f as the key and the pointer to the row position information list as the value.

[0102] (d): Get the unused position information pos in the value of rec. If all the position information in the value of rec has been used, execute (b).

[0103] (e): If f[pos] cannot be found in the hash table h, it means that the row where pos is located appears for the first time in the group of this task. Perform the following operations: append f[pos] to the key key of rec; create a new row position information list l and add the initial element pos; add the new record rec2{key,l} to H2; add the new record {f[pos], pointer to rec2 value} to h; execute (d);

[0104] Otherwise, it indicates that the row where pos is located should be assigned to an existing group. The row position list is obtained through the value of the record found in h (that is, the pointer to the row position list), and pos is added to it, and then (d) is executed.

[0105] (f): H2 already contains the result of grouping the second connection field F2. Exchanging H2 and the sub-hash table HashTable completes the grouping of field f.

[0106] The following is a further explanation of the grouping of field F2 by Task 1. At this time, Task 1 has completed the grouping of the first connection field F1. Task 1 first takes the record {field value list {1}, location information list {0,2,4}} from the sub-hash table HashTable. At this time, the record contains multiple location information, so a hash table h is established with the value of the first connection field F1 as the key and the location information list pointer as the value, and execute (e);

[0107] Take the position information 0 in the record. At this time, h is empty. F2[0] (value is 2) cannot find the same group of the second connection field F2 value in h, so add the value 2 to the key of the original record. The added key is the field value list {1,2}; then create a new position information list l{0}; finally, add the record rec2{key,l} to H2, and add the new record {2, pointer to rec2 value} to h.

[0108] Take the position information 2 in the record, F2[2] (value is 3) cannot find the same group of the second connection field F2 value in h, so add the value 3 to the key of the original record, and the added key is the field value list {1,3}; then create a new position information list l{2}; finally, add the record rec2{key,l} to H2, and add the new record {3, pointer to rec2 value} to h.

[0109] Take the position information 4 in the record, F2[2] (value is 2) corresponds to the record added in step 1, so through the pointer of the rec2 value in the record, add the position information 2 to the value of rec2 (that is, the position information list). After adding, rec2 is {field value list {1,2}, position information list {0,2}}.

[0110] Next, Task 1 takes the next (and last) record {field value list {3}, location information list {1}} in the sub-hash table HashTable. At this time, the record contains only a single location, so the remaining logic in (c) is executed, F2[1] (value is 4) is added to the key key of the record, and the new record {key, value of the old record} ({field value list {3,4}, location information list {1}}) is added to H2. Finally, Task 1 exchanges H2 with the sub-hash table HashTable, thus completing the grouping of the first connection field F1 and the second connection field F2. The results are shown in Table 5-1 and Table 5-2.

[0111] Figure 7 : is a flowchart of the hash detection subtask provided in this embodiment, including the following steps:

[0112] S51: For the shard numbered i, the HashTable H3 constructed by the previous construction subtask numbered i is used for hash detection.

[0113] S52: Get an unused position pos on the data table to be connected. If all positions of the data table to be connected have been used, the subtask is completed.

[0114] S53: For each equi-join condition involving the base table and the data table to be connected, according to the fields of each base table and the order of appearance of the fields of the base table when constructing the sub-hash table HashTable, the values ​​of the expressions containing the corresponding field values ​​of the data table to be connected in the equi-join condition are taken in turn, and the result columns of each value are grouped according to steps S1-S4, but the obtained Key and Value are not stored in the sub-hash table HashTable. Instead, the Key is used to search in HashTable H3. If no record is found, S5.2 is executed; if a record is found, the position sequence information in the found Value is used as the position information of the base table, and the position sequence information in the existing Value is used as the position information of the table to be connected. The data rows at the corresponding positions are obtained from the two data tables respectively, and the Cartesian product of all the data rows is performed, and the result is added to the connection result set, and finally S5.3 is executed.

[0115] The overall process diagram of equivalue connection of the data in Table 1 and Table 2 is as follows: Figure 8 As shown, a possible result of hash sharding is assumed and is not intended to be limiting.

[0116] This embodiment improves the algorithm performance by reducing random access to memory during the equi-join process; reducing comparison operations on related data fields during multi-column equi-join processes; and reducing access operations to HashTable. It also improves the performance of the database equi-join algorithm by splitting the HashJoin task into subtasks and executing multiple subtasks in parallel to increase the parallelism of the equi-join algorithm.

[0117] Those of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory or optical memory, etc. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM).

[0118] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0119] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the attached claims.

Claims

1. A database parallel equivalent join method based on column storage format, characterized in that: The following steps are involved: Step 1: Determine whether all given equijoin conditions contain equijoin conditions involving more than or equal to 2 data tables. If not, directly filter the data of each data table in the order specified in the equijoin conditions, and then splice the data according to the filtering results and the equijoin conditions to obtain the connection result; if contained, execute step 2; Step 2: Select the data table with the least amount of data from the multiple data tables involved as the base table; Step 3: Hash the basic table according to the hash function, and divide the data in the basic table into several shards according to the hash result. Number each shard and assign it to several subtasks to concurrently build a sub-HashTable with the connection field value list as the key and the row location information list as the value. Hash the data table to be connected using the same hash function, and divide the data in the data table to be connected into several shards according to the hash result. Number each shard and assign it to several subtasks to concurrently build a sub-HashTable with the connection field value list as the key and the row position information list as the value, and perform hash detection concurrently; In each hash detection, according to the equal connection condition, the sub-HashTable of the data table to be connected with the corresponding number is used to search the sub-HashTable of the basic table with the corresponding number to obtain the position information sequence pair; Step 4: Based on the position information sequence pairs obtained by hash detection, concatenate the connection field values ​​of the corresponding position information of the base table and the data table to be connected to obtain the concatenated result. The set of all concatenated results is the final connection result.

2. According to the method for parallel equal join of a database based on a column storage format according to claim 1, it is characterized in that: In step 3, the sub-HashTable is constructed with the connection field value list as the key and the row position information list as the value. The specific operations include: S2.1: Take the first connection field to group and write the grouping result into the child HashTable; S2.2: Based on the grouping results currently stored in the sub-HashTable, the second connection field is grouped and the grouping results are written into the sub-HashTable; and so on, until all the connection fields are grouped and the final sub-HashTable is obtained.

3. The method for parallel equal join of a database based on a column storage format according to claim 2, characterized in that: The first connection field is taken for grouping, and the grouping result is written into the sub-HashTable. The specific operations include: S2.2.1: Determine whether there is an unused row position pos on the first connection field. If not, it means that the sub-HashTable already contains the grouping result of the first connection field; if so, execute S2.2.2; S2.2.2: Take the unused row position pos from the first connection field, and determine whether the connection field value at the row position pos, recorded as f[pos], can be found in the sub-HashTable. If not, create a new record {f[pos], row position pos}, add this new record to the sub-HashTable, and then execute S2.2.1; if so, search the sub-HashTable for the row position information list corresponding to the connection field value f[pos], add the row position pos to the row position information list, and then execute S2.2.

1.

4. The method for parallel equal join of a database based on a column storage format according to claim 3, characterized in that: The method of grouping the second connection field based on the grouping result currently stored in the sub-HashTable and writing the grouping result into the sub-HashTable includes: S2.3.1: Determine whether there is an unused record rec in the grouping result currently stored in the sub-HashTable. If yes, execute S2.3.2; if not, it means that the sub-HashTable contains the grouping result of the second connection field based on the grouping result currently stored in the sub-HashTable; S2.3.2: Take an unused record rec from the grouping results currently stored in the child HashTable, and determine whether the record rec contains only one row position information. If it contains only one row position information, record the row position information as POS, and append f[POS] to the Key of the record rec. f[POS] is the value of the second connection field at position POS, and then execute S2.3.1; if it contains multiple row position information, execute S2.3.3; S2.3.3: Determine whether there is unused row position information in the record rec. If not, execute S2.3.1; if yes, obtain the unused row position information POS1 from the record rec, and determine whether the value f[POS1] of the second connection field corresponding to the row position information POS1 can be found in the sub-HashTable. If not, execute S2.3.4; if so, search the sub-HashTable for the row position information list corresponding to the value f[POS1] of the second connection field, and add the row position information POS1 to the row position information list; S2.3.4: Create a new record {f[POS1], POS1} in the child HashTable and execute S2.3.

3.

5. The method for parallel equal join of a database based on a column storage format according to claim 3, characterized in that: In each hash detection, according to the equal connection condition, the sub-HashTable of the data table to be connected with the corresponding number is used to search the sub-HashTable of the basic table with the corresponding number to obtain the position information sequence pair. The specific operations include: For each equi-join condition involving the base table and the data table to be joined, take the join field value of the sub-HashTable of the data table to be joined in the equi-join condition in turn, and search the position information sequence corresponding to the join field value from the sub-HashTable of the base table. The position information sequence and the position information sequence of the join field value in the sub-HashTable of the data table to be joined constitute a position information sequence pair.

6. A computer device, characterized in that: The invention comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the steps of a database parallel equivalent connection method based on a column storage format as described in any one of claims 1 to 5 are implemented.

7. A storage medium, characterized in that: The storage medium stores an equi-connection program, and when the equi-connection program is executed by at least one processor, the steps of a database parallel equi-connection method based on a column storage format according to any one of claims 1 to 5 are implemented.

Images