A method for distributed database to perform hash join

Abstract

The application discloses a method for performing hash connection of a distributed database, and relates to the technical field of distributed databases, which comprises the following steps: setting a relative inclination rate, calculating the product of the relative inclination rate and table data volume to obtain an inclination threshold; obtaining a heat map of table statistical information, screening elements in the table which exceed the inclination threshold to obtain an inclination value; expanding the table statistical information by using the inclination value; executing an SQL statement to obtain two input data tables, generating a hash connection physical plan by using the new table statistical information, performing hash distribution, average distribution or mirror distribution on tuples in the input data tables according to the plan, and after each node receives the tuples of the input data tables, establishing a hash table by using input data table data with small data volume, performing detection by using input data table data with large data volume, and finally performing a set union on the hash connection results of each node, so that the set union is the final hash connection result. The application can realize balanced distribution of tasks and shorten the total SQL execution time.

Description

Technical Field

[0001] This invention relates to the field of distributed database technology, specifically a method for performing hash joins in a distributed database. Background Technology

[0002] Hash join is a common operation in databases used to analyze the relationships between data in two or more tables. It is a fundamental operation in both OLTP scenarios of various transaction models and OLAP scenarios of computational analysis models, and the hash join operator is also a fundamental operator.

[0003] Traditional hash join operators generally consist of two steps: for the smaller table, a hash table is built in memory based on the join columns; for the larger table, the hash table is checked one by one to detect the matches between the join columns in the larger table and those in the smaller table, and finally the result is output. Traditional hash joins deployed on a single machine also follow these steps.

[0004] For distributed databases, especially those with a Shared-Nothing architecture, their operation differs from single-machine hash joins in the following ways: The distribution of large and small table data differs from a single-machine scenario. The data may be distributed across different database nodes, each connected via a network and possessing a portion of the large or small table data, or both. This means that in a distributed environment, to leverage the parallel computing power of multiple nodes, the original large and small table data must be redistributed across the network to each database node. Each node then performs a hash join, and the final result is the aggregation of all nodes' data. Based on these characteristics, most distributed databases use hash redistribution to achieve parallel computing on each node, determining which node to distribute the original data to based on the hash value of the join column. However, hash redistribution introduces the following problems: Since the original data may contain skew values, these skewed values ​​with the same hash value may be distributed to the same node, causing an imbalance in workload among nodes. This means that one or more nodes may receive the majority of the data. Specifically, the nodes receiving more data will have longer connection completion times, causing other nodes to wait and slowing down the entire execution process. Therefore, how to handle skew is a key issue in improving the efficiency of hash joins in distributed databases.

[0005] Currently, regarding the handling of skew values, the invention patent application "A Large Table Join Optimization Method Based on Spark Computing Framework", publication number CN113868230A, specifically proposes a skew value detection mechanism based on reservoir sampling, and a skew optimization method that splits the data so that skewed data is sent to the faster processing node. Its purpose is to filter out a large amount of useless data when joining two large tables in the Spark computing framework, improve the data skew situation, shorten the join query time, and solve the memory overflow problem of Spark cluster nodes.

[0006] The published paper, Y. Xu, P. Kostamaa, X. Zhou, and L. Chen, "Handling data skew in parallel joins in shared-nothing systems," in SIGMOD, pages 1043–1052, 2008, proposes a skew optimization join method called "PRPD" that broadcasts partial data. In PRPD, skewed data in a table is redistributed using hashing, and the skewed data remains locally. The corresponding data that matches the skewed data in the relative table (another table participating in the join) is broadcast to all participating nodes. This method achieves good results under the assumption that the original data is ideally uniformly distributed across the nodes, but its optimization effect is limited for more complex data distributions. Summary of the Invention

[0007] This invention addresses the needs and shortcomings of current technological development by providing a method for performing hash joins in a distributed database.

[0008] The present invention provides a method for performing hash joins in a distributed database, and the technical solution adopted to solve the above-mentioned technical problems is as follows:

[0009] A method for performing hash joins in a distributed database includes the following steps:

[0010] To obtain the data sampling volume of table statistics in the distributed database, set a relative skew rate, calculate the product of the relative skew rate and the table data sampling volume, and obtain the skew threshold.

[0011] Obtain a heatmap of table statistics in a distributed database, and use the heatmap to filter elements in the table that exceed the skew threshold. The filtered elements are called skew values.

[0012] By utilizing skew values, the table statistics of a distributed database can be expanded to obtain new table statistics.

