Method for correcting high-frequency value of statistical information of database

Abstract

The invention relates to the technical field of database management and optimization, in particular to a database statistical information high-frequency value correction method, which comprises the following steps of: carrying out probability estimation on initial MCV list inner values in sequence by adopting non-playback sampling; carrying out correction processing on the probabilities of all MCV values; comparing the lower bound row number of the confidence interval of the corrected value with the row number appearing in the sample data; if the line number in the sample data is greater than the lower limit of the confidence interval, the value is considered as MCV and is reserved; otherwise, removing the value from the initial MCV list; and the reserved MCV value forms a corrected MCV value list with smaller deviation. According to the method, the probability of the MCV is corrected by adopting the variance and the standard deviation of the hyper-geometric distribution, an MCV value list with smaller deviation can be obtained after correction processing, and the accuracy of MCV information is greatly improved compared with the prior art.

Description

Technical Field

[0001] The present invention relates to the technical field of database management and optimization, and in particular to a method for correcting high-frequency values ​​of database statistical information. Background Art

[0002] MCV is an important technique used in databases to optimize query performance. It records the most frequently occurring values ​​and their frequency in a column of a database table. These statistics are automatically collected and maintained by the database system to assist the query optimizer in more accurately estimating the cost of the query plan and thus selecting the most efficient execution plan.

[0003] The traditional method of estimating the MCV value assumes that the data distribution in the table is similar to the binomial distribution. The estimated MCV value only focuses on the proportion of the value in a single sampling. If the probability of A=1 in the sample is p, then the probability of A=1 in the whole is also p. This idea is based on the idea that the probability of taking out A=1 each time is equal, which is a sampling with replacement idea. The traditional estimation method takes a fixed value as the baseline. When the data is severely skewed, the deviation will be large. Summary of the invention

[0004] Technical issues solved

[0005] In view of the above-mentioned shortcomings of the prior art, the present invention provides a method for correcting high-frequency values ​​of database statistical information. The method adopts an idea that is more in line with the database statistical information sampling process, that is, each time a data is taken out, it will affect the probability of the next time it is taken out. In this way, the probability of the MCV value is calculated.

[0006] Technical Solution

[0007] To achieve the above objectives, the present invention is implemented through the following technical solutions:

[0008] The present invention provides a method for correcting high-frequency values ​​of database statistical information, the steps comprising:

[0009] Collect sample data from the data population, perform frequency statistics on the values ​​of each row of the sample data, and filter and form a preliminary MCV list;

[0010] And the probability of the values ​​in the preliminary MCV list was estimated sequentially using sampling without replacement;

[0011] Correct the probabilities of all MCV values;

[0012] Compare the number of rows of the lower bound of the confidence interval of the value after correction with the number of rows in which the value appears in the sample data;

[0013] If the number of rows in the sample data is greater than the lower limit of the confidence interval, the value is considered to be MCV and retained; otherwise, the value is removed from the preliminary MCV list;

[0014] The retained MCV values ​​constitute the list of less biased, corrected MCV values.

[0015] Furthermore, the correction processing step of the probability of the MCV value includes:

[0016] Calculate the estimated selectivity of this value;

[0017] Calculate the variance and standard deviation stddev of the hypergeometric distribution of this value;

[0018] Calculate the confidence interval of the frequency of the value in the population based on the variance variance and standard deviation stddev;

[0019] Determine the size of the selection rate select estimate and the lower bound low value of the confidence interval.

[0020] Furthermore, the estimated method for calculating the selection rate select is:

[0021] Assuming the current value is not the MCV value, estimate the selection rate at this time as non-MCV select;

[0022] non-MCV select=1 - the frequency of this value in the sample - the frequency of the null value;

[0023] Then, the select estimate of the value = non-MCV select / the number of remaining different values.

[0024] Further, the variance of the value is obtained to represent the degree of dispersion of the number of times the MCV value is successfully extracted in the preliminary MCV list;

[0025] Variance variance = n*K*(NK)*(Nn) / (N*N*(N-1))

[0026] Where n is the number of sample rows extracted, N is the total number of sample rows, and K is the number of times the MCV value is successfully extracted.

[0027] Furthermore, the standard deviation stddev of the value is the square root of the variance, representing the degree of fluctuation of the data.

