A bloom filter configuration method for hash join

Abstract

The application provides a Bloom filter configuration method for a hash connection, comprising: sampling initial data, analyzing the initial data, dynamically analyzing a variety of change trends, fitting a change trend model, constructing a Bloom filter, evaluating a Bloom filter size, and adjusting a Bloom filter configuration. The application combines probability statistics and mathematical modeling to dynamically analyze part of data sets and estimate code value inclination, uses a change slope and a coefficient of variation to judge a stable trend of data, uses Hausdorff Distance to select an optimal model to estimate a Bloom filter, finally proposes different configuration Bloom filter strategies, efficiently configures a Bloom filter size, fully utilizes memory resources, and improves overall query efficiency of the hash connection.

Description

Technical Field

[0001] This invention relates to the field of computer data storage technology, and in particular to a Bloom filter configuration method for hash joins. Background Technology

[0002] Hash joins have been extensively studied over the past two decades and are currently commonly used in commercial database systems to implement equi-join queries. The basic idea behind hash joins is to build a hash table on the smaller data table (driving table) and then search for matching tuples from the larger data table (driven table) within the hash table. The hash table needs to reside in memory, and its size and the number of lookups are the main factors affecting the efficiency of hash joins. Therefore, optimizing the performance of hash join algorithms focuses on optimizing memory access.

[0003] Proper use of Bloom filters can optimize hash join algorithms. The main idea is to reduce the number of hash lookups and disk I / O by using Bloom filters. The advantages of Bloom filters are high space efficiency and fast query speed. The disadvantage is that there is a certain false positive rate, but this false positive rate can be changed by adjusting parameters such as the size of the Bloom filter.

[0004] The size of the Bloom filter configuration greatly affects the efficiency of hash joins: if the Bloom filter is set too small, the false positive rate increases, the Bloom filter cannot effectively filter the data of the driven table, and there is additional overhead in building the query Bloom filter, which ultimately leads to a decrease in hash join efficiency; if the Bloom filter is set too large, although the Bloom filter can effectively filter the data of the driven table, the reduction in available memory of the hash table may lead to multiple disk I / O operations on the data of the driving table, which ultimately reduces the efficiency of hash joins.

[0005] Therefore, configuring a suitable Bloom filter is a key consideration during the hash join process. Summary of the Invention

[0006] To address the shortcomings of existing technologies, this invention provides a Bloom filter configuration method for hash joins.

[0007] The technical solution of this invention is: a Bloom filter configuration method for hash joins, comprising the following steps:

[0008] S1) Sample the initial data, construct an expandable hash table H, and count the current number of categories for every x data entries read in, and record the number of categories by constructing a vector c.

[0009] S2) Based on the vector c constructed in step S1), use linear fitting to construct an initial trend model and calculate the slope k1 of the fitted line; if P1 (P1 < N) driving table records are read in or the hash table size is expanded to H1 (H1 < M), the analysis is complete; where N is the total number of driving table records and M is the available memory allocated by the system to the hash connection.

[0010] S3) Construct a trend model Dx based on the current number of species vector cx, dynamically analyze the trend of the number of species, and then execute the process judgment;

[0011] S4) Fitting the trend model; constructing the current species number change model D' using all records of c vector and cx vector; fitting the current species number change model D' with the base model to obtain candidate estimation models: D1 (linear model), D2 (logarithmic curve model), and D3 (combination of linear and logarithmic curve models); using Hausdorff Distance as the scoring index goal of model similarity, selecting the candidate estimation model with the smallest goal as the final estimation model:

[0012]

[0013] Where A = {D1, D2, D3}, B = D';

[0014] S5) Estimate the number of driving table categories c' based on the final estimation model from step S4); calculate the Bloom filter size B':

[0015] B' = g(c',f,h)×α;

[0016] Where α (α > 1) is the fault tolerance factor, which reduces the possibility of underestimating the Bloom filter; construct the Bloom filter; traverse the hash table data and insert into the Bloom filter;