[0013] Execute SQL statements containing hash joins in a distributed database to obtain two input data tables. Use the new table statistics to generate a hash join physical plan. Based on the plan, perform hash distribution, average distribution, or mirror distribution on the tuples in the input data tables to send the tuples in the input data tables to the destination node according to the hash value, distribute them to all nodes according to the average probability, or copy them and send them to all nodes participating in the computation.

[0014] After each node receives the tuples from the input data table, it builds a hash table using the smaller input data table and performs probing using the larger input data table. Finally, the hash join results of each node are combined into a union, which is the final hash join result.

[0015] Optionally, execute SQL statements containing hash joins in the distributed database to obtain two input data tables, and generate a hash join physical plan using the new table statistics, specifically including:

[0016] When executing an SQL statement, two input tables are retrieved from the distributed database. The input table with a larger amount of data is called the R table, and the input table with a smaller amount of data is called the S table.

[0017] Based on the new table statistics, confirm whether the R and S tables contain skewed values:

[0018] (a) The skew values ​​in both the R and S tables are not empty.

[0019] (b) The skew values ​​in table R are not empty, and the skew values ​​in table S are empty.

[0020] (c) The skew values ​​in table R are empty, while the skew values ​​in table S are not empty.

[0021] (d) The skew values ​​in both the R and S tables are empty;

[0022] For cases (a) and (b), the non-skewed values ​​in the R and S tables are hashed, the skewed values ​​in the R table are averaged, and the skewed values ​​in the S table are mirrored.

[0023] For cases (c) and (d), the tuples in the R and S tables are directly hashed and distributed.

[0024] Further, optionally, a hash distributor, a mirror distributor, and an average distributor can be constructed, wherein,

[0025] The hash distributor is used to receive a tuple from the R table / S table and send it to the destination node according to the hash value of the tuple;

[0026] The mirror distributor is used to receive a tuple from table S and send it to all nodes participating in the computation;

[0027] The average distributor is used to receive a tuple from the R table and distribute the tuple randomly or in a round-robin fashion to the nodes participating in the computation according to the average probability.

[0028] Preferably, for cases (a) and (b),

[0029] The R-table read operator sequentially reads a tuple from the R-table and determines whether the tuple is a skew value of the R-table based on the new table statistics. If it is, the average distributor distributes the tuple to the participating nodes randomly or in a round-robin manner according to the average probability. If not, the hash distributor sends the tuple to the destination node according to the hash value of the tuple.

[0030] The S-table read operator sequentially reads a tuple from the S-table and determines whether the tuple is a skew value of the R-table based on the new table statistics. If it is, the mirror distributor sends the tuple to all participating nodes. If not, the hash distributor sends the tuple to the destination node according to the hash value of the tuple.

[0031] Preferably, for cases (c) and (d),

[0032] The R-table read operator sequentially reads a tuple from the R-table, and then the hash distributor sends the tuple to the destination node according to the hash value of the tuple.

[0033] The S-table read operator sequentially reads a tuple from the S-table, and then the hash distributor sends the tuple to the destination node according to the hash value of the tuple.

[0034] Optionally, obtain a heatmap of database table statistics, obtain the data sampling size N and the number of buckets M of the heatmap, and count the number of bucket boundary elements num. eq In the screening bucket The elements are defined by α, which represents the set relative tilt rate. The elements that are filtered out are the tilt values ​​that exceed the tilt threshold.

[0035] Preferably, the new table statistics are added to the system tables and cache.

[0036] Preferably, a uniform relative skew rate is set for all tables in the distributed database, and tables with different data sampling amounts have different skew thresholds based on the data sampling amount of the table statistics in the distributed database.

[0037] The method for performing hash joins in a distributed database according to the present invention has the following advantages compared with the prior art:

[0038] (1) This invention expands the skewed data in the table statistics of the distributed database and adopts different distribution methods according to whether the table contains skewed data, so that the execution time of each node is roughly balanced, avoiding the situation that the execution time of a few nodes is very skewed, thereby improving the SQL execution performance of the distributed database.

[0039] (2) The present invention expands skewed data in the table statistics of the distributed database, which does not add too much burden to the existing database. Attached Figure Description

[0040] Appendix Figure 1 This is a flowchart illustrating step (four) in Embodiment 1 of the present invention. Detailed Implementation

[0041] To make the technical solution, the technical problem solved, and the technical effect of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments.

[0042] Example 1:

[0043] This embodiment proposes a method for performing hash joins in a distributed database, including the following steps:

[0044] (i) Obtain the data sampling amount of table statistics in the distributed database, set a relative skewness α, calculate the product of the relative skewness α and the table data sampling amount, and obtain the skewness threshold.