[0028] Furthermore, the population frequency is set to be within 2 standard errors of the sample frequency, and the lower bound of the confidence interval is calculated:

[0029] low value = select estimated value * samplerows + 2 * stddev + correctio

[0030] Where samplerows is the number of sample rows and correctio is the continuity correction coefficient.

[0031] Furthermore, when the frequency calculation is performed on the value of each row of the sample data, when the total sample N contains K such values, the probability of sampling n rows and extracting k such values ​​is:

[0032]

[0033] is the permutation combination when n rows have been sampled from the population N, is the permutation combination when sampling k rows from the total K rows is the value, It is the permutation combination when the sampled nk rows in the total NK rows are not of this value.

[0034] Beneficial Effects

[0035] Compared with the known public technology, the technical solution provided by the present invention has the following beneficial effects:

[0036] This solution estimates the probability of the values ​​in the preliminary MCV list by sampling the sample data without replacement, and further uses the variance and standard deviation of the hypergeometric distribution to correct the probability of MCV. After the correction process, an MCV value list with smaller deviation can be obtained, which greatly improves the accuracy of MCV information compared with the existing technology, thereby better assisting in improving the performance and efficiency of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the prior art descriptions are briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention, and for ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0038] Figure 1 It is a flow chart of the high frequency value correction method of the present invention. DETAILED DESCRIPTION

[0039] In order to make the purpose, technical solution and advantages of the embodiments of the present invention clearer, the technical solution in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0040] The present invention will be further described below in conjunction with the embodiments.

[0041] Example:

[0042] The present invention provides a method for correcting high-frequency values ​​of database statistical information. The high-frequency value MCV is a kind of statistical information automatically collected and maintained by the database, which is used to record the most frequently occurring values ​​(i.e., high-frequency values) of a column or columns in a table and their respective frequencies of occurrence. This information is usually automatically calculated and stored by the database management system during data loading, updating or regular maintenance. The core correction mechanism of this scheme is as follows: since the sampling process of actual database statistical information is actually a table scanning process, the data taken out last time during the scanning process will not be put back into the table for further scanning, so the sampling process of database statistical information is more in line with the process of sampling without replacement; the distribution of the sampled data deviates from the real data, and a reasonable distribution curve is needed to correct the probability and improve the accuracy.

[0043] Specifically, the steps of this scheme to collect information statistics on the MCV value of the data set include:

[0044] S10: Collect sample data from the data population, perform frequency statistics on the values ​​of each row of the sample data, and filter and form a preliminary MCV list; the frequency statistics are automatically calculated and stored by the database management system when the data is loaded, updated or regularly maintained.

[0045] S20: Probability estimation is performed on the values ​​in the preliminary MCV list in turn using sampling without replacement; the frequency of the MCV value in the preliminary MCV list is used to approximately calculate the ratio of the number of rows that meet the conditions to the total number of rows, and this ratio can be regarded as a probability estimation of the query result.

[0046] S30: Correcting the probabilities of all MCV values ​​to improve the accuracy of the MCV values; specifically, the correction processing steps of the probabilities of the MCV values ​​include:

[0047] S31: Calculate the estimated value of the selectivity select of the value;

[0048] S32: Calculate the variance variance and standard deviation stddev of the hypergeometric distribution of the value;

[0049] S33: Based on the variance and standard deviation, calculate the confidence interval of the frequency of the value in the population; the lower bound of the confidence interval is calculated by the expected value and the standard deviation. It is used to determine whether it can become the MCV value.

[0050] S34: Determine the size of the selection rate select estimate and the lower bound low value of the confidence interval.

[0051] S40: Compare the number of rows of the lower limit of the confidence interval of the value after correction with the number of rows in which it appears in the sample data;

[0052] S50: If the number of rows in the sample data is greater than the lower limit of the confidence interval, the value is considered to be MCV and retained; otherwise, the value is removed from the preliminary MCV list;

[0053] S60: The retained MCV values ​​form a list of corrected MCV values ​​with smaller deviations.

[0054] Among them, as a basic explanation, sampling without replacement is a sampling method. When the non-repetitive sampling method is used, the number of units in the population gradually decreases during the sampling process, and the probability of each unit in the population being selected is different. Sampling without replacement also refers to the sampling method in which the entire sample is drawn at the same time; the sampling process of database statistics is a table scanning process, which belongs to the sampling process without replacement.