[0017] S6) Read in N records from the driving table, count the actual number of categories c0, and use the function from step S3) to calculate the theoretically optimal Bloom filter size B0:

[0018] B0 = g(c0,f,h);

[0019] Evaluate the error of the Bloom filter size B', if The Bloom filter estimation error is large, so proceed to step S7);

[0020] like If the Bloom filter estimation is successful, skip step S7 and continue executing the hash join algorithm;

[0021] S7), Adjust the Bloom filter configuration.

[0022] Preferably, step S3) involves the following steps to dynamically analyze the trend of changes in the number of species:

[0023] S31) Continuously read in the driving table data, record the number of categories cx[i] read in the current pass, and construct the category number vector cx;

[0024] S32) Linearly fit the current category number vector cx and construct a trend model Dx to estimate the number of categories c' driving the table data:

[0025] c' = Dx(cx);

[0026] S33) Calculate the current Bloom filter size Bx based on the estimated number of categories c' from step S32:

[0027] Bx = g(c',f,h);

[0028] Where Bx = g(c',f,h) is a function that estimates the size using three parameters, where c' is the number of record types in the driving table estimated in step S32), f is the expected false positive rate of the Bloom filter, and h is the number of hash functions in the Bloom filter;

[0029] S34) Calculate the slope vector kx of the current change in the number of species, where kx[i];

[0030]

[0031] S35), Analyze the changing trend of the number of categories;

[0032] S36) Execution process judgment.

[0033] Preferably, in step S35), the trend of change in the number of species is determined to be stable if the following condition is met:

[0034] 1) The statistical sampling model determines that the current estimated c' meets the conditions; the probability p of currently drawing cx[i] is calculated as follows:

[0035] p = p(ix, cx[i], c', N);

[0036] Where p = p(ix, cx[i], c', N) is a function that uses four parameters to calculate the probability of extracting cx[i], where i is the current number of passes, ix is ​​the number of drive table records read in the current pass, cx is the number of types of records in the current pass, c' is the number of types of drive table records estimated in step S32), and N is the number of drive table records.

[0037] If p < pthreshold, the current estimated number of species c' is incorrect and does not meet the current condition.

[0038] If p > pthreshold, the current condition is satisfied;

[0039] 2) If the coefficient of variation v of the slope of the continuous l-segment is lower than the variation threshold vthreshold, i.e. v < vthreshold, it is determined that the dispersion of the current l-segment data is low and the condition is met.

[0040] 3) In step S34), record the average value of the vector kx representing the slope of the change in the number of species. The relationship between the slope k1 of the line in step S2) and the following is satisfied: Then the current condition is met;

[0041] Where, kthreshold=T(ix,N) is a function that calculates the threshold of change slope using two parameters, where i is the current pass number, ix is ​​the current number of records read from the driving table, and N is the total number of records in the driving table.

[0042] Preferably, the coefficient of variation v satisfies the following relationship:

[0043]

[0044] in, Let be the average slope of segment l, and kx be the vector of the slope of the change in the number of species recorded in step S34).

[0045] Preferably, in step S36), if one of the following conditions is met, then step S4) is executed; otherwise, step S3) is executed.

[0046] 1) Read in P2 records of the driving table, where P1 < P2 < N;

[0047] 2) The current hash table size Hx and the estimated Bloom filter size Bx satisfy Hx + Bx ≈ M;

[0048] 3) All conditions in step S35) are satisfied.

[0049] Preferably, in step S4), the basic model is a logarithmic curve model, a straight line model, or a combination of a straight line and a logarithmic curve model. During the fitting process, the skewness and randomness of the search code values ​​of the data are fully considered. The skewness of the search code refers to multiple data mappings having the same search code value, and the randomness of the data with the same search code value is randomly distributed.

[0050] The data search code value skewed approximates random distribution in the logarithmic curve model described herein;

