A method for correcting high-frequency values of database statistical information

Abstract

The present invention relates to the technical field of database management and optimization, and particularly relates to a method for correcting high-frequency values of database statistical information. The method uses sampling without replacement to estimate the probabilities of values in the preliminary MCV list in sequence; corrects the probabilities of all MCV values; compares the lower bound number of rows of the confidence interval of the corrected value 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 an MCV and retained; otherwise, the value is removed from the preliminary MCV list; the retained MCV values form a corrected MCV value list with a smaller deviation. The variance and standard deviation of the hypergeometric distribution are used to correct the probability of the MCV. After the correction process, an MCV value list with a smaller deviation can be obtained, greatly improving the accuracy of MCV information 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 database table column. This statistical information is automatically collected and maintained by the database system to help the query optimizer more accurately estimate query plan costs and select the most efficient execution plan.

[0003] Traditional methods for estimating MCV values assume that the distribution of data in a table is similar to a binomial distribution. These MCV estimates focus solely on the proportion of values in a single sample. For example, if the probability of A = 1 in a sample is p, then the probability of A = 1 in the population is also assumed to be p. This approach is based on the assumption that sampling guarantees an equal probability of A = 1 each time a value is drawn, a practice known as sampling with replacement. Traditional estimation methods use a fixed value as a baseline, which can lead to significant deviations when the data is severely skewed. Summary of the Invention

[0004] Technical problems solved

[0005] In response to the above-mentioned shortcomings of the existing technology, the present invention provides a method for correcting the 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, comprising the following steps:

[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] The probability of the values in the preliminary MCV list was estimated sequentially using sampling without replacement;

[0011] Correct the probability of all MCV values;

[0012] Compare the number of rows of the lower bound of the confidence interval of the corrected value 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 form a list of revised MCV values with smaller deviations.