[0055] In this embodiment, the method for calculating the estimated value of the selection rate select in step S31 is specifically as follows:

[0056] First, assume that all current values ​​are not MCV values ​​and estimate the selection rate non-MCV select at this time;

[0057] non-MCV select = 1 - the frequency of this value in the sample - the frequency of the null value; subtract the frequency of the MCV value and the null value from 1 to get the total frequency of non-MCV and non-null values. This can be seen as the sum of the frequencies of all values ​​(regardless of whether they are the same or not) except MCV and null.

[0058] The selectivity is then further estimated by dividing by the number of distinct values ​​remaining,

[0059] The select estimate of this value = non-MCV select / number of remaining different values.

[0060] Here, the number of remaining distinct values ​​is the sum of the number of remaining distinct values ​​excluding MCV and null values.

[0061] In step S32, when n rows of samples are extracted from the total number of rows N containing MCV values ​​(most common values), the variance (Variance) and standard deviation (Standard Deviation) of the MCV values ​​successfully extracted K times are obtained, wherein the variance of the value obtained is used to represent the degree of dispersion of the number of times the MCV values ​​are successfully extracted in the preliminary MCV list;

[0062] Variance variance = n*K*(NK)*(Nn) / (N*N*(N-1))

[0063] Where n is the number of sample rows extracted, N is the total number of sample rows, and K is the number of successful MCV value extractions. Specifically, assuming that the frequency of the MCV value in the sample is equal to its frequency in the whole:

[0064] K = N * mcv_counts [num_mcv-1] / n

[0065] After obtaining the variance of the value, the standard deviation stddev of the value is the square root of the variance, which represents the degree of fluctuation of the data.

[0066] In step S33, the lower bound low of the continuity-corrected confidence interval is calculated. In this embodiment, the overall frequency is set to be within 2 standard errors of the sample frequency, and the lower bound low value of the confidence interval is calculated:

[0067] low value = select estimated value * samplerows + 2 * stddev + correctio

[0068] Among them, samplerows is the number of sample rows, correctio is the continuity correction coefficient, and is often a very small constant used to adjust the approximation of discrete data to continuous data.

[0069] In addition, in step S20, when the present embodiment adopts the sampling without replacement method to estimate the probability of the MCV value, when the frequency of the value of each row of the sample data is calculated, when the total sample N contains K such values, the probability of sampling n rows and extracting k such values ​​is:

[0070]

[0071] is the permutation combination when n rows have been sampled from the population N, is the permutation combination when sampling k rows from the total K rows is the value, It is the permutation combination when the sampled nk rows in the total NK rows are not of this value.

[0072] The following is an example of a specific analysis of the probability distribution calculation formula for the above MCV calculation scenario:

[0073] Assume that there is a table Table 1 with a total number of N rows, A=1 and there are K rows. We sample 1 row each time for a total of n times.

[0074]

[0075]

[0076] Table 1

[0077] First of all, the samples extracted from the database should be sampled without replacement, because each time a part of the sample is taken out, it will not be put back into the whole for calculation, and the final value of the sample is unknown during the process.

[0078] Each time a row is extracted, whether A=1 or A!=1, it will affect the data distribution when the next row is extracted. The specific derivation process is as follows:

[0079] 1. Sampling row 1, current sampling row

[0080] The probability of A=1 is: P(A=1)=K / N;

[0081] The probability of A!=1 is: P(A!=1)=(NK) / N;

[0082] 2. Sample the second row,

[0083] When A=1 in the first row, the probability that A=1 in the current sampling row is: P(A=1)=(K / N)*((K-1) / (N-1));

[0084] When A!=1 in the first row, the probability that A=1 in the current sampling row is: P(A=1)=((NK) / N)*(K / (N-1));

[0085] 3. Sample the 3rd row,

[0086] When A=1 in both the first and second rows: P(A=1)=(K-2) / (N-2);

[0087] When A!=1 in both the 1st and 2nd rows: P(A=1)=((NK) / N)*((NK-1) / (N-1))*K / (N-2);

[0088] When A=1 in the first row and A!=1 in the second row, P(A=1)=K / N*(K / N)*((NK-1) / (N-1))*(K-1) / (N-2);