[0051] The linear model data has a non-skewed search code value, while the combined linear and logarithmic curve model data has a skewed search code value and a non-random distribution.

[0052] Preferably, in step S7), if B' > B0, then the estimated Bloom filter size B' is used.

[0053] If B' < B0, reconstruct the Bloom filter. The current hash table data is divided into two parts: one part is written to disk, and the saved memory and the original estimated Bloom filter memory are used to reconstruct Bloom filter B. e The remaining portion continues to be stored in the in-memory hash table H. e The hash table size H e Size B of Bloom filter e Satisfying H e +B e ≤M.

[0054] Preferably, in step S7), if B' < B0, the specific steps include:

[0055] S71) Analyze the current hash table data and calculate which part of the data is retained in memory, satisfying condition H. e +B e ≤M;

[0056] S72) Write part of the hash table data to disk, reconstruct the Bloom filter, and reuse the remaining hash table data in memory to insert into the Bloom filter.

[0057] The beneficial effects of this invention are as follows:

[0058] 1. This invention combines probability statistics and mathematical modeling to dynamically analyze a portion of the dataset and predict its code value skew. It uses the slope of change and coefficient of variation to determine the stable trend of the data. It uses HausdorffDistance to select the best model to estimate the Bloom filter and finally proposes different strategies for configuring the Bloom filter, which can efficiently configure the size of the Bloom filter, make full use of memory resources, and improve the overall query efficiency of hash join. Attached Figure Description

[0059] Figure 1 This is a flowchart of the configuration method of the present invention;

[0060] Figure 2 This is a schematic diagram illustrating memory contention for the hash table and Bloom filter in this invention.

[0061] Figure 3 This is an example diagram of linear fitting in this invention;

[0062] Figure 4 This is an example diagram of logarithmic curve fitting according to the present invention; Detailed Implementation

[0063] The specific embodiments of the present invention will be further described below with reference to the accompanying drawings:

[0064] Example

[0065] like Figure 1 and 2 As shown, this embodiment provides a Bloom filter configuration method for hash joins. The database management system (DBMS) allocates M available memory to the hash join, which is sufficient to hold the Bloom filter B. Each time x records are read, the number of records in the driving table is N. Specifically, the method includes the following steps:

[0066] S1) Sample the initial data, construct an expandable hash table H, and count the current number of categories for every x data entries read in, and record the number of categories by constructing a vector c.

[0067] S2) Based on the vector c constructed in step S1), an initial trend model is built using linear fitting, and the slope k1 of the fitted line is calculated; if P1 (P1 < N) driving table records are read in or the hash table size is expanded to H1 (H1 < M), the analysis is completed; in this embodiment, P1 = 0.02N and H1 = 0.1M are set; indicating that at most 2% of the driving table records are allowed to be read in during the sampling phase, or the memory occupied by the currently read driving table records is... Once the above conditions are met, the initial sampling and analysis phase ends, and the above data is used to construct an initial trend model.

[0068] S3) Based on the current category number vector cx, construct a trend model Dx, dynamically analyze the trend of category number changes, and then execute the process judgment; specifically:

[0069] S31) Continuously read in the driving table data, record the number of categories cx[i] read in the current pass, and construct the category number vector cx;

[0070] S32) Linearly fit the current category number vector cx and construct a trend model Dx to estimate the number of categories c' driving the table data:

[0071] c' = Dx(cx);

[0072] S33) Calculate the current Bloom filter size Bx based on the estimated number of categories c' from step S32:

[0073] Bx = g(c',f,h);

[0074] Where Bx = g(c',f,h) is a function for estimating the size using three parameters. In this embodiment, the following formula is used:

[0075]

[0076] Where c' is the number of record types in the driving table estimated in step S32), f is the expected false positive rate of the Bloom filter, and h is the number of hash functions in the Bloom filter;

[0077] S34) Calculate the slope vector kx of the current change in the number of species, where kx[i];

[0078]

[0079] S35) Analyze the trend of change in the number of species; if the following conditions are met, determine that the trend of change in the number of species is stable:

[0080] 1) The statistical sampling model determines that the current estimated c' meets the conditions; the probability p of currently drawing cx[i] is calculated as follows:

[0081] p = p(ix, cx[i], c', N);

[0082] Where p = p(ix, cx[i], c', N) is a function that calculates the probability of drawing cx[i] using four parameters. In this embodiment, p = P(A1∪A2∪A3…∪A) is used. cx [ i ]),

[0083] Where A represents the probability of drawing a specified type from the current ix records, and the above formula represents the probability of drawing cx[i] types from the current ix records;

[0084] i is the current pass number, ix is ​​the current number of driver table records read in, cx is the number of record types in the current pass, c' is the number of driver table record types estimated in step S32), and N is the total number of driver table records.

[0085] If p < pthreshold, the current estimated number of species c' is incorrect and does not meet the current condition.

[0086] If p > pthreshold, the current condition is satisfied;

[0087] In this embodiment, pthreshold = 0.05 is set, indicating that the probability of the current result is less than 5%, and there is reason to reject the current hypothesis, thus judging that the current estimated number of species c' is incorrect;

[0088] 2) If the coefficient of variation v of the slope of the continuous l-segment is lower than the variation threshold vthreshold, i.e. v < vthreshold, it is determined that the current l-segment data has low dispersion and meets the condition. In this embodiment, vthreshold = 0.1 is set as the weak variation threshold and l = 20. When v < 0.1, it is determined that the current data has weak variation and low dispersion, and meets the condition.

[0089] Wherein, the coefficient of variation v satisfies the following relationship:

[0090]

[0091] in, The average slope of segment l is given, and the vector kx is the vector recording the slope of the change in the number of species in step S34).

[0092] 3) In step S34), record the average value of the vector kx representing the slope of the change in the number of species. The relationship between the slope k1 of the line in step S2) and the following is satisfied: Then the current condition is met;

[0093] Where, kthreshold=T(ix,N) is a function that calculates the threshold of the slope of change using two parameters. In this embodiment, it takes the following form:

[0094]

[0095] Where i is the current pass number, ix is ​​the current number of records read from the driver table, and N is the total number of records in the driver table.

[0096] S36) Execute the process judgment. If one of the following conditions is met, then execute step S4); otherwise, continue to execute step S3.

[0097] 1) Read in P2 driving table records, where P1 < P2 < N; in this embodiment, P2 = 0.3N is set, indicating that at most 30% of the driving table records are allowed to be read in for analysis during the dynamic analysis phase;

[0098] 2) The current hash table size Hx and the estimated Bloom filter size Bx satisfy Hx + Bx ≈ M; Figure 2 This shows the memory contention between the hash table and the Bloom filter;

[0099] 3) All conditions in step S35) are satisfied.

[0100] S4) Fitting the trend model; constructing the current species number change model D' using all records of c vector and cx vector; fitting the current species number change model D' with the base model to obtain candidate estimation models: D1 (linear model), D2 (logarithmic curve model), and D3 (combination of linear and logarithmic curve models); using Hausdorff Distance as the scoring index goal of model similarity, selecting the candidate estimation model with the smallest goal as the final estimation model:

[0101]

[0102] Where A = {D1, D2, D3}, B = D';

[0103] S5) Estimate the number of driving table categories c' based on the final estimation model from step S4); calculate the Bloom filter size B':

[0104]

[0105] Where α (α > 1) is the fault tolerance factor, which reduces the possibility of underestimating the Bloom filter; construct the Bloom filter; traverse the hash table data and insert into the Bloom filter;

[0106] S6) Read in N records from the driving table, count the actual number of categories c0, and use the function from step S3) to calculate the theoretically optimal Bloom filter size B0:

[0107] B0 = g(c0,f,h);

[0108] Evaluate the error of the Bloom filter size B', if The Bloom filter estimation error is large, so proceed to step S7);