[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 the value;

[0018] Calculate the confidence interval of the frequency of the value in the population based on the 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 value method for calculating the selectivity select is:

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

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

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

[0024] Furthermore, 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 = 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 successful MCV values extracted.

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

[0028] Furthermore, assuming that the population frequency is within 2 standard errors of the sample frequency, the lower bound of the confidence interval is calculated:

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

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

[0031] Furthermore, when calculating the frequency of the value of each row of the sample data, when there are K such values in the total sample N, the probability of sampling n rows and obtaining k such values is:

[0032]

[0033] is the permutation and combination when sampling n rows from the population N, is the permutation and combination when sampling k rows with this value from the population K rows, is the permutation and combination when sampling n - k rows with non - this value from the population N - K rows.

[0034] Beneficial effects

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

[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, a list of MCV values with smaller deviations can be obtained, greatly improving the accuracy of MCV information compared with the prior art, and thus better assisting in improving the performance and efficiency of the database. Description of the drawings

[0037] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments of the present invention, and those of ordinary skill in the art can also obtain other drawings based on these drawings without creative efforts.

[0038] Figure 1 is the flowchart of the high - frequency value correction method of the present invention. Detailed implementation manners

[0039] To make the purpose, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only 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 making any creative efforts shall fall within the scope of protection of the present invention.

[0040] The present invention will be further described below with reference to 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 type 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 solution 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 to continue scanning, so the process of sampling 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 for performing 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 data is loaded, updated, or regularly maintained.

[0045] S20: Probability estimation is performed on the values in the preliminary MCV list in sequence using sampling without replacement; the frequency of the MCV value in the preliminary MCV list is used to approximately calculate the proportion of rows that meet the conditions to the total number of rows, and this proportion 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 steps of the MCV probabilities include:

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

[0048] S32: Calculate the 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 for 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. This is used to determine whether it can be used as 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: comparing the number of rows of the lower bound of the confidence interval of the corrected value with the number of rows in which the lower bound 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 revised MCV values with smaller deviations.

[0054] As a basic explanation, sampling without replacement is a sampling method in which the number of units in a population is gradually reduced during the sampling process, resulting in different probabilities for each unit in the population to be selected. Sampling without replacement also refers to a sampling method in which the entire sample is drawn simultaneously. Database statistics are sampled through a table scan, which is a sampling without replacement process.

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

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

[0057] non-MCV select = 1 - the frequency of the 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 thought of 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 remaining distinct values,

[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 numbers of remaining distinct values excluding MCV and null values.

[0061] In step S32, when extracting n rows of samples from the total number of rows N containing the MCV value (the most common value), the variance and standard deviation of successfully extracting the MCV value K times are calculated. Among them, the variance variance is obtained to represent the degree of dispersion of the number of times of successfully extracting the MCV value in the preliminary MCV list;

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

[0063] Among them, n is the number of sample rows extracted, N is the total number of sample rows, and K is the number of times of successfully extracting the MCV value. Specifically, when 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 variance of this value, the standard deviation stddev of this value is the square root of the variance variance, representing the degree of data fluctuation range.

[0066] In step S33, calculate the lower bound low of the confidence interval with continuity correction. In this embodiment, it is set that the overall frequency is within 2 standard errors of the sample frequency, and calculate the lower bound low value of the confidence interval:

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

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

[0069] In addition, in step S20, when this embodiment uses sampling without replacement to estimate the probability of the MCV value, when calculating the frequency of each row value of the sample data, when there are K such values in the total sample N, the probability of sampling n rows and extracting k such values is:

[0070]

[0071] Is the permutation and combination when sampling n rows from the overall N, Is the permutation and combination when sampling k rows as this value from the overall K rows, Is the permutation and combination when sampling n - k rows of non - this value from the overall N - K rows.

[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 samples.

[0074]

[0075]

[0076] Table 1

[0077] First, the samples extracted from the database should be sampled without replacement. This is because each time a 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 sampled 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 rows 1 and 2: P(A=1)=(K-2) / (N-2);

[0087] When A!=1 in both rows 1 and 2: 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 has A = 1, but the first row has A != 1, a probability P(A = 1) can also be obtained.

[0090] At this point, it can be found that both the first row and the second row may have A = 1 or A != 1, which is a permutation and combination. Expressing it through formulas is too complex, so here it can be simply represented by permutation and combination.

[0091] When sampling the first row, it is actually taking one row from N rows. Represented by permutation and combination, it is Among them, one row has A = 1. Represented by permutation and combination, it is Then

[0092]

[0093] When sampling the second row, it is equivalent to taking two rows from N rows. Represented by permutation and combination, it is

[0094]

[0095] If both rows have A = 1, represented by permutation and combination, it is Then

[0096]

[0097] If one row has A = 1 and one row has A != 1, represented by permutation and combination, it is Then

[0098] In summary, when there are K cases of A = 1 in the total sample N, the probability distribution of sampling n rows and obtaining k cases of A = 1 is:

[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 a smaller deviation can be obtained, greatly improving 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 them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements for some of the technical features; and 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 statistics to assist a query optimizer in more accurately estimating the cost of a query plan, characterized in that, Including: Collect sample data from the overall data, perform frequency statistics on the values of each row of the sample data, and screen to form a preliminary MCV list; Use sampling without replacement to estimate the probability of the values in the preliminary MCV list in turn; Perform correction processing on the probabilities of all MCV values; Compare the lower bound row number of the confidence interval of the value after correction processing with the row number in which it appears in the sample data; If the row number in the sample data is greater than the lower limit of the confidence interval, then consider this value as an MCV and retain it; otherwise, remove this value from the preliminary MCV list; The retained MCV values form a list of MCV values with smaller deviation and after correction; The correction processing steps for the probability of the MCV value include: Calculate the estimated value of the selection rate select of this value; Calculate the variance variance and standard deviation stddev of the hypergeometric distribution of this value; According to the variance variance and standard deviation stddev, calculate the confidence interval of the frequency of this value in the population; Judge the magnitude of the estimated value of the selection rate select and the lower bound low value of the confidence interval; The process for calculating the estimated value of the selection rate select is: Assume that the current value is not an MCV value, and estimate the selection rate non-MCV select at this time; non-MCV select = 1 - the frequency of this value in the sample - the null value frequency; Then, the estimated value of the select of this value = non-MCV select / the number of remaining different values.

2. A method for correcting high-frequency values of database statistics for assisting a query optimizer to more accurately estimate the cost of a query plan according to claim 1, characterized in that Obtain the variance variance of this value, which is used to represent the degree of dispersion of the number of times of successfully extracting MCV values in the preliminary MCV list; variance = n * K * (N - K) * (N - n) / (N * N * (N - 1)) Where, n is the number of sampled rows, N is the total number of sample rows, and K is the number of times of successfully extracting MCV values.

3. A method for correcting high-frequency values of database statistics for assisting a query optimizer to more accurately estimate the cost of a query plan according to claim 1, characterized in that, The standard deviation stddev of this value is the square root of the variance variance, representing the degree of data fluctuation range.

4. A method for correcting high-frequency values of database statistics for assisting a query optimizer to more accurately estimate the cost of a query plan according to claim 1, characterized in that Set the population frequency within 2 standard errors of the sample frequency, and calculate the lower bound low value 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.

5. A method for correcting high-frequency values of database statistics for assisting a query optimizer to more accurately estimate the cost of a query plan according to claim 1, characterized in that When performing frequency calculation on the values of each row of the sample data, when there are K such values in the total sample N, the probability of sampling n rows and extracting k such values is: is the permutation and combination when sampling n rows from the overall N, is the permutation and combination when sampling k rows from the overall K rows to obtain this value, is the permutation and combination when sampling n - k rows from the overall N - K rows that are not this value.

Images