[0089] Similarly, when the second row is A=1, but the first row is A!=1, we can also get a probability P(A=1);

[0090] Now we can find that both the first and second rows may have A=1 or A!=1, which is a permutation and combination. It is too complicated to express it by formula, so we can simply express it by permutation and combination.

[0091] When sampling the first row, we actually take a row from N rows, and use permutations and combinations to represent it. There is one row where A=1, which can be expressed using permutations and combinations. So

[0092]

[0093] When sampling the second row, it is equivalent to taking 2 rows from N rows, which can be expressed using permutations and combinations.

[0094]

[0095] If both rows have A=1, then we can express it using permutations and combinations. So

[0096]

[0097] If 1 row is A=1, 1 row is A!=1, using permutations and combinations to express it is So

[0098] In summary, we can get the probability distribution of sampling n rows and extracting k A=1 when the total sample N contains K A=1:

[0099] This solution estimates the probability of the values ​​in the preliminary MCV list by sampling the sample data without replacement, and further uses the variance and standard deviation of the hypergeometric distribution to correct the probability of MCV. After the correction process, an MCV value list with smaller deviation can be obtained, which greatly improves the accuracy of MCV information compared with the existing technology, thereby better assisting in improving the performance and efficiency of the database.

[0100] The above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements will not cause the essence of the corresponding technical solutions to deviate from the protection scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for correcting high-frequency values ​​of database statistical information, characterized in that: include: Collect sample data from the data population, perform frequency statistics on the values ​​of each row of the sample data, and filter and form a preliminary MCV list; Sampling without replacement was used to estimate the probability of the values ​​in the preliminary MCV list in sequence; Correct the probabilities of all MCV values; Compare the number of rows of the lower bound of the confidence interval of the value after correction with the number of rows in which the value appears in the sample data; If the number of rows in the sample data is greater than the lower limit of the confidence interval, the value is considered to be MCV and retained; otherwise, the value is removed from the preliminary MCV list; The retained MCV values ​​constitute the list of less biased, corrected MCV values.

2. A method for correcting high-frequency values ​​of database statistical information according to claim 1, characterized in that: The correction processing step of the probability of the MCV value includes: Calculate the estimated selectivity of this value; Calculate the variance and standard deviation stddev of the hypergeometric distribution of the value; Calculate the confidence interval of the frequency of the value in the population based on the variance variance and standard deviation stddev; Determine the size of the selection rate select estimate and the lower bound low value of the confidence interval.

3. A method for correcting high-frequency values ​​of database statistical information according to claim 2, characterized in that: The process of calculating the estimated value of the selection rate select is: Assuming the current value is not the MCV value, estimate the selection rate at this time as non-MCV select; non-MCV select=1 - the frequency of this value in the sample - the frequency of the null value; Then, the select estimate of the value = non-MCV select / the number of remaining different values.

4. A method for correcting high-frequency values ​​of database statistical information according to claim 2, characterized in that: Get the variance of the value, which is used to represent the degree of dispersion of the number of successful MCV value extractions in the preliminary MCV list; Variance variance = n*K*(NK)*(Nn) / (N*N*(N-1)) Where n is the number of sample rows extracted, N is the total number of sample rows, and K is the number of times the MCV value is successfully extracted.

5. A method for correcting high-frequency values ​​of database statistical information according to claim 2, characterized in that: The standard deviation of this value, stddev, is the square root of the variance, which represents the degree of fluctuation of the data.

6. A method for correcting high-frequency values ​​of database statistical information according to claim 2, characterized in that: Assuming the population frequency is within 2 standard errors of the sample frequency, calculate the lower bound of the confidence interval: low value = select estimated value * samplerows + 2 * stddev + correctio Where samplerows is the number of sample rows and correctio is the continuity correction coefficient.

7. A method for correcting high-frequency values ​​of database statistical information according to claim 1, characterized in that: When the frequency calculation is performed on the value of each row of the sample data, when the total sample N contains K such values, the probability of sampling n rows and extracting k such values ​​is: is the permutation combination when n rows have been sampled from the population N, is the permutation combination when sampling k rows from the total K rows is the value, It is the permutation combination when the sampled nk rows in the total NK rows are not of this value.

Images