[0109] like If the Bloom filter estimation is successful, skip step S7) and continue executing the hash join algorithm; in this embodiment, Bthreshold=4 is set, indicating that if the estimated Bloom filter size differs from the theoretical optimal Bloom filter size by more than 4 times, the Bloom filter estimation error is considered large;

[0110] S7), Adjust the Bloom filter configuration.

[0111] In a preferred embodiment, in step S4), the basic model is a logarithmic curve model, a straight line model, or a combination of a straight line and a logarithmic curve model. During the fitting process, the skewness and randomness of the search code values ​​of the data are fully considered. The skewness of the search code refers to multiple data mappings having the same search code value, and the randomness of the data with the same search code value is randomly distributed. Figure 3 , Figure 4 Example graphs of logarithmic curves and linear fitting are shown;

[0112] The data search code value skewed approximates random distribution in the logarithmic curve model described herein;

[0113] The linear model data has a non-skewed search code value, while the combined linear and logarithmic curve model data has a skewed search code value and a non-random distribution.

[0114] In a preferred embodiment, in step S7), if B' > B0, the estimated Bloom filter size B' is used.

[0115] If B' < B0, reconstruct the Bloom filter. The current hash table data is divided into two parts: one part is written to disk, and the saved memory and the original estimated Bloom filter memory are used to reconstruct Bloom filter B. e The remaining portion continues to be stored in the in-memory hash table H. e The hash table size H e Size B of Bloom filtere Satisfying H e +B e ≤M.

[0116] In a preferred embodiment, step S7) if B' < B0 specifically includes the following steps:

[0117] S71) Analyze the current hash table data and calculate which part of the data is retained in memory, satisfying condition H. e +B e ≤M;

[0118] S72) Write part of the hash table data to disk, reconstruct the Bloom filter, and reuse the remaining hash table data in memory to insert into the Bloom filter.

[0119] The embodiments and descriptions above are merely illustrative of the principles and preferred embodiments of the present invention. Various changes and modifications may be made to the present invention without departing from its spirit and scope, and all such changes and modifications fall within the scope of the present invention as claimed.

Claims

1. A Bloom filter configuration method for hash joins, characterized in that, Includes the following steps: S1) Sample initial data and construct a scalable hash table. Each time it is read Data points, count the current number of categories, and construct vectors. Number of record categories; S2), the vector constructed based on step S1). An initial trend model is constructed using linear fitting, and the slope of the fitted line is calculated. ; If read in Expanding the number of records in the drive table or the size of the hash table to [a new size]. Then the analysis is complete; among them, , To drive the total number of records in the table, Allocate the available memory for the hash join by the system; S3) Based on the current category number vector Building a trend model It dynamically analyzes the changing trend of the number of categories, and then executes the process judgment; S4), Fitting the trend model; using vector, Construct a model of the change in the number of categories using all records in the vector. Current model of changes in the number of categories The candidate estimation model is obtained by fitting the model with the base model: It is a linear model. Logarithmic curve model It is a combination model of linear and logarithmic curves; the Hausdorff Distance is used as the scoring metric for model similarity, and the candidate estimation model with the smallest goal is selected as the final estimation model. in, ; S5) Estimate the number of driving table types based on the final estimation model from step S4). ; Calculate the size of the Bloom filter : ； in, As a fault tolerance factor, Reduce the likelihood that the estimated Bloom filter is too small; construct the Bloom filter; traverse the hash table data and insert data into the Bloom filter; S6) Read N records from the driving table and count the actual number of categories. The theoretically optimal Bloom filter size is calculated using the function in step S3). : ； Evaluate Bloom filter size Error, if The Bloom filter estimation error is large, so proceed to step S7. like If the Bloom filter estimation is successful, skip step S7 and continue executing the hash join algorithm; S7), Adjust the Bloom filter configuration; Dynamic analysis of the trend of changes in the number of categories includes the following steps: S31) Continuously read data from the driving table and record the number of types read in the current pass. Construct a vector of the number of species ; S32) Linear fitting of the current category number vector And construct a trend model Estimate the number of categories in the driving table data. : ； S33), the number of categories estimated based on step S32). Calculate the current Bloom filter size : ； in, This is a function that estimates the size using three parameters, where The number of record types in the driving table estimated in step S32). This represents the expected false positive rate of the Bloom filter. The number of Bloom filter hash functions; S34) Calculate the slope vector of the current change in the number of species. ,in : ； S35), Analyze the changing trend of the number of categories; S36) Execution process judgment.