[0045] (ii) Obtain a heatmap of table statistics from the distributed database, obtain the data sampling size N and the number of buckets M of the heatmap, and count the number of bucket boundary elements num. eq Using heatmaps to filter the buckets The elements that are filtered out are those whose skew values ​​exceed the skew threshold.

[0046] (iii) Use skew values ​​to expand the table statistics of the distributed database to obtain new table statistics.

[0047] Add the new table statistics to the system tables and cache.

[0048] (iv) First, execute SQL statements containing hash joins in the distributed database to obtain two input data tables. Then, use the new table statistics to generate a hash join physical plan, specifically including:

[0049] When executing an SQL statement, two input tables are retrieved from the distributed database. The input table with a larger amount of data is called the R table, and the input table with a smaller amount of data is called the S table.

[0050] Based on the new table statistics, confirm whether the R and S tables contain skewed values:

[0051] (a) The skew values ​​in both the R and S tables are not empty.

[0052] (b) The skew values ​​in table R are not empty, and the skew values ​​in table S are empty.

[0053] (c) The skew values ​​in table R are empty, while the skew values ​​in table S are not empty.

[0054] (d) The skew values ​​in both the R and S tables are empty.

[0055] Subsequently, in conjunction with the appendix Figure 1 For cases (a) and (b), the non-skewed values ​​in tables R and S are hashed and distributed, the skewed values ​​in table R are distributed evenly, and the skewed values ​​in table S are distributed by mirroring. For cases (c) and (d), the tuples in tables R and S are hashed and distributed directly. The operations to achieve this step are as follows:

[0056] (1) Construct a hash distributor to receive a tuple from table R / S and send it to the destination node according to the hash value of the tuple.

[0057] Construct a mirror distributor to receive a tuple from table S and send it to all nodes participating in the computation.

[0058] Construct an average distributor to receive a tuple from the R table and distribute the tuple randomly or in a round-robin fashion to the nodes participating in the computation according to the average probability.

[0059] (2) For cases (a) and (b), the R-table read operator sequentially reads a tuple from the R-table and determines whether the tuple is a skew value of the R-table based on the new table statistics. If it is, the average distributor distributes the tuple randomly or in a round-robin fashion to the participating nodes according to the average probability. If not, the hash distributor sends the tuple to the destination node according to the hash value of the tuple.

[0060] The S-table read operator sequentially reads a tuple from the S-table and determines whether the tuple is a skew value of the R-table based on the new table statistics. If it is, the mirror distributor sends the tuple to all participating nodes. If not, the hash distributor sends the tuple to the destination node according to the hash value of the tuple.

[0061] (3) For cases (c) and (d),

[0062] The R-table read operator sequentially reads a tuple from the R-table, and then the hash distributor sends the tuple to the destination node according to its hash value.

[0063] The S-table read operator sequentially reads a tuple from the S-table, and then the hash distributor sends the tuple to the destination node according to the hash value of the tuple.

[0064] Finally, after each node receives the tuples from the R and S tables, it uses the tuples from the S table to build a hash table and uses the tuples from the R table to perform probing. Finally, the results of the hash joins of each node are combined into a union, which is the final hash join result.

[0065] In this embodiment, the same relative skew rate is set for all tables in the distributed database. Based on the data sampling amount of the table statistics in the distributed database, it can be known that tables with different data sampling amounts have different skew thresholds.

[0066] For convenience, the implementation method of this embodiment will be referred to as the equal-distribution-broadcast method. A comparison will be made between the hash join method widely used in the CockroachDB database and the equal-distribution-broadcast method of this embodiment.

[0067] Taking four nodes as an example, with the relative skewness of the R and S tables set at 5%, the execution times shown in Table 1 are obtained.

[0068] node Node 1 Node 2 Node 3 Node 4 Equal-broadcast method 4.2s 4.5s 6s 4.6s Hash join method 517ms 585ms 9.3s 610ms

[0069] As shown in Table 1, the execution time of the equal-spread method is more even.

[0070] Taking four nodes as an example, the relative skewness rates of table R and table S are set to 5%, 10%, 20%, and 50%, respectively, resulting in the total execution time shown in Table 2.

[0071] relative inclination 5％ 10％ 20％ 50％ Equal-broadcast method 6.8s 25.6s 63.2s 98s Hash join method 10.4s 38.9s 89s 143s

[0072] As shown in Table 2, the relative skew rate has a significant impact on the total execution time of all nodes.

[0073] In summary, the distributed database hash join method of the present invention can make the execution time of each node roughly balanced, avoid the situation where the execution time of a few nodes is very skewed, and improve the SQL execution performance of the distributed database.