2. The Bloom filter configuration method for hash-join-oriented operation according to claim 1, characterized in that: In step S35), if the following condition is met, the trend of the number of species is considered stable: 1) Statistical sampling model determines the current estimate Meets the criteria; calculate the current extraction. probability for: ； in, To calculate extraction using four parameters A function of probability, where, This is the current number of trips. This represents the number of records currently read from the driving table. This represents the number of categories in the current pass. The number of record types in the driving table estimated in step S32). This represents the total number of records in the driving table. like Determine the current estimated number of categories. Error, current conditions are not met; like The current conditions are met; 2) Continuous Coefficient of variation of the slope of the segment change Below the mutation threshold ,Right now , Determine the current The segment data has low dispersion, thus meeting the condition; 3) The slope of the change in the number of species in step S34) Vector average The slope of the straight line in step S2) Relationship satisfies: If so, then the current condition is satisfied; in, This is a function that calculates the threshold of the slope of change using two parameters, where, This is the current number of trips. This represents the number of records currently read from the driving table. This represents the total number of records in the driving table.

3. The Bloom filter configuration method for hash-join-oriented operation according to claim 2, characterized in that: The coefficient of variation v satisfies the following relationship: ； in, for The average slope of the segment change, For step S34), record the vector of the slope of the change in the number of species.

4. The Bloom filter configuration method for hash-join-oriented operation according to claim 1, characterized in that: In step S36), if one of the following conditions is met, then step S4) is executed; otherwise, step S3) is executed. 1) Read in The number of records in the driving table, of which, ; 2) Current hash table size Estimate the size of the Bloom filter. satisfy ; 3) All conditions in step S35) are satisfied.

5. A Bloom filter configuration method for hash-join-oriented operation according to claim 1, characterized in that: In step S4), the basic model is a logarithmic curve model, a straight line model, or a combination of a straight line and a logarithmic curve model. During the fitting process, the skewness and random distribution of the search code values ​​of the data are fully considered. Search code skewness refers to multiple data mappings having the same search code value, and random distribution refers to the random distribution of data with the same search code value. The logarithmic curve model described above corresponds to the case where the search code value of the data is skewed and approximately randomly distributed. The linear model corresponds to a situation where the search code value is not skewed, while the combined linear and logarithmic curve model corresponds to a situation where the search code value is skewed and not randomly distributed.

6. The Bloom filter configuration method for hash-join-oriented operation according to claim 1, characterized in that: In step S7), if Then, the estimated Bloom filter size will be used. .

7. A Bloom filter configuration method for hash-join-oriented processing according to claim 6, characterized in that: In step S7), if The Bloom filter is reconstructed. The current hash table data is divided into two parts: one part is written to disk, and the saved memory and the memory originally estimated for the Bloom filter are used to reconstruct the Bloom filter. The remaining portion continues to be stored in an in-memory hash table He, where the hash table size He is equal to the Bloom filter size. satisfy .

8. A Bloom filter configuration method for hash-join-oriented operation according to claim 7, characterized in that: In step S7), if Specifically, it includes the following steps: S71) Analyze the current hash table data and calculate which part of the data is retained in memory, satisfying the following conditions. ; S72) Write part of the hash table data to disk, reconstruct the Bloom filter, and reuse the remaining hash table data in memory to insert into the Bloom filter.

Images