[0074] The above specific examples illustrate the principles and implementation methods of the present invention in detail. These embodiments are merely for the purpose of helping to understand the core technical content of the present invention. Based on the above specific embodiments of the present invention, any improvements and modifications made to the present invention by those skilled in the art without departing from the principles of the present invention should fall within the patent protection scope of the present invention.

Claims

1. A method for distributed database to perform hash join, characterized in that, The method comprises the following steps: Obtain the data sampling amount of table statistics in the distributed database, set a relative skew rate, calculate the product of the relative skew rate and the table data sampling amount, and obtain a skew threshold; Obtain a heat map of the table statistics in the distributed database, and use the heat map to filter elements in the table that exceed the skew threshold. The filtered elements are referred to as skew values; Expand the table statistics of the distributed database using the skew values to obtain new table statistics; Execute a SQL statement containing a hash join in the distributed database, obtain two input data tables, generate a hash join physical plan using the new table statistics, and distribute tuples in the input data tables according to the plan by hash distribution, average distribution, or mirror distribution, so as to send the tuples in the input data tables to destination nodes according to hash values, to all nodes according to average probability, or to all nodes participating in the calculation after replication; After each node receives the tuples in the input data tables, a hash table is established using the input data table with a small data amount, and the input data table with a large data amount is used for detection. Finally, the results of the hash join of each node are combined, and the combination is the final hash join result; During the execution of the above steps, the SQL statement containing the hash join is executed in the distributed database, two input data tables are obtained, and a hash join physical plan is generated using the new table statistics. Specifically, the steps include: When the SQL statement is executed, two input data tables are obtained from the distributed database. The input data table with a large data amount is referred to as an R table, and the input data table with a small data amount is referred to as an S table; Based on the new table statistics, it is determined whether the R table and the S table contain skew values: (a) The skew values of the R table and the S table are not empty, (b) The skew value of the R table is not empty, and the skew value of the S table is empty, (c) The skew value of the R table is empty, and the skew value of the S table is not empty, (d) The skew values of the R table and the S table are empty; For cases (a) and (b), the non-skew values of the R table and the S table are subjected to hash distribution, the skew values in the R table are subjected to average distribution, and the skew values in the S table are subjected to mirror distribution. For cases (c) and (d), the tuples in the R table and the S table are directly subjected to hash distribution.

2. The method for performing a hash join of a distributed database according to claim 1, wherein, A hash distributor, a mirror distributor, and an average distributor are constructed, wherein: The hash distributor is used to receive a tuple in the R table / S table, and send the tuple to a destination node according to the hash value of the tuple; The mirror distributor is used to receive a tuple in the S table, and send the tuple to all nodes participating in the calculation; The average distributor is used to receive a tuple in the R table, and randomly or round-robin distribute the tuple to the nodes participating in the calculation according to the average probability.

3. The method for performing a hash join of a distributed database according to claim 2, wherein, For cases (a) and (b), The R table reading operator sequentially reads a tuple in the R table, and determines whether the tuple is a skew value of the R table based on the new table statistics. If yes, the average distributor is used to randomly or round-robin distribute the tuple to the nodes participating in the calculation according to the average probability. If no, the hash distributor is used to send the tuple to a destination node according to the hash value of the tuple. The S table reading operator reads a tuple in the S table in sequence, and judges whether the tuple is a skew value of the R table based on new table statistical information. If yes, the mirror distributor is used to send the tuple to all nodes participating in the calculation. If not, the hash distributor is used to send the tuple to the destination node according to the hash value of the tuple.

4. The method for performing a hash join of a distributed database according to claim 2, wherein, For the cases of (c) and (d), The R table reading operator reads a tuple in the R table in sequence, and then uses the hash distributor to send the tuple to the destination node according to the hash value of the tuple. The S table reading operator reads a tuple in the S table in sequence, and then uses the hash distributor to send the tuple to the destination node according to the hash value of the tuple.

5. The method for performing a hash join of a distributed database according to claim 1, wherein, A heat map of database table statistics information is obtained, and a data sampling quantity of the heat map is obtained And the number of buckets M, the number of elements at the bucket boundary is counted , the elements in the screening bucket are screened , Indicates the set relative inclination rate, and the screened elements are inclination values exceeding the inclination threshold.

6. The method for performing a hash join of a distributed database according to claim 2, wherein, New table statistical information is added to the system table and the cache.

7. The method of claim 1, wherein the Hash Join execution method is detecting skew data. A same relative skew rate is set for all tables in the distributed database, and different tables have different skew thresholds based on the data sampling amount of the table statistical information in the distributed database.